Hans Castorp has asked for the wisdom of the Perl Monks concerning the following question:

Greetings Monks:

I need to write a script that will pull hours of operation from two tables in our integrated library system--one table has our regular hours and consists of the Days of the week, Open Time, and Close Time. The other table is an exception table that lists all the exceptions to our regular hours and consists of Dates, Open Time, and Close Time.

What I need to do is pull the data from both of these tables and write a new table that combines the regular and exception hours of operation. The columns for that table would be libhours_id, yearmonthday (ymd), dayofweek (dow), opening, closing, is_closed (value of 1 or 0).

I have Date::Calendar, Date::Calc, Date::Calendar::Year Perl modules.

Would anyone be willing to tell me what procedures I need to learn to use in order to do this?

Thank you,
HC

EDIT

Today, Leap Day 2016, I figured it out! :-) I have not implemented it yet, however, because I think it would be a good thing to preserve at least a couple weeks of our calendar while writing over the rest each night. Does anyone have a good idea about how to do that? At any rate, working from poj's sample code and previous scripts and some actual writing of my own (!) I came up with three scripts--perhaps not the most elegant solution, but it works.

First, I run a script that gathers the exception hours, changes them to standard time, and puts them into a csv file. Closed dates hours are 12: am to 12: am.

#!/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 = "clos
+ed" }
  if ( not defined $exception_closehour ) { $exception_closehour = "cl
+osed" }

  # 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 + h
+our, 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 fo
+rmat: hh:mm:ss
    # -- our time string is like 800 or 2300 so we must do some format
+ing 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

[download]

Then, another script, based on poj's code, that pulls in the except hours and puts them in a hash, which is then combined with the regular hours and output into another csv file that contains all hours until December 31, 2016.

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

[download]

Then, another script wipes out the existing data in the hours table and inserts the new hours.

#!/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, RaiseEr
+ror => 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, o
+pening, closing, is_closed FROM libhourstest));

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

END {
 $dbh->disconnect if $dbh }

[download]

Once the data is in the table, a php script prints it to the analog calendar on our website.

Does anyone have an idea for a simple way to keep a couple weeks of previous dates in the table?

Thank you so much! This is awesome, and I hope maybe will help someone else someday!
HC

Replies are listed 'Best First'.
Re: Pull data from two tables, insert into new table?
by NetWallah (Canon) on Feb 16, 2016 at 18:05 UTC
    Why don't you leverage the database engine, which is usually spectacularly good at joining tables together ?

            "Think of how stupid the average person is, and realize half of them are stupider than that." - George Carlin

[reply]
Re: Pull data from two tables, insert into new table?
by poj (Abbot) on Feb 16, 2016 at 18:04 UTC

    Which database Oracle, MySQL, MSSQL Server etc ?

    Is the period on time from now to last date in exception table ?

    poj
[reply]

      Well, the two calendar tables I'll be pulling from are Oracle based, and the table I'll be writing to is MySQL.

      I'm not sure I understand your second question, but if you are asking whether the time period would be from now until the last date in the exception table, the answer is yes--this script would run every night on the crontab and pick up any changes made to the exception table that day.

[reply]

        My 2nd question really was 'How far into the future do you want the dates in the mysql table to go '.

        It could get more complicated if you want to detect changes to the 2 oracle tables. Assuming the record count is in the hundreds, it might be simpler just to delete all future records and regenerate the table each night.

        As a starter idea, here is a test script that generates the records you would need to insert.

        #!perl
use strict;
use DBI;
use Time::Piece;
use Time::Seconds;

my %regular = (
  Mon =>   ['08:00','17:00'],
  Tue =>   ['08:00','17:00'],
  Wed =>   ['08:00','17:00'],
  Thu =>   ['08:00','17:00'],
  Fri =>   ['08:00','17:00'],
  Sat =>   ['08:00','12:00'],
  Sun =>   ['00:00','00:00'],
);   

my %except = (
  '2016-05-30' => ['00:00','00:00'],
  '2016-07-04' => ['00:00','00:00'],
  '2016-09-05' => ['00:00','00:00'],
  '2016-10-10' => ['00:00','00:00'],
  '2016-11-08' => ['00:00','00:00'],
  '2016-11-11' => ['00:00','00:00'],
  '2016-11-24' => ['00:00','00:00'],
  '2016-12-26' => ['00:00','00:00'],
);

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->wdayname;
  my $open  = $except{$ymd}[0] || $regular{$w}[0];
  my $close = $except{$ymd}[1] || $regular{$w}[1];
  my $is_closed = ($open eq '00:00') ? 1 : 0;
  
  print OUT "$id,$ymd,$w,$open,$close,$is_closed\n";
 
  $t += ONE_DAY;
  ++$id;
}
close OUT;

        [download]
        poj
[reply]
[d/l]

Back to Seekers of Perl Wisdom