#!/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 %lex = ('Main' => 'Main',
	   'Error' => 'Error',
	   'Import Students' => 'Import Students',
	   'Export' => 'Export',
	   'Upload Student CSV file' => 'Upload Student 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',
	   'Cannot sysopen student number file.' => 'Cannot sysopen student number file.',
	   'Complete' => 'Complete',

	   );

my $self = 'importCSV.pl';

my $maxbufcount = 500; # 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 = $lex{'Import Students'};
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};
    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 qq{<b>$lex{'The file must be a .csv file!'}</b>};
	    print qq{</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 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};
    }

    # 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 qq{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 qq{Field Index: $maxrecfields<br>\n}; # note zero based


    # Load the fieldnames and fieldvalues from meta.
    my $sth = $dbh->prepare("select fieldid, fieldname from meta 
      where tableid = 'student' order by arrayidx");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

    my @fields;
    while ( my ( $fieldid, $fieldname ) = $sth->fetchrow ) {
	$fieldname =~ s/\(//g; # strip open parens
	if ( $fieldid eq 'studid' ) { next; } # skip studid;autoincrement
	if ( $fieldid eq 'studnum' ) { next; }
	# Restrict studnum import: if ( $fieldid eq 'studnum' ) { next; } # skip studnum
	push @fields, "$fieldname ($fieldid)";
    }

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

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

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

	print qq{<tr><td class="ra"><select name="fld$fieldcount"><option></option>\n};
	foreach my $fld ( @fields ) { print qq{<option>$fld</option>}; }
	print qq{</select></td>\n};

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

    }

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

    exit;
}


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

    print qq{<table cellpadding="3" cellspacing="0" border="0">\n};

    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">\n};

    print qq{<tr><td colspan="2" class="cn">};
    print qq{<input type="submit" value="$lex{'Upload Student CSV file'}">\n};
    print qq{</td></tr></form></table></body></html>\n};

    exit;

}


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

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

    my %fieldids;
    my ( $studnumidx, $lastnameidx, $firstnameidx ); # if we have those fields in import...
    foreach my $key ( sort keys %arr ) { 

	my ($dud, $val) = split /fld/, $key;

	if ( $arr{$key} ) { # if we have a value...
	    my ($dud, $fieldid) = split /\(/, $arr{$key};
	    chop $fieldid;

	    if ( $fieldid eq 'studnum' ) { $studnumval = $val; } 
	    elsif ( $fieldid eq 'lastname' ) { $lastnameval = $val; } 
	    elsif ( $fieldid eq 'firstname' ) { $firstnameval = $val; } 

	    $fieldids{$val} = $fieldid;  # format: $fieldids{csvcolumn} = $fieldid
	}
    }
    #  This gives us the fieldid of each column in imported records. 

    #foreach my $key ( sort keys %fieldids ) { print qq{K:$key V:$fieldids{$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";
    }

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

	my @fieldnames;
	my @values;
	my $writemode;  # add or update student records.

	if ( $csv->parse($line) ) {
	    my @fields = ();	    
	    @fields = $csv->fields;
	    foreach my $idx ( 0 .. $#fields ) { # go through each array element (field)
		if ( $fieldids{$idx} ) { # if we have a matching fieldname

		    if ( $fieldids{$idx} eq 'birthdate' ) { # convert the date;
			$fields[$idx] = convertbirthdate( $fields[$idx] );
		    }

		    my $val = $dbh->quote( $fields[$idx] );
		    push @values,$val;
		    push @fieldnames, $fieldids{$idx};
		}
	    }
	} else { # Failure to parse
	    print qq{$lex{'Error Reading Record'}:<br>$line<br>\n};
	    die;
	}

	# Search for existing record student number ( studnum )
	my $studnum;
	if ( $studnumval ) {
	    $studnum = $fields[ $studnumval ];
	    # Check for Duplicates
	    my $sth = $dbh->prepare("select count(studnum) from student where studnum = ?");
	    $sth->execute( $studnum );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $count = $sth->fetchrow;
	    if ( $count > 0 ) { $writemode = 'update'; }

	} elsif ( $lastnameval and $firstnameval ) {

	    my $sth = $dbh->prepare("select studnum from student where 
              lastname = ? and firstname = ?");
	    $sth->execute( $fields[ $lastnameval ], $fields[ $firstnameval ] );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    $studnum = $sth->fetchrow;
	} 

	if ( not $studnum ) { # we didn't get it from above, then only add records.
	    $writemode = 'add';

	    # Get Student number and update file.
	    sysopen (SNUM, "../../etc/studentnumber", O_RDWR | O_CREAT) or 
		die $lex{'Cannot sysopen student number file.'};

	    flock(SNUM,LOCK_EX);
	    
	    $studentnum = <SNUM> || 0;
	    chomp $studentnum;
	    seek(SNUM,0,0) or die $lex{Error}. ": $!\n";
	    truncate(SNUM,0) or die $lex{Error}. ": $!\n";

	    $newstudentnum = $studentnum + 1;
	    print SNUM $newstudentnum or die $lex{Error}. ": $!\n";
	    close SNUM or die $lex{Error}. ": $! \n";

	    $studnum = $studentnum;

	}

	if ( $writemode eq 'add' ) {

	    push @values, $studentnum;
	    push @fieldnames, 'studnum';

	    my $fields = join(',', @fieldnames );
	    my $values = join(',', @values );
	    print qq{Insert Fields: $fields  Values: $values<br>\n};
	    my $sth = $dbh->prepare("insert into student ( $fields ) values( $values )");
	    $sth->execute;
	    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }

	} else { # we must update existing record

	    foreach my $value ( @values ) {

		my $fieldname = pop @fieldnames;

		my $sth = $dbh->prepare("update student set $fieldname = $value where studnum = ?");
		$sth->execute( $studnum );
		if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	    }
	}

    } # end of this line read.

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

    print qq{<h1>$lex{'Import students'} $lex{Complete}</h1>\n};
    print qq{</body></html>\n};

    exit;

}


#-------------------
sub convertbirthdate {
#-------------------

    my $oldbirthdate = shift;

    # MODIFY to suit.
    my ($mo,$da,$yr) = split /\//, $oldbirthdate;

    my $newbirthdate;
    if ( $da ) { # parse ok
	$newbirthdate = "$yr-$mo-$da";
    } else {
	$newbirthdate = $oldbirthdate;
    }

    return $newbirthdate;

}
