Here are the PHP programs and cronjob I use:
I am not the most experienced PHP programmer. So maybe the code can be smarter.
First fetch the data for the graph in MySQL and transform into the right format JSON file:
?PHP
$file = '/var/www/html/test/5min_usage.json';
$json_data = '';
$data_1 = '';
$data_2 = '';
$con = mysql_connect("localhost","username","password");
if (!$con) {
die('Could not connect: ' . mysql_error());
};
mysql_select_db("p1", $con);
$result = mysql_query("SELECT DATE_FORMAT(timestamp,'%Y,%m,%d,%H,%i,%s') as tijd, (0 + P1_in_5min + P2_in_5min - P1_out_5min - P2_out_5min) as imex FROM `5minutes` WHERE Date(timestamp) = curdate()");
while($row = mysql_fetch_array($result)) {
$maand = substr($row['tijd'],5,2) - 1;
$data_1 = $data_1 . "[Date.UTC(".substr($row['tijd'],0,5).$maand.substr($row['tijd'],7,9)."),".$row['imex']."],";
};
$result = mysql_query("SELECT DATE_FORMAT(timestamp,'%Y,%m,%d,%H,%i,%s') as tijd, pow_usage FROM `5minutes` WHERE Date(timestamp) = curdate()");
while($row = mysql_fetch_array($result)) {
$maand = substr($row['tijd'],5,2) - 1;
$data_2 = $data_2 . "[Date.UTC(".substr($row['tijd'],0,5).$maand.substr($row['tijd'],7,9)."),".$row['pow_usage']."],";
};
$json_data = '{
chart: {
type: \'area\',
width: 750,
zoomType: \'x\'
},
title: {
text: \'Import / Export vs Usage per 5 minutues today (kWh)\'
},
subtitle: {
text: \''.date("d-m-Y H:i").'\'
},
credits: {
text: \'© Copyright Conrad Hagemans\',
href: false,
},
navigation: {
buttonOptions: {
enabled: false
},
},
xAxis: {
type: \'datetime\'
},
series:[{
name: \'Import/Export (kWh)\',
color: \'orange\',
negativeColor: \'green\',
data: [ '. $data_1 . ' ]
},{
name: \'Power Usage (kWh)\',
color: \'#000099\',
data: [ '. $data_2 . ' ]
}]
}';
file_put_contents($file, $json_data);
mysql_close($con);
?>
This script is stored under the name: json_impexp5min.php
Next step is call the highchart export server with the output of the file above as input and generate a picture to store in a folder that can be read by Home Assistant.
I am using a cronjob to this every 5 minutes:
In my crontab file I have the following 2 line:
*/5 * * * * /usr/bin/php /var/www/html/test/json_impexp5min.php
*/10 * * * * /usr/local/bin/highcharts-export-server -infile /var/www/html/test/5min_usage.json -outfile /var/www/html/chart/chart.png >> /var/log/highcharts.log
The result looks like this:
