#!/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}. " $@
\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$title \n}; if ( not $arr{page} ) { $focus = 1; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; } else { $focus = 0; } print qq{$chartype\n}; print qq{\n}; print qq{[ $lex{Main} |\n}; print qq{$lex{Fees} ]\n}; print qq{

$title

$currdate

\n}; if ( not $arr{page} ) { showStartPage(); } else { delete $arr{page}; } #foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # Print Start/End Dates print qq{

$lex{'Start Date'} $arr{sdate}
\n}; print qq{$lex{'End Date'} $arr{edate}

\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{

No Payments

\n}; print qq{[ $lex{Main} ]\n}; print qq{\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{\n}; print qq{\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{\n}; } $grandtotal += $studenttotal; # do this before formatting $studenttotal = format_number( $studenttotal, 2,2); print qq{\n}; print qq{}; print qq{\n}; } # end of Student # print family totals $familytotal = format_number( $familytotal, 2,2); print qq{}; print qq{\n}; print qq{\n}; } # End of homephone loop; # Now the nophone loop; if ( @nophone ) { print qq{
$lex{Date}$lex{Description}}; print qq{$lex{Amount}
$trans_date\n}; if ($name ) { print $name; } if ( $name and $description ) { print ' - '; } if ( $description ) { print $description; } print qq{ ($studnum)$total
}; print qq{$lastname, $firstname ($studnum) $lex{Total}$studenttotal
}; print qq{$surname ($phone) $lex{'Family Total'}}; print qq{$familytotal
 

\n}; # Print Table Header print qq{\n}; print qq{\n}; print qq{\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{\n}; } $grandtotal += $studenttotal; # do this before formatting $studenttotal = format_number( $studenttotal, 2,2); print qq{\n}; print qq{}; print qq{\n}; } # end of student loop print qq{
Student Without Home Phone
$lex{Date}$lex{Description}}; print qq{$lex{Amount}
$trans_date}; if ($name ) { print $name; } if ( $name and $description ) { print ' - '; } if ( $description ) { print $description; } print qq{ ($studnum)$total
}; print qq{$lastname, $firstname ($studnum) $lex{Total}$studenttotal

\n}; } # Print Grand Total print qq{\n}; print qq{\n}; print qq{\n}; print qq{
}; print qq{$lex{'Grand Total'}\n}; print format_number( $grandtotal, 2,2); print qq{
\n}; #---------------- sub showStartPage { #---------------- print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n\n}; print qq{\n\n}; print qq{\n}; print qq{
$lex{'Start Date'}}; print qq{\n}; print qq{
$lex{'End Date'}}; print qq{\n}; print qq{
}; print qq{\n}; print qq{
\n}; print qq{\n}; print qq{\n}; exit; }