<?php
function dateDiff($date1, $date2){
    $diff = abs($date1 - $date2); // abs pour avoir la valeur absolute, ainsi éviter d'avoir une différence négative
    $retour = array();
 
    $tmp = $diff;
    $retour['second'] = $tmp % 60;
 
    $tmp = floor( ($tmp - $retour['second']) /60 );
    $retour['minute'] = $tmp % 60;
 
    $tmp = floor( ($tmp - $retour['minute'])/60 );
    $retour['hour'] = $tmp % 24;
 
    $tmp = floor( ($tmp - $retour['hour'])  /24 );
    $retour['day'] = $tmp;
 
    return $retour;
}

$date_dernier='27/01/2015' ;
$txt  = $date_dernier;
$date = DateTime::createFromFormat('d/m/Y', $txt);
 $date->format('d-m-Y');

  $da = dateDiff($now, $date ) ; 
include_once 'code/init.php';
 
print_header ();

// echo "<pre>" ; 
// print_r($_POST) ; 
// echo "<pre>" ;

// echo "<pre>" ; 
// print_r($_GET) ; 
// echo "<pre>" ;

function get_bureau_drop_down($name = '', $param = '', $value ='', $choose = 'auto')
{
	global $agence ;
	if(!is_array($value))
	$value = array($value) ;
	$ret = "<select onchange='GetIdBureau()'; name=\"$name\" $param>".((in_array('', $value) || in_array('0', $value)) && count($value) == 1 && $choose == 'auto' || $choose === true ? '<option value="" selected  style="font-weight:bold"></option>' : '') ;
	$q = dbi_query("SELECT * FROM bureau WHERE agence_id=".$agence." ORDER BY nom_agence") ;
	while($res = dbi_fetch_row($q)) {
		$ret .= sprintf('<option value="%s"%s>%s </option>', $res['id_bureau'], in_array($res['id_bureau'], $value) ? ' selected' : '', $res['nom_agence']) ;
	}
	return $ret."</select>" ;
}
$where2='';
if($_GET['id'] && !empty($_GET['id']) )
{
   if($_GET['id']>0)
	{
			
		$id=$_GET['id'];
		$tabbursel=dbi_fetch_row((dbi_query('select * from administratif where Admin_ID='.$_GET['id'].' and agence_id='.$agence.''))) ;
		$id_bureau=$id;
		$nom_bureau=$tabbursel['Admin_nom']." ".$tabbursel['Admin_pnom'];
		// $where1=" AND Admin_ID != $id_bureau ";
		$where2=" AND Admin_ID = $id_bureau ";
    }
	elseif($_GET['id']=="INDEFINI")
	{
	$id_bureau=$_GET['id'];
	$where2=" AND client_bureau='0' ";
	$selected='selected="selected"';
	}
}
$CA=0;
?>
<script language="Javascript">
   function GetIdBureau()
   {
		var id="";
  	  id = document.bureau_form.client_bureau.value ;
	if( id > 0 || id=="INDEFINI" )
	  window.location = "solde2.php?id="+id;
	  	else
	window.location = "solde2.php";
   }
</script>
 <style type="text/css">
div#hdp {
     border:1;
position:absolute;
     top:0;
     left:25px;
}
</style>
<br/>
<br/>
<br/>

<table style="position:absolute; top:35px;  right:20px;">
<form id="bureau_form" name="bureau_form" method="POST" action="">
		<tr>
				<td align="left" valign="top" class="td_listing0" > NOM PERSONNEL ADMINISTRATIF :</td>
				<br/>
				<td align="left" valign="top" class="td_listing0">
				<select id="client_bureau" name="client_bureau" onchange="GetIdBureau()";>
				<option selected="selected" value=""> TOUS </option>
				<?php if(isset($id_bureau) && isset($nom_bureau)) { ?>
						<option selected="selected" value="<?=$id_bureau ?>"><?=$nom_bureau?></option>
				<?php } ?>

					<?php 
					$req_admin =dbi_query("SELECT * FROM administratif where agence_id = $agence ") ;
							// $Reqbureau=dbi_query("select * from bureau where agence_id=$agence $where1") or die(mysql_error());
							while($listadmin=dbi_fetch_row($req_admin)){ ?>
				<option value="<?php echo $listadmin['Admin_ID']?>"><?php echo $listadmin['Admin_nom']." ".$listadmin['Admin_pnom'] ?></option>
							<?php } ?>
				<option <?=$selected ?> value="INDEFINI">INDEFINI</option>
				</select>
				  </td>
			</tr>
</form>
</table>
<div id="tab">
<table align="left"   >
        <tbody><tr>
		
		<TH align="center" >ID_client</TH>
          <TH align="center" >Nom client</TH>
		  <TH align="center" >Solde</TH>
		  <TH align="center" >Nombre de factures</TH>
          <TH align="center" >DERNIER PAIEMENT</TH>
          <TH align="center" >ERREUR REGLEMENT</TH>		
		  <TH align="center" >SUIVI</TH>
		  <TH align="center" > Nom Personnel Administratif</TH>
		  <TH align="center" > Ajouter suivi</TH>
		  </tr><tbody>

<?php
$Client=array();
$order=' ASC ';
$query = dbi_query("SELECT f.client_id, client_pnom, client_nom, f.num_fact, c.client_reglement,
f.fact_id, total,total-sum(if(p.montant is null, 0, p.montant))-sum(if(a.montant is null, 0, a.montant)) AS paye,
if(a.montant is null, 0, a.montant) avoir,
f.date_fact, f.relance, IF(client_genre='SERENA',1,0) as serena
FROM ".TABLE_FACT." f
LEFT JOIN ".TABLE_PAIEMENT." p ON f.fact_id=p.fact_id AND f.client_id=p.client_id
LEFT JOIN ".TABLE_AVOIR." a ON f.fact_id = a.fact_id 
LEFT JOIN ".TABLE_CLIENT." c ON c.client_id = f.client_id
WHERE f.agence_id=$agence 
GROUP BY date_fact, f.fact_id, total
HAVING paye > 0.001
ORDER BY client_nom $order , client_pnom $order  , f.date_fact ASC ") ;
while($f = dbi_fetch_row($query))
	{
	$Client[$f['client_id']][]=$f;
	}
	
	
$R=dbi_query("SELECT client_id id, concat(client_nom, ' ', client_pnom) civ, client_prospect, IF(client_genre='SERENA',1,0) as serena, IF(client_genre='FORMATION',1,0) as formation FROM ".TABLE_CLIENT." where agence_id=$agence ".( $_SESSION['client_actif']=='off' ? "" : "AND client_actif='0'")." AND client_prospect ='0'   ORDER BY civ");
$abss = 0 ; 
while($cl = dbi_fetch_row($R))
	{
			$client_id=$cl['id'];
			$a=$client_id ; 
	
		$q = dbi_query("SELECT sum(f.total)
FROM (
	(select -montant total from ".TABLE_PAIEMENT." where client_id= '".$client_id."' AND agence_id='$agence')
	union all
	(
		SELECT total-sum(if(a.montant is null, 0, a.montant)) AS total
		FROM ".TABLE_FACT." f
		LEFT JOIN ".TABLE_AVOIR." a ON f.fact_id = a.fact_id
		WHERE f.client_id= '".$client_id."'
		AND f.agence_id='$agence'
		GROUP BY f.fact_id, f.total
	)
) f") ;

$nb=count($Client[$client_id]);
list($solde) = dbi_fetch_row($q) ;
if(!empty($solde))
// $solde=number_format($solde, 2);
list($regl) = dbi_fetch_row(dbi_query("select date from ".TABLE_PAIEMENT." where client_id=$a order by date DESC  limit 1")) ;
$date_haujourduit = date("d/m/Y");
$date_dernier=int2date($regl) ; 



$date_haujd = explode("/", $date_haujourduit);
$date_dern = explode("/", $date_dernier);

$red = 0 ;
$bleu = 0 ;
$jour_d = $date_dern[0] ; 
$mois_d = $date_dern[1] ;
$anne_d = $date_dern[2] ; 
$jour_h = $date_haujd[0] ;
$mois_h = $date_haujd[1] ;
$anne_h = $date_haujd[2] ;
//echo "---->".$jour_d."<br/>" ;
//echo "---->".$mois_d."<br/>" ;
$dif_mois = $mois_h - $mois_d ;
$comp = 0 ; 
$nbrjour = 0 ;
for($i=$jour_d ; $i <=30 ; $i++ )
{
$comp = $comp + 1 ;
}
$nbrjour = $comp + $jour_h ;

$yass=$anne_h - $anne_d ;
$yass = 0 ;

$req_fact_nn=dbi_query("SELECT date_fact FROM `facture` WHERE facture.client_id = $a and `fact_id` not in ( select fact_id from paiement where facture.client_id = $a) and `fact_id` not in (select fact_id from avoir ) ") ;


$req_fact=dbi_query("SELECT max(date_fact) as max_fact FROM facture,paiement WHERE facture.fact_id=paiement.fact_id and facture.client_id = $a") ;


$ya = dbi_fetch_row($req_fact_nn);
$yb = dbi_fetch_row($req_fact);
list($datefactt) = $ya['date_fact'] ; 
// echo "-----------------g>$datefactt <br/> " ; 

$var = $ya['max(date)'] ;

if($var != NULL)
{
$req_fac=dbi_query("SELECT  max(date_fact) FROM `facture` WHERE `fact_id` in ( select fact_id from paiement where date= $var) ") ;
$yy= dbi_fetch_row($req_fac);
}

 $dateP = $yy['max(date_fact)'] ; 
 $dateF = $y['date_fact'] ; 


$suivmax = dbi_query("SELECT max(client_suivi_id) as idmaxs FROM `client_suivi` WHERE client_id = $a ") ;
$suivf = dbi_fetch_row($suivmax);
$r=$suivf['idmaxs'];

$suiv = dbi_query("SELECT * FROM `client_suivi`,`suivi` , suivi_biais WHERE client_id = $a and client_suivi.suivi_id = `suivi`.suivi_id and client_suivi.suivi_biais_id = suivi_biais.suivi_biais_id and suivi.suivi_id = 10 and client_suivi.client_suivi_clos = '0'  ORDER BY suivi_date DESC") ;


$s = dbi_fetch_row($suiv) ;

$action = '';
if ($s['suivi_biais_id']=='4') 
$action = '<img src="images/icones/warning.gif" alt="Message repondeur" border="0" align="absmiddle">&nbsp;'.$s['suivi_biais_intitule'].' effectu&eacute;';
else if ($s['suivi_programme']=='1') 
$action = '<img src="images/icones/programme.gif" alt="Action programme" border="0" align="absmiddle">&nbsp;Pr&eacute;voir un '.strtolower($s['suivi_biais_intitule']);
else $action = $s['suivi_biais_intitule'].' effectu&eacute;';
if ($s['suivi_biais_id']=='6') 
$action = '<img src="images/controles-16.png" alt="Contr&ocirc;le au domicile du client" border="0" align="absmiddle">&nbsp;Pr&eacute;voir une '.strtolower($s['suivi_biais_intitule']);
// $total+= $l['montant_devis'];
if($s['suivi_date'])
$action = '<b>'.$action.' le '.mysql_datefr($s['suivi_date'],'-','Djma');
else
$action = "";
// echo "---->".$s['suivi_date']."<b/>"
list($datefact,$dddd)=dbi_fetch_row(dbi_query("SELECT max(date_fact), date_fact   FROM facture,paiement WHERE  facture.client_id = $a")) ;
$datefact = int2date($datefact) ; 

 
 // list($nom,$penom)=dbi_fetch_row(dbi_query("SELECT Admin_nom,Admin_pnom FROM administratif where Admin_ID =".$s['Admin_ID'])) ;

 
if(!empty ($datefact) )
{

$date = DateTime::createFromFormat('d/m/Y', $datefact);
$date_dernier_d= $date->format('Y-m-d');

$now   = time();
$date2 = strtotime($date_dernier_d);


$dif_datte_jours = dateDiff($now, $date2);



list($reglement,$reglementDelai) = dbi_fetch_row(dbi_query("SELECT client_reglement,reglement_delai FROM ".TABLE_CLIENT." WHERE client_id='$client_id' AND agence_id='$agence'")) ;

 // echo "----------------->$reglementDelai<br/> " ; 

if( $solde > 0 and ($dif_datte_jours['day'] >30 or $nb > 1 or $ya["date_fact"] < $yb["max_fact"]) and ($dif_datte_jours['day'] >$reglementDelai))
{
if(!empty ($s['Admin_ID']) )
{
 list($nom,$penom)=dbi_fetch_row(dbi_query("SELECT Admin_nom,Admin_pnom FROM administratif where Admin_ID =".$s['Admin_ID'])) ;
 $nom_penom = $nom." ".$penom ;
}
else
{
$nom_penom="" ; 
}
// echo "--->".$s['Admin_ID']."<br/>" ;
$abss++ ; 
// echo "--> $nbrjour <br/>" ;


// if($s['Admin_ID']== $id_bureau)
if(empty($id_bureau) && !isset($id_bureau))
{
// echo "---tous <br/> " ; 
// exit() ; 
$CA+=$solde;
echo '<tr>'; 
echo "<Td align='center' >".$a."</Td>" ; 
echo "<td >".$cl['civ']."</td>";
echo "<td align='center' ><b>$solde</b></td>";
echo "<td align='center'  ><b>$nb</b></td>";
if($anne_h != $anne_d)
{
echo "<td align='center'  > <b> <font color=\"red\">$date_dernier </font></b></td>";
}
elseif( ($nbrjour >= 30 or $dif_mois >= 2) and $mois_d != $mois_h  )
{
echo "<td align='center'  > <b> <font color=\"red\">$date_dernier </font></b></td>";
}
else
{
echo "<td align='center'  ><b>$date_dernier</b></td>";
}
$var_nn=$ya["date_fact"]; 
$var_yes =$yb["max_fact"] ;
if($ya["date_fact"] < $yb["max_fact"] && $solde!=0 && $solde > 0)
{
$var_nn = substr($var_nn,6,2).'/'.substr($var_nn,4,2).'/'.substr($var_nn,0,4);
echo "<td align='center'><b>  <img height=\"16px\" width=\"16px\" src=\"ima.png\" onclick=\"alert('La date de la facture non reglee : $var_nn')\"/>  </td>";
}
else 
{
echo "<td align='center'  ><b> <img height=\"16px\" width=\"16px\" src=\"camera_test.png\"/>  </b></td>"; 
}
$date_now= date('Y-m-d H:i:s');  
$date_now = new DateTime($date_now);
$date_de_suivi = new DateTime($s['suivi_date']);
if($date_de_suivi >= $date_now)
{
?>
<td><a color="red" href="suivi_pop_uppppppppp.php?client_id=<?=$s['client_id']?>&action<?=3?>" class="modal" rel="{handler:'iframe',size:{x:600,y:400}}"><?=$action?><h4> </td>
<?
}
else
{
?>
<td><a color="red" href="suivi_pop_uppppppppp.php?client_id=<?=$s['client_id']?>&action=<?=3?>" class="modal" rel="{handler:'iframe',size:{x:600,y:400}}"><font color="red"><?=$action?></font><h4> </td>
<?
}
// echo "<td align='center' ><b>$action</b></td>";
echo "<td align='center' ><b>$nom_penom </b></td>";
?>
<td align="center"><a href="suivi_pop_up.php?action=nouveau_suivi&=<?=3?>" class="modal" rel="{handler:'iframe',size:{x:600,y:400}}"><?echo " <img height=\"20px\" width=\"20px\" src=\"ajouter.jpg\"/>  "; ?> </td>
<?

echo '</tr>';
}
elseif(isset($_GET) && empty($id_bureau) )
// if(empty($id_bureau))
{
echo "eee<br/>" ; 
$CA+=$solde;
echo '<tr>'; 
echo "<Td align='center' >".$a."</Td>" ; 
echo "<td >".$cl['civ']."</td>";
echo "<td align='center' ><b>$solde</b></td>";
echo "<td align='center'  ><b>$nb</b></td>";
if($anne_h != $anne_d)
{
echo "<td align='center'  > <b> <font color=\"red\">$date_dernier </font></b></td>";
}
elseif( ($nbrjour >= 30 or $dif_mois >= 2) and $mois_d != $mois_h  )
{
echo "<td align='center'  > <b> <font color=\"red\">$date_dernier </font></b></td>";
}
else
{
echo "<td align='center'  ><b>$date_dernier</b></td>";
}
$var_nn=$ya["date_fact"]; 
$var_yes =$yb["max_fact"] ;
if($ya["date_fact"] < $yb["max_fact"] && $solde!=0 && $solde > 0)
{
$var_nn = substr($var_nn,6,2).'/'.substr($var_nn,4,2).'/'.substr($var_nn,0,4);
echo "<td align='center'><b>  <img height=\"16px\" width=\"16px\" src=\"ima.png\" onclick=\"alert('La date de la facture non reglee : $var_nn')\"/>  </td>";
}
else 
{
echo "<td align='center'  ><b> <img height=\"16px\" width=\"16px\" src=\"camera_test.png\"/>  </b></td>"; 
}
$date_now= date('Y-m-d H:i:s');  
$date_now = new DateTime($date_now);
$date_de_suivi = new DateTime($s['suivi_date']);
if($date_de_suivi >= $date_now)
{
?>
<td><a color="red" href="suivi_pop_uppppppppp.php?client_id=<?=$s['client_id']?>&action<?=3?>" class="modal" rel="{handler:'iframe',size:{x:600,y:400}}"><?=$action?><h4> </td>
<?
}
else
{
?>
<td><a color="red" href="suivi_pop_uppppppppp.php?client_id=<?=$s['client_id']?>&action=<?=3?>" class="modal" rel="{handler:'iframe',size:{x:600,y:400}}"><font color="red"><?=$action?></font><h4> </td>
<?
}
// echo "<td align='center' ><b>$action</b></td>";
echo "<td align='center' ><b>$nom_penom </b></td>";
?>
<td align="center"><a href="suivi_pop_up.php?action=nouveau_suivi&=<?=3?>" class="modal" rel="{handler:'iframe',size:{x:600,y:400}}"><?echo " <img height=\"20px\" width=\"20px\" src=\"ajouter.jpg\"/>  "; ?> </td>
<?

echo '</tr>';
}
// echo "ddd<br/>" ; 
elseif($s['Admin_ID']== $id_bureau)
// if(empty($id_bureau))
{
// echo "---bureau <br/> " ; 
// exit() ; 
$CA+=$solde;
echo '<tr>'; 
echo "<Td align='center' >".$a."</Td>" ; 
echo "<td >".$cl['civ']."</td>";
echo "<td align='center' ><b>$solde</b></td>";
echo "<td align='center'  ><b>$nb</b></td>";
if($anne_h != $anne_d)
{
echo "<td align='center'  > <b> <font color=\"red\">$date_dernier </font></b></td>";
}
elseif( ($nbrjour >= 30 or $dif_mois >= 2) and $mois_d != $mois_h  )
{
echo "<td align='center'  > <b> <font color=\"red\">$date_dernier </font></b></td>";
}
else
{
echo "<td align='center'  ><b>$date_dernier</b></td>";
}
$var_nn=$ya["date_fact"]; 
$var_yes =$yb["max_fact"] ;
if($ya["date_fact"] < $yb["max_fact"] && $solde!=0 && $solde > 0)
{
$var_nn = substr($var_nn,6,2).'/'.substr($var_nn,4,2).'/'.substr($var_nn,0,4);
echo "<td align='center'><b>  <img height=\"16px\" width=\"16px\" src=\"ima.png\" onclick=\"alert('La date de la facture non reglee : $var_nn')\"/>  </td>";
}
else 
{
echo "<td align='center'  ><b> <img height=\"16px\" width=\"16px\" src=\"camera_test.png\"/>  </b></td>"; 
}
$date_now= date('Y-m-d H:i:s');  
$date_now = new DateTime($date_now);
$date_de_suivi = new DateTime($s['suivi_date']);
if($date_de_suivi >= $date_now)
{
?>
<td><a color="red" href="suivi_pop_uppppppppp.php?client_id=<?=$s['client_id']?>&action<?=3?>" class="modal" rel="{handler:'iframe',size:{x:600,y:400}}"><?=$action?><h4> </td>
<?
}
else
{
?>
<td><a color="red" href="suivi_pop_uppppppppp.php?client_id=<?=$s['client_id']?>&action=<?=3?>" class="modal" rel="{handler:'iframe',size:{x:600,y:400}}"><font color="red"><?=$action?></font><h4> </td>
<?
}
// echo "<td align='center' ><b>$action</b></td>";
echo "<td align='center' ><b>$nom_penom </b></td>";
?>
<td align="center"><a href="suivi_pop_up.php?action=nouveau_suivi&=<?=3?>" class="modal" rel="{handler:'iframe',size:{x:600,y:400}}"><?echo " <img height=\"20px\" width=\"20px\" src=\"ajouter.jpg\"/>  "; ?> </td>
<?

echo '</tr>';
}
elseif($_GET['id']== 'INDEFINI')
// if(empty($id_bureau))
{
if($s['Admin_ID']== '')
{
// echo "---INDEFINI <br/> " ; 
$CA+=$solde;
echo '<tr>'; 
echo "<Td align='center' >".$a."</Td>" ; 
echo "<td >".$cl['civ']."</td>";
echo "<td align='center' ><b>$solde</b></td>";
echo "<td align='center'  ><b>$nb</b></td>";
if($anne_h != $anne_d)
{
echo "<td align='center'  > <b> <font color=\"red\">$date_dernier </font></b></td>";
}
elseif( ($nbrjour >= 30 or $dif_mois >= 2) and $mois_d != $mois_h  )
{
echo "<td align='center'  > <b> <font color=\"red\">$date_dernier </font></b></td>";
}
else
{
echo "<td align='center'  ><b>$date_dernier</b></td>";
}
$var_nn=$ya["date_fact"]; 
$var_yes =$yb["max_fact"] ;



if($ya["date_fact"] < $yb["max_fact"] && $solde!=0 && $solde > 0)
{
$var_nn = substr($var_nn,6,2).'/'.substr($var_nn,4,2).'/'.substr($var_nn,0,4);
echo "<td align='center'><b>  <img height=\"16px\" width=\"16px\" src=\"ima.png\" onclick=\"alert('La date de la facture non reglee : $var_nn')\"/>  </td>";
}
else 
{
echo "<td align='center'  ><b> <img height=\"16px\" width=\"16px\" src=\"camera_test.png\"/>  </b></td>"; 
}
$date_now= date('Y-m-d H:i:s');  
$date_now = new DateTime($date_now);
$date_de_suivi = new DateTime($s['suivi_date']);
if($date_de_suivi >= $date_now)
{
?>
<td><a color="red" href="suivi_pop_uppppppppp.php?client_id=<?=$s['client_id']?>&action<?=3?>" class="modal" rel="{handler:'iframe',size:{x:600,y:400}}"><?=$action?><h4> </td>
<?
}
else
{
?>
<td><a color="red" href="suivi_pop_uppppppppp.php?client_id=<?=$s['client_id']?>&action=<?=3?>" class="modal" rel="{handler:'iframe',size:{x:600,y:400}}"><font color="red"><?=$action?></font><h4> </td>
<?
}
// echo "<td align='center' ><b>$action</b></td>";
echo "<td align='center' ><b>$nom_penom </b></td>";
?>
<td align="center"><a href="suivi_pop_up.php?action=nouveau_suivi&=<?=3?>" class="modal" rel="{handler:'iframe',size:{x:600,y:400}}"><?echo " <img height=\"20px\" width=\"20px\" src=\"ajouter.jpg\"/>  "; ?> </td>
<?

echo '</tr>';
}
}





























} 
}
 }
$a = 4 ; 
?>

</table>











</div>

				

<div id="hdp"><h1>CA : <?=number_format($CA, 2, ",", " ")?>&nbsp;&euro;&nbsp;&nbsp;</h1></div>