Alternative to Grafana

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

5 Likes

Really nice work! Looks like it’s much like JpGraph under the hood. I’m also after more lightweight graphing and have started a small project which uses JpGraph to draw a line plot to visualize my heat pump.

@pmknowles Have a look in here.

looks nice, are these graps also proxyed by myopenhab?
for me that is the main problem with grafana, the inability of myopenhab to proxy the graphs.

I don’t use myopenhab so can’t answer that. At the moment I’m using this in my sitemap as an image element.

Image url="http://192.168.0.10/scripts/graph.php"

No because it uses a local web address. I’m running it on a NAS so, in theory, I could link to the web interface.

I’m going to look into whether I could get openHAB to run a rule every 15 minutes or so and save a snapshot of the graph as a png. Not real time but good enough for most things.

Regards

Phil

Mail](https://go.microsoft.com/fwlink/?LinkId=550986) for Windows 10

If you replace the last command in the php with $myPicture->Render(“image.png”); it saves an image.

If this is saved in your openHAB conf/html folder and then referenced in sitemap as

Item image url=http://localhost:8080/static/image.png

It will then appear in the browser, Android app and myopenHAB

I’m going to (hopefully) run it as a rule every 15 minutes

Mail](https://go.microsoft.com/fwlink/?LinkId=550986) for Windows 10

Using the $myPicture->Render() command works and allows the graph to be shown remotely. My last line in the php file is


$myPicture->Render("d:/openHAB23/conf/html/image.png");

This saves the image file in the correct location for openHAB

The line in the Rule which draws the chart is

executeCommandLine("d:/php/Files/php.exe -q d:/php/pChart/examples/example.test2.php")

and the line to display in Sitemap

Image url="http://localhost:8080/static/image.png"

It then displays in normal, remote and Android sitemaps (and apps) and is scaled.

Hope that helps