Students in
RED\n};
print qq{have some name, birthdate, school, etc problems. };
print qq{Copy the 9 digit provincial number and then use the SDS Website, go to
Enrollments };
print qq{and paste in };
print qq{their provincial number.
};
print qq{
SDS Web Site\n};
print qq{Opens in a new tab
\n};
print qq{Synchronization Errors\n};
print qq{| Name | Error | MSS | Open Admin | New
Tab |
|---|
\n};
my $count = 1;
while ( my $ref = $sth->fetchrow_hashref ) {
%r = %$ref; # mss record
if ( not $r{provnum} ) { # notification is above.
next;
}
# Look for matching OA student
$sth1->execute( $r{provnum} );
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
my $first = 1;
while ( my $oaref = $sth1->fetchrow_hashref ) {
my %oa = %$oaref;
$first = 0;
my $error; # set a number if an error
# Check if withdrawn
my $wdtext;
$sth2->execute( $r{provnum} );
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
my $wd = $sth2->fetchrow;
if ( $wd ) {
$withdrawn{ $oa{provnum} } = 1;
$wdtext = qq{WD\n};
}
# Add mssid is not already done.
if ( not $oa{mssid} ) { # no id from mss yet
$sth4->execute( $r{mssid}, $oa{studid} );
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
# print qq{MssID added
\n};
}
# Delete from %oastud, so remainder will be students not in MSS.
delete $oastud{ $oa{provnum} };
# print Name
# Verify the name matches and also the birthdate.
if ( $r{lastname} ne $oa{lastname} or $r{firstname} ne $oa{firstname} ) {
print qq{| $wdtext $oa{lastname}, $oa{firstname} | };
print qq{Name Mismatch | };
print qq{$r{firstname} $r{lastname} | };
print qq{FN:$oa{firstname} LN:$oa{lastname} | };
print qq{\n};
print qq{ |
\n};
$error = 'Name';
$count++;
}
# Verify Birthdate matches;
if ( $r{birthdate} ne $oa{birthdate} ) {
print qq{| $wdtext $oa{lastname}, $oa{firstname} | };
print qq{Birthdate Error | $r{birthdate} | };
print qq{$oa{birthdate} | \n};
print qq{\n};
print qq{ |
\n};
$error = 'Birthdate';
$count++;
}
# Verify Grade matches;
my $mssgrade = $r{grade};
$mssgrade =~ s/^0//;
if ( $mssgrade ne $oa{grade} ) {
print qq{| $wdtext $oa{lastname}, $oa{firstname} | };
print qq{Grade Error | };
print qq{$r{grade} | };
print qq{$oa{grade} | \n};
print qq{\n};
print qq{ |
\n};
$error = 'Grade';
$count++;
}
} # end of OA loop
if ( $first ) {
print qq{| No Matching student for $r{provnum}};
print qq{ - $r{firstname} $r{lastname} / $r{birthdate}};
print qq{ |
\n};
$count++;
}
# $count++;
if ( $count > 24 and $count % 25 == 0 ) { # show header again
print qq{| Name | Error | MSS | Open Admin |
|---|
\n};
$count = 1;
}
# print qq{| Count:$count |
\n};
} # end of student loop
print qq{
\n};
# Students remaining in OA, not in MSS (Must be added to MSS)
my $mcount = 1;
my (@sort, %sort, %name, %dob, %grade, %studnum);
my $sth1 = $dbh->prepare("select count(distinct subjcode) from eval where studnum = ?");
my $sth3 = $dbh->prepare("select * from transfer where provnum = ? order by date desc");
my $sth4 = $dbh->prepare("select studnum from studentall where provnum = ?");
my $sth5 = $dbh->prepare("select * from transfer where studnum = ? order by date desc");
my $sth = $dbh->prepare("select lastname, firstname, studnum, birthdate, grade from studentall
where provnum = ?");
foreach my $provnum ( keys %oastud ) {
$sth->execute($provnum);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
my ($ln,$fn,$sn, $dob,$gr) = $sth->fetchrow;
$sort{"$ln$fn$sn"} = $provnum;
$name{$provnum} = qq{\n};
print qq{
\n};
print qq{Students to Enrol in MSS };
print qq{(In OA, not MSS)\n};
print qq{| Name | Prov # | Birth date | Grade | Course# | };
print qq{Entry Date/Code |
|---|
\n};
$first = 0;
}
# Get course count
$sth1->execute( $studnum{$provnum} );
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
my $coursecount = $sth1->fetchrow;
# Get transfer record, entry
# get withdrawal date and code
$sth3->execute($provnum);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
my $ref = $sth3->fetchrow_hashref;
if ( not defined $ref ) { # no provnum in transfer record
# lookup local student number
$sth4->execute($provnum);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
my $studnum = $sth4->fetchrow;
# print "Studnum:$studnum
\n";
# now get the transfer records based on student numbers.
$sth5->execute($studnum);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
$ref = $sth5->fetchrow_hashref;
}
my %t = %$ref; # need date, and entrytype
my $error;
if ( $t{type} ne 'enrol' ) {
$error = qq{Enrol not found - $t{date}/$t{type}};
}
print qq{| $mcount. $name{$provnum} | $provnum | $dob{$provnum} | };
print qq{$grade{$provnum} | $coursecount | };
print qq{$t{date}/$g_enrol{$t{entrytype}} |
\n};
$mcount++;
}
if ( not $first ){
print qq{
\n\n};
} else {
print qq{
No Students to enrol in MSS
\n};
}
# end of students in OA, not MSS. Students to enrol in MSS.
# OA withdrawn students still in MSS (Must be removed from MSS)
my (@sort, %sort, %name, %grade, %dob);
my $sth1 = $dbh->prepare("select mssid from mss_student where provnum = ?");
my $sth2 = $dbh->prepare("update studentwd set mssid = ? where provnum = ?");
my $sth3 = $dbh->prepare("select * from transfer where provnum = ? order by date desc");
my $sth4 = $dbh->prepare("select studnum from studentall where provnum = ?");
my $sth5 = $dbh->prepare("select * from transfer where studnum = ? order by date desc");
my $sth = $dbh->prepare("select lastname, firstname, studnum, grade, birthdate
from studentwd where provnum = ?");
foreach my $provnum ( keys %withdrawn ) {
$sth->execute($provnum);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
my ($ln,$fn,$sn,$gr,$dob) = $sth->fetchrow;
$sort{"$ln$fn$sn"} = $provnum;
$name{$provnum} = qq{
$ln, $fn ($sn)};
$grade{$provnum} = $gr;
$dob{$provnum} = $dob;
}
my @sort = sort keys %sort;
my $first = 1;
my $wdcount = 1;
foreach my $key ( @sort ) {
my $provnum = $sort{$key};
if ( $first ) { # print header
print qq{
\n};
print qq{};
print qq{Students to Withdraw from MSS (Withdrawn in OA)\n};
print qq{| Name | Prov # | Mssid | Birthdate | };
print qq{Gr | Exit Date/Code |
|---|
\n};
$first = 0;
}
# Get MssId
$sth1->execute($provnum);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
my $mssid = $sth1->fetchrow;
# update withdrawn student record mssid
if ( $mssid ) {
$sth2->execute($mssid, $provnum);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
}
# get withdrawal date and code from transfer table.
$sth3->execute($provnum);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
my $ref = $sth3->fetchrow_hashref;
# print "PN:$provnum REF:$ref
\n";
if ( not defined $ref ) { # no provnum in transfer record
# lookup local student number
$sth4->execute($provnum);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
my $studnum = $sth4->fetchrow;
# print "Studnum:$studnum
\n";
# now get the transfer records based on student numbers.
$sth5->execute($studnum);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
$ref = $sth5->fetchrow_hashref;
}
my %t = %$ref; # need date, and exittype
my $error;
if ( $t{type} ne 'withdraw' ) {
$error = qq{Withdraw not found - $t{date}/$t{type}};
}
print qq{| $wdcount. $name{$provnum} | $provnum | $mssid | };
print qq{$dob{$provnum} | $grade{$provnum} | };
if ( not $error ) {
print qq{$t{date}/$g_wdraw{$t{exittype}} | \n};
} else {
print qq{$error | };
}
print qq{
\n};
$wdcount++;
}
if ( not $first ) {
print qq{
\n};
} else {
print qq{
No Students to withdraw from MSS
\n};
}
print qq{