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

# Check the school marks from current or 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)
# Still using the completed marks table, and mss export imported into there.


my %lex = ('Main' => 'Main',
	   'Continue' => 'Continue',
	   'Error' => 'Error',
	   'Select' => 'Select',
	   'School Year' => 'School Year',

    );


my $self = 'checkMarksMss.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 ); 


# Possibly Load Remote database, remote values in configuration system
my $dbhr;
if ( $arr{db} and $arr{db} ne 'curr' ) { # from start page
    my $db = $arr{db};
    my $dsn = "DBI:mysql:database=$db;host=$remotehost";
    $dbhr = DBI->connect($dsn,$remoteuser,$remotepassword);
}

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

if ( not defined $dbhr ) { $dbhr = $dbh; } # we are using the current database for marks.

# HTML Header
my $title = 'Check for Missing MSS Marks - Current/Previous Years';
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> | <a href="$reppage">Report Card</a> ]\n};
print qq{<h1>$title</h1>};

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

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



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

    # Get remote databases
    my $remotedbase = 'information_schema';
    my $dsnr = "DBI:mysql:database=$remotedbase;host=$remotehost";
    my $dbhr = DBI->connect($dsnr,$remoteuser,$remotepassword);


    my $sth = $dbhr->prepare("select distinct table_schema from TABLES order by table_schema");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    my %remotedb;


    while ( my $db = $sth->fetchrow ) {
	if ( $db eq 'mysql' or $db eq 'information_schema' ) { next; }

#	print qq{DB:$db<br>\n};
	if ( $db =~ m/$dbase/ ) {
#	    print qq{Match! $dbase - $db<br>\n};
	    $remotedb{$db} = 1;
	}

    }

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

    print qq{<table cellpadding="3" cellspacing="0" border="0" };
    print qq{style="padding:0.5em;border:1px solid gray;">\n};

    print qq{<tr><td class="bla">$lex{Select} $lex{'School Year'}</td><td></td></tr>\n};
    foreach my $db ( sort keys %remotedb ) {

	my $temp = $db;
	$temp =~ s/$dbase//; # strip database name;
	my $year = $temp - 1; # previous year;
	$year = qq{$year-$temp}; # now in 2018-2019 format
	
	print qq{<tr><td class="la"><input type="radio" name="db" value="$db"> $year ($db)</td></tr>\n};
    }

    # Current School Year
    print qq{<tr><td class="la"><input type="radio" name="db" value="current">Current Year</td></tr>\n};
    
    
    print qq{<tr><td colspan="2"><hr></td></tr>\n};

    # Check next Page
#    print qq{<tr><td class="bla"><input type="checkbox" name="checknextpage" value="1"> };
#    print qq{$lex{'Check Next Page'} (Grades)</td></tr>\n};

    print qq{<tr><td>Term <select name="endterm"><option value=""></option>};
    for $trm (1..8) { 
	print qq{<option>$trm</option>};
    }
    print qq{</select></td></tr>\n};

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

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

    exit;

} # end of New Start Page




#-------------
sub checkTerm {  # Check marks in current term agains MSS/Aspen
#-------------

    #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);
#    $dbh->{mysql_enable_utf8} = 1;

    if ( not $arr{db} ) {
	print qq{<h3>No Database Selected!</h3>\n};
	print qq{</body></html>\n};
	exit;
    }


    # Load Main admin configuration ('admin') from remote/local/database
    my $sth = $dbhr->prepare("select id, datavalue from conf_system where filename = 'admin'");
    $sth->execute;
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    while (	my ($id, $datavalue) = $sth->fetchrow ) {
	eval $datavalue;
	if ( $@ ) {
	    print "$lex{Error}: $@<br>\n";
	    die "$lex{Error}: $@\n";
	}
    }
    

    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 Aspen (MSS)
		 every night.<br>The results below compare the local OA marks against
		 what we have downloaded last night from MSS. </div><p>If you have
		 updated MSS/SDS with marks today, they will not be shown
		 below. They will be updated tonight.</p>\n};

    
    # get courses in the passed term for 10-12 courses.
    my %marks; # $marks{subjsec}{provnum} = mark.
    my %teacher; # teacher{subjsec} = Name;
    
    my $sth = $dbhr->prepare("select subjsec, teacher from subject 
			     where ( grade = 10 or grade = 11 or grade = 12 )
			     and endrptperiod = ? order by grade, description");
    
    my $sth1 = $dbhr->prepare("select studnum, a1 from eval where subjcode = ? and term = ?"); 
    my $sth2 = $dbhr->prepare("select count(distinct studnum) from eval where subjcode = ?");
    my $sth3 = $dbhr->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{$firstname $lastname};
	
	# 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;
	}
    }


    # Now get students sorted by name
    my %sortedmarks;
    # Sort students by name
    my $sth4 = $dbhr->prepare("select lastname, firstname,grade from studentall where studnum = ?");

    foreach my $crs ( keys %marks ) {
	# Get all students in this course.
	my %sort = ();
	my @sort = ();
	foreach my $studnum ( keys %{ $marks{$crs}} ) {
	    # Get name,grade
	    $sth4->execute( $studnum );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my ($ln,$fn,$gr) = $sth4->fetchrow;
	    $sort{"$ln$fn$studnum"} = $studnum;
	}
	foreach my $key ( sort keys %sort ) {
	    my $sn = $sort{$key};
	    push @sort, $sn;
	}
	$sortedmarks{$crs} = \@sort;
    }


    # Test for arrays of student numbers in sortedmarks;
=head    
    foreach my $subjsec ( sort keys %sortedmarks ) {
	my $ref = $sortedmarks{$subjsec};
	my @tmp = @$ref;
    	print "Subjsec:$subjsec REF:$ref STUDS:@tmp<br><br>\n";	
    }
=cut
    
    # 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;
    my ($currgrade, $prevgrade);
    
    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;

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

	if ( $currgrade ne $prevgrade ) {
	    print qq{<tr><td colspan="5" style="background-color:#DDD;font-size:120%;">};
	    print qq{Grade $currgrade</td></tr>\n};
	}

	
	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 = $dbhr->prepare("select * from sasked_completedcourses 
			     where courseid = ? and provnum = ?");
    my $sth1 = $dbhr->prepare("select provnum, firstname, lastname, grade from studentall 
			      where studnum = ?");
    my $sth3 = $dbhr->prepare("select exammix from subject where subjsec = ?");

    # Withdrawn?
    my $sth4 = $dbhr->prepare("select count(*) from studentwd where studnum = ?");
    

    my %coursename;

    my $first = 1;
    my $count = 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 = $dbhr->prepare("select title from sasked_courses where code = ?");
	    $sth2->execute($courseid);
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    $coursename{$courseid}  = $sth2->fetchrow;
	}

	# Load course exammix to see if blended dept/school mark
	$sth3->execute( $subjsec );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $exammix = $sth3->fetchrow;
	if ( not $exammix ) {
	    $exammix = 'School';
	}
	if ( $exammix eq 'Blended' ) {  # make it more visible
	    $exammix = qq{<span style="font-size:144%;color:red;font-weight:bold;">$exammix</span>\n};
	}
	
	if ( not $first ) { # print out course info

	    print qq{</table>\n};
	    print qq{<table cellspacing="0" cellpadding="3" border="1" style="margin:1em;">\n};
	    print qq{<tr><th colspan="2">$coursename{$courseid}<br>$subjsec/$exammix Mark - };
	    print qq{$teacher{$subjsec}</th>};
	    print qq{<th>OA<br>Mark</th><th>SDS<br>Mark</th></tr>\n};
	}


#	 my $ref = $sortedmarks{$subjsec};
#	 my @studs = @$ref;
	#	 foreach my $studnum ( @studs ) {
	# foreach my $subjsec ( sort keys %sortedmarks ) {
	my $ref = $sortedmarks{$subjsec};
	my @tmp = @$ref;
	foreach my $studnum ( @tmp ) {

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

		print qq{<tr><th colspan="2">$coursename{$courseid}<br>$subjsec / $exammix - };
		print qq{$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 - };
		print qq{Grade $grade ($studnum)</h3>\n};
		next;
	    }

	    # Check for Withdrawn
	    $sth4->execute($studnum); # note courseid (8017), Not subjsec (8017-1)
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $wdcount = $sth4->fetchrow;
	    if ( $wdcount ) {
		$wd = qq{<span style="color:red;font-weight:bold;font-size:120%">WD</span>};
	    }
	    

	    # 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>$wd <b>$lastname</b>, $firstname ($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;">};
		    print qq{<td>$wd <b>$lastname</b>, $firstname ($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'; }
		    if ( $markcolor eq 'red' and $exammix eq 'Blended' ) {
			$markcolor = 'DarkOrange';
		    }
		    
		    my $datecolor = 'green';
		    if ( $r{courseenddate} ne $enddate ) { $datecolor = 'red'; }

		    print qq{<tr><td>$wd <b>$lastname</b>, $firstname ($studnum)</td>};
		    print qq{<td>$provnum</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

	} # student loop

	$count++;
	if ( $count % 3 == 0 ) {
	    print qq{<div style="clear:left;"></div>\n};
	}

	
    } # end of subjsec loop

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

    exit;


} # end of checkTerm

