#!/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}. " $@<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{'Fix Zero Balance'};

print qq{$doctype\n<html><head><title></title>
<link rel="stylesheet" href="$css" type="text/css">
$chartype\n</head><body>[ <a href="$homepage">$lex{Main}</a> |\n};
print qq{<a href="$feespage">$lex{Fees}</a> ]\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 != '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{<h1>$lex{'No Outstanding Charges(s) Found'}!</h1>\n};
    print qq{[ <a href="$homepage">$lex{Main}</a> ]\n}; 
    print qq{</body></html>\n};
    
    exit;
}

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


#----------------
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" cellspacing="0" border="1">\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{<tr><td class="la"><input type="checkbox" name="$lastname$firstname$studnum" };
	    print qq{value="$studnum"> };
	    print qq{<b>$lastname</b>, $firstname ($studnum)</td></tr>\n};

	    $studentcount++;

	}
    } # end of student loop.

    if ( not $studentcount ) { # no students
	print qq{<h1>$lex{'Not Found'}</h1>\n};
	print qq{</body></html>\n};
	exit;
    }

    print qq{<tr><td colspan="2" class="cn"><input type="submit" value="$lex{Continue}"></td>\n};

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

    exit;

}


#----------
sub fixZero {
#----------

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\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<br>\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{<p>$lex{'Record(s) Updated'}</p></body></html>\n};

    exit;

} # end of fixZero
