#!/usr/bin/perl # Copyright 2001-2023 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 = 'mssTranscriptView.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 Transcript Table (TRN Import)}; print qq{$doctype\n$title \n}; print qq{[ $lex{Main} |\n}; print qq{Export ]\n}; print qq{

$title

\n}; if ( not $arr{page} ) { showRecords(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; updateMssid(); } #-------------- sub updateMssid { #-------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key Val:$arr{$key}
\n}; } $sth = $dbh->prepare("update studentwd set mssid = ? where studid = ?"); $sth1 = $dbh->prepare("select lastname, firstname, provnum, studnum from studentwd where studid = ?"); foreach my $key ( sort keys %arr ) { my ($studid,$mssid,$tb) = split(':',$key); $sth1->execute($studid); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($ln,$fn,$provnum,$studnum) = $sth1->fetchrow; if ($tb) { # must be a 1 to be a withdrawn student update $sth->execute($mssid, $studid); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } print qq{
$fn $ln ($studnum / $provnum) mssid $mssid updated
\n}; } } print qq{\n}; exit; } #-------------- sub showRecords { #-------------- my $sth = $dbh->prepare("show columns from mss_transcript"); $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 'comment' or $cols[0] eq 'integrationid' or $cols[0] eq 'grade') { next; } push @fields, $cols[0]; } $sth = $dbh->prepare("select * from mss_transcript order by mssid, date"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } # Find name by mssid my $sth1 = $dbh->prepare("select lastname, firstname, studnum, provnum from studentall where mssid = ?"); # Find name by provnum my $sth2 = $dbh->prepare("select lastname, firstname, studnum from studentall where provnum = ?"); my $first = 1; my %found; while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; if ( $first ) { # do header print qq{\n}; print qq{\n}; print qq{}; foreach my $fld (@fields ) { print qq{\n}; } print qq{\n}; $first = 0; } if ( $r{date} eq '0000-00-00' ) { # skip blank records next; } if ( $r{mark} ) { $r{mark} = round($r{mark},0); } if ( $r{credit} ) { $r{credit} = round($r{credit},0); } # Get Name by MSSID $sth1->execute( $r{mssid} ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($ln,$fn,$studnum,$provnum) = $sth1->fetchrow; if ( not $ln ) { my $res = findStudent( $r{mssid} ); my ($pn,$confcount) = split(':',$res); # confidence count, courses the same if ( $pn ) { # Find Name, info $sth2->execute( $pn ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } ($ln,$fn,$studnum) = $sth2->fetchrow; if ( $ln ) { # found name $ln = qq{}. qq{FND $ln}; $provnum = $pn; $found{"$pn:$r{mssid}:$confcount"} = 1; } else { # not found on lookup $ln = qq{PN:$pn Not found}; } } } print qq{}; if ( not $fn ) { print qq{}; } else { print qq{\n}; } foreach my $field ( @fields ) { print qq{}; } print qq{\n}; } if ($first ) { print qq{

$lex{'No Records Found'}

\n}; print qq{\n}; exit; } else { # close table print qq{
Skipping Blank Date Records
Name$fld
$ln$ln, $fn ($provnum / $studnum)$r{$field}
\n}; } # Found Provincial Numbers to Verify on MSS my $sth = $dbh->prepare("select lastname, firstname, studnum, studid from studentall where provnum = ?"); my $sth1 = $dbh->prepare("select count(*) from studentwd where provnum = ?"); my %sort; foreach my $found ( keys %found ) { my ($pn,$mssid,$confcount) = split(':',$found); $sth->execute( $pn ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($ln,$fn,$studnum, $studid ) = $sth->fetchrow; $sort{"$ln$fn$studnum"} = $found; } # Turn off from here =head # Start Form print qq{
}; print qq{\n}; print qq{
\n}; print qq{\n}; print qq{\n}; print qq{}; print qq{}; print qq{\n}; foreach my $key ( sort keys %sort ) { my $found = $sort{$key}; my ($pn,$mssid,$confcount) = split(':',$found); # Find name by provnum $sth->execute( $pn ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($ln,$fn,$studnum, $studid ) = $sth->fetchrow; if ( not $ln ) { $ln = qq{Not Found}; } # Check if withdrawn $sth1->execute( $pn ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $wd = $sth1->fetchrow; print qq{}; print qq{}; my $val = qq{$studid:$mssid:$wd}; print qq{\n}; # print qq{}; print qq{\n}; } print qq{
Found Provincial Numbers - Verify on MSS
NameProv#MSSIDMatchingUpdate
$ln, $fn ($studnum)$pn$mssid$confcount\n}; # print qq{\n}; # if ( $wd ) { # print qq{\n}; # } # print qq{
\n}; print qq{\n}; =cut print qq{
\n}; print qq{

[ $lex{Main} |\n}; print qq{Export ]

\n}; print qq{\n}; exit; } #-------------- sub findStudent { #-------------- my $mssid = @_[0]; # Get courses in mss_transcript with this mssid; my %courses; # courses this student has taken, to try to match. # courses{coursecode}{mark} = date my $sth = $dbh->prepare("select * from mss_transcript where mssid = ?"); $sth->execute( $mssid ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $ref = $sth->fetchrow_hashref ) { my %c = %$ref; $c{mark} =~ s/\.00//; $courses{ $c{coursecode} }{ $c{mark} }{ $c{date} } = 1; } # now look in normal transcripts (sasked_completedcourses) for a matching student. my $sth = $dbh->prepare("select provnum from sasked_completedcourses where courseid = ? and finalmark = ?"); my %match; # match{provnum}{crs} = mark; foreach my $crs ( sort keys %courses ) { foreach my $mark ( sort keys %{ $courses{$crs}} ) { if ( $mark < 2 ) { next; } # some students have 1 as a DUD mark. $sth->execute( $crs,$mark ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $provnum = $sth->fetchrow ) { if ( $provnum ) { $match{$provnum}{$crs} = $mark; } } # end of loop populating %match } # end of mark loop } my ($maxcount, $bestpn); foreach my $provnum (sort keys %match ) { foreach my $crs ( sort keys %{$match{$provnum}} ) { # print qq{PN:$provnum CRS:$crs
\n}; } my $count = scalar keys %{$match{$provnum}}; if ($count > $maxcount) { $maxcount = $count; $bestpn = $provnum; } # print qq{Count:$count
\n}; } # print "Best PN:$bestpn

\n"; return qq{$bestpn:$maxcount}; }