#!/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}. " $@ \n";
print $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 $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
my $title = $lex{'Student Fees Outstanding'};
print qq{$doctype\n$title
\n};
print qq{\n};
print qq{$chartype\n\n};
print qq{
\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{
$lex{'No Outstanding Charges(s) Found'}!
\n};
print qq{[ $lex{Main} ]\n};
print qq{\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{
\n};
print qq{
$lex{Date}
$lex{Description}
};
print qq{$lex{Amount}
\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{
$trans_date
};
if ($name ) { print $name; }
if ( $name and $description ) { print ' - '; }
if ( $description ) { print $description; }
print qq{ ($studnum)
$total
\n};
}
$grandtotal += $studenttotal; # do this before formatting
$studenttotal = format_number( $studenttotal, 2,2);
print qq{