<?php
include_once 'code/init.php';

// echo "test <br/>" ; 

global $agence;

 // $tva_base= dbi_query("SELECT * FROM  TVA ") ; 
// $f=dbi_query("SELECT * FROM TVA ");
// echo "<pre>" ; 
// print_r($tva_base) ; 
// echo "</pre>" ; 
function arrondir ($n) {
	return round($n,2);
}

// si on change les valeurs des config
if(isset($_POST['modif']) )
{	
dbi_query("UPDATE ".TABLE_CONFIG." SET `value`='".$_POST['FactM']."' WHERE `key`='Fact' AND agence_id='".$agence."'");
dbi_query("UPDATE ".TABLE_CONFIG." SET `value`='".$_POST['TVAM']."' WHERE `key`='TVA' AND agence_id='".$agence."'");
dbi_query("UPDATE ".TABLE_CONFIG." SET `value`='".$_POST['RegM']."' WHERE `key`='Reg' AND agence_id='".$agence."'");
dbi_query("UPDATE ".TABLE_CONFIG." SET `value`='".$_POST['JourV']."' WHERE `key`='JourDV' AND agence_id='".$agence."'");
}
elseif(isset($_POST['modifcli']) )
{
	// si on modifie le code d'un client
	foreach( $_POST['client_num'] as $id => $cli_id)
		dbi_query("UPDATE ".TABLE_CLIENT." SET client_numexport='".($cli_id != '' ? $cli_id : '')."' WHERE client_id='".$_POST['client_id'][$id]."'");
}

// on va chercher les valeur des config
$conf=dbi_query("SELECT `key`, `value` FROM ".TABLE_CONFIG." WHERE agence_id=".$agence." AND `key` IN('Fact','TVA','Reg','JourDV') ");
$typF='Fact'; $typT='TVA' ; $typR='Reg'; $typV='JourDV';
while( $co=dbi_fetch_row($conf) )
{
	if( $co['key']=="Fact" ) $typF= $co['value']=='' ? 'Fact' : $co['value'];
	elseif( $co['key']=="TVA" ) $typT= $co['value']=='' ? 'TVA' : $co['value'];
	elseif( $co['key']=="Reg" ) $typR= $co['value']=='' ? 'Reg' : $co['value'];
	elseif( $co['key']=="JourDV" ) $typV= $co['value']=='' ? 'Journal des ventes' : $co['value'];
}

// si on affiche le csv
if(isset($_POST['csv']))
{
	$from = getPostDate('debut') ;
	$to = getPostDate('fin') ;
	header('Content-type: application/csv') ;
	header('Pragma: public');
	header('Cache-Control: private');
	header(sprintf('Content-disposition: attachment; filename="factures du %s au %s.csv"', date( "d-m-Y", (int2time($from)) ), date( "d-m-Y", (int2time($to)) )));
if(!isset($_POST['factu']) )
{
// exit("test") ; 
	$solde=0;

	$q=("SELECT f.fact_id, f.num_fact, c.client_nom, f.date_fact, f.total ,SUM(a.montant) 'avoir', f.data
     FROM ".TABLE_FACT." f LEFT JOIN ".TABLE_AVOIR." a ON f.fact_id=a.fact_id JOIN ".TABLE_CLIENT." c
	 WHERE f.client_id=c.client_id
	 AND f.date_debut BETWEEN '".$from."' AND '".$to."' AND f.agence_id='".$agence."' GROUP BY f.fact_id");

	// on affiche date_fact ou date_debut
	$q=dbi_query($q);
	// for($i=0; $f = dbi_fetch_row($q) ; $i++)
	// {
	
		// $dataF[$i]['num_fact'] = $f['num_fact'];
		// $dataF[$i]['client_nom'] = $f['client_nom'] ;
		// $dataF[$i]['date_fact'] = date( "d/m/Y", (int2time($f['date_fact'])) );
		
		// if ($f['date_fact']<'20120101')
		// {
		// $dataF[$i]['totalHT'] = $f['total']/1.055;
		// $dataF[$i]['totalTTC'] = $f['total'];
		// $dataF[$i]['avoirHT'] = $f['avoir'] === NULL ? 0 : $f['avoir']/1.055;
		// $dataF[$i]['avoirTTC'] = $f['avoir'] === NULL ? 0 : $f['avoir'];
		// }
		// if ($f['date_fact']>='20120101')
		// {
		// $dataF[$i]['totalHT'] = $f['total']/1.196;
		// $dataF[$i]['totalTTC'] = $f['total'];
		// $dataF[$i]['avoirHT'] = $f['avoir'] === NULL ? 0 : $f['avoir']/1.196;
		// $dataF[$i]['avoirTTC'] = $f['avoir'] === NULL ? 0 : $f['avoir'];
		// }
				// if ($f['date_fact']>='20120101')
		// {
		// $dataF[$i]['totalHT'] = $f['total']/1.2;
		// $dataF[$i]['totalTTC'] = $f['total'];
		// $dataF[$i]['avoirHT'] = $f['avoir'] === NULL ? 0 : $f['avoir']/1.2;
		// $dataF[$i]['avoirTTC'] = $f['avoir'] === NULL ? 0 : $f['avoir'];
		// }
		
		/*$dataF[$i]['totalHT'] = $f['total']/1.055;
		$dataF[$i]['totalTTC'] = $f['total'];
		$dataF[$i]['avoirHT'] = $f['avoir'] === NULL ? 0 : $f['avoir']/1.055;
		$dataF[$i]['avoirTTC'] = $f['avoir'] === NULL ? 0 : $f['avoir'];
		*/
		
		
		
		// $req=("SELECT reglement_desc, SUM(montant) 'Reglement' FROM ".TABLE_PAIEMENT." p, ".TABLE_REGLEMENT." r
			   // WHERE p.fact_id='".$f['fact_id']."' AND p.reglement_id=r.reglement_id AND date between '".$from."' AND '".$to."' GROUP BY reglement_desc");
		
		//echo $req;
		
		$req=(" SELECT client_nom , client_numexport, date, montant , reglement_desc  FROM reglement r ,  reglementt rt, client c where rt.client_id = c.client_id and rt.reglement_id = r.reglement_id and agence_id = 47 AND date between '".$from."' AND '".$to."' ");
		
		$req=dbi_query($req);

		// $dataF[$i]['Prelevement'] = 0;
		// $dataF[$i]['Cheque'] = 0;
		// $dataF[$i]['Virement'] = 0;
		// $dataF[$i]['CESU'] = 0;

		
		$format_base = "%s;%s;%s;%s;%s\n" ;

	printf("client_nom;date_fact;Montant;Client numexport;Type de reglement \n") ;
	
		while($reg = dbi_fetch_row($req))
		{
			// if( $reg['reglement_desc']=="Prélèvement") $dataF[$i]['Prelevement'] = $reg['Reglement'];
			// elseif( $reg['reglement_desc']=="Chèque") $dataF[$i]['Cheque'] = $reg['Reglement'];
			// elseif( $reg['reglement_desc']=="Virement") $dataF[$i]['Virement'] = $reg['Reglement'];
			// elseif( $reg['reglement_desc']=="CESU") $dataF[$i]['CESU'] = $reg['Reglement'];
			
			// $dataF[$i]['type'] =$reg['reglement_desc'] ; 
		// }

		// $dataF[$i]['solde']=( $dataF[$i]['totalTTC'] - $dataF[$i]['avoirTTC'] - ($dataF[$i]['Prelevement']+$dataF[$i]['Cheque']+$dataF[$i]['CESU']+$dataF[$i]['Virement']) );

	// }



	

	
		printf($format_base,$reg['client_nom'], int2date($reg['date']), $reg['montant'], $reg['client_numexport'], $reg['reglement_desc']  ) ;
}
}
else
{
// listes de facture différente, template différent
///////////////////////////////////////////////////

    $dateTVA=mktime(0,0,0,'01','01','2012');
	// chaque facture pour l'agence correspondante, trié par un truc particulier !? client ...

	$q=dbi_query("SELECT fact_id, num_fact, date_fact, date_debut, total, f.client_id, client_numexport FROM ".TABLE_FACT." f JOIN ".TABLE_CLIENT." c WHERE c.client_id=f.client_id AND date_fact between '".$from."' AND '".$to."' AND f.agence_id='".$agence."' ORDER BY date_fact, f.client_id, fact_id");
  //  printf("On récupère la liste des factures\n");

	for($i=0; $f = dbi_fetch_row($q) ; $i++)
	{
		$dataF[$i]['num_fact']=$f['num_fact'];
		$dataF[$i]['date_fact'] = date( "dmy", (int2time($f['date_fact'])) );
		$dataF[$i]['totalTTC'] = $f['total'];
		$dataF[$i]['client'] = $f['client_numexport']==NULL ? $f['client_id'] : $f['client_numexport'];
        $dataF[$i]['date_debut'] = date( "dmy", (int2time($f['date_debut'])) );
		// on récupère tous les paiements
//		$req=dbi_query("SELECT montant, date FROM ".TABLE_PAIEMENT." WHERE fact_id='".$f['fact_id']."' AND agence_id='".$agence."' AND `date` between ".$from." AND ".$to." ");
//
//		for( $j=0; $p = dbi_fetch_row($req); $j++ )
//		{
//			$dataF[$i]['paiement'][$j]['montant']=$p['montant'];
//			$dataF[$i]['paiement'][$j]['date']=date( "dmy", (int2time($p['date'])) );
//		}
//
		// on récupère tous les avoirs
		//$req=dbi_query("SELECT montant, date_fact, num_avoir FROM ".TABLE_AVOIR." LEFT JOIN ".TABLE_FACT." USING (fact_id) WHERE fact_id='".$f['fact_id']."' AND agence_id='".$agence."' AND `date_fact` between '".$from."' AND '".$to."'");
		$req=dbi_query("SELECT montant, a.date, num_avoir FROM ".TABLE_AVOIR." a LEFT JOIN ".TABLE_FACT." USING (fact_id) WHERE fact_id='".$f['fact_id']."' AND a.agence_id='".$agence."' AND a.date between '".$from."' AND '".$to."'");

		for( $av=0; $a = dbi_fetch_row($req); $av++ )
		{
			$dataF[$i]['avoir'][$av]['num']=$a['num_avoir'];
			$dataF[$i]['avoir'][$av]['montant']=$a['montant'];
			$dataF[$i]['avoir'][$av]['date']=date( "dmy", (int2time($a['date'])) );
		}
	}
      //  printf("On a récupéré la liste des factures, %d enregistrements\n", $i);
	// rajouter dans config des valeur attribué à fact-TVA et regl
	// on va chercher leur valeur, si rien on laisse écrit fact.TVA..
	// requête d'ajout des valeur pour, à faire sur l'autre base une fois que ça marche
/*	for($ag=0; $ag<=28; $ag++)
	{
		dbi_query("INSERT INTO ".TABLE_CONFIG." (`agence_id`, `key`, `value` ) VALUES ( ".$ag.", 'Fact', '' ) ");
		dbi_query("INSERT INTO ".TABLE_CONFIG." (`agence_id`, `key`, `value` ) VALUES ( ".$ag.", 'TVA', '' ) ");
		dbi_query("INSERT INTO ".TABLE_CONFIG." (`agence_id`, `key`, `value` ) VALUES ( ".$ag.", 'Reg', '' ) ");
		dbi_query("INSERT INTO ".TABLE_CONFIG." (`agence_id`, `key`, `value` ) VALUES ( ".$ag.", 'JourDV', '' ) ");
	}
*/

	$format_avoir = "\n ".$typV.";AV-%s;%s;%.2f;;;".$typF ;
	$format_avoir_tva = "\n ".$typV.";AV-%s;%s;%.2f;;;".$typT ;
	$format_avoir_reg = "\n ".$typV.";AV-%s;%s;;%.2f;%s;".$typR ;
	$format_fact = "\n ".$typV.";FA-%s;%s;;%.2f;;".$typF ;
	$format_fact_tva = "\n ".$typV.";FA-%s;%s;;%.2f;;".$typT ;
	$format_reg = "\n ".$typV.";FA-%s;%s;%.2f;;;%s;" ;

	//printf("id_piece;date_emission;montantD;montantC;client;Type\n");
	$lastfact=substr($from,4,2);
	for($k=0; $k<$i ; $k++)
	{
	//    printf("Facture N°%d - paramètre lastfact=%s\n",$k+1,$lastfact);
      //  printf(" - comparaison avec mois de %s=%s\n",$dataF[$k]['date_debut'],substr($dataF[$k]['date_debut'],2,2));
		$TTC=0;
		$HT=0;
		$TVA=0;


		// liste des avoirs si on change de mois
		if($lastfact!=substr($dataF[$k]['date_fact'],2,2) )
		{
			$req=dbi_query("SELECT montant, a.date, num_avoir, f.client_id, client_numexport, f.date_fact FROM ".TABLE_AVOIR." a JOIN ".TABLE_FACT." f JOIN ".TABLE_CLIENT." c WHERE c.client_id=f.client_id AND a.fact_id=f.fact_id AND a.agence_id='".$agence."' AND SUBSTRING(a.date,5,2)='".$lastfact."' AND a.date BETWEEN '".$from."' AND '".$to."' ORDER BY a.date ");
			//$req=dbi_query("SELECT montant, `date_fact`, num_avoir, f.client_id, client_numexport FROM ".TABLE_AVOIR." a JOIN ".TABLE_FACT." f JOIN ".TABLE_CLIENT." c WHERE c.client_id=f.client_id AND a.fact_id=f.fact_id AND a.agence_id='".$agence."' AND SUBSTRING(`date_fact`,5,2)='".$lastfact."' AND `date_fact` BETWEEN '".$from."' AND '".$to."'".( $_SESSION['client_actif']=='off' ? "" : " AND client_actif='0' ")." ORDER BY `date_fact` ");

			for( $av=0; $a = dbi_fetch_row($req); $av++ )
			{
				$avoirTTC=0;
				$avoirHT=0;
				$avoirTVA=0;

				//$datavoir= date("dmy", int2time($a['date_fact']) );
				$datavoir= date("dmy", int2time($a['date']) );
				$cliexport = $a['client_numexport']==NULL ? $a['client_id'] : $a['client_numexport'];
				$avoirTTC=arrondir($a['montant']);
				$avoirHT=arrondir($avoirTTC/1.2);
                if ($a['date_fact']<'20120101')
                {
                    $avoirHT=arrondir($avoirTTC/1.055);
                }

				$avoirTVA=$avoirTTC-$avoirHT;
				printf($format_avoir_reg, $a['num_avoir'], $datavoir, $avoirTTC, $cliexport );
				printf($format_avoir, $a['num_avoir'], $datavoir, $avoirHT );
				printf($format_avoir_tva, $a['num_avoir'], $datavoir, $avoirTVA );
			}
		}

		// montant de la facture et sa TVA.

		$TTC=arrondir($dataF[$k]['totalTTC']);
    	$HT=arrondir($TTC/1.2);
        $dateFact=mktime(0,0,0,substr($dataF[$k]['date_fact'],2,2),substr($dataF[$k]['date_fact'],0,2),2000+substr($dataF[$k]['date_fact'],4,2));
        //printf("\nDate fact %d - Date TVA %d",,$dateTVA);
        if ($dateFact<$dateTVA)
        {
          $HT=arrondir($TTC/1.055);
        }
		$TVA=$TTC-$HT;

		printf($format_reg, $dataF[$k]['num_fact'], $dataF[$k]['date_fact'], $TTC, $dataF[$k]['client'] );
		printf($format_fact, $dataF[$k]['num_fact'], $dataF[$k]['date_fact'], $HT );
		printf($format_fact_tva, $dataF[$k]['num_fact'], $dataF[$k]['date_fact'], $TVA );
		$lastfact=substr($dataF[$k]['date_fact'],2,2);

		//Liste de paiements !
//		foreach( $dataF[$k]['paiement'] as $donnee )
//		{
//			printf($format_reg, $dataF[$k]['num_fact'], $donnee['date'], $donnee['montant'], $dataF[$k]['client'] );
//		}
		// liste des avoirs
//		foreach( $dataF[$k]['avoir'] as $don )
//		{
//			$don['montantHT']=$don['montant']/1.055;
//			printf($format_avoir, $don['num'], $don['date'], $don['montantHT'], $dataF[$k]['client'] );
//			printf($format_avoir_tva, $don['num'], $don['date'], $don['montant']-$don['montantHT'], $dataF[$k]['client'] );
//			printf($format_avoir_reg, $don['num'], $don['date'], $don['montant'], $dataF[$k]['client'] );
//		}
	}
	// liste des avoirs pour le dernier mois affiché
	//$req=dbi_query("SELECT montant, `date_fact`, num_avoir, f.client_id, client_numexport FROM ".TABLE_AVOIR." a JOIN ".TABLE_FACT." f JOIN ".TABLE_CLIENT." c WHERE c.client_id=f.client_id AND a.fact_id=f.fact_id AND a.agence_id='".$agence."' AND SUBSTRING(`date_fact`,5,2)='".$lastfact."' AND `date_fact` BETWEEN '".$from."' AND '".$to."'".( $_SESSION['client_actif']=='off' ? "" : " AND client_actif='0' ")." ORDER BY `date_fact` ");
	$req=dbi_query("SELECT montant, a.date, num_avoir, f.client_id, client_numexport FROM ".TABLE_AVOIR." a JOIN ".TABLE_FACT." f JOIN ".TABLE_CLIENT." c WHERE c.client_id=f.client_id AND a.fact_id=f.fact_id AND a.agence_id='".$agence."' AND SUBSTRING(a.date,5,2)='".$lastfact."' AND a.date BETWEEN '".$from."' AND '".$to."' ORDER BY a.date ");
	
	for( $av=0; $a = dbi_fetch_row($req); $av++ )
	{
		$avoirTTC=0;
		$avoirHT=0;
		$avoirTVA=0;

		$avoirTTC=arrondir($a['montant']);
		$avoirHT=arrondir($avoirTTC/1.055);
		$avoirTVA=$avoirTTC-$avoirHT;


		//$datavoir= date("dmy", int2time($a['date_fact']) );
		$datavoir= date("dmy", int2time($a['date']) );
		$cliexport = $a['client_numexport']==NULL ? $a['client_id'] : $a['client_numexport'];
		$avoirHT=$a['montant']/1.055;
		printf($format_avoir_reg, $a['num_avoir'], $datavoir, $avoirTTC, $cliexport );
		printf($format_avoir, $a['num_avoir'], $datavoir, $avoirHT );
		printf($format_avoir_tva, $a['num_avoir'], $datavoir, $avoirTVA );
	}
}

}
// Affichage formulaire
else {
	print_header ();

	?>
	<form action="admin_listFactt.php" name="LFact" method="post">
	<table class="td_normal">
	<tr><td class="td_listing1b" align=left > Donn&eacute;es comptable </td></tr>
	<tr><td class="td_listing0">Du <?=date_selection_html("debut", $fact_du)?><br></td></tr>
	<tr><td class="td_listing1">Au <?=date_selection_html("fin", $fact_au)?>&nbsp;&nbsp;</td></tr>
	<input type="hidden" name="csv" value="csv">
	<tr><td class="td_listing0"><input type="submit" value="Export des Reglements" class="bigbutton">
	</td></tr>
	</form><br>
	</table>
	<br />
	<table class="td_normal">
	<tr><td align=left class="td_listing1b"> Modification </td>
	<td align=left class="td_listing1b"> Numero de compte </td></tr>
	<form action="admin_listFactt.php" name="Lmodif" method="post">
	<input type="hidden" name="modif" value="mod">
	<tr><td class="td_listing0"> Facture : </td><td class="td_listing0"><input id="FactM" name="FactM" value="<?=$typF?>"></td></tr>
	<tr><td class="td_listing1"> TVA : </td><td class="td_listing1"><input id="TVAM" name="TVAM" value="<?=$typT?>"></td></tr>
	<tr><td class="td_listing0"> Reglement : </td><td class="td_listing0"><input id="RegM" name="RegM" value="<?=$typR?>"></td></tr>
	<tr><td class="td_listing0"> Journal des ventes : </td><td class="td_listing0"><input id="RegM" name="JourV" value="<?=$typV?>"></td></tr>
  <tr><td class="td_listing0" colspan=2><input type="submit" name="Modifconf" value="Enregistrer" class="bigbutton"></td></tr>
	</form><br>
	</table><br />
	

<!-- Modification des numero pour les clients -->	
<form action="admin_listFactt.php" name="Cmodif" method="post">
<input type="hidden" name="modifcli" value="mod">
<TABLE class="td_normal" cellpadding="0" cellspacing="0" style="margin-top:10px">
<TR>
  <TD class="td_listing1b" width="25%">Client</TD>
  <TD class="td_listing1b" width="25%">Numero de compte</TD>
</TR>
<?
$clie=dbi_query("SELECT client_id, client_nom, client_pnom, client_numexport FROM ".TABLE_CLIENT." WHERE agence_id='".$agence."'".( $_SESSION['client_actif']=='off' ? "" : " AND client_actif='0' ")." AND client_genre!='FORMATION' AND client_prospect!='1' ORDER BY client_nom, client_pnom");

$compteur=0;
while($c = dbi_fetch_row($clie)) 
{
	if (!($compteur % 2)) $style='td_listing0';
	else $style='td_listing1';
?>
<TR>
  <TD class="<?=$style?>"><?=strtoupper($c['client_nom']).' '.ucwords(strtolower($c['client_pnom']))?></TD>
  <input type="hidden" name="client_id[]" value="<?=$c['client_id']?>">
  <TD class="<?=$style?>"><input id="cliM" name="client_num[]" value="<?=$c['client_numexport']?>"></TD>
</TR>
<? $compteur++;
} ?>
<tr><td class="td_listing0" colspan=2><input type="submit" name="climodif" value="Enregistrer" class="bigbutton"></td></tr>
</form>
</TABLE>
	
<? } ?>