package GTSPP::db;
BEGIN
{
   use Exporter();
   @ISA = qw(Exporter);
   @EXPORT = qw(&dbRetrv &get_profileType);
   $VERSION = 1.2;
}

sub dbRetrv
{
my $dbh_GTSPP = shift;
my $Mkey = shift;
my $stn = shift;
my $dpcFlag = shift;
my $dpcParm = shift;
my $output = shift;
my $oformat = shift;

my (@array, @stationRecord, @profileRecord, @profileHeader);
my ($sql,$sth_);
my (@oceans,@stations,@callsigns,@tmp);
my (@ocean_s,@station_s,@callsign_s);

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 ($seq,$IdentCode,$prcCode,$version,$prc_date,$actCode,$actParm,$auxId,
$prevVal);
my ($profile,$profileType,$profileSeg,$noDepths,$d_p_Code);
my ($depthPress,$depresQ,$profParm,$profQparm);
my ($noseg,$profType,$dupFlag,$digitCode,$standard,$deepDepth);
my ($pCode,$parm,$qParm);
my ($srfcCode,$srfcParm,$srfcQparm);

my ($usrObsMon,$usrObsDay,$usrObsYear);
my ($startDate,$endDate); 

my $selectByObsDate = 0;
my $verbose = 0; 
my $verboseFlag = "off";
my $dataAvailFlag = "A";
my $sortKey = 0;

my $dbid;

# define station information field size
my @stn_fs = (8,10,4,2,2,4,2,12,1,1,8,8,9,1,1,1,8,12,6,4,4,4,1,2,2,2,3);
# define station information field justify indicator: 0 - L; 1 - R
my @stn_lr = (1,0,0,0,0,0,0,1,0,0,1,1,1,0,0,0,0,0,0,0,0,0,0,1,1,1,1);
#define profile information field size
my @prof_fs = (2,4,1,1,1,5);
#define profile information field justify indicator; 0 - L; 1 - R
my @prof_lr = (1,0,0,0,0,1);
#define surface parameter group field size
my @sfc_parm_fs = (4,10,1);
#define surface parameter group field justify indicator; 0 - L; 1 - R
my @sfc_parm_lr = (0,1,0);
#define surface codes group field size
my @sfc_code_fs = (4,10,1);
#define surface codes group field justify indicator; 0 - L; 1 - R
my @sfc_code_lr = (0,0,0);
#define history group field size
my @hist_fs= (2,4,4,8,2,4,8,10);
#define surface codes group field justify indicator; 0 - L; 1 - R
my @hist_lr = (0,0,0,1,0,0,1,1);
#define profile record field size
my @prof_rec_fs= (8,10,4,2,2,4,2,12,4,2,4,1);
#define profile record field justify indicator; 0 - L; 1 - R
my @prof_rec_lr= (1,0,0,0,0,0,0,1,0,1,1,0);
#define parameter group field size
my @parm_grp_fs= (6,1,9,1);
#define parameter group field justify indicator; 0 - L; 1 - R
my @parm_grp_lr= (1,0,1,0);

#
# check if the $output file exists
#
if (-e $output) {open (OUTFILE,">>$output") || 
                    die "Couldn't open $output: error $!\n";}
{

$sql = "SELECT * from station WHERE (ACTIVE='Y') AND station = $stn";

#print "The SQL statement is \"$sql\"\n";

my $sth_station = $dbh_GTSPP->prepare($sql); $sth_station->execute();

while (@array = $sth_station->fetchrow)
{
   $sortKey++;
   $array[1] =~ s/ //g;# remove white spaces
   foreach (@array[3..27])  { $_ =~ s/ //g;} # remove white spaces
   ($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;

   my $i=0;
   my $line = '';
#  if ($dpcFlag) {
#  print "dpcFlag: $dpcFlag\n";
#
# increase the number of surface code group by two, if $dpcParm=04
#
   if ($dpcParm eq "01") {$array[26]=$array[26]+1;}
   if ($dpcParm eq "02") {$array[26]=$array[26]+1;}
   if ($dpcParm eq "03") {$array[26]=$array[26]+1;}
   if ($dpcParm eq "04") {$array[26]=$array[26]+2;}
   $nSurfc = $array[26];
#
# increase the number of the history group by one
#
   $array[27]++; 
   $numHists = $array[27];
#  }
   foreach (@array[1..27])
   {
      my $tmp=' 'x$stn_fs[$i];
      if ($stn_lr[$i] == 0)
         {substr($tmp,0,length($_))=$_;} #Left Justified
      else
         {substr($tmp,-length($_),length($_))=$_;} #Right Justified
      $line .= $tmp;
      $i++;
   } # end of foreach (@array[1..27])

#
# write out the first component of the station record, @stationRecord.
#
   if (lc($oformat) eq "meds") {
   if ($verbose) {printf "%08d",$Mkey; print $line;}
   printf OUTFILE "%08d",$Mkey; print OUTFILE $line;}
   else {
   @stationRecord = join(',',@array[1..27]);
   if ($verbose) {printf "%08d,",$Mkey; print @stationRecord;}
   printf OUTFILE "%08d,",$Mkey; print OUTFILE @stationRecord;}

#printf "%08d",$Mkey; print $line;

#
# retrieve profile information from Table nProfs. The "seq" field indicates
# the profile number.
#
   $sql = "SELECT noseg,profType,dupFlag,digitCode,standard,".
      "deepDepth FROM nProfs WHERE station=$station" ;  

   my $sth_nProfs = $dbh_GTSPP->prepare($sql); $sth_nProfs->execute();
   while (@array = $sth_nProfs->fetchrow)
   {
      foreach (@array)  { $_ =~ s/ //g;} # remove white spaces
      my $i=0;
      my $line = '';
      if ($dpcFlag) {
         $array[5] = $array[5]*1.0336;
         $array[5] = sprintf "%.0f",$array[5];}
      foreach (@array)
      {
         my $tmp=' 'x$prof_fs[$i];
         if ($prof_lr[$i] == 0)
         {substr($tmp,0,length($_))=$_;} #Left Justified
         else
         {substr($tmp,-length($_),length($_))=$_;} #Right Justified
         $line .= $tmp;
        $i++;
      } # end of foreach (@array)
#
# write out the second component of the station record.
# repeats No_prof(noProf) times (1-30)
#
   if (lc($oformat) eq "meds") {
     if ($verbose) {print $line;}
     print OUTFILE  $line;}
   else {
     @stationRecord = join(',',@array);
     if ($verbose) {print ",",@stationRecord;}
     print OUTFILE  ",",@stationRecord;}
#print $line;
   } # end of while (@array = $sth_nProfs->fetchrow)
   $sth_nProfs->execute(); $sth_nProfs->finish();

if ($nParms != 0) 
{   
#
# retrieve the third component of the station record from 
# the surfaceParms table.
#
   $sql = "SELECT pCode,parm,qParm ".
      "FROM surfaceParms WHERE station=$station" ;  
   $sth_ = $dbh_GTSPP->prepare($sql); $sth_->execute();
   while (@array = $sth_->fetchrow)
   { 
#     foreach (@array)  { $_ =~ s/ //g;} # remove white spaces
      my $i=0;
      my $line = '';
      foreach (@array)
      {
         my $tmp=' 'x$sfc_parm_fs[$i];
         if ($sfc_parm_lr[$i] == 0)
         {substr($tmp,0,length($_))=$_;} #Left Justified
         else
         {substr($tmp,-length($_),length($_))=$_;} #Right Justified
         $line .= $tmp;
         $i++;
      }
#
# write out the third component of the station record.
# repeats nParms times (0-30)
#
   if (lc($oformat) eq "meds") {
      if ($verbose) {print $line;}
      print OUTFILE $line;}
   else {
     @stationRecord = join(',',@array);
     if ($verbose) {print ",",join(',',@array);}
     print OUTFILE  ",",join(',',@array);}
#print $line;
   } # end of  while (@array = $sth_->fetchrow)
   $sth_->execute(); $sth_->finish();
} # end of if (!nParms) 

if ($nSurfc != 0) 
{   
# retrieve the fourth component of the station record from 
# the surfaceCodes table

   $sql = "SELECT srfcCode,srfcParm,srfcQparm ".
      "FROM surfaceCodes WHERE station=$station" ;  
   $sth_ = $dbh_GTSPP->prepare($sql); $sth_->execute();
   while (@array = $sth_->fetchrow)
   {   
#     foreach (@array)  { $_ =~ s/ //g;} # remove white spaces
      if ($array[0] =~m/PEQ$/ && $dpcParm eq "04") {$array[1] += 1;} #V1.2
      if ($array[0] =~m/PFR$/ && $dpcParm eq "04") {$array[1] += 1;} #V1.2
      my $i=0;
      my $line = '';
      foreach (@array)
      {
         my $tmp=' 'x$sfc_code_fs[$i];
         if ($sfc_code_lr[$i] == 0)
         {substr($tmp,0,length($_))=$_;} #Left Justified
         else
         {substr($tmp,-length($_),length($_))=$_;} #Right Justified
         $line .= $tmp;
         $i++;
      }
#
# write out the fourth component of the station record.
# repeats nSurfc times (0-30)
#
   if (lc($oformat) eq "meds") {
      if ($verbose) {print $line;}
      print OUTFILE $line;}
   else {
     @stationRecord = join(',',@array);
     if ($verbose) {print ",",join(',',@array);}
     print OUTFILE  ",",join(',',@array);}
#print $line;
   } #end of while (@array = $sth_->fetchrow)
   $sth_->execute(); $sth_->finish();

   if (lc($oformat) eq "meds") {
      if ($dpcParm eq "01") {print OUTFILE "DPC\$".$dpcParm."        2";}
      if ($dpcParm eq "02") {print OUTFILE "DPC\$".$dpcParm."        2";}
      if ($dpcParm eq "03") {print OUTFILE "DPC\$".$dpcParm."        2";}
      if ($dpcParm eq "04") {print OUTFILE "DPC\$".$dpcParm."        2FRA\$"."1.03362   2";}
      }
   else {
      if ($dpcParm eq "01") {print OUTFILE "DPC\$,$dpcParm,2";}
      if ($dpcParm eq "02") {print OUTFILE "DPC\$,$dpcParm,2";}
      if ($dpcParm eq "03") {print OUTFILE "DPC\$,$dpcParm,2";}
      if ($dpcParm eq "04") {print OUTFILE "DPC\$,$dpcParm,2"."FRA\$,1.03362,2";}
      }
 } # end of if ($nSurfc != 0) 

#print "$line\n";

my $year ='0000';
my $mon   ='00';
my $day   ='00';

my $year_ = (localtime)[5]+1900;
my $mon_ = (localtime)[4]+1;
my $day_ = (localtime)[3];
{substr($year,-length($year_),length($year_))=$year_;} #Right Justified
{substr($mon,-length($mon_),length($mon_))=$mon_;} #Right Justified
{substr($day,-length($day_),length($day_))=$day_;} #Right Justified

if ($numHists != 0) 
{   
#print "numHists= $numHists\n";
# retrieve the fifth component of the station record from 
# the stationHistory table

   $sql = "SELECT IdentCode,prcCode,version,prc_date,actCode,".
      "actParm,auxId,prevVal ".
      "FROM stationHistory WHERE station=$station" ;  
   $sth_ = $dbh_GTSPP->prepare($sql); $sth_->execute();
   while (@array = $sth_->fetchrow)
   { 
#    foreach (@array)  { $_ =~ s/ //g;} # remove white spaces
     my $i=0;
     my $line = '';
     foreach (@array)
     {
     my $tmp=' 'x$hist_fs[$i];
     if ($hist_lr[$i] == 0)
        {substr($tmp,0,length($_))=$_;} #Left Justified
     else
        {substr($tmp,-length($_),length($_))=$_;} #Right Justified
     $line .= $tmp;
     $i++;
     } #end of foreach (@array) 

#
# write out the fifth component of the station record.
# repeats numHists times (0-100)
#
   if (lc($oformat) eq "meds") {
   if ($verbose) {print $line;}
      print OUTFILE $line;}
   else {
     if ($verbose) {print ",",join(',',@array);}
     print OUTFILE  ",",join(',',@array);}
#print $line;
   } # end of while (@array = $sth_->fetchrow)
   $sth_->execute(); $sth_->finish();
} #end of if ($numHists != 0) 
   
   if (lc($oformat) eq "meds") {
     if ($dpcParm eq "04") {
     print OUTFILE "NOv3cd1.2 ".$year.$mon.$day."DPDEPH9999.999  9999.999";}
     else {
     print OUTFILE "NOv3cd1.2 ".$year.$mon.$day."CRRCRD9999.999  9999.999";}
     }
   else {
     if ($dpcParm eq "04") {
     print OUTFILE "NOv3cd1.2,$year$mon$day,DP,DEPH,9999.999,9999.999";}
     else {
     print OUTFILE "NOv3cd1.2,$year$mon$day,CR,RCRD,9999.999,9999.999";}
     }

   if ($verbose) {print "\n";}
   print OUTFILE "\n";
#print "\n";
#
# retrieve profile record from the profile table
#

   $sql = "SELECT profile,profileType,profileSeg,noDepths,d_p_Code ".
      "FROM profile WHERE station=$station";
   my $sth_profile = $dbh_GTSPP->prepare($sql); $sth_profile->execute();

   while (@array = $sth_profile->fetchrow)
   { 

   ++$Mkey;

      foreach (@array)  { $_ =~ s/ //g;} # remove white spaces
      my $profileNo = $array[0];
      @profileHeader = ($oneSq,$cruiseID,$obsYear,$obsMon,$obsDay,$obsTime,
          $dataType,$iumsgno,@array[1..4]);

#
# write out first component of the profile record
#
   if (lc($oformat) eq "meds") {
      my $i=0;
      my $lineHeader = '';
      foreach (@profileHeader)
      {
         my $tmp=' 'x$prof_rec_fs[$i];
         if ($prof_rec_lr[$i] == 0)
         {substr($tmp,0,length($_))=$_;} #Left Justified
         else
         {substr($tmp,-length($_),length($_))=$_;} #Right Justified
         $lineHeader .= $tmp;
         $i++;
      } #end of foreach (@profileHeader)
      if ($verbose) {printf "%08d",$Mkey; print $lineHeader; }
         printf OUTFILE "%08d",$Mkey; print OUTFILE $lineHeader;
#printf "%08d",$Mkey; print $lineHeader;
      }
      else {
         foreach (@profileHeader)  { $_ =~ s/ //g;} # remove white spaces
         @profileHeader = join(',',@profileHeader); 
         if ($verbose) {printf "%08d,",$Mkey; print @profileHeader; }
         printf OUTFILE "%08d,",$Mkey; print OUTFILE @profileHeader;
#printf "%08d",$Mkey; print @profileHeader;
      }

   $sql = "SELECT profile,depthPress,depresQ,profParm,profQparm ".
      "FROM observation WHERE (profile=$profileNo) AND (station=$station)";
   my $sth_observation = $dbh_GTSPP->prepare($sql); $sth_observation->execute();

#my $sequence; my $sequence_old=0;

   while (@array = $sth_observation->fetchrow)
   {
      if ($dpcFlag) {
         $array[1] = $array[1]*1.0336;
         $array[1] = sprintf "%.1f",$array[1];}
      foreach (@array)  { $_ =~ s/ //g;} # remove white spaces
      my $i=0;
      my $line = '';
      foreach (@array[1..4])
      {
         my $tmp=' 'x$parm_grp_fs[$i];
         if ($parm_grp_lr[$i] == 0)
            {substr($tmp,0,length($_))=$_;} #Left Justified
         else
            {substr($tmp,-length($_),length($_))=$_;} #Right Justified
         $line .= $tmp;
         $i++;
      }

   if (lc($oformat) eq "meds") {
      if ($verbose) {print $line;}
      print OUTFILE $line;}
   else {
      my @obsarray = join(',',@array[1..4]);
      if ($verbose) {print ",",@obsarray;}
      print OUTFILE ",",@obsarray;     
   }  
   }
   $sth_observation->execute(); $sth_observation->finish();

   if ($verbose) {print "\n";}
   print OUTFILE "\n";

   }
   $sth_profile->execute(); $sth_profile->finish();
   $Mkey = ($sortKey+1)*100;

} #end of while (@array = $sth_station->fetchrow)
$sth_station->execute(); $sth_station->finish();

}
return 0;
}

sub get_profileType
{

my $dbh = shift;
my $station = shift;

my ($profile,$profileType,$profileSeg,$noDepths,$d_p_Code);
my (@array,@profileTypes);
my $noProf;
my $sql = "SELECT noProf FROM station WHERE station=$station";
my $sth = $dbh->prepare($sql); $sth->execute();

while (@array = $sth->fetchrow)
{
   ($noProf)=@array;
}
$sth->execute(); $sth->finish();
my $sql = "SELECT profileType FROM profile WHERE station=$station";
my $sth = $dbh->prepare($sql); $sth->execute();
while (@array = $sth->fetchrow)
{
    push(@profileTypes,"@array");
}
$sth->execute(); $sth->finish();
@profileTypes=join(',',@profileTypes);
return $noProf,@profileTypes;
}
return 1;
END { }