Tristar logovanie cez PHP

[ixo]

Ahojte, pochválim sa mojou prvou verziu logovania dát z Tristaru cez PHP :

Funkčný panel je tu: http://oddych.sk/e/panel.php

Stručný popis riešenia, pôjdem odzadu:
a) Grafy a ciferníky sú generové cez API Google Charts, volaným z PHP scriptu;
b) Tento PHP načítava dáta z MySQL;
c) Do MySQL sa dáta dostávajú PHP scriptom, ktorý načítava dáta z Tristaru vďaka šikovnosti ľudí na tomto fóre;
d) Uploadovací PHP script je spúšťaný každú minútu volaním z Raspberry, kde je nacronovaný dvoj-trojriadkový Python script, ktorý iba zavolá URL uploadovacieho scriptu (dalo by sa to aj bez Raspberry, ale na verejnom serveri nemám shell).

Ak bude mať niekto záujem, môžem poskytnúť všetky zdrojáky.
Mám ešte v pláne pár vychytávok, a tiež viem o dvoch bugoch s dátumami, ale po pár dňoch snaženia sa už na to dá pozerať.

[ixo]

Takže rovno zdieľam zdrojáky pre T0masa - berte to ako inšpiráciu pre váš ďalší "odpich".

Python script, ktorý je nacronovaný na každú minútu:

Kód: Vybrat vše

#!/usr/bin/python

import urllib
url = "http://oddych.sk/e/tristar_log.php"
response = urllib.urlopen(url).read()
print response

PHP script, ktorý načítava dáta z tristaru a ukladá ich do MySQL, tu som sa inspiroval kódom z tohto fóra (neviem si spomenúť na autora, ale vďaka mu):

Kód: Vybrat vše

<?
include_once('../includes/nastavenia.php');
include_once('../classes/db.php');
$db = new db($_SESSION['db_name'],$_SESSION['db_user'],$_SESSION['db_pw']);

$casovyposun = 9;

$query = "select ip from elektraren;";
$db->query($query);
$ip = $db->rs[0]['ip'];

$ip_tristaru=$ip.":80";


$klice=array(
  "nap_bat"  =>array("38","V"),
	"nap_pole"     =>array("27","V"),
	"prud_pole"      =>array("29","A"),
  "vykon"  =>array("58","W"),
	"tep_bat" =>array("37","C"),
	"tep_tris" =>array("35","C"),
	"kwh_all"       =>array("56","kWh"),
	"stav_nab"   =>array("50",""),
	"cas_abs"   =>array("77","min"),
	"cas_float"     =>array("79","min"),
	"max_vyk_d" =>array("70","W"),
	"wh_d"=>array("68","Wh"),
	);

// funkce
function get_data($ip,$alo) {
	if (($handle = fopen("http://".$ip."/MBCSV.cgi?ID=1&F=4&AHI=0&ALO=".$alo."&RHI=0&RLO=1", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
			$hodnota[1]=$data[3];  
			$hodnota[2]=$data[4];     }
    fclose($handle);
}
Return $hodnota;
}
function get_scale($ip,$alo){
$hi=get_data($ip,$alo);
$lo=get_data($ip,$alo+1);
$hi=$hi[2];
$lo=$lo[2];
$scale_factor=$hi.($lo/65535);
return $scale_factor;
}
function get_scaled_value($raw_data,$jednotka,$vscale,$iscale){
  switch ($jednotka) {
  	case "V":
  	$hodnota=$raw_data[1]*256+$raw_data[2];
  	$vysledek=(($hodnota*$vscale)/32768)/10;
  	break;

  	case "A":
  	$hodnota=$raw_data[1]*256+$raw_data[2];
  	$vysledek=(($hodnota*$iscale)/32768)/10;
  	break;

  	case "W":
  	$hodnota=$raw_data[1]*256+$raw_data[2];
  	$vysledek=(($hodnota*$vscale*$iscale)/131072)/100;
#    if ($vysledek < 0) {$vysledek = 0;}
  	break;

  	case "C":
  	$vysledek=$raw_data[2];
  	break;

  	case "kWh":
  	$vysledek=$raw_data[2];
  	break;

  	case "min":
  	$vysledek=($raw_data[1]*256+$raw_data[2])/60;
  	break;

  	case "Ah":
  	$vysledek=($raw_data[1]*256+$raw_data[2])*0.1;
  	break;

  	case "Wh":
  	$vysledek=($raw_data[1]*256+$raw_data[2]);
  	break;

  	case "LED":
  	$vysledek=$raw_data[2];

  	$led_state = Array(	"LED_START","LED_START2","LED_BRANCH","Rychle blikajici zelena ","pomalu blikajici zelena ","blika 1x za sec zelena ",
  	"Sviti zelena ","UNDEFINED","Sviti zluta ","UNDEFINED","blika cervena ","sviti cervena","R-Y-G ERROR","R/Y-G ERROR","R/G-Y ERROR",
  	"R-Y ERROR (HTD)","R-G ERROR (HVD)","R/Y-G/Y ERROR","G/Y/R ERROR","G/Y/R x 2");
  	$vysledek=$led_state[$vysledek];
  	break;

  	default:
  	$vysledek=$raw_data[2];
  	$charge_state = Array("Start","Night Check","Disconnect","Night","Fault","MPPT","Absorbtion","Float","Equalize","Slave");
  	$vysledek=$charge_state[$vysledek];
  	break;
  }
  if(is_numeric($vysledek)) {
    return round($vysledek,2);}
    else {
    return $vysledek;
  }
}
  
 // samotny program
$vscale=get_scale($ip_tristaru,0);
$iscale=get_scale($ip_tristaru,2);

#echo time()+(60*60*6);
$cas = date('Y-m-d  H:i:s', time()+(60*60*$casovyposun));
#echo $cas;
$query = "insert into tristar_log values ('$cas'";

foreach($klice as $polozka=>$hodnota)
  {
  list($alo,$jednotka)=$hodnota;
  $raw_data=get_data($ip_tristaru,$alo);
#  echo $polozka . ": " . get_scaled_value($raw_data,$jednotka,$vscale,$iscale).$jednotka."<br>";
  $query = $query.", '".get_scaled_value($raw_data,$jednotka,$vscale,$iscale)."'";
  }
  $query = $query.");";

#  print $query;

#print "<OK>";

$db->execute($query);

Kod samotneho panelu:

Kód: Vybrat vše

<html>

<head>
<?
  if (htmlspecialchars($_GET["refresh"]) != 'off') {
    echo "<meta http-equiv='refresh' content='60'>";
  }
?>
</head>

<body>

<table border=0>
  <tr>
    <td valign=top>
      <iframe src=http://oddych.sk/e/tristar_graf.php?pole=vykon frameborder=0 width=900 height=280 scrolling=no sandbox=allow-scripts></iframe>
    </td>
    <td valign=center valign=top>
      <br>
      <iframe src=http://oddych.sk/e/tristar_cifernik.php?pole=vykon frameborder=0 width=320 height=320 scrolling=no sandbox=allow-scripts></iframe>
    </td>
  </tr>
  <tr>
    <td cellspacing=0 cellpadding=0 valign=top>
      <iframe style="padding:0px; margin:0px;" src=http://oddych.sk/e/tristar_graf.php?pole=nap_bat frameborder=0 width=900 height=280 scrolling=no sandbox=allow-scripts></iframe>
    </td>
    <td valign=top align=center>
      <br>
      <iframe src=http://oddych.sk/e/tristar_cifernik.php?pole=nap_bat frameborder=0 width=320 height=320 scrolling=no sandbox=allow-scripts></iframe>
    </td>
  </tr>
</table>

  <div style="font-size:10px; font-family:'Arial'; font-weight:bold;">
    Autorefresh:
    <a href=panel.php>ON</a>
    <img src=grafika/separator.png>
    <a href=panel.php?refresh=off>OFF</a>
 </div>

</body>

</html>

Kod grafov:

Kód: Vybrat vše

<?php
include_once('../includes/nastavenia.php');
include_once('../classes/db.php');
$db = new db($_SESSION['db_name'],$_SESSION['db_user'],$_SESSION['db_pw']);

$casovyposun = 9;

$pole = htmlspecialchars($_GET["pole"]);
$get_cas = htmlspecialchars($_GET["cas"]);
if ($get_cas == 'all') {
#  $cas = "";
$query = "select DATE_FORMAT(cas,'%Y, %m, %d, %H, %i') cas, ".$pole." from tristar_log where nap_bat > 0;";
}
else {
#  $cas = " where cas >(cas-(60*60*(24+".$casovyposun.")))";
#  $cas = " and cas like '".date('Y-m-d', time()+(60*60*$casovyposun))."%'";
$query = "select DATE_FORMAT(cas,'%Y, %m, %d, %H, %i') cas, ".$pole." from tristar_log where nap_bat > 0 and cas like '".date('Y-m-d', time()+(60*60*$casovyposun))."%'";
#  echo date('Y-m-d', time()+(60*60*$casovyposun));
}
#$query = "select DATE_FORMAT(cas,'%H:%i') cas, ".$pole." from tristar_log ".$cas.";";
#$query = "select DATE_FORMAT(cas,'%Y, %m, %d, %H, %i') cas, ".$pole." from tristar_log where nap_bat > 0 ".$cas.";";
#echo $query;
$db->query($query);

?>

<html>
  <head>
   	<meta http-equiv="Content-Type"     content="text/html; charset=utf-8" />
    <meta http-equiv=”refresh” content="5" />
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = google.visualization.arrayToDataTable([
          <?
          if ($pole == "nap_bat") {
            echo "['Čas', 'Batéria V'],";
          }
          else {
            echo "['Čas', 'Panely W'],";
          }

      for ($a=0; $a<count($db->rs); $a++) {
        echo "[new Date(";
        echo $db->rs[$a]['cas'];
#        echo date('d.m.YY H:i', $db->rs[$a]['cas']);
        echo "), ";
        echo $db->rs[$a][$pole];
        echo "],";
      }
?>
        ]);

        var options = {
          curveType: 'none',
          legend: { position: 'none' },
          chartArea:{left:15,top:5, height:'90%', width:'90%'},
          hAxis:{slantedTextAngle:45},
          series:[{targetAxisIndex:1},{targetAxisIndex:0}],
          vAxes:[{}, {}],

        };

        var chart = new google.visualization.LineChart(document.getElementById('chart_div'));

        chart.draw(data, options);
      }
    </script>
  </head>
  <body>
    <div style="font-size:20px; font-family:'Arial'; font-weight:bold;">
        <?
      if ($pole == "nap_bat") {
        echo "Batéria V";
      }
      else {
        echo "Výkon W";
      }
      ?>
    <span style="margin-left:660px; font-size:10px;">
      <a href=http://oddych.sk/e/tristar_graf.php?pole=<?echo $pole;?>>Dnes</a>
      <img src=grafika/separator.png>
      <a href=http://oddych.sk/e/tristar_graf.php?pole=<?echo $pole;?>&cas=all>Všetko</a>
    </span>
    </div>
    <div id="chart_div" style="width: 900px; height: 250px; border-style: none; border-color: #ff0000;"></div>

  </body>
</html>

<?
?>

Kod cifernikov:

Kód: Vybrat vše

<?php
include_once('../includes/nastavenia.php');
include_once('../classes/db.php');
$db = new db($_SESSION['db_name'],$_SESSION['db_user'],$_SESSION['db_pw']);

$casovyposun = 9;

$pole = htmlspecialchars($_GET["pole"]);
#$query = "select DATE_FORMAT(cas,'%H:%i') cas, ".$pole." from tristar_log ".$cas.";";
$query = "select ".$pole.", stav_nab, max_vyk_d from tristar_log where nap_bat > 0 order by cas desc limit 1;";
#echo $query;
$db->query($query);

?>

<html>
  <head>
 	<meta http-equiv="Content-Type"     content="text/html; charset=utf-8" />
  <meta http-equiv=”refresh” content="5" />

  <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["gauge"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {

        var data = google.visualization.arrayToDataTable([
          ['Label', 'Value'],
          <?if ($pole == 'nap_bat') {
              echo "['V',";
            }
            else {
              echo "['W',";
            }
            echo $db->rs[0][$pole];
          ?>],

        ]);
        var options = {
          <?
          if ($pole == nap_bat) {
          echo "
            min: 21, max: 29,
            width: 250, height: 250,
            redFrom: 21, redTo: 22.5,
            yellowFrom:22.5, yellowTo: 23.7,
            greenFrom: 25.2, greenTo: 29,
            minorTicks: 10, majorTicks: ['21', '22', '23','24', '25', '26', '27','28','29']
          ";}
          else {
            echo "
            min: 0, max: 500,
            width: 250, height: 250,
            redFrom: 0, redTo: 0,
            yellowFrom:0, yellowTo: 200,
            greenFrom: 200, greenTo: 500,
            minorTicks: 10, majorTicks: ['0', '100','200', '300', '400', '500']
          ";}
//
          ?>

        };

        var chart = new google.visualization.Gauge(document.getElementById('chart_div'));

        chart.draw(data, options);

      }
    </script>
  </head>
  <body>
    <div id="chart_div" style="width:250px; height: 250px; border-style:none; border-color: #ff0000; margin-left:20px;"></div>
    <div style="margin-left:60px; font-size:15px; font-family:'Arial'; font-weight:bold;">
      <?
        if ($pole == 'nap_bat') {
          echo "Fáza nabíjania: ".$db->rs[0]['stav_nab'];
        }
        else {
          echo "Max výkon dnes: ".$db->rs[0]['max_vyk_d']."W";
        }
      ?>
    </div>

  </body>
</html>

<?
?>

Enjoy!

[Frenky]

Autor puvodniho kodu je prochazka85, taky jeho kod pouzivam v jine sve variante, paradni prace a diky mu.

[Frenky]

A tohle je taky pekny, prostuduji a urcite s toho neco pouziji.

[dedo]

Vidím, že stránka http://oddych.sk/e/panel.php sa stále inovuje k lepšiemu.

[dedo]

Update zdrojákov nebude?

[dedo]

V programe na načítanie dát z Tristaru by som opravil sumár kilowatthodín:

Kód: Vybrat vše

	case "kWh":	
	$vysledek = $raw_data[1]*256 + $raw_data[2];
	break;

Mám pocit, že ten. čo to ladil, nemal na Tristare tých kWh natočených veľa

[ixo]

Update zdrojákov nebude?

Rad ich sem dam, len este dnes som dokoncil graf dobiti, tak este pockaj nejaky den-dva, kym ho uplne vyladim. Neviem, ci ho mam spravit tak, ako je teraz (stlpcovy), alebo ciarovy. Ciarovy ma tu vyhodu, ze dokaze zobrazovat plynulu casovu os s datumami. Pri stlpcovom sa os pri vela polozkach zahlti a je nanic (rovno som ju skryl a detail sa zobrazi onmouseover nad grafom). Pri ciarovom sa pocet popisov dynamicky meni podla poctu poloziek, a to je fajn.

[miroc]

Pekne ixo... nemohli by sa pod Max. vykonom dnes vypisovat aj Wh dnes?

[ixo]

Veru ano, tiez to planujem, zaroven aj ako sumarny graf vlavo, podobne ako historia dobiti.

[ixo]

Je čas na zverejnenie updatu zdrojákov. Nová verzia má tieto fičúry: logovanie nabíjacieho prúdu, zobrazovanie info, kedy naposledy boli batérie v absorbption a kedy vo floate. Nový graf - prehľad dobíjania (7 dní, 30 dní, celá história). V grafe napätia batérií sa zobrazuje nielen reálne, ale aj cieľové napätie. Taktiež Tristar už nemusí byť prístupný z vonkajšej siete, čo výrazne prispieva k bezpečnosti riešenia. Odladil som kopu veci, taktiež niektoré veci prerobil z dôvodu optimalizácie.

Všetko začína python scriptom tristar_log.py, ktorý je nacronovaný raz za minútu na Raspberry Pi. Script iba zavolá PHP script bežiaci na verejnom severi:

Kód: Vybrat vše

#!/usr/bin/python
import urllib
url = "http://192.168.1.104/tristar_log_rpi.php"
response = urllib.urlopen(url).read()
print response

Druhý python script tristar_log_sumar.py je nacronovaný raz za hodinu a na verejnom serveri spustí analýzu logu dobití a výslednú hodnotu updatne v na to určenej tabuľke:

Kód: Vybrat vše

#!/usr/bin/python
import urllib
url = "http://oddych.sk/e/tristar_graf_log_sumar.php"
response = urllib.urlopen(url).read()
print response

Subor tristar_log_rpi.php je volany vyssie spustanym pythons criptom a zapise udaje do MySQL:

Kód: Vybrat vše

<?
$ip = "192.168.1.253";

$ip_tristaru="192.168.1.253:80";


$klice=array(
  "nap_bat"  =>array("38","V"),
	"nap_pole"     =>array("27","V"),
	"prud_pole"      =>array("29","A"),
  "vykon"  =>array("58","W"),
	"tep_bat" =>array("37","C"),
	"tep_tris" =>array("35","C"),
	"kwh_all"       =>array("56","kWh"),
	"stav_nab"   =>array("50",""),
	"cas_abs"   =>array("77","min"),
	"cas_float"     =>array("79","min"),
	"max_vyk_d" =>array("70","W"),
	"wh_d"=>array("68","Wh"),
  "nab_prud"=>array("39","A"),
	"tar_vol"=>array("51","V"),
	);

/*$klice=array(
  "napeti baterie"  =>array("38","V"),
  "cilove napeti"   =>array("51","V"),
	"Nabijeci proud"  =>array("39","A"),
	"Napeti pole"     =>array("27","V"),
	"Proud pole"      =>array("29","A"),
	"Vystupni vykon"  =>array("58","W"),
	"Sweep Vmp"       =>array("61","V"),
	"Sweep Voc"       =>array("62","V"),
	"Sweep Pmax"      =>array("60","W"),
	"Teplota Baterie" =>array("37","C"),		
	"Teplota Tristar" =>array("35","C"),
	"Kilowatty"       =>array("56","kWh"),
	"Stav nabijeni"   =>array("50",""),
	"doba absorbce"   =>array("77","min"),
	"doba equalizace" =>array("78","min"),
	"doba floatu"     =>array("79","min"),
	"Max vykon(dnes)" =>array("70","W"),
	"Amper hodin(dnes)"=>array("67","Ah"),
	"Watt hodin(dnes)"=>array("68","Wh"),
	"Max Napeti pole(dnes)" =>array("66","V"),	
	"Max napeti baterie(dnes)"=>array("65","V"),
	"Min napeti baterie(dnes)"=>array("64","V"),
	"Vstupni vykon"   =>array("59","W"),
	"LED indikator"   =>array("49","LED"),
	"napeti baterie na svorkach"=>array("25","V"),
	"napeti baterie na sonde"=>array("26","V"),
	);
*/
// funkce 
function get_data($ip,$alo) {
	if (($handle = fopen("http://".$ip."/MBCSV.cgi?ID=1&F=4&AHI=0&ALO=".$alo."&RHI=0&RLO=1", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
			$hodnota[1]=$data[3];  
			$hodnota[2]=$data[4];     }
    fclose($handle);
}
Return $hodnota;
}
function get_scale($ip,$alo){
$hi=get_data($ip,$alo);
$lo=get_data($ip,$alo+1);
$hi=$hi[2];
$lo=$lo[2];
$scale_factor=$hi.($lo/65535);
return $scale_factor;
}
function get_scaled_value($raw_data,$jednotka,$vscale,$iscale){
  switch ($jednotka) {
  	case "V":
  	$hodnota=$raw_data[1]*256+$raw_data[2];
  	$vysledek=(($hodnota*$vscale)/32768)/10;
  	break;

  	case "A":
  	$hodnota=$raw_data[1]*256+$raw_data[2];
  	$vysledek=(($hodnota*$iscale)/32768)/10;
  	break;

  	case "W":
  	$hodnota=$raw_data[1]*256+$raw_data[2];
  	$vysledek=(($hodnota*$vscale*$iscale)/131072)/100;
#    if ($vysledek < 0) {$vysledek = 0;}
  	break;

  	case "C":
  	$vysledek=$raw_data[2];
  	break;

  	case "kWh":
  	$vysledek=$raw_data[2];
  	break;

  	case "min":
  	$vysledek=($raw_data[1]*256+$raw_data[2])/60;
  	break;

  	case "Ah":
  	$vysledek=($raw_data[1]*256+$raw_data[2])*0.1;
  	break;

  	case "Wh":
  	$vysledek=($raw_data[1]*256+$raw_data[2]);
  	break;

  	case "LED":
  	$vysledek=$raw_data[2];

  	$led_state = Array(	"LED_START","LED_START2","LED_BRANCH","Rychle blikajici zelena ","pomalu blikajici zelena ","blika 1x za sec zelena ",
  	"Sviti zelena ","UNDEFINED","Sviti zluta ","UNDEFINED","blika cervena ","sviti cervena","R-Y-G ERROR","R/Y-G ERROR","R/G-Y ERROR",
  	"R-Y ERROR (HTD)","R-G ERROR (HVD)","R/Y-G/Y ERROR","G/Y/R ERROR","G/Y/R x 2");
  	$vysledek=$led_state[$vysledek];
  	break;

  	default:
  	$vysledek=$raw_data[2];
  	$charge_state = Array("Start","Night Check","Disconnect","Night","Fault","MPPT","Absorbtion","Float","Equalize","Slave");
  	$vysledek=$charge_state[$vysledek];
  	break;
  }
  if(is_numeric($vysledek)) {
    return round($vysledek,2);}
    else {
    return $vysledek;
  }
}
  
 // samotny program
$vscale=get_scale($ip_tristaru,0);
$iscale=get_scale($ip_tristaru,2);

#echo time()+(60*60*6);
$cas = date('Y-m-d  H:i:s', time()+(60*60*$casovyposun));
#echo $cas;
$query = "insert into tristar_log values ('$cas'";

foreach($klice as $polozka=>$hodnota)
  {
  list($alo,$jednotka)=$hodnota;
  $raw_data=get_data($ip_tristaru,$alo);
#  echo $polozka . ": " . get_scaled_value($raw_data,$jednotka,$vscale,$iscale).$jednotka."<br>";
  $query = $query.", '".get_scaled_value($raw_data,$jednotka,$vscale,$iscale)."'";
  }
  $query = $query.");";

  print $query;

#print "<OK>";

#$db->execute($query);

Subor tristar_graf_log_sumar.php precita logy za aktualny den a updatne sumarny udaj o dobiti pre dany den:

Kód: Vybrat vše

<?
include_once('../includes/nastavenia.php');
include_once('../classes/db.php');
$db = new db($_SESSION['db_name'],$_SESSION['db_user'],$_SESSION['db_pw']);

$casovyposun = 9;

$cas = date('Y-m-d', time()+(60*60*$casovyposun));

#echo $cas;

# Nacitaj vstup

$query = "
  SELECT COUNT(1) AS pocet
  FROM tristar_log_dobitia
  WHERE datum = '$cas';
";
$db->query($query);
$pocet = $db->rs[0]['pocet'];

#echo $pocet;

if ($pocet == 0) {
  $query = "
  insert into tristar_log_dobitia (datum, absorbtion_, float_)
      SELECT
        distinct cas_den,
        (select max(cas_abs) from tristar_log where stav_nab = 'Absorbtion' and cas_den = '$cas' group by cas_den) as faza_absorbtion,
        (select max(cas_float) from tristar_log where stav_nab = 'Float' and cas_den = '$cas' group by cas_den) as faza_float
        from tristar_log tl
        where cas_den = '$cas'
  ;";
}
else {
  $query = "
  update tristar_log_dobitia
    set
      absorbtion_ = (select max(cas_abs) from tristar_log where stav_nab = 'Absorbtion' and cas_den = '$cas' group by cas_den),
      float_ = (select max(cas_float) from tristar_log where stav_nab = 'Float' and cas_den = '$cas' group by cas_den)
    where datum = '$cas'
  ";
}

  print $query;

  $db->execute($query);

print "<OK>";

Este struktura databazy:
Tabulka tristar_log:

Kód: Vybrat vše

-- phpMyAdmin SQL Dump
-- version 2.11.11.3
-- http://www.phpmyadmin.net
--
-- Hostiteľ: 188.121.40.170
-- Vygenerované:: 24.Sep, 2014 - 13:06
-- Verzia serveru: 5.0.96
-- Verzia PHP: 5.1.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Databáza: `oddychDB`
--

-- --------------------------------------------------------

--
-- Štruktúra tabuľky pre tabuľku `tristar_log`
--

CREATE TABLE `tristar_log` (
  `cas` datetime NOT NULL,
  `nap_bat` float NOT NULL,
  `nap_pole` float NOT NULL,
  `prud_pole` float NOT NULL,
  `vykon` int(11) NOT NULL,
  `tep_bat` float NOT NULL,
  `tep_tris` float NOT NULL,
  `kwh_all` float NOT NULL,
  `stav_nab` varchar(20) NOT NULL,
  `cas_abs` float NOT NULL,
  `cas_float` float NOT NULL,
  `max_vyk_d` float NOT NULL,
  `wh_d` float NOT NULL,
  `nab_prud` float NOT NULL,
  `tar_vol` float NOT NULL,
  `ip` varchar(15) NOT NULL,
  `cas_den` date default NULL,
  KEY `cas` (`cas`),
  KEY `cas_abs` (`cas_abs`),
  KEY `cas_float` (`cas_float`),
  KEY `stav_nab` (`stav_nab`),
  KEY `dobitia` (`cas_den`,`stav_nab`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Tabulka tristar_log_dobitia:

-- phpMyAdmin SQL Dump
-- version 2.11.11.3
-- http://www.phpmyadmin.net
--
-- Hostiteľ: 188.121.40.170
-- Vygenerované:: 24.Sep, 2014 - 13:07
-- Verzia serveru: 5.0.96
-- Verzia PHP: 5.1.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Databáza: `oddychDB`
--

-- --------------------------------------------------------

--
-- Štruktúra tabuľky pre tabuľku `tristar_log_dobitia`
--

CREATE TABLE `tristar_log_dobitia` (
`id` int(11) NOT NULL auto_increment,
`datum` date NOT NULL,
`absorbtion_` int(1) NOT NULL,
`float_` int(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `datum` (`datum`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=166 ;

A teraz samotne zobrazovanie grafov:
Subor tristar_graf sluzi na zobrazenie vsetkych timeline grafov:

Kód: Vybrat vše

<?php
include_once('../includes/nastavenia.php');
include_once('../classes/db.php');
$db = new db($_SESSION['db_name'],$_SESSION['db_user'],$_SESSION['db_pw']);

$casovyposun = 9;

$pole = htmlspecialchars($_GET["pole"]);
$get_cas = htmlspecialchars($_GET["cas"]);
if ($get_cas == 'all') {
  $query = "select
     CONCAT(
        DATE_FORMAT((cas), '%Y %m %d %H '),
        CASE
            WHEN MINUTE(cas) BETWEEN  0 AND 14 THEN '00'
            WHEN MINUTE(cas) BETWEEN 15 AND 29 THEN '15'
            WHEN MINUTE(cas) BETWEEN 30 AND 44 THEN '35'
            WHEN MINUTE(cas) BETWEEN 45 AND 59 THEN '45'
        END
        ) AS cas_grouped,
    avg(".$pole.") as $pole,
    avg(tar_vol) tar_vol
    from tristar_log
    where nap_bat > 0
      and (nab_prud < 30 or nab_prud > 150)
    group by cas_grouped
    order by cas_grouped;";
  $query_min_nap = "select nap_bat from tristar_log where nap_bat > 0 order by nap_bat limit 1;";
}
else {
  $query = "select DATE_FORMAT(cas,'%Y %m %d %H %i') cas, ".$pole.", tar_vol from tristar_log where nap_bat > 0 and cas like '".date('Y-m-d', time()+(60*60*$casovyposun))."%' order by cas";
  $query_min_nap = "select nap_bat from tristar_log where nap_bat > 0 and cas like '".date('Y-m-d', time()+(60*60*$casovyposun))."%' order by nap_bat limit 1;";
}
$db->query($query);
$db2 = new db($_SESSION['db_name'],$_SESSION['db_user'],$_SESSION['db_pw']);
$db2->query($query_min_nap);
#echo $query;
#echo $query_min_nap;
$posledne = 0;
$min_vol = floor($db2->rs[0]['nap_bat']);
#echo $min_vol;
?>

<html>
  <head>
   	<meta http-equiv="Content-Type"     content="text/html; charset=utf-8" />
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = google.visualization.arrayToDataTable([
          <?
          if ($pole == "nap_bat") {
            echo "['Čas', 'Batéria V', 'Target'],";
          }
          elseif ($pole == "vykon") {
            echo "['Čas', 'Využitý výkon panelov W'],";
          }
          elseif ($pole == "nab_prud") {
            echo "['Čas', 'Nabíjací prúd A'],";
          }

      for ($a=0; $a<count($db->rs); $a++) {
        # Ak predch. hodnota bola vacsia ako 20, tak nula bude chyba tristaru - ignoruj ju
        if (($db->rs[$a][$pole] == 0 and $posledne < 20) || ($db->rs[$a][$pole]> 0)) {
#echo "tu som";
            if ($db->rs[$a]['cas'] != null) {
              list ($rok, $mesiac, $den, $hodina, $minuta) = preg_split('/[ :-]/',$db->rs[$a]['cas']);
#echo "tu som cas";
            }
            else {
              list ($rok, $mesiac, $den, $hodina, $minuta) = preg_split('/[ :-]/',$db->rs[$a]['cas_grouped']);
#echo "tu som cas_grouped";
            }
            $mesiac = $mesiac-1;
            $datumcas = $rok.", ".$mesiac.", ".$den.", ".$hodina.", ".$minuta;
#echo $datumcas;

//          if ($posledne < 20) {
            echo "[new Date(";
            echo $datumcas;
    #        echo date('d.m.YY H:i', $db->rs[$a]['cas']);
            echo "), ";
            $hodnota = $db->rs[$a][$pole];
            if ($pole == 'nab_prud') {
              if ($hodnota > 30) {
                $hodnota = 0;
              }
            }
            echo $hodnota;
            if ($pole == "nap_bat") {
              if ($db->rs[$a]['tar_vol'] == 0) {
                $target_voltage = $min_vol+0.05;
              }
              else {
                $target_voltage = $db->rs[$a]['tar_vol'];
              }
              echo ", ";
              echo $target_voltage;
            }
            echo "],";
            $posledne = $db->rs[$a][$pole];
//          }
        }
      }
?>
        ]);

        var options = {
          curveType: 'none',
          legend: { position: 'none' },
          chartArea:{left:15,top:5, height:'87%', width:'90%'},
          hAxis:{slantedTextAngle:45},
          <?
          if ($pole == 'nap_bat') {
            echo "vAxes:[
              {viewWindow:{min:".$min_vol.", max:29.5}},
              {viewWindow:{min:".$min_vol.", max:29.5}}
              ],";
          }
          else {
            echo "vAxes:[{}, {}],";
          }
          ?>
          series:[
            {targetAxisIndex:1,},
            {targetAxisIndex:0, color:'lightblue', lineWidth:2, lineDashStyle: [4, 1]}
          ],


        };

        var chart = new google.visualization.LineChart(document.getElementById('chart_div'));

        chart.draw(data, options);
      }
    </script>
  </head>
  <body>
    <div style="font-size:15px; font-family:'Arial'; font-weight:bold;">
        <?
      if ($pole == "nap_bat") {
        echo "Batéria V";
      }
      elseif ($pole == 'vykon') {
        echo "Využitie panelov W";
      }
      elseif ($pole == 'nab_prud') {
        echo "Nabíjací prúd A";
      }
      ?>
    <span style="margin-left:
    <?
      if ($pole == 'nap_bat') {
        echo "380";
      }
      else {
        echo "700";
      }
    ?>px; font-size:10px;">
      <a href=http://oddych.sk/e/tristar_graf.php?pole=<?echo $pole;?>>Dnes</a>
      <img src=grafika/separator.png>
      <a href=http://oddych.sk/e/tristar_graf.php?pole=<?echo $pole;?>&cas=all>Všetko</a>
    </span>
    </div>
    <div id="chart_div" style="width:
    <?
    if ($pole == "nap_bat") {
      echo "560";
    }
    else {
      echo "960";
    }
    ?>px; height: 220px; border-style: none; border-color: #ff0000;"></div>

  </body>
</html>

<?
?>

Tristar_graf_sumar.php zobrazuje (zatial iba) sumarny graf dobití batérií:

Kód: Vybrat vše

<?php
include_once('../includes/nastavenia.php');
include_once('../classes/db.php');
$db = new db($_SESSION['db_name'],$_SESSION['db_user'],$_SESSION['db_pw']);

$casovyposun = 9;

$co = htmlspecialchars($_GET["co"]);
$get_cas = htmlspecialchars($_GET["cas"]);
if ($get_cas == 'all') {
  $query = "
SELECT
  datum,
  absorbtion_ as faza_absorbtion,
  float_ as faza_float
  from tristar_log_dobitia tld
  where
  id = (select max(id) from tristar_log_dobitia tld2 where tld2.datum=tld.datum)
  order by datum
  ;";
}
else {
  $query = "
SELECT
  datum,
  absorbtion_ as faza_absorbtion,
  float_ as faza_float
  from tristar_log_dobitia tld
  where
--    id = (select max(id) from tristar_log_dobitia tld2 where tld2.datum=tld.datum)
--    and
    datum >= DATE_SUB(NOW(), INTERVAL $get_cas day)
  order by datum
  ;";
}
#echo $query;
#die();
$db->query($query);

$posledne = 0;
?>

<html>
  <head>
   	<meta http-equiv="Content-Type"     content="text/html; charset=utf-8" />
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = google.visualization.arrayToDataTable([
          <?
          if ($co == "dobitia") {
            echo "['Čas', 'Absorbption', 'Float'],";
          }

      for ($a=0; $a<count($db->rs); $a++) {
              list ($rok, $mesiac, $den, $hodina, $minuta) = preg_split('/[ :-]/',$db->rs[$a]['datum']);
#            $mesiac = $mesiac-1;
#            $datumcas = $rok.", ".$mesiac.", ".$den.", 00, 00";
            $datumcas = $den.".".$mesiac.".".$rok;
            echo "['";
            echo $datumcas;
            echo "', ";
            echo $db->rs[$a]['faza_absorbtion'];
            echo ", ";
            echo $db->rs[$a]['faza_float'];
            echo "],";
//          }

      }
?>
        ]);

        var options = {
          legend: { position: 'none' },
          chartArea:{left:25,top:0, height:'98%', width:'100%'},
          isStacked: true,
          hAxis:{slantedTextAngle:0},
          colors: ['blue','green']
        };

        var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));

        chart.draw(data, options);
      }
    </script>
  </head>
  <body>
    <div style="font-size:15px; font-family:'Arial'; font-weight:bold;">
        <?
      if ($co == "dobitia") {
        echo "História dobití";
      }
      ?>

    <span style="margin-left:107px; font-size:10px;">
      <a href=http://oddych.sk/e/tristar_graf_sumar.php?co=<?echo $co;?>&cas=7>7 dní</a>
      <img src=grafika/separator.png>
      <a href=http://oddych.sk/e/tristar_graf_sumar.php?co=<?echo $co;?>&cas=30>30 dní</a>
      <img src=grafika/separator.png>
      <a href=http://oddych.sk/e/tristar_graf_sumar.php?co=<?echo $co;?>&cas=all>Všetko</a>
    </span>

    </div>
    <div id="chart_div" style="width:340px; height: 200px; border-style: none; border-color: #ff0000;"></div>

  </body>
</html>

<?
?>

Súbor tristar_cifernik.php zobrazuje vsetky ciferniky:

Kód: Vybrat vše

<?php
include_once('../includes/nastavenia.php');
include_once('../classes/db.php');
$db = new db($_SESSION['db_name'],$_SESSION['db_user'],$_SESSION['db_pw']);

$casovyposun = 9;

$pole = htmlspecialchars($_GET["pole"]);
#$query = "select DATE_FORMAT(cas,'%H:%i') cas, ".$pole." from tristar_log ".$cas.";";
$query = "select
  ".$pole.",
    stav_nab,
    round(max_vyk_d) max_vyk_d,
    datediff(date(addtime(now(),'0".($casovyposun).":00')), date((select cas from tristar_log where stav_nab = 'Absorbtion' order by cas desc limit 1 ))) nab_ciast,
    datediff(date(addtime(now(),'0".($casovyposun).":00')), date((select cas from tristar_log where stav_nab = 'Float' order by cas desc limit 1 ))) nab_plne
    from tristar_log where nap_bat > 0 order by cas desc limit 1;";
#echo "query: ".$query;
$db->query($query);

?>

<html>
  <head>
 	<meta http-equiv="Content-Type"     content="text/html; charset=utf-8" />
  <meta http-equiv=”refresh” content="5" />

  <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["gauge"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {

        var data = google.visualization.arrayToDataTable([
          ['Label', 'Value'],
          <?
          $hodnota = $db->rs[0][$pole];
          if ($pole == 'nap_bat') {
              echo "['V',";
            }
            elseif ($pole == 'vykon') {
              echo "['W',";
            }
            elseif ($pole == 'nab_prud') {
              if ($db->rs[0][$pole] > 50) {
                $hodnota = 0;
              }
              echo "['A',";
            }
            echo $hodnota;
          ?>],

        ]);
        var options = {
          <?
          if ($pole == 'nap_bat') {
          echo "
            min: 21, max: 30,
            width: 250, height: 187,
            redFrom: 21, redTo: 22.5,
            yellowFrom:22.5, yellowTo: 23.7,
            greenFrom: 25.2, greenTo: 30,
            minorTicks: 10, majorTicks: ['21', '22', '23','24', '25', '26', '27','28','29', '30']
          ";}
          elseif ($pole == 'vykon'){
            echo "
            min: 0, max: 500,
            width: 250, height: 187,
            redFrom: 0, redTo: 0,
            yellowFrom:0, yellowTo: 200,
            greenFrom: 200, greenTo: 500,
            minorTicks: 10, majorTicks: ['0', '100','200', '300', '400', '500']
          ";}
          elseif ($pole == 'nab_prud') {
            echo "
            min: 0, max: 16,
            width: 250, height: 187,
            redFrom: 0, redTo: 0,
            yellowFrom:15, yellowTo: 16,
            greenFrom: 0, greenTo: 15,
            minorTicks: 10, majorTicks: ['0', '2', '4', '6', '8', '10', '12', '14', '16',]
          ";}
//
          ?>

        };

        var chart = new google.visualization.Gauge(document.getElementById('chart_div'));

        chart.draw(data, options);

      }
    </script>
  </head>
  <body>
    <div id="chart_div" style="width:187px; height: 175px; border-style:none; border-color: #ff0000; margin-left:22px;text-align:center;"></div>
    <div style="margin-left:0px; font-size:13px; font-family:'Arial'; font-weight:bold; border-style:none; border-color: #000000;text-align:center;">
      <?
        function PocetDni ($pocet) {
          if ($pocet == 0) {
            return "<span style='color:green;'> Dnes</span>";
          }
          elseif ($pocet == 1) {
            return " Včera";
          }
          elseif ($pocet >= 2 and $pocet <= 4) {
            return " <span style='color:red;'>$pocet dni dozadu</span>";
          }
          else {
            return " <span style='color:red;'>$pocet dní dozadu</span>";
          }
        }
        if ($pole == 'nap_bat') {
          echo "Fáza nabíjania: ".$db->rs[0]['stav_nab'];
#          echo "<br />Plne dobité: ".$db->rs[0]['nab_plne']." dní dozadu";
          echo "<br />Plne dobité: ".PocetDni($db->rs[0]['nab_plne']);
#          echo "<br />Čiastočne dobité: ".$db->rs[0]['nab_ciast']." dní dozadu";
          echo "<br />Čiastočne dobité: ".PocetDni($db->rs[0]['nab_ciast']);
        }
        elseif (($pole == 'vykon')) {
          echo "Max výkon dnes: ".$db->rs[0]['max_vyk_d']." W";
        }
      ?>
    </div>

  </body>
</html>

<?
?>

Súbor panel.php slúži ako hlavná stránka pre zobrazovanie všetkých grafov cez vložené iframes:

Kód: Vybrat vše

<html>

<head>
<title>Solárna elektráreň 460wp</title>
<meta http-equiv="Content-Type"     content="text/html; charset=utf-8" />
<?
  $refresh = htmlspecialchars($_GET["refresh"]);
  if ($refresh == '') {
    echo "<meta http-equiv='refresh' content='120'>";
  }
  elseif ($refresh > 0) {
    echo "<meta http-equiv='refresh' content=".$refresh.">";
  }
?>
</head>

<body>

<table border=0 style="margin-left:0px;">
  <tr>
    <td valign=top colspan=2>
      <iframe src=http://oddych.sk/e/tristar_graf.php?pole=vykon frameborder=0 width=950 height=250 scrolling=no sandbox=allow-scripts></iframe>
    </td>
    <td valign=center valign=top>
      <iframe src=http://oddych.sk/e/tristar_cifernik.php?pole=vykon frameborder=0 width=250 height=250 scrolling=no sandbox=allow-scripts></iframe>
    </td>
  </tr>
  <tr>
    <td>
      <iframe style="padding:0px; margin:0px;" src=http://oddych.sk/e/tristar_graf_sumar.php?co=dobitia&cas=7 frameborder=0 width=340 height=250 scrolling=no sandbox=allow-scripts></iframe>
    </td>
    <td cellspacing=0 cellpadding=0 valign=top>
      <iframe style="padding:0px; margin:0px;" src=http://oddych.sk/e/tristar_graf.php?pole=nap_bat frameborder=0 width=570 height=250 scrolling=no sandbox=allow-scripts></iframe>
    </td>
    <td valign=top align=center>
      <iframe src=http://oddych.sk/e/tristar_cifernik.php?pole=nap_bat frameborder=0 width=250 height=250 scrolling=no sandbox=allow-scripts></iframe>
    </td>
  </tr>
  <tr>
    <td cellspacing=0 cellpadding=0 valign=top colspan=2>
      <iframe style="padding:0px; margin:0px;" src=http://oddych.sk/e/tristar_graf.php?pole=nab_prud frameborder=0 width=950 height=250 scrolling=no sandbox=allow-scripts></iframe>
    </td>
    <td valign=top align=center>
      <iframe src=http://oddych.sk/e/tristar_cifernik.php?pole=nab_prud frameborder=0 width=250 height=250 scrolling=no sandbox=allow-scripts></iframe>
    </td>
  </tr>
</table>

  <div style="font-size:10px; font-family:'Arial'; font-weight:bold;">
    Autorefresh min.:
    <a href=panel.php?refresh=60>1</a>
    <img src=grafika/separator.png>
    <a href=panel.php?refresh=120>2</a>
    <img src=grafika/separator.png>
    <a href=panel.php?refresh=300>5</a>
    <img src=grafika/separator.png>
    <a href=panel.php?refresh=600>10</a>
    <img src=grafika/separator.png>
    <a href=panel.php?refresh=off>OFF</a>
 </div>

</body>

</html>

Enjoy!

[ixo]

Druhý python script tristar_log_sumar.py je nacronovaný raz za hodinu a na verejnom serveri spustí analýzu logu dobití a výslednú hodnotu updatne v na to určenej tabuľke:

Ešte opravím pár nepresností v popise - python scrip tristar_log_sumar.py vola PHP script tristar_log_rpi.php, ktorý beží tiež na Raspberry Pi. Tento script načíta údaje z Tristaru cez jeho vnútornú IP a odošle ich ako parameter verejnému scriptu tristar_log_02.php, ktorý dáta prijme a uloží do MySQL. Tu je jeho zdroják:

<?
include_once('../includes/nastavenia.php');
include_once('../classes/db.php');
$db = new db($_SESSION['db_name'],$_SESSION['db_user'],$_SESSION['db_pw']);

$casovyposun = 9;

$query = "select ip from elektraren;";
$db->query($query);
$ip = $db->rs[0]['ip'];

$cas = date('Y-m-d H:i:s', time()+(60*60*$casovyposun));

# Nacitaj vstup


$query = "insert into tristar_log values ("
."'".$cas."',"
."'".$_GET['nap_bat']."',"
."'".$_GET['nap_pole']."',"
."'".$_GET['prud_pole']."',"
."'".$_GET['vykon']."',"
."'".$_GET['tep_bat']."',"
."'".$_GET['tep_tris']."',"
."'".$_GET['kwh_all']."',"
."'".$_GET['stav_nab']."',"
."'".$_GET['cas_abs']."',"
."'".$_GET['cas_float']."',"
."'".$_GET['max_vyk_d']."',"
."'".$_GET['wh_d']."',"
."'".$_GET['nab_prud']."',"
."'".$_GET['tar_vol']."',"
."'".$_SERVER['REMOTE_ADDR']."',"
."'".$cas."'"
.");";

/*
foreach($klice as $polozka=>$hodnota) {
list($alo,$jednotka)=$hodnota;
$raw_data=get_data($ip_tristaru,$alo);
# echo $polozka . ": " . get_scaled_value($raw_data,$jednotka,$vscale,$iscale).$jednotka."<br>";
$query = $query.", '".get_scaled_value($raw_data,$jednotka,$vscale,$iscale)."'";
}
$query = $query.");";
*/



print $query;

$db->execute($query);

print "<OK>";

[ixo]

Medzicasom som si vsimol v popise vyssie par nepresnosti. Kto by mal zaujem kody vyuzit, nech sa mi teda radsej ozve a poopravil by som mu to potom.

[ixo]

Ahojte, počas dvoch dní som zamakal a výsledkom je logovanie odberu z elektrárne: http://oddych.sk/e/panel.php
Aktuálne tam vidno v odbere len testovacie dáta, keďže odber je odpojený a všetko svieti len do .
Spravené je to takto: Raspberry odchytáva impulzy z elektromera. Pri každom impulze zavolá webovú službu, ktorá impulz zapíše do databázy. A zvyšok - zobrazovanie v grafoch - už poznáte. Bolo to na hodiny skúmania a práce. Bugy sú vychytané, ale po prvých hodinách prevádzky je ešte skoro na vyhlásenie final verzie a môže čo-to ešte vyskočiť. Ale kto by chcel zdrojáky alebo knkow-how, nech dá vedieť.

[ixo]

Riešim tak zľahka absurdný "problém" - navonok to vyzerá tak, že keď napr. sťahujem z netu nejaký film, hriankovač a všetky väčšie spotrebiče majú iba polovičnú spotrebu. Dôvodom v skutočnosti je, že pri preťaženom DSL pripojení timeoutujú niektoré requesty na webovú službu a nezalogujú sa mi tak všetky impulzy z elektroemera. (Poriešim to cez exception na timeout a napočítavaním impulzov v cykle do premennej, až kým timeouty neprejdú a request na službu znova neprejde.)

[Frenky]

Dobře vědět, pro větší úsporu energie stačí sledovat filmy přes net a hned je menší spotřeba