#!/usr/bin/perl -w

use strict;
use CGI;
use Pg;
use POSIX;

############################################################################
my $db = 'cattleweb';
my $bgcolor = '#ffffff';
my $defbanner = 'http://www.cattleweb.net/images/cattlewebtitle.gif';
my $numperpage = 10;
############################################################################

#my $conn = Pg::new('','','','',$db);
my $conn = Pg::connectdb("dbname=cattleweb");

my $q = new CGI;
my $time = localtime;
print $q->header('text/html');

my $query;

my $pid = 0;
my $banner;
my $nextpage;
my $prevpage;
my $cursor;
my $ntuples;

# get the banner url if it exists
if($q->param('id'))
{
	$pid = $q->param('id');
	$query = "SELECT bannerurl FROM classified_owners WHERE id=$pid";
	my $r = $conn->exec($query);
	$banner = $r->getvalue(0,0);
}

print $q->start_html(-title=>'Classifieds',
							-BGCOLOR=>$bgcolor);

# show the banner if we can
print "<CENTER>";
if (length($banner))
{
	BannerSrc($banner);
} else {
	BannerSrc($defbanner);
}
print "<BR>Current Time: <I>" .localtime(). "</I>\n";
print "</center><P><HR>\n";


my $r = &DoQuery;

# determine if next page exists
if( $r->ntuples() == ($numperpage+1) )
{
	$nextpage = 1;
	$ntuples = ($r->ntuples()) - 1;
} else {
	$nextpage = 0;
	$ntuples = $r->ntuples();
}

# determine if previous page exists
if( $q->param('cursor') >= $numperpage )
{
	$prevpage = 1;
} else {
	$prevpage = 0;
}

my $i;

for($i=0; $i < $ntuples; $i++)
{
	print <<END;
   <P>
   <TABLE>
   <TR>
   	<TD>
   	<IMG SRC="http://gkg-com.com/images/rightarrow.gif" align=center>
		</td>
END
	my $text = $r->getvalue($i,$r->fnumber('text'));
	$text =~ s/\n/<BR>\n/g;
	print "<TD>$text<BR>";

	if( length($r->getvalue($i,$r->fnumber('name'))) )
	{
		print "<B>Contact: </b>". $r->getvalue($i,$r->fnumber('name')). "<BR>";
	}

	if( length($r->getvalue($i,$r->fnumber('city'))) )
	{
		print "<B>City: </b>". $r->getvalue($i,$r->fnumber('city')). "<BR>";
	}

	if( length($r->getvalue($i,$r->fnumber('state'))) )
	{
		print "<B>State: </b>". $r->getvalue($i,$r->fnumber('state')). "<BR>";
	}

	if( length($r->getvalue($i,$r->fnumber('country'))) )
	{
		print "<B>Country: </b>". $r->getvalue($i,$r->fnumber('country')). "<BR>";
	}


	if( length($r->getvalue($i,$r->fnumber('phone'))) )
	{
		print "<B>Phone: </b>". $r->getvalue($i,$r->fnumber('phone')). "<BR>";
	}

	if( length($r->getvalue($i,$r->fnumber('email'))) )
	{
		print "<B>Email: </b> <A HREF=\"mailto:". 
				$r->getvalue($i,$r->fnumber('email')). "\">". 
				$r->getvalue($i,$r->fnumber('email')). "</a><BR>";
	}

	if( length($r->getvalue($i,$r->fnumber('url'))) )
	{
		print "<B>URL: </b> <A HREF=\"". 
				$r->getvalue($i,$r->fnumber('url')). "\">". 
				$r->getvalue($i,$r->fnumber('url')). "</a><BR>";
	}

#	my $secs = $r->getvalue(0,0);
#
#	my $date = strftime("%c %Z", localtime($secs));
#	print "<FONT SIZE=-1><I>Submitted : $date</I></font><BR>\n";
#	print "<FONT SIZE=-1><I>Submitted : $ts</I></font><BR>\n";

	print "<FONT SIZE=-1><I>Submitted : ".
		$r->getvalue($i, $r->fnumber('ts')). "</I></font><BR>\n";


	print "<FONT SIZE=-1><I>Category: ".
			$r->getvalue($i,$r->fnumber('category')). "</font>";
	print "</td></tr>";


	print "</table>";
	print "<HR>";

}

print "<TABLE BORDER=0 WIDTH=100%><TR>";
my $script = $q->script_name();
if($prevpage)
{
	my $str = $q->query_string();
	my $num = $cursor-$numperpage;

	$str =~ s/cursor=\d+/cursor=$num/;
	print "<TD ALIGN=LEFT WIDTH=100><A HREF=\"$script?$str\">Previous Page</a></td>";
}

if($nextpage)
{
	if(!$q->param('cursor'))
	{
		$q->param('cursor','0');
	}

	my $str = $q->query_string();
	my $num = $cursor+$numperpage;


	$str =~ s/cursor=\d+/cursor=$num/;
	print "<TD ALIGN=LEFT WIDTH=100><A HREF=\"$script?$str\">Next Page</a></td>";


}

print <<END;
<TD ALIGN=RIGHT>Classifieds Courtesy of <A HREF="http://www.cattleweb.net">CATTLEweb</a></td>
</table>
END

print $q->end_html();



################################################################

sub DoQuery
{

	my $query;

	# create base query  (one if partnerid is given, another if not)
	if($pid)
	{
		$query = "DECLARE clportal CURSOR FOR ".
					"SELECT date_part('epoch',abstime_datetime(c.ts)),c.* ".
					"FROM   classified c, ad_owners a ".
					"WHERE expire > 'now' AND a.adid=c.id AND a.ownerid=$pid AND ";
	} else {
		$query = "DECLARE clportal CURSOR FOR ".
		         "SELECT date_part('epoch',abstime_datetime(c.ts)),c.* ".
					"FROM   classified c ".
					"WHERE expire > 'now' AND ";
	}

	if($q->param('keywords'))
	{
		my $tmp = $q->param('keywords');
		$tmp =~ s/'/\\'/g;
		my @words = split(" ", $tmp);

		$query .= " (c.text ~* '".join("' OR c.text ~* '", @words)."') AND ";
	}

	if($q->param('category'))
	{
		my $cat = $q->param('category');
		$query .= " c.category='$cat' ";
	}

	$query =~ s/AND $//;
	$query =~ s/WHERE $//;

	# show newest ads first, oldest ads at the end
	$query .= " ORDER BY c.ts DESC ";


	$cursor = $q->param('cursor');
	if( (!$cursor) ) { 
		$cursor = 0; 
	}
	$cursor = int($cursor);

	$conn->exec("BEGIN");
	$conn->exec($query);

	if($cursor > 0)
	{
		$conn->exec("MOVE $cursor IN clportal");
	}

	my $num = $numperpage+1;
	my $res = $conn->exec("FETCH $num IN clportal");
	
	return($res);


}



#-----------------------------------------------------------
# BannerSrc
#
# Show a banner with $src as the src tag
#-----------------------------------------------------------
sub BannerSrc
{
	my ($src) = @_;
	print "<IMG SRC=\"$src\" ALT=\"BANNER\">\n";
}