#!/usr/bin/perl # Copyright 2001-2022 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. my $self = "gbSearch.pl"; my %lex = ('Gradebook Search' => 'Gradebook Search', 'Continue' => 'Continue', 'Main' => 'Main', 'GB Main' => 'GB Main', 'Search For' => 'Search For', 'Blank=All' => 'Blank=All', 'Scores' => 'Scores', 'Averages' => 'Averages', 'Search In' => 'Search In', 'All' => 'All', 'Terms' => 'Terms', 'Sort By' => 'Sort By', 'Student' => 'Student', 'Please Log In' => 'Please Log In', 'No Search' => 'No Search', 'Error' => 'Error', 'Course' => 'Course', 'Item' => 'Item', 'Score' => 'Score', 'Value' => 'Value', 'Average' => 'Average', 'No Records Found' => 'No Records Found', 'Search Again' => 'Search Again', 'and' => 'and', 'Courses' => 'Courses', ); # Characters NOT to search for in score field entries. my $searchclass = '^0-9,.'; use DBI; use CGI; use CGI::Session; use Number::Format qw{ round }; eval require "../../etc/admin.conf"; if ( $@ ) { print qq{$lex{Error}: $@
\n}; print qq{\n}; die qq{$lex{Error}: $@\n}; } eval require "../../etc/gbook.conf"; if ( $@ ) { print qq{$lex{Error}: $@
\n}; print qq{\n}; die qq{$lex{Error}: $@\n}; } my $q = CGI->new; my $dsn = "DBI:$dbtype:dbname=$dbase"; $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); # checked below page header.. my $userid = $session->param('userid'); my $subjsec = $session->param('subjsec'); print $q->header( -charset, $charset ); my %arr = $q->Vars; my ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $iddst) = localtime(time); $year = $year + 1900; $mon++; $wday++; # Now using global values for month and dow. my $currdate = "$dow[$wday], $month[$mon] $mday, $year"; # print page header my $title = $lex{'Gradebook Search'}; print qq{$doctype\n$title\n}; print qq{}; print qq{\n}; print qq{[ $lex{Main} |\n}; print qq{$lex{'GB Main'} \n}; if ( %arr ) { print qq{| $lex{'Search Again'} \n}; } print qq{ ]\n}; if ( not $logged_in ) { print qq{

$lex{'Please Log In'}

\n}; print qq{\n}; exit; } # Select courses to print. if ( not $arr{searchType} ) { showStartPage(); } # get input values and leave subjsec's in @arr. my ($averageGtSearch, $averageLtSearch, $scoreGtSearch, $scoreLtSearch, $searchType, $sortby, $textSearch); if ( defined $arr{averageGtSearch} ) { $averageGtSearch = $arr{averageGtSearch}; delete $arr{averageGtSearch}; } if ( defined $arr{averageLtSearch} ) { $averageLtSearch = $arr{averageLtSearch}; delete $arr{averageLtSearch}; } if ( defined $arr{scoreGtSearch} ) { $scoreGtSearch = $arr{scoreGtSearch}; delete $arr{scoreGtSearch}; } if ( defined $arr{scoreLtSearch} ) { $scoreLtSearch = $arr{scoreLtSearch}; delete $arr{scoreLtSearch}; } if ( defined $arr{searchType} ) { $searchType = $arr{searchType}; delete $arr{searchType}; } if ( defined $arr{sortby} ) { $sortby = $arr{sortby}; delete $arr{sortby}; } if ( defined $arr{textSearch} ) { $textSearch = $arr{textSearch}; delete $arr{textSearch}; } # Define termsearch for term selection. my $termsearch; # if ( $arr{terms} ne $lex{All} ) { # we have a term selection. # my $startdate = $TRMstart{ $arr{terms} }; # my $enddate = TRMend{ $arr{terms} }; # delete $arr{terms}; # $termsearch = "and to_days(tdate) >= to_days('$startdate') ". # "and to_days(tdate) <= to_days('$enddate')"; #} #foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n"; } if ( $searchType eq 'text' ) { if ( not %arr ) { print qq{

No Courses Selected

\n}; print qq{\n}; exit; } searchText( $textSearch, $sortby, \%arr, $termsearch ); # termsearch is now undefined. no search for this. } elsif ( $searchType eq 'scores' ) { if ( not %arr ) { print qq{

No Courses Selected

\n}; print qq{\n}; exit; } searchScores( $scoreLtSearch, $scoreGtSearch, $sortby, \%arr, $termsearch ); } elsif ( $searchType eq 'averages' ) { if ( not %arr ) { print qq{

No Courses Selected

\n}; print qq{\n}; exit; } searchAverages( $averageLtSearch, $averageGtSearch, $sortby, \%arr ); } else { print qq{

$lex{'No Search'}

\n}; print qq{\n}; } #--------------- sub searchText { #--------------- # print qq{SUB:searchText
\n}; # foreach my $key ( sort keys %arr ) { print qq{K:$key Val:$arr{$key}
\n} }; # All courses passed as key values. # Passed %arr ref; also need selects and sortorder; my ( $textSearch, $sortorder, $subj_ref, $tsearch ) = @_; # print qq{TS:$textSearch SORT:$sortorder CRS:$subj_ref SRCH:$tsearch
\n}; my $sth = $dbh->prepare("select description from subject where subjsec = ?"); my $sth1 = $dbh->prepare("select id, description,tdate from gbtest where subjsec = ? $tsearch order by tdate"); my $sth2 = $dbh->prepare("select id, score, studnum from gbscore where testid = ? "); my $sth3 = $dbh->prepare("select lastname, firstname from studentall where studnum = ? "); print qq{

\n}; print qq{\n}; print qq{\n}; my $first = 1; # flag to see if any results... foreach my $subjsec (keys %{$subj_ref} ) { # Get course name... $sth->execute( $subjsec ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my $description = $sth->fetchrow; # Now loop through assessments... $sth1->execute( $subjsec ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( my ($testid, $testdesc, $tdate ) = $sth1->fetchrow ) { # Now loop through scores $sth2->execute( $testid ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( my ( $id, $score, $studnum ) = $sth2->fetchrow ) { if ( ( $textSearch and $score eq $textSearch ) or ( not $textSearch and $score =~ /[$searchclass]/ )) { # Get Student Name $sth3->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my ($lastname, $firstname ) = $sth3->fetchrow; $first = 0; print qq{\n}; print qq{\n}; } } } } if ( $first ) { print qq{\n}; } print qq{

$lex{Course}	$lex{Student}	$lex{Item}	$lex{Score}
$description	$firstname $lastname ($studnum)	$testdesc ($tdate)	$score
}; print qq{$lex{'No Records Found'}

\n}; exit; } #----------------- sub searchAverages { #----------------- eval require "./libgbreport.pl"; if ( $@ ) { print qq{
$lex{Error}: $@
\n}; print qq{\n}; die $lex{Error}. ": $@\n"; } # Passed %arr ref; also need selects and sortorder; my ( $maxAvg, $minAvg, $sortorder, $subj_ref ) = @_; if ( not $minAvg ) { $minAvg = 0; } if ( not $maxAvg ) { $maxAvg = 200; } # 200% should be safe... my $sth = $dbh->prepare("select description from subject where subjsec = ?"); my $sth1 = $dbh->prepare("select distinct e.studnum from eval as e, studentall as s where e.subjcode = ? and e.studnum = s.studnum order by s.lastname, s.firstname"); my $sth3 = $dbh->prepare("select lastname, firstname from studentall where studnum = ? "); print qq{

\n}; print qq{\n}; my $first = 1; # flag to see if any results... foreach my $subjsec (keys %{$subj_ref} ) { # Get course name... $sth->execute( $subjsec ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my $description = $sth->fetchrow; # Get Students $sth1->execute( $subjsec ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( my $studnum = $sth1->fetchrow ) { my $avg = calcStudentAverage( $studnum, $subjsec, 2 ); # print "SN:$studnum CRS:$subjsec AVG:$avg
\n"; if ( ( $avg >= $minAvg ) and ( $avg <= $maxAvg ) ) { # Get Student Name $sth3->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my ($lastname, $firstname ) = $sth3->fetchrow; $first = 0; print qq{\n}; print qq{\n}; } } } if ( $first ) { print qq{\n}; } print qq{

$lex{Course}	$lex{Student}	$lex{Average}
$description	$firstname $lastname ($studnum)	}. round( $avg, 2 ); print qq{%
}; print qq{$lex{'No Records Found'}

\n}; exit; } #--------------- sub searchScores { #--------------- # Passed %arr ref; also need selects and sortorder; my ( $maxScore, $minScore, $sortorder, $subj_ref, $tsearch ) = @_; if ( not $minScore ) { $minScore = 0; } if ( not $maxScore ) { $maxScore = 200; } # 200% should be safe... my $sth = $dbh->prepare("select description from subject where subjsec = ?"); my $sth1 = $dbh->prepare("select * from gbtest where subjsec = ? $tsearch order by tdate"); my $sth2 = $dbh->prepare("select id, score, studnum from gbscore where testid = ? "); my $sth3 = $dbh->prepare("select lastname, firstname from studentall where studnum = ? "); print qq{

\n}; print qq{\n}; print qq{\n}; my $first = 1; # flag to see if any results... foreach my $subjsec (keys %{$subj_ref} ) { # Get course name... $sth->execute( $subjsec ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my $description = $sth->fetchrow; # Now loop through assessments... $sth1->execute( $subjsec ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( my $ref = $sth1->fetchrow_hashref ) { # get tests my %t = %$ref; # while ( my ($testid, $testdesc, $maxscore, $tdate ) = $sth1->fetchrow ) { if ( not defined $t{score} ) { next; } #skip missing score tests (shouldn't be any). # score here in gbtest is really maxscore # Now loop through scores $sth2->execute( $t{id} ); # testid if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( my ( $id, $score, $studnum ) = $sth2->fetchrow ) { if ( $score =~ /[$searchclass]/ ) { next; } # skip if text... my $avg = $score / $t{score} * 100; # t{score} is maxscore if ( ( $avg >= $minScore ) and ( $avg <= $maxScore ) ) { # if it fits # Get Student Name $sth3->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my ($lastname, $firstname ) = $sth3->fetchrow; $first = 0; print qq{\n}; print qq{}; print qq{\n}; } } } } if ( $first ) { print qq{\n}; } print qq{

$lex{Course}	$lex{Student}	$lex{Item}	$lex{Score}
$description	$firstname $lastname ($studnum)	$t{name}/$t{description} ($t{tdate})	}. round( $avg, 2); print qq{% ($score/$t{score} )
}; print qq{$lex{'No Records Found'}

\n}; exit; } #---------------- sub showStartPage { # Get search values... #---------------- # Note: assume that userid and subjsec is already loaded from Session. my $checkall; if ( $arr{checkall} ) { $checkall = qq{checked="checked"}; delete $arr{checkall}; } my %terms; # Now find the subjects and store the subjsec, desc for them $sth = $dbh->prepare("select subjsec, description, startrptperiod, endrptperiod from subject where teacher = ? order by description"); $sth->execute( $userid ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while (my ( $subjsec,$desc, $sterm, $eterm ) = $sth->fetchrow){ for my $t ( $sterm..$eterm ) { $terms{$t} = 1; } # set term values. $desc =~ s/://g; push @subjsec, "$subjsec:$desc"; } print qq{

$lex{'Gradebook Search'}

\n}; # Check All courses. print qq{\n}; print qq{\n}; exit; } # End of showStartPage

\n}; print qq{
$lex{'Search For'}	}; print qq{$lex{Score} $lex{Value}\n}; print qq{ $lex{'Blank=All'}
	$lex{Scores}}; print qq{ >=\n}; print qq{ % \n}; print qq{$lex{and} <= %\n}; print qq{
	}; print qq{$lex{Averages}\n}; print qq{ >= %\n}; print qq{$lex{and} <= % \n}; print qq{
$lex{'Search In'} \n}; print qq{$lex{Courses}
$desc ($subjsec)
\n}; print qq{}; print qq{