ca41398986d176c371224445461171640cd42daf
[mspang/www.git] / library / index.cgi
1 #!/usr/bin/perl
2
3 ###############################################
4 # Postgres SQL Library Database Web Interface #
5 # Matt Thiffault (January 22,2009)            #
6 # matt.thiffault@gmail.com                    #
7 ###############################################
8
9
10 use DBI;
11
12 print "Content-type: text/html\n\n";
13
14 $in = $ENV{'QUERY_STRING'};
15
16 @pairs = split(/[&=]/,$in);
17 %field = @pairs;
18 chomp(%field);
19 $webroot = "..";
20
21 print <<END;
22
23 <html xmlns="http://www.w3.org/1999/xhtml">
24 <head><link rel="shortcut icon" href="/favicon.ico"/>
25 <link rel="alternate" title="CSC Events Feed" href="$webroot/events.atom" type="application/rss+xml"/>
26 <link rel="alternate" title="CSC News Feed" href="$webroot/news.atom" type="application/rss+xml"/>
27 <title>Computer Science Club of the University of Waterloo: Home</title>
28 <link rel="stylesheet" href="$webroot/default.css" type="text/css"/></head>
29 <body>
30 <div class="content">
31 <a id="pagetop"/>
32 <div class="biglogo">
33 <div class="uwlogo">
34 <a href="http://www.uwaterloo.ca/"><img src="$webroot/logos/uw_logo.png" alt="University of Waterloo" width="105" height="70"/></a>
35 </div><div class="csclogo">
36 <a href="/"><img src="$webroot/logos/csc_logo.png" alt="Computer Science Club" width="420" height="79"/></a>
37 </div></div>
38 <div class="dirheader">
39
40 END
41
42
43 open(DIR,"/users/www/www/directory.xml") or print "Couldn't open file\n";
44 @items = <DIR>;
45 close(MENU);
46
47 shift @items;
48 shift @items;
49 shift @items;
50 shift @items;
51 shift @items;
52 $items[-1] = undef;
53
54 foreach $item (@items){
55     if($item){
56         
57         $item =~ s/[<>]//g;
58         $item =~ s/\/$//g;
59         $item =~ s/menuitem//g;
60         $item =~ s/ //;
61         @pairs = split(/[ =]/,$item);
62         shift @pairs;
63         %fields = @pairs;
64         
65         $fields{'href'} =~ s/"//g;
66         
67         chomp($fields{'href'});
68         
69         $fields{'title'} =~ s/"//g;
70
71         if($fields{'absolute'} =~ /true/){
72             print "<a class=\"diritem\" href=\"$fields{'href'}\">$fields{'title'}</a>\n";
73         }else{
74             print "<a class=\"diritem\" href=\"$webroot/$fields{'href'}\">$fields{'title'}</a>\n";
75         }
76     }
77 }
78
79 print "<br><br><br>\n";
80
81 #display the search form
82 if($field{'do'} eq "home" || $field{'do'} eq ""){
83
84 #isbn,title,authors,year,edition,publisher
85
86     print <<END;
87     <center>
88     <br>
89     <h3>Computer Science Library Collection Search</h3>
90     <br>
91     <br>
92
93     <table>
94
95     <form name="search" action="index.cgi" method="get">
96         <input class="libraryInput" type="hidden" name="do" value="query" />
97
98         <tr><td>Title:</td> <td><input class="library" type="text" name="title" /></td></tr>
99
100         <tr><td>Publisher:</td> <td><input class="library" type="text" name="publisher" /><td></tr>
101
102         <tr><td>Edition:</td> <td><input class="library" type="text" name="edition" /></td></tr>
103
104         <tr><td>Year:</td> <td><input class="library" type="text" name="year" /></td></tr>
105
106         <tr><td>ISBN:</td> <td><input class="library" type="text" name="isbn" /></td></tr>
107
108     <tr>
109     <td></td>
110     <td><input type="submit" value="Search" /><br>
111     </td>
112     </tr>
113     </form>
114     </table>
115     </center>
116
117 END
118
119 }
120
121 #query the database
122 if($field{'do'} eq "query"){
123
124     print "<h3>Search Results</h3>";
125
126     #build query string here, query and display.
127     my $dbh = DBI->connect("dbi:Pg:dbname=library;host=localhost", 
128                            'librarian', 
129                            'KJI3RVgszrxrxe6W4gX3', 
130                            {AutoCommit => 1})
131         or print "Couldn't connect to database: " . DBI->errstr;
132
133     $searchterm = undef;
134     $orterm = undef;
135     @results;
136     @oresults;
137     $title = $field{'title'};
138     $title =~ s/[';:"]/ /g;
139     $publisher = $field{'publisher'};
140     $publisher =~ s/[';:"]/ /g;
141     $edition = $field{'edition'};
142     $edition =~ s/[';:"]/ /g;
143     $year = $field{'year'};
144     $year =~ s/[';:"]/ /g;
145     $isbn = $field{'isbn'};
146     $isbn =~ s/[';:"]/ /g;
147
148     if($title){
149
150         @tmp = split(/\+/,$title);
151         my @keywords;
152         my @orwords;
153         push @keywords,"'%$tmp[0]%'";
154         push @orwords,"'%tmp[0]%'";
155         shift @tmp;
156         
157         foreach $keyword (@tmp){
158             push @keywords,"AND (title ILIKE '%$keyword%')";
159             push @orwords,"OR (title ILIKE '%$keyword%')";
160         }
161         
162         $searchterm = "(title ILIKE @keywords)";
163         $orterm = "(title ILIKE @orwords)";
164
165     }
166
167     if($publisher){
168         
169         @tmp = split(/\+/,$publisher);
170         my @keywords;
171         my @orwords;
172         push @keywords,"'%$tmp[0]%'";
173         shift @tmp;
174
175         foreach $keyword (@tmp){
176             push @keywords,"AND (publisher ILIKE '%$keyword%')";
177             push @orwords,"OR (publisher ILIKE '%$keyword%')";
178         }
179         
180         if($searchterm eq undef){
181             $searchterm = "(publisher ILIKE @keywords)";
182             $orterm = "(publisher ILIKE @orwords)";
183         }else{
184             $temp = $searchterm;
185             $searchterm = $temp." AND (publisher ILIKE @keywords)";
186             $temp = $orterm;
187             $orterm = $temp." OR (publisher ILIKE @keywords)";
188         }
189         
190     }
191
192     if($edition){
193         
194         @tmp = split(/\+/,$edition);
195         my @keywords;
196         my @orwords;
197         push @keywords,"'%$tmp[0]%'";
198         push @orwords,"'%$tmp[0]%'";
199         shift @tmp;
200
201         foreach $keyword (@tmp){
202             push @keywords,"AND (edition ILIKE '%$keyword%')";
203             push @keywords,"OR (edition ILIKE '%$keyword%')";
204         }
205
206         if($searchterm eq undef){
207             $searchterm = "(edition ILIKE @keywords)";
208             $orterm = "(edition ILIKE @keywords)"
209         }else{
210             $temp = $searchterm;
211             $searchterm = $temp." AND (edition ILIKE @keywords)";
212             $temp = $orterm;
213             $orterm = $temp." OR (edition ILIKE @keywords)";                                                                                                                                                                               
214         }
215
216     }
217
218     if($year){
219         
220         @tmp = split(/\+/,$year);
221         my @keywords;
222         my @orwords;
223         push @keywords,"'%$tmp[0]%'";
224         push @orwords,"'%$tmp[0]%'";
225         shift @tmp;
226
227         foreach $keyword (@tmp){
228             push @keywords,"AND (year ILIKE '%$keyword%')";
229             push @orwords,"OR (year ILIKE '%$keyword%')";
230         }
231
232         if($searchterm eq undef){
233             $searchterm = "(year ILIKE @keywords)";
234             $orterm = "(year ILIKE $orwords)";
235         }else{
236             $temp = $searchterm;
237             $searchterm = $temp." AND (year ILIKE @keywords)";
238             $temp = $orterm;
239             $orterm = $temp." OR (year ILIKE @orwords)";
240             
241         }
242
243     }
244
245     if($isbn){
246         
247         @tmp = split(/\+/,$isbn);
248         my @keywords;
249         my @orwords;
250         push @keywords,"'%$tmp[0]%'";
251         push @orwords,"'%$tmp[0]%'";
252         shift @tmp;
253
254         foreach $keyword (@tmp){
255             push @keywords,"AND (isbn ILIKE '%$keyword%')";
256             push @orwords,"OR (isbn ILIKE '%keyword%')";
257         }
258
259         if($searchterm eq undef){
260             $searchterm = "(isbn ILIKE @keywords)";
261             $orterm = "(isbn ILIKE @keywords)";
262         }else{
263             $temp = $searchterm;
264             $searchterm = $temp." AND (isbn ILIKE @keywords)";
265             $temp = $orterm;
266             $orterm = $temp." OR (year ILIKE @orwords)";
267         }
268
269     }
270
271     if($searchterm){
272
273         my $sth = $dbh->prepare("SELECT title,isbn,year,publisher,edition FROM book WHERE $searchterm;")
274             or print "Couldn't prepare statement: " . $dbh->errstr;
275
276         my $oth = $dbh->prepare("SELECT title,isbn,year,publisher,edition FROM book WHERE $orterm;")
277             or print "Couldn't prepare statement: " . $dbh->errstr;
278         
279         $sth->execute()             # Execute the query
280             or print "Couldn't execute statement: " . $sth->errstr;
281         
282         while($data = $sth->fetchrow_arrayref()){
283             push @results, [@{$data}];
284         }
285
286         $oth->execute()
287             or print "Couldn't execute statement: " . $oth->errstr;
288
289         while($ordata = $oth->fetchrow_arrayref()){
290             push @oresults, [@{$ordata}];
291         }
292
293         $sth->finish;
294         $oth->finish;
295
296         
297         if ($sth->rows == 0 && $oth->rows == 0) {
298             print "<center>No names matched</center></br>";
299         }else{
300             print "<center><table border>";
301             print "<tr> <td>Title</td> <td>ISBN</td> <td>Year</td> <td>Publisher</td> <td>Edition</td> </tr>";
302             
303             for my $book (@results){
304                 print "<tr>";
305                 for my $field (@{$book}){
306                     print "<td> $field </td>\n";
307                 }
308                 print "</tr>";
309             }
310
311             for my $book (@oresults){
312                 foreach $andbook (@results){
313                     if(@{$book}[0] eq @{$andbook}[0]){
314                         $book = undef;
315                     }
316                 }
317                 
318                 if($book){
319                     print "<tr>";
320                     for my $field (@{$book}){
321                         print "<td> $field </td>\n";
322                     }
323                     print "</tr>";
324                 }
325             }
326
327             print "</table></center>";
328         }
329         
330     }else{
331         print "<br><center>You did not enter any search terms.</center></br>";
332     }
333 }
334
335 print <<END;
336
337 <br><br>
338
339 <div class="footer">
340 <div class="menubar">
341 END
342
343 open(MENU,"/users/www/www/menu.xml") or print "Couldn't open file\n";
344 @items = <MENU>;
345 close(MENU);
346
347 $items[0] = undef;
348 $items[-1] = undef;
349
350 foreach $item (@items){
351     if($item){
352         $item =~ s/[<>]//g;
353         $item =~ s/\/$//g;
354         $item =~ s/menuitem//g;
355         $item =~ s/ //;
356         @pairs = split(/[ =]/,$item);
357         shift @pairs;
358         %fields = @pairs;
359         $fields{'href'} =~ s/"//g;
360         chomp($fields{'href'});
361         $fields{'icon'} =~ s/"//g;
362
363         print "<div class=\"menuitem\">\n";
364
365         if($fields{'absolute'} =~ /true/){
366             print "<a href=\"$fields{'href'}\">\n";
367         }else{
368             print "<a href=\"$webroot/$fields{'href'}\">\n"
369         }
370
371         print "<img src=\"$webroot/buttons/$fields{'icon'}.png\" width=\"40\" height=\"40\" alt=$fields{'title'}/>\n";
372
373         $fields{'title'} =~ s/"//g;
374
375         print "<br/>$fields{'title'}</a></div>\n\n";
376
377     }
378 }
379
380 print <<END;
381
382 </div>
383
384 <a href="#pagetop" class="pagetop">Go to top</a>
385
386 <div class="infobox">
387
388 <p>
389 This site supports TLS/SSL using a certificate signed by the
390 "CSC Certificate Authority". Click <a href="$webroot/csclub.pe\
391 m">
392 here</a> (PEM format) or <a href="$webroot/csclub.der">here</a\
393 > (DER
394 format) to install the certificate authority into your browser\
395 .
396 </p>
397
398 </div>
399
400 </body>
401 </html>
402
403 END