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

#  This file is part of Open Admin for Schools.

#  Open Admin for Schools is free software; you can redistribute it 
#  and/or modify it under the terms of the GNU General Public License
#  as published by the Free Software Foundation; either version 2 of 
#  the License, or (at your option) any later version.

# evaldelstud.pl - delete subject enrollments/gradebook records by student.

# NOTE: Gradebook (tests and scores) deletion is commented out! It will
# not erase these. This will not cause any problems since withdrawn
# students will not show up in gradebook...


my %lex = ('Student Course' => 'Student Course',
	   'Please search again' => 'Please search again',
	   'Main' => 'Main',
	   'Report Card' => 'Report Card',
	   'Last,First/Last/Initials/Studnum' => 'Last,First/Last/Initials/Studnum',
	   'Subject' => 'Subject',
	   'Withdraw' => 'Withdraw',
	   'No Student(s) Found' => 'No Student(s) Found',
	   'Withdraw from selected subjects' => 'Withdraw from selected subjects',
	   'No Course Enrollments' => 'No Course Enrollments',
	   'Search' => 'Search',
	   'Teacher' => 'Teacher',
	   'Student' => 'Student',
	   'Error' => 'Error',
	   'Enrollment' => 'Enrollment',
	   'Deleted' => 'Deleted',
	   'Withdraw' => 'Withdraw',
	   'View' => 'View',
	   'Yes' => 'Yes',

	   );

my $self = 'evaldelstud.pl';

use DBI;
use CGI;

eval require "../../etc/admin.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 $student = $arr{student};

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



# Print Html Head
my $title = qq{$lex{'Student Course'} $lex{View}/$lex{Withdraw} };
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>\n<body>\n};

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

print qq{<h1>$title</h1>\n};

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

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

} elsif ( $arr{page} == 2 ) {
    delete $arr{page};
    deleteEvalRecords(); 
}



#----------------
sub searchStudent {
#----------------

    # Setup the Search
    if ( $student =~ /\d+/ ) {  # we have a student number
	$studentnumber = $student;
	$sth = $dbh->prepare("select lastname, firstname, studnum 
          from studentall where studnum = ?");
	$sth->execute( $student );
    } else { # we have words hopefully with a comma
	($lastname,$firstname)  = split /\,/, $student;
	$firstname =~ s/^\s*//;
	$lastname =~ s/^\s*//;
	if ( $lastname and $firstname ) { # both entered.
	    $sth = $dbh->prepare("select lastname, firstname, studnum 
              from studentall where lastname = ? and firstname = ?");
	    $sth->execute( $lastname, $firstname );

	} elsif ($lastname and not $firstname){ # only lastname (no comma)
	    if (length($lastname) == 2){ # search by initials: fi, li.
		$fi = substr($lastname,0,1). '%'; $li = substr($lastname,1,1). '%';
		$sth = $dbh->prepare("select lastname, firstname, studnum 
                 from studentall where lastname $sql{like} ? and firstname $sql{like} ?");
		$sth->execute( $li, $fi );
	    } else {
		$sth = $dbh->prepare("select lastname, firstname, studnum 
                 from studentall where lastname = ? order by firstname");
		$sth->execute( $lastname );
	    }
	} else { # print an error....
	    print qq{<h1>$lex{'No Student(s) Found'}</h1>\n};
	    mkSearchError();
	}

    } # Last Else

    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    my $rows = $sth->rows;

    if ($rows < 1) { 
	print qq{<h1>$lex{'No Student(s) Found'}</h1>\n};
	mkSearchError();
    } 

    for ( 1..$rows ) {
	my ($ln,$fn,$sn) = $sth->fetchrow;
	push @students, qq{$ln:$fn:$sn}; 
    }

    my $sth1 = $dbh->prepare("select lastname, firstname from staff where userid = ?");

    
    foreach  my $s ( sort @students ) {
	my ( $lastname, $firstname, $studnum) = split(/:/, $s);
	
	print qq{<h1>$firstname $lastname ($studnum)</h1>\n};
	print qq{<form action="$self" method="post">\n};
	print qq{<input type="hidden" name="page" value="2">\n};
	print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
	print qq{<tr><th>$lex{Course}</th><th>$lex{Teacher}</th><th>Terms</th>\n};
	print qq{<th>$lex{Withdraw}</th>\n};

	my $sth = $dbh->prepare("select distinct e.subjcode, s.description, s.teacher, 
          s.startrptperiod, s.endrptperiod
          from eval as e, subject as s  where e.studnum = ? and e.subjcode = s.subjsec
          order by s.description");

	$sth->execute( $studnum );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	my $rows = $sth->rows;

	if ( $rows < 1 ){ # No eval records found!
	    print qq{<tr><td colspan="3"><b>$lex{'No Course Enrollments'}};
	    print qq{</b></td></tr>\n};
	    print qq{</table></form>\n};
	    next; # student
	} 

	# print Submit button
#	print qq{<tr><td colspan="3" class="cn">};
#	print qq{<input type="submit" value="$lex{'Withdraw from selected courses'}"></td></tr>\n};

	for ( 1..$rows ){ # Loop through all courses

	    my ($subjsec, $desc, $teacher, $startterm, $endterm) = $sth->fetchrow;

	    $sth1->execute( $teacher );
	    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	    my ($lastname, $firstname) = $sth1->fetchrow;

	    print qq{<tr><td>$desc ($subjsec)</td><td>$firstname $lastname ($teacher)</td>};
	    print qq{<td class="cn">$startterm-$endterm</td>\n};
	    print qq{<td class="cn"><input type="checkbox" name="$studnum:$subjsec" value="1"></td></tr>\n};

	}

	print qq{<tr><td colspan="4" class="cn">};
	print qq{<input type="submit" value="$lex{Yes}, $lex{Withdraw}"></td></tr>\n};
	print qq{</table></form><p></p>\n};


    } # end of student loop

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

    exit;

} # end of searchStudent;



#----------------
sub mkSearchError {
#----------------

    print qq{<div style="border:1px solid gray;padding:1em;margin:1em;width:400px;">\n};
    print qq{<b>$lex{'Student Course'} $lex{Enrollment} $lex{Search}</b><br>\n};

    print qq{<form action="$self" method="post" style="display:inline;">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="text" name="student" style="width:20em;">\n};
    print qq{<input type="submit" value="$lex{Search}"><br>\n};
    print qq{$lex{Student} ($lex{'Last,First/Last/Initials/Studnum'})\n};
    print qq{</form></div>\n};

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

    exit;

}


#--------------------
sub deleteEvalRecords { # Delete eval records and gradebook records
#--------------------

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

    # Loop through all passed values, and delete 
    foreach my $key ( keys %arr ){

	my ($studnum, $subjsec) = (split ':',$key);
	if ( $studnum and $subjsec ) {
	    my $sth = $dbh->prepare("delete from eval where studnum = ? and subjcode = ?");
	    $sth->execute($studnum, $subjsec);
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

	    print qq{<div>$lex{Enrollment} $lex{Deleted}</div>\n};

=head
	    # Find the gradebook tests and delete student from each in turn.
	    $sth = $dbh->prepare("select id from gbtest where subjsec = ?");
	    $sth->execute( $subjsec );
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    while ( my $testid = $sth->fetchrow ) {

		#print qq{TID: $testid<br>\n}; # remove along with other comments

		my $sth1 = $dbh->prepare("delete from gbscore
                 where studnum = ? and  testid = ?");
		$sth1->execute( $studnum, $testid );
		if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    }
=cut

	}
    }

    mkSearchError();

}
