Teleinfo kwh jpgraph.php

<?php
// Génére un graphe en image PNG en fonction des données téléinfo de la base MySql.
// Consommation sur 8 jours / 8 semaines / 8 mois en kwh HP et HC.
// Par Domos.
 
/*
Appel par: 
http://localhost/teleinfo/kwh_jpgraph.php?periode=8jours
http://localhost/teleinfo/kwh_jpgraph.php?periode=8semaines
http://localhost/teleinfo/kwh_jpgraph.php?periode=8mois
*/
 
// Requète MySql.
/*
Format de la table:
timestamp 	rec_date 	rec_time 	adco 		optarif isousc 	hchp 		hchc 		ptec 	inst1 	inst2 	inst3 	imax1 	imax2 	imax3 	pmax 	papp 	hhphc 	motdetat 	ppot 	adir1 	adir2 	adir3
1234998004 	2009-02-19 	00:00:04 	700609361116 	HC.. 	20 	11008467 	10490214 	HP 	1 	0 	1 	18 	23 	22 	8780 	400 	E 	000000 		00 	0 	0 	0
1234998065 	2009-02-19 	00:01:05 	700609361116 	HC.. 	20 	11008473 	10490214 	HP 	1 	0 	1 	18 	23 	22 	8780 	400 	E 	000000 		00 	0 	0 	0
1234998124 	2009-02-19 	00:02:04 	700609361116 	HC.. 	20 	11008479 	10490214 	HP 	1 	0 	1 	18 	23 	22 	8780 	390 	E 	000000 		00 	0 	0 	0
1234998185 	2009-02-19 	00:03:05 	700609361116 	HC.. 	20 	11008484 	10490214 	HP 	1 	0 	0 	18 	23 	22 	8780 	330 	E 	000000 		00 	0 	0 	0
1234998244 	2009-02-19 	00:04:04 	700609361116 	HC.. 	20 	11008489 	10490214 	HP 	1 	0 	0 	18 	23 	22 	8780 	330 	E 	000000 		00 	0 	0 	0
1234998304 	2009-02-19 	00:05:04 	700609361116 	HC.. 	20 	11008493 	10490214 	HP 	1 	0 	0 	18 	23 	22 	8780 	330 	E 	000000 		00 	0 	0 	0
1234998365 	2009-02-19 	00:06:05 	700609361116 	HC.. 	20 	11008498 	10490214 	HP 	1 	0 	0 	18 	23 	22 	8780 	320 	E 	000000 		00 	0 	0 	0
 
Consommation sur 1 semaine.
 
SELECT `rec_date` ,
( ( MAX( `hchp` ) - MIN( `hchp` ) ) /1000) AS "kWh HP sur journée", 
( ( MAX( `hchc` ) - MIN( `hchc` ) ) /1000) AS "kWh HC sur journée"
FROM `teleinfo` 
WHERE timestamp > "1234134000"			# 09/02/2009 00:00:00
GROUP BY rec_date
ORDER BY rec_date ; 
 
Résultat:
rec_date 	kWh HP sur journée 	kWh HC sur journée
2009-02-09 	8.8670 			21.2310
2009-02-10 	19.0240 		11.9830
2009-02-11 	32.1250 		27.1690
2009-02-12 	31.6670 		22.5270
2009-02-13 	34.3350 		25.1270
2009-02-14 	37.5690 		29.7890
2009-02-15 	26.9540 		24.6730
2009-02-16 	18.1340 		14.0820
 
 
Consommation sur n semaines:
SELECT rec_date, DATE_FORMAT( rec_date, 'sem %v' ) AS 'sem', 
ROUND( ((MAX( `hchp` ) - MIN( `hchp` ) ) /1000), 1), 
ROUND( ((MAX( `hchc` ) - MIN( `hchc` ) ) /1000), 1)
FROM `teleinfo`
WHERE timestamp > '1228950000'			# 11/12/2008 00:00:00
GROUP BY sem
ORDER BY rec_date ; 
 
rec_date 	sem 	ROUND( ((MAX(`hchp`) - MIN(`hchp`)) / 1000) ,1 ) 	ROUND( ((MAX(`hchc`) - MIN(`hchc`)) / 1000) ,1 )
2008-12-11  	sem 50  	58.2  	77.2
2008-12-15 	sem 51 		161.5 	146.0
2008-12-22 	sem 52 		144.1 	106.9
2008-12-29 	sem 01 		194.5 	153.1
2009-01-05 	sem 02 		239.2 	181.7
2009-01-12 	sem 03 		100.2 	111.5
2009-01-25 	sem 04 		5.7 	0.0
2009-01-26 	sem 05 		91.4 	115.6
2009-02-03 	sem 06 		125.3 	107.0
2009-02-09 	sem 07 		190.6 	162.5
2009-02-16 	sem 08 		106.3 	67.8
 
 
Consommation sur n  mois:
SELECT rec_date, DATE_FORMAT( rec_date, '%b' ) AS 'mois', 
ROUND( ((MAX( `hchp` ) - MIN( `hchp` ) ) /1000), 1),
ROUND( ((MAX( `hchc` ) - MIN( `hchc` ) ) /1000), 1)
FROM `teleinfo`
WHERE timestamp > '1228950000'			# 11/12/2008 00:00:00
GROUP BY mois
ORDER BY rec_date ; 
 
rec_date 	mois 	ROUND( ((MAX(`hchp`) - MIN(`hchp`)) / 1000) ,1 ) 	ROUND( ((MAX(`hchc`) - MIN(`hchc`)) / 1000) ,1 )
2008-12-11  	Dec  	457.6  		409.7
2009-01-01 	Jan 	701.8 		644.7
2009-02-03 	Feb 	422.5 		337.3
*/
 
//Librairies JpGraph
include ("/var/www/jpgraph/src/jpgraph.php");
include ("/var/www/jpgraph/src/jpgraph_bar.php");
 
setlocale (LC_ALL, "fr_FR") ;
 
$periode = $_GET['periode'] ;
 
if (! $periode) { $periode = "8jours" ; } ;
switch ($periode) {
	case "8jours":
		$nbjours = 7 ;								// nb jours.
		$xlabel = "jours" ;
		$periodesecondes = $nbjours*24*3600 ;					// Periode en secondes.
		$timestampheure = mktime(0,0,0,date("m"),date("d"),date("Y"));		// Timestamp courant.
		$timestampdebut = $timestampheure - $periodesecondes ;			// Recule de $periodesecondes.
		$dateformatsql = "%a %e" ;
		break;
	case "8semaines":
		$timestampdebut = mktime(0,0,0, date("m")-2, date("d"), date("Y"));
		$nbjour=1 ;
		while ( date("w", $timestampdebut) != 1 )	// Avance d'un jour tant que celui-ci n'est pas un lundi. 
		{
			$timestampdebut = mktime(0,0,0, date("m")-2, date("d")+$nbjour, date("Y"));
			$nbjour++ ;
		}
		$xlabel = "semaines" ;
		$dateformatsql = "sem %v" ;
		break;
	case "8mois":
		$timestampdebut = mktime(0,0,0, date("m")-7, 1, date("Y"));
		$xlabel = "mois" ;
		$dateformatsql = "%b" ;
		break;
	default:
		die("Periode erronée, valeurs possibles: [8jours|8semaines|8mois] !");
		break;
}
 
//printf("datedebut : (%s) %s<br \>", $timestampdebut, date("YmdHis", $timestampdebut)) ;
 
$serveur="localhost" ; 
$login="root" ;
$base="maison" ;
$fd = fopen ("./mysql.txt", "r") ;
$pass = rtrim(fgets($fd, 4096)) ;	// $pass contient password compte root MySql.
fclose ($fd);
 
mysql_connect($serveur, $login, $pass) or die("Erreur de connexion au serveur MySql");
mysql_select_db($base) or die("Erreur de connexion a la base de donnees $base");
$table="teleinfo"; 
$query="SET lc_time_names = 'fr_FR'" ;						// Pour afficher date en français dans MySql.
mysql_query($query) ; 
$query="SELECT rec_date, DATE_FORMAT(rec_date, '$dateformatsql') AS 'periode' ,
	ROUND( ((MAX(`hchp`) - MIN(`hchp`)) / 1000) ,1 ), 
	ROUND( ((MAX(`hchc`) - MIN(`hchc`)) / 1000) ,1 )  
	FROM `$table` 
	WHERE timestamp > '$timestampdebut'
	GROUP BY periode
	ORDER BY rec_date" ; 
$result=mysql_query($query) or die ("<b>Erreur</b> dans la requète <b>" . $query . "</b> : "  . mysql_error() . " !<br>"); 
$num_rows = mysql_num_rows($result) ;
$no = 0 ;
while ($row = mysql_fetch_array($result)) 
{
	//printf("%s, %s, %s<br \>", $row["rec_date"], $row["periode"], $row[2], $row[3]) ;
	$date[$no] = $row["rec_date"] ;
	$timestp[$no] = $row["periode"] ;
	$kwhhp[$no]=$row[2] ;
	$kwhhc[$no]=$row[3] ;
	$no++ ;
}
mysql_free_result($result) ;
mysql_close() ;
 
//exit(0) ;	
 
$date_digits_dernier_releve=explode("-", $date[count($date) -1]) ; 
$date_dernier_releve =	Date("d/m/Y", mktime(0,0,0, $date_digits_dernier_releve[1] ,$date_digits_dernier_releve[2], $date_digits_dernier_releve[0])) ;
 
// Génération du graphe.	
$graph = new Graph(800,400);
$graph->SetMargin(60,40,40,80);
$graph->SetMarginColor('black');
$graph->SetColor('gray1');
 
$graph->SetScale("textlin");
 
// Specify X-labels
$graph->xaxis->SetTickLabels($timestp);
$graph->xaxis->title->SetColor("gray7");
$graph->xaxis->SetColor("gray5","gray7");
$graph->yaxis->title->SetColor("gray7");
$graph->yaxis->SetColor("gray5","gray7");
 
$graph->ygrid->SetColor("gray5");
$graph->ygrid->Show(true, true) ;
$graph->xgrid->SetColor("gray5");
$graph->xgrid->Show(true) ;
 
// Adjust the legend position
$graph->legend->SetLayout(LEGEND_HOR);
$graph->legend->Pos(0.05,0.97,"left","bottom");
 
// Create the bar plots
$b1plot = new BarPlot($kwhhp);
$b1plot->SetFillColor("red");
$b1plot->SetShadow();
$b1plot->value->SetColor("white") ; 
$b1plot->value->Show();
$b1plot->SetLegend("HP");
 
 
$b2plot = new BarPlot($kwhhc);
$b2plot->SetFillColor("green");
$b2plot->SetShadow();
$b2plot->value->SetColor("black") ; 
$b2plot->value->Show();
$b2plot->SetLegend("HC");
 
// Create the grouped bar plot
//$gbplot = new GroupBarPlot(array($b1plot,$b2plot)) ;
$gbplot = new AccBarPlot(array($b2plot,$b1plot)) ;
 
// ...and add it to the graPH
$graph->Add($gbplot);
 
$graph->title->Set("Historique de la consommation le $date_dernier_releve");
$graph->title->SetColor("gray7");
$graph->xaxis->title->Set("$xlabel");
$graph->yaxis->title->Set("kwh");
 
$graph->title->SetFont(FF_FONT1,FS_BOLD);
$graph->yaxis->title->SetFont(FF_FONT1,FS_BOLD);
$graph->xaxis->title->SetFont(FF_FONT1,FS_BOLD);
$graph->xaxis->title->SetMargin(25); 
 
// Display the graph
$graph->Stroke();
?>