Monday, 2 January 2017

Emailing you a daily solar performance update

The ability to see the live data, and view a performance graph is great, but sometimes you'll be away from home and be wondering how your system is doing.

You could spend some time configuring your network to allow remote connections by changing some firewall rules and adding some authentication, or vpn if your router supported it - that would work well, and would mean that you had all the abilities that you have when you're at home.

However there is an alternative method, in reality it's probably sufficient for you to get a daily email of the performance data sent from your Raspberry Pi - that would have the advantage that since that traffic is outbound, it would require no firewall changes, and you could pick up your email anywhere - so that's the method I'm going to describe now.

These instructions presume you've already got a Raspberry Pi or similar making periodic connections to your charge controller and you can read the data on demand.

All that's needed in addition to that is -

  • Some software to send emails (smtp client)
  • A couple of cron jobs to send the emails and tidy up old data
  • A gmail account


Create a Gmail account 
You could use an existing one, though for security you may just make a new one, as you may have to configure it to use less secure authentication and the username and password are stored in plain text on the Pi, which, depending on your setup, may be in an relatively insecure location, e.g. garden shed.

Test the Gmail account to make sure you can send an email and have the email address and password to hand.


Install an smtp client for php 
Full instructions for this are given at
https://www.digitalocean.com/community/tutorials/how-to-use-gmail-or-yahoo-with-php-mail-function

If you're on Raspbian or similar follow the Debian/Ubuntu instructions.


Make some cron jobs
I send my daily email at 9am - I chose this time as at most times of the year there's light at that time of day, and I've found that to be necessary in order to be able to get the kWh data from the charge controller reliably with my setup.


I have the following root cronjobs


* * * * * /var/www/html/epsolar/getsolarstats.php

This harvests performance data every minute and stores it in a database - this is for the dashboard.


50 8 * * * truncate -s 0 /home/pi/report.txt

At 08:50 each day I delete the contents of the previous days email.

 0 9 * * * (sleep 30; /var/www/html/epsolar/example_cli.php >> /home/pi/report.txt)

At 09:00 I run the command line solar data harvesting script and save it to a text file (report.txt).  If this were done exactly at 09:00 then it would clash with the script that runs every minute to do similar things, and would end up not getting data.  Cronjobs have a resolution of 1 minute, so without a little trick there would always be a clash whatever time I chose to do this.

The trick to avoid the clash is to trigger the cron job, but to include a sleep 30 command which makes the command wait for 30 seconds then do it's data gathering - in this way the clash is avoided and both scripts get their data.


As the Pi user I have the following cron job

1 9 * * * cat /home/pi/report.txt | msmtp -a gmail targetemailaddress

Send the email at 09:01


Script modifications
I modified /var/www/html/epsolar/example_cli.php to include

#!/usr/bin/php

as the first line and

$tracer = new PhpEpsolarTracer('/dev/ttyUSB21');

contains the device connection parameters (yours will differ).


The original comes from https://github.com/toggio/PhpEpsolarTracer

I further modified it to order the data into the desired sequence, and I removed some sections that I didn't need.


That's it - I now get a daily email so I can keep an eye on my voltages and power generation when away from home.











Wednesday, 9 November 2016

Instructions for creating a dashboard to monitor your epsolar tracer setup


This is a project that I made for a Raspberry Pi (though it would work on many different platforms) so that it could record and display data from an EPSolar Tracer A Series MPPT charge controller.

The easiest way to know if you need to read further is to simply show you what it outputs (see below).


The gauges on the left show the live status, the central column shows the charging status and the charge controller temperature, and the graph to the right shows all parameters in the previous 48 hours, and is zoomable.


A simpler version is also possible using node-red as in the example below.





If you're here, I guess you're still interested so I give you much more detail and some source code.


Step by step video - note copy the fusioncharts files across at the same time I mention phpepsolartracer library being copied across.




The first thing you need is some sort of connection to the charge controller.

I have made a wireless device that plugs into the RJ45 port on the device (care none standard wiring layout) so that I can wirelessly communicate with it.

Detailed instructions for the construction of this device can be found at Colin Hickey's Youtube channel, specifically

Part 1

Part 2


Part 3


Or you can make or buy a wired connection.  Instructions for making one are given by on Adam Welche's Youtube Channel, specifically




Next you'll need a device to harvest the data, store it, and then to display it on request.

I had a Raspberry Pi from a previous project, so used that.

I installed the Raspbian operating system , specifically I installed the Jessie lite version.

Once that was working and updated I turned the Pi into a LEMP stack following these instructions.

I did deviate slightly from those instructions and changed the root path of the webserver, so when editing the Nginx configuration use the command

root /var/www/html;

instead of the path given by the instructions.  A full copy of my configuration file is given in the comments at the end of these instructions.


Nginx is the webserver running the PHP scripting language
and MySQL as the database to store the data.


The next thing to do is to connect the Pi to the solar charge controller.

Option 1
If you've got a physical wire then when you plug the USB connector into the Pi a new device appears, in my case it appears as /dev/ttyUSB0 

This connection will be useable by root, but not other users/groups, so the simplest (but most insecure) method to change this is to give full control to everyone.

sudo chmod 777 /dev/ttyUSB0

---End of Option 1---

Option 2
If you've built a wireless device then you will have configured it to a specific IP address and Port on your lan - we need to connect to it.

NB - I use the internal IP addresses of 192.168.123.10 for my Pi and 192.168.123.21 for my wireless device - use the IP addresses that are appropriate for the configuration of your lan, i.e., you will most likely have different IP addresses than those I use.


We need a piece of software called Socat to do that so at the Pi command prompt...

sudo apt-get install socat 

After it installs we need to connect socat to our device - as a naming convention I made the tty number correspond to the lan IP I was connecting to

sudo socat pty,link=/dev/ttyUSB21,unlink-close=0,raw,echo=0 tcp:WirelessDeviceIPAddress:23&

e.g.

sudo socat pty,link=/dev/ttyUSB21,unlink-close=0,raw,echo=0 tcp:192.168.123.21:23&

sudo chmod 777 /dev/ttyUSB21

---End of Option 2---


Irrespective of whether you've used Option 1 or Option 2 we need to make sure that the device is useable by non-root users, so the simplest (but most insecure) method to change this is to give full control to everyone.

So issue the command
sudo chmod 777 /dev/ttyUSB0
or
sudo chmod 777 /dev/ttyUSB21

changing the device details to those you're using

I know I've said that twice, but if you omit that stage then you can have everything set up and permissions will prevent it working.




Nearly there....
We've got a connected webserver, now we need to get the data from the charge controller and store it.

Remember we set up a mysql server, well now we need to make a database.  When you set up mysql you will have configured a root user password.

Using the tool of your choice eg command line or phpmyadmin run the following sql commands to build the database (note it will delete a table called stats in the created database if you rerun the command).

CREATE DATABASE `solardata` ;

/*Table structure for table `stats` */

DROP TABLE IF EXISTS `stats`;

CREATE TABLE `stats` (
  `Controller` int(2) NOT NULL,
  `timestamp` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `PV array voltage` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `PV array current` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `PV array power` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Battery voltage` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Battery charging current` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Battery charging power` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Load voltage` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Load current` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Load power` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Charger temperature` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Heat sink temperature` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Battery status` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Equipment status` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`Controller`,`timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I made a field Controller in the database as I may expand my system and have 2 controllers, the other fields are the ones that I chose to store from the available data from the controller - you are able to modify this as required.

We now need some PHP that speaks the same language (modbus) as our charge controller - there's a really useful library already written that does just that written by Luca Soltoggio http://arduinoelectronics.wordpress.com/ ~ http://minibianpi.wordpress.com and containing PhpSerial by Rémy Sanchez and Rizwan Kassim - the library can be downloaded from GitHub at https://github.com/toggio/PhpEpsolarTracer


Once downloaded, expand it and put it on a folder in your webserver, e.g.

mkdir /var/www/html/epsolar

and put the contents of the expanded folder into it.


Now we'll set up a script to get data from the controller and store it in the database.

Using your favorite text editor edit a file in /var/www/html/epsolar called getsolarstats.php

add the following to that file, changing 'databaseusername' and 'databasepassword' to ones that you've got set up for your database.

#!/usr/bin/php

<?php
//harvest data and stores it in a database

$dbh = new PDO("mysql:host=localhost;dbname=solardata", "databaseusername", "databasepassword");

 
//this is planning for future expansion, this array holds the wireless device connection details
$solararray = array();
$solararray["/dev/ttyUSB21"]["ip"] = '192.168.123.21';
$solararray["/dev/ttyUSB21"]["port"] = '23';

//eg expanded system with a second controller
//$solararray["/dev/ttyUSB22"]["ip"] = '192.168.123.22';
//$solararray["/dev/ttyUSB22"]["port"] = '23';


require_once 'PhpEpsolarTracer.php';

$time = time();

$i = 1;
while (list ($key, $val) = each($solararray)) {

    $tracer = new PhpEpsolarTracer($key);


    if ($tracer->getRealtimeData()) {
 
        $sth = $dbh->prepare("insert into stats (`Controller`,`timestamp`,`PV array voltage`,`PV array current`,`PV array power`,`Battery voltage`,`Battery charging current`,`Battery charging power`,`Load voltage`,`Load current`,`Load power`,`Charger temperature`, `Heat sink temperature`,`Battery status`,`Equipment status`) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        $sth->BindParam(1, $i);
        $sth->BindParam(2, $time);
        $sth->BindParam(3, $tracer->realtimeData[0]);
        $sth->BindParam(4, $tracer->realtimeData[1]);
        $sth->BindParam(5, $tracer->realtimeData[2]);
        $sth->BindParam(6, $tracer->realtimeData[3]);
        $sth->BindParam(7, $tracer->realtimeData[4]);
        $sth->BindParam(8, $tracer->realtimeData[5]);
        $sth->BindParam(9, $tracer->realtimeData[6]);
        $sth->BindParam(10, $tracer->realtimeData[7]);
        $sth->BindParam(11, $tracer->realtimeData[8]);
        $sth->BindParam(12, $tracer->realtimeData[10]);
        $sth->BindParam(13, $tracer->realtimeData[11]);
        $sth->BindParam(14, $tracer->realtimeData[15]);
        $sth->BindParam(15, $tracer->realtimeData[16]);

        $sth->execute();

        //station id
        $i++;
    }
}
?>


Change the permissions on that script so it's runable

chmod 755 /var/www/html/epsolar/getsolarstats.php

That script should be runnable now, and will pull the data and store it in the database.  To do that automatically we can set up a cronjob - the following one will get the data every minute.

sudo crontab -e

Then add the following

* * * * * /var/www/html/epsolar/getsolarstats.php


Nearly there....  

Our data is now being stored in the database - we can display it in one of 2 ways - the first is more detailed

Method 1

For the nice gauges I used a commercial, free to use, javascript library from http://www.fusioncharts.com/ 

Download it and place it in  
/var/www/html/epsolar/fusioncharts 
so that the folder fusion charts contains four folders and index.html

Make sure it has the correct permissions using the command

chmod -R 755 /var/www/html/epsolar


And finally, here's a highly modified version of example_web.php from phpepsolartracer

Using your favorite editor make a file index.php in /var/www/html/epsolar
change /dev/ttyUSB21 and 'databaseusername' and 'databasepassword' to ones that you've used


<?php
/*
 * PHP EpSolar Tracer Class (PhpEpsolarTracer) v0.9
 *
 * Library for communicating with
 * Epsolar/Epever Tracer BN MPPT Solar Charger Controller
 *
 * THIS PROGRAM COMES WITH ABSOLUTELY NO WARRANTIES !
 * USE IT AT YOUR OWN RISKS !
 *
 * Copyright (C) 2016 under GPL v. 2 license
 * 13 March 2016
 *
 * @author Luca Soltoggio
 * http://www.arduinoelettronica.com/
 * https://arduinoelectronics.wordpress.com/
 *
 * This is an example on how to use the library
 * It creates a web page with tracer data
 * 
 * The version below is a highly modified version of that referred to by the headers above, the origninal can be found at https://github.com/toggio/PhpEpsolarTracer
 */

require_once 'PhpEpsolarTracer.php';
$tracer = new PhpEpsolarTracer('/dev/ttyUSB21');

$tracerstatus_bgcolor = "#dedede";
// $ecolor = "black";
// $battSoc = 0;
// Get Info and check if is connected
if ($tracer->getInfoData()) {
    $connection = "Connected";
    $connection_bgcolor = "lime";
} else {
    $connection = "Disconnected";
    $connection_bgcolor = "red";
}

// Get Real Time Data
if ($tracer->getRealTimeData()) {
    $tracerstatus_bgcolor = "lime";
    $equipStatus = $tracer->realtimeData[16];
    $chargStatus = 0b11 & ($equipStatus >> 2);
    switch ($chargStatus) {
        case 0: $eStatus = "Not charging";
            break;
        case 1: $eStatus = "Float (13.8V)";
            break;
        case 2: $eStatus = "Boost (14.4V)";
            break;
        case 3: $eStatus = "Equalization (14.6V)";
            break;
    };
    if ($equipStatus >> 4) {
        $eStatus = "<font color=\"red\">FAULT</font>";
        $tracerstatus_bgcolor = "red";
    }

    $battStatus = $tracer->realtimeData[15];
    $battLevel = 0b1111 & $battStatus;
    switch ($battLevel) {
        case 0: $bStatus = "Normal";
            break;
        case 1: $bStatus = "<font color=\"red\">Overvolt</font>";
            break;
        case 2: $bStatus = "<font color=\"yellow\">Undervolt</font>";
            break;
        case 3: $bStatus = "<font color=\"red\">Low volt disconnect</font>";
            break;
        case 4: {
                $bStatus = "<font color=\"red\">FAULT</font>";
                $tracerstatus_bgcolor = "red";
                break;
            }
    }

    $battSoc = $tracer->realtimeData[12];
}

//get data for the last 2 weeks
//$ago = time() - 1209600;
//get data for the last 24 hrs
//$ago = time() - 86400;
//get data for the last 48 hrs
$ago = time() - (86400 * 2);
$dbh = new PDO("mysql:host=localhost;dbname=solardata", "
databaseusername", "databasepassword");
$sth = $dbh->prepare("select `timestamp`,`PV array voltage`,`PV array current`,`PV array power`,`Battery voltage`,`Battery charging current`,`Battery charging power`,`Load voltage`,`Load current`,`Load power` from stats where `Controller` = 1 and `timestamp` > ? order by `timestamp` asc");
$sth->bindParam(1, $ago);
$sth->execute();

//build the json array
$data = array();
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
    $data["category"][] = date("H:i", $row["timestamp"]);
    while (list($key, $val) = each($row)) {
        $data[$key][] = $val;
    }
}

unset($data["timestamp"]);

reset($data);
?>
<!DOCTYPE html>
<html lang="it">
    <head>
        <script type="text/javascript" src="./fusioncharts/js/fusioncharts.js"></script>
        <script type="text/javascript" src="fusioncharts/js/fusioncharts.charts.js"></script>
        <script type="text/javascript" src="fusioncharts/js/fusioncharts.widgets.js"></script>
        <script type="text/javascript" src="fusioncharts/js/themes/fusioncharts.theme.fint.js"></script>



        <script type="text/javascript">
                    FusionCharts.ready(function () {
                    var fusioncharts = new FusionCharts({
                    type: 'zoomlinedy',
                            renderAt: 'chart',
                            width: '800',
                            height: '600',
                            dataFormat: 'json',
                            dataSource: {
                            "chart": {
                            "caption": "Performance History",
                                    "pYAxisname": "Value",
                                    "sYAxisname": "PV Array Voltage (V)",
                                    "xAxisname": "Time",
                                    "pYAxisMinValue":"0",
                                    "pYAxisMaxValue":"15",
                                    "sYAxisMaxValue": "100",
                                    "sYAxisMinValue": "0",
                                    "lineThickness": "1",
                                    "compactdatamode": "1",
                                    "dataseparator": "|",
                                    "labelHeight": "30",
                                    "theme": "fint"
                            },
                                    "categories": [{
                                    "category": "<?php
echo implode('|', $data["category"]);
unset($data["category"]);
reset($data);
?>"
                                    }],
<?php
$i = 1;
echo '"dataset":[';
while (list ($key, $val) = each($data)) {


    echo '{"seriesname": "' . $key . '",';
    if (stripos($key, 'PV array voltage') !== FALSE) {
        echo '"parentYAxis": "S",';
    } else {
        echo '"parentYAxis": "P",';
    }
    echo '"data": "' . implode('|', $val) . '"';
    echo "}";
    if ($i != count($data)) {
        echo ",";
    }

    $i++;
}
?>

                            ]
                            }
                    }
                    );
                            fusioncharts.render();
                    });</script>


        <script type="text/javascript">
                    FusionCharts.ready(function () {
                    var fusioncharts = new FusionCharts({
                    type: 'angulargauge',
                            renderAt: 'currentflow',
                            width: '400',
                            height: '250',
                            dataFormat: 'json',
                            dataSource: {
                            "chart": {
                            "caption": "Nett Current (A)",
                                    "subcaption": "-ve = from battery | +ve = to battery ",
                                    "lowerLimit": "-30",
                                    "upperLimit": "+30",
                                    "theme": "fint",
                                    "showValue": "1",
                                    "valueBelowPivot": "1",
                                    "majorTMNumber": "7",
                                    "minorTMNumber": "9",
                            },
                                    "colorRange": {
                                    "color": [{
                                    "minValue": "-30",
                                            "maxValue": "0",
                                            "code": "#e44a00"
                                    }, {
                                    "minValue": "0.001",
                                            "maxValue": "30",
                                            "code": "#6baa01"
                                    }]
                                    },
                                    "dials": {
                                    "dial": [{
                                    "value": "<?php echo $tracer->realtimeData[4] - $tracer->realtimeData[7]; ?>"
                                    }]
                                    }
                            }
                    }
                    );
                            fusioncharts.render();
                    });</script>

        <script type="text/javascript">
                    FusionCharts.ready(function () {
                    var fusioncharts = new FusionCharts({
                    type: 'angulargauge',
                            renderAt: 'PV voltage',
                            width: '300',
                            height: '200',
                            dataFormat: 'json',
                            dataSource: {
                            "chart": {
                            "caption": "PV Voltage (V)",
                                    "lowerLimit": "0",
                                    "upperLimit": "100",
                                    "theme": "fint",
                                    "showValue": "1",
                                    "valueBelowPivot": "1",
                                    "majorTMNumber": "9",
                                    "minorTMNumber": "5",
                            },
                                    "colorRange": {
                                    "color": [{
                                    "minValue": "0",
                                            "maxValue": "90",
                                            "code": "#6baa01"
                                    }, {
                                    "minValue": "91",
                                            "maxValue": "100",
                                            "code": "#e44a00"
                                    }]
                                    },
                                    "dials": {
                                    "dial": [{
                                    "value": "<?php echo $tracer->realtimeData[0]; ?>"
                                    }]
                                    }
                            }
                    }
                    );
                            fusioncharts.render();
                    });</script>


        <script type="text/javascript">
                    FusionCharts.ready(function () {
                    var fusioncharts = new FusionCharts({
                    type: 'angulargauge',
                            renderAt: 'Battery voltage',
                            width: '300',
                            height: '200',
                            dataFormat: 'json',
                            dataSource: {
                            "chart": {
                            "caption": "Battery Voltage (V)",
                                    "lowerLimit": "10",
                                    "upperLimit": "15",
                                    "theme": "fint",
                                    "showValue": "1",
                                    "valueBelowPivot": "1",
                                    "majorTMNumber": "7",
                                    "minorTMNumber": "9",
                            },
                                    "colorRange": {
                                    "color": [{
                                    "minValue": "10",
                                            "maxValue": "11",
                                            "code": "#e44a00"
                                    }, {
                                    "minValue": "11.001",
                                            "maxValue": "13.8",
                                            "code": "#6baa01"
                                    }, {
                                    "minValue": "13.801",
                                            "maxValue": "14.5",
                                            "code": "#f8bd19"
                                    }, {
                                    "minValue": "14.501",
                                            "maxValue": "15",
                                            "code": "#e44a00"
                                    }]
                                    },
                                    "dials": {
                                    "dial": [{
                                    "value": "<?php echo $tracer->realtimeData[3]; ?>"
                                    }]
                                    }
                            }
                    }
                    );
                            fusioncharts.render();
                    });</script>

        <script type="text/javascript">
                    FusionCharts.ready(function () {
                    var fusioncharts = new FusionCharts({
                    type: 'angulargauge',
                            renderAt: 'Load voltage',
                            width: '300',
                            height: '200',
                            dataFormat: 'json',
                            dataSource: {
                            "chart": {
                            "caption": "Load Voltage (V)",
                                    "lowerLimit": "10",
                                    "upperLimit": "15",
                                    "theme": "fint",
                                    "showValue": "1",
                                    "valueBelowPivot": "1",
                                    "majorTMNumber": "16",
                                    "minorTMNumber": "5",
                            },
                                    "colorRange": {
                                    "color": [{
                                    "minValue": "0",
                                            "maxValue": "13.8",
                                            "code": "#6baa01"
                                    }, {
                                    "minValue": "13.801",
                                            "maxValue": "14.5",
                                            "code": "#f8bd19"
                                    }, {
                                    "minValue": "14.501",
                                            "maxValue": "15",
                                            "code": "#e44a00"
                                    }]
                                    },
                                    "dials": {
                                    "dial": [{
                                    "value": "<?php echo $tracer->realtimeData[6]; ?>"
                                    }]
                                    }
                            }
                    }
                    );
                            fusioncharts.render();
                    });</script>


        <script type="text/javascript">
                    FusionCharts.ready(function () {
                    var fusioncharts = new FusionCharts({
                    type: 'angulargauge',
                            renderAt: 'PV power',
                            width: '300',
                            height: '200',
                            dataFormat: 'json',
                            dataSource: {
                            "chart": {
                            "caption": "PV power (W)",
                                    "lowerLimit": "0",
                                    "upperLimit": "400",
                                    "theme": "fint",
                                    "showValue": "1",
                                    "valueBelowPivot": "1",
                                    "majorTMNumber": "5",
                                    "minorTMNumber": "9",
                            },
                                    "colorRange": {
                                    "color": [{
                                    "minValue": "0",
                                            "maxValue": "350",
                                            "code": "#6baa01"
                                    }, {
                                    "minValue": "351",
                                            "maxValue": "400",
                                            "code": "#e44a00"
                                    }]
                                    },
                                    "dials": {
                                    "dial": [{
                                    "value": "<?php echo $tracer->realtimeData[2]; ?>"
                                    }]
                                    }
                            }
                    }
                    );
                            fusioncharts.render();
                    });</script>


        <script type="text/javascript">
                    FusionCharts.ready(function () {
                    var fusioncharts = new FusionCharts({
                    type: 'angulargauge',
                            renderAt: 'Battery power',
                            width: '300',
                            height: '200',
                            dataFormat: 'json',
                            dataSource: {
                            "chart": {
                            "caption": "Battery Power (W)",
                                    "lowerLimit": "0",
                                    "upperLimit": "400",
                                    "theme": "fint",
                                    "showValue": "1",
                                    "valueBelowPivot": "1",
                                    "majorTMNumber": "5",
                                    "minorTMNumber": "9",
                            },
                                    "colorRange": {
                                    "color": [{
                                    "minValue": "0",
                                            "maxValue": "350",
                                            "code": "#6baa01"
                                    }, {
                                    "minValue": "351",
                                            "maxValue": "400",
                                            "code": "#e44a00"
                                    }]
                                    },
                                    "dials": {
                                    "dial": [{
                                    "value": "<?php echo $tracer->realtimeData[5]; ?>"
                                    }]
                                    }
                            }
                    }
                    );
                            fusioncharts.render();
                    });</script>

        <script type="text/javascript">
                    FusionCharts.ready(function () {
                    var fusioncharts = new FusionCharts({
                    type: 'angulargauge',
                            renderAt: 'Load power',
                            width: '300',
                            height: '200',
                            dataFormat: 'json',
                            dataSource: {
                            "chart": {
                            "caption": "Load Power (W)",
                                    "lowerLimit": "0",
                                    "upperLimit": "400",
                                    "theme": "fint",
                                    "showValue": "1",
                                    "valueBelowPivot": "1",
                                    "majorTMNumber": "5",
                                    "minorTMNumber": "9",
                            },
                                    "colorRange": {
                                    "color": [{
                                    "minValue": "0",
                                            "maxValue": "350",
                                            "code": "#6baa01"
                                    }, {
                                    "minValue": "351",
                                            "maxValue": "400",
                                            "code": "#e44a00"
                                    }]
                                    },
                                    "dials": {
                                    "dial": [{
                                    "value": "<?php echo $tracer->realtimeData[8]; ?>"
                                    }]
                                    }
                            }
                    }
                    );
                            fusioncharts.render();
                    });</script>

        <script type="text/javascript">
                    FusionCharts.ready(function () {
                    var fusioncharts = new FusionCharts({
                    type: 'angulargauge',
                            renderAt: 'PV current',
                            width: '300',
                            height: '200',
                            dataFormat: 'json',
                            dataSource: {
                            "chart": {
                            "caption": "PV Current (A)",
                                    "lowerLimit": "0",
                                    "upperLimit": "30",
                                    "theme": "fint",
                                    "showValue": "1",
                                    "valueBelowPivot": "1",
                                    "majorTMNumber": "4",
                                    "minorTMNumber": "9",
                            },
                                    "colorRange": {
                                    "color": [{
                                    "minValue": "0",
                                            "maxValue": "25",
                                            "code": "#6baa01"
                                    }, {
                                    "minValue": "25.001",
                                            "maxValue": "30",
                                            "code": "#e44a00"
                                    }]
                                    },
                                    "dials": {
                                    "dial": [{
                                    "value": "<?php echo $tracer->realtimeData[1]; ?>"
                                    }]
                                    }
                            }
                    }
                    );
                            fusioncharts.render();
                    });</script>

        <script type="text/javascript">
                    FusionCharts.ready(function () {
                    var fusioncharts = new FusionCharts({
                    type: 'angulargauge',
                            renderAt: 'Battery current',
                            width: '300',
                            height: '200',
                            dataFormat: 'json',
                            dataSource: {
                            "chart": {
                            "caption": "Battery Current (A)",
                                    "lowerLimit": "-30",
                                    "upperLimit": "30",
                                    "theme": "fint",
                                    "showValue": "1",
                                    "valueBelowPivot": "1",
                                    "majorTMNumber": "7",
                                    "minorTMNumber": "9",
                            },
                                    "colorRange": {
                                    "color": [{
                                    "minValue": "-30",
                                            "maxValue": "0",
                                            "code": "#e44a00"
                                    }, {
                                    "minValue": "0.001",
                                            "maxValue": "30",
                                            "code": "#6baa01"
                                    }]
                                    },
                                    "dials": {
                                    "dial": [{
                                    "value": "<?php echo $tracer->realtimeData[4]; ?>"
                                    }]
                                    }
                            }
                    }
                    );
                            fusioncharts.render();
                    });</script>

        <script type="text/javascript">
                    FusionCharts.ready(function () {
                    var fusioncharts = new FusionCharts({
                    type: 'angulargauge',
                            renderAt: 'Load current',
                            width: '300',
                            height: '200',
                            dataFormat: 'json',
                            dataSource: {
                            "chart": {
                            "caption": "Load Current (A)",
                                    "lowerLimit": "0",
                                    "upperLimit": "30",
                                    "theme": "fint",
                                    "showValue": "1",
                                    "valueBelowPivot": "1",
                                    "majorTMNumber": "4",
                                    "minorTMNumber": "9",
                            },
                                    "colorRange": {
                                    "color": [{
                                    "minValue": "0",
                                            "maxValue": "25",
                                            "code": "#6baa01"
                                    }, {
                                    "minValue": "25.001",
                                            "maxValue": "30",
                                            "code": "#e44a00"
                                    }]
                                    },
                                    "dials": {
                                    "dial": [{
                                    "value": "<?php echo $tracer->realtimeData[7]; ?>"
                                    }]
                                    }
                            }
                    }
                    );
                            fusioncharts.render();
                    });</script>


        <script type="text/javascript">
                    FusionCharts.ready(function(){
                    var fusioncharts = new FusionCharts({
                    type: 'thermometer',
                            renderAt: 'Charger temp',
                            width: '160',
                            height: '400',
                            dataFormat: 'json',
                            dataSource: {
                            "chart": {
                            "caption": "Charger Temperature",
                                    "lowerLimit": "-20",
                                    "upperLimit": "100",
                                    "numberSuffix": "°C",
                                    "showhovereffect": "1",
                                    "decimals": "2",
                                    "majorTMNumber": "13",
                                    "minorTMNumber": "5",
                                    "thmBulbRadius": "25",
                                    "thmOriginX": "80",
<?php
switch ($tracer->realtimeData[10]) {
    case ($tracer->realtimeData[10] < 10):
        echo '"gaugeFillColor": "#008ee4",';
        echo '"gaugeBorderColor": "#008ee4",';
        break;
    case ($tracer->realtimeData[10] >= 10 && $tracer->realtimeData[10] < 70):
        echo '"gaugeFillColor": "#6baa01",';
        echo '"gaugeBorderColor": "#6baa01",';
        break;
    case ($tracer->realtimeData[10] >= 70 && $tracer->realtimeData[10] < 75):
        echo '"gaugeFillColor": "#f8bd19",';
        echo '"gaugeBorderColor": "#f8bd19",';
        break;
    case ($tracer->realtimeData[10] >= 75):
        echo '"gaugeFillColor": "#e44a00",';
        echo '"gaugeBorderColor": "#e44a00",';
        break;
}
?>
                            "gaugeFillAlpha": "70",
                                    //Customizing gauge border
                                    "showGaugeBorder": "1",
                                    "gaugeBorderThickness": "2",
                                    "gaugeBorderAlpha": "60",
                                    "theme": "fint",
                                    "chartBottomMargin": "20"
                            },
                                    "value": "<?php echo $tracer->realtimeData[10]; ?>"
                            }
                    }
                    );
                            fusioncharts.render();
                    });
        </script>

        <meta charset="utf-8">
        <meta name="description" content="">
        <meta name="keywords" content="">
        <title>Power Status</title>
        <meta name="viewport" content="width=device-width, initial-scale=1.0" />
        <style>
            table.gridtable {
                font-family: verdana,arial,sans-serif;
                font-size:12px;
                color:#333333;
                border-width: 1px;
                border-color: #666666;
                border-collapse: collapse;
                width: 100%;
            }
            table.gridtable th {
                border-width: 1px;
                padding: 8px;
                border-style: solid;
                border-color: #666666;
                background-color: #dedede;
                text-align: center;
            }
            table.gridtable th.connection {
                background-color: <?php echo $connection_bgcolor ?>;
                text-align:center;
            }
            table.gridtable th.tracerstatus {
                background-color: <?php echo $tracerstatus_bgcolor ?>;
                text-align:center;
            }
            table.gridtable td {
                border-width: 1px;
                border-top: 0px;
                padding: 5px;
                border-style: solid;
                border-color: #666666;
                background-color: #ffffff;
                text-align:right;
                height:17px;
            }
            table.gridtable td.bold {
                font-weight: bold;
                width: 33.3%;
                text-align:left;
            }
            table.gridtable td.head {
                font-weight: bold;
                width: 33.3%;
                text-align:right;
            }
            table.gridtable td.button {
                width: 15%;
                text-align:center;
                background-color:#efefef;
                color:#cecece;
                cursor: default;
            }
            div.centered
            {
                text-align: center;
            }
            div.inner
            {
                max-width: 650px;
                width: 95%;
                text-align: center;
                margin: 0 auto;
            }
            div.inner table
            {
                margin: 0 auto;
                text-align: left;
            }
            #chargepercentp {
                width: 100%;
                height: 100%;
                position: absolute;
                vertical-align: middle;
                left:-5px;
                z-index: 10;
            }
            #chargepercentg {
                top: 0;
                width: <?php echo $battSoc; ?>%;
                height: 100%;
                position: absolute;
                background-color:#dedede;
                margin: 0 auto;
                padding: 0;
                z-index: 1;
            }
            #container {
                position: relative;
                top: 0;
                left: 0;
                width:100%;
                height:100%;
                margin: 0 auto;
                padding: 0;
                vertical-align: middle;
                line-height: 27px;
            }
        </style>
    </head>
    <body>
        <div class="centered">
            <table style='width:97%;'>
                <tr>
                    <td>
                        <table>
                            <tr><td colspan="3" style='text-align:center;'><div id="currentflow"></div></td></tr>
                            <tr><td><div id="PV voltage"></div></td><td><div id="Battery voltage"></div></td><td><div id="Load voltage"></div></td></tr>
                            <tr><td><div id="PV current"></div></td><td><div id="Battery current"></div></td><td><div id="Load current"></div></td></tr>
                            <tr><td><div id="PV power"></div></td><td><div id="Battery power"></div></td><td><div id="Load power"></div></td></tr>
                        </table>
                    </td>
                    <td>
                        <table class="gridtable">
                            <tr>
                                <th class="tracerstatus" id="tracerstatus" colspan=2>-= Tracer Status =-</th>
                            </tr>
                            <tr>
                                <td class="bold">Battery status</td><td class="status" id="batterystatus"><?php echo $bStatus; ?></td>
                            </tr>
                            <tr>
                                <td class="bold">Equipment status</td><td class="status" id="equipmentstatus"><?php echo $eStatus; ?></td>
                            </tr>
                            <tr>
                                <td colspan="2" style='text-align:center;'><div id="Charger temp"></div></td>
                            </tr>
                        </table>
                    </td>
                    <td><div id="chart"></div></td>
                </tr>
                <tr><td colspan="3"><table class="gridtable">
                            <tr>
                                <th class="connection" id="connection"><?php echo $connection; ?></th>
                            </tr>
                        </table></td></tr>
            </table>
            <br>
        </div>
    </body>
</html>


Make sure it has the correct permissions using the command

chmod -R 755 /var/www/html/epsolar

That's it, just point a web browser to the Pi

e.g.

http://192.168.123.10/epsolar/index.php
or whatever your Pi network address is

You should see a dashboard similar to the one pictured at the top of this blog.

Mine is in development still, and I may add/remove graphs and gauges, eg I'm not sure if a nett charging gauge is needed.  I'd like to add a switch to turn on and off the load, but I've not managed to do that yet.

Things you'll want to change - each script on the page is responsible for an individual graph, and I've coloured and scaled them for my needs, you may well want to eg change the maximum deflection, and colour boundaries. Do this as required, it should be easy to identify what needs changing.

Thanks to all those that posted code and instructions that allowed me to do my little bit.

Enjoy your dashboard :)


UPDATE
I have now managed to figure out how to toggle the load - this opens up a whole raft of possibilities, including cron jobs :)


You will need to be able to issue the 'turn on load' and 'turn off load' commands.

I don't know how these commands will interact with other devices, so use at your own risk, but they work well with my 30A Tracer through the day, but at night they're not working as well, don't know if this is pv voltage related, or the low night time temperatures effecting my wireless transmitter - more investigation needed on that front.


So we need to add a couple of functions to PhpEpsolarTracer.php


    //manually turn on
    public function setLoadOn() {
        $this->tracer->sendRawQuery("\x01\x05\x00\x02\xff\x00\x2d\xfa", false);
    }

    //manually turn off
    public function setLoadOff() {
        $this->tracer->sendRawQuery("\x01\x05\x00\x02\x00\x00\x6c\x0a", false);
    }



- I added them after the function below


private function divide($a, $b) {
        return $a / $b;
    }



We'll need to add something to index.php that can handle our request to change the load status - my Pi is firewalled so there's no security to this, but don't have this publicly facing as anyone could toggle your load.


At the very top of index.php, just after


require_once 'PhpEpsolarTracer.php';
$tracer = new PhpEpsolarTracer('/dev/ttyUSB21');



add the following lines


//do this first so we can see the result in the collected data
if ($_GET["load"] == 'on') {
    $tracer->setLoadOn();
}
if ($_GET["load"] == 'off') {
    $tracer->setLoadOff();
}



That's it - just browse to your dashboard with the url


http://yourpiipaddress/epsolar/index.php?load=on


and your load will be turned on


OR


http://yourpiipaddress/epsolar/index.php?load=off


and your load will be turned off

I've added a slider to my dashboard that means I can do this automatically, and I'll be working on some additions that will mean I can easily add timed events.





Method 2

It's possible to display the data from the database in node-red.

The installation of node-red on your raspberry pi is not covered here, but you can follow the instructions given in this video to see what node-red can do and also how to install it on the raspberry pi.


One of the nice things about node-red is that it's easy to share a flow - the following uses the mysql node to get data from the database and plot it on the node-red dashboard, just copy and import the following.



[
    {
        "id": "540ef0ed.76002",
        "type": "mysql",
        "z": "41bc4e86.a2c73",
        "mydb": "4c3e4c8a.898584",
        "name": "Solardata",
        "x": 442,
        "y": 1418,
        "wires": [
            [
                "dab8ec4a.48d97"
            ]
        ]
    },
    {
        "id": "97a4a648.fd044",
        "type": "inject",
        "z": "41bc4e86.a2c73",
        "name": "",
        "topic": "SELECT SUM((SELECT `Battery voltage` FROM stats WHERE Controller = 1 ORDER BY `timestamp` DESC LIMIT 1) + (SELECT `Battery voltage` FROM stats WHERE Controller = 2 ORDER BY `timestamp` DESC LIMIT 1))/2 AS `Battery Voltage`, SUM((SELECT `Battery charging power` FROM stats WHERE Controller = 1 ORDER BY `timestamp` DESC LIMIT 1) + (SELECT `Battery charging power` FROM stats WHERE Controller = 2 ORDER BY `timestamp` DESC LIMIT 1)) AS `Battery Charging power`, SUM((SELECT `Load power` FROM stats WHERE Controller = 1 ORDER BY `timestamp` DESC LIMIT 1)) AS `Load power`",
        "payload": "",
        "payloadType": "str",
        "repeat": "60",
        "crontab": "",
        "once": true,
        "x": 209,
        "y": 1347,
        "wires": [
            [
                "540ef0ed.76002"
            ]
        ]
    },
    {
        "id": "8d0012a4.32f418",
        "type": "ui_gauge",
        "z": "41bc4e86.a2c73",
        "name": "Battery Voltage",
        "group": "9f358bc8.315728",
        "order": 1,
        "width": "6",
        "height": "6",
        "gtype": "gage",
        "title": "Battery Voltage",
        "label": "Volts",
        "format": "{{value}}",
        "min": "10",
        "max": "15",
        "colors": [
            "#b50012",
            "#00e606",
            "#ca3838"
        ],
        "seg1": "11",
        "seg2": "14.3",
        "x": 1100,
        "y": 1275,
        "wires": []
    },
    {
        "id": "143a1fa7.c76eb",
        "type": "debug",
        "z": "41bc4e86.a2c73",
        "name": "",
        "active": false,
        "console": "false",
        "complete": "false",
        "x": 1041,
        "y": 1476,
        "wires": []
    },
    {
        "id": "dab8ec4a.48d97",
        "type": "function",
        "z": "41bc4e86.a2c73",
        "name": "parse data",
        "func": "var voltage = msg.payload[0]['Battery Voltage'].toFixed(2);\nvar power = msg.payload[0]['Battery Charging power'].toFixed(2);\nvar loadpower = msg.payload[0]['Load power'].toFixed(2);\n\nmsg.topic = \"Voltage\";\nmsg.payload = voltage;\n\nvar msg1 = {topic:\"Charging Power\", payload: power};\nvar msg2 = {topic:\"Load Power\", payload: loadpower};\nvar msg3 = {topic:\"Voltage\", payload: voltage};\n\nreturn [msg, msg1, [msg1,msg2,msg3]];",
        "outputs": "3",
        "noerr": 0,
        "x": 625.9794921875,
        "y": 1332.0484619140625,
        "wires": [
            [
                "8d0012a4.32f418",
                "143a1fa7.c76eb"
            ],
            [
                "143a1fa7.c76eb",
                "b1566728.7253b"
            ],
            [
                "91e58ef9.5d602"
            ]
        ]
    },
    {
        "id": "91e58ef9.5d602",
        "type": "ui_chart",
        "z": "41bc4e86.a2c73",
        "name": "Performance",
        "group": "9f358bc8.315728",
        "order": 3,
        "width": "6",
        "height": "5",
        "label": "Recent Performance",
        "chartType": "line",
        "legend": "false",
        "xformat": "HH:mm",
        "interpolate": "linear",
        "nodata": "",
        "ymin": "0",
        "ymax": "",
        "removeOlder": "36",
        "removeOlderPoints": "",
        "removeOlderUnit": "3600",
        "cutout": 0,
        "colors": [
            "#1f77b4",
            "#aec7e8",
            "#ff7f0e",
            "#2ca02c",
            "#98df8a",
            "#d62728",
            "#ff9896",
            "#9467bd",
            "#c5b0d5"
        ],
        "x": 1120,
        "y": 1391,
        "wires": [
            [],
            []
        ]
    },
    {
        "id": "b1566728.7253b",
        "type": "ui_gauge",
        "z": "41bc4e86.a2c73",
        "name": "Charging Power",
        "group": "9f358bc8.315728",
        "order": 2,
        "width": "6",
        "height": "6",
        "gtype": "gage",
        "title": "Charging Power",
        "label": "Watts",
        "format": "{{value}}",
        "min": "0",
        "max": "750",
        "colors": [
            "#00e606",
            "#00e606",
            "#ca3838"
        ],
        "seg1": "",
        "seg2": "700",
        "x": 1105,
        "y": 1323,
        "wires": []
    },
    {
        "id": "4c3e4c8a.898584",
        "type": "MySQLdatabase",
        "z": "",
        "host": "192.168.123.10",
        "port": "3306",
        "db": "solardata",
        "tz": ""
    },
    {
        "id": "9f358bc8.315728",
        "type": "ui_group",
        "z": "",
        "name": "Solar Stuff",
        "tab": "ff53e552.1dc31",
        "order": 2,
        "disp": true,
        "width": "18"
    },
    {
        "id": "ff53e552.1dc31",
        "type": "ui_tab",
        "z": "",
        "name": "Home",
        "icon": "dashboard"
    }
]



When you've imported it change the topic in the inject node to

SELECT `Battery voltage` AS `Battery Voltage`,`Battery charging power`, `Load power` FROM stats ORDER BY `timestamp` DESC LIMIT 1

if you've only 1 charge controller, I run 2 and so my query was doing some maths and returning average figures.


You'll also need to update the mysql node with your database username and password.


Legal stuff

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
https://www.gnu.org/licenses/old-licenses/gpl-2.0.en.html