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

%lex = ( 'Nominal Roll' => 'Nominal Roll',
	 'Main' => 'Main',
	 'Unable to open template file:' => 'Unable to open template file:',
	 'Select' => 'Select',
	 'Students' => 'Students',
	 'Homeroom' => 'Homeroom',
	 'Grade' => 'Grade',
	 'Blank=All' => 'Blank=All',
	 'Check' => 'Check',
	 'Next Page' => 'Next Page',
	 'Continue' => 'Continue',
	 'Error' => 'Error',
	 'Save' => 'Save',
	 'Record(s) Stored' => 'Record(s) Stored',
	 'Record Exists' => 'Record Exists',
	 'Red' => 'Red',
	 'Withdrawn Students' => 'Withdrawn Students',
	 'Show' => 'Show',
	 'Add' => 'Add',
	 'Withdrawn' => 'Withdrawn',

	 );

use CGI;
use DBI;
use Data::Password qw(:all);  # for password checking...

my $self = 'nradd.pl';

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

eval require "../../lib/libmeta.pl";
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";
$dbh = DBI->connect($dsn,$user,$password);
$dbh->{mysql_enable_utf8} = 1;


# Print Page Header
my $title = "$lex{Add} $lex{'Nominal Roll'}";
print qq{$doctype\n<html><head><title>$title</title>\n};

print qq{<link rel="stylesheet" href="$css" type="text/css">\n};
print qq{$chartype\n</head><body style="margin:1em;">[ <a href="$homepage">$lex{Main}</a> ]};

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

} elsif ( $arr{page} == 3 ) {
    delete $arr{page};
    writeRecords();
}



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

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

    print qq{<tr><td class="bra">$lex{Select} $lex{Students}</td>\n};
    print qq{<td class="la"><select name="groupname">};
    print qq{<option value="grade">$lex{Grade}</option>\n};
    print qq{<option value="homeroom">$lex{Homeroom}</option>\n};
    print qq{</select>\n};
    print qq{<input type="text" name="groupvalue" size="10"> $lex{'Blank=All'}</td></tr>\n};

    print qq{<tr><td class="bra">$lex{'Check'} $lex{'Next Page'}</td>\n};
    print qq{<td><input type="checkbox" name="chk" value="1">\n};
    print qq{</td></tr>\n};

    print qq{<tr><td class="bra">$lex{'Show'} $lex{'Withdrawn Students'}</td>\n};
    print qq{<td><input type="checkbox" name="showwithdrawn" value="1">\n};
    print qq{</td></tr>\n};

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

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

    exit;

} # end of showStartPage



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

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

    my $studtable = 'student';
    if ( $arr{showwithdrawn} ) { $studtable = 'studentall'; }


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

    my $chk;
    if ( $arr{chk} ) { $chk = 'checked="checked"'; }
    delete $arr{chk};

    my $select;
    if ( $groupvalue ) { # we're picking a group
	if ( $arr{groupname} eq 'grade' ) {
	    $select = 'where grade = ?';
	} else {
	    $select = 'where homeroom = ?';
	}
    }

    my $sth = $dbh->prepare("select lastname, firstname, studnum, grade from $studtable
     $select order by lastname, firstname");
    if ( $select ) {
	$sth->execute( $groupvalue );
    } else {
	$sth->execute;
    }
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

    # Check if withdrawn;
    my $sth1 = $dbh->prepare("select count(*) from studentwd where studnum = ?");

    # Get Transfer Info
    my $sth2 = $dbh->prepare("select date, type from transfer where studnum = ? order by date desc");

    # Check if record already exists in NR.
    my $sth3 = $dbh->prepare("select count(*) from student_inac where studnum = ?");



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

    print qq{<div><input type="submit" value="$lex{Continue}"></div>};

    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
    print qq{<tr><th></th><th>Student</th><th>Grade</th><th>Transfer</th></tr>\n};
    print qq{<caption><b>NR = Nominal Roll Record Exists</b></caption>};



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

	my ($wd, $transfer);

	$sth1->execute( $studnum ); # check if withdrawn.
	if ( $DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my $count = $sth1->fetchrow;
	if ( $count ) { # if withdrawn
	    $wd = qq{<span style="color:red;text-weight:bold;">WD</span>};
	}

	# Get Latest Transfer record.
	$sth2->execute( $studnum );
	if ( $DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my ($date,$type) = $sth2->fetchrow;
	if ( $type ) {
	    $transfer = "$type ($date)";
	} else {
	    $transfer = "Not Found";
	}
	

	# Check if record already exists in NR table
	$sth3->execute( $studnum );
	if ( $DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my $nrcount = $sth3->fetchrow;

	print qq{<tr><td class="la">};
	if ( $nrcount ) { 
	    print qq{<b>NR</b> }; 
	} else {
	    print qq{<input type="checkbox" name="$studnum" value="1" $chk> };
	}
	print qq{</td><td>$wd <b>$lastname</b>, $firstname ($studnum)</td><td>$grade</td><td>$transfer</td></tr>\n};

    }


    print qq{</table>\n};
    print qq{<div><input type="submit" value="$lex{Continue}"></div>};

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

    exit;

} # end of selectStudents


#-----------------
sub addEditRecords {
#-----------------

    #foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    # all of %arr will contain student numbers.


    # Read in Template
    unless (open (FH,"<../../template/inac.tpl")) {
	print $lex{'Unable to open template file:'},"$!\n";
	die $lex{'Unable to open template file:'},"$!\n";
    }
    my $formtext;
    { local $/; $formtext = <FH>; close FH;}

    # Create hash for fieldnames from meta.
    my $sth = $dbh->prepare("select fieldid, fieldname from meta where tableid = ?");
    $sth->execute( 'student_inac' );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    my %fieldnames = ();
    while ( my ( $fieldid, $fieldname ) = $sth->fetchrow ) {
	$fieldnames{$fieldid} = $fieldname;
    }

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

    # Now put replacement text back in.
    $formtext =~ s{\<\*(.*?)\*\>}
    { exists( $fieldnames{$1} ) 
	  ? $fieldnames{$1} 
          : $1
          }gsex;
    # Formtext is now ready for multiple use; only contains <@fieldid@> values.
    # now parse for form entry replacement elements  <@name@> 
    # Extract fields from template
    my @fields = ();
    while ( $formtext =~ m/\<\@(.*)\@\>/g){
	push @fields, $1;
    }

    # Create Sorted Array - by Name.
    $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");
    my %sortedStudents = ();
    foreach my $studnum ( keys %arr ) {
	$sth1->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ( $lastname, $firstname ) = $sth1->fetchrow;
	$sortedStudents{"$lastname$firstname$studnum"} = $studnum;
    }

    # Prepare Withdrawn Check
    my $sth2 = $dbh->prepare("select count(*) from studentwd where studnum = ?");

    # print top of form
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="3">\n};
    print qq{<input type="submit" value="$lex{'Save'}">\n};
    
    print qq{<div style="color:red;">$lex{Red}=$lex{Withdrawn}</div>\n};

    # Prepare to get record values.
    $sth = $dbh->prepare("select * from student_inac where studnum = ?");

    # now show students in name order.
    foreach my $key ( sort keys %sortedStudents ) {

	my $studnum = $sortedStudents{ $key };
	my $text = $formtext; # formtext contains original form.

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

	# Check if withdrawn; if so, show red.
	my $color;
	$sth2->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $count = $sth2->fetchrow;
	if ( $count > 0 ) {
	    $color = q{color:red;};
	}

	my $name = qq{<span style="font-weight:bold;font-size:120%;$color">$firstname $lastname</span> ($studnum)};

	$text =~ s/\<\@studnum\@\>/$name/;

	# Get their record, if any.
	my $hasrecord; # flag to indicate if record already exists.
	$sth->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $recref = $sth->fetchrow_hashref;
	if ( $recref ) { $hasrecord = 1; }

	my %fieldvals = %{ $recref };

	# get replacement values for fields
	foreach my $fieldid ( @fields ) {

	    $values{$fieldid} = metaInputField('student_inac',
		$fieldid, $fieldvals{$fieldid}, $dbh, $studnum );

	}

	# now put field values back into $text variable...
	$text =~ s{ \<\@(.*?)\@\> }
	{ exists($values{$1}) 
	       ? $values{$1} 
	       : "$values{$1}-$1"
	}gsex;

	print qq{<div style="padding:0.6em;border:1px solid gray;margin:0.5em;">\n};
	if ( $hasrecord ) { print qq{<div style="font-weight:bold;color:blue;">$lex{'Record Exists'}</div>\n}; }
	print $text,"\n";
	print qq{</div>\n};

    }
    
    print qq{<input type="submit" value="$lex{'Save'}">\n};
    print qq{</center></form></center></body></html>\n};


}


#---------------
sub writeRecords {
#---------------

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

    my $currstudnum = -1;
    my $prevstudnum;

    my $sth = $dbh->prepare("select id from student_inac where studnum = ?");
    my $sth1 = $dbh->prepare("insert into student_inac (studnum) values (?)");


    foreach my $key ( sort keys %arr ) {

	my ( $studnum, $fieldid ) = split(/:/, $key);

	$prevstudnum = $currstudnum;
	$currstudnum = $studnum;
	my $id;

	if ( $currstudnum != $prevstudnum ) {

	    # We need to check for a record and create one if not...
	    $sth->execute( $currstudnum );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    $id = $sth->fetchrow;

	    if ( not $id ) { # create a new record;
		$sth1->execute( $currstudnum ); # create the record
		if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    }
	}

	my $sth2 = $dbh->prepare("update student_inac set $fieldid = ? where studnum = ?");
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

	if ( defined $arr{$key} ) {
	    $sth2->execute( $arr{$key}, $studnum );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	} else { # no value for field.
	    $sth2->execute( NULL, $studnum );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	}

    }

    print qq{<h1>$lex{'Record(s) Stored'}</h1>\n};
    print qq{</body></html>\n};

    exit;

}
