#!/usr/bin/perl
#

# Populates new bTT column in DT table only if contents are NULL (Cells may
# contain data already from using vproweather or possibly from a data load)
#
# This relies on the Timezone being properly set
#
# 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.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]; }

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 RecID, bDateTime, bTT FROM DT WHERE vpID = \"$vpID\" && bTT IS NULL && bDateTime IS NOT NULL ORDER BY RecID";
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 $DBRecID = $ref->{RecID};
my $DBbDateTime = $ref->{bDateTime};
my $DBbTT = $ref->{bTT};

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

printf stderr "\nProcessing list...\n\n";
my $line;

my $Query = "";
my $NEWbTT = "";
foreach $line (@Data) {
    ($DBRecID,$DBbDateTime,$DBbTT) = split /,/, $line;
    $Query = "";
    if ($DBbDateTime ne "" && $DBbTT eq "") {
        $NEWbTT = getTT($DBbDateTime);
        $Query = "UPDATE DT SET bTT = \"$NEWbTT\" WHERE RecID = \"$DBRecID\"";
    }
    if ($Query ne "") {
        printf stderr "$Query ";
        $sth = $dbh->prepare($Query);
        $sth->execute();
        printf stderr "Done.\n";
    }
}

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

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

#




