#!/usr/bin/perl
#  Copyright 2001-2024 Leslie Richardson

# Check the etc/coursemap.conf to make sure it's entered correctly.

my %lex = ('Main' => 'Main',
	   'Report Card' => 'Report Card',
	   'Grade' => 'Grade',
	   'Continue' => 'Continue',
	   'Error' => 'Error',
    );	   


use DBI;
use CGI;

my $self = 'checkPrereg.pl';


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

# load Course Map, also
eval require "../../etc/coursemap.conf";
if ( $@ ) {
    print $lex{Error}. ": $@<br>\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);
$dbh->{mysql_enable_utf8} = 1;

# Print Page Header
my $title = qq{Check Course Prerequisites};
print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{ <link rel="stylesheet" href="$css" type="text/css">\n};
print qq{$chartype\n</head><body>};

print qq{[ <a href="$homepage">$lex{Main}</a> | \n};
print qq{<a href="$reppage">$lex{'Report Card'}</a> ]\n};
print qq{<h1>$title - Find Credit Errors</h1>\n};

print qq{<div style="margin:1em;">[ <a href="#nopre">Problem Courses without Prerequisites</a> ]</div>\n};

checkPrereq();




#---------------
sub checkPrereq {
#---------------

    # Load Course values needed
    # Reminder: crsmap{shortname} = coursecode;
    my %course; # all courses with prereq
    my %revmap = reverse %crsmap; # goes from code to shortname. %revmap{code} = shortname.
    my @otherfails; # list of record id for transcript to display below table

    
#    print qq{REV MAP\n};
#    foreach my $key ( sort keys %revmap ) {
#	print qq{K:$key VAL:$revmap{$key}\n};
#    }

    # Load Course title, grade for prereq courses
    my $sth1 = $dbh->prepare("select title,grade from sasked_courses where code = ?");
    foreach my $shortname ( sort keys %crsmap ) {
	my $code = $crsmap{$shortname};

	# Get Course Title and Grade
	$sth1->execute($code);
	if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
	my ($title,$grade) = $sth1->fetchrow;
	$course{$code}{title} = $title;
	$course{$code}{grade} = $grade;
    }

    
    # %prereq hash.  shortname => shortname
#    print qq{PreReq values\n};
#    foreach my $shortname ( sort keys %prereq ) {
#	print qq{K:$shortname $course{$shortname}{title} VAL:$prereq{$shortname}\n};
#    }	


=head        
    # print out coursemap values %crsmap
    print qq{<div>Staring Course Map</div>\n};
    foreach my $shortname ( sort keys %crsmap ) {
	my $code = $crsmap{$shortname};

	# Get Course Title and Grade
	$sth->execute($code);
	if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
	my ($title,$grade) = $sth->fetchrow;

	print qq{<div>$shortname - $code - $title ($grade)</div>\n};

   }
=cut

    # Get different course, not in prereq hash.
    # not needed, used above. my $sth1= $dbh->prepare("select title from sasked_courses where code = ?");
    my $sth2= $dbh->prepare("select * from sasked_completedcourses where provnum = ? and courseid = ?");
    
    # Get name, grade, and if withdrawn
    my $sth3= $dbh->prepare("select lastname, firstname, grade,program from studentall where provnum = ?");
    my $sth4= $dbh->prepare("select count(*) from studentwd where provnum = ?");

    # we have the id of the record
    my $sth5= $dbh->prepare("select * from sasked_completedcourses where id = ?");

    # Look for student name in transfer tables.
    my $sth6= $dbh->prepare("select * from transfer where provnum = ?");
    
    print qq{<table cellspacing="0" cellpadding="3" border="1">\n};
    print qq{<tr><th>Student Name</th><th>Course</th><th>Prerequisite</th><th>Satisfying Course(s)</th>};
    print qq{<th>School Year</th><th>Transcript<br>New Tab</th></tr>\n};

    
    # Get Problem courses; passing mark, no credit
    my $count = 1;
    $sth = $dbh->prepare("select * from sasked_completedcourses where finalmark >= 50 and creditsearned = 0
    order by schoolyear desc");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref; # transcript record

	# Get coursecode. Find prerequisite, if any.
	my $coursecode = $r{courseid};
	my $shortname = $revmap{$coursecode}; # code to shortname.
	my $prereq = $prereq{$shortname}; # shortname to prerequsites.
	
	if ( not $shortname or not $prereq ) { # not in revmap/crsmap
	    push @otherfails, $r{id}; # store id of record to display later (no prerequisites)
	    
	} else { # normal situation
	    
	    my $prereq = $prereq{$shortname};
	    # print qq{Course:$prereq needed for $shortname\n};
	    
	    if ( $prereq =~ m/or/ ) { # alternate course options
		my @prenames = split(/\s*or\s*/, $prereq);
		
		# Check to see if she/he has one of these courses; if so then display results to be fixed.
		my $passflag = 0; 
		foreach my $crsname ( @prenames ) {
		    my $crscode = $crsmap{ $crsname };
		    #		    print qq{REQ:$crsname ($crscode) for $shortname\n};
		    # Check if this course IS present and passed.
		    $sth2->execute($r{provnum}, $crscode);
		    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
		    my $cref = $sth2->fetchrow_hashref;
		    if ( $cref ) {
			my %c = %$cref;
			if ( $c{creditsearned} > 0 ) {
			    $passflag = $c{id};
#			    print qq{Passflag:$passflag $crsname<br>\n};
			    last;
			}
		    }
		    
		} # end of prereq course loop


		if ( $passflag > 0 ) { # they have at least one prereq course
			
		    # Student name,grade, program
		    $sth3->execute($r{provnum});
		    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
		    my ($ln,$fn,$gr, $program) = $sth3->fetchrow;
		    if ( $program eq '09' ) { # adult secondary, no prereq
#			print qq{<div>Skipping $fn $ln ($gr) - Adult Secondary</div>\n};
			next;
		    }
		    my $name = qq{$fn $ln ($gr)};
		    if ( not $ln ) { # look in transfer
			$sth6->execute($r{provnum});
			if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
			my ($ln,$fn) = $sth6->fetchrow;
			$name = qq{$fn $ln};
			if ( not $ln ) { $name = $r{provnum}; }
		    }
		    
			
		    # Withdrawn?
		    my $wd;
		    $sth4->execute($r{provnum});
		    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
		    if ( $wdcount = $sth4->fetchrow ) {
			$wd = qq{<span style="color:red;">WD</span>};
		    }

		    my $id = $passflag; # course that satisfies prereq
		    $sth5->execute($id);
		    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
		    my $cref = $sth5->fetchrow_hashref;
		    my %c = %$cref;

		    
		    print qq{<tr><td>$count. $name</td><td>$shortname ($r{schoolyear})</td><td>$prereq</td>\n};
		    print  qq{<td>$c{coursetitle} ($c{finalmark})</td><td>$c{schoolyear}</td>\n};

		    print qq{<td class="cn">};
		    print qq{<form action="../sasked/checkgrad_local.pl" target="_blank">\n};
		    print qq{<input type="hidden" name="page" value="2">\n};
		    print qq{<input type="hidden" name="provnum" value="$r{provnum}">\n};
		    print qq{<input type="submit" value="View"></form>\n};
		    print qq{</td></tr>\n};
		    $count++;

		    
		}

		
	    } elsif ( $prereq =~ m/and/ ) { # and condition
		my @prenames = split(/\s*and\s*/, $prereq);
		
		# Check to see if she/he has all of these courses
		my $failflag = 0;
		my @passcrs; # passed courses to fulfill prereq
		foreach my $crsname ( @prenames ) {
		    my $crscode = $crsmap{ $crsname };
		    $sth2->execute($r{provnum}, $crscode);
		    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
		    my $cref = $sth2->fetchrow_hashref;
		    my %t = $cref; # test course
		    if ( $t{creditsearned} ) {
			push @passcrs, $crsref;
		    } else {
			$failflag = 1;
		    }

		}

		if ( $failflag == 0 ) { # student has prereq
		
		    # Student name,grade
		    $sth3->execute($r{provnum});
		    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
		    my ($ln,$fn,$gr) = $sth3->fetchrow;
		    my $name = qq{$fn $ln ($gr)};
		    if ( not $ln ) { # look in transfer
			$sth6->execute($r{provnum});
			if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
			my ($ln,$fn) = $sth6->fetchrow;
			$name = qq{$fn $ln};
			if ( not $ln ) { $name = $r{provnum}; }
		    }
		    
		    # WD?
		    my $wd;
		    $sth4->execute($r{provnum});
		    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
		    if ( $wdcount = $sth4->fetchrow ) {
			$wd = qq{<span style="color:red;">WD</span>};
		    }

		    # We can use @passcrs to display passed courses, later.
		    print qq{<tr><td>$count. $name</td>};
		    print qq{<td>$shortname ($r{schoolyear})</td><td>$prereq</td>\n};
		    print qq{<td colspan="2">};
		    foreach my $ref ( @passcrs ) {
			print "REF:$ref<br>\n";
			my %p = %$ref;
			print  qq{$p{coursetitle}  ($p{finalmark})/ $p{schoolyear}<br>\n};
		    }
		    print qq{</td>\n<td class="cn">};

		    print qq{<form action="../sasked/checkgrad_local.pl" target="_blank">\n};
		    print qq{<input type="hidden" name="page" value="2">\n};
		    print qq{<input type="hidden" name="provnum" value="$r{provnum}">\n};
		    print qq{<input type="submit" value="View"></form>\n};
		    print qq{</td></tr>\n};
		    $count++;

		}

		
	    } else { # only a single course prereq

		my $crscode = $crsmap{ $prereq };

		# Check to see if we have any prerequisite done
		$sth2->execute($r{provnum}, $crscode);
		if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
		while ( my $cref = $sth2->fetchrow_hashref ) {
		    my %c = %$cref;

		    if ( $c{creditsearned} > 0) { # we have the prereq and it's passed.

			# Student name,grade
			$sth3->execute($r{provnum});
			if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
			my ($ln,$fn,$gr) = $sth3->fetchrow;
			my $name = qq{$fn $ln ($gr)};
			if ( not $ln ) { # look in transfer
			    $sth6->execute($r{provnum});
			    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
			    my ($ln,$fn) = $sth6->fetchrow;
			    $name = qq{$fn $ln};
			    if ( not $ln ) { $name = $r{provnum}; }
			}
		    
			# Withdrawn?
			my $wd;
			$sth4->execute($r{provnum});
			if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
			if ( $wdcount = $sth4->fetchrow ) {
			    $wd = qq{<span style="color:red;">WD</span>};
			}

			print qq{<tr><td>$count. $name</td><td>$shortname ($r{schoolyear})</td>};
			print qq{<td>$prereq</td>\n};
			print  qq{<td>$c{coursetitle} ($c{finalmark})</td><td>$c{schoolyear}</td>\n};

			print qq{<td class="cn">};
			print qq{<form action="../sasked/checkgrad_local.pl" target="_blank">\n};
			print qq{<input type="hidden" name="page" value="2">\n};
			print qq{<input type="hidden" name="provnum" value="$r{provnum}">\n};
			print qq{<input type="submit" value="View"></form>\n};
			print qq{</td></tr>\n};
			$count++;
			
			last;
		    }

		} # end of prereq course loop 
		
	    } # end of else; single prereq 
	    
	} # end of normal operation

    } # loop over all problem records (0 credit; passing mark)

    print qq{</table>\n};

    
    # Now display the 'other fails' - no prereq.
    print qq{<a name="nopre"></a>\n};
    print qq{<h2>Courses without Prerequisites - Missing Credits</h2>\n};
    print qq{<table cellspacing="0" cellpadding="3" border="1" style="margin:1em;">\n};
    print qq{<tr><th>Student Name</th><th>Course</th><th>Credit</th>};
    print qq{<th>Mark</th><th>School Year</th><th>Transcript<br>New Tab</th></tr>\n};

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

    # Get name, grade, and if withdrawn
    my $sth3= $dbh->prepare("select lastname, firstname, grade from studentall where provnum = ?");
    my $sth4= $dbh->prepare("select count(*) from studentwd where provnum = ?");

    foreach my $id ( @otherfails ) {
        $sth->execute($id);
	if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
	my $ref = $sth->fetchrow_hashref;
	my %r = %$ref;


	# Student name,grade
	$sth3->execute($r{provnum});
	if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
	my ($ln,$fn,$gr) = $sth3->fetchrow;
	my $name = qq{$fn $ln ($gr)};
	if ( not $ln ) { # look in transfer
	    $sth6->execute($r{provnum});
	    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
	    my ($ln,$fn) = $sth6->fetchrow;
	    $name = qq{$fn $ln};
	    if ( not $ln ) { $name = $r{provnum}; }
	}

	# Withdrawn?
	my $wd;
	$sth4->execute($r{provnum});
	if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
	if ( $wdcount = $sth4->fetchrow ) {
	    $wd = qq{<span style="color:red;">WD</span>};
	}


	print qq{<tr><td>$count. $name</td><td>$r{coursetitle} ($r{courseid})</td>};
	print  qq{<td class="cn">$r{creditsearned}</td><td>$r{finalmark}</td><td>$r{schoolyear}</td>\n};

	print qq{<td class="cn">};
	print qq{<form action="../sasked/checkgrad_local.pl" target="_blank">\n};
	print qq{<input type="hidden" name="page" value="2">\n};
	print qq{<input type="hidden" name="provnum" value="$r{provnum}">\n};
	print qq{<input type="submit" value="View"></form>\n};
	print qq{</td></tr>\n};
	$count++;

    }
    print qq{</table>\n};

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

    exit;

}
