############################################################################### #### #### #### U N I V E R S A L D A T E T R A N S L A T O R #### #### #### #### Written by JPDeni (Carol Hall) #### #### Email: deni@jpdeni.com #### #### 5-Apr-2001 #### #### ------######------ #### #### #### #### If you have your users entering dates, you will quickly learn that #### #### DBMan is very picky about the format for the dates. They must be #### #### entered exactly in the correct format or the script will reject them. #### #### #### #### This mod will let users enter dates in just about any format. There #### #### are still some entries it might reject, but not many. #### #### #### #### All of the date formats below are acceptable: #### #### ddMmmyyyy ddmmyyyy yyyymmdd #### #### dd-Mmm-yyyy Mmm-dd-yyyy dd/mm/yyyy mm/dd/yyyy #### #### #### #### It will even accept some dates with a two-digit year. The script #### #### assumes that a two-digit year is between 1920 and 2019. If you are #### #### using this in the year 2020, you will need to change some of the code #### #### in sub date_to_unix. #### #### #### #### It will accept any non-letter/non-number delimiter between elements, #### #### so that users can enter dd/Mmm/yyyy or mm-dd-yyyy or dd mm yyyy #### #### #### #### Entries are case-insensitive, so that if your users forget to #### #### capitalize the month -- 22-oct-1999 -- or if they have their #### #### "Caps Lock" key on -- 22-OCT-1999 -- the script won't care. #### #### #### #### It also strips out any "."s and ","s so a user could enter #### #### Oct. 22, 1999 #### #### and the script will accept it. #### #### #### #### I have also included the full spelling of the months in addition to #### #### the three-letter abbreviations. If you are likely to have non-English #### #### speaking users, feel free to edit the month names. Just follow the #### #### pattern you see in the %months definition. Be sure to enter the month #### #### names in lower case. #### #### #### #### The mod also changes the way DBMan sorts and looks for "greater than" #### #### and "less than" dates, to allow you to search for dates before #### #### 1 Jan 1970, which is the earliest date that Unix supports. It will not#### #### search for dates before 1 Jan 1000, however. #### #### #### #### The one problem you might run into is if users enter the number of the#### #### month and the day is less than 13. This results in an ambiguous date. #### #### In America, the date 07/04/1999 is the 4th of July. In the rest of the#### #### world, the same date is the 7th of April. You must decide how you want#### #### to translate ambiguous dates. There is a new setting for the .cfg file#### #### to indicate how you would like to translate ambiguous dates. #### ############################################################################### ############################################################################### ######## file: default.cfg ######## ######## add ######## ######## ######## ######## ######## ######## What it does -- ######## ######## Sets the mode for translating ambiguous dates ######## ######## ######## ######## Where to put it -- ######## ######## Just after ######## ######## # Bold search results (1 = Yes, 0 = No). ######## ######## $db_bold = 1; ######## ############################################################################### # Translation of ambiguous dates, such as "07/04/1999" # (1 = "July 4, 1999", 0 = "7 April 1999") $american_dates = 1; ################################################################################ ######## script: db.cgi ######## ######## change lines ######## ######## subroutine: sub query ######## ######## ######## ######## What it does -- ######## ######## changes dates entered in any format to the uniform format for ######## ######## the database, to allow searches by date ######## ######## ######## ######## This is *not* the same code as the bug fix for sub query. If ######## ######## you use this mod, you must make this change to sub query ######## ######## instead of the bug fix. ######## ################################################################################ ### old code ### else { # Otherwise this is a regular search, and we only want records $i = 0; # that match everything the user specified for. foreach $column (@db_cols) { if ($in{$column} =~ /^\>(.+)$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($1) or return "Invalid date format: '$1'"); push (@search_gt_fields, $i); $in{"$column-gt"} = $1; $i++; next; } if ($in{$column} =~ /^\<(.+)$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($1) or return "Invalid date format: '$1'"); push (@search_lt_fields, $i); $in{"$column-lt"} = $1; $i++; next; } if ($in{$column} !~ /^\s*$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($in{$column}) or return "Invalid date format: '$in{$column}'"); push(@search_fields, $i); $i++; next; } if ($in{"$column-gt"} !~ /^\s*$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($in{"$column-gt"}) or return "Invalid date format: '$in{$column}'"); push(@search_gt_fields, $i); } if ($in{"$column-lt"} !~ /^\s*$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($in{"$column-lt"}) or return "Invalid date format: '$in{$column}'"); push(@search_lt_fields, $i); } $i++; } } ### new code ### else { # Otherwise this is a regular search, and we only want records $i = 0; # that match everything the user specified for. foreach $column (@db_cols) { if ($in{$column} =~ /^\>(.+)$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($1) or return "Invalid date format: '$1'"); push (@search_gt_fields, $i); $in{"$column-gt"} = $1; $i++; next; } if ($in{$column} =~ /^\<(.+)$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($1) or return "Invalid date format: '$1'"); push (@search_lt_fields, $i); $in{"$column-lt"} = $1; $i++; next; } if ($in{$column} !~ /^\s*$/) { if ($db_sort{$column} eq 'date') { if (&date_to_unix($in{$column})) { $in{$column} = &get_computed_date(&date_to_unix($in{$column})); } else { return "Invalid date format: '$in{$column}'"; } } push(@search_fields, $i); $i++; next; } if ($in{"$column-gt"} !~ /^\s*$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($in{"$column-gt"}) or return qq|Invalid date format: '$in{"$column-gt"}'|); push(@search_gt_fields, $i); } if ($in{"$column-lt"} !~ /^\s*$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($in{"$column-lt"}) or return qq|Invalid date format: '$in{"$column-lt"}'|); push(@search_lt_fields, $i); } $i++; } } ################################################################################ ######## script: db.cgi ######## ######## replace ######## ######## subroutine: sub date_to_unix ######## ######## ######## ######## What it does -- ######## ######## translates different date formats into a form readable by DBMan######## ################################################################################ sub date_to_unix { # -------------------------------------------------------- # This routine takes dates in almost any format and returns the time a la UNIX time(). # Some things to be careful about.. # int your values just in case to remove spaces, etc. # catch the fatal error timelocal will generate if you have a bad date.. # Unlike the original subroutine, months are indexed from 1, not 0. my ($date) = $_[0]; my (%months) = ("jan" => 1, "feb" => 2, "mar" => 3, "apr" => 4, "may" => 5, "jun" => 6, "jul" => 7, "aug" => 8, "sep" => 9, "oct" => 10, "nov" => 11,"dec" => 12, "january" => 1, "february" => 2, "march" => 3, "april" => 4, "june" => 6, "july" => 7, "august" => 8, "september" => 9, "october" => 10, "november" => 11, "december" => 12, "sept" => 9); my ($time); $date =~ s/,|\.//g; # delete any commas or periods that might be in there $date =~ s/\W/-/g; # change any separators into hyphens if ((defined($months{lc(substr($date,2,3))})) && (length($date) == 9)) { # date format ddMmmyyyy $day = substr($date,0,2); $month = $months{lc(substr($date,2,3))}; $year = substr($date,-4); } elsif ((defined($months{lc(substr($date,1,3))})) && (length($date) == 8)) { # date format dMmmyyyy $day = substr($date,0,1); $month = $months{lc(substr($date,1,3))}; $year = substr($date,-4); } elsif ((substr($date,4,2) > 12) && (length($date) == 8)) { # date format ddmmyyyy $year = substr($date,4,4); $month = substr($date,2,2); $day = substr($date,0,2); } elsif (($date > 19000000) && (length($date) == 8)) { # date format yyyymmdd $year = substr($date,0,4); $month = substr($date,4,2); $day = substr($date,6,2); } else { $date = lc($date); @date_part = split /-/,$date; $year = $date_part[2]; $year = int($year); if ($year<100) { if ($year<20) { $year += 2000; } else { $year += 1900; } } if (defined($months{$date_part[1]})) { #date format dd-Mmm-yyyy $day = $date_part[0]; $month = $months{$date_part[1]}; } elsif (defined($months{$date_part[0]})) { #date format Mmm-dd-yyyy $day = $date_part[1]; $month = $months{$date_part[0]}; } elsif ($date_part[0] > 12) { #date format dd-mm-yyyy $day = $date_part[0]; $month=$date_part[1]; } elsif ($date_part[1] > 12) { #date format mm-dd-yyyy $day = $date_part[1]; $month=$date_part[0]; } elsif ($american_dates) { #ambiguous date -- American format $day = int($date_part[1]); $month=int($date_part[0]); } else { #ambiguous date -- the rest of the world $day = int($date_part[0]); $month=int($date_part[1]); } } unless ($day and $month and $year) { return undef; } ($day < 10) and ($day = "0" . int($day)); $year = int($year); ($month < 10) and ($month="0" . int($month)); $time= $year . $month . $day; if ($time < 10000101) { return undef; } return ($time); } ################################################################################ ######## script: db.cgi ######## ######## new subroutine ######## ######## sub get_computed_date ######## ######## ######## ######## What it does -- ######## ######## translates dates from sub date_to_unix into a uniform format ######## ################################################################################ sub get_computed_date { # -------------------------------------------------------- # Returns the date in the format "dd-mmm-yyyy". # If you have changed your date format in sub get_date, you should also change it here. my ($time) = $_[0]; $year = substr($time,0,4); $mon = substr($time,4,2); $day = substr($time,6,2); unless ($day && $mon && $year) { return undef; } --$mon; my (@months) = qw!Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec!; return "$day-$months[$mon]-$year"; } ################################################################################ ######## script: db.cgi ######## ######## change lines ######## ######## subroutine: sub validate record ######## ######## What it does -- ######## ######## checks to see if dates entered are in an acceptable format ######## ################################################################################ ### old lines if ($db_sort{$col} eq "date") { push (@input_err, "$col (Invalid date format)") unless &date_to_unix($in{$col}); } ### new lines if ($db_sort{$col} eq "date") { if (&date_to_unix($in{$col})) { $in{$col} = &get_computed_date(&date_to_unix($in{$col})); } else { push (@input_err, "$col (Invalid date format)"); } }