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

print_header ('', '', $BodyX );
$style="td_listing1";
$le_mois_dernier = sprintf("%04d%02d%02d", date('Y'), date('m')-1, date('d'));
$ce_mois_ci = sprintf("%04d%02d%02d", date('Y'), date('m'), date('d'));
$debut_de_ce_mois = sprintf("%04d%02d%02d", date('Y'), date('m'), 1);
$fin_de_ce_mois = sprintf("%04d%02d%02d", date('Y'), date('m'), date('t'));



if(!empty($_POST))
{
	$from = getPostDate('debut') ;
	$to = getPostDate('fin') ;

	$fact_du =$from;
	$fact_au = $to;
	
	
} else {
	$from = $debut_de_ce_mois ;
	$to = $fin_de_ce_mois ;}



?>



		   
		<form action="" name="stat" method="post">
		Du <?=date_selection_html("debut", $fact_du)?><br>
		Au <?=date_selection_html("fin", $fact_au)?>&nbsp;&nbsp;
		<input type="submit" value="Voir les stats" class="bigbutton">
		</form><br>
<?php
if(!empty($_POST))
{
	$from = getPostDate('debut') ;
	$to = getPostDate('fin') ;
	$rc1 = dbi_query("SELECT client_id FROM ".TABLE_CLIENT." WHERE client_actif='0'  AND client_prospect=1 AND agence_id=$agence AND client_genre!='FORMATION' ") ;	
$CLIENTIN='( 0  ';
	while($cl1 = dbi_fetch_row($rc1))
	{
			$CLIENTIN.=' , '.$cl1['client_id'];
	}
$CLIENTIN.=') '	;
$ClientSql=array();
	$sql1="SELECT c.Client_ID FROM chantier c , client cl WHERE c.Client_ID IN $CLIENTIN AND c.agence_id=$agence AND Chantier_ID  IN 
( SELECT Chantier_ID FROM fact_chantier fc WHERE fc.Chantier_ID=c.Chantier_ID AND chantier_recur = '0'
 AND (Chantier_debut <= $from AND (Chantier_fin =0 OR Chantier_fin  IS NULL OR Chantier_fin  >= $to ))

)
AND Chantier_ID NOT IN 
( 
SELECT Chantier_ID FROM fact_chantier fc WHERE fc.Chantier_ID=c.Chantier_ID AND fc.chantier_recur = '0' 
AND ((fc.Chantier_debut < $to AND fc.Chantier_debut > $from ) OR (fc.Chantier_fin < $to AND fc.Chantier_fin > $from))

)  
  
	AND cl.Client_ID=c.Client_ID ORDER BY  client_nom, client_pnom";
	
$sql2="SELECT c.Client_ID FROM chantier c , client cl WHERE c.Client_ID IN $CLIENTIN AND c.agence_id=$agence AND Chantier_ID IN 
( 
SELECT Chantier_ID FROM fact_chantier fc WHERE fc.Chantier_ID=c.Chantier_ID AND fc.chantier_recur = '0' 
AND ((fc.Chantier_debut < $to AND fc.Chantier_debut > $from ) OR (fc.Chantier_fin < $to AND fc.Chantier_fin > $from))

)  
AND cl.Client_ID=c.Client_ID ORDER BY  client_nom, client_pnom";

	$sql3="SELECT c.Client_ID FROM chantier c , client cl WHERE c.Client_ID IN $CLIENTIN AND c.agence_id=$agence AND Chantier_ID IN 
( SELECT Chantier_ID FROM fact_chantier fc WHERE fc.Chantier_ID=c.Chantier_ID  AND  chantier_recur = '1' )
	AND cl.Client_ID=c.Client_ID ORDER BY  client_nom, client_pnom";	

	$TABSQL=array($sql1,$sql2,$sql3);	
	
	
		for($s=0;$s<count($TABSQL);$s++)
		{
		if ($s==0)
		echo "<h2>chantiers réguliers</h2>";
		elseif ($s==1)
		echo "<h2/>Nouveaux chantiers et chantiers arrêtés<h2/>";
		elseif ($s==2)
		echo "<h2>chantiers ponctuels</h2>";
		?>
		 <div id="tab">
<TABLE width=100%>
<thead>
<TR>
  <th width=40%>Client</th>
  <th width=40%>Chantier</th>
  <th width=20%>Facture</th>
</TR>
</thead>
    <tbody>
	<?


		$R=dbi_query($TABSQL[$s]);
		while($CHN = dbi_fetch_row($R))
		{
$client_id = $CHN[0];
$rf = dbi_query("SELECT * FROM ".TABLE_FACT." WHERE date_debut>=$from AND date_fin<=$to AND agence_id=$agence AND client_id=$client_id") ;
$NB = 0;
 
while($fs = dbi_fetch_row($rf))
	{
	list($data, $factures, $acompte, $attdata, $option) = unserialize($fs['data']) ;
	
	foreach($data as $id => $l)
		{
				if($l['type'] == 'c')
			{
		$NB++;		
			}
		}	
	}
	
	$chantier = dbi_fetch_row(dbi_query("SELECT * FROM ".chantier." c WHERE Client_ID = $client_id AND agence_id=$agence  "));
	$chantier_id = $chantier['Chantier_ID'];
	if($chantier){
		// $qch = dbi_query("SELECT * FROM ".fact_chantier." fc WHERE Chantier_ID = ".$chantier['Chantier_ID']." AND ( Chantier_debut <= $from OR Chantier_debut between $from and $to) AND (Chantier_fin IS NULL OR Chantier_fin > $from OR Chantier_fin =0 )");
        $qch = dbi_query("SELECT * FROM ".fact_chantier." fc WHERE Chantier_ID = ".$chantier['Chantier_ID']." 
		AND ((chantier_recur != '1' AND (Chantier_debut <= $from OR Chantier_debut between $from and $to) AND (Chantier_fin IS NULL OR Chantier_fin > $from OR Chantier_fin =0) )
		OR (chantier_recur = '1' AND Chantier_fin between $from and $to) )");
		
		
				}
			
		
	// echo mysql_num_rows($rf) ==0 || mysql_num_rows($qch).' || '.$NB	;
if( mysql_num_rows($qch) > $NB )
{
$c = dbi_fetch_row(dbi_query("SELECT * FROM ".TABLE_CLIENT." WHERE client_id=$client_id"));
echo '<TR>';
?>
  <TD><A href="top.php?link=client&amp;page=aaa&amp;id=<?=$c['client_id']?>" target="ident"><?=strtoupper($c['client_nom']).' '.ucwords(strtolower($c['client_pnom']))?></A></TD>
 <?php 
  echo '<td>';
  

				while($ch = dbi_fetch_row($qch))
				{
		echo $ch['Chantier_designation'].'<br/>';		
				
				}
				
  echo '</td>';
 
?>
<td style="text-align:center;">
<?php if($fs['fact_id'] ){ ?>
<a href="fact.php?fid=<?=$fs['fact_id']?>" target="_blank" style="cursor:pointer; font-weight:bold;color: white; text-decoration:none;" class="bigbutton">Voir</a>
<?php } ?>
</td>
</TR>
<?php	
}
		?>





<?php	

}

echo '</tbody></table></div>';	
		}// sql
		

		}