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

# Check the school course enrollments in OA VS MSS using mssCurrCourse table.

my $self = 'mssCheckCourses.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);
$dbh->{mysql_enable_utf8} = 1;

my @tim = localtime(time);
my $year = $tim[5] + 1900;
$tim[4]++;
for (0..4){if (length($tim[$_]) == 1){ $tim[$_] = '0'.$tim[$_];}}
my $currdate = "$year-$tim[4]-$tim[3]";
# my $currtime = "$tim[2]:$tim[1]:$tim[0]";


# HTML Header
my $title = 'Compare Course Enrollment/ 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>};

checkCourses(); 



#---------------
sub checkCourses {
#---------------

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

    
    # Get the current grade 12 term based on the current date;
    my $currterm; # set below
    my $track = $g_MTrackTermType{12};
    my $currjd = julian_day(split('-', $currdate));
    foreach my $term ( sort keys %{ $g_MTrackTerm{$track} } ) {
	my $startjd = julian_day( split('-', $g_MTrackTerm{$track}{$term}{'start'} ));
	my $endjd = julian_day(split('-', $g_MTrackTerm{$track}{$term}{'end'} ));
	if ( $currjd >= $startjd and $currjd <= $endjd ) { # we have the term.
	    $currterm = $term;
	    last;
	}
    };

    if ( $currterm ) {
	print qq{<h3>Current Grade 12 Term - $currterm</h3>\n};
    }


    # Get courses for this current term with enrollments.
    my %courses;

    # get Enrollment
    my $sth1 = $dbh->prepare("select count(*) from eval where subjcode = ?"); 

    my $sth = $dbh->prepare("select * from subject where 
			     grade = 10 or grade = 11 or grade = 12 and
			    endrptperiod >= ?  and startrptperiod <= ?
			    order by grade, startrptperiod, endrptperiod, subjsec");
    $sth->execute( $currterm, $currterm );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

    my $first = 1;
    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;
	
#	if ( $first ) {
#	    print qq{<table cellspacing="0" border="1" cellpadding="3" style="margin:1em;">\n};
#	    print qq{<tr><th>course</th><th>Subjsec (Code)</th><th>Grade</th><th>Terms</th>};
#	    print qq{<th>Enrol</th></tr>\n};
#	    $first = 0;
#	}

	# check for enrollment.
	my $trms =  $r{endrptperiod} - $r{startrptperiod} + 1;
	$sth1->execute( $r{subjsec} );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $count = $sth1->fetchrow;
	if ( not $count ) { next; }

	$courses{ $r{subjcode} }{ $r{subjsec} } = 1; # code first, subjsec second
	
#	my $enrol;
#	if ($trms > 0 ) {
#	    $enrol = $count / $trms;
#	}
	
#	print qq{<tr><td>$r{description}</td><td>$r{subjsec} ($r{subjcode})</td>};
#	print qq{<td>$r{grade}</td><td>$r{startrptperiod}-$r{endrptperiod}</td>};
#	print qq{<td>$enrol</td></tr>\n};

    }

    # Get Students enrolled in these current term courses.
    my %crsStud;
    my $sth = $dbh->prepare("select distinct studnum from eval where subjcode = ?");
    foreach my $subjcode ( sort keys %courses ) {
	foreach my $subjsec ( sort keys %{ $courses{$subjcode}} ) {
	    $sth->execute($subjsec); # Note: subjsec is really the value in the eval table
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    while ( my $studnum = $sth->fetchrow ) {
		$crsStud{$subjcode}{$studnum} = 1; # list to track who is enrolled in a subject.
	    }
	}
    }
    

    # find provnum, local num
    my $sth1 = $dbh->prepare("select studnum, provnum from studentall where mssid = ?");

    my $sth2 = $dbh->prepare("select id from eval where studnum = ? and subjcode = ?");

    # Get Student Name
    my $sth3 = $dbh->prepare("select lastname, firstname, grade from studentall
       	       		     where studnum = ?");
    # Get Course Name
    my $sth4 = $dbh->prepare("select title from sasked_courses where code = ?");
    
    
    # load each record from mss_currcourse
    my $sth = $dbh->prepare("select mssid, coursecode from mss_currcourse order by coursecode");
    $sth->execute;
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

    my $first = 1;

    while ( my ($mssid,$coursecode) = $sth->fetchrow ) {

	# Find the student provnum and studnum (local number)
	$sth1->execute($mssid);
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ($studnum,$provnum) = $sth1->fetchrow;
	if ( not $studnum ) {
	    print qq{<div>Student not found for MSSID: $mssid</div>\n};
	    next;
	}

	
	# Find his/her enrollment, loop over each course section if more than one.
	my $enrolflag;
	foreach my $subjsec ( sort keys %{ $courses{$coursecode} } ) {
	    $sth2->execute($studnum, $subjsec);
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $eid = $sth2->fetchrow_hashref;
	    if ( $eid ) {
		$enrolflag = 1;
		last;
	    }
	}

	if ( not $enrolflag ) { # no course found in OA.
	    if ( $first ) {
		print qq{<table cellspacing="0" border="1" cellpadding="3" };
		print qq{style="margin:1em;float:left;">\n};
		print qq{<caption style="font-weight:bold;font-size:120%;">};
		print qq{Missing Course Enrollments in OA</caption>\n};
		print qq{<tr><th>Course</th><th>Course Code</th><th>Student</th></tr>\n};
		$first = 0;
	    }

	    # Get Student Name
	    $sth3->execute($studnum);
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my ($lastname, $firstname,$grade) = $sth3->fetchrow;

	    # Get Course Name
	    $sth4->execute($coursecode);
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $title = $sth4->fetchrow;
	    
	    print qq{<tr><td>$title</td><td>$coursecode</td>};
	    print qq{<td>$firstname $lastname ($grade)</td></tr>\n};
	    
	} else {
	    # They are enrolled. Delete from enrolled students hash. Those left are not in MSS.
	    delete $crsStud{$coursecode}{$studnum};
	}

    } # end of student loop.

    if ( not $first ) {
	print qq{</table>\n};
	
    } else {
	print qq{<h3>No MSS Courses Found for Grades 10,11,12</h3>\n};
	print qq{</body></html>\n};
	exit;
    }

   
    # Print Students not in MSS but are in OA.
    if ( %crsStud ) { # setup table.
	print qq{<table cellspacing="0" border="1" cellpadding="3" style="margin:1em;float:left;">\n};
	print qq{<caption style="font-weight:bold;font-size:120%;">};
	print qq{Missing Course Enrollments in MSS</caption>\n};
	print qq{<tr><th>Course</th><th>Course Code</th><th>Student</th></tr>\n};
    }
	
    foreach my $subjcode ( sort keys %crsStud ) {
	foreach my $studnum ( sort keys %{ $crsStud{$subjcode}} ) {
	    
	    # Get Student Name
	    $sth3->execute($studnum);
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my ($lastname, $firstname,$grade) = $sth3->fetchrow;

	    # Get Course Name
	    $sth4->execute($subjcode);
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $title = $sth4->fetchrow;
	    
	    print qq{<tr><td>$title</td><td>$subjcode</td>};
	    print qq{<td>$firstname $lastname ($grade)</td></tr>\n};
	    
#	    print qq{<div>Not in MSS:$subjcode - $studnum</div>\n};
	}
    }

    if ( %crsStud ) {
	print qq{</table>\n};
    }
    
    print qq{</body></html>\n};

    exit;


} # end of checkCourses



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

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

    # Get the current grade 12 term based on the current date;
    my $currterm; # set below
    my $track = $g_MTrackTermType{12};
    my $currjd = julian_day(split('-', $currdate));
    foreach my $term ( sort keys %{ $g_MTrackTerm{$track} } ) {
	my $startjd = julian_day( split('-', $g_MTrackTerm{$track}{$term}{'start'} ));
	my $endjd = julian_day(split('-', $g_MTrackTerm{$track}{$term}{'end'} ));
	if ( $currjd >= $startjd and $currjd <= $endjd ) { # we have the term.
	    $currterm = $term;
	    last;
	}
    };

    
    print qq{<h3>Current Grade 12 Term - $currterm</h3>\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};
 
    # there are NO ending dates for current courses, it will just display current term values.
    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;

}

