#!/usr/bin/perl # Copyright Les Richardson 2001-2009 # This file is part of Open Administration for Schools. Released under GPL Licensing. my %lex = ('Student Field Reset' => 'Student Field Reset', 'Main' => 'Main', 'Eoy' => 'Eoy', 'Continue' => 'Continue', 'Select Field to Reset' => 'Select Field to Reset', 'Type of Field Selection' => 'Type of Field Selection', 'Type in Values' => 'Type in Values', 'Select from List' => 'Select from List', 'No Field Selected' => 'No Field Selected', 'Mistake in Input Type' => 'Mistake in Input Type', 'Default Fill - Blank Fields' => 'Default Fill - Blank Fields', 'Grade' => 'Grade', 'Homeroom' => 'Homeroom', 'Student Group' => 'Student Group', 'Separate with Spaces' => 'Separate with Spaces', 'No field to reset' => 'No field to reset', 'Blank=All' => 'Blank=All', 'Student' => 'Student', 'Your update is now stored' => 'Your update is now stored', 'There was an error storing your data' => 'There was an error storing your data', 'Contact' => 'Contact', 'Record the following error' => 'Record the following error', 'Error' => 'Error', 'Gr' => 'Gr', 'HRm' => 'HRm', ); 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); 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); $dbh->{mysql_enable_utf8} = 1; # Show top of page. print "$doctype\n". $lex{'Student Field Reset'}. " $chartype\n\n"; print "[ ". $lex{Main}. " | \n"; print "". $lex{Eoy}. " ]\n"; print "

". $lex{'Student Field Reset'}. "

\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 "
\n"; print "\n"; print "\n"; print "\n"; # Display the fields from which to select one. print "\n"; # Now type of selection: Type in Values, (Individual Text Input) or Select from a List print "\n"; # Now a group selection by homeroom or grade, accept multiple values separated by spaces... print "\n"; # Add a 'Default' fill value for any fields that are blank... print "\n"; print "
". $lex{'Select Field to Reset'}. "\n"; print "\n"; print "
". $lex{'Type of Field Selection'}. "\n"; print ""; print $lex{'Type in Values'}. "
\n"; print ""; print $lex{'Select from List'}. "\n"; print "
". $lex{'Student Group'}. ' ('. $lex{'Separate with Spaces'}. ' )'; print "\n"; print " "; print $lex{'Blank=All'}. "
". $lex{'Default Fill - Blank Fields'}. "\n"; print "
\n"; print "\n"; print "
\n"; exit; } # end of showStartPage #---------------- sub selectChanges { #---------------- #foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}
\n"; } # Passed: inputtype : 'text' or 'select' # field: fieldname (fieldid) # groupvalue: # grouptype: my $mode = $arr{inputtype}; delete $arr{inputtype}; # Setup field name and fieldid. my ( $fieldname, $fieldid ) = split /\(/, $arr{field}; chop $fieldid; # remove trailing ). if ( not $fieldid ) { print "

". $lex{'No Field Selected'}. "

\n"; print "\n"; exit; } #print "Name: $fieldname ID: $fieldid
\n"; # Set Default Fill Value (if any) if ( $arr{defaultfill} ) { $defaultfill = $arr{defaultfill}; } # 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; 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 student 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 student order by $sortorder"); $sth->execute; if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my $studnum = $sth->fetchrow ) { push @students, $studnum; } } $sth = $dbh->prepare("select lastname, firstname, grade, homeroom, $fieldid from student where studnum = ?"); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } # Start the Form... print "
\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n\n"; # 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 ) = $sth->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; } print "\n"; } # End of Student Loop print "
". $lex{Student}. "". $lex{Gr}. '/'. $lex{HRm}. ""; print "$arr{field}
$firstname $lastname ($studnum)$grade / $homeroom\n"; if ( $mode eq 'text' ) { print ""; } elsif ( $mode eq 'select' ) { print "\n"; } else { # Fail very badly... print "

". $lex{'Mistake in Input Type'}. "

\n"; exit; } print "
\n"; print "
\n"; exit; } # end of selectChanges #--------------- sub writeChanges { #--------------- #foreach my $key (keys %arr ) { print "K:$key V:$arr{$key}
\n"; } # first get field name to update if ( $arr{resetfield} ) { $resetfield = $arr{resetfield}; delete $arr{resetfield}; } else { print "

". $lex{'No field to reset'}. "

\n"; exit; } foreach my $key ( keys %arr ) { $sth = $dbh->prepare("update student set $resetfield = ? where studnum = ?"); $sth->execute( $arr{$key}, $key ); if ($DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } } if (not $DBI::errstr ) { print '

'. $lex{'Your update is now stored'}. "

"; } else { print "

". $lex{'There was an error storing your data'}. "\n"; print $lex{Contact}. " $adminname at $adminemail\n"; print $lex{'Record the following error'}. ": $DBI::errstr \n"; } print "[ ". $lex{Main}. " | ". $lex{Eoy}; print " ]

\n"; exit; } # end of writeChanges