use LWP::Simple;
use Time::Local;
use Date::Calc qw(Decode_Date_US Now Today Delta_Days Delta_DHMS System_Clock);
use Win32::ODBC;
use Date::Manip qw(ParseDate DateCalc UnixDate);

use strict;

#-------------------------------------------
#-----Globals-------------------------------
#-------------------------------------------

my @Now;
my $URL;

my $content;
my @thelines;
my $nlines;
my @thewords;

my $DSN;
my $TelemOK;
my $cnTelem;

my $StationNumber;
my $StationNumber_lastletter;
my $Station;
my $Station_state;

my $TheRawDate;
my $TheRawTime;
my @TheRawTime_parts;
my $TheCorrectedDateTime;
my @WeirdTime;
my $TheDate;
my $TheHour;
my $TheMin;
my $TheSec;

my $WindDir;
my $WindAvg;
my $WindMax;
my $BPAvg;
my $TempAvg;
my $TempMax;
my $TempMin;
my $DewPoint;
my $WindDir;
my $WindAvg;
my $WindMax;

# \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

# Subroutines

sub SnapTo15 { 

   # This routine is used to snap any date/time string to the nearest 15
   # minutes. This is useful for insuring common timestamps when doing SQL
   # joins needed for the delta-p plot on Waconia. The only input is a
   # date/time string. For example:  "5/5/2000 23:16"; It returns one
   # date/time string of the format generally returned by ParseDate.

   use LWP::Simple;
   use Time::Local;
   use Date::Calc qw(Decode_Date_US Now Today);
   use Win32::ODBC;
   use Date::Manip qw(ParseDate DateCalc UnixDate Delta_Format);
   use POSIX qw(floor);

   use strict;

   my $ParsedDate;
   my $DeltaDate;
   my $DeltaInHours;
   my $DeltaIn15mins;
   my $Rounded;
   my $CorrectedHoursDelta;
   my $JustTheHoursDelta;
   my $JustTheMinsDelta;
   my $AdderString;
   my $TheFixedDate;

   # Here is the input parameter...
   my $TheDateAndTime = $_[0];

   # First generate a parsedate date string;

   $ParsedDate = Date::Manip::ParseDate($TheDateAndTime);
   #print "$ParsedDate\n";
   #print Date::Manip::ParseDate("1/1/2000"), "\n";

   # Calculate the difference between that date and a reference date, 1/1/2000;

   $DeltaDate = DateCalc(Date::Manip::ParseDate("1/1/2000"), $ParsedDate, 0);
   #print " The delta = $DeltaDate \n";

   # Convert that difference string into a delta in hours.

   $DeltaInHours = Date::Manip::Delta_Format($DeltaDate, 5, "%ht");
   #print " The delta in hours = $DeltaInHours \n";

   # And next to a difference in 15-min chunks of time;

   $DeltaIn15mins = $DeltaInHours * 4.0;
   #print " The delta in 15-mins = $DeltaIn15mins \n";

   # Now round that number to the nearest integer. This is the magical step
   # that causes the snap-to-15 event.

   $Rounded = sprintf("%.0f",$DeltaIn15mins);
   #print " The rounded version = $Rounded \n";

   # Now convert back to hours;

   $CorrectedHoursDelta = $Rounded / 4.0;
   #print " The rounded hours = $CorrectedHoursDelta \n";

   # Now calculate the pure hours and pure minutes parts 

   $JustTheHoursDelta = POSIX::floor($Rounded / 4.0); 
   #print "Just the hours = $JustTheHoursDelta \n";
   $JustTheMinsDelta = 60 * ($CorrectedHoursDelta - $JustTheHoursDelta);
   #print "Just the minutes = $JustTheMinsDelta \n";

   # Make an adder string (in hours and minutes) to use relative to the 1/1/2000 date;

   $AdderString = "+ " . $JustTheHoursDelta . "Hours " . $JustTheMinsDelta . "Minutes";
   #print " $AdderString \n";

   # Use the adder string to calcualate the fixed (snapped to 15) date;

   $TheFixedDate = Date::Manip::DateCalc("1/1/2000",$AdderString);
   #print "$TheFixedDate \n"; 

   return $TheFixedDate;
}

sub CheckForWeirdLoggerTimeStamps {

   use strict;

   # The arguments passed
   my $SnappedTime = $_[0];
   my $RawHour = $_[1];   # The first element in the array passed

   # Local variables
   my $CorrectedDateTime;
   my $DD; 
   my $DH; 
   my $DM; 
   my $DS;

   my $TheDate = UnixDate($SnappedTime, "%m/%d/%Y");  
   my $TheHour = substr($SnappedTime,8,2);
   my $TheMin = substr($SnappedTime,11,2);
   my $TheSec = substr($SnappedTime,14,2);
  
   my $DecimalDayDiff;

   # Check for weird time stamps (around midnight) that are used by some of the
   # HMS data loggers. Look at the difference between the system clock and the 
   # snapped time.

   ($DD,$DH,$DM,$DS) = Delta_DHMS(Today(), Now(), 
                                  Decode_Date_US($TheDate), $TheHour, $TheMin, $TheSec);   

   # This DecimalDayDiff calc should really be done after the time correction is made and account for
   # daylight savings time.
   $DecimalDayDiff = $DD + $DH/24.0 + $DM/(24.0*60.0);

   # If there is more than a 5 hour difference and its around midnight
   # it must be a one of the suspect loggers.

   if (  (abs($DH) > 5) && (($RawHour==23) || ($RawHour==24))   ) {
      $CorrectedDateTime = Date::Manip::DateCalc($SnappedTime,"- 1 days"); 
      print " Differences from system clock (d,h,m,s):  $DD,$DH,$DM,$DS  $CorrectedDateTime  \n";
      return ($CorrectedDateTime, $DecimalDayDiff);  # return an array with two elements...
   } else {
      return ($SnappedTime, $DecimalDayDiff);
   }
}

sub WriteToMDB {

   use strict;

   my $DateTimeStamp;
   my $LiteralDateTimeStamp;

   my $TheYear;
   my $TheMonth;
   my $TheDay;
   
   my $Waconia_Year;
   my $Waconia_Month;
   my $Waconia_Day;
   my $Waconia_Hour;
   my $Waconia_Min;
   my $Waconia_Sec;

   my $PerlTimeTxt;
   my $DateTimeStamp;
   my $LiteralDateTimeStamp;
   my $sql;

   my @SystemClock;
   my $NextDayDate;

   # Here is the HMS data date
   ($TheYear, $TheMonth, $TheDay) = Decode_Date_US($TheDate);

   # Here is the server date and time 
   ($Waconia_Year, $Waconia_Month, $Waconia_Day) = Today();
   ($Waconia_Hour, $Waconia_Min, $Waconia_Sec) = Now();

   #Timelocal requires that months start with zero.
   $PerlTimeTxt = timelocal(0,($Waconia_Min),($Waconia_Hour),($Waconia_Day),($Waconia_Month-1),$Waconia_Year);

   # Create the SQL statement for inserting a record of data into the mdb file.
  
   $DateTimeStamp = $TheMonth . "/" . $TheDay . "/" . $TheYear . " " . $TheHour . ":" . $TheMin;
   $LiteralDateTimeStamp = $TheRawDate . " " . $TheRawTime;

   
   # Check for bad values or no sensor report.
   # This block puts an unquoted Null in the SQL string if there is no sensor (or a bad reading). If there is a good value, it puts
   # the quoted value in the SQL string. Notice the lack of quotes for these variables in the VALUES string in the SQL below.
   
   
   # Check for extreme BPAvg values.
   if ( ($BPAvg eq "Null") || (($BPAvg < 29.0) || ($BPAvg > 31.0)) ) {
      $BPAvg = "Null";
   } else {
      $BPAvg = "'$BPAvg'";
   }
   
   # Check for extreme TempMin values. Do this numeric comparison
   # before turning these into quoted strings below.
   if ( ($TempMin eq "Null") || ( abs($TempAvg - $TempMin) > 20) ) {
      $TempMin = "Null";
   } else {
      $TempMin = "'$TempMin'";
   }
   
   # Put the value in quotes.
   if ($DewPoint ne "Null") {
      $DewPoint = "'$DewPoint'";
   }
   
   # Put the value in quotes.
   if ($TempAvg ne "Null") {
      $TempAvg = "'$TempAvg'";
   }
   
   # Null out the direction value for any calm readings.
   if ( ($WindAvg eq "0") && ($WindMax eq "0") ){
      $WindDir = "Null";
   } else {
      $WindDir = "'$WindDir'";
   }
   
   # Notice that some variables in the VALUES string are not quoted below. See block above for explanation.
   $sql = "INSERT INTO FifteenMinData (PerlTime, DateTimeStamp, LiteralDateTimeStamp, TimeMDY, TimeHr, TimeMin, StationNumber, ";
   $sql .= "StationName, WindDirection, WindSpeed, WindGust, ";
   $sql .= "TempAvg, TempMax, TempMin, Pressure, DewPoint) ";
   $sql .= "VALUES ('$PerlTimeTxt','$DateTimeStamp','$LiteralDateTimeStamp','$TheDate','$TheHour','$TheMin','$StationNumber',";
   $sql .= "'$Station',$WindDir,'$WindAvg','$WindMax',$TempAvg,'$TempMax',$TempMin, $BPAvg, $DewPoint)";

   # If all values are reasonable, write the record...
   if (  (($TempAvg >= -20) && ($TempAvg <=  120)) && 
         (($TempMax >= -20) && ($TempMax <=  120)) &&
         (($WindAvg >=   0) && ($WindAvg <=  110)) &&
         (($WindMax >=   0) && ($WindMax <=  130)) &&
         (($WindDir >=   0) && ($WindDir <=  360)) && 
         ( ($StationNumber ne "19a") ) &&
         ( ($StationNumber ne "6a") ) &&
         (($TheHour >= 0) && ($TheHour <= 23)) &&
         (($TheMin >= 0) && ($TheMin <= 59)) &&
         ($WeirdTime[1] < 100.00)   ) {  # Decimal day check for dead loggers that are dragging their time stamps into the next day...

         # The following checks were causing problems collecting records near midnight during 
         # daylight savings time...
         #($TheHour <= $Waconia_Hour) &&

      print "$TheDate, $TheHour, $TheMin, $StationNumber, $Station, WIND: $WindDir, $WindAvg, $WindMax, PRESSURE: $BPAvg, TEMP: $TempAvg, $TempMax, $TempMin, $DewPoint\n";
      #print "@WeirdTime[1]\n";


      # Execute the SQL. Write weather data to the database.
      
      # Error checking here: the SQL method returns undefined if it is
      # successful and a non-zero integer error number if it fails.

      if ($TelemOK) {
         if ($cnTelem->Sql($sql)) {
            print "  Waconia: SQL failed (probably the record already exists).\n";
            # If not the -1605 error message (duplicate records) then log it.
            if (!($cnTelem->Error() =~ /-1605/)) {
               print OUTPUTFILE localtime(time) . " Waconia SQL failure, Error: " . $cnTelem->Error() . ", SQL=" . $sql . "\n";
            }
         }
      }

      # Write out the TimeMDY date to a special table that is used to quickly populate the date combo box.
      # Check for special case of Daylight Savings time AND the PST hour of 23. 
      # Otherwise you won't be able to request a chart from midnight to 1am.
      # The ninth element in the returned array is a boolean for daylight savings time...
      
      @SystemClock = System_Clock(); 
      if (($SystemClock[8]) && ($TheHour==23)) {
         $NextDayDate = UnixDate( Date::Manip::DateCalc( $TheDate, "+ 1 days" ), "%m/%d/%Y"); 
         #print "Next Day = $NextDayDate\n";
         $sql = "INSERT INTO DaysGleaned (TimeMDY) VALUES ('$NextDayDate')";
      } else {
         $sql = "INSERT INTO DaysGleaned (TimeMDY) VALUES ('$TheDate')";
      }
      
      if ($TelemOK) {$cnTelem->Sql($sql);}

   } else {
      print "Record failed reasonable test\n";
      print "  $TheDate, $TheHour, $TheMin, $StationNumber, $Station, WIND: $WindDir, $WindAvg, $WindMax, PRESSURE: $BPAvg, TEMP: $TempAvg, $TempMax, $TempMin, $DewPoint\n";
   }
   
}

# \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

# Main Program Body

# Open the log file. Note the '$!' variable returns system errors.

@Now = localtime(time);

open(OUTPUTFILE, '>>C:\Users\Jim\Documents\webcontent\waconia\rosielog.txt') or do {
   print "Timestamp = @Now[5,4,3,2,1]\n";
   print "Could not find rosielog.txt\n";
   die "On open (Jdm): $!";
};  # This semicolon is important here....

print OUTPUTFILE localtime(time) . " from geturl.pl " . "\n";

# Get the page of data. Note the get function from 'LWP::Simple' returns undefined on error, so check for
# errors as follows....

#$URL = "http://hms.rl.gov/stainfo.htm";
#$URL = "http://hms.pnl.gov/stainfo.htm";
$URL = "http://www.hanford.gov/c.cfm/hms/realtime.cfm";

unless (defined ($content = get $URL)) {
   print OUTPUTFILE "Timestamp = @Now[5,4,3,2,1]\n";
   print OUTPUTFILE "Could not get $URL\n";
   close(OUTPUTFILE); 
   die "Could not get $URL\n";
}

# Split the page into lines
@thelines = split (/\n/, $content);
$nlines = @thelines;

# Make a database connection objects and point them at the mdb files. A future improvement here would
# be to set some flags so that only if both connections fail it exits. If there is at least one good
# connection, then proceed to the SQL execution, but only execute for the good connection.

$DSN = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:/Users/Jim/Documents/webcontent/waconia/data/telem.mdb;UID=admin";

$TelemOK = 1;
if (!($cnTelem = new Win32::ODBC($DSN))){
    print OUTPUTFILE "Timestamp = @Now[5,4,3,2,1]\n";
    print OUTPUTFILE "Error connecting to $DSN\n";
    print OUTPUTFILE "Error: " . Win32::ODBC::Error() . "\n";
    $TelemOK = 0;
    warn "Error connecting to $DSN\n";
}

# Note: I have left rain gauge out of this data collection script. It is hard
# to include because it is the last entry in each section and/but some sections
# don't have it at all. The list of those that don't have it are EDNA, FRNK,
# GABL, PFP, GABW, VERN, HAMR. The write statement has to trigger on something
# that is common to all sections. Otherwise that section will just not get
# printed out. To make this work you would have to extend the 2 tier code that
# groups on those that are like FFTP and those that are not, to a 3 tier
# grouping: (1) like FFTP, (2a) not like FFTP and with Rain gauge, (2b) not
# like FFTP and without Rain gauge. 

#print "$nlines \n";

$Station = "Null";

foreach (@thelines) {
   
   if (/Station\#/) { 
      @thewords = split(" ", $_);
      
      $StationNumber = $thewords[1];
      if ($StationNumber eq "19a"){
        # Remove the letter from StationNumber (i.e., the a).
        #$StationNumber_lastletter = chop($StationNumber)
        
        # Or better yet, just use 31, the old number for this site. Can't use 19 because the database
        # key includes the station number and 19 is already being used.
        $StationNumber = 31
      }
      
      if ($thewords[2] eq "(") { 
         $Station = substr($thewords[3],0,3) . " "; 
      } else {
         $Station = substr($thewords[2],1,4);
      }
      
      # Each time you find a new station section on the page initialize sensor values. Note that
      # not every sensor type is available at all stations, so it's important to set to Null here
      # in those cases.
      $BPAvg = "Null";
      $DewPoint = "Null";
      
      $TempAvg = "Null";
      $TempMin = "Null";
      
      # Some stations might be listed on the page but be decommissioned or temporarily down.
      # This flag can be used to make note if there is an indication given that it's not active.
      $Station_state = "ON"; 

   }

   if (/Time:/) {
      @thewords = split(" ", $_);
      $TheRawDate = $thewords[4];
      $TheRawTime = $thewords[1];

      # If there's not a problem with the timestamp, run SnapTo15..
      # Note that this check is applied here (to avoid errors in SnapTo15) and also
      # below in the next block...
      
      @TheRawTime_parts = split(":", $TheRawTime);       
      
      if ($TheRawTime_parts[0] ne "99") {
	 
         # Nudge the date/time to the nearest 15 minutes.

         $TheCorrectedDateTime = SnapTo15($TheRawDate . " " . $TheRawTime);

         @WeirdTime = CheckForWeirdLoggerTimeStamps($TheCorrectedDateTime, @TheRawTime_parts);
         $TheCorrectedDateTime = @WeirdTime[0]; # The first array element is the fixed time...

         $TheDate = UnixDate($TheCorrectedDateTime, "%m/%d/%Y");  
         $TheHour = substr($TheCorrectedDateTime,8,2);
         $TheMin = substr($TheCorrectedDateTime,11,2);
         $TheSec = substr($TheCorrectedDateTime,14,2);
      }
   }

   # Look for the strings that indicate whether or not station is collecting data.
   if ((/Temporarily Unavailable/) || (/Decommissioned/)) {$Station_state = "OFF"}
   
   # Don't process this station if it has a bad date or if it is OFF for some reason.
   if (($TheRawTime_parts[0] ne "99") && ($Station_state eq "ON")) {
      
      # This block does the main parsing by looking for defining strings in each line.
      
      # Split the line up by the spaces in it.
      @thewords = split(" ", $_);  
            
      # Divide stations into two categories. The first group is contains 3 stations 
      # that have more detailed reporting and use a slightly different nomenclature
      # because of sensors positioned at various heights.
      if (($Station eq "FFTF") || ($Station eq "300A") || ($Station eq "100A")) {
	
         if (/Ave Wind Direction 10m/) {
            $WindDir = $thewords[5];
            if ($WindDir eq "-999") { $WindDir = ""; }
            
         } elsif (/Ave Wind Speed 10m/) {
            $WindAvg = $thewords[5];
            if ($WindAvg eq "-999") { $WindAvg = ""; } 
            
         } elsif (/Max Wind Speed 10m/) {
            $WindMax = $thewords[5];
            if ($WindMax eq "-999") { $WindMax = ""; }
            
         } elsif (/Ave SLP/) {
            $BPAvg = $thewords[2];
            #print "Pressure reading at " . $Station . " = " . $BPAvg . "\n";
            
         } elsif (/Ave Temp 2m/) {
            $TempAvg = $thewords[4];
            $DewPoint = $thewords[8];
            
         } elsif (/Max Temp 2m/) {
            $TempMax = $thewords[4];
            
         } elsif (/Min Temp 2m/) {
            $TempMin = $thewords[4];
         
         }
      
      # This second group covers the remainder of the stations on the page.
      } else {
         if (/Ave Wind Direction/) {
            $WindDir = $thewords[4];
            if ($WindDir eq "-999") {$WindDir = ""; }
            
         } elsif (/Ave Wind Speed/) {
            $WindAvg = $thewords[4];
            if ($WindAvg eq "-999") {$WindAvg = ""; }
            
         } elsif (/Max Wind Speed/) {
            $WindMax = $thewords[4];
            if ($WindMax eq "-999") {$WindMax = ""; }
            
         } elsif (/Ave SLP/) {
            $BPAvg = $thewords[2];
            #print "Pressure reading at " . $Station . " = " . $BPAvg . "\n";

         } elsif (/Ave Temp/) {
            # This forces the first Ave Temp reading within each station group. Some
            # stations have second building, so this prevents that temperature reading
            # from being used.
            if ($TempAvg eq "Null") {$TempAvg = $thewords[3];}
            
            if ($Station eq "200E") {
               $DewPoint = $thewords[7];
            }
            
         } elsif (/Max Temp/) {
            $TempMax = $thewords[3]; 
            
         } elsif (/Min Temp/) {
            $TempMin = $thewords[3]; 
         
         } elsif (/Dew Point/) {
            # RMTN
            $DewPoint = $thewords[3]; 
         
         } elsif ((/DewPt/)&&(/RH/)) {
            # HMS
            $DewPoint = $thewords[2]; 
         
         }
         
      } # divide stations into two categories...  

      # Write to the database at the end of each station block.
      if ((/Return to Map/) && ($Station ne "Null")) {
         WriteToMDB;
         $Station = "Null";
      }   
      
   } # Check for bad date
} #For each line...

# Close the database and file connections

if ($TelemOK) {$cnTelem->Close();}
close(OUTPUTFILE);