#! /usr/bin/perl
#  Copyright 2001-2011 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',
	   'No Student(s) Found' => 'No Student(s) Found',
	   'View/Download' => 'View/Download',
	   'Textbook' => 'Textbook',
	   'Records' => 'Records',
	   'Report' => 'Report',
	   'View Log File' => 'View Log File',
	   'Continue' => 'Continue',
	   'Homeroom' => 'Homeroom',
	   'Grade' => 'Grade',
	   'Blank=All' => 'Blank=All',
	   'Select' => 'Select',
	   'Students' => 'Students',
	   'Paper Size' => 'Paper Size',
	   'Letter' => 'Letter',
	   'Legal' => 'Legal',
	   'A4' => 'A4',
	   'Font Size' => 'Font Size',
	   'Error' => 'Error',
	   'Fees' => 'Fees',
	   'Sort by' => 'Sort by',
	   'In' => 'In',
	   'Out' => 'Out',
	   'All' => 'All',
	   'Not Found' => 'Not Found',
	   'Copies' => 'Copies',
	   'Student' => 'Student',
	   'Barcode' => 'Barcode',
	   'Id' => 'Id',
	   'Show' => 'Show',
	   'without' => 'without',
	   'Status' => 'Status',
	   'Sign Out' => 'Sign Out',

	   );

my $self = 'tb_report.pl';

use DBI;
use CGI;
use Cwd;


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;


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

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



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

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

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

print "<h1>$title</h1>\n";


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

} elsif ( $arr{page} == 1 ) {
    delete $arr{page};
    printReport();
}



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

    print "<form action=\"$self\" method=\"post\">\n";
    print "<input type=\"hidden\" name=\"page\" value=\"1\">\n";

    # Start Table
    print "<table cellpadding=\"3\" cellspacing=\"0\" border=\"0\">\n";

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

    # Sort order
    print "<tr><td class=\"bra\">$lex{'Sort by'}</td><td class=\"la\">";
    print " <select name=\"sort\"><option value=\"textbook\">$lex{Textbook}</option>";
    print "<option value=\"student\">$lex{Student}</option></select></td></tr>\n";

    # Textbook Records to print:  All, In, Out
    print "<tr><td class=\"bra\">$lex{Textbook} $lex{Records}</td><td class=\"la\">";
    print " <select name=\"mode\"><option value=\"all\">$lex{All}</option><option value=\"in\">$lex{In}</option>";
    print "<option value=\"out\">$lex{Out}</option></select></td></tr>\n";

    # Student Records to print
    print "<tr><td class=\"bra\">$lex{Student} $lex{Records}</td><td class=\"la\">";
    print "<select name=\"group\"><option>$lex{Grade}</option>";
    print "<option>$lex{Homeroom}</option></select> \n";
    print "<input type=\"text\" name=\"groupvalue\" size=\"8\">\n";
    print "$lex{'Blank=All'}</td></tr>\n";

    # Records without Copies
    print "<tr><td class=\"bra\">$lex{Show} $lex{Records} $lex{without} $lex{Copies}</td><td class=\"la\">";
    print "<input type=\"checkbox\" name=\"showrecord\" value=\"1\"></td></tr>\n";


    # Paper Size
    print "<tr><td class=\"bra\">". $lex{'Paper Size'}. "</td><td>";
    print " <select name=\"papersize\"><option>". $lex{Letter}. "</option>\n";
    print "<option>". $lex{Legal}. "</option><option>". $lex{A4};
    print "</option></select></td></tr>\n";

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

    # Continue / Submit button
    print "<tr><td></td><td><input type=\"submit\" value=\"$lex{Continue}\"></td></tr>\n";
    print "</table></form></body></html>\n";

    exit;

}



#--------------
sub printReport {
#--------------

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

    # Title setup: lookup either way
    my %title = ();
    my %id2title = ();
    my $sth = $dbh->prepare("select id, title, author, pubdate from book_title");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my ($id, $title, $author, $pubdate ) = $sth->fetchrow ) {
	$title{"$title ($pubdate)"} = $id;
	$id2title{$id} = "$title ($pubdate)";
    }


    # TeX Setup
    my $fontsize = $arr{fontsize}. 'pt';

    # Set Paper Size, text width and height
    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;
    }
    delete $arr{papersize}; # no longer needed.


    # Filter for Passed values that might crash LaTeX 
    foreach my $key ( keys %arr ) {
	( $arr{$key} ) = latex_filter( $arr{$key} );
    }


    my $shortname = "tbreport$$";
    my $filename = "$shortname.tex";

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

    print TEX "\\documentclass[$fontsize,$papersize ]{article}
\\usepackage{array,fancyhdr,colortbl,inputenc}
$a_latex_header
\\pagestyle{empty}
\\setlength{\\textwidth}{$textwidth}
\\setlength{\\textheight}{$textheight}

\\setlength{\\hoffset}{-36mm}
\\setlength{\\voffset}{-30mm}
\\addtolength{\\evensidemargin}{0mm}
\\addtolength{\\oddsidemargin}{0mm}
\\setlength{\\tabcolsep}{1mm}
\\setlength{\\extrarowheight}{2mm}

\\pagestyle{fancy}
\\lhead{$schoolname -- $schoolyear}
\\rfoot{ $currdate }\n";


    print TEX "\\begin{document}\n";
    print TEX "{\\Large\\bf $title}\n\n";

    if ( $arr{sort} eq 'textbook' ) { # textbook report

	my $sth = $dbh->prepare("select id, barcode from book_copy 
          where titleid = ? order by barcode");

	foreach my $title ( sort keys %title ) { # print out by title.
	    my $titleid = $title{$title};

	    # Get Copy Information
	    $sth->execute( $titleid );
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }	    

	    my $first = 1;
	    while ( my ( $copyid, $barcode ) = $sth->fetchrow ) {

		# Check Copy Status
		my $sth1 = $dbh->prepare("select count(*) from book_signout where copyid = ? and returndate is NULL");
		$sth1->execute($copyid);
		if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
		my $count = $sth1->fetchrow;
		my $copystatus;
		if ( $count ) { $copystatus = $lex{Out}; } else { $copystatus = $lex{In}; }

		if ( ( $arr{mode} eq 'out' and not $count) or
		     ( $arr{mode} eq 'in' and $count ) ) {
		    next; # skip this record
		}


		if ( $first ) { # print Header
		    print TEX "\\begin{tabular}{|l|l|l|} \\hline\n";
		    print TEX "\\rowcolor[gray]{0.80}\\multicolumn{3}{|l|}{ $title} \\\\ \\hline\n";
		    print TEX "\\rowcolor[gray]{0.92}$lex{Barcode} &$lex{Id} &$lex{Status} \\\\ \\hline\n";
		}

		print TEX "$barcode & $copyid & $copystatus \\\\ \\hline\n";
		$first = 0;
	    }

	    if ( $first and $arr{showrecord} ) { # print records for title with no copies.
		print TEX "{\\it $title -- $lex{Copies} $lex{'Not Found'}}\\medskip\n\n";
	    }

	    if ( not $first ) { # close tabular
		print TEX "\\end{tabular}\\medskip\n\n";
	    }

	} # end of titles.



    } else { # student report

	# sort by student
	my ($found, $sth);

	my $localgroup;
	if ( $group eq 'grade' ) {
	    $localgroup = 'grade';
	} else { $localgroup = 'homeroom'; }

	my $select;
	if ( $arr{groupvalue} ) {
	    # check if group value found.
	    my $sth1 = $dbh->prepare("select distinct $localgroup from student 
             where $localgroup is not NULL and $localgroup != ''");
	    $sth1->execute;
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	    while ( my $grp = $sth1->fetchrow ) {
		if ( $grp eq $arr{groupvalue} ) {
		    $found = 1;
		    last;
		}
	    }

	    if ( not $found ) {
		print "<h3>$lex{Grade}/$lex{Homeroom} $lex{'Not Found'}</h3>\n";
		print "</body></html>\n";
		exit;
	    }

	    $sth = $dbh->prepare("select lastname, firstname, studnum from student
             where $localgroup = ? order by lastname, firstname");
	    $sth->execute( $arr{groupvalue} );
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

	} else { # do all students

	    $sth = $dbh->prepare("select lastname, firstname, studnum from student
             order by lastname, firstname");
	    $sth->execute;
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	}


	# Get Signout Info
	$sth1 = $dbh->prepare("select copyid, signout, start_condition from book_signout 
          where studnum = ? and returndate is NULL");

	# Get Copy Info
	$sth2 = $dbh->prepare("select titleid, barcode from book_copy where id = ?");

	# read all student data.
	while ( my ($lastname, $firstname, $studnum ) = $sth->fetchrow ) {

	    # Get Signout information, if any.
	    $sth1->execute( $studnum );
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

	    # Loop through signouts.
	    my $first = 1;
	    while ( my ( $copyid, $signout, $start_condition ) = $sth1->fetchrow ) {

		if ( $first ) { # print Header
		    print TEX "\\begin{tabular}{|l|l|l|} \\hline\n";
		    print TEX "\\multicolumn{3}{|l|}{ $firstname $lastname ($studnum)} \\\\ \\hline\n";

		}

		# Get Copy Info
		$sth2->execute( $copyid );
		if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
		my ( $titleid, $barcode ) = $sth2->fetchrow;

		my $title = $id2title{ $titleid };
		print TEX "$title & $barcode & $signout \\\\ \\hline\n";
		$first = 0;
	    } # end of this book


	    if ( $first and $arr{showrecord} ) { # print records for title with no copies.
		print TEX "{\\it $firstname $lastname ($studnum) -- $lex{'Sign Out'} -- $lex{'Not Found'}}\\medskip\n\n";
	    }

	    if ( not $first ) { # close tabular
		print TEX "\\end{tabular}\\medskip\n\n";
	    }


	} # end of this student


    } # end of student mode.


    # Finish Document
    print TEX "\\end{document}";
    close TEX;

    # Solve download location issues with cgi vs tcgi..
    # Get current dir so know what CSS to display;
    if (getcwd() =~ /tcgi/){ # we are in tcgi
	$downloaddir = $tchdownloaddir;
	$webdownloaddir = $tchwebdownloaddir;
    }

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

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

}
