#!/usr/bin/perl
#  Copyright 2001-2023 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',
	   'Withdrawn' => 'Withdrawn',
	   'Birthdate' => 'Birthdate',

	   );

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

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 qq{</body></html>\n};
    exit;
}


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

# Check if onion lake schools.
my $dir = getcwd;
foreach my $db ( keys %oldbase ) {
    if ( $dir =~ m/.*$db.*/ ) {
	%dbase = %oldbase;
	last;
    }
}
# Add Onion Lake Schools.
# %dbase = (%dbase,%oldbase);

#print "DBASE:", %dbase, "<br>\n";

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



# Page Header.
my $title = "$lex{Mass} $lex{Enrol} $lex{Students}";
print qq{$doctype\n<html><head><title>$title</title>
<link rel="stylesheet" href="$css" type="text/css">\n};

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

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


print qq{<div>[ <a href="$homepage">$lex{Main}</a> | };
print qq{ <a href="$eoypage">$lex{Eoy}</a> ]</div>\n};
print qq{<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 qq{K:$key V:$arr{$key}<br>\n}; }

    # Check Password
    if ( not $arr{password} or $arr{password} ne $pwd ) {
	print qq{<div style="font-size:150%;font-weight:bold;padding:1em;">};
	print qq{$lex{Password} $lex{Error}</div>\n};
	print qq{</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 $schoolname = $dbase{$db};

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

    } else { # all students
	$sth = $dbh1->prepare("select * from studentwd order by lastname, firstname"); 
	$sth->execute;
    }


    print qq{<h2>$lex{School} $schoolname </h2>\n};


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

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

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

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

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

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

    }

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

    print qq{</table>\n};
    print qq{</form>\n};
    print qq{</body></html>\n};

    exit;

} # end of selectStudents



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

    # foreach my $key ( sort keys %arr ) { print qq{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 qq{K:$key V:$arr{$key}<br>\n}; }



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


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

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


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

	# check for duplicate student name and dob
	$sth2 = $dbh->prepare("select count(*) from studentall 
			      where lastname = ? and firstname = ? and birthdate = ?");
    
	
	# 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;

	# Check for an existing student with same name and birthdate.
	$sth2->execute( $rec{lastname},$rec{firstname},$rec{birthdate} );
	if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr \n"; }
	my $dupcount = $sth2->fetchrow;
        if ( $dupcount ) {
	    print qq{<h2>Student already exists in this school:};
	    print qq{$rec{lastname},$rec{firstname} ($rec{birthdate})<br>\n};
	    print qq{Skipping!</h2>\n};
	    next; # student
	}

##
#	print qq{Student Record:<br>\n};
#	foreach my $id ( sort keys %rec ) { print qq{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 qq{<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 qq{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 qq{Insert Fields: $fields<br>Values: $values<br>\n};

	my $sth = $dbh->prepare("insert into student ( $fields ) values( $values )");
	$sth->execute;
	if ($DBI::errstr){ print qq{$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 qq{$DBI::errstr\n}; die $DBI::errstr; }



    } # end of loop to add student records


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

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

    print qq{<p>[ <a href="$self">$title</a> |\n};
    print qq{ <a href="$homepage">$lex{Main}</a> | \n};
    print qq{ <a href="$eoypage">$lex{Eoy}</a> ]</p>\n};
    print qq{</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 qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};

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


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

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

    foreach $db ( sort 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 qq{<tr><td class="bra">$lex{Withdrawn} $lex{Student} $lex{Grade}</td>\n};
    print qq{<td class="la"><input type="text" name="grade" size="4"> $lex{'Blank=All'}</td></tr>\n};


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

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


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


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

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

    print qq{</body></html>\n};

    exit;

} # end of showStartPage

