#!/usr/bin/perl
#  Copyright 2001-2022 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.

my $self = "gbSearch.pl";

my %lex = ('Gradebook Search' => 'Gradebook Search',
	   'Continue' => 'Continue',
	   'Main' => 'Main',
	   'GB Main' => 'GB Main',
	   'Search For' => 'Search For',
	   'Blank=All' => 'Blank=All',
	   'Scores' => 'Scores',
	   'Averages' => 'Averages',
	   'Search In' => 'Search In',
	   'All' => 'All',
	   'Terms' => 'Terms',
	   'Sort By' => 'Sort By',
	   'Student' => 'Student',
	   'Please Log In' => 'Please Log In',
	   'No Search' => 'No Search',
	   'Error' => 'Error',
	   'Course' => 'Course',
	   'Item' => 'Item',
	   'Score' => 'Score',
	   'Value' => 'Value',
	   'Average' => 'Average',
	   'No Records Found' => 'No Records Found',
	   'Search Again' => 'Search Again',
	   'and' => 'and',
	   'Courses' => 'Courses',

	   );


# Characters NOT to search for in score field entries.
my $searchclass = '^0-9,.';

use DBI;
use CGI;
use CGI::Session;
use Number::Format qw{ round };

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

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

my $q = CGI->new;

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


# Get Session Information...
my $session = new CGI::Session("driver:mysql;serializer:FreezeThaw",
 undef,{Handle => $dbh}) or die CGI::Session->errstr;

my $logged_in = $session->param(logged_in); # checked below page header..
my $userid = $session->param('userid');
my $subjsec = $session->param('subjsec');

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

my ($sec, $min, $hour, $mday, $mon, $year, $wday, 
 $yday, $iddst) = localtime(time);
$year = $year + 1900;
$mon++;
$wday++;
# Now using global values for month and dow.
my $currdate = "$dow[$wday], $month[$mon] $mday, $year";


# print page header
my $title = $lex{'Gradebook Search'};
print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$tchcss" type="text/css"></head>};
print qq{<body style="margin:1em;">\n};

print qq{[ <a href="$tchpage">$lex{Main}</a> |\n};
print qq{<a href="gbmain.pl">$lex{'GB Main'}</a> \n};
if ( %arr ) {
    print qq{| <a href="$self">$lex{'Search Again'}</a> \n};
}
print qq{ ]\n};

if ( not $logged_in ) {
    print qq{<h1>$lex{'Please Log In'}</h1>\n};
    print qq{</body></html>\n};
    exit;
}


# Select courses to print.
if ( not $arr{searchType} ) { 
    showStartPage();
}

# get input values and leave subjsec's in @arr.  
my ($averageGtSearch, $averageLtSearch, $scoreGtSearch, $scoreLtSearch, $searchType,
    $sortby, $textSearch);

if ( defined $arr{averageGtSearch} ) {
    $averageGtSearch =  $arr{averageGtSearch};
    delete $arr{averageGtSearch};
}
if ( defined $arr{averageLtSearch} ) {
    $averageLtSearch = $arr{averageLtSearch};
    delete $arr{averageLtSearch};
}
if ( defined $arr{scoreGtSearch} ) {
    $scoreGtSearch = $arr{scoreGtSearch};
    delete $arr{scoreGtSearch};
}
if ( defined $arr{scoreLtSearch} ) {
    $scoreLtSearch = $arr{scoreLtSearch};
    delete $arr{scoreLtSearch};
}
if ( defined $arr{searchType} ) {
    $searchType = $arr{searchType};
    delete $arr{searchType};
}

if ( defined $arr{sortby} ) {
    $sortby = $arr{sortby};
    delete $arr{sortby};
}
if ( defined $arr{textSearch} ) {
    $textSearch = $arr{textSearch};
    delete $arr{textSearch};
}

# Define termsearch for term selection.
my $termsearch;
# if ( $arr{terms} ne $lex{All} ) { # we have a term selection.
#    my $startdate = $TRMstart{ $arr{terms} };
#    my $enddate = TRMend{ $arr{terms} };
#    delete $arr{terms};
#    $termsearch = "and to_days(tdate) >= to_days('$startdate') ". 
#	"and to_days(tdate) <= to_days('$enddate')";
#}

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

if ( $searchType eq 'text' ) {
    if ( not %arr ) {
	print qq{<h3>No Courses Selected</h3>\n};
	print qq{</body></html>\n};
	exit;
    }
    searchText( $textSearch, $sortby, \%arr, $termsearch );
    # termsearch is now undefined. no search for this.
	

} elsif ( $searchType eq 'scores' ) {
    
    if ( not %arr ) {
	print qq{<h3>No Courses Selected</h3>\n};
	print qq{</body></html>\n};
	exit;
    }
    searchScores( $scoreLtSearch, $scoreGtSearch, $sortby, \%arr, $termsearch );

} elsif ( $searchType eq 'averages' ) {

    if ( not %arr ) {
	print qq{<h3>No Courses Selected</h3>\n};
	print qq{</body></html>\n};
	exit;
    }

    searchAverages( $averageLtSearch, $averageGtSearch, $sortby, \%arr );

} else {
    print qq{<h1>$lex{'No Search'}</h1>\n};
    print qq{</body></html>\n};
}


#---------------
sub searchText {
#---------------

    # print qq{SUB:searchText<br>\n};
    # foreach my $key ( sort keys %arr ) { print qq{K:$key Val:$arr{$key}<br>\n} };
    # All courses passed as key values.
    
    # Passed %arr ref; also need selects and sortorder;
    my ( $textSearch, $sortorder, $subj_ref, $tsearch ) = @_;
#    print qq{TS:$textSearch SORT:$sortorder CRS:$subj_ref  SRCH:$tsearch<br>\n};
    
    my $sth = $dbh->prepare("select description from subject where subjsec = ?");
    my $sth1 = $dbh->prepare("select id, description,tdate from gbtest 
			     where subjsec = ? $tsearch order by tdate");
    my $sth2 = $dbh->prepare("select id, score, studnum from gbscore where testid = ? ");
    my $sth3 = $dbh->prepare("select lastname, firstname from studentall where studnum = ? ");

    print qq{<p></p><table cellpadding="4" cellspacing="0" border="1">\n};
    print qq{<tr><th>$lex{Course}</th><th>$lex{Student}</th><th>$lex{Item}</th>\n};
    print qq{<th>$lex{Score}</th></tr>\n};
    my $first = 1; # flag to see if any results...

    foreach my $subjsec (keys %{$subj_ref} ) {

	# Get course name...
	$sth->execute( $subjsec );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}     
	my $description = $sth->fetchrow;

	# Now loop through assessments...
	$sth1->execute( $subjsec );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}     
	while ( my ($testid, $testdesc, $tdate ) = $sth1->fetchrow ) {
	    
	    # Now loop through scores
	    $sth2->execute( $testid );
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}     
	    while ( my ( $id, $score, $studnum ) = $sth2->fetchrow ) {

		if ( ( $textSearch and $score eq $textSearch ) or 
		     ( not $textSearch and  $score =~ /[$searchclass]/ )) {

		    # Get Student Name
		    $sth3->execute( $studnum );
		    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}     
		    my ($lastname, $firstname ) = $sth3->fetchrow;
		    $first = 0;
		    print qq{<tr><td>$description</td><td>$firstname $lastname ($studnum)</td>\n};
		    print qq{<td>$testdesc ($tdate)</td><td>$score</td></tr>\n};
		}
	    }
	}

    }

    if ( $first ) { 
	print qq{<tr><td colspan="4" class="cn">};
	print qq{$lex{'No Records Found'}</td></tr>\n}; 
    }

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

}


#-----------------
sub searchAverages {
#-----------------

    eval require "./libgbreport.pl";
    if ( $@ ) {
	print qq{<br>$lex{Error}: $@<br>\n};
	print qq{</body></html>\n};    
	die $lex{Error}. ": $@\n";
    }

    # Passed %arr ref; also need selects and sortorder;
    my ( $maxAvg, $minAvg, $sortorder, $subj_ref ) = @_;
    if ( not $minAvg ) { $minAvg = 0; }
    if ( not $maxAvg ) { $maxAvg = 200; } # 200% should be safe...

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

    my $sth3 = $dbh->prepare("select lastname, firstname from studentall where studnum = ? ");

    print qq{<p></p><table cellpadding="4" cellspacing="0" border="1">\n};
    print qq{<tr><th>$lex{Course}</th><th>$lex{Student}</th><th>$lex{Average}</th></tr>\n};
    my $first = 1; # flag to see if any results...

    foreach my $subjsec (keys %{$subj_ref} ) {

	# Get course name...
	$sth->execute( $subjsec );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}     
	my $description = $sth->fetchrow;

	# Get Students
	$sth1->execute( $subjsec );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}     
	while ( my $studnum = $sth1->fetchrow ) {

	    my $avg = calcStudentAverage( $studnum, $subjsec, 2 );
#	    print "SN:$studnum CRS:$subjsec AVG:$avg<br>\n";
	    
	    if ( ( $avg >= $minAvg ) and ( $avg <= $maxAvg ) ) {

		# Get Student Name
		$sth3->execute( $studnum );
		if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}     
		my ($lastname, $firstname ) = $sth3->fetchrow;

		$first = 0;

		print qq{<tr><td>$description</td><td>$firstname $lastname ($studnum)</td>\n};
		print qq{<td class="bla">}. round( $avg, 2 );
		print qq{%</td></tr>\n};
	    }
	}
    }

    if ( $first ) { 
	print qq{<tr><td colspan="3" class="cn">};
	print qq{$lex{'No Records Found'}</td></tr>\n}; 
    }
    print qq{</table>\n};
    exit;

}


#---------------
sub searchScores {
#---------------

    # Passed %arr ref; also need selects and sortorder;
    my ( $maxScore, $minScore, $sortorder, $subj_ref, $tsearch ) = @_;
    if ( not $minScore ) { $minScore = 0; }
    if ( not $maxScore ) { $maxScore = 200; } # 200% should be safe...

    my $sth = $dbh->prepare("select description from subject where subjsec = ?");
    my $sth1 = $dbh->prepare("select * from gbtest where subjsec = ? $tsearch order by tdate");
    my $sth2 = $dbh->prepare("select id, score, studnum from gbscore where testid = ? ");
    my $sth3 = $dbh->prepare("select lastname, firstname from studentall where studnum = ? ");


    print qq{<p></p><table cellpadding="4" cellspacing="0" border="1">\n};
    print qq{<tr><th>$lex{Course}</th><th>$lex{Student}</th><th>$lex{Item}</th>\n};
    print qq{<th>$lex{Score}</th></tr>\n};
    my $first = 1; # flag to see if any results...

    foreach my $subjsec (keys %{$subj_ref} ) {

	# Get course name...
	$sth->execute( $subjsec );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}     
	my $description = $sth->fetchrow;

	# Now loop through assessments...
	$sth1->execute( $subjsec );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}     
	while ( my $ref = $sth1->fetchrow_hashref ) { # get tests
	    my %t = %$ref;
	    
	    # while ( my ($testid, $testdesc, $maxscore, $tdate ) = $sth1->fetchrow ) {
	    if ( not defined $t{score} ) { next; } #skip missing score tests (shouldn't be any).
	    # score here in gbtest is really maxscore

	    
	    # Now loop through scores
	    $sth2->execute( $t{id} ); # testid
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}     
	    while ( my ( $id, $score, $studnum ) = $sth2->fetchrow ) {
		if ( $score =~ /[$searchclass]/ ) { next; } # skip if text...

		my $avg = $score / $t{score} * 100; # t{score} is maxscore

		if ( ( $avg >= $minScore ) and ( $avg <= $maxScore ) ) { # if it fits

		    # Get Student Name
		    $sth3->execute( $studnum );
		    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}     
		    my ($lastname, $firstname ) = $sth3->fetchrow;
		    $first = 0;
		    print qq{<tr><td>$description</td><td>$firstname $lastname ($studnum)</td>\n};
		    print qq{<td>$t{name}/$t{description} ($t{tdate})</td>};
		    print qq{<td class="bla">}. round( $avg, 2);
		    print qq{% ($score/$t{score} )</td></tr>\n};
		}
	    }
	}

    }

    if ( $first ) { 
	print qq{<tr><td colspan="4" class="cn">};
	print qq{$lex{'No Records Found'}</td></tr>\n}; 
    }
    print qq{</table>\n};

    exit;


}


#----------------
sub showStartPage { # Get search values...
#----------------

    # Note: assume that userid and subjsec is already loaded from Session.
    my $checkall;
    if ( $arr{checkall} ) {
	$checkall = qq{checked="checked"};
	delete $arr{checkall};
    }

    my %terms;
    # Now find the subjects and store the subjsec, desc for them 
    $sth = $dbh->prepare("select subjsec, description, startrptperiod, endrptperiod from subject
     where teacher = ? order by description");
    $sth->execute( $userid );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    while (my ( $subjsec,$desc, $sterm, $eterm ) = $sth->fetchrow){
	for my $t ( $sterm..$eterm ) { $terms{$t} = 1; } # set term values.
	$desc =~ s/://g;
	push @subjsec, "$subjsec:$desc";
    }

    print qq{<h1>$lex{'Gradebook Search'}</h1>\n};
    
    # Check All courses.
    print qq{<form action="$self" method="post" style="margin-bottom:1em;">\n};
    print qq{<input type="hidden" name="checkall" value="1">\n};
    print qq{<input type="submit" value="Check All Courses"></form>\n};

    

    print qq{<form action="$self" method="post">\n};

    print qq{<table cellpadding="4" cellspacing="0" border="0" style="border:1px solid gray;">\n};

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

    # SEARCH FOR section
    print qq{<tr><td class="bla">$lex{'Search For'}</td>\n};
    print qq{<td class="la"><input type="radio" name="searchType" value="text" checked> };
    print qq{$lex{Score} $lex{Value}\n};
    print qq{<input type="text" name="textSearch" size="10"> $lex{'Blank=All'}</td></tr>\n};


    print qq{<tr><td></td>\n};
    print qq{<td><input type="radio" name="searchType" value="scores"> $lex{Scores}};
    print qq{&nbsp;&nbsp;>=\n};
    print qq{<input type="text" name="scoreGtSearch" size="4"> % \n};
    print qq{$lex{and} <= <input type="text" name="scoreLtSearch" size="4"> %\n};
    print qq{</td></tr>\n};

    print qq{<tr><td></td>\n};
    print qq{<td><input type="radio" name="searchType" value="averages"> };
    print qq{$lex{Averages}\n};
    print qq{ >= <input type="text" name="averageGtSearch" size="4"> %\n};
    print qq{$lex{and} <= <input type="text" name="averageLtSearch" size="4"> % \n};
    print qq{</td></tr>\n};

    # now the SEARCH IN section
    print qq{<tr><td class="bla" colspan="2">$lex{'Search In'} \n};
    print qq{$lex{Courses}</td></tr>\n};

    foreach my $subj ( @subjsec ) {
	my ($subjsec, $desc) = split(':',$subj);

	print qq{<tr><td class="la" colspan="2"><input type="checkbox" name="$subjsec" };
	print qq{value="1" $checkall> $desc ($subjsec)</td></tr>\n};
    }
    
    print qq{<tr><td colspan="3" class="la">\n};
    print qq{<input type="submit" value="$lex{Continue} ">};
    print qq{</td></tr>\n</table></form></body></html>\n};

    exit;

} # End of showStartPage
