#!/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}. ": $@ \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". $lex{'Locker System Search'}. "[ ". $lex{Main}. " \n";
if ( $runmode eq 'main' ) { print " ". $lex{Fees}. "\n"; }
print " ]\n";
print "
". $lex{'Locker System Search'}. "
\n";
if ( not $arr{page} ) {
showStartPage();
} elsif ( $arr{page} == 1 ) {
delete $arr{page};
distributeSearch();
}
#----------------
sub showStartPage {
#----------------
# Now start the form.
print "\n";
print "\n";
exit;
}
#-------------------
sub distributeSearch {
#-------------------
#foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key} \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 "
\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 "
". $lex{Locker};
print "
$locker_num
\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 "
\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 "
\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 "
". $lex{Student}. "
";
print $lex{'No Student(s) Found'}. "
\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 "
\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 "
". $lex{Lock}. "
";
print $lex{None}. "
\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 "
". $lex{Lock};
print "
$lock_num ( $combination ) $pool - $comment
\n";
}
print "
\n";
print "
\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 '
'. $lex{'No Student(s) Found'}. ".
\n";
showStartPage();
exit;
}
print "
\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 "