//------------------------------------------------------------------------------------------
//NOUS RECUPERONS LES VALEURS DATES

$choix_date_debut = $_POST['date_debut'];
$choix_date_fin = $_POST['date_fin'];

//FORMAT BASE SQL
$format_choix_date_debut = $_POST['date_debut'];
$format_choix_date_fin = $_POST['date_fin'];
//------------------------------------------------------------------------------------------
//echo $format_choix_date_debut;
//echo $format_choix_date_fin;

//-------------------------------- menu placer a droite ------------------------------------


?>

<fieldset class="fieldset">
<legend class="legend">Exportation Termin&eacute;e</legend>
<?php

$date = date("d-m-Y");
$heure = date("H:i");


////////////////////////////////////////////////////////////////////////////////////////
//
//     FONCTION REALISANT LA REDIRECTION DE L'AFFICHAGE PHP VERS UN FICHIER EXCEL     //
//
////////////////////////////////////////////////////////////////////////////////////////
//
// fonction de gestion du buffer d'affichage redirig� par ob_end_flush()
function enregistrer_sous_excel($buffer)
{
  // enregistre l'affichage dans un fichier Excel
  file_put_contents ("exportation_opportunites/opportunites.xls", $buffer);
  // affiche le r�sultat sur le navigateur client
  return "
   <meta charset='UTF-8'> 
  <table width=\"100%\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\" style=\"padding:30px;\">
  <tr>
    <td width=\"240\" valign=\"middle\"  align=\"center\">Le fichier a &eacute;t&eacute; cr&eacute;&eacute; ==></td>
    <td valign=\"middle\"><a href=\"exportation_opportunites/opportunitesl.xls\"><img src=\"images/excel_f.png\" alt=\"Téléchagement des inscriptions\"></a></td>
  </tr>
</table>";
}
// redirection de l'affichage vers la fonction d�sign�e
ob_start('enregistrer_sous_excel');

///////////////////////////////////////////////////////////////////////////////
//
//     PROGRAMME REALISANT L'AFFICHAGE DES DONNEES A EXPORTER VERS EXCEL     //
//
///////////////////////////////////////////////////////////////////////////////
//

//CHOIX DE LA MONNAIE



setlocale(LC_MONETARY, 'it_IT');
// L. 1.234,56


// connexion �  la base de donn�es
 $conn = db_connect();

// cr�er la liste des membres
 $listeMembres = "" ; 
 $listeContractes = "" ; 
 $listeAbandonnees = "";}
$sql = "SELECT * FROM ".OPPORTUNITES." WHERE derniere_date_modif BETWEEN  (str_to_date( '".$choix_date_debut."' , '%d/%m/%Y %H:%i:%s')) AND (str_to_date( '".$choix_date_fin."' , '%d/%m/%Y %H:%i:%s'))";
//echo $sql;
$result = mysql_query($sql) or die(mysql_error());
$addition = "0";
while($result && $row=mysql_fetch_array($result))
{



	$listeMembres .= "<tr>
	<td>".utf8_encode($row['num_opportunite'])."</td>
<td>".utf8_encode($row['entite'])."</td>
<td>".utf8_encode($row['contact_client'])."</td>
<td>".utf8_encode($row['nom_opportunite'])."</td>
<td>".utf8_encode(htmlentities($row['ligne_service_1']))."</td>
<td>".utf8_encode($row['ligne_service_2'])."</td>
<td>".utf8_encode(htmlentities($row['opportunite_commerciale']))."</td>
<td>".utf8_encode(htmlentities($row['quadri_courtier']))."</td>
<td>".utf8_encode(htmlentities($row['estimation_echeance_opportunite']))."</td>
<td>".utf8_encode(htmlentities($row['total_revenus']))."</td>";


$addition = $addition + $row['total_revenus'];

	$listeContractes .= "<tr> 
	<td>".utf8_encode($row['num_opportunite'])."</td>
<td>".utf8_encode($row['entite'])."</td>
<td>".utf8_encode($row['contact_client'])."</td>
<td>".utf8_encode($row['nom_opportunite'])."</td>
<td>".utf8_encode(htmlentities($row['ligne_service_1']))."</td>
<td>".utf8_encode($row['ligne_service_2'])."</td>
<td>".utf8_encode(htmlentities($row['quadri_courtier']))."</td>
<td style='text-align:right;'>".money_format('%.2n', ($row['total_revenus']))."</td>";


	$listeAbandonnees .= "<tr>
<td>".utf8_encode($row['num_opportunite'])."</td>
<td>".utf8_encode($row['entite'])."</td>
<td>".utf8_encode($row['contact_client'])."</td>
<td>".utf8_encode($row['nom_opportunite'])."</td>
<td>".utf8_encode(htmlentities($row['ligne_service_1']))."</td>
<td>".utf8_encode($row['ligne_service_2'])."</td>
<td>".utf8_encode(htmlentities($row['quadri_courtier']))."</td>
<td>".utf8_encode(htmlentities($row['raison_perdue']))."</td>";
	}




// fin de connexion
mysql_close();

// affichage de la liste des membres sous forme d'une tableau HTML
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<style>
.test th {
background-color:rgb(255,127,0);

}

</style>
<title>Liste des opportunités</title>
</head>
<body>
<!-- <h1 style="color:#5bc210"><center>Exportation Base de donn�es CRM Prolival</center></h1>-->
<table>
<?php // echo $format_choix_date_debut; ?>
<tr> <th style="text-align:right;font-size:18px;color:rgb(226,107,10);" colspan="10">REUNION  <?php echo date("d-m-Y"); ?></th> </tr>
</table>
<br>
<br>
<table>
<tr> <th style="text-align:left;font-size:18px;color:rgb(226,107,10);" colspan="10">NOUVELLES OPPORTUNITES RENTREES DEPUIS LE <?php echo $choix_date_debut;?></th> </tr>
</table>



<table border="1" cellpadding="0" cellspacing="10">
<tr  class="test" >
<th>N&deg;</th>
<th>Client/Prospect</th>
<th>Contact</th>
<th>Nom de l'opportunit&eacute;</th>
<th>Ligne de Service 1</th>
<th>Ligne de Service 2</th>
<th>Statut</th>
<th>Commercial</th>
<th>Estimation Ech&eacute;ance</th>
<th>Revenu</th>
</tr>
  
<?php  echo $listeMembres;?>
</table>

<br>
<br>


<table>
<tr> <th style="text-align:left;font-size:18px;color:rgb(226,107,10);" colspan="10">OPPORTUNITES CONTRACTEES DEPUIS LE <?php echo $choix_date_debut; ?></th> </tr>
</table>
<table border="1" cellpadding="0" cellspacing="10">
<tr  class="test" >
<th style="background-color:rgb(58,157,35);">N&deg;</th>
<th style="background-color:rgb(58,157,35);">Client/Prospect</th>
<th style="background-color:rgb(58,157,35);">Contact</th>
<th style="background-color:rgb(58,157,35);">Nom de l'opportunit&eacute;</th>
<th style="background-color:rgb(58,157,35);">Ligne de Service 1</th>
<th style="background-color:rgb(58,157,35);">Ligne de Service 2</th>
<th style="background-color:rgb(58,157,35);">Commercial</th>
<th style="background-color:rgb(58,157,35);"> Revenu </th>
</tr>
<?php  echo $listeContractes; ?>

<tr>
<td colspan="7" style="text-align:right; color:rgb(58,157,35); font-weight:bold;">Total revenus </td>
<td style="text-align:right; color:rgb(58,157,35); font-weight:bold;"><?php echo money_format('%.2n', $addition); ?></td>
</tr>

</table>

<br>
<br>

<table>
<tr> <th style="text-align:left;font-size:18px;color:rgb(226,107,10);" colspan="10">OPPORTUNITES ABANDONNEES OU PERDUES DEPUIS LE <?php echo $choix_date_debut; ?></th> </tr>
</table>
<table border="1" cellpadding="0" cellspacing="10">
<tr  class="test" >
<th style="background-color:rgb(219,23,2);">N&deg;</th>
<th style="background-color:rgb(219,23,2);">Client/Prospect</th>
<th style="background-color:rgb(219,23,2);">Contact</th>
<th style="background-color:rgb(219,23,2);">Nom de l'opportunit&eacute;</th>
<th style="background-color:rgb(219,23,2);">Ligne de Service 1</th>
<th style="background-color:rgb(219,23,2);">Ligne de Service 2</th>
<th style="background-color:rgb(219,23,2);">Commercial</th>
<th style="background-color:rgb(219,23,2);">Raison</th>
</tr>

<?php 	echo $listeAbandonnees; ?>
</table>
</body>
</html>

<?php
///////////////////////////////////////////////////////////////////////////////
//
//     FONCTION FINALE DECLENCHANT LA REDIRECTION DE L'AFFICHAGE             //
//
///////////////////////////////////////////////////////////////////////////////
//
ob_end_flush();
?>

</fieldset>

// cr�er la liste des membres
$listeMembres = "";
$listeContractes = "";
$listeAbandonnees = "";
$sql = "SELECT * FROM ".OPPORTUNITES." WHERE derniere_date_modif BETWEEN  (str_to_date( '".$choix_date_debut."' , '%d/%m/%Y %H:%i:%s')) AND (str_to_date( '".$choix_date_fin."' , '%d/%m/%Y %H:%i:%s'))";
//echo $sql;
$result = mysql_query($sql) or die(mysql_error());
$addition = "0";
while($result && $row=mysql_fetch_array($result))
{
	if($row['opportunite_etape_commerciale'] =="détectée") {
	$listeMembres .= "<tr>
	<td>".utf8_encode($row['num_opportunite'])."</td>
<td>".utf8_encode($row['entite'])."</td>
<td>".utf8_encode($row['contact_client'])."</td>
<td>".utf8_encode($row['nom_opportunite'])."</td>
<td>".utf8_encode(htmlentities($row['ligne_service_1']))."</td>
<td>".utf8_encode($row['ligne_service_2'])."</td>
<td>".utf8_encode(htmlentities($row['opportunite_commerciale']))."</td>
<td>".utf8_encode(htmlentities($row['quadri_courtier']))."</td>
<td>".utf8_encode(htmlentities($row['estimation_echeance_opportunite']))."</td>
<td>".utf8_encode(htmlentities($row['derniere_date_modif']))."</td>";
}

$addition = $addition + $row['total_revenus'];
	if($row['opportunite_etape_commerciale'] =="contractée") {
	$listeContractes .= "<tr>
	<td>".utf8_encode($row['num_opportunite'])."</td>
<td>".utf8_encode($row['entite'])."</td>
<td>".utf8_encode($row['contact_client'])."</td>
<td>".utf8_encode($row['nom_opportunite'])."</td>
<td>".utf8_encode(htmlentities($row['ligne_service_1']))."</td>
<td>".utf8_encode($row['ligne_service_2'])."</td>
<td>".utf8_encode(htmlentities($row['quadri_courtier']))."</td>
<td style='text-align:right;'>".money_format('%.2n', ($row['total_revenus']))."</td>";
}
	if($row['opportunite_etape_commerciale'] ="perdue" or $row['opportunite_etape_commerciale'] ="abandonnée") {
	$listeAbandonnees .= "<tr>
	<td>".utf8_encode($row['num_opportunite'])."</td>
<td>".utf8_encode($row['entite'])."</td>
<td>".utf8_encode($row['contact_client'])."</td>
<td>".utf8_encode($row['nom_opportunite'])."</td>
<td>".utf8_encode(htmlentities($row['ligne_service_1']))."</td>
<td>".utf8_encode($row['ligne_service_2'])."</td>
<td>".utf8_encode(htmlentities($row['quadri_courtier']))."</td>
<td>".utf8_encode(htmlentities($row['raison_perdue']))."</td>";
	}
}