#!/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}. ": $@ \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{Field}";
print qq{$doctype\n$title
$chartype\n\n};
print qq{[ $lex{Main} | \n};
print qq{$lex{Eoy} ]\n};
print qq{
$title
\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{\n};
exit;
} # end of showStartPage
#----------------
sub selectChanges {
#----------------
#foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key} \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{
$lex{Field} $lex{'Not Selected'}
\n};
print qq{
\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{