#! /usr/bin/perl
#  Copyright 2001-2019 Leslie Richardson
#  This file is part of Open Admin for Schools.

# Query School Courses and compare to provincial enrollments.

# Passed Values: none.

my $self = 'qryschcourses.pl';

use DBI;
use CGI;
use XML::Writer;
use XML::Writer::String;
use Data::UUID;
use HTTP::Request::Common qw(POST); 
use HTTP::Headers; 
use LWP::UserAgent; 
use XML::LibXML;

my %lex = ( 'Main' => 'Main',
	    'Error' => 'Error',

	    );


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


eval require "slxmllibNew.pl";
if ( $@ ) {
    print $lex{Error}. " $@<br>\n";
    die $lex{Error}. " $@\n";
}


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


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

if ($arr{debug}){
    $debug = 1;
    delete $arr{debug};
}


print qq{$doctype\n<html><head><title>SDS Query Course Enrollments</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">\n};
print qq{<style type="text/css">\n};
print qq{body { padding: 1em 3em;}</style>\n};
print qq{$chartype\n</head><body>\n};
print qq{[ <a href="$homepage">Main</a> | <a href="$exppage">Export</a> ]\n};

print qq{<h1>Query Sask Ed Course Enrollments</h1>\n};
print qq{<p>Query Sask Ed's records of student course enrollments for your school</p>\n};


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


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

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


#------------
sub showQuery {
#------------

    my ($reqstart, $reqend) = split(':', $arr{terms});
    # requested start and end terms.

    
    # Find all 10-12 subjects and add those with students enrolled into a hash.
    my  $sth = $dbh->prepare("select distinct eval.subjcode from eval
     left outer join subject on eval.subjcode = subject.subjsec
     where subject.grade = 10 or subject.grade = 11 or subject.grade = 12");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
    while ( my $subjsec = $sth->fetchrow ) {
	$enrolledsubjects{$subjsec} = 1;
    }


#    foreach my $subjsec ( sort keys %enrolledsubjects ) {
#	print qq{ $subjsec /\n};
#    }


    
    # Create a new user agent
    my $ua = LWP::UserAgent->new();
    $ua->agent("OpenAdmin");

    $count=1;
    mkQueryString($count); # Generate $output string

    # DEBUG Data Errors
    if ($debug){
	print qq{<div style="color:blue;">\n};
	print qq{<h1>DEBUG - Request sent to Sask Ed</h1>\n};
	my $temp = $output->value;
	$temp =~ s/</&lt;/g;
	$temp =~ s/>/&gt;/g;
	print qq{<pre>",$temp,"</pre></div><p></p>\n};
    }

    
    # Create the https post request 

    my $req = POST $url, [ XML=>$output->value ];

    $req->content_type('application/xml;charset="utf-8"');
    $req->authorization_basic($sds_userid, $sds_password); 

    # Issue the request and receive a response
    my $res = $ua->request($req);

    # Check the status of the response
    if ($res->is_success) {

	# For Debugging Data Errors
	if ($debug){
	    print qq{<h1>DEBUG - Sask Ed XML Response</h1>\n};
	    print qq{<div style="color:green;">\n};
	    my $temp = $res->content;
	    $temp =~ s/</&lt;/g;
	    $temp =~ s/>/&gt;/g;
	    print qq{<br>$temp</div>\n};
	}


	# Parse the response.
	my $parser = XML::LibXML->new();
    
	eval {$doc = $parser->parse_string($res->content)};
	if ($@){
	    print qq{Error: $@<br>\n};
	    print qq{<pre>Sask Ed Error:\n",$res->content,"</pre><br>\n};
	    print qq{</body></html>\n}; die;
	}

	$doc->setEncoding('UTF-8');
	$root = $doc->getDocumentElement;
	$root->setNamespace($xmlns,'sl',1);

	# find Message ID.
	$mastermsgid = $root->findvalue('//sl:SL_MsgId');
	$status = $root->findvalue('//sl:SL_Status/sl:SL_StatusCode');

	if ($status eq 'Errors' or $status eq 'Invalid'){ 
	    
	    $statusmsg = $root->findvalue('//sl:SL_Status/sl:SL_StatusMsg');
	    $errormsg = $root->findvalue('//sl:SL_Error/sl:SL_ErrorMsg');
	    # Print out error and exit
	    print qq{<h1 style="color:red;">Error: $statusmsg</h1>\n};
	    print qq{<h1 style="color:red;">Error: $errormsg</h1>\n};

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

	} elsif ($status eq 'Successful'){ 
	    # Done below instead.
	    # parseStudentCourseEnrollments();

	} else { # print warnings... ($status eq 'Warnings')
	    print qq{There were warnings...};
	}
    
    }  else { # Transfer Error!
	my $err = $res->status_line;
	print qq{<h1>Transfer Error: $err</h1>};
    }

    my ($course_ref,$coursedata_ref) = parseSCEbyClass();
    # $courseid:startdate:enddate = mode:marksource  mode is classroom or distance,marksource is school / blended.

# TEST VALUES    
#    my %courses = %$course_ref;
#    foreach my $subjsec ( sort keys %courses ) { print qq{ $subjsec / \n}; }

#    my %coursedata = %$coursedata_ref;
#    foreach my $key ( sort keys %coursedata ) { print qq{ KEY:$key Val:$coursedata{$key}<br>\n}; }

    
    
    my $track = $g_MTrackTermType{'12'}; # use grade 12's values
    my %termdates; # termdates{date} = term:start/end
    foreach my $term ( sort keys %{ $g_MTrackTerm{$track}} ) {
	my $startdate = $g_MTrackTerm{$track}{$term}{start};
	$termdates{$startdate} = qq{$term:start};

	my $enddate = $g_MTrackTerm{$track}{$term}{end};
	$termdates{$enddate} = qq{$term:end};
    }

    
    my %sort;  # control sorting order of the course_ref object.
    my %coursedata;
    
    # Check if subjsec... normally should be...
    my $sth1 = $dbh->prepare("select * from subject where subjsec = ?");
    
    foreach my $cl (keys %{$course_ref}) {
	my ($subj,$startdate,$enddate) = split ':',$cl;

	#Get Description, Grade, and Terms. (all fields)
	my $ref;
	$sth1->execute( $subj );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	$ref = $sth1->fetchrow_hashref;

	# Just in case a manually entered course....
	if ( not $ref ) { # a manually entered course
	    my ($tch,$code,$section, $sdate) = split('-', $subj);
	    my $subj = qq{$code-$section};
	    $sth1->execute( $subj );
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	    $ref = $sth1->fetchrow_hashref;
	}

	
	my %r = %$ref;
	$coursedata{$subj} = $ref;
	
	# Check term values against Sask Ed info.
	my $stermval = $termdates{$startdate};
	if ( not $stermval ) { 
	    print qq{<h3>Error: No matching start date for SDS $startdate for $r{description} ($subj)</h3>\n};
	}	    
	my ($sterm, $dud) = split(':', $stermval);
	
	my $etermval = $termdates{$enddate};
	if ( not $etermval ) { 
	    print qq{<h3>Error: No matching end date for SDS $startdate for $r{description} ($subj)</h3>\n};
	}
	my ($eterm, $dud) = split(':', $etermval);

	# $sort{"$sterm$eterm$r{grade}$r{description}$subj"} = $cl;
	$sort{"$r{grade}$r{description}$subj"} = $cl;

    }


    print qq{<div style="width:80%;border:1px solid black;padding:1em;text-align:left;">}; 

    print qq{<p>A <b style="color:red;">red value</b> in the
<b>classid</b> above the table shows that Sask Ed has stored a value
that is actually just a subject code, not a real subject-section. The
script has looked for a subject that matches this classid and used
that instead.</p>\n};

    print qq{<p>A <b style="color:red;">red value</b> in the <b>total</b>
at the bottom of the table for a class indicates that there is
difference of opinion between Sask Ed and the local
enrollment records. This means that you should run the synchronization
script for that subject-section to update enrollments for that
class. Currently OA will not remove an entire missing class
enrollment.</p>\n}; 

    print qq{</div>\n};
    print qq{<p style="margin:2em 0.5em;font-size:120%;font-weight:bold;">};
    print qq{[ <a href="#notreg">Courses that are NOT registered with Sask Ed</a> ]</p>\n};
    

    
    my $first = 1;
    my ($currgrade, $prevgrade, $currterm, $prevterm);
    my $coursecount = 0;
    
    # Course Main Loop
    foreach my $key (sort keys %sort ) {

	my $cl = $sort{$key};

	my ($count, $subjsec, $description);
	my ($subj,$startdate,$enddate) = split ':',$cl;

#	print qq{SUB:$subj Start:$startdate End:$enddate<br>\n};
	
	my $grade = ${coursedata}{$subj}{'grade'};
	my $sterm = ${coursedata}{$subj}{'startrptperiod'};
	my $eterm = ${coursedata}{$subj}{'endrptperiod'};


	# Check if subjsec... normally should be...
	my $sth1 = $dbh->prepare("select id, description from subject where subjsec = ?");
	$sth1->execute( $subj );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	my ($id, $description) = $sth1->fetchrow;
	
	if ($id) {
	    $subjsec = $subj;
	    
	} else { # no id found, look at subject only for match.
	    my $sth1 = $dbh->prepare("select id, description, subjsec from subject where subjcode = ?");
	    $sth1->execute( $subj );
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}

	    my ($id, $description, $sub) = $sth1->fetchrow;
	    if (not $id) {
		print qq{<div style="color:red;font-weight:bold;">};
		print qq{No Matching local Course found for $subj. Error!</div>\n};
		next; # CLASS; # jump to next class;
	    } else {
		$subjsec = $sub;
	    }
	}

	# remove from total count so ones left don't have kids enrolled.
	delete $enrolledsubjects{$subjsec};    

	
	# skip any terms not requested in start page
	if ( $eterm and ( $reqstart ne $sterm  or $reqend ne $eterm && $eterm ) ) {
#~~	    print qq{SKIP REQ:$reqstart-$reqend START:$sterm END:$eterm SUB:$subj<br><br>\n}; 
	    next;
	}

	# Needs to be after the skip for courses not in this term group.
	$prevgrade = $currgrade;
	$currgrade = $grade;


	# Print divider if change in grade or terms
	if ( $currgrade != $prevgrade ) { # or $currterm ne $prevterm ) {
	    print qq{<br clear="left"><hr><h3>GRADE $currgrade</h3><hr><br>\n\n};
	    $coursecount = 1;
	}

	
	my (%localstudents, %totalstudents, %slstudents);
	my $localenrollment;

	# Find local students for this course.
	my $sth1 = $dbh->prepare("select distinct studnum, count(id) from eval
          where subjcode = ? group by studnum");
	$sth1->execute($subjsec);
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}

	# loop through all local students for this course.
	while ( my ($sn, $count) = $sth1->fetchrow ) {

	    $localstudents{$sn} = $count;

	    my ($lastname, $firstname, $studnum, $provnum) = split(':', getStudentInfo($sn,0) );
	    $totalstudents{"$lastname:$firstname:$studnum:$provnum"} = 1;

	}


	my ($mode,$marksource) = split ':',$coursedata_ref->{$cl};
	my ($tmp, $termstart,$termend) = split ':',$cl;
	if ($tmp ne $subjsec) { $tmp = qq{<span style="color:red;"$tmp</span>}; }

	# Main Section for each grade level.
	print qq{<div style="float:left;padding:0.5em;">\n};
	
	print qq{<div style="font-size:130%;font-weight:bold;">};
	print qq{$description ($subjsec)</div>\n};

	print qq{<b>ClassId</b> $tmp&nbsp;&nbsp;<b>Mode</b> $mode&nbsp;&nbsp;};
	if ($marksource eq 'Blended') { 
	    $marksource .= qq{ [ <span style="color:red;font-size:130%;font-weight:800;">Dept Exams</span> ]}; 
	}
	print qq{<b>Mark Source</b> $marksource<br>};
	print qq{<b>Start</b> $termstart <b>End</b> $termend\n};
	print qq{<b>Terms</b> $sterm - $eterm <b>Grade</b> $grade\n};

	print qq{<table cellpadding="4" cellspacing="0" border="1">\n};
	print qq{<tr><th>Name (Studnum)</th><th>Prov<br>Number</th>};
	print qq{<th>Trms</th><th>Local<br>Enrol</th><th>SkLrn<br>Enrol</th>};
	print qq{</tr>\n};
	
	foreach my $pn ( @{ $course_ref->{$cl} } ) {

	    # Get student info for this provnum
	    my ($lastname, $firstname, $studnum, $provnum) = split ':', getStudentInfo(0,$pn);

	    $slstudents{$pn} = 1;
	    $totalstudents{"$lastname:$firstname:$studnum:$pn"} = 1;
	}


	my $localcount;
	my $provcount;
	my $count = 1; # simple student counter
	# counts the number of students that have values in local recs.

	foreach my $rec (sort keys %totalstudents) {
	    my ($lastname,$firstname, $studnum, $pn) = split(':',$rec);

	    $lastname =~ s/WD$/<span style="color:red;">WD<\/span>/;
	    
#	    print qq{TOTAL LN:$lastname FN:$firstname SN:$studnum PN:$pn<br>\n};

	    my $provenrol;
	    if ($slstudents{$pn}) { 
		$provenrol = 'Y';
		$provcount++;
	    } else { 
		$provenrol = qq{<span style="color:red;font-weight:bold;">N</span>};
	    }

	    my $localenrol;
	    if ($localstudents{$studnum}) { 
		$localenrol = 'Y';
		$localcount++;
	    } else { 
		$localenrol = qq{<span style="color:red;font-weight:bold;">N</span>};
	    }


	    print qq{<tr><td>$count.<b>$lastname</b>, $firstname ($studnum)</td>};
	    print qq{<td>$pn</td><td class="cn">$localstudents{$studnum}</td>};
	    print qq{<td class="cn">$localenrol</td>};
	    print qq{<td class="cn">$provenrol</td></tr>\n};
	    $count++;
	}

	my $newstyle;
	if ($provcount != $localcount) { 
	    $newstyle = 'style="color:red;background-color:#DDD;"'; 
	}
    
	print qq{<tr><th colspan="5" $newstyle>SaskEd Count: $provcount };
	print qq{Local Count: $localcount</th></tr>\n};
	print qq{</table></div>\n\n};

	$coursecount++;
	if ( $coursecount % 3 == 0 ) { print qq{<br clear="left">\n} }
	
    } # End of course Loop
    
    
    # Courses without registrations with Sask Ed.
    print qq{<br clear="left"><hr><a name="notreg"></a>\n};
    print qq{<h1>Courses <span style="font-size:150%;color:red;">NOT</span> registered with Sask Ed</h1>\n};
    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
    print qq{<tr><th>Course</th><th>Teacher</th><th>Grade</th><th>Terms</th>};
    print qq{<th>Enrollment</th></tr>\n};


    my %sort;  # control sorting order of the course_ref object.

    my $sth = $dbh->prepare("select * from subject where subjsec = ?");

    foreach my $subjsec (keys %enrolledsubjects) {
	
	$sth->execute( $subjsec );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	my $ref = $sth->fetchrow_hashref;
	$coursedata{$subjsec} = $ref;
	my %r = %$ref;
	$sort{"$r{startrptperiod}$r{endrptperiod}$r{grade}$r{description}"} = $subjsec;
    }

    
    my $sth1 = $dbh->prepare("select count(distinct studnum) from eval where subjcode = ?");
    my ($currsterm, $prevsterm, $curreterm, $preveterm, $currgrade, $prevgrade );
    
    # foreach my $subjsec (keys %enrolledsubjects) {
    foreach my $key (sort keys %sort ) {
	my $subjsec = $sort{$key};
    
	# Now get enrollment.
	$sth1->execute( $subjsec);
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	my $enrollment = $sth1->fetchrow;

	my %r = %{ $coursedata{$subjsec}};

	$prevsterm = $currsterm;
	$preveterm = $curreterm;
	
	$currsterm = $r{startrptperiod};
	$curreterm = $r{endrptperiod};

	$prevgrade = $currgrade;
	$currgrade = $r{grade};



	if ( $currsterm ne $prevsterm or $curreterm ne $preveterm ) { # print divider;
	    print qq{<tr><td style="background-color:#999;font-weight:bold;font-size:120%;" colspan="5">};
	    print qq{Terms $currsterm - $curreterm</td></tr>\n};
	}


	if ( $currgrade ne $prevgrade ) {
	    print qq{<tr><td style="background-color:#CCC;font-weight:bold;" colspan="5">};
	    print qq{Grade $currgrade</td></tr>\n};
	}

	
	print qq{<tr><td><b>$r{description}</b> ($subjsec)</td><td>$r{teacher}</td>};
	print qq{<td class="cn">$r{grade}</td><td>$r{startrptperiod} - $r{endrptperiod}</td>};
	print qq{<td class="cn">$enrollment</td></tr>\n};
    }

    print qq{</table>\n};

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

} # end of showQuery
    


# Functions =============================

#--------------
sub mkQueryString { # for Course Enrollments
#--------------
    
    my $grade = shift;
    my $provnum = shift;
    my $idcount = shift;
    # Date and schoolnumber are globals.

    # Create Writer Instance
    $output = new XML::Writer::String;
    my $datamode = 0;
    if ($debug){ $datamode = 1;} # pretty print xml output
    $wr = new XML::Writer(OUTPUT => $output, 
			  DATA_MODE => $datamode, 
			  DATA_INDENT => '2');

    # Set XML Header and write Root Element
    $wr->xmlDecl("UTF-8");
    $wr->startTag('SL_Message','xmlns' =>$xmlns, 
		  'xmlns:xsi' =>$xmlnsxsi, 
		  'xsi:schemaLocation' => $xsischemaLocation);

    $wr->startTag('SL_Request');
    mkSL_Header($currdate, $currtime, $schoolnumber,$idcount);

    $wr->startTag('SL_Query');

    $wr->startTag('QueryBySchool',
		  'RefId' => "$schoolnumber",
		  'ObjectName' => 'StudentCourseEnrollments',
		  'ScopeCode' => 'Current'
		  );

    $wr->dataElement('SchoolId',$schoolnumber); 

    my $sdate = $schoolstart;
    $sdate =~ s/-//g;
    my $edate = $schoolend;
    $edate =~ s/-//g;

    $wr->dataElement('FromDate', $sdate );
    $wr->dataElement('ToDate', $edate );
#    print qq{Start:$sdate End:$edate<br>\n};

    $wr->endTag('QueryBySchool');

    $wr->endTag('SL_Query');
    $wr->endTag('SL_Request');
    $wr->endTag('SL_Message');
    $wr->end();
    
}


#--------------------------------
sub parseStudentCourseEnrollments { # passed document root object.
#--------------------------------
    
    my @studinfo = $root->findnodes('//sl:StudentCourseEnrollments');

    my $count = 1;

    foreach my $student (@studinfo){
	my $provnum = $student->findvalue('sl:StudentIdentification/sl:DeptAssignedPersonId');
	my $birthdate = $student->findvalue('sl:StudentIdentification/sl:BirthDate');

	my $sth = $dbh->prepare("select lastname, firstname from student
           where provnum = '$provnum'");
	$sth->execute;
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	my ($lastname, $firstname) = $sth->fetchrow;

	print qq{<h1>$firstname $lastname ($provnum - $birthdate</h1>\n};
	print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
	print qq{<tr><th>Schoolyear</th><th>Termstart</th><th>Termend</th>};
	print qq{<th>ClassId</th><th>Mode</th><th>Mark Src</th></tr>\n};

	my @classes = $student->findnodes('sl:StudentSchoolClasses/sl:StudentTermClasses');
	foreach my $class (@classes) {
	    my $schoolyear = $class->findvalue('sl:TermInfo/@SchoolYear');
	    my $termstart = $class->findvalue('sl:TermInfo/sl:StartDate');
	    my $termend = $class->findvalue('sl:TermInfo/sl:EndDate');
	    my @sections = $class->findnodes('sl:StudentClass');
	    foreach my $section (@sections) {
		my $classid = $section->findvalue('sl:ClassId');
		my $mode = $section->find('sl:ModeOfInstruction/@Code');
		my $marksource = $section->findvalue('sl:MarkSource/@Code');
		print qq{<tr><td>$schoolyear</td><td>$termstart</td><td>$termend</td>\n};
		print qq{<td>$classid</td><td>$mode</td><td>$marksource</td></tr>\n};
	    }
	}
	print qq{</table>\n};
    }

} # End of Sub 


#------------------ 
sub parseSCEbyClass { # passed document root object.  
#------------------ 

# Parse SchoolCourseEnrollments, and return a reference to a courses
# hash containing refs to a list of provincial numbers.

    my @studinfo = $root->findnodes('//sl:StudentCourseEnrollments');

    my $count = 1;
    my (%courses, %coursedata);

    foreach my $student (@studinfo){
	my $provnum = $student->findvalue('sl:StudentIdentification/sl:DeptAssignedPersonId');
	# my $birthdate = $student->findvalue('sl:StudentIdentification/sl:BirthDate');

	my @classes = $student->findnodes('sl:StudentSchoolClasses/sl:StudentTermClasses');
	foreach my $class (@classes) {
	    #my $schoolyear = $class->findvalue('sl:TermInfo/@SchoolYear');
	    my $termstart = $class->findvalue('sl:TermInfo/sl:StartDate');
	    my $termend = $class->findvalue('sl:TermInfo/sl:EndDate');
	    my @sections = $class->findnodes('sl:StudentClass');
	    foreach my $section (@sections) {
		my $classid = $section->findvalue('sl:ClassId');

		if ( $classid =~ m/^[^0-9-]/ ) {
		    my ($tch,$code,$section, $sdate) = split('-', $classid);
		    $classid = qq{$code-$section};
		    # print "Non digits - $classid<br>\n";
		}
		
		my $mode = $section->find('sl:ModeOfInstruction/@Code');
		my $marksource = $section->findvalue('sl:MarkSource/@Code');
		$coursedata{"$classid:$termstart:$termend"} = "$mode:$marksource";
		if (not defined $courses{"$classid:$termstart:$termend"}) {
		    $courses{"$classid:$termstart:$termend"} = [ $provnum ];
		} else {
		    push @{$courses{"$classid:$termstart:$termend"}},$provnum;
		}
	    }
	} # End of StudentSchoolClasses

    } # End of student loop

    return \%courses, \%coursedata;

} # End of parseSCEbyCourse


#-----------------
sub getStudentInfo {
#-----------------

    my $sn = shift; # passed student number (studnum)
    my $pn = shift; # passed provincial number (provnum)

    my $sth;
    my ($lastname, $firstname, $studnum, $provnum);
    
    if ($pn) {
	$sth = $dbh->prepare("select lastname, firstname, studnum, provnum
         from student where provnum = ?");
	$sth->execute($pn);	
    } elsif ($sn) {
	$sth = $dbh->prepare("select lastname, firstname, studnum, provnum
         from student where studnum = ?");
	$sth->execute($sn);
    } else {
	return; # nothing passed.
    }

    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    ($lastname, $firstname,$studnum,$provnum) = $sth->fetchrow;


    if ( not $lastname ) {  # look them up in withdrawn student table.
	if ( $pn ) {
	    $sth = $dbh->prepare("select lastname, firstname,studnum,provnum
             from studentwd where provnum = ?");
	    $sth->execute($pn);
	} elsif ($sn) {
	    $sth = $dbh->prepare("select lastname, firstname,studnum,provnum
             from studentwd where studnum = ?");
	    $sth->execute($sn);
	}

	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	($lastname, $firstname,$studnum, $provnum) = $sth->fetchrow;

	$lastname = qq{$lastname WD};
	if ( not $lastname ) { 
	    $lastname = qq{Not Found}; 
	}
    }

    return "$lastname:$firstname:$studnum:$provnum";

} # end of getStudentInfo


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

    
    print qq{<div style="font-size:120%;font-weight:bold;">\n};
    print qq{Select a start term / end term combination</div>\n};

    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};

    print qq{<table cellspacing="0" cellpadding="3" border="1" style="text-align:center;">\n};
    print qq{<tr><th>Start Term</th><th>End Term</th><th>Select</th></tr>\n};

    # Show the Start/End terms for choosing subjsec's.
    my $sth1 = $dbh->prepare("select distinct startrptperiod, endrptperiod from subject
      where grade = 10 or grade = 11 or grade = 12
      group by startrptperiod, endrptperiod order by startrptperiod, endrptperiod");
    $sth1->execute;
    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr;}
    while ( my ( $startterm, $endterm ) = $sth1->fetchrow ) {
	print qq{<tr><td>$startterm</td><td>$endterm</td>};
	print qq{<td><input type="radio" name="terms" value="$startterm:$endterm"></td></tr>\n};
    }

    print qq{<tr><td colspan="3"><input type="submit" value="Submit Term">\n};
    print qq{ Chk <input type="checkbox" name="check" value="1">\n};
    print qq{ Debug <input type="checkbox" name="debug" value="1">\n};
    print qq{</td></tr>\n};
    print qq{</table></form></body></html>\n};

    exit;

}
