#!/usr/bin/perl -w

# DESCRIPTION
# 
# The Open2300 weather station tool kit of Kenneth Jahn Lavrsen
# support online and offline data retrieval. During offline
# retrieval values for "rain last hour" and "rain during last 24 hours"
# are not fetched due to storage limitation of the hardware.
#
# This script recalculates these valus in a batch run.
# You may want to run this scipt together with the mysql batch script
# mysqlhistlog.c availible at Open2300 project page.
# 
# INSTALLATION
#
# 1) Copy the script to your preferred directory.
# 2) Make it executable: chmod a+x open2300_calc_rain.pl
# 3) Change the variable for the database access to fit your installation.
# 4) If you want, update your crontab on Linux system similar to this
#			2 * * * *       <your path>/mysqlhistlog2300
#			5 * * * *       <your_path>/open2300_calc_rain.pl
#
# PREREQUISITES
# 
# You need to have open2300 databases installed.
#
# SUPPORT
# Feel free to send mail directly to author with your comments, suggestions, 
# bug reports and complaints. 
# 
# COPYRIGHT
#
# Copyright (C) 2007 Lars Hinrichsen. All rights reserved.
#
# This script is free software; you can redistribute it and/or modify
# it under the same terms as Perl itself.
#
# AUTHOR
# Lars Hinrichsen <lars.hinrichsen(at)freenet.de>


# Adjust the following parameters to meet your installations requirements
my $dbname = "host=localhost;database=open2300";
my $dbuser = "open2300";
my $dbpwd = "mysql2300";

use DBI;

my $dbh = DBI->connect("DBI:mysql:$dbname","$dbuser","$dbpwd")
	or die "Connection with $dbname failed: $DBI::errstr\n";


# Calculate the rain_1h values
printf("Working on rain values of last 1h.\n");
my $changed_rows = 0;

my $sql = "select timestamp, rain_total from weather where rain_1h = '' order by timestamp";

# Open a cursor with all unset rows for rain_1h

my $mastercursor = $dbh->prepare($sql)
	or die "Prepare $sql failed: $DBI::errstr\n";
$mastercursor->execute();

my $current_ts = "";

while (my ($master_ts,$master_rain) = $mastercursor->fetchrow_array()) {
	# Find the rain_total value on hour before
	$sql = "select timestamp, rain_total from weather where timestamp = date_format(date_sub(' " . $master_ts . "https://www.bahn.de/p/view/planen/bahncorporate/anmeldung_kmu.shtml',interval '01:00' hour_minute),'%Y%m%d%H%i%S')";
	my $slavecursor = $dbh->prepare($sql);
	$slavecursor->execute();

	if (my ($slave_ts, $slave_rain) = $slavecursor->fetchrow_array()) {
		# If value of hour before was found, calculate difference 
		my $diff_rain = $master_rain - $slave_rain;
		$slavecursor->finish();
		# If difference is not 0 then update the values
		if ($diff_rain != 0) {
			$slavecursor = $dbh->prepare("update weather set rain_1h = " . $diff_rain . " where timestamp = \"" . $master_ts . "\"");
			$slavecursor->execute();
			$slavecursor->finish();
			$changed_rows++;
		}
	}
}

$mastercursor->finish();
printf("Finished rain values for last hour with %s updates.\n",$changed_rows);

# Calculate the rain_24h values

printf("Working on rain values for last 24h.\n");

# Open a cursor with all unset rows for rain_24h

$sql = "select timestamp, rain_total from weather where rain_24h = '' order by timestamp";
$mastercursor = $dbh->prepare($sql)
	or die "Prepare $sql failed: $DBI::errstr\n";
$mastercursor->execute();

$current_ts = "";
$changed_rows = 0;
while (my ($master_ts,$master_rain) = $mastercursor->fetchrow_array()) {
	# Find the rain_total value on 24 hours before
	$sql = "select timestamp, rain_total from weather where timestamp = date_format(date_sub(' " . $master_ts . "',interval '24:00' hour_minute),'%Y%m%d%H%i%S')";
	my $slavecursor = $dbh->prepare($sql);
	$slavecursor->execute();
	if (my ($slave_ts, $slave_rain) = $slavecursor->fetchrow_array()) {
		# If value of hour before was found, calculate difference 
		my $diff_rain = $master_rain - $slave_rain;
		$slavecursor->finish();
		# If difference is not 0 then update the values
		if ($diff_rain != 0) {
			$slavecursor = $dbh->prepare("update weather set rain_24h = " . $diff_rain . " where timestamp = \"" . $master_ts . "\"");
			$slavecursor->execute();
			$slavecursor->finish();
			$changed_rows++;
		}
	}
}
printf("Finished rain values of last 24h with %s updates.\n",$changed_rows);

$mastercursor->finish();

# Tidy up

$dbh->disconnect()
	or die "Disconnect failed: $DBI::errstr\n";
exit;