#! /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 = ('Error' => 'Error',
	   'Main' => 'Main',
	   'Bus Route' => 'Bus Route',
	   'Students' => 'Students',
	   'Route' => 'Route',
	   'Name' => 'Name',
	   'Grade' => 'Grade',
	   'HRm' => 'HRm',
	   'Address' => 'Address',
	   'Ph' => 'Ph',
	   'Parent 1' => 'Parent 1',
	   'Parent 2' => 'Parent 2',
	   'View/Download' => 'View/Download',
	   'View Log File' => 'View Log File',
	   'Rm' => 'Rm',
	   'Report' => 'Report',
	   'Not Found' => 'Not Found',
	   'Family' => 'Family',
	   'Sort by' => 'Sort by',
	   'Continue' => 'Continue',
	   'Attendance' => 'Attendance',
	   
	   );

use DBI;
use CGI;

my $self = 'rptbusroute1.pl';

# Basic Constants
my $logfile = "pdflog$$.txt";
my $shortname = "busrpt$$";
my $filename = "$shortname.tex";
my $maxlines = 28;

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

my $altNameField = $g_FamilyGroupNameField; # provides a family rather than a/b/c forms

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


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

if ($arr{maxstudents}) {
    $maxlines = $arr{maxstudents};
}


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

my ($sec, $min, $hour, $mday, $mon, $year, $wday, 
 $yday, $iddst) = localtime(time);
$year = $year + 1900;
$mon++;
$wday++;
my $currdate = "$dow[$wday], $month[$mon] $mday, $year";

my $title = "$lex{'Bus Route'} 1 - $lex{Attendance}";
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{<h1>$title</h1>\n};


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

# foreach my $key ( sort keys %arr ) { print qq{K:$key VAL:$arr{$key}<br>\n}; }
# exit;
# passed groupFld, sortorder

my %bus;
my $select = qq{where busroute is not NULL and  busroute != ''};
$sth = $dbh->prepare("select distinct busroute from student $select 
		     order by busroute, lastname, firstname");

if ( $arr{sortorder} eq 'family' ) { # Assemble all families into bus driver hash

    # Get bus routes/drivers
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die; } 
    while ( $busroute = $sth->fetchrow ) {  
	my $ref = doFamily( $busroute ); # get a list of students ordered by family/name (fam:studnum)
	$bus{$busroute} = $ref; # array ref
    }

} else {  # end of family sorting; now normal sorting by name
    
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die; } 
    while ( $busroute = $sth->fetchrow ) {  
	my $ref = doName( $busroute ); # get a list of students ordered by name only
	$bus{$busroute} = $ref; # array ref
    }
}


# start of TeX setup
open(TEX,'>', $filename) || die "Can't open tex file";

print TEX "\\documentclass[12pt,letterpaper]{article}
\\usepackage{colortbl}
\\pagestyle{empty}
\\setlength{\\textwidth}{8in}
\\setlength{\\textheight}{10.6in}
\\setlength{\\hoffset}{-1.5in}
\\setlength{\\voffset}{-1.4in}
\\setlength{\\parindent}{0pt}
\\setlength{\\tabcolsep}{3.5pt}\n";

print TEX "\\begin{document}\n";
# end of TEX setup.

my ($currfamily,$prevfamily);
#my $curroute = -1;
#my $oldroute;
my $studcount;

# Loop over all student records.
my $first = 1;
my $sth = $dbh->prepare("select * from student where studnum = ?");

#foreach my $key (keys %bus ) {
#    print "<br><br>K:$key VAL:$bus{$key}<br>\n";
#    my @t = @{$bus{$key}};
#    foreach my $val ( @t ) {
#	print qq{/ $val /};
#    }
#}


foreach my $route ( sort keys %bus ) {

    print qq{<div style="float:left;"><span style="font-weight:bold;font-size:120%;margin:1em;">};
    print qq{Route:$route</span><br>\n};
    
    my $count = 1;

    print TEX "\\center{{\\bf $schoolname $lex{'Bus Report'} $lex{Attendance}} - $currdate \\\\ \n";
    print TEX "{\\bf $lex{Route}: $route } \\\\ \n";
    print TEX "\\medskip\n\\begin{tabular}{|p{7cm}|p{2.5cm}|p{2.5cm}|p{2.5cm}|p{2.5cm}|p{2.5cm}|}\n";
    print TEX "\\hline\n\\rule[4pt]{0pt}{11pt} {\\bf $lex{Name}/$lex{Grade}/$lex{HRm} }";
    print TEX "& \\hfil {\\bf Monday }\\hfil &\\hfil {\\bf Tuesday}\\hfil &\\hfil{\\bf Wedneday}\\hfil";
    print TEX "&\\hfil {\\bf Thursday}\\hfil &\\hfil{\\bf Friday}\\hfil \\\\ \n\\hline\n";
    
    foreach my $val ( @{$bus{$route}} ) { # student/family loop

#	print qq{VAL:$val<br>\n};
	
	my ( $fam,$studnum ) = split(':', $val);
	if ( not $studnum ) { # only studnum passed not fam:studnum )
	    $studnum = $fam;
	    undef $fam;
	}

	$prevfamily = $currfamily;
	$currfamily = $fam; # if exists;
	
	# get student record
	$sth->execute($studnum);
	if ($DBI::errstr){ print $DBI::errstr; die; }
	my $ref = $sth->fetchrow_hashref;
	my %r = %$ref;
	foreach my $val ( values %r ) {
	    ( $val ) = latex_filter( $val );
	}
	print qq{$r{firstname} $r{lastname} / \n};
	

	#remove any accidental spaces from the front or end of the route
	# $route =~ s/^\s+//;
	# $route =~ s/\s+$//; 
	
	if ( $linecount > $maxlines ) { # end current page and start a new one.
	    
	    # We'll print a new page header here
	    print TEX "Students: $studcount & & & &  \\\\ \\hline";
	    print TEX "\\end{tabular}\\\\";
       
	    print TEX "\n\\newpage\n";

	    print TEX "\\center{{\\bf $schoolname $lex{'Bus Report'} $lex{Attendance}} - ";
	    print TEX "$currdate \\\\ \n";
	    print TEX "{\\bf $lex{Route}: $route } \\\\ \n";
	    print TEX "\\medskip\n\\begin{tabular}";
	    print TEX "{|p{7cm}|p{2.5cm}|p{2.5cm}|p{2.5cm}|p{2.5cm}|p{2.5cm}|}\n";
	    print TEX "\\hline\n {\\bf $lex{Name}/$lex{Grade}/$lex{HRm} }";
	    print TEX "& {\\bf Monday } & {\\bf Tuesday} & {\\bf Wedneday}";
	    print TEX "& {\\bf Thursday} & {\\bf Friday} \\\\ \n\\hline\n";

	    
	    $linecount = 0;
	    $studcount = 0;
	    $count = 1;

	} # End of New Page Header


	# Normal record printing.
	if ( $fam and $currfamily ne $prevfamily ) {
	    # print a blank family line.
	    # $fam is just the LINK field, not the Family Name field.
	    my $namefield = $g_FamilyGroupNameField;
	    my $familyname = $r{$namefield};
	    $tempfam = $fam;
	    $tempfam =~ s/#//;
	    print TEX "\\rowcolor[gray]{0.8}\\multicolumn{6}{|l|}";
	    print TEX "{\\bf Family $familyname ($tempfam)}\\\\\n\\hline\n";
	    $linecount++;
	}

	
	print TEX "\\rule[4pt]{0pt}{11pt}\\raggedright{\\bf $r{lastname}, ";
	print TEX "$r{firstname} $r{middlename}} ";
	print TEX "G$r{grade}/$r{homeroom} ";
	print TEX "& & & & & ";
	print TEX "\\\\ \\hline \n"; 

	$linecount++;
	$studcount++;
	$count++;
	
    }  # End of student loop
    
    # Now end the partial page
    print TEX "Students: $studcount & & & & & \\\\ \\hline";
    print TEX "\\end{tabular}\\\\\n";
    print TEX "\\newpage\n\n";
    $studcount = 0;
    $linecount = 0;
    
} # end of bus driver route

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


system("$pdflatex $filename > $logfile");
system("mv $shortname.pdf $downloaddir");
system("mv $logfile $downloaddir");
system("rm -f $shortname.*");

print qq{<p></p>\n};
print qq{<div style="font-size:130%;font-weight:bold;margin:1em 0.4em 1em 0.4em;">\n};
print qq{[ <a href="$webdownloaddir/$shortname.pdf">$lex{'View/Download'} $title</a> ]</div>\n};

print qq{<div>[ <a href="$webdownloaddir/$logfile">$lex{'View Log File'}</a> | };
print qq{<a href="$homepage">$lex{Main}</a> ]</div>\n};

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




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

    # Display Current Busroute numbers;
    my $sth = $dbh->prepare("select distinct busroute, count(busroute) from student 
			    where busroute != '' and busroute is NOT NULL
			    group by busroute order by busroute");
    $sth->execute;
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

    my $first = 1;    
    while ( my ( $busroute, $count ) = $sth->fetchrow ) {

	if ( $first ) {
	    print qq{<table cellpadding="3" cellspacing="0" border="1" };
	    print qq{style="margin:0 1em;float:left;">\n};
	    print qq{<tr><th>Bus Routes</th><th>Count</th></tr>\n};
	    $first = 0;
	}

	print qq{<tr><td>$busroute</td><td>$count</td></tr>\n};
    }
    if ( not $first ) {
	print qq{</table>\n};
    } else {
	print qq{<h3>No Bus Routes found</h3>\n};
	print qq{</body></html>\n};
	exit;
    }

    
    # Create meta hash for fieldnames from meta.
    my $sth = $dbh->prepare("select fieldid, fieldname from meta where tableid = ?");
    $sth->execute( 'student' );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    my %meta;
    while ( my ( $fieldid, $fieldname, $required ) = $sth->fetchrow ) {
	$meta{$fieldid} = $fieldname;
    }
    
    # start form.
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};

    print qq{<div style="float:left;">\n}; # outer div
    print qq{<div style="padding:1em;border:1px solid gray;margin:0.5em;width:60ch;">};
    
    print qq{<input type="hidden" name="sortorder" value="family">};

    print qq{<div style="margin:0.3em 0;title="The Field that is the same for all family members; };
    print qq{The field that joins them as a family">};
    print qq{Family Link Field <select name="groupFld">};
    foreach my $fld ( @g_FamilyGroupLinkList ) {
	print qq{<option value="$fld">$meta{$fld}</option>};
    }
    print qq{</select></div>\n};

    print qq{<div>Maximum Lines/page };
    print qq{<input type="text" style="width:4ch;" name="maxstudents" value="$maxlines"></div>\n};
    print qq{<br>\n <input type="submit" value="$lex{Continue}"></form></div>\n};
   
    # Also allow for an edit of the family values
    print qq{<div style="padding:1em;border:1px solid gray;margin:0.5em;width:60ch;};
    print qq{background-color:#DDD;">\n};
    print qq{Edit Family Fields that join Students into Families/Provide unified Family Name \n};
    print qq{<form action="/cgi-bin/configure/confedit.pl" method="post" target="_blank" };
    print qq{style="display:inline;">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="hidden" name="filename" value="admin">\n};
    print qq{<input type="hidden" name="sectionname" value="family">\n};
    print qq{<input type="submit" value="Configure"></form> (new tab)</div>\n};

    # Reset Bus Routes
    print qq{<div style="padding:1em;border:1px solid gray;margin:0.5em;width:60ch;};
    print qq{background-color:#DDD;">\n};
    print qq{<form action="/cgi-bin/eoy/resetselect.pl" method="post" target="_blank">\n};
    print qq{<input type="hidden" name="inputtype" value="text">\n};
    print qq{<input type="hidden" name="field" value="busroute">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="submit" value="Reset Bus Routes"></form> (new tab)</div>\n};
     
    print qq{</div>\n};

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

    exit;


}

#---------
sub doName {  # find students on a bus route, sort by name
#---------

    # passed a bus driver value
    # find all students within and return array of students

    my $busroute = shift;
    my @studnums;
    
    my $sth = $dbh->prepare("select studnum from student where busroute = ?
			    order by lastname, firstname");
    $sth->execute( $busroute );
    if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
    while ( my $studnum = $sth->fetchrow ) {
	push@studnums, $studnum;
    }

    return \@studnums;
}
    

    
#------------
sub doFamily {  # find students on a route sort by family, then name.
#------------

    # passed a bus driver value
    # find all families within and return array of students, plus any errors

    my $busroute = shift;
    
    my @studnums;
    my $groupFld = $arr{groupFld};
    
    # find all families within this route, check for errors (same family, different route)
    # Find all family groups within this busdriver
    my $sth = $dbh->prepare("select distinct $groupFld from student 
			    where $groupFld != '' and $groupFld is not null and busroute = ? 
			    order by $groupFld");

    my $sth1 = $dbh->prepare("select studnum, lastname, firstname from student 
			     where $groupFld = ? and busroute = ?
			     order by lastname, firstname");

#    print qq{<br><div style="font-weight:bold;">Family Busroute $busroute</div>\n};
    my $count = 1;
    
    $sth->execute( $busroute );
    if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
    while ( my $family = $sth->fetchrow ) {

	$sth1->execute( $family, $busroute );
	if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
	while ( my ($studnum,$lastname,$firstname) = $sth1->fetchrow ) {
#	    print qq{<div>$count. FAM:$family STUD:$studnum - $lastname,$firstname</div>\n};
	    $count++;
	    push @studnums, qq{$family:$studnum};
	}
    }

    # Check for errors, students outside busroute in same family
   
    
    return \@studnums;
}
