#!/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. # Fix zero balance accounts my %lex = ('Main' => 'Main', 'No Outstanding Charges(s) Found' => 'No Outstanding Charges(s) Found', 'Error' => 'Error', 'Continue' => 'Continue', 'Record(s) Updated' => 'Record(s) Updated', 'Not Found' => 'Not Found', 'Fix Zero Balance' => 'Fix Zero Balance', 'Fees' => 'Fees', ); use DBI; use CGI; use Number::Format qw(round); my $self = 'fixzeroacct.pl'; # 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{'Fix Zero Balance'}; print qq{$doctype\n $chartype\n[ $lex{Main} |\n}; print qq{$lex{Fees} ]\n}; print qq{

$title

$currdate

\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 != 'pay'"); $sth->execute; if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } while ( my $studnum = $sth->fetchrow ) { push @outstanding, $studnum; } if ( not @outstanding ){ # No outstanding charges found print qq{

$lex{'No Outstanding Charges(s) Found'}!

\n}; print qq{[ $lex{Main} ]\n}; print qq{\n}; exit; } if ( not $arr{page} ) { showStartPage(); } else { delete $arr{page}; fixZero(); } #---------------- sub showStartPage { #---------------- print qq{
\n}; print qq{\n}; print qq{\n}; # Check outstanding balance and see if zero balance account my @students = (); my $sth = $dbh->prepare("select distinct studnum from fees_jrl where studnum != 0"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $studnum = $sth->fetchrow ) { push @students, $studnum; } # check for zero balance accounts (and not paid) $sth = $dbh->prepare("select id, total, trans_type from fees_jrl where studnum = ? and paid_id is NULL and trans_type != 'pay'"); my $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?"); my $studentcount; foreach my $studnum ( @students ) { # find outstanding $sth->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $balance; my @ids = (); my $first = 1; while ( my ($id, $total,$transtype ) = $sth->fetchrow ) { if ( $transtype eq 'pay' ) { next; } #skip pay types since old code not set paid_id push @ids, $id; $balance += $total; $first = 0; } if ( not $balance and not $first ) { # a zero account # Get student name. $sth1->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($lastname, $firstname) = $sth1->fetchrow; print qq{\n}; $studentcount++; } } # end of student loop. if ( not $studentcount ) { # no students print qq{

$lex{'Not Found'}

\n}; print qq{\n}; exit; } print qq{\n}; print qq{
}; print qq{$lastname, $firstname ($studnum)
\n}; exit; } #---------- sub fixZero { #---------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # Now mark the records paid... (skip receipt update for the roa transactions since already done. my $sth = $dbh->prepare("select id, trans_type, receipt from fees_jrl where studnum = ? and paid_id is NULL order by id desc"); my $sth1 = $dbh->prepare("update fees_jrl set paid_id = ? where id = ?"); my $sth2 = $dbh->prepare("update fees_jrl set receipt = ? where id = ?"); foreach my $key ( sort keys %arr ) { my $studnum = $arr{$key}; # get their transactions without paid_id values. $sth->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($curreceipt, $currpayid); while ( my ( $id, $transtype,$receipt ) = $sth->fetchrow ) { print qq{ID:$id TT:$transtype REC:$receipt
\n}; if ( $transtype eq 'pay' ) { # update it's paidid to it's id $sth1->execute( $id, $id ); # set paid_id = $id; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } } elsif ( $transtype eq 'roa' ) { $curreceipt = $receipt; $currpayid = $id; $sth1->execute( $id, $id ); # set paid_id = $id; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } } else { # it's a chg (charge) $sth1->execute( $currpayid, $id ); # set paid_id = $id; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } $sth2->execute( $curreceipt, $id ); # set paid_id = $id; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } } } } # end of student loop print qq{

$lex{'Record(s) Updated'}

\n}; exit; } # end of fixZero