#! /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 = 'markview2_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'} 2"; 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}; selectCourses(); } 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 selectCourses { #---------------- # 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}; } if ( not $arr{grade} ) { # print qq{

Missing Grade!

\n}; print qq{\n}; exit; } print qq{

Grade $arr{grade} Courses

\n}; # Form Header print qq{
\n}; print qq{\n}; print qq{\n}; # Get Courses for the grade my $sth = $dbhr->prepare("select * from subject where grade = ? order by startrptperiod, endrptperiod, description"); $sth->execute( $arr{grade} ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $sth1 = $dbhr->prepare("select count(distinct studnum) from eval where subjcode = ?"); # Name of the Teacher of this course my $sth2 = $dbhr->prepare("select lastname, firstname from staff where userid = ?"); my $first = 1; while ( my $ref = $sth->fetchrow_hashref ) { if ( $first ) { print qq{\n}; print qq{}; print qq{\n}; $first = 0; } my %c = %$ref; # find enrollment $sth1->execute( $c{subjsec} ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $ecount = $sth1->fetchrow; # get teacher name; $sth2->execute( $c{teacher} ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my ($ln,$fn) = $sth2->fetchrow; my $teacher = qq{$ln, $fn}; if ( $ecount ) { print qq{}; } else { print qq{}; } print qq{\n}; print qq{}; print qq{\n}; } print qq{
CourseNumberTeacherTermsEnrolled
$c{description}$c{subjsec}$teacher$c{startrptperiod}-$c{endrptperiod}$ecount
\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. # my $finalterm = $arr{finalterm}; # delete $arr{finalterm}; # if ( $finalterm ) { # print qq{

Final Term Override: $finalterm

\n}; # } my (%courses, %students, %enrol); # courses{subjsec} = ref; $students{$studnum} = name; enrol{$subjsec} = @studnum; # Get Course Info. my $sth = $dbhr->prepare("select * from subject where subjsec = ?"); my $sth1 = $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"); my $sth2 = $dbhr->prepare("select a1 from eval where studnum = ? and subjcode = ? and term = ?"); foreach my $subjsec ( keys %arr ) { $sth->execute( $subjsec ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $ref = $sth->fetchrow_hashref; $courses{ $ref->{subjsec} } = $ref; # index by subjsec # get students in the course, sort by name $sth1->execute( $subjsec ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my ($sn,$ln,$fn) = $sth1->fetchrow ) { push @{ $enrol{$subjsec} }, qq{$sn}; $students{$sn} = qq{$ln, $fn}; } } # now print courses. # my $first = 1; my $count; foreach my $subjsec ( keys %courses ) { my %c = %{ $courses{$subjsec}}; my $finalterm = $c{endrptperiod}; # if ( $first ) { print qq{\n}; print qq{\n}; print qq{\n}; # $first = 0; # } foreach my $studnum ( @{ $enrol{$subjsec} } ) { # 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{
$c{description}
}; print qq{$subjsec $c{teacher} $c{startrptperiod}-$c{endrptperiod}
Student$lex{Mark}
$students{$studnum}$mark
\n}; $count++; if ( $count % 4 == 0 ) { print qq{
\n}; } } print qq{\n}; exit; }