#!/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',
	   'Enrol' => 'Enrol',
	   'Student' => 'Student',
	   'Continue' => 'Continue',
	   'Contact' => 'Contact',
	   'Error' => 'Error',
	   'Grade' => 'Grade',
	   'Description' => 'Description',
	   'Reason' => 'Reason',
	   'Date' => 'Date',
	   'Withdrawn' => 'Withdrawn',
	   'School' => 'School',
	   'Cannot open' => 'Cannot open',
	   'Record(s) Stored' => 'Record(s) Stored',
	   'Select' => 'Select',
	   'Students' => 'Students',
	   'Duplicate Check' => 'Duplicate Check',
	   'Mass' => 'Mass',
	   'Blank=All' => 'Blank=All',
	   'Password' => 'Password',
	   'Eoy' => 'Eoy',

	   );

use DBI;
use CGI;
use Fcntl qw(:DEFAULT :flock);
use Crypt::GeneratePassword qw(:all); # password generation.

my $self = 'massenrol.pl';

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

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;


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";
}


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


# Page Header.
my $title = "$lex{Mass} $lex{Enrol} $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";
}

# Set Focus to Last Name field.
print "$chartype\n</head>";
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 "<div>[ <a href=\"$homepage\">$lex{Main}</a> | ";
print " <a href=\"$eoypage\">$lex{Eoy}</a> ]</div>\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};
    enrolStudents();
} 


#-----------------
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;
    }

    # Open the remote database and get the students.
    my $db = $arr{dbase};
    my $dsn1 = "DBI:$dbtype:dbname=$db";
    my $dbh1 = DBI->connect($dsn1,$guser,$gpassword);

    my $sth;
    if ( $arr{grade} ) {
	$sth = $dbh1->prepare("select * from studentwd 
           where grade = ? 
           order by grade, lastname, firstname"); 
	$sth->execute( $arr{grade} );
    } else {
	$sth = $dbh1->prepare("select * from studentwd 
           order by lastname, firstname"); 
	$sth->execute;
    }

     
    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=\"entrytype\" value=\"$arr{entrytype}\">\n";
    print "<input type=\"hidden\" name=\"date\" value=\"$arr{date}\">\n";
    print "<input type=\"hidden\" name=\"dbase\" value=\"$arr{dbase}\">\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"> $rec{lastname}, $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{Enrol} $lex{Students}"></td></tr>\n};

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

    exit;

} # end of selectStudents



#----------------
sub enrolStudents { 
#----------------

    # foreach my $key ( sort 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 $dbase = $arr{dbase};
    delete $arr{dbase};

    my $entrytype = $arr{entrytype};
    delete $arr{entrytype};
    
    # Now only student numbers left in %arr. One approach.
    # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}<br>\n"; }


    # Open the remote database and get the students.
    my $db = $dbase;
    my $dsn1 = "DBI:$dbtype:dbname=$db";
    my $dbh1 = DBI->connect($dsn1,$guser,$gpassword);

    my $sth1 = $dbh1->prepare("select * from studentwd where studnum = ?");



    # Check for duplicates setup
    $sth = $dbh->prepare("select count(*) from student where studnum = ?");

    # Loop through all students to enrol
    foreach my $remotestudnum ( keys %arr ) { 


	# Load the remote record into a hash.
	$sth1->execute( $remotestudnum );
	if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr \n";}
	my $ref = $sth1->fetchrow_hashref;
	my %rec = %$ref;

##
#	print "Student Record:<br>\n";
#	foreach my $id ( sort keys %rec ) { print "K:$id V:$rec{$id}<br>\n"; }
##


	delete $rec{studid};
	delete $rec{utag};
	delete $rec{graddate};
	delete $rec{owing};
	if ( $rec{birthdate} eq '0000-00-00' ) {
	    delete $rec{birthdate};
	}


	# Get next Student number and update file.
	sysopen (SNUM, "../../etc/studentnumber", O_RDWR | O_CREAT) or 
	    die "$lex{'Cannot open'} student number file\n";

	flock(SNUM,LOCK_EX);

	$studentnum = <SNUM> || 0;
	chomp $studentnum;
	seek(SNUM,0,0) or die "Cannot rewind student number file: $!\n";
	truncate(SNUM,0) or die "Cannot truncate student number file'}: $!\n";

	my $done = 0;

	while ( not $done ) {

	    # Duplicate Check
	    $sth->execute( $studentnum );
	    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr \n";}
	    my $count = $sth->fetchrow;
	    if ( $count > 0 ) {
		print "<div>$lex{'Duplicate Check'} $studentnum </div>\n";
		$studentnum++; # try next number
	    } else {
		$done = 1;
	    }

	}

	# Write next available student number into the file
	$newstudentnum = $studentnum + 1;
	print SNUM $newstudentnum or die "Cannot write to student number file: $!\n";
	close SNUM or die "Cannot close student number file: $! \n";

	print "New Student Number:$studentnum<br>\n";

	$rec{studnum} = $studentnum;


	# Create array called "@fields" storing fieldid values.
	my @fields = ();
	$sth2 = $dbh->prepare("select fieldid from meta
          where tableid = 'student' order by arrayidx");
	$sth2->execute;
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	while (my $fld = $sth2->fetchrow){
	    push @fields,$fld;
	}


	# Add a Password ...
	my $password = word( $g_studentpwd_minlen, $g_studentpwd_maxlen,
			  $g_studentpwd_lang, $g_studentpwd_signs,
			  $g_studentpwd_caps, $g_studentpwd_minfreq,
			  $g_studentpwd_avgfreq );

	$rec{password} = $password;


	# Now run through @fields array and populate arrays.
	my @values = ();
	my @fieldnames = ();

	foreach my $fld ( @fields ) {
	    if ( $rec{$fld} ) { # if matching hash value exists, use it.
		push @fieldnames, $fld;
		push @values, $dbh->quote( $rec{$fld} );
	    }
	}

	my $fields = join(',', @fieldnames );
	my $values = join(',', @values );


	# print "Insert Fields: $fields<br>Values: $values<br>\n";

	my $sth = $dbh->prepare("insert into student ( $fields ) values( $values )");
	$sth->execute;
	if ($DBI::errstr){ print "$lex{Error}: $DBI::errstr; $!\n"; die $DBI::errstr; }


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

	$sth->execute( $rec{studnum}, $date, 'enrol', $description, $entrytype, 
		       $rec{lastname}, $rec{firstname}, $rec{initial}, 
		       $rec{birthdate}, $rec{provnum} );

	if ($DBI::errstr) { print "$DBI::errstr\n"; die $DBI::errstr; }



    } # end of loop to add student records



    if ( not $DBI::errstr ) {
	print "<h3>$lex{'Record(s) Stored'}</h3>\n";

    } else {
	print "<p><b>$lex{Error}: $DBI::errstr</b><br>\n";
	print "$lex{Contact}:$adminname [ <a href=\"mailto:$adminemail\">";
	print "$adminemail</a> ]</p>\n";
    }

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

    exit;


} # end of enrolStudents



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

    # 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";

    # School Database
    print "<tr><td class=\"bra\">$lex{School}</td>\n";
    print qq{<td class="la"><select name="dbase"><option></option>};

    foreach $db ( keys %dbase ) { #defined in global config file.
	print qq{<option value="$db">$dbase{$db}</option>\n};
    }
    print qq{</select></td></tr>\n};

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


    # Enrol Date
    print "<tr><td class=\"bra\">$lex{Enrol} $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";

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


    # Enrolment Description
    print "<tr><td class=\"bra\">$lex{Enrol} $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

