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

#  This file is part of Open Admin for Schools.


use DBI;
use CGI;

my %lex = ('Student' => 'Student',
	   'Main' => 'Main',
	   'Report Card' => 'Report Card',
	   'Cannot open tex file' => 'Cannot open tex file',
	   'View/Download' => 'View/Download',
	   'View Log File' => 'View Log File',
	   'Select' => 'Select',
	   'Lastname, Firstname' => 'Lastname, Firstname',
	   'Homeroom, Lastname, Firstname' => 'Homeroom, Lastname, Firstname',
	   'Grade, Lastname, Firstname' => 'Grade, Lastname, Firstname',
	   'Band, Lastname, Firstname' => 'Band, Lastname, Firstname',
	   'Sort by' => 'Sort by',
	   'Grade' => 'Grade',
	   'Homeroom' => 'Homeroom',
	   'Continue' => 'Continue',
	   'No Staff Found' => 'No Staff Found',
	   'Error' => 'Error',
	   'Blank=All' => 'Blank=All',
	   'Term' => 'Term',
	   'Period' => 'Period',
	   'Day' => 'Day',
	   'Separate with Spaces' => 'Separate with Spaces',
	   'Terms' => 'Terms',
	   'Missing' => 'Missing',
	   'Band' => 'Band',
	   'Student Number' => 'Student Number',
	   
	);

my $self = "rptMissingCourse.pl";


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


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

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


# print page header
my $title = qq{Missing Course Enrollments};
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">$lex{Main}</a> | \n};
print qq{<a href="$reppage">$lex{'Report Card'}</a> ]\n};
print qq{<h1>$title</h1>\n};


if ( not $arr{page} ) {
    showStartPage();  # main options
    
} elsif ($arr{page} == 1 ) {
    delete $arr{page};
    showReport();
}


#-------------
sub showReport {
#-------------

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

    my $coursenum = $arr{coursenum};
    delete $arr{coursenum};
    my $term = $arr{term};
    delete $arr{term};

    # remaining values in %arr are grades to check. Blank = all.

    
    my @grades = sort {$a <=> $b} keys %arr;
    if ( not @grades ) {
	my $sth = $dbh->prepare("select distinct grade from student 
      	   where grade is not NULL and grade != ''");
	$sth->execute;
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	while ( my $gr = $sth->fetchrow ) {
	    push @grades, $gr;
	}
	@grades = sort {$a <=> $b} @grades;
    }

    # Loop through all grades, and look for missing course enrollments based on coursenum value.
    my $sth = $dbh->prepare("select lastname, firstname,studnum, homeroom from student
       	    where grade = ? order by lastname, firstname");

    my $termselect;
    if ( $term =~ m/\d/ ) {
	$termselect = qq{and term = $term};
    }
    my $sth1 = $dbh->prepare("select count(distinct subjcode) from eval where studnum = ? $termselect");

    my $gcount = 0;
    foreach my $grade ( @grades ) {
	my $count;
	
	my $first = 1;
	$sth->execute( $grade );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my ($ln,$fn,$studnum,$hr) = $sth->fetchrow ) {
	    
	    # Check for enrolled courses.
	    $sth1->execute( $studnum );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $ecount = $sth1->fetchrow;
	    if ( $ecount <= $coursenum ) {

		if ( $first ) {
		    $gcount++;
		    print qq{<table cellpadding="3" cellspacing="0" border="1" };
		    print qq{style="float:left;margin:1em;">\n};
		    print qq{<caption style="font-weight:bold;font-size:120%;">Grade $grade };
		    if ( $term ) { print qq{Term $term}; }
		    print qq{</caption>\n};
		    
		    print qq{<tr><th>Student</th><th>Room</th><th>Crs Enrol</th></tr>\n};
		    $first = 0;
		}
		
		if ( $ecount == 0 ) { $ecount = qq{<span style="color:red;">$ecount</span>}; }
		print qq{<tr><td><b>$ln</b>, $fn ($studnum)</td><td>$hr</td>};
		print qq{<td class="cn">$ecount</td></tr>\n};

		$count++;
		if ( $count % 10 == 0 ) {
		    print qq{<tr><th>Student</th><th>Room</th><th>Crs Enrol</th></tr>\n};
		}

	    }
	}
	print qq{</table>\n};
	if ( $gcount % 3 == 0 ) {
	    print qq{<p style="clear:left;"></p>\n};
	}

    }

    exit;
}




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

    
    my @grades;
    # Get Grades
    $sth = $dbh->prepare("select distinct grade from student 
      where grade is not NULL and grade != ''");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my $gr = $sth->fetchrow ) {
	push @grades, $gr;
    }
    @grades = sort {$a <=> $b} @grades;
    
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="submit" value="$lex{Continue}"></td></tr>\n};
    
    print qq{<table cellpadding="3" cellspacing="0" border="0" };
    print qq{style="margin:0.4em 0; border:1px solid gray;padding:0.3em;">\n};
    
    print qq{<tr><td class="bla">Grades to Check<br>(Blank = ALL)</td></tr>};
    foreach my $gr ( @grades ) {
	print qq{<tr><td style="la"><input type="checkbox" name="$gr" value="1"> $gr</td></tr>\n};
    }

    # Minimum Course Enrollments (typically zero)
    print qq{<tr><td><b>Min Course Enrollments</b><br>};
    print qq{<input type="text" style="width:3ch;" name="coursenum" value="0">};
    print qq{ 0:No Course Enrollments</td></tr>\n};

    # Term?
    print qq{<tr><td><b>Term</b> <input type="text" style="width:3ch;" name="term"> };
    print qq{Blank:All Terms</td></tr>\n};

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

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

    exit;

}
