Monday, 6 November 2017

Flashing sonoffs with custom firmware

First things first.

DO NOT work on any sonoff devices that are plugged into the mains, at best you may get a nasty shock, and at worst you may kill yourself.  Do not mess with mains devices if you don't know what you're doing, or local laws prevent it.

Sonoffs are relays.  They turn things on and off.  There are many different versions available, but the good thing for home hackers is that they are based on the 8266 chip, and there are lots of clever people who've done lots of work to make replacing the factory software with your own easy.

I will show you how to flash sonoff and sonoffrf devices as that's what I've got, and I'll be adding firmware that allows the sonoff to respond to MQTT commands.

You will need


  • The Arduino IDE to compile your firmware [Free]
  • A firmware of your choice [Free]
  • A FTDI FT232RL USB to TTL Serial UART Converter Module 5V / 3.3V Arduino, PIC
    [£2-3]
  • Male pin headers 2.54mm [£1]





First a sonoff


The device will have a paper seal that shows tampering, run a 'spudger' or flat blunt knife blade in the groove at the bottom of the housing and unclip the top cover, it should click off easily.

The insides should look like this


The thing that allows us to do our magic is the unpopulated connectors on the board.  You'll need to buy some pin headers and then solder them in place



When you've finished it should look like this


I populated all 5 pins, but we only need the left 4 for our reflashing.


Next you will need your software set up.   I don't plan to reinvent the wheel, so there's a good video by Bruh Home Automation https://www.youtube.com/watch?v=-JxPWA-qxAk so have a look at that - it gives lots of good information, though I would do one or two things differently - it's a good overview with lots of details though, so well worth a watch.

To replace the original firmware in my sonoffs I use the Tasmota firmware which can be obtained from https://github.com/arendst/Sonoff-Tasmota

Download the zip file, extract it and put it in the path of your Arduino IDE software.

This should be something like

path/Arduino/Sonoff-Tasmota-versionnumber/project/

where project is something like sonoff1

You will have to make some changes to the code in order to customise it to your wifi and needs - this is covered in the Bruh HA video above, but a list of changes is as follows.

In your

path/Arduino/libraries/PubSubClient/src/

folder find and edit PubSubClient.h

Comment out the line that specifies the max packet size by putting // at the beginning of the line
Copy that line and paste it afterwards and change the number to 512
It should look like below


//#define MQTT_MAX_PACKET_SIZE 128
#define MQTT_MAX_PACKET_SIZE 512


Save PubSubClient.h



The only other file you need to modify is user_config.h
This can be found in

path/Arduino/Sonoff-Tasmota-versionnumber/project/



Change the following lines


#define PROJECT                "sonoff1"


sonoff1 will be the name of your switch when you use it, so you could call it something more specific like kitchenswitch1 etc

Next update some wifi settings

#define WIFI_IP_ADDRESS        "0.0.0.0"       
#define WIFI_GATEWAY           "192.168.1.1" 
#define WIFI_SUBNETMASK        "255.255.255.0" 
#define WIFI_DNS               "192.168.1.1" 


Change 192.168.1.1 to whatever you use on your lan


Now enter your wifi name and password

#define STA_SSID1              "Wifiname"               // [Ssid1] Wifi SSID
#define STA_PASS1              "secretpassword"     //

You can also add

#define STA_SSID2              "alternativewifiname"      // [Ssid2] Optional alternate AP Wifi SSID
#define STA_PASS2             "alternativepassword"

if you've more than one wifi network avaiable.


I run my own mqtt broker on my RPi

So we need to configure that

#define MQTT_HOST            "192.168.1.10"          // [MqttHost]
#define MQTT_PORT            1883              // [MqttPort] MQTT port (10123 on CloudMQTT)
#define MQTT_USER            "brokeruser"       // [MqttUser] Optional user
#define MQTT_PASS            "brokerpassword"       // [MqttPassword] Optional password


Change the details to those that you use.


Next it's possible to send a command to all sonoff devices eg turn everything off

You may set up all your kitchen devices to respond, or as I'm doing set all sonoffs to respond to this group topic (rather than telling each switch specifying to turn on or off)

#define MQTT_GRPTOPIC          "sonoffs"

and finally, give your device a friendly name

#define FRIENDLY_NAME        "Sonoff1"


There are other things you can configure like security etc but I'm not covering that.

Once you've done all that you're now ready to flash.

Check the settings on your IDE are as follows


  • Tools->Board->Generic ESP8266
  • Flash mode ->DIO  [Check firmware version as later ones say DOUT should be used]
  • Flash frequency -> 40MHz
  • CPU Frequency-> 80MHz
  • Flash Size-> 1M (64k SPIFFS)
  • Debug port-> Disabled
  • Debug level-> None
  • Reset Method->ck
  • Upload Speed-> 115200
  • Port->Where you connected your FTDI board (eg Com5 or /ttyUSB0 etc)



Press the compile button on the IDE, if all goes well it will tell you.  If not, look at the error messages - it will most likely be a typo, or you need to install a library that the code uses, but you don't have.

Tools->Add files->Manage libraries

Will allow you to search for and add any libraries you need.

Assuming you've no errors

Connect your sonoff to your FTDI as below, plug the usb lead into your PC at this stage.  Make sure you FTDI is set to 3.3v



I've had a fair bit of frustration at this point, but the most reliable way to get the firmware uploaded is as follows.


Plug in the usb to your computer, wait until the port for the FDTI device is selectable in the Arduino IDE, then select it.

You will see the FDTI connectors from left to right are

VCC, Tx, Rx, Gnd  the Gnd IS NOT connected at this point.

We need to set the sonoff to accept new firmware, in the case of the basic sonoff what is needed is to
PRESS and HOLD DOWN the on/off switch, while AT THE SAME TIME connecting the Gnd to the 4th pin (the one next to the green wire in the picture above, the 5th pin is not used).

Once you've slide the Gnd wire on the 4th pin you can release the switch.

If when you're doing this you break the connection between the ground wire, and the ground pin then you will have to start again, as the sonoff will not be in a mode to accept new firmware.



When you've done this you can now press the upload button in your IDE.  Your FDTI board led will flash and you should see lots of dots in the IDE indicating progress.

Hopefully this reaches 100% in which case you're done.

Your sonoff should now be able to connect to your MQTT broker and respond to commands to /sonoffs/sonoff1

If you receive errors then try again.  I had most problems when using a 5v logic level 8266 based plug, and think the voltage was boarderline, so moved the USB on the computer to a port directly attached to the board, rather than one on the front plate of my PC, and that allowed successful flashing.

The process for flashing other sonoff devices is similar, though sometimes a bit more awkward.

I've also got sonoffrf devices



Outwardly they look similar, but inside they've a daughter board with the Rf receiver, but the same empty pin holes on the main board.


Solder pin headers as before so you can attach your FTDI board


The process for flashing a sonoff Rf is exactly the same as the basic sonoff, with one difference.

In order to put the sonoff Rf into a mode that allows firmware update we DO NOT press the button down when connecting the ground wire.  Instead it's a bit more fiddly.

We need to look at the underside of the board



And then instead of holding down the switch we need to short these two points when connecting the ground wire.


This is fiddly, some have had success doing this with the leads of an led, I used a pair of metal tweezers. If you have difficulty, or you'll be doing this a lot, it may be worth rigging up an inline switch for the last FDTI wire so you can make the connection more easily when shorting these two points.

The rest of the process is the same as before.

Now that you've one (or more) sonoffs flashed, the next article will be on controlling them with node-red and an MQTT broker.

For subsequent sonoffs, you would only need to change the

#define PROJECT                "sonoff1"

and

#define FRIENDLY_NAME        "Sonoff1"

so that each sonoff had it's own unique identifier.


I should add one more thing, sonoffs don't have an earth wire, but your device may need one.  If that's the case the you should have an earth wire that bypasses the sonoff.  If that's the case then I would suggest that you also place your sonoff in an enclosure - I'd actually always recommend this.  That also has the advantage that you'll be able to add additional cable strain relief to the sonoff wires, no bad thing as you don't want it to be possible to pull a live mains wire free from the end of your device.

See https://www.youtube.com/watch?v=8mz5sCAvDAY for some safety advice




It's possible to apply OTA updates to sonoffs, or have them check an appstore for new firmwares, but that's not covered here.










Sunday, 22 October 2017

My small solar power system

This is the first part of a series of blog posts that will cover how I've set up a small solar electricity system, that generates and stores solar energy, and how I monitor and control it.  

I should be very clear at the outset, I'm no expert, and am not qualified in this field - I've just read a bit and have built my own system.  I take precautions as best I can, but if you're contemplating doing anything like this yourself, make sure you know what you're doing as you can get a shock that could hurt you, or especially if you're using an inverter to generate mains electricity, you could get at shock sufficient to kill you.

I'm doing this small scale - it's a hobby, and I generate some electricity that I use to power all my usb devices (phones, tablets and cameras) all summer.  I also store the electricity and power my living room TV.

It's the sort of system that would be useful in a caravan/camper van or on a boat.

So what have I got ?




2 x 100 Watt Panels2 x 250 Watt Panels

2 x Epsolar Tracer A (30A)



Approx. 200Ah Lead Acid Batteries
(24V configuration)
Approx 3kWh Lipo powerwall
(7S configuration)



A busbar for connecting storage
and chargers
A Big Red Button for killing the Load

















A number of sonoff switchesA raspberry Pi





I used to have more lead acid batteries but I'm slowly transitioning away from them, and my system was originally a 12V one, but now is configured as a 24V system.

I've been building a 3kWh powerwall with lithium polymer batteries - some are reused laptop cells, and others are new ones.


By themselves, the solar charge controllers can do an awful lot, but with the addition of a small computer such as a RPi and sonoffs they become much more flexible.

Elsewhere in this blog I've already covered how to add software to the RPi to obtain, store and display the data from your EPSolar Tracer A charge controllers.


To display what your system is doing and that gives output like this



What I'll be covering in the next series of posts is more to do with what you can measure and control once you've got this data, and a little more hardware, and how you can integrate your solar data with other sensors and actuators.

I'll be using node-red to do this, it's a simple interface for controlling things.  We'll start off by making small flows, but once you get going the complexity of your control can build to meet your needs.  It produces really crisp and clean dashboards, controls and graphs, you can make small ones for phones






Or larger dashboards for PC or tablets



But you can also leave node-red running in the background, and have it monitor, and turn things on and off as necessary without any input from you.



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