#!/usr/bin/perl # Copyright 2001-2008 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.', ); 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; 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". $lex{'Import Students'}; print "\n"; print "$chartype\n\n"; print "[ ". $lex{Main}. " |\n"; print "". $lex{Export}. " ]\n"; print "

". $lex{'Import Students'}. "

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

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

\n"; print "\n"; die $lex{'Maximum File Upload size exceeded!'}; } } close OUTFILE; } else { print $lex{'Cannot open file'}; print "\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 "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 "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 push @fields, "$fieldname ($fieldid)"; } # Now create the form for field name selection. print "\n"; exit; } #---------------- sub showStartPage { #---------------- print "\n"; print "\n"; print "\n"; print "\n"; print "

"; print "\n"; print "

\n"; exit; } #----------------- sub importStudents { #----------------- my $filename = $arr{filename}; delete $arr{filename}; my %fieldids = (); foreach my $key ( sort keys %arr ) { my ($dud, $val) = split /fld/, $key; if ( $arr{$key} ) { # if we have a value... my ($dud, $field) = split /\(/, $arr{$key}; chop $field; $fieldids{$val} = $field; # format is: $fields{lastname} = $arrayindex } } #foreach my $key ( sort keys %fieldids ) { print "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 "LINE: $line
\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 ( $fieldids{$idx} ) { # if we have a matching fieldname my $val = $dbh->quote( $fields[$idx] ); push @values,$val; push @fieldnames, $fieldids{$idx}; } } } else { # Failure to parse print $lex{'Error Reading Record'}. ":
$line
\n"; die; } # 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"; push @values, $studentnum; push @fieldnames, 'studnum'; my $fields = join(',', @fieldnames ); my $values = join(',', @values ); print "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; } } close FH; system("rm -f $filename"); exit; }