#!/usr/bin/perl
#  Copyright 2001-2009 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.

use DBI;
use CGI;

my %lex = ('Preregistration Select' => 'Preregistration Select',
	   'Select Preregistered Students' => 'Select Preregistered Students',
	   'Please select pre-registered students to transfer into normal student table' =>
	     'Please select pre-registered students to transfer into normal student table',
	   'Select Preregistered Students' => 'Select Preregistered Students',
	   'Student' => 'Student',
	   'Select' => 'Select',
	   'Add Pre-Registrants to Student Table' => 'Add Pre-Registrants to Student Table',
	   'Main' => 'Main',
	   'Eoy' => 'Eoy',
	   'Duplicate Student Number' => 'Duplicate Student Number',
	   'Skipping' => 'Skipping',
	   'Transfer Table error' => 'Transfer Table error',
	   'Your pre-registered students are now in the student table' =>
	     'Your pre-registered students are now in the student table',
	   'There was an error storing your data' => 'There was an error storing your data',
	   'Contact' => 'Contact',
	   'Please record the following error' => 'Please record the following error',
	   'Error' => 'Error',
	   'Preregistration' => 'Preregistration',

	   );

my $self = 'prexfer.pl';

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);



print "$doctype\n<html><head><title>". $lex{'Preregistration Select'}. "</title>
<link rel=\"stylesheet\" href=\"$css\" type=\"text/css\">
$chartype\n</head><body>\n";

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

if ( $arr{xferflag} ) {
    delete $arr{xferflag};
    transferRecords();
}

print "<center><h1>". $lex{'Select Preregistered Students'}. "</h1>\n<p>";
print $lex{'Please select pre-registered students to transfer into normal student table'};
print "</p>\n"; 


my $sth = $dbh->prepare("select lastname, firstname, studnum from prereg 
 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=\"xferflag\" value=\"1\">\n";
print "<table  border=\"1\" cellpadding=\"3\" cellspacing=\"0\">\n";
print "<tr><th>". $lex{Student}. "</th><th>". $lex{Select}. "</th></tr>\n";

while (my ($lastname, $firstname, $studnum)  = $sth->fetchrow){
    print "<tr><td><b>$lastname</b> $firstname</td>\n";
    print "<td><input type=\"checkbox\" name=\"$studnum\" value=\"1\"></td></tr>\n";
}

print "<tr><td colspan=\"2\" align=\"center\">\n";
print "<input type=\"submit\" value=\"";
print $lex{'Add Pre-Registrants to Student Table'}. "\"></td></tr>\n";
print "</table></form></body></html>\n";


#------------------
sub transferRecords {
#------------------

    foreach my $studnum ( keys %arr ) {

	# Get student from prereg table.
	$sth = $dbh->prepare("select * from prereg where studnum = ?");
	$sth->execute( $studnum );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my @student = $sth->fetchrow;

	# Check for existing student number.
	$sth = $dbh->prepare("select count(*) from student
          where studnum = ?");
	$sth->execute( $studnum );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

	my $count = $sth->fetchrow;
	if ($count){ # We have a duplicate record coming out of prereg.
	    print "<h1>". $lex{'Duplicate Student Number'}. ": $studnum \n";
	    print " $student[2] $student[3] $student[1] <br>\n";
	    print $lex{Skipping}. "!</h1>\n";
	    next;
	}

	# Prepare student array for record entry
	foreach my $fld (@student){ 
	    $fld = $dbh->quote($fld);
	    $fld .= ',';
	}

	chop $student[$#student]; # Chop trailing comma in last element
	$student[0] = $sql{default}. ',';  # reset the studid.

	$sth = $dbh->prepare("insert into student values (@student)");
	$sth->execute;
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

	# Add a transfer record for this student.
	my $entrytype = '18'; # New Enrollment, no previous schooling.
	$sth2 = $dbh->prepare("insert into transfer values (
         $sql{default}, '$studnum', now(), 'enrol', '$lex{Preregistration}',
         '$entrytype', $sql{default}, $sql{default}, $sql{default},'Y', 
         $student[1] $student[2] $student[3] $student[8] $sql{default} )");
 	#NOTE: The student fields already have single quotes and trailing comma!
 
	$sth2->execute;
	if ( $DBI::errstr ) { print $lex{'Transfer Table error'}. ": $DBI::errstr\n"; }

	# delete existing record in prereg.
	$sth = $dbh->prepare("delete from prereg where studnum = ?");
	$sth->execute( $studnum );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

    } # end of Loop 


    if (not $DBI::errstr ) {
	print "<h3>". $lex{'Your pre-registered students are now in the student table'};
	print ".</h3>\n";

    } else {
	print "<h3>". $lex{'There was an error storing your data'}. ".\n";
	print $lex{Contact}. " $adminname <a href=\"mailto:$adminemail\">$adminemail</a>\n"; 
	print $lex{'Please record the following error'}. ": $DBI::errstr</h3>\n";
    }

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

    exit;

}

