#!/usr/bin/perl

use strict;
use warnings;
use DBI;
use Time::Format qw(%time time_format %strftime %manip);
use Data::Dumper;
use Skidmore::Utils qw(setOracleEnv);

# -- call sub to set Oracle env so script can run as cronjob
&setOracleEnv;

# -- define outfile 
my $htmlout = "/www/includes/lib_except_hours.html";

# -- connection info for  db  
my ($voy_dsn,$voy_dbun,$voy_dbpass) = ( 'dbi:Oracle:***','***','***' ); 


# -- connect to  db
my $dbh = DBI->connect( $voy_dsn,$voy_dbun,$voy_dbpass, { AutoCommit => 0, RaiseError => 0, PrintError => 1 } ) || die $DBI::errstr; 



# -- query to get exception dates for the semester  
my $sql = qq(select TO_CHAR (exception_date, 'DY/MM/DD/YYYY'), exception_openhour, exception_closehour,
CASE when UPPER(Exception_open) = 'N' THEN 'Closed'
END AS Closed
FROM exception_calendar);
 

my $sth = $dbh->prepare( $sql );
$sth->execute or die $DBI::errstr;


# -- fetch all data and create var to refer to   
my $arrayref_exc_dates = $sth->fetchall_arrayref();

# -- hash to store found exception dates
my %fnd_excep = ();

# -- loop thru the exception data from Voyager table
foreach my $row ( @$arrayref_exc_dates ) {
  
  my ( $exception_date,$exception_openhour,$exception_closehour ) = @$row;
  if ( not defined $exception_openhour ) { $exception_openhour = "closed" }
  if ( not defined $exception_closehour ) { $exception_closehour = "closed" }

}


# -- uncomment this for testing
#print Data::Dumper->Dump([%fnd_excep]); 

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

# -- store html in here  
my $content;

# -- begin html code
$content = qq(<p sizcache="4" sizset="3"><strong><br /><br /></strong></p>
<p style="text-align: left;"><strong><em></em></strong></p>
<p style="text-align: left;"><strong> <a name="exceptions"></a>Exceptions to Regular Hours</strong></p>);
$content .= qq(<table style="width: 600px;" border="1" cellpadding="5"><tbody style="text-align: left;">
<tr style="text-align: left;"><td style="text-align: left;"><strong>Day/Date</strong></td>
<td style="text-align: left;"><strong>Times</strong></td>);

# -- store exception hours in here 
my %except_hrs = (); 


# -- now we can figure out the hours to use and create the table rows 
for my $key ( sort keys %fnd_excep ) {

  # -- date in exception hash so use exception data 
  # -- use sub to format time
  if ( exists $fnd_excep{$key} ) {
    
    $fnd_excep{$key}{open} = format_time( $fnd_excep{$key}{open} );
    $fnd_excep{$key}{close} = format_time( $fnd_excep{$key}{close} );
        
  # -- date not in exception hash so use normal hours
  } else {
    
    $fnd_excep{$key}{open} = format_time( $except_hrs{$key}{open} );
    $fnd_excep{$key}{close} = format_time( $except_hrs{$key}{close} );
    
  }  ## end exception open/closed if
  
  # -- let's write the table rows 
  if ( $fnd_excep{$key}{open} eq 'closed') {

    $content .= "<tr><td>$fnd_excep{$key}{day_text}</td><td>Closed</td></tr>";   
 
  } else {

    $content .= "<tr><td>$fnd_excep{$key}{day_text}</td><td>$fnd_excep{$key}{open} - $fnd_excep{$key}{close}</td></tr>";

  }  ## end if 

}  ## end final hours for 

# -- uncomment for testing
print Data::Dumper->Dump([%fnd_excep]);  

# -- close the html table and print all html to file 
open OUTFILE, ">$htmlout" or die $!; 
print OUTFILE $content .= qq(</tbody></table>);
close OUTFILE;

# -- Subs start here
# --
sub format_time {
 
  my $time = shift;
  my $formatted_time; 
 
  # -- handle if 'closed' passed (don't do anything else)
  if ( $time =~m/closed/ ) { 
      
     $formatted_time = $time;
  
  } else {   
     
    # -- Voyager system indicates morning of "same" open day as 24 + hour, ex: 1:00AM = 2500
    # -- must change to more standard time string
    if ( $time > 2400 ) { 

      $formatted_time = $time - 2400; 

    } else { 

      $formatted_time =  $time;

    }  ## end check time over 2400 if
 
    # -- time function (from Time module) needs time string in this format: hh:mm:ss
    # -- our time string is like 800 or 2300 so we must do some formating to it  
    
    # -- first, pad with zero if only 3 character
    $formatted_time = sprintf( "%04s", $formatted_time );
    # -- second, add two zeros to end of time string - seconds
    $formatted_time = $formatted_time .'00';
    # -- third, add ":" separators 
    $formatted_time =~ s/(\d\d)(\d\d)(\d\d)/$1:$2:$3/;
    # -- now use time function for final formatting  
    $formatted_time = time_format('H:mm am', "$formatted_time");
    
    # -- for some reason "0" showing in times less than 10 even though format for time format looks correct 
    # -- let's remove it now
    $formatted_time =~ s/^0//;

  }   ## end check time if
 
 # -- return formatted time string
 return $formatted_time;

}   ## end format time sub