#!/usr/bin/perl
#

# Sets the MinutesRad column in the Solar table to the new format starting in
# DB v11.5 i.e. mmm.ss where mmm = Minutes ss = Seconds
#
# 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 MinutesRad 
# column if it is 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 if you need to update other Console ID's
#	./RemediateSolarTime 1
#	./RemediateSolarTime 2
#
# 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.RecID, t1.YearMonthDay, t1.bTT, t2.MinutesRad FROM DT AS t1 INNER JOIN Solar AS t2 USING (RecID) WHERE t1.vpID = \"$vpID\" && t2.Radiation > 0 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 $DBRecID = $ref->{RecID};
my $DBYearMonthDay = $ref->{YearMonthDay};
my $DBbTT = $ref->{bTT};
my $DBMinutesRad = $ref->{MinutesRad};

printf stderr "\nSetting up list... ";
my @Data = ();
while($DBRecID ne "") {
    push @Data, "$DBRecID,$DBYearMonthDay,$DBbTT,$DBMinutesRad";
    $ref = $sth->fetchrow_hashref();
    $DBRecID = $ref->{RecID};
    $DBYearMonthDay = $ref->{YearMonthDay};
    $DBbTT = $ref->{bTT};
    $DBMinutesRad = $ref->{MinutesRad};
}
if ($sth) { $sth->finish(); }
printf stderr "Done.\n";

my $line = "";
my $YMDlast = "";
my $DBbTTlast = 0.0;
foreach $line (@Data) {
    ($DBRecID,$DBYearMonthDay,$DBbTT,$DBMinutesRad) = split /,/, $line;
    $Query = "";
    if ($DBRecID ne "" && $DBYearMonthDay ne "" && $DBbTT ne "") {
        if ($DBYearMonthDay ne $YMDlast) {
            $Query =  sprintf("SELECT t1.bTT from DT AS t1 USE INDEX (YMDvpID) INNER JOIN Solar AS t2 USING (RecID) WHERE t1.YearMonthDay = $DBYearMonthDay && t1.vpID = \"$vpID\" && t2.Radiation > 0 ORDER BY t1.bTT LIMIT 1");
            printf stderr "\n$Query ";
            my $sth = $dbh->prepare($Query);
            $sth = $dbh->prepare($Query);
            $sth->execute();
            my $ref = $sth->fetchrow_hashref();
            $DBbTTlast = $ref->{bTT};
            if ($sth) { $sth->finish(); }
            printf stderr "Done.\n";
        }
        my $eTime = 0.0;
        $Query = "";
        if ($DBbTTlast > 0 && $DBbTT > 0) {
            $eTime = sprintf("%i.%02i", ($DBbTT - $DBbTTlast)/60, ($DBbTT - $DBbTTlast)%60);
            if ($DBMinutesRad != $eTime) { $Query = "UPDATE Solar SET MinutesRad = \"$eTime\" WHERE RecID = \"$DBRecID\""; }
        }
        if ($sth) { $sth->finish(); }
    }
    if ($Query ne "") {
        printf stderr "  Updating DB... $Query\t# \$DBbTTlast $DBbTTlast \$DBbTT $DBbTT Minutes %i Seconds %i ", ($DBbTT - $DBbTTlast)/60, ($DBbTT - $DBbTTlast)%60;
        $sth = $dbh->prepare($Query);
        $sth->execute();
        if ($sth) { $sth->finish(); }
        printf stderr "Done.\n";
    }
    $YMDlast = $DBYearMonthDay;
}

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

#
