View source for PVOutput with the Raspberry Pi and SolarRiver Inverters

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. [https://www.ebay.com.au/sch/i.html?_from=R40&_trksid=p2380057.m570.l1313&_nkw=serial+to+usb&_sacat=0 ebay].

=== USB to RS232 Serial DB9 ===
As the Raspberry Pi does not have any Serial ports I purchased DB9 to RJ4 connectors as shown [https://au.element14.com/search?st=db9%20to%20rj45%20adapter%20kit here].  You will need a male and female version.<br>

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 [http://klaverstyn.com.au/david/downloads/pvoutput.tar.gz pvoutput.tar.gz] <br>
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 ==
<pre>
apt install libappconfig-perl
</pre>

<pre>
PERL_MM_USE_DEFAULT=1 perl -MCPAN -e 'install Device::SerialPort'
</pre>

== Edit pvoutput ==

<pre> vi /opt/solar/3kw/pvoutput.pl </pre>

On lines 61, 62, and 63 enter the details of your pvoutput account.
<pre>
SERIAL_NUM    => "S22123T486",
API_KEY       => "a84af5efb4ea4daa0dccd4f0a7c27ec31c194a8c",
SYSTEM_ID     => "12345",
</pre>

== 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.

<pre>
#!/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
</pre>

<pre>
/opt/solar-cron-setup.php
</pre>


= Logging to MySQL =
== Create Table ==
<pre>
mysql
create database solar;
</pre>

<pre>
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;
</pre>

== Modify inverter.pl ==
Under the section
<pre>
# Request Inverter Data (regular data poll)
.
.
&writeToFile();
</pre>

add

<pre>
      #
      # 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();
       }
</pre>

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.

<pre>
<?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);
					}
				}
			}
		}
	}
}


?>
</pre>

= My PVOutput Data =
* [http://pvoutput.org/list.jsp?sid=24642 2kW system]
* [http://pvoutput.org/list.jsp?sid=24643 3kW system ]

= Reference =
* http://cqlug.linux.org.au/node/219
* http://lester-burnham.id.au/samil-inverter-pvoutput-org-with-raspberry-pi/


[[ Category : raspbian]]