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

#  This file is part of Open Admin for Schools.

#  Open Admin for Schools is free software; you can redistribute it 
#  and/or modify it under the terms of the GNU General Public License
#  as published by the Free Software Foundation; either version 2 of 
#  the License, or (at your option) any later version.

my %lex = ('Main' => 'Main',
	   'Error' => 'Error',
	   'Timetable' => 'Timetable',
	   'Grade' => 'Grade',
	   'Term' => 'Term',
	   'Terms' => 'Terms',

	   'Continue' => 'Continue',

	   );


use DBI;
use CGI;

my $self = "ttMissing.pl";

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

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


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


# print page header.
my $title = "Find Missing Timetables for Enrolled Courses"; # based on course enrollment.
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 style="margin: 0 2em;">\n};

print qq{[ <a href="$homepage">$lex{Main}</a> |\n};
print qq{<a href="$schpage">$lex{Timetable}</a> ]\n};
print qq{<h1>$title</h1>\n};


# Starting Page
if ( not $arr{page} ){
    showStartPage();

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



#--------------
sub findMissing {
#--------------

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

    # Check for missing values
    if ( not $arr{grades} or not $arr{terms} ) {
	print qq{<h3>Error: Missing Grades or Terms</h3>\n};
	print qq{</body></html>\n};
	exit;
    }

    
    my @grades = split(/\s+/, $arr{grades} );
    my @terms = split(/\s+/, $arr{terms} );

    print qq{<h3 style="margin-left:1em;">Grades - @grades<br>Terms - @terms</h3>\n};

    
    
    # find courses in each grade and their terms in preparation
    my %coursegrade; # grade,term,subjsec;
    my $sth = $dbh->prepare("select description, subjsec,startrptperiod,endrptperiod from subject
			    where grade = ?");

    foreach my $grade ( @grades ) {
	$sth->execute($grade);
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my ( $description, $subjsec, $startterm,$endterm ) = $sth->fetchrow ){
	    foreach my $trm ( $startterm..$endterm ) {
		$coursegrade{$grade}{$trm}{$subjsec} = $description;
	    }
	}
    }
    # we now have course master information about all grades of interest.

       
    # Get enrolled student count from grade/terms of interest.
    my $sth = $dbh->prepare("select count(*) from eval where subjcode = ? and term = ?");
    my $sth1 = $dbh->prepare("select count(*) from schedat 
			     where subjsec = ? and term = ?"); # not worried about day or period

    print qq{<table cellpadding="3" cellspacing="0" border="0" };
    print qq{style="border:1px solid gray;margin:1em;padding:0.4em;">\n};
    print qq{<tr><th>Course</th><th>Code/Section</th><th>Term</th><th>Grade</th><th>Enrol</th></tr>\n};
    print qq{<caption style="font-weight:bold;font-size:120%;">};
    print qq{Enrolled Courses without a Timetable</caption>\n};
    
    # Loop through all terms; we already have the grade courses saved.
    my $first = 1;
    foreach my $term ( @terms ) {
	foreach my $grade ( @grades ) {
	    foreach my $subjsec ( keys %{ $coursegrade{$grade}{$term} }) {

		# Get courses enrolled this term. 
		$sth->execute($subjsec,$term);
		if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		my $count = $sth->fetchrow;
		# if count, we have enrollments in this course in this term.
		
		# check if we have a timetable set.
		if ( $count) { # check for a timetable.
		    $sth1->execute($subjsec,$term);
		    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		    my $tcount = $sth1->fetchrow; # timetable count.
		    if ( not $tcount ) { # no timetable
			print qq{<tr><td>$coursegrade{$grade}{$term}{$subjsec}</td>\n};
			print qq{<td>$subjsec</td><td class="cn">$term</td><td class="cn">$grade</td>};
			print qq{<td class="cn">$count</td></tr>\n};
			$first = 0;
		    }
		}
	    } # end subjsec
	} # end grade
    } # end term

    if ( $first ) { # no missing timetables
	print qq{<tr><td colspan="5">No Missing Timetables</td></tr>\n};
    }
    
    print qq{</table>\n};
    print qq{</body></html>\n};

    exit;

} # end of findMissing



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

    # Get Terms and Grades to check for.

    my $grades; # grades with subject attendance
    foreach my $gr ( sort {$a <=> $b} keys %g_AttendanceEntryMethod ) {
#	print qq{GR:$gr Method:$g_AttendanceEntryMethod{$gr}<br>\n};
	if ($g_AttendanceEntryMethod{$gr} eq 'subject' ) {
	    $grades .= qq{$gr };
	}
    }
    

    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="border:1px solid gray;margin:1em;padding:0.4em;">\n};

    # Grades
    print qq{<tr><td class="bra">Grades to check</td>};
    print qq{<td><input type="text" name="grades" style="width:10ch;" value="$grades"> \n};
    print qq{Separate with Spaces</td></tr>\n};

    # Terms
    print qq{<tr><td class="bra">Terms to check</td>\n};
    print qq{<td><input type="text" name="terms" style="width:10ch;"> };
    print qq{Separate with Spaces</td></tr>\n};

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

    exit;
}
