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

#  This file is part of Open Admin for Schools.

# export.pl - export values to a CSV file format for download by teacher.
#   for a single subject only.
#  New Feature: compress all subjects into a zip for the teacher as a
#   backup.

my $tempdir = '/tmp'; 
# temporary working area when creating zip for complete backup.

my %lex = ('Export' => 'Export',
	   'Courses' => 'Courses',
	   'Main' => 'Main',
	   'Combine failed on input' => 'Combine failed on input',
	   'Test Weight' => 'Test Weight',
	   'Select Courses' => 'Select Courses',
	   'Download' => 'Download',
	   'Marks' => 'Marks',
	   'All' => 'All',
	   'Error' => 'Error',
	   'Please Log In' => 'Please Log In',
	   'Compressed Zip' => 'Compressed Zip',
	   'Continue' => 'Continue',

	   );


my $self = 'export.pl';

use DBI;
use CGI;
use CGI::Session;
use Text::CSV_XS;

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

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

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

=head
# 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);
if (not $logged_in){
    print $q->header( -charset, $charset );
    print $lex{'Please Log In'}. "!<br>\n";
    exit;
}
my $subjsec = $session->param('subjsec');
=cut

my $userid = $ENV{'REMOTE_USER'};


my ($sec, $min, $hour, $mday, $mon, $year, $wday, 
 $yday, $iddst) = localtime(time);
$mon++;
$wday++;
$year = $year + 1900;
my $currsdate = "$year-$mon-$mday";
my $currdate = "$dow[$wday], $month[$mon] $mday, $year";

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


# Get the courses.
my (%courses, @courses);
$sth = $dbh->prepare("select description,smdesc,subjsec from subject 
		     where teacher = ? order by description");
$sth->execute( $userid );
if ($DBI::errstr) { print "$DBI::errstr"; die $DBI::errstr; }
while ( my $ref = $sth->fetchrow_hashref ) {
    push @courses, $ref->{subjsec};
    $courses{$ref->{subjsec}} = $ref;
}

=head
# Get the Subject Information, for current subject from session.
$sth = $dbh->prepare("select * from subject where subjsec = ?");
$sth->execute( $subjsec );
if ($DBI::errstr) { print "$DBI::errstr"; die $DBI::errstr; }
my $ref = $sth->fetchrow_hashref;
my %c = %$ref;
my $userid = $c{teacher}; # teacher userid
=cut


# Print Page Header
print qq{$doctype\n<html><head><title>$lex{Export} $lex{Marks}};
print qq{</title>\n<link rel="stylesheet" href="$tchcss" type="text/css">
$chartype\n</head><body style="padding:1em;">\n};

my $title = qq{$lex{Export} $lex{Courses} -> Spreadsheet};
print qq{<div>[ <a href="$tchpage">$lex{Main}</a> | <a href="gbmain.pl">GB Main</a> ]</div>\n};
print qq{<h1>$title</h1>\n};


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

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


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

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

    print qq{<h3 style="margin-bottom:0em;">$lex{'Select Courses'}</h3>\n};
    
    foreach my $subjsec ( @courses ) {
	my %c = %{ $courses{$subjsec}};
	print qq{<div><input type="checkbox" name="$subjsec" value="1">};
	print qq{$c{description} $subjsec</div>\n};
    }
    print qq{<input type="submit" value="$lex{Continue}">\n};    
    print qq{</form>\n};

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

} # end of showStartPage


#---------------
sub createExport {
#---------------

    # foreach my $key ( sort keys %arr ) { print qq{K:$key VAL:$arr{$key}<br>\n}; }
    # passed: courses (subjsec) in the keys
    

    # In case of no subject selected, halt here.
    if ( not %arr ){
	print qq{<h3>No Courses Selected</h3></body></html>\n};
	exit;
    }


    # Create a folder to do the zip in.
    my $tempdir = "tempzip$$";
    my $zipfile = "grades$currsdate-$$.zip";
    mkdir $tempdir;

    my $sth = $dbh->prepare("select smdesc,description from subject where subjsec = ?");
    
    foreach my $subjsec ( sort keys %arr ){
	$sth->execute( $subjsec );
	if ($DBI::errstr) { print "$DBI::errstr"; die $DBI::errstr; }
	my ($smdesc,$desc) = $sth->fetchrow;
	if ( not $smdesc ) {
	    $smdesc = $desc;
	}
	$desc =~ s/\s//g; # strip any spaces

	my $filename = "$tempdir/$desc-$subjsec.csv";
	exportCourse($subjsec,$filename); # write to the exported file.

    }

    system("zip $zipfile $tempdir/* >/dev/null");
    system("mv $zipfile $tchdownloaddir");
#    system("rm -f $tempdir/*");
#    rmdir $tempdir;

    print qq{<p>[ <a href="$tchwebdownloaddir/$zipfile">};
    print qq{<span style="font-size:120%;font-weight:bold;">$lex{Download} $lex{Marks} </span>\n};
    print qq{</a> ]</p>\n};
    print qq{</body></html>\n};

    exit;
}

    


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

#----------------
sub exportCourse { # build export file for single course
#----------------
    my ($subjsec,$filename) = @_;  # passed subjsec, filename
    my @test;
    my @studname;

    # Get the Course Information
    my $sth = $dbh->prepare("select * from subject where subjsec = ?");
    $sth->execute( $subjsec );;
    if ($DBI::errstr) { print qq{$DBI::errstr}; die $DBI::errstr; }
    my $ref = $sth->fetchrow_hashref;
    my %c = %$ref;

    # Find the enrollments for this course and read them into @studnum array
    my $sth = $dbh->prepare("select distinct e.studnum, s.lastname, s.firstname from eval e
			    left outer join studentall s on s.studnum = e.studnum 
			    where e.subjcode = ? order by s.lastname, s.firstname");

    $sth->execute( $subjsec );
    if ($DBI::errstr) { print qq{$DBI::errstr}; die;}
    while ( my ($studnum, $lastname,$firstname) = $sth->fetchrow ) {
	push @studname,"$lastname, $firstname :$studnum";
    }
    # End of adding student names.


    # lookup tests for this subjsec
    $sth = $dbh->prepare("select name, id, score, weight from gbtest 
			 where subjsec = ? order by tdate");
    $sth->execute( $subjsec );
    if ($DBI::errstr) { print "$DBI::errstr"; die;}
    while (my ($name,$id,$score,$weight) = $sth->fetchrow){
	push @test, "$name:$id:$score";
	$weight{$id} = "$weight"; # used for calculating averages.
    }
    # Now done putting in the tests.

    # Open File;
    open (EX,">", $filename) || die "Can't open Export file $fileName";

   
    # print the test names
    my @names;
    push @names,'Tests';
    foreach my $test (@test){
	my ($name,$id,$score) = split(':',$test);
	push @names,$name;
    }

    if ($csv->combine(@names)) {
	my $record = $csv->string;
	print EX $record, "\r\n";
    } else {
	my $err = $csv->error_input;
	print "Combine failed on input: ",$err,":\n\n";
    }
    # End of Test Names


    # Print the test max scores
    my @scores;
    push @scores,'Max Scores';
    foreach my $test (@test){
	my ($name,$id,$score) = split(/:/,$test);
	push @scores,$score;
    }

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


    # Print the students and their marks;
    foreach my $studname (@studname) {
	my ($sname,$studnum) = split(':', $studname);
	my @labels;
	push @labels,qq{$sname ($studnum)};
	
	foreach my $test (@test){
	    my ($sname,$studnum) = split(':',$studname);
	    my ($tname,$id,$maxscore) = split(':',$test);

	    $sth = $dbh->prepare("select score from gbscore 
				 where testid = ? and studnum = ?");
	    $sth->execute( $id, $studnum );
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

	    # Testing Code for errors; there shouldn't be more than 1 record
	    $row = $sth->rows;
	    if ($row > 1){ 
		print qq{</table><b>Error: More than 1 score for this test for:};
		print qq{$studname for test $test!</b></body></html>};
                exit;
	    }

	    my $score = $sth->fetchrow;
	    push @labels, $score;
	} # End of test loop
   
	if ($csv->combine(@labels)) {
	    my $record = $csv->string;
	    print EX $record, "\r\n";
	} else {
	    my $err = $csv->error_input;
	    print $lex{'Combine failed on input'}. ": ",$err,":\n\n";
	}
    }
    # End of printing all Student Marks

    my @weights;

    # Print the Test Weighting Row
    push @weights, $lex{'Test Weight'};
    foreach my $test (@test){
	my ($name,$id,$score) = split(':',$test);
	push @weights,$weight{$id};
    }

    if ($csv->combine(@weights)) {
	my $record = $csv->string;
	print EX $record, "\r\n";
    } else {
	my $err = $csv->error_input;
	print $lex{'Combine failed on input'}. ": ",$err,":\n\n";
    }

    close EX;

    return;

}
