#!/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}. ": $@
\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$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, 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{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; # Display the fields from which to select one. print qq{\n}; # Now type of selection: Type in Values, (Individual Text Input) or Select from a List print qq{\n}; # Add a 'Default' fill value for any fields that are blank... print qq{\n}; print qq{
$lex{Select} $lex{Field}\n}; print qq{\n}; print qq{
$lex{Type}\n}; print qq{$lex{'Enter Values'}
\n}; print qq{}; print qq{$lex{'Select from List'}\n}; print qq{
$lex{Blank} $lex{'Field Fill'}\n}; print qq{
\n}; print qq{\n}; print qq{
\n}; exit; } # end of showStartPage #---------------- sub selectChanges { #---------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\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}. ": $@
\n"; die $lex{Error}. ": $@\n"; } my $inputmode = $arr{inputtype}; delete $arr{inputtype}; my ( $usertable, $tableview, $metafield ); $usertable = 'staff'; $tableview = "

$lex{Staff}

\n"; $metafield = 'staff'; # Setup field name and fieldid. my $fieldid = $arr{field}; if ( not $fieldid ) { print qq{

$lex{'No Field Selected'}

\n}; print qq{\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}. " $@
\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{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n\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('
',@position); # print row start print qq{\n}; } # End of Staff Loop print qq{
$lex{Position}}; print qq{$arr{field}
$rec{firstname} $rec{lastname} ($userid)$position\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{}; } elsif ( $inputmode eq 'select' ) { my $inputtext = metaInputField('staff', $fieldid, $rec{$fieldid}, $dbh, "U:$userid" ); print qq{$inputtext\n}; =head print qq{\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{\n}; } else { # use text print qq{ }; } } if ( $first ) { if ( @defaults ) { # use select print qq{\n}; } else { # use text print qq{ }; } } } print qq{
\n}; print qq{
\n}; exit; } # end of selectChanges #--------------- sub writeChanges { #--------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # first get field name to update if ( $arr{resetfield} ) { $resetfield = $arr{resetfield}; delete $arr{resetfield}; } else { print qq{

$lex{'No Field Selected'}

\n}; print qq{\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{

$lex{Error}: ADD - $lex{User} $lex{'Not Found'}

\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}
\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{

$lex{'Record(s) Updated'}

}; } print qq{

[ $lex{Reset} $lex{Fields} |\n}; print qq{$lex{Main} |\n}; print qq{$lex{Eoy} ]

\n}; print qq{\n}; exit; } # end of writeChanges