<?php
session_start();
include_once 'code/init.php';
print_header ();
$style =''; 
?>
<style type="text/css">
<!--
h2 {
	text-align:center;
}
-->
</style>

<div style="width:50%; float:left; vertical-align:top" class="header_ray">
  <h2>Clients</h2>
  <div align="center">
    <?php
	list($count) = dbi_fetch_row(dbi_query("SELECT count(*) FROM ".TABLE_CLIENT." WHERE agence_id = '$agence' AND client_actif='0' AND client_prospect='0' AND client_genre!='FORMATION'")) ;
	list($countp) = dbi_fetch_row(dbi_query("SELECT count(*) FROM ".TABLE_CLIENT." WHERE agence_id = '$agence' AND client_actif='0' AND client_prospect='1'")) ;
	list($agence_nom) = dbi_fetch_row(dbi_query("SELECT agence_nom FROM ".TABLE_AGENCE." WHERE agence_id = $agence")) ;
	printf('<h3><span style="color:green;">%s</span> clients actifs</h3><h3><span style="color:green;">%s</span> prospect%s</h3>', $count, $countp, $countp > 1 ? 's' : '') ;
	$suivi = dbi_query("SELECT client_id, concat(client_nom, ' ', client_pnom) nom FROM ".TABLE_CLIENT." WHERE client_id NOT IN (SELECT client_id FROM ".TABLE_CLIENT_SUIVI." WHERE agence_id = $agence AND client_suivi_clos = '0') AND agence_id = $agence AND client_prospect='1' AND client_actif='0'") ;
	$a_faire = '';
	while ($a = dbi_fetch_row($suivi))
	{
		$a_faire .= '<li'.$style.'><a href="top.php?link=client&amp;page=suivi_gestion&suivi=historique" target="ident">'.$a['nom'].'</a></li>';
	}
	if ($a_faire != '') echo '<h3>Prospects sans suivi actif :</h3><ul style="text-align:left;">'.$a_faire.'</ul><br />';
	
	$rib = dbi_query("SELECT client_id, concat(client_nom, ' ', client_pnom) nom FROM ".TABLE_CLIENT." WHERE (client_reglement = '1' OR client_reglement = '4') AND client_num_compte <= '0' AND agence_id = $agence AND client_prospect='0' AND client_actif='0' AND client_genre!='FORMATION'") ;
	$a_faire = '';
	while ($a = dbi_fetch_row($rib))
	{
		$a_faire .= '<li'.$style.'><a href="top.php?link=client&amp;page=aad&amp;id='.$a['client_id'].'" target="ident">'.$a['nom'].'</a></li>';
	}
	if ($a_faire != '') echo '<h3>Clients sans RIB renseign&eacute; :</h3><ul style="text-align:left;">'.$a_faire.'</ul><br />';
	
	$cles = dbi_query("SELECT client_id, concat(client_nom, ' ', client_pnom) nom FROM ".TABLE_CLIENT." c LEFT JOIN ".TABLE_CLES." USING(client_id) WHERE c.agence_id = '$agence' AND client_prospect='0' AND client_actif='0' AND (cles_nb IS NULL AND cles_nb_telecommande IS NULL) AND client_genre!='FORMATION'") ;
	$a_faire = '';
	while ($a = dbi_fetch_row($cles))
	{
		$a_faire .= '<li'.$style.'><a href="top.php?link=client&amp;page=cles&amp;id='.$a['client_id'].'" target="ident">'.$a['nom'].'</a></li>';
	}
	if ($a_faire != '') echo '<h3>Clients sans remise de clé renseignée :</h3><ul style="text-align:left;">'.$a_faire.'</ul><br />';
	
	$presence = dbi_query("SELECT client_id, concat(client_nom, ' ', client_pnom) nom FROM ".TABLE_CLIENT." WHERE agence_id = '$agence' AND client_present='0' AND client_prospect='0' AND client_actif='0' AND client_genre!='FORMATION'") ;
	$a_faire = '';
	while ($a = dbi_fetch_row($presence))
	{
		$a_faire .= '<li'.$style.'><a href="top.php?link=client&amp;page=aad&amp;id='.$a['client_id'].'" target="ident">'.$a['nom'].'</a></li>';
	}
	if ($a_faire != '') echo '<h3>Clients dont la présence n\'est pas renseignée :</h3><ul style="text-align:left;">'.$a_faire.'</ul><br />';
	
	$zone = dbi_query("SELECT client_id, concat(client_nom, ' ', client_pnom) nom FROM ".TABLE_CLIENT." WHERE agence_id = '$agence' AND client_zones_id='' AND client_prospect='0' AND client_actif='0' AND client_genre!='FORMATION'") ;
	$a_faire = '';
	while ($a = dbi_fetch_row($zone))
	{
		$a_faire .= '<li'.$style.'><a href="top.php?link=client&amp;page=aad&amp;id='.$a['client_id'].'" target="ident">'.$a['nom'].'</a></li>';
	}
	if ($a_faire != '') echo '<h3>Clients dont la zone d\'habitation n\'est pas renseignée :</h3><ul style="text-align:left;">'.$a_faire.'</ul><br />';
	
	?>
  </div>
  </div>
  <div style="width:50%; float:left; vertical-align:top" class="header_ray">
  <h2>Intervenants</h2>
  <div align="center" style="padding-left:20px;">
    <?php
list($count) = dbi_fetch_row(dbi_query("SELECT count(*) FROM ".TABLE_INTERV." WHERE agence_id = $agence AND inter_actif='0' AND inter_candidature != '1'")) ;
list($countc) = dbi_fetch_row(dbi_query("SELECT count(*) FROM ".TABLE_INTERV." WHERE agence_id = $agence AND inter_actif='0' AND inter_candidature != '0'")) ;

printf('<h3><span style="color:green;">%s</span> intervenants actifs</h3><h3><span style="color:green;">%s</span> candidat%s en attente de contrat</h3>', $count, $countc, $countc > 1 ? 's' : '') ;
		
echo '<br />';

$medi_prevoir = dbi_query("SELECT contrat_debut, concat(inter_nom, ' ', inter_pnom) nom, inter_id as id, inter_medidate_suiv FROM ".TABLE_INTERV." i LEFT JOIN ".TABLE_CONTRAT." USING(inter_id) WHERE i.agence_id = $agence AND inter_actif='0' AND inter_candidature != '1' AND (inter_medidate_suiv IS NULL OR inter_medidate_suiv = '0') AND parent_id IS NULL") ;
$a_faire = '';
while ($a = dbi_fetch_row($medi_prevoir))
{
	$debut_contrat = int2time($a['contrat_debut']);
	$echeance_renouvellement_periode_essai = mktime(0,0,0,date("m",$debut_contrat), date("d",$debut_contrat) + 30, date("Y",$debut_contrat));
	
	$aujourdhui = mktime(0,0,0,date("m"), date("d"), date("Y"));
	$alerte_periode_essai = mktime(0,0,0,date("m",$debut_contrat), date("d",$debut_contrat) + 35, date("Y",$debut_contrat));
	$echeance_periode_essai = mktime(0,0,0,date("m",$debut_contrat), date("d",$debut_contrat) + 40, date("Y",$debut_contrat));
	
	if ($aujourdhui>=$echeance_renouvellement_periode_essai) {
		if ($aujourdhui>=$alerte_periode_essai) $style = ' style="color:red; font-weight:bold;"';
		else $style = '';
		$a_faire .= '<li'.$style.'><a href="top.php?link=interv&amp;page=caf&amp;id='.$a['id'].'" target="ident"'.$style.'>'.$a['nom'].'</a> (avant le '.int2date(time2int($echeance_periode_essai)).')</li>';
	}
}
if ($a_faire != '') echo '<h3>Visites médicales à prévoir sous 10 jours :</h3><ul style="text-align:left;">'.$a_faire.'</ul><br />';


$medi_mois = dbi_query("SELECT inter_medi, inter_medidate, inter_pmedi, inter_medidate_suiv, concat(inter_nom, ' ', inter_pnom) nom, inter_id as id FROM ".TABLE_INTERV." WHERE agence_id = $agence AND inter_actif='0' AND inter_candidature != '1' AND inter_medidate_suiv IS NOT NULL AND inter_medidate_suiv != '0' AND RDV_pris != '1' ") ;
$mois_prochain = mktime(0,0,0,date("m"), date("d") + 30, date("Y"));
$aujourdhui = mktime(0,0,0,date("m"), date("d"), date("Y"));
$deja_fait = '';
$a_faire = '';
while ($a = dbi_fetch_row($medi_mois))
{
	$prochaine_visite = int2time($a['inter_medidate_suiv']);
	if ($prochaine_visite<=$mois_prochain) {
		if ($prochaine_visite<=$aujourdhui) {
			$alerte_visite_statut = mktime(0,0,0,date("m",$prochaine_visite), date("d",$prochaine_visite) + 10, date("Y",$prochaine_visite));
			if ($aujourdhui>=$alerte_visite_statut) $style = ' style="color:green; font-weight:bold;"';
			else $style='';
			$deja_fait .= '<li><a href="top.php?link=interv&amp;page=caa&amp;id='.$a['id'].'" target="ident" '.$style.'>'.$a['nom'].'</a> : '.int2date($a['inter_medidate_suiv']).'</li>';
		}
		else {
			list($rappel_ok) = array_values_bd(dbi_fetch_row(dbi_query("SELECT COUNT(inter_id) FROM client_suivi WHERE TO_DAYS(NOW()) - TO_DAYS(suivi_date) <= 60 AND annotation='Rappel de la visite médicale' AND inter_id = '".$a['id']."' AND client_suivi_clos='1'")));
			$alerte_visite_imminente = mktime(0,0,0,date("m",$prochaine_visite), date("d",$prochaine_visite) - 2, date("Y",$prochaine_visite));
			if ($aujourdhui>=$alerte_visite_imminente) {
				if ($rappel_ok!='1') $style = ' style="color:red; font-weight:bold; background:lightyellow;"';
				else $style='';
			}
			else $style='';
			$a_faire .= '<li><a href="top.php?link=interv&amp;page=caa&amp;id='.$a['id'].'" target="ident" '.$style.'>'.$a['nom'].'</a> : '.int2date($a['inter_medidate_suiv']).'</li>';
		}
	}
}
if ($a_faire != '') echo '<h3>Visite médicale pr&eacute;vues sous 30 jours :</h3><ul style="text-align:left;">'.$a_faire.'</ul><br />';
if ($deja_fait != '') echo '<h3>Visites médicales effectu&eacute;es (&agrave; valider) :</h3><ul style="text-align:left;">'.$deja_fait.'</ul><br />';
	

$sejour = dbi_query("SELECT inter_sejourdate, concat(inter_nom, ' ', inter_pnom) nom, inter_id as id FROM ".TABLE_INTERV." WHERE agence_id = $agence AND inter_actif='0' AND inter_candidature != '1' AND inter_sejourdate IS NOT NULL") ;
$a_faire = '';
$mois_prochain = mktime(0,0,0,date("m"), date("d") + 60, date("Y"));
while ($a = dbi_fetch_row($sejour))
{
	$expiration = int2time($a['inter_sejourdate']);
	if ($expiration<$mois_prochain) {
		$a_faire .= '<li><a href="top.php?link=interv&amp;page=caf&amp;id='.$a['id'].'" target="ident">'.$a['nom'].'</a> : '.int2date($a['inter_sejourdate']).'</li>';
	}
}
if ($a_faire != '') echo '<h3>Titres de séjour expirant sous 60 jours :</h3><ul style="text-align:left;">'.$a_faire.'</ul><br />';

$essai_prevoir = dbi_query("SELECT contrat_debut, concat(inter_nom, ' ', inter_pnom) nom, inter_id as id FROM ".TABLE_INTERV." i LEFT JOIN ".TABLE_CONTRAT." USING(inter_id) WHERE i.agence_id = '$agence' AND inter_actif='0' AND inter_candidature = '0' AND contrat_renouvelle = '0' AND parent_id IS NULL") ;
$a_faire = '';
while ($a = dbi_fetch_row($essai_prevoir))
{
	$debut_contrat = int2time($a['contrat_debut']);
	$echeance_renouvellement_periode_essai = mktime(0,0,0,date("m",$debut_contrat), date("d",$debut_contrat) + 30, date("Y",$debut_contrat));
	
	$aujourdhui = mktime(0,0,0,date("m"), date("d"), date("Y"));
	$echeance_periode_essai = mktime(0,0,0,date("m",$debut_contrat), date("d",$debut_contrat) + 25, date("Y",$debut_contrat));
	$alerte_periode_essai = mktime(0,0,0,date("m",$debut_contrat), date("d",$debut_contrat) + 30, date("Y",$debut_contrat));
	
	if ($aujourdhui>=$echeance_renouvellement_periode_essai) {
		if ($aujourdhui>=$alerte_periode_essai) $style = ' style="color:red; font-weight:bold;"';
		else $style = '';
		$a_faire .= '<li'.$style.'><a href="top.php?link=interv&amp;page=cac&amp;id='.$a['id'].'" target="ident"'.$style.'>'.$a['nom'].'</a> (avant le '.int2date(time2int($echeance_periode_essai)).')</li>';
	}
}

if ($a_faire != '') echo '<h3>Périodes d\'essai à renouveller sous 5 jours :</h3><ul style="text-align:left;">'.$a_faire.'</ul><br />';

    $casier = dbi_query("SELECT concat(inter_nom, ' ', inter_pnom) nom, inter_id as id, contrat_debut FROM ".TABLE_INTERV." i LEFT JOIN ".TABLE_CONTRAT." USING(inter_id) WHERE i.agence_id = '$agence' AND inter_actif='0' AND inter_judi != '1' AND inter_candidature != '1' AND parent_id IS NULL") ;
$a_faire = '';
while ($a = dbi_fetch_row($casier))
{
	$debut_contrat = int2time($a['contrat_debut']);
	$aujourdhui = mktime(0,0,0,date("m"), date("d"), date("Y"));
	$alerte_casier = mktime(0,0,0,date("m",$debut_contrat), date("d",$debut_contrat) + 7, date("Y",$debut_contrat));
	if ($aujourdhui>=$alerte_casier) $style = ' style="color:green; font-weight:bold;"';
	else $style='';
	$a_faire .= '<li><a href="top.php?link=interv&amp;page=caf&amp;id='.$a['id'].'" target="ident" '.$style.'>'.$a['nom'].'</a></li>';
}
if ($a_faire != '') echo '<h3>Intervenants sans extrait de CJ :</h3><ul style="text-align:left;">'.$a_faire.'</ul><br />';

    $absence = dbi_query("SELECT concat(inter_nom, ' ', inter_pnom) nom, inter_id as id, date_debut FROM ".TABLE_INTERV." i LEFT JOIN ".TABLE_ABSENCES." USING (inter_id) WHERE i.agence_id = '$agence' AND inter_actif='0' AND inter_candidature != '1' AND cause IS NULL AND absence_id IS NOT NULL GROUP BY inter_id") ;
$a_faire = '';
while ($a = dbi_fetch_row($absence))
{
	$debut_absence = int2time($a['contrat_debut']);
	$aujourdhui = mktime(0,0,0,date("m"), date("d"), date("Y"));
	$alerte_absence = mktime(0,0,0,date("m",$debut_absence), date("d",$debut_absence) + 5, date("Y",$debut_absence));
	if ($aujourdhui>=$alerte_absence) $style = ' style="color:red; font-weight:bold;"';
	else $style='';
	$a_faire .= '<li><a href="top.php?link=interv&amp;page=cae&amp;id='.$a['id'].'" target="ident" '.$style.'>'.$a['nom'].'</a></li>';
}

if ($a_faire != '') echo '<h3>Intervenants ayant une absence injustifiée :</h3><ul style="text-align:left;">'.$a_faire.'</ul><br />';

	$controles_ne = dbi_query("SELECT concat(inter_nom, ' ', inter_pnom) nom, i.inter_id as id, contrat_debut FROM ".TABLE_INTERV." i LEFT JOIN ".TABLE_CONTRAT." c ON i.inter_id = c.inter_id AND c.parent_id IS NULL WHERE i.inter_id NOT IN (SELECT inter_id FROM ".TABLE_CLIENT_SUIVI." cs WHERE i.agence_id = '$agence' AND suivi_biais_id='6' AND suivi_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH)) AND i.agence_id = '$agence' AND inter_candidature='0' AND inter_actif='0'") ;
$a_faire = '';
while ($a = dbi_fetch_row($controles_ne))
{
	$debut_contrat = int2time($a['contrat_debut']);
	$echeance_trimestre = mktime(0,0,0,date("m",$debut_contrat)+3, date("d",$debut_contrat), date("Y",$debut_contrat));
	$aujourdhui = mktime(0,0,0,date("m"), date("d"), date("Y"));
	if ($aujourdhui<=$echeance_trimestre) $a_faire .= '<li><a href="top.php?link=interv&amp;page=caa&amp;id='.$a['id'].'" target="ident">'.$a['nom'].'</a></li>';
}
if ($a_faire != '') echo '<h3>Intervenants n\'ayant pas été contr&ocirc;l&eacute;s depuis plus d\'un mois :</h3><ul style="text-align:left;">'.$a_faire.'</ul><br />';
	$controles = dbi_query("SELECT concat(inter_nom, ' ', inter_pnom) nom, i.inter_id as id, contrat_debut FROM ".TABLE_INTERV." i LEFT JOIN ".TABLE_CONTRAT." c ON i.inter_id = c.inter_id AND c.parent_id IS NULL WHERE i.inter_id NOT IN (SELECT inter_id FROM ".TABLE_CLIENT_SUIVI." cs WHERE i.agence_id = '$agence' AND suivi_biais_id='6' AND suivi_date >= DATE_SUB(NOW(), INTERVAL 3 MONTH)) AND i.agence_id = '$agence' AND inter_candidature='0' AND inter_actif='0'") ;
$a_faire = '';
while ($a = dbi_fetch_row($controles))
{ 
	$debut_contrat = int2time($a['contrat_debut']);
	$echeance_trimestre = mktime(0,0,0,date("m",$debut_contrat)+3, date("d",$debut_contrat), date("Y",$debut_contrat));
	$aujourdhui = mktime(0,0,0,date("m"), date("d"), date("Y"));
	if ($echeance_trimestre<=$aujourdhui) $a_faire .= '<li><a href="top.php?link=interv&amp;page=caa&amp;id='.$a['id'].'" target="ident">'.$a['nom'].'</a></li>';
}
if ($a_faire != '') echo '<h3>Intervenants n\'ayant pas été contr&ocirc;l&eacute;s depuis plus de trois mois :</h3><ul style="text-align:left;">'.$a_faire.'</ul><br />';

	
	$zone = dbi_query("SELECT concat(inter_nom, ' ', inter_pnom) nom, inter_id FROM ".TABLE_INTERV." i WHERE i.agence_id = '$agence' AND inter_zones_id='' AND inter_candidature='0' AND inter_actif='0'") ;
	$a_faire = '';
	while ($a = dbi_fetch_row($zone))
	{
		$a_faire .= '<li'.$style.'><a href="top.php?link=interv&amp;page=caf&amp;id='.$a['inter_id'].'" target="ident">'.$a['nom'].'</a></li>';
	}
	if ($a_faire != '') echo '<h3>Intervenants dont la zone d\'intervention n\'est pas renseignée :</h3><ul style="text-align:left;">'.$a_faire.'</ul><br />';
	// while($a) ; 

  ?>
  </div>
</div>
</body></html>