#!/usr/bin/perl
#  Copyright 2001-2024 Leslie Richardson

#  This file is part of Open Admin for Schools.

#  Description: Grade based fees assessment (ie. add charges).


my %lex = ('Main' => 'Main',
	   'Report Card' => 'Report Card',
	   'Student Group' => 'Student Group',
	   'Grade' => 'Grade',
	   'Homeroom' => 'Homeroom',
	   'Continue' => 'Continue',
	   'Show Withdrawn Students' => 'Show Withdrawn Students',
	   'Subject Enrollments' => 'Subject Enrollments',
	   'Add' => 'Add',
	   'Checked' => 'Checked',
	   'Blank=All' => 'Blank=All',
	   'Separate with Spaces' => 'Separate with Spaces',
	   'Starts with' => 'Starts with',
	   'Terms' => 'Terms',
	   'Start' => 'Start',
	   'End' => 'End',
	   'Student' => 'Student',
	   'Selection' => 'Selection',
	   'Subject' => 'Subject',
	   'Grades' => 'Grades',
	   'Starts with' => 'Starts with',
	   'Subjects' => 'Subjects',
	   'Students' => 'Students',
	   'Record(s) Stored' => 'Record(s) Stored',
	   'Record Exists' => 'Record Exists',
	   'Contact' => 'Contact',
	   'Error' => 'Error',
	   'Checked' => 'Checked',
	   'Term' => 'Term',
	   'Not Found' => 'Not Found',
	   'Skip' => 'Skip',
	   'and' => 'and',
	   'Or' => 'Or',
	   'Name' => 'Name',
	   'Student Number' => 'Student Number',
	   'Next Page' => 'Next Page',
	   'Sort by' => 'Sort by',
	   'Mdl' => 'Mdl',
	   'Save' => 'Save',

	   'Fee' => 'Fee',
	   'Fees' => 'Fees',
	   'Charge' => 'Charge',
	   'Record Exists' => 'Record Exists',

	   );

my $self = 'assessfeesall.pl';

use DBI;
use CGI;

eval require "../../etc/admin.conf";
if ( $@ ) {
    print $lex{Error}. " $@<br>\n";
    die $lex{Error}. " $@\n";
}

my $q = new CGI;
print $q->header( -charset, $charset ); 
my %arr = $q->Vars;


my $grade = $arr{grade};
my $checked = $arr{checked};

my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);


# Print Page Header
my $title = "$lex{Charge} $lex{Fees}";

print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">\n};
print qq{$chartype\n</head><body>\n};

# Links
print qq{[ <a href="$homepage">$lex{Main}</a> |\n};
print qq{<a href="$feespage">$lex{Fees}</a> ]\n};

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


if ( not $arr{page} ) {
    showStartPage();

} elsif ( $arr{page} == 1 ) {
    delete $arr{page};
    selectGroups();

} elsif ( $arr{page} == 2 ) {
    delete $arr{page};
    selectFeesStud();

} elsif ( $arr{page} == 3 ) {
    delete $arr{page};
    writeRecords();
}


#----------------
sub showStartPage {
#----------------

    my @grades;
    my $sth = $dbh->prepare("select distinct grade from student 
      where grade != '' and grade is not NULL");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    while ( my $gr = $sth->fetchrow ) {
	push @grades, $gr;
    }
    @grades = sort { $a <=> $b } @grades;


    my @hrooms;
    my $sth = $dbh->prepare("select distinct homeroom from student 
      where homeroom is not NULL and homeroom != ''");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    while ( my $hr = $sth->fetchrow ) {
	push @hrooms, $hr;
    }
    @hrooms = sort { $a <=> $b } @hrooms;


    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};

    print qq{<table border="0" cellpadding="3" cellspacing="0">\n};
 
#style="padding:1em;margin:0.5em 5em;border:1px solid gray;">\n};
#    print qq{<tr style="color:white; background-color:blue;">\n};
#    print qq{<td colspan="2" style="font-size:150%">

    print qq{<tr><th colspan="2">$lex{Student} $lex{Selection}</th></tr>\n};

    # Grade
    print qq{<tr><td class="bra">$lex{Grade}</td>\n};
    print qq{<td class="la"><select name="grade"><option></option>\n};
    foreach my $gr ( @grades ) { print qq{<option>$gr</option>}; }
    print qq{</select></td></tr>\n};

    # OR
    print qq{<tr><td class="bra"></td><td class="la"><b>$lex{Or}</b></td></tr>\n};

    # Homeroom
    print qq{<tr><td class="bra">$lex{Homeroom}</td>\n};
    print qq{<td class="la"><select name="homeroom"><option></option>\n};
    foreach my $hr ( @hrooms ) { print qq{<option>$hr</option>}; }
    print qq{</select></td></tr>\n};


#    print qq{<tr><td class="bra">$lex{'Student Group'}</td>\n};
#    print qq{<td class="la"><select name="group"><option>$lex{Grade}</option>\n};
#    print qq{<option>$lex{Homeroom}</option><option>$lex{'Student Number'}</option></select>\n};

#    print qq{<input type="input" name="groupid" size="12">\n};
#    print qq{$lex{'Separate with Spaces'}, $lex{'Blank=All'}</td></tr>\n};


    print qq{<tr><td class="bra">$lex{'Next Page'} $lex{Checked}?</td>\n};
    print qq{<td><input type="checkbox" name="studcheck" value="1" checked="checked"></td></tr>\n};

    print qq{<tr><td class="bra">$lex{'Show Withdrawn Students'}</td>\n};
    print qq{<td class="la"><input type="checkbox" name="showwithdrawn" value="1"></td></tr>\n};

#    print qq{<tr><td class="bra">$lex{'Sort by'} $lex{Name}</td>\n};
#    print qq{<td class="la"><input type="checkbox" name="sortbyname" value="1"></td></tr>\n};

    # Spacer
    print qq{<tr><td colspan="2">&nbsp;</td></tr>\n};


    # Now Fee Selection.
#    print qq{<div style="padding:1em;margin:0.5em 5em;border:1px solid gray;">\n};
#    print qq{<div style="color:white; background-color:blue; font-size:150%;">\n};
#    print qq{$lex{Fee} $lex{Selection}</div>\n};


#    print qq{<table border="0" cellpadding="3" cellspacing="0">\n};
 
#style="padding:1em;margin:0.5em 5em;border:1px solid gray;">\n};
#    print qq{<tr style="color:white; background-color:blue;">\n};
#    print qq{<td colspan="2" style="font-size:150%">

    print qq{<tr><th colspan="2">$lex{Fee} $lex{Selection}</th></tr>\n};


    # Search for Fees by Grade
    print qq{<tr><td class="bra">$lex{Grades}</td>\n};
    print qq{<td><input type="input" name="feesgrade" size="12"> $lex{'Separate with Spaces'}</td></tr>\n};

    # Alternate future choices for fees,
#    print qq{<tr><td class="bra"></td><td class="la"><b>};
#    print $lex{Or}. "</b></td></tr>\n};

=head
    # Search by Subject Name (Description)
    print qq{<tr><td class="bra">};
    print $lex{'Starts with'}. "</td>\n<td>};
    print qq{<input type="input" name="subjname" size="16"></td></tr>\n};

    print qq{<tr><td class="bra"></td><td class="la"><b>};
    print $lex{Or}. "</b></td></tr>\n};
=cut


#    Replace the two lines above with this once ready to do AND operations.
#    print qq{<tr><td></td><td class="la"><select name="selecttype">\n};
#    print qq{<option>". $lex{Or}. "</option><option>". $lex{and}. "</option></select>\n};
#    print qq{</td></tr>\n};


    print qq{<tr><td class="bra">$lex{'Next Page'} $lex{Checked}?</td>\n};
    print qq{<td><input type="checkbox" name="feescheck" value="1" checked="checked"></td></tr>\n};

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

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

    exit;
}



#-----------------
sub selectGroups { # select groups of students and subjects to enrol.
#-----------------

    #foreach my $key ( sort keys %arr) { print qq{K:$key V:$arr{$key}<br>\n}; }

    my %sortedstudents;
    my (@students, %students);

    my $table = 'student'; # overridden to studentall if show withdrawn selected.
    if ( $arr{showwithdrawn} ) { # change to both student tables.
	$table = 'studentall';
    }

    my ($studcheck, $feescheck);
    if ( $arr{studcheck} ) {
	$studcheck = qq{checked="checked"};
    }
    if ( $arr{feescheck} ) {
	$feescheck = qq{checked="checked"};
    }

    my @feesgrade = split(/\s/, $arr{feesgrade});

    # get student names
    my $groupvalue = $arr{grade};
    my $grouptype = 'grade';
    if ( $arr{homeroom} ) {
	$groupvalue = $arr{homeroom};
	$grouptype = 'homeroom';
    }
    my $groupselect;
    if ( $groupvalue ) {
	$groupselect = qq{where $grouptype = ?};
    }


    my $sth = $dbh->prepare("select lastname, firstname, studnum, $grouptype from $table
      $groupselect
      order by grade, lastname, firstname");

    if ( $groupvalue ) {
	$sth->execute( $groupvalue );
    } else {
	$sth->execute;
    }

    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    while ( my ( $lastname, $firstname, $studnum, $grp ) = $sth->fetchrow ) {
	push @students, $studnum;
	$students{$studnum} = "$lastname, $firstname ($grp)";
    }


    # Now do Fee Selection.
    my ( @fees, %fees);


=head   # leave intact for future search function 
    # Search by description if any...
    if ( $arr{subjname} ) { 

	# Find all subjects with this name.
	my $sth = $dbh->prepare("select description, subjsec from subject 
             where description like ? order by description");
	$desc = $arr{subjname}. '%';
	$sth->execute( $desc );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

	while ( my ( $description, $subjsec ) = $sth->fetchrow ) {
	    push @subjects_name, $subjsec;
	    $subjects_name{$subjsec} = $description;
	}
	
    }
=cut


    my @feesgrade = split(/\s+/, $arr{feesgrade});

    foreach my $gr ( @feesgrade ) {
	# Find all fees in this grade...
	my $sth = $dbh->prepare("select id, name, description, amount from fees_predefined
          where group_name = 'grade' and group_value = ? order by description");
	$sth->execute( $gr );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    
	while ( my ( $id, $name, $description, $amount ) = $sth->fetchrow ) {
	    push @fees, $id;
	    $fees{$id} = "$description ($name)";
	}
    }

    # Now should have @fees and %fees populated.



    # Print Form start
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="2">\n};

    # Get Checkbox state (for next page for select particular fee-student add).
    print qq{<div><span style="border:1px solid gray;margin:1em;padding:1em; text-align:left;">};
    print qq{$lex{'Next Page'} $lex{Checked}? };
    print qq{<input type="checkbox" name="checked" value="1" checked="checked">\n};
    print qq{</span></div><p></p>\n};


    # Print Student Information.
    print qq{<table cellpadding="3" cellspacing="0" border="1" style="float:left;margin:1em;">\n};
    print qq{<tr><th colspan="2">$lex{Students}</th></tr>\n};
    print qq{<tr><td class="cn"><input type="submit" value="$lex{Continue}"></td></tr>\n};

    my $class = 'blue';
    
    foreach my $studnum ( @students ){  #loop through students

	# Manage Class value.
	if ( not $arr{sortbyname}  ) {
	    my ($dud, $grp) = split(/\(/, $students{$studnum});
	    chop $grp; # remove trailing parens.
	    $oldgrp = $currgrp;
	    $currgrp = $grp;
	    if ( $currgrp ne $oldgrp ) { # switch colors;
		if ( $class eq 'blue' ) { $class = 'gray'; } else { $class = 'blue'; }
	    }
	}

	print qq{<tr class="$class"><td>};
	print qq{<input type="checkbox" name="$students{$studnum}:ST:$studnum" value="1" $studcheck>\n};
	print qq{ $students{$studnum}</td></tr>\n};

    }

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



    # Print Fee Information, now
    print qq{<table cellpadding="3" cellspacing="0" border="1" style="float:left;margin:1em;">\n};

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

    my $sth = $dbh->prepare("select * from fees_predefined where id = ?");

    my $first = 1;

    foreach my $id ( @fees ){  # loop through fees
	$sth->execute( $id );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $ref = $sth->fetchrow_hashref;
	my %r = %$ref;
	
	print qq{<tr class="gray"><td><input type="checkbox" name="$fees{$id}:FE:$id" value="1" $feescheck>\n};
	print qq{$r{description} ($r{name})</td><td>$r{amount}</td></tr>\n};
    }

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

    exit;

}



#-----------------
sub selectFeesStud {
#-----------------

    # select Student-Fees - one student in one fee;
    my $checked;
    if ( $arr{checked} ) { $checked = qq{checked="checked"}; }
    delete $arr{checked};

    my (%fees, %students);
    foreach my $key ( sort keys %arr) {
	my ($desc, $sigil, $value ) = split /:/, $key;
	if ( $sigil eq 'ST' ) {
	    $students{$value} = $desc; 
	} elsif ( $sigil eq 'FE' ) { 
	    $fees{$value} = $desc; 
	} else { # problems!
	    print qq{<h3>$lex{Error}: $key</h3>\n};
	    exit;
	}

    }

    my $sth = $dbh->prepare("select * from fees_predefined where id = ?");
    # Check for duplicates
    my $sth1 = $dbh->prepare("select count(*) from fees_jrl where name = ? and studnum = ?");


    # Start Form
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="3">\n};

    print qq{<div style="text-align:left;margin:0.5em;"><input type="submit" value="$lex{Save}"></div>\n};

    foreach my $id ( keys %fees ) {

	# Read the fee information (since need to check for duplicates based on name)
	$sth->execute( $id );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	my $fref = $sth->fetchrow_hashref;
	%fee = %$fref;


	# Print Table Heading
	print qq{<table cellpadding="3" cellspacing="0" border="1" style="float:left;margin:0.5em;">\n};
	print qq{<tr><th>$fee{description} ($fee{name})</th></tr>\n};

	# get sorted list of student names
	my %sorted;
	foreach my $key ( keys %students ) {
	    my $desc = $students{$key};
	    $sorted{"$desc$key"} = $key;
	}

	foreach my $key ( sort keys %sorted ) {

	    my $studnum = $sorted{$key};

	    # Check for the existence of the same record(s) (same fee name, same student number);
	    $sth1->execute( $fee{name}, $studnum );
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	    my $dupcount = $sth1->fetchrow;
	    my $localcheck = $checked;
	    my $exists;
	    if ( $dupcount > 0 ) {
		$localcheck = undef;
		$exists = qq{<span style="color:red;font-weight:bold;">$lex{'Record Exists'}</span>};
	    }
	    
	    # print a current table row
	    print qq{<tr><td><input type="checkbox" name="$id:$studnum" value="1" $localcheck>\n};
	    print qq{$students{$studnum} $exists</td>\n};
	}

	print qq{</table>\n};

    }

    print qq{<br clear="left">\n};
    print qq{<div style="text-align:left;margin:0.5em;">\n};
    print qq{<input type="submit" value="$lex{Save}"></div>\n};

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

    exit;


} # end of selectFeesStud



#---------------
sub writeRecords {
#---------------

    # calc current date
    my @tim = localtime(time);
    my $year = @tim[5] + 1900;
    my $month = @tim[4] + 1;
    my $day = @tim[3];
    my $currdate = "$year-$month-$day";

    
    # foreach my $key (keys %arr) { print qq{K:$key V:$arr{$key}<br>\n}; }

    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
    print qq{<tr><th>$lex{Student}</th><th>$lex{Fee}</th></tr>\n};


    my $sth = $dbh->prepare("select * from fees_predefined where id = ?");
    my $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");

    # Check for duplicates; now done above on previous page
    # my $sth2 = $dbh->prepare("select count(*) from fees_jrl where name = ? and studnum = ?");

    # Insert a new fee charge record
    my $sth3 = $dbh->prepare("insert into fees_jrl ( studnum, trans_date, trans_type, 
     name, description, subtotal, tax1, tax1_name, tax2, tax2_name, tax3, tax3_name, tax4, tax4_name,
     total ) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )");


    foreach my $key ( sort keys %arr ) {  # $key contains studnum:subjsec 

	my ( $id, $studnum ) = split(/:/,$key);
       
	# Read the fee information
	$sth->execute( $id );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	my $fref = $sth->fetchrow_hashref;
	%fee = %$fref;

	# Get Total Fee charge
	my $total = $fee{amount} + $fee{tax1} + $fee{tax2} + $fee{tax3} + $fee{tax4};

	# Get Student Name
	$sth1->execute( $studnum );
	if ( $DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my ( $lastname, $firstname ) = $sth1->fetchrow;
	if ( not $lastname ) { 
	    $lastname = qq{<span style="color:red;">$lex{'Not Found'}</span>}; 
	}


	# Insert Record
	$sth3->execute( $studnum, $currdate, 'chg', $fee{name}, $fee{description}, $fee{amount},
          $fee{tax1}, $fee{'tax1_name'}, $fee{tax2}, $fee{'tax2_name'}, $fee{tax3}, $fee{'tax3_name'}, 
	  $fee{tax4}, $fee{'tax4_name'}, $total );
	if ( $DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }


	# print line for this record.
	print qq{<tr><td><b>$lastname</b>, $firstname ($studnum)</td>};
	print qq{<td>$fee{description} ($fee{name})</td></tr>\n};

	
    } # Next Student/Fee combination.


    if ( not $DBI::errstr ) {
	print qq{</table>\n};
	print qq{<h3>$lex{'Record(s) Stored'}</h3>\n};

    } else {
	print qq{</table><p>$lex{Error}: $DBI::errstr<br>\n};
	print qq{$lex{'Contact'}:$adminname <a href="mailto:$adminemail">$adminemail</a></p>\n};
    }

    print qq{<p>[ <a href="$feespage">$lex{Fees}</a> ]</p>\n};
    print qq{</body></html>\n};

    exit;

} # End of writeRecords
