#!/usr/bin/perl
#

# This populates the new PerMinute column in the Rain table starting with DB 
# v11.5 and will set the IncrementalRain column if necessary using data
# stored in the database.
#
# Run this after to have upgraded the DB to v11.5 and are using vproweather 
# v1.0.8 or later
#
# You can run this at anytime thereafter it will only update the IncrementalRain 
# column and PerMinute column if either are found to differ.
#
# If you have data from mutltiple Vantage Pro Consoles stored in the DB you
# will need to run it for each console ID. This updates Console ID 0 by
# default:
#	./RemediateRainTable 1
#	./RemediateRainTable 2
#
# This may take a bit of time to run if you have years of data in the DB. You 
# can pipe the STDERR output to a log which will show everthing it updates
#
# You may need to set your $password below
# 
# Use as is, user assumes all risk, no warranty is expressed or implied. This
# has been rigorously tested but hey, this is Open Source, use at your own risk.
#
# Copyright 2012 Curtis J Blank a.k.a. curtronics dot com
# Subject to GPL License

use strict;
use DBI;
use Time::Local;

        my $Version = "1.0.0";

my $user = "root";
my $password = "ReplaceWithMyPW";	# may not be needed if ~.my.cnf contains a [vproweather] section
my $DBhost = "localhost";
my $DBname = "Weather";
my $vpID = 0;   # Vantage Pro Console ID 0 (zero is default)

if ($ARGV[0] ne "") { $vpID = $ARGV[0]; }

# try connecting using ~.my.cnf
my $dbh = DBI->connect("DBI:mysql:$DBname;mysql_read_default_file=~$user/.my.cnf;mysql_read_default_group=vproweather","$user");
# if not try using username and password
if (!$dbh) { $dbh=DBI->connect("DBI:mysql:host=$DBhost;database=$DBname","$user","$password"); }
# if not can't do nothin'
if (!$dbh) { die("ERROR! Connecting to $DBhost $DBname.\n"); }

my $Query = "SELECT t1.bDateTime, t1.RecID, t1.bTT, t2.StormStartedDate, t2.DayAmount, t2.StormAmount, t2.IncrementalRain, t2.PerMinute FROM DT AS t1 INNER JOIN Rain AS t2 USING (RecID) WHERE t1.vpID = \"$vpID\" && (t2.DayAmount IS NOT NULL || t2.StormAmount IS NOT NULL) ORDER BY t1.bDateTime";
#my $Query = "SELECT t1.bDateTime, t1.RecID, t1.bTT, t2.StormStartedDate, t2.DayAmount, t2.StormAmount, t2.IncrementalRain, t2.PerMinute FROM DT AS t1 INNER JOIN Rain AS t2 USING (RecID) WHERE t1.vpID = \"$vpID\" && (t2.DayAmount IS NOT NULL || t2.StormAmount IS NOT NULL) && RecID > 148000 ORDER BY t1.bDateTime";
my $sth = $dbh->prepare($Query);
printf stderr "\nQuerying DB... Query: $Query ";
$sth = $dbh->prepare($Query);
$sth->execute();
printf stderr "Done.\n";

my $ref = $sth->fetchrow_hashref();
my $DBbDateTime = $ref->{bDateTime};
my $DBRecID = $ref->{RecID};
my $DBbTT = $ref->{bTT};
my $DBStormStartedDate = $ref->{StormStartedDate};
my $DBDayAmount = $ref->{DayAmount};
my $DBStormAmount = $ref->{StormAmount};
my $DBIncrementalRain = $ref->{IncrementalRain};
my $DBPerMinute = $ref->{PerMinute};

printf stderr "\nSetting up list... ";
my @Data = ();
while($DBRecID ne "") {
    push @Data, "$DBbDateTime,$DBRecID,$DBbTT,$DBStormStartedDate,$DBDayAmount,$DBStormAmount,$DBIncrementalRain,$DBPerMinute";
    $ref = $sth->fetchrow_hashref();
    $DBbDateTime = $ref->{bDateTime};
    $DBRecID = $ref->{RecID};
    $DBbTT = $ref->{bTT};
    $DBStormStartedDate = $ref->{StormStartedDate};
    $DBDayAmount = $ref->{DayAmount};
    $DBStormAmount = $ref->{StormAmount};
    $DBIncrementalRain = $ref->{IncrementalRain};
    $DBPerMinute = $ref->{PerMinute};
}
if ($sth) { $sth->finish(); }
printf stderr "Done.\n";

my $line = "";
my $useStormStartedDate = "";
foreach $line (@Data) {
    my $retDBRecID = "";
    my $retDBbTT = "";
    my $retDBDayAmount = "";
    my $retDBStormAmount = "";
    my $retDBIncrementalRain = "";
    my $retDBPerMinute = "";
    my ($DBbDateTime,$DBRecID,$DBbTT,$DBStormStartedDate,$DBDayAmount,$DBStormAmount,$DBIncrementalRain,$DBPerMinute) = split /,/, $line;
    $Query = "";
    if ($DBStormStartedDate ne "") { $useStormStartedDate = $DBStormStartedDate; }
    if ($useStormStartedDate ne "") {
        $Query =  sprintf("SELECT t1.RecID, t1.bTT, t2.DayAmount, t2.StormAmount, t2.IncrementalRain, t2.PerMinute from DT AS t1 USE INDEX (PRIMARY,bDate) INNER JOIN Rain AS t2 USING (RecID) WHERE t1.vpID = \"$vpID\" && t1.bDate >=  \"$useStormStartedDate\" && t1.bDateTime < \"$DBbDateTime\" && (t2.DayAmount > 0 || t2.StormAmount > 0) ORDER BY t1.bDateTime DESC LIMIT 1");
    } else {
        my $tmpbTT = $DBbTT - 864000;
        $Query =  sprintf("SELECT t1.RecID, t1.bTT, t2.DayAmount, t2.StormAmount, t2.IncrementalRain, t2.PerMinute from DT AS t1 USE INDEX (PRIMARY,bTT) INNER JOIN Rain AS t2 USING (RecID) WHERE t1.vpID = \"$vpID\" && t1.bTT > $tmpbTT && t1.bDateTime < \"$DBbDateTime\" && (t2.DayAmount > 0 || t2.StormAmount > 0) ORDER BY t1.bDateTime DESC LIMIT 1");
    }
    printf stderr "\n$Query ";
    my $sth = $dbh->prepare($Query);
    $sth = $dbh->prepare($Query);
    $sth->execute();
    my $ref = $sth->fetchrow_hashref();
    $retDBRecID = $ref->{RecID};
    $retDBbTT = $ref->{bTT};
    $retDBDayAmount = $ref->{DayAmount};
    $retDBStormAmount = $ref->{StormAmount};
    $retDBIncrementalRain = $ref->{IncrementalRain};
    $retDBPerMinute = $ref->{PerMinute};
    if ($sth) { $sth->finish(); }
    printf stderr "Done.\n";
    $Query = "";
    my $IncrementalRain = "";
    my $PerMinute = "";
    if ($DBStormAmount ne "" && $retDBStormAmount ne "") {
        $IncrementalRain = $PerMinute = sprintf("%.02f", $DBStormAmount - $retDBStormAmount);
    } elsif ($DBDayAmount ne "" && $retDBDayAmount ne "") {
        $IncrementalRain = $PerMinute = sprintf("%.02f", $DBDayAmount - $retDBDayAmount);
    }
    if ($IncrementalRain ne "" && $IncrementalRain < 0.0) {
        if ($DBDayAmount > 0.0) {
            $IncrementalRain = $PerMinute = sprintf("%.02f", $DBDayAmount);
        } elsif ($DBStormAmount > 0.0) {
            $IncrementalRain = $PerMinute = sprintf("%.02f", $DBStormAmount);
        } else {
           $IncrementalRain = $PerMinute = "0.00";
        }
    } elsif ($PerMinute ne "")  {
        if ($DBbTT ne "" && $retDBbTT ne "") {
            my $eTime = ($DBbTT - $retDBbTT)/60;
            if ($eTime > 0.0) { $PerMinute = $PerMinute/$eTime; }
        }
    }
    if ($IncrementalRain ne "" && $IncrementalRain != $DBIncrementalRain) {
        $Query = sprintf("IncrementalRain = %.02f, ", $IncrementalRain);
    }
    if ($PerMinute ne "" && $PerMinute != $DBPerMinute) {
        $Query = sprintf("%sPerMinute = %.03f", $Query, $PerMinute);
    } elsif ($DBPerMinute eq "" && $retDBIncrementalRain ne "") {
        $Query = "PerMinute = \"0.000\"";
    }
    $Query =~ s/, $//;
    if ($sth) { $sth->finish(); }
    if ($Query ne "") {
        $Query = "UPDATE Rain SET $Query WHERE RecID = $DBRecID";
        printf stderr "  Updating DB... $Query ";
        $sth = $dbh->prepare($Query);
        $sth->execute();
        if ($sth) { $sth->finish(); }
        printf stderr "Done.\n";
    }
}

if ($sth) { $sth->finish(); }
if ($dbh) { $dbh->disconnect(); }

#
