X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FRecord.pm;h=4e0cf8efaec755709ee1bbb0a890a3e1e80a2d97;hb=e310ed83422fee8511df926141a7606676ff1331;hp=7b52f508eceada4737b1d6ed4beb26c938a96f00;hpb=b65b8096089410001dfbcd35f9a56f9405b9f5f1;p=freeside.git diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm index 7b52f508e..4e0cf8efa 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 + $no_update_diff $no_history $no_check_foreign + @encrypt_payby ); use Exporter; use Carp qw(carp cluck croak confess); @@ -30,10 +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 concat_sql + midnight_sql ); $DEBUG = 0; @@ -42,6 +47,7 @@ $me = '[FS::Record]'; $nowarn_identical = 0; $nowarn_classload = 0; $no_update_diff = 0; +$no_history = 0; $no_check_foreign = 0; my $rsa_module; @@ -52,17 +58,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 @@ -268,7 +282,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 }; @@ -531,6 +545,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))); } @@ -645,6 +664,8 @@ sub get_real_fields { qq-( $column $op "" )-; } } + } elsif ( $op eq '!=' ) { + qq-( $column IS NULL OR $column != ? )-; #if this needs to be re-enabled, it needs to use a custom op like #"APPROX=" or something (better name?, not '=', to avoid affecting other # searches @@ -966,10 +987,12 @@ sub insert { my $error = $self->check; return $error if $error; - #single-field unique keys are given a value if false + #single-field non-null unique keys are given a value if empty #(like MySQL's AUTO_INCREMENT or Pg SERIAL) foreach ( $self->dbdef_table->unique_singles) { - $self->unique($_) unless $self->getfield($_); + next if $self->getfield($_); + next if $self->dbdef_table->column($_)->null eq 'NULL'; + $self->unique($_); } #and also the primary key, if the database isn't going to @@ -999,7 +1022,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))); } } @@ -1126,7 +1154,7 @@ sub insert { my $h_sth; - if ( defined dbdef->table('h_'. $table) ) { + if ( defined( dbdef->table('h_'. $table) ) && ! $no_history ) { my $h_statement = $self->_h_statement('insert'); warn "[debug]$me $h_statement\n" if $DEBUG > 2; $h_sth = dbh->prepare($h_statement) or do { @@ -1279,6 +1307,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))); } @@ -1289,7 +1322,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 ''; } @@ -1633,6 +1668,8 @@ Class method for batch imports. Available params: =item fields - Alternate way to specify import, specifying import fields directly as a listref +=item preinsert_callback + =item postinsert_callback =item params @@ -1667,9 +1704,14 @@ sub batch_import { 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'}; @@ -1742,7 +1784,7 @@ sub batch_import { if ( $type eq 'csv' ) { - my %attr = (); + my %attr = ( 'binary' => 1, ); $attr{sep_char} = $sep_char if $sep_char; $parser = new Text::CSV_XS \%attr; @@ -1750,7 +1792,7 @@ sub batch_import { eval "use Parse::FixedLength;"; die $@ if $@; - $parser = new Parse::FixedLength $fixedlength_format; + $parser = Parse::FixedLength->new($fixedlength_format); } else { @@ -1846,15 +1888,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' ) { @@ -1914,6 +1961,16 @@ sub batch_import { } 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 ) { @@ -1964,7 +2021,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; } @@ -2163,11 +2220,18 @@ is an error, returns the error, otherwise returns false. 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); + + if ( $self->getfield($field) eq '' ) { + $self->setfield($field, 0); + } elsif ( $self->getfield($field) =~ /^\s*(\-)?\s*(\d*)(\.\d{1})\s*$/ ) { + #handle one decimal place without barfing out + $self->setfield($field, ( ($1||''). ($2||''). ($3.'0') ) || 0); + } elsif ( $self->getfield($field) =~ /^\s*(\-)?\s*(\d*)(\.\d{2})?\s*$/ ) { + $self->setfield($field, ( ($1||''). ($2||''). ($3||'') ) || 0); + } else { + return "Illegal (money) $field: ". $self->getfield($field); + } + ''; } @@ -2202,7 +2266,7 @@ sub ut_text { #warn "notexist ". \¬exist. "\n"; #warn "AUTOLOAD ". \&AUTOLOAD. "\n"; $self->getfield($field) - =~ /^([µ_0123456789aAáÁàÀâÂåÅäÄãêæÆbBcCçÇdDðÐeEéÉèÈêÊëËfFgGhHiIíÍìÌîÎïÏjJkKlLmMnNñÑoOóÓòÒôÔöÖõÕøغpPqQrRsSßtTuUúÚùÙûÛüÜvVwWxXyYýÝÿzZþÞ \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=\[\]\<\>]+)$/ + =~ /^([\wô \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=\[\]\<\>$money_char]+)$/ or return gettext('illegal_or_empty_text'). " $field: ". $self->getfield($field); $self->setfield($field,$1); @@ -2212,7 +2276,7 @@ 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 @@ -2343,6 +2407,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 @@ -2431,11 +2531,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/^(-)//; @@ -2483,7 +2589,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, @_); @@ -2519,13 +2625,31 @@ 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 \,\.\-\']+)$/ - $self->getfield($field) =~ /^([µ_0123456789aAáÁàÀâÂåÅäÄãêæÆbBcCçÇdDðÐeEéÉèÈêÊëËfFgGhHiIíÍìÌîÎïÏjJkKlLmMnNñÑoOóÓòÒôÔöÖõÕøغpPqQrRsSßtTuUúÚùÙûÛüÜvVwWxXyYýÝÿzZþÞ \,\.\-\']+)$/ + $self->getfield($field) =~ /^([\w \,\.\-\']+)$/ or return gettext('illegal_name'). " $field: ". $self->getfield($field); - $self->setfield($field,$1); + my $name = $1; + $name =~ s/^\s+//; + $name =~ s/\s+$//; + $name =~ s/\s+/ /g; + $self->setfield($field, $name); ''; } +=item ut_namen COLUMN + +Check/untaint proper names; allows alphanumerics, spaces and the following +punctuation: , . - ' + +May not be null. + +=cut + +sub ut_namen { + my( $self, $field ) = @_; + return $self->setfield($field, '') if $self->getfield($field) =~ /^$/; + $self->ut_name($field); +} + =item ut_zip COLUMN Check/untaint zip codes. @@ -2559,7 +2683,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); } @@ -2800,7 +2924,7 @@ You should generally not have to worry about calling this, as the system handles sub encrypt { my ($self, $value) = @_; - my $encrypted; + my $encrypted = $value; if ($conf->exists('encryption')) { if ($self->is_encrypted($value)) { @@ -2946,6 +3070,22 @@ sub scalar_sql { defined($scalar) ? $scalar : ''; } +=item count [ WHERE ] + +Convenience method for the common case of "SELECT COUNT(*) FROM table", +with optional WHERE. Must be called as method on a class with an +associated table. + +=cut + +sub count { + my($self, $where) = (shift, shift); + my $table = $self->table or die 'count called on object of class '.ref($self); + my $sql = "SELECT COUNT(*) FROM $table"; + $sql .= " WHERE $where" if $where; + $self->scalar_sql($sql); +} + =back =head1 SUBROUTINES @@ -3133,7 +3273,7 @@ sub not_regexp_sql { =item concat_sql [ DRIVER_NAME ] ITEMS_ARRAYREF -Returns the items concatendated based on database type, using "CONCAT()" for +Returns the items concatenated 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 @@ -3154,6 +3294,24 @@ sub concat_sql { } +=item midnight_sql DATE + +Returns an SQL expression to convert DATE (a unix timestamp) to midnight +on that day in the system timezone, using the default driver name. + +=cut + +sub midnight_sql { + my $driver = driver_name; + my $expr = shift; + if ( $driver =~ /^mysql/i ) { + "UNIX_TIMESTAMP(DATE(FROM_UNIXTIME($expr)))"; + } + else { + "EXTRACT( EPOCH FROM DATE(TO_TIMESTAMP($expr)) )"; + } +} + =back =head1 BUGS