Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- hardware:channels:solar_inverters:sma [2018/04/01 15:54] – veschoben von /howto/wechselrichter_sma jau
+++ hardware:channels:solar_inverters:sma [2020/09/27 16:20] – 2. versuch Markus mauwjo
@@ Zeile 7: / Zeile 7: @@
 ==== SMAspot ====
-Die Software SMASpot ist inzwischen bei [[https://smaspot.codeplex.com/]] zu finden. Die downloads
+Die Software SMAspot ist bei [[https://smaspot.codeplex.com/]] zu finden.
-befinden sich unter [[https://smaspot.codeplex.com/releases]]
+<note important>SMASpot wurde eingestellt, das Projekt wird unter dem Namen SBFspot weitergeführt. [[https://github.com/SBFspot/SBFspot/]]</note>
 === Kompilieren von SMAspot unter Linux ===
 Die folgenden Punkte wurden unter Ubuntu 12.04 LTS getestet sollten aber auch in anderen Debian-kompatiblen
@@ Zeile 51: / Zeile 51: @@
-Ich besitze den Wechselrichter STP 9000TL-20 [[http://www.sma.de/produkte/solar-wechselrichter-ohne-transformator/sunny-tripower-5000tl-6000tl-7000tl-8000tl-9000tl.html]]. Dieser hat direkt einen Ethernetanschluss, der mit dem Tool smaspot [[https://smaspot.codeplex.com/]] angesprochen werden kann. Das Tool smaspot ist zu kompilieren. Sobald es läuft, findet es den Wechselrichter und gibt ein paar Infos aus:
+Ich besitze den Wechselrichter STP 9000TL-20 [[http://www.sma.de/produkte/solar-wechselrichter-ohne-transformator/sunny-tripower-5000tl-6000tl-7000tl-8000tl-9000tl.html]]. Dieser hat direkt einen Ethernetanschluss, der mit dem Tool SMAspot angesprochen werden kann. Das Tool ist zu kompilieren. Sobald es läuft, findet es den Wechselrichter und gibt ein paar Infos aus:
 SMAspot -sp0 -v
@@ 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>