'); echo(''); // ******************************** echo ('

'); echo ('

'); fait_liste_exercice($id_exercice); echo('

Date mise à jour : '.$last_update.'

'); //**************************************** echo(""); //************* echo('

Contrôle

'); verif(); fait_banque_raprochement(); echo('

Synthèse

'); fait_banque(); fait_analytique(); echo('

Bilan Details par Catégorie

'); fait_detail_categorie(7,1,0,"Adhésion",""); //cotisation fait_detail_categorie(19); //fx fonctionnement fait_detail_categorie(16,1,0,"Vente Directe",""); //boutique fait_detail_categorie(21); //bulletin fait_detail_categorie(20); //antenne fait_detail_categorie(28); //salon fait_detail_categorie(30); //opération speciale echo("
"); echo('

Impact Evènements

'); //detail_planning(3,false);//AG //detail_planning(1,false);//retromobile //detail_planning(22,false);//Salon Toulouse //detail_planning(5,false);//Antenne Alsace //detail_planning(4,false);//Antenne Ardennes //detail_planning(7,false);//Antenne Belgique //detail_planning(8,false);//Antenne Berry Sologne //detail_planning(2,false);//Antenne Bourgogne //detail_planning(9,false);//Antenne Idf //detail_planning(11,false);//Antenne Midi Pyrenne detail_planning(26,false);//Trans mediteranné //detail_planning(10);//Antenne Nord pas de Calais detail_planning(12,false);//Antenne Pays de Loire detail_planning(13,false);//Antenne Provence /*detail_planning(14),false;//Antenne Rhone Alpes detail_planning(15,false);//Antenne Touraine */ echo('

Impact poste dépense

'); detail_planning(16);//16 affranchissement detail_planning(23);//23 imprimerie*/ //echo('

Synthèse poste dépense

'); //liste_fee_planning(17);//fourniture function verif() { global $link,$exercice; // sans catégorie anomalie hors initialisation echo(' '); $sql="SELECT * FROM `fee` WHERE" .$exercice." and `categorie`=-1 ORDER BY `dateTransaction` DESC;"; //echo($sql); $somme=$link->query($sql); while($s=$somme->fetch_assoc()) { $sql=" SELECT * FROM `reference` WHERE `id` = ".$s['ventilation']; $compte=$link->query($sql); $ctitre=""; while($c=$compte->fetch_assoc()) { $ctitre=$c['titre']; } //echo($s['dateTransaction']."---".$s['titre']."--".$ctitre."--".number_format($s['montant'], 2, ',', ' ')"
"); echo(""); } echo ("

Operations sans catégorie
Date	Titre	Compte	Montant
".$s['dateTransaction']."	".$s['titre']."	".$ctitre."	".number_format($s['montant'], 2, ',', ' ')." €

"); // non rapprochées echo(' '); $sql="SELECT * FROM `fee` WHERE ".$exercice." and `valide`=0 ORDER BY `dateTransaction` DESC;"; //echo($sql); $somme=$link->query($sql); while($s=$somme->fetch_assoc()) { $sql=" SELECT * FROM `reference` WHERE `id` = ".$s['ventilation']; $compte=$link->query($sql); $ctitre=""; while($c=$compte->fetch_assoc()) { $ctitre=$c['titre']; } //echo($s['dateTransaction']."---".$s['titre']."--".$ctitre."--".number_format($s['montant'], 2, ',', ' ')"
"); if($s['recette']==1){$s_op=$s['montant'];}else {$s_op=-$s['montant'];} echo(""); } echo ("

Operations non rapprochées
Date	Titre	Compte	Montant
".$s['dateTransaction']."	".$s['titre']."	".$ctitre."	".number_format($s_op, 2, ',', ' ')." €

"); fait_detail_categorie(27,1,1,"Credit","Debit"); //transfert echo("

"); } //****************************************************************************** function fait_banque() { global $link,$exercice,$d_deb,$d_fin; $ct_0=0;$ct_s=0;$ct_e=0; echo(' '); //compte courant id=1 fait_ligne_banque(1,$ct_0,$ct_s,$ct_e); //compte epargne id=8 fait_ligne_banque(8,$ct_0,$ct_s,$ct_e); //compte caisse id=11 fait_ligne_banque(11,$ct_0,$ct_s,$ct_e); echo(""); echo ("

Bilan Bancaire
Compte	'.$d_deb.'	'.$d_fin.'	Exercice
"."Total"."	".number_format($ct_0, 2, ',', ' ')." €	".number_format($ct_s, 2, ',', ' ')."	".number_format($ct_e, 2, ',', ' ')." €

"); } //****************************************************************************** function fait_banque_raprochement() { global $link,$exercice,$d_deb,$d_fin; echo(' '); echo('

Situation Bancaire Rapprochée
Compte	'."Kananas".'	'."Non Rapprochée".'	Relevé Banque'); //compte courant id=1 fait_ligne_rapprochement(1); //compte epargne id=8 fait_ligne_rapprochement(8); //compte caisse id=11 fait_ligne_rapprochement(11); echo("

"); } //****************************************************************************** function fait_ligne_rapprochement($id) { global $link,$exercice,$d_deb,$d_fin; $ct_a=0;$ct_e=0;$ct_r=0; $c_r=0;$c_d=0;$c_e=0;$c_s=0;$c_t=""; $sql="SELECT * FROM `reference` WHERE `id` =".$id; //echo($sql); $somme=$link->query($sql); while($s=$somme->fetch_assoc()) {$c_t=$s['titre'];} //analytique $sql="SELECT SUM(`montant`) FROM `fee` WHERE `ventilation` = ".$id." AND ".$exercice." and `recette`= 1"; //echo($sql); $somme=$link->query($sql); while($s=$somme->fetch_assoc()) {$c_r=$s['SUM(`montant`)'];} $sql="SELECT SUM(`montant`) FROM `fee` WHERE `ventilation` = ".$id." AND ".$exercice." and `recette`= 0"; //echo($sql); $somme=$link->query($sql); while($s=$somme->fetch_assoc()) {$c_d=$s['SUM(`montant`)'];} $c_a=$c_r-$c_d; //non rapproche $sql="SELECT SUM(`montant`) FROM `fee` WHERE `ventilation` = ".$id." AND ".$exercice." and `recette`= 1 and `valide`= 0"; //echo($sql); $somme=$link->query($sql); while($s=$somme->fetch_assoc()) {$c_r=$s['SUM(`montant`)'];} $sql="SELECT SUM(`montant`) FROM `fee` WHERE `ventilation` = ".$id." AND ".$exercice." and `recette`= 0 and `valide`= 0"; //echo($sql); $somme=$link->query($sql); while($s=$somme->fetch_assoc()) {$c_d=$s['SUM(`montant`)'];} $c_e=$c_r-$c_d; echo("".$c_t."".number_format($c_a, 2, ',', ' ')." €".number_format($c_e, 2, ',', ' ')." €".number_format($c_a-$c_e, 2, ',', ' ')." €"); } //****************************************************************************** function fait_analytique() { global $link,$exercice; echo(' '); $d=0;$r=0;$e=0;$t=""; $dt=0;$rt=0;$et=0; // cotisation=7 fait_ligne_analytique(7,$rt,$dt,$et); // frais fonctionnement=19 fait_ligne_analytique(19,$rt,$dt,$et); // boutique =16 fait_ligne_analytique(16,$rt,$dt,$et); // bulletin=21 fait_ligne_analytique(21,$rt,$dt,$et); // antennes=20 fait_ligne_analytique(20,$rt,$dt,$et); // salon=28 fait_ligne_analytique(28,$rt,$dt,$et); // opération speciale=30 fait_ligne_analytique(30,$rt,$dt,$et); //total echo(""); echo(""); echo(""); echo(""); echo(""); echo("

Bilan Activité
Poste	Recettes	Dépenses	Exercice
"."Total"."	".number_format($rt, 2, ',', ' ')." €	".number_format($dt, 2, ',', ' ')." €	".number_format($et, 2, ',', ' ')." €

"); } //****************************************************************************** function detail_planning($id,$total=true) { global $link,$exercice; $sql='SELECT SUM(`montant`) FROM `fee` WHERE `idActivity`='.$id; $somme=$link->query($sql); while($s=$somme->fetch_assoc()) {$gt=$s['SUM(`montant`)'];} //if(!$gt){return;} $matrice = array(); $index=array(); for($i=0;$i<100;$i++) {$matrice[$i] = array("","","","");$index[$i]=$i;} $d=0;$r=0;$e=0;$t=""; $dt=0;$rt=0;$et=0; $sql="SELECT * FROM `activity` WHERE `id` = ".$id; $somme=$link->query($sql); while($s=$somme->fetch_assoc()) {$t=$s['titre'];} echo(' '); //recette $sql="SELECT SUM(`montant`),`categorie` FROM `fee` WHERE `idActivity` =".$id." and `recette`=1 AND ".$exercice." group by `categorie`;"; //echo($sql."
"); $somme=$link->query($sql); while($s=$somme->fetch_assoc()) { $c_r=+$s['SUM(`montant`)']; $id_titre= $s['categorie']; $sql="SELECT * FROM `reference` WHERE `id` =".$id_titre; // echo($sql."
"); global $link; $somme2=$link->query($sql); while($s=$somme2->fetch_assoc()) {$titre=$s['titre'];} //echo($titre."************".$id_titre."
"); $matrice[$id_titre][0]=$titre; $matrice[$id_titre][1]=+$c_r; $matrice[$id_titre][3]=+$c_r; $ct_r=$ct_r+$c_r; $ct_e=$ct_e+$c_r; } //depense $sql="SELECT SUM(`montant`),`categorie` FROM `fee` WHERE `idActivity` =".$id." and `recette`=0 AND ".$exercice." group by `categorie`;"; //echo($sql."
"); $somme=$link->query($sql); while($s=$somme->fetch_assoc()) { $c_d=+$s['SUM(`montant`)']; $id_titre= $s['categorie']; $sql="SELECT * FROM `reference` WHERE `id` =".$id_titre; //echo($sql."
"); global $link; $somme2=$link->query($sql); while($s=$somme2->fetch_assoc()) {$titre=$s['titre'];} //echo($titre."************".$id_titre."
"); $matrice[$id_titre][0]=$titre; $matrice[$id_titre][2]=+$c_d; $matrice[$id_titre][3]=$matrice[$id_titre][3]-$c_d; $ct_d=$ct_d+$c_d; $ct_e=$ct_e-$c_d; } uasort($matrice, 'cmp'); foreach($matrice as $m){ //if(strlen($matrice[$i][0]>0)) { if( $m[1]*$m[1]+$m[2]*$m[2]>0){ echo(""); if($m[1]>0){echo("");}else{echo("");} if($m[2]>0){echo("");}else{echo("");} if($m[3]<>0){echo("");}else{echo("");} //echo("-------------------------------------------".$i); } } //total if($total){ echo(""); echo(""); echo(""); echo(""); echo("");} echo("

Chiffres '.$t.'
Catégorie	Recettes	Dépenses	Exercice
".$m[0]."	".number_format($m[1], 2, ',', ' ')." €		".number_format($m[2], 2, ',', ' ')." €		".number_format($m[3], 2, ',', ' ')." €
"."Total"."	".number_format($ct_r, 2, ',', ' ')." €	".number_format($ct_d, 2, ',', ' ')." €	".number_format($ct_e, 2, ',', ' ')." €

"); //var_dump($matrice); } //****************************************************************************** function fait_detail_categorie($id,$recette_factorise=0,$depense_factorise=0,$titre_r="recette",$titre_d="Dépenses") { global $link,$exercice; $ct_d=0;$ct_r=0;$ct_e=0;$libel=""; $matrice = array(); for($i=0;$i<100;$i++) {$matrice[$i] = array("","","","");} $sql="SELECT * FROM `reference` WHERE `id` =".$id; $somme=$link->query($sql); while($s=$somme->fetch_assoc()) {$libel=$s['titre'];} echo(' '); if($recette_factorise!=0) {//recette orphelin factorisé $sql="SELECT SUM(`montant`) FROM `fee` WHERE `idActivity` = -1 AND `recette` = 1 AND ".$exercice." AND `categorie` =".$id; $somme=$link->query($sql); while($s=$somme->fetch_assoc()) { $c_r=$s['SUM(`montant`)']; $ct_r=$ct_r+$c_r; $ct_e=$ct_e+$c_r; echo(""); echo(""); echo(""); } } //depenses $sql="SELECT SUM(`montant`),`idActivity` FROM `fee` WHERE `categorie` = ".$id." and `idActivity`<>-1 and `recette`=0 AND ".$exercice." group by `idActivity`;"; //echo($sql."
"); $somme=$link->query($sql); while($s=$somme->fetch_assoc()) { $c_d=+$s['SUM(`montant`)']; $id_titre= $s['idActivity']; $sql="SELECT * FROM `activity` WHERE `id` =".$id_titre; //echo($sql."
"); global $link; $somme2=$link->query($sql); while($s=$somme2->fetch_assoc()) {$titre=$s['titre'];} //echo($titre."************".$id_titre."
"); $matrice[$id_titre][0]=$titre; $matrice[$id_titre][2]=+$c_d; $matrice[$id_titre][3]=-$c_d; $ct_d=$ct_d+$c_d; $ct_e=$ct_e-$c_d; //echo($c_d."--".$id_titre."--".$titre."
"); } //recettes $sql="SELECT SUM(`montant`),`idActivity` FROM `fee` WHERE `categorie` = ".$id." and `idActivity`<>-1 and `recette`=1 AND ".$exercice." group by `idActivity`;"; //echo($sql."
"); $somme=$link->query($sql); while($s=$somme->fetch_assoc()) { $c_r=$s['SUM(`montant`)']; $id_titre= $s['idActivity']; $sql="SELECT * FROM `activity` WHERE `id` =".$id_titre; //echo($sql."
"); global $link; $somme2=$link->query($sql); while($s=$somme2->fetch_assoc()) {$titre=$s['titre'];} $matrice[$id_titre][0]=$titre; $matrice[$id_titre][1]=+$c_r; $matrice[$id_titre][3]=$matrice[$id_titre][3]+$c_r; $ct_r=$ct_r+$c_r; $ct_e=$ct_e+$c_r; //echo($c_d."--".$id_titre."--".$titre."
"); } uasort($matrice, 'cmp'); foreach($matrice as $m) { //if(strlen($matrice[$i][0]>0)) { if( $m[1]*$m[1]+$m[2]*$m[2]>0){ echo(""); if($m[1]>0){echo("");}else{echo("");} if($m[2]>0){echo("");}else{echo("");} if($m[3]<>0){echo("");}else{echo("");} //echo("-------------------------------------------".$i); } } /*var_dump($matrice); echo("
".strlen($matrice[12][0])); */ //recette orphelin non factorisé if($recette_factorise==0){ $sql="SELECT * FROM `fee` WHERE `idActivity` = -1 AND `recette` = 1 AND ".$exercice." AND `categorie` =".$id; $somme=$link->query($sql); while($s=$somme->fetch_assoc()) { $c_r=$s['montant']; $ct_r=$ct_r+$c_r; $ct_e=$ct_e+$c_r; echo(""); echo(""); echo(""); } } //depenses orphelin non factorisé if($depense_factorise==0){ $sql="SELECT * FROM `fee` WHERE `idActivity` = -1 AND `recette` = 0 AND ".$exercice." AND `categorie` =".$id; $somme=$link->query($sql); while($s=$somme->fetch_assoc()) { $c_d=$s['montant']; $ct_d=$ct_d+$c_d; $ct_e=$ct_e-$c_d; echo(""); echo(""); echo(""); } } if($depense_factorise!=0){//factorisation $sql="SELECT SUM(`montant`) FROM `fee` WHERE `idActivity` = -1 AND `recette` = 0 AND ".$exercice." AND `categorie` =".$id; // $sql="SELECT SUM(`montant`) FROM `fee` WHERE `idActivity` = -1 AND `recette` = 0 AND`dateTransaction` >= '01-01-".$exercice."2021' AND `categorie` =".$id; $somme=$link->query($sql); while($s=$somme->fetch_assoc()) { $c_d=$s['SUM(`montant`)']; $ct_d=$ct_d+$c_d; $ct_e=$ct_e-$c_d; echo(""); echo(""); echo(""); } } //total echo(""); if($ct_r>0){echo("");}else {echo("");} if($ct_d>0){echo("");}else {echo("");} echo(""); echo("

Détail '.$libel.'
Compte	Recettes	Dépenses	Exercice
".$titre_r."	".number_format($c_r, 2, ',', ' ')." €		".number_format($c_r, 2, ',', ' ')." €
".$m[0]."	".number_format($m[1], 2, ',', ' ')." €		".number_format($m[2], 2, ',', ' ')." €		".number_format($m[3], 2, ',', ' ')." €
".$s['titre']."	".number_format($c_r, 2, ',', ' ')." €		".number_format($c_r, 2, ',', ' ')." €
".$s['titre']."		".number_format($c_d, 2, ',', ' ')." €	".number_format(-$c_d, 2, ',', ' ')." €
".$titre_d."		".number_format($c_d, 2, ',', ' ')." €	".number_format(-$c_d, 2, ',', ' ')." €
"."Total"."	".number_format($ct_r, 2, ',', ' ')." €		".number_format($ct_d, 2, ',', ' ')." €		".number_format($ct_e, 2, ',', ' ')." €

"); /*var_dump($matrice);echo("
"); echo($ct_d."-----------".$ct_r."
"); echo($libel);*/ } //***************************************************************** function fait_ligne_banque($id,&$ct_0,&$ct_s,&$ct_e) { global $link,$exercice; //compte courant id=1 $c_0=0;$c_r=0;$c_d=0;$c_e=0;$c_s=0;$c_t=""; $sql="SELECT * FROM `reference` WHERE `id` =".$id; $somme=$link->query($sql); while($s=$somme->fetch_assoc()) {$c_t=$s['titre'];} $sql="SELECT * FROM `fee` WHERE `categorie` = -1 AND `ventilation` =".$id." AND ".$exercice. "ORDER BY `id` DESC"; //echo($sql); $somme=$link->query($sql); while($s=$somme->fetch_assoc()) {$c_0=$s['montant'];} $sql="SELECT SUM(`montant`) FROM `fee` WHERE `ventilation` = ".$id." AND ".$exercice." and `recette`= 1"; //echo($sql); $somme=$link->query($sql); while($s=$somme->fetch_assoc()) {$c_r=$s['SUM(`montant`)'];} $sql="SELECT SUM(`montant`) FROM `fee` WHERE `ventilation` = ".$id." AND ".$exercice." and `recette`= 0"; //echo($sql); $somme=$link->query($sql); while($s=$somme->fetch_assoc()) {$c_d=$s['SUM(`montant`)'];} $c_s=$c_r-$c_d; $c_e=$c_s-$c_0; echo("".$c_t."".number_format($c_0, 2, ',', ' ')." €".number_format($c_s, 2, ',', ' ')." €".number_format($c_e, 2, ',', ' ')." €"); //total $ct_s=$ct_s+$c_s; $ct_0=$ct_0+$c_0; $ct_e=$ct_e+$c_e; } //****************************************************************************** function fait_ligne_analytique($id,&$rt,&$dt,&$et) { global $link,$exercice; echo(""); $sql="SELECT * FROM `reference` WHERE `id` =".$id; $somme=$link->query($sql); //echo($sql); while($s=$somme->fetch_assoc()) {$t=$s['titre'];} echo("".$t.""); //echo($t); $sql="SELECT sum(`montant`) FROM `fee` WHERE ".$exercice." AND `recette` = 1 AND `categorie` = ".$id; //echo($sql); $somme=$link->query($sql); while($s=$somme->fetch_assoc()) {$r=$s['sum(`montant`)'];} if($r>0){echo("".number_format($r, 2, ',', ' ')." €");} else {echo("");} $sql="SELECT sum(`montant`) FROM `fee` WHERE ".$exercice." AND `recette` = 0 AND `categorie` = ".$id; //echo($sql); $somme=$link->query($sql); while($s=$somme->fetch_assoc()) {$d=$s['sum(`montant`)'];} if ($d>0){echo("".number_format($d, 2, ',', ' ')." €");} else {echo("");} $e=$r-$d; echo("".number_format($e, 2, ',', ' ')." €"); $dt=$dt+$d;$rt=$rt+$r;$et=$et+$e; } //****************************************************************************** function liste_fee_planning($id) { global $link,$exercice; echo(""); $sql="SELECT * FROM `activity` WHERE `id` =".$id; //echo($sql); $somme=$link->query($sql); while($s=$somme->fetch_assoc()) {$t=$s['titre'];} //echo($t); echo('
'); $sql="SELECT * FROM `fee` WHERE `idActivity` =".$id." AND ".$exercice; //echo($sql); $somme=$link->query($sql); while($s=$somme->fetch_assoc()) {$l=$s['titre']; echo(""); echo(""); //strftime('%d-%m-%Y',strtotime($s['dateTransaction'])); echo(""); } echo("

Detail Imputation '.$t.'
Date	Libel	Dépenses	Recette
".strftime('%d-%m-%Y',strtotime($s['dateTransaction']))."	".$l."

"); } //****************************************************************************** // Fonction de comparaison function cmp($a, $b) { if($a[3]*$a[3]>= $b[3]*$b[3]){return -1;}else{return 1;} } //****************************************************************************** function get_encours() { global $exercice,$d_deb,$d_fin,$last_update,$link,$id_exercice; $exercice="`dateTransaction` BETWEEN '2021-01-01' AND '2021-08-31'"; //$d_deb="-01/01/2021-";$d_fin="-01/08/2021-"; $sql="SELECT * FROM `encours` WHERE `id` = 1"; $liste=$link->query($sql); while($ex=$liste->fetch_assoc()) { $id_exercice=$ex['id_exercice']; } //echo($id_exercice); $sql="SELECT * FROM `encours` WHERE `id` = 2"; $liste=$link->query($sql); while($ex=$liste->fetch_assoc()) { $last_update=$ex['id_exercice']; } $sql="SELECT * FROM `exercice` WHERE `id` =". $id_exercice; //echo($sql); $liste=$link->query($sql); while($ex=$liste->fetch_assoc()) { $d_deb=$ex['dateDebut']; $d_fin=$ex['dateFin']; $exercice="`dateTransaction` BETWEEN '".$d_deb."' AND '".$d_fin."'"; } } //****************************************************************************** function fait_liste_exercice($id) { echo(''); $link = mysqli_connect("meharicltest.mysql.db", "meharicltest", "Pva131254", "meharicltest"); if (!$link) { echo "Erreur : Impossible de se connecter à MySQL." . PHP_EOL; echo "Errno de débogage : " . mysqli_connect_errno() . PHP_EOL; echo "Erreur de débogage : " . mysqli_connect_error() . PHP_EOL; exit;} $sql="SELECT * FROM `exercice`;"; $liste=$link->query($sql); //echo($sql); while($ex=$liste->fetch_assoc()) { $extitre=$ex['titre']; if($ex['id']==$id){ echo(''.$extitre.'');} else{ echo(''.$extitre.'');} //echo($extitre."---
"); } echo(''); } ?>