#!/free/bin/perl 
#
my $scriptName = "v3cd.pl";
my $VERSION = "1.3";
my $VERSION_DATE = "2002-AUG-02";
#
# Revision history
# ===================================================================
#
# Version 1.1 04/28/02 Created (Charles Sun)
#
# Version 1.2 07/18/02
#
#   required db.pm version 1.2
#   fixed a typo in the LOGFILE header
#
# Version 1.3 08/02/02
#
#   Included checking datatype, 'DT', if needs depth correction.
#

use strict;
use DBI;
use lib "/usr/home/xyz/perlLib";

use GTSPP::db;
use GTSPP::proc;
GTSPP::db->require_version(1.2);
GTSPP::proc->require_version(1.1);

my $Mkey = 100;
my $mode='B';
my ($oyear,$ocean);
my ($usrObsMon,$usrObsDay,$usrObsYear);
my ($ostart,$oend,$startDate,$endDate);
my $output='gtspp.meds';
my $oformat='meds';
my $logFile='gtspp.log';
my $selectByObsDate = 0;
my $ldate;
my (@array,@options);

my ($station,$oneSq,$cruiseID,$obsYear,$obsMon,$obsDay,$obsTime,$dataType,
$iumsgno,$streamSource,$uFlag,$stnNumber,$latitude,$longitude,$qPos,
$qDateTime,$qRecord,$Update,$bulTime,$bulHeader,$sourceId,$streamIdent,
$qcVersion,$dataAvail,$noProf,$nParms,$nSurfc,$numHists,$loadEpoch,
$active,$dmode,$ocean);
my ($dpcParm,$peqParm,$pfrParm,$prtParm);

#
#define the default database server
#
my ($dbuser,$password);
my $dbserver = 'xyz1234.nodc.noaa.gov';
my $dbname = 'DEVELOP';
print "$scriptName $VERSION \n";
my $modulepath = "/usr/home/xyz12/pgms/Ldmodules";
open (CFGFILE, $modulepath . '/.rddb.cfg') ||
  die "Can't open configuration file: $!\n";
while (<CFGFILE>) {
  chomp;
  if (/^dbuser *\= *(\S+)$/)    {$dbuser = $1;}
  if (/^password *\= *(\S+)$/)  {$password = $1;}
}
close CFGFILE;
if (length($dbuser) == 0 || length($password) == 0) {
  die "Improper configuration file\n";
}

my $userid = (getpwuid($<))[0];                     #!!! UNIX
#
if (length($userid) > 8) {$userid = substr($userid, 0, 8);}
#
# Interpret Command Line
#
CMD: for (my $n = 0; $n <= $#ARGV; $n++) {
   if ($ARGV[$n] =~ /^-h$/ || $ARGV[$n] =~ /^-help$/){
print <<EOT;
<$scriptName $VERSION $VERSION_DATE>
usage: v3cd.pl
       -dbserver [xyz1234 (default) | xyz-abc]
       [-mode B (default) | R | D]
       [-oyear obs_year | -ostart obsDate(01-01-1990) -oend obsDate(01-31-1990)]
       [-ldate loadepoch_cut_off_Date (31-DEC-2001)]
       [-ocean all [| oceanArea(s)]]
       [-out output_filename]
       [-log logfile_filename]
       [-oformat meds (default) | csv]
EOT
   exit(0);
   }
  if ($ARGV[$n] =~ /^-dbserver$/i) {
      $dbserver = $ARGV[$n+1]; $ARGV[$n]=""; $ARGV[$n+1]=""; next CMD;}
  if ($ARGV[$n] =~ /^-mode$/i) {
      $mode = uc($ARGV[$n+1]);
     if ($mode ne 'B' ) {
         push(@options,"(dmode='$mode') ");} 
     else {
         push(@options,"(dmode='B') ");} 
     $ARGV[$n]=""; $ARGV[$n+1]=""; next CMD;}
  if ($ARGV[$n] =~ /^-ocean$/i) {
      $ocean = uc($ARGV[$n+1]);
      push(@options,"(ocean='$ocean') "); 
      $ARGV[$n]=""; $ARGV[$n+1]=""; next CMD;}
  if ($ARGV[$n] =~ /^-oyear$/i) {
      $oyear = $ARGV[$n+1]; 
      push(@options,"(obsYear='$oyear') ");
      $ARGV[$n]=""; $ARGV[$n+1]=""; next CMD;}
  if ($ARGV[$n] =~ /^-ldate$/i) {
      $ldate = $ARGV[$n+1]; 
      push(@options,"(loadEpoch <='$ldate') ");
      $ARGV[$n]=""; $ARGV[$n+1]=""; next CMD;}
  if ($ARGV[$n] =~ /^-ostart$/i) {
      $ostart = $ARGV[$n+1];
      $selectByObsDate = 1;
      ($usrObsMon,$usrObsDay,$usrObsYear) = split("-",uc($ostart));
      $startDate = "'".$usrObsYear.$usrObsMon.$usrObsDay."'";
      $ARGV[$n]=""; $ARGV[$n+1]=""; next CMD;}
  if ($ARGV[$n] =~ /^-oend$/i) {
      $oend = $ARGV[$n+1]; 
      $selectByObsDate = 1;
      ($usrObsMon,$usrObsDay,$usrObsYear) = split("-",uc($oend));
      $endDate = "'".$usrObsYear.$usrObsMon.$usrObsDay."'";
      $ARGV[$n]=""; $ARGV[$n+1]=""; next CMD;}
  if ($ARGV[$n] =~ /^-out$/i) {
      $output = $ARGV[$n+1]; $ARGV[$n]=""; $ARGV[$n+1]=""; next CMD;}
  if ($ARGV[$n] =~ /^-log$/i) {
      $logFile = $ARGV[$n+1]; $ARGV[$n]=""; $ARGV[$n+1]=""; next CMD;}
  if ($ARGV[$n] =~ /^-oformat$/i) {
      $oformat = $ARGV[$n+1]; $ARGV[$n]=""; $ARGV[$n+1]=""; next CMD;}
}

if (!$output) {$output="gtspp.meds";}
open (OUTFILE,">$output") || die "Couldn't open $output: error $!\n";
if (!$logFile) {$logFile="gtspp.log";}
open (LOGFILE,">$logFile") || die "Couldn't open $logFile: error $!\n";
print LOGFILE "Mkey,DBID,year,month,day,time,lat,lon(+W),".
              "datatype,dpcParm,dpcFlag,peq\$,pfr\$,prt\$,numProbeIDs\n";
if ($selectByObsDate)
{
   push (@options,
   "(CONCAT(CONCAT(obsYear,obsMon),obsDay) BETWEEN $startDate AND $endDate)");
}

if (lc($dbserver) =~ m/hokulea/) {
   $dbserver = 'hokulea.nodc.noaa.gov';
   $dbname = 'GTSPPcmd';
}

my $dbd = "dbi:Oracle:host=$dbserver;sid=$dbname";

#print "connecting $dbname at $dbserver\n";

my $dbh_GTSPP = DBI->connect($dbd, $dbuser, $password);

if (!$dbh_GTSPP)
{
   die &error("Couldn't connect to $dbname\: $DBI::errstr\n");
}

my $sql_station = "SELECT * FROM station WHERE (ACTIVE='Y') AND ";

$sql_station .= join("AND ",@options);

#print "SQL: $sql_station\n";

my $sth_station = $dbh_GTSPP->prepare($sql_station); $sth_station->execute();
while (@array = $sth_station->fetchrow)
{
   if ($mode =~ /B/) {
     my $sql2 = "select rStation from stationMatch where rStation = ".
                 "$array[0]";
      my $sth_stationMatch = $dbh_GTSPP->prepare($sql2);
      $sth_stationMatch->execute();
      while (my @stationMatch = $sth_stationMatch->fetchrow)
      {
         if (@stationMatch) {
#           printf LOGFILE "9,@stationMatch\n";
            goto NEXT_STATION;}
      }
   }
   my $dpcFlag=0; #default, don't need correction
   my $dpcParm='02'; #default, known probe type, don't need correction
   ($station, $oneSq, $cruiseID, $obsYear, $obsMon, $obsDay,
   $obsTime, $dataType, $iumsgno, $streamSource, $uFlag,
   $stnNumber, $latitude, $longitude, $qPos, $qDateTime,
   $qRecord, $Update, $bulTime, $bulHeader, $sourceId,
   $streamIdent, $qcVersion, $dataAvail, $noProf, $nParms,
   $nSurfc, $numHists, $loadEpoch, $active, $dmode, $ocean) = @array;
#
# determine if this station has been depth corrected
#
my $sql_srfc = "SELECT srfcCode,srfcParm,srfcQparm ".
      "FROM surfaceCodes WHERE station=$station" ;
my $sth_srfc = $dbh_GTSPP->prepare($sql_srfc); $sth_srfc->execute();
   while (my @array_srfc = $sth_srfc->fetchrow)
   {
      if ($array_srfc[0] =~m/DPC\$/) {
      $dpcFlag=0; #default, don't need correction
      $dpcParm='99'; # has been depth corrected by CSIRO
         goto DBRETRV;} #V1.2
   }
#
# determine if this station needs depth correction
#
   my $numProbeIDs = 0;
   if ($dataType eq 'XB' || $dataType eq 'BA' || $dataType eq 'DT') {
   ($peqParm,$pfrParm,$prtParm,$dpcFlag,$dpcParm,$numProbeIDs) =
       &ident_probe($station,$dbh_GTSPP);}
   else {$dpcFlag=0; $dpcParm='02';}
DBRETRV:;   
   dbRetrv($dbh_GTSPP,$Mkey,$station,$dpcFlag,$dpcParm,$output,$oformat);
   $latitude =~ s/ //g;
   $longitude =~ s/ //g;
   printf LOGFILE "$Mkey,$station,$obsYear,$obsMon,$obsDay,$obsTime,".
                  "$latitude,$longitude,$dataType,$dpcParm,".
                  "$dpcFlag,$peqParm,$pfrParm,$prtParm,$numProbeIDs\n";
   $Mkey = $Mkey + 100;
NEXT_STATION:;
} # end of  while (@array = $sth_station->fetchrow)
$dbh_GTSPP->disconnect();

close OUTFILE;
close LOGFILE;
exit(0);