94334d8127ce9510e334af0e609115e88dedd021
[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 %fields = @pairs;
18 chomp(%fields);
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         print "<a class=\"diritem\" href=\"$fields{'title'}\">$fields{'title'}</a>\n";
72         
73     }
74 }
75
76 print "<br><br><br>\n";
77
78 #display the search form
79 if($fields{'do'} eq "home" || $fields{'do'} eq ""){
80
81 #isbn,title,authors,year,edition,publisher 
82
83     print <<END;
84     <center>
85     <br>
86     <h3>Computer Science Library Collection Search</h3>
87     <br>
88     <br>
89
90     <table>
91
92     <form name="search" action="index.cgi" method="get">
93         <input type="hidden" name="do" value="query" />
94         
95         <tr><td>Title:</td> <td><input type="text" name="title" /></td></tr>
96         
97         <tr><td>Publisher:</td> <td><input type="text" name="publisher" /><td></tr>
98         
99         <tr><td>Edition:</td> <td><input type="text" name="edition" /></td></tr>
100
101         <tr><td>Year:</td> <td><input type="text" name="year" /></td></tr>
102         
103         <tr><td>ISBN:</td> <td><input type="text" name="isbn" /></td></tr>
104
105     <tr>
106     <td></td>
107     <td><input type="submit" value="Search" /><br>
108     </td>
109     </tr>
110     </form>
111     </table>
112     </center>
113
114 END
115
116 }
117
118 #query the database
119 if($fields{'do'} eq "query"){
120
121     print "<h3>Search Results</h3>";
122
123     #build query string here, query and display.
124     my $dbh = DBI->connect("dbi:Pg:dbname=library;host=localhost", 
125                            'librarian', 
126                            'KJI3RVgszrxrxe6W4gX3', 
127                            {AutoCommit => 1})
128         or print "Couldn't connect to database: " . DBI->errstr;
129
130     $searchterm = undef;
131     $orterm = undef;
132     @results;
133     @oresults;
134     $title = $fields{'title'};
135     $title =~ s/[';:"]/ /g;
136     $publisher = $fields{'publisher'};
137     $publisher =~ s/[';:"]/ /g;
138     $edition = $fields{'edition'};
139     $edition =~ s/[';:"]/ /g;
140     $year = $fields{'year'};
141     $year =~ s/[';:"]/ /g;
142     $isbn = $fields{'isbn'};
143     $isbn =~ s/[';:"]/ /g;
144
145     if($title){
146
147         @tmp = split(/\+/,$title);
148         my @keywords;
149         my @orwords;
150         push @keywords,"'%$tmp[0]%'";
151         push @orwords,"'%tmp[0]%'";
152         shift @tmp;
153         
154         foreach $keyword (@tmp){
155             push @keywords,"AND (title ILIKE '%$keyword%')";
156             push @orwords,"OR (title ILIKE '%$keyword%')";
157         }
158         
159         $searchterm = "(title ILIKE @keywords)";
160         $orterm = "(title ILIKE @orwords)";
161
162     }
163
164     if($publisher){
165         
166         @tmp = split(/\+/,$publisher);
167         my @keywords;
168         my @orwords;
169         push @keywords,"'%$tmp[0]%'";
170         shift @tmp;
171
172         foreach $keyword (@tmp){
173             push @keywords,"AND (publisher ILIKE '%$keyword%')";
174             push @orwords,"OR (publisher ILIKE '%$keyword%')";
175         }
176         
177         if($searchterm eq undef){
178             $searchterm = "(publisher ILIKE @keywords)";
179             $orterm = "(publisher ILIKE @orwords)";
180         }else{
181             $temp = $searchterm;
182             $searchterm = $temp." AND (publisher ILIKE @keywords)";
183             $temp = $orterm;
184             $orterm = $temp." OR (publisher ILIKE @keywords)";
185         }
186         
187     }
188
189     if($edition){
190         
191         @tmp = split(/\+/,$edition);
192         my @keywords;
193         my @orwords;
194         push @keywords,"'%$tmp[0]%'";
195         push @orwords,"'%$tmp[0]%'";
196         shift @tmp;
197
198         foreach $keyword (@tmp){
199             push @keywords,"AND (edition ILIKE '%$keyword%')";
200             push @keywords,"OR (edition ILIKE '%$keyword%')";
201         }
202
203         if($searchterm eq undef){
204             $searchterm = "(edition ILIKE @keywords)";
205             $orterm = "(edition ILIKE @keywords)"
206         }else{
207             $temp = $searchterm;
208             $searchterm = $temp." AND (edition ILIKE @keywords)";
209             $temp = $orterm;
210             $orterm = $temp." OR (edition ILIKE @keywords)";                                                                                                                                                                               
211         }
212
213     }
214
215     if($year){
216         
217         @tmp = split(/\+/,$year);
218         my @keywords;
219         my @orwords;
220         push @keywords,"'%$tmp[0]%'";
221         push @orwords,"'%$tmp[0]%'";
222         shift @tmp;
223
224         foreach $keyword (@tmp){
225             push @keywords,"AND (year ILIKE '%$keyword%')";
226             push @orwords,"OR (year ILIKE '%$keyword%')";
227         }
228
229         if($searchterm eq undef){
230             $searchterm = "(year ILIKE @keywords)";
231             $orterm = "(year ILIKE $orwords)";
232         }else{
233             $temp = $searchterm;
234             $searchterm = $temp." AND (year ILIKE @keywords)";
235             $temp = $orterm;
236             $orterm = $temp." OR (year ILIKE @orwords)";
237             
238         }
239
240     }
241
242     if($isbn){
243         
244         @tmp = split(/\+/,$isbn);
245         my @keywords;
246         my @orwords;
247         push @keywords,"'%$tmp[0]%'";
248         push @orwords,"'%$tmp[0]%'";
249         shift @tmp;
250
251         foreach $keyword (@tmp){
252             push @keywords,"AND (isbn ILIKE '%$keyword%')";
253             push @orwords,"OR (isbn ILIKE '%keyword%')";
254         }
255
256         if($searchterm eq undef){
257             $searchterm = "(isbn ILIKE @keywords)";
258             $orterm = "(isbn ILIKE @keywords)";
259         }else{
260             $temp = $searchterm;
261             $searchterm = $temp." AND (isbn ILIKE @keywords)";
262             $temp = $orterm;
263             $orterm = $temp." OR (year ILIKE @orwords)";
264         }
265
266     }
267
268     if($searchterm){
269
270         my $sth = $dbh->prepare("SELECT title,isbn,year,publisher,edition FROM book WHERE $searchterm;")
271             or print "Couldn't prepare statement: " . $dbh->errstr;
272
273         my $oth = $dbh->prepare("SELECT title,isbn,year,publisher,edition FROM book WHERE $orterm;")
274             or print "Couldn't prepare statement: " . $dbh->errstr;
275         
276         $sth->execute()             # Execute the query
277             or print "Couldn't execute statement: " . $sth->errstr;
278         
279         while($data = $sth->fetchrow_arrayref()){
280             push @results, [@{$data}];
281         }
282
283         $oth->execute()
284             or print "Couldn't execute statement: " . $oth->errstr;
285
286         while($ordata = $oth->fetchrow_arrayref()){
287             push @oresults, [@{$ordata}];
288         }
289
290         $sth->finish;
291         $oth->finish;
292
293         
294         if ($sth->rows == 0 && $oth->rows == 0) {
295             print "<center>No names matched</center></br>";
296         }else{
297             print "<center><table border>";
298             print "<tr> <td>Title</td> <td>ISBN</td> <td>Year</td> <td>Publisher</td> <td>Edition</td> </tr>";
299             
300             for my $book (@results){
301                 print "<tr>";
302                 for my $field (@{$book}){
303                     print "<td> $field </td>\n";
304                 }
305                 print "</tr>";
306             }
307
308             for my $book (@oresults){
309                 foreach $andbook (@results){
310                     if(@{$book}[0] eq @{$andbook}[0]){
311                         $book = undef;
312                     }
313                 }
314                 
315                 if($book){
316                     print "<tr>";
317                     for my $field (@{$book}){
318                         print "<td> $field </td>\n";
319                     }
320                     print "</tr>";
321                 }
322             }
323
324             print "</table></center>";
325         }
326         
327     }else{
328         print "<br><center>You did not enter any search terms.</center></br>";
329     }
330 }
331
332 print <<END;
333
334 <br><br>
335
336 <div class="footer">
337 <div class="menubar">
338 END
339
340 open(MENU,"/users/www/www/menu.xml") or print "Couldn't open file\n";
341 @items = <MENU>;
342 close(MENU);
343
344 $items[0] = undef;
345 $items[-1] = undef;
346
347 foreach $item (@items){
348     if($item){
349         $item =~ s/[<>]//g;
350         $item =~ s/\/$//g;
351         $item =~ s/menuitem//g;
352         $item =~ s/ //;
353         @pairs = split(/[ =]/,$item);
354         shift @pairs;
355         %fields = @pairs;
356         $fields{'href'} =~ s/"//g;
357         chomp($fields{'href'});
358         $fields{'icon'} =~ s/"//g;
359
360         print "<div class=\"menuitem\">\n";
361
362         if($fields{'absolute'} =~ /true/){
363             print "<a href=\"$fields{'href'}\">\n";
364         }else{
365             print "<a href=\"$webroot/$fields{'href'}\">\n"
366         }
367
368         print "<img src=\"$webroot/buttons/$fields{'icon'}.png\" width=\"40\" height=\"40\" alt=$fields{'title'}/>\n";
369
370         $fields{'title'} =~ s/"//g;
371
372         print "<br/>$fields{'title'}</a></div>\n\n";
373
374     }
375 }
376
377 print <<END;
378
379 </div>
380
381 <a href="#pagetop" class="pagetop">Go to top</a>
382
383 <div class="infobox">
384
385 <p>
386 This site supports TLS/SSL using a certificate signed by the
387 "CSC Certificate Authority". Click <a href="$webroot/csclub.pe\
388 m">
389 here</a> (PEM format) or <a href="$webroot/csclub.der">here</a\
390 > (DER
391 format) to install the certificate authority into your browser\
392 .
393 </p>
394
395 </div>
396
397 </body>
398 </html>
399
400 END