#!/usr/bin/perl -w
# bc_hi_lo_temp.PL
# written by BearCreekMan 08/17/04
# calculates high and low temperatures for all data records by date and inserts
# into bc_temp table

#use strict;

# load module
use DBI;

# database connect parameters
my $dsn = 'DBI:mysql:bc_weather:localhost';
my $db_user = 'bearcreekman';
my $db_pass = 'XXXXXXXX';

# initialize variables
my $htemp   = 0.00;
my $htime   = "0000";
my $ltemp   = 0.00;
my $ltime   = "0000";
my $thtemp  = 0.00;
my $thtime  = "0000";
my $tltemp  = 0.00;
my $tltime  = "0000";
my $flag    = 0;
my $bflag   = "00000000";
my $dflag   = "00000000";
my $tflag   = "0000";
my $dirtyf  = "X";
my $cleanf  = "";

# connect to database
my $dbh = DBI->connect ($dsn,$db_user,$db_pass)
	|| die "Could not connect to database: "
	. DBI-> errstr;

# prepare SQL statement
my $sql = "SELECT RecDate, OutdoorTemperature FROM bc_weather ORDER BY RecDate";
my $sth = $dbh->prepare($sql);

# execute SQL statement
$sth->execute();

# retrieve results
while(my($RecDate, $OutdoorTemperature) = $sth->fetchrow_array())
{
	$bflag = substr(sprintf("%s",$RecDate),0,8);
	
	# start of new date
	if ($flag == 0) 
	{
		$flag = 1;

		# initialize sort buckets
		$htemp  = $OutdoorTemperature;
		$htime  = substr(sprintf("%s",$RecDate),8,4);
		$thtemp = $htemp;
		$thtime = $htime;
		$ltemp  = $OutdoorTemperature;
		$ltime  = $htime;
		$tltemp = $ltemp;
		$tltime = $ltime;
		$dflag  = substr(sprintf("%s",$RecDate),0,8);
	}

	# somewhere we lost some data records possibly from a power outage
	if ($bflag ne $dflag)
	{
		$tdate = sprintf("%4s-%2s-%2s",substr($dflag,0,4),substr($dflag,4,2),substr($dflag,6,2));
		$dbh->do("INSERT INTO bc_temp(Rec_Date,RecDate,hitemp,hitime,lotemp,lotime,dirty) VALUES(?,?,?,?,?,?,?)",undef,$tdate,$dflag,$thtemp,$thtime,$tltemp,$tltime,$dirtyf); 

		# reinitialize sort buckets since flag wasn't triggered
		$htemp  = $OutdoorTemperature;
		$htime  = substr(sprintf("%s",$RecDate),8,4);
		$thtemp = $htemp;
		$thtime = $htime;
		$ltemp  = $OutdoorTemperature;
		$tltemp = $ltemp;
		$tltime = $ltime;
		$dflag  = substr(sprintf("%s",$RecDate),0,8);
	}

	# set date and time flags
	$dflag = substr(sprintf("%s",$RecDate),0,8);
	$tflag = substr(sprintf("%s",$RecDate),8,4);

	# sort out high temperature
	if ($OutdoorTemperature > $htemp) 
	{ 
		$htemp = $OutdoorTemperature; 
		$thtemp = $htemp;
		$htime = substr(sprintf("%s",$RecDate),8,4);
		$thtime = $htime;
	}

	# sort out low temperature
	if ($OutdoorTemperature < $ltemp) 
	{ 
		$ltemp = $OutdoorTemperature; 
		$tltemp = $ltemp;
		$ltime = substr(sprintf("%s",$RecDate),8,4);
		$tltime = $ltime;
	}

	# if we have reached end of date then insert record
	if ($tflag eq "2340")
	{
		$tdate = sprintf("%4s-%2s-%2s",substr($RecDate,0,4),substr($RecDate,4,2),substr($RecDate,6,2));
		$dbh->do("INSERT INTO bc_temp(Rec_Date,RecDate,hitemp,hitime,lotemp,lotime,dirty) VALUES(?,?,?,?,?,?,?)",undef,$tdate,$bflag,$htemp,$htime,$ltemp,$ltime,$cleanf); 
		$flag = 0;
	}

	# loop to top and process next record
}

# finish off the table cursor
$sth->finish();

# disconnect from the database
$dbh->disconnect();

# end of bc_hi_lo_temp.PL