1 #! /usr/local/bin/perl -T
2
3 use strict;
4
5 use warnings;
6
7 use DBI;
8
9 use CGI;
10
11 use CGI::Carp; This Line Isn't Really In The Actual Program
12
13 use Time::HiRes qw ( gettimeofday tv_interval);
14
15 my $now = [gettimeofday];
16
17 use Fcntl qw(:flock);
18
19 $ENV{'PATH'} = '/bin:/usr/bin';
20
21 $ENV{'IFS'} = '';
22
23 open LOG, ">>/web/webdata/w1sdm/73/database.log" or die "Cannot open log file\n$!\n";
24
25 flock (LOG, LOCK_EX());
26
27 my @days = ("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday");
28
29 my @months = (" ", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "D
30
31 my ($sec,$min,$hour,$mday,$mon,$year,$wday) = (localtime(time))[0,1,2,3,4,5,6];
32
33 my $time = sprintf("%02d:%02d:%02d",$hour,$min,$sec);
34
35 $year += 1900;
36
37 ++$mon;
38
39 my $date = "$days[$wday], $months[$mon] $mday, $year at $time";
40
41 print LOG "Date: $date\n";
42
43 my $dir = "/web/webdata/w1sdm";
44
45 our ($db_connected_flag, $dbh);
46
47 my $q = new CGI;
48
49 my $title = lc($q->param('title'));
50
51 my $author = lc($q->param('author'));
52
53 my $form_month = $q->param('issue_month');
54
55 my $form_year = $q->param('issue_year');
56
57 my $info = "Search submission time: $date\n\n<br>\n\nBrowser: $ENV{'HTTP_USER_AGENT'}\n\n<br>\n\nIP Address: $ENV{'REMOTE_ADDR'}\n\
58
59 print LOG "Browser: $ENV{'HTTP_USER_AGENT'}\nIP Address: $ENV{'REMOTE_ADDR'}\nTitle: $title\nAuthor: $author\nMonth: $form_month\nY
60
61 my ($author_search_terms, $title_search_terms, $mysql_command, @mysql_data, @rows, $html, @search_terms);
62
63 if ($author) { $author_search_terms = clean($author); }
64
65 if ($title) { $title_search_terms = clean($title); }
66
67 $html = load_file("$dir/73/templates/results.html");
68
69 $mysql_command = "SELECT * FROM data WHERE";
70
71 if ($author_search_terms) {
72
73 ($mysql_command, @mysql_data) = expand_terms($mysql_command, 'author', $author_search_terms, @mysql_data);
74
75 }
76
77 if ($title_search_terms) {
78
79 ($mysql_command, @mysql_data) = expand_terms($mysql_command, 'title', $title_search_terms, @mysql_data);
80
81 }
82
83 if ($form_month) {
84
85 ($mysql_command, @mysql_data) = append_term($mysql_command, 'month', $form_month, @mysql_data);
86
87 $html =~ s/<option selected="selected"><\/option>/<option><\/option>/;
88
89 $html =~ s/<option>$form_month<\/option>/<option selected="selected">$form_month<\/option>/;
90
91 }
92
93 if ($form_year) {
94
95 ($mysql_command, @mysql_data) = append_term($mysql_command, 'year', $form_year, @mysql_data);
96
97 $html =~ s/<option selected="selected"><\/option>/<option><\/option>/;
98
99 $html =~ s/<option>$form_year<\/option>/<option selected="selected">$form_year<\/option>/;
100
101 }
102
103 if (! $form_year and ! $form_month) {
104
105 $html =~ s/<option><\/option>/<option selected="selected"><\/option>/g;
106
107 }
108
109 print LOG "MySQL Command: $mysql_command\nMySQL Data: @mysql_data\n";
110
111 my $search_start_time = [gettimeofday];
112
113 @rows = get_records($mysql_command, @mysql_data);
114
115 my $search_time = substr(sprintf ( "%.6f", tv_interval $search_start_time, [gettimeofday] ), 0, 8) . " seconds";
116
117 $html =~ s/<!--FORM_AUTHOR-->/$author/g;
118
119 $html =~ s/<!--FORM_TITLE-->/$title/g;
120
121 my $table = "<table class=\"results\">\n\n<tr class=\"row_yellow\"><th>Date</th><th>Title</th><th>Author</th><th>Page</th></tr>\n\n";
122
123 $info .= "<br>\n\nNumber of results: " . ($#rows + 1) . "\n\n";
124
125 print LOG "Results: $#rows\n";
126
127 if ($#rows < 0) {
128
129 $table .= "<tr class=\"row_green\"><td colspan=\"4\">No results using those search terms...</td></tr>\n\n";
130
131 } else {
132
133 my $row_color;
134
135 foreach my $x (0..@rows) {
136
137 $row_color = "row_blue";
138
139 if ($x / 2 == int($x / 2)) { $row_color = "row_green"; }
140
141 if ($rows[$x][0]) {
142
143 $table .= "<tr class=\"$row_color\"><td class=\"col1\">$rows[$x][1] $rows[$x][2]</td><td class=\"col2\">$rows[$x][3]</td><td class=\"col>$rows[$x][4]</td><td class=\"col4\">$rows[$x][5]</td></tr>\n\n";
144
145 }
146
147 }
148
149 }
150
151 $table .= "</table>\n\n";
152
153 $html =~ s/<!--TABLE-->/$table/;
154
155 my $creation_time = substr(sprintf ( "%.6f", tv_interval $now, [gettimeofday] ), 0, 8) . " seconds";
156
157 my $html_size = int(length($html) / 1000) . "K bytes";
158
159 $info .= "<br>\n\nHTML file size: ~$html_size\n\n<br>\n\nDatabase search time: $search_time\n\n<br>\n\nPage creation time: $creation_time\n\n";
160
161 $html =~ s/<!--INFO-->/$info/;
162
163 print "Content-Type: text/html\n\n$html";
164
165 print LOG "Database access time: $search_time\nPage creation time: $creation_time\n" . ('*' x 75) . "\n";
166
167 flock (LOG, LOCK_UN());
168
169 sub load_file {
170
171 my $filename = shift;
172
173 open (IN, "<$filename") or die "Content-Type: text/plain\n\nCould not open file: $filename\n";
174
175 return join('', <IN>);
176
177 }
178
179 sub get_records {
180
181 my ($mysql_command, @mysql_data) = @_;
182
183 $dbh = DBI->connect("dbi:mysql:secretstuff","secretstuff","secretstuff");
184
185 my $sth = $dbh->prepare($mysql_command);
186
187 $sth->execute(@mysql_data);
188
189 my @rows = ();
190
191 while ( my @row = $sth->fetchrow_array() ) { push @rows, [@row]; }
192
193 return @rows;
194
195 }
196
197 sub clean {
198
199 my $string = shift;
200
201 $string =~ s/ /SpAcE/g;
202
203 $string =~ s/\s*[\W]*//g;
204
205 $string =~ s/SpAcE/ /g;
206
207 return $string;
208
209 }
210
211 sub append_term {
212
213 my ($this_command, $element, $term, @this_data) = @_;
214
215 if (index($this_command, "LIKE") > -1 or index($this_command, " = ") > -1) { $this_command .= " AND "; }
216
217 $this_command .= " $element = (?)";
218
219 push @this_data,$term;
220
221 return ($this_command, @this_data);
222
223 }
224
225 sub expand_terms {
226
227 my ($this_command, $element, $search_terms, @this_data) = @_;
228
229 @search_terms = split " ", $search_terms;
230
231 foreach my $term (@search_terms) {
232
233 if (index($this_command, "LIKE") > -1) { $this_command .= ' AND'; }
234
235 $this_command .= " $element LIKE (?)";
236
237 push @this_data, "\%$term\%";
238
239 }
240
241 return ($this_command, @this_data);
242
243 }
244