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
|
// On recupère toutes les années présentes dans la base
$query_years = "SELECT DISTINCT YEAR(DATE_ENR) AS year FROM ".$pref_table."commandes";
$years = mysql_query($query_years, $mysql);
// Debut requete
$query_fournisseurs = "SELECT f.CODEPDV AS CODEPDV, f.LIBFOURNISSEUR AS LIBFOURNISSEUR";
// Pour chaque année
while( $year = mysql_fetch_array($years) )
{
$year = $year['year'];
// Définit les titres des colonnes au format mm-yyyy
$str_titres_colonnes .= "01-".$year.";02-".$year.";03-".$year.";04-".$year.";05-".$year.";06-".$year.";07-".$year.";08-".$year.";09-".$year.";10-".$year.";11-".$year.";12-".$year.";";
// Champs à récupérer sous forme de requetes imbriquées
// Volume par mois et par année
$query_fournisseurs .= ",(SELECT SUM(c1".$year.".volume) FROM ".$pref_table."commandes c1".$year." WHERE MONTH(c1".$year.".DATE_ENR)=1 AND f.IDFOURNISSEUR = c1".$year.".IDFOURNISSEUR AND YEAR(c1".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c1".$year.".DATE_ENR) ) AS VOL1".$year.",
(SELECT SUM(c2".$year.".volume) FROM ".$pref_table."commandes c2".$year." WHERE MONTH(c2".$year.".DATE_ENR)=2 AND f.IDFOURNISSEUR = c2".$year.".IDFOURNISSEUR AND YEAR(c2".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c2".$year.".DATE_ENR) ) AS VOL2".$year.",
(SELECT SUM(c3".$year.".volume) FROM ".$pref_table."commandes c3".$year." WHERE MONTH(c3".$year.".DATE_ENR)=3 AND f.IDFOURNISSEUR = c3".$year.".IDFOURNISSEUR AND YEAR(c3".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c3".$year.".DATE_ENR) ) AS VOL3".$year.",
(SELECT SUM(c4".$year.".volume) FROM ".$pref_table."commandes c4".$year." WHERE MONTH(c4".$year.".DATE_ENR)=4 AND f.IDFOURNISSEUR = c4".$year.".IDFOURNISSEUR AND YEAR(c4".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c4".$year.".DATE_ENR) ) AS VOL4".$year.",
(SELECT SUM(c5".$year.".volume) FROM ".$pref_table."commandes c5".$year." WHERE MONTH(c5".$year.".DATE_ENR)=5 AND f.IDFOURNISSEUR = c5".$year.".IDFOURNISSEUR AND YEAR(c5".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c5".$year.".DATE_ENR) ) AS VOL5".$year.",
(SELECT SUM(c6".$year.".volume) FROM ".$pref_table."commandes c6".$year." WHERE MONTH(c6".$year.".DATE_ENR)=6 AND f.IDFOURNISSEUR = c6".$year.".IDFOURNISSEUR AND YEAR(c6".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c6".$year.".DATE_ENR) ) AS VOL6".$year.",
(SELECT SUM(c7".$year.".volume) FROM ".$pref_table."commandes c7".$year." WHERE MONTH(c7".$year.".DATE_ENR)=7 AND f.IDFOURNISSEUR = c7".$year.".IDFOURNISSEUR AND YEAR(c7".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c7".$year.".DATE_ENR) ) AS VOL7".$year.",
(SELECT SUM(c8".$year.".volume) FROM ".$pref_table."commandes c8".$year." WHERE MONTH(c8".$year.".DATE_ENR)=8 AND f.IDFOURNISSEUR = c8".$year.".IDFOURNISSEUR AND YEAR(c8".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c8".$year.".DATE_ENR) ) AS VOL8".$year.",
(SELECT SUM(c9".$year.".volume) FROM ".$pref_table."commandes c9".$year." WHERE MONTH(c9".$year.".DATE_ENR)=9 AND f.IDFOURNISSEUR = c9".$year.".IDFOURNISSEUR AND YEAR(c9".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c9".$year.".DATE_ENR) ) AS VOL9".$year.",
(SELECT SUM(c10".$year.".volume) FROM ".$pref_table."commandes c10".$year." WHERE MONTH(c10".$year.".DATE_ENR)=10 AND f.IDFOURNISSEUR = c10".$year.".IDFOURNISSEUR AND YEAR(c10".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c10".$year.".DATE_ENR) ) AS VOL10".$year.",
(SELECT SUM(c11".$year.".volume) FROM ".$pref_table."commandes c11".$year." WHERE MONTH(c11".$year.".DATE_ENR)=11 AND f.IDFOURNISSEUR = c11".$year.".IDFOURNISSEUR AND YEAR(c11".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c11".$year.".DATE_ENR) ) AS VOL11".$year.",
(SELECT SUM(c12".$year.".volume) FROM ".$pref_table."commandes c12".$year." WHERE MONTH(c12".$year.".DATE_ENR)=12 AND f.IDFOURNISSEUR = c12".$year.".IDFOURNISSEUR AND YEAR(c12".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c12".$year.".DATE_ENR) ) AS VOL12".$year;
}
$str_csv .= $str_titres_colonnes."Total;\n";
// Fin requete
$query_fournisseurs .= " ,(SELECT SUM(total.volume) FROM ".$pref_table."commandes total WHERE f.IDFOURNISSEUR = total.IDFOURNISSEUR GROUP BY f.IDFOURNISSEUR ) AS VOL_TOTAL FROM ".$pref_table."fournisseurs f
ORDER BY f.LIBFOURNISSEUR,f.CODEPDV";
$errors='';
$fournisseurs = mysql_query($query_fournisseurs);
$errors[] = mysql_error();
/* print_r($errors);
echo $query_fournisseurs;*/
while( $fournisseur = mysql_fetch_row($fournisseurs) )
{
foreach($fournisseur as $value)
{
$str_csv .= $value.";";
}
$str_csv .= "\n";
} |
Partager