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
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
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;
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 }
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
In reply to Pull data from two tables, insert into new table? by Hans Castorp
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |