#!/usr/bin/perl
#

# Populates new StormStartedTime and StormEndedTime columns in Rain table.
#
# You 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 2011-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.2";

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]; }

sub getTT {
    my ($time) = @_;

    my ($date, $time) = split /\s+/, $time;
    my ($hour, $minute, $second) = split /:/, $time;
    my ($year, $month, $day) = split /-/, $date;
    my $TT = timelocal($second, $minute, $hour, $day, $month-1, $year-1900);

    return $TT;
}

# 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 StormStartedDate, StormStartedTime, StormEndedDate, StormEndedTime, StormAmount FROM Storms WHERE vpID = \"$vpID\" ORDER BY StormStartedDate";
my $sth = $dbh->prepare($Query);
printf stderr "\nQuerying DB... ";
$sth = $dbh->prepare($Query);
$sth->execute();
printf stderr "Done.\n";

my $ref = $sth->fetchrow_hashref();
my $DBStormStartedDate = $ref->{StormStartedDate};
my $DBStormStartedTime = $ref->{StormStartedTime};
my $DBStormEndedDate = $ref->{StormEndedDate};
my $DBStormEndedTime = $ref->{StormEndedTime};
my $DBStormAmount = $ref->{StormAmount};

printf stderr "\nSetting up list... ";
my @Data = ();
while($DBStormStartedDate ne "") {
    push @Data, "$DBStormStartedDate,$DBStormStartedTime,$DBStormEndedDate,$DBStormEndedTime,$DBStormAmount";
    $ref = $sth->fetchrow_hashref();
    $DBStormStartedDate = $ref->{StormStartedDate};
    $DBStormStartedTime = $ref->{StormStartedTime};
    $DBStormEndedDate = $ref->{StormEndedDate};
    $DBStormEndedTime = $ref->{StormEndedTime};
    $DBStormAmount = $ref->{StormAmount};
}
if ($sth) { $sth->finish(); }
printf stderr "Done.\n";

printf stderr "\nProcessing list...\n\n";
my $line;
my $Query = "";
my $Update = "";
foreach $line (@Data) {
    $Query = "";
    $Update = "";
    ($DBStormStartedDate,$DBStormStartedTime,$DBStormEndedDate,$DBStormEndedTime,$DBStormAmount) = split /,/, $line;
    if ($DBStormStartedDate ne "0000-00-00") {
        if ($DBStormStartedDate ne "") {
            $Query = "SELECT RecID FROM Rain AS t1 INNER JOIN DT AS t2 USING (RecID) WHERE t2.vpID = \"$vpID\" && t1.StormStartedDate =\"$DBStormStartedDate\"";
                $sth = $dbh->prepare($Query);
            $sth->execute();
            $ref = $sth->fetchrow_hashref();
            my $RecID = $ref->{RecID};
            if ($RecID ne "") {
                $Query = "SELECT bTime from DT WHERE WHERE vpID = \"$vpID\" && RecID = \"$RecID\"";
                $sth = $dbh->prepare($Query);
                $sth->execute();
                $ref = $sth->fetchrow_hashref();
                my $bTime = $ref->{bTime};
                if ($bTime ne "") {
                    $Update = "StormStartedTime = \"$bTime\"";
                } else {
                    printf stderr "No start bTime returned for $RecID\n";
                }
            } else {
                printf stderr "Can't find start time for $DBStormStartedDate\n";
            }
        }
        $Query = "";
        if ($DBStormEndedDate ne "") {
            $Query = "SELECT T1.RecID from Rain AS T1 INNER JOIN DT AS T2 USING (RecID) WHERE T2.vpID = \"$vpID\" && T2.bDate = \"$DBStormEndedDate\" && StormAmount = \"$DBStormAmount\" ORDER BY T2.bDateTime LIMIT 1";
            $sth = $dbh->prepare($Query);
            $sth->execute();
            $ref = $sth->fetchrow_hashref();
            my $RecID = $ref->{RecID};
            if ($RecID eq "") {
                $Query = "SELECT MAX(T1.StormAmount) AS newStormAmount FROM Rain AS T1 INNER JOIN DT AS T2 USING (RecID) WHERE T2.vpID = \"$vpID\" && T2.bDate = \"$DBStormEndedDate\"";
                $sth = $dbh->prepare($Query);
                $sth->execute();
                $ref = $sth->fetchrow_hashref();
                my $newStormAmount = $ref->{newStormAmount};
                if ($newStormAmount ne "" && $newStormAmount != $DBStormAmount) {
                    printf stderr "Found new StormAmount = $newStormAmount vPro lied Old = $DBStormAmount using but not changing\n";
                    $Query = "SELECT T1.RecID from Rain AS T1 INNER JOIN DT AS T2 USING (RecID) WHERE T2.vpID = \"$vpID\" && T2.bDate = \"$DBStormEndedDate\" && StormAmount = \"$newStormAmount\" ORDER BY T2.bDateTime LIMIT 1";
                    $sth = $dbh->prepare($Query);
                    $sth->execute();
                    $ref = $sth->fetchrow_hashref();
                    $RecID = $ref->{RecID};
                } else { 
                    printf stderr "wasn't able to find new StormAmount\n";
                }
            }
            if ($RecID ne "") {
                $Query = "SELECT bTime from DT WHERE T2.vpID = \"$vpID\" && RecID = \"$RecID\"";
                $sth = $dbh->prepare($Query);
                $sth->execute();
                $ref = $sth->fetchrow_hashref();
                my $bTime = $ref->{bTime};
                if ($Update ne "") { $Update = "$Update,"; }
                if ($bTime ne "") {
                    $Update = "$Update StormEndedTime = \"$bTime\"";
                } else {
                    printf stderr "No end bTime returned for   $RecID\n";
                }
            } else {
                printf stderr "Can't find end time for   $DBStormStartedDate\n";
            }
        }
        if ($Update ne "") {
            $Update = "UPDATE Storms SET $Update WHERE StormStartedDate = \"$DBStormStartedDate\"";
            printf stderr "$Update ";
            $sth = $dbh->prepare($Update);
            $sth->execute();
            printf stderr "Done.\n";
        }
    }
}

printf stderr "\nAll Done.\n\n";

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

#




