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

// echo "bonjour  $agence <br/>" ; 

print_header ();
// exit() ; 
$qq= dbi_query("SELECT client_id  FROM  client where agence_id = $agence and client_actif = '0' order by client_nom ") ; 


		$du = sprintf("%04d%02d%02d", $_POST['duyear'], $_POST['dumonth'], $_POST['duday']) ;
		$au = sprintf("%04d%02d%02d", $_POST['auyear'], $_POST['aumonth'], $_POST['auday']) ;
		
		// echo "===> $du <br/>" ;
		// echo "===> $au <br/>" ;
		if(!empty($_POST))
		{

	  ?>
	  
<table BORDER="1px">
	 <tr>
			 
			  <th>nom de client </th>
			 <th>somme  paiement</th>
			 <th> somme facture  </th>
			 <th> somme avoir  </th>
			 <th> sm avoir + sm factur </th>
			 <th> diff </th>

	 </tr>	

      <?
	  $som_f_a = 0 ;

	  while($a=dbi_fetch_row($qq))
{

	   $som_f_a = 0 ;
 
  $diff =  0 ; 
  $smmpaiment  =  0  ; 
  $sommeAvoir = 0 ; 
// SELECT SUM( total ) 
// FROM (

// SELECT DISTINCT f.fact_id, num_fact, total, date_debut, date_fin, date_fact
// FROM paiement p
// INNER JOIN facture f ON f.fact_id = p.fact_id
// WHERE p.client_id =  '09772'
// AND p.agence_id =2
// AND p.date >=  '20160101'
// AND p.date <=  '20161231'
// ) AS B



$cId = $a['client_id'] ; 
	// list($smfacture) = dbi_fetch_row(dbi_query("
	
	// SELECT SUM( total ) 
// FROM (
	// SELECT  DISTINCT f.fact_id, num_fact, total, date_debut, date_fin, date_fact
 // FROM  ".TABLE_FACT." f
	// WHERE f.client_id='$cId' AND f.agence_id=$agence and f.date_fact >= '$du' and f.date_fact <= '$au' ) as B")) ;
	
	list($smfacture) = dbi_fetch_row(dbi_query("
	SELECT SUM( total ) 
FROM (

SELECT DISTINCT f.fact_id, num_fact, total, date_debut, date_fin, date_fact
FROM paiement p
INNER JOIN facture f ON f.fact_id = p.fact_id
WHERE p.client_id =  '$cId'
AND p.agence_id =$agence
AND p.date >=  '$du'
AND p.date <=  '$au'
) AS B
"));
list($sommeAvoir)= dbi_fetch_row(dbi_query("select sum(a.montant) from avoir a , ".TABLE_FACT." f where f.fact_id= a.fact_id AND f.client_id='$cId' AND a.agence_id=$agence and a.date >= '$du' and a.date <= '$au' ")) ; 
 
	list($sommeAvoir) = dbi_fetch_row(dbi_query("


SELECT sum(a.montant) FROM paiement p
INNER JOIN avoir a  ON a.fact_id = p.fact_id
WHERE p.client_id =  '$cId'
AND p.agence_id =$agence
AND p.date >=  '$du'
AND p.date <=  '$au'

"));

	
	list($smmpaiment) = dbi_fetch_row(dbi_query("SELECT SUM(p.montant) FROM ".TABLE_PAIEMENT." p  WHERE p.client_id='$cId' AND p.agence_id=$agence and p.date >= '$du' and p.date <= '$au'")) ;
	list($nomc , $pnomc) = dbi_fetch_row(dbi_query("SELECT client_nom ,client_pnom  FROM  `client`  WHERE  client_id =$cId")) ;
  
  
 
 $som_f_a = $smfacture - $sommeAvoir ;
 
 
 $som_f = intval($som_f_a)  ;
  $smmpai = intval($smmpaiment)  ;
 $diff=  $som_f - $smmpai ; 
// echo " $som_f_a ___ $smmpaiment ____ $diff <br/>" ;



 ?>
  <tr>
  <?
 if($diff == 0)
 {
?>
 <td> <?=$nomc." ".$pnomc ?>  </td>
  <td> <?=$smmpaiment ?>  </td>
  <td> <?=$smfacture ?>  </td>
  <td> <?=$sommeAvoir ?>  </td>
  <td> <?=$som_f_a ?>  </td>
   <td  > <?=$diff ?>  </td>
   <?
  }
  else
  {
  ?>
   <td bgcolor="#A5DF010"> <?=$nomc." ".$pnomc ?>  </td>
  <td bgcolor="#A5DF010" > <?=$smmpaiment ?>  </td>
  <td bgcolor="#A5DF010" > <?=$smfacture ?>  </td>
  <td bgcolor="#A5DF010" > <?=$sommeAvoir ?>  </td>
  <td bgcolor="#A5DF010" > <?=$som_f_a ?>  </td>
   <td bgcolor="#A5DF010" > <?=$diff ?>  </td>
   <?
  }
  ?>
 </tr>
 <?
 $som_f_a = 0 ;
 
  $diff =  0 ; 
  $smmpaiment  =  0  ; 
}

	  ?>

	 
</table>
<?




}
?>
      
          <form action="" method="post">
            <input type="hidden" name="type" value="attestation" />
            <table width="400">
            <tr>
              Du :
              <?php print_date_selection('du', $att_du)?>
            </tr>
            <tr>
              Au : 
              <?php print_date_selection('au', $att_au)?>
            </tr>
 
           
            <tr>
              <input type="submit" value="Cr&eacute;er" class="bigbutton" onClick="this.form.submit();">
          </form>
		
      </tr>
    </table>

</body>
</html>