#! /usr/bin/perl
#  Copyright 2001-2018 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 = ('Main' => 'Main',
	   'View/Download' => 'View/Download',
	   'Staff' => 'Staff',
	   'View Log File' => 'View Log File',
	   'Continue' => 'Continue',
	   'Grade' => 'Grade',
	   'Rm' => 'Rm',
	   'Settings' => 'Settings',
	   'Paper Size' => 'Paper Size',
	   'Letter' => 'Letter',
	   'A4' => 'A4',
	   'Legal' => 'Legal',
	   'Error' => 'Error',
	   'Report' => 'Report',
	   'Select' => 'Select',
	   'Blank=All' => 'Blank=All',
	   'Count' => 'Count',
	   'Absence' => 'Absence',
	   'Page Break' => 'Page Break',
	   'between' => 'between',
	   'Font Size' => 'Font Size',
	   'Total Days' => 'Total Days',
	   'Printed' => 'Printed',
	   'Check All' => 'Check All',
	   'Late Count' => 'Late Count',
	   'Total' => 'Total',

    );


my $self = 'staffabsRpt1.pl';

use DBI;
use CGI;

# Configurable settings
my $maxstaff = 26; # maximum staff per page.
my $maxstaffpositions = 12; # max type of staff types in admin.conf;
my $width = 8;  # default width of columns, 8mm;

my $entrylimit = 15; # Max entries allowed for rotated text.
my $maxentrysize = 14; # Maximum number of characters for the entry

my $namewidth = 50; # Width of the first 'Name' column in mm.
my $extracolwidth = 10; # Width of the second column in mm. Done in
# in case I need this for another field later...

# No longer used: my $margins = 26; # leave 0.5in (13mm) for each side margin.

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 $q = new CGI;
print $q->header( -charset, $charset );
my %arr = $q->Vars;


# Date set after pulling in month and dow array.
my ($sec, $min, $hour, $mday, $mon, $year, $wday, 
 $yday, $iddst) = localtime(time);
$year = $year + 1900;
$mon++;
$wday++;
my $currdate = "$s_dow[$wday], $s_month[$mon] $mday, $year";


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


my $title = qq{$lex{Staff} $lex{Absence} $lex{Report} 1};
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 style="padding:1em 2em;">\n};

print qq{[ <a href="$homepage">$lex{Main}</a> ]\n};

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


if ( not $arr{page} ){
    showStartPage();
    
} elsif ( $arr{page} == 1 ) {
    delete $arr{page};
    selectStaff();
    
} elsif ( $arr{page} == 2 ) {
    delete $arr{page};
    showReport();
}



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

    # Get dates in order to figure out the years.
    my  %startyear;
    my $sth = $dbh->prepare("select distinct year(adate),month(adate) from staff_absent 
			    order by adate");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my ($yr,$mo) = $sth->fetchrow ) {
	if ( $yr == 0 ) { next; }
	if ( $mo > 7 ) { $startyear{$yr} = 1; }
#	print "YR:$yr MO:$mo<br>\n";
    }

    # Check for NULL dates (0000-00-00);
    my $sth = $dbh->prepare("select * from staff_absent where adate = '0000-00-00'");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    my $first = 1;
    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;
	if ( $first ) {
	    print qq{<table cellpadding="3" cellspacing="0" border="1" style="margin-bottom:1em;">\n};
	    print qq{<caption style="color:red;font-weight:bold;">$lex{Error} - Blank Date</caption>\n};
	    $first = 0;
	}
	print qq{<tr><td>$r{firstname} $r{lastname}</td><td>$r{reason}</td>};
	print qq{<td>$r{daypart} / $r{other}</td><td>$r{comment}</td></tr>\n};
    }
    
    if ( not $first ){ # close table
	print qq{</table>\n};
    }
    
    
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};

    
    print qq{<div>Select Year };
    print qq{<select name="startyear">\n};

    my $size = keys %startyear;
    if (  $size == 1 ) { # just display this year as default
	my ($startyear) = keys %startyear;
	my $endyear = $startyear + 1;
	print qq{<option value="$startyear">$startyear-$endyear</option>\n};
	print qq{</select></div>\n};
	
    } else { # more than 1 year to select
	print qq{<option value=""></option>\n};
	foreach my $startyear ( sort keys %startyear ) {
	    my $endyear = $startyear + 1;
	    print qq{<option value="$startyear">$startyear-$endyear</option>\n};
	}
	print qq{</select></div>\n};
    }

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

    exit;
}


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

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

    my ( $papersize, $textwidth, $textheight );
    
    if ( $arr{papersize} eq 'letter' ) {
	$papersize = 'letterpaper';
	$textwidth = $g_letterpaper_textwidth;
	$textheight = $g_letterpaper_textheight;

    } elsif ( $arr{papersize} eq 'legal' ) {
	$papersize = 'legalpaper';
	$textwidth = $g_legalpaper_textwidth;
	$textheight = $g_legalpaper_textheight;

    } elsif ( $arr{papersize} eq 'a4' ) {
	$papersize = 'a4paper';
	$textwidth = $g_a4paper_textwidth;
	$textheight = $g_a4paper_textheight;
    } 


    # Strip out extra passed values
    my $pagebreak = $arr{pagebreak};
    delete $arr{pagebreak};
    delete $arr{papersize};
    my $fontsize = $arr{fontsize};
    delete $arr{fontsize};

    my $startyear = $arr{startyear};
    delete $arr{startyear};
    my $endyear = $startyear + 1;
    my $startdate = qq{$startyear-08-01};
    my $enddate = qq{$endyear-06-30};
    

    my $paperwidth;
    if ( $textwidth =~ m/(\d+)/ ) { $paperwidth = $1; }

    $fontsize = $fontsize . 'pt';


    $shortname = "staffabsent$$";
    $filename = "$shortname.tex";


    open( TEX,">$filename" ) || die "Can't open tex file\n";

    print TEX "\\documentclass[$fontsize,$papersize]{article}
\\usepackage{array,newcent,rotating,colortbl,inputenc,fancyhdr}
$a_latex_header
\\pagestyle{empty}
\\setlength{\\textwidth}{$textwidth}
\\setlength{\\textheight}{$textheight}
\\setlength{\\hoffset}{-36mm}
\\setlength{\\voffset}{-24mm}
\\addtolength{\\evensidemargin}{0mm}
\\addtolength{\\oddsidemargin}{0mm}
\\setlength{\\tabcolsep}{1mm}
\\setlength{\\extrarowheight}{2mm}
\\renewcommand{\\familydefault}{\\sfdefault}\n";

    print TEX qq{\\pagestyle{fancy}
\\lhead{\\footnotesize Staff Absence Report 1}
\\rfoot{\\footnotesize $lex{Printed}: $currdate }
\\cfoot{ } };



    # Note above... a columncolor of 1.00 means white, smaller number is darker.

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

    # Prepare Queries
    my $sth = $dbh->prepare("select * from staff where userid = ?");
#    my $sth1 = $dbh->prepare("select * from staff_absent where userid = ? order by adate desc");
    my $sth1 = $dbh->prepare("select * from staff_absent where 
			     to_days(adate) > to_days('$startdate') and 
			     to_days(adate) < to_days('$enddate') and userid = ?
			     order by adate desc");

    my $tabularlayout = qq{\\begin{tabular}{|p{40 mm}|p{60mm}|p{15mm}|p{60mm}|}};


    foreach my $key ( sort keys %arr ) {
	my $userid = $arr{$key};
	my $daycount;
	my $totallate;
	my $latecount;

	# Get Staff Record
	$sth->execute( $userid );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my $ref = $sth->fetchrow_hashref;
	my %r = %$ref;

	# print TEX qq{\n\n{\\Large $r{firstname} $r{lastname}}\n\\medskip\n\n};

	# Get Their Absences
	$sth1->execute( $userid );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

	# Loop through all absences.
	my $first = 1;
	my $linecount;
	while ( my $aref = $sth1->fetchrow_hashref ) {
	    my %a = %$aref;

	    ($a{other})  = latex_filter( $a{other} );

	    if ( $a{reason} eq 'Late' ) {
		$latecount++;
	    }

	    if ( $a{late} ) {
		$a{late} =~ s/\D//g; # strip any non digit text
		$totallate += $a{late};
		$a{reason} = $a{reason}. qq{ ($a{late} min)};
	    }

	
	    if ( $a{daypart} eq 'All Day' ) {
		$daycount++;
	    } else {
		$daycount += 0.5;
	    }

	    if ( $first ) {
		print TEX $tabularlayout;
		print TEX "\\multicolumn{4}{l}{\\Large\\bf $r{firstname} $r{lastname}}\n\n";
		print TEX "\\\\ \\hline\n\n";

		$first = 0;
	    }


	    print TEX qq{$a{adate} & $a{reason} & $a{daypart} & $a{other}\\\\ \\hline\n};
	    $linecount++;
	    if ( $linecount % 6 == 0 ) { # put in place to split text over pages
		print TEX qq{\\end{tabular}\n\n};
		print TEX $tabularlayout;
		print TEX qq{ \\hline\n}
	    }


	} # end of absences loop

	if ( not $first ) { # end tabular
	    # Total
	    print TEX qq{\\rowcolor[gray]{0.9} $lex{'Total Days'} $daycount &};
	    if ( $latecount ) {
		print TEX qq{$lex{'Late Count'}:$latecount $lex{Total}:$totallate min &};
	    } else {
		print TEX qq{ & };
	    }
	    
	    print TEX qq{& \\\\ \\hline \n};
	    print TEX qq{\\end{tabular}\n};
	    print TEX qq{\\bigskip\n\n};
	}


	if ( $pagebreak ) {
	    print TEX qq{\\newpage\n};
	}

    }  # End of Loop for all staff


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

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

    print qq{<h1><a href="$webdownloaddir/$shortname.pdf">};
    print qq{$lex{'View/Download'} $title</a></h1>\n};
    print qq{<p>[ <a href="$homepage">Main</a> |\n};
    print qq{<a href="$webdownloaddir/pdflog$$.txt">$lex{'View Log File'}</a>\n ]</p>\n};
    print qq{</body></html>\n};

    exit;
    
} # end of showReport

    
#---------------
sub selectStaff {
#---------------

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    # passed the startyear value. Thus get dates between startyear-08-01 and endyear-06-30.

    if ( not $arr{startyear} ) {
	print qq{<h3>Missing School Year</h3>\n};
	print qq{</body></html>\n};
	exit;
    }

    my $startyear = $arr{startyear};
    my $endyear = $startyear + 1;
    my $startdate = qq{$startyear-08-01};
    my $enddate = qq{$endyear-06-30};

#    print "Start:$startdate End:$enddate<br>\n";
    
    # Get default papersize
    my $papersize = $defaultpapersize;
    $papersize =~ s/paper//; # strip off the 'paper' ending;
    $papersize = ucfirst( $papersize );

    # Checked option form
    print qq{<div style="margin:1em;"><form action="$self" method="post">\n};
    print qq{<input type="hidden" name="checked" value="1">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="hidden" name="startyear" value="$startyear">\n};
    print qq{<input type="submit" value="$lex{'Check All'}"></form></div>\n};
    

    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="2">\n};
    print qq{<input type="hidden" name="startyear" value="$startyear">\n};

    my $checked;
    if ( $arr{checked} ) {
	$checked = qq{checked="checked"};
    }

    # Outermost Table
    print qq{<table cellpadding="3" cellspacing="0" border="0">\n};
    print qq{<tr><td style="vertical-align:top;">\n};


    # First Cell as Table
    print qq{<table cellpadding="3" cellspacing="0" border="0" style="margin:0 1em;">\n};
    print qq{<tr><th colspan="2">$lex{Select} $lex{Staff}</th>\n};
    print qq{<th>$lex{Absence}<br>$lex{Count}</th></tr>\n};

    # Find the staff
    $sth = $dbh->prepare("select * from staff order by lastname,firstname");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

    # Get Absence Count
    my $sth1 = $dbh->prepare("select count(*) from staff_absent where 
			     to_days(adate) > to_days('$startdate') and 
			     to_days(adate) < to_days('$enddate') and userid = ?");

    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;

	$sth1->execute( $r{userid} );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my $abscount = $sth1->fetchrow;

	print qq{<tr><td class="la">};
	if ( $abscount ) {
	    print qq{<input type="checkbox" };
	    print qq{name="$r{lastname}$r{firstname}$r{userid}" value="$r{userid}" $checked>};
	}
	print qq{</td>};
	print qq{<td class="bla">$r{lastname}, $r{firstname}</td><td class="cn">$abscount</td></tr>\n};
    }
    print qq{</table>\n};


    # Top Right now
    print qq{</td><td style="vertical-align:top;">\n};


    # Now Set Width and Column Headings,
    print qq{<table cellpadding="3" cellspacing="0" border="0" style="margin:0 1em;">\n};
    print qq{<tr><th>$lex{Settings}</th></tr>\n};
    print qq{<tr><td class="la" colspan="2"><input type="checkbox" name="pagebreak" value="1"> };
    print qq{$lex{'Page Break'} $lex{between} $lex{Staff}</td></tr>\n};

    # Paper Size
    print qq{<tr><td class="ra">$lex{'Paper Size'}</td>\n};
    print qq{<td class="la"><select name="papersize">};
    if ( $papersize ) { 
	my $lcpapersize = lc $papersize;
	print qq{<option value="$lcpapersize">$papersize</option>\n}; 
    }
    print qq{<option value="letter">$lex{Letter}</option>};
    print qq{<option value="a4">$lex{A4}</option>};
    print qq{<option value="legal">$lex{Legal}</option>};
    print qq{</select></td></tr>\n};

    # Font Size
    print qq{<tr><td class="ra">$lex{'Font Size'}</td>\n};
    print qq{<td><select name="fontsize">};
    foreach my $size ( qw( 12 11 10 )) {
	print qq{<option>$size</option>};
    }
    print qq{</select></td></tr>\n};

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


    print qq{</td></tr></table>\n}; # end of outer table.
    print qq{</form></body></html>\n};

    exit;
}


#-----------
sub prHeader {
#-----------

    # Done at the start of a new page.
    print TEX "\\begin{tabular}{G|p{$extracolwidth mm}|";
    for (1..$colcount){	print TEX "p{$wi mm}|"; }
    print TEX "}\n";

    print TEX "\\raggedright\\bf $schoolname ". $title;
    for (1..$colcount + 1){ print TEX "& ";}
    print TEX "\\\\ \n";

    print TEX "\\small\\raggedright $currdate\n\\bigskip ";

    for (1..$colcount + 1){ print TEX "& ";}

    print TEX "\\\\ \n";

    if ($arr{group} eq $lex{Grade} ){
	print TEX "\\Large\\raggedright ". $lex{Grade}. " $curroom & ";
    } else {
	#print TEX "\\bf\\raggedright $teachername ";
	#print TEX $lex{Rm}. " $curroom & ";
	print TEX ' & ';
    }

    my $remainder = $colcount - ( $repeatcount * $entrysize );

    if ( $repeatcount ) {
	for (1..$repeatcount){ # times to loop to print rotated text.
	    foreach my $txt ( @entry ) {
		print TEX "&\\hfil\\rule{6pt}{0pt}\\begin{rotate}{90}$txt";
		print TEX "\\end{rotate}\\hfil";
	    }
	}
    }
    for (1..$remainder){ print TEX "& ";}
    print TEX "\\\\ \\hline\n";

} # End of prHeader




#-----------
sub prRecord { # print normal record (ie. line)
#-----------

    my $userid = shift;
    my $sth = $dbh->prepare("select lastname, firstname from staff where userid = ?");
    $sth->execute( $userid );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    my ( $lastname, $firstname ) = $sth->fetchrow;

    if ($alternatingcolor == 0){
	print TEX "\\rowcolor[gray]{0.93}";
	$alternatingcolor = 1;
    } else { $alternatingcolor = 0;}

    print TEX "\\raggedright {\\bf $lastname}, $firstname";

    print TEX " & ";

    for (1..$colcount){ print TEX "& ";}
    print TEX "\\\\\\hline\n";
}
