#!/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.


my %lex = ('Edit' => 'Edit',
	   'Nominal Roll' => 'Nominal Roll',
	   'Main' => 'Main',
	   'Delete' => 'Delete',
	   'Error' => 'Error',
	   'Student' => 'Student',
	   'Grade' => 'Grade',
	   'Select' => 'Select',
	   'Unable to open template file:' => 'Unable to open template file:',
	   'Record(s)' => 'Record(s)',
	   'Updated' => 'Updated',
	   'Continue' => 'Continue',
	   'All' => 'All',
	   'Fields' => 'Fields',
	   'Students' => 'Students',
	   'Homeroom' => 'Homeroom',
	   'Check' => 'Check',
	   'Next Page' => 'Next Page',
	   'Blank=All' => 'Blank=All',
	   'Name' => 'Name',
	   'Moved' => 'Moved',
	   'Not Found' => 'Not Found',
	   'Record' => 'Record',
	   'Group' => 'Group',
	   'Save' => 'Save',

	   );


use DBI;
use CGI;

my $self = 'nrdeled.pl';
$nrtable = 'student_inac';


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 Page Header
my $title = "$lex{Edit}/$lex{Delete} $lex{'Nominal Roll'} $lex{'Record(s)'}";
print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">
<style type="text/css">input { margin:0.4em; }</style>
$chartype\n</head><body>\n};

print qq{[ <a href="$homepage">$lex{Main}</a> ]\n};
print qq{<h1>$title</h1>\n};


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

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

} elsif ( $arr{page} == 2 and $arr{type} eq $lex{Edit} ) {
    delete $arr{page};
    delete $arr{type};
    editStudents();

} elsif ( $arr{page} == 2 and $arr{type} eq $lex{Delete} ) {
    delete $arr{page};
    delete $arr{type};
    confirmDelete();

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

} elsif ( $arr{page} == 4 ) {
    delete $arr{page};
    updateRecords();
}



#----------------
sub showStartPage { # Entry Values for Custom Script
#----------------

    # Read student inac fields, and defaults into fields and %fieldname hash.
    my $sth = $dbh->prepare("select fieldid, fieldname
    from meta where tableid = 'student_inac' order by fieldname");
    $sth->execute;
    my (%fieldnames, @fields);
    while ( ( my $fieldid, $fieldname ) = $sth->fetchrow ) {
	if ( $fieldid eq 'id' ) { next; } # skip id field.
	$fieldname =~ s/\(//g;
	$fieldname =~ s/\)//g; # strip parentheses. (sp?)
	push @fields, $fieldid;
	$fieldname{ $fieldid } = $fieldname;
    }


    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></td><td><input type="submit" value="$lex{Continue}"></td></tr>\n};


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


    # Select Fields.
    print qq{<tr><td class="bra">$lex{Select} $lex{Fields}</td><td class="la">};
    print qq{<select name="field"><option value="all">$lex{All} $lex{Fields}</option>\n};
    foreach my $fieldid ( @fields ) {
	print qq{<option value="$fieldid">$fieldname{$fieldid}</option>};
    }
    print qq{</select></td></tr>\n};

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

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

    exit;

}


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

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

    # values passed: check, field, grouptype, groupvalue,

    my $select;
    my (@grp, @students, %studrec );

    my $checked;
    if ( $arr{check} ) {
	$checked = q{checked="checked"};
    }

    # Load Meta Values for Service Provision.
    my $sth = $dbh->prepare("select defaultvalue from meta where tableid = 'student_inac' and 
       fieldid = 'serviceprovision'");
    $sth->execute;
    if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    my $sp = $sth->fetchrow;
    $sp =~ s/\s*$//; # remove trailing spaces
    my %metasp = split /\s+/, $sp;
    foreach my $key ( sort keys %metasp ) {
	$metasp{$key} =~ s/_/ /g;
#	print qq{K:$key V:$metasp{$key}<br>\n};
    }

     

    if ( $arr{groupvalue} ) {
	my $grp = 'grade';
	if ( $arr{grouptype} eq 'homeroom' ) { $grp = 'homeroom'; }

	# Check Value
	my $sth = $dbh->prepare("select distinct $grp from student where $grp != '' and $grp is not NULL");
	$sth->execute;
	my %group = ();
	if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my $val = $sth->fetchrow ) {
	    $group{$val} = 1;
	}

	if ( not $group{ $arr{groupvalue}} ) {
	    print qq{<h3>$lex{Group} $lex{'Not Found'}</h3>\n};
	    print qq{</body></html>\n};
	    exit;
	}

	# Get Student Numbers
	my $sth = $dbh->prepare("select studnum from student where $grp = ? order by lastname, firstname");
	$sth->execute( $arr{groupvalue} );
	if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my $studnum = $sth->fetchrow ) {
	    push @students, $studnum;
	}

    } else { # all records in nominal roll table

	my %students;

	my $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");

	my $sth = $dbh->prepare("select studnum from $nrtable order by studnum");
	$sth->execute;
	if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

	while ( my $studnum = $sth->fetchrow ) {
	    $sth1->execute( $studnum);
	    if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my ($lastname, $firstname ) = $sth1->fetchrow;

	    $students{"$lastname$firstname$studnum"} = $studnum;
	}

	foreach my $key ( sort keys  %students ) {
	    push @students, $students{$key};
	}
	    
    }


    # Select Students
    # Form Start
    print qq{<td><form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="2">\n};
    print qq{<input type="hidden" name="field" value="$arr{field}">\n};

    print qq{<div><input type="submit" name="type" value="$lex{Edit}">};
    print qq{<input type="submit" name="type" value="$lex{Delete}"></div>\n};

    # Table Start
    print qq{<table cellpadding="3" border="1" cellspacing="0">\n};
    print qq{<tr><th>$lex{Select}</th><th>$lex{Student}</th><th>$lex{Grade}/<br>$lex{Homeroom}</th>};
    print qq{<th>Tranfers<br>Enrol/Withdraw</th><th>Service<br>Provision</th></tr>\n};

    # Get Name
    my $sth1 = $dbh->prepare("select lastname, firstname, grade, homeroom from studentall where studnum = ?");
    my $sth2 = $dbh->prepare("select count(*) from $nrtable where studnum = ?");

    my $sth3 = $dbh->prepare("select count(*) from studentwd where studnum = ?");
    my $sth4 = $dbh->prepare("select type,date from transfer where studnum = ? order by date desc");

    my $sth5 = $dbh->prepare("select serviceprovision from $nrtable where studnum = ?");
    
    my $count = 1;
    foreach my $studnum ( @students ) {

	$sth1->execute($studnum);
	if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ($lastname, $firstname, $grade, $homeroom ) = $sth1->fetchrow;

	# Check for NR record
	$sth2->execute($studnum);
	if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $nrcount = $sth2->fetchrow;


	# Check if withdrawn.
	my $wd;
	$sth3->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $wdcount  = $sth3->fetchrow;
	if ( $wdcount ) {
	    $wd = qq{<span style="color:red;font-weight:bold;">WD</span>\n};
	}

	# Service Provision (current or last year)
	$sth5->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $serviceprovision  = $sth5->fetchrow;

	
	# Get Transfers (Enrol/Withdraw)
	my (@transfers, $transfers);
	$sth4->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my ($type,$date) = $sth4->fetchrow ) {
	    push @transfers, "$type - $date";
	}
	my $transfers = join('<br>', @transfers[0..1]);

	if ( $nrcount > 0 ) {
	    print qq{<tr><td class="cn"><input type="checkbox" name="$studnum" value="1" $checked></td>\n};
	} else {
	    print qq{<tr><td>$lex{Record} $lex{'Not Found'}</td>\n};
	}

	
	print qq{<td>$count. $wd <b>$lastname</b>, $firstname ($studnum)</td>};
	print qq{<td class="cn">$grade/$homeroom</td><td>$transfers</td>};
	print qq{<td>$metasp{$serviceprovision}</td></tr>\n};
	

	$count++;

    }

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

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


} # end of selectStudents


#---------------
sub editStudents {
#---------------

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

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


    my $formtext;
    if ( $field eq 'all' ) {

	# 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";
	}
	{ local $/; $formtext = <FH>; close FH;}

    } else { # dynamically create the form for the fields of interest
	#$formtext = "<table cellpadding="3" cellspacing="0" border="0">\n";
	$formtext = q{<tr><td class="bra"><@studnum@></td>}; #<td class="la">};
	#$formtext .= q{<*}. $field. q{*></td>";
	$formtext .= q{<td><@}. $field. '@>'. "</td></tr>\n";
	# $formtext .= "</table><p></p>\n";
    }
#   print qq{Formtext:$formtext<br>\n};


    # Read in meta library
    eval require "../../lib/libmeta.pl";
    if ( $@ ) {
	print $lex{Error}. ": $@<br>\n";
	die $lex{Error}. ": $@\n";
    }


    # 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 = ?");
    $sth2 = $dbh->prepare("select count(*) from studentwd 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;
    }


    # print top of form
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="4">\n};
    print qq{<input type="submit" value="$lex{'Save'}">\n};

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

    # If not all fields, start a table.
    if ( $field ne 'all' ) { # we're creating a table
	print qq{<table cellpadding="3" cellspacing="0" border="0">\n};
	print qq{<tr><th>$lex{Name}</th><th>$fieldnames{$field}</th></tr>\n};
    }

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

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

	
	# Check if withdrawn.
	my $wd;
	$sth2->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $wdcount  = $sth2->fetchrow;
	if ( $wdcount ) {
	    $wd = qq{<span style="color:red;font-weight:bold;">WD</span>\n};
	}

	
	# Now get name; $sth1 defined above.
	$sth1->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ( $lastname, $firstname ) = $sth1->fetchrow;
	my $name = qq{<span style="font-weight:bold;font-size:120%;">$firstname $lastname</span> ($studnum)};

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

	# Get their record, if any.
	$sth->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $recref = $sth->fetchrow_hashref;
	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;

	if ( $field eq 'all' ) {
	    print qq{<div style="padding:0.6em;border:1px solid gray;margin:0.5em;">\n};
	    print qq{$text\n</div>\n};
	} else {
	    print qq{$text\n};
	}
    }

    if ( $field ne 'all' ) { # we're creating a table
	print qq{</table>\n};
    }

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

    exit;

}


#----------------
sub updateRecords {
#----------------

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

    my $sth1 = $dbh->prepare("select count(*) from student_inac where studnum = ?");


    foreach my $key ( sort keys %arr ) {

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

	# Check for existing record
	$sth1->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $count = $sth1->fetchrow;
	if ( not $count ) { # Error!
	    print qq{<div>$lex{Error}: No Nominal Roll record for: $studnum</div>\n};
	    next;
	}


	# Update Record
	if ( $arr{$key} ) { # if we have a value
	    my $sth = $dbh->prepare("update student_inac set $fieldid = ? where studnum = ?");
	    $sth->execute( $arr{$key}, $studnum );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

	} else { # no value for field; set to NULL
	    my $sth = $dbh->prepare("update student_inac set $fieldid = NULL where studnum = ?");
	    $sth->execute( $studnum );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	}

    }

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

    print qq{<p><a href="$self">$lex{Edit}/$lex{Delete} $lex{'Nominal Roll'}</a>};
    print qq{ | <a href="$homepage">$lex{Main}</a> ]</p>\n};
    print qq{</body></html>\n};

    exit;

}


#----------------
sub confirmDelete {
#----------------

    delete $arr{field}; # not useful, here, only for edit updates

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

    my $sth = $dbh->prepare("select lastname, firstname, grade from studentall where studnum = ?");
    my $sth1 = $dbh->prepare("select count(*) from student where studnum = ?");


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

    print qq{<table cellpadding="3" border="1" cellspacing="0">\n};

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

    print qq{<tr><th>$lex{Student}</th><th>$lex{Grade}</th><th>$lex{Select}</th></tr>\n};
    
    foreach my $studnum ( sort keys %arr ) {

	$sth->execute( $studnum );
	my ( $lastname, $firstname, $grade ) = $sth->fetchrow;

	
	# Check for current student
	$sth1->execute( $studnum );
	my $scount = $sth1->fetchrow;
		
	
	print qq{<tr><td><b>$lastname</b>, $firstname ($studnum)</td><td>$grade</td>\n};
	if ( $scount ) { # Warn he/she is current, turn off checkbox
	    print qq{<td><span style="color:red;font-weight:bold;font-size:120%;">Current Student!</span>};
	    print qq{ <input type="checkbox" name="$studnum" value="1">\n};

	} else {
	    print qq{<td><input type="checkbox" name="$studnum" value="1" checked="checked">\n};
	}
	
	print qq{</td></tr>\n};

    }

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

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

    exit;

}


#----------------
sub deleteRecords {
#----------------

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

    my $sth = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");
    my $sth1 = $dbh->prepare("select * from student_inac where studnum = ?");

    print qq{<table cellpadding="3" border="1" cellspacing="0">\n};
    print qq{<tr><th>$lex{'Nominal Roll'} $lex{'Record(s)'} $lex{Moved}</th></tr>\n};

    foreach my $studnum ( sort keys %arr ) { 

	# print qq{Student: $studnum<br>\n};

	# Get Name
	$sth->execute( $studnum );
	my ( $lastname, $firstname ) = $sth->fetchrow;
	# print qq{$firstname $lastname<br>\n};

	# Get and Move Record
	$sth1->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my @rec = $sth1->fetchrow;

	if ( not @rec ) { #Error!
	    print qq{<h1>$lex{Error}: $lex{Record} $lex{'Not Found'}: $studnum</h1>\n};
	    next;
	}

	$rec[0] = $sql{default};  # reset the id.
	foreach my $field ( @rec ){ 
	    if ( $field ne $sql{default} ) { $field = $dbh->quote( $field ); }
	}
	my $recordstring = join(', ', @rec);

	# Insert New Record
	my $sth2 = $dbh->prepare("insert into student_nomrollwd values ( $recordstring )");
	$sth2->execute;
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

	# Delete Original
	my $sth3 = $dbh->prepare("delete from student_inac where studnum = ?"); 
	$sth3->execute( $studnum );

	if ( not $DBI::errstr ) {
	    print qq{<tr><td>$firstname $lastname $lex{'Nominal Roll'} };
	    print qq{$lex{Record} $lex{'Moved'}</td></tr>\n};
	
	} else {
	    print qq{<tr><td>$lex{Error}: $DBI::errstr</td></tr>\n};
	}

   
    }
    


    print qq{</table>\n};

    print qq{<p>[ <a href="$self">$lex{Edit}/$lex{Delete} $lex{'Nominal Roll'} $lex{Record}</a>};
    print qq{ | <a href="$homepage">$lex{Main}</a> ]</p>\n};

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

    exit;

}
