#!/usr/bin/perl
#

# Populates new IncrentalRain column in Rain table
# Sets CurrentlyRaining column if any cells are not poplulated or if they
# are incorrectly populated (possibly from a data load)
#
# 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;

	my $Version = "1.0.3";

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 t2.bDateTime, t1.RecID, t1.CurrentlyRaining, t1.IncrementalRain, t1.DayAmount, t1.StormAmount, t1.Rate from Rain AS t1 INNER JOIN DT AS t2 USING (RecID) WHERE t2.vpID = \"$vpID\" ORDER BY t2.bDateTime";
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 $curDateTimeSQL = $ref->{bDateTime};
my $curRecID = $ref->{RecID};
my $curCurrentlyRaining = $ref->{CurrentlyRaining};
my $curIncrementalRain = $ref->{IncrementalRain};
my $curDayAmount = $ref->{DayAmount};
my $curStormAmount = $ref->{StormAmount};
my $incRate = $ref->{Rate};

printf stderr "\nSetting up list... ";
my @Data = ();
while($curRecID ne "") {
    push @Data, "$curDateTimeSQL,$curRecID,$curCurrentlyRaining,$curIncrementalRain,$curDayAmount,$curStormAmount,$incRate";
    $ref = $sth->fetchrow_hashref();
    $curDateTimeSQL = $ref->{bDateTime};
    $curRecID = $ref->{RecID};
    $curCurrentlyRaining = $ref->{CurrentlyRaining};
    $curIncrementalRain = $ref->{IncrementalRain};
    $curDayAmount = $ref->{DayAmount};
    $curStormAmount = $ref->{StormAmount};
    $incRate = $ref->{Rate};
}
if ($sth) { $sth->finish(); }
printf stderr "Done.\n";

printf stderr "\nProcessing list...\n\n";
my $line;
my $lastDateTimeSQL = "";
my $lastRecID = "";
my $lastCurrentlyRaining = "";
my $lastIncrementalRain = "";
my $lastDayAmount = "";
my $lastStormAmount = "";
my $lastRate = "";
#
my $IncrementalRain;
my $IncrementalDayRain;
my $IsRaining;
foreach $line (@Data) {
    ($curDateTimeSQL,$curRecID,$curCurrentlyRaining,$curIncrementalRain,$curDayAmount,$curStormAmount,$incRate) = split /,/, $line;
    if ($lastDateTimeSQL ne "" && $lastRecID ne "") {
        if ($curDateTimeSQL ne "" && $curRecID ne "") {
               if ($incRate > 0.0) {
                   $IsRaining = 1;
               } else {
                   $IsRaining = 0;
               }
               if ($curStormAmount ne "" && $lastStormAmount ne "") {
                   $IncrementalRain = $curStormAmount - $lastStormAmount;
               } else {
                   $IncrementalRain = "";
               }
               if ($IncrementalRain eq "" && $curDayAmount ne "") {
                   if ($lastDayAmount eq "" && $curStormAmount eq "") {
                       $IncrementalDayRain = $curDayAmount;
                   } elsif ($lastDayAmount ne "") {
                       $IncrementalDayRain = $curDayAmount - $lastDayAmount;
                   } elsif ($curDayAmount > 0.0) {
                       $IncrementalDayRain = "0.0";
                   }
               } else {
                   $IncrementalDayRain = "";
               }
               if ($IncrementalRain eq "" && $IncrementalDayRain ne "") { $IncrementalRain = $IncrementalDayRain; }
               if ($IncrementalRain ne "") { $IncrementalRain = sprintf "%.2f", $IncrementalRain; }
               $Query = "";
               if ($IncrementalRain ne "" && "$IncrementalRain" ne "$curIncrementalRain") {
                   $Query = "SET IncrementalRain = \"$IncrementalRain\"";
               }
               if ($curCurrentlyRaining eq "" || $IsRaining != $curCurrentlyRaining) {
                   if ($Query eq "") {
                       $Query = "SET ";
                   } else {
                       $Query = "$Query, ";
                   }
                   $Query = "$Query CurrentlyRaining = \"$IsRaining\"";
               }
               if ($Query ne "") {
                   $Query = "UPDATE Rain $Query where RecID = $curRecID";
                   printf stderr "$Query ";
                   $sth = $dbh->prepare($Query);
                   $sth->execute();
                   printf stderr "Done.\n";
               }
        }
    }
    $lastDateTimeSQL = $curDateTimeSQL;
    $lastRecID = $curRecID;
    $lastIncrementalRain = $curIncrementalRain;
    $lastStormAmount = $curStormAmount;
    $lastDayAmount = $curDayAmount;
}
printf stderr "\nAll Done.\n\n";

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

#
