wiki.volkszaehler.org

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Zuletzt angesehen:
hardware:channels:solar_inverters:sma

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
Nächste Überarbeitung		Vorhergehende Überarbeitung
hardware:channels:solar_inverters:sma [2018/08/13 16:10] – URL im Script korrigiert jauhardware:channels:solar_inverters:sma [2020/12/18 13:30] (aktuell) – [SMA Sunny Boy] wasserma
Zeile 167: Zeile 167:
 </code> </code>
  
 +== Modifiziertes Script (2. Versuch von Markus) ==
 +Läuft Fix, und berechnet den Eigenverbrauch sehr gut, insbesondere bei Lücken in der AUfzeichnung.
 +
 +<code bash eigenverbrauch.sh>
 +#!/usr/bin/perl
 +
 +use Time::Piece;
 +use LWP::UserAgent;
 +use DBI;
 +#Gesamtverbrauch einbauen
 +$debug = 0;
 +#$debug = 1;
 +
 +my $dbh = DBI->connect("DBI:mysql:database=volkszaehler;", "vz", "Geheimagent",{AutoCommit => 0} ) or die $DBI::errstr;
 +my $ideigen = "7";
 +#step_clean();
 +#step_get_SMA();
 +step_copy_SMA();
 +step_max_10();
 +step_lin();
 +add_missig_dates(1);
 +step_gesamtverbrauch();
 +
 +sub step_get_SMA { # ad300 -> 300 tage
 +system("/home/markus/hack/sma/SBFspot-3.7.0/SBFspot/mariadb/bin/SBFspot -finq -nocsv -am2 -ad300 -v");
 +}
 +
 +sub step_copy_SMA {
 +my $sth =  $dbh->prepare ("insert into volkszaehler.data (channel_id, timestamp, value) SELECT  4 , TimeStamp * 1000,  TotalYield/1000 FROM SMA.DayData where cast(TimeStamp/60/10 as int) not in (select cast(timestamp/1000/60/10 as int) from volkszaehler.data where channel_id = 4) and Serial = 304951132");
 +my $numrows = $sth->execute();
 +$dbh->commit();
 +
 +print "step_copy_SMA done -  $numrows copy\n";
 +}
 +
 +sub step_clean {
 +my $sth =  $dbh->prepare ("delete FROM volkszaehler.`data` where channel_id in (1029, 1030, 1031, 8, ".$ideigen.")");
 +my $numrows = $sth->execute();
 +$dbh->commit();
 +print "step_clean done -  $numrows delted\n";
 +}
 +
 +sub step_max_10 {xRe9AnfH7b9uNPS8
 +my $inserted = 0;
 +# Neue Idee zuerst die 10 Minuten lücken füllen ...
 +# Danach Linear zur Einspeisung ...
 +my $sth_4_solar = $dbh->prepare ("SELECT id, channel_id, `timestamp`, value FROM volkszaehler.`data` left outer join (select timestamp as ts1029 from volkszaehler.`data` where channel_id = ".$ideigen." ) x on ts1029 = timestamp where channel_id = 4 and ts1029 is null order by timestamp");
 +my $sth_2_einsp = $dbh->prepare ("SELECT id, channel_id, `timestamp`, value FROM volkszaehler.`data` where channel_id = 2 order by timestamp");
 +my $sth =  $dbh->prepare ("INSERT into   volkszaehler.`data` (channel_id, `timestamp`, value)  VALUES (?,?, ?) ");
 +
 +$sth_4_solar->execute ();
 +$sth_2_einsp->execute ();
 +my $timestamp3  = 0;
 +
 +while (my ($id, $channel_id, $timestamp, $value) = $sth_4_solar->fetchrow_array()) {
 +    while ( $timestamp3  < $timestamp and   my ($id2, $channel_id2, $timestamp2, $value2) = $sth_2_einsp->fetchrow_array()){
 +$timestamp3_old  = $timestamp3;
 +$value3_old = $value3;
 +$timestamp3  = $timestamp2;
 +$value3 = $value2/1000;
 +#print  "                  " .$timestamp3 ."\n";
 +}
 +my $time_diff_tot = $timestamp3  -   $timestamp3_old;
 +my $einsp_MS = ($value3  -   $value3_old)/ $time_diff_tot;
 +my $time_diff = $timestamp   -   $timestamp3_old;
 +my $einsp_estim = $value3_old + ( ($time_diff   ) * $einsp_MS );
 +my $value_eigenv = $value - $einsp_estim ;
 +
 +if ($debug > 0) {
 +
 +print "-------------------------";
 +print localtime($timestamp/1000)->strftime('%Y-%m-%d-%H:%M:%S'."\n");
 +print "Zaehlerstand WR: $value \n";
 +print "Einspeisung:\n";
 +print "    " .localtime($timestamp3_old/1000)->strftime('%Y-%m-%d-%H:%M:%S'.": $value3_old \n");
 +print " NEW" .localtime($timestamp     /1000)->strftime('%Y-%m-%d-%H:%M:%S'.": $einsp_estim \n");
 +print "    " .localtime($timestamp3    /1000)->strftime('%Y-%m-%d-%H:%M:%S'.": $value3 \n");
 +print  "Zeitdifferenz    Einspeisung:".  (  $timestamp3  -   $timestamp3_old )  ."         \n";
 +print  "Zaehlerdifferenz Einspeisung:".  (  $value3  -   $value3_old )  ."         \n";
 +print  "einspeisung MS:".    $einsp_MS   ."         \n";
 +print  "eigenverbrauch:". $value_eigenv."         \n";
 +}
 +if ($time_diff_tot< (1000*60*10)){ # nur bis 10 Minuten pausen
 +$inserted += 1;
 +$sth->execute($ideigen, $timestamp, $value_eigenv);
 +$sth->execute(2, $timestamp, $einsp_estim*1000);
 +}
 +}
 +$dbh->commit();
 +print "step_max_10 done $inserted affected\n";
 +}
 +
 +sub step_lin{
 +my $inserted = 0;
 +# Neue Idee zuerst die 10 Minuten lücken füllen ...
 +# Danach Linear zur Einspeisung ...
 +my $sth_4_solar = $dbh->prepare ("SELECT id, channel_id, `timestamp`, value FROM volkszaehler.`data` left outer join (select timestamp as ts1029 from volkszaehler.`data` where channel_id = ".$ideigen." ) x on ts1029 = timestamp where channel_id = 4 and ts1029 is null order by timestamp");
 +my $sth_2_einsp = $dbh->prepare ("select timestamp4, value1029, value4 FROM 
 +(SELECT              `timestamp` as timestamp4, value as value4 FROM volkszaehler.`data` where channel_id = 4 ) as z4 inner join 
 +(SELECT  channel_id, `timestamp` as timestamp1029, value as value1029 FROM volkszaehler.`data` where channel_id = ".$ideigen." ) as z2 on timestamp4 = timestamp1029
 +order by timestamp4");
 +my $sth =  $dbh->prepare ("INSERT into   volkszaehler.`data` (channel_id, `timestamp`, value)  VALUES (?,?, ?) ");
 +
 +$sth_4_solar->execute ();
 + $sth_2_einsp->execute ();
 +my $timestamp3  = 0;
 +while (my ($id, $channel_id, $timestamp, $value) = $sth_4_solar->fetchrow_array()) {
 +#print localtime($timestamp     /1000)->strftime('%Y-%m-%d-%H:%M:%S'."\n");
 +    while ( $timestamp3  < $timestamp and   my ($timestamp2, $value1029, $value4, ) = $sth_2_einsp->fetchrow_array()){
 +$timestamp3_old  = $timestamp3;
 +$value1029_old = $value1029_new;
 +   $value4_old = $value4_new;
 +$timestamp3  = $timestamp2;
 +$value1029_new = $value1029;
 +   $value4_new = $value4;
 +#print  "                  " .$timestamp3 ."\n";
 +}
 +
 +#my $einsp_MS = ($value3  -   $value3_old)/ ( $timestamp3  -   $timestamp3_old);
 +#my $time_diff = $timestamp   -   $timestamp3_old;
 +#my $einsp_estim = $value3_old + ( ($time_diff   ) * $einsp_MS );
 +#my $value_eigenv = $value - $einsp_estim ;
 +my $diffeinspeisung = $value1029_new - $value1029_old;
 +my $differz         = $value4_new - $value4_old;
 +my $differz_daz     = $value - $value4_old;
 +if ($differz > 0){
 +
 +
 +my $anteil  =  $diffeinspeisung /$differz  ;
 +my $diff_ber = $differz_daz *  $anteil;
 +my $value_ber = $diff_ber + $value1029_old;
 +my $value_ber_einsp = $value- $value_ber; 
 +if ($debug > 0) {
 +
 +print "-----------------------------------\n";
 +
 +print "Zeit:\n";
 +print " ALT:" .localtime($timestamp3_old/1000)->strftime('%Y-%m-%d-%H:%M:%S'." \n");
 +print " DAZ:" .localtime($timestamp     /1000)->strftime('%Y-%m-%d-%H:%M:%S'." \n");
 +print " NEU:" .localtime($timestamp3    /1000)->strftime('%Y-%m-%d-%H:%M:%S'." \n");
 +print "\n";
 +print "Zaehlerdifferenz EIGENVERB\n";
 +print " NEU: $value1029_new\n" ;
 +print " ALT: $value1029_old\n" ;
 +print "      --------------\n" ;
 +print "      $diffeinspeisung\n";
 +print "\n";
 +print "Zaehlerdifferenz Erzeugung\n";
 +print " NEU: $value4_new\n" ;
 +print " ALT: $value4_old\n" ;
 +print "      --------------\n" ;
 +print "      $differz\n";
 +print "\n";
 +print "ANTEIL: $anteil\n";
 +print "\n";
 +print "Zaehlerdifferenz Erzeugung2\n";
 +print " DAZ: $value\n" ;
 +print " ALT: $value4_old\n" ;
 +print "      --------------\n" ;
 +print "      $differz_daz \n";
 +print "\n";
 +print "Differenz Berechnet: $diff_ber\n";
 +print "Zähler NEU: $value_ber \n";
 +print "Zähler Einspeisung: $value_ber_einsp \n";
 +print "\n";
 +}
 +$inserted += 1;
 +$sth->execute($ideigen, $timestamp, $value_ber);
 +$sth->execute(2, $timestamp, $value_ber_einsp*1000);
 +
 +}
 +}
 +$dbh->commit();
 +print "step_lin done  $inserted affected\n";
 +}
 +
 +
 +sub step_gesamtverbrauch {
 +#my $sth =  $dbh->prepare ("delete FROM volkszaehler.`data` where channel_id in ( 8)");
 +#my $numrows = $sth->execute();
 +
 +my $inserted = 0;
 +# Neue Idee zuerst die 10 Minuten lücken füllen ...
 +# Danach Linear zur Einspeisung ...
 +my $sth_4_solar = $dbh->prepare ("SELECT id, channel_id, `timestamp`, value FROM volkszaehler.`data` left outer join (select timestamp as ts1029 from volkszaehler.`data` where channel_id = 8 ) x on ts1029 = timestamp where channel_id = 1 and ts1029 is null and timestamp < (SELECT max(`timestamp`) FROM volkszaehler.`data` where channel_id = 7 )  order by timestamp");
 +my $sth_2_einsp = $dbh->prepare ("SELECT id, channel_id, `timestamp`, value FROM volkszaehler.`data` where channel_id = 7 order by timestamp");
 +my $sth =  $dbh->prepare ("INSERT into   volkszaehler.`data` (channel_id, `timestamp`, value)  VALUES (?,?, ?) ");
 +
 +$sth_4_solar->execute ();
 +$sth_2_einsp->execute ();
 +my $timestamp3  = 0;
 +
 +while (my ($id, $channel_id, $timestamp, $value) = $sth_4_solar->fetchrow_array()) {
 +    while ( $timestamp3  < $timestamp and   my ($id2, $channel_id2, $timestamp2, $value2) = $sth_2_einsp->fetchrow_array()){
 +$timestamp3_old  = $timestamp3;
 +$value3_old = $value3;
 +$timestamp3  = $timestamp2;
 +$value3 = $value2;
 +#print  "                  " .$timestamp3 ."\n";
 +}
 +my $time_diff_tot = $timestamp3  -   $timestamp3_old;
 +my $einsp_MS = ($value3  -   $value3_old)/ $time_diff_tot;
 +my $time_diff = $timestamp   -   $timestamp3_old;
 +my $einsp_estim = $value3_old + ( ($time_diff   ) * $einsp_MS );
 +my $value_eigenv = $value/1000 + $einsp_estim ;
 +
 +if ($debug > 0) {
 +
 +print "-------------------------";
 +print localtime($timestamp/1000)->strftime('%Y-%m-%d-%H:%M:%S'."\n");
 +print "Zaehlerstand BEZUG: $value \n";
 +print "Eigenverbrauch:\n";
 +print "    " .localtime($timestamp3_old/1000)->strftime('%Y-%m-%d-%H:%M:%S'.": $value3_old \n");
 +print " NEW" .localtime($timestamp     /1000)->strftime('%Y-%m-%d-%H:%M:%S'.": $einsp_estim \n");
 +print "    " .localtime($timestamp3    /1000)->strftime('%Y-%m-%d-%H:%M:%S'.": $value3 \n");
 +print  "Zeitdifferenz    Einspeisung:".  (  $timestamp3  -   $timestamp3_old )  ."         \n";
 +print  "Zaehlerdifferenz Einspeisung:".  (  $value3  -   $value3_old )  ."         \n";
 +print  "einspeisung MS:".    $einsp_MS   ."         \n";
 +print  "Gesamtverbauch:". $value_eigenv."         \n";
 +}
 +#if ($time_diff_tot< (1000*60*10)){ # nur bis 10 Minuten pausen
 +$inserted += 1;
 +$sth->execute(8, $timestamp, $value_eigenv);
 +#$sth->execute(1031, $timestamp, $einsp_estim*1000);
 +#}
 +}
 +$dbh->commit();
 +print "step_max_10 done $inserted affected\n";
 +}
 +
 +
 +sub add_missig_dates {
 +  my ($id) = @_;
 +$inserted = 0;
 +#my $id = 1;
 +my $timestamp3  = 0;
 +
 +my $sth = $dbh->prepare ("SELECT id, channel_id, `timestamp`, value FROM volkszaehler.`data` where channel_id = $id order by timestamp");
 +$sth->execute ();
 +my $sth2 =  $dbh->prepare ("INSERT into   volkszaehler.`data` (channel_id, `timestamp`, value)  VALUES (?,?, ?) ");
 +
 +my $t = Time::Piece->strptime("2014-06-01", "%Y-%m-%d");
 +print $t->epoch*1000;
 +for (my $i= $t->epoch - 60*60*2-1; $i  <= localtime; $i = $i + (24*60*60)) {
 +#   print "$i\n";
 +my $timestamp = $i*1000;
 +while ( $timestamp3  < $timestamp and  my ($id2, $channel_id2, $timestamp2, $value2) = $sth->fetchrow_array()){
 +$timestamp3_old  = $timestamp3;
 +$value3_old = $value3;
 +$timestamp3  = $timestamp2;
 +$value3 = $value2;
 +#print  "                  " .$timestamp3 ."\n";
 +}
 +my $time_diff_tot = $timestamp3  -   $timestamp3_old;
 +my $einsp_MS  = ($value3  -   $value3_old)/ ( $time_diff_tot);
 +my $time_diff = $timestamp   -   $timestamp3_old;
 +my $value     = ($value3_old + ( ($time_diff   ) * $einsp_MS )) ;
 +
 +if ($time_diff_tot> (1000*60*60*5)){ # nur bis 10 Minuten pausen
 +if ($debug > 0) {
 +print "-------------------------\n$id\n";
 +print localtime($timestamp/1000)->strftime('%Y-%m-%d-%H:%M:%S'."\n");
 +
 +print "    " .localtime($timestamp3_old/1000)->strftime('%Y-%m-%d-%H:%M:%S'.": $value3_old \n");
 +print "NEW " .localtime($timestamp     /1000)->strftime('%Y-%m-%d-%H:%M:%S'.": $value \n");
 +print "    " .localtime($timestamp3    /1000)->strftime('%Y-%m-%d-%H:%M:%S'.": $value3 \n");
 +print  "Zeitdifferenz    Einspeisung:".  (  $timestamp3  -   $timestamp3_old )  ."         \n";
 +}
 +$inserted += 1;
 +$sth2->execute($id, $timestamp, $value);
 +}
 +}
 +$dbh->commit();
 +print "step_max_10 done $inserted affected - $id\n";
 +}
 +
 +
 +</code>
 == Modifiziertes Script (Versuch von Wolfgang) == == Modifiziertes Script (Versuch von Wolfgang) ==
 <code bash eigenverbrauch.sh> <code bash eigenverbrauch.sh>
Zeile 984: Zeile 1262:
  
 </code> </code>
 +
 +===== SMA Sunny Boy =====
 +Den SMA Sunny Boy kann man per URL auslesen, dazu muss man die Default seite von dem Sunny Boy freischalten, so dass man auch online login die Daten sehen kann.
 +Dann kann man per request die JSON daten abfragen.
 +
 +http://sunnyboyurl/dyn/getDashValues.json
 +json response example:
 +<code>
 +...
 +"6100_40263F00": {
 +"1": [
 +{
 +"val": 1488
 +}
 +]
 +},
 +...
 +</code>
 +
 +Hierzu ein python script, dass den aktullen Wert in eine Datei schreibt, die wir dann mit dem vzlogger auslesen können:
 +
 +<code>
 +import requests
 +import time
 +import logging
 +import argparse
 +
 +
 +logging.basicConfig(
 +    level=logging.INFO,
 +    format="%(asctime)s  [%(name)-12.12s] [%(threadName)-12.12s] [%(levelname)-5.5s]  %(message)s",
 +    handlers=[logging.StreamHandler()],
 +)
 +
 +sma_host = : str = None
 +out_put_file  : str = None
 +
 +def pullData():
 +    try:
 +        data = requests.get(
 +            "http://" + sma_host + "/dyn/getDashValues.json", timeout=5
 +        ).json()
 +        value = data["result"]["012F-730C09D7"]["6100_40263F00"]["1"][0]["val"]
 +        logging.debug(data)
 +        if value:
 +            return value
 +    except Exception as e:
 +        logging.error("Unexpected error: {}".format(e))
 +    return -1
 +
 +
 +def main():
 +    while True:
 +        value = pullData()
 +        if value != -1:
 +            if out_put_file:
 +                f = open(out_put_file, "w")
 +                f.write(str(value))
 +                f.close()
 +            else:
 +                print(value)
 +        time.sleep(5)
 +
 +
 +if __name__ == "__main__":
 +    print("SMA sunny boy file writer logger v0.1")
 +    parser = argparse.ArgumentParser()
 +    parser.add_argument("--sma", "-s", type=str, help="sma sunny boy host name")
 +    parser.add_argument("--out", "-o", type=str, help="output file name is not set sysout is used")
 +    args = parser.parse_args()
 +
 +    if not args.sma:
 +        print("missing sma host name parameter")
 +        exit(-1)
 +
 +    #global sma_host
 +    
 +    sma_host = args.sma
 +    print("connect to sma host: " + sma_host)
 +
 +    if args.out:
 +        out_put_file = args.out
 +        print("use output file: " + out_put_file)
 +    else:
 +        print("output file not set use sysout")
 +        
 +
 +    main()
 +
 +</code>
 +
 +
 +vzlogger.conf file meter:
 +
 +<code>
 + {
 +      "enabled": true,
 +      "allowskip": false,
 +      "aggtime": 300,
 +      "channels": [
 +        {
 +          "api": "volkszaehler",
 +          "uuid": "60964f00-9f85-11ea-8200-37539aba2eb7",
 +          "identifier": "",
 +          "middleware": "http://localhost/middleware.php",
 +          "aggmode": "AVG",
 +          "duplicates": 0
 +        }
 +      ],
 +      "protocol": "file",
 +      "path": "/home/pi/sma/sma.log",
 +      "rewind": true
 +    }
 +    </code>
hardware/channels/solar_inverters/sma.1534169434.txt.gz · Zuletzt geändert: 2018/08/13 16:10 von jau