#! c:\perl\bin\perl.exe

#
# Script to generate BPAS market web page directly from database
# Will work as CGI or as command line script. If command line writes to file 
# defined in variables below, otherwise writes to STDOUT for CGI
# 

use DBI;
use DBI qw(:sql_types);
use strict;
use CGI::Pretty qw( :html3 :standard *table *div *ul);
use Carp;
use bpas;
use Win32;
use Data::Dumper;

my ($dbConnect,$outputFile,$outputSubDir,$debug ) = 
	("DBI:ODBC:BPASMembership", 
	 "Marketd.html",
	 "market",
	 1);
my $dbh; # global DB connection handle

my $marketStatement = qq(SELECT marketno, membershipno, description, FORMAT(price,2) price,itemname
				 	 FROM Market
					 ORDER BY membershipno, marketno);

my ($bCGI, $out);

########################################################
# subroutine to handle special chars:
#   Replace \n with <BR>
########################################################
sub ProcessSpecial($)
{
	local $_ = $_[0];
	$_ =~ s#\n#<BR>#g;
	$_ =~ s#\r##g;
	return $_
}

####################################
# subroutine to write the top, fixed, bit of the web page
####################################
sub WebHeader()
{
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
my $genDate = $mday . "/" . ($mon+1) . "/" . ($year+1900);

DbgPrint "WEB HEADER\n";
PrintWeb (start_html({-title=>"BPAS Market Place", 
					   -style=>{'src'=>'../bpasstyles.css'},
						meta=>{GENERATOR=>'perl CGI',
                               keywords=>'BPAS market place selling',
					   		   description=>'BPAS market place. 
Generated using perl::DBI to extract data from an mySQL database and perl::CGI to generate the web page.
(Craig Nicholas, Dethorpe Ltd, 2009)'}}));

# BPAS header and page description
PrintWeb(div({class=>"header"},'<p><a href="../index.htm"><img src="../images/BPASbanner.jpg" alt="BPAS banner [Click to go to home page]"</a></p>'));
PrintWeb(start_div({class=>"simplePage"}));
PrintWeb(h1("The Market"));
PrintWeb ( <<END
<p><em>Last Updated: $genDate</em></p>
<p>This is a page for members to advertise
any equipment and kit they want to sell.</p>
<h6>(Note: This page will blank out after clicking an
E-Mail link, this is due to anti e-mail harvesting techniques, just use
your browsers back button to return)
</h6>
<form method="post" action="/cgi-bin/Nomailto.pl"><input
 name="dest" value="market" type="hidden">If
you are a member and want to advertise anything here than
e-mail the<input value="market" class="submitLink"
 type="submit">with
the details.</form>
<p>Include a photo
if you want, jpegs or gifs only and keep the size small (800x600 max),
I don&rsquo;t
want to start getting megabytes of pics in my inbox.</p>
<p>If you are interested in any of the items for sale then just
contact the person via the
supplied e-mail address (click on the name), arrangements for payment
and collection are
up to you. (The easiest way would be to arrange to exchange things at
the next show you
are both at).</p>
<p><strong>WARNING.This page is publicly viewable,
non-members are welcome to enquire about purchasing items using the
email links, but both buyer and seller are responsible for ensuring a
safe and reliable transaction method is worked out. The
society takes no responsibilty for incomplete transactions, scamms,
lost postage items etc.</strong></p>
<p><strong>If selling re-enactment weapons, sharp or blunt,
then it is the sellers responsibility to ensure the buyer is legaly
allowed to purchase.</strong></p>
END
);
}

####################################
# subroutine to write the bottom, fixed, bit of the web page
####################################
sub WebFooter()
{
	DbgPrint "WEB FOOTER\n";
	# end of market div
	PrintWeb (end_div());
	# generated by perl footer

	PrintWeb( p(img({src=>"../images/marble_bar.gif",height=>"12"}))); 

	PrintWeb (small(strong("Generated with perl - Craig Nicholas, Dethorpe Ltd, 2009<BR>")));
	PrintWeb a({href=>"http://www.perl.com"},
			 img({src=>"../images/powered_by_perl.gif",alt=>"generated by perl"})
	 	    );

	PrintWeb (end_html());
}

#####################################################
# Subroutine to write out a members market header and the 
# start of the item table
#####################################################

sub writeSellersIntro($)
{
	my ($member) = shift;
	
	my $sellerInfoStatement = qq(SELECT membershipno,message,emailtag, Membership.Name Name
		FROM MarketSellerInfo,Membership
		WHERE  MarketSellerInfo.membershipno = ? AND
			   MarketSellerInfo.membershipno = Membership.No);
	DbgPrint ("writeSellersInfo: $member\n");
	
	# Get the members market details
	my $sellerSth = $dbh->prepare ($sellerInfoStatement);
	$sellerSth->bind_param(1,$member, SQL_INTEGER);
	# execute the market query
	$sellerSth->execute();
	
	my $sellerHashRef = $sellerSth->fetchrow_hashref;
	
	croak "No seller info for member $member" if (!defined $sellerHashRef);
	DbgPrint Dumper($sellerHashRef);
	
	PrintWeb (hr());
	# Print the form containing sellers name and email
	PrintWeb (start_form({-method=>"post",-action=>"/cgi-bin/Nomailto.pl"}));
	PrintWeb (hidden({-name=>"dest",-value=>"$sellerHashRef->{emailtag}"}));
	PrintWeb (h2("Seller: $sellerHashRef->{name}"));
	PrintWeb (h2("Email to:<input value=\"$sellerHashRef->{emailtag}\" class=\"submitLink\" type=\"submit\">"));
	PrintWeb (end_form);

	# Print the sellers message
	PrintWeb (h2("Sellers message"));
	PrintWeb (p("$sellerHashRef->{message}"));
	
	# start the item table
	PrintWeb (start_table({class=>"bpasTable"}));
	PrintWeb (Tr(
	    		th(['Item','Description','Price','Photo (Click for larger image']))
	    	  );
}

#####################################################
# Subroutine to produce the html for an items 
# thumbnail cell
#####################################################

sub Thumbnails($$$)
{
	my ($membershipNo,$marketNo, $itemName) = @_;
	DbgPrint("Writting thumbnails for item: $membershipNo.$marketNo\n");
	my $marketPicturesStatement = qq(SELECT marketno, picture,thumbnail,pictureno
		FROM MarketPictures
		WHERE  marketno = ?
		ORDER BY pictureno);
		
	my $htmlText = start_div({class=>"imgTable"});
	DbgPrint ("Thumbnails: $membershipNo.$marketNo\n");
	
	# Get the picture details
	my $pictureSth = $dbh->prepare ($marketPicturesStatement);
	$pictureSth->bind_param(1,$marketNo, SQL_INTEGER);
	$pictureSth->execute();

	# TBD get the images from the DB and write out as files if not 
	# allready present

	# Print the links to the thumbnails
	$htmlText .= start_ul();
	my $pictureHashRef;
	while (defined ($pictureHashRef = $pictureSth->fetchrow_hashref)){
		DbgPrint ("Got picture: $pictureHashRef->{pictureno}\n");
		my $name = "images//${membershipNo}_${marketNo}_$pictureHashRef->{pictureno}";
		$htmlText .= li(
					  a({href=>"${name}.jpg"},img({src=>"${name}_thumb.jpg",alt=>"$itemName"}))
				     );
	}
	$htmlText .= end_ul();
	$htmlText .= end_div();
	return $htmlText;
}
 
#####################################################
# Subroutine to process a market Row
#####################################################

sub ProcessItem($)
{
	my ($itemHash) = shift;
	DbgPrint("processItem: $itemHash->{membershipno}.$itemHash->{marketno}\n");
	DbgPrint (Dumper($itemHash));
	 
	PrintWeb (Tr(
				td([$itemHash->{itemname},
				    ProcessSpecial($itemHash->{description}),
				    "£$itemHash->{price}",
				    Thumbnails($itemHash->{membershipno},$itemHash->{marketno},$itemHash->{itemname})])
			 ));	
}

########################
# MAIN PROCESSING
########################

SetDebug($debug); #set debug level
# get output dir from environment
my $outputDir = $ENV{'BPAS_WEBSITE_DIR'};
if (!defined ($outputDir))
{
	croak "BPAS_WEBSITE_DIR environment variable not defined";
}

# connect to the database
$dbh = DBI->connect($dbConnect, "", "", {PrintError=>0, RaiseError=>1});
          
# set required params for the DB
$dbh->{LongReadLen} = 1000000;
$dbh->{FetchHashKeyName} = 'NAME_lc';

# work out required output. If running as CGI then use stdout
# otherwise use configured output file
$bCGI = SetOut("$outputDir\\$outputSubDir\\$outputFile");

DbgPrint "Connected\n";

# prepare the main market query 
my $marketSth = $dbh->prepare ( $marketStatement);

#execute the market query
$marketSth->execute() ;

# write out the webpage header
WebHeader();

# iterate over the market item entries
my ($currMember,$marketHashRef);
while (defined ($marketHashRef = $marketSth->fetchrow_hashref)){
	# if its a change of member print the member details
	if (!defined($currMember) || $currMember ne $marketHashRef->{membershipno} ) {
		# we've reached a new members list of items
		DbgPrint ("Starting new member: $marketHashRef->{membershipno}\n");
		if (defined $currMember) {
			# end the previous members item table
			PrintWeb(end_table());
		}
		$currMember = $marketHashRef->{membershipno};
		writeSellersIntro($currMember);
	}
	ProcessItem($marketHashRef);
}

# end the last memebrs table
PrintWeb(end_table());

#write the web page footer
WebFooter();

CloseOut();
 
if (!$bCGI)
{
	Win32::MsgBox("### Web page '$outputDir\\$outputSubDir\\$outputFile' generated ###",MB_ICONINFORMATION,"Market Finished");	
#	print "### Web page '$outputDir/$outputFile' generated\n### Press ENTER to exit";
#	read STDIN, $key, 1
}