#!/usr/bin/perl
# Copyright 2001-2024 Leslie Richardson
# This file is part of Open Admin for Schools.
my %lex = ('View' => 'View',
'Staff' => 'Staff',
'Absences' => 'Absences',
'Main' => 'Main',
'Eoy' => 'Eoy',
'No Records Found' => 'No Records Found',
'Error' => 'Error',
'Edit' => 'Edit',
'Delete' => 'Delete',
);
use DBI;
use CGI;
use Cwd;
use Number::Format qw{round};
my $self = 'mssCourseView.pl';
eval require "../../etc/admin.conf";
if ( $@ ) {
print $lex{Error}. ": $@ \n";
die $lex{Error}. ": $@\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 Page Header
my $title = qq{View MSS Current Courses};
print qq{$doctype\n$title
\n};
print qq{[ $lex{Main} |\n};
print qq{Export ]\n};
print qq{\n};
print qq{
$title
\n};
showRecords();
#--------------
sub showRecords {
#--------------
# We are only using 2 fields from mss_currcourse: mssid and coursecode.
# my @fields;
# my $sth = $dbh->prepare("show columns from mss_currcourse");
# $sth->execute;
# if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
# while (my @cols = $sth->fetchrow ) {
# if ( $cols[0] eq 'id' or $cols[0] eq 'date' or $cols[0] eq 'credit' or
# $cols[0] eq 'integrationid' or $cols[0] eq 'mark') { next; }
# push @fields, $cols[0];
# }
# Setup Queries
# Get student name
my $sth1 = $dbh->prepare("select lastname, firstname, studnum, provnum from studentall
where mssid = ?");
# Are they current students?
my $sth3 = $dbh->prepare("select lastname, firstname, studnum, provnum from student
where mssid = ?");
# Get course description
my $sth2 = $dbh->prepare("select title from sasked_courses where code = ?");
my $sort = qq{order by mssid, coursecode};
if ( $arr{sort} eq 'course' ) {
$sort = qq{order by coursecode,mssid};
}
# get all the mssid values in the mss_currcourses table and find matching names.
my (%current, %withdrawn, @noname);
my (@sort,%sort);
my $sth = $dbh->prepare("select distinct mssid from mss_currcourse");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
while (my $mssid = $sth->fetchrow) {
$sth3->execute($mssid);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
my $cref = $sth3->fetchrow_hashref;
my %c = %$cref;
# print qq{$c{lastname}, $c{firstname} \n};
if ( not $cref) { # not a current student
$sth1->execute($mssid);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
my $wref = $sth1->fetchrow_hashref;
if ( not $wref ) {
$noname{$mssid} = 1;
next;
}
$withdrawn{$mssid} = $wref;
} else {
# must be a current student;
$current{$mssid} = $cref;
# print "CREF:", $cref, " \n";
}
}
# we now have student info in %current,%withdrawn, and %noname.
# now loop over all students current and withdrawn to create a sorted list by name.
foreach my $mssid ( keys %current ) {
my $ref = $current{$mssid};
# print qq{$ref->{lastname} $ref->{firstname} $mssid \n};
$sort{"$ref->{lastname}$ref->{firstname}$mssid"} = $mssid;
}
foreach my $mssid ( keys %withdrawn ) {
my $ref = $withdrawn{$mssid};
# print qq{$ref->{lastname} $ref->{firstname} $mssid \n};
$sort{"$ref->{lastname}$ref->{firstname}$mssid"} = $mssid;
}
my @sort = sort keys %sort;
# Test if we have everything in 3 hashes.
=head
my $count = 1;
foreach my $key ( @sort ) {
my $mssid = $sort{$key};
if ( $withdrawn{$mssid} ) {
my %r = %{ $withdrawn{$mssid} };
print qq{
\n};
}
$count++;
}
=cut
my $sth = $dbh->prepare("select * from mss_currcourse where mssid = ? order by coursecode");
my %courses; # courses{coursecode} = $title;
my $first = 1;
my $bgcolor = '#FFF';
# Loop through mss_currcourse records
foreach my $key ( @sort ) {
my $mssid = $sort{$key};
# get name, etc. from current or withdrawn
my %r; # demographics record
if ( $current{$mssid} ) {
%r = %{ $current{$mssid} };
} elsif ( $withdrawn{$mssid} ) {
%r = %{ $withdrawn{$mssid} };
} else {
print qq{