#!/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 = ('Student Fees Outstanding' => 'Student Fees Outstanding',
	   'Main' => 'Main',
	   'No Outstanding Charges(s) Found' => 'No Outstanding Charges(s) Found',
	   'Date' => 'Date',
	   'Description' => 'Description',
	   'Amount' => 'Amount',
	   'Grand Total' => 'Grand Total',
	   'Family Total' => 'Family Total',
	   'Total' => 'Total',
	   'Fees' => 'Fees',
	   'Error' => 'Error',
	   'Phone' => 'Phone',
	   'Error' => 'Error',
	   'Fix Zero Balance' => 'Fix Zero Balance',
	   'Withdrawn' => 'Withdrawn',
	   'Students' => 'Students',

	   );

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

# Extra fields reported for each student...
my @fields = ('parent1','address1','hphone1');

# Read config variables
eval require "../../etc/admin.conf";
if ( $@ ) {
    print $lex{Error}. " $@<br>\n";
    print $lex{Error}. " $@<br>\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 $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);


my $title = $lex{'Student Fees Outstanding'};

print qq{$doctype\n<html><head><title>$title</title>
<link rel="stylesheet" href="$css" type="text/css">\n};
print qq{<style type="text/css">td.fam {font-size:130%;font-weight:bold;}</style>\n};

print qq{$chartype\n</head><body>\n};
print qq{<p>[ <a href="$homepage">$lex{Main}</a> |\n};
print qq{<a href="$feespage">$lex{Fees}</a> ]</p>\n};

print qq{<div><form action="fixzeroacct.pl" method="post">\n};
print qq{<input type="submit" value="$lex{'Fix Zero Balance'}"></form></div>\n};

print qq{<h1>$title</h1><p><b>$currdate</b></p>\n};


# First, find distinct student numbers in outstanding invoices.
my @outstanding;
my $sth = $dbh->prepare("select distinct studnum from fees_jrl where
 paid_id is null and ( trans_type = 'chg' or trans_type = 'roa') ");
$sth->execute;
if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
while ( my $studnum = $sth->fetchrow ) {
    push @outstanding, $studnum;
}

if ( not @outstanding ){ # No outstanding transactions found
    print qq{<h1>$lex{'No Outstanding Charges(s) Found'}!</h1>\n};
    print qq{[ <a href="$homepage">$lex{Main}</a> ]\n}; 
    print qq{</body></html>\n};
    
    exit;
}


my %homephone;
my @nophone;
my @withdrawn;

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

foreach my $studnum ( @outstanding ) {

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

    # Get Withdrawn count
    $sth1->execute( $studnum );
    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
    my $wdcount = $sth1->fetchrow;
    if ( $wdcount ) {
	push @withdrawn, $studnum;
	next;
    }

    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 = ?");

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

    #my $phone = $sortedphone{$key};
    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;


	# Find outstanding transactions
	$sth = $dbh->prepare("select trans_date, name, description, total from fees_jrl
          where paid_id is null and ( trans_type = 'chg' or trans_type = 'roa') and
          studnum = ? order by trans_date desc");
	$sth->execute( $studnum );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }

	# Loop through all outstanding student values
	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 class="ra">}; 
	    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" class="ra">};
	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" style="text-align:right;font-weight:bold;" class="fam"><b>};
    print qq{$surname ($phone) $lex{'Family Total'}};
    print qq{</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 outstanding transactions
	$sth = $dbh->prepare("select trans_date, name, description, total from fees_jrl
          where paid_id is null and ( trans_type = 'chg' or trans_type = 'roa') and
          studnum = ? order by trans_date desc");
	$sth->execute( $studnum );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }

	# Loop through all outstanding student values
	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 class="ra">}; 
	    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" class="ra">};
	print qq{<b>$lastname</b>, $firstname ($studnum) <b>$lex{Total}</b></td>};
	print qq{<td>$studenttotal</td></tr>\n};

    } # end of student loop

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


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

    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;">$lex{Withdrawn} $lex{Students}</caption>\n};
    print qq{<tr><th>$lex{Date}</th><th>$lex{Description}</th><th>};
    print qq{$lex{Amount}</th></tr>\n};

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


	# Find outstanding transactions
	$sth = $dbh->prepare("select trans_date, name, description, total from fees_jrl
          where paid_id is null and ( trans_type = 'chg' or trans_type = 'roa') and
          studnum = ? order by trans_date desc");
	$sth->execute( $studnum );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }

	# Loop through all outstanding student values
	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 class="ra">}; 
	    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" class="ra">};
	print qq{<b>$lastname</b>, $firstname ($studnum) <b>$lex{Total}</b></td>};
	print qq{<td>$studenttotal</td></tr>\n};

    } # end of student loop

    print qq{</table><p></p>\n};
} # end of withdrawn loop


# 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" class="ra" class="fam">};
print qq{<b>$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};
