#!/usr/bin/perl
#  Copyright 2001-2009 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',
	   'Upload Locks' => 'Upload Locks',
	   'Fees' => 'Fees',
	   'Upload Lock CSV file' => 'Upload Lock CSV file',
	   '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',
	   'Fields' => 'Fields',
	   'Values' => 'Values',

	   );

my $self = 'lock_upload.pl';

my @lockfields = ( lock_num, combination, pool, comment );
my $maxbufcount = 500; # 500k max; change as required
my $maxrecorddisplay = 5;

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


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
print "$doctype\n<html><head><title>". $lex{'Upload Locks'};
print "</title><link rel=\"stylesheet\" href=\"$css\" type=\"text/css\">\n";
print "$chartype\n</head><body>\n";

print "[ <a href=\"$homepage\">". $lex{Main}. "</a> |\n";
print "<a href=\"$feespage\">". $lex{Fees}. "</a> ]\n";
print "<center><h1>". $lex{'Upload Locks'}. "</h1></center>\n";

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


#---------------
sub selectFields {
#---------------

    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' ){
	    print "<b>". $lex{'The file must be a .csv file!'}. "</b>";
	    print "</body></html>\n";
	    die;
	}

	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 "<h1>". $lex{'Maximum File Upload size exceeded!'};
		print " ($maxbufcount K)</h1>\n";
		print "</body></html>\n";
		die $lex{'Maximum File Upload size exceeded!'};
	    }
	}

	close OUTFILE;

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

    # We should now have the file in place.
    # Open csv file for reading
    unless ( open ( FH,"<$filename" ) ) {
	print $lex{'Cannot open file'}. ": $!\n";
	die $lex{'Cannot open file'}. ": $!\n";
    }

    my @records = ();
    for my $count ( 1 .. $maxrecorddisplay ) {
	my $line = <FH>;
	if ( not $line ) { next; } # skip blank lines / empty records up to limit
	#print "LINE: $line<br>\n";
	if ( $csv->parse($line) ) {
	    my @fields = $csv->fields;
	    push @records, [ @fields ];
	} else { # Failure to parse
	    print $lex{'Error Reading Record'}. ":<br>$line<br>\n";
	    die;
	}
    }
    close FH;
    my $maxrecfields; # now find largest number of fields in any record
    for my $idx (0..$#records) {
	if ( $#{ $records[$idx] } > $maxrecfields ) {
	    $maxrecfields = $#{ $records[$idx] }
	}
    }

    #print "Field Index: $maxrecfields<br>\n"; # note zero based

    # located at top.
    #my @lockfields = ( lock_num, combination, pool, comment );

    # Now create the form for field name selection.
    print "<center><form action=\"$self\" method=\"post\">\n";
    print "<input type=\"hidden\" name=\"filename\" value=\"$filename\">\n";
    print "<input type=\"hidden\" name=\"page\" value=\"2\">\n";

    print "<input type=\"submit\" value=\"". $lex{Continue}. "\">\n";
    print "<table cellspacing=\"0\" cellpadding=\"3\" border=\"1\">\n";

    foreach my $fieldcount ( 0 .. $maxrecfields ) { # from import values.

	print "<tr><td align=\"right\"><select name=\"fld". $fieldcount. "\"><option></option>\n";
	foreach my $fld ( @lockfields ) { print "<option>$fld</option>"; }
	print "</select></td>\n";

	# Now display imported records.
	for my $rec (0 .. 3) {
	    if ( $records[$rec]->[$fieldcount] ) {
		print "<td>". $records[$rec]->[$fieldcount]. "</td>\n";
	    }
	}
	print "</tr>\n";

    }

    print "</table>\n";
    print "</center></body></html>\n";

    exit;
}


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

    print "<center><table cellpadding=\"3\" cellspacing=\"0\" border=\"0\">\n";

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

    print "<tr><td colspan=\"2\" align=\"center\">";
    print "<input type=\"submit\" value=\"". $lex{'Upload Lock CSV file'}. "\">\n";
    print "</td></tr></form></table></body></html>\n";

    exit;

}


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


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

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

    my %fieldid = ();
    foreach my $key ( sort keys %arr ) { 
	my ($dud, $val) = split /fld/, $key;
	if ( $arr{$key} ) { # if we have a value...
	    $fieldid{$val} = $arr{$key};  # format is:  $fields{lastname} = $arrayindex
	}
    }

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

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

    my $first = 1;

    while ( my $line = <FH> ) {
	#print "LINE: $line<br>\n";

	my @fieldnames = ();
	my @values = ();

	if ( $csv->parse($line) ) {
	    my @fields = ();	    
	    @fields = $csv->fields;
	    foreach my $idx ( 0 .. $#fields ) { # go through each array element (field)
		if ( $fieldid{$idx} ) { # if we have a matching fieldname
		    my $val = $dbh->quote( $fields[$idx] );
		    push @values,$val;
		    push @fieldnames, $fieldid{$idx};
		}

	    }
	} else { # Failure to parse
	    print $lex{'Error Reading Record'}. ":<br>$line<br>\n";
	    die;
	}

	my $fields = join(',', @fieldnames );
	my $values = join(',', @values );

	if ( $first ) {
	    print $lex{Insert}. q{ }. $lex{Fields}. ": $fields<br>\n";
	    $first = 0;
	}
	print $lex{Values}. ": $values<br>\n";
	my $sth = $dbh->prepare("insert into lok_lock ( $fields ) values( $values )");
	$sth->execute;
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }	

    }

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

    exit;

}
