#!/usr/bin/perl # Copyright 2001-2009 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', 'Upload Locks' => 'Upload Locks', 'Fees' => 'Fees', 'Upload Lock CSV file' => 'Upload Lock 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', 'Fields' => 'Fields', 'Values' => 'Values', ); my $self = 'lock_upload.pl'; my @lockfields = ( lock_num, combination, pool, comment ); my $maxbufcount = 500; # 500k max; change as required my $maxrecorddisplay = 5; use DBI; use CGI; use Text::CSV_XS; 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 print "$doctype\n". $lex{'Upload Locks'}; print "\n"; print "$chartype\n\n"; print "[ ". $lex{Main}. " |\n"; print "". $lex{Fees}. " ]\n"; print "

". $lex{'Upload Locks'}. "

\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 # located at top. #my @lockfields = ( lock_num, combination, pool, comment ); # Now create the form for field name selection. print "
\n"; print "\n"; print "\n"; print "\n"; print "\n"; foreach my $fieldcount ( 0 .. $maxrecfields ) { # from import values. print "\n"; # Now display imported records. for my $rec (0 .. 3) { if ( $records[$rec]->[$fieldcount] ) { print "\n"; } } print "\n"; } print "
". $records[$rec]->[$fieldcount]. "
\n"; 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}; #foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}
\n"; } my %fieldid = (); foreach my $key ( sort keys %arr ) { my ($dud, $val) = split /fld/, $key; if ( $arr{$key} ) { # if we have a value... $fieldid{$val} = $arr{$key}; # format is: $fields{lastname} = $arrayindex } } #foreach my $key ( sort keys %fieldid ) { print "K:$key V:$fieldid{$key}
\n"; } # Open csv file for reading, again. unless ( open ( FH,"<$filename" ) ) { print $lex{'Cannot open file'}. ": $!\n"; die $lex{'Cannot open file'}. ": $!\n"; } my $first = 1; 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 ( $fieldid{$idx} ) { # if we have a matching fieldname my $val = $dbh->quote( $fields[$idx] ); push @values,$val; push @fieldnames, $fieldid{$idx}; } } } else { # Failure to parse print $lex{'Error Reading Record'}. ":
$line
\n"; die; } my $fields = join(',', @fieldnames ); my $values = join(',', @values ); if ( $first ) { print $lex{Insert}. q{ }. $lex{Fields}. ": $fields
\n"; $first = 0; } print $lex{Values}. ": $values
\n"; my $sth = $dbh->prepare("insert into lok_lock ( $fields ) values( $values )"); $sth->execute; if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } } close FH; system("rm -f $filename"); exit; }