#! /usr/bin/perl # Copyright 2001-2020 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 = ('Marks' => 'Marks', 'Main' => 'Main', 'No Record(s) Found' => 'No Record(s) Found', 'Grade' => 'Grade', 'Error' => 'Error', 'Students' => 'Students', 'Continue' => 'Continue', 'School Year' => 'School Year', 'Check Next Page' => 'Check Next Page', 'Show Withdrawn' => 'Show Withdrawn', 'Select' => 'Select', 'Withdrawn' => 'Withdrawn', 'Student' => 'Student', 'Course' => 'Course', 'Terms' => 'Terms', 'Mark' => 'Mark', ); use DBI; use CGI; my $self = 'markview_past.pl'; # Read config variables eval require "../../etc/admin.conf.root"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } eval require "../../lib/libattend.pl"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } eval require "../../lib/liblatex.pl"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } # Local connection my $dsn = "DBI:mysql:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); $dbh->{mysql_enable_utf8} = 1; # Load Main admin configuration ('admin') my $sth = $dbh->prepare("select id, datavalue from conf_system where filename = 'admin'"); $sth->execute; if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ($id, $datavalue) = $sth->fetchrow ) { eval $datavalue; if ( $@ ) { print "$lex{Error}: $@
\n"; die "$lex{Error}: $@\n"; } } # Get the values from configuration system my $sth = $dbh->prepare("select datavalue from conf_system where dataname = ?"); foreach my $val ( qw( r_MarkField r_SupressSubject r_AdditionalComments )) { $sth->execute( $val ); my $datavalue = $sth->fetchrow; eval $datavalue; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@\n"; } } # Capture this, since may change in older configs. my $currdownloaddir = $downloaddir; my $q = new CGI; my %arr = $q->Vars; print $q->header( -charset, $charset ); my @tim = localtime(time); my $year = $tim[5] + 1900; my $month = $tim[4] + 1; my $day = $tim[3]; if (length($month) == 1){ $month = "0".$month;} if (length($day) == 1){ $day = "0".$day;} my $currsdate = "$year-$month-$day"; my $currdate = "$month[$month] $day, $year"; # Load Remote database my $dbhr; if ( $arr{db} ) { # from start page my $db = $arr{db}; my $dsn = "DBI:mysql:database=$db;host=$remotehost"; $dbhr = DBI->connect($dsn,$remoteuser,$remotepassword); # Get the values from configuration system of this year. my $sthr = $dbhr->prepare("select datavalue from conf_system where dataname = ?"); foreach my $val ( qw( schoolyear )) { $sthr->execute( $val ); my $datavalue = $sthr->fetchrow; eval $datavalue; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@\n"; } } } my $title = "$lex{Student} $lex{'Marks'}"; print qq{$doctype\n$title $chartype\n\n}; print qq{[ $lex{Main} |\n}; print qq{SSP ]\n}; print qq{

$title

\n}; if ( not $arr{page} ) { showStartPage(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; selectStudents(); } elsif ( $arr{page} == 2 ) { delete $arr{page}; showMarks(); } #---------------- sub showStartPage { #---------------- # Get remote databases my $remotedbase = 'information_schema'; my $dsnr = "DBI:mysql:database=$remotedbase;host=$remotehost"; my $dbhr = DBI->connect($dsnr,$remoteuser,$remotepassword); my $sth = $dbhr->prepare("select distinct table_schema from TABLES order by table_schema"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my %remotedb; # Select Previous Databases for this school while ( my $db = $sth->fetchrow ) { if ( $db eq 'mysql' or $db eq 'information_schema' ) { next; } if ( $db =~ m/$dbase/ ) { $remotedb{$db} = 1; } } # Get Student Grades my @grades; $sth = $dbh->prepare("select distinct grade from student where grade is not NULL and grade != ''"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; } while ( my $grade = $sth->fetchrow ) { push @grades, $grade; } print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; foreach my $db ( sort keys %remotedb ) { my $temp = $db; $temp =~ s/$dbase//; # strip database name; my $year = $temp - 1; # previous year; $year = qq{$year-$temp}; # now in 2018-2019 format print qq{\n}; } print qq{\n}; # Get Grade print qq{\n}; # Check next Page print qq{\n}; print qq{\n}; # Show Withdrawn print qq{\n}; print qq{\n}; # Continue print qq{\n}; print qq{
$lex{Select} $lex{'School Year'}
$year ($db)

$lex{Select} $lex{Grade}}; print qq{
$lex{'Check Next Page'}
$lex{'Show Withdrawn'}
\n}; print qq{\n}; exit; } #---------------- sub selectStudents { #---------------- # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}
\n"; } my $studenttable = 'student'; if ( $arr{showwithdrawn} ) { $studenttable = 'studentall'; } delete $arr{showwithdrawn}; my $checked; if ( $arr{checknextpage} ) { $checked = qq{checked="checked"}; delete $arr{checknextpage}; } my ($select, $gradetext); if ( $arr{grade} ) { # we're picking a grade $select = 'where grade = ?'; $gradetext = qq{– $lex{Grade} $arr{grade}}; } my $sth = $dbhr->prepare("select lastname, firstname, studnum, grade from $studenttable $select order by lastname, firstname"); if ( $select ) { $sth->execute( $arr{grade} ); } else { $sth->execute; } if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $sth1 = $dbhr->prepare("select count(*) from studentwd where studnum = ?"); print qq{

$lex{Select} $lex{Students} $gradetext \n}; print qq{(WD = $lex{Withdrawn})

\n}; # Form Header print qq{
\n}; print qq{\n}; print qq{\n}; print qq{

Final Term Override }; print qq{ Blank = Default

\n}; print qq{\n}; print qq{\n}; print qq{\n}; while ( my ( $lastname, $firstname, $studnum, $grade ) = $sth->fetchrow ) { # Find out if withdrawn my $wd; $sth1->execute( $studnum ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $wdcount = $sth1->fetchrow; if ( $wdcount ) { $wd = qq{WD}; } print qq{ }; print qq{}; print qq{\n}; } print qq{\n}; print qq{
$lex{Student}Number$lex{Grade}
$wd $lastname, $firstname$studnum$grade
\n}; exit; } # end of selectStudents #------------- sub showMarks { #------------- # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}
\n"; } delete $arr{db}; # not needed, since used at top of script. my $finalterm = $arr{finalterm}; delete $arr{finalterm}; if ( $finalterm ) { print qq{

Final Term Override: $finalterm

\n}; } # Student Info my $sth = $dbhr->prepare("select lastname, firstname, grade from studentall where studnum = ?"); my (%sortedname, %studname, %studgrade); foreach my $studnum ( keys %arr ) { $sth->execute( $studnum ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my ($lastname, $firstname, $grade ) = $sth->fetchrow; $sortedname{"$lastname$firstname$studnum"} = $studnum; $studname{$studnum} = "$lastname, $firstname"; $studgrade{$studnum} = $grade; } # Course Codes for this student # Subject (Course) Info my $sth2 = $dbhr->prepare("select * from subject where subjsec = ?"); # Mark for this student my $sth3 = $dbhr->prepare("select * from eval where studnum = ? and subjcode = ? and term = ?"); # Name of the Teacher of this course my $sth4 = $dbhr->prepare("select lastname, firstname from staff where userid = ?"); my %courses; # course info: $courses{subjsec} = $ref; # Loop through each student, getting mark, subject, and demographic info. foreach my $key ( sort keys %sortedname ) { my $studnum = $sortedname{$key}; my $first = 1; print qq{

$studname{$studnum} ($studnum) $lex{Grade} $studgrade{$studnum}\n}; print qq{ - $schoolyear

\n}; # Get student's courses (look into eval) my $sth = $dbhr->prepare("select distinct subjcode from eval where studnum = ?"); $sth->execute($studnum); while ( my $subjsec = $sth->fetchrow ) { if ( $first ) { print qq{\n}; print qq{}; print qq{\n}; $first = 0; } # Get Course Info, if not already loaded my %c; if ( not $courses{$subjsec} ) { $sth2->execute( $subjsec ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $cref = $sth2->fetchrow_hashref; %c = %$cref; $courses{$subjsec} = $cref; # put into cache. } else { %c = %{ $courses{$subjsec}}; # print "From Cache
\n"; # foreach my $key ( sort keys %c ) { print "K:$key V:$c{$key}
\n"; } } $sth4->execute( $c{teacher} ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my ($ln,$fn) = $sth4->fetchrow; my $teacher = qq{$ln, $fn}; # Allow override if no values in end reporting period. my $endterm = $c{endrptperiod}; if ( $finalterm ) { $endterm = $finalterm; } $sth3->execute( $studnum, $subjsec, $endterm); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $eref = $sth3->fetchrow_hashref; my %e = %$eref; print qq{}; print qq{\n}; print qq{\n}; } # subject loop if ( $first ) { print qq{

No Courses Found

\n}; } else { print qq{
$lex{Course}Teacher$lex{Terms}$lex{Mark}
$c{description} ($subjsec)$teacher$c{startrptperiod}-$c{endrptperiod}$e{a1}
\n}; } } # studnum loop print qq{\n}; exit; }