#!/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}. " $@
\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); $dbh->{mysql_enable_utf8} = 1; my $csv = Text::CSV_XS->new( {binary => 1} ); # Print Page Header my $title = $lex{'Import Students'}; 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}; 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{$lex{'The file must be a .csv file!'}}; print qq{\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{

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

\n}; print qq{\n}; die $lex{'Maximum File Upload size exceeded!'}; } } 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 $lex{'Cannot open file'}. ": $!\n"; die $lex{'Cannot open file'}. ": $!\n"; } 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 $lex{'Error Reading Record'}. ":
$line
\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
\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{
\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 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}
\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 $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'}:
$line
\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 = || 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
\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{

$lex{'Import students'} $lex{Complete}

\n}; print qq{\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; }