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

#  This file is part of Open Admin for Schools.

# Attcheck.pl - check attendance table (attend) for duplicate
# records, blank dates, and blank reasons. Link to something that
# will allow it to be fixed...

my %lex = ('Check' => 'Check',
	   'Main' => 'Main',
	   'Attendance' => 'Attendance',
	   'Duplicate Records' => 'Duplicate Records',
	   'Name' => 'Name',
	   'Date' => 'Date',
	   'Period' => 'Period',
	   'Reason' => 'Reason',
	   'Delete' => 'Delete',
	   'Empty' => 'Empty',
	   'Reasons' => 'Reasons',
	   'Edit' => 'Edit',
	   'Not Found' => 'Not Found',
	   'Back to Top' => 'Back to Top',
	   'Dates' => 'Dates',
	   'Record(s) Updated' => 'Record(s) Updated',
	   'Record(s) Deleted' => 'Record(s) Deleted',
	   'Subject' => 'Subject',
	   'Update' => 'Update',
	   'Error' => 'Error',

	   );

use DBI;
use CGI;

my $self = 'attcheck.pl';

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

# Load libDate library to parse the grades field of dates_periods
eval require "../../lib/libDate.pl";
if ( $@ ) {
    print $lex{Error}. ": $@<br>\n";
    die $lex{Error}. ": $@\n";
}


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

$dsn = "DBI:$dbtype:dbname=$dbase";
$dbh = DBI->connect($dsn,$user,$password);
$dbh->{mysql_enable_utf8} = 1;

# Page Head Section.
my $title = "$lex{Attendance} $lex{Check}";
print qq{$doctype\n<html><head><title>$title</title>
<link rel="stylesheet" href="$css" type="text/css">
$chartype\n</head><body>\n};

print qq{<a name="top"></a>[ <a href="$homepage">$lex{Main}</a> | \n};
print qq{<a href="$attpage">$lex{Attendance}</a> ]\n};

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


if ($arr{updateflag} == 2){  # We want to delete duplicates...
    delDuplicates();
}

if ($arr{updateflag} == 3){  # We want to edit reasons
    edReasons();
}

if ($arr{updateflag} == 4){  # We want to edit dates
    edDates();
}

if ($arr{updateflag} == 5){  # We want to update reasons
    updateReasons();
}

if ($arr{updateflag} == 6){  # We want to update dates
    updateDates();
}

if ($arr{updateflag} == 7){  # Delete School closed errors
    delClosureErrors();
}



# First... Find Duplicates.
$sth = $dbh->prepare("select attid, absdate, studentid, subjsec, period, reason
		     from attend order by absdate, studentid, period");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die;}

$firstflag = 1;

while ( my ($id, $absdate, $studnum, $subjsec, $period,
	    $reason) = $sth->fetchrow){
    
    if ($prevdate eq $absdate and 
	$prevstud == $studnum and 
	$prevper == $period){ # We have a duplicate

	$sth1 = $dbh->prepare("select lastname, firstname from studentall
           where studnum = '$studnum'");
	$sth1->execute;
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	my ($lastname, $firstname) = $sth1->fetchrow;
	if (not $lastname){ $lastname = 'Not Found';}

	if ($firstflag){
	    print qq{<h1>$lex{'Duplicate Records'}</h1>\n};
	    print qq{<form action="attcheck.pl" method="post">\n};
	    print qq{<input type="hidden" name="updateflag" value="2">\n};
	    print qq{<table border="1" cellpadding="3" cellspacing="0">\n};
	    print qq{<tr><td colspan="6" class="cn">};
	    print qq{<input type="submit" value="$lex{Delete} $lex{'Duplicate Records'}"></td>};
	    print qq{</tr>\n<tr><th>$lex{Name}</th><th>$lex{Date}</th><th>$lex{Period}</th>\n};
	    print qq{<th>$lex{Subject}</th><th>$lex{Reason}</th><th>$lex{Delete}</th></tr>\n};
	    $firstflag = 0;
	}
	
	# Print Previous record
	print qq{<tr style="background:#BCF"><td>$firstname $lastname };
	print qq{($studnum)</td><td>$prevdate</td>};
	print qq{<td>$prevper</td><td>$prevsub</td>\n};
	print qq{<td>$prevrsn</td><td></td></tr>\n};

        # Print Current Record
	print qq{<tr><td>$firstname $lastname ($studnum)</td><td>$absdate</td>};
	print qq{<td>$period</td><td>$subjsec</td><td>$reason</td>\n};
	print qq{<td><input type="checkbox" name="$id" value="1" checked="checked">};
	print qq{</tr>\n};

    }
    $prevdate = $absdate;
    $prevstud = $studnum;
    $prevper = $period;
    $prevsub = $subjsec;
    $prevrsn = $reason;

}


if ( $firstflag ){ 
    print qq{<b style="font-size: 150%">$lex{'Duplicate Records'} - $lex{'Not Found'}</b>\n};
} else {
    print qq{<tr><td colspan="6" class="cn">};
    print qq{<input type="submit" value="$lex{Delete} $lex{'Duplicate Records'}"></td></tr>\n};
    print qq{</table></form>\n};
}

print qq{<p>[ <a href="#top">Back to Top</a> ]</p>\n};

#============


# Find School Closure Errors (Attendance done while school closed
my (%closed, %closedGR, %closedHR);

# Load Closure Data
my $sth = $dbh->prepare("select * from dates");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die;}
while ( my $ref = $sth->fetchrow_hashref ) {
    $closed{$ref->{date}}{$ref->{dayfraction}} = 1;
}

#foreach my $date ( sort keys %closed ) {
#    foreach my $frac ( sort keys %{ $closed{$date} } ) {
#	print qq{<div>Date:$date Fraction:$frac</div>\n};
#    }
#}

my $sth = $dbh->prepare("select * from dates_homeroom");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die;}
while ( my $ref = $sth->fetchrow_hashref ) {
    my %d = %$ref;
    $closedHR{ $d{date} }{ $d{homeroom} }{ $d{period} } = 1;
}

# CHECK %closedHR
=head
foreach my $date ( sort keys %closedHR ) {
    foreach my $homeroom ( sort keys %{ $closedHR{$date} } ) {
	foreach my $period ( sort keys %{ $closedHR{$date}{$homeroom} } ) {
	    print qq{<div>Date:$date Homeroom:$homeroom Period:$period</div>\n};
	}
    }
}
=cut


my $sth = $dbh->prepare("select * from dates_periods");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die;}
while ( my $ref = $sth->fetchrow_hashref ) {
    my %d = %$ref;
    my $date = $d{date};
    # Extract individual grades
    my $dref = parseGradesPeriod( $date, $dbh);
    my %r = %$dref;
    
    foreach my $gr ( keys %r ) {
	foreach my $per ( keys %{ $r{$gr} } ) {
	    $closedGR{ $d{date} }{ $gr }{ $per }  = 1;  # date,grade,period
#	    print qq{Date:$d{date} GR:$gr PER:$per<br>\n};
	}
    }
}

# CHECK %closedGR 
#foreach my $date ( sort keys %closedGR ) {
#    foreach my $grade ( sort keys %{ $closedGR{$date} } ) {
#	foreach my $period ( sort keys %{ $closedGR{$date}{$grade} } ) {
#	    print qq{<div>Date:$date Grade:$grade Period:$period</div>\n};
#	}
#    }
#}



#use Data::Dumper;
#print Dumper 

# Now loop through all records looking for errors.

# Get student HR and GR.
my $sth2 = $dbh->prepare("select grade, homeroom from studentall where studnum = ?");


my $sth = $dbh->prepare("select * from attend order by absdate");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die;}

$firstflag = 1;
my $count = 0;

while ( my $ref = $sth->fetchrow_hashref ){
    my %r = %$ref;
   
    my $deleteflag;

    # Check for a full day closure
    if ( $closed{ $r{absdate} }{'1.000'} ) { # day fully closed.
	$deleteflag = 1;
    }
    
    # Get Student Grade and Homeroom 
    $sth2->execute( $r{studentid} ); # studnum
    if ($DBI::errstr){ print $DBI::errstr; die;}
    my ( $grade, $homeroom ) = $sth2->fetchrow;

    
    # Check for Grade/Homeroom Closure
    if ( $closedGR{ $r{absdate} }{ $grade }{ $r{period} } ) { # closed this period
	$deleteflag = 1;
    }
    
    if ( $closedHR{ $r{absdate} }{ $homeroom }{ $r{period} } ) { # closed this period
	$deleteflag = 1;
    }
    
       
    # Print Current Record
    if ( $deleteflag ) {

	if ($firstflag){
	    print qq{<h1>School Closed Attendance Error</h1>\n};
	    print qq{<form action="attcheck.pl" method="post">\n};
	    print qq{<input type="hidden" name="updateflag" value="7">\n};
	    
	    print qq{<table border="1" cellpadding="3" cellspacing="0">\n};
	    print qq{<tr><td colspan="6" class="cn">};
	    print qq{<input type="submit" value="$lex{Delete} School Closed Errors"></td>};
	    print qq{</tr>\n<tr><th>$lex{Name}</th><th>$lex{Date}</th><th>$lex{Period}</th>\n};
	    print qq{<th>$lex{Subject}</th><th>$lex{Reason}</th><th>$lex{Delete}</th></tr>\n};
	    $firstflag = 0;
	}


	# Student Name
	$sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");
	$sth1->execute( $r{studentid} );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	my ($lastname, $firstname) = $sth1->fetchrow;
	if (not $lastname){ $lastname = 'Not Found';}
	
	print qq{<tr><td>$firstname $lastname ($r{studentid})</td><td>$r{absdate}</td>};
	print qq{<td class="cn">$r{period}</td><td>$r{subjsec}</td><td>$r{reason}</td>\n};
	print qq{<td><input type="checkbox" name="$r{attid}" value="1" checked="checked">};
	print qq{</tr>\n};

	$count++;
    }
    
}


if ( $firstflag ){ 
    print qq{<b style="font-size: 150%">School Closed Attendance Errors - $lex{'Not Found'}</b>\n};
    
} else {
    print qq{<tr><td colspan="6" class="cn">};
    print qq{<input type="submit" value="$lex{Delete} School Closed Errors"></td></tr>\n};
    print qq{</table></form>\n};
    print qq{<div>$count Records to delete</div>\n};
}

print qq{<p>[ <a href="#top">Back to Top</a> ]</p>\n};

# End of Find Closure Errors
#==============


# Now look for blank reasons....

$sth = $dbh->prepare("select * from attend where reason is null or reason = ''
		     order by absdate, period");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
my $firstblankflag = 1;

while (my ($id, $studnum,$absdate,$reason,$period,$subjsec) = $sth->fetchrow){
    if ($firstblankflag){ # write the table head section...
	print qq{<h1>$lex{Empty} $lex{Reasons}</h1>\n};
	print qq{<form action="attcheck.pl" method="post">\n};
	print qq{<input type="hidden" name="updateflag" value="3">\n};
	print qq{<table border="1" cellpadding="3" cellspacing="0">\n};
	print qq{<tr><td colspan="6" class="cn">};
	print qq{<input type="submit" value="$lex{Edit} $lex{Reasons}"></td></tr>\n};
	print qq{<tr><th>$lex{Name}</th><th>$lex{Date}</th><th>$lex{Period}</th>\n};
	print qq{<th>$lex{Subject}</th><th>$lex{Reason}</th><th>$lex{Edit}</th></tr>\n};
	$firstblankflag = 0;
    }

    $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");
    $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>};}

    # Print Current Record
    print qq{<tr><td>$firstname $lastname ($studnum)</td><td>$absdate</td>};
    print qq{<td>$period</td><td>$subjsec</td><td>$reason</td>\n};
    print qq{<td><input type="checkbox" name="$id" value="1">};
    print qq{</tr>\n};

}

if ($firstblankflag){
   print qq{<b style="font-size: 150%">$lex{Empty} $lex{Reasons} - $lex{'Not Found'}</b>\n};

} else {
    print qq{<tr><td colspan="6" class="cn">};
    print qq{<input type="submit" value="$lex{Edit} $lex{Reasons}"></td></tr>\n};
    print qq{</table></form>\n};
}
print qq{<p>[ <a href="\#top">$lex{'Back to Top'}</a> ]</p>\n};

#----------------------
# Now find blank dates.
#----------------------

$sth = $dbh->prepare("select * from attend where absdate = '' order by absdate, period");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
$firstflag = 1;

while (my ($id, $studnum,$absdate,$reason,$period,$subjsec) = $sth->fetchrow){

    if ($firstflag){ # write the table head section...
	    print qq{<h1>$lex{Empty} $lex{Dates}</h1>\n};
	    print qq{<form action="attcheck.pl" method="post">\n};
	    print qq{<input type="hidden" name="updateflag" value="4">\n};
	    print qq{<table border="1" cellpadding="3" cellspacing="0">\n};
	    print qq{<tr><td colspan="6" class="cn">};
	    print qq{<input type="submit" value="$lex{Edit} $lex{Dates}"></td></tr>\n};

	    print qq{<tr><th>$lex{Name}</th><th>$lex{Date}</th><th>$lex{Period}</th>\n};
	    print qq{<th>$lex{'Subject'}</th><th>$lex{Reason}</th><th>$lex{Edit}</th></tr>\n};
	    $firstflag = 0;
    }


    # Get Name
    $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");
    $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>}; }


    # Get Subject
    $sth1 = $dbh->prepare("select description from subject
      where subjsec = '$subjsec'");
    $sth1->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
    my $desc = $sth1->fetchrow;
    if (not $desc){ $desc = qq{<span style="color:red;">$lex{'Not Found'}</span>};}

    # Print Current Record
    print qq{<tr><td>$firstname $lastname ($studnum)</td><td>$absdate</td>};
    print qq{<td>$period</td><td>$desc ($subjsec)</td><td>$reason</td>\n};
    print qq{<td><input type="checkbox" name="$id" value="1">};
    print qq{</tr>\n};

}

if ($firstflag){ 
    print qq{<b style="font-size: 150%">$lex{Empty} $lex{Dates} - $lex{'Not Found'}</b>\n};
} else {
    print qq{<tr><td colspan="6" class="cn">};
    print qq{<input type="submit" value="$lex{Edit} $lex{Dates}"></td></tr>\n};
    print qq{</table></form>\n};
}

print qq{<p>[ <a href="#top">$lex{'Back to Top'}</a> ]</p>\n};
print qq{</body></html>\n};


# Now the functions



#-------------------
sub delClosureErrors { # Delete Closure Errors Attendance Records
#-------------------

    # foreach my $key ( sort keys %arr ) { print qq{<div>K:$key VAL:$arr{$key}</div>\n}; }
    
    delete $arr{updateflag};

    my $sth = $dbh->prepare("delete from attend where attid = ?");
    foreach my $key (keys %arr){
	$sth->execute( $key );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
    }

    print qq{<h1>$lex{'Record(s) Deleted'}</h1>\n};

    print qq{<p>[ <a href="$attpage">$lex{Attendance}</a> | };
    print qq{<a href = "$self">$title</a> ]</p>\n};
    print qq{</body></html>\n};

    exit;

}



#----------------
sub delDuplicates { # Delete duplicate attendance records
#----------------

    delete $arr{updateflag};

    my $sth = $dbh->prepare("delete from attend where attid = ?");
    foreach my $key (keys %arr){
	$sth->execute( $key );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
    }

    print qq{<h1>$lex{'Record(s) Deleted'}</h1>\n};

    print qq{<p>[ <a href="$attpage">$lex{Attendance}</a> | };
    print qq{<a href = "$self">$title</a> ]</p>\n};
    print qq{</body></html>\n};

    exit;

}



#------------
sub edReasons { # edit reasons
#------------

    delete $arr{updateflag};

    print qq{<h1>$lex{Edit} $lex{Reasons}</h1>\n};

    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="updateflag" value="5">\n};
    print qq{<table border="1" cellpadding="3" cellspacing="0">\n};
    print qq{<tr><td colspan="5" class="cn">};
    print qq{<input type="submit" value="$lex{Update}"></td></tr>};

    print qq{</tr>\n<tr><th>$lex{Name}</th><th>$lex{Date}</th><th>$lex{Period}</th>\n};
    print qq{<th>$lex{Subject}</th><th>$lex{Reason}</th></tr>\n};


    foreach my $key (keys %arr){

	my $sth = $dbh->prepare("select * from attend where attid = ?");
	$sth->execute( $key );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	my ($id, $studnum,$absdate,$reason,$period,$subjsec) = $sth->fetchrow;

	# Get Name
	$sth1 = $dbh->prepare("select lastname, firstname from studentall
         where studnum = ?");
	$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>}; }


	# Get Course, if present...
	if ( $subjsec ) {
	    $sth1 = $dbh->prepare("select description from subject where subjsec = ?");
	    $sth1->execute( $subjsec );
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	    my $desc = $sth1->fetchrow;
	    if ( not $desc ){ $desc = qq{<span style="color:red;">$lex{'Not Found'}</span>};}
	    $desc .= qq{ ($subjsec)};
	}

	# Print Current Record
	print qq{<tr><td>$firstname $lastname ($studnum)</td><td>$absdate</td>};
	print qq{<td>$period</td><td>$desc</td><td>\n};
	print qq{<select name="$id"><option></option>\n};
	foreach my $rsn (@attend){
	    print qq{<option>$rsn</option>\n};
	}
	print qq{</select></td></tr>\n};

    }

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

    exit;
}


#----------------
sub updateReasons { # Write Reasons
#----------------

    delete $arr{updateflag};

    foreach my $key (keys %arr){
	#print qq{K:$key V:$arr{$key}<br>\n};
	my $sth = $dbh->prepare("update attend set reason = ? where attid = ?");
	$sth->execute( $arr{$key}, $key );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
    }

    print qq{<h1>$lex{'Record(s) Updated'}</h1>\n};
    print qq{<p>[ <a href="$attpage">$lex{Attendance}</a> | };
    print qq{<a href = "$self">$title</a> ]</p>\n};
    print qq{</body></html>\n};

    exit;
}


#------------
sub edDates { # edit dates
#------------

    delete $arr{updateflag};

    print qq{<h1>$lex{Edit} $lex{Dates}</h1>\n};
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="updateflag" value="6">\n};

    print qq{<table border="1" cellpadding="3" cellspacing="0">\n};
    print qq{<tr><td colspan="5" class="cn">};

    print qq{<input type="submit" value="$lex{Update}"></td>};
    print qq{</tr>\n<tr><th>$lex{Name}</th><th>$lex{Date}</th><th>$lex{Period}</th>\n};
    print qq{<th>$lex{Subject}</th><th>$lex{Reason}</th></tr>\n};

    my $sth = $dbh->prepare("select * from attend where attid = ?");
    my $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");
    my $sth2 = $dbh->prepare("select description from subject where subjsec = ?");

    foreach my $key ( keys %arr ) {


	$sth->execute( $key );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	my ($id, $studnum,$absdate,$reason,$period,$subjsec) = $sth->fetchrow;

	# Get 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>}; }

	# Get Subject
	$sth2->execute( $subjsec );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	my $desc = $sth2->fetchrow;
	if (not $desc){ $desc = qq{<span style="color:red;">$lex{'Not Found'}</span>};}

	# Print Current Record
	print qq{<tr><td>$firstname $lastname ($studnum)</td><td>};
	print qq{<input type="text" name="$id" size="12" };
	print qq{value="$absdate"></td>};
	print qq{<td>$period</td><td>$desc ($subjsec)</td><td>$reason</td>\n};
	print qq{</tr>\n};

    }

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

}


#----------------
sub updateDates { # Write Dates
#----------------

    delete $arr{updateflag};

    my $sth = $dbh->prepare("update attend set absdate = ? where attid = ?");

    foreach my $key (keys %arr){
	$sth->execute( $arr{$key}, $key );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
    }

    print qq{<h1>$lex{'Record(s) Updated'}</h1>\n};
    print qq{<p>[ <a href="$attpage">$lex{Attendance}</a> | };
    print qq{<a href = "attcheck.pl">$title</a> ]</p>\n};
    print qq{</body></html>\n};

}
