#! /usr/bin/perl # Copyright 2001-2021 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 = 'markview3_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'} 3 - By Grade"; 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}; selectGrades(); } 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} for Courses}; # print qq{
$lex{'Check Next Page'}
$lex{'Show Withdrawn'}
\n}; print qq{\n}; exit; } #--------------- sub selectGrades { #--------------- # 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}; } # 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{

Select Grades

\n}; # Form Header print qq{
\n}; print qq{\n}; print qq{\n}; my $first = 1; foreach my $grade ( sort {$a <=> $b } @grades ) { if ( $first ) { print qq{\n}; print qq{\n}; $first = 0; } print qq{}; print qq{\n}; } print qq{
Grade
$grade
\n}; print qq{
\n}; print qq{
\n}; exit; } # end of selectCourses #------------- 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. # only grades left here in %arr # Setup SQL scripts my $sth = $dbhr->prepare("select * from subject where grade = ? order by description"); my $sth1 = $dbhr->prepare("select studnum, lastname, firstname from student where grade = ? order by lastname, firstname"); my $sth2 = $dbhr->prepare("select a1 from eval where studnum = ? and subjcode = ? and term = ?"); my $sth3 = $dbhr->prepare("select distinct e.studnum, s.lastname, s.firstname from eval e, studentall s where subjcode = ? and e.studnum = s.studnum order by s.lastname, s.firstname"); foreach my $grade ( sort keys %arr ) { my (%course, @course, %studname, %enrol, @studsort); # course{subjsec} = ref; $studname{$studnum} = name; eval{studnum}{$subjsec} = mark; # Get Courses in this grade $sth->execute( $grade ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my $ref = $sth->fetchrow_hashref ) { $course{ $ref->{subjsec} } = $ref; # index by subjsec push @course, $ref->{subjsec}; } # Get Students in this grade $sth1->execute( $grade ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my ($studnum, $lastname, $firstname) = $sth1->fetchrow ) { $studname{ $studnum } = qq{$lastname, $firstname}; push @studsort, $studnum; } # Find any student outliers (possibly in other grades) foreach my $subjsec ( @course ) { $sth3->execute( $subjsec ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my ($studnum, $lastname, $firstname) = $sth3->fetchrow ) { if ( not $studname{ $studnum } ) { # another grade student; add him/her $studname{ $studnum } = qq{$lastname, $firstname}; push @studsort, $studnum; } } } # Print the Grade Table. my $first = 1; foreach my $studnum ( @studsort ) { # each row if ( $first ) { # start the table. print qq{\n}; print qq{\n}; # print Course Names; print qq{\n}; foreach my $subjsec ( @course ) { print qq{}; } print qq{\n}; $first = 0; } print qq{}; foreach my $subjsec ( @course ) { my $finalterm = $course{$subjsec}->{endrptperiod}; # Get Student Mark $sth2->execute( $studnum, $subjsec, $finalterm ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $mark = $sth2->fetchrow; print qq{\n}; } print qq{\n}; # end of course loop / this row } # end of student loop print qq{
}; print qq{Grade $grade
$course{$subjsec}->{smdesc}
$studname{$studnum}$mark
\n}; # $count++; # if ( $count % 4 == 0 ) { # print qq{
\n}; # } } # end of grade loop print qq{\n}; exit; }