1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80
| <FORM action="" method="post" name="un"><?php //selection des commerciaux ?>
<SELECT id="" name="commercial1">
<OPTION value="0">-- Commercial --</OPTION>
<OPTION value="0">Toute l'équipe</OPTION>
<?php
$sql1 = "SELECT DISTINCT comm_id, comm_nom, comm_prenom FROM commercial ORDER BY comm_nom";
$rep1 = mysql_query($sql1) ;
while($data1 = mysql_fetch_array($rep1)){
echo "<OPTION value=".$data1['comm_id'].">".$data1['comm_nom'].' '.$data1['comm_prenom']."</OPTION>";
}
?>
</SELECT><br>
<input type='checkbox' name='annee1[]' value='0'> Toutes les années
<input type='checkbox' name='annee1[]' value='2013'>2013
<input type='checkbox' name='annee1[]' value='2014'>2014
<input type='checkbox' name='annee1[]' value='2015'>2015
<input type="submit" value="Ok">
</form>
<?php
if (isset($_POST['commercial1']) AND isset($_POST['annee1'])) {
if ($_POST['annee1'] == '0'){
$annee1 = "YEAR(date_vente) NOT LIKE '0'";
}
else{
$annee1 = "YEAR(date_vente) = '" . intval($_POST['annee1']) . "' ";
}
if ($_POST['commercial1'] == '0'){
$commercial1 = "comm_id NOT LIKE '0'";
}
else{
$commercial1 = "comm_id = '" . intval($_POST['commercial1']) . "'";
}
$sql1 = "SELECT year(date_vente) an1,
SUM(CASE WHEN MONTH(date_vente) = 1 THEN ttc/(1+(tva/100)) ELSE 0 END) Jan,
SUM(CASE WHEN MONTH(date_vente) = 2 THEN ttc/(1+(tva/100)) ELSE 0 END) Fev,
SUM(CASE WHEN MONTH(date_vente) = 3 THEN ttc/(1+(tva/100)) ELSE 0 END) Mar,
SUM(CASE WHEN MONTH(date_vente) = 4 THEN ttc/(1+(tva/100)) ELSE 0 END) Avril,
SUM(CASE WHEN MONTH(date_vente) = 5 THEN ttc/(1+(tva/100)) ELSE 0 END) Mai,
SUM(CASE WHEN MONTH(date_vente) = 6 THEN ttc/(1+(tva/100)) ELSE 0 END) Juin,
SUM(CASE WHEN MONTH(date_vente) = 7 THEN ttc/(1+(tva/100)) ELSE 0 END) Juil,
SUM(CASE WHEN MONTH(date_vente) = 8 THEN ttc/(1+(tva/100)) ELSE 0 END) Aout,
SUM(CASE WHEN MONTH(date_vente) = 9 THEN ttc/(1+(tva/100)) ELSE 0 END) Sept,
SUM(CASE WHEN MONTH(date_vente) = 10 THEN ttc/(1+(tva/100)) ELSE 0 END) Oct,
SUM(CASE WHEN MONTH(date_vente) = 11 THEN ttc/(1+(tva/100)) ELSE 0 END) Nov,
SUM(CASE WHEN MONTH(date_vente) = 12 THEN ttc/(1+(tva/100)) ELSE 0 END) `Dec`,
SUM(ttc/(1+(tva/100))) as Total
FROM contacts WHERE $commercial1 or $annee1 GROUP BY an1 ";
$req1 = mysql_query($sql1) or die('Erreur SQL !<br />'.$sql1.'<br />'.mysql_error());
$tab1 = array();
while($data1 = mysql_fetch_array($req1) ) {
//construction du tableau à partir du resultat de la requête
$tab1[ $data1['an1'] ] = $data1;
}
?>
<table>
<thead>
<tr>
<th>Année</th><th>Janv</th><th>Fevr</th><th>Mars</th><th>Avr</th><th>Mai</th><th>Juin</th><th>Juil</th><th>Août</th><th>Sept</th><th>Oct</th><th>Nov</th><th>Dec</th>
</tr>
</thead>
<tbody>
<?php foreach( $tab1 as $an1=>$mois ) { ?>
<tr>
<th><?php echo $an1 ?></th>
<td><?php echo number_format($mois[1], 2, ',', '');?></td>
<td><?php echo number_format($mois[2], 2, ',', '');?></td>
<td><?php echo number_format($mois[3], 2, ',', '');?></td>
<td><?php echo number_format($mois[4], 2, ',', '');?></td>
<td><?php echo number_format($mois[5], 2, ',', '');?></td>
<td><?php echo number_format($mois[6], 2, ',', '');?></td>
<td><?php echo number_format($mois[7], 2, ',', '');?></td>
<td><?php echo number_format($mois[8], 2, ',', '');?></td>
<td><?php echo number_format($mois[9], 2, ',', '');?></td>
<td><?php echo number_format($mois[10], 2, ',', '');?></td>
<td><?php echo number_format($mois[11], 2, ',', '');?></td>
<td><?php echo number_format($mois[12], 2, ',', '');?></td>
</tr>
<?php } ?>
</tbody>
</table> |
Partager