#!/usr/bin/perl # Copyright Les Richardson 2001-2020 # 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', 'No Field Selected' => 'No Field Selected', 'Field Fill' => 'Field Fill', 'Grade' => 'Grade', 'Homeroom' => 'Homeroom', 'Student Group' => 'Student Group', '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', ); 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 medical 1 utag 1 owing 1 studnum 1 entry 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); $dbh->{mysql_enable_utf8} = 1; # Show top of page. my $title = qq{$lex{Reset} $lex{Student} $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 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 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}; # Now a group selection by homeroom or grade, accept multiple values separated by spaces... print qq{\n}; # Add a 'Default' fill value for any fields that are blank... print qq{\n}; # Tables.... student, withdrawn print qq{\n}; # Additional Display Fields 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{Select from Existing List\n}; print qq{
$lex{'Student Group'} ($lex{'Separate with Spaces'}) }; print qq{\n}; print qq{ }; print qq{$lex{'Blank=All'}
$lex{Blank} $lex{'Field Fill'}\n}; print qq{
$lex{Students}\n}; print qq{ $lex{Current} }; print qq{ $lex{Withdrawn}}; print qq{
Additional Display $lex{Field}\n}; print qq{\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 # groupvalue: # grouptype: # displayfield: additional field to display along with the name my $mode = $arr{inputtype}; delete $arr{inputtype}; my $studtable = 'student'; my $tableview = "

$lex{Current} $lex{Students}

\n"; if ( $arr{studtable} eq 'withdrawn' ) { $studtable = 'studentwd'; $tableview = "

$lex{Withdrawn} $lex{Students}

\n"; } elsif ( $arr{studtable} eq 'both' ) { $studtable = 'studentall'; $tableview = "

$lex{Current}+$lex{Withdrawn} $lex{Students}

\n"; } # Display Field my $displayfield = $arr{displayfield}; delete $arr{displayfield}; # Load Meta Library eval require "../../lib/libmeta.pl"; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@\n"; } # Setup field name and fieldid. my $fieldid = $arr{field}; if ( not $fieldid ) { print qq{

$lex{'No Field Selected'}

\n}; print qq{\n}; exit; } #print qq{Name: $fieldname ID: $fieldid
\n}; # Set Default Fill Value (if any) if ( $arr{defaultfill} ) { $defaultfill = $arr{defaultfill}; } # Get Display Field's Meta Name my $sth = $dbh->prepare("select fieldname from meta where fieldid = ?"); $sth->execute( $displayfield ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $displayFieldName = $sth->fetchrow; # Now get this field's metadata my $sth = $dbh->prepare("select * from meta where fieldid = ?"); $sth->execute( $fieldid ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $ref = $sth->fetchrow_hashref; my %meta = %$ref; my $defaultvalue = $meta{defaultvalue}; my $fieldname = $meta{fieldname}; 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 $studtable 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 $studtable order by $sortorder"); $sth->execute; if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my $studnum = $sth->fetchrow ) { push @students, $studnum; } } my $sth = $dbh->prepare("select * from $studtable where studnum = ?"); # print which students we're viewing print $tableview, "\n"; # Start the Form... print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{}; if ( $displayfield ) { print qq{}; } print qq{}; print qq{\n}; my $sth = $dbh->prepare("select * from $studtable where studnum = ?"); # 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, $displayfield ) = $sth->fetchrow; my $ref = $sth->fetchrow_hashref; my %r = %$ref; # If no field in current record, and we have default fill set, then put it in... if ( ( not defined $r{$fieldid} or $r{$fieldid} eq '') and defined $defaultfill ) { $r{$fieldid} = $defaultfill; } print qq{}; if ( $displayfield ) { print qq{}; } print qq{\n}; # Make sure we can use default values, etc. if ( $mode eq 'select' and $meta{formtype} ne 'text' ) { $mode = 'text'; } if ( $mode eq 'text' ) { my $val = metaInputField('student', $fieldid, $r{$fieldid}, $dbh,$r{studnum}); print qq{\n}; # print qq{}; } elsif ( $mode eq 'select' ) { print qq{\n}; } else { # Fail very badly... print qq{

$lex{Type} $lex{Error}

\n}; print qq{\n}; exit; } print qq{\n}; } # End of Student Loop print qq{
$lex{Student}$displayFieldName$lex{Grade}/$lex{Homeroom}$fieldname
$r{lastname}, $r{firstname} ($r{studnum})$r{$displayfield}$r{grade} / $r{homeroom}$val
\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; } my $studtable = $arr{studtable}; delete $arr{studtable}; my $sth = $dbh->prepare("update $studtable set $resetfield = ? where studnum = ?"); foreach my $key ( keys %arr ) { if ( $key =~ m/:/ ) { # we have a colon separated value my ($id, $fld ) = split(':', $key); $sth->execute( $arr{$key}, $id ); } else { $sth->execute( $arr{$key}, $key ); } if ($DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } } if (not $DBI::errstr ) { print qq{

$lex{'Record(s) Updated'}

}; } else { print qq{

$lex{Error}: $DBI::errstr

\n}; print qq{

$lex{Contact} $adminname at $adminemail

\n}; } print qq{

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

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