#! /usr/bin/perl # Copyright 2001-2011 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. use DBI; use CGI; use Number::Format qw(:all); my %lex = ('Ranking Report' => 'Ranking Report', 'Main' => 'Main', 'Report Card' => 'Report Card', 'Error' => 'Error', 'Select' => 'Select', 'Term' => 'Term', 'Grade' => 'Grade', 'Homeroom' => 'Homeroom', 'Continue' => 'Continue', 'School Year' => 'School Year', 'Blank=All' => 'Blank=All', 'No Quality Score for' => 'No Quality Score for', ); my $self = "rptrank.pl"; eval require "../../etc/admin.conf"; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@\n"; } my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); my $q = new CGI; print $q->header( -charset, $charset ); my %arr = $q->Vars; # print page header my $title = "$lex{'Ranking Report'}"; print "$doctype\n$title\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "$chartype\n\n"; print "[ $lex{Main} | \n"; print "$lex{'Report Card'} ]\n"; print "

$title

\n"; #foreach my $key (sort keys %arr ) { print "K:$key V:$arr{$key}
\n"; } if ( not $arr{page} ) { showStartPage(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; showRanking(); } #----------------- sub showStartPage { #----------------- my $sth = $dbh->prepare("select distinct schoolyear, term from tscriptdata order by schoolyear"); $sth->execute(); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } print "
\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "
". $lex{Select}. "\n"; print "\n"; print "\n"; print " \n"; print $lex{'Blank=All'}. "
$lex{'School Year'}
"; print "
\n"; print "
\n"; exit; } #-------------- sub showRanking { #-------------- #foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}
\n"; } eval require "../../etc/transcript.conf"; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@\n"; } my $select; if ( $arr{grouptype} eq $lex{Grade} ) { $select = 'where grade = ?'; } else { $select = 'where homeroom = ?'; } # Find the Students in that grade or homeroom. my $sth = $dbh->prepare("select lastname, firstname, studnum from student $select"); $sth->execute( $arr{groupval} ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my %student = (); my %gpa = (); my %gpa_order = (); print "\n"; while ( my ($lastname, $firstname, $studnum ) = $sth->fetchrow ) { # print "Name: $lastname $firstname ($studnum)
\n"; $student{$studnum} = "$lastname, $firstname"; my $gpa = calcTotalGPA( $studnum ); $gpa_order{"$gpa-$studnum"} = $studnum; $gpa{$studnum} = $gpa; } foreach my $key ( reverse sort keys %gpa_order ) { my $studnum = $gpa_order{$key}; print "\n"; } exit; }; #--------------- sub calcTotalGPA { #--------------- my ( $studnum ) = @_; my $sth = $dbh->prepare("select id, subjectcode, subjecttext, subjectarea, score_mark, score_letter, score_diff, schoolyear, credit, term, term_desc from tscriptdata where studnum = ?"); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } $sth->execute( $studnum ); my ( $totalGPCredits, $totalGPQuality ); # Loop through all transcript records. while ( my ( $id, $subjectcode, $subjecttext, $subjectarea, $score_mark, $score_letter, $score_diff, $schoolyear, $credit, $term, $term_desc ) = $sth->fetchrow ) { # letterToQual hash in transcript.conf; add up quality scores and credit counts. # Check for missing Quality value in transcript.conf if ( ( not defined $letterToQual{ $score_letter } ) and ($score_letter ne $passletter ) ) { print $lex{'No Quality Score for'}. " $score_letter
\n"; next; } # Now do Pass Letter settings. if ( $score_letter eq $passletter ) { # Nothing to do, since P/F subject Passes don't affect GPA. next; } elsif ( $score_letter eq $failletter ) { # No additions to credits, GP credits increase to bring down avg. $totalGPCredits += $credit; } else { # calculate passing grade settings. my $quality = $letterToQual{ $score_letter }; if ( $quality ) { # if we have a nonzero value, add in difficulty score. $quality += $score_diff; # difficulty factor } # Update Quality scores for weighted average. $totalGPQuality += $quality * $credit; # Update GP Credit totals for weighted average. $totalGPCredits += $credit; } } my $retval; if ( $totalGPCredits ) { $retval = $totalGPQuality / $totalGPCredits; } else { $retval = 0; } return format_number( $retval, 3, 3); } # end of calcTotalGPA # NOT USED ---- #-------------- sub calcTermGPA { #-------------- my ( $studnum, $yearterm ) = @_; my ( $year, $term ) = split( /\(/, $yearterm); chop $term; # remove trailing bracket. my $sth = $dbh->prepare("select id, subjectcode, subjecttext, subjectarea, score_mark, score_letter, score_diff, schoolyear, credit, term, term_desc from tscriptdata where studnum = ? and schoolyear = ? and term = ?"); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } # process transcript data $sth->execute( $studnum, $year, $term ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $termGPQuality = 0; my $termGPCredits = 0; # Loop through all transcript records. while ( my ( $id, $subjectcode, $subjecttext, $subjectarea, $score_mark, $score_letter, $score_diff, $schoolyear, $credit, $term, $term_desc ) = $sth->fetchrow ) { # letterToQual hash in transcript.conf; add up quality scores and credit counts. # Check for missing Quality value in transcript.conf if ( ( not defined $letterToQual{ $score_letter } ) and ($score_letter ne $passletter ) ) { print $lex{'No Quality Score for'}. " $score_letter
\n"; next; } # Now do Pass Letter settings. if ( $score_letter eq $passletter ) { # Nothing to do, since P/F subject Passes don't affect GPA. next; } elsif ( $score_letter eq $failletter ) { # No additions to credits, GP credits increase to bring down avg. $termGPCredits += $credit; } else { # calculate passing grade settings. my $quality = $letterToQual{ $score_letter }; if ( $quality ) { # if we have a nonzero value, add in difficulty score. $quality += $score_diff; # difficulty factor } # Update Quality scores for weighted average. $termGPQuality += $quality * $credit; # Update GP Credit totals for weighted average. $termGPCredits += $credit; } } # end of while Loop my $retval; if ( $termGPCredits ) { $retval = $termGPQuality / $termGPCredits; } else { $retval = 0; } return $retval; } # end of calcTermGPA
$student{$studnum}$gpa{$studnum}