#!/usr/bin/perl
#

# Populates new rtTT & wxTT columns in IndexDT table only if they 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.1";

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, rtCaptureTime, wxCaptureTime, rtTT, wxTT FROM IndexDT WHERE (rtTT IS NULL && rtCaptureTime IS NOT NULL) || (wxTT IS NULL && wxCaptureTime IS NOT NULL) && vpID = \"$vpID\" 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 $DBrtCaptureTime = $ref->{rtCaptureTime};
my $DBwxCaptureTime = $ref->{wxCaptureTime};
my $DBrtTT = $ref->{rtTT};
my $DBwxTT = $ref->{wxTT};

printf stderr "\nSetting up list... ";
my @Data = ();
while($DBRecID ne "") {
    push @Data, "$DBRecID,$DBrtCaptureTime,$DBwxCaptureTime,$DBrtTT,$DBwxTT";
    $ref = $sth->fetchrow_hashref();
    $DBRecID = $ref->{RecID};
    $DBrtCaptureTime = $ref->{rtCaptureTime};
    $DBwxCaptureTime = $ref->{wxCaptureTime};
    $DBrtTT = $ref->{rtTT};
    $DBwxTT = $ref->{wxTT};
}
if ($sth) { $sth->finish(); }
printf stderr "Done.\n";

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

my $Query = "";
my $NEWrtTT = "";
my $NEWwxTT = "";
foreach $line (@Data) {
    ($DBRecID,$DBrtCaptureTime,$DBwxCaptureTime,$DBrtTT,$DBwxTT) = split /,/, $line;
    $Query = "";
    if ($DBrtCaptureTime ne "" && $DBrtTT eq "") {
        $NEWrtTT = getTT($DBrtCaptureTime);
        $Query = "rtTT = \"$NEWrtTT\"";
    }
    if ($DBwxCaptureTime ne "" && $DBwxTT eq "") {
        $NEWwxTT = getTT($DBwxCaptureTime);
        if ($Query eq "") {
            $Query = "wxTT = \"$NEWwxTT\"";
        } else {
            $Query = "$Query, wxTT = \"$NEWwxTT\"";
        }
    }
    if ($Query ne "") {
        $Query = "UPDATE IndexDT SET $Query where RecID = $DBRecID";
        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(); }

#




