#!/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',
	   'Top' => 'Top',
           'Locker Number' => 'Locker Number',
           'Assign Lockers' => 'Assign Lockers',
           'Student' => 'Student',
	   'Error' => 'Error',
	   'Student Group' => 'Student Group',
	   'Students per Locker' => 'Students per Locker',
	   'Match Gender' => 'Match Gender',
	   'Locker Location' => 'Locker Location',
	   'Starting Locker' => 'Starting Locker',
	   'Ending Locker' => 'Ending Locker',
	   'Grade' => 'Grade',
	   'Homeroom' => 'Homeroom',
	   'or' => 'or',
	   'Continue' => 'Continue',
	   'if' => 'if',
	   'No Lockers Found' => 'No Lockers Found',
	   'Lock Pool' => 'Lock Pool',
	   'Assign Locks' => 'Assign Locks',
	   'Blank=All' => 'Blank=All',
	   'Too many students for locker' => 'Too many students for locker',
	   'No Lock' => 'No Lock',
	   'All Students have Lockers' => 'All Students have Lockers',
	   'Assigned' => 'Assigned',
	   'Lock' => 'Lock',
	   'Locker' => 'Locker',

	   );

my $self = 'lockerassign.pl';
my $maxstudentsperlocker = 6; 

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

print "<h1>". $lex{'Assign Lockers'}. "</h1>\n";


if ( not $arr{page} ) {
    showStartPage();
} elsif ( $arr{page} == 1 ) {
    delete $arr{page};
    selectLockers();
} elsif ( $arr{page} == 2 ) {
    delete $arr{page};
    addRecords();
}


#----------------
sub selectLockers {
#----------------

    #foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}<br>\n"; }
    
    my $studentsperlocker;
    if ( $arr{studentsperlocker} =~ m/^\d/ and $arr{studentsperlocker} <= $maxstudentsperlocker  
	 and $arr{studentsperlocker} > 0 ) {
	$studentsperlocker = $arr{studentsperlocker};
    } else {
	$studentsperlocker = 1;
    }

    #  homeroom or grade?
    my $group;
    if ( $arr{group} eq $lex{Grade} ) {
	$group = 'grade';
    } else {
	$group = 'homeroom';
    }
    my @groups = split /\s/, $arr{groupid}; # multiple grades or homerooms.
    delete $arr{group};
    delete $arr{groupid};

    my $matchgender;
    if ( $arr{matchgender} and $studentsperlocker > 1 ) { # both must be true.
	$matchgender = 1;
    }


    # get a list of students who do not have a locker 
    my @students = ();
    my %names = ();
    my %gender = ();
    my $sth1 = $dbh->prepare("select count(*) from lok_rlink where studnum = ?");

    foreach my $grp ( @groups ) { # loop through all grades / homerooms.

	my $sth = $dbh->prepare("select studnum, lastname, firstname, sex from student 
          where $group = ? order by lastname, firstname");
	$sth->execute( $grp );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

	while  ( my ($studnum, $lastname, $firstname, $sex ) = $sth->fetchrow ) {

	    # Check for Locker assigned, skip if so.
	    $sth1->execute( $studnum );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $count = $sth1->fetchrow;
	    if ( $count > 0 ) { next; }

	    push @students, $studnum;
	    $names{$studnum} = "$lastname, $firstname";
	    $gender{$studnum} = $sex;
	}

    }
    # We should now have the students in @students and names in %names;
    if ( not @students ) {
	print "<h1>". $lex{'All Students have Lockers'}. "</h1>\n";
	print "</body></html>\n";
	exit;
    }



    my $lockerrows;
    my @lockers = ();
    my $sth;
    # find our lockers
    if ( $arr{lockerlocation} ) { # use this to select locker
	$sth = $dbh->prepare("select locker_num from lok_locker
         where location = ?");
	$sth->execute( $arr{lockerlocation} );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

    } elsif ( $arr{startlocker} and $arr{endlocker} and 
	      ( $arr{endlocker} > $arr{startlocker} ) ) {
	$sth = $dbh->prepare("select locker_num from lok_locker
         where locker_num >= ? and locker_num <= ? ");
	$sth->execute( $arr{startlocker}, $arr{endlocker} );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

    } else { # find them all
	$sth = $dbh->prepare("select locker_num from lok_locker");
	$sth->execute;
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    }

    $lockerrows = $sth->rows;
    if ( $lockerrows < 1 ) {
	print "<p>". $lex{'No Lockers Found'}. "</p>\n";
	print "</body></html>\n";
	die;
    }


    # Now load all the lockers, checking for assigned ones.
    my $sth1 = $dbh->prepare("select count(*) from lok_rlink where locker_num = ?");

    while ( my $locker_num = $sth->fetchrow ) {
	$sth1->execute( $locker_num );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $count = $sth1->fetchrow;
	if ( $count >= $arr{studentsperlocker} ) { next; } # skip this locker, if maxed out.

	push @lockers, $locker_num; 
    }


    # Now find locks, if going to assign locks, as well.
    my @locks = ();
    if ( $arr{assignlocks} ) {

	# First find the locks for a pool (or not).
	if ( $arr{lockpool} ) {
	    $sth = $dbh->prepare("select lock_num from lok_lock where pool = ?
              order by lock_num");
	    $sth->execute( $arr{lockpool} );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	} else { # load them all
	    $sth = $dbh->prepare("select lock_num from lok_lock order by lock_num");
	    $sth->execute;
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	}

	my $sth1 = $dbh->prepare("select count(*) from lok_link where lock_num = ?");

	while ( my $lock_num = $sth->fetchrow ) {
	    $sth1->execute( $lock_num );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $count = $sth1->fetchrow;
	    if ( $count > 0 ) { next; } # skip this lock

	    push @locks, $lock_num; 
	}

    }


    # Now start the form.
    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{Student}. "</th>"; 
    print "<th>". $lex{'Locker Number'}. "</th>\n";
    if ( $arr{assignlocks} ) {  print "<th>". $lex{'Assign Locks'}. "</th>"; }
    print "</tr>\n";

    my $rowcolor = 'gray'; # row colors are blue or gray;

    my $sth1 = $dbh->prepare("select lastname, firstname from student where studnum = ?");
    my $sth2 = $dbh->prepare("select count(*) from lok_rlink where locker_num = ?");

    # Loop through all lockers, assigning students, and possibly locks.
    foreach my $locker_num ( @lockers ) {

	# Figure out how many students to add to this locker.
	$sth2->execute( $locker_num );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $count = $sth2->fetchrow;
	my $studcount = $arr{studentsperlocker} - $count;
	if ( $studcount == 0 ) { next; } # skip this locker, if maxed out.
	if ( $studcount < 0 ) { 
	    print $lex{Error}. q{ }. $lex{'Too many students for locker'}. " $locker_num\n";
	}


	$addlock = 1; # so only 1 lock per locker, if assigning.
	while ( $studcount ) {
	    # Get Next student
	    my $studnum = pop @students;
	    if ( not $studnum ) { last; } # done all students;

	    # Get Student Name
	    $sth1->execute( $studnum );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my ( $lastname, $firstname ) = $sth1->fetchrow;

	    print "<tr class=\"$rowcolor\"><td align=\"left\">";
	    print "<b>$lastname</b>, $firstname</td>\n<td>";
	    print "<input type=\"text\" name=\"SN-$studnum\" size=\"5\" ";
	    print "value=\"$locker_num\"></td>\n";

	    if ( $arr{assignlocks} ) {
		if ( $addlock ) { # only 1 lock per locker
		    my $lock_num = pop @locks;
		    $addlock = 0;
		    if ( $lock_num ) { # if we still have some locks left...
			print "<td>$lock_num =&gt; <input type=\"text\" name=\"$lock_num\" ";
			print "size=\"5\" value=\"$locker_num\"></td>";
		    } else { print "<td>". $lex{'No Lock'}. "</td>"; }
		} else {
		    print "<td></td>";
		}
	    }
	    print "</tr>\n";
	    
	    $studcount--;
	}

	# toggle row color;
	if ( $rowcolor eq 'blue'){ $rowcolor = 'gray'; } else { $rowcolor = 'blue'; }

    } # end of lock loop.


    print "</table>\n";
    print "<input type=\"submit\" value=\"". $lex{'Assign Lockers'}. "\">\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 "</center></body></html>\n";

} # end of selectLockers


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

    # Show existing grades and homerooms (for selection of students).
    # Show existing locker locations and numbers (in that location).
    # Allow auto-assign of locks.

    # Misc Options: Gender Matching, Students per Locker.

    # Get Locker Locations
    my $sth = $dbh->prepare("select distinct location from lok_locker
      where location != '' and location is not NULL order by locker_num");
    $sth->execute;
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    my @locations;
    while ( my $loc = $sth->fetchrow ) {
	push @locations, $loc;
    }

    # Get Lock Pools
    my $sth = $dbh->prepare("select distinct pool from lok_lock
      where pool != '' and pool is not NULL order by pool");
    $sth->execute;
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    my @lockpools;
    while ( my $pool = $sth->fetchrow ) {
	push @lockpools, $pool;
    }

    print "<form action=\"$self\" method=\"post\">\n";
    print "<input type=\"hidden\" name=\"page\" value=\"1\">\n";
    print "<table border=\"0\" cellpadding=\"3\" cellspacing=\"0\">\n";

    print "<tr><td align=\"right\">". $lex{'Student Group'}. "</td>\n<td align=\"left\">";
    print "<select name=\"group\"><option>". $lex{Grade}. "</option><option>\n";
    print $lex{Homeroom}. "</option></select>";
    print "<input type=\"input\" name=\"groupid\" size=\"12\"> ";
    print $lex{'Blank=All'}. "</td></tr>\n";

    print "<tr><td align=\"right\">". $lex{'Students per Locker'}. "</td>\n<td align=\"left\">";
    print "<input type=\"text\" name=\"studentsperlocker\" size=\"4\" value=\"1\">\n";
    print "</td></tr>\n";

    print "<tr><td align=\"right\">". $lex{'Match Gender'}. "</td>\n<td align=\"left\">";
    print "<input type=\"checkbox\" name=\"matchgender\" value=\"1\"> "; 
    print $lex{if}. q{ }. $lex{'Students per Locker'}. " &gt; 1</td></tr>\n";

    print "<tr><td align=\"center\" colspan=\"2\">\n";
    print "<hr></td></tr>\n";

    print "<tr><td align=\"right\">". $lex{'Locker Location'}. "</td>\n<td align=\"left\">";
    if ( @locations ) {
	print "<select name=\"lockerlocation\"><option></option>\n";
	foreach my $loc ( @locations )  {
	    print "<option>$loc</option>";
	}
	print "</select>\n";
    } else {
	print "<input type=\"text\" name=\"lockerlocation\" size=\"12\">\n";
    }
    print "</td></tr>\n";

    print "<tr><td align=\"right\"><b>". $lex{or}. "</b> ". $lex{'Starting Locker'};
    print "</td>\n<td align=\"left\">";
    print "<input type=\"text\" name=\"startlocker\" size=\"4\">\n";
    print "</td></tr>\n";

    print "<tr><td align=\"right\">". $lex{'Ending Locker'}. "</td>\n<td align=\"left\">";
    print "<input type=\"text\" name=\"endlocker\" size=\"4\">\n";
    print "</td></tr>\n";

    print "<tr><td align=\"center\" colspan=\"2\">\n";
    print "<hr></td></tr>\n";

    print "<tr><td align=\"right\">". $lex{'Assign Locks'}. "</td>\n<td align=\"left\">";
    print "<input type=\"checkbox\" name=\"assignlocks\" value=\"1\"></td></tr>\n"; 

    print "<tr><td align=\"right\">". $lex{'Lock Pool'}. "</td>\n<td align=\"left\">";
    print "<select name=\"lockpool\"><option></option>\n";
    foreach my $pool ( @lockpools )  {
	print "<option>$pool</option>";
    }
    print "</select></td></tr>\n";

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

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

    exit;

}



#----------------
sub addRecords { # assign lockers to students;
#----------------

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

    my $sth = $dbh->prepare("insert into lok_rlink values( ?,? )");
    my $sth1 = $dbh->prepare("insert into lok_link values( ?,? )");
    my $sth2 = $dbh->prepare("select lastname, firstname from student where studnum = ?");

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

    foreach my $key ( sort keys %arr ) {
	my ($sn, $studnum ) = split /-/, $key;
	if ( $sn eq 'SN' ) { # we have a student number; add locker
	    if ( $arr{$key} ) { # we have a locker number in $arr{$key}
		$sth->execute( $arr{$key}, $studnum );
		if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

		$sth2->execute( $studnum );
		if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		my ( $lastname, $firstname ) = $sth2->fetchrow;

		print "<tr><td><b>$lastname</b>, $firstname ". $lex{Assigned}. " ";
		print $lex{Locker}. "$arr{$key}</td></tr>\n";
	    }
	} else { # we're adding a lock to a locker.
	    if ( $arr{$key} ) { # we have locker_num in $arr{$key}, and lock_num in $key
		$sth1->execute( $arr{$key}, $key );
		if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		
		print $lex{Lock}. " $key ". $lex{Assigned}. " ";
		print $lex{Locker}. "$arr{$key}</td></tr>\n";
	    }
	}
    }

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

    print "[ <a href=\"#top\">". $lex{Top}. "</a> ]</p>\n";
    print "</center></body></html>\n";

    exit;

}

