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

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

my %lex = ('Reset' => 'Reset',
	   'Main' => 'Main',
	   'Eoy' => 'Eoy',
	   'Continue' => 'Continue',
	   'Fields' => 'Fields',
	   'Field' => 'Field',
	   'Type' => 'Type',
	   'Enter Values' => 'Enter Values',
	   'No Field Selected' => 'No Field Selected',
	   'Field Fill' => 'Field Fill',
	   'Grade' => 'Grade',
	   'Homeroom' => 'Homeroom',
	   'Student Group' => 'Student Group',
	   'Separate with Spaces' => 'Separate with Spaces',
	   'Blank=All' => 'Blank=All',
	   'Student' => 'Student',
	   'Contact' => 'Contact',
	   'Error' => 'Error',
	   'Record(s) Updated' => 'Record(s) Updated',
	   'Blank' => 'Blank',
	   'Students' => 'Students',
	   'Current' => 'Current',
	   'Withdrawn' => 'Withdrawn',
	   'Update' => 'Update',
	   'Select' => 'Select',

	   );


my $self = 'resetselect.pl';
my $maxTypeCount = 30; # don't allow more than $maxTypeCount different types in a selection list
my %disallow = qw(studid 1 grade 1 homeroom 1 medical 1 utag 1 owing 1 studnum 1 entry 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.
my $title = qq{$lex{Reset} $lex{Student} $lex{Fields}};

print qq{$doctype\n<html><head><title>$title</title>
<link rel="stylesheet" href="$css" type="text/css">
$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>$title</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
    from meta where tableid = 'student' order by fieldname");
    $sth->execute;
    my (@fields, %fieldnames);
    while ( ( my $fieldid, $fieldname ) = $sth->fetchrow ) {
	if ( $disallow{$fieldid} ) { 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;margin:0.5em;">\n};

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

    # Now type of selection: Type in Values, (Individual Text Input) or Select from a List
    print qq{<tr><td class="ra">$lex{Type}</td><td>\n};
    print qq{<input type="radio" name="inputtype" value="text" checked>$lex{'Enter Values'}<br>\n};
    print qq{<input type="radio" name="inputtype" value="select">};
    print qq{Select from Existing List\n};
    print qq{</td></tr>\n};

    # Now a group selection by homeroom or grade, accept multiple values separated by spaces...
    print qq{<tr><td class="ra">$lex{'Student Group'} ($lex{'Separate with Spaces'}) };
    print qq{</td><td><select name="grouptype">\n};
    print qq{<option>$lex{Grade}</option><option>$lex{Homeroom}</option></select>\n};
    print qq{ <input type="text" name="groupvalue" size="15"> };
    print qq{$lex{'Blank=All'}</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>\n};
    print qq{<input type="text" name="defaultfill" size="30"></td></tr>\n};

    # Tables.... student, withdrawn
    print qq{<tr><td class="ra">$lex{Students}</td><td>\n};
    print qq{<input type="radio" name="studtable" value="current" checked="checked"> $lex{Current} };
    print qq{<input type="radio" name="studtable" value="withdrawn"> $lex{Withdrawn}};
    print qq{</td></tr>\n};

    # Additional Display Fields
    print qq{<tr><td class="ra">Additional Display $lex{Field}</td><td>\n};
    print qq{<select name="displayfield"><option></option>\n};
    foreach my $fld ( @fields ) {
	print qq{<option value="$fld">$fieldname{$fld} ($fld)</option>\n};
    }
    print qq{</select>\n};
    print qq{</td></tr>\n};
    
    print qq{</table>\n};
    print qq{<input type="submit" value="$lex{Continue}">\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}; }

    # Passed: inputtype : 'text' or 'select'
    #         field: fieldid
    #         groupvalue:
    #         grouptype:
    #         displayfield: additional field to display along with the name

    my $mode = $arr{inputtype};
    delete $arr{inputtype};

    my $studtable = 'student';
    my $tableview = "<h3>$lex{Current} $lex{Students}</h3>\n";

    if ( $arr{studtable} eq 'withdrawn' ) {
	$studtable = 'studentwd';
	$tableview = "<h3>$lex{Withdrawn} $lex{Students}</h3>\n";
    } elsif ( 
	$arr{studtable} eq 'both' ) {
	$studtable = 'studentall';
	$tableview = "<h3>$lex{Current}+$lex{Withdrawn} $lex{Students}</h3>\n";
    }

    # Display Field
    my $displayfield = $arr{displayfield};
    delete $arr{displayfield};

    # Load 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{<h3>$lex{'No Field Selected'}</h3>\n};
	print qq{</body><html>\n};
	exit;
    }
    
    #print qq{Name: $fieldname ID: $fieldid<br>\n};

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


    # Get Display Field's Meta Name
    my $sth = $dbh->prepare("select fieldname from meta where fieldid = ?");
    $sth->execute( $displayfield );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    my $displayFieldName = $sth->fetchrow;


    
    # Now get this field's metadata
    my $sth = $dbh->prepare("select * from meta where fieldid = ?");
    $sth->execute( $fieldid );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    my $ref = $sth->fetchrow_hashref;
    my %meta = %$ref;

    my $defaultvalue = $meta{defaultvalue};
    my $fieldname = $meta{fieldname};

    my @defaults = split(' ',$defaultvalue);
    if ( not @defaults ) { # no defaults found; use values present if less than $maxTypeCount

	my $sth1 = $dbh->prepare("select count(distinct $fieldid) from student");
	$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 
              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] = ''; } # tilde signals blank start field.
    }


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

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


	$sth = $dbh->prepare("select studnum from $studtable where $group = ? 
           order by $sortorder");
	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
	$sth = $dbh->prepare("select studnum from $studtable order by $sortorder");
	$sth->execute;
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my $studnum = $sth->fetchrow ) {
	    push @students, $studnum;
	}
    }

    
    my $sth = $dbh->prepare("select * from $studtable where studnum = ?");

    # print which students we're viewing
    print $tableview, "\n";


    # 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="hidden" name="studtable" value="$studtable">\n};

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

    print qq{<table cellpadding="4" cellspacing="0" border="1" style="background-color:#CCD;">\n};
    print qq{<tr><th>$lex{Student}</th>};
    if ( $displayfield ) {
	print qq{<th>$displayFieldName</th>};
    }
    print qq{<th>$lex{Grade}/$lex{Homeroom}</th>};
    print qq{<th>$fieldname</th></tr>\n};

    my $sth = $dbh->prepare("select * from $studtable where studnum = ?");

    # Loop through students
    foreach my $studnum ( @students ) {

	# Get Student Info
	$sth->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	# my ( $lastname, $firstname, $grade, $homeroom, $resetfield, $displayfield ) = $sth->fetchrow;
	my $ref = $sth->fetchrow_hashref;
	my %r = %$ref;

	# If no field in current record, and we have default fill set, then put it in...
	if ( ( not defined $r{$fieldid} or $r{$fieldid} eq '') and defined $defaultfill ) {
	    $r{$fieldid} = $defaultfill;
	}

	print qq{<tr><td><b>$r{lastname}</b>, $r{firstname} ($r{studnum})</td>};
	if ( $displayfield ) { print qq{<td>$r{$displayfield}</td>}; }
	print qq{<td>$r{grade} / $r{homeroom}</td>\n};

	# Make sure we can use default values, etc.
	if ( $mode eq 'select' and $meta{formtype} ne 'text' ) { $mode = 'text'; }

	if ( $mode eq 'text' ) {
	    my $val = metaInputField('student', $fieldid, $r{$fieldid}, $dbh,$r{studnum});
	    print qq{<td>$val</td>\n};
	    # print qq{<input type="text" name="$studnum" value="$resetfield" size="$viewsize">};

	} elsif ( $mode eq 'select' ) {
	    print qq{<td><select name="$studnum"><option>$resetfield</option>\n};
	    # Now print all values...
	    foreach my $def ( @defaults ) {
		print qq{<option>$def</option>};
	    }
	    print qq{</select></td>\n};

	} else {
	    # Fail very badly...
	    print qq{<h1>$lex{Type} $lex{Error}</h1>\n};
	    print qq{</body></html>\n};
	    exit;

	}

	print qq{</tr>\n};

    } # End of Student Loop

    print qq{</table><input type="submit" value="$lex{Update}">\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{<h3>$lex{'No Field Selected'}</h3>\n};
	print qq{</body></html>\n};
	exit;
    }

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

    my $sth = $dbh->prepare("update $studtable set $resetfield = ? where studnum = ?");

    foreach my $key ( keys %arr ) {
	if ( $key =~ m/:/ ) { # we have a colon separated value
	    my ($id, $fld ) = split(':', $key);
	    $sth->execute( $arr{$key}, $id );
	} else {
	    $sth->execute( $arr{$key}, $key );
	}
	if ($DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    }


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

    } 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="$self">$lex{Reset} $lex{Fields}</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 writeChanges
