#!/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";
}