#!/usr/bin/perl
use Pg;
use CGI qw(:standard);
use CGI qw(:cgi-lib);
use CGI::Carp qw(fatalsToBrowser);
###########################################################
# Copyright Paul Barrette and the Trajan Column Project,  #
# 1999.                                                   #
# Interested parties may use parts and/or adapt           #
# this script for their own NON-COMMERCIAL projects       #
# provided that this copywrite notice is kept intact      #
# at the head of the CGI script.                          #
# Thank you.                                              #
# Paul Barrette, barretp@mcmaster.ca                      #
###########################################################
print header;
ReadParse();

#get checkboxes and string, check for default values
$scenenumber=$in{scenenumber};  #*
$drumnumber=$in{drumnumber};  #menu
$spiral=$in{spiral};  #menu
$vwdescription=$in{vwdescription};  #subject  *
$notessubject=$in{notessubject};  #subject description  *
$subjectkeywords=$in{subjectkeywords};  #*
$notesdescription=$in{notesdescription};   #tech description  *
$toolkeywords=$in{toolkeywords};  #*
$photographer=$in{photographer};  #menu
$cdnumber=$in{cdnumber};	#menu
$imagenumber=$in{imagenumber};	#*
$slidenumber=$in{slidenumber};  #*
$simplesearch=$in{simplesearch}; #*


#get header and footer
$header=`cat stndheader.txt`;
$footer=`cat stndfooter.txt`;

##################################################
#  check for bad chars and then change those to _
##################################################
$vwdescription=~ s/[^a-zA-Z0-9.,;:'" ]/_/go;
$subjectkeywords=~ s/[^a-zA-Z0-9.,;:'" ]/_/go;
$notessubject=~ s/[^a-zA-Z0-9.,;:'" ]/_/go;
$notesdescription=~ s/[^a-zA-Z0-9.,;:'" ]/_/go;
$toolkeywords=~ s/[^a-zA-Z0-9.,;:'" ]/_/go;
$imagenumber=~s/[^0-9]//go;
$slidenumber=~s/[^0-9.]//go;
$scenenumber=~s/[^0-9]//go;
$simplesearch=~s/[^a-zA-z0-9., ]//go;

##################################################
# build SQL query from input strings ANDING them
##################################################
#the sql will look like  "SELECT * FROM traj WHERE <input field> ~ <input> AND <input field> ~ <input> AND ..."
#So, test to see if not null and then build the query
$sql="SELECT * FROM traj WHERE ";   #Now just append to it
$bool="AND";  #operator to use below
$op="~*";

if ($scenenumber){$sql.=" scenenumber = \'$scenenumber\' $bool ";}
if ($drumnumber){$sql.=" drumnumber $op \'$drumnumber\' $bool ";}
if ($spiral){$sql.=" drumnumber $op \'$spiral\' $bool ";}
if ($vwdescription){$sql.=" vwdescription $op \'$vwdescription\' $bool ";}
if ($notessubject){$sql.=" notessubject $op \'$notessubject\' $bool ";}
if ($subjectkeywords){$sql.=" subjectkeywords $op \'$subjectkeywords\' $bool ";}
if ($notesdescription){$sql.=" notesdescription $op \'$notesdescription\' $bool ";}
if ($toolkeywords){$sql.=" toolkeywords $op \'$toolkeywords\' $bool ";}
if ($photographer){$sql.=" photographer = \'$photographer\' $bool ";}
if ($cdnumber){$sql.=" cdnumber = \'$cdnumber\' $bool ";}
if ($imagenumber){$sql.=" imagenumber = \'$imagenumber\' $bool ";}
if ($slidenumber){$sql.=" slidenumber = \'$slidenumber\' $bool ";}

$tesql=$sql;


#strip off the last AND
$sql=~s/AND $//;


#search only the text fields, this overides all
if ($simplesearch){
	$sql="SELECT * from traj WHERE
	 vwdescription  ~*\'$simplesearch\' 
	 OR notessubject  ~*\'$simplesearch\'
	 OR subjectkeywords  ~*\'$simplesearch\' 
	 OR notesdescription  ~*\'$simplesearch\' 
	 OR toolkeywords ~*\'$simplesearch\'";
}

####################################
#CONNECT TO THE DATABASE
####################################
$conn = Pg::connectdb("dbname=trajan");
#execute the query
Pg::doQuery($conn, $sql, \@ary);

#######################################
#postprocess and print the results out
#######################################
#  info returned with be 
#0 key 1slidenumber;2 cdnumber; 3 imagenumber; 4 scenenumber;5 vw; 6 vwdescription;7 subjectkeywords;
#8 notessubject; 9 toolkeywords; 10 notesdescription; 11 refs; 12 relatedslides; 13 furthernotes; 
#14 drumnumber; 15 photographer; 16 sideofcolumn; 17 notesphoto; 18 btnproblem; 19 btnscanned; 20 btnchecked
############################################

&BUILDLINK;

&DEBUG;

&SHOWRESULTS;

sub BUILDLINK {
	$count=1;
	for $i( 0 .. $#ary ) {
		
		#test for odd or even number
		$tc_count=$count;
		$tc=chop($tc_count);
		#if even
			if ($tc=~/[02468]/){ $col=" BGCOLOR=\"\#CCCCCC\"";}
			else{$col="";} 
		$link.="<TR$col><TD><A HREF=\"buildtrajanpage.cgi?$ary[$i][0]\">$count $ary[$i][6]<\/A><\/TD><\/TR>";
		$count++;
	}
	$count--;
}

sub DEBUG {

$debug="<center><table border=0>
<tr><th colspan=2>Summary of Search Strings</th></tr>

<!--simplesearch-->
      <TR width=\"30%\">       
	<TD align=\"right\"> 
	  Simple Search:
	</TD>	
	<TD> 
	  $simplesearch
	</TD>
      </TR>
<!--scene number-->
      <TR  width=\"30%\">       
	<TD BGCOLOR=\"#CCCCCC\" align=\"right\"> 
	  Scene Number:
	</TD>	
	<TD BGCOLOR=\"#CCCCCC\"> 
	  $scenenumber
	</TD>
      </TR>
<!--Drumnumber-->
      <TR width=\"30%\"> 
	<TD align=\"right\">  
	  Drum Number (1-19):
	</TD>
	<TD> 
	  $drumnumber
	</TD>
      </TR>
<!--spiral-->
      <TR width=\"30%\"> 
	<TD BGCOLOR=\"#CCCCCC\" align=\"right\"> 
	  Spiral Number (2-23):
	</TD>
	<TD BGCOLOR=\"#CCCCCC\"> 
	  $spiral
	</TD>
      </TR>
<!--subject-->
      <TR width=\"30%\"> 
	<TD align=\"right\">  
	  Subject:
	</TD>
	<TD> 
	 $vwdescription
	</TD>
      </TR>
<!--subject descrip-->   
       <TR width=\"30%\"> 
	<TD BGCOLOR=\"#CCCCCC\" align=\"right\">  
	  Subject Description:
	</TD>
	<TD BGCOLOR=\"#CCCCCC\"> 
	  $notessubject
	</TD>
      </TR>
<!--subject keyword-->
      <TR width=\"30%\"> 
	<TD align=\"right\"> 
	  Subject Keyword:
	</TD>
	<TD> 
	$subjectkeywords
	</TD>
      </TR>
<!--tech descrip-->
   <TR width=\"30%\">       
	<TD BGCOLOR=\"#CCCCCC\" align=\"right\"> 
	  Technical Description:
	</TD>
	<TD BGCOLOR=\"#CCCCCC\">  	
	$notesdescription
	</TD>
      </TR>
<!--tech keyword-->
      <TR>       
	<TD align=\"right\"> 
	  Technical Keyword:
	</TD>	
	<TD> 
	 $toolkeywords
	</TD>
      </TR>

<!--Photographer--> 
      <TR width=\"30%\"> 
	<TD BGCOLOR=\"#CCCCCC\" align=\"right\">   	
	  Photographer:
	</TD>
	<TD BGCOLOR=\"#CCCCCC\">  	
	$photographer
	</TD>
      </TR>
      
<!--CD--> 
      <TR width=\"30%\"> 
	<TD align=\"right\">  
	  CD Number:
	</TD>
	<TD> 
	 $cdnumber
	</TD>
      </TR>    

<!--imagenumber-->
      <TR width=\"30%\">       
	<TD BGCOLOR=\"#CCCCCC\" align=\"right\">  	 
	  Image Number:
	</TD>	
	<TD BGCOLOR=\"#CCCCCC\">  	
	$imagenumber
	</TD>
      </TR>

<!--slide number-->
      <TR width=\"30%\">       
	<TD align=\"right\"> 
	  PR Slide Number:
	</TD>	
	<TD> 
	  $slidenumber
	</TD>
      </TR>
      
    </TABLE></center>
    <HR>
<P>
";

}
sub SHOWRESULTS {
print <<end_print;
<html>
<head>
<title>Trajan Project - Search Results</title>
<link rel="schema.DC" href="http://purl.org/dc">
<meta name="DC.Title" lang="en" content="Trajan's Column - Search Results for SQL Query: $sql">
<meta name="DC.Creator" content="Umholtz, Gretchen; Rockwell, Geoffrey; George, Michele; Barrette, Paul; Beckmann, Martin; Rich, Saul">
<meta name="DC.Subject" lang="en" content="Trajan, Trajan's Column, Roman Architecture, Roman Carving Technique, Sculpture, Roman Army">
<meta name="DC.Description" lang="en" content="Search engine for the Trajan Project database">
<meta name="DC.Publisher" content="McMaster University">
<meta name="DC.Contributor" content="Rockwell, Peter; Martini, Claudio; Rockwell, Mary">
<meta name="DC.Date" scheme="WTN8601" content="1999-08-20">
<meta name="DC.Type" content="Interactive.Multimedia">
<meta name="DC.Format" content="text/html">
<meta name="DC.Identifier" content="http://cheiron.humanities.mcmaster.ca/~trajan/trajanSearch.cgi">
<meta name="DC.Language" content="English">
<meta name="DC.Rights" content="Image copyright - Peter Rockwell; WWW Site copyright - Umholtz, G., Rockwell, G., George, M., McMaster University">

</head>
<body bgcolor=white LINK="#993300" VLINK="#330000">
$header
<!-- End of Header Row -->
$debug

<center>
<TABLE BORDER=0 WIDTH=500>
<TR><TH>Search Resulted In $count
Match(es).</TH></tr>
$link
</TABLE>
</center>
$footer
</html>
end_print
}


exit;