#!/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}. ": $@<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{'Assign Available Locks'}. "</title>\n";
print "<link rel=\"stylesheet\" href=\"$css\" type=\"text/css\">\n";
print "</head><body><a name=\"top\"></a>";
print "[ <a href=\"$homepage\">". $lex{Main}. "</a> \n";
if ( $runmode eq 'main' ) { 
    print "| <a href=\"$feespage\">". $lex{Fees}. "</a> ]\n"; 
} else { print "]\n"; }


print "<h1>". $lex{'Assign Available Locks'}. "</h1>\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 "<h1>". $lex{'No Entry'}. "</h1>\n";
    print "</body></html>\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<br>\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<br>\n";
	push @locks, $lock_num;
    }
}

if ( not @locks ) {
    print "<p>". $lex{'No Records Found'}. "</p>\n";
    print "</body></html>\n";
    die;
}

# print "Locks:", @locks ,"<br>\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, "<br>\n";


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


print "<table border=\"1\" cellpadding=\"3\" cellspacing=\"0\">\n";
print "<tr><th>". $lex{'Lock Number'}. "</th><th>\n";
print $lex{Combination}. "</th><th>". $lex{Pool}. "</th><th>";
print $lex{Comment} . "</th><th>". $lex{Student}. "</th></tr>\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 "<tr class=\"$colcolor\"><td>$lock_num</td>\n";
    print "<td>$combination</td><td>$pool</td><td>$comment</td>\n";
   
    # we need a dropdown list for every lock
    print "<td><select name =\"$lock_num\">";
    print "<option></option>";
    foreach my $studnum ( @students ) {
	$sth1->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ( $lastname, $firstname, $grade ) = $sth1->fetchrow;

        print "<option><b>$lastname,</b> $firstname ($studnum) $grade</option>";
    }
    print "</select></td></tr>\n";

}

print "</table>\n";
print "<input type=\"submit\" value=\"". $lex{Assign}. "\">\n";
print "</form>";

if ( $runmode eq 'main' ) { print "[ <a href=\"$feespage\">". $lex{Fees}. "</a> ]\n"; }

print "[ <a href=\"#top\">". $lex{Top}. "</a> ]</p>\n";
print "</body></html>\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 "<table border=\"1\" cellpadding=\"3\" cellspacing=\"0\">\n";
    print "<tr><th>". $lex{Lock}. "</th><th>\n";
    print $lex{Locker}. "</th><th>". $lex{Student}. "</th></tr>\n";


    foreach my $lock_num ( sort keys %arr ) { 
	#print "Key: $lock_num Value: $arr{$lock_num}<br>\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 "<tr><td>$lock_num</td><td>$locker_num</td><td>";
	    print "$lastname, $firstname ($studnum)</td></tr>\n";
	}
    }

    print "</table>";
    print "</body></html>\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 "<form action=\"$self\" method=\"post\">\n";
    print "<input type=\"hidden\" name=\"page\" value=\"1\">\n";
    print "<table cellspacing=\"0\" cellpadding=\"4\" border=\"0\">\n";

    # Student Group
    print "<tr><td align=\"right\"><i>". $lex{Group}. "</i></td><td>";
    print $lex{Grade}. " <select name=\"grade\"><option></option>\n";
    foreach my $grade ( sort { $a <=> $b} @grades ) {
	print "<option>$grade</option>";
    }
    print "</select>\n&nbsp;<b>". $lex{or}. "</b>&nbsp;";

    print $lex{Homeroom}. " <select name=\"homeroom\"><option></option>\n";
    foreach my $homeroom ( sort { $a <=> $b} @homerooms ) {
	print "<option>$homeroom</option>";
    }
    print "</select> ". $lex{'Blank=All'}. "</td></tr>\n";


    print "<tr><td align=\"center\" colspan=\"2\">";
    print "<input type=\"submit\" value=\"". $lex{Continue}. "\"></td></tr>\n";
    print "</table></form>\n";

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

    exit;
}
