#!/usr/bin/perl
#  Copyright 2001-2009 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 = (
	   'Locker System Search' => 'Locker System Search',
	   'Fees' => 'Fees',
	   'Main' => 'Main',
	   'Lock' => 'Lock',
	   'Locker' => 'Locker',
	   'Search' => 'Search',
           'Locker Number' => 'Locker Number',
           'Lock Number' => 'Lock Number',
           'Student' => 'Student',
           'is not assigned' => 'is not assigned',
	   'Error' => 'Error',
	   'Unassigned' => 'Unassigned',
	   'Last,First/Last/Initials/Studnum' => 'Last,First/Last/Initials/Studnum',
	   'No Student(s) Found' => 'No Student(s) Found',
	   'Cleaned' => 'Cleaned',
	   'Partners' => 'Partners',
	   'Grade' => 'Grade',
	   'Gender' => 'Gender',
	   'None' => 'None',
	   'Not Found' => 'Not Found',

	   );

my $self = 'lockersearch.pl';

use DBI;
use CGI;
use Cwd;


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

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

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


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

# Print Page Header
print "$doctype\n<html><head><title>". $lex{'Locker System Search'}. "</title>
<link rel=\"stylesheet\" href=\"$css\" type=\"text/css\">
</head><body><a name=\"top\"></a>[ <a href=\"$tchpage\">". $lex{Main}. "</a> \n";
if ( $runmode eq 'main' ) { print " <a href=\"$feespage\">". $lex{Fees}. "</a>\n"; }
print " ]\n";

print "<center><h1>". $lex{'Locker System Search'}. "</h1>\n";

if ( not $arr{page} ) {
    showStartPage();
} elsif ( $arr{page} == 1 ) {
    delete $arr{page};
    distributeSearch();
}


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

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

    print "<table border=\"0\" cellpadding=\"4\" cellspacing=\"0\">\n";

    print "<tr><th>". $lex{Search}. "</th>"; 
    print "<th><input type=\"text\" name=\"searchterm\" size=\"20\"></th></tr>\n";

    print "<tr><td colspan=\"2\">";
    print "<input type=\"submit\" name=\"type\" value=\"". $lex{Lock}. "\">\n";
    print "<input type=\"submit\" name=\"type\" value=\"". $lex{Locker}. "\">\n";
    print "<input type=\"submit\" name=\"type\" value=\"". $lex{Student}. "\">\n";
    print "</td></tr></table>\n";

    print $lex{Student}. q{ (}. $lex{'Last,First/Last/Initials/Studnum'}. ")\n";

    print "</form>\n";
    print "</body></html>\n";

    exit;

}


#-------------------
sub distributeSearch {
#-------------------

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

    if ( $arr{type} eq $lex{Lock} ) { # search for Lock
	lockSearch( $arr{searchterm} );

    } elsif ( $arr{type} eq $lex{Locker} ) { # search for Locker
	lockerSearch( $arr{searchterm} );

    } elsif ( $arr{type} eq $lex{Student} ) { # search for Student
	studentSearch( $arr{searchterm} );

    }

    exit;

}



#-------------
sub lockSearch { # search for this lock and it's status.
#-------------

    my $searchterm = shift;

    my $sth = $dbh->prepare("select * from lok_lock where lock_num = ?");
    $sth->execute( $searchterm );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    my $rows = $sth->rows;

    if ( $rows < 1 ) {
	print "<p>" . $lex{'Lock Number'} . " <b>$searchterm</b> "; 
	print $lex{'Not Found'}. "</p>\n";
	showStartPage();
	exit;

    }

    print "<table border=\"1\" cellpadding=\"4\" cellspacing=\"0\">\n";

    $sth = $dbh->prepare("select locker_num from lok_link where lock_num = ?");
    $sth->execute( $searchterm );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    my $lockercount = $sth->rows;

    while ( my $locker_num = $sth->fetchrow ) {
	print "<tr><th align=\"right\">". $lex{Locker};
	print "</th><td><b>$locker_num</b></td></tr>\n";

	my $sth1 = $dbh->prepare("select lastname, firstname from student as s,
         lok_rlink as l where l.studnum = s.studnum and l.locker_num = ?");
	$sth1->execute( $locker_num );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my ( $lastname, $firstname ) = $sth1->fetchrow ) {
	    print "<tr><th align=\"right\">". $lex{Student};
	    print "</th><td>$firstname $lastname</td></tr>\n";
	}
    }

    if ( not $lockercount ) {
	print "<tr><td>". $lex{Lock}. " <b>$searchterm</b> ". $lex{Unassigned}. "</td></tr>\n";
    }
    
    print "</table>\n";
    print "<p>&nbsp;</p>\n";

    showStartPage();

    exit;

}



#---------------
sub lockerSearch { # search for this locker and it's status.
#---------------

    my $searchterm = shift;

    my $sth = $dbh->prepare("select * from lok_locker where locker_num = ?");
    $sth->execute( $searchterm );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    my $rows = $sth->rows;

    if ( $rows < 1 ) {
	print "<p>" . $lex{'Locker Number'} . " <b>$searchterm</b> "; 
	print $lex{'is not assigned'}. "</p>\n";
	showStartPage();
	exit;
    }

    print "<table border=\"1\" cellpadding=\"4\" cellspacing=\"0\">\n";

    # Find students linked to locker.
    $sth = $dbh->prepare("select studnum from lok_rlink where locker_num = ?");
    $sth->execute( $searchterm );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    my $rows = $sth->rows;
    if ( $rows < 1 ) { 
	print "<tr><th align=\"right\">". $lex{Student}. "</th><td>";
	print $lex{'No Student(s) Found'}. "</td></tr>\n";
    } else { # we have some students

	$sth1 = $dbh->prepare("select lastname, firstname, grade, sex from studentall
          where studnum = ?"); 

	while ( my $studnum = $sth->fetchrow ) {
	    # Print Student Info
	    $sth1->execute( $studnum ) ;
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my  ( $lastname, $firstname, $grade, $sex ) = $sth1->fetchrow;
	    print "<tr><th align=\"right\">". $lex{Student};
	    print "</th><td>$firstname $lastname ($studnum) ";
	    print $lex{Grade}. " $grade - ". $lex{Gender}. " $sex</td></tr>\n";
	}
    }

    # Find lock linked to locker.
    $sth = $dbh->prepare("select lock_num from lok_link where locker_num = ?");
    $sth->execute( $searchterm );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    my $lock_num = $sth->fetchrow;

    if ( not $lock_num ) { 
	print "<tr><th align=\"right\">". $lex{Lock}. "</th><td>";
	print $lex{None}. "</td></tr>\n";
    } else { # we have a lock;
	# Find More info about lock.
	$sth = $dbh->prepare("select combination, pool, comment from lok_lock 
          where lock_num = ?");
	$sth->execute( $lock_num );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ( $combination, $pool, $comment ) = $sth->fetchrow;

	print "<tr><th align=\"right\">". $lex{Lock};
	print "</th><td>$lock_num ( $combination ) $pool - $comment</td></tr>\n";
    }

    print "</table>\n";
    print "<p>&nbsp;</p>\n";

    showStartPage();

    exit;

}

#----------------
sub studentSearch {
#----------------

    my $student = shift;
    my $sth;

    if ( $student =~ /^\d+/) {  # we have a student number
	$sth = $dbh->prepare("select studnum from studentall where studnum = ?");
	$sth->execute( $student ) ;

    } else { # we have words hopefully with a comma
	($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 = ? and firstname = ?");
	    $sth->execute( $lastname, $firstname ) ;

	} elsif ($lastname and not $firstname){ # only lastname (no comma)
	    if ( length($lastname) == 2 ){ # search by initials: fi, li.
		my $fi = substr($lastname,0,1). '%';
		my $li = substr($lastname,1,1). '%';
		$sth = $dbh->prepare("select studnum from studentall 
                  where lastname $sql{like} ? and firstname $sql{like} ?");
		$sth->execute( $li, $fi ) ;

	    } else {
		$sth = $dbh->prepare("select studnum
                 from studentall where lastname = ?");
		$sth->execute( $lastname ) ;
	    }
	} 

    } # end of character search

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

    if ( $rows < 1 ) { # student not found
	print '<p><b>'. $lex{'No Student(s) Found'}. ".</b></p>\n";
	showStartPage();
	exit;
    }


    print "<table border=\"1\" cellpadding=\"4\" cellspacing=\"0\">\n";

    $sth1 = $dbh->prepare("select locker_num from lok_rlink where studnum = ?"); 
    $sth2 = $dbh->prepare("select cleaned, location, comment from lok_locker 
      where locker_num = ?");
    $sth3 = $dbh->prepare("select lock_num from lok_link where locker_num = ?"); 
    $sth4 = $dbh->prepare("select combination, pool, comment from lok_lock 
      where lock_num = ?");
    $sth5 = $dbh->prepare("select studnum from lok_rlink where locker_num = ?"); 
    $sth6 = $dbh->prepare("select lastname, firstname, grade, sex from studentall
      where studnum = ?"); 



    while ( my $studnum = $sth->fetchrow ) { # do all students

	# Print Student Info
	$sth6->execute( $studnum ) ;
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my  ( $lastname, $firstname, $grade, $sex ) = $sth6->fetchrow;
	print "<tr><th align=\"right\">". $lex{Student};
	print "</th><td style=\"background-color:#666;color:white;\">$firstname $lastname ($studnum) ";
	print $lex{Grade}. " $grade - ". $lex{Gender}. " $sex</td></tr>\n";

	# find locker, then lock, then other partners, possibly, in locker.
	$sth1->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

	while ( my $locker_num = $sth1->fetchrow ) {

	    #locker characteristics
	    $sth2->execute( $locker_num ) ;
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my ( $cleaned, $location, $comment ) = $sth2->fetchrow;
	    if ( $cleaned ) { $cleaned = $lex{Cleaned} } else { $cleaned = ''; }

	    print "<tr><th align=\"right\">". $lex{Locker};
	    print "</th><td>$locker_num ($location) $cleaned - $comment</td></tr>\n";
	    
	    # locker lock
	    $sth3->execute( $locker_num ) ;
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my  $lock_num = $sth3->fetchrow;

	    $sth4->execute( $lock_num ) ;
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my  ( $combination, $pool, $comment ) = $sth4->fetchrow;
	    print "<tr><th align=\"right\">". $lex{Lock}. "</th><td>";
	    if ( $lock_num ) {
		print "$lock_num ($combination) $pool - $comment</td></tr>\n";
	    } else {
		print $lex{None}. "</td></tr>\n";
	    }

	    # locker Partners
	    my $partnercount;
	    $sth5->execute( $locker_num ) ;
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

	    while ( my $sn = $sth5->fetchrow ) {
		if ( $sn == $studnum ) { next; }
		$sth6->execute( $studnum ) ;
		if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		my  ( $lastname, $firstname, $grade, $sex ) = $sth6->fetchrow;
		print "<tr><th align=\"right\">". $lex{Partners};
		print "</th><td>$firstname $lastname ($studnum) ";
		print $lex{Grade}. " $grade - ". $lex{Gender}. " $sex</td></tr>\n";
		$partnercount++;
	    }
	    if ( not $partnercount) {
		print "<tr><th align=\"right\">". $lex{Partners};
		print "</th><td>". $lex{None}. "</td></tr>\n";
	    }


	} # locker loop (ie. all lockers... hopefully just 1 )

    } # student loop - all students that match.

    print "</table><p>&nbsp;</p>\n";
    showStartPage();

    exit;

}
