#!/usr/bin/perl
#  Copyright 2001-2024 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.

my %lex = ('Main' => 'Main',
	   'Date' => 'Date',
	   'Description' => 'Description',
	   'Amount' => 'Amount',
	   'Grand Total' => 'Grand Total',
	   'Family Total' => 'Family Total',
	   'Total' => 'Total',
	   'Fees' => 'Fees',
	   'Phone' => 'Phone',
	   'Error' => 'Error',

	   'Payment' => 'Payment',
	   'Report' => 'Report',
	   'Start Date' => 'Start Date',
	   'End Date' => 'End Date',
	   'Continue' => 'Continue',
	   'Receipt' => 'Receipt',
	   
	   );


my $self = 'rptpayment.pl';

# Show up on hover for transactions.
my @extrafields = qw(name1 hphone1 cell1);
my $extrafieldstring = join(',', @extrafields);

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

#$INT_CURR_SYMBOL = '$';


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

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


my ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, 
    $iddst) = localtime(time);
$year = $year + 1900;
$mon++;
$wday++;
my $currdate = "$dow[$wday], $month[$mon] $mday, $year";
my $currsdate = "$year-$mon-$mday";

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



# Load Meta names for extrafields (@extrafields);
my %extranames;
my $sth = $dbh->prepare("select fieldname from meta where fieldid = ?");
foreach my $fld ( @extrafields ) {
    $sth->execute( $fld );
    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
    my $fieldname = $sth->fetchrow;
    $extranames{$fld} = $fieldname;
}


my $title = "$lex{Payment} $lex{Report}";

print qq{$doctype\n<html><head><title>$title</title>
<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};
print qq{</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><h3>$currdate</h3>\n};


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

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

# Print Start/End Dates
print qq{<h3>$lex{'Start Date'} $arr{sdate}<br>\n};
print qq{$lex{'End Date'} $arr{edate}</h3>\n};


# First, find distinct student numbers in payments
my @students;
my $sth = $dbh->prepare("select distinct studnum from fees_jrl 
  where ( trans_type = 'pay' or trans_type = 'roa') and 
  to_days(trans_date) >= to_days('$arr{sdate}') and 
  to_days(trans_date) <= to_days('$arr{edate}')");

$sth->execute;
if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
while ( my $studnum = $sth->fetchrow ) {
    push @students, $studnum;
}


if ( not @students ){ # No outstanding transactions found
    print qq{<h1>No Payments</h1>\n};
    print qq{[ <a href="$homepage">$lex{Main}</a> ]\n}; 
    print qq{</body></html>\n};

    exit;
}


my %homephone;
my @nophone;

my $sth = $dbh->prepare("select hphone1 from studentall where studnum = ?");

foreach my $studnum ( @students ) {

    # Get homephone number.
    $sth->execute( $studnum );
    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
    my $phone = $sth->fetchrow;

    if ( $phone ) { # if we have a phone number, put them into hash
	push @{ $homephone{$phone} }, $studnum;
    } else { 
	push @nophone, $studnum;
    }
}


my %sortedphone;
my $sth = $dbh->prepare("select lastname from studentall where hphone1 = ? order by lastname");

foreach my $phone ( keys %homephone ) {

    my ($surname, $finalname);
    $sth->execute( $phone );
    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }

    while ( my $lastname = $sth->fetchrow ) {
	if ( not $surname ) { 
	    $surname = $finalname = $lastname;
	} elsif ( $lastname ne $surname ) {
	    $finalname .= "/$lastname";
	}
    }
    $sortedphone{"$finalname:$phone"} = $phone;
}



# Print Table Header
print qq{<table border="1" cellpadding ="3" cellspacing="0">\n};
print qq{<tr><th>$lex{Date}</th><th>$lex{Description}</th><th>};
print qq{$lex{Amount}</th></tr>\n};


my $grandtotal;

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

# Now loop through all phone number (ie. should be families)
foreach my $key ( sort keys %sortedphone ) {

    my ($surname,$phone) = split(/:/, $key);
    my $familytotal;

    foreach my $studnum ( @{ $homephone{$phone} } ) {

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

	# Get Extra Fields
	$sth2->execute( $studnum );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	my $fieldref = $sth2->fetchrow_hashref;
	my %extravalues = %$fieldref;


	# Find payment transactions
	$sth = $dbh->prepare("select trans_date, name, description, total, receipt from fees_jrl
          where ( trans_type = 'pay' or trans_type = 'roa') and
           to_days(trans_date) >= to_days('$arr{sdate}') and 
           to_days(trans_date) <= to_days('$arr{edate}') and 
           studnum = ? order by trans_date desc");
	$sth->execute( $studnum );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }

	# Loop through all payments
	my $studenttotal;
	while ( my ( $trans_date, $name, $description, $total, $receipt ) = $sth->fetchrow ) {
	    $studenttotal += $total;
	    $familytotal += $total;
	    $total = format_number( $total, 2,2);
	    print qq{<tr><td>$trans_date</td><td align="right" }; 
	    print qq{title="$lex{Receipt}:$receipt };
	    foreach my $fld ( @extrafields ) {
		print qq{$extranames{$fld}:$extravalues{$fld} };
	    }
	    print qq{">\n};

	    if ($name ) { print $name; }
	    if ( $name and $description ) { print ' - '; }
	    if ( $description ) { print $description; } 
	    print qq{ ($studnum)</td><td>$total</td></tr>\n}; 
	}

	$grandtotal += $studenttotal; # do this before formatting

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



    } # end of Student

    # print family totals
    $familytotal = format_number( $familytotal, 2,2);
    print qq{<tr style="background-color:#BBB;">};
    print qq{<td colspan="2" align="right" class="fam"><b>};
    print qq{$surname ($phone) $lex{'Family Total'}};
    print qq{</b></td><td  class="fam">$familytotal</td></tr>\n};

    print qq{<tr><td colspan="3">&nbsp;</td></tr>\n};


} # End of homephone loop;


# Now the nophone loop;
if ( @nophone ) {

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

    # Print Table Header
    print qq{<table border="1" cellpadding ="3" cellspacing="0">\n};
    print qq{<caption style="font-size:140%;font-weight:bold;">Student Without Home Phone</caption>\n};
    print qq{<tr><th>$lex{Date}</th><th>$lex{Description}</th><th>};
    print qq{$lex{Amount}</th></tr>\n};

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

	# Find payments
	$sth = $dbh->prepare("select trans_date, name, description, total from fees_jrl
          where ( trans_type = 'pay' or trans_type = 'roa' ) and
            to_days(trans_date) >= to_days('$arr{sdate}') and 
            to_days(trans_date) <= to_days('$arr{edate}') and
            studnum = ? order by trans_date desc");
	$sth->execute( $studnum );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }

	# Loop through all payments
	my $studenttotal;
	while ( my ( $trans_date, $name, $description, $total) = $sth->fetchrow ) {
	    $studenttotal += $total;
	    $familytotal += $total;
	    $total = format_number( $total, 2,2);
	    print qq{<tr><td>$trans_date</td><td align="right">}; 
	    if ($name ) { print $name; }
	    if ( $name and $description ) { print ' - '; }
	    if ( $description ) { print $description; } 
	    print qq{ ($studnum)</td><td>$total</td></tr>\n}; 
	}

	$grandtotal += $studenttotal; # do this before formatting

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

    } # end of student loop

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


# Print Grand Total		
print qq{<table border="1" cellpadding ="3" cellspacing="0">\n};
print qq{<tr style="background-color:#444; color:white;">\n};
print qq{<td colspan="2" align="right" class="fam"><b>};
print qq{$lex{'Grand Total'}</b></td><td class="fam">\n};
print format_number( $grandtotal, 2,2);
print qq{</td></tr>\n};

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



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

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

    print qq{<tr><td class="bra">$lex{'End Date'}</td>\n<td class="la">};
    print qq{<input type="text" name="edate" id="edate" size="12" value="$currsdate">\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{<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};

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

}
