Sorting Days of Week Using today () to today () + 6

[Hans Castorp]

Hello Holy Ones

I am modifying a script that currently runs through a table of normal business hours, stores that data in a hash, then runs through a table of exceptions to our business hours and stores any found for the current week to a hash, then writes this week's business hours to an html table. The script uses Date::Simple.

The current way this information is formatted is Sunday through Saturday. I need to change it so that the script runs every day, then posts today's business hours, with a drop-down table of the next 6 days' business hours. I think that what I need to do is sort my normal hours according to the time range of today () to today () + 6, but I don't know how to do that (or if I can do that?).

Here is what I believe is the relevant code:

# -- define today
my $today = today();

# -- use today object and return a number indicating the current day o
+f week (0=sun 1=mon, etc.)
my $dow = $today->day_of_week;

# -- get the start and end dates for the week 
# -- Date module helps us do this easily 
  
my $today_date = ( $today );  # start of week
my $end_date = ( $today_date + 6 );          # end of week


# -- will store data for Sun <-> Sat with day of week (number) as key
my %this_wk = ();

# -- simple arrays to define days 
my @days = (0 .. 6);
my @day_text = qw(Sunday Monday Tuesday Wednesday Thursday Friday Satu
+rday);

# -- loop thru all days of week and add data to hash
# -- we will add more data to hash later
for my $day (@days) {

  # -- add date and day text identifier to hash   
  $this_wk{$day}{date} = ( $today_date  + $day );
  $this_wk{$day}{day_text} = $day_text[$day]; 

}  ## end days for loop     

# -- connect to Voyager db
my $dbh = DBI->connect( $voy_dsn,$voy_dbun,$voy_dbpass, { AutoCommit =
+> 0, RaiseError => 0, PrintError => 1 } ) || die $DBI::errstr; 

# -- get normal hours of operations
# -- yes, believe it or not there is only one row (there must be a rea
+son for this setup but I can't think of one)
my $sql = qq(SELECT sunday_open,sunday_openhour,sunday_closehour,
monday_open,monday_openhour,monday_closehour,
tuesday_open,tuesday_openhour,tuesday_closehour,
wednesday_open,wednesday_openhour,wednesday_closehour,
thursday_open,thursday_openhour,thursday_closehour,
friday_open,friday_openhour,friday_closehour,
saturday_open,saturday_openhour,saturday_closehour
FROM calendar
WHERE calendar_desc = '$voy_cal2use');

my $sth = $dbh->prepare( $sql );
$sth->execute;

# -- fetch all data and create var to refer to   
my $arrayref_norm_hrs = $sth->fetchall_arrayref( );

# -- store normal hours for each day of week in here 
my %normal_hrs = (); 

# -- loop thru the data array returned by query
# -- put in a data structure we can work with
foreach my $row ( @$arrayref_norm_hrs ) {
  
 my ( $sun_open,$sun_openhr,$sun_closehr,$mon_open,$mon_openhr,$mon_cl
+osehr, $tue_open,$tue_openhr,$tue_closehr,$wed_open,$wed_openhr,$wed_
+closehr,
  $thu_open,$thu_openhr,$thu_closehr,$fri_open,$fri_openhr,$fri_closeh
+r,$sat_open,$sat_openhr,$sat_closehr ) = @$row; 

  # -- store normal hours data in hash of hashes 
  $normal_hrs{$days[0]}{openorclosed} = $sun_open;
  $normal_hrs{$days[0]}{open} = $sun_openhr;
  $normal_hrs{$days[0]}{close} = $sun_closehr;
  $normal_hrs{$days[1]}{openorclosed} = $mon_open;
  $normal_hrs{$days[1]}{open} = $mon_openhr;
  $normal_hrs{$days[1]}{close} = $mon_closehr;
  $normal_hrs{$days[2]}{openorclosed} = $tue_open;
  $normal_hrs{$days[2]}{open} = $tue_openhr;
  $normal_hrs{$days[2]}{close} = $tue_closehr;
  $normal_hrs{$days[3]}{openorclosed} = $wed_open;
  $normal_hrs{$days[3]}{open} = $wed_openhr;
  $normal_hrs{$days[3]}{close} = $wed_closehr;
  $normal_hrs{$days[4]}{openorclosed} = $thu_open;
  $normal_hrs{$days[4]}{open} = $thu_openhr;
  $normal_hrs{$days[4]}{close} = $thu_closehr;
  $normal_hrs{$days[5]}{openorclosed} = $fri_open;
  $normal_hrs{$days[5]}{open} = $fri_openhr;
  $normal_hrs{$days[5]}{close} = $fri_closehr;
  $normal_hrs{$days[6]}{openorclosed} = $sat_open;
  $normal_hrs{$days[6]}{open} = $sat_openhr;
  $normal_hrs{$days[6]}{close} = $sat_closehr;

}  ## end normal hours data foreach loop

Can anyone point me in the right direction, so that I can sort this hash of hashes according to

my $today_date = ( $today ); # start of week

my $end_date = ( $today_date + 6 ); # end of week

Or am I going at this in the wrong way? Thanks in advance!

[MidLifeXis]

Just a couple of comments:

• WHERE calendar_desc = '$voy_cal2use': http://www.bobby-tables.com. Use What are placeholders in DBI, and why would I want to use them? instead.
• I could not find a declaration for $voy_cal2use.
• There is a lot of duplicated code. Quite a bit could be reduced by generating part of the SQL:

  my $column_names = join( ",", map {
      ( "${_}_open", "${_}_openhour", "${_}_closehour" );
  } map {
      lc( $_ );
  } @day_text;
  
  my $sql = qq(SELECT $column_names ...);
  

• I would probably use a slightly different data structure and leverage the bind_col or bind_columns call from DBI instead of the block of $normal_hrs{$day}{$subkey} = ... calls.

No time yet to evaluate other things.

[Hans Castorp]

Many thanks MidLifeXis for your teaching-based response--there is much to think about here.

I did not include all the code because it's over 200 lines long--the declaration for $voy_cal2use is above the snippet I posted.

Still very new to Perl (and any programming), so although I know "There is more than one way to do it," I am very hesitant to mess with my predecessor's code. I've thought many times that it might be easier to write my own script, but I don't know enough to do that yet. Thanks for the introduction to more streamlined possibilities--and the humor. :-) I will play with the SQL.

[ww]

It may be that I took one too many stupid pills this am but I really don't understand why you "think that what (you) need to do is sort (your) normal hours according to ..." ...DOW. It appears that you expect the db to cough up the row with reasonable sorting ... Sun-Sat... and the biz hours row to do likewise so your requirement is merely that you select today's DOW as your starting point in each case and proceed thru DOW + 6.

And, without working out the details, numerous questions on mergine two arrays; on dispatch tables; or perhaps slices would spare a lot of unnecessary work.

If I've misconstrued your question or the logic needed to answer it, I offer my apologies to all those electrons which were inconvenienced by the creation of this post.

[Hans Castorp]

Thank you for your response ww--I am trying to understand what you are telling me....

I've done a Data::Dumper::Dump for the normal hours, so I've seen that the output is not organized in any meaningful way. Nevertheless, when the script runs and the html file is generated, it is always Sunday-Saturday.

What I'm trying to figure out is how to get the data to put out an html file that is the current dow - dow + 6--I'm not sure where in the script I begin to address this.

The reason I'm having trouble is, the normal hours table does not have dates--it's strictly Sun - Sat. The exception hours table has dates, so it's easy to specify in the SQL the dates I want.

Forgive if I seem clueless--it's because, largely, I am, when it comes to programming. I'm doing my best to figure it out, but it's not the bulk of my job by a long shot. (More like sporadically being thrown in deep water and figuring out how to swim.)

[ww]

You're storing (Ln 62) "normal hours data in hash of hashes." But you already have that data in a perfectly good array, namely @row (Ln 57-60) as fetched from the db. You can code your html production such that the "current" day is found by its offset -- a dispatch table, for one possibility. The order of the array's elements will be the order you coded in 59-60.

But when you talk about using Data::Dumper to look at the structure, I have to suspect you're dumping your hash. To keep it simple, be aware that hashes determine their own internal ordering, which means that what you get back will have to be sorted to some other (your choice) order. But arrays don't give you that sorting requirement. Given your @row, $tuesday_openhour will always be $row[7]

:

#!/usr/bin/perl
use 5.016;
# 1043504

my $sunday_open = 0;          # element 0 of @row, or $row[0]
my $sunday_openhour = 0;
my $sunday_closehour = 0;
my $monday_open = 1;          # element 3 of @row, or $row[3]
my $monday_openhour = 7;
my $monday_closehour = 5;
my $tuesday_open = 1;         # !!! change to 0 to see the effect;
                              # then back to 1 for a normal Tuesday wo
+rkday
my $tuesday_openhour = 7;     # element 7 of @row, or $row[7]
my $tuesday_closehour = 5;
my $wednesday_open = 1;
my $wednesday_openhour = 7;
my $wednesday_closehour = 9;
my $thursday_open = 1;
my $thursday_openhour = 7;
my $thursday_closehour = 5;
my $friday_open = 1;
my $friday_openhour = 7;
my $friday_closehour = 5;
my $saturday_open = 1;
my $saturday_openhour = 9;
my $saturday_closehour= 2;    # element 20 of @ row or $row[20]


my @row = ( $sunday_open, $sunday_openhour, $sunday_closehour, 
            $monday_open, $monday_openhour, $monday_closehour,
            $tuesday_open, $tuesday_openhour, $tuesday_closehour, 
            $wednesday_open, $wednesday_openhour, $wednesday_closehour
+, 
            $thursday_open, $thursday_openhour, $thursday_closehour, 
            $friday_open, $friday_openhour, $friday_closehour, 
            $ saturday_open, $saturday_openhour, $saturday_closehour,
          );

say " FOR ILLUSTRATION:  The var at \$row[7] will always be the sevent
+h element\n of \@row (array element counting starts at zero)\n and th
+e content of that var is $row[7], the value assiged in your hours_arr
+ay to \$tuesday_openhour. \n";

if ($row[6] == 1 ) {
    say " We're open on Tuesday, starting at $row[7]a.m. until $row[8]
+p.m.";
}elsif ($row[6] == 0) {
    say " Sorry, we won't be open today.\n But if you'd like to come i
+n on Saturday, do so between $row[19]a.m. and $row[20]p.m.";
}else {
    say "oops!";
}

=head

C:\Users\wheelerw>D:\_Perl_\PMonks\1043504.pl
 FOR ILLUSTRATION:  The var at $row[7] will always be the seventh elem
+ent
 of @row (array element counting starts at zero)
 and the content of that var is 7, the value assiged in your hours_arr
+ay to $tuesday_openhour.

 We're open on Tuesday, starting at 7a.m. until 5p.m.

=cut

Hope this clarifies what I failed to communicate previously.

[Cristoforo]

I think you want today plus the next 6 days with the day of week for each of the days. If so, then the code below will do that.

#!/usr/bin/perl
use strict;
use warnings;
use 5.014;
use Date::Simple qw/ today /;

my @day_text = qw(Sunday Monday Tuesday Wednesday Thursday Friday Satu
+rday);

my $dow = today->day_of_week;

my @in_order = map {$_ % 7} $dow .. $dow + 6;

say "localtime is: " . localtime;
say "$_ $day_text[$_]" for @in_order;

__END__
*** results of running the program
C:\Old_Data\perlp>perl t5.pl
localtime is: Wed Jul 10 16:10:33 2013
3 Wednesday
4 Thursday
5 Friday
6 Saturday
0 Sunday
1 Monday
2 Tuesday

Update: The same without need for the @day_text array. Can get the day names right from Date::Simple.

my $today = today;
my @names = map { ($today + $_)->strftime("%A")} 0 .. 6;

my $dow = today->day_of_week;
my @in_order = map {$_ % 7} $dow .. $dow + 6;

say "localtime is: " . localtime;
say $in_order[$_], ' ',  $names[$_] for 0 .. $#in_order;

The hash, %normal_hrs, could then be accessed like this sample code.

my $today = today;
my @names = map { ($today + $_)->strftime("%A")} 0 .. 6;

my $dow = $today->day_of_week;
my @in_order = map {$_ % 7} $dow .. $dow + 6;

my $today_dow = shift @in_order;

my $today_name = shift @names;
# using a hash slice
my @today_info = @{ $normal_hrs{$today_dow} }{qw/ openorclosed open cl
+ose /};
# write today's info to html

# drop down list
for my $dow (@in_order) {
    my $day_name = shift @names;
    # using a hash slice
    my @info = @{ $normal_hrs{$dow} }{qw/ openorclosed open close /};
    # write this day's info to drop down list
}

Update: For a little cleaner code, I put the contents of the 2 arrays, (@dow and @names into an array of hashes to more safely access the data below - less chance of an error.

my $today = today;
my @names = map { ($today + $_)->strftime("%A")} 0 .. 6;

my $dow = $today->day_of_week;
my @dow = map {$_ % 7} $dow .. $dow + 6;

my @data;
for my $i (0 .. 6) {
    push @data, {dow => $dow[$i], name => $names[$i]};    
}

my $date = shift @data;

# using a hash slice for today's info
# You have access to $date->{dow} and $date->{name}
my @today_info = @{ $normal_hrs{ $date->{dow} } }{qw/ openorclosed ope
+n close /};
# write today's info to html

# drop down list
for my $date (@data) {
    # You have access here to $date->{dow} and $date->{name}
    # using a hash slice
    my @info =     @{ $normal_hrs{$date->{dow}} }{qw/ openorclosed ope
+n close /};
    # write this day's info to drop down list
}

[Cristoforo]

I don't think you need that hash %normal_hrs as you have all the info in the returned SQL query. So, I think this version will be simpler without the hash initialization. Also, I made a minor change to the way the dates, (day_of_week and names), were created.

# -- connect to Voyager db
my $dbh = DBI->connect( $voy_dsn,$voy_dbun,$voy_dbpass, { AutoCommit =
+> 0, RaiseError => 0, PrintError => 1 } ) || die $DBI::errstr; 

# -- get normal hours of operations
# -- yes, believe it or not there is only one row (there must be a rea
+son for this setup but I can't think of one)
my $sql = qq(SELECT sunday_open,sunday_openhour,sunday_closehour,
monday_open,monday_openhour,monday_closehour,
tuesday_open,tuesday_openhour,tuesday_closehour,
wednesday_open,wednesday_openhour,wednesday_closehour,
thursday_open,thursday_openhour,thursday_closehour,
friday_open,friday_openhour,friday_closehour,
saturday_open,saturday_openhour,saturday_closehour
FROM calendar
WHERE calendar_desc = '$voy_cal2use');

my $sth = $dbh->prepare( $sql );
$sth->execute;


my @row = $sth->fetchrow->array;

my $today = today;
my @day = map $today + $_, 0 .. 6;

my @data;
for my $i (0 .. 6) {
    push @data, {dow => $day[$i]->day_of_week,
                name => $day[$i]->strftime("%A")};    
}

my $date = shift @data;

# you have $date->{name} here too
my $i = 3 * $date->{dow};
my @today_info = @row[$i .. $i+2];
# write today's info to html

# drop down list
for my $date (@data) {
    # You have access here to $date->{dow} and $date->{name}
    my $i = 3 * $date->{dow};
    my @info = @row[$i .. $i+2];
    # write this day's info to drop down list
}

[Hans Castorp]

Cristoforo,

Thank you so much for all your help. I am going to keep working on this but I came up against my deadline and had to find another solution. I'm going to respond to my original post with what I ended up doing. I have soooo much to learn!

Warm regards, HC

[Hans Castorp]

Thank you so much! I will work on this today and let you know how it goes!

[Hans Castorp]

Do-over!

OK, well, my boss decided she didn't want the drop-down menu of hours for the rest of the week, so I was left needing a simple "Today's hours are" html statement. I created a new, integrated table with all the library hours in it so the script doesn't have to loop through two tables and work with two hashes. Since I don't need the date or the day, my original problem is not addressed here--sorry about that. I hope the monks' responses will help anyone looking for that answer. Here is the script I ended up with:

#!/usr/bin/perl

use strict;
use warnings;
use DBI;
use Date::Simple qw(date today);
use Time::Format qw(%time time_format %strftime %manip);
use Data::Dumper;
use ''::Utils qw(setOracleEnv);

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

# -- define outfile 
my $htmlout = "/www/includes/lib_hours2.html";

#connection info for webprod
my ($dsn,$dbun,$dbpass) = ( 'dbi:Oracle:host=;sid=;port=','','' );

# -- define today
my $today = today();


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

# -- query to get hours for the upcoming week  
my $sql = qq(SELECT TO_CHAR(libdate,'YYYY-MM-DD'),openhour,closehour
FROM hours 
WHERE TO_CHAR(libdate,'YYYY-MM-DD') = '$today');

my $sth = $dbh->prepare( $sql );
$sth->execute;

# -- fetch all data and create var to refer to   
my $arrayref_hrs = $sth->fetchall_arrayref( );

# -- store hours for each day of week in here 
my %hrs = (); 

# -- loop thru the data from calendar table
foreach my $row ( @$arrayref_hrs ) {
  
  my ( $libdate,$openhour,$closehour ) = @$row;
  if ( not defined $openhour ) { $openhour = "closed" }
  if ( not defined $closehour ) { $closehour = "closed" }
   
   $hrs{$libdate} = {
      open => $openhour,
      close => $closehour,
   };
}

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

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

# -- store html in here  
my $content;

for my $key ( sort keys %hrs ) {

  # -- use sub to format time
  if ( exists $hrs{$key} ) {
    
    $hrs{$key}{open} = format_time( $hrs{$key}{open} );
    $hrs{$key}{close} = format_time( $hrs{$key}{close} );
    $hrs{$key}{date} = time_format( 'Month dth', $hrs{$key}{date} );
        
     
  }  ## end open/closed if

# -- begin html code
# -- write today's hours 
  if ( $hrs{$key}{open} eq 'closed') {

    $content = qq(<p class="scribsmalltext">Today the library is close
+d</p>);   
 
  } else {

    $content = qq(<p class="scribsmalltext">Today's hours are $hrs{$ke
+y}{open} - $hrs{$key}{close}</p>);

  }  ## end if
  
  
  # -- uncomment for testing
print Data::Dumper->Dump([%hrs]);

# -- 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
+ting 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
}

My next project is to write a script that will pull the normal hours and exception hours from their respective tables, and populate my new integrated table.

Thank you to MidLifeXis, ww, Cristoforo, and also tobyink, who helped me understand how to store data in a hash. The Monastery rocks!!