root/docs/usc/nautilus/usr2/prod/buoys/perl/xenia/processServiceNIDB.pl

#!/usr/bin/perl

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

########################################################
####config specific

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

# See note below about what to expect from this DB.
my $xp_env = XML::LibXML->new->parse_file("../environment_xenia_$env.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 $path_psql = $xp_env->findvalue('//path/psql');

#note $m_date is particular to the file setup
#note the @platform and @obs which are specific to the file_type and setup

####

#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_add;
if ($isdst) {$time_add = 4; } else {$time_add = 5; }

#establish database connection
my ($dbh,$sth,$sql);
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";}

########################################################
#process platforms same one by one, xml->sql->database

my @platform = qw(1 niwolmet met
                2 niwolwq water);

#my @platform = qw(2 niwolwq water);

while (@platform) {

my $platform_id = shift(@platform);
my $cdmo_id = shift(@platform);
my $file_type = shift(@platform);

$sql = qq{ select platform_handle,fixed_longitude,fixed_latitude from platform where row_id = $platform_id };
#print "sql:".$sql."\n";
$sth = $dbh->prepare( $sql );
$sth->execute();
if ($sth->rows == 0) { next; }
my ($platform_handle,$m_lon,$m_lat) = $sth->fetchrow_array;

my $filename = './tmp/cdmo_'.$cdmo_id;
my $sql_out = $filename.'.sql';
open (SQL_OUT,">$sql_out");

########################################################
#get rid of extra unnecessary header and footer lines with namespaces that confuse LibXML

open (FILE, $filename.'.txt');

my $line_feed = 0;
my $content = '';
foreach my $line (<FILE>) {     
        if (!($line =~ /nds/) && ($line_feed == 0)) { next; } else { $line_feed = 1; }
        $content .= $line;
        if ($line =~ /\/nds/) { last; }
}

close (FILE);

open (FILE_OUT,">$filename".'.xml');
print FILE_OUT $content;
close(FILE_OUT);

########################################################
#process the clean xml document into SQL statements

my $xp = XML::LibXML->new->parse_file($filename.'.xml');

#foreach my $element ($xp->findnodes('/soapenv:Envelope/soapenv:Body/ns1:exportAllParamsXMLResponse/exportAllParamsXMLReturn/exportAllParamsXMLReturn/nds/data/r/c/@v')) {
#foreach my $element ($xp->findnodes('//Envelope/Body/exportAllParamsXMLResponse/exportAllParamsXMLReturn/nds/data/r/c/@v')) {

my $line_count = 0;
foreach my $row ($xp->findnodes('//data/r')) {
        $line_count++;
        if ($line_count < 2) { next; }

        print "###################\n";

        #possible vars used
        my @obs = ();
        my $m_date;
        my ($row_id,$historical);
        my ($air_temp,$air_temp_qc,$rh,$rh_qc,$air_pressure,$air_pressure_qc,$wind_speed,$wind_speed_qc,$wind_from_direction,$wind_from_direction_qc,$precipitation,$precipitation_qc,$solar,$solar_qc);
        my ($water_temp,$water_temp_qc,$water_conductivity,$water_conductivity_qc,$salinity,$salinity_qc,$do_percent,$do_percent_qc,$do_mgl,$do_mgl_qc,$water_level,$water_level_qc,$ph,$ph_qc,$turbidity,$turbidity_qc);

        #push attribute values into array and then array into correct variables
        my @values = ();
        foreach my $element ($row->findnodes('c/@v')) { push (@values, $element->string_value()); }

        ##file types
        if ($file_type eq 'met') {
                ($row_id,$historical,$m_date,$air_temp,$air_temp_qc,$rh,$rh_qc,$air_pressure,$air_pressure_qc,$wind_speed,$wind_speed_qc,$wind_from_direction,$wind_from_direction_qc,$precipitation,$precipitation_qc,$solar,$solar_qc) = @values;
                print "$air_temp\n";
                @obs = (1,$air_temp,$air_temp_qc,3,$rh,$rh_qc,4,$air_pressure,$air_pressure_qc,5,$wind_speed,$wind_speed_qc,6,$wind_from_direction,$wind_from_direction_qc,7,$precipitation,$precipitation_qc,8,$solar,$solar_qc);
        }
        if ($file_type eq 'water') {
                ($row_id,$historical,$m_date,$water_temp,$water_temp_qc,$water_conductivity,$water_conductivity_qc,$salinity,$salinity_qc,$do_percent,$do_percent_qc,$do_mgl,$do_mgl_qc,$water_level,$water_level_qc,$ph,$ph_qc,$turbidity,$turbidity_qc) = @values;
                print "$water_temp\n";
                @obs = (2,$water_temp,$water_temp_qc,9,$water_conductivity,$water_conductivity_qc,10,$salinity,$salinity_qc,11,$do_percent,$do_percent_qc,12,$do_mgl,$do_mgl_qc,13,$water_level,$water_level_qc,14,$ph,$ph_qc,15,$turbidity,$turbidity_qc);
        }

        $m_date = '20'.substr($m_date,6,2).'-'.substr($m_date,0,2).'-'.substr($m_date,3,2).' '.substr($m_date,9,5).':00';
        print "$m_date\n";

        while (@obs) {

                my $sensor_id = shift(@obs);
                my $m_value = shift(@obs);
                my $qc_level = shift(@obs);

                $sql = qq{ select m_type_id,fixed_z from sensor where row_id = $sensor_id };
                #print "sql:".$sql."\n";
                $sth = $dbh->prepare( $sql );
                $sth->execute();
                if ($sth->rows == 0) { next; }
                my ($m_type_id,$m_z) = $sth->fetchrow_array;

                print SQL_OUT "INSERT INTO multi_obs (row_id,row_entry_date,row_update_date,platform_handle,sensor_id,m_type_id,m_date,m_lon,m_lat,m_z,m_value,qc_level) VALUES (nextval('multi_obs_row_id_seq'),now(),now(),'$platform_handle',$sensor_id,$m_type_id,timestamp '$m_date' + interval '$time_add hours',$m_lon,$m_lat,$m_z,$m_value,$qc_level);\n";

        } #while @obs

} #foreach $row

close (SQL_OUT);

`$path_psql -U $db_user -d $db_name -h $db_host -f $sql_out`;

} #while (@platform) {

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

exit 0;