#!/usr/bin/perl # Copyright 2001-2016 Leslie Richardson # This file is part of Open Administration for Schools use DBI; use CGI; # Pull completed courses from local copy of data. my $self = 'viewcompletedcourses.pl'; my %lex = ( 'Main' => 'Main', 'Error' => 'Error', 'Grade' => 'Grade', 'Homeroom' => 'Homeroom', 'Group' => 'Group', 'Separate with Spaces' => 'Separate with Spaces', 'Blank=All' => 'Blank=All', 'Continue' => 'Continue', 'Report Card' => 'Report Card', 'School' => 'School', 'View' => 'View', 'Completed Courses' => 'Completed Courses', 'Select' => 'Select', 'Student' => 'Student', 'Blank=All' => 'Blank=All', 'Report' => 'Report', 'View/Download' => 'View/Download', 'View Log File' => 'View Log File', ); my $grayscale = '0.92'; eval require "../../etc/admin.conf"; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@\n"; } my $q = CGI->new; my %arr = $q->Vars; print $q->header( -charset, $charset ); my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); my @tim = localtime(time); my $year = $tim[5] + 1900; $tim[4]++; for (0..4){if (length($tim[$_]) == 1){ $tim[$_] = '0'.$tim[$_];}} my $currdate = "$year-$tim[4]-$tim[3]"; my $currtime = "$tim[2]:$tim[1]:$tim[0]"; my $title = "$lex{View} $lex{'Completed Courses'}"; print qq{$doctype\n$title\n}; print qq{\n}; print qq{$chartype\n}; print qq{\n}; print qq{[ Report Card ]\n}; print qq{

$title

\n}; if ( not $arr{page} ) { showStartPage(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; selectStudent(); } elsif ( $arr{page} == 2 ) { delete $arr{page}; if ( $arr{htmlreport} ) { delete $arr{htmlreport}; getCourseHistory(); } else { # pdf report delete $arr{pdfreport}; courseReport(); } } #---------------- sub showStartPage { #---------------- # Setup the form and start of table. print qq{
\n}; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{
$lex{Select} $lex{Student} $lex{Group}, $lex{'Blank=All'}
\n}; print qq{
Check Next Page
}; print qq{
\n}; print qq{\n}; exit; } #---------------- sub selectStudent { #---------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } my $chk; if ( $arr{chk} ) { $chk = 'checked="checked"'; } delete $arr{chk}; my $select; my $groupname; if ( $arr{groupvalue} ) { # we're picking a group if ( $arr{grouptype} eq 'grade' ) { $select = 'where grade = ?'; $groupname = $lex{Grade}; } else { $select = 'where homeroom = ?'; $groupname = $lex{Homeroom}; } } my $sth = $dbh->prepare("select lastname, firstname, studnum, provnum from student $select order by lastname, firstname"); if ( $select ) { $sth->execute( $arr{groupvalue} ); } else { $sth->execute; } if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } print qq{

$groupname $arr{groupvalue}

\n}; # Form Header print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; while ( my ( $lastname, $firstname, $studnum, $provnum ) = $sth->fetchrow ) { print qq{\n}; } print qq{\n}; print qq{
}; print qq{
}; print qq{$lastname, $firstname ($studnum)
}; print qq{
\n}; exit; } # end of selectStudent #------------------- sub getCourseHistory { #------------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # Setup to get student data from table my $sth = $dbh->prepare("select * from sasked_completedcourses where provnum = ? order by schoolyear, courseenddate, coursetitle"); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $sth1 = $dbh->prepare("select lastname, firstname, grade, studnum from studentall where provnum = ?"); my $sth2 = $dbh->prepare("select distinct eval.subjcode from eval, subject where eval.subjcode = subject.subjsec and studnum = ? order by startrptperiod, description"); my $sth3 = $dbh->prepare("select description, endrptperiod from subject where subjsec = ?"); my $sth4 = $dbh->prepare("select a1 from eval where studnum = ? and subjcode = ? and term = ?"); my %sortedstudent; # sort the students by name; foreach my $provnum ( keys %arr ) { $sth1->execute( $provnum ); my ($lastname, $firstname ) = $sth1->fetchrow; $sortedstudent{"$lastname$firstname$provnum"} = $provnum; } foreach my $key ( sort keys %sortedstudent ) { my $provnum = $sortedstudent{$key}; my %courses; # Get Completed Courses $sth->execute( $provnum ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } # Get Name, Grade, Local Number $sth1->execute( $provnum ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ( $lastname, $firstname, $grade, $studnum ) = $sth1->fetchrow; my $first = 1; my $totalcredits; my $prevschoolyear = -1; my $currschoolyear; my $colora = '#CCC'; my $colorb = '#FFF'; print qq{

$firstname $lastname
Grade:$grade – $provnum

\n}; while ( my $cref = $sth->fetchrow_hashref ) { my %c = %$cref; if ( $first ) { # start the table. $first = 0; print qq{\n}; print qq{}; print qq{\n}; } $prevschoolyear = $currschoolyear; $currschoolyear = $c{courseenddate}; if ( $currschoolyear ne $prevschoolyear ) { # toggle color if ( $bgcolor eq $colora ) { $bgcolor = $colorb; } else { $bgcolor = $colora; } } $courses{"$c{courseid}"} = $c{creditsearned}; print qq{}; print qq{}; $totalcredits += $c{creditsearned}; if ( $c{creditsearned} == 0 ) { $c{creditsearned} = qq{$c{creditsearned}}; } print qq{\n}; } if ( not $first ) { print qq{\n}; print qq{
Course TitleCodeSchool YearEnd DateMarkCredits
$c{coursetitle}$c{courseid}$c{schoolyear}$c{courseenddate}$c{finalmark}$c{creditsearned}
Credits Earned}; print qq{$totalcredits

\n}; } else { print qq{

No Courses Found

\n}; } # Now Do the Local Courses, print qq{

Current Courses

\n}; my $first = 1; my $localcredits; # Get Course Codes in Eval (Course Marks) $sth2->execute( $studnum ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my $subjsec = $sth2->fetchrow ) { # got the subjsecs my ( $code, $section ) = split(/-/, $subjsec); if ( $courses{$code} == 1 ) { print qq{
Skipping $subjsec. Already Recorded.
\n}; next; } # Get the Course Info $sth3->execute( $subjsec ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ($desc, $endterm) = $sth3->fetchrow; my $track = $g_MTrackTermType{$grade}; my $enddate = $g_MTrackTerm{$track}{$endterm}{'end'}; # Get the Mark. $sth4->execute( $studnum, $subjsec, $endterm ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $mark = $sth4->fetchrow; if ( $first ) { # start the table. $first = 0; print qq{\n}; print qq{}; print qq{\n}; } print qq{}; print qq{}; my $creditsearned; if ( $mark > 49 ) { $creditsearned = 1 } else { $creditsearned = 0; } $localcredits += $creditsearned; if ( $creditsearned == 0 ) { $creditsearned = qq{$creditsearned}; } print qq{\n}; } # end of local subjects loop my $grandtotal = $localcredits + $totalcredits; if ( not $first ) { print qq{\n}; print qq{\n}; print qq{
Course TitleCodeSchool YearEnd DateMarkCredits
$desc$subjsecCurrent$enddate$mark$creditsearned
Credits Earned}; print qq{$localcredits
Grand TotalCredits Earned}; print qq{$grandtotal

\n}; } else { print qq{

No Courses Found

\n}; } } # end of student loop. print qq{\n}; } # end of getCourseHistory #--------------- sub courseReport { #--------------- my @tim = localtime(time); my $year = $tim[5] + 1900; $tim[4]++; for (0..4){if (length($tim[$_]) == 1){ $tim[$_] = '0'.$tim[$_];}} my $currdate = "$year-$tim[4]-$tim[3]"; # my $currtime = "$tim[2]:$tim[1]:$tim[0]"; # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # Setup to get student data from table my $sth = $dbh->prepare("select * from sasked_completedcourses where provnum = ? order by schoolyear, courseenddate, coursetitle"); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $sth1 = $dbh->prepare("select lastname, firstname, grade, birthdate from studentall where provnum = ?"); # Sask Ed Course Names my $sth2 = $dbh->prepare("select title from sasked_courses where code = ?"); # Setup for PDF / TEX output my $shortname = "completedcourses$$"; my $filename = "$shortname.tex"; open(TEX,">$filename") || die "Can't open tex file"; my $papersize; if ( $defaultpapersize ) { $papersize = $defaultpapersize; } else { $papersize = 'letterpaper'; } print TEX "\\documentclass[12pt,$papersize]{article} \\usepackage{array,inputenc,colortbl,fancyhdr} $a_latex_header \\renewcommand{\\familydefault}{\\sfdefault} \\setlength{\\headsep}{10pt} \\setlength{\\headheight}{14pt} \\setlength{\\topmargin}{0mm} \\setlength{\\textwidth}{7.50in} \\setlength{\\textheight}{9.5in} \\setlength{\\hoffset}{-13mm} \\setlength{\\voffset}{-20mm} \\setlength{\\parindent}{0in} \\setlength{\\evensidemargin}{0in} \\setlength{\\oddsidemargin}{0in} \\setlength{\\extrarowheight}{4pt} \\pagestyle{fancy} \\lhead{$schoolname -- $schoolyear} \\rfoot{ Printed: $currdate } \\cfoot{ } \n"; print TEX "\\begin{document}\n"; my %sortedstudent; # sort the students by name; foreach my $provnum ( keys %arr ) { $sth1->execute( $provnum ); my ($lastname, $firstname ) = $sth1->fetchrow; $sortedstudent{"$lastname$firstname$provnum"} = $provnum; } foreach my $key ( sort keys %sortedstudent ) { my $provnum = $sortedstudent{$key}; # Get Completed Courses $sth->execute( $provnum ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } # Get Name, Grade $sth1->execute( $provnum ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ( $lastname, $firstname, $grade, $birthdate ) = $sth1->fetchrow; my $first = 1; my $totalcredits; my $prevschoolyear = -1; my $currschoolyear; my $colora = "\\rowcolor[gray]{$grayscale}"; my $colorb = '1'; print TEX "\n{\\normalsize Completed Courses}\n\\medskip\n\n"; print TEX "{\\large $firstname $lastname} Grade $grade -- $birthdate ($provnum)\n\n"; while ( my $cref = $sth->fetchrow_hashref ) { my %c = %$cref; # Get Course Title, if not present if ( not $c{coursetitle} ) { $sth2->execute( $c{courseid} ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $title = $sth2->fetchrow; $c{coursetitle} = $title; # since some records don't have the course title. } if ( $first ) { # start the table. $first = 0; # Start Table: 6 columns print TEX "\\begin{tabular}{|p{70mm}|p{10mm}|p{23mm}|p{22mm}|p{10mm}|p{10mm}|}\\hline\n"; print TEX qq{\\bf Course Title &\\bf Code &\\bf{School~Year} &\\bf End Date &\\bf Mark &\\bf{Credits}\\\\ \\hline\n}; } $prevschoolyear = $currschoolyear; $currschoolyear = $c{courseenddate}; if ( $currschoolyear ne $prevschoolyear ) { # toggle color if ( $bgcolor eq $colora ) { $bgcolor = ''; } else { $bgcolor = $colora; } } print TEX "$bgcolor $c{coursetitle} &$c{courseid} &$c{schoolyear} "; print TEX "&$c{courseenddate} &$c{finalmark}"; $totalcredits += $c{creditsearned}; if ( $c{creditsearned} == 0 ) { $c{creditsearned} = "{\\bf $c{creditsearned} }"; } print TEX "&$c{creditsearned}\\\\\ \\hline\n"; } if ( not $first ) { print TEX "\\bf{Credits Earned} &\\bf $totalcredits & & & & \\\\ \\hline\n"; print TEX "\\end{tabular}\n\n"; print TEX "\\newpage\n"; } else { print TEX "No Courses Found\n"; } } # end of student loop. print TEX "\\end{document}\n"; close TEX; system("$pdflatex $filename >pdflog$$.txt"); system("mv $shortname.pdf $downloaddir"); system("mv pdflog$$.txt $downloaddir"); system("rm -f $shortname.*"); print qq{

\n}; print qq{$lex{'View/Download'} $title

\n}; print qq{

[ $lex{'View Log File'}}; print qq{\n | $lex{Main} ]

\n}; print qq{\n}; } # end of courseReport