#!C:\Program Files\Perl\bin\perl.exe -w

package spyTech;

do 'header.pl';

print start_html("Search");
print <<HTML;
</style>
<style type="text/css">
<!--
a:link {
	color: #000000;
}
a:visited {
	color: #000000;
}
-->
</style>
<table width="90%"  border="0" bordercolor="#999999" align="center" cellpadding="0" cellspacing="0">
	<tr>
		<td>
			<form method="post" name="search" action="">
			<table width="100%"  border="0" cellspacing="0" cellpadding="0">
      			<tr>
        			<td bgcolor="#000000" width="70%" class="style5"><h3 align="left">&nbsp;&nbsp;Search</h3></td>
        			<td bgcolor="#000000" class="style5"> Sort By
						<select type="select" name="orderby">
							<option value="id ASC" selected>Product ID : Increasing</option>
							<option value="id DESC" selected>Product ID : Decreasing</option>
					        <option value="title ASC" selected>Product : A to Z</option>
					        <option value="title DESC">Product : Z to A</option>
					        <option value="manufacturer ASC">Manufacturer : A to Z</option>
					        <option value="manufacturer DESC">Manufacturer : Z to A</option>
					        <option value="stock ASC">Stock : Lowest to Highest</option>
					        <option value="stock DESC">Stock : Highest to Lowest</option>
					        <option value="price ASC">Price : Lowest to Highest</option>
					        <option value="price DESC">Price : Highest to Lowest</option>
				        </select>
					</td>
					<td bgcolor="#000000" class="style5">
						<input type="submit" name="submit" value="SEARCH">
					</td>        			
      			</tr>
      		</table>
      		<br>
	      	<table width="100%"  border="0" cellspacing="1" cellpadding="5">
        		<tr bgcolor="#CCCCCC" align="center">
        			<td width="10%">
        				<strong>Product ID</strong><br>
HTML
print textfield(-name=>'id', -size=>10, value=>param('id'));
print <<HTML;
        			</td>
        			<td width="15%">
        				<strong>Name</strong><br>
HTML
print textfield(-name=>'title', -size=>20, value=>param('title'));
print <<HTML;
        			</td>
        			<td width="30%">
        				<strong>Description</strong><br>
HTML
print textfield(-name=>'description', -size=>40, value=>param('description'));
print <<HTML;
        			</td>
        			<td width="15%">
        				<strong>Manufacturer</strong><br>
HTML
print textfield(-name=>'manufacturer', -size=>20, value=>param('manufacturer'));
print <<HTML;
        			</td>
        			<td width="10%">
        				<strong>Stock Quantity</strong><br>
HTML
print textfield(-name=>'stock', -size=>10, value=>param('stock'));
print <<HTML;
        			</td>
        			<td width="10%">
        				<strong>Price</strong><br>
HTML
print textfield(-name=>'price', -size=>10, value=>param('price'));
print <<HTML;
        			</td>
        		</tr>
      		</table>      		
      		<br><hr><br>
      		</form>
HTML

if (param()) {
	my $query = "SELECT * FROM product WHERE true";
	if (param('id') ne "") { $query .= " AND \"id\" = " . param('id'); }
	if (param('title') ne "") { $query .= " AND UPPER(\"title\") LIKE '%" . uc(param('title')) . "%'"; }
	if (param('description') ne "") { $query .= " AND UPPER(\"description\") LIKE '%" . uc(param('description')) . "%'"; }
	if (param('manufacturer') ne "") { $query .= " AND UPPER(\"manufacturer\") LIKE '%" . uc(param('manufacturer')) . "%'"; }
	if (param('stock') ne "") { $query .= " AND \"stock\" = " . param('stock'); }
	if (param('price') ne "") { $query .= " AND \"price\" = " . param('price'); }
	$query .= " ORDER BY " . param('orderby');
	
	$dbh = DBI->connect("DBI:PgPP:dbname=$dbName;host=localhost", "$dbUser", "$dbPass") or die "Unable to connect to localhost because $DBI::errstr";
	$sth = $dbh->prepare($query) or die "Couldn't prepare statement: " . $dbh->errstr;	
	$sth->execute or die "Couldn't execute statement: " . $sth->errstr;
	
  	print "<table width=\"100%\"  border=\"0\" cellspacing=\"1\" cellpadding=\"0\">",;
  	print "<tr><td width=\"10%\"></td><td width=\"15%\"></td><td width=\"30%\"></td><td width=\"15%\"></td><td width=\"10%\"></td><td width=\"10%\"></td></tr>";
  	my $i = 0;
  	while (my @rows = $sth->fetchrow) {
  		if ($i==0) { print "<tr bgcolor=\"#BBBBBB\" align=\"center\">"; } else { print "<tr bgcolor=\"#EEEEEE\" align=\"center\">"; }
  		$i = ($i + 1) % 2;
  		foreach my $field (@rows) {
  			print "<td><a href=productinfo.pl?item=$rows[0]>$field</a></td>";
  		}
  		print "</tr>";
 	}
  	print "</table>";	
}

print <<HTML;
		</td>
	</tr>
</table>
HTML
print end_html();