#!/usr/bin/perl
#  Copyright 2001-2017 Leslie Richardson

#  This file is part of Open Admin for Schools.

#  Open Admin for Schools is free software; you can redistribute it 
#  and/or modify it under the terms of the GNU General Public License
#  as published by the Free Software Foundation; either version 2 of 
#  the License, or (at your option) any later version.

# Generate Receipts for Payments.

# Note: The payment id (id of record with a trans_type = 'pay'), will
# find all of the payments on existing records, 

my $defaultrecords = 10;
my $receiptsperpage = 2;

# Logo Setup
my $f_ShowLogo = 0; 
my $f_LogoWidth = '25mm';



my %lex = ('Fees' => 'Fees',
	   'Main' => 'Main',
	   'Total' => 'Total',
	   'Payment' => 'Payment',
	   'Date' => 'Date',
	   'Description' => 'Description',
	   'Receipt Number' => 'Receipt Number',
	   'Paid' => 'Paid',
	   'View Log File' => 'View Log File',
	   'Student' => 'Student',
	   'Action' => 'Action',
	   'Search' => 'Search',
	   'No Student(s) Found' => 'No Student(s) Found',
	   'Last,First/Last/Initials/Studnum' => 'Last,First/Last/Initials/Studnum',
	   'Print Receipt' => 'Print Receipt',
	   'Continue' => 'Continue',
	   'Amount' => 'Amount',
	   'Description' => 'Description',
	   'View/Download' => 'View/Download',
	   'Grade' => 'Grade',
	   'Receipt' => 'Receipt',
	   'Phone' => 'Phone',
	   'Fax' => 'Fax',
	   'Payment' => 'Payment',
	   'Signature' => 'Signature',
	   'Payee' => 'Payee',
	   'Name' => 'Name',
	   'Error' => 'Error',
	   'Receipts' => 'Receipts',
	   'View/Print' => 'View/Print',
	   'Start Date' => 'Start Date',
	   'End Date' => 'End Date',
	   'Start' => 'Start',
	   'End' => 'End',
	   'Deposit Report' => 'Deposit Report',
	   'Last' => 'Last',
	   'Not Found' => 'Not Found',
	   'Type' => 'Type',
	   'Paper Size' => 'Paper Size',
	   'Font Size' => 'Font Size',
	   'Letter' => 'Letter',
	   'Legal' => 'Legal',
	   'A4' => 'A4',
	   'A5' => 'A5',
	   'Homeroom' => 'Homeroom',
	   'Download CSV File' => 'Download CSV File',
	   'Outstanding Balance' => 'Outstanding Balance',

	   );

my $self = 'receipt.pl';

use CGI;
use DBI;
use Cwd;
use Number::Format qw(:all);

eval require "../../etc/admin.conf";
if ( $@ ) {
    print qq{$lex{Error} $@<br>\n};
    die qq{$lex{Error} $@\n};
}

eval require "../../etc/fees.conf";
if ( $@ ) {
    print qq{$lex{Error} $@<br>\n};
    die qq{$lex{Error} $@\n};
}


my $q = new CGI;
print $q->header( -charset, $charset ); 
my %arr = $q->Vars;


my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);



# Get values for supression from configuration system
my @fieldnames = qw( r_LogoFile r_SchoolAddressWidth r_ShowAdmins r_AddressSpacer ); 
my $sth = $dbh->prepare("select datavalue from conf_system where dataname = ?");
foreach my $var ( @fieldnames ) {
    $sth->execute( $var );
    my $datavalue = $sth->fetchrow;
    eval $datavalue;
    if ( $@ ) {
	print qq{$lex{Error}: $@<br>\n};
	die qq{$lex{Error}: $@\n};
    }
}




# Show page Header
my $title = "$lex{'View/Print'} $lex{Receipts}";
print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">\n};

if ( not $arr{page} ) {
    $focus = 1;

    print qq{<link rel="stylesheet" type="text/css" media="all" };
    print qq{href="/js/calendar-blue.css" title="blue">\n};
    print qq{<script type="text/javascript" src="/js/calendar.js"></script>\n};
    print qq{<script type="text/javascript" src="/js/lang/calendar-en.js"></script>\n};
    print qq{<script type="text/javascript" src="/js/calendar-setup.js"></script>\n};

} else {
    $focus = 0;
}
print qq{$chartype\n</head><body onload="document.forms[0].elements[$focus].focus()">\n};

print qq{[ <a href="$homepage">$lex{Main}</a> |\n};
print qq{<a href="$feespage">$lex{Fees}</a> ]\n};
print qq{<h1>$title</h1>\n};


if ( not $arr{page} ) {
    showStartPage();

} elsif ( $arr{page} == 1 ) {
    delete $arr{page};
    showDateReceipt( $arr{sdate}, $arr{edate} ); # direct to printReceipts

} elsif ( $arr{page} == 2 ) {
    delete $arr{page};
    showLastReceipt( $arr{records} );

} elsif ( $arr{page} == 3 ) { # called by search form; find students.
    delete $arr{page};
    showStudentReceipt( $arr{student} );


} elsif ( $arr{page} == 5 ) { # Receipt Printing (from select receipts)
    delete $arr{page};

    if ( $arr{type} eq 'PDF' ) {
	delete $arr{type};
	printNewReceipt();

    } elsif ( $arr{type} eq 'CSV' ) {
	delete $arr{type};
	exportCSV();

    } else {
	delete $arr{type};
	printHtmlReceipt();
    }


} elsif ( $arr{page} == 6 ) { # Deposit Report (Bank Reconciliation)
    printDepositReport();
}

print qq{</body></html>\n};




#------------------
sub printNewReceipt { # pdf version using LaTeX
#------===---------

    my $fontsize = $arr{fontsize};
    my $papersize = $arr{papersize};
    delete $arr{fontsize};
    delete $arr{papersize};

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }

    # Load Latex filtering script;
    eval require "../../lib/liblatex.pl";
    if ( $@ ) {
	print $lex{Error}. " $@<br>\n";
	die $lex{Error}. " $@\n";
    }

    my $shortname = "pdfreceipt$$";
    my $filename = "$shortname.tex";


    # Set Paper Size, text width and height
    my ( $textwidth, $textheight, $hoffset, $voffset );
    my @transColWidth = qw(25mm 110mm 25mm); # transaction table column widths.

    $hoffset='-12mm';
    $voffset = '-30mm';

    if ( $papersize eq $lex{Letter} ) {
	$papersize = 'letterpaper';
	$textwidth = $g_letterpaper_textwidth;
	$textheight = $g_letterpaper_textheight;

    } elsif ( $papersize eq $lex{Legal} ) {
	$papersize = 'legalpaper';
	$textwidth = $g_legalpaper_textwidth;
	$textheight = $g_legalpaper_textheight;

    } elsif ( $papersize eq $lex{A4} ) {
	$papersize = 'a4paper';
	$textwidth = $g_a4paper_textwidth;
	$textheight = $g_a4paper_textheight;

    } elsif ( $papersize eq $lex{A5} ) {
	$papersize = 'a5paper';
	$hoffset = '-20mm';
	$voffset = '-30mm';
	@transColWidth = qw(25mm 73mm 25mm);
	# ~~ Remove these conditionals once we have a5 values stored for a while in distro
	if ( not $g_a5paper_textwidth ) {
	    $textwidth = $g_a5paper_textwidth = '148mm';
	} else { $textwidth = '148mm'; }

	if ( not $g_a5paper_textheight ) {
	    $textheight = $g_a5paper_textheight = '210mm';
	} else {
	    $textheight = '210mm';
	}
    }


    # Start the TeX file...
    open(TEX,">$filename") || die "Can't open tex file";


    print TEX "\\documentclass[$fontsize, $papersize, landscape]{article}\n";
    print TEX "\\usepackage{array,newcent,rotating,colortbl, inputenc}\n";
    print TEX "$a_latex_header\n\\renewcommand{\\familydefault}{\\sfdefault}\n";
    print TEX "\\pagestyle{empty}";

    print TEX "\\setlength{\\textwidth}{$textwidth}\n";
    print TEX "\\setlength{\\textheight}{$textheight}\n";
    print TEX "\\setlength{\\hoffset}{$hoffset}\n";
    print TEX "\\setlength{\\voffset}{$voffset}\n";
    print TEX "\\setlength{\\evensidemargin}{0mm}\n";
    print TEX "\\setlength{\\oddsidemargin}{0mm}\n";
    print TEX "\\setlength{\\tabcolsep}{1mm}\n";

    print TEX "\\begin{document}\n";

    my $receiptcount;

    # Loop through all records
    foreach my $paymentid ( sort keys %arr ) {

	# Get payment transaction from paymentid.
	my $sth = $dbh->prepare("select studnum, trans_date, name, description, total, 
         receipt, trans_type from fees_jrl where id = ?");
	$sth->execute( $paymentid );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	my ( $studnum, $trans_date, $name, $description, $total, $receipt, $trans_type ) =
	    $sth->fetchrow;
	($description) = latex_filter( ($description) );

	# Extract Payee information from the description.
	my ($desc, $name) = split(/\(/, $description);
	chop $name; # remove trailing bracket;
	my ($dud, $payee) = split(/:/, $name );


	# Get Records paid by this payment, if any.
	@transactions = ();
	$sth = $dbh->prepare("select id from fees_jrl where paid_id = ? 
          order by trans_date, id");
	$sth->execute( $paymentid );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	while ( my $newid = $sth->fetchrow ) {
	    # if ( $newid == $paymentid ) { next; }  # skip the payment itself.
	    push @transactions, $newid;
	}


	if ( $f_ShowLogo ) {
	    # if ( not $r_LogoFile and $r_logoFile ) { $r_LogoFile = $r_logoFile; }
	    print TEX "\\includegraphics[width=$f_LogoWidth]{";
	    print TEX "../../etc/$r_LogoFile}\\quad \n";
	    print TEX "\\parbox[b]{$r_SchoolAddressWidth}{\\begin{center}{";
	    print TEX "\\Large\\bf $schoolname}\\\\";
	    if ( $schooladdr1 ) {
		print TEX "$schooladdr1\\\\";
	    }
	    print TEX "$schoolcity, $schoolprov $schoolpcode\\\\";
	    
	    # Phones Lines
	    if ( $schoolphone ) {
		print TEX $lex{Phone}. " $schoolphone ";
		if ( $r_ShowAdmins ) { print TEX "\\hfill ";}
		else { print TEX "\\qquad "; }
	    }
	    if ( $schoolfax ) {
		print TEX $lex{Fax}. " $schoolfax";
	    }
	    print TEX "\\\\\n\n";

	    print TEX "\\vspace{$r_AddressSpacer}\n\n";
	    print TEX " \\end{center}}\\qquad\n";

	}

	print TEX "\\bigskip\n\n";

	print TEX "\\begin{tabular}{p{75mm}|p{75mm}|p{75mm}|}\n";
	print TEX "\\hfil Bank Al-Habib Limited \\hfil & \\hfil Bank Al-Habib Limited \\hfil & ";
	print TEX "\\hfil Bank Al-Habib Limited \\hfil \\\\ \n";
	print TEX "\\hfil A/C No 123456 \\hfil & \\hfil A/C No 123456 \\hfil & \\hfil ";
	print TEX "A/C No 123456 \\hfil \\\\ \n";
	print TEX "\\bigskip & \\bigskip & \\bigskip \\\\ \n";
	
	# get Student Info
	#$sth = $dbh->prepare("select lastname, firstname, grade, address1, city1, pcode1 
	$sth = $dbh->prepare("select * from studentall where studnum = ?");
	$sth->execute( $studnum );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }

	my $stud_ref = $sth->fetchrow_hashref;
	my %stud = %$stud_ref;
	#($lastname, $firstname, $grade, $address1, $city1, $pcode1 )
	foreach my $key ( keys %stud ) {
	    ( $stud{$key} ) = latex_filter( $stud{$key} );
	}

	# print Student Info
	# print TEX "\\begin{tabular}{ll}\n";
	
	print TEX "{\\bf $lex{Name}}: $stud{firstname} $stud{lastname}\\hfill C-ID:$studnum & ";
	print TEX "{\\bf $lex{Name}}: $stud{firstname} $stud{lastname}\\hfill  C-ID:$studnum & ";
	print TEX "{\\bf $lex{Name}}: $stud{firstname} $stud{lastname}\\hfill  C-ID:$studnum \\\\ \n";
	    
	print TEX "\\hrule & \\hrule & \\hrule \\\\ \n";


        print TEX "\\end{tabular}\n";

    
    } # payment id loop


    print TEX "\n\\end{document}";
    close TEX;

    system("$pdflatex $filename >pdflog$$.txt");
    system("mv $shortname.pdf $downloaddir");
    system("mv pdflog$$.txt $downloaddir");
    system("rm -f $shortname.*");
    
    print qq{<h1><a href="$webdownloaddir/$shortname.pdf">};
    print qq{$lex{'View/Download'} $lex{'Receipt'}</a></h1>\n};

    print qq{<p>[ <a href="$homepage">$lex{Main}</a> |\n};
    print qq{<a href="$webdownloaddir/pdflog$$.txt">};
    print qq{$lex{'View Log File'}</a>\n ]</p></body></html>\n};

}  # end of printNewReceipt




#------------
sub exportCSV {
#------------

    use Text::CSV_XS;

    # Not Needed here.
    delete $arr{fontsize};
    delete $arr{papersize};

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }

    # Start Export
    my $csv = Text::CSV_XS->new({binary => 1});

    # Open output file
    my $filename = "receipt$$.csv";
    open (EX,">$filename") || die "Can't open CSV file $filename";

    # Setup SQL
    my $sth = $dbh->prepare("select studnum, total, receipt, trans_type from fees_jrl where id = ?");
    my $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");

    # Loop through all receipts.
    foreach my $paymentid ( sort {$a <=> $b} keys %arr ) {

	# Get Payment transaction and get the amount, student number, and receipt #.
	my @rec;
	$sth->execute( $paymentid );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	@rec = $sth->fetchrow;
	my $studnum = $rec[0];

	# Get student name.
	$sth1->execute( $studnum );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	my ($lastname, $firstname) = $sth1->fetchrow;
	push @rec, $lastname;
	push @rec, $firstname;

	# Format the total value
	$rec[1] = format_number( -$rec[1], 2, 2 );

	if ( $csv->combine(@rec) ) {
	    my $record = $csv->string;
	    print EX $record, "\r\n";
	} else {
	    my $err = $csv->error_input;
	    print qq{Combine failed on input: ",$err,":\n\n};
	}

    } # end of payment loop


    # Close and Link to Exported File
    close EX;
    system("mv $filename $downloaddir");

    print qq{<p>[ <a href="$webdownloaddir/$filename">$lex{'Download CSV File'}</a> ]</p>\n};
    print qq{</body></html>\n};

    exit;
    
}



#----------------
sub _showReceipts {
#----------------

    # a helper function.
    # Passed all the ids, print them in a table with form.
    # Return without exiting.

    my @ids = @_; # passed receipt id's.


    # Get the Payment / Receipt transactions.
    my $sth = $dbh->prepare("select * from fees_jrl where id = ?");

    # Get Student Name
    my $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");

    # Start Form
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="5">\n};

    # Start Table
    print qq{<table cellpadding="4" border="1" cellspacing="0">\n};
    # Table Heading
    print qq{<tr><th>$lex{Receipt}</th><th>$lex{Student}</th><th>$lex{Date}</th><th>};
    print qq{$lex{Amount}</th><th>$lex{Description}</th><th>$lex{Action}</th></tr>\n};

    my $count = 1;
    foreach my $id ( @ids ) {

	$sth->execute( $id );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my $ref = $sth->fetchrow_hashref;
	my %rec = %$ref;

	my $total = format_number( abs $rec{total}, 2, 2); # remove the negative value from payment.

	# Get student name
	$sth1->execute( $rec{studnum} );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my ($lastname, $firstname) = $sth1->fetchrow;

	if ( not $rec{description} ) { $rec{description} = $rec{name}; } # req'd for roa transactions.

	print qq{<tr><td class="la">$rec{receipt}</td><td class="la">$firstname $lastname</td>\n};
	print qq{<td class="la">$rec{trans_date}</td><td class="la">$total</td>};
	print qq{<td class="la">$rec{description}</td><td>};
	print qq{<input type="checkbox" name="$id" value="1"></td></tr>\n};

    }

    print qq{<tr><td colspan="6" class="cn">\n};
    print qq{<input type="submit" value="$lex{'Print Receipt'}">};
    print qq{</td></tr>\n};

    print qq{<tr><td colspan="6" class="la">\n};
    print qq{$lex{Type} <select name="type"><option>PDF</option>};
    print qq{<option>HTML</option><option>CSV</option></select>\n};

    print qq{&nbsp;&nbsp;$lex{'Paper Size'} };
    $defaultpapersize =~ s/paper//; # strip out word paper so lex works; from admin.conf
    my $defpaper = ucfirst( $defaultpapersize );
    print qq{<select name="papersize"><option>$lex{$defpaper}</option>\n};
    my @sizes = qw(Letter A4 A5 Legal);
    foreach my $size ( @sizes ) {
	if ( $size eq $defpaper ) { next; }
	print qq{<option value="$size">$lex{$size}</option>};
    }
    print qq{</select>};

    # Font Size
    print qq{&nbsp;&nbsp;$lex{'Font Size'} };
    print qq{<select name="fontsize">\n};
    if ( $g_fontsize ) { print qq{<option>$g_fontsize</option>\n};}
    print qq{<option>10pt</option><option>11pt</option><option>12pt</option></select>\n };


    print qq{</td></tr>\n};

    print qq{</form></table>\n};
    
    return;

}



#----------------
sub showStartPage {
#----------------


    # Get Current Date
    my @tim = localtime(time);
    my $year = @tim[5] + 1900;
    my $month = @tim[4] + 1;
    my $day = @tim[3];
    my $currdate = "$year-$month-$day";


    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};

    print qq{<table cellpadding="4" border="1" cellspacing="0">\n};
    print qq{<tr><td colspan="2" class="bcn">$lex{Date} $lex{Search}</td></tr>\n};

    print qq{<tr><td class="bra">$lex{'Start Date'}</td><td class="la">};
    print qq{<input type="text" name="sdate" id="sdate" size="12">\n};
    print qq{<button type="reset" id="start_trigger">...</button></td></tr>\n};

    print qq{<tr><td class="bra">$lex{'End Date'}</td><td class="la">};
    print qq{<input type="text" name="edate" id="edate" size="12" value="$currdate">\n};
    print qq{<button type="reset" id="end_trigger">...</button></td></tr>\n};

    print qq{<tr><td colspan="2" class="cn">};
    print qq{<input type="submit" value="$lex{Continue}">\n};
    print qq{</td></tr>\n};

    print qq{</table></form>\n};

    print qq{<p></p>\n};

    # Print Last 'X' receipts.
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="2">\n};

    print qq{<table cellpadding="4" border="1" cellspacing="0">\n};

    print qq{<tr><td class="bla">$lex{'View/Print'} $lex{Last} };
    print qq{<input name="records" size="6" value="$defaultrecords"> };
    print qq{$lex{Receipts}</td></tr>\n};

    print qq{<tr><td class="cn">};
    print qq{<input type="submit" value="$lex{Continue}"></td></tr>\n};

    print qq{</table></form>\n};
    print qq{<p></p>\n};


    # Bank Deposit Report Link
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="6">\n};

    print qq{<table cellpadding="4" border="1" cellspacing="0">\n};

    print qq{<tr><td colspan="2" class="bcn">$lex{'View/Print'} $lex{Receipts}};
    print qq{ - $lex{'Receipt Number'}</td></tr>\n};

    print qq{<tr><td class="bra">$lex{Start}#</td><td class="la">};
    print qq{<input type="text" name="startreceipt" size="6"></td></tr>\n};

    print qq{<tr><td class="bra">$lex{End}#</td><td class="la">};
    print qq{<input type="text" name="endreceipt" size="6"></td></tr>\n};

    print qq{<tr><td colspan="2" class="bcn">};
    print qq{<input type="submit" name="submit" value="$lex{'Deposit Report'}">};
    print qq{</td></tr></table></form>\n};



    print qq{<script type="text/javascript">
     Calendar.setup({
        inputField     :    "sdate", // id of the input field
        ifFormat       :    "%Y-%m-%d", // format of the input field
        button         :    "start_trigger", // trigger for the calendar (button ID)
        singleClick    :    false,        // double-click mode
        step           :    1             // show all years in drop-down boxes 
    })

     Calendar.setup({
        inputField     :    "edate",
        ifFormat       :    "%Y-%m-%d",
        button         :    "end_trigger",
        singleClick    :    false,
        step           :    1
    })};
    print qq{</script>\n};

    showSearchForm();
    
    print qq{</body></html>\n};
    exit;

}



#------------------
sub showLastReceipt {
#------------------

    my ( $records ) = @_;

    print qq{<table cellpadding="4" cellspacing="0" border="1">\n};

    # Get payments
    my $sth = $dbh->prepare("select id from fees_jrl 
			    WHERE trans_type != 'chg' ORDER BY receipt desc");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

    my @recs = ();
    my $counter = 1;
    while ( my $id = $sth->fetchrow ) {
	push @recs, $id;
	if ( $counter >= $records ) { last; }
	$counter++;
    }

    _showReceipts( @recs );

    print qq{<p></p>\n};
    showSearchForm();
    print qq{</body></html>\n};
    exit;
    
}



#------------------
sub showDateReceipt {
#------------------

    my ( $startdate, $enddate ) = @_;

    print qq{<p>$lex{'Start Date'}: $startdate  $lex{'End Date'}: $enddate</p>\n};
    print qq{<table cellpadding="4" cellspacing="0" border="1">\n};

    # Get payments
    my $sth = $dbh->prepare("select id from fees_jrl 
      WHERE trans_type != 'chg' AND
        to_days(trans_date) >= to_days('$startdate') and 
        to_days(trans_date) <= to_days('$enddate')
      ORDER BY receipt");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

    my @recs = ();
    while ( my $id = $sth->fetchrow ) {
	push @recs, $id;
    }

    _showReceipts( @recs );

    print qq{<p></p>\n};
    showSearchForm();
    print qq{</body></html>\n};
    exit;

}



#---------------------
sub showStudentReceipt {
#---------------------

    # foreach my $key ( sort keys %arr ) { print "K:$key VAL:$arr{$key}<br>\n"; }
    
    my $student = shift;

    # Setup the Search
    if ($student =~ /\d+/) {  # we have a student number
	$sth = $dbh->prepare("select lastname, firstname, studnum from studentall 
          where studnum = ?");
	$sth->execute( $student );

    } else { # we have words possibly with a comma
	($lastname,$firstname)  = split /\,/, $student;
	$firstname =~ s/^\s*//;
	$lastname =~ s/^\s*//;
	if ($lastname and $firstname){ # both entered.
	    $sth = $dbh->prepare("select  lastname, firstname, studnum from studentall 
             where lastname = ? and firstname = ?");
	    $sth->execute( $lastname, $firstname );

	} elsif ($lastname and not $firstname){ # only lastname (no comma)
	    # add these 4 lines in case name is Ng, etc. only NG or ng will search for initials.
	    my $uc = uc( $lastname );
	    my $lc = lc( $lastname );

	    if ( (length($lastname) == 2 and $uc eq $lastname ) or 
		 (length($lastname) == 2 and $lc eq $lastname )  ){ # search by initials: fi, li.

		my $fi = substr($lastname,0,1). '%'; 
		my $li = substr($lastname,1,1). '%';
		$sth = $dbh->prepare("select lastname, firstname, studnum from studentall 
				     where lastname $sql{like} ? and firstname $sql{like} ?");
		$sth->execute( $li, $fi );

	    } else {
		$sth = $dbh->prepare("select lastname, firstname, studnum from studentall 
                  where lastname = ? order by firstname");
		$sth->execute( $lastname );
	    }
	} else { # print an error....
	    showSearchForm();
	    print qq{</body></html>\n};
	    exit;
	}

    } # Last Else
    # We should now have a $sth defined.
    
    

    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    my $rows = $sth->rows;
    # print qq{Rows: $rows<br>\n};

    if ($rows < 1) { 
	print qq{<h1>$lex{'No Student(s) Found'}</h1>\n};
	showSearchForm();
	print qq{</body></html>\n}; 
	exit;
    }


    # Prepare to find payments
    my $sth1 = $dbh->prepare("select id from fees_jrl 
     WHERE trans_type != 'chg' AND studnum = ?
     order by receipt");

    # Loop Through all Students
    while ( my ($lastname, $firstname, $studnum) = $sth->fetchrow ) {


	# Find Payments
	$sth1->execute($studnum);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

	my @recs = ();
	while ( my $id = $sth1->fetchrow ) {
	    push @recs, $id;
	}

	print qq{<h3>$firstname $lastname ($studnum)</h3>\n};
	if ( not @recs ) {
	    print qq{<p>$lex{Receipts} $lex{'Not Found'}</p>\n};
	} else {
	    _showReceipts( @recs );
	    print qq{<p></p>\n};
	}

    } # end of Student Loop

    
    if ( $rows > 20 ) { # only show if lots of records
	showSearchForm();
    }

    print qq{</body></html>\n};
    
    exit;

} # end of selectStudent



#---------------
sub printReceipt { # pdf version using LaTeX
#---------------

    my $fontsize = $arr{fontsize};
    my $papersize = $arr{papersize};
    delete $arr{fontsize};
    delete $arr{papersize};

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }

    # Load Latex filtering script;
    eval require "../../lib/liblatex.pl";
    if ( $@ ) {
	print $lex{Error}. " $@<br>\n";
	die $lex{Error}. " $@\n";
    }

    my $shortname = "pdfreceipt$$";
    my $filename = "$shortname.tex";


    # Set Paper Size, text width and height
    my ( $textwidth, $textheight, $hoffset, $voffset );
    my @transColWidth = qw(25mm 110mm 25mm); # transaction table column widths.

    $hoffset='-12mm';
    $voffset = '-30mm';

    if ( $papersize eq $lex{Letter} ) {
	$papersize = 'letterpaper';
	$textwidth = $g_letterpaper_textwidth;
	$textheight = $g_letterpaper_textheight;

    } elsif ( $papersize eq $lex{Legal} ) {
	$papersize = 'legalpaper';
	$textwidth = $g_legalpaper_textwidth;
	$textheight = $g_legalpaper_textheight;

    } elsif ( $papersize eq $lex{A4} ) {
	$papersize = 'a4paper';
	$textwidth = $g_a4paper_textwidth;
	$textheight = $g_a4paper_textheight;

    } elsif ( $papersize eq $lex{A5} ) {
	$papersize = 'a5paper';
	$hoffset = '-20mm';
	$voffset = '-30mm';
	@transColWidth = qw(25mm 73mm 25mm);
	# ~~ Remove these conditionals once we have a5 values stored for a while in distro
	if ( not $g_a5paper_textwidth ) {
	    $textwidth = $g_a5paper_textwidth = '148mm';
	} else { $textwidth = '148mm'; }

	if ( not $g_a5paper_textheight ) {
	    $textheight = $g_a5paper_textheight = '210mm';
	} else {
	    $textheight = '210mm';
	}
    }


    # Start the TeX file...
    open(TEX,">$filename") || die "Can't open tex file";


    print TEX "\\documentclass[$fontsize, $papersize]{article}\n";
    print TEX "\\usepackage{array,newcent,rotating,colortbl, inputenc}\n";
    print TEX "$a_latex_header\n\\renewcommand{\\familydefault}{\\sfdefault}\n";
    print TEX "\\pagestyle{empty}";

    print TEX "\\setlength{\\textwidth}{$textwidth}\n";
    print TEX "\\setlength{\\textheight}{$textheight}\n";
    print TEX "\\setlength{\\hoffset}{$hoffset}\n";
    print TEX "\\setlength{\\voffset}{$voffset}\n";
    print TEX "\\setlength{\\evensidemargin}{0mm}\n";
    print TEX "\\setlength{\\oddsidemargin}{0mm}\n";
    print TEX "\\setlength{\\tabcolsep}{1mm}\n";

    print TEX "\\begin{document}\n";

    my $receiptcount;

    # Loop through all records
    foreach my $paymentid ( sort keys %arr ) {

	# Get payment transaction from paymentid.
	my $sth = $dbh->prepare("select studnum, trans_date, name, description, total, 
         receipt, trans_type from fees_jrl where id = ?");
	$sth->execute( $paymentid );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	my ( $studnum, $trans_date, $name, $description, $total, $receipt, $trans_type )
	    = $sth->fetchrow;
	($description) = latex_filter( ($description) );

	# Extract Payee information from the description.
	my ($desc, $name) = split(/\(/, $description);
	chop $name; # remove trailing bracket;
	my ($dud, $payee) = split(/:/, $name );


	# Get Records paid by this payment, if any.
	@transactions = ();
	$sth = $dbh->prepare("select id from fees_jrl where paid_id = ? 
          order by trans_date, id");
	$sth->execute( $paymentid );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	while ( my $newid = $sth->fetchrow ) {
	    # if ( $newid == $paymentid ) { next; }  # skip the payment itself.
	    push @transactions, $newid;
	}


	if ( $f_ShowLogo ) {
	    # if ( not $r_LogoFile and $r_logoFile ) { $r_LogoFile = $r_logoFile; }
	    print TEX "\\includegraphics[width=$f_LogoWidth]{";
	    print TEX "../../etc/$r_LogoFile}\\quad \n";
	    print TEX "\\parbox[b]{$r_SchoolAddressWidth}{\\begin{center}{";
	    print TEX "\\Large\\bf $schoolname}\\\\";
	    if ( $schooladdr1 ) {
		print TEX "$schooladdr1\\\\";
	    }
	    print TEX "$schoolcity, $schoolprov $schoolpcode\\\\";
	    
	    # Phones Lines
	    if ( $schoolphone ) {
		print TEX $lex{Phone}. " $schoolphone ";
		if ( $r_ShowAdmins ) { print TEX "\\hfill ";}
		else { print TEX "\\qquad "; }
	    }
	    if ( $schoolfax ) {
		print TEX $lex{Fax}. " $schoolfax";
	    }
	    print TEX "\\\\\n\n";

	    print TEX "\\vspace{$r_AddressSpacer}\n\n";
	    print TEX " \\end{center}}\\qquad\n";

	}

=head
    # print School Info
    print TEX "\\begin{center}{\\large $schoolname}\n\n";
	print TEX "$schooladdress1 $schooladdress2 \n\n";
	print TEX "$schoolcity $schoolprov $schoolpcode\n\n";
	print TEX $lex{Phone}. " $schoolphone\n\n";
	if ( $schoolfax ) {
	    print TEX $lex{Fax}. " $schoolfax\n\n";
	}
	print TEX "\\end{center}\n";
=cut

	print TEX "\\bigskip\n\n";

	print TEX "{\\Large $lex{Payment} $lex{Receipt} \\# $receipt}\n\\bigskip\n\n";

	# get Student Info
	#$sth = $dbh->prepare("select lastname, firstname, grade, address1, city1, pcode1 
	$sth = $dbh->prepare("select * from studentall where studnum = ?");
	$sth->execute( $studnum );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }

	my $stud_ref = $sth->fetchrow_hashref;
	my %stud = %$stud_ref;

	#($lastname, $firstname, $grade, $address1, $city1, $pcode1 )
	foreach my $key ( keys %stud ) {
	    ( $stud{$key} ) = latex_filter( $stud{$key} );
	}

	# print Student Info
	print TEX "\\begin{tabular}{ll}\n";
	print TEX "{\\bf $lex{Student}}: & $stud{firstname} $stud{lastname} ($studnum)\\\\ \n";
	print TEX " & $lex{Grade} $stud{grade} \\\\\n";
	if ( $stud{homeroom} ) {
	    print TEX " & $lex{Homeroom} $stud{homeroom} \\\\\n";
	}
	if ( $stud{address1} ) {
	    print TEX " & $stud{address1} \\\\\n";
	}
	if ( $stud{city1} or $stud{pcode1} ) {
	    print TEX " & $stud{city1} $stud{pcode1} \\\\\n";
	}

	print TEX "\\end{tabular}\n\\smallskip\n\n";

	# Payment Date
	print TEX "{\\bf $lex{Payment} $lex{Date}}: $trans_date \n\\smallskip\n\n";

	# Payment Description
	print TEX "\\parbox{160mm}{{\\bf $lex{Description}}: $desc} \n\\smallskip\n\n";

	# Amount of Payment
	$total =~ s/^-//; # strip negative sign
	$total = format_number( $total, 2,2 );
	print TEX "{\\bf ". $lex{Amount}. "}: $total \n\\smallskip\n\n";

	# Outstanding Balance
	my $sth1 = $dbh->prepare("select sum(total) from fees_jrl where studnum = ?");
	$sth1->execute( $studnum );
	my $balance = $sth1->fetchrow;
	$balance = format_number( $balance, 2,2 );
	print TEX "{\\bf $lex{'Outstanding Balance'} }: $balance\n\\smallskip\n\n";

	# Payee
	print TEX "{\\bf ". $lex{Payee}. "}: $payee\n\\smallskip\n\n";
    

	if ( @transactions ) {

	    # Start Table
	    print TEX "{\\bf ". $lex{Fees}. q{ }. $lex{Paid}. "}\n\\medskip\n\n";
	    print TEX "\\setlength{\\extrarowheight}{4pt}\n";
	    # @transColWidth set in the pagesize settings area above.
	    print TEX "\\begin{tabular}{|p{$transColWidth[0]}|p{$transColWidth[1]}|";
	    print TEX "p{$transColWidth[2]}|}\\hline\n";
	    print TEX "\\rowcolor[gray]{0.92}". $lex{Date}. " & ". $lex{Description}. " & ";
	    print TEX " $lex{Amount}\\\\ \\hline\n";


	    $sth = $dbh->prepare("select trans_date, trans_type, name, description, total 
				 from fees_jrl where id = ?");
	    my $studenttotal;

	    foreach my $id ( @transactions ) {
		$sth->execute( $id );
		my ( $trans_date, $trans_type, $name, $description, $total ) = $sth->fetchrow;
		($name,$description) = latex_filter( ($name,$description) );
		
		print TEX "$trans_date & {\\it $name} $description & \\hfil $total \\\\ \\hline\n";
	
		$studenttotal += $total;

	    }

#~~ remove the total at the bottom.
#	    $studenttotal = format_number( $studenttotal, 2,2 );
#	    print TEX " & \\hfill $lex{Total}& {\\bf $studenttotal} \\\\ \\hline\n";
	    print TEX "\\end{tabular}\n\n";

	}


	# Signature Lines
	print TEX "\\vspace{7mm}\n";
	print TEX "\\underline{\\hspace{3in}}\n\n";
	print TEX "{\\small ". $lex{Signature}. "}\n\n";
	print TEX "\\vspace{5mm}\n\n";

	$receiptcount++;
	if ( $receiptcount % $receiptsperpage == 0 ) {
	    print TEX "\\newpage\n";
	} else {
	    print TEX "\\dotfill\n\n";
	    print TEX "\\vspace{5mm}\n\n";
	}

    } # payment id loop


    print TEX "\n\\end{document}";
    close TEX;

    system("$pdflatex $filename >pdflog$$.txt");
    system("mv $shortname.pdf $downloaddir");
    system("mv pdflog$$.txt $downloaddir");
    system("rm -f $shortname.*");
    
    print qq{<h1><a href="$webdownloaddir/$shortname.pdf">};
    print qq{$lex{'View/Download'} $lex{'Receipt'}</a></h1>\n};

    print qq{<p>[ <a href="$homepage">$lex{Main}</a> |\n};
    print qq{<a href="$webdownloaddir/pdflog$$.txt">};
    print qq{$lex{'View Log File'}</a>\n ]</p></body></html>\n};

}



#-------------------
sub printHtmlReceipt {
#-------------------

    # Remove, not required
    delete $arr{fontsize};
    delete $arr{papersize};

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    
    foreach my $paymentid ( sort keys %arr ) {

	# Get Payment transaction and get the amount, student number, and receipt #.
	my $sth = $dbh->prepare("select studnum, total, receipt, trans_type from fees_jrl 
				where id = ?");
	$sth->execute( $paymentid );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	my ($studnum, $total, $receipt, $trans_type ) = $sth->fetchrow;
	# Note: receipt= receipt num;pay xactions.

#       print qq{SN:$studnum Tot:$total Rec:$receipt Type:$trans_type<br>\n};

	# Get student name.
	my $sth = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");
	$sth->execute( $studnum );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	my ($lastname, $firstname) = $sth->fetchrow;

#	print qq{Name: $firstname $lastname<br>\n};

	# print name, payment, and receipt number
	my $payment = format_number( -$total, 2, 2 );
	print qq{<h3>$firstname $lastname ($studnum)&nbsp;&nbsp;<b>$lex{Payment}</b> };
	print qq{$payment</h3>\n};
	print qq{<h3><b>$lex{'Receipt Number'}</b>&nbsp;&nbsp;$receipt</h3>\n};

	if ( $trans_type eq 'pay' ) { # show the transactions associated with this

	    # Setup table headings.
	    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
	    print qq{<tr><th>$lex{Date}</th><th>$lex{Name}/$lex{Description}};
	    print qq{</th><th>$lex{Amount}</th></tr>\n};

	    # Get chg and roa transactions for this payment.
	    my $sth = $dbh->prepare("select id, trans_date, name, description, total from fees_jrl 
             where paid_id = ?  order by trans_date, id");
	    $sth->execute( $paymentid );
	    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }

	    # print transactions and add up total.
	    my $grandtotal;
	    while ( my ( $id, $trans_date, $name, $description, $total ) = $sth->fetchrow ) {
		# if ( $id == $paymentid ) { next; } # no skip, since showing balance.
		$grandtotal += $total;
		print qq{<tr><td>$trans_date</td><td>$name / $description</td><td>$total</td></tr>\n};
	    }

#~~ Les
#	    $grandtotal = format_number( $grandtotal, 2,2);
#	    print qq{<tr style="background-color:#DDD">\n};
#	    print qq{<td colspan="2" align="right"><b>$lex{Total}};
#	    print qq{</b></td><td>$grandtotal</td></tr>\n};

	    print qq{</table>};
	}

    } # end of payment loop

    print qq{</body></html>\n};
    exit;

} # end of printHtmlReceipt




#-----------------
sub showSearchForm {
#-----------------

    print qq{<div style="padding:2em 0.2em;">\n};

    print qq{<table cellspacing="0" cellpadding="12" border="1">\n};
    print qq{<tr><td><b>$lex{Student} $lex{Search}</b><br>\n};

    print qq{<form action="$self" method="post">\n};
    print qq{<input type="text" name="student" size="30">\n};
    print qq{<input type="submit" value="$lex{Search}"><br>\n};
    print qq{<input type="hidden" name="page" value="3">\n};
    print qq{$lex{Student} ($lex{'Last,First/Last/Initials/Studnum'})\n};
    
    print qq{</form></tr></td></table></div>\n};

}


#---------------------
sub printDepositReport {
#---------------------

    print qq{<h3>$lex{'Deposit Report'}</h3>\n};

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    my $sth = $dbh->prepare("select studnum, trans_date, name, description, total from fees_jrl 
      WHERE receipt = ? and ( trans_type = 'pay' or trans_type = 'roa')");
    my $grandtotal;

    my $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");

    print qq{<table cellpadding="3" border="1" cellspacing="0">\n};    

    foreach my $receiptnum ( $arr{startreceipt} .. $arr{endreceipt} ) {

	# Get Receipt Info
	$sth->execute( $receiptnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ( $studnum, $transdate, $name, $description, $total ) = $sth->fetchrow;

	# Get Name
	$sth1->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ( $lastname, $firstname ) = $sth1->fetchrow;


	print qq{<tr><td class="la"><b>$receiptnum:</b> $firstname $lastname</td>};
	print qq{<td class="la">$transdate</td>};
	print qq{<td class="la">$name</td><td class="la">$description</td>};
	print qq{<td class="ra">$total</td></tr>\n};
	$grandtotal += $total;

    }
    
    $grandtotal = format_number($grandtotal, 2, 2);

    print qq{<tr><td colspan="4" class="bra"><b>$lex{Total}</b></td>};
    print qq{<td>$grandtotal</td></tr>\n};
    print qq{</table>\n};

    print qq{</body></html>\n};

    exit;

}
