Good morning Monks,
First, I am not a programmer, so I apologize for my utter ignorance, but I need to modify an already existing script that currently gathers our normal operating hours, runs through a table of exceptions, then posts this week's operating hours in a simple html table. I am trying to simply gather the exception hours for the whole semester and post them to an html table. However, I have been unable even to gather the data, it seems, and I can't figure out why. Any light you could shed on this would be greatly appreciated. Here is the code:
#!/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'), excepti +on_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 = "clos +ed" } if ( not defined $exception_closehour ) { $exception_closehour = "cl +osed" } } # -- 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>Excepti +ons 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>Da +y/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 + 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"); # -- 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
In reply to Using DBI to gather time by Hans Castorp
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |