#!/usr/bin/perl # Copyright 2001-2018 Leslie Richardson # This file is part of Open Admin for Schools. # Open Admin for Schools is free software; you can redistribute it # and/or modify it under the terms of the GNU General Public License # as published by the Free Software Foundation; either version 2 of # the License, or (at your option) any later version. # Design: We have an array called '@sortorder' with the studnum's in # the correct order for display. # If there is no test named 'sortorder' then we will put students # initially in lastname, firstname order. The order values will start # at 5 and increment by 5. # If there IS a sortorder test, then it will be used to populate the # sortorder array in order. my %lex = ('Change' => 'Change', 'Name' => 'Name', 'GB Main' => 'GB Main', 'Main' => 'Main', 'Please Log In' => 'Please Log In', 'Error' => 'Error', 'Student' => 'Student', 'Sort Order' => 'Sort Order', 'Save' => 'Save', 'Record(s) Updated' => 'Record(s) Updated', 'Drag' => 'Drag', ); use DBI; use CGI; use CGI::Session; my $self = 'sortorder.pl'; eval require "../../etc/admin.conf"; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@
\n"; } my $q = new CGI; my %arr = $q->Vars; my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); # Get Session Information... my $session = new CGI::Session("driver:mysql;serializer:FreezeThaw", undef,{Handle => $dbh}) or die CGI::Session->errstr; my $logged_in = $session->param(logged_in); if ( not $logged_in ){ print $q->header( -charset, $charset ); print $lex{'Please Log In'}. "
\n"; print qq{\n}; exit; } my $subjsec = $session->param('subjsec'); print $session->header( -charset, $charset ); # Now setup HTML header my $title = "$lex{Change} $lex{Student} $lex{'Sort Order'}"; print qq{$doctype\n$title\n}; print qq{\n}; print qq{\n}; if ( not $arr{page} ) { # load jQuery libs print qq{\n}; print qq{\n}; } print qq{$chartype\n}; print qq{\n}; # Get Subject Description my $sth = $dbh->prepare("select description from subject where subjsec = ?"); $sth->execute($subjsec); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $desc = $sth->fetchrow; print qq{

[ $lex{Main} | \n}; print qq{$lex{'GB Main'} ]

\n}; print qq{

$desc - $title

\n}; # Update / Add sortorder test and scores if ( $arr{page} ){ # write values into gbscore, gbtest delete $arr{page}; updateRecords(); } # get 'sortorder' test id, if any. $sth = $dbh->prepare("select id from gbtest where subjsec = ? and name = 'sortorder'"); $sth->execute( $subjsec ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $id = $sth->fetchrow; # id is used to find all of the sortorder recs. #(no $id = no test) my $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?"); if ( $id ){ # We HAVE a sortorder test id # Get the sortorder score records (giving the order) $sth = $dbh->prepare("select studnum, score from gbscore where testid = ?"); $sth->execute($id); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my %presort; while ( my ($studnum, $score) = $sth->fetchrow ) { # Get Name of student $sth1->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($lastname, $firstname ) = $sth1->fetchrow; $studname{$studnum} = "$lastname, $firstname"; $presort{$score} = $studnum; } foreach my $key ( sort {$a <=> $b} keys %presort ) { push @sortorder, $presort{$key}; } } else { # no sortorder test found, order by lastname, firstname # Find the enrollments for this class and read into %studname hash # ordered by lastname, firstname. $sth = $dbh->prepare("select distinct e.studnum, s.lastname, s.firstname from eval e left outer join studentall s on s.studnum = e.studnum where e.subjcode = ? order by s.lastname, s.firstname"); $sth->execute( $subjsec ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my ( $studnum, $lastname,$firstname) = $sth->fetchrow){ push @sortorder, $studnum; $studname{$studnum} = "$lastname, $firstname"; } } print qq{

$lex{Drag} $lex{Name} Up/Down to $lex{Change} $lex{'Sort Order'}

\n}; print qq{\n}; print q{ }; print qq{\n}; #---------------- sub updateRecords { #---------------- # foreach my $key (keys %arr) { print qq{K:$key V:$arr{$key}
\n}; } # single sortorder value, comma separated with leading 's_' before studnum; my @students = split(",", $arr{sortorder} ); foreach my $stud ( @students ) { $stud =~ s/s\_//; } # print qq{Students:", @students, "
\n}; my $id = $arr{id}; delete $arr{id}; my $subjsec = $arr{subjsec}; delete $arr{subjsec}; # These records are passed as studentnumber:number. If the number is # one then a record already exists and it is updated. If the number is # a 0 then no record exists and a new one is added to the gbscore # table. if ( not $id ){ # We don't have a test 'sortorder' for this subject. # Insert Test my $testdesc = qq{$lex{Student} $lex{'Sort Order'}}; my $sth = $dbh->prepare("insert into gbtest (subjsec, name, description ) values( ?,?,? ) "); $sth->execute( $subjsec, 'sortorder', $testdesc ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } # Now get the id for this test. $sth = $dbh->prepare("select id from gbtest where name = 'sortorder' and subjsec = ? "); $sth->execute( $subjsec ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;} $id = $sth->fetchrow; } # We have an $id now my $sth1 = $dbh->prepare("select id from gbscore where testid = ? and studnum = ?"); my $sth2 = $dbh->prepare("update gbscore set score = ? where id = ?"); my $sth3 = $dbh->prepare("insert into gbscore (studnum, testid, score ) values ( ?, ?, ? )"); my $counter = 100; foreach my $studnum ( @students ) { # Check for existing score record; $sth1->execute($id, $studnum); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $scoreid = $sth1->fetchrow; if ( $scoreid ) { # update the score with the counter value. $sth2->execute( $counter, $scoreid ); } else { # add a new record. $sth3->execute( $studnum, $id, $counter ); } if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } $counter += 5; } print qq{

$lex{'Record(s) Updated'}

\n}; print qq{\n}; exit; } # End of updateRecords