#! /usr/bin/perl
#  Copyright 2001-2018 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 in current school year.

my $self = 'checkcurrmarks_local.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 = 'Check for Missing SDS Marks';
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};
print qq{[ <a href=\"$homepage\">Main</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}; }

    require "$globdir/global.conf" or die "Cannot open global.conf!\n";

    # open database connection to central
    my $db = 'central';
    my $dsn1 = "DBI:$dbtype:dbname=$db";
    my $dbh1 = DBI->connect($dsn1,$guser,$gpassword);



    my $schoolstartjd = julian_day( split('-', $schoolstart));

    my $track = $g_MTrackTermType{12}; # track of grade 12;
    my $enddate = $g_MTrackTerm{$track}{$arr{endterm}}{end};

    print qq{<h3>Term End Date:$enddate</h3>\n};

    print qq{<div>We download the completed course marks from Sask Ed
    every night.<br>The results below compare the local OA marks against
    what we have downloaded last night from Sask Ed (SDS).</div><p>If you have
    updated Sask Ed with marks today, they will not be shown
    below. They will be updated tonight.<br> <b>OR</b> you can update them using the button below.</div>\n};

    print qq{<form action="updatecompletedcourses.pl" method="post" style="display:inline;">\n};
    print qq{<input type="submit"  value="Update Completed Courses from Sask Ed">\n};
    print qq{</form><p></p>\n};

    

    # get courses in passed term for 10-12 courses.
    my %marks; # $marks{subjsec}{provnum} = mark.
    my %teacher; # teacher{subjsec} = Name;
    
    my $sth = $dbh->prepare("select subjsec, teacher from subject where ( grade = 10 or grade = 11 or grade = 12 )
      and endrptperiod = ? order by grade, description");

    my $sth1 = $dbh->prepare("select studnum, a1 from eval where subjcode = ? and term = ?"); 
    my $sth2 = $dbh->prepare("select count(distinct studnum) from eval where subjcode = ?");
    my $sth3 = $dbh->prepare("select lastname, firstname from staff where userid = ?");
    
    my @courses;
    $sth->execute( $arr{endterm} );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    while ( my ($subjsec, $userid) = $sth->fetchrow ) {

	# Get Teacher Name
	$sth3->execute( $userid );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ($lastname, $firstname)  = $sth3->fetchrow;
	if ( not $lastname ) { $lastname = qq{Teacher Not Found ($userid)}; }
	$teacher{$subjsec} = qq{<b>$lastname</b>, $firstname};
	
	# Get Student Count
	$sth2->execute( $subjsec );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $studcount  = $sth2->fetchrow;
#	print "Course:$subjsec Count:$studcount<br>\n";
	if ( not $studcount ) { next; } # skip this subject, no enrollments.

	push @courses, $subjsec;

	# Get students; studnum and mark for this course.
	$sth1->execute( $subjsec, $arr{endterm} );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my ($studnum, $mark) = $sth1->fetchrow ) {
#	    print "SN:$studnum Mark:$mark<br>\n";
	    #$mark =~ s/\D+//g; # strip any non numeric values
	    # if ( not $mark ) { next; }  # skip any blank marks # No, we want to see all enrolled.
	    $marks{$subjsec}{$studnum} = $mark;
	}
    }


    # Prep for loop building hash of subjects and marks
    my $sth = $dbh->prepare("select * from subject where subjsec = ?");
    my $sth1 = $dbh->prepare("select count(distinct studnum) from eval where subjcode = ?");

    print qq{<h3>Current OA Subjects ending in term $arr{endterm} ($enddate)</h3>\n};
    my $first = 1;
    foreach my $subjsec ( @courses ) {
	if ( $first ) {
	    print qq{<table cellspacing="0" cellpadding="3" border="1">\n};
	    print qq{<tr><th>Course Name</th><th>Teacher</th><th>Course<br>Section</th>};
	    print qq{<th>Grade</th><th>Enrollment</th></tr>\n};
	    $first = 0;
	}

	# Get Course Info
	$sth->execute( $subjsec );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	$cref  = $sth->fetchrow_hashref;
	%c = %$cref;

	# Get Student Count
	$sth1->execute( $subjsec );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $studcount  = $sth1->fetchrow;

	print qq{<tr><td class="bla">$c{description}</td><td>$teacher{$subjsec}</td><td>$subjsec</td>};
	print qq{<td>$c{grade}</td><td>$studcount</td></tr>\n};

    }

    if ( not $first ) {
	print qq{</table><p></p>\n};
    } else {
	print qq{<h3>No Courses Found</h3>\n};
    }



    # Test
=head
    foreach my $subjsec ( sort keys %marks ) {
	my ($courseid, $section) = split('-', $subjsec);
	foreach my $studnum ( sort keys %{ $marks{$subjsec} } ) {
	    print "Sub:$subjsec SN:$studnum Mark:$marks{$subjsec}{$studnum}<br>\n";
	}
    }
=cut


    # Now check, using this %marks hash for all matching marks in the completed courses.
    my $sth = $dbh->prepare("select * from sasked_completedcourses where courseid = ? and provnum = ?");
    my $sth1 = $dbh->prepare("select provnum, firstname, lastname, grade from studentall where studnum = ?");

    my %coursename;


    my $first = 1;
    foreach my $subjsec ( @courses ) {  #sort keys %marks ) {

	my ($courseid, $section) = split('-', $subjsec);

	# Load course name if not in hash
	if ( not $coursename{$courseid} ) {
	    my $sth2 = $dbh1->prepare("select title from sasked_courses where code = ?");
	    $sth2->execute($courseid);
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    $coursename{$courseid}  = $sth2->fetchrow;
	}


	if ( not $first ) { # print out course info
	    print qq{<tr><th colspan="2">$coursename{$courseid} ($subjsec) - $teacher{$subjsec}</th>};
	    print qq{<th>OA<br>Mark</th><th>SDS<br>Mark</th></tr>\n};
	}


	foreach my $studnum ( sort keys %{ $marks{$subjsec} } ) {

	    my $mark = $marks{$subjsec}{$studnum}; # OA Mark
	    
	    if ( $first ) {
		print qq{<table cellspacing="0" cellpadding="3" border="1">\n};
		print qq{<caption ><span style="color:red;">Red SDS Mark</span> = Mismatch with OA Mark };
		print qq{<span style="color:red;">Red Date</span> = Different Course End Date</caption>\n};

		print qq{<tr><th colspan="2">$coursename{$courseid} ($subjsec) - $teacher{$subjsec}</th>};
		print qq{<th>OA<br>Mark</th><th>SDS<br>Mark</th></tr>\n};
		$first = 0;
	    }


	    # Get their provincial number. If missing show error and skip
	    $sth1->execute($studnum); # note courseid (8017), Not subjsec (8017-1)
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my ($provnum, $firstname, $lastname, $grade) = $sth1->fetchrow;
	    if ( not $provnum ) {
		print qq{<h3>Missing Provincial Number for $firstname $lastname - Grade $grade</h3>\n};
		next;
	    }



	    # Load the SDS Course Mark, this student, this course.
	    $sth->execute( $courseid, $provnum ); # note courseid (8017), Not subjsec (8017-1)
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $ref = $sth->fetchall_hashref(id);
	    my %data = %$ref;

	    # use Data::Dumper;
	    # print Dumper $ref;

	    if ( not %data ) { # no record 
		print qq{<tr><td>$firstname $lastname ($studnum)</td>};
		print qq{<td>$provnum</td><td>$mark</td>};
		print qq{<td style="color:red;font-weight:bold;">No SDS Mark</td></tr>\n};
		next;
	    }
		
	    foreach my $id ( keys %data ) {
		# print "ID:$id - V:$data{$id} - $data{$id}{courseid}<br>\n";
		    
		my %r = %{ $data{$id}};

		my $endjd = julian_day( split('-', $r{courseenddate} ));

		if ( $endjd < $schoolstartjd ) { next; } # skip

		if ( not $r{id} ) { # no record 
		    print qq{<tr style="background-color:#DDD;"><td>$firstname $lastname ($studnum)</td>};
		    print qq{<td colspan="3">No SDS Mark</td></tr>\n};

		} else {

		    my $markcolor = 'green';
		    if ( $r{finalmark} != $marks{$subjsec}{$studnum} ) { $markcolor = 'red'; }

		    my $datecolor = 'green';
		    if ( $r{courseenddate} ne $enddate ) { $datecolor = 'red'; }

		    print qq{<tr><td>$firstname $lastname ($studnum)</td>};
		    print qq{<td>$provnum</td>}; #<td title="$coursename{$courseid}">$subjsec</td>};
		    # print qq{<td style="color:$datecolor;">$r{courseenddate}</td>};

		    # the OA Mark
		    print qq{<td>$marks{$subjsec}{$studnum}</td>};

		    # the SDS mark.
		    print qq{<td style="color:$markcolor;">$r{finalmark}</td>};

		    print qq{</tr>\n};
		
		}
	    } # end of id loop for student-course, multiple records

	} # provnum loop
    } # end of subjsec loop

    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">\n};
    print qq{<tr><th>Select End Term</th></tr>\n};
 
    # Get Ending Terms
    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>Term <select name="endterm"><option></option>};
    while ( my $endterm = $sth->fetchrow ) {
	print qq{<option>$endterm</option>};
    }
    print qq{</select></td></tr>\n};
    print qq{</table>\n};


    print qq{<input type="submit" value="Check"></form>};

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

    exit;

}

