#!/usr/bin/perl
# Copyright 2001-2023 Leslie Richardson
# This file is part of Open Admin for Schools.
# Find the provincial numbers matching the mssid values in the mss
# transcript table, and update the matching student records.
use DBI;
use CGI;
use Cwd;
my $self = 'mssFixMssid.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{Update Fields};
print qq{$doctype\n$title
\n};
print qq{[ $lex{Main} |\n};
print qq{Export ]\n};
my @mssid;
my $sth = $dbh->prepare("select distinct mssid from mss_transcript");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
while ( my $mssid = $sth->fetchrow ) {
my $pn = findStudent( $mssid );
}
#--------------
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 @level1;
foreach my $crs ( sort keys %courses ) {
foreach my $mark ( sort keys %{ $courses{$crs}} ) {
if ( not $mark ) { next; }
$sth->execute( $crs,$mark );
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
while ( my $provnum = $sth->fetchrow ) {
if ( $provnum ) {
push @level1, $provnum;
}
}
}
}
if ( not @level1 ) {
return;
}
return $level1[0];
# return $provnum;
}