#!/usr/bin/perl # Copyright 2001-2024 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 %lex = ('View' => 'View', 'Enrollment' => 'Enrollment', 'Report Card' => 'Report Card', 'Grades' => 'Grades', 'Withdrawn' => 'Withdrawn', 'Error' => 'Error', 'Continue' => 'Continue', 'Terms' => 'Terms', 'Separate with Spaces' => 'Separate with Spaces', 'Blank=All' => 'Blank=All', 'Teacher' => 'Teacher', 'Sort by' => 'Sort by', 'Term' => 'Term', 'Grade' => 'Grade', 'Description' => 'Description', 'Enrol' => 'Enrol', 'Main' => 'Main', 'Code' => 'Code', 'Student' => 'Student', 'Count' => 'Count', 'Std Dev' => 'Std Dev', 'Mean' => 'Average', 'Success' => 'Success', 'Rate' => 'Rate', 'Report' => 'Report', 'Course' => 'Course', 'Not Found' => 'Not Found', ); my $self = 'rptcourse1.pl'; my $markfield = 'a1'; # marks normally stored in first field. use DBI; use CGI; use Statistics::Basic qw(:all); use Number::Format qw(:all); eval require "../../etc/admin.conf"; if ( $@ ) { print $lex{Error}. " $self: $@
\n"; die $lex{Error}. " $self: $@\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); # Print HTML Page Header my $title = "$lex{Course} $lex{Report} 1"; print qq{$doctype\n$title\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} | }; print qq{$lex{'Report Card'} ]\n}; print "

$title

\n"; if (not $arr{page} ) { showStartPage(); } else { delete $arr{page}; showCourses(); } #---------------- sub showStartPage { #---------------- # Find all of the term patterns. my $sth = $dbh->prepare("select distinct startrptperiod, endrptperiod from subject order by startrptperiod, endrptperiod"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } # Find Teachers my $sth1 = $dbh->prepare("select distinct s.teacher, st.lastname, st.firstname from subject s, staff st where s.teacher = st.userid order by st.lastname, st.firstname"); $sth1->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } print qq{

\n}; print qq{\n}; print qq{\n}; # Grade Select print qq{\n}; print qq{\n}; # Term Select print qq{\n}; # Teacher Select print qq{\n}; # Sorting Order my %sorttext = ('term' => "$lex{Term}", grade => "$lex{Grade}", teacher => "$lex{Teacher}", description => "$lex{Description}"); my @sort = qw (term grade teacher description); my $default1 = 'term'; my $default2 = 'grade'; my $default3 = 'description'; print qq{\n}; print qq{\n}; print qq{

$lex{Grades}	}; print qq{ }; print qq{$lex{'Separate with Spaces'}, $lex{'Blank=All'}
$lex{Terms}	}; while ( my ( $startterm, $endterm ) = $sth->fetchrow ) { print qq{\n}; print qq{$startterm-$endterm \n}; } print qq{ $lex{'Blank=All'}
$lex{Teacher}	}; print qq{
$lex{'Sort by'}	}; # Sort 1 print qq{ \n}; # Sort 2 print qq{ \n}; # Sort 3 print qq{ \n}; print qq{
\n}; print qq{

\n}; exit; } #-------------- sub showCourses { #-------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n"; } # Grade Select my $gradeSelect; if ( $arr{grade} ) { my @grades = split(/\s+/, $arr{grade}); my $first = 1; my $gs; foreach my $gr ( @grades ) { $gr = $dbh->quote( $gr ); if ( not $first ) { $gs .= ' or '; } else { $first = 0; } $gs .= "grade = $gr "; } $gradeSelect = $gs; } # delete $arr{grade}; # Teacher Select my ($lastname, $firstname); my $teacherSelect; if ( $arr{teacher} ) { my $sth = $dbh->prepare("select lastname, firstname from staff where userid = ?"); $sth->execute( $arr{teacher} ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;} ($lastname, $firstname) = $sth->fetchrow; my $tch = $dbh->quote( $arr{teacher} ); $teacherSelect = "teacher = $tch"; } # delete $arr{teacher}; # Term Select my ($startterm, $endterm); my $termSelect; if ( not $arr{terms} ) { $startterm = 1; my $sth = $dbh->prepare("select max(endrptperiod) from subject"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;} $endterm = $sth->fetchrow; } else { ( $startterm, $endterm ) = split('-', $arr{terms}); $start = $dbh->quote( $startterm ); $end = $dbh->quote( $endterm ); $termSelect = "startrptperiod = $start and endrptperiod = $end"; } my $globalSelect; if ( $teacherSelect ) { $globalSelect = $teacherSelect; } if ( $gradeSelect ) { if ( $globalSelect ) { $globalSelect .= ' and '; } $globalSelect .= "( $gradeSelect )"; } if ( $termSelect ) { if ( $globalSelect ) { $globalSelect .= ' and '; } $globalSelect .= "( $termSelect )"; } print qq{

$lex{Terms} $startterm - $endterm }; if ( $arr{grade} ) { print qq{$lex{Grades} $arr{grade}\n}; } if ( $arr{teacher} ) { print qq{$lex{Teacher} $firstname $lastname ($arr{teacher})\n}; } print qq{

\n}; # replace term text with rptperiod. for my $i (1..3) { my $key = 'sort'. $i; if ( $arr{$key} eq 'term' ) { $arr{$key} = 'startrptperiod, endrptperiod'; } } # print qq{Global Sel:$globalSelect
\n}; # print qq{Grade Sel: $gradeSelect
\n}; # print qq{Term Sel: $termSelect
\n}; if ( $globalSelect ) { $globalSelect = "where $globalSelect"; } # prepend where my $sth = $dbh->prepare("select * from subject $globalSelect order by $arr{sort1}, $arr{sort2}, $arr{sort3}"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;} my $sth1 = $dbh->prepare("select count(distinct studnum) from eval where subjcode = ?"); my $sth2 = $dbh->prepare("select lastname, firstname from staff where userid = ?"); my $sth3 = $dbh->prepare("select $markfield, studnum from eval where subjcode = ? and term = ?"); my $first = 1; while ( my $ref = $sth->fetchrow_hashref ) { if ( $first ) { # start the table print qq{\n}; print qq{}; for my $t ( $startterm..$endterm ) { print qq{}; } print qq{\n}; $first = 0; } my %r = %$ref; my $subjsec = $r{subjsec}; my (%marks, %avg, %sdev ); my $success; foreach my $term ( $startterm..$endterm ) { # Get any marks $sth3->execute( $subjsec, $term ); # end term has final marks if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;} while ( my ($mark,$studnum) = $sth3->fetchrow ) { $mark =~ s/\D+|%//g; # strip out percents or text. if ( not $mark ) { next; } if ( $mark =~ m/\d/) { if ( $term == $endterm and $mark >= 50 ) { $success++; } $marks{$subjsec}{$term}{$studnum} = $mark; } } my @marks = values %{ $marks{$subjsec}{$term} }; if ( @marks ) { $avg{$term} = round( mean( @marks ), 1); $sdev{$term} = round ( stddev( @marks ), 1); } } # Enrollment Count $sth1->execute( $r{subjsec} ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;} my $ecount = $sth1->fetchrow; # if (not $ecount ) { next; } # no students; skip print qq{}; foreach my $term ( $startterm..$endterm ) { print qq{}; } my $rate; if ( $success ) { $rate = round ($success / $ecount * 100, 1). '%'; } print qq{\n}; } # end of course loop if ( $first ) { print qq{

$lex{Course} $lex{'Not Found'}

\n}; } else { print qq{

$lex{Description}	$lex{Code}	$lex{Student} $lex{Count}	$lex{Term} $t $lex{Mean} (%)	$lex{Term} $t $lex{'Std Dev'} (%)	$lex{Success}#	$lex{Success} $lex{Rate}
$r{description}	$r{subjsec}	$ecount	$avg{$term}	$sdev{$term}	$success	$rate

\n}; } print qq{\n}; } # end of showEnrollment.