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


use DBI;
use CGI;

my %lex = ('Student Timetables' => 'Student Timetables',
	   'Main' => 'Main',
	   'Timetable' => 'Timetable',
	   '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 Student(s) Found' => 'No Student(s) Found',
	   'Error' => 'Error',
	   'Blank=All' => 'Blank=All',
	   'Band' => 'Band',
	   'Term' => 'Term',
	   'Period' => 'Period',
	   'Day' => 'Day',
	   'Separate with Spaces' => 'Separate with Spaces',
	   'Term(s)' => 'Term(s)',
	   'Font Size' => 'Font Size',
	   'Paper Size' => 'Paper Size',
	   'Letter' => 'Letter',
	   'Legal' => 'Legal',
	   'A4' => 'A4',
	   'Student Number' => 'Student Number',

	);

my $grayshade = '0.80';
my $maxchars = 10; # maximum characters for subject description (if not small desc field)

my $logowidth = '20mm';
my $logofile = 'schoollogo.jpg';

my $self = "rptstudenttimetable.pl"; # previously called 'rptschedule.pl'.


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

eval require "../../lib/liblatex.pl";
if ( $@ ) {
    print $lex{Error}. " $@<br>\n";
    die $lex{Error}. " $@\n";
}


my $maxlines = 28;
my $shortname = "schedrpt$$";
my $filename = "$shortname.tex";

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 = $lex{'Student Timetables'};
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="$schpage">$lex{Timetable}</a> ]\n};


if ( not $arr{page} ) {
    showStartPage();
}

my %staffnames; # global


my $sortorder = "lastname, firstname";
if ( $arr{sortorder} eq 'grade' ) {
    $sortorder = "grade, lastname, firstname";
} elsif ( $arr{sortorder} eq 'band' ) {
    $sortorder = "band, lastname, firstname";
} elsif ( $arr{sortorder} eq 'homeroom' ) {
    $sortorder = "homeroom, lastname, firstname";
}
delete $arr{sortorder};


my $includelocation;
if ( $arr{includelocation} ) {
    $includelocation = 1;
}
delete $arr{includelocation};


my @terms = split /\s/, $arr{term};
delete $arr{term};
if ( not @terms ) {
    print qq{<h3>No Terms Found</h3>\n};
    print qq{</body></html>\n};
    exit;
}



my $select;
if ( $arr{group} ) {
    if ( $arr{select} eq $lex{Grade} ) { # Find this grade;
	my $grp = $dbh->quote( $arr{group} );
	$select = "where grade = $grp";

    } elsif ( $arr{select} eq $lex{Homeroom} ) {
	my $grp = $dbh->quote( $arr{group} );
	$select = "where homeroom = $grp";

    } elsif ( $arr{select} eq $lex{Band} ) {
	my $grp = $dbh->quote( $arr{group} );
	$select = "where band = $grp";

    } elsif ( $arr{select} eq $lex{'Student Number'} ) {
	my $val = $dbh->quote( $arr{group} );
	$select = "where studnum = $val";
    }
}




# foreach my $key (sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
# print qq{Sortorder: $sortorder  Select: $select<br>\n};


# Now create an array, @students,  to store student numbers of students to print
my $sth = $dbh->prepare("select studnum from student $select
 order by $sortorder");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
while ( my $studnum = $sth->fetchrow ) {
    push @students, $studnum;
}

if ( @students ) { # We have students to print; setup LaTeX file.
    open(TEX,">$filename") || die $lex{'Cannot open tex file'};


    my ( $papersize, $textwidth, $textheight );

    if ( $arr{papersize} eq $lex{Letter} ) {
	$papersize = 'letterpaper';
	$textwidth = $g_letterpaper_textwidth;
	$textheight = $g_letterpaper_textheight;
    } elsif ( $arr{papersize} eq $lex{Legal} ) {
	$papersize = 'legalpaper';
	$textwidth = $g_legalpaper_textwidth;
	$textheight = $g_legalpaper_textheight;
    } elsif ( $arr{papersize} eq $lex{A4} ) {
	$papersize = 'a4paper';
	$textwidth = $g_a4paper_textwidth;
	$textheight = $g_a4paper_textheight;
    } 


    print TEX "\\documentclass[$arr{fontsize},$papersize, landscape]{article}
\\usepackage{array,colortbl,multicol,inputenc,graphicx,helvet}
$a_latex_header
\\pagestyle{empty}
\\setlength{\\textwidth}{$textwidth}
\\setlength{\\textheight}{$textheight}
\\setlength{\\evensidemargin}{0mm}
\\setlength{\\oddsidemargin}{0mm}
\\setlength{\\hoffset}{-10mm}
\\setlength{\\voffset}{-25mm}
\\setlength{\\extrarowheight}{2pt}
\\setlength{\\parindent}{0pt}\n";

    print TEX "\\begin{document}\n";

} else {
    print qq{<h3>$lex{'No Student(s) Found'}</h3>\n};
    print qq{</body></html>\n};
    die $lex{'No Student(s) Found'}. "\n";
}

# Now Do Student Timetables
my %subtimes; # used for global subject storage;

# max periods even if last one is blank.
my $sth = $dbh->prepare("select max(period) from schedat");
$sth->execute;
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my $maxperiods = $sth->fetchrow;


foreach my $studnum ( @students ) {

    prTimetable( $studnum, $term ); # do 1 term and 1 student.l

} # End of Student Loop

print TEX "\\end{document}";
close TEX;

system("$pdflatex $filename >pdflog$$.txt");
system("mv $shortname.pdf $downloaddir");
system("mv pdflog$$.txt $downloaddir");
system("rm $shortname.*");

print qq{<h1><a href="$webdownloaddir/$shortname.pdf">\n};
print qq{$lex{'View/Download'} $lex{'Student Timetables'}</a></h1>\n};

print qq{[ <a href="$schpage">$lex{Timetable}</a> |\n};
print qq{<a href="$webdownloaddir/pdflog$$.txt">$lex{'View Log File'}\n};
print qq{</a> ]\n</body></html>\n};


#--------------
sub prTimetable {
#--------------

    my $studnum = shift;

    # Get more student info
    my $sth = $dbh->prepare("select lastname, firstname, grade, homeroom, band,
     provnum from studentall where studnum = ?");
    $sth->execute( $studnum );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    my ( $lastname, $firstname, $grade, $homeroom, $band, $provnum ) = $sth->fetchrow;

    # Setup for Get Subject Information
    my $sth2 = $dbh->prepare("select description, smdesc, location, teacher 
      from subject where subjsec = ?");

    # Setup for Get Teacher Info
    my $sth3 = $dbh->prepare("select lastname, firstname, sal from staff where userid = ?");



    # Now print the Timetable in TeX, heading first.
    print TEX "\\includegraphics[width=$logowidth]{../../etc/$logofile}\\quad\n";

    print TEX "\\parbox[b]{150mm}{ {\\Large $schoolname}\n\n\n\n";
    print TEX "{\\LARGE\\bf $firstname $lastname} ($provnum)\n\n";
    print TEX "{\\bf $lex{Grade}} $grade\\\\\n";
    if ( $homeroom ) {
	print TEX "{\\bf $lex{Homeroom}} $homeroom\\\\\n";
    }
    if ( $band ) {
	print TEX "{\\bf $lex{Band}} $band\\\\\n";
    }
    print TEX " }\n\n"; # end of parbox.
    print TEX "\\vspace{8mm}\n";

    foreach my $term ( @terms ) {

	# Get their enrollment this term; could be repeated for multiple terms.
	$sth = $dbh->prepare("select distinct subjcode from eval 
          where studnum = ? and term = ?");
	$sth->execute( $studnum, $term );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

	my @timetable;
	my $maxdays = 0;

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

	while ( my $subjsec  = $sth->fetchrow ) {

	    if ( not $subtimes{$subjsec} ) { # don't have subject's timetable values
		# load each timetable record for this subject.
		$sth1->execute( $subjsec ) ;
		if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		$subtimes{ $subjsec } = [ ]; # create
		while ( my ( $day, $period ) = $sth1->fetchrow ) {
		    push @{ $subtimes{$subjsec} }, "$day:$period";
		}
	    }

	    # Populate 2D day:period array; use global values for day and period.
	    my @dayperiod = @{ $subtimes{$subjsec} };

	    foreach my $rec ( @dayperiod ) {
		my ( $day, $period ) = split ':', $rec;
		if ( $day > $maxdays ) { $maxdays = $day; }
		if ( $period > $maxperiods ) { $maxperiods = $period; }
		$timetable[$period][$day] = $subjsec; # since we iterate over periods first.
	    }
	}

	if ( not $maxdays ) { next; }

#$maxdays = 1; # override for Eagleview...

	print TEX "\\begin{tabular}{|c|";
	for ( 1 .. $maxdays ) { print TEX "c|" }
	print TEX "}\\hline\n\n";

	# Print Top Row here.
	print TEX "\\rowcolor[gray]{0.92}$lex{Term} $term / ". $lex{Day};
	for my $day ( 1 .. $maxdays ) { print TEX " & $day "; }
	print TEX "\\\\ \\hline\n";

	for my $period ( 1 .. $maxperiods ) {   # $#timetable ) {
	    print TEX $lex{Period}. " $period";

	    my %dayname;  # day -> name
	    for my $day ( 1 .. $maxdays ) { 
		my $subjsec = $timetable[$period][$day];

		# If no subject, skip to next
		if ( not $subjsec ) {
		    print TEX "& ";
		    next;
		}

		# Load subject information.
		$sth2->execute( $subjsec );
		if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		# Get $description, $smdesc, $location, $teacher (userid)
		my $ref = $sth2->fetchrow_hashref;
		foreach my $key ( keys %$ref ) {
		    ($ref->{$key}) = latex_filter($ref->{$key});
		}
		
		my ( $description, $smdesc, $location, $teacher ) = 
		    ( $ref->{description}, $ref->{smdesc}, $ref->{location}, $ref->{teacher} );
		if ( not $smdesc ) {
		    $smdesc = substr( $description, $maxchars ); # defined at top.
		}

		my $teachername;
		if ( not $staffnames{$teacher} ) { # get teacher name.
		    $sth3->execute( $teacher ); # userid
		    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		    my ($lastname, $firstname, $sal) = $sth3->fetchrow;
		    my $finitial = substr($firstname, 0, 1);
		    $teachername = "$finitial.$lastname";
		    $staffnames{$teacher} = $teachername;
		} else {
		    $teachername = $staffnames{$teacher};
		}
		$dayname{$day} = $teachername;


		print TEX " & $smdesc ($subjsec)";
		if ( $location and $includelocation ) { print TEX " $location";  }


	    }
	    print TEX "\\\\ \n";


	    # Now do the second line for same period, all days
	    print TEX ' '; # below periods value
	    for my $day ( 1 .. $maxdays ) {
		print TEX " & $dayname{$day}";
	    }
	    print TEX "\\\\ \\hline\n";


	}
	print TEX "\\end{tabular}\\hfill\n";

    } # end of Term Loop
    
    print TEX "\n\\newpage\n"; # new line for next student.


} # end of prTimetable




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


    # Get default papersize
    my $papersize;
    if ( $defaultpapersize ) {
	$papersize = $defaultpapersize;
	$papersize =~ s/paper//; # strip off the 'paper' ending;
	$papersize = ucfirst( $papersize );
    }

    # print sortorder and selection input form.
    print qq{<h1>$lex{'Student Timetables'}</h1>\n};
    
    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">\n};
    print qq{<tr><td class="bra">$lex{'Sort by'}</td>\n};
    print qq{<td><select name="sortorder"><option value="name">$lex{'Lastname, Firstname'}</option>\n};
    print qq{<option value="homeroom">$lex{'Homeroom, Lastname, Firstname'}</option>\n};
    print qq{<option value="grade">$lex{'Grade, Lastname, Firstname'}</option>\n};
    print qq{<option value="band">$lex{'Band, Lastname, Firstname'}</option>\n};
    print qq{</select></td></tr>\n};

    print qq{<tr><td class="bra">$lex{Select}</td>\n};
    print qq{<td><select name="select"><option>$lex{Grade}</option>\n};
    print qq{<option>$lex{Homeroom}</option>\n};
    print qq{<option>$lex{Band}</option>\n};
    print qq{<option>$lex{'Student Number'}</option>\n};
    print qq{</select>\n};
    print qq{<input type="text" name="group" size="14"> $lex{'Blank=All'}</td></tr>\n};

    print qq{<tr><td class="bra">$lex{'Paper Size'}</td>\n};
    print qq{<td class="la"><select name="papersize">};
    if ( $papersize ) { print qq{<option>$papersize</option>\n}; }
    print qq{<option>$lex{Letter}</option>};
    print qq{<option>$lex{A4}</option>};
    print qq{<option>$lex{Legal}</option>};
    print qq{</select></td></tr>\n};

    print qq{<tr><td class="bra">$lex{'Font Size'}</td>\n};
    print qq{<td><select name="fontsize"><option>10</option><option>11</option>\n};
    print qq{<option>12</option></select></td></tr>\n};

#    print qq{<tr><td class="bra">$lex{Orientation}</td>\n};
#    print qq{<td><select name="orientation"><option value="portrait">$lex{Portrait}</option>};
#    print qq{<option value="landscape">$lex{Landscape}</option></select></td></tr>\n};

    print qq{<tr><td class="bra">Include Room Locaton?</td>};
    print qq{<td class="la"><input type="checkbox" name="includelocation" value="1"></td></tr>\n};
    
    
    # Now do the term
    print qq{<tr><td class="bra">$lex{'Term(s)'}</td><td>};
    print qq{<input type="text" name="term" size="12">\n};
    print qq{$lex{'Separate with Spaces'}</td></tr>\n};

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

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

    exit;

}
