#! /usr/bin/perl -wT
use strict;	#force all variables to be declared before use
use DBI;	# import database interface methods
use CGI qw(:standard escape escapeHTML);	
use CGI::Carp qw(warningsToBrowser fatalsToBrowser); 
use lib qw(/home/gmacfadd/public_html/cgi-bin/WorkControl); 
use WorkControlDB;
print header (),	# use the text/html (default) Content-type headet
start_html ();
my $dbh = WorkControlDB::connect ();
my @row3;                         #declare an array for listing of task items
push (@row3, qq(<table border=3 bordercolor="blue" align="left"
      bgcolor="rgb(153,255,153)">) );
#@ MAIN_LOGIC DISPATCHING LOGIC
my $choice = lc (param ("choice"));	# return lowercased version of string
if ($choice eq "")			# initial script invocation
       {display_current_items ($dbh);
        print table(@row3);}
elsif ($choice eq "delete")	# else if delete record was selected....
       {if (!delete_item($dbh, param("id")))	# if delete was not successful 
	    { print p ("No record id" . param ("id") . " was found.") }
        else 			# else (record was not found), so 
	    {display_current_items ($dbh);
             print table(@row3);}
       }
else
       {print p ("Logic error, unknown choice: $choice");}
$dbh->disconnect ();
print end_html ();
exit (0);

#@ DISPLAY_CURRENT_ITEMS
sub display_current_items
{
my $dbh = shift;  
my ($sth, $stmt, $count);
print <<EOF;
<B><FONT COLOR=#FF0000>List Of mySQL rows</FONT></B>
EOF
$stmt = qq { SELECT * FROM testblob ORDER BY id ASC   };
$sth = $dbh->prepare ($stmt);	# tell DBI what we are preparing to do
$sth->execute ();		# send the command to MySQL
$count = 0;
while (my $row = $sth->fetchrow_hashref ())	
	{display_item ($row);
		++$count;}
$sth->finish ();				
print p ("No items were found") if $count == 0;
}
#@ END DISPLAY_CURRENT_ITEMS
#@ START DISPLAY_ITEM
sub display_item
{
my $row = shift;    
my $delete_url = sprintf ("%s?choice=delete;id=%d", url (), $row->{id});
push (@row3, Tr (
     qq(<TD ROWSPAN=4 ALIGN="LEFT" VALIGN="CENTER" BGCOLOR="rgb(153,255,153)" COLSPAN="1">), 
     qq(<B>mySQL row #: </B><FONT COLOR="rgb(0,0,255)">$row->{id}</FONT>),
     qq(<TD ALIGN="LEFT" VALIGN="TOP" BGCOLOR="rgb(153,255,153)" COLSPAN="1">), 
     qq(<B>Filename: </B><FONT COLOR="rgb(255,0,0)">$row->{filename}</FONT>),
     qq(<TD ALIGN="LEFT" VALIGN="TOP" BGCOLOR="rgb(153,255,153)" COLSPAN="1">), 
   qq(<B>Mime-Type: </B><FONT COLOR="rgb(0,0,255)">$row->{mime_type}</FONT>)));
push (@row3, Tr (
     qq(<TD ALIGN="CENTER" VALIGN="TOP" BGCOLOR="rgb(153,255,153)" COLSPAN="2">), 
     qq(<FONT COLOR="rgb(255,0,255)">),
     a({-href=>$delete_url}, "Click here to delete this mySQL entry"),
     qq(</FONT>) ) );
push (@row3, Tr (
     qq(<TD ALIGN="CENTER" VALIGN="TOP" BGCOLOR="rgb(153,255,153)" COLSPAN="2">), 
     qq(<FONT COLOR="rgb(255,0,255)">),
######-after here insert logic here to download file in $row->{file}
     "insert link reserved for download here",


#####
     qq(</FONT>) ) );

push (@row3, Tr (
     qq(<TD ALIGN="CENTER" VALIGN="TOP" BGCOLOR="rgb(153,204,204)" COLSPAN="6">),
     qq (<FONT COLOR="rgb(0,0,0)">end of this record</FONT>) ) );
}
#@ END DISPLAY_ITEM
#@ START DELETE_ITEM
sub delete_item
{
my ($dbh, $id) = @_;	# @_ contains the parameters passed to that subroutine
return ($dbh->do(qq{DELETE FROM testblob WHERE id = ?},undef,$id)); 
# return 0 if deletion of this record "id" was successful 
}
#@ END DELETE_ITEM