#!/usr/bin/perl # Copyright 2001-2010 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 = ( 'Fees' => 'Fees', 'Main' => 'Main', 'No Records Found' => 'No Records Found', 'Top' => 'Top', 'Lock Number' => 'Lock Number', 'Combination' => 'Combination', 'Comment' => 'Comment', 'Pool' => 'Pool', 'Assign Available Locks' => 'Assign Available Locks', 'Student' => 'Student', 'Assign' => 'Assign', 'Group' => 'Group', 'Grade' => 'Grade', 'Homeroom' => 'Homeroom', 'Blank=All' => 'Blank=All', 'Continue' => 'Continue', 'No Entry' => 'No Entry', 'Lock' => 'Lock', 'Locker' => 'Locker', 'Error' => 'Error', 'or' => 'or', ); my $self = 'lockassign.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{'Assign Available Locks'}. "\n"; print "\n"; print ""; print "[ ". $lex{Main}. " \n"; if ( $runmode eq 'main' ) { print "| ". $lex{Fees}. " ]\n"; } else { print "]\n"; } print "

". $lex{'Assign Available Locks'}. "

\n"; if ( not $arr{page} ) { showStartPage(); } elsif ( $arr{page} == 2 ) { delete $arr{page}; updateRecords(); } my ( $group, $groupid ); if ( $arr{grade} ) { $group = 'grade', $groupid = $arr{grade}; } elsif ( $arr{homeroom} ) { $group = 'homeroom'; $groupid = $arr{homeroom}; } else { # no entry print "

". $lex{'No Entry'}. "

\n"; print "\n"; die; } # find unassigned locks my @locks = (); # available locks my $sth1 = $dbh->prepare("select count(*) from lok_link where lock_num = ?"); my $sth = $dbh->prepare("select lock_num from lok_lock order by lock_num"); $sth->execute; if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my $lock_num = $sth->fetchrow ) { #print "Lock Number: $lock_num
\n"; # Check if lock assigned $sth1->execute( $lock_num ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $count = $sth1->fetchrow; if ( $count < 1 ) { # not assigned # print "Available: $lock_num
\n"; push @locks, $lock_num; } } if ( not @locks ) { print "

". $lex{'No Records Found'}. "

\n"; print "\n"; die; } # print "Locks:", @locks ,"
\n"; # get our list of students for that student grouping with a locker but no lock. $sth = $dbh->prepare("select s.studnum, l.locker_num from student as s, lok_rlink as l where $group = ? and s.studnum = l.studnum order by $group, lastname, firstname"); $sth->execute( $groupid ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } $sth1 = $dbh->prepare("select count(*) from lok_link where locker_num = ?"); my @students = (); while ( my ( $studnum, $locker_num ) = $sth->fetchrow ) { # Check if locker has a lock assigned $sth1->execute( $locker_num ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $count = $sth1->fetchrow; if ( $count < 1 ) { # not assigned push @students, $studnum; } } # print "Students:", @students, "
\n"; print "
\n"; print "\n"; print "\n"; print "\n"; my $colcolor = 'gray'; # tr colors are blue or gray; $sth = $dbh->prepare("select combination, pool, comment from lok_lock where lock_num = ?"); $sth1 = $dbh->prepare("select lastname, firstname, grade from studentall where studnum = ?"); foreach my $lock_num ( @locks ) { if ($colcolor eq 'blue'){ $colcolor = 'gray'; } else { $colcolor = 'blue'; } $sth->execute( $lock_num ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ( $combination, $pool, $comment ) = $sth->fetchrow; print "\n"; print "\n"; # we need a dropdown list for every lock print "\n"; } print "
". $lex{'Lock Number'}. "\n"; print $lex{Combination}. "". $lex{Pool}. ""; print $lex{Comment} . "". $lex{Student}. "
$lock_num$combination$pool$comment
\n"; print "\n"; print "
"; if ( $runmode eq 'main' ) { print "[ ". $lex{Fees}. " ]\n"; } print "[ ". $lex{Top}. " ]

\n"; print "\n"; #---------------- sub updateRecords { # assign locks to lockers; #---------------- my $sth = $dbh->prepare("select locker_num from lok_rlink where studnum = ?"); my $sth1 = $dbh->prepare("select lastname, firstname from student where studnum = ?"); my $sth3 = $dbh->prepare("insert into lok_link values( ?, ? )"); # locker_num, lock_num print "\n"; print "\n"; foreach my $lock_num ( sort keys %arr ) { #print "Key: $lock_num Value: $arr{$lock_num}
\n"; if ( $arr{$lock_num} ) { # if we have a selection... my ( $head, $tail ) = split /\(/, $arr{$lock_num}; my ( $studnum, $rest ) = split /\)/, $tail; # Get Locker Number $sth->execute( $studnum ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $locker_num = $sth->fetchrow; # Get Name $sth1->execute( $studnum ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ( $lastname, $firstname ) = $sth1->fetchrow; # Insert Lock (Assign Lock) if ( $locker_num ) { $sth3->execute( $locker_num, $lock_num ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } } print "\n"; } } print "
". $lex{Lock}. "\n"; print $lex{Locker}. "". $lex{Student}. "
$lock_num$locker_num"; print "$lastname, $firstname ($studnum)
"; print "\n"; exit; } #---------------- sub showStartPage { #---------------- # Find all the grades my @grades = (); my $sth = $dbh->prepare("select distinct grade from student"); $sth->execute; if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } while ( my $grade = $sth->fetchrow ) { push @grades, $grade; } # Find all the homerooms my @homerooms = (); $sth = $dbh->prepare("select distinct homeroom from student"); $sth->execute; if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } while ( my $homeroom = $sth->fetchrow ) { push @homerooms, $homeroom; } print "
\n"; print "\n"; print "\n"; # Student Group print "\n"; print "\n"; print "
". $lex{Group}. ""; print $lex{Grade}. " \n ". $lex{or}. " "; print $lex{Homeroom}. " ". $lex{'Blank=All'}. "
"; print "
\n"; print "\n"; exit; }