#!/usr/bin/perl
# Copyright Les Richardson 2001-2019

# This file is part of Open Administration for Schools. Released under GPL Licensing.

my %lex = ('Nominal Roll' => 'Nominal Roll',
	   'Reset' => 'Reset',
	   'Main' => 'Main',
	   'Eoy' => 'Eoy',
	   'Continue' => 'Continue',
	   'Select Field' => 'Select Field',
	   'No Field Selected' => 'No Field Selected',
	   'Field Fill' => 'Field Fill',
	   'Grade' => 'Grade',
	   'Homeroom' => 'Homeroom',
	   'Student' => 'Student',
	   'Contact' => 'Contact',
	   'Error' => 'Error',
	   'Record(s) Updated' => 'Record(s) Updated',
	   'Blank' => 'Blank',
	   'Not Found' => 'Not Found',
	   'Field' => 'Field',
	   );

my $self = 'resetnomroll.pl';
my $maxTypeCount = 30; # don't allow more than $maxTypeCount different types in a selection list

#my %disallow = qw(id 1); # double since a HASH.


use DBI;
use CGI;

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);
$dbh->{mysql_enable_utf8} = 1;


# Show top of page.
print qq{$doctype\n<html><head><title>$lex{Reset} $lex{Student} $lex{Field}</title>
<link rel="stylesheet" href="$css" type="text/css">\n};

if ( not $arr{page} ) { # load jQuery
    print qq{<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>\n};
}

print qq{$chartype\n</head><body>\n};

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

print qq{<h1>$lex{Reset} $lex{'Nominal Roll'}</h1>\n};


# Select what to do.
if ( not $arr{page} ) {
    showStartPage();

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

} elsif ( $arr{page} == 2 ) {
    delete $arr{page};
    writeChanges();
}


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

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

    # Start the form.
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="submit" value="$lex{Continue}">\n};

    print qq{<table cellpadding="3" cellspacing="0" border="1" };
    print qq{style="background-color:#CCD;">\n};

    # Display the fields from which to select one.
    print qq{<tr><td class="ra">$lex{'Select Field'}</td><td>\n};
    print qq{<select name="field" id="field"><option></option>\n};
    foreach my $fld ( @fields ) {
	print qq{<option value="$fld">$fieldname{$fld}</option>\n};
    }
    print qq{</select></td></tr>\n};

    # Add a 'Default' fill value for any fields that are blank...
    print qq{<tr><td class="ra">$lex{Blank} $lex{'Field Fill'}</td><td id="defaultfill">\n};
    # print qq{<select name="defaultfill" id="defaultfill">};
    print qq{</td></tr>\n};

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

    print qq{<script type="text/javascript">
    \$('#field').change( function(e){
      var choice = \$(this).val();
      \$('#defaultfill').load('/cgi-bin/nomroll/resetshowchoice.pl', { fieldid:choice } );
    });
    </script>\n};

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

    exit; 


} # end of showStartPage


#----------------
sub selectChanges {
#----------------

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

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

    # Setup field name and fieldid.
    my $fieldid = $arr{field};
    if ( not $fieldid ) {
	print qq{<h1>$lex{'No Field Selected'}</h1>\n};
	print qq{</body><html>\n};
	exit;
    }

    
    # Set Default Fill Value (if any)
    my $defaultfill;
    if ( $arr{defaultfill} ) {
	$defaultfill = $arr{defaultfill};
    }

    # Now get this field's metadata
    my $sth = $dbh->prepare("select viewsize, defaultvalue, fieldname, formtype from meta where fieldid = ?");
    $sth->execute( $fieldid );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    my ($viewsize, $defaultvalue, $fieldname, $formtype ) = $sth->fetchrow;


    my @defaults = split(/\s+/, $defaultvalue);

    if ( not @defaults ) { # no defaults found; use values present if less than $maxTypeCount
	my $sth1 = $dbh->prepare("select count(distinct $fieldid) from student_inac");
	$sth1->execute;
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $count = $sth1->fetchrow;

	if ( $count < $maxTypeCount ) {
	    my $sth1 = $dbh->prepare("select distinct $fieldid from student_inac 
              where $fieldid is not null and $fieldid != '' order by $fieldid desc");
	    $sth1->execute;
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    while (my $val = $sth1->fetchrow) {
		push @defaults, $val;
	    }
	    @defaults = reverse(@defaults);
	}
    } else { # @defaults exists
	if ( $defaults[0] eq '~' ) { $defaults[0] = ''; } # remove tilde, signals blank start field.
    }


    # Get Students
    my $group;
    my @groups;
    my $sortorder = 'lastname, firstname';
    my @students;
    my %sortstudents;

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

    if ( $arr{groupvalue} ) { # then we have to do something...
	@groups = split /\s+/, $arr{groupvalue};
	if ( $arr{grouptype} eq 'grade' ) {
	    $group = 'grade';
	} else {
	    $group = 'homeroom';
	}
	$sortorder = "$group, lastname, firstname";


	$sth = $dbh->prepare("select studnum from student_inac where $group = ?");
	foreach my $grp ( @groups ) {
	    $sth->execute( $grp );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    while ( my $studnum = $sth->fetchrow ) {
		push @students, $studnum;
	    }
	}

    } else { # all students in student_inac;

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

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

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


    $sth = $dbh->prepare("select lastname, firstname, grade, homeroom 
      from studentall where studnum = ?");

    $sth1 = $dbh->prepare("select $fieldid from student_inac where studnum = ?");
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

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


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

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

    print qq{<table cellpadding="5" cellspacing="0" border="1" };
    print qq{style="background-color:#CCD;">\n};
    print qq{<tr><th>$lex{Student}</th><th>$lex{Grade}<br>/$lex{Homeroom}</th>\n};
    print qq{<th>Transfer</th><th><b>$fieldname</b></th></tr>\n};

    my $count = 1;
    # Loop through students
    foreach my $key ( sort keys %sortstudents ) {

	my $studnum = $sortstudents{$key};

	# Get Student Info
	$sth->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ( $lastname, $firstname, $grade, $homeroom ) = $sth->fetchrow;
	if ( not $lastname ) { $lastname = $lex{'Not Found'}; }

	# Get Nomroll Info.
	$sth1->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $resetfield  = $sth1->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};
	}

	# 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 no field in current record, and we have default fill set, then put it in...
	if ( (not defined $resetfield or $resetfield eq '') and defined $defaultfill ) {
	    $resetfield = $defaultfill;
	}

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

	# my $inputtext = metaInputField('student_inac', $fieldid, $resetfield, $dbh, $studnum );
	my $inputtext = metaInputField('student_inac', $fieldid, $resetfield, $dbh, $studnum );
	print qq{$inputtext</td></tr>\n};

	$count++;

    } # End of Student Loop


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

    exit;

} # end of selectChanges




#---------------
sub writeChanges {
#---------------

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

    # first get field name to update
    if ( $arr{resetfield} ) {
	$resetfield = $arr{resetfield};
	delete $arr{resetfield};
    } else {
	print qq{<h1>$lex{'No Field Selected'}</h1>\n};
	print qq{</body></html>\n};
	exit;
    }


    foreach my $key ( keys %arr ) {

	my ( $studnum, $tmp ) = split(/:/, $key);
	$sth = $dbh->prepare("update student_inac set $resetfield = ? where studnum = ?");
	$sth->execute( $arr{$key}, $studnum );

	# print qq{Update: Val:$arr{$key} Stud:$studnum<br>\n};

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


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

    } else {
	print qq{<h3>$lex{Error}: $DBI::errstr</h3>\n};
	print qq{<h3>$lex{Contact} $adminname at <a href="mailto:$adminemail">$adminemail</a></h3>\n};
    }

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

    exit;

} # end of writeChanges
