#!/usr/bin/perl

=comments

Assumptions about this graph process
1)the graph output directory structure
2)the 'no_data.png' file
3)the database configuration utility
4)eval won't be a security issue

Class dependent parts are:

- the initialization of variables from ARGV
- the built SQL query

=cut

require "graphCommon.lib";
require "graphSingleLine.lib";

use strict;
use DBI;
use Config::IniFiles;

my $graph_class = shift;
#print $graph_class."\n";

my $time_query = shift;

#declaring all possible variables whether used or not
my ($station_name, $station_id, $station_num, $platform_id, $sensor_id, $db_id, $table_id, $time_id, $column_id, $m_date, $m_type_id, $time_interval, $from_date, $to_date, $range_min, $range_max, $range_conversion, $title, $y_units, $unit_conversion, $break_interval, $dir_path, $size_x, $size_y);

if ($time_query eq 'TimeDate') {
	$from_date = shift;
	$to_date = shift;
}
elsif ($time_query eq 'TimeLast') {
	$time_interval = shift;	
}

if ($graph_class eq 'singleLine') {
	($station_id, $db_id, $time_id, $column_id, $range_min, $range_max, $range_conversion, $title, $y_units, $unit_conversion, $break_interval, $dir_path) = @ARGV;

	$station_name = $station_id;
}
elsif ($graph_class eq 'singleLineMultiTable') {
	($station_name, $station_id, $station_num, $db_id, $table_id, $time_id, $column_id, $range_min, $range_max, $range_conversion, $title, $y_units, $unit_conversion, $break_interval, $dir_path) = @ARGV;
}
elsif ($graph_class eq 'singleLineMultiObsTable') {
	($platform_id, $db_id, $table_id, $m_type_id, $sensor_id, $time_id, $column_id, $range_min, $range_max, $range_conversion, $title, $y_units, $unit_conversion, $break_interval, $dir_path, $size_x, $size_y) = @ARGV;
}

#get database configuration
my $cfg = new Config::IniFiles( -file => "../processBuoys.ini" );

my $db_name = $cfg->val($db_id, 'db_name');
my $db_user = $cfg->val($db_id, 'db_user');
my $db_passwd = $cfg->val($db_id, 'db_passwd');
my $graphics_dir = $cfg->val('BUOY', 'graphics_dir');
my $tmp_dir = $cfg->val('BUOY', 'tmp_dir');

#read in parameters

#print "dir_path:".$dir_path."\n";

print $db_name.":".$db_user.":".$db_passwd."\n";

#establish database connection
my $dbh = DBI->connect ("dbi:Pg:dbname=$db_name", $db_user, $db_passwd);
if ( !defined $dbh ) {die "Cannot connect to database!\n";} 

#unit convert $range_min_converted, $range_max_converted

my $conversion_string = $range_conversion;
$conversion_string =~ s/var1/$range_min/g;
my $range_min_converted = eval $conversion_string;

$conversion_string = $range_conversion;
$conversion_string =~ s/var1/$range_max/g;
my $range_max_converted = eval $conversion_string;

#print "\n".$range_min_converted.":".$range_max_converted."\n";

#database query

#daylight savings time consideration
my ($temp_sec,$temp_min,$temp_hour,$temp_mday,$temp_mon,$temp_year,$temp_wday,$temp_yday,$isdst) = localtime(time);

my $time_subtract;
if ($isdst) {$time_subtract = 4;}
else {$time_subtract = 5;}

my ($sql, $sth);

#'Mon dd hh:mi PM' more readable time format
#'MM DD YYYY hh24:mi'  military time format

if ($graph_class eq 'singleLine') {
	if ($time_query eq 'TimeDate') {
	$sql = qq{ SELECT TO_CHAR(($time_id - interval '$time_subtract hours'), 'MM DD YYYY hh24:mi'), $column_id FROM $station_id where $time_id >= timestamp '$from_date 00:00:00' + interval '$time_subtract hours' and $time_id <= timestamp '$to_date 00:00:00' + interval '$time_subtract hours' and $column_id >= $range_min_converted and $column_id <= $range_max_converted order by $time_id };
	}
	elsif ($time_query eq 'TimeLast') {
        $sql = qq{ SELECT TO_CHAR(($time_id - interval '$time_subtract hours'), 'MM DD YYYY hh24:mi'), $column_id FROM $station_id where date($time_id) > date(current_timestamp - interval '$time_interval') and $column_id >= $range_min_converted and $column_id <= $range_max_converted order by $time_id };
	}
}
elsif ($graph_class eq 'singleLineMultiTable') {
	if ($time_query eq 'TimeDate') {
	$sql = qq{ SELECT TO_CHAR(($time_id - interval '$time_subtract hours'), 'MM DD YYYY hh24:mi'), $column_id FROM $table_id where $station_id = '$station_num' and $time_id >= timestamp '$from_date 00:00:00' + interval '$time_subtract hours' and $time_id <= timestamp '$to_date 00:00:00' + interval '$time_subtract hours' and $column_id >= $range_min_converted and $column_id <= $range_max_converted order by $time_id };
	}
	elsif ($time_query eq 'TimeLast') {
	$sql = qq{ SELECT TO_CHAR(($time_id - interval '$time_subtract hours'), 'MM DD YYYY hh24:mi'), $column_id FROM $table_id where $station_id = '$station_num' and date($time_id) > date(current_timestamp - interval '$time_interval') and $column_id >= $range_min_converted and $column_id <= $range_max_converted order by $time_id };
	}
}
elsif ($graph_class eq 'singleLineMultiObsTable') {
#FIX need to redo sql for multi-obs
        if ($time_query eq 'TimeDate') {        $sql = qq{ SELECT TO_CHAR(($time_id - interval '$time_subtract hours'), 'MM DD YYYY hh24:mi'), $column_id FROM $table_id where $station_id = '$station_num' and $time_id >= timestamp '$from_date 00:00:00' + interval '$time_subtract hours' and $time_id <= timestamp '$to_date 00:00:00' + interval '$time_subtract hours' and $column_id >= $range_min_converted and $column_id <= $range_max_converted order by $time_id };
        }
        elsif ($time_query eq 'TimeLast') {        $sql = qq{ SELECT TO_CHAR(($time_id), 'MM DD YYYY hh24:mi'), $column_id FROM $table_id where m_type_id = $m_type_id and platform_id = $platform_id and sensor_id = $sensor_id and date($time_id) > date(current_timestamp - interval '$time_interval') and $column_id >= $range_min_converted and $column_id <= $range_max_converted order by $time_id };
        }
}



print "sql:".$sql."\n";
$sth = $dbh->prepare( $sql );
$sth->execute();

#use 'no data' image and exit if no data
if ($sth->rows < 2) {
        printf("Not enough recent measurements to produce graph\n");
        my $file_ref = $graphics_dir."no_data.png";
        `cp $file_ref $dir_path`;

	$sth->finish;
	$dbh->disconnect();

        exit 0;
}

#generate a random file handle concatenating a random number and timestamp
my $graph_data_file = $tmp_dir.'gnuplot_'.int(rand(10000000));

open (OUTFILE,">$graph_data_file");

my $conversion_val;
while ( my @row = $sth->fetchrow() ) {

    #unit conversion using supplied equation(e.g. celcius to fahrenheit)
    	$conversion_string = $unit_conversion;
    	$conversion_string =~ s/var1/@row[1]/g;
    	$conversion_val = eval $conversion_string;

    	print OUTFILE @row[0]."\t".$conversion_val."\n";
}

close OUTFILE;

insert_break_interval($graph_data_file, $break_interval);

my $time_zone = '-'.$time_subtract;
my_graph($graph_data_file, $title, $y_units, $dir_path, $time_zone, $size_x, $size_y);

$sth->finish;
$dbh->disconnect();

exit 0;