X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FRecord.pm;h=126b0cb31dee2740ec507531c8d4b81cfb2c39a4;hp=bc075dde952bbb23e59e64fe8988fc5f30df35d6;hb=aed8ec35ccb9cdeb7ea0cb6ff2946f9d83d582f6;hpb=42a1267af992831cb8069835a18b8672a5f9afcb

diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm
index bc075dde9..126b0cb31 100644
--- a/FS/FS/Record.pm
+++ b/FS/FS/Record.pm
@@ -2,10 +2,12 @@ package FS::Record;
 
 use strict;
 use vars qw( $AUTOLOAD @ISA @EXPORT_OK $DEBUG
-             $conf $conf_encryption $me
              %virtual_fields_cache
+             $conf $conf_encryption $money_char $lat_lower $lon_upper
+             $me
              $nowarn_identical $nowarn_classload
              $no_update_diff $no_check_foreign
+             @encrypt_payby
            );
 use Exporter;
 use Carp qw(carp cluck croak confess);
@@ -21,6 +23,7 @@ use FS::CurrentUser;
 use FS::Schema qw(dbdef);
 use FS::SearchCache;
 use FS::Msgcat qw(gettext);
+use NetAddr::IP; # for validation
 #use FS::Conf; #dependency loop bs, in install_callback below instead
 
 use FS::part_virtual_field;
@@ -29,9 +32,13 @@ use Tie::IxHash;
 
 @ISA = qw(Exporter);
 
+@encrypt_payby = qw( CARD DCRD CHEK DCHK );
+
 #export dbdef for now... everything else expects to find it here
-@EXPORT_OK = qw(dbh fields hfields qsearch qsearchs dbdef jsearch
-                str2time_sql str2time_sql_closing regexp_sql not_regexp_sql );
+@EXPORT_OK = qw(
+  dbh fields hfields qsearch qsearchs dbdef jsearch
+  str2time_sql str2time_sql_closing regexp_sql not_regexp_sql concat_sql
+);
 
 $DEBUG = 0;
 $me = '[FS::Record]';
@@ -49,17 +56,25 @@ my $rsa_decrypt;
 $conf = '';
 $conf_encryption = '';
 FS::UID->install_callback( sub {
+
   eval "use FS::Conf;";
   die $@ if $@;
   $conf = FS::Conf->new; 
   $conf_encryption = $conf->exists('encryption');
+  $money_char = $conf->config('money_char') || '$';
+  my $nw_coords = $conf->exists('geocode-require_nw_coordinates');
+  $lat_lower = $nw_coords ? 1 : -90;
+  $lon_upper = $nw_coords ? -1 : 180;
+
   $File::CounterFile::DEFAULT_DIR = $conf->base_dir . "/counters.". datasrc;
+
   if ( driver_name eq 'Pg' ) {
     eval "use DBD::Pg ':pg_types'";
     die $@ if $@;
   } else {
     eval "sub PG_BYTEA { die 'guru meditation #9: calling PG_BYTEA when not running Pg?'; }";
   }
+
 } );
 
 =head1 NAME
@@ -265,7 +280,7 @@ sub _bind_type {
 
   my $bind_type = { TYPE => SQL_VARCHAR };
 
-  if ( $type =~ /(big)?(int|serial)/i && $value =~ /^\d+(\.\d+)?$/ ) {
+  if ( $type =~ /(big)?(int|serial)/i && $value =~ /^-?\d+(\.\d+)?$/ ) {
 
     $bind_type = { TYPE => SQL_INTEGER };
 
@@ -350,7 +365,8 @@ sub qsearch {
   my @bind_type = ();
   my $dbh = dbh;
   foreach my $stable ( @stable ) {
-    my $record      = shift @record;
+    #stop altering the caller's hashref
+    my $record      = { %{ shift(@record) || {} } };#and be liberal in receipt
     my $select      = shift @select;
     my $extra_sql   = shift @extra_sql;
     my $extra_param = shift @extra_param;
@@ -527,6 +543,11 @@ sub qsearch {
          && eval 'defined(@FS::'. $table . '::encrypted_fields)' ) {
       foreach my $record (@return) {
         foreach my $field (eval '@FS::'. $table . '::encrypted_fields') {
+          next if $field eq 'payinfo' 
+                    && ($record->isa('FS::payinfo_transaction_Mixin') 
+                        || $record->isa('FS::payinfo_Mixin') )
+                    && $record->payby
+                    && !grep { $record->payby eq $_ } @encrypt_payby;
           # Set it directly... This may cause a problem in the future...
           $record->setfield($field, $record->decrypt($record->getfield($field)));
         }
@@ -995,7 +1016,12 @@ sub insert {
        && $conf->exists('encryption')
   ) {
     foreach my $field (eval '@FS::'. $table . '::encrypted_fields') {
-      $self->{'saved'} = $self->getfield($field);
+      next if $field eq 'payinfo' 
+                && ($self->isa('FS::payinfo_transaction_Mixin') 
+                    || $self->isa('FS::payinfo_Mixin') )
+                && $self->payby
+                && !grep { $self->payby eq $_ } @encrypt_payby;
+      $saved->{$field} = $self->getfield($field);
       $self->setfield($field, $self->encrypt($self->getfield($field)));
     }
   }
@@ -1275,6 +1301,11 @@ sub replace {
        && scalar( eval '@FS::'. $new->table . '::encrypted_fields')
   ) {
     foreach my $field (eval '@FS::'. $new->table . '::encrypted_fields') {
+      next if $field eq 'payinfo' 
+                && ($new->isa('FS::payinfo_transaction_Mixin') 
+                    || $new->isa('FS::payinfo_Mixin') )
+                && $new->payby
+                && !grep { $new->payby eq $_ } @encrypt_payby;
       $saved->{$field} = $new->getfield($field);
       $new->setfield($field, $new->encrypt($new->getfield($field)));
     }
@@ -1285,7 +1316,9 @@ sub replace {
                    ? ($_, $new->getfield($_)) : () } $old->fields;
                    
   unless (keys(%diff) || $no_update_diff ) {
-    carp "[warning]$me $new -> replace $old: records identical"
+    carp "[warning]$me ". ref($new)."->replace ".
+           ( $primary_key ? "$primary_key ".$new->get($primary_key) : '' ).
+         ": records identical"
       unless $nowarn_identical;
     return '';
   }
@@ -1580,6 +1613,7 @@ sub process_batch_import {
     format_headers             => $opt->{format_headers},
     format_sep_chars           => $opt->{format_sep_chars},
     format_fixedlength_formats => $opt->{format_fixedlength_formats},
+    format_xml_formats         => $opt->{format_xml_formats},
     format_row_callbacks       => $opt->{format_row_callbacks},
     #per-import
     job                        => $job,
@@ -1589,6 +1623,7 @@ sub process_batch_import {
     params                     => { map { $_ => $param->{$_} } @pass_params },
     #?
     default_csv                => $opt->{default_csv},
+    postinsert_callback        => $opt->{postinsert_callback},
   );
 
   if ( $opt->{'batch_namecol'} ) {
@@ -1611,6 +1646,8 @@ Class method for batch imports.  Available params:
 
 =item table
 
+=item format - usual way to specify import, with this format string selecting data from the formats and format_* info hashes
+
 =item formats
 
 =item format_types
@@ -1623,6 +1660,12 @@ Class method for batch imports.  Available params:
 
 =item format_row_callbacks
 
+=item fields - Alternate way to specify import, specifying import fields directly as a listref
+
+=item preinsert_callback
+
+=item postinsert_callback
+
 =item params
 
 =item job
@@ -1633,9 +1676,7 @@ FS::queue object, will be updated with progress
 
 =item type
 
-csv, xls or fixedlength
-
-=item format
+csv, xls, fixedlength, xml
 
 =item empty_ok
 
@@ -1647,21 +1688,74 @@ sub batch_import {
   my $param = shift;
 
   warn "$me batch_import call with params: \n". Dumper($param)
-  ;#  if $DEBUG;
+    if $DEBUG;
 
   my $table   = $param->{table};
-  my $formats = $param->{formats};
 
   my $job     = $param->{job};
   my $file    = $param->{file};
-  my $format  = $param->{'format'};
   my $params  = $param->{params} || {};
 
-  die "unknown format $format" unless exists $formats->{ $format };
+  my( $type, $header, $sep_char, $fixedlength_format, 
+      $xml_format, $row_callback, @fields );
+
+  my $postinsert_callback = '';
+  $postinsert_callback = $param->{'postinsert_callback'}
+	  if $param->{'postinsert_callback'};
+  my $preinsert_callback = '';
+  $preinsert_callback = $param->{'preinsert_callback'}
+	  if $param->{'preinsert_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';
+
 
-  my $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'} };
+
+  } else {
+    die "neither format nor fields specified";
+  }
+
+  #my $file    = $param->{file};
 
   unless ( $type ) {
     if ( $file =~ /\.(\w+)$/i ) {
@@ -1675,25 +1769,6 @@ sub batch_import {
       if $param->{'default_csv'} && $type ne 'xls';
   }
 
-  my $header = $param->{'format_headers'}
-                 ? $param->{'format_headers'}{ $param->{'format'} }
-                 : 0;
-
-  my $sep_char = $param->{'format_sep_chars'}
-                   ? $param->{'format_sep_chars'}{ $param->{'format'} }
-                   : ',';
-
-  my $fixedlength_format =
-    $param->{'format_fixedlength_formats'}
-      ? $param->{'format_fixedlength_formats'}{ $param->{'format'} }
-      : '';
-
-  my $row_callback =
-    $param->{'format_row_callbacks'}
-      ? $param->{'format_row_callbacks'}{ $param->{'format'} }
-      : '';
-
-  my @fields = @{ $formats->{ $format } };
 
   my $row = 0;
   my $count;
@@ -1711,9 +1786,10 @@ sub batch_import {
 
       eval "use Parse::FixedLength;";
       die $@ if $@;
-      $parser = new Parse::FixedLength $fixedlength_format;
- 
-    } else {
+      $parser = Parse::FixedLength->new($fixedlength_format);
+
+    }
+    else {
       die "Unknown file type $type\n";
     }
 
@@ -1739,7 +1815,22 @@ sub batch_import {
     $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";
   }
@@ -1757,6 +1848,7 @@ sub batch_import {
   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'};
 
@@ -1774,7 +1866,8 @@ sub batch_import {
 
     $params->{ $batch_col } = $batch_value;
   }
-  
+
+  #my $job     = $param->{job};
   my $line;
   my $imported = 0;
   my( $last, $min_sec ) = ( time, 5 ); #progressbar foo
@@ -1789,15 +1882,20 @@ sub batch_import {
       next if $line =~ /^\s*$/; #skip empty lines
 
       $line = &{$row_callback}($line) if $row_callback;
+      
+      next if $line =~ /^\s*$/; #skip empty lines
 
       $parser->parse($line) or do {
         $dbh->rollback if $oldAutoCommit;
-        return "can't parse: ". $parser->error_input();
+        return "can't parse: ". $parser->error_input() . " " . $parser->error_diag;
       };
       @columns = $parser->fields();
 
     } elsif ( $type eq 'fixedlength' ) {
 
+      last unless scalar(@buffer);
+      $line = shift(@buffer);
+
       @columns = $parser->parse($line);
 
     } elsif ( $type eq 'xls' ) {
@@ -1811,6 +1909,11 @@ sub batch_import {
       #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";
     }
@@ -1832,6 +1935,7 @@ sub batch_import {
 
     }
 
+    #my $table   = $param->{table};
     my $class = "FS::$table";
 
     my $record = $class->new( \%hash );
@@ -1840,11 +1944,27 @@ sub batch_import {
     while ( scalar(@later) ) {
       my $sub = shift @later;
       my $data = shift @later;
-      &{$sub}($record, $data, $conf, $param); # $record->&{$sub}($data, $conf);
+      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} );
 
+    if ( $preinsert_callback ) {
+      my $error = &{$preinsert_callback}($record, $param);
+      if ( $error ) {
+        $dbh->rollback if $oldAutoCommit;
+        return "preinsert_callback error". ( $line ? " for $line" : '' ).
+               ": $error";
+      }
+      next if exists $param->{skiprow} && $param->{skiprow};
+    }
+
     my $error = $record->insert;
 
     if ( $error ) {
@@ -1855,6 +1975,15 @@ sub batch_import {
     $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;
@@ -1862,9 +1991,12 @@ sub batch_import {
 
   }
 
-  $dbh->commit or die $dbh->errstr if $oldAutoCommit;;
+  unless ( $imported || $param->{empty_ok} ) {
+    $dbh->rollback if $oldAutoCommit;
+    return "Empty file!";
+  }
 
-  return "Empty file!" unless $imported || $param->{empty_ok};
+  $dbh->commit or die $dbh->errstr if $oldAutoCommit;;
 
   ''; #no error
 
@@ -1883,7 +2015,7 @@ sub _h_statement {
   ;
 
   # If we're encrypting then don't store the payinfo in the history
-  if ( $conf && $conf->exists('encryption') ) {
+  if ( $conf && $conf->exists('encryption') && $self->table ne 'banned_pay' ) {
     @fields = grep { $_ ne 'payinfo' } @fields;
   }
 
@@ -2121,7 +2253,7 @@ sub ut_text {
   #warn "notexist ". \&notexist. "\n";
   #warn "AUTOLOAD ". \&AUTOLOAD. "\n";
   $self->getfield($field)
-    =~ /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=\[\]\<\>]+)$/
+    =~ /^([\wô \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=\[\]\<\>$money_char]+)$/
       or return gettext('illegal_or_empty_text'). " $field: ".
                  $self->getfield($field);
   $self->setfield($field,$1);
@@ -2131,18 +2263,15 @@ sub ut_text {
 =item ut_textn COLUMN
 
 Check/untaint text.  Alphanumerics, spaces, and the following punctuation
-symbols are currently permitted: ! @ # $ % & ( ) - + ; : ' " , . ? /
+symbols are currently permitted: ! @ # $ % & ( ) - + ; : ' " , . ? / = [ ] < >
 May be null.  If there is an error, returns the error, otherwise returns false.
 
 =cut
 
 sub ut_textn {
   my($self,$field)=@_;
-  $self->getfield($field)
-    =~ /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=\[\]]*)$/
-      or return gettext('illegal_text'). " $field: ". $self->getfield($field);
-  $self->setfield($field,$1);
-  '';
+  return $self->setfield($field, '') if $self->getfield($field) =~ /^$/;
+  $self->ut_text($field);
 }
 
 =item ut_alpha COLUMN
@@ -2265,6 +2394,42 @@ sub ut_hexn {
   $self->setfield($field, uc($1));
   '';
 }
+
+=item ut_mac_addr COLUMN
+
+Check/untaint mac addresses.  May be null.
+
+=cut
+
+sub ut_mac_addr {
+  my($self, $field) = @_;
+
+  my $mac = $self->get($field);
+  $mac =~ s/\s+//g;
+  $mac =~ s/://g;
+  $self->set($field, $mac);
+
+  my $e = $self->ut_hex($field);
+  return $e if $e;
+
+  return "Illegal (mac address) $field: ". $self->getfield($field)
+    unless length($self->getfield($field)) == 12;
+
+  '';
+
+}
+
+=item ut_mac_addrn COLUMN
+
+Check/untaint mac addresses.  May be null.
+
+=cut
+
+sub ut_mac_addrn {
+  my($self, $field) = @_;
+  ($self->getfield($field) eq '') ? '' : $self->ut_mac_addr($field);
+}
+
 =item ut_ip COLUMN
 
 Check/untaint ip addresses.  IPv4 only for now, though ::1 is auto-translated
@@ -2299,6 +2464,35 @@ sub ut_ipn {
   }
 }
 
+=item ut_ip46 COLUMN
+
+Check/untaint IPv4 or IPv6 address.
+
+=cut
+
+sub ut_ip46 {
+  my( $self, $field ) = @_;
+  my $ip = NetAddr::IP->new($self->getfield($field))
+    or return "Illegal (IP address) $field: ".$self->getfield($field);
+  $self->setfield($field, lc($ip->addr));
+  return '';
+}
+
+=item ut_ip46n
+
+Check/untaint IPv6 or IPv6 address.  May be null.
+
+=cut
+
+sub ut_ip46n {
+  my( $self, $field ) = @_;
+  if ( $self->getfield($field) =~ /^$/ ) {
+    $self->setfield($field, '');
+    return '';
+  }
+  $self->ut_ip46($field);
+}
+
 =item ut_coord COLUMN [ LOWER [ UPPER ] ]
 
 Check/untaint coordinates.
@@ -2324,11 +2518,17 @@ for lower and upper bounds, respectively.
 =cut
 
 sub ut_coord {
-
   my ($self, $field) = (shift, shift);
 
-  my $lower = shift if scalar(@_);
-  my $upper = shift if scalar(@_);
+  my($lower, $upper);
+  if ( $field =~ /latitude/ ) {
+    $lower = $lat_lower;
+    $upper = 90;
+  } elsif ( $field =~ /longitude/ ) {
+    $lower = -180;
+    $upper = $lon_upper;
+  }
+
   my $coord = $self->getfield($field);
   my $neg = $coord =~ s/^(-)//;
 
@@ -2376,7 +2576,7 @@ sub ut_coordn {
 
   my ($self, $field) = (shift, shift);
 
-  if ($self->getfield($field) =~ /^$/) {
+  if ($self->getfield($field) =~ /^\s*$/) {
     return '';
   } else {
     return $self->ut_coord($field, @_);
@@ -2411,6 +2611,7 @@ May not be null.
 
 sub ut_name {
   my( $self, $field ) = @_;
+#  warn "ut_name allowed alphanumerics: +(sort grep /\w/, map { chr() } 0..255), "\n";
   $self->getfield($field) =~ /^([\w \,\.\-\']+)$/
     or return gettext('illegal_name'). " $field: ". $self->getfield($field);
   $self->setfield($field,$1);
@@ -2450,7 +2651,7 @@ sub ut_zip {
     {
       $self->setfield($field,'');
     } else {
-      $self->getfield($field) =~ /^\s*(\w[\w\-\s]{2,8}\w)\s*$/
+      $self->getfield($field) =~ /^\s*(\w[\w\-\s]{0,8}\w)\s*$/
         or return gettext('illegal_zip'). " $field: ". $self->getfield($field);
       $self->setfield($field,$1);
     }
@@ -2512,6 +2713,20 @@ sub ut_enum {
   return "Illegal (enum) field $field: ". $self->getfield($field);
 }
 
+=item ut_enumn COLUMN CHOICES_ARRAYREF
+
+Like ut_enum, except the null value is also allowed.
+
+=cut
+
+sub ut_enumn {
+  my( $self, $field, $choices ) = @_;
+  $self->getfield($field)
+    ? $self->ut_enum($field, $choices)
+    : '';
+}
+
+
 =item ut_foreign_key COLUMN FOREIGN_TABLE FOREIGN_COLUMN
 
 Check/untaint a foreign column key.  Call a regular ut_ method (like ut_number)
@@ -2818,7 +3033,8 @@ sub scalar_sql {
   my $sth = dbh->prepare($sql) or die dbh->errstr;
   $sth->execute(@_)
     or die "Unexpected error executing statement $sql: ". $sth->errstr;
-  my $scalar = $sth->fetchrow_arrayref->[0];
+  my $row = $sth->fetchrow_arrayref or return '';
+  my $scalar = $row->[0];
   defined($scalar) ? $scalar : '';
 }
 
@@ -3007,6 +3223,29 @@ sub not_regexp_sql {
 
 }
 
+=item concat_sql [ DRIVER_NAME ] ITEMS_ARRAYREF
+
+Returns the items concatendated based on database type, using "CONCAT()" for
+mysql and " || " for Pg and other databases.
+
+You can pass an optional driver name such as "Pg", "mysql" or
+$dbh->{Driver}->{Name} to return a function for that database instead of
+the current database.
+
+=cut
+
+sub concat_sql {
+  my $driver = ref($_[0]) ? driver_name : shift;
+  my $items = shift;
+
+  if ( $driver =~ /^mysql/i ) {
+    'CONCAT('. join(',', @$items). ')';
+  } else {
+    join('||', @$items);
+  }
+
+}
+
 =back
 
 =head1 BUGS