#!/usr/bin/perl

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

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

# -- define outfile 
my $filename = "except.csv";

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


# -- connect to Voyager 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, 'YYYY-MM-DD'), 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 and hours
my %except = ();

# -- 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" }

  # Put data in hash
   $except{$exception_date} = {
     date  => $exception_date,
     open   => $exception_openhour,
     close  => $exception_closehour,
     };
}



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

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

# -- store table in here  
my $content;


# -- 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 %except ) {

  # -- date in exception hash so use exception data 
  # -- use sub to format time
  if ( exists $except{$key} ) {
    
    $except{$key}{open} = format_time( $except{$key}{open} );
    $except{$key}{close} = format_time( $except{$key}{close} );
        
  } 
  
# -- let's write the table rows 
  if ( $except{$key}{open} eq 'closed') {

    $content .= "$except{$key}{date},12: am,12: am \n";   
 
  } else {

    $content .= "$except{$key}{date},$except{$key}{open},$except{$key}{close} \n";

  }  ## end if
  
} ## end final hours for 

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

# -- close the html table and print all html to file 
open OUTFILE, "> $filename" or die $!; 
print OUTFILE $content;
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");
    return ("12:00 am") if $time == 2400;
   
    # -- 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