#!/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

##</code><code>##

#!/usr/bin/perl
#use strict;
use warnings;
use DBI;
use Time::Piece;
use Time::Seconds;
use Data::Dumper;
use S::Utils qw(setOracleEnv);

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


my $filename = "all_dates.csv";

my %regular = (
  Monday =>   ['08:00 am','1:00 am'],
  Tuesday =>   ['08:00 am','1:00 am'],
  Wednesday =>   ['08:00 am','1:00 am'],
  Thursday =>   ['08:00 am','1:00 am'],
  Friday =>   ['08:00 am','10:00 pm'],
  Saturday =>   ['09:00 am','10:00 pm'],
  Sunday =>   ['11:00 am','1:00 am'],
);

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

my %except;
open my $fh, '<', 'except.csv' or die "Cannot open: $!";
while (my $line = <$fh>) {
  $line =~ s/\s*\z//;
  my @array = split /,/, $line;
  my $key = shift @array;
  $except{$key} = \@array;
}
close $fh;


my $t = localtime;
my $end_date = '2016-12-31';

open OUT,'>','all_dates.csv' or die "$!";
my $id = 1;
while ($t->ymd le $end_date){
  
  my $ymd   = $t->ymd;
  my $w     = $t->fullday;
  my $open  = $except{$ymd}[0] || $regular{$w}[0];
  my $close = $except{$ymd}[1] || $regular{$w}[1];
  my $is_closed = ($open eq '12: am') ? 1 : 0;
  
  print OUT "$id,$ymd,$w,$open,$close,$is_closed\n";
 
  $t += ONE_DAY;
  ++$id;
}
close OUT;



##</code><code>##

#!/usr/bin/perl

use DBI;
use S::Utils qw(setOracleEnv);
use strict;
use warnings;

# Declare variables
my $DBNAME   =  "calendar";
my $DBTABLE  =  "libhourstest";
my $csvfile  =  "all_dates.csv";

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

#connection info for db
my ($dsn,$dbun,$dbpass) = ( 'dbi:mysql:calendar:localhost:', '', '' );

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

### Get rid of the old records in the web content db table
    $dbh->do (qq{ delete from libhourstest });

my $update_h = $dbh->prepare(qq{INSERT INTO $DBTABLE (libhours_id, ymd, dow, opening, closing, is_closed) VALUES (?, ?, ?, ?, ?, ?)});

open my $ih, $csvfile or die "Can't open file, $csvfile: $!";

while (<$ih>) {
    chomp;
    
    my @rows = split ','; # A comma is the delimiter

    my $libhours_id  =  $rows[0];
    my $ymd   =  $rows[1];
    my $dow   =  $rows[2];
    my $opening   =  $rows[3];
    my $closing   =  $rows[4];
    my $is_closed   =  $rows[5];

    $update_h->execute($libhours_id, $ymd, $dow, $opening, $closing, $is_closed) or die $dbh->errstr;
}

my $res = $dbh->selectall_arrayref( q( SELECT libhours_id, ymd, dow, opening, closing, is_closed FROM libhourstest));

foreach( @$res ) {
    print "\n$_->[0], $_->[1] $_->[2] $_->[3] $_->[4] $_->[5]\n\n";
}

END {
 $dbh->disconnect if $dbh }