#!/usr/bin/perl
use strict;

use DBI;

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

my ($dbh, $sth, $sth_2, $sql, $sql_2);

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

#SpringDO
my $platform_id = 3;

#setup date month range for report based on calling argument
my $timestamp = $ARGV[0]; #like '200602' or 'YYYYMM'

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';

open(CSV_FILE, ">/usr2/prod/CSV/Springmaid_DO/Springmaid_DO_$timestamp.csv");

my $m_type_id = 1;
my $sensor_id = 20; 

my ($m_date, $m_value);
print CSV_FILE "measurement_date,water_temperature(celcius),salinity(psu),pressure(psia),dissolved_oxygen(percent saturation),dissolved_oxygen(mg/L),battery_remaining_power(percent)\n";
$sql = qq{ SELECT m_date,m_value FROM multi_obs where m_type_id = $m_type_id and m_date >= '$m_date_start' and m_date < '$m_date_end' and platform_id = $platform_id and sensor_id = $sensor_id order by m_date };
#print $sql;
$sth = $dbh->prepare( $sql );
$sth->execute();
$sth->bind_columns( undef,\$m_date,\$m_value );

while( $sth->fetch() ) {

print CSV_FILE "$m_date,$m_value";

#an array listing of pairs (m_type_id, sensor_id, ...)
my @obs = (3,22,4,23,5,24,6,25,7,26);

while (@obs) {

my $m_type_id = shift(@obs);
my $sensor_id = shift(@obs);
my $m_value_2;
#print $this_obs;

$sql_2 = qq{ SELECT m_value from multi_obs where m_type_id = $m_type_id and m_date = '$m_date' and platform_id = $platform_id and sensor_id = $sensor_id };
$sth_2 = $dbh->prepare( $sql_2 );
$sth_2->execute();
$sth_2->bind_columns( undef, \$m_value_2 );
$sth_2->fetch();

print CSV_FILE ",$m_value_2";

} #while ($obs)

print CSV_FILE "\n";

}

close(CSV_FILE);

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

exit 0;