+sub batch_import {
+ my $param = shift;
+
+ warn "$me batch_import call with params: \n". Dumper($param)
+ if $DEBUG;
+
+ my $table = $param->{table};
+
+ my $job = $param->{job};
+ my $file = $param->{file};
+ my $params = $param->{params} || {};
+
+ my( $type, $header, $sep_char, $fixedlength_format,
+ $xml_format, $row_callback, @fields );
+ my $postinsert_callback = '';
+ if ( $param->{'format'} ) {
+
+ my $format = $param->{'format'};
+ my $formats = $param->{formats};
+ die "unknown format $format" unless exists $formats->{ $format };
+
+ $type = $param->{'format_types'}
+ ? $param->{'format_types'}{ $format }
+ : $param->{type} || 'csv';
+
+
+ $header = $param->{'format_headers'}
+ ? $param->{'format_headers'}{ $param->{'format'} }
+ : 0;
+
+ $sep_char = $param->{'format_sep_chars'}
+ ? $param->{'format_sep_chars'}{ $param->{'format'} }
+ : ',';
+
+ $fixedlength_format =
+ $param->{'format_fixedlength_formats'}
+ ? $param->{'format_fixedlength_formats'}{ $param->{'format'} }
+ : '';
+
+ $xml_format =
+ $param->{'format_xml_formats'}
+ ? $param->{'format_xml_formats'}{ $param->{'format'} }
+ : '';
+
+ $row_callback =
+ $param->{'format_row_callbacks'}
+ ? $param->{'format_row_callbacks'}{ $param->{'format'} }
+ : '';
+
+ @fields = @{ $formats->{ $format } };
+
+ } elsif ( $param->{'fields'} ) {
+
+ $type = ''; #infer from filename
+ $header = 0;
+ $sep_char = ',';
+ $fixedlength_format = '';
+ $row_callback = '';
+ @fields = @{ $param->{'fields'} };
+
+ $postinsert_callback = $param->{'postinsert_callback'}
+ if $param->{'postinsert_callback'}
+
+ } else {
+ die "neither format nor fields specified";
+ }
+
+ #my $file = $param->{file};
+
+ unless ( $type ) {
+ if ( $file =~ /\.(\w+)$/i ) {
+ $type = lc($1);
+ } else {
+ #or error out???
+ warn "can't parse file type from filename $file; defaulting to CSV";
+ $type = 'csv';
+ }
+ $type = 'csv'
+ if $param->{'default_csv'} && $type ne 'xls';
+ }
+
+
+ my $row = 0;
+ my $count;
+ my $parser;
+ my @buffer = ();
+ if ( $type eq 'csv' || $type eq 'fixedlength' ) {
+
+ if ( $type eq 'csv' ) {
+
+ my %attr = ();
+ $attr{sep_char} = $sep_char if $sep_char;
+ $parser = new Text::CSV_XS \%attr;
+
+ } elsif ( $type eq 'fixedlength' ) {
+
+ eval "use Parse::FixedLength;";
+ die $@ if $@;
+ $parser = new Parse::FixedLength $fixedlength_format;
+
+ }
+ else {
+ die "Unknown file type $type\n";
+ }
+
+ @buffer = split(/\r?\n/, slurp($file) );
+ splice(@buffer, 0, ($header || 0) );
+ $count = scalar(@buffer);
+
+ } elsif ( $type eq 'xls' ) {
+
+ eval "use Spreadsheet::ParseExcel;";
+ die $@ if $@;
+
+ eval "use DateTime::Format::Excel;";
+ #for now, just let the error be thrown if it is used, since only CDR
+ # formats bill_west and troop use it, not other excel-parsing things
+ #die $@ if $@;
+
+ my $excel = Spreadsheet::ParseExcel::Workbook->new->Parse($file);
+
+ $parser = $excel->{Worksheet}[0]; #first sheet
+
+ $count = $parser->{MaxRow} || $parser->{MinRow};
+ $count++;
+
+ $row = $header || 0;
+ } elsif ( $type eq 'xml' ) {
+ # FS::pay_batch
+ eval "use XML::Simple;";
+ die $@ if $@;
+ my $xmlrow = $xml_format->{'xmlrow'};
+ $parser = $xml_format->{'xmlkeys'};
+ die 'no xmlkeys specified' unless ref $parser eq 'ARRAY';
+ my $data = XML::Simple::XMLin(
+ $file,
+ 'SuppressEmpty' => '', #sets empty values to ''
+ 'KeepRoot' => 1,
+ );
+ my $rows = $data;
+ $rows = $rows->{$_} foreach @$xmlrow;
+ $rows = [ $rows ] if ref($rows) ne 'ARRAY';
+ $count = @buffer = @$rows;
+ } else {
+ die "Unknown file type $type\n";
+ }
+
+ #my $columns;
+
+ local $SIG{HUP} = 'IGNORE';
+ local $SIG{INT} = 'IGNORE';
+ local $SIG{QUIT} = 'IGNORE';
+ local $SIG{TERM} = 'IGNORE';
+ local $SIG{TSTP} = 'IGNORE';
+ local $SIG{PIPE} = 'IGNORE';
+
+ my $oldAutoCommit = $FS::UID::AutoCommit;
+ local $FS::UID::AutoCommit = 0;
+ my $dbh = dbh;
+
+ #my $params = $param->{params} || {};
+ if ( $param->{'batch_namecol'} && $param->{'batch_namevalue'} ) {
+ my $batch_col = $param->{'batch_keycol'};
+
+ my $batch_class = 'FS::'. $param->{'batch_table'};
+ my $batch = $batch_class->new({
+ $param->{'batch_namecol'} => $param->{'batch_namevalue'}
+ });
+ my $error = $batch->insert;
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return "can't insert batch record: $error";
+ }
+ #primary key via dbdef? (so the column names don't have to match)
+ my $batch_value = $batch->get( $param->{'batch_keycol'} );
+
+ $params->{ $batch_col } = $batch_value;
+ }
+
+ #my $job = $param->{job};
+ my $line;
+ my $imported = 0;
+ my( $last, $min_sec ) = ( time, 5 ); #progressbar foo
+ while (1) {
+
+ my @columns = ();
+ if ( $type eq 'csv' ) {
+
+ last unless scalar(@buffer);
+ $line = shift(@buffer);
+
+ next if $line =~ /^\s*$/; #skip empty lines
+
+ $line = &{$row_callback}($line) if $row_callback;
+
+ $parser->parse($line) or do {
+ $dbh->rollback if $oldAutoCommit;
+ return "can't parse: ". $parser->error_input();
+ };
+ @columns = $parser->fields();
+
+ } elsif ( $type eq 'fixedlength' ) {
+
+ @columns = $parser->parse($line);
+
+ } elsif ( $type eq 'xls' ) {
+
+ last if $row > ($parser->{MaxRow} || $parser->{MinRow})
+ || ! $parser->{Cells}[$row];
+
+ my @row = @{ $parser->{Cells}[$row] };
+ @columns = map $_->{Val}, @row;
+
+ #my $z = 'A';
+ #warn $z++. ": $_\n" for @columns;
+
+ } elsif ( $type eq 'xml' ) {
+ # $parser = [ 'Column0Key', 'Column1Key' ... ]
+ last unless scalar(@buffer);
+ my $row = shift @buffer;
+ @columns = @{ $row }{ @$parser };
+ } else {
+ die "Unknown file type $type\n";
+ }
+
+ my @later = ();
+ my %hash = %$params;
+
+ foreach my $field ( @fields ) {
+
+ my $value = shift @columns;
+
+ if ( ref($field) eq 'CODE' ) {
+ #&{$field}(\%hash, $value);
+ push @later, $field, $value;
+ } else {
+ #??? $hash{$field} = $value if length($value);
+ $hash{$field} = $value if defined($value) && length($value);
+ }
+
+ }
+
+ #my $table = $param->{table};
+ my $class = "FS::$table";
+
+ my $record = $class->new( \%hash );
+
+ my $param = {};
+ while ( scalar(@later) ) {
+ my $sub = shift @later;
+ my $data = shift @later;
+ eval {
+ &{$sub}($record, $data, $conf, $param); # $record->&{$sub}($data, $conf)
+ };
+ if ( $@ ) {
+ $dbh->rollback if $oldAutoCommit;
+ return "can't insert record". ( $line ? " for $line" : '' ). ": $@";
+ }
+ last if exists( $param->{skiprow} );
+ }
+ next if exists( $param->{skiprow} );
+
+ my $error = $record->insert;
+
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return "can't insert record". ( $line ? " for $line" : '' ). ": $error";
+ }
+
+ $row++;
+ $imported++;
+
+ if ( $postinsert_callback ) {
+ my $error = &{$postinsert_callback}($record, $param);
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return "postinsert_callback error". ( $line ? " for $line" : '' ).
+ ": $error";
+ }
+ }
+
+ if ( $job && time - $min_sec > $last ) { #progress bar
+ $job->update_statustext( int(100 * $imported / $count) );
+ $last = time;
+ }
+
+ }
+
+ unless ( $imported || $param->{empty_ok} ) {
+ $dbh->rollback if $oldAutoCommit;
+ return "Empty file!";
+ }
+
+ $dbh->commit or die $dbh->errstr if $oldAutoCommit;;
+
+ ''; #no error
+
+}
+
+sub _h_statement {
+ my( $self, $action, $time ) = @_;
+
+ $time ||= time;
+
+ my %nohistory = map { $_=>1 } $self->nohistory_fields;
+
+ my @fields =
+ grep { defined($self->get($_)) && $self->get($_) ne "" && ! $nohistory{$_} }
+ real_fields($self->table);
+ ;
+
+ # If we're encrypting then don't store the payinfo in the history
+ if ( $conf && $conf->exists('encryption') ) {
+ @fields = grep { $_ ne 'payinfo' } @fields;
+ }
+
+ my @values = map { _quote( $self->getfield($_), $self->table, $_) } @fields;
+
+ "INSERT INTO h_". $self->table. " ( ".
+ join(', ', qw(history_date history_user history_action), @fields ).
+ ") VALUES (".
+ join(', ', $time, dbh->quote(getotaker()), dbh->quote($action), @values).
+ ")"
+ ;
+}
+
+=item unique COLUMN
+
+B<Warning>: External use is B<deprecated>.
+
+Replaces COLUMN in record with a unique number, using counters in the
+filesystem. Used by the B<insert> method on single-field unique columns
+(see L<DBIx::DBSchema::Table>) and also as a fallback for primary keys
+that aren't SERIAL (Pg) or AUTO_INCREMENT (mysql).
+
+Returns the new value.
+
+=cut
+
+sub unique {
+ my($self,$field) = @_;
+ my($table)=$self->table;
+
+ croak "Unique called on field $field, but it is ",
+ $self->getfield($field),
+ ", not null!"
+ if $self->getfield($field);
+
+ #warn "table $table is tainted" if is_tainted($table);
+ #warn "field $field is tainted" if is_tainted($field);
+
+ my($counter) = new File::CounterFile "$table.$field",0;
+# hack for web demo
+# getotaker() =~ /^([\w\-]{1,16})$/ or die "Illegal CGI REMOTE_USER!";
+# my($user)=$1;
+# my($counter) = new File::CounterFile "$user/$table.$field",0;
+# endhack
+
+ my $index = $counter->inc;
+ $index = $counter->inc while qsearchs($table, { $field=>$index } );
+
+ $index =~ /^(\d*)$/;
+ $index=$1;
+
+ $self->setfield($field,$index);
+
+}
+
+=item ut_float COLUMN
+
+Check/untaint floating point numeric data: 1.1, 1, 1.1e10, 1e10. May not be
+null. If there is an error, returns the error, otherwise returns false.
+
+=cut
+
+sub ut_float {
+ my($self,$field)=@_ ;
+ ($self->getfield($field) =~ /^\s*(\d+\.\d+)\s*$/ ||
+ $self->getfield($field) =~ /^\s*(\d+)\s*$/ ||
+ $self->getfield($field) =~ /^\s*(\d+\.\d+e\d+)\s*$/ ||
+ $self->getfield($field) =~ /^\s*(\d+e\d+)\s*$/)
+ or return "Illegal or empty (float) $field: ". $self->getfield($field);
+ $self->setfield($field,$1);
+ '';
+}
+=item ut_floatn COLUMN
+
+Check/untaint floating point numeric data: 1.1, 1, 1.1e10, 1e10. May be
+null. If there is an error, returns the error, otherwise returns false.
+
+=cut
+
+#false laziness w/ut_ipn
+sub ut_floatn {
+ my( $self, $field ) = @_;
+ if ( $self->getfield($field) =~ /^()$/ ) {
+ $self->setfield($field,'');
+ '';
+ } else {
+ $self->ut_float($field);
+ }
+}
+
+=item ut_sfloat COLUMN
+
+Check/untaint signed floating point numeric data: 1.1, 1, 1.1e10, 1e10.
+May not be null. If there is an error, returns the error, otherwise returns
+false.
+
+=cut
+
+sub ut_sfloat {
+ my($self,$field)=@_ ;
+ ($self->getfield($field) =~ /^\s*(-?\d+\.\d+)\s*$/ ||
+ $self->getfield($field) =~ /^\s*(-?\d+)\s*$/ ||
+ $self->getfield($field) =~ /^\s*(-?\d+\.\d+[eE]-?\d+)\s*$/ ||
+ $self->getfield($field) =~ /^\s*(-?\d+[eE]-?\d+)\s*$/)
+ or return "Illegal or empty (float) $field: ". $self->getfield($field);
+ $self->setfield($field,$1);
+ '';
+}
+=item ut_sfloatn COLUMN
+
+Check/untaint signed floating point numeric data: 1.1, 1, 1.1e10, 1e10. May be
+null. If there is an error, returns the error, otherwise returns false.
+
+=cut
+
+sub ut_sfloatn {
+ my( $self, $field ) = @_;
+ if ( $self->getfield($field) =~ /^()$/ ) {
+ $self->setfield($field,'');
+ '';
+ } else {
+ $self->ut_sfloat($field);
+ }
+}
+
+=item ut_snumber COLUMN
+
+Check/untaint signed numeric data (whole numbers). If there is an error,
+returns the error, otherwise returns false.
+
+=cut
+
+sub ut_snumber {
+ my($self, $field) = @_;
+ $self->getfield($field) =~ /^\s*(-?)\s*(\d+)\s*$/
+ or return "Illegal or empty (numeric) $field: ". $self->getfield($field);
+ $self->setfield($field, "$1$2");
+ '';
+}
+
+=item ut_snumbern COLUMN
+
+Check/untaint signed numeric data (whole numbers). If there is an error,
+returns the error, otherwise returns false.
+
+=cut
+
+sub ut_snumbern {
+ my($self, $field) = @_;
+ $self->getfield($field) =~ /^\s*(-?)\s*(\d*)\s*$/
+ or return "Illegal (numeric) $field: ". $self->getfield($field);
+ if ($1) {
+ return "Illegal (numeric) $field: ". $self->getfield($field)
+ unless $2;
+ }
+ $self->setfield($field, "$1$2");
+ '';
+}
+
+=item ut_number COLUMN
+
+Check/untaint simple numeric data (whole numbers). May not be null. If there
+is an error, returns the error, otherwise returns false.
+
+=cut
+
+sub ut_number {
+ my($self,$field)=@_;
+ $self->getfield($field) =~ /^\s*(\d+)\s*$/
+ or return "Illegal or empty (numeric) $field: ". $self->getfield($field);
+ $self->setfield($field,$1);
+ '';
+}
+
+=item ut_numbern COLUMN
+
+Check/untaint simple numeric data (whole numbers). May be null. If there is
+an error, returns the error, otherwise returns false.
+
+=cut
+
+sub ut_numbern {
+ my($self,$field)=@_;
+ $self->getfield($field) =~ /^\s*(\d*)\s*$/
+ or return "Illegal (numeric) $field: ". $self->getfield($field);
+ $self->setfield($field,$1);
+ '';
+}
+
+=item ut_money COLUMN
+
+Check/untaint monetary numbers. May be negative. Set to 0 if null. If there
+is an error, returns the error, otherwise returns false.
+
+=cut
+
+sub ut_money {
+ my($self,$field)=@_;
+ $self->setfield($field, 0) if $self->getfield($field) eq '';
+ $self->getfield($field) =~ /^\s*(\-)?\s*(\d*)(\.\d{2})?\s*$/
+ or return "Illegal (money) $field: ". $self->getfield($field);
+ #$self->setfield($field, "$1$2$3" || 0);
+ $self->setfield($field, ( ($1||''). ($2||''). ($3||'') ) || 0);
+ '';
+}
+
+=item ut_moneyn COLUMN
+
+Check/untaint monetary numbers. May be negative. If there
+is an error, returns the error, otherwise returns false.
+
+=cut
+
+sub ut_moneyn {
+ my($self,$field)=@_;
+ if ($self->getfield($field) eq '') {
+ $self->setfield($field, '');
+ return '';
+ }
+ $self->ut_money($field);
+}
+
+=item ut_text COLUMN
+
+Check/untaint text. Alphanumerics, spaces, and the following punctuation
+symbols are currently permitted: ! @ # $ % & ( ) - + ; : ' " , . ? / = [ ] < >
+May not be null. If there is an error, returns the error, otherwise returns
+false.
+
+=cut
+
+sub ut_text {