#!/usr/bin/perl
#  Copyright 2001-2021 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 %lex = ('Main' => 'Main',
	   'Error' => 'Error',
	   'Grade' => 'Grade',
	   'Continue' => 'Continue',
	   'Course' => 'Course',
	   'Export' => 'Export',
	   'Evaluations' => 'Evaluations',
	   'Homeroom' => 'Homeroom',
	   'Course' => 'Course',
	   'or' => 'or',
	   'Term' => 'Term',
	   'Download' => 'Download',
	   'File' => 'File',
	   
	   
    );


use DBI;
use CGI;

my $self = "exporteval.pl";


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


# load report card configuration
my $sth = $dbh->prepare("select id, datavalue from conf_system where filename = 'repcard' 
   order by dataname");
$sth->execute;
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
while (	my ($id, $datavalue) = $sth->fetchrow ) {
    eval $datavalue;
    if ( $@ ) {
	print "$lex{Error}: $@<br>\n";
	die "$lex{Error}: $@\n";
    }
}


# print page header.
my $title = "$lex{Export} $lex{Course} $lex{Evaluations}";
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 style="margin:0 2em;">\n};

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



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

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

} elsif ( $arr{page} == 2 ) {
    delete $arr{page};
    exportRecords(); # to CSV.
}


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

    # Find all the grades
    my @grades;
    my $sth = $dbh->prepare("select distinct grade from student");
    $sth->execute;
    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
    while ( my $grade = $sth->fetchrow ) {
	push @grades, $grade;
    }

    # Find all the homerooms
    my @homerooms;
    $sth = $dbh->prepare("select distinct homeroom from student");
    $sth->execute;
    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
    while ( my $homeroom = $sth->fetchrow ) {
	push @homerooms, $homeroom;
    }

    # Find the courses
    $sth = $dbh->prepare("select subjsec, description from subject");
    $sth->execute;
    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
    while ( my ( $subjsec, $desc ) = $sth->fetchrow ) {

	# Skip Unwanted Subjects
	my ($subjcode, $dud) = split('-', $subjsec);

	if ( $r_SupressSubject{$subjsec} or $r_SupressSubject{$subjcode} or 
	     $r_AdditionalComments{$subjsec} or $r_AdditionalComments{$subjcode} ) {
	    next;
	}
	
	$subjects{"$desc ($subjsec)"} = $subjsec;
    }

    
    # Find the terms
    my @terms;
    $sth = $dbh->prepare("select distinct term from eval 
      where term is not NULL and term != '' order by term");
    $sth->execute;
    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
    while ( my  $trm = $sth->fetchrow ) {
	push @terms, $trm;
    }


    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="0" };
    print qq{style="border:1px solid gray;padding:0.4em;">\n};

    print qq{<tr><td colspan="2" class="bla">Select Courses by</td></tr>\n};
    
    # Grade
    print qq{<tr><td class="bla">$lex{Grade}</td>};
    print qq{<td><select name="grade"><option></option>\n};
    foreach my $grade ( sort { $a <=> $b} @grades ) {
	print qq{<option>$grade</option>};
    }
    print qq{</select></td></tr>\n};

    # OR
    print qq{<tr><td colspan="2" class="bla">$lex{or}</td></tr>\n};

    # Homeroom
    print qq{<tr><td class="bla">$lex{Homeroom}</td><td><select name="homeroom"><option></option>\n};
    foreach my $homeroom ( sort { $a <=> $b} @homerooms ) {
	print qq{<option>$homeroom</option>};
    }
    print qq{</select></td></tr>\n};

    # OR
    print qq{<tr><td colspan="2" class="bla">$lex{or}</td></tr>\n};
    
    # Course
    print qq{<tr><td class="bla">$lex{Course}</td><td><select name="subjsec"><option></option>\n};
    foreach my $desc ( sort keys %subjects ) {
	print qq{<option value="$subjects{$desc}">$desc</option>};
    }
    print qq{</select></td></tr>\n};

    # Divider
    print qq{<tr><td colspan="2" class="bla"><hr></td></tr>\n};
    

    # Get the Term
    print qq{<tr><td class="bra">$lex{Term}</td>\n};
    print qq{<td class="la"><select name="term"><option></option>};

    foreach my $trm ( @terms ) {
	print qq{<option value="$trm">$trm</option>\n};
    }
    print qq{</select></td></tr>\n};


    # Check next page
    print qq{<tr><td class="bra">Check Next Page</td>\n};
    print qq{<td><input type="checkbox" name="checked" value="1"></td></tr>\n};

    # Check Marks Only
    print qq{<tr><td class="bra">Check Marks Only</td>\n};
    print qq{<td><input type="checkbox" name="checkmark" value="1"></td></tr>\n};
    
    
    print qq{<tr><td></td><td class="la">\n};
    print qq{<input type="submit" value="$lex{Continue}"></td></tr>\n};
    print qq{</table></form>\n};

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

    exit;
}


#----------------
sub selectCourses {
#----------------

    # foreach my $key ( sort keys %arr ) { print qq{<div>K:$key V:$arr{$key}</div>\n}; }
    # Passed: grade, homeroom, subjsec,  term, checked

    my $checked;
    if ( $arr{checked} ) {
	$checked = qq{checked="checked"};
    }
    delete $arr{checked};

    my $checkmark = $arr{checkmark};
    delete $arr{checkmark};

    if ( $checkmark ) { $checked = ''; }

    my $term = $arr{term};
    delete $arr{term};
    
    
    # If grade or homeroom, get the students, and then their course enrollments to find courses.
    my %courses;
    if ( $arr{grade} ) {

	my $sth1 = $dbh->prepare("select distinct subjcode from eval where studnum = ?");
	
	# Get Students in this grade
	my $sth = $dbh->prepare("select studnum from student where grade = ?");
	$sth->execute( $arr{grade} );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	while ( my $studnum = $sth->fetchrow ) {

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

		# Skip Unwanted Courses
		my ($subjcode, $dud) = split('-', $subjsec);
		if (   $r_SupressSubject{$subjsec} or $r_SupressSubject{$subjcode} or 
		       $r_AdditionalComments{$subjsec} or $r_AdditionalComments{$subjcode} ) {
		    next; 
		}

		
		$courses{$subjsec} = 1;
	    }
	}


    } elsif ( $arr{homeroom} ) {
	

	my $sth1 = $dbh->prepare("select distinct subjcode from eval where studnum = ?");
	
	# Get Students in this homeroom
	my $sth = $dbh->prepare("select studnum from student where homeroom = ?");
	$sth->execute( $arr{homeroom} );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	while ( my $studnum = $sth->fetchrow ) {

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

		# Skip Unwanted Courses
		my ($subjcode, $dud) = split('-', $subjsec);
		if (   $r_SupressSubject{$subjsec} or $r_SupressSubject{$subjcode} or 
		       $r_AdditionalComments{$subjsec} or $r_AdditionalComments{$subjcode} ) {
		    next; 
		}
		
		$courses{$subjsec} = 1;
	    }
	}

    } elsif ( $arr{subjsec} ) {
	$courses{$arr{subjsec} } = 1;
    }
    # we now have %courses populated.

    
    # display courses and outcomes/objectives.
    my (%desc, %sort, %courserec );
    
    my $sth = $dbh->prepare("select * from subject where subjsec = ?");

    foreach my $subjsec ( keys %courses ) {

	# skip if member of %r_SupressSubject or AdditionalComments
	my ($tsubjcode, $dud) = split('-', $subjsec );

	if ( $r_SupressSubject{$tsubjcode} or $r_SupressSubject{$subjsec} or
	     $r_AdditionalComments{$tsubjcode} or $r_AdditionalComments{$subjsec} ) {
	    next; 
	}

	
	$sth->execute( $subjsec );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	my $ref = $sth->fetchrow_hashref;
	
	my %r = %$ref;

	my $desc = $r{description};

	$courserec{$subjsec} = $ref;
	
	$sort{"$desc$subjsec"} = $subjsec;
	$desc{$subjsec} = $desc;
    }

    # Start Form
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="2">\n};
    print qq{<input type="hidden" name="term" value="$term">\n};
    print qq{<input type="hidden" name="grade" value="$arr{grade}">\n};
    print qq{<input type="hidden" name="homeroom" value="$arr{homeroom}">\n};

    
    
    print qq{<input type="submit" value="$lex{Continue}">\n};
    
    print qq{<table cellspacing="0" cellpadding="3" border="0" };
    print qq{style="border:1px solid gray;padding:0.4em;">\n};
    print qq{<caption style="font-weight:bold;font-size:120%;text-align:left;">};
    print qq{Select Outcomes to Export</caption>\n};
    
    print qq{<tr><th>$lex{Course}</th><th>Outcomes</th></tr>\n};

    my $sth = $dbh->prepare("select  from subject where subjsec = ?");
    
    foreach my $key ( sort keys %sort ) {
	my $subjsec = $sort{$key};

	print qq{<tr><td>$desc{$subjsec} ($subjsec)</td><td>};

	my %r = %{ $courserec{$subjsec} };
	my %outcomes;
	if ( not $r{q1} ) { # no outcomes
	    $outcomes{q1} = 'Mark';
	} else { # loop through each one.
	    foreach my $idx (1..20) {
		my $key = 'q'. $idx;

		if ( $r{$key} ) {
		    $outcomes{$key} = $r{$key};
		}
	    }
	}

	foreach my $key ( sort keys %outcomes ) {
	    if ( $key eq 'q1' and $checkmark ) { 
		print qq{<input type="checkbox" name="$subjsec:$key" value="1" checked="checked">$outcomes{$key} / };
		
	    } else {
		print qq{<input type="checkbox" name="$subjsec:$key" value="1" $checked>$outcomes{$key} / };
	    }
	}

	print qq{</td></tr>\n};

    }

    print qq{</table>\n};
    print qq{<input type="submit" value="$lex{Continue}">\n};
    print qq{</form>\n};
    print qq{</body>\n};

    exit;

}



#----------------
sub exportRecords {
#----------------

    #foreach my $key ( sort keys %arr ) { print qq{<div>K:$key V:$arr{$key}</div>\n}; }
    # Passed values are in subjsec:q* format

    my $term = $arr{term};
    delete $arr{term};

    my $grade = $arr{grade};
    delete $arr{grade};

    my $homeroom = $arr{homeroom};
    delete $arr{homeroom};

    
    use Text::CSV_XS;
    my $csv = Text::CSV_XS->new( {binary => 1} );


    my %data;
    foreach my $key ( keys %arr ) {
	my ($subjsec,$field) = split(':', $key);
	$field =~ s/^q//; # strip leading 'q', leaving the number 1,2,3,
	$data{$subjsec}{$field} = 1;
    }

    # Only want to display the first field with a mark value.
    my $markonlymode = 1;
    foreach my $subjsec ( keys %data ) {
	if ( $data{$subjsec}{2} ) {
	    $markonlymode = 0;
	}
    }
    
    
=head    
    # Get Student numbers for the courses.
    my %students;
    my $sth = $dbh->prepare("select distinct studnum from eval where subjcode = ?");

    foreach my $subjsec ( keys %data ) {
	$sth->execute( $subjsec);
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	while ( my $studnum = $sth->fetchrow ) {
	    $students{$studnum} = 1;
	}
    }
=cut

    my @courses = sort keys %data;
    # Unambiguous ordering for courses.

    
    # Get Students and Names from homeroom/grade/course.
    my (%sort,%name);
    my $sth;

    if ( $grade ) {
	$sth = $dbh->prepare("select lastname, firstname, studnum from student where grade = ?");
	$sth->execute( $grade );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	while ( my ($lastname, $firstname, $studnum ) = $sth->fetchrow ) {
	    $sort{"$lastname$firstname$studnum"} = $studnum;
	    $name{$studnum} = "$lastname, $firstname";
	}
	
    } elsif ( $homeroom ) {
	$sth = $dbh->prepare("select lastname, firstname, studnum from student where homeroom = ?");
	$sth->execute( $homeroom );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	while ( my ($lastname, $firstname, $studnum ) = $sth->fetchrow ) {
	    $sort{"$lastname$firstname$studnum"} = $studnum;
	    $name{$studnum} = "$lastname, $firstname";
	}
    } 


    if ( not $grade and not $homeroom ) { # must be a course.

	my $sth = $dbh->prepare("select distinct studnum from eval where subjcode = ?");
	my $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");
	
	foreach my $subjsec ( keys %data ) {
	    $sth->execute( $subjsec);
	    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	    while ( my $studnum = $sth->fetchrow ) {

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

	    }
	}
    }

	    
    # We now have all data ready for export.
    # Open output file
    my $filename = "exportEval$$.csv";
    open (EX,">$filename") || die " $fileName";

    my $sth = $dbh->prepare("select * from subject where subjsec = ?");
    
    # Header Line
    my @line;
    push @line, "Name";
    foreach my $subjsec ( @courses ) {
	push @line, $subjsec; # course code first
	
	# Load that course
	$sth->execute( $subjsec );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	my $ref = $sth->fetchrow_hashref;
	my %r = %$ref;

	# Add outcomes to @line.
	if ( not $markonlymode ) {
	    foreach my $fld ( sort {$a <=> $b} keys %{ $data{$subjsec}} ) { # numeric sort
		$fld = 'q'. $fld; # add a 'q' back in for the subject table.
		my $outcome = $r{$fld};
		push @line, $outcome;
	    }
	}
    }
	

    if ( $csv->combine( @line ) ) {
	my $record = $csv->string;
	print EX $record, "\r\n";
	
    } else {
	my $err = $csv->error_input;
	print qq{Combine failed: $err\n\n};
    }
    # End of Header Line

    my $sth = $dbh->prepare("select * from eval where subjcode = ? and studnum = ? and term = ?");
    
    # Now loop through all the students
    foreach my $key ( sort keys %sort ) {
	my $studnum = $sort{$key};
	
	my @line;
	push @line, $name{$studnum};

	
	foreach my $subjsec ( @courses ) {
	
	    # Load that course
	    $sth->execute( $subjsec, $studnum, $term );
	    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	    my $ref = $sth->fetchrow_hashref;
	    my %r = %$ref;

	    if ( $markonlymode ) {
		push @line, $r{a1};
	    } else {
		push @line, $subjsec; # course code first
		# Add outcomes to @line.
		foreach my $fld ( sort {$a <=> $b} keys %{ $data{$subjsec}} ) { # numeric sort
		    $fld = 'a'. $fld; # add a 'a' back in for the eval table.
		    my $outcome = $r{$fld};
		    push @line, $outcome;
		}
	    }
	}


	if ( $csv->combine( @line ) ) {
	    my $record = $csv->string;
	    print EX $record, "\r\n";
	
	} else {
	    my $err = $csv->error_input;
	    print qq{Combine failed: $err\n\n};
	}
    }

    
    
    close EX;

    system("mv $filename $downloaddir");

    print qq{<h1><a href="$webdownloaddir/$filename">};
    print qq{$lex{Download} CSV $lex{File}</a></h1>\n};

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

    exit;
}
