#!/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 $self = 'tt_report1.pl';

my %lex = ('View' => 'View',
	   'Main' => 'Main',
	   'Timetable' => 'Timetable',
	   'No Record(s) Found' => 'No Record(s) Found',
	   'Grade' => 'Grade',
	   'Term' => 'Term',
	   'Delete Entries' => 'Delete Entries',
	   'No Value' => 'No Value',
	   'Period' => 'Period',
	   'Day' => 'Day',
	   'Term' => 'Term',
	   'Error' => 'Error',
	   'Course' => 'Course',
	   'Missing' => 'Missing',
	   'Enrollments' => 'Enrollments',
	   'Enrol' => 'Enrol',
	   
	   );

use DBI;
use CGI;
use Cwd;

# Set prepath for config file: (/tcgi or cgi/schedule)
my $prepath = '../..';
if (getcwd() =~ /tcgi/){ # we are in tcgi
    $prepath = '..';
}

eval require "$prepath/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;

# Get current dir so know what CSS to display;
if (getcwd() =~ /tcgi/){ # we are in tcgi
    $css = $tchcss;
    $homepage = $tchpage;
}

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


my ($select, $term);
if ($arr{term} =~ m/\d/) { # must be a digit
    $term = $arr{term};
    my $trm = $dbh->quote($arr{term});
    $select = "where term = $trm";
}


# print page header.
my $title = qq{$lex{View} $lex{Timetable} - Student 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};
unless (getcwd() =~ /tcgi/){ # unless we are in tcgi
    print qq{| <a href="$schpage">$lex{Timetable}</a> \n};
}
print qq{]\n};

print qq{<h1>$title</h1>\n};

print qq{<div style="clear:both;margin-bottom:1em;"></div>\n};


my (%course, %timetable, %noenrol, %nottbl, %grades);

my $sth1 = $dbh->prepare("select distinct term, count(*) from schedat where subjsec = ?");
my $sth2 = $dbh->prepare("select count(distinct studnum) from eval where subjcode = ?");


# Loop through all courses in subject table.
my $sth = $dbh->prepare("select subjsec, description, grade, startrptperiod, endrptperiod from subject");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
my $maxterm = 1; # max term value for looping through timetable entries.

while ( my ( $subjsec, $desc, $grade, $startterm, $endterm ) = $sth->fetchrow ) {

    if ( $endterm > $maxterm ) { $maxterm = $endterm; }

    # store course info in hash.
    $course{$subjsec}{desc} = $desc;
    $course{$subjsec}{start} = $startterm;
    $course{$subjsec}{end} = $endterm;
    $course{$subjsec}{grade} = $grade;

    $grades{$grade} = 1; # track which grades we have info for.

    
    # Loop through Timetable data checking for number of entries.
    $sth1->execute($subjsec);
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    my $tfirst = 1;
    while ( my ($term, $tcount) = $sth1->fetchrow ) {
	$timetable{$subjsec}{$term} = $tcount;
	$tfirst = 0;
    }
    if ( $tfirst ) { $nottbl{$subjsec} = 1; } # track if no timetable done.

    
    # Enrollment Count
    $sth2->execute($subjsec);
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    my $ecount = $sth2->fetchrow;
    if (not $ecount ) { 
	$noenrol{$grade}{$subjsec} = 1; # track if no student enrollments
    } else {
	$course{$subjsec}{enrol} = $ecount;
    }
}


# Loop Through Each Grade, and Display Courses and Timetable Values.
my $count = 1;
foreach my $grade ( sort {$a <=> $b} keys %grades ) {

    print qq{<table cellpadding="3" cellspacing="0" border="1" style="float:left;margin:0.5em;">\n};
    print qq{<caption style="font-weight:bold;">$lex{Grade} $grade</caption>\n};
    
    print qq{<tr><th>$lex{Course}</th><th>$lex{Enrol}</th><th colspan="$maxterm">};
    print qq{Term / Timetable Rec</th></tr>\n};
    
    print qq{<tr><th></th><th></th>};
    foreach my $trm ( 1..$maxterm ) {
	print qq{<th>$trm</th>};
    }
    print qq{</tr>\n};

    
    # Find courses in this grade and alphabetize, skip any without enrollments.
    my %sort; # ie. grade courses
    foreach my $subjsec ( keys %course ) {
	if ( $noenrol{$grade}{$subjsec} ) { next; } # skip no enrollments.
	# print qq{Subjsec:$subjsec - $course{$subjsec}{grade}<br>\n};
	if ( $course{$subjsec}{grade} eq $grade ) {
	    my $desc = $course{$subjsec}{desc};
	    $sort{"$desc$subjsec"} = $subjsec;
	}
    }


    # Now use this %sort hash to go through this grades courses
    foreach my $key ( sort keys %sort ) {
	my $subjsec = $sort{$key};

	print qq{<tr><td>$course{$subjsec}{desc} ($subjsec)</td><td class="cn">};
	print qq{$course{$subjsec}{enrol}</td>};
	
	foreach my $trm ( 1..$maxterm ) {
	    print qq{<td>$timetable{$subjsec}{$trm}</td>};
	}
	print qq{</tr>\n};
    }
    print qq{</table>\n};

    # Display No Enrollments for this grade
    if ( %{ $noenrol{$grade}} ) {

	my %sortem;
	foreach my $subjsec ( keys %{ $noenrol{$grade} } ) {
	    my $desc = $course{$subjsec}{desc};
	    my $trm = "$course{$subjsec}{start}-$course{$subjsec}{end}";
	    $sortem{"$trm$desc$subjsec"} = $subjsec;
	}
	
	print qq{<table cellpadding="3" cellspacing="0" border="1" style="float:left;margin:0.5em;">\n};
	print qq{<caption style="font-weight:bold;">Courses without $lex{Enrollments}</caption>\n};
	print qq{<tr><th>$lex{Course}</th><th>$lex{Term}</th></tr>\n};
	
	# foreach my $subjsec ( keys %{ $noenrol{$grade} } ) {
	foreach my $key ( sort keys %sortem ) {
	    my $subjsec = $sortem{$key};
	    print qq{<tr><td>$course{$subjsec}{desc} ($subjsec)</td>};
	    print qq{<td><b>$course{$subjsec}{start}-$course{$subjsec}{end}</b></td></tr>\n};
	}
	print qq{<table>\n};
	print qq{<div style="clear:left;"></div>\n};
	$count++;
    }

    $count++;
    if ( $count % 3 == 0 ) {
	print qq{<div style="clear:left;"></div>\n};
    }
    
#    print qq{<div style="clear:both;margin-bottom:1.5em;"></div>\n};
    print qq{<p style="page-break-after:always;"></p>\n};
    
}

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