#!/usr/bin/perl

#note $time_zone, $time_diff EST/EDT specific args

use strict;
use DBI;
use XML::LibXML;

#print `date`;

#load database and path info
my ($env) = @ARGV;

my $xp_env = XML::LibXML->new->parse_file("../environment_xenia_$env.xml");
my $xp_graph = XML::LibXML->new->parse_file("environment_xenia_graph.xml");

my $db_host   = $xp_env->findvalue('//db/host');
my $db_name   = $xp_env->findvalue('//db/name');
my $db_user   = $xp_env->findvalue('//db/user');
my $db_passwd = $xp_env->findvalue('//db/passwd');

my $dir_base = $xp_env->findvalue('//path/dir_base');

#setup date month range for report based on calling argument
# $timestamp like '200602' or 'YYYYMM'
my $timestamp = `date +%Y%m`;
chomp ($timestamp);
#my $timestamp = '200611';
#print "$timestamp\n";

my $year_start = substr($timestamp,0,4);
my $month_start = substr($timestamp,4,2);

my ($year_end, $month_end);

if ($month_start == 12) { $month_end = 1; $year_end = $year_start + 1; }
else { $month_end = $month_start + 1; $year_end = $year_start; }

#print "year_end:$year_end\n";
#print "month_end:$month_end\n";

my $m_date_start = $year_start.'-'.$month_start.'-01';
my $m_date_end = $year_end.'-'.$month_end.'-01';

#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_zone,$time_diff);
if ($isdst) {$time_zone = 'EDT'; $time_diff = 4;}
else {$time_zone = 'EST'; $time_diff = 5;}


#establish database connection
my ($dbh,$sth,$sql,$sth_2,$sth_3,$sth_4);
if ($db_host eq '') { $dbh = DBI->connect ("dbi:Pg:dbname=$db_name", "$db_user", "$db_passwd"); } #remove host reference if local
else { $dbh = DBI->connect ("dbi:Pg:dbname=$db_name;host=$db_host", "$db_user", "$db_passwd"); }
if ( !defined $dbh ) {die "Cannot connect to database!\n";}

#get platforms for this xenia instance
$sql = qq{ select row_id,platform_handle from platform };
#print "sql:".$sql."\n";
$sth = $dbh->prepare( $sql );
$sth->execute();
if ($sth->rows == 0) { print "no data\n"; &exit_no_data; }

#####################################################################
while ( my ($platform_id,$platform_handle) = $sth->fetchrow() ) {
        #print "platform_id:$platform_id\n";


#data specifics are CSV_FILE, header line and @obs)

open(CSV_FILE, ">$dir_base\platform/$platform_handle/archive/$platform_handle\_$year_start\_$month_start.csv");
my $csv_content = '';
print CSV_FILE "measurement_date($time_zone),meaurement_date(GMT),";

#get sensor_id's for platforms to build header line
$sql = qq{ select sensor.row_id,sensor.m_type_id,m_type_display_order.row_id from sensor
    left join m_type_display_order on m_type_display_order.m_type_id=sensor.m_type_id
    where platform_id = $platform_id
    order by m_type_display_order.row_id; 
};
#print "sql:".$sql."\n";
$sth_2 = $dbh->prepare( $sql );
$sth_2->execute();

while ( my ($sensor_id,$m_type_id) = $sth_2->fetchrow() ) {
        #print "  sensor_id:$sensor_id\n";

	my $title = $xp_graph->findvalue('//observation_list/observation[@m_type_id="'.$m_type_id.'"]/title'); 
	my $y_title = $xp_graph->findvalue('//observation_list/observation[@m_type_id="'.$m_type_id.'"]/y_title'); 

	$csv_content .= "$title($y_title),";

	my $standard_uom = $xp_graph->findvalue('//observation_list/observation[@m_type_id="'.$m_type_id.'"]/standard_uom'); 
	my $standard_uom_en = $xp_graph->findvalue('//observation_list/observation[@m_type_id="'.$m_type_id.'"]/standard_uom_en');
	if ($standard_uom ne $standard_uom_en) {
		$y_title = $xp_graph->findvalue('//unit_conversion_list/unit_conversion[@id="'.$standard_uom.'_to_'.$standard_uom_en.'"]/y_title');
		$csv_content .= "$title($y_title),";
	}
}

$csv_content = substr($csv_content, 0, -1);
print CSV_FILE "$csv_content\n";

#####################################################
#get list of m_date's using first available platform sensor_id
#the below bases all timestamps off of the first retrieved - assumes timestamps are the same for all obs at platform

$sql = qq{ select sensor.row_id,m_type_display_order.row_id from sensor
    left join m_type_display_order on m_type_display_order.m_type_id=sensor.m_type_id
    where platform_id = $platform_id
    order by m_type_display_order.row_id limit 1;
};
#print "sql:".$sql."\n";
$sth_2 = $dbh->prepare( $sql );
$sth_2->execute();
my ($sensor_id) = $sth_2->fetchrow();

$sql = qq{ SELECT m_date + interval '$time_diff hours',m_date FROM multi_obs where m_date >= '$m_date_start' and m_date < '$m_date_end' and sensor_id = $sensor_id order by m_date };
#print $sql;
$sth_2 = $dbh->prepare( $sql );
$sth_2->execute();

#####################################################
#cycle through sensor_id's with this m_date

while( my ($m_date_local,$m_date) = $sth_2->fetchrow() ) {

$csv_content = '';
$csv_content .= "$m_date_local,$m_date,";

$sql = qq{ select sensor.row_id,sensor.m_type_id,m_type_display_order.row_id from sensor
    left join m_type_display_order on m_type_display_order.m_type_id=sensor.m_type_id
    where platform_id = $platform_id
    order by m_type_display_order.row_id;
};
#print "sql:".$sql."\n";
$sth_3 = $dbh->prepare( $sql );
$sth_3->execute();

while ( my ($sensor_id,$m_type_id) = $sth_3->fetchrow() ) {
        #print "  sensor_id:$sensor_id\n";

	my $conversion_formula = '';
        my $standard_uom = $xp_graph->findvalue('//observation_list/observation[@m_type_id="'.$m_type_id.'"]/standard_uom');
        my $standard_uom_en = $xp_graph->findvalue('//observation_list/observation[@m_type_id="'.$m_type_id.'"]/standard_uom_en');
        my $conversion_formula = $xp_graph->findvalue('//unit_conversion_list/unit_conversion[@id="'.$standard_uom.'_to_'.$standard_uom_en.'"]/conversion_formula');

$sql = qq{ SELECT m_value from multi_obs where m_date = '$m_date' and sensor_id = $sensor_id };
$sth_4 = $dbh->prepare( $sql );
$sth_4->execute();
my ($m_value) = $sth_4->fetchrow();
$csv_content .= "$m_value,";

#unit conversion using supplied equation(e.g. celcius to fahrenheit)
if ($standard_uom ne $standard_uom_en) {
	my $conversion_temp = $conversion_formula;
	$conversion_temp =~ s/var1/$m_value/g;
	my $conversion_val = eval $conversion_temp;
	$csv_content .= "$conversion_val,";
}

} #sensor

$csv_content = substr($csv_content, 0, -1);
print CSV_FILE "$csv_content\n";

} #m_date
} #platform

close(CSV_FILE);

$sth->finish;
$sth_2->finish;
$sth_3->finish;
$sth_4->finish;
$dbh->disconnect();

#print `date`;
exit 0;

#####################################################################
sub exit_no_data {
        $sth->finish;
        $dbh->disconnect();
        exit 0;
}