Sunny Webbox export data in MySQL using PHP and cron

Neu hier Angemeldet: 27.03.2008 Beiträge: 1

Hi,

First of all sorry for not writing in German, I do understand it but writing it is somewhat diffcult for me Embarassed

Anyway, for all of you who are wrestling with getting the data out of the webbox and into MySQL I created a PHP script.

With this script you can pull the data out of the Sunny Webbox and into the MySQL database. The code is based on that of czan from this topic:
http://www.photovoltaikforum.com/ftopic10880.html

With a cron job on your webserver you can automatically execute the php script.
My cron job looks like this:

Code:

15 * * * * /usr/bin/php -q /srv/www/htdocs/sunny_input.php

My script gets executed every 15 minutes.

The SQL script to create the table in MySQL looks like this:

Code:

CREATE TABLE IF NOT EXISTS `eco_data` (
`id` int(100) NOT NULL auto_increment,
`timestamp` varchar(12) NOT NULL,
`cur_power` varchar(255) NOT NULL,
`cur_energy` varchar(255) NOT NULL,
`total_energy` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;

And last but not least, here is my PHP script Laughing

Code:

$hostname = "mysql.host.com";
$database = "webbox";
$username = "sunny";
$password = "sunny";
$webboxip = "10.10.40.50";

//any data below should not be edited...
//setup link with MySQL server/database
$ECO = mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR);

//JSON Request im PHP Array Format (will be transformed afterwards with the PHP 5.2 json_encode/decode function)
$requestarray = array(
'version'=>"1.0",
'proc'=>"GetPlantOverview",
'id'=>"1",
'format'=>"JSON"
);

//encode array in JSON
$request = json_encode($requestarray);

//do the JSON request
$request = 'RPC='.rawurlencode($request);

$ch=curl_init();
curl_setopt($ch, CURLOPT_URL, "http://".$webboxip."/rpc");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION,1);
curl_setopt($ch, CURLOPT_HEADER , 0);
curl_setopt($ch, CURLOPT_POST, 1 );
curl_setopt($ch, CURLOPT_POSTFIELDS, $request);

//here is the result of the request
$response = curl_exec ($ch);

if (curl_errno($ch)) {
$err=1;
$errtext = curl_error($ch);
}

curl_close($ch);

//decode the answer
$response = json_decode($response);

//put the values into variables
$cur_power = $response->result->overview[0]->value/1000;
$cur_energy = $response->result->overview[1]->value;
$total_energy = $response->result->overview[2]->value;

//create a timestamp
$timestamp = date("YmdHi");

//build a SQL statement for our database
$insertSQL = "INSERT INTO eco_data (timestamp, cur_power, cur_energy, total_energy) VALUES (
$timestamp,
$cur_power,
$cur_energy,
$total_energy)";

//select the database we defined and execute the SQL statement
mysql_select_db($database, $ECO);
$invoer = mysql_query($insertSQL, $ECO) or die(mysql_error());

//optionally show the variables to check the values with a browser
// echo $timestamp."<br>";
// echo $cur_power."<br>";
// echo $cur_energy."<br>";
// echo $total_energy."<br>";
// echo $insertSQL;

Enjoy

arthur

Verfasst am: 31.03.2008, 22:15

Ebenfalls hochschieb-

Gruß
Peter