<?php 
if (isset($_GET["csv"]) && $_GET["csv"]="true") {
	$csv=true;
	$EXPORT_GLOBAL=true;
	}
else $csv=false;
if (!$csv) include ("header.php");
else {

	include ("../master.php");
	}

statistique_display();

function statistique_display(){
	global $csv, $CONTRATS;
	$sinistre=false;
	$resiliation=false;
	$sql_more="WHERE 1 ";
	if (!isset($_GET["cf"])) $_GET["cf"]=""; //contrat_filtre
	switch ($_GET["cf"]) {
		case "1" : $sql_more.=" AND id_contrat='0' AND resiliation=''"; break; //case "1" : $sql_more.=" AND id_contrat='0' AND remplace_par=''"; break;
		case "2" : $sql_more.="  AND resiliation=''"; break; //case "2" : $sql_more.=" AND id_contrat='0'"; break;
		case "3" : $sql_more.=" AND id_contrat!='0' AND resiliation=''"; break; //case "3" : $sql_more.=" AND id_contrat='0' AND remplace_par!=''"; break;
		case "4" : $sql_more.=" AND id_contrat!='0' AND resiliation!=''"; $resiliation=true; break;
		case "5" : $sql_more.=" "; $sinistre=true; break;
		default  : $sql_more.=" AND id_contrat='0'"; break;
		}
		


	
	if (!$sinistre){
	 	if (!isset($_GET["dfc"])) $_GET["dfc"]="";//date_field_contrat
	 	switch ($_GET["dfc"]) {
	 		case "so" :	$date_order="date_souscription"; break;
	 		case "aj" : $date_order="date_add"; break;
	 		case "ef" : $date_order="date_effet_debut"; break;
	 		case "fe" : $date_order="date_effet_fin"; break;
	 		case "ec" : $date_order="date_echeance"; break;
	 		default   : $date_order="date_effet_debut"; break;
	 		}
	 	if (isset($_GET["ct"]) && $_GET["ct"]!="")  //contrat_type
	 		$sql_more.=" AND id_type='".sn($_GET["ct"])."'";
	 	if (isset($_GET["ida"]) && $_GET["ida"]!="") 
	 		$sql_more.=" AND id_apporteur='".sn($_GET["ida"])."'";
		if (isset($_GET["reg"]) && $_GET["reg"]!="") 
	 		$sql_more.=" AND incomplet_global='".sn($_GET["reg"])."'";
	 	if (isset($_GET["idu"]) && $_GET["idu"]!="") 
	 		$sql_more.=" AND id_utilisateur='".sn($_GET["idu"])."'";
	 	if (isset($_GET["sl"]) && $_GET["sl"]!="") 
	 		$sql_more.=" AND souscription_lieu='".sn($_GET["sl"])."'";
		}
	else {
		switch ($_GET["dfs"]) {
			case "si" : $date_order="s.date_sinistre"; break;
			case "de" : $date_order="s.date_declaration"; break;
			case "aj" : $date_order="s.date_add"; break;
			case "rg" : $date_order="s.date_reglement"; break;
			case "rl" : $date_order="s.date_relance"; break;
			case "cl" : $date_order="s.date_cloture"; break; 
			default   : $date_order="s.date_add"; break;
			}
		$sql_more.=" AND s.id_contrat=c.id_axa";
		if (isset($_GET["ct"]) && $_GET["ct"]!="") 
			$sql_more.=" AND c.id_type='".sn($_GET["ct"])."'";
		if (isset($_GET["st"]) && $_GET["st"]!="") 
			$sql_more.=" AND s.id_type ='".sn($_GET["st"])."'";
		if (isset($_GET["gere"]) && $_GET["gere"]!="") 
			$sql_more.=" AND s.gere='".sn($_GET["gere"])."'";
		if (isset($_GET["exp"]) && $_GET["exp"]!="") {
			// blah blah blah n'importe quoi
			if($CONTRATS["sinistre_expert"][$_GET["exp"]]=="[Entreprise Service] Autre" || $CONTRATS["sinistre_expert"][$_GET["exp"]]=="Autre"){
				foreach ($CONTRATS["sinistre_expert"] as $key=>$value){
					if($CONTRATS["sinistre_expert"][$_GET["exp"]]!="[Entreprise Service] Autre" && $CONTRATS["sinistre_expert"][$_GET["exp"]]!="Autre")
						$sql_more.=" AND s.expert NOT LIKE '".sn($value)."'";
					}
				if($CONTRATS["sinistre_expert"][$_GET["exp"]]=="[Entreprise Service] Autre")
					$sql_more.=" AND s.expert LIKE '[Entreprise Service] %'";
				if($CONTRATS["sinistre_expert"][$_GET["exp"]]=="Autre")
					$sql_more.=" AND s.expert NOT LIKE '[Entreprise Service] %'";
				}
			else $sql_more.=" AND s.expert LIKE '".sn($CONTRATS["sinistre_expert"][$_GET["exp"]])."%'";
			}
		if (isset($_GET["clos"])) {
			if ($_GET["clos"]=="1" || $_GET["clos"]=="0")
				$sql_more.=" AND s.clos='".sn($_GET["clos"])."'";
			elseif ($_GET["clos"]=="-1")
				$sql_more.=" AND s.clos='0' AND s.date_relance !=''";
			}
		}
		
	
	if (!isset($_GET["date_type"])) $sql_more.=" AND YEAR(".$date_order.")='".date("Y")."'";
	if (isset($_GET["date_type"]) && $_GET["date_type"]=="0") {
		if (isset($_GET["dlannee"])) $sql_more.=" AND YEAR(".$date_order.")='".sn($_GET["dlannee"])."'";
		if (isset($_GET["dlmois"]) && $_GET["dlmois"]!="") $sql_more.=" AND MONTH(".$date_order.")='".sn($_GET["dlmois"])."'";
		}
	elseif (isset($_GET["date_type"]) && $_GET["date_type"]=="1") {
		$sql_more.=" AND ".$date_order.">='".sn($_GET["dannee"])."-".sn($_GET["dmois"])."-".sn($_GET["djour"])." 00:00:00'";
		$sql_more.=" AND ".$date_order."<='".sn($_GET["fannee"])."-".sn($_GET["fmois"])."-".sn($_GET["fjour"])." 00:00:00'";
		}
	
	$orderby=$date_order;
	$way="ASC";
	$way_new="d";
				
			
	if (isset($_GET["ob"]) && $_GET["ob"]=="t") $orderby=(($sinistre)?"s":"c").'.id_type';
	if (isset($_GET["ob"]) && $_GET["ob"]=="a") $orderby=(($sinistre)?"s":"c").'.id_axa';
	if (isset($_GET["ob"]) && $_GET["ob"]=="sc")$orderby='s.id_contrat';
	if (isset($_GET["ob"]) && $_GET["ob"]=="m") $orderby='('.(($sinistre)?"s":"c").'.montant+0)';
	if (isset($_GET["ob"]) && $_GET["ob"]=="d") $orderby=$date_order;
	if (isset($_GET["ob"]) && $_GET["ob"]=="s") $orderby=(($sinistre)?"s.date_declaration":"c.date_souscription");
	if (isset($_GET["ob"]) && $_GET["ob"]=="so")$orderby='s.corporel';
	if (isset($_GET["ob"]) && $_GET["ob"]=="sg")$orderby='s.gere';
	if (isset($_GET["ob"]) && $_GET["ob"]=="se")$orderby='s.expert';
	if (isset($_GET["ob"]) && $_GET["ob"]=="cl")$orderby='c.souscription_lieu';
	if (isset($_GET["ob"]) && $_GET["ob"]=="cu")$orderby='c.id_utilisateur';
	if (isset($_GET["ob"]) && $_GET["ob"]=="p")$orderby='incomplet_global';
	if (isset($_GET["way"]) && $_GET["way"]=="a") {
		$way="ASC";
		$way_new="d";
		}
	if (isset($_GET["way"]) && $_GET["way"]=="d"){
		$way="DESC";
		$way_new="a";
		}
	
	$sql="SELECT ".(($sinistre)?"s":"c").".*, ".$date_order." as my_date FROM ".(($sinistre)?"sinistres s, contrats c":"contrats c")." 
		".$sql_more." 
		ORDER BY ".$orderby.' '.$way;
	/*$sql="SELECT ".(($sinistre)?"s":"c").".*, ".$date_order." as my_date , fournie FROM ".(($sinistre)?"sinistres s, contrats c":"contrats c")." 
		LEFT JOIN contrats_pieces ON contrats_pieces.id_contrat = c.id
		".$sql_more." 
		ORDER BY ".$orderby.' '.$way;*/
	$result=mysql_query($sql); echo mysql_error();
	$echo_me="";
	$total=0;
	$nb=0;
	
	while ($row=mysql_fetch_array($result)){
		if ($_GET["cf"]=="4") $res_all=unserialize($row["resiliation"]);
		if ($_GET["cf"]!="4" || ($_GET["cf"]=="4" && ($_GET["res_motif"]=="" || $res_all["motif"]==$_GET["res_motif"]))){
			$total+=$row["montant"];
			$nb++;
			
			if ($sinistre) {
				$id_client=contrat_id_client(contrat_id($row["id_contrat"]));
				if ($csv){					
					//Type de sinistre Sinistre Type Contrat NOM CLIENT Client Date Déclaré le Corp Géré par Montant
					if ($nb==1)	   
						$echo_me.='"Type de sinistre";"Sinistre";"Type";"Contrat";"Nom Client";"Client";"Date";"Déclaré le";"Corporel";"Géré par";"Montant"'."\n";
					
					$echo_me.='"'.str_replace('"', "", ucwords(strtolower(sinistre_type_nom($row["id_type"])))).'";';
					$echo_me.='"'.str_replace('"', "", $row["id_axa"]).'";';
					$echo_me.='"'.str_replace('"', "", ucwords(strtolower( contrat_type_nom(contrat_id_type(contrat_id($row["id_contrat"]))) ))).'";';
					$echo_me.='"'.str_replace('"', "", $row["id_contrat"]).'";';
					$echo_me.='"'.str_replace('"', "", client_nom($id_client)).'";';// EN +
					$echo_me.='"'.str_replace('"', "", client_id_axa($id_client)).'";';
					$echo_me.='"'.str_replace('"', "", dateshort($row["my_date"])).'";';
					$echo_me.='"'.str_replace('"', "", dateshort($row["date_declaration"])).'";';
					$echo_me.='"'.str_replace('"', "", (($row["corporel"]=="0")?"Non":"Oui")).'";';
					$echo_me.='"'.str_replace('"', "", (($row["gere"]=="0")?"Agence":"Compagnie")).'";';
					$echo_me.='"'.str_replace('"', "", $row["expert"]).'";';
					$echo_me.='"'.str_replace('"', "", $row["montant"]).'"';
					$echo_me.="\n";
					}
				else {//Type de sinistre Sinistre Type Contrat NomClient Client Date Déclaré le Corp Géré par Montant
					$echo_me.='<tr>
					<td></td>
					<td bgcolor="#ffffff" align="center">'.ucwords(strtolower(sinistre_type_nom($row["id_type"]))).'</td>
					<td bgcolor="#ffffff"><a target="_blank" href="../collaborateurs/sinistre.php?req=mod&id='.$row["id"].'">'.$row["id_axa"].'</a></td>
					<td bgcolor="#ffffff" align="center">'.ucwords(strtolower( contrat_type_nom(contrat_id_type(contrat_id($row["id_contrat"]))) )).'</td>
					<td bgcolor="#ffffff"><a target="_blank" href="../collaborateurs/sinistre.php?req=liste&id_axa='.$row["id_contrat"].'">'.$row["id_contrat"].'</a></td>
					<td bgcolor="#ffffff" align="center">'.client_nom($id_client).'</td>					
					<td bgcolor="#ffffff" align="center"><a target="_blank" href="../collaborateurs/client.php?req=mod&id='.$id_client.'">'.client_id_axa($id_client).'</a></td>
					<td bgcolor="#ffffff" align="center">'.dateshort($row["my_date"]).'</td>
					<td bgcolor="#ffffff" align="center">'.dateshort($row["date_declaration"]).'</td>
					<td bgcolor="#ffffff" align="center">'.(($row["corporel"]=="0")?"Non":"Oui").'</td>
					<td bgcolor="#ffffff" align="center">'.(($row["gere"]=="0")?"Agence":"Compagnie").'</td>
					<td bgcolor="#ffffff" align="center">'.$row["expert"].'</td>
					<td bgcolor="#ffffff" align="center">'.$row["montant"].'€</td>
					</tr>
					';
					}
				}
			else {//Type Contrat Client Nom Client Date Souscris le Lieu Traité par Montant
				if ($row["id_contrat"]!="0"){
					$id_client=contrat_id_client($row["id_contrat"]);
					$id_type=contrat_id_type($row["id_contrat"]);
					$id_axa=contrat_id_axa($row["id_contrat"]);
					}
				else {
					$id_client=$row["id_client"];
					$id_type=$row["id_type"];
					$id_axa=$row["id_axa"];
					}
				
				if ($resiliation){
					$res_all=contrat_resiliation_get($row["id"]);
					$res_text="";
					foreach ($CONTRATS["resiliation_motif"] as $optgroups=>$options){
						foreach ($options as $option_id=>$option_text){
							if ($option_id==$res_all["motif"]) {
								$res_text=$optgroups." - ".$option_text;
								break;
								}
							}
						}
					}
				
				if ($csv){
					if ($nb==1)	   
						$echo_me.='"Type";"Contrat";"Nom Client";"Client";"Date";"Souscris le";"Lieu";"Traité par";'.(($resiliation)?'Motif de Résiliation";':"").'"Montant"'."\n";
					
					$echo_me.='"'.str_replace('"', "", ucwords(strtolower(contrat_type_nom($id_type)))).'";';
					$echo_me.='"'.str_replace('"', "", $id_axa).'";';
					$echo_me.='"'.str_replace('"', "", client_nom($id_client)).'";';
					$echo_me.='"'.str_replace('"', "", client_id_axa($id_client)).'";';
					$echo_me.='"'.str_replace('"', "", dateshort($row["my_date"])).'";';
					$echo_me.='"'.str_replace('"', "", dateshort($row["date_souscription"])).'";';
					$echo_me.='"'.str_replace('"', "", ((isset($CONTRATS["souscription_lieu"][$row["souscription_lieu"]]))?$CONTRATS["souscription_lieu"][$row["souscription_lieu"]]:"")).'";';
					$echo_me.='"'.str_replace('"', "", utilisateur_nom($row["id_utilisateur"])).'";';
					if ($resiliation) $echo_me.='"'.str_replace('"', "", $res_text).'";';
					$echo_me.='"'.str_replace('"', "", $row["montant"]).'"';
					$echo_me.="\n";
					}
				else {
					$echo_me.='<tr>
					<td></td>
					<td bgcolor="#ffffff" align="center">'.ucwords(strtolower(contrat_type_nom($id_type))).'</td>				
					<td bgcolor="#ffffff"><a target="_blank" href="../collaborateurs/contrat.php?req=liste&id_axa='.$id_axa.'">'.$id_axa.'</a></td>						
					<td bgcolor="#ffffff" align="center">'.client_nom($id_client).'</td>					
					<td bgcolor="#ffffff" align="center"><a target="_blank" href="../collaborateurs/client.php?req=mod&id='.$id_client.'">'.client_id_axa($id_client).'</a></td>	
					<td bgcolor="#ffffff" align="center">'.dateshort($row["my_date"]).'</td>
					<td bgcolor="#ffffff" align="center">'.dateshort($row["date_souscription"]).'</td>
					<td bgcolor="#ffffff" align="center">'.((isset($CONTRATS["souscription_lieu"][$row["souscription_lieu"]]))?$CONTRATS["souscription_lieu"][$row["souscription_lieu"]]:"").'</td>
					<td bgcolor="#ffffff" align="center">'.utilisateur_nom($row["id_utilisateur"]).'</td>
					<td bgcolor="#ffffff" align="center">';
					if($row["incomplet_global"] == 1)
						$echo_me .= '<span style="color:red;font-size:16px;">•</span></td>';
					else
						$echo_me .= '<span style="color:#AED536;font-size:16px;">•</span></td>';
					$echo_me .= '<td bgcolor="#ffffff" align="center">'.$row["montant"].'€</td>
					</tr>';
				
					}
				}
			}
		}
	if (!$csv){
		$qr=$_SERVER["QUERY_STRING"];
		$qr=str_replace("&ob=".((isset($_GET["ob"]))?$_GET["ob"]:"")."&way=".((isset($_GET["way"]))?$_GET["way"]:""), "", $qr);
		
		echo '<center>
		
		<table border=0 cellpadding="4" cellspacing="1" bgcolor="#eeeeee" width="100%">
			<tr class="white" bgcolor="#999999">
				<td width="1%"></td>
				<td ><b>TOTAUX</b></td>
				<td colspan="'.(($sinistre)?10:8).'"><b>'.$nb.' '.(($sinistre)?"sinistre":"contrat").(($nb>1)?"s":"").'</b></td>
				<td align="center" ><b>'.$total.'€</b></td>
			</tr>
		<tr class="white">
			<td bgcolor="#000000" width="1%"></td>
			
			'.(($sinistre)?'
			<td bgcolor="#000000" align="center"><a class="white" href="statistique.php?'.$qr.'&ob=t&way='.$way_new.'"><u><b>Type de sinistre</b></u></a></td>
			<td bgcolor="#000000" align="center"><a class="white" href="statistique.php?'.$qr.'&ob=a&way='.$way_new.'"><u><b>Sinistre</b></u></a></td>
			<td bgcolor="#000000" align="center"><b>Type</b></td>
			<td bgcolor="#000000" align="center"><a class="white" href="statistique.php?'.$qr.'&ob=sc&way='.$way_new.'"><u><b>Contrat</b></u></a></td>
			<td bgcolor="#000000" align="center"><b>Nom Client</b></td>
			<td bgcolor="#000000" align="center"><b>Client</b></td>
			<td bgcolor="#000000" align="center"><a class="white" href="statistique.php?'.$qr.'&ob=d&way='.$way_new.'"><u><b>Date</b></u></a></td>			
			<td bgcolor="#000000" align="center"><a class="white" href="statistique.php?'.$qr.'&ob=s&way='.$way_new.'"><u><b>Déclaré le</b></u></a></td>			
			<td bgcolor="#000000" align="center"><a class="white" href="statistique.php?'.$qr.'&ob=so&way='.$way_new.'"><u><b>Corp</b></u></a></td>
			<td bgcolor="#000000" align="center"><a class="white" href="statistique.php?'.$qr.'&ob=sg&way='.$way_new.'"><u><b>Géré par</b></u></a></td>			
			<td bgcolor="#000000" align="center"><a class="white" href="statistique.php?'.$qr.'&ob=se&way='.$way_new.'"><u><b>Expert</b></u></a></td>
			<td bgcolor="#000000" align="center"><a class="white" href="statistique.php?'.$qr.'&ob=m&way='.$way_new.'"><u><b>Montant</b></u></a></td>
			
			':'
			<td bgcolor="#000000" align="center"><a class="white" href="statistique.php?'.$qr.'&ob=t&way='.$way_new.'"><u><b>Type</b></u></a></td>
			<td bgcolor="#000000" align="center"><a class="white" href="statistique.php?'.$qr.'&ob=a&way='.$way_new.'"><u><b>Contrat</b></u></a></td>
			<td bgcolor="#000000" align="center"><b>Nom Client</b></td>
			<td bgcolor="#000000" align="center"><b>Client</b></td>
			<td bgcolor="#000000" align="center"><a class="white" href="statistique.php?'.$qr.'&ob=d&way='.$way_new.'"><u><b>Date</b></u></a></td>			
			<td bgcolor="#000000" align="center"><a class="white" href="statistique.php?'.$qr.'&ob=s&way='.$way_new.'"><u><b>Souscris le</b></u></a></td>			
			<td bgcolor="#000000" align="center"><a class="white" href="statistique.php?'.$qr.'&ob=cl&way='.$way_new.'"><u><b>Lieu</b></u></a></td>
			<td bgcolor="#000000" align="center"><a class="white" href="statistique.php?'.$qr.'&ob=r&way='.$way_new.'"><u><b>Traité par</b></u></a></td>
			<td bgcolor="#000000" align="center"><a class="white" href="statistique.php?'.$qr.'&ob=p&way='.$way_new.'"><u><b>Pièces</b></u></a></td>
			<td bgcolor="#000000" align="center"><a class="white" href="statistique.php?'.$qr.'&ob=m&way='.$way_new.'"><u><b>Montant</b></u></a></td>
			
			'
			).'
			
		</tr>';
		
		echo $echo_me;
		echo '
			<tr class="white" bgcolor="#999999">
				<td width="1%"></td>
				<td ><b>TOTAUX</b></td>
				<td colspan="'.(($sinistre)?10:8).'"><b>'.$nb.' '.(($sinistre)?"sinistre":"contrat").(($nb>1)?"s":"").'</b></td>
				<td align="center" ><b>'.$total.'€</b></td>
			</tr>
		
		</table></center>';
		}
	else {
		$dh = opendir("./"); 
	   	while ($file = readdir($dh)) {
			if($file!='..' && $file!='.' && $file!='' && strrchr($file, ".")==".csv"){ 
				@unlink($file);
				}
			}
		closedir($dh);
		$uid=uniqid("");
		$file=fopen("export_".$uid.".csv", "w");
		fwrite($file, $echo_me.'"";"'.$nb.'";"";"";"";"";"";""'.(($sinistre)?';"";""':(($resiliation)?';""':'')).';"'.$total.'"');
		fclose($file);
		kick_me("export_".$uid.".csv");
		}
	}




if (!$csv) include ("footer.php");
?>