I have various energy measurement devices - PZEM, Sonoff POW and TP-LinkHS110. I wanted to produce a chart which showed the consumption of the devices over the last moth and also compare it to my target consumption.
I tried with Grafana and couldn’t get what I wanted. In particular, it’s not good when there’s only one reading a day and the average function only works over a maximum of 48 hours.
After some research I came across pChart (www.pchart.net). It’s quite old but still works. I downloaded it to my Synology NAS and after a bit of fiddling around to get the paths right, and a lot of reading because I hadn’t used php before, produced my first chart. I need to tweak the colours. The green line across the bars is the target threshold - it goes red if the average exceeds it. The width of the line increase to show how far above and below.
I also had to fight with the Date as it wouldn’t read the timestamp properly from the way I had to set io up for Grafana to work. I also need to work out how to only read the last 30 days of data but I’ve got two weeks to sort that out.
<?php
include("class/pData.class.php");
include("class/pDraw.class.php");
include("class/pImage.class.php");
/* connect to MySQL */
$myData = new pData();
$db = mysql_connect("127.0.0.1:3307", "openHAB", "password");
mysql_select_db("openHAB",$db);
$Request = "SELECT * FROM `Daily_Consumption`";
$Result = mysql_query($Request,$db);
$Date="" ; $kWh=""; $Immersion=""; $Washer=""; $TV;
while($row = mysql_fetch_array($Result))
{
/* Push the results of the query in an array */
$Date[] = substr($row["Date"],8,2) . "/" . substr($row["Date"],5,2);
$Other[] = $row["kWh"] - $row["Immersion"] - $row["Washer"] - $row[TV];
$Immersion[] = $row["Immersion"];
$Washer[] = $row["Washer"];
$kWh[] = $row["kWh"];
$TV[] = $row["TV"];
}
/* Save the data in the pData array */
$myData->addPoints($Date,"Date");
$myData->addPoints($Washer,"Washer");
$myData->addPoints($Immersion,"Immersion");
$myData->addPoints($kWh,"kWh");
$myData->addPoints($TV,"TV");
$myData->addpoints($Other,"Other");
$myData->setAbscissa("Date");
/* Allocate data to axes */
$limit = 13.4; /* set target average */
$Average = $myData->getSerieAverage("kWh");
$myData->setSerieOnAxis("Washer", 0);
$myData->setSerieOnAxis("Immersion", 0);
$myData->setSerieOnAxis("IV", 0);
$myData->setSerieOnAxis("Other", 0);
$myData->setXAxisName("Date");
$myData->setXAxisDisplay(AXIS_FORMAT_DEFAULT);
$myData->setAxisName(0,"kWh");
$myData->setSerieDrawable("kWh",FALSE);
$myData->setAxisPosition(0,AXIS_POSITION_LEFT);
/* set size of chart */
$width = 1000;
$height = 500;
$myPicture = new pImage($width,$height,$myData);
$Settings = array("R"=>255, "G"=>255, "B"=>255);
$myPicture->drawFilledRectangle(0,0,$width,$height,$Settings);
$myPicture->drawRectangle(0,0,$width - 1,$height - 1,array("R"=>0,"G"=>0,"B"=>0));
$half = $width / 2;
$title = "Energy Consumption";
$myPicture->setFontProperties(array("FontName"=>"fonts/calibri.ttf","FontSize"=>14));
$TextSettings = array("Align"=>TEXT_ALIGN_MIDDLEMIDDLE, "R"=>0, "G"=>0, "B"=>0);
$myPicture->drawText(($half - 50),25,$title,$TextSettings);
$myPicture->setGraphArea(30,30,$width - 30,$height - 30);
$myPicture->setFontProperties(array("R"=>0,"G"=>0,"B"=>0,"FontName"=>"fonts/calibri.ttf","FontSize"=>10));
$myData->loadPalette("palettes/custom.color", TRUE);
/* draw chart */
$Settings = array("Pos"=>SCALE_POS_LEFTRIGHT, "LabelingMethod"=>LABELING_ALL, "GridR"=>255, "GridG"=>255, "GridB"=>255, "GridAlpha"=>50, "TickR"=>0, "TickG"=>0, "TickB"=>0, "TickAlpha"=>50, "LabelRotation"=>0, "CycleBackground"=>1, "DrawXLines"=>1, "DrawSubTicks"=>1, "SubTickR"=>255, "SubTickG"=>0, "SubTickB"=>0, "SubTickAlpha"=>50, "DrawXLines"=>1, "Mode"=>SCALE_MODE_ADDALL_START0);
$myPicture->drawScale($Settings);
$Config = array("AroundZero"=>0);
$myPicture->drawStackedBarChart(array("DisplayValues"=>FALSE,"DisplayColor"=>DISPLAY_AUTO,"Rounded"=>FALSE,"Surrounding"=>60));
$Config = array("FontR"=>0, "FontG"=>0, "FontB"=>0, "FontName"=>"fonts/calibri.ttf", "FontSize"=>10, "Margin"=>6, "Alpha"=>30, "BoxSize"=>5, "Style"=>LEGEND_NOBORDER
, "Mode"=>LEGEND_VERTICAL
);
/* set colours for alert */
$liner = 0;
$lineg = 255;
if ($Average > $limit) {
$liner = 255;
$lineg = 0;
}
$myPicture->drawThresholdArea($limit,$Average,array("R"=>$liner,"G"=>$lineg,"B"=>0));
$myPicture->drawLegend($width - 100,16,$Config);
$myPicture->stroke();
?>
I run this rule every night to populate the MySQL table
rule "Daily energy consumption"
when
Time cron "45 59 23 1/1 * ? *"
then
TVToday = TVSwitch_Energy.state
TVToday = TVToday - TVTotal
TVTotal = TVSwitch_Energy.state
currentTime = transform("REGEX","s/T/ /g",Electricity_POW_Update.state.toString)
command = "d:\\MySQL\\mysql.exe -h 192.168.0.211 --port 3307 -u openHAB -ppassword -e \"INSERT INTO Daily_Consumption VALUES ('"
command = command + currentTime +"',"
command = command + Electricity_POW_Day.state +","
command = command + Immersion_Switch_POW_Day.state +","
command = command + Washer_Drier_POW_Day.state +","
command = command + TVToday +");\" openHAB"
executeCommandLine(command)
end
And this is the line in the sitemap to display the chart
Webview url="http://192.168.0.211:8008/examples/example.test.php" height=15
It draws quickly despite my novice php coding status
