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

# Rip out all student stuff... make it a Staff Only function.

# 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',
	   'Select from List' => 'Select from List',
	   'No Field Selected' => 'No Field Selected',
	   'Field Fill' => 'Field Fill',
	   'Grade' => 'Grade',
	   'Homeroom' => 'Homeroom',
	   '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',
	   'Staff' => 'Staff',
	   'Position' => 'Position',

	   );


my $self = 'resetstaff.pl';
my $mode = 'staff';

my $maxTypeCount = 30; # don't allow more than $maxTypeCount different types in a selection list
my %disallow = qw(id 1 alt_street 1 alt_city 1 alt_prov 1 alt_country 1 alt_pcode 1 );

my %multifields = qw(position 1 grade 1 homeroom 1 access 1);


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



# Show top of page.
my $title = "$lex{Reset} $lex{Staff} $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 staff fields, and defaults into a @fields and %fieldname hash.
    my $tableid = 'staff';
 
    my $sth = $dbh->prepare("select fieldid, fieldname
    from meta where tableid = '$tableid' 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 parentheses.

	push @fields, $fieldid;
	$fieldname{ $fieldid } = $fieldname;
    }


    # insert the multi record fields.
    foreach my $fld ( sort keys %multifields ) {
	unshift @fields, $fld;
	my $fname = ucfirst $fld;
	$fieldname{$fld} = $fname;
    }


    # 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 $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{$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{Blank} $lex{'Field Fill'}</td><td>\n};
    print qq{<input type="text" name="defaultfill" size="30"></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
    #         defaultfill: value to fill blank fields

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

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

    my ( $usertable, $tableview, $metafield );
    $usertable = 'staff';
    $tableview = "<h3>$lex{Staff}</h3>\n";
    $metafield = 'staff';

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


    if ( $multifields{$fieldid} ) { # we have chosen a multicolumn field to edit.
	$inputmode = 'multi';
    }

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

    my @defaults;
    if ( $inputmode ne 'multi' ) {

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

	if ( $fieldid eq 'position' ) {
	    my $sth = $dbh->prepare("select datavalue from conf_system where dataname = ?");
	    $sth->execute( 'g_position' );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $datavalue = $sth->fetchrow;
	    eval $datavalue;
	    if ( $@ ) {
		print $lex{Error}. " $@<br>\n";
		die $lex{Error}. " $@\n";
	    }
	    @defaults = @g_position;

	} elsif ( $fieldid eq 'grade' ) {
	    my $sth = $dbh->prepare("select distinct grade from student 
              where grade is not NULL and grade != ''");
	    $sth->execute;
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    while ( my $gr = $sth->fetchrow ) {
		push @defaults, $gr;
	    }
	    @defaults = sort { $a <=> $b } @defaults;

	} elsif ( $fieldid eq 'homeroom' ) {
	    my $sth = $dbh->prepare("select distinct homeroom from student 
              where homeroom is not NULL and homeroom != ''");
	    $sth->execute;
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    while ( my $hr = $sth->fetchrow ) {
		push @defaults, $hr;
	    }
	    @defaults = sort { $a <=> $b } @defaults;
	}
    }


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

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

    my $sth;
    if ( $inputmode ne 'multi' ) {
	$sth = $dbh->prepare("select lastname, firstname, $fieldid from staff where userid = ?");
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

    } else { # just get name
	$sth = $dbh->prepare("select lastname, firstname from staff where userid = ?");
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    }

    # multirecord select
    $sth1 = $dbh->prepare("select id, field_value from staff_multi where userid = ? and field_name = ?");


    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="submit" value="$lex{Update}">\n};

    print qq{<table cellpadding="4" cellspacing="0" border="1">\n};
    print qq{<tr><th></th><th>$lex{Position}</th>\n<th>};
    print qq{<b>$arr{field}</b></th></tr>\n};


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

	# Get user Info
	$sth->execute( $userid );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $ref = $sth->fetchrow_hashref;
	my %rec = %$ref;


	# get Position(s), Multirec
	$sth1->execute( $userid, 'position');
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my @position = ();
	while ( my ($id,$pos) = $sth1->fetchrow ) {
	    push @position, $pos;
	}
	my $position = join('<br>',@position);

	# print row start
	print qq{<tr><td>$rec{firstname} $rec{lastname} ($userid)</td><td>$position</td><td>\n};

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


	if ( $inputmode eq 'text' ) {
	    print qq{<input type="text" name="U:$userid" value="$rec{$fieldid}" };
	    print qq{size="$viewsize">};

	} elsif ( $inputmode eq 'select' ) {

		my $inputtext = metaInputField('staff', $fieldid, $rec{$fieldid}, $dbh, "U:$userid" );
		print qq{$inputtext\n};

=head
	    print qq{<select name="U:$userid"><option>$rec{$fieldid}</option>\n};
	    if ( $rec{$fieldid} ) { print qq{<option></option>\n}; } # put in a blank
	    # Now print all values...
	    foreach my $def ( @defaults ) {
		if ( $def eq $rec{$fieldid} ) { next; } # skip current value
		print qq{<option>$def</option>};
	    }
	    print qq{</select>\n};
=cut

	} else { # multi record mode, we must load the record(s)

	    $sth1->execute( $userid, $fieldid );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $first = 1;
	    while ( my ($id, $val) = $sth1->fetchrow ) {
		$first = 0;
		if ( @defaults ) { # use select
		    print qq{<select name="R:$id"><option>$val</option>};
		    if ( $val ) { print qq{<option></option>\n}; } # put in a blank
		    foreach my $def ( @defaults ) {
			print qq{<option>$def</option>};
		    }
		    print qq{</select>\n};

		} else { # use text
		    print qq{<input type="text" name="R:$id" value="$val"> };
		}
	    }
	    if ( $first ) {
		if ( @defaults ) { # use select
		    print qq{<select name="R:add:$userid"><option></option>};
		    foreach my $def ( @defaults ) {
			print qq{<option>$def</option>};
		    }
		    print qq{</select>\n};

		} else { # use text
		    print qq{<input type="text" name="R:add:$userid" value=""> };
		}
	    }

	}

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


    } # End of Staff 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{<h1>$lex{'No Field Selected'}</h1>\n};
	print qq{</body></html>\n};
	exit;
    }

    # For the Staff (non-multi) Updates ( Type = U )
    my $sth1 = $dbh->prepare("update staff set $resetfield = ? where userid = ?");

    foreach my $key ( keys %arr ) {

	my ( $type, $id, $userid ) = split(/:/, $key);

	if ( $type eq 'R' ) { # staff_multi record ID (or add)

	    if ( $id eq 'add' ) { # add to multi record.

		if ( not $userid ) { # need userid to add record
		    print qq{<h3>$lex{Error}: ADD - $lex{User} $lex{'Not Found'}</h3>\n};
		    next;
		}

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

	    } elsif ( not $arr{$key} ) { # no value in the field, delete record.
		my $sth = $dbh->prepare("delete from staff_multi where id = ?");
		$sth->execute( $id );
		if ($DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

	    } else { # just update the staff_multi record.

		# print qq{ID:$id VAL:$arr{$key}<br>\n};
		$sth = $dbh->prepare("update staff_multi set field_value = ? where id = ?");
		$sth->execute( $arr{$key}, $id );
		if ($DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    }

	} else { #  'U' (userid) record

	    $sth1->execute( $arr{$key}, $id );
	    if ($DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	}
    }


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


    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
