#!/usr/bin/perl
#  Copyright 2001-2009 Leslie Richardson

#  This file is part of Open Admin for Schools.

use DBI;
use CGI qw\escape unescape\;
use Number::Format qw(round);
use Time::JulianDay;


my %lex = ('Lunch Search' => 'Lunch Search',
	   'Fees' => 'Fees',
	   'Main' => 'Main',
	   'Name' => 'Name',
	   'Date' => 'Date',
	   'Fee Type' => 'Fee Type',
	   'Meal' => 'Meal',
	   'Amount' => 'Amount',
	   'Delete' => 'Delete',
	   'Paid' => 'Paid',
	   'Balance' => 'Balance',
	   'Student' => 'Student',
	   'Edit' => 'Edit',
	   'Delete' => 'Delete',
	   'Error' => 'Error',
	   'No Student(s) Found',
	   'Period' => 'Period',
	   'Reason' => 'Reason',
	   'Subject' => 'Subject',

	   );

my $self = 'lunchsearch.pl';

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

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

# Figure out yesterday
my $prevjd = $currjd - 1;
my $yesterday = join('-', inverse_julian_day($prevjd));


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


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


# Print Page header.
my $title = $lex{'Lunch Search'};
print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{</title><link rel="stylesheet" href="$css" type="text/css">\n};
print qq{ $chartype\n</head><body><a name="top"></a>\n};
print qq{[ <a href="$homepage">$lex{Main}</a> | \n};
print qq{<a href="$feespage">$lex{Fees}</a> ]\n};

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


my $sth;
my $student;

if ( $arr{student} ) {
    $student = $arr{student};
    if ($student =~ /\d+/) {  # we have a student number
	push @studnum, $student; # only a single value
    } else { # we have some text.
	($lastname,$firstname)  = split /\,/, $student;
	$firstname =~ s/^\s*//;
	$lastname =~ s/^\s*//;
	if ($lastname and $firstname){ # both entered.
	    $sth = $dbh->prepare("select studnum from studentall
              where lastname = '$lastname' and 
              firstname = '$firstname'");
	} elsif ($lastname and not $firstname){ # only lastname (no comma)
	    if (length($lastname) == 2){ # search by initials: fi, li.
		$fi = substr($lastname,0,1); $li = substr($lastname,1,1);
		$sth = $dbh->prepare("select studnum from studentall
                 where lastname $sql{like} '$li%' and firstname $sql{like} '$fi%'");
	    } else {
		$sth = $dbh->prepare("select studnum from studentall
                 where lastname = '$lastname' order by firstname");
	    }
	}
    }

    if ($sth) {  # sth should be set above in one of 3 places
	$sth->execute;
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	while ( my $studnum = $sth->fetchrow) {
	    push @studnum, $studnum;
	}
    }

    printStudents(@studnum);
    print "</body></html>\n";
    exit;
}
# End of student only. 


# otherwise we have a date...
if ($arr{date}) {
    if ($arr{date} eq "today"){
	$sth = $dbh->prepare("select * from attend, student 
         where attend.studentid = student.studnum and 
         attend.absdate = '$today' 
         order by attend.absdate desc, student.lastname, 
           student.firstname,attend.period");
    }
    if ($arr{date} eq "yesterday"){
	$sth = $dbh->prepare("select * from attend, student 
         where attend.studentid = student.studnum and 
         attend.absdate = '$yesterday' 
         order by attend.absdate desc, student.lastname, 
         student.firstname, attend.period");
	$yesterday = substr($yesterday,0,4) . "-" . substr($yesterday,4,2) . "-" . substr($yesterday,6,2);
	$currdate = $yesterday;
    }
}

if ($arr{datestart}) { # We select a date range
    $arr{datestart} = "20" . $arr{datestart};
    $arr{dateend} = "20" . $arr{dateend};
    $sth = $dbh->prepare("select * from attend, student 
     where attend.studentid = student.studnum and 
     to_days(attend.absdate) >= to_days($arr{datestart}) and 
     to_days(attend.absdate) <= to_days($arr{dateend}) 
     order by attend.absdate desc, student.lastname, 
      student.firstname, attend.period");
}

if (not $sth) { # if no dates, then halt with Error
    print $lex{'No Student(s) Found'};
    print "</body></html>\n";
    exit 0;
}

$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
$rows = $sth->rows;


print "<p>$currdate</p>\n";

print "<table cellpadding=\"3\" border=\"1\" cellspacing=\"0\">
 <caption>$studentname</caption>";
print "<tr><th>".$lex{Student}."</th><th>".$lex{Date}."</th><th>". $lex{Subject};
print "</th><th>".$lex{Period}. q{/}. $lex{Reason}."</th><th>".$lex{Edit};
print "</th><th>".$lex{Delete}."</th></tr>\n";

$currstud = -1;
for ($i=1; $i <= $rows; ++$i) {
    @arr = $sth->fetchrow;
    $name = escape($arr[9]." ".$arr[8]);

    $sth1 = $dbh->prepare("select description from subject 
     where subjsec = '$arr[5]'"); 
    $sth1->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    $desc = $sth1->fetchrow;
  
    if (not $desc){
	$desc = "Gr:$arr[12]";
    } else {
	$desc = $desc."($arr[5])";
    }

    $oldstud = $currstud; 
    $currstud = $arr[1];
    if ($currstud != $oldstud){
	if ($color eq "blue"){ 
	    $color = "gray";
	} else { 
	    $color = "blue";} 
    }
    print "<tr class=\"$color\"><td><b>$arr[1]:</b> $arr[8], $arr[9]</td>\n";
    print "<td>$arr[2]</td><td>$desc</td><td>$arr[4]- $arr[3]</td>\n";
    print "<td><a href=\"atted.pl?recnum=$arr[0]&name=$name\">\n";
    print $lex{Edit}. "</a></td><td><a href=\"attdel.pl?recnum=$arr[0]&name=$name\">\n";
    print "Delete</a></td></tr>\n";
}

print "</table></center></body></html>";


#----------------
sub printStudents {
#----------------

    my @studs = @_;
    my $sth = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");

    # print the anchor line of names

    if ( $#studs > 0 ) { # Display a jump to line
	my $first = 1;
	foreach my $studnum (@studs) {
	    if ($first) { print "<p>[ "; $first = 0; } else { 
		print ' | ';
	    }

	    $sth->execute( $studnum );
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	    my ($lastname, $firstname) = $sth->fetchrow;
	    print "<a href=\"#$studnum\">$firstname $lastname</a>\n";
	}
	print " ]</p>\n";

    }


    my $first = 1;
    foreach my $studnum (@studs) {
	$sth->execute($studnum);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my ($lastname, $firstname) = $sth->fetchrow;

	if (not $first) { print "</table>\n";} else { $first = 0; }

	print "<a name=\"$studnum\"></a>\n";
	print "<div style=\"font-size:130%; font-weight:bold; padding:0.8em;\">";
	print "$firstname $lastname&nbsp;&nbsp;[ <a href=\"#top\">Top</a> ]</div>\n";
	print "<table cellspacing=\"0\" cellpadding=\"3\" border=\"1\">\n";

	print "<tr><th>". $lex{Name}. "</th><th>". $lex{Date}. "</th><th>\n";
	print $lex{'Fee Type'}. "</th><th>". $lex{Meal}. "</th><th>". $lex{Amount};
	print "</th><th>". $lex{Paid}. "</th><th>". $lex{Balance}. "</td></tr>\n";


	my $sth1 = $dbh->prepare("select * from attend_lunch
          where studnum = ? $select order by attdate ");
	$sth1->execute($studnum);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my $name = escape($firstname.' '.$lastname);	

	my $currdate = -1;
	my $balance;

	while ( my ( $id,$studnum,$attdate,$feeType,$mealType,$amount,$paid,$posted) = 
		$sth1->fetchrow){

	    $prevdate = $currdate; $currdate = $absdate;
	    if ($prevdate ne $currdate){ # color switch
		if ( not $attrCol ) { 
		    $attrCol = 'style="background-color:#D8D8D8;"';
		} else { 
		    $attrCol = '';
		}
	    }

	    $balance += $amount;

	    $displayBalance = round( $balance, 2);
	    $displayAmount = round( $amount, 2);

	    print "<tr class=\"$colcolor\"><td>$firstname $lastname ($studnum)</td>\n";
	    print "<td>$attdate</td><td>$feeType</td><td>$mealType</td><td>$amount</td>\n";
	    print "<td>$paid</td><td align=\"right\">$displayBalance</td></tr>\n";

	} # End of Lunch Records

	print "</table>\n"; # final table

    }

} # End of printStudents
