Categories
Uncategorized

Ovo Energy integration to smart home Dashboard

My smarthome dashboard now has a graph that shows a rolling energy usage over the last 7 days both Gas and Electricity from Ovo.

I like to have good visibility of my usage alongside charging my Electric car so getting this on my smarthome dashboard was a priority.

Unfortunately, Ovo doesn’t provide a public API, but since when has that ever stopped us? Let’s take a look at how I did it.

For starters, let the haters hate, but I just love PHP for these hack and slash projects, so PHP, Javascript and HTML will be the stack for this. I host my dashboard on my webserver with GoDaddy alongside a MySQL database.

I got started simply by viewing the requests made to Ovo with Postman and I found a request that looked promising: https://smartpaym.ovoenergy.com/api/energy-usage/half-hourly/#######?date=2020-01-01
######## is a customer ID (Mine) we’ll work on that soon.

That request was able to give me kWh usage for both Electricity and Gas. Perfect!

Postman really makes life simple for getting around these undocumented API’s once we have found the request we need we can create the code off the bat with their code Generation. I choose PHP, drop it in a PHP file and off we go.


<?php

$curl = curl_init();

curl_setopt_array($curl, array(
  CURLOPT_URL => "https://smartpaym.ovoenergy.com/api/energy-usage/half-hourly/#######?date=2020-01-01",
  CURLOPT_RETURNTRANSFER => true,
  CURLOPT_ENCODING => "",
  CURLOPT_MAXREDIRS => 10,
  CURLOPT_TIMEOUT => 30,
  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
  CURLOPT_CUSTOMREQUEST => "GET",
  CURLOPT_HTTPHEADER => array(
    "accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9",
    "cache-control: no-cache",
    "postman-token: 5c8a7abe-d6e4-6250-8082-b22c6215c01b",
    "sec-fetch-dest: document",
    "upgrade-insecure-requests: 1",
    "user-agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.87 Safari/537.36"
  ),
));

$response = curl_exec($curl);
$err = curl_error($curl);

curl_close($curl);

if ($err) {
  echo "cURL Error #:" . $err;
} else {
  echo $response;
}

Press save the PHP file, access it from the browser and…..
{
"message": "No refresh token cookie or authorization header sent",
"redirect": "https://my.ovoenergy.com"
}

Okay, that makes sense we are not logged in! and now I know I need a refresh token.

Off to the login page we got following the same process with Postman , submit the request and generate the code:

<?php

$curl = curl_init();

curl_setopt_array($curl, array(
  CURLOPT_URL => "https://my.ovoenergy.com/api/v2/auth/login",
  CURLOPT_RETURNTRANSFER => true,
  CURLOPT_ENCODING => "",
  CURLOPT_MAXREDIRS => 10,
  CURLOPT_TIMEOUT => 30,
  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
  CURLOPT_CUSTOMREQUEST => "POST",
  CURLOPT_POSTFIELDS => "{\"password\":\"**********\",\"rememberMe\":true,\"username\":\"**************@***mail.com\"}",
  CURLOPT_HTTPHEADER => array(
    "accept: application/json, text/plain, */*",
    "cache-control: no-cache",
    "content-type: application/json;charset=UTF-8",
    "origin: https://my.ovoenergy.com",
    "postman-token: c65075e4-2f2f-c968-25be-eb38fbe0086e",
    "sec-fetch-dest: empty",
    "user-agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.87 Safari/537.36"
  ),
));

$response = curl_exec($curl);
$err = curl_error($curl);

curl_close($curl);

if ($err) {
  echo "cURL Error #:" . $err;
} else {
  echo $response;
}

Perfect, this returns with a refresh token! just what we needed.
Some modification to the PHP file and we can have this store the cookie we need and reuse it for getting the data.

$cookie_file ='/tmp/cookie.txt';
if (! file_exists($cookie_file) || ! is_writable($cookie_file))
{
    echo 'Cookie file missing or not writable.';
    exit;
}

$curl = curl_init();
curl_setopt_array($curl, array(
  CURLOPT_URL => "https://my.ovoenergy.com/api/v2/auth/login",
  CURLOPT_COOKIEJAR => $cookie_file,
  CURLOPT_COOKIEFILE => $cookie_file,
  CURLOPT_COOKIESESSION => true,
  CURLOPT_HEADER=> 1,
  CURLOPT_RETURNTRANSFER => true,
  CURLOPT_ENCODING => "",
  CURLOPT_MAXREDIRS => 10,
  CURLOPT_TIMEOUT => 30,
  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
  CURLOPT_CUSTOMREQUEST => "POST",
  CURLOPT_POSTFIELDS => "{\"password\":\"**********!\",\"rememberMe\":true,\"username\":\"*******@*****mail.com\"}",
  CURLOPT_HTTPHEADER => array(
    "accept: application/json, text/plain, */*",
    "cache-control: no-cache",
    "content-type: application/json;charset=UTF-8",
    "origin: https://my.ovoenergy.com",
    "postman-token: de767d8a-1a43-0627-f243-6219bc725175",
    "sec-fetch-dest: empty",
    "user-agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.87 Safari/537.36"
  ),
));

$response = curl_exec($curl);
$err = curl_error($curl);
curl_close($curl);


$ch = curl_init();
$date=date("Y-m-d", strtotime( '-1 days' ) );
curl_setopt($ch, CURLOPT_COOKIEJAR,  $cookie_file); 
curl_setopt($ch, CURLOPT_COOKIEFILE, $cookie_file);
curl_setopt($ch, CURLOPT_COOKIESESSION, true );
curl_setopt($ch, CURLOPT_URL ,"https://smartpaym.ovoenergy.com/api/energy-usage/half-hourly/#######?date=".$date); 
curl_setopt($ch, CURLOPT_RETURNTRANSFER , true);
curl_setopt($ch, CURLOPT_ENCODING , "");
curl_setopt($ch, CURLOPT_MAXREDIRS , 10);
curl_setopt($ch, CURLOPT_TIMEOUT , 30);
curl_setopt($ch, CURLOPT_HTTP_VERSION , CURL_HTTP_VERSION_1_1);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "GET");
curl_setopt($ch, CURLOPT_HTTPHEADER , 		array(
    "accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9",
    "cache-control: no-cache",
    "user-agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.87 Safari/537.36")
);

$response = curl_exec($ch);
$err = curl_error($ch);
curl_close($ch);

There we go. $response now contains a big set of JSON for the data we need!

I’ve put myself together a MySQL Database [OvoEnergy] on my webserver with 2 tables, [Electricity] and [Gas]. with a bit more PHP what I will do is run the PHP everymorning at 5AM with a Cron Job.

Pull down all of the energy used the day before as we can’t pull any data from the same day! so my rolling graph will have to show the last “7 days” not inclusive of “today”.

<?php
$cookie_file ='/tmp/cookie.txt';
if (! file_exists($cookie_file) || ! is_writable($cookie_file))
{
    echo 'Cookie file missing or not writable.';
    exit;
}

$curl = curl_init();
curl_setopt_array($curl, array(
  CURLOPT_URL => "https://my.ovoenergy.com/api/v2/auth/login",
  CURLOPT_COOKIEJAR => $cookie_file,
  CURLOPT_COOKIEFILE => $cookie_file,
  CURLOPT_COOKIESESSION => true,
  CURLOPT_HEADER=> 1,
  CURLOPT_RETURNTRANSFER => true,
  CURLOPT_ENCODING => "",
  CURLOPT_MAXREDIRS => 10,
  CURLOPT_TIMEOUT => 30,
  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
  CURLOPT_CUSTOMREQUEST => "POST",
  CURLOPT_POSTFIELDS => "{\"password\":\"********!\",\"rememberMe\":true,\"username\":\"********@*****mail.com\"}",
  CURLOPT_HTTPHEADER => array(
    "accept: application/json, text/plain, */*",
    "cache-control: no-cache",
    "content-type: application/json;charset=UTF-8",
    "origin: https://my.ovoenergy.com",
    "postman-token: de767d8a-1a43-0627-f243-6219bc725175",
    "sec-fetch-dest: empty",
    "user-agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.87 Safari/537.36"
  ),
));

$response = curl_exec($curl);
$err = curl_error($curl);
curl_close($curl);


$ch = curl_init();
$date=date("Y-m-d", strtotime( '-1 days' ) );
curl_setopt($ch, CURLOPT_COOKIEJAR,  $cookie_file); 
curl_setopt($ch, CURLOPT_COOKIEFILE, $cookie_file);
curl_setopt($ch, CURLOPT_COOKIESESSION, true );
curl_setopt($ch, CURLOPT_URL ,"https://smartpaym.ovoenergy.com/api/energy-usage/half-hourly/#######?date=".$date); 
curl_setopt($ch, CURLOPT_RETURNTRANSFER , true);
curl_setopt($ch, CURLOPT_ENCODING , "");
curl_setopt($ch, CURLOPT_MAXREDIRS , 10);
curl_setopt($ch, CURLOPT_TIMEOUT , 30);
curl_setopt($ch, CURLOPT_HTTP_VERSION , CURL_HTTP_VERSION_1_1);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "GET");
curl_setopt($ch, CURLOPT_HTTPHEADER , 		array(
    "accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9",
    "cache-control: no-cache",
    "user-agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.87 Safari/537.36")
);

$response = curl_exec($ch);
$err = curl_error($ch);
curl_close($ch);

if ($err) {
  echo "cURL Error #:" . $err;
} else {
	
	$header_size = curl_getinfo($ch, CURLINFO_HEADER_SIZE);
	$header = substr($response, 0, $header_size);
	$body = substr($response, $header_size);
	$arr = json_decode($body, true);

	$ElectricityRecords = $arr['electricity']['data'];
	$GasRecords = $arr['gas']['data'];
	
		
	$link = mysqli_connect("MySQLServerIP", "Username", "Password", "OvoEnergy");
	// Check connection
	if($link === false){
		die("ERROR: Could not connect. " . mysqli_connect_error());
	}
	 
	foreach ($ElectricityRecords as $value) {
		$value["interval"]["end"] = substr(str_replace("T"," ",$value["interval"]["end"]),0,-4);

		$sql = "INSERT INTO Electricity (dateandtime, consumption, unit) VALUES ('".$value["interval"]["end"]."', '".$value["consumption"]."', 'kWh')";
		if(mysqli_query($link, $sql)){
			echo "Record inserted successfully.";
		} else{
			echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
		}
	}
	
	foreach ($GasRecords as $value) {
		$value["interval"]["end"] = substr(str_replace("T"," ",$value["interval"]["end"]),0,-4);
		
		$sql = "INSERT INTO Gas (dateandtime, consumption, unit) VALUES ('".$value["interval"]["end"]."', '".$value["consumption"]."', 'kWh')";
		if(mysqli_query($link, $sql)){
			echo "Record inserted successfully.";
		} else{
			echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
		}
		
	}

	mysqli_close($link);
	


}
?>

Set this with a cron job to run at every 5AM and now your database will build up its data.

Onto the dashboard!

Okay so I love PHP, I used PHP to get the data from Ovo, and put it into the database, now lets write another PHP file to get that data from the Database ready to give to Charts.JS on the dashboard.

What’s going on below? okay
We are going to call into the database and request all of the information in the database for each table because we are being lazy and prototyping so we don’t care that we could return millions of records, we’ll deal with that later.
Pull the data and time values along with the consumption, the values are how much kWh of energy where used in a half-hour period. we’ll just use PHP to limit this to the last 7 days of data to return to the frontend.

We are returning 2 sets of data so for simplicity in the frontend we will join the 2 datasets using an array of arrays!

<?php
error_reporting(E_ALL);
ini_set('display_errors', true);
header('Content-Type: application/json');

$dbhost = "MySQLServerIP";
$dbuser = "Username";
$dbpass = "Password";
$dbname = "OvoEnergy";

$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sqlE = "SELECT dateandtime, consumption FROM Electricity ORDER BY dateandtime";
$sqlG = "SELECT dateandtime, consumption FROM Gas ORDER BY dateandtime";
$resultE = $conn->query($sqlE);


$dataE = array();
foreach ($resultE as $row) {
	if(time() - strtotime($row["dateandtime"]) > 60*60*24*7) {} else {
		$row["dateandtime"] = date("d-M H:i", strtotime($row["dateandtime"]));
		$dataE[] = $row;
	}
}

$resultG = $conn->query($sqlG);
$dataG = array();
foreach ($resultG as $row) {
	if(time() - strtotime($row["dateandtime"]) > 60*60*24*7) {} else {
		$row["dateandtime"] = date("d-M H:i", strtotime($row["dateandtime"]));
		$dataG[] = $row;
	}
}

$join = array($dataE,$dataG);

echo json_encode($join);



$conn->close();
?>

Wow awersome, now lets get into the front end.

I have a dashboard already put together with some simple bootstrap so we drop in a new column into one of our rows of graphs, We’ll be displaying the graph with Charts.Js which gets drawn on a canvas, so lets prepare the space, graphCanvasOvo is what we will call it!
Then we will put a Javascript call in there to a function we will write in a Javascript file where the rest of my smarthome data is handled.

<div class="col-5">
	<div class="card card-chart">
	  <div class="card-header ">
		<div class="row">               
		</div>
	  </div>
	  <div class="card-body">
		<div id="chart-container">
			<canvas id="graphCanvasOvo"></canvas>
		</div>
		<div class="card-body">
		</div>
		<script>
			showOvoGraph();
		</script>                   
	  </div>
	</div>
  </div>

I have a javasctipt file called smartFunction.Js and in there we write the Charts.JS call.

This is going to call the PHP function to pull the last 7 days of information from the database, and parse it into a lovely graph.

function showOvoGraph()
{
	{
		$.post("../smarthome/php/OvoTemps.php",
		function (data)
		{
			console.log(data);
			var Etime = [];
			var Etemps = [];
			var Gtemps = [];

			for (var i in data[0]) {
				Etime.push(data[0][i].dateandtime);
				Etemps.push(data[0][i].consumption);
			}
			for (var i in data[1]) {
				Gtemps.push(data[1][i].consumption);
			}

			var chartdata = {
				labels: Etime,
				datasets: [
					{
						radius: 0,
						label: 'Electricity',
						borderColor: '#666666',
						hoverBackgroundColor: '#666666',
						hoverBorderColor: '#666666',
						fill: false,
						data: Etemps
					},
					{
						radius: 0,
						label: 'Gas',
						borderColor: '#cfb34e',
						hoverBackgroundColor: '#cfb34e',
						hoverBorderColor: '#cfb34e',
						fill: false,
						data: Gtemps
					}
				]
			};

			var graphTarget = $("#graphCanvasOvo");

			var barGraph = new Chart(graphTarget, {
				type: 'line',
				data: chartdata
			});
		});
	}
}

Wow, and just like that, we have a lovely graph!

For simplicity sake and duplication I have created a GitHub where you can simply copy the files to your web server and replace the “secret” information.

Find it here: [github]

Leave a Reply

Your email address will not be published. Required fields are marked *