#!/usr/local/bin/perl
# Script: db.cgi
#
# db.cgi A perl script to search and display target strings from a database file.
# In this case, the database is the camping database generated from NorCal's
# meeting destinations.
# Searching the database is done on a specified field basis, that is the search is
# directed to find a specific string in a specific field. As such, each line is
# first parsed into appropriate variables or arrays, then only the appropriate
# variable or array is searched for the string.
# Written by Scot Marburger starting 10/25/96
#
# Copywrite 1997 by Scot J. Marburger
#
# Data File format notes:
# It consists of lines of the following format:
# ID^Camp Name^Type^City^State^X^Y^^Description^Date1[|Date2...]^minutes^[|minutes...]^toururl1[|toururl2...]^histurl1[|histurl2...]
# Note that the Date, minutes, toururl, and histurl fields are themselves sub-delimited
# by the | character
#
BEGIN {
unshift(@INC, '../');
}
require "home_def.cgi";
require "CGI.pm";
require "sjm_lib.cgi";
require "timelocal.pl";
# set up storage for form input
$query = new CGI;
# all these forms use the POST method
$method = 'POST';
# Start of configuration area
# urls
$web_server = "http://www.bmwnorcal.org";
$db_url = "$web_server/cgi/norcal/db_camp.cgi";
$hist_rep_url = "$web_server/norcal/bookshelf";
$min_rep_url = "$web_server/norcal/bookshelf";
$tour_rep_url = "$web_server/norcal/bookshelf";
# images
$img_line = "$web_server/norcal/images/line_blue_ltblue.gif";
# files
# $datafile1 = "$cgiHOME/norcal/db_test_out.dat";
$datafile1 = "$rootHOME/norcal/db_camp.dat";
$db_name1 = 'Camping';
$datafile2 = "$cgiHOME/norcal/event.dat";
$db_name2 = 'Supplier';
$head_file = "$cgiHOME/norcal/norcal_gen_header.html";
$tail_file = "$cgiHOME/norcal/norcal_gen_footer.html";
# company info
$CompanyName = "NorCal";
# End configuration area
# print the html header for text/html
print $query->header;
&printHead( $head_file );
# parse the form
$search_field = $query->param('search_field');
$case_sensitivity = $query->param('case_sensitivity');
$pattern = $query->param('pattern');
$name_pattern = $query->param('name_pattern');
$location_pattern = $query->param('location_pattern');
$pattern_x = $query->param('x');
$pattern_y = $query->param('y');
$month = $query->param('month');
$day = $query->param('day');
$year = $query->param('year');
$month_visit = $query->param('month_visit');
$year_visit = $query->param('year_visit');
$month_start = $query->param('month_start');
$day_start = $query->param('day_start');
$year_start = $query->param('year_start');
$month_end = $query->param('month_end');
$day_end = $query->param('day_end');
$year_end = $query->param('year_end');
# set up for displaying search results. the $col array is used as an index for
# accessing the elements of the @line arrray. The columns in the display tables
# are always displayed starting at $col[0] on the left to $col[n] on the right.
# So by adjusting the number stored in an element of $col, we access a different
# element of the @line array:
# 0 1 2 3 4 5 6 7 8 9 10 11
# @line = ($camp_key, $name, $admin, $location, $state, $x, $y, $general, $date, $desc, $tour_url, $hist_url )
#
# for example (I love examples)
# after the assignments made in the "Location" group below,
# $line[$col[0] = 3 and so "points" to where $location is stored in the @line array
# $line[$col[1] = 1 and so "points" to where $name is stored in the @line array
# $line[$col[2] = 4 and so "points" to where $state is stored in the @line array
# $line[$col[3] = 5 and so "points" to where $x is stored in the @line array
# $line[$col[4] = 6 and so "points" to where $y is stored in the @line array
# Now we can loop through the $col array to display the appropriate data in the appropriate
# place in the table. In the example, since we're doing a Location search, Location is
# displayed first.
# And as an added bonus, if we test the value of $col[$n], we can tell when a given data
# element is about to be displayed, and do something special like make it into a url.
$camp_key_field = 0;
$name_field = 1;
$admin_field = 2;
$location_field = 3;
$state_field = 4;
$x_field = 5;
$y_field = 6;
$general_field = 7;
$date_field = 8;
$desc_field = 9;
$tour_url_field = 10;
$hist_url_field = 11;
if ( $search_field =~ /Name/ ) {
$col[0] = $name_field; # Name
$col[1] = $location_field; # Location
$col[2] = $state_field; # State
$col[3] = $x_field; # X
$col[4] = $y_field; # Y
}
elsif ( $search_field =~ /Location/ ) {
$col[0] = $location_field; # Location
$col[1] = $name_field; # Name
$col[2] = $state_field; # State
$col[3] = $x_field; # X
$col[4] = $y_field; # Y
}
elsif ( $search_field =~ /Date/ ) {
$col[0] = $date_field; # Date
$col[1] = $name_field; # Name
$col[2] = $location_field; # Location
$col[3] = $state_field; # State
$col[4] = $x_field; # X
$col[5] = $y_field; # Y
}
elsif ( $search_field eq "xy" ) {
$col[0] = $x_field; # X
$col[1] = $y_field; # Y
$col[2] = $name_field; # Name
$col[3] = $location_field; # Location
$col[4] = $state_field; # State
}
elsif ( $search_field eq "yx" ) {
$col[0] = $y_field; # Y
$col[1] = $x_field; # X
$col[2] = $name_field; # Name
$col[3] = $location_field; # Location
$col[4] = $state_field; # State
}
# some more constants for information display
@alphabet = (A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,0,1,2,3,4,5,6,7,8,9);
@year_array = qw(1970 1971 1972 1973 1974 1975 1976 1977 1978 1979
1980 1981 1982 1983 1984 1985 1986 1987 1988 1989
1990 1991 1992 1993 1994 1995 1996 1997 1998 1999
2000 2001 2002 2003 2004 2005 2006 2007 2008 2009
2010 2011 2012 2013 2014 2015 2016 2017 2018 2019);
# 0 1 2 3 4 5 6
@lables = ("Key", "Name", "Administered By", "Location", "State", "X Coordinate", "Y Coordinate",
# 7 8
"Description", "Date");
# set up a month associative array
$month{ "Jan" } = 0;
$month{ "Feb" } = 1;
$month{ "Mar" } = 2;
$month{ "Apr" } = 3;
$month{ "May" } = 4;
$month{ "Jun" } = 5;
$month{ "Jul" } = 6;
$month{ "Aug" } = 7;
$month{ "Sep" } = 8;
$month{ "Oct" } = 9;
$month{ "Nov" } = 10;
$month{ "Dec" } = 11;
# and a regular array to go the other way
@month = ( "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" );
# which database?
$datafile = $datafile1;
$db_name = $db_name1;
# if ( $query->param( 'database' ) eq $db_name1 ) {
# $datafile = $datafile1;
# $db_name = $db_name1;
# }
# elsif ( $query->param( 'database' ) eq $db_name2 ) {
# $datafile = $datafile2;
# $db_name = $db_name2;
# }
#
# first time through, put up instructions and search tools.
#
if ( $query->param('Search') ) {
&new_search;
}
elsif ( $query->param('Alpha') ) {
α # display all companies that start with a letter
}
elsif ( $query->param('All') ) {
&all; # diaplay all companies
}
elsif ( $query->param('Start Date Search') ) { # date searches
&search_date();
}
else {
&setup(); # some directions, please
}
#end the HTML page
&printTail( $tail_file, $datafile );
print $query->endform;
###
# setup() Display some instructions and the search type buttons
###
sub setup
{
&clean_up( $query ); # zero out all the fields
print $query->start_html( "$CompanyName Search Configuration" );
print $query->startform;
print "
";
print "
Welcome to the $CompanyName $dbname Database.
";
print "
You may search for any Campsite Name or Location in the database using the
\"Search\" button, display all Campsites or Locations starting with a
specified letter using the \"Alpha\" button, or display the entire
database using the \"All\" button. Warning: The database
holds more than 150K of information, and may take a while to
download.
";
print "
";
print "Search Tool
Find Campsite Names and Locations:
Enter the Name or Location you wish to find in the database and click
the \"Search\" button. The search can be case sensitive (\"A\"
is not the same as \"a\") or not, and all entries that contain the
search pattern will be retrieved and displayed.
Case Sensitivity:";
print $query->radio_group(-name=>'case_sensitivity',
-values=>['On', 'Off'],
-default=>'Off');
print "
";
print "
All Tool
Click the \"All\" Button to display the entire database. Warning:
The entire database holds more than 150K of information, and may
take a while to download. ";
print "First, please pick the field by which you would like the list organized:
Campsite Location
Campsite Name
Meeting Date
X Map Coordinate
Y Map Coordinate
";
print $query->submit( 'All' );
print "
";
print $query->endform;
}
###
# all(). Displays all the text in the datafile in an appropriately formatted
# manner.
###
sub all
{
if ( $search_field eq "Date" ) {
&all_dates;
return;
}
# print the canned HTML header
print $query->start_html( "$CompanyName\'s Complete $db_name Index" );
print "
";
open( DATAFILE, $datafile ) || die( "Can't open $datafile!" );
# suck in the file
@file = ;
# Put up the table headers
print "
";
for ( $i = 0; $i < 5; $i++ ) {
print "
$lables[$col[$i]]
";
}
print "
";
foreach $letter ( @alphabet ) { # for each letter of the alphabet
foreach $line ( @file ) { # search every line in the buffer
&parse();
# $line[$col[0]] is always our search field
if ( $line[$col[0]] =~ /^$letter/ ) { # if the search field starts with our letter
$tmp_line = $line[$col[0]]."\^".$line; # put a copy of the search field at the
# front of the line for later sorting
@sortarray = ( @sortarray, $tmp_line ); # then save the line
}
}
@sortedarray = sort { lc( $a ) cmp lc( $b ) } @sortarray; # sort based on charas at
# start of each string ($line)
# in the sortarray
# if ( $keys[0] ne "" ) { # uncommenting prints Initials only for those letters that have associated campsites
print "
"; # Make initial letter into an anchor
# }
foreach $line ( @sortedarray ) {
&sort_parse(); # now get a line, but delete the temporary copy of the sort field
print "
";
for ( $i = 0; $i < 5; $i++ ) {
$bold_name = $line[$col[$i]]; # make a copy of the field to be hyperlinked
$enc_name = $line[$col[$i]]; # make a copy of the field to be hyperlinked
if ( $i == 0 ) { # this is the search field
$bold_name =~ s/^($letter)/$1<\/B>/i; # Bold the found pattern
}
if ( $col[$i] == $name_field ) { # is this camp name?
$enc_name =~ s/ /_/g; #change spaces to _ for url transmission
print "
";
}
undef @sortarray; # nuke the sort array
undef %sortedarray; # and the sorted array
}
print "
";
&setup; # Get ready for another search
}
###
# all_dates(). Displays all the text in the datafile in an appropriately formatted
# manner.
###
sub all_dates
{
# print the canned HTML header
print $query->start_html( "$CompanyName\'s Complete $db_name Index" );
print "
";
open( DATAFILE, $datafile ) || die( "Can't open $datafile!" );
# suck in the file
@file = ;
# Put up the table headers
print "
";
for ( $i = 0; $i < 6; $i++ ) {
print "
$lables[$col[$i]]
";
}
print "
";
foreach $pattern_year ( @year_array ) { # for each year
foreach $line ( @file ) { # search every line in the buffer
&parse();
$i = 0;
while ( $date[$i] ne "" ) {
$date[$i] =~ s/,//; # remove the comma
( $mm, $dd, $yy ) = split( / /, $date[$i] ); # get month, day and year
$testdate = &get_date( $mm, $dd, $yy); # convert to date number
if ( $yy eq $pattern_year ) { # is it the one we want?
$sortarray{ $testdate } = $line; # save line, using date for key
}
$i++;
}
}
@keys = sort numeric keys(%sortarray); # sort by date
print "
", $pattern_year, "
"; # Make initial letter into an anchor
foreach $key ( @keys ) { # now print each line we found
$line = $sortarray{ $key }; # get the line we found
&parse(); # now get a line from the sorted array
print "
";
$n = 0;
for ( $i = 0; $i < 6; $i++ ) {
if ( $col[$i] == $name_field ) { # is this camp name?
$bold_name = $line[$col[$i]]; # make it a hyperlink
$enc_name = $line[$col[$i]]; # make it a hyperlink
$enc_name =~ s/ /_/g; #change spaces to _ for url transmission
$bold_name =~ s/^($letter)/$1<\/B>/i; # Bold the found pattern
print "
";
}
elsif ( $col[$i] == $date_field ) { # this is the date
( $sec, $min, $hour, $mday, $mon, $year ) = localtime( $key );
$year += 1900;
print "
$month[$mon] $mday, $year
";
}
else {
print "
$line[$col[$i]]
";
}
}
$n++;
print "
";
}
undef %sortarray; # nuke the sort array
undef @keys; # and the sorted array
}
print "
";
&setup; # Get ready for another search
}
###
# alpha() Display all supplier names that start with a given letter
###
sub alpha
{
if ( $search_field eq "Name" ) {
$pattern = $name_pattern;
}
else {
$pattern = $location_pattern;
}
$pattern =~ s/_/ /g; # change _ to space in encoded pattern
print $query->start_html( "$search_field\s That Start With \"$pattern\"" );
print $query->startform;
open( DATAFILE, $datafile );
# build an array of the lines that have the correct info, keyed to the search field
@file = ;
foreach $line ( @file ) { # search every line in the buffer
&parse();
if ( $line[$col[0]] =~ /^$pattern/ ) {
$tmp_line = $line[$col[0]]."\^".$line; # put a copy of the search field at the
# front of the line for later sorting
@sortarray = ( @sortarray, $tmp_line ); # then save the line
}
}
@sortedarray = sort { lc( $a ) cmp lc( $b ) } @sortarray;
if ( @sortedarray ) {
print "
$search_field\s that start with \"$pattern\":";
print "
";
for ( $i = 0; $i < 5; $i++ ) {
print "
$lables[$col[$i]]
";
}
print "
";
foreach $line ( @sortedarray ) {
&sort_parse(); # now get a line, but delete the temporary copy of the sort field
print "
";
for ( $i = 0; $i < 5; $i++ ) {
if ( $col[$i] == $name_field ) { # is this camp name?
$tmp_name = $line[$col[$i]]; # make it a hyperlink
$tmp_name =~ s/ /_/g; #change spaces to _ for url transmission
$line[$col[$i]] =~ s/^($letter)/$1<\/B>/i; # Bold the found pattern
print "
";
}
else {
print "
Sorry, no $search_field\s that start with \"$pattern\" were found.
";
}
&setup();
}
###
# new_search() Print all supplier information that contains the given string
###
sub new_search
{
if ( $search_field eq "Name" ) {
$pattern = $name_pattern;
}
elsif ( $search_field =~ /Location/ ) {
$pattern = $location_pattern;
}
elsif ( $search_field =~ /xy/ ) {
$pattern = $pattern_x;
}
$pattern =~ s/_/ /g; # change _ to space in encoded pattern
if ( $search_field eq "xy" ) { # find matching x and y coords from imagemap
# print the canned HTML header
print $query->start_html( "Campsites in Coordinates X=$x, Y=$y" );
print "
Campsites that lie in map sector
X= \"$pattern_x\",
Y= \"$pattern_y\" are displayed below:
";
}
else {
# print the canned HTML header
print $query->start_html( "$search_field\s That Start With \"$pattern\"" );
print "
Your $search_field search results for pattern
\"$pattern\"are displayed below:
Case Sensitivity is ",
$case_sensitivity, ".
";
}
if ( $pattern eq "" ) {
print "
Sorry, no pattern specified. Please try again.
";
&setup();
return;
}
open( DATAFILE, $datafile ) || die( "Can't open $datafile!" );
# suck in the file
@file = ;
#print "pattern_x = $pattern_x, pattern_y = $pattern_y ";
foreach $line ( @file ) { # search every line in the buffer
&parse();
if ( $search_field eq "xy" ) { # find matching x and y coords from imagemap
if ( $pattern_x =~ /^$x/ && $pattern_y =~ /^$y/ ) {
$sortarray{ $line[0] } = $line; # save it for later, key is camp key
}
}
elsif ( $case_sensitivity eq "Off" ) {
if ( $line[$col[0]] =~ /$pattern/i ) { # if the line has our pattern
$sortarray{ $line[0] } = $line; # save it for later, key is camp name
}
}
else {
if ( $line[$col[0]] =~ /$pattern/ ) { # if the line has our pattern
$sortarray{ $line[0] } = $line; # save it for later, key is camp name
}
}
}
@keys = sort { $sortarray{ $a } cmp $sortarray{ $b } } keys %sortarray; # sort by our field
if ( $search_field ne "Name" ) {
# Put up the table headers for the non-Name search displays
print "
";
for ( $i = 0; $i < 5; $i++ ) {
print "
$lables[$col[$i]]
";
}
print "
";
}
foreach $key ( @keys ) { # now print each line we found
$line = $sortarray{ $key }; # get the line we found
&parse();
if ( $search_field eq "Name" ) {
print "Camp Name: $name
Camp Location: $location, $state
Run By: $admin
X Map Coordinate: $x
Y Map Coordinate: $y
Comments: $general
";
}
#print "hist = \'", $hist_url[$n], "\' ";
if ( ! ( $hist_url[$n] =~ /history/ ) && ( $hist_url[$n] ne "" )) {
print "Go To History Report ";
}
#print "tour = \'", $tour_url[$n], "\' ";
if ( ! ( $tour_url[$n] =~ /toureport/ ) && ( $tour_url[$n] ne "" )) {
print "Go To Tour Report ";
}
print " * * * * *
";
$n++;
}
}
else {
print "
";
for ( $i = 0; $i < 5; $i++ ) {
if ( $col[$i] == $name_field ) { # is this camp name?
$tmp_name = $line[$col[$i]]; # make it a hyperlink
$tmp_name =~ s/ /_/g; #change spaces to _ for url transmission
$line[$col[$i]] =~ s/^($letter)/$1<\/B>/i; # Bold the found pattern
print "
";
}
}
undef @keys; # nuke the keys array
undef %sortarray; # and the storage array
print "
";
&setup; # Get ready for another search
print $query->endform;
}
###
# sub search_date
###
sub search_date( )
{
$time = &get_date( $month, $day, $year );
# print "search_date time = $time ";
# print the canned HTML header
if ( $search_field eq "Date" ) { # find matching date
print $query->start_html( "$CompanyName Date Search Results For \"$month $day, $year\"" );
print "
Your search results for
\"$month $day, $year\" are displayed below: ";
}
elsif ( $search_field eq "Date_month" ) { # find matching month only
print $query->start_html( "$CompanyName Date Search Results For \"$month_visit\"" );
print "
Your search results for
\"$month_visit\" are displayed below: ";
}
elsif ( $search_field eq "Date_year" ) { # find matching month only
print $query->start_html( "$CompanyName Date Search Results For \"$year_visit\"" );
print "
Your search results for
\"$year_visit\" are displayed below: ";
}
elsif ( $search_field eq "Date_range" ) { # find matching month only
print $query->start_html( "$CompanyName Search Results For Visits Between
\"$month_start $day_start, $year_start\" And \"$month_end $day_end, $year_end\"" );
print "
Your search results for Visits Between
\"$month_start $day_start, $year_start And
$month_end $day_end, $year_end\"
are displayed below: ";
}
open( DATAFILE, $datafile ) || die( "Can't open $datafile!" );
# suck in the file
@file = ;
# get a numerical version of our date pattern
$datepattern = &get_date( $month, $day, $year);
$startpattern = &get_date( $month_start, $day_start, $year_start);
$endpattern = &get_date( $month_end, $day_end, $year_end);
foreach $line ( @file ) { # search every line in the buffer
&parse();
if ( $search_field eq "Date" ) { # find matching date
$i = 0;
while ( $date[$i] ne "" ) {
$date[$i] =~ s/,//; # remove the comma
( $mm, $dd, $yy ) = split( / /, $date[$i] ); # get month, day and year
$testdate = &get_date( $mm, $dd, $yy); # convert to date number
if ( $testdate == $datepattern ) { # is it the one we want?
$sortarray{ $testdate } = $line; # save line, using date for key
}
$i++;
}
}
elsif ( $search_field eq "Date_month" ) { # find matching month only
$i = 0;
while ( $date[$i] ne "" ) {
$date[$i] =~ s/,//; # remove the comma
( $mm, $dd, $yy ) = split( / /, $date[$i] ); # get month, day and year
$testdate = &get_date( $mm, $dd, $yy); # convert to date number
if ( $mm eq $month_visit ) { # is it the one we want?
$sortarray{ $testdate } = $line; # save line, using date for key
}
$i++;
}
}
elsif ( $search_field eq "Date_year" ) { # find matching month only
$i = 0;
while ( $date[$i] ne "" ) {
$date[$i] =~ s/,//; # remove the comma
( $mm, $dd, $yy ) = split( / /, $date[$i] ); # get month, day and year
$testdate = &get_date( $mm, $dd, $yy); # convert to date number
if ( $yy eq $year_visit ) { # is it the one we want?
$sortarray{ $testdate } = $line; # save line, using date for key
#print "line = ", $line, " ";
}
$i++;
}
}
elsif ( $search_field eq "Date_range" ) { # find matching month only
$i = 0;
while ( $date[$i] ne "" ) {
$date[$i] =~ s/,//; # remove the comma
( $mm, $dd, $yy ) = split( / /, $date[$i] ); # get month, day and year
$testdate = &get_date( $mm, $dd, $yy); # convert to date number
if ( ( $startpattern <= $testdate ) && ( $testdate <= $endpattern ) ) { # is it the one we want?
$sortarray{ $testdate } = $line; # save line, using date for key
}
$i++;
}
}
}
@keys = sort numeric keys(%sortarray); # sort by date
if ( $search_field =~ /Date_/ ) {
# Put up the table headers for the non-Name search displays
print "
";
for ( $i = 0; $i < 6; $i++ ) {
print "
$lables[$col[$i]]
";
}
print "
";
}
foreach $key ( @keys ) { # now print each line we found
$line = $sortarray{ $key }; # get the line we found
&parse();
if ( $search_field eq "Date" ) {
print "Visit Date: $date[$n] ";
print "Camp Name: $name
Camp Location: $location, $state
X Map Coordinate: $x
Y Map Coordinate: $y
Comments: $description
";
if ( ! $hist_url[$n] =~ /^No Historian\'s Report/ ) {
print "Go To History Report ";
}
if ( ! $tour_url[$n] =~ /^No Tour Description/ ) {
print "Go To Tour Report ";
}
print " * * * * *