Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- hardware:channels:solar_inverters:sma [2018/06/18 10:43] – Hinweis auf SBFspot jau
+++ hardware:channels:solar_inverters:sma [2020/09/27 16:20] – 2. versuch Markus mauwjo
@@ Zeile 95: / Zeile 95: @@
-    my $server_endpoint = "http://localhost/volkszaehler.org/htdocs/middleware.php/data/${uuid}.json?value=" . $val;
+    my $server_endpoint = "http://localhost/volkszaehler.org/middleware.php/data/${uuid}.json?value=" . $val;
-#    get("http://localhost/volkszaehler.org/htdocs/middleware.php/data/2d130060-dd27-11e3-b78b-738251a19608.json?value=" . $1)."\n";
+#    get("http://localhost/volkszaehler.org/middleware.php/data/2d130060-dd27-11e3-b78b-738251a19608.json?value=" . $1)."\n";
     #print "serverget = " .  $server_endpoint . "\n";
@@ Zeile 167: / Zeile 167: @@
 </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) ==
 <code bash eigenverbrauch.sh>