So after DIY Smart Electricity Smart Meter ESP32 - Part 1 Count The Flashes we should now have data being stored every minute into a mySQL database.
Now we need to take that data and display it so we can make use of it. As the data is being stored on my own local server, ideally I wanted the data to displayed locally.
Tried lots of PHP website graphical software and could not get any of them to work or do what I wanted.
Found Google Charts API which seemed to do everything I wanted. But as always some of the documentation was not that good for the normal human. But with looking at lots of examples online from many websites managed to pull it all together.
The result did some of what I needed, hard coded the information required for the current date. At the bottom of the page is a bootstrap display which shows the total KWH used today, the cost of electricity for the day, KWH used total for the current year and finally the KWH Daily average.
This has been very usefull and worked quite well but there are limitations. The data is parsed via the google charts API, it has limits and is not local. I also found it does not like being over used lets say. The original version I had auto updated and after a while it stops work as google have blocked you for over use. Took off the auto update and had no problems since.
See DIY Smart Electricity Smart Meter ESP32 - Part 3 Make the Data work (Grafana)
<?php
$con = mysqli_connect('localhost','php','php','test_arduino');
?>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>EnergyUse</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js" integrity="sha384-wfSDF2E50Y2D1uUdj0O3uMBJnjuUD4Ih7YwaYd1iqfktj0Uod8GCExl3Og8ifwB6" crossorigin="anonymous"></script>
<script src="https://code.jquery.com/jquery-3.4.1.slim.min.js" integrity="sha384-J6qa4849blE2+poT4WnyKhv5vZF5SrPo0iEjwBvKU7imGFAV0wwj1yYfoRSJoZ+n" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<script type="text/javascript">
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable([
['energy','recorded'],
<?php
$query = "SELECT * FROM `energy` WHERE DATE(recorded) = DATE(NOW())";
$exec = mysqli_query($con,$query);
while($row = mysqli_fetch_array($exec)){
echo "['".$row['recorded']."',".$row['energy']."],";
}
?>
]);
var options = {
title: 'Todays WH Energy Use',
explorer: {
maxZoomOut:2,
keepInBounds: true
},
height: window.innerHeight,
width: window.innerWidth,
};
var chart = new google.visualization.LineChart(document.getElementById("columnchart12"));
chart.draw(data,options); //try material line charts as newer and better apparently
}
</script>
</head>
<body>
<div class="container-fluid">
<div id="columnchart12" ;"></div>
</div>
<div class="alert alert-primary" role="alert">
<p><h1> KWH used today
<?php
$query = "SELECT SUM(energy) AS value_sum FROM energy WHERE DATE(recorded) = DATE(NOW())";
$exec = mysqli_query($con,$query);
$row = mysqli_fetch_array($exec);
echo number_format((float) (($row['value_sum']/1000)), 2, '.', '');
// echo ($row['value_sum']/1000);
?>
Cost today £
<?php
$query = "SELECT SUM(energy) AS value_sum FROM energy WHERE DATE(recorded) = DATE(NOW())";
$exec = mysqli_query($con,$query);
$row = mysqli_fetch_array($exec);
echo number_format((float) (($row['value_sum']/1000*20.67)/100), 2, '.', '');
// echo (($row['value_sum']/1000*17.112)/100);
?>
KWH used total
<?php
$query = "SELECT SUM(energy) AS value_sum FROM energy";
$exec = mysqli_query($con,$query);
$row = mysqli_fetch_array($exec);
echo number_format((float) (($row['value_sum']/1000)), 2, '.', '');
// echo $row['value_sum'];
?>
KWH daily average
<?php
$query = "SELECT SUM(energy) AS value_sum FROM energy where recorded between '2021-05-03' and now()";
$exec = mysqli_query($con,$query);
$row = mysqli_fetch_array($exec);
$sumtotal = number_format((float) (($row['value_sum']/1000)), 2, '.', '');
$now = time(); // or your date as well
$your_date = strtotime("2021-05-03");
$datediff = $now - $your_date;
$days = round($datediff / (60 * 60 * 24));
echo number_format((float)($sumtotal/$days),2);
?>
</div>
</body>
</html> I will give you a break down of the code above.
PHP setting up connection for mySQL database
<?php
$con = mysqli_connect('localhost','php','php','test_arduino');
?>Now the webpage itself is started a title for the webpage and javscript calls are setup.
Not all of these are needed for Google charts, some of them are bootstrap and they are for formatting of the webpage. The important one you do need is
https://www.gstatic.com/charts/loader.js <!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>EnergyUse</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js" integrity="sha384-wfSDF2E50Y2D1uUdj0O3uMBJnjuUD4Ih7YwaYd1iqfktj0Uod8GCExl3Og8ifwB6" crossorigin="anonymous"></script>
<script src="https://code.jquery.com/jquery-3.4.1.slim.min.js" integrity="sha384-J6qa4849blE2+poT4WnyKhv5vZF5SrPo0iEjwBvKU7imGFAV0wwj1yYfoRSJoZ+n" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<script type="text/javascript"> Google Chart setup
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable([
['energy','recorded'],
<?php
$query = "SELECT * FROM `energy` WHERE DATE(recorded) = DATE(NOW())";
$exec = mysqli_query($con,$query);
while($row = mysqli_fetch_array($exec)){
echo "['".$row['recorded']."',".$row['energy']."],";
}
?>
]);
var options = {
title: 'Todays WH Energy Use',
explorer: {
maxZoomOut:2,
keepInBounds: true
},
height: window.innerHeight,
width: window.innerWidth,
};
var chart = new google.visualization.LineChart(document.getElementById("columnchart12"));
chart.draw(data,options); //try material line charts as newer and better apparently
}
</script>
</head>
<body>
<div class="container-fluid">
<div id="columnchart12" ;"></div>
</div>
<div class="alert alert-primary" role="alert">
<p><h1> KWH used today
<?php
$query = "SELECT SUM(energy) AS value_sum FROM energy WHERE DATE(recorded) = DATE(NOW())";
$exec = mysqli_query($con,$query);
$row = mysqli_fetch_array($exec);
echo number_format((float) (($row['value_sum']/1000)), 2, '.', '');
// echo ($row['value_sum']/1000);
?>
Cost today £
<?php
$query = "SELECT SUM(energy) AS value_sum FROM energy WHERE DATE(recorded) = DATE(NOW())";
$exec = mysqli_query($con,$query);
$row = mysqli_fetch_array($exec);
echo number_format((float) (($row['value_sum']/1000*20.67)/100), 2, '.', '');
// echo (($row['value_sum']/1000*17.112)/100);
?>
KWH used total
<?php
$query = "SELECT SUM(energy) AS value_sum FROM energy";
$exec = mysqli_query($con,$query);
$row = mysqli_fetch_array($exec);
echo number_format((float) (($row['value_sum']/1000)), 2, '.', '');
// echo $row['value_sum'];
?>
KWH daily average
<?php
$query = "SELECT SUM(energy) AS value_sum FROM energy where recorded between '2021-05-03' and now()";
$exec = mysqli_query($con,$query);
$row = mysqli_fetch_array($exec);
$sumtotal = number_format((float) (($row['value_sum']/1000)), 2, '.', '');
$now = time(); // or your date as well
$your_date = strtotime("2021-05-03");
$datediff = $now - $your_date;
$days = round($datediff / (60 * 60 * 24));
echo number_format((float)($sumtotal/$days),2);
?>
</div>
</body>
</html>
Comments
Post a Comment