#!/usr/bin/perl
#  Copyright 2001-2017 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 = ('View' => 'View',
	   'Transactions' => 'Transactions',
	   'Select' => 'Select',
	   'Main' => 'Main',
	   'Error' => 'Error',
	   'Fees' => 'Fees',
	   'Date' => 'Date',
	   'Type' => 'Type',
	   'Name' => 'Name',
	   'Description' => 'Description',
	   'Amount' => 'Amount',
	   'Receipt' => 'Receipt',
	   'Id' => 'Id',
	   'No Records Found' => 'No Records Found',
	   'Continue' => 'Continue',
	   'All' => 'All',
	   'Paid' => 'Paid',
	   'Unpaid' => 'Unpaid',
	   'Student Group' => 'Student Group',
	   'Grade' => 'Grade',
	   'Homeroom' => 'Homeroom',
	   'Last,First/Last/Initials/Studnum' => 'Last,First/Last/Initials/Studnum',
	   'Student' => 'Student',
	   'or' => 'or',
	   'No Student(s) Found' => 'No Student(s) Found',
	   'Mode' => 'Mode',
	   'Balance' => 'Balance',
	   'Check' => 'Check',
	   'Next Page' => 'Next Page',
	   'Starting Date' => 'Starting Date',

	   );

use DBI;
use CGI;
use Number::Format qw(:all);

# 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 $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);



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

if ( not $arr{page} ) { # calendar popup is on start page.
    print qq{<link rel="stylesheet" type="text/css" media="all" };
    print qq{href="/js/calendar-blue.css" title="blue">\n};
    print qq{<script type="text/javascript" src="/js/calendar.js"></script>\n};
    print qq{<script type="text/javascript" src="/js/lang/calendar-en.js"></script>\n};
    print qq{<script type="text/javascript" src="/js/calendar-setup.js"></script>\n};
}

print qq{$chartype\n</head>\n};

print qq{<body>[ <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};

    if ( $arr{student} ) {
	searchStudent();
    } else {
	searchGroup();
    }

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



#-------------------
sub showTransactions {
#-------------------

    # Passed: list of student numbers (studnum)
    #  Secondly: which transactions.
    # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}<br>\n"; }

    my $transactmode = $arr{transactmode};
    delete $arr{transactmode};

    my $startdate = $arr{startdate};
    delete $arr{startdate};

    print qq{<p><b>$lex{Mode}</b> $transactmode  <b>$lex{'Starting Date'}</b> $startdate</p>\n};

    
    # Setup Query
    my $sth = $dbh->prepare("select lastname, firstname, grade, homeroom from studentall where studnum = ?");

    my $sth1 = $dbh->prepare("select id, trans_date, trans_type, name, description, total, 
      paid_id, receipt from fees_jrl
      where studnum = ? and to_days(trans_date) >= to_days('$startdate') order by trans_date, id");

    my $sth2 = $dbh->prepare("select sum(total) from fees_jrl where studnum = ?");
    my $sth3 = $dbh->prepare("select count(*) from fees_jrl where paid_id is NULL and studnum = ?");

    # create sorting structure.
    my %students;
    foreach my $studnum ( keys %arr ) {
	$sth->execute( $studnum );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	my ( $lastname, $firstname, $grade, $homeroom ) = $sth->fetchrow;

	$students{"$lastname$firstname$studnum"} = { 'lastname' => $lastname, 
						     'firstname' => $firstname, 
						     'studnum' => $studnum,
						     'grade' => $grade,
						     'homeroom' => $homeroom
	};
    };

    foreach my $key ( sort keys %students ) {
	my $ref = $students{$key};
	my %r = %$ref;
	my $studnum = $r{studnum};

	
	# Check for error with zero balance and some unmarked paid_id values.
	$sth2->execute( $studnum );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	my $balance = $sth2->fetchrow;
	my $pcount;
	if ( $balance == 0 ) { # check for blank paid_id
	    $sth3->execute( $studnum );
	    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	    $pcount = $sth3->fetchrow;
	}

	if ( $pcount ) { # if we have zero balance and some unmarked paid_id fields, show form
	    print qq{<form action="transed.pl" method="post" style="display:inline">\n};
	    print qq{<input type="hidden" name="studnum" value="$studnum">\n};
	    print qq{<input type="submit" value="Fix Record"></form>\n};
	}



	# Table Header
	print qq{<table border="1" cellpadding ="3" cellspacing="0" style="margin-bottom:1em;">\n};

	# Student Info
	print qq{<caption style="font-weight:bold;text-align:left;"><b>$r{firstname} $r{lastname}</b> ($studnum)\n};
	print qq{ <b>$lex{Grade}</b>: $r{grade}\n};
	if ( $r{homeroom} ) { print qq{<b>$lex{Homeroom}</b>: $r{homeroom}\n}; }
	print qq{</caption>\n};

	# Field Description Line
	print qq{<tr><th>$lex{Id}</th><th>$lex{Date}</th><th>$lex{Type}</th>};
	print qq{<th>$lex{Name}</th><th>$lex{Description}</th>};
	print qq{<th>$lex{Amount}</th><th>$lex{Paid} $lex{Id}</th><th>$lex{Receipt}</th></tr>\n};

	 
		 
	
	# Find  student transactions, if any after the start date.
	$sth1->execute( $studnum );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }

	my $first = 1;

	while ( my ($id, $trans_date, $trans_type, $name, 
		    $description, $total, $paid_id, $receipt ) = $sth1->fetchrow ) {

	     if ( $transactmode eq $lex{Unpaid} ) {
		 if ( $trans_type eq 'pay' or $paid_id )  { next; }
	     }

	     $first = 0;

	     print qq{<tr><td>$id</td><td>$trans_date</td><td>$trans_type</td><td>$name</td><td>$description</td>\n};
	     print qq{<td class="ra">$total</td><td>$paid_id</td><td>$receipt</td></tr>\n};
	 }

	 if ( $first ) {
	     print qq{<tr style="border:1px solid gray;padding:0.4em;">};
	     print qq{<td colspan="8" class="la">$lex{'No Records Found'}</td></tr>\n};
	 }

	# Balance
	my $balanceduefmt = format_number( $balance, 2,2);
	print qq{<tr style="background-color:#DDD;font-size:120%;">};
	print qq{<td colspan="5" class="bra">$lex{Balance}</td><td class="ra">$balanceduefmt</td>\n};
	print qq{<td colspan="2"></td></tr>\n};

	
	print qq{</table>\n};

     }

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

    exit;

}


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

    # Get value for fees rollover date
    my @fieldnames = qw( f_FeesRolloverDate ); 
    my $sth = $dbh->prepare("select datavalue from conf_system where dataname = ?");
    foreach my $var ( @fieldnames ) {
	$sth->execute( $var );
	my $datavalue = $sth->fetchrow;
	eval $datavalue;
	if ( $@ ) {
	    print "$lex{Error}: $@<br>\n";
	    die "$lex{Error}: $@\n";
	}
    }


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

    print qq{<table cellspacing="0" cellpadding="3" border="0">\n};

    print qq{<tr><td class="bra">};
    print qq{$lex{Select} $lex{Transactions}</td>\n};
    print qq{<td><select name="transactmode"><option>$lex{All}</option>\n};
    print qq{<option>$lex{Unpaid}</option></select>\n};
    print qq{</td></tr>\n};

    print qq{<tr><td class="bra">$lex{'Starting Date'}</td>\n};
    print qq{<td><input type="text" name="startdate" id="startdate" size="12" value="$f_FeesRolloverDate">\n};
    print qq{<button type="reset" id="start_trigger">...</button></td></tr>\n};
    print qq{<tr><td colspan="2"><hr></td></tr>\n\n};    

    print qq{<tr><td class="bra">$lex{'Student Group'}</td>\n};
    print qq{<td><select name="group"><option>$lex{Grade}</option>\n};
    print qq{<option>$lex{Homeroom}</option></select>\n};
    print qq{<input type="text" name="groupid" size="6"></td></tr>\n};;

    print qq{<tr><td></td><td style="bla">$lex{or}</td></tr>\n};

    print qq{<tr><td class="bra" style="vertical-align:top;">$lex{Student}</td>\n};
    print qq{<td><input type="text" name="student" size="30"><br>\n};
    print qq{$lex{'Last,First/Last/Initials/Studnum'}</td></tr>\n};

    print qq{<tr><td colspan="2"><hr></td></tr>\n\n};
    
    print qq{<tr><td class="bra">$lex{Check} $lex{'Next Page'}</td>\n};
    print qq{<td><input type="checkbox" name="check"></td></tr>\n};

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

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

    print qq{<script type="text/javascript">
     Calendar.setup({
        inputField     :    "startdate", // id of the input field
        ifFormat       :    "%Y-%m-%d", // format of the input field
        button         :    "start_trigger", // trigger for the calendar (button ID)
        singleClick    :    false,        // double-click mode
        step           :    1             // show all years in drop-down boxes 
    }) };
    print qq{</script>\n};
    print qq{</body></html>\n};
    
    exit;

}


#----------------
sub searchStudent {
#----------------

    my $checked;
    if ( $arr{check} ) {
	$checked = q{checked = "checked"};
    }

    my $student = $arr{student};

    # Setup the Search
    if ($student =~ /\d+/) {  # we have a student number
	$sth = $dbh->prepare("select lastname, firstname, studnum from studentall 
          where studnum = ?");
	$sth->execute( $student );

    } else { # we have words possibly with a comma
	($lastname,$firstname)  = split /\,/, $student;
	$firstname =~ s/^\s*//;
	$lastname =~ s/^\s*//;
	if ($lastname and $firstname){ # both entered.
	    $sth = $dbh->prepare("select  lastname, firstname, studnum from studentall 
             where lastname = ? and firstname = ?");
	    $sth->execute( $lastname, $firstname );

	} elsif ($lastname and not $firstname){ # only lastname (no comma)
	    if (length($lastname) == 2){ # search by initials: fi, li.
		my $fi = substr($lastname,0,1). '%'; 
		my $li = substr($lastname,1,1). '%';
		$sth = $dbh->prepare("select lastname, firstname, studnum from studentall 
                 where lastname $sql{like} ? and firstname $sql{like} ?");
		$sth->execute( $li, $fi );

	    } else {
		$sth = $dbh->prepare("select lastname, firstname, studnum from studentall 
                  where lastname = ? order by firstname");
		$sth->execute( $lastname );
	    }
	} else { # print an error....
	    print qq{<h1>$lex{Error}</h1>\n};
	    print qq{</body></html>\n};
	    exit;
	}

    } # Last Else
    # We should now have a $sth defined.


    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    my $rows = $sth->rows;

    if ( $rows < 1 ) { 
	print qq{<h1>$lex{'No Student(s) Found'}</h1>\n};
	print qq{<table cellspacing="0" cellpadding="3" border="0">\n};
	
	print qq{<form action="$self" method="post">\n};
	print qq{<input type="hidden" name="page" value="1">\n};
	print qq{<input type="hidden" name="transactmode" value="$arr{transactmode}">\n};
	print qq{<input type="hidden" name="startdate" value="$arr{startdate}">\n};
	print qq{<table cellspacing="0" cellpadding="3" border="0">\n};

	print qq{<tr><td colspan="2" class="cn">$lex{Student} };
	print qq{($lex{'Last,First/Last/Initials/Studnum'})<br>};
	print qq{<input type="text" name="student" size="30"></td></tr>\n};

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

    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="2">\n};
    print qq{<input type="hidden" name="transactmode" value="$arr{transactmode}">\n};
    print qq{<input type="hidden" name="startdate" value="$arr{startdate}">\n};
    
    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};

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

    print qq{<tr><th>$lex{Student}</th><th>$lex{Select}</th></tr>\n};

    my $sth1 = $dbh->prepare("select count(*) from fees_jrl where studnum = ?");

    my $first = 1;
    while ( my ($lastname, $firstname, $studnum) = $sth->fetchrow ) {

	# Find any transactions; otherwise skip
	$sth1->execute( $studnum );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my $count = $sth1->fetchrow;

	print qq{<tr><td><b>$lastname</b>, $firstname ($studnum)</td><td>};
	if ( $count ) {
	    print qq{<input type="checkbox" name="$studnum" value="1" $checked>};
	} else {
	    print $lex{'No Records Found'};
	}
	print qq{</td></tr>\n};

    }


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

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

    exit;

} # end of searchStudent



#--------------
sub searchGroup {
#--------------
    # search for a group of students (by grade(s) or homeroom(s))
    # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}<br>\n"; }

    my $checked;
    if ( $arr{check} ) {
	$checked = q{checked = "checked"};
    }

    my ( $group, $groupid ) = ($arr{group}, $arr{groupid});


    # Select Students
    my @students;
    my $sort = 'name'; # hold type of sort.

    if ( $groupid ) { # then we have to do something...

	my @groups = ();
	@groups = split /\s+/, $arr{groupid};
	if ( $group eq $lex{Grade} ) {
	    $group = 'grade';
	    $sort = 'grade';
	} else {
	    $group = 'homeroom';
	    $sort = 'homeroom';
	}

	my $sth = $dbh->prepare("select studnum from student where $group = ? 
         order by $group, lastname, firstname");
	foreach my $grp ( @groups ) {
	    $sth->execute( $grp );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    while ( my $studnum = $sth->fetchrow ) {
		push @students, $studnum;
	    }
	}

    } else { # all students

	my $sortorder = "lastname, firstname";
	if ( $arr{sortorder} eq $lex{Homeroom} ) {
	    $sortorder = "homeroom, lastname, firstname";
	    $sort = 'homeroom';
	} elsif ( $arr{sortorder} eq $lex{Grade} ) {
	    $sortorder = "grade, lastname, firstname";
	    $sort = 'grade';
	}

	$sth = $dbh->prepare("select studnum from student order by $sortorder");
	$sth->execute;
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my $studnum = $sth->fetchrow ) {
	    push @students, $studnum;
	}
    }

    
    # Now loop through all students
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="2">\n};
    print qq{<input type="hidden" name="transactmode" value="$arr{transactmode}">\n};
    print qq{<input type="hidden" name="startdate" value="$arr{startdate}">\n};
    
    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
    print qq{<tr><td colspan="2" class="cn">\n};
    print qq{<input type="submit" value="$lex{Continue}"></td></tr>\n};
    print qq{<tr><th>$lex{Student}</th><th>$lex{Select}</th></tr>\n};


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

    my $first = 1;

    foreach my $studnum ( @students ) {

	$sth->execute( $studnum );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my ($lastname, $firstname ) = $sth->fetchrow;

	# Find any transactions; otherwise skip
	$sth1->execute( $studnum );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my $count = $sth1->fetchrow;

	print qq{<tr><td><b>$lastname</b>, $firstname ($studnum)</td><td>\n};
	if ( $count ) {
	    print qq{<input type="checkbox" name="$studnum" value="1" $checked>\n};
	} else {
	    print $lex{'No Records Found'};
	}
	print qq{</td></tr>\n};

    }

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

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

    exit;

} # end of selectGroup
