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

#  This file is part of Open Admin for Schools.


my %lex = ('Main' => 'Main',
	   'Error' => 'Error',
	   'Export' => 'Export',
	   'Error Reading Record' => 'Error Reading Record',
	   'The file must be a .csv file!' => 'The file must be a .csv file!',
	   'Maximum File Upload size exceeded!' => 'Maximum File Upload size exceeded!',
	   'Cannot open file' => 'Cannot open file',
	   'Continue' => 'Continue',
	   'Complete' => 'Complete',

	   );

my $self = 'mssUploadTranscript.pl';

my $maxbufcount = 800; # 500k max; change as required
my $maxrecorddisplay = 5;

use DBI;
use CGI;
use Text::CSV_XS;
use Fcntl qw(:DEFAULT :flock);

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);


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

# Print Page Header
my $title = qq{Upload Student CSV - MSS MyBlueprint TRN (Transcript)};
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>\n};

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


if ( not $arr{page} ) {
    showStartPage();
    
} elsif ( $arr{page} == 1 ) {
    delete $arr{page};
    importStudents();
}
    

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

    print qq{<form action="$self" method="post"  enctype="multipart/form-data">\n};
    print qq{<input type="file" name="filename">\n};
    print qq{<input type="hidden" name="page" value="1"><br>\n};

    print qq{<input type="submit" style="margin:0.5em 0;" };
    print qq{value="Upload Transcript CSV file">\n};
    print qq{</form></body></html>\n};

    exit;

}


#-----------------
sub importStudents {
#-----------------

    my $file = $q->param("filename");
    my $name; my $ext; 
    my $filename = $file;  # fileName is output filename, file is input.

    if ( $file ) {

	$filename =~ s!^.*(\\|\/)!!; 
	$filename = lc($filename);
	@name = split /\./, $filename; # split on dots.
	$ext = $name[$#name];  # last element is the extension.
	unless ( $ext eq 'csv' or $ext eq 'txt' ){
	    print qq{<b>The file must be a .csv or .txt file!</b>};
	    print qq{</body></html>\n};
	    exit;
	}

	pop(@name); # pull off extension.
	foreach $n (@name){ $name .= "$n.";} # assemble name 
	chop; # remove trailing dot

	open ( OUTFILE, ">$filename") || 
	    die $lex{'Cannot open file'}. " $filename"; 
	my $bufcount = 0;
	while ( my $bytesread = read( $file, my $buffer, 1024) ) { 
	    print OUTFILE $buffer;
	    $bufcount++;
	    if ( $bufcount > $maxbufcount ) {
		print qq{<h1>$lex{'Maximum File Upload size exceeded!'}};
		print qq{ ($maxbufcount K)</h1>\n};
		print qq{</body></html>\n};
		die $lex{'Maximum File Upload size exceeded!'};
	    }
	}

	close OUTFILE;

    } else {
	print $lex{'Cannot open file'};
	print qq{</body></html>\n};
    }
    

    unless ( open ( FH,"<$filename" ) ) {
	print $lex{'Cannot open file'}. ": $!\n";
	die $lex{'Cannot open file'}. ": $!\n";
    }

    
    # Empty the mss_transcript table.
    my $sth = $dbh->prepare("truncate mss_transcript");
    $sth->execute;
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

    
    my $sth = $dbh->prepare("insert into mss_transcript (mssid,coursecode,date,mark,credit)
			    values(?,?,?,?,?)");
    
    my $heading = <FH>; # read the columns descriptions.
    
    while ( my $line = <FH> ) {
	#print qq{LINE: $line<br>\n};
	my @fields;
	
	if ( $csv->parse($line) ) {

	    my @fields = $csv->fields;
	    my @new;
	    foreach my $idx ( 0,1,4,5,6 ) { # mssid, coursecode, date, mark, credit
		push @new, $fields[$idx];
	    }

	    $sth->execute( @new ); # insert into table.
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    
	} # end of line parse

	    
    } # end of this line read.

    close FH;
    system("rm -f $filename");

    print qq{<h3>Import into MSS Transcript Table complete</h3>\n};
    print qq{[ <a href="$homepage">$lex{Main}</a> |\n};
    print qq{<a href="$exppage">$lex{Export}</a> ]\n};
    
    print qq{</body></html>\n};

    exit;

}
