#!/usr/bin/perl
#  Copyright 2001-2012 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 = ('Main' => 'Main',
	   'Eoy' => 'Eoy',
	   'Select' => 'Select',
	   'Student(s) Not Found' => 'Student(s) Not Found',
	   'Withdraw' => 'Withdraw',
	   'Transfer' => 'Transfer',
	   'Error' => 'Error',
	   'Insert' => 'Insert',
	   'Mass' => 'Mass',
	   'Grade' => 'Grade',
	   'Password' => 'Password',
	   'Description' => 'Description',
	   'Continue' => 'Continue',
	   'Students' => 'Students',
	   'Blank=All' => 'Blank=All',
	   'Date' => 'Date',
	   'Cannot open' => 'Cannot open',
	   'Reason' => 'Reason',
	   'Enrol' => 'Enrol',

	   );

my $self = 'masswithdraw.pl';
my $pwd = 'tansi'; # later, pull from security system.

use DBI;
use CGI;


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

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


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


my $title = "$lex{Mass} $lex{Withdraw} $lex{Students}";
print "$doctype\n<html><head><title>$title</title>
<link rel=\"stylesheet\" href=\"$css\" type=\"text/css\">\n";

if ( not $arr{page} ) { # calendar popup.
    print "<link rel=\"stylesheet\" type=\"text/css\" media=\"all\" ";
    print "href=\"/js/calendar-blue.css\" title=\"blue\">\n";
    print "<script type=\"text/javascript\" src=\"/js/calendar.js\"></script>\n";
    print "<script type=\"text/javascript\" src=\"/js/lang/calendar-en.js\"></script>\n";
    print "<script type=\"text/javascript\" src=\"/js/calendar-setup.js\"></script>\n";
}

print "$chartype\n</head><body>\n";
if ( not $arr{page} ) {
    print "<body onload=\"document.forms[0].elements[1].focus()\" style=\"padding:1em 3em;\">\n";
} else {
    print "<body style=\"padding:1em 3em;\">\n";
}


print "[ <a href=\"$homepage\">$lex{Main}</a> |\n";
print " <a href=\"$eoypage\">$lex{Eoy}</a> ]\n";

print "<h1>$title</h1>\n";


if ( not $arr{page} ) {
    showStartPage();

} elsif ( $arr{page} == 1 ) {
    delete $arr{page};
    selectStudents();

} elsif ( $arr{page} == 2 ) {
    delete $arr{page};
    withdrawStudents();
} 



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

    # Load Global Reasons (Exit)
    if ( not -e "$globdir/global.conf" ) {
	print $lex{'Cannot open'}. " global.conf file!";
	print "</body></html>\n";
	die;
    }

    # Read database names and also the global enrol/withdraw reasons.
    eval { require "$globdir/global.conf"; };
    if ( $@ ) {
	print "<h3>". $lex{'Cannot open'}. " global.conf:\n $@</h3>\n";
	die $lex{'Cannot open'}. " global.conf: $@\n";
    }


    # Get current date
    my @tim = localtime(time);
    my $year = @tim[5] + 1900;
    my $month = @tim[4] + 1;
    my $day = @tim[3];
    my $currdate = "$year-$month-$day";


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

    print "<table cellpadding=\"3\" cellspacing=\"0\" border=\"0\" ";
    print "style=\"padding:0.5em;border:1px solid gray;\">\n";


    # Password
    print "<tr><td class=\"bra\">$lex{Password}</td>\n";
    print "<td class=\"la\"><input type=\"password\" name=\"password\" size=\"12\"></td></tr>\n";


    # Student Grade
    print "<tr><td class=\"bra\">$lex{Grade}</td>\n";
    print "<td class=\"la\"><input type=\"text\" name=\"grade\" size=\"4\"> $lex{'Blank=All'}</td></tr>\n";


    # Withdraw Date
    print "<tr><td class=\"bra\">$lex{Withdraw} $lex{Date}</td>\n<td class=\"la\">";
    print "<input type=\"text\" ";
    print "name=\"date\" id=\"date\" size=\"10\" value=\"$currdate\">\n";
    print "<button type=\"reset\" id=\"start_trigger\">...</button>\n";
    print "</td></tr>\n";

    # Withdraw Reason
    print "<tr><td class=\"bra\">$lex{Withdraw} $lex{Reason}</td>\n";
    print "<td class=\"la\"><select name=\"exittype\"><option></option>\n";
    foreach my $type ( sort keys %g_wdraw ){
        print "<option value=\"$type\">$g_wdraw{$type}</option>\n";
    }
    print "</select></td></tr>\n";

    # Withdrawal Description
    print "<tr><td class=\"bra\">$lex{Withdraw} $lex{Description}</td>\n<td class=\"la\">";
    print "<textarea name=\"description\" rows=\"3\" cols=\"50\"></textarea></td></tr>\n";


    print "<tr><td></td><td class=\"la\"><input type=\"submit\" value=\"$lex{Continue}\"></td></tr>\n";
    print "</table></form>";

    print "<script type=\"text/javascript\">
     Calendar.setup({
        inputField     :    \"date\", 
        ifFormat       :    \"%Y-%m-%d\",
        button         :    \"start_trigger\",
        singleClick    :    false,
        step           :    1
    });
    </script>\n";

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

    exit;

} # end of showStartPage



#-----------------
sub selectStudents { 
#-----------------

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

    # Check Password
    if ( not $arr{password} or $arr{password} ne $pwd ) {
	print "<div style=\"font-size:150%;font-weight:bold;padding:1em;\">$lex{Password} $lex{Error}</div>\n";
	print "</body></html>\n";
	exit;
    }


    if ( $arr{grade} ) {
	$sth = $dbh->prepare("select * from student where grade = ? 
           order by grade, lastname, firstname");
	$sth->execute( $arr{grade} );
    } else {
	$sth = $dbh->prepare("select * from student 
           order by lastname, firstname"); 
	$sth->execute;
    }
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

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

    print "<input type=\"hidden\" name=\"description\" value=\"$arr{description}\">\n";
    print "<input type=\"hidden\" name=\"exittype\" value=\"$arr{exittype}\">\n";
    print "<input type=\"hidden\" name=\"date\" value=\"$arr{date}\">\n";

    print "<h2>$lex{Select} $lex{Students}</h2>\n";
    
    print "<table border=\"1\" cellspacing=\"0\" cellpadding=\"3\">\n";
    print "<tr><th>$lex{Students}</th><th>$lex{Grade}</th></tr>\n";

    print qq{<tr><td colspan="3" class="cn"><input type="submit" value="$lex{Enrol} $lex{Students}"></td></tr>\n};

    while ( my $ref = $sth->fetchrow_hashref ) {
	my %rec = ();
	%rec = %$ref;

	print "<tr><td class=\"la\">";
	print qq{<input type="checkbox" name="$rec{studnum}" value="1"> <b>$rec{lastname}</b>, $rec{firstname}</td>\n};
	print qq{<td class="la">$rec{grade}</td></tr>\n};

    }

    print qq{<tr><td colspan="3" class="cn"><input type="submit" value="$lex{Withdraw} $lex{Students}"></td></tr>\n};

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

    exit;

} # end of selectStudents



#-------------------
sub withdrawStudents {
#-------------------

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

    my $date = $arr{date};
    delete $arr{date};

    my $description = $arr{description};
    delete $arr{description};

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

    # Put results into table format
    print "<table cellpadding=\"3\" cellspacing=\"0\" border=\"0\">\n";

    foreach my $studnum ( keys %arr ) { # loop through passed student numbers

	# Get Student Info
	my $sth = $dbh->prepare("select * from student where studnum = ?"); 
	$sth->execute( $studnum );
	if ($DBI::errstr){ print $DBI::errstr; next; }
	my @student = $sth->fetchrow;
	my $lastname = $student[1];
	my $firstname = $student[2];


       # Load student record into hash
        $sth->execute( $studnum );
        if ($DBI::errstr){ print $DBI::errstr; next; }
        my $ref = $sth->fetchrow_hashref;
        my %rec = %$ref;
        # used to put values into transfer table.


	if ( not @student ){ 
	    print "<tr><td>$lex{'Student(s) Not Found'}: $studnum</td></tr>\n";
	    next;
	}

	# Insert Record in Student WD
	$student[0] = $sql{default};  # reset the studid/id.
	foreach my $studfld ( @student ){ 
	    if ( $studfld ne $sql{default} ) { $studfld = $dbh->quote( $studfld ); }
	}
	my $studentstr = join(', ', @student);

	$sth = $dbh->prepare("insert into studentwd values ( $studentstr )");
	$sth->execute;
	if ( $DBI::errstr ) { 
	    print "<tr><td>$lex{Insert} $lex{Error}: $firstname $lastname";
	    print "</td></tr>\n";
	    next;
	}

	# Remove from Student Table
	$sth = $dbh->prepare("delete from student where studnum = ?"); 
	$sth->execute( $studnum );
	if ( $DBI::errstr) {
	    print "<tr><td style=\"color:red;\">$lex{Withdraw} $lex{Error}";
	    print " $firstname $lastname: $DBI::errstr</font></td></tr>\n";
	} else {
	    print "<tr><td>$lex{Withdraw} - <b>$lastname</b>, $firstname</td></tr>\n";
	}


	# Add Transfer Record
	my $sth2 = $dbh->prepare("insert into transfer 
         ( studnum, date, type, description, exittype,  
          lastname, firstname, middlename, birthdate, provnum ) 
         values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )");

	$sth2->execute( $rec{studnum}, $date, 'withdraw', $description, $exittype, 
		       $rec{lastname}, $rec{firstname}, $rec{initial}, 
		       $rec{birthdate}, $rec{provnum} );

	if ($DBI::errstr) { 
	    print "<tr><td><font color=\"red\">$lex{Transfer} $lex{Error}";
	    print " - $rec{lastname} $rec{firstname}: $DBI::errstr\n"; 
	}

    } # End of Student Withdrawal Loop

    print "</table>\n";

    print "<p>[ <a href=\"$homepage\">$lex{Main}</a> |\n";
    print "<a href=\"$eoypage\">$lex{Eoy}</a> ]</p>\n";
    print "</body></html>\n";

    exit;

}
