PVOutput with the Raspberry Pi and SolarRiver Inverters

From KlavoWiki
Jump to navigationJump to search

This was a lot simpler to what I was expecting. many thanks for all those that have posted their steps for pvoutput and the Raspberry Pi.

raspbian

Download, write image to SD card and boot.

Hardware

USB to Serial

You can use a USB to Serial adapter which I have working with Raspbian. Just plug and play. ebay.

USB to RS232 Serial DB9

As the Raspberry Pi does not have any Serial ports I purchased DB9 to RJ4 connectors as shown here. You will need a male and female version.

Wiring.

  • 2-2
  • 3-3
  • 5-5

The other pins are not required but can be wired if you want.

Install PVOutput

Download and extract pvoutput.tar.gz
I have modified the pvoutput perl file to also include the inverters voltage which was previously not logging.

This will extract files to:

  • ./opt
  • ./opt/solar

I have 2 inverts so under /opt/solar I have 2 folders called 2kw and 3kw separating the 2 inverters.

Install Prerequisite

apt install libappconfig-perl
PERL_MM_USE_DEFAULT=1 perl -MCPAN -e 'install Device::SerialPort'

Edit pvoutput

 vi /opt/solar/3kw/pvoutput.pl 

On lines 61, 62, and 63 enter the details of your pvoutput account.

SERIAL_NUM    => "S22123T486",
API_KEY       => "a84af5efb4ea4daa0dccd4f0a7c27ec31c194a8c",
SYSTEM_ID     => "12345",

cron

I have a bash file that creates cron entries so everything runs. Based on South East Queensland time the scripts will create 2 cron entries for sunrise to start the monitoring of the inverters. Once the inverters power off of an afternoon the inverter.pl scripts will try to contact the inverters for 5 attempts with a minute retry before terminating.

Run the following command to setup the cron entires.

#!/bin/bash


sunrise()  {

  # First obtain a location code from: https://weather.codes/search/

  location="ASXX6905"                   # Bollon, QLD
  tmpfile=/tmp/$location.out

  # Obtain sunrise and sunset raw data from weather.com
  /usr/bin/wget -q "https://weather.com/weather/today/l/$location" -O "$tmpfile"

  SUNR=$(/usr/bin/grep SunriseSunset "$tmpfile" | grep -oE '((1[0-2]|0?[1-9]):([0-5][0-9]) ?([AaPp][Mm]))' | /usr/bin/head -1)
  SUNS=$(/usr/bin/grep SunriseSunset "$tmpfile" | grep -oE '((1[0-2]|0?[1-9]):([0-5][0-9]) ?([AaPp][Mm]))' | /usr/bin/tail -1)


  sunrise=$(/usr/bin/date --date="$SUNR" +%R)
  sunset=$(/usr/bin/date --date="$SUNS" +%R)


  sunriseh=$(cut -d: -f1 <<< "$sunrise")
  sunrisem=$(cut -d: -f2 <<< "$sunrise")

}



update()  {

filename='/var/spool/cron/crontabs/root'

echo -e '15 3 * * * /bin/bash /opt/solar-cron-setup.sh' > $filename
echo -e $sunrisem $sunriseh ' * * * /bin/bash /opt/grid-tie-inverter.sh >/dev/null 2>&1' >> $filename
echo -e '' >> $filename

}



#Begin

/usr/bin/systemctl stop cron

sunrise
update

/usr/bin/systemctl start cron

#End
/opt/solar-cron-setup.php


Logging to MySQL

Create Table

mysql
create database solar;
CREATE TABLE 2kw (
   DATE DATETIME NOT NULL default '0000-00-00 00:00:00',
   TEMP FLOAT(3,1), 
    VPV FLOAT(4,1),
    IAC FLOAT(3,1),
    VAC FLOAT(4,1),
    FAC FLOAT(4,2),
    PAC INT(4) NOT NULL default '0',
 ETOTAL FLOAT(10,1),
 HTOTAL INT,
   MODE INT(3),
 ETODAY FLOAT(4,2) NOT NULL default '0.00',
PRIMARY KEY (DATE),
    KEY PAC (PAC)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Modify inverter.pl

Under the section

# Request Inverter Data (regular data poll)
.
.
&writeToFile();

add

      #
      # Write data to MySQL
      #


         use POSIX qw(strftime);
         my $date = strftime "%Y-%m-%d %H:%M:%S", localtime;

         my $pac = $HoH{PAC}{VALUE};

         if ($pac gt 0) {

         print "Logging to: MySQL\n";

         my $etotal = ($HoH{ETOTALL}{VALUE} + $HoH{ETOTALH}{VALUE});
         my $htotal = ($HoH{HTOTALL}{VALUE} + $HoH{HTOTALH}{VALUE});

         my $temp = $HoH{TEMP}{VALUE};
         my $vpv = $HoH{VPV1}{VALUE};
         my $iac = $HoH{IAC}{VALUE};
         my $vac = $HoH{VAC}{VALUE};
         my $fac = $HoH{FAC}{VALUE};
         my $mode = $HoH{MODE}{VALUE};
         my $etoday = $HoH{ETODAY}{VALUE};


         use DBI;
         my $dsn = "DBI:mysql:solar";
         my $username = "root";
         my $password = "";
         my $table = "2kw";

         my $dbh  = DBI->connect($dsn,$username,$password);
         $dbh->do("INSERT INTO $table VALUES ('$date', $temp, $vpv, $iac, $vac, $fac, $pac, $etotal, $htotal, $mode, $etoday)");

         $dbh->disconnect();
       }

Once I get some web pages created to view the local data from MySQL I will attach here.

Export CSV to MySQL

To send all your existing CSV data to a MySQL database copy all your CSV files to a single folder and create the following file making sure to edit the top sections for database details.

<?php
ini_set('max_execution_time', 0);
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "solar";
$table = "2kw";
$path = "/tmp/solar/logs";
$files_to_scan = glob("$path/*csv");

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

function csv_to_array($filename='', $delimiter=',')
{
    if(!file_exists($filename) || !is_readable($filename))
        return FALSE;

    $header = NULL;
    $data = array();
    if (($handle = fopen($filename, 'r')) !== FALSE)
    {
        while (($row = fgetcsv($handle, 1000, $delimiter)) !== FALSE)
        {
            if(!$header)
                $header = $row;
            else
                $data[] = array_combine($header, $row);
        }
        fclose($handle);
    }
    return $data;
}

if(isset($files_to_scan) && !empty($files_to_scan)){
	foreach ($files_to_scan as $file_key => $file_val) {
		$res = csv_to_array($file_val);

		if(isset($res) && !empty($res)){
			foreach($res as $key => $res_val){
				if(isset($res_val['PAC']) && !empty($res_val['PAC'])){
					$insert = array();
					$TEMP = mysqli_real_escape_string($conn,$res_val['TEMP']);
					$VPV1 = mysqli_real_escape_string($conn,$res_val['VPV1']);
					$IAC = mysqli_real_escape_string($conn,$res_val['IAC']);
					$VAC = mysqli_real_escape_string($conn,$res_val['VAC']);
					$FAC = mysqli_real_escape_string($conn,$res_val['FAC']);
					$PAC = mysqli_real_escape_string($conn,$res_val['PAC']);
					$ETOTAL = mysqli_real_escape_string($conn,$res_val['ETOTAL']);
					$HTOTAL = mysqli_real_escape_string($conn,$res_val['HTOTAL']);
					$MODE = mysqli_real_escape_string($conn,$res_val['MODE']);
					$ETODAY = mysqli_real_escape_string($conn,$res_val['ETODAY']);
					$date = date('Y-m-d H:i:s',($res_val['TIMESTAMP']));
					
					$sql = "INSERT INTO $table (DATE,TEMP,VPV,IAC,VAC,FAC,PAC,ETOTAL,HTOTAL,MODE,ETODAY) 
					VALUES ('$date','$TEMP','$VPV1','$IAC','$VAC','$FAC','$PAC','$ETOTAL','$HTOTAL','$MODE','$ETODAY')";

					if (mysqli_query($conn, $sql)) {
						// echo $key." " . $sql ."<br>";
					} else {
						echo "Error: " . $sql . "<br>" . mysqli_error($conn);
					}
				}
			}
		}
	}
}


?>

My PVOutput Data

Reference