#!/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}. " $@
\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$title\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} |\n}; print qq{$lex{Export} ]\n}; print qq{

$title

\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{". $lex{'The file must be a .csv file!'}. "}; print qq{\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{

$lex{'Maximum File Upload size exceeded!'}}; print qq{ ($maxbufcount K)

\n}; print qq{\n}; exit; } } close OUTFILE; } else { print $lex{'Cannot open file'}; print qq{\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 = ; if ( not $line ) { next; } # skip blank lines / empty records up to limit #print qq{LINE: $line
\n"; if ( $csv->parse($line) ) { my @fields = $csv->fields; push @records, [ @fields ]; } else { # Failure to parse print qq{$lex{'Error Reading Record'}:
$line
\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
\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{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; foreach my $fieldcount ( 0 .. $maxrecfields ) { # from import values. print qq{\n}; # Now display imported records. for my $rec (0 .. 3) { if ( $records[$rec]->[$fieldcount] ) { print qq{\n}; } } print qq{\n}; } print qq{
$records[$rec]->[$fieldcount]
\n}; print qq{\n}; exit; } #---------------- sub showStartPage { #---------------- print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{
}; print qq{\n}; print qq{
\n}; exit; } #-------------- sub importStaff { #-------------- #foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\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}
\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 = ) { #print qq{LINE: $line
\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'}:
$line
\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
\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
\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
\n}; $sth->execute( $userid, $fieldname, $fieldvalue ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } } } } close FH; system("rm -f $filename"); print qq{

$lex{Staff} $lex{Imported}

\n}; print qq{\n}; exit; }