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

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

my %lex = ('Field' => 'Field',
	   'Staff' => 'Staff',
	   'Reset' => 'Reset',
	   'Main' => 'Main',
	   'Eoy' => 'Eoy',
	   'Continue' => 'Continue',
	   'Select' => 'Select',
	   'Type' => 'Type',
	   'Enter Values' => 'Enter Values',
	   'Select from List' => 'Select from List',
	   'Not Selected' => 'Not Selected',
	   'Fill' => 'Fill',
	   'Contact' => 'Contact',
	   'Error' => 'Error',
	   'Record(s) Updated' => 'Record(s) Updated',
	   'Blank' => 'Blank',
	   'Multiple' => 'Multiple',
	   'Add' => 'Add',


	   );

my $self = 'staffreset.pl';
my $maxTypeCount = 30; # don't allow more than $maxTypeCount different types in a selection list
my %disallow = qw(id 1 userid 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 = "$lex{Reset} $lex{Staff} $lex{Field}";

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 staff fields, @fields and %fieldname hash.
    my %fieldnames = ();
    my $sth = $dbh->prepare("select fieldid, fieldname
     from meta where tableid = 'staff'");
    $sth->execute;
    while ( ( my $fieldid, $fieldname ) = $sth->fetchrow ) {
	if ( $disallow{$fieldid} ) { next; } # skip disallowed fields to edit
	$fieldnames{$fieldname} = $fieldid;
    }

    $sth = $dbh->prepare("select distinct field_name from staff_multi");
    $sth->execute;
    while ( my $fieldname = $sth->fetchrow ) {

	$fieldid = 'M:'. $fieldname;
	$fieldname .= ' (multi)';
	$fieldnames{$fieldname} = $fieldid;

    }


    # 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} $lex{Field}</td><td>\n};
    print qq{<select name="field"><option></option>\n};
    foreach my $key ( sort keys %fieldnames ) {
	print qq{<option value="$fieldnames{$key}">$key</option>\n};
    }
    print qq{</select></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>};
    print qq{$lex{'Enter Values'}<br>\n};
    print qq{<input type="radio" name="inputtype" value="select">};
    print qq{$lex{'Select from List'}\n};
    print qq{</td></tr>\n};


    # Add a 'Default' fill value for any fields that are blank...
    print qq{<tr><td class="ra">$lex{Fill} $lex{Blank} $lex{Field}</td><td>\n};
    print qq{<input type="text" name="defaultfill" size="20"></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 Values:
    # inputtype : 'text' or 'select'
    # field: fieldid ( M:fieldid if in staff_multi )
    # defaultfill: value to populate empty fields with

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

    # Setup field name and fieldid.
    my ($multimode, $fieldid ) = split(':', $arr{field} );
    if ( $multimode and not $fieldid ) { 
	$fieldid = $multimode;
	$multimode = '';
    }
    if ( $multimode ne 'M' ) { $multimode = 0; } # turn it off, if not M:field type.
    if ( not $fieldid ) {
	print qq{<h1>$lex{Field} $lex{'Not Selected'}</h1>\n};
	print qq{</body><html>\n};
	exit;
    }

    # Get Field Description from Meta
    my $sth = $dbh->prepare("select fieldname from meta where fieldid = ? and tableid = 'staff'");
    $sth->execute( $fieldid );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    my $fielddesc = $sth->fetchrow;


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


    if ( not $multimode ) { # normal staff table

	# Get this field's metadata
	my $sth = $dbh->prepare("select viewsize, defaultvalue from meta 
           where fieldid = ? and tableid = 'staff'");
	$sth->execute( $fieldid );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ($viewsize, $defaultvalue) = $sth->fetchrow;
	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 staff");
	    $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 staff 
                 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.
	}

    } else { # multimode; select from staff_multi;
	# No metadata for this, only load particular types of values.

	my $sth = $dbh->prepare("select count(distinct field_value) from staff_multi 
         where field_name = ?");
	$sth->execute( $fieldid );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $count = $sth->fetchrow;

	if ( $count < $maxTypeCount ) {
	    my $sth = $dbh->prepare("select distinct field_value from staff_multi 
              where field_name = ? and 
              field_value is not NULL and field_value != '' order by field_value");
	    $sth->execute($fieldid);
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    while ( my $val = $sth->fetchrow ) {
		push @defaults, $val;
	    }
	    @defaults = reverse(@defaults);
	}
    } # end of multimode getting default values.


    # Get Staff
    my $sortorder = 'lastname, firstname';
    my @staff = ();

    $sth = $dbh->prepare("select userid from staff order by $sortorder");
    $sth->execute;
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    while ( my $userid = $sth->fetchrow ) {
	push @staff, $userid;
    }


    # Setup for Getting Staff Names
    $sth = $dbh->prepare("select lastname, firstname from staff where userid = ?");

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

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

    print qq{<table cellpadding="4" cellspacing="0" border="1" };
    print qq{style="background-color:#CCD;">\n};
    print qq{<tr><th>$lex{Staff}</th><th><b>$fielddesc $fieldid};
    if ( $multimode ) { print qq{ ($lex{Multiple})}; }
    print qq{</b></th>};
    if ( $multimode ) { print qq{<th>$lex{Add}</th>}; }
    print qq{</tr>\n};


    # Loop through staff
    foreach my $userid ( @staff ) {

	my $resetfield;

	# Get Staff Name
	$sth->execute( $userid );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ( $lastname, $firstname ) = $sth->fetchrow;

	print qq{<tr><td><b>$lastname</b>, $firstname ($userid)</td><td>\n};

	# Get Field Values
	if ( not $multimode ) { 
	    my $sth1 = $dbh->prepare("select $fieldid from staff where userid = ?");
	    $sth1->execute( $userid );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    $resetfield = $sth1->fetchrow;

	    # 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;
	    }

	    if ( $mode eq 'text' ) {
		print qq{<input type="text" name="$userid" value="$resetfield" };
		print qq{style="width:50ch;">};

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

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

	} else { # multimode

	    my $sth1 = $dbh->prepare("select id, field_value from staff_multi 
              where field_name = '$fieldid' and userid = ?");
	    $sth1->execute( $userid );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    while ( my ($id, $fieldvalue) = $sth1->fetchrow ) {
		print qq{<input type="text" name="$id" value="$fieldvalue"> \n};
	    }
	    print qq{</td><td><input type="text" style="width:12ch;" name="add:$userid"></td>\n};

	}

	print qq{</td></tr>\n};


    } # End of Staff 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}; }

    my $fieldid = $arr{resetfield};
    delete $arr{resetfield};
    my $multimode = $arr{multimode};
    delete $arr{multimode};

    foreach my $key ( keys %arr ) { 

	# Find the Add fields - only in multimode.
	if ( $key =~ m/^add:/ ) {

	    if ( $arr{$key} ) { # have a value
		my ($dud, $userid ) = split(/:/, $key);
		$sth = $dbh->prepare("insert into staff_multi (userid, field_name, field_value) 
                  values(?,?,?)");
		$sth->execute( $userid, $fieldid, $arr{$key} );
		if ($DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

	    } # otherwise skip the blank add record.

	} else { # existing records

	    if ( not $arr{$key} and $multimode ) { # no value and doing staff_multi, delete the record
		$sth = $dbh->prepare("delete from staff_multi where id = ?");
		$sth->execute( $key );

	    } elsif ( $multimode ) { # $key contains the record id.
		$sth = $dbh->prepare("update staff_multi set field_value = ? where id = ?");
		$sth->execute( $arr{$key}, $key );
		if ($DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

	    } else { # $key contains the userid.
		$sth = $dbh->prepare("update staff set $fieldid = ? where userid = ?");
		$sth->execute( $arr{$key}, $key );
		if ($DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    }
	} # end of existing records

    } # end of record loop


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

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

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

    exit;

} # end of writeChanges
