#!/usr/bin/perl
use strict;

use DBI;
use Config::IniFiles;

my $platform_id = 3;
my ($m_date, $water_temp, $conductivity, $salinity, $pressure, $DO_percent, $DO_absolute, $battery_remaining);
my ($sensor_id, $m_type_id, $m_value);


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

my ($db_name, $db_user, $db_passwd, $dbh, $sql, $sth, @stored_record);

$db_name = $cfg->val('DB_WLS', 'db_name');
$db_user = $cfg->val('DB_WLS', 'db_user');
$db_passwd = $cfg->val('DB_WLS', 'db_passwd');

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

#date
$sensor_id = 26;
$m_type_id = 7;
$sql = qq{ SELECT m_date FROM multi_obs where m_type_id = $m_type_id and platform_id = $platform_id and sensor_id = $sensor_id order by m_date desc limit 1 };
$sth = $dbh->prepare( $sql );
$sth->execute();
#$rows_returned = $sth->rows;
#@stored_record = $sth->fetchrow_array;
@stored_record = $sth->fetchrow_array;
my $m_date = @stored_record[0];
#print $m_date."\n";



#DO_absolute
$sensor_id = 25;
$m_type_id = 6;
$sql = qq{ SELECT m_value FROM multi_obs where m_type_id = $m_type_id and platform_id = $platform_id and sensor_id = $sensor_id and m_date = '$m_date' };
$sth = $dbh->prepare( $sql );
$sth->execute();
@stored_record = $sth->fetchrow_array;
my $DO_absolute = @stored_record[0];

#DO_percent
$sensor_id = 24;
$m_type_id = 5;
$sql = qq{ SELECT m_value FROM multi_obs where m_type_id = $m_type_id and platform_id = $platform_id and sensor_id = $sensor_id and m_date = '$m_date' };
$sth = $dbh->prepare( $sql );
$sth->execute();
@stored_record = $sth->fetchrow_array;
my $DO_percent = @stored_record[0];


#water_temp
$sensor_id = 20;
$m_type_id = 1;
$sql = qq{ SELECT m_value FROM multi_obs where m_type_id = $m_type_id and platform_id = $platform_id and sensor_id = $sensor_id and m_date = '$m_date' };
$sth = $dbh->prepare( $sql );
$sth->execute();
@stored_record = $sth->fetchrow_array;
my $water_temp = sprintf("%.1f", @stored_record[0]*9/5+32);

#salinity
$sensor_id = 22;
$m_type_id = 3;
$sql = qq{ SELECT m_value FROM multi_obs where m_type_id = $m_type_id and platform_id = $platform_id and sensor_id = $sensor_id and m_date = '$m_date' };
$sth = $dbh->prepare( $sql );
$sth->execute();
@stored_record = $sth->fetchrow_array;
my $salinity = @stored_record[0];

#remaining_power
$sensor_id = 26;
$m_type_id = 7;
$sql = qq{ SELECT m_value FROM multi_obs where m_type_id = $m_type_id and platform_id = $platform_id and sensor_id = $sensor_id and m_date = '$m_date' };
$sth = $dbh->prepare( $sql );
$sth->execute();
@stored_record = $sth->fetchrow_array;
my $remaining_power = @stored_record[0];



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


open (HTML_OUT, ">/var/www/html/longbay/dynamic/SpringDO.html");

my $m_date_webformat = get_local_time($m_date); 
print HTML_OUT "<span class=\"label\">Last update</span> $m_date_webformat <br>";
print HTML_OUT "<span class=\"label\"><a href=\"./platform_graph_content.php?platform=3&interval=3_days#do_absolute\" target=\"new\">Dissolved Oxygen</a></span> $DO_absolute mg/L <br>";
print HTML_OUT "<span class=\"label\"><a href=\"./platform_graph_content.php?platform=3&interval=3_days#do_percent\" target=\"new\">Dissolved Oxygen(percent saturation)</a></span> $DO_percent % <br>";
print HTML_OUT "<span class=\"label\"><a href=\"./platform_graph_content.php?platform=3&interval=3_days#water_temp\" target=\"new\">Water Temperature</a></span> $water_temp F <br>";
print HTML_OUT "<span class=\"label\"><a href=\"./platform_graph_content.php?platform=3&interval=3_days#salinity\" target=\"new\">Salinity</a></span> $salinity ppt <br>";
print HTML_OUT "<span class=\"label\"><a href=\"./platform_graph_content.php?platform=3&interval=3_days#remaining_power\" target=\"new\">Battery Power Remaining(percent)</a></span> $remaining_power % <br>";
print HTML_OUT "<br>";

close (HTML_OUT);

exit 0;


sub get_local_time
{

my $date = shift;

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

my ($year, $month, $day, $hour, $minute, $second, $time_zone) = split(/[\/ :-]/,$date);

my $date_converted_1 = `date --date='$year-$month-$day $hour:$minute:$second +0000' +%s`;

my $date_converted_2 = `date -u -d '1970-01-01 $date_converted_1 seconds' +"%m/%d %r"`;

my $date_converted_3 = substr($date_converted_2,0,11).substr($date_converted_2,14,3);

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

        my $str_month = substr($date_converted_3,0,2);
        $str_month =~ s/^0+//;

        my $str_day = substr($date_converted_3,3,2);
        $str_day =~ s/^0+//;

        my $str_time = substr($date_converted_3,6,8);
        $str_time =~ s/^0+//;

	my $zone_display;
	if ($time_zone = -5 ) {$zone_display = 'EST';} else {$zone_display = 'EDT';}

return $str_month."/".$str_day." ".$str_time." ".$zone_display;

}