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> |
