Gossamer Forum
Home : Products : DBMan : Customization :

Search time on the time of the search

Quote Reply
Search time on the time of the search
Hi, I have a little problem here. I have a database of Organizations with Operation Hours in this format.

mon_open = X;
mon_close = X;
...
Sun_open = X;
Sun_close = X;

And X are 24 Hours, Closed, 12:00 AM, 1:00 AM, ... , 11:00PM

Now I need to have a way to search for Organizations that are open the time I started my search without inputing the time and without have a time as a hidden value in my html.pl.

I don't know if I am stating this clearly, but someone please help.

I want to have a button in my search form that states "Open Now?" and when I click on it and search. I want it to search the database for all the Organizations that are opened at the time the search was started. I do not want to embed this in the html because if I leave the page open for a hour and than click on it, it would be a hour late. So in another word, I need the time search done on processing time.

Thank you so much.

Scott Wang


Quote Reply
Re: Search time on the time of the search In reply to
Woah! This is a biggie.

So there are fourteen fields, yes? One for opening time and one for closing time for each day of the week. (I don't even want to think about holidays!! Smile)

So the script would have to figure out which day of the week it is before it even knew which field to search on.

I think you would need to enter your times as four-digit numbers -- 0000 to 2359. The script could then convert the current time to a four-digit number for comparison purposes. For days when the organization was closed, you would need to enter -1 in both fields. And the fields would have to be field type "numer."

And all of this would have to be done in sub query.

Yeah, I think it can be done, if the fields are set up the way I described above.

I do want to make sure this is what you want before I start writing code. Smile

JPD
http://www.jpdeni.com/dbman/
Quote Reply
Re: Search time on the time of the search In reply to
Yes, I can make those modifications to the database. :)
But instead of making the time into military time, is there a way I can make a subroutine that can convert the time into what military time and stuff?

This is a fake code I am just typing.
Example:

sub get_day {
my ($sec, $min, $hour, $day, $mon, $year, $dweek, $dyear, $daylight) = localtime(time());
my (@day) = qw!Sun Mon Tue Wed Thu Fri Sat!;
return "$day[$dweek]";
}

sub convert_time_to_military {
# $hour:$min $ampm , this is the way the values are in.
# is there anyway to assign the first two value as $hour
# the next one as junk and the next two as $min and
# the next one as junk and the last two as $ampm?
# ex. "scott wa", and $hour = "sc", $min = "tt", $ampm = "wa"?
my ($hour, $min, $ampm) = $in{$get_day+_open};
if (($hour == "12") && ($ampm == "AM")) {return "24$min";}
else if (($hour == "12") && ($ampm == "PM")) {return "12$min";}
# For "24 hours"
else if ($hour == "24") {return "-2";}
# For "Closed"
else if ($hour == "Cl") {return "-1";}
else ($ampm == "PM") {$hour = $hour + 12; return "$hour$min";}
}
I want the code above to find out what day of the week it is and to convert the time to military, like 12:00 AM to 2400.

If this cannot be done or it is tedious and slow for the computer, than can I have a way of displaying 12:00 AM from the military time of 2400, like soemthing in html.pl?

Thank you so much for replying to me and helping me.

Scott Wang


Quote Reply
Re: Search time on the time of the search In reply to
Displaying the time in "non-military" time would not be a problem at all. But it needs to be that way in the database file in order for the script to be able to compare it to the current time. I still have a lot of trouble understanding the query subroutine and I don't think I could come up with the code to convert it during a search. Also, it would take a whole lot more work internally to convert each record during the search than it would to convert it for display.

What *exactly* are the names of your fields going to be?

JPD
http://www.jpdeni.com/dbman/
Quote Reply
Re: Search time on the time of the search In reply to
Hi, Right now this is what is inside my default.cfg
################################
Mon_open => [19, 'alpha', 0, 18, 0, '', ''],
Mon_close => [20, 'alpha', 0, 18, 0, '', ''],
Tue_open => [21, 'alpha', 0, 18, 0, '', ''],
Tue_close => [22, 'alpha', 0, 18, 0, '', ''],
Wed_open => [23, 'alpha', 0, 18, 0, '', ''],
Wed_close => [24, 'alpha', 0, 18, 0, '', ''],
Thu_open => [25, 'alpha', 0, 18, 0, '', ''],
Thu_close => [26, 'alpha', 0, 18, 0, '', ''],
Fri_open => [27, 'alpha', 0, 18, 0, '', ''],
Fri_close => [28, 'alpha', 0, 18, 0, '', ''],
Sat_open => [29, 'alpha', 0, 18, 0, '', ''],
Sat_close => [30, 'alpha', 0, 18, 0, '', ''],
Sun_open => [31, 'alpha', 0, 18, 0, '', ''],
Sun_close => [32, 'alpha', 0, 18, 0, '', ''],

#in the select field I have
rMon_open => '24 Hours,CLOSED,12:00 AM,...',
rMon_close => '12:00 AM,12:15 AM,12:30,...',

Well, I can change this to military time if it will help this database to work like the way I want.

I was also hoping to add in new fields for HotLine Hours such as.

Office_mon_open
Office_mon_close
...
Office_sun_open
Office_sun_close

and

Hotline_mon_open
Hotline_mon_close
and etc.

Well I guess what I want to accomplish in the end is to allow users to do a search to see if whether an office or hotline is open at the time a search was processed.

So, "Closed" can be equal to -1, "24 Hours = X, 12:00 AM = 2400, and etc.

It would be great if you can help me get this working.
Thank you so much.

Scott Wang

Oh, and can you please explain to me on how I can make the conversion of military time to "non-military" time for display?

Thanks again.

Quote Reply
Re: Search time on the time of the search In reply to
First I need you to decide exactly what the field names will be. The whole rest of the code depends on it. You have several different structures here:

Mon_open
rMon_open
Office_mon_open
Hotline_mon_open

I need to know *exactly* what the fields are going to be, including the case of the field names. So, decide what you want to end up with and we'll work from there. Smile

I'm not sure why you have both Mon_open and rMon_open. I need an explanation of that.

The next thing we'll need to do is to convert the data from your select fields into the correct format for the database. "Closed" would be -2 for both "open" and "close" times. "24 Hours" would have to be -1 for open time and 2400 for close time. Hmmmm. I'm thinking about this as I go along. I just realized that the script searches for "greater than" and "less than" but not "greater than or equal to." So we have to fudge the numbers a little bit so the times will come out right.

(I know this is different than I said before. This is all new to me, so I have to play it by ear. Smile)

When the search is done (eventually), the script will figure out what day it is so it can look in the right field. Then it will figure out what time it is and search for "day_open" that is less than the current time and "day_closed" that is greater than the current time. That's how you will get your results.

As for converting military time to "regular" time -- it would go something like this:

Code:

if ($time == 0) {
$output = "12:00 midnight";
}
elsif ($time < 100) {
$min = $time;
if ($min<10) { $min = "0" . $min; }
$output = "12:" . $min . " a.m.";
}
elsif ($time < 1200) {
$min = $time % 100;
if ($min < 10) { $min = "0" . $min; }
$output = int($time/100) . ":" . $min . " a.m.";
}
elsif ($time == 1200) {
$output = "12:00 noon";
}
elsif ($time < 1300) {
$min = $time-1200;
if ($min < 10) { $min = "0" . $min; }
$output = "12:" . $min . " p.m.";
}
elsif ($time < 2400) {
$time -= 1200;
$min = $time % 100;
if ($min < 10) { $min = "0" . $min; }
$output = int($time/100) . ":" . $min . " p.m.";
}
else { $output = "12:00 midnight"; }
This probably would be best to put into a subroutine to be called whenever you needed it. But you're not quite ready for this as yet. You gotta decide for certain about those field names first.

JPD
http://www.jpdeni.com/dbman/
Quote Reply
Re: Search time on the time of the search In reply to
Hi :)

Well lets see,
Mon_open <---- This is what I have now.
rMon_open <---- This is a mistake. :)
Office_mon_open <--|----These two are what I want for the
Hotline_mon_open <-- Future. But I can do without them now.

Okay, I am going to be daring and hopefully not bother you too much about this, but I would like the fields to be Office_mon_open,Office_mon_close,Hotline_mon_open,Hotline_mon_close.

I think I understand about the displaying, thank you for showing it to me.

Thank you for helping me.

Scott Wang



Quote Reply
Re: Search time on the time of the search In reply to
That's fine. It's much better if we get all the fields you are going to want now than to try to add more later on. (I only want to do this once! Smile)

Get your fields set up and I'll be back with you later. It's almost 7am where I am and I have not yet gone to bed, so it will be a while before I get back. Smile

JPD
http://www.jpdeni.com/dbman/
Quote Reply
Re: Search time on the time of the search In reply to
Okay, I have set up the fields. :)

Office_mon_open => [9, 'numer', 4, 4, 1, '', ''],
Office_mon_close => [10, 'numer', 4, 4, 1, '', ''],
Office_tue_open => [11, 'numer', 4, 4, 1, '', ''],
Office_tue_close => [12, 'numer', 4, 4, 1, '', ''],
Office_wed_open => [13, 'numer', 4, 4, 1, '', ''],
Office_wed_close => [14, 'numer', 4, 4, 1, '', ''],
Office_thu_open => [15, 'numer', 4, 4, 1, '', ''],
Office_thu_close => [16, 'numer', 4, 4, 1, '', ''],
Office_fri_open => [17, 'numer', 4, 4, 1, '', ''],
Office_fri_close => [18, 'numer', 4, 4, 1, '', ''],
Office_sat_open => [19, 'numer', 4, 4, 1, '', ''],
Office_sat_close => [20, 'numer', 4, 4, 1, '', ''],
Office_sun_open => [21, 'numer', 4, 4, 1, '', ''],
Office_sun_close => [22, 'numer', 4, 4, 1, '', ''],
Hotline_mon_open => [23, 'numer', 4, 4, 1, '', ''],
Hotline_mon_close => [24, 'numer', 4, 4, 1, '', ''],
Hotline_tue_open => [25, 'numer', 4, 4, 1, '', ''],
Hotline_tue_close => [26, 'numer', 4, 4, 1, '', ''],
Hotline_wed_open => [27, 'numer', 4, 4, 1, '', ''],
Hotline_wed_close => [28, 'numer', 4, 4, 1, '', ''],
Hotline_thu_open => [29, 'numer', 4, 4, 1, '', ''],
Hotline_thu_close => [30, 'numer', 4, 4, 1, '', ''],
Hotline_fri_open => [31, 'numer', 4, 4, 1, '', ''],
Hotline_fri_close => [32, 'numer', 4, 4, 1, '', ''],
Hotline_sat_open => [33, 'numer', 4, 4, 1, '', ''],
Hotline_sat_close => [34, 'numer', 4, 4, 1, '', ''],
Hotline_sun_open => [35, 'numer', 4, 4, 1, '', ''],
Hotline_sun_close => [36, 'numer', 4, 4, 1, '', ''],

Thanks for helping. :))

Scott Wang


Quote Reply
Re: Search time on the time of the search In reply to
This took a little thinking. Smile

Add the following to your .cfg file:

Code:

%fancy_select = (
'Open' => [
['---','Open'],
['-1','24 Hours'],
['2500','Closed'],
['0','12:00 midnight'],
['30','12:30 a.m.'],
['100','1:00 a.m.'],
['130','1:30 a.m.'],
['200','2:00 a.m.'],
['230','2:30 a.m.'],
['300','3:00 a.m.'],
['330','3:30 a.m.'],
['400','4:00 a.m.'],
['430','4:30 a.m.'],
['500','5:00 a.m.'],
['530','5:30 a.m.'],
['600','6:00 a.m.'],
['630','6:30 a.m.'],
['700','7:00 a.m.'],
['730','7:30 a.m.'],
['800','8:00 a.m.'],
['830','8:30 a.m.'],
['900','9:00 a.m.'],
['930','9:30 a.m.'],
['1000','10:00 a.m.'],
['1030','10:30 a.m.'],
['1100','11:00 a.m.'],
['1130','11:30 a.m.'],
['1200','12:00 noon'],
['1230','12:30 p.m.'],
['1300','1:00 p.m.'],
['1330','1:30 p.m.'],
['1400','2:00 p.m.'],
['1430','2:30 p.m.'],
['1500','3:00 p.m.'],
['1530','3:30 p.m.'],
['1600','4:00 p.m.'],
['1630','4:30 p.m.'],
['1700','5:00 p.m.'],
['1730','5:30 p.m.'],
['1800','6:00 p.m.'],
['1830','6:30 p.m.'],
['1900','7:00 p.m.'],
['1930','7:30 p.m.'],
['2000','8:00 p.m.'],
['2030','8:30 p.m.'],
['2100','9:00 p.m.'],
['2130','9:30 p.m.'],
['2200','10:00 p.m.'],
['2230','10:30 p.m.'],
['2300','11:00 p.m.'],
['2330','11:30 p.m.']
],
'Close' => [
['---','Close'],
['2500','24 Hours'],
['-1','Closed'],
['2400','12:00 midnight'],
['30','12:30 a.m.'],
['100','1:00 a.m.'],
['130','1:30 a.m.'],
['200','2:00 a.m.'],
['230','2:30 a.m.'],
['300','3:00 a.m.'],
['330','3:30 a.m.'],
['400','4:00 a.m.'],
['430','4:30 a.m.'],
['500','5:00 a.m.'],
['530','5:30 a.m.'],
['600','6:00 a.m.'],
['630','6:30 a.m.'],
['700','7:00 a.m.'],
['730','7:30 a.m.'],
['800','8:00 a.m.'],
['830','8:30 a.m.'],
['900','9:00 a.m.'],
['930','9:30 a.m.'],
['1000','10:00 a.m.'],
['1030','10:30 a.m.'],
['1100','11:00 a.m.'],
['1130','11:30 a.m.'],
['1200','12:00 noon'],
['1230','12:30 p.m.'],
['1300','1:00 p.m.'],
['1330','1:30 p.m.'],
['1400','2:00 p.m.'],
['1430','2:30 p.m.'],
['1500','3:00 p.m.'],
['1530','3:30 p.m.'],
['1600','4:00 p.m.'],
['1630','4:30 p.m.'],
['1700','5:00 p.m.'],
['1730','5:30 p.m.'],
['1800','6:00 p.m.'],
['1830','6:30 p.m.'],
['1900','7:00 p.m.'],
['1930','7:30 p.m.'],
['2000','8:00 p.m.'],
['2030','8:30 p.m.'],
['2100','9:00 p.m.'],
['2130','9:30 p.m.'],
['2200','10:00 p.m.'],
['2230','10:30 p.m.'],
['2300','11:00 p.m.'],
['2330','11:30 p.m.']
]
);
Add the following new subroutine to db.cgi:

Code:

sub build_fancy_select_field {
# --------------------------------------------------------
#
# To call this subroutine from html_record_form, use the following syntax:
#
# print &build_fancy_select_field("FieldName",$rec{'FieldName'},"SelectorName")
#
# Be sure to express the field name *exactly* as it is defined in your .cfg file.
#

my ($field,$compare,$name) = @_;
my ($i);

$output = qq|<SELECT NAME="$field">\n|;
$i = 0;
while ( $fancy_select{$name}[$i][0] ) {
$fancy_select{$name}[$i][0] eq $compare ?
($output .= qq|<OPTION VALUE="$fancy_select{$name}[$i][0]" SELECTED>$fancy_select{$name}[$i][1]\n|) :
($output .= qq|<OPTION VALUE="$fancy_select{$name}[$i][0]">$fancy_select{$name}[$i][1]\n|);
++$i;
}

if ($i) { $output .= "</SELECT>"; }
else { $output = "Incorrect field definition"; }
return $output;
}
Set up your form like this:

Code:

<tr><td>Monday office hours:</td>
<td>&nbsp;|;
print &build_fancy_select_field("Office_mon_open",$rec{'Office_mon_open'},"Open");
print qq|&nbsp;|;
print &build_fancy_select_field("Office_mon_close",$rec{'Office_mon_close'},"Close");
print qq|</td></tr>
Make one of those rows for each day of the week and then make similar rows for the hotline hours.

Then give it a try and see if it works. Smile Don't worry right now about printing out the time correctly. Just be sure the select field gives you the correct values and that, when you modify a record, the select field has the value selected.

JPD
http://www.jpdeni.com/dbman/
Quote Reply
Re: Search time on the time of the search In reply to
Hi :),

I did what you said and it all work correctly except for this part.

%fancy_select = (
'Open' => [
['---','Open'],
['-1','24 Hours'],
['2500','Closed'],
['0','12:00 midnight'], <--- if this is zero, then fancy field end at "Closed". But once I changed the 0 to 00, fancy field showed everything.

Ex. If 12:00 midnight = 0;
Monday Office Hours:
Opened
24 Hours
Closed

If 12:00 midnight = 00;
Monday Office Hours:
Opened
24 Hours
Closed
12:00 midnight
...
11:30 p.m.

I hope this doesn't matter.
Thanks for helping. :)

Scott Wang

Quote Reply
Re: Search time on the time of the search In reply to
Good catch! I know why it didn't work. I just didn't think about it before.

Now take the code I posted before to convert the military time into regular time and put it in a subroutine:

Code:

sub convert_time {
my ($time) = $_[0];
insert the code from above
return $output;
}
In sub html_record, after

my (%rec) = @_;

add

Code:

for ($conv=9; $conv<=36; ++$conv) {
$rec{$db_cols[$conv]} = &convert_time($rec{$db_cols[$conv]};
}
That should convert all your times into the format you want.

Then we get to start working on the search. Smile

Can you give me a ballpark figure of how many records might be returned in a search for offices and hotlines that are currently open in a city? Let's say the search was done at noon on a Wednesday, when most things would be open. What would be the most that would be returned?

JPD
http://www.jpdeni.com/dbman/
Quote Reply
Re: Search time on the time of the search In reply to
Hi, I did what you ask and this is what I got.. I don't really know where to put the subroutine so I put it first in db.cgi and then in html.pl. Either way, I get the same result.

CGI ERROR
==========================================
Error Message : Error loading required libraries.
Check that they exist, permissions are set correctly and that they compile.
Reason: syntax error at ./html.pl line 180, near "};"

Hmmm... Well, since I am in NY, there are about 1200 Youth Centers. So, depending on zip code, it can be as little as 0 to 25. But if they just type *, it would be around 1200.

Thanks. :)
Scott Wang

Quote Reply
Re: Search time on the time of the search In reply to
Oh I am sorry but line 180 is this

for ($conv=9; $conv<=36; ++$conv) { $rec{$db_cols[$conv]} = &convert_time($rec{$db_cols[$conv]};}

Scott Wang :)

Quote Reply
Re: Search time on the time of the search In reply to
OOPS, sorry fixed it. There wasn't a ending ')' before the last ;. :) Thanks.

Scott Wang.

Quote Reply
Re: Search time on the time of the search In reply to
Well, I guess I'm partly right. Even if I forget little things like closing parentheses! Smile

The reason I asked about the number of searches returned, specifically from your "What's open now" thing is that we're going to have to fudge the search a bit. If I understand what you want correctly, you want to return records that match

city + office open time + office close time
or
city + hotline open time + hotline close time

The way the script is set up, you can't mix "ands" and "ors." So we have to fudge it. We can do a search for the offices being open and then tack on the results of a search for the hotlines being open. But it's all got to be on one page or it won't work.

Regular searches are not a problem. It's just the "What's open now" thing that's the problem.

JPD
http://www.jpdeni.com/dbman/
Quote Reply
Re: Search time on the time of the search In reply to
:) hhmmm... I am a bit confused. What do you mean it all got to be on one page? Well, the db is on one big flat txt file and if it has to be displayed on one page, its okay with me. :)

Scott Wang