#!/usr/bin/perl
#  Copyright 2001-2024 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.

# Display the Timetables for Grades and Terms

# Note: We get courses by grade based on enrollments in courses for
# grade students rather than using the grade value in the course
# record. (ie. subject table)


my $self = 'tt_report2.pl';

my %lex = ('View' => 'View',
	   'Main' => 'Main',
	   'Timetable' => 'Timetable',
	   'No Records Found' => 'No Records 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',
	   'Report' => 'Report',

	   );

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 = "$lex{Timetable} $lex{Report} 2";

print qq{$doctype\n<html><head><title>$title</title>
<link rel="stylesheet" href="$css" type="text/css">
$chartype\n</head><body>\n};

print qq{[ <a href="$homepage">$lex{Main}</a> };
unless (getcwd() =~ /tcgi/){ # unless we are in tcgi
    print qq{| <a href="$schpage">$lex{Timetable}</a> };
}
print qq{]\n};
print qq{<div style="clear:both;margin-bottom:1em;"></div>\n};


my (%studcourse, %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 = ?");
my $sth3 = $dbh->prepare("select description, grade, startrptperiod, endrptperiod from subject where subjsec = ?");

# Find courses of this student;
my $sth4 = $dbh->prepare("select distinct subjcode from eval where studnum = ?");

# Get grade, studnum from all current students.
my $sth = $dbh->prepare("select distinct grade, studnum from student");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

# NOTE: Getting grade from student, not value in course, since they
# may not be in same grade as course normally taught in.
while ( my ($grade, $studnum ) = $sth->fetchrow ) {

    # Get his/her courses
    $sth4->execute($studnum);
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    while ( my $subjsec = $sth4->fetchrow ) {
	$studcourse{$grade}{$subjsec} = 1;
    }
}

#print qq{Student Course}, %studcourse, "<br>\n};


# Now Loop through all grades, then all subjects.
foreach my $grade ( sort {$a <=> $b} keys %studcourse ) {

    print qq{<hr><h2>$lex{Grade} $grade</h2>\n};

    my (%gcourse, %courseorder, $maxterm);

    foreach my $subjsec ( keys %{ $studcourse{$grade}} ) {

	# Get Course Info
	$sth3->execute($subjsec);
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my ( $desc, $grade, $startterm, $endterm ) = $sth3->fetchrow;

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

	# Put into data struct
	$gcourse{$subjsec}{desc} = $desc;
	$gcourse{$subjsec}{start} = $startterm;
	$gcourse{$subjsec}{end} = $endterm;
	$gcourse{$subjsec}{grade} = $grade;
	$courseorder{"$desc$subjsec"} = $subjsec; # order of courses for display;
	
    };

    my @sorted = map { $courseorder{$_} } sort {$a <=> $b} keys %courseorder;

    foreach my $term (1..$maxterm) {
	printTimetable($grade, $term, \%gcourse, \@sorted);
    }
    print qq{<div style="clear:both;"></div>\n};


} # end of grade loop

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



#-----------------
sub printTimetable { # print timetable for 1 grade for 1 term.
#-----------------

    my ($grade, $term, $courseref, $sortref) = @_;
    my %course = %$courseref;
    my @sortedcourse = @$sortref;
    my (%tb, $rows, $cols );

 
    # print qq{[ <a href="schedel.pl?gr=$grade&trm=$term">$lex{'Delete Entries'}</a> ]</p>\n};

    my $sth = $dbh->prepare("select day,period from schedat where term = ? and subjsec = ? order by period, day");

    foreach my $subjsec ( @sortedcourse ) {
	# Get Timetable values
	$sth->execute( $term, $subjsec );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	while ( my ($day,$period) = $sth->fetchrow ) {
#	    print qq{S:$subjsec D:$day P:$period<br>\n};
	    if ( $period > $rows ) { $rows = $period; }
	    if ( $day > $cols ) { $cols = $day; }
	    push @{ $tb{$period}{$day} }, $subjsec;
	}

    }

    $sth = $dbh->prepare("select description from subject where subjsec = ?");

    if ( %tb ) {
	print qq{<table cellpadding="3" cellspacing="0" border="1" style="float:left;margin:0.5em;">\n};
	print qq{<caption style="font-size:120%;font-weight:bold;">$lex{Term} $term</caption>\n};

	# heading
	print qq{<tr><th></th>};
	for my $day ( 1 .. $cols ){ print qq{<th>$lex{Day} $day</th>}; }
	print qq{</tr>\n};
    
	# Main body of table.
	for my $i ( 1 .. $rows ) {
	    print qq{<tr><td>$lex{Period} $i</td>};
	    for ( 1 .. $cols ) {
		if ( $tb{$i}{$_} ){ # if we have a course code

		    my $first = 1;
		    @subjval = @{ $tb{$i}{$_} };
		    print qq{<td>};
		    foreach my $sv ( @subjval ) {
			if (not $first){ print qq{<br>}; } else { $first = 0;}
			$sth->execute($sv);
			if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}	    
			my $desc = $sth->fetchrow;
			print qq{$desc ($sv)};

		    }
		    print qq{</td>};

		} else {
		    print qq{<td style="color:red;">$lex{'No Value'}</td>};
		}
	    }
	    print qq{</tr>\n};
	}
	print qq{</table>\n};

    } else { # no data
	print qq{<div style="clear:both;font-weight:bold;">$lex{Term} $term - $lex{'No Records Found'}</div>\n};
    }

    return;

} # End of printTimetable
