#!/usr/bin/perl
#  Copyright 2001-2024 Leslie Richardson
#  This file is part of Open Administration for Schools

# Check OA student information against the mss_Student record.
# Also populate the mssid field from this table, if there is a match.


use DBI;
use CGI;
use Number::Format qw(round);


my $self = 'mssCheckDemog.pl';

my %lex = ( 'Main' => 'Main',
	    'Error' => 'Error',
	    'Grade' => 'Grade',
	    'Homeroom' => 'Homeroom',
	    'Group' => 'Group',
	    'Separate with Spaces' => 'Separate with Spaces',
	    'Blank=All' => 'Blank=All',
	    'Continue' => 'Continue',
	    'Values' => 'Values',

	    );


eval require "../../etc/admin.conf";
if ( $@ ) {
    print $lex{Error}. " $@<br>\n";
    die $lex{Error}. " $@\n";
}

# contains entry/exit codes and text.
eval require "/opt/openadmin/global/global.conf";
if ( $@ ) {
    print $lex{Error}. " $@<br>\n";
    die $lex{Error}. " $@\n";
}

# Shorten up.
foreach my $key ( keys %g_enrol ) {
    $g_enrol{$key} =~ s/\(.*//;
}
 

my $q = CGI->new;
my %arr = $q->Vars;
print $q->header( -charset, $charset ); 

my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);


my @tim = localtime(time);
my $year = $tim[5] + 1900;
$tim[4]++;
for (0..4){if (length($tim[$_]) == 1){ $tim[$_] = '0'.$tim[$_];}}
my $currdate = "$year-$tim[4]-$tim[3]";
my $currtime = "$tim[2]:$tim[1]:$tim[0]";


my $title = "MSS - Check OA Demographic Data vs MSS student table";
print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">$chartype\n};
print qq{</head><body>[ <a href="$homepage">Main</a> |\n};
print qq{<a href="$exppage">Export</a> ]\n};
print qq{<h1>$title</h1>\n};

#if ( not $arr{page} ) {
#    showStartPage();

#} elsif ( $arr{page} == 1 ) {
#    delete $arr{page};
    checkStudents();
#}



#-----------------
sub checkStudents {
#-----------------

    #foreach my $key ( keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }

    #    print qq{<form action="$self" method="post">\n};
    #    print qq{<input type="hidden" name="page" value="2">\n};
    #    print qq{<input type="submit" value="Send Selected Student Marks"><br>\n};


    # check if we have any data.
    my $sth = $dbh->prepare("select count(*) from mss_student");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    my $studcount = $sth->fetchrow;
    if ( not $studcount ) { # no data
	print qq{<h3 style="margin:1em;">No MSS Student Data</h3>\n};
	print qq{</body></html>\n};
	exit;
    }

    # check for any students without mssid and/or prov num
    my $sth = $dbh->prepare("select lastname,firstname, studnum, grade, mssid, provnum from student
    where (mssid is NULL or mssid = 0) or (provnum is null or provnum = '') ");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    my $first = 1;
    while ( $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;
	if ( $first ) { # start table
	    print qq{<table cellpadding="3" cellspacing="0" border="1" style="float:left;">\n};
	    print qq{<caption style="font-weight:bold;font-size:120%;">Missing MSSID or Provincial Number</caption>\n};
	    print qq{<tr><th>Name</th><th>Grade</th><th>Prov Num</th><th>MSSID</th></tr>\n};
	    $first = 0;
	}

	print qq{<tr><td><b>$r{lastname}</b>, $r{firstname} ($r{studnum})</td><td>$r{grade}</td>\n};
	print qq{<td>$r{provnum}</td><td>$r{mssid}</td></tr>\n};
    }
    if ( not $first ) { print qq{</table>\n}; }
    

    
    # Loop through the mss_student table, and compare to a possibly matching student record.
    my $sth = $dbh->prepare("select * from mss_student order by lastname, firstname");

    my $sth1 = $dbh->prepare("select * from studentall where provnum = ?");
    my $sth2 = $dbh->prepare("select count(*) from studentwd where provnum = ?");

    # Get Student Names based on provincial number
    my %oastud;
    my $sth3 = $dbh->prepare("select provnum, firstname, lastname, birthdate from student");
    $sth3->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    while ( my $ref = $sth3->fetchrow_hashref ) {
	my %r = %$ref;
	if ( not $r{provnum} ) {
#	    print qq{<div style="font-weight:bold;color:red;">};
	    #	    print qq{Missing provincial number for: $r{firstname} $r{lastname}</div>\n};
	    next;
	}
	$oastud{ $r{provnum} } = qq{<b>$r{lastname}</b>, $r{firstname} ($r{birthdate}) };
    }

    my $sth4 = $dbh->prepare("update student set mssid = ? where studid = ?");

    my %withdrawn; # all withdrawn students in the mss table list (ie. active)

    
    # loop over the mss_student table
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}

    print qq{<div style="width:60ch;margin:1em;float:left;">Students in <span style="color:red;">RED</span>\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 <b>Enrollments</b> and paste in };
    print qq{their provincial number.<br><br>};
    print qq{<a href="https://www.k12.gov.sk.ca/sdsprod/index.jsp" };
    print qq{class="button" style="background-color:#6A6;" target="_blank">SDS Web Site</a>\n};
    print qq{Opens in a new tab</div>\n};
    print qq{<div style="clear:left;"></div>\n};

    
    print qq{<table cellpadding="3" border="1" cellspacing="0" style="margin:1em;float:left;">\n};
    print qq{<caption style="text-align:right;margin-right:4ch;">Edit Opens in New Tab</caption>\n};
    print qq{<tr><th>Name</th><th>Error</th><th>MSS</th><th colspan="2">Open Admin</tr>\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{<span style="color:red;">WD</span>\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{<div>MssID added</div>\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{<tr><td>$wdtext <b>$oa{lastname}</b>, $oa{firstname}</td>};
		print qq{<td class="la">Name Mismatch</td>};
		print qq{<td>$r{firstname} $r{lastname}</td>};
		print qq{<td>FN:$oa{firstname} LN:$oa{lastname}</td>};

		print qq{<td><form action="$cgiurl/studed.pl" method="post" target="_blank" };
		print qq{style="display:inline;">\n};
		print qq{<input type="hidden" name="id" value="$oa{studid}">\n};
		if ( $wd ) {
		    print qq{<input type="hidden" name="tb" value="wd">\n};
		}
		    
		print qq{<input type="submit" name="submit" value="Edit"></form>\n};

		print qq{</td></tr>\n};
		$error = 'Name';
		$count++;
	    }

	    # Verify Birthdate matches;
	    if ( $r{birthdate} ne $oa{birthdate} ) {
		print qq{<tr><td>$wdtext <b>$oa{lastname}</b>, $oa{firstname}</td>};
		print qq{<td>Birthdate Error</td><td>$r{birthdate}</td>};
		print qq{<td>$oa{birthdate}</td>\n};

		print qq{<td><form action="$cgiurl/studed.pl" method="post" target="_blank" };
		print qq{style="display:inline;">\n};
		print qq{<input type="hidden" name="id" value="$oa{studid}">\n};
		if ( $wd ) {
		    print qq{<input type="hidden" name="tb" value="wd">\n};
		}
		print qq{<input type="submit" name="submit" value="Edit"></form>\n};

		print qq{</td></tr>\n};
		$error = 'Birthdate';
		$count++;
	    }

	    # Verify Grade matches;
	    my $mssgrade = $r{grade};
	    $mssgrade =~ s/^0//;
	    if ( $mssgrade ne $oa{grade} ) {
		print qq{<tr><td>$wdtext <b>$oa{lastname}</b>, $oa{firstname}</td>};
		print qq{<td class="la">Grade Error</td>};
		print qq{<td>$r{grade}</td>};
		print qq{<td>$oa{grade}</td>\n};

		print qq{<td><form action="$cgiurl/studed.pl" method="post" target="_blank" };
		print qq{style="display:inline;">\n};
		print qq{<input type="hidden" name="id" value="$oa{studid}">\n};
		if ( $wd ) {
		    print qq{<input type="hidden" name="tb" value="wd">\n};
		}
		print qq{<input type="submit" name="submit" value="Edit"></form>\n};

		print qq{</td></tr>\n};
		$error = 'Grade';
		$count++;
	    }

	    
	} # end of OA loop

	if ( $first ) {
	    print qq{<tr><td style="color:red;" colspan="4">No Matching student for $r{provnum}};
	    print qq{ - $r{firstname} $r{lastname} / $r{birthdate}};
	    print qq{</td></tr>\n};
	    $count++;
	
	}

#	$count++;
	if ( $count > 24 and $count % 25 == 0 ) { # show header again
	    print qq{<tr><th>Name</th><th>Error</th><th>MSS</th><th colspan="2">Open Admin</tr>\n};
	    $count = 1;
	}

#	print qq{<tr><td colspan="3">Count:$count</td></tr>\n};
	
    } # end of student loop

    print qq{</table>\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{<b>$ln</b>, $fn ($sn)};
	$grade{$provnum} = $gr;
	$dob{$provnum} = $dob;
	$studnum{$provnum} = $sn;
    }
    my @sort = sort keys %sort;

    my $first = 1;
    
    foreach my $key ( @sort ) {
	my $provnum = $sort{$key};

	if ( $first ) {
	    print qq{<div style="float:left;">\n};
	    print qq{<table cellpadding="3" border="1" cellspacing="0" };
	    print qq{style="margin:1em;">\n};
	    print qq{<caption style="font-weight:bold;font-size:120%;">Students to Enrol in MSS };
	    print qq{(In OA, not MSS)</caption>\n};
	    print qq{<tr><th>Name</th><th>Prov #</th><th>Birth date</th><th>Grade</th><th>Course#</th>};
	    print qq{<th>Entry Date/Code</th></tr>\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<br>\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{<tr><td>$mcount. $name{$provnum}</td><td>$provnum</td><td>$dob{$provnum}</td>};
	print qq{<td class="cn">$grade{$provnum}</td><td>$coursecount</td>};
	print qq{<td>$t{date}/$g_enrol{$t{entrytype}}</tr>\n};
	$mcount++;
    }

    if ( not $first ){
	print qq{</table>\n\n};
    } else {
	print qq{<h3 style="margin:1em;">No Students to enrol in MSS</h3>\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{<b>$ln</b>, $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{<table cellpadding="3" border="1" cellspacing="0" };
	    print qq{style="margin:1em;">\n};
	    print qq{<caption style="font-weight:bold;font-size:120%;">};
	    print qq{Students to Withdraw from MSS (Withdrawn in OA)</caption>\n};
	    print qq{<tr><th>Name</th><th>Prov #</th><th>Mssid</th><th>Birthdate</th>};
	    print qq{<th>Gr</th><th>Exit Date/Code</tr>\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<br>\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<br>\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{<tr><td>$wdcount. $name{$provnum}</td><td>$provnum</td><td>$mssid</td>};
	print qq{<td>$dob{$provnum}</td><td>$grade{$provnum}</td>};
	if ( not $error ) {
	    print qq{<td>$t{date}/$g_wdraw{$t{exittype}}</td>\n};
	} else {
	    print qq{<td>$error</td>};
	}
	print qq{</tr>\n};

	$wdcount++;
    }

    if ( not $first ) { 
	print qq{</table>\n};
    } else {
	print qq{<h3 style="margin:1em;">No Students to withdraw from MSS</h3>\n};
    }

    print qq{</div>\n}; # end of floated div.
    print qq{</body></html>\n};

    exit;


} # end of selectStudents



#----------------------
# Lower Level Functions
#----------------------

#----------------
sub checkprovnum {
#----------------

    my @pn = split(//,$_[0]);
    for ( my $i=1; $i<=8; $i += 2) {
	$pn[$i] *= 2;
	if ($pn[$i] > 9) {
	    my @tmp = split(//,$pn[$i]);
	    $pn[$i] = $tmp[0] + $tmp[1];
        }
    }
    
    my $tempnum = 0;
    foreach my $pnum (@pn) {
	$tempnum += $pnum;
    }
    @pn = split(//,$tempnum);   
    my $retval= pop(@pn);
    $retval = 9 - $retval;
    return $retval;

}


#----------------
sub checkHealthId {
#----------------

    my $hn = shift;
    my @hn = split(//,$hn);
    my ($i, $total);
    foreach my $num (reverse @hn){
	$i++;
	$num *= $i;
	$total += $num;
    }
    my $remainder = $total % 11;  # should be zero, if correct.
    
    return $remainder;

}



#----------------
sub showStartPage {
#----------------

    # Setup the form and start of table.
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<table cellpadding="3" border="1" cellspacing="0">\n};
    
    print qq{<tr><td class="bra">$lex{Group}</td><td class="la">\n};
    print qq{<select name="groupType"><option>$lex{Grade}</option>\n};
    print qq{<option>$lex{Homeroom}</option></select>\n};
    print qq{</td></tr>\n};

    print qq{<tr><td class="bra">$lex{Values}</td><td class="la">\n};
    print qq{<input type="text" name="groupValue" size="25"><br>\n};
    print qq{$lex{'Separate with Spaces'}, $lex{'Blank=All'}</td></tr>\n};


    print qq{<tr><td class="cn" colspan="2">};
    print qq{<input type="submit" value="$lex{Continue}">};
    print qq{</td></tr>\n};

    print qq{</table></form>\n};
    print qq{</body></html>\n};

    exit;

}

