#!/usr/bin/perl
#  Copyright 2001-2024 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' => 'Import',
	   'Staff' => 'Staff',
	   'Export' => 'Export',
	   'Upload' => 'Upload',
	   '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',
	   'Grade' => 'Grade',
	   'Homeroom' => 'Homeroom',
	   'Position' => 'Position',
	   'Imported' => 'Imported',

	   );

my $self = 'importstaff.pl';

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

use DBI;
use CGI;
use Text::CSV_XS;
use Crypt::GeneratePassword qw(word);

# Password config; rest in admin.conf
# For Generation
$g_staffpwd_minfreq = .001;
$g_staffpwd_avgfreq = .001;
$g_staffpwd_lang = 'en'; # only en or de available.
# For Checking
my $g_staffpwd_groups = 0; # turn off character group (uppercase, lowercase, symbols) checking
my $g_staffpwd_following = 0; # turn off following character checking (keyboard, same)


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} $lex{Staff}";

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};
    importStaff();
}


#---------------
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};
	    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};
		exit;
			    
	    }
	}

	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 qq{$lex{'Cannot open file'}: $!\n};
	exit;
    }

    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 qq{$lex{'Error Reading Record'}:<br>$line<br>\n};
	    exit;
	}
    }
    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 = 'staff' 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 'id' ) { next; } # skip id
	$fields{"$fieldname"} = $fieldid;
    }


    # values stored in staff_multi.
    $fields{"$lex{Grade}"}= 'grade';
    $fields{"$lex{Homeroom}"} = 'homeroom';
    $fields{"$lex{Position}"} = 'position';


    # 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 align="right"><select name="fld$fieldcount"><option></option>\n};
	foreach my $key ( sort keys %fields ) {
	    print qq{<option value="$fields{$key}">$key</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" align="center">};
    print qq{<input type="submit" value="$lex{Upload}">\n};
    print qq{</td></tr></form></table></body></html>\n};

    exit;

}


#--------------
sub importStaff {
#--------------

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

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

    my %fieldids = ();
    my %fieldname = ();
    my $pwdmode = 0;  # check and add pwd if not imported.
    my $usermode = 0;  # check and add userid if not imported.

    foreach my $key ( sort keys %arr ) { 
	my ($dud, $val) = split /fld/, $key;

	if ( $arr{$key} ) { # if we have a value...

	    if ( $arr{$key} eq 'password' ) { $pwdmode = 1; }
	    if ( $arr{$key} eq 'userid' ) { $usermode = 1; }
	    $fieldids{$val} = $arr{$key};  # format is:  $fieldids{$arrindex} = lastname

	}
    }
    %fieldname = reverse %fieldids;  # format is fieldname{'lastname'} = arrayidx;

    #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 @fields = ();
	my %multivals = ();

	my $userid;
	my $password;

	if ( $csv->parse($line) ) {
	    @fields = $csv->fields;

	    foreach my $idx ( 0 .. $#fields ) { # go through each array element (field)
		if ( $fieldids{$idx} ) { # if we have a matching fieldname
		    # Check if we have a grade, homeroom, or position field
		    if ( $fieldids{$idx} eq 'grade' or $fieldids{$idx} eq 'homeroom'
			 or $fieldids{$idx} eq 'position' ) {
			$multivals{ $fieldids{$idx} } = $fields[$idx]; 

		    } else {
			my $val = $dbh->quote( $fields[$idx] );
			push @values,$val;
			push @fieldnames, $fieldids{$idx};
			# Set the Userid
			if ( $fieldids{$idx} eq 'userid' ) {
			    $userid = $val;
			}
		    }
		}
	    }

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


	if ( not $pwdmode ) { # add a password

	    # Now set password, other config values at top of script
	    $password = word( $g_staffpwd_minlen, $g_staffpwd_genlen,
			      $g_staffpwd_lang, $g_staffpwd_signs,
			      $g_staffpwd_caps, $g_staffpwd_minfreq,
			      $g_staffpwd_avgfreq );

	    $password = $dbh->quote( $password );

	    push @values,$password;
	    push @fieldnames, 'password';

	}

	if ( not $usermode ) { # add a user

	    if ( $fields[ $fieldname{'lastname'} ] and $fields[ $fieldname{'firstname'} ] ) {
		
		my $fn = lc substr $fields[ $fieldname{'firstname'} ], 0, 1;
		my $ln = lc substr $fields[ $fieldname{'lastname'} ], 0, 12;
		$userid = $fn. '.'. $ln;
		#print qq{FN:$fn LN:$ln USER:$userid<br>\n};

		# Make sure userid doesn't already exist...
		my $sth = $dbh->prepare("select count(*) from staff where userid = ?");
		$sth->execute( $userid );
		if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
		my $count = $sth->fetchrow;

		my $usercount = 1;
		my $origuserid = $userid;

		while ( $count ) {
		    $userid = $origuserid. '_'. $usercount;

		    $sth->execute( $userid );
		    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
		    $count = $sth->fetchrow;
		    $usercount++;
		    if ( $usercount > 5 ) { last; } # give up....
		}

		if ( not $count ) { # no duplicates
		    my $uid = $dbh->quote( $userid );
		    push @values,$uid;
		    push @fieldnames, 'userid';
		}

	    }
	} # End of Userid mode


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

	$userid =~ s/^'|'$//g; # remove leading/trailing quotes

	if ( %multivals ) { # we have values for the staff_multi table
	    my $sth = $dbh->prepare("insert into staff_multi ( userid, field_name, field_value ) values( ?, ?, ? )");

	    foreach my $fieldname ( keys %multivals ) {
		my $fieldvalue = $multivals{ $fieldname };

		print qq{User:$userid  Name: $fieldname  Value: $fieldvalue<br>\n};

		$sth->execute( $userid, $fieldname, $fieldvalue );
		if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	    }
	}

    }

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

    print qq{<h1>$lex{Staff} $lex{Imported}</h1>\n};
    print qq{</body></html>\n};

    exit;

}
