#! /usr/bin/perl
#  Copyright 2001-2023 Leslie Richardson
#  This file is part of Open Admin for Schools.

# Check the school marks from previous years and then compare the
# marks with student values stored in the sasked completed marks table
# (which is assumed to be up to date.

# Check for Missing Marks when entered into MSS compared to the values in OA.

my $self = 'mssCheckMarks.pl';

use DBI;
use CGI;
use Time::JulianDay;


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


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

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



# HTML Header
my $title = 'Compare Marks in MSS vs OA';
print qq{$doctype<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">\n};

print qq{$chartype</head><body style="margin:1em;">\n};
print qq{[ <a href="$homepage\">Main</a> |\n};
print qq{<a href="$exppage">Export</a> ]\n};
print qq{<h1>$title</h1>};

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

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


#-------------
sub checkTerm {
#-------------

    #foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    # passed mss end date and oa end term.
    # enddate, endterm
    

    # Get courses for this end term.
    my %courses;
    my $sth = $dbh->prepare("select description, subjsec, subjcode from subject where 
			    grade = 10 or grade = 11 or grade = 12 and endrptperiod = ?");
    $sth->execute( $arr{endterm} );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    while ( my ($desc,$subjsec,$subjcode) = $sth->fetchrow ) {
#	print "DESC:$desc SUB:$subjsec, $subjcode<br>\n";
	$courses{$subjcode}{$subjsec} = $desc;  # may be multiple sections of same course
    }

    
    my $sth1 = $dbh->prepare("select lastname, firstname, studnum from studentall where mssid = ?");
    my $sth2 = $dbh->prepare("select a1 from eval where subjcode = ? and studnum = ? and term = ?");
    my $sth3 = $dbh->prepare("select title from sasked_courses where code = ?"); 
    
    my (%studid,%studname); # studid{mssid} = studnum;
    my $sth = $dbh->prepare("select * from mss_currcourse where date = ? order by mssid");
    $sth->execute( $arr{enddate} );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

    print qq{<h3>MSS End date:$arr{enddate} / OA End Term $arr{endterm}</h3>\n};

    print qq{<table cellspacing="0" border="1" cellpadding="3" style="margin:1em;">\n};
    print qq{<caption style="font-weight:bold;">Marks in Red are Errors</caption>\n};
    print qq{<tr><th>Name</th><th>Course</th><th>Marks</th></tr>\n};

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

	# Get the student
	if ( not $studid{$r{mssid}} ) { # get student info
	    $sth1->execute( $r{mssid} );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my ($ln,$fn,$studnum) = $sth1->fetchrow;
#	    print "HERE:$r{mssid} $ln $fn $studnum<br>\n";	    
	    if ( not $ln ) { # not found
		print $ln = qq{Not Found};
	    } else { # ok
		$studid{ $r{mssid} } = $studnum;
		$studname{$studnum} = qq{<b>$ln</b>, $fn};
	    }
	}
	my $studnum = $studid{ $r{mssid} };
#	print qq{SN:$studnum Name:$studname{$studnum}<br>\n};
	
	# Get his/her OA Mark
	# loop over any sections of the same course
	my ($mark, $subjsec);
	foreach my $subjsec ( %{ $courses{$r{coursecode}} } ) {

	    # subjcode, studnum, term
	    $sth2->execute( $subjsec, $studnum, $arr{endterm} );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $tempmark = $sth2->fetchrow;
	    if ( $tempmark ) {
		$mark = $tempmark;
	    }
	}

	# Get Course Description from sasked_courses;
	$sth3->execute( $r{coursecode} );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $coursedesc = $sth3->fetchrow;
	
	print qq{<tr><td>$studname{$studnum}</td><td>$coursedesc ($r{coursecode})</td> };
	my $color = 'black';
	if ( $mark !~ m/N.*/ ) {
	    if ( $r{mark} != $mark ) {
		$color = 'red';
	    }
	}
	print qq{<td><span style="color:$color;">MSS:$r{mark} OA:$mark</span></td></tr>\n};

    }

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

    exit;


} # end of checkTerm



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

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

    # Start the Form
    print qq{<form action="$self" method="post" style="display:inline;">\n};
    print qq{<input type="hidden" name="page" value="1">\n};

    
    print qq{<table cellspacing="0" border="1" cellpadding="3" style="margin:1em;">\n};
    print qq{<tr><th>Select</th><th></th></tr>\n};
 
    # Get Ending Dates in mss_currcourse table. Show last 8 or so.
    my $sth = $dbh->prepare("select distinct date from mss_currcourse order by date desc");
    $sth->execute;
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

    my $count;
    print qq{<tr><td>MSS Course End Date</td><td><select name="enddate"><option value=""></option>};
    while ( my $enddate = $sth->fetchrow ) {
	print qq{<option>$enddate</option>};
	$count++;
	if ( $count > 8 ) { last; }
    }
    print qq{</select></td></tr>\n};

    
    # Get the End terms in OA
    my $sth = $dbh->prepare("select distinct endrptperiod  from subject 
      where grade = 10 or grade = 11 or grade = 12 
      and endrptperiod != '' and endrptperiod is not NULL order by endrptperiod");
    $sth->execute;
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

    print qq{<tr><td>OA Ending Term</td><td><select name="endterm"><option value=""></option>};
    while ( my $endterm = $sth->fetchrow ) {
	print qq{<option>$endterm</option>};
    }
    print qq{</select></td></tr>\n};

    print qq{</table>\n};
    print qq{<div style="margin-left:1em;"><input type="submit" value="Check"></div></form>};

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

    exit;

}

