X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FRecord.pm;h=a667f4e7e1ec8ed511c367f88d304abf7d7f1d8a;hp=e4bc6c562bc4d211a8b7bef05f031f1cefc8e545;hb=7516e3da0f17eeecba27219ef96a8b5f46af2083;hpb=fb4ab1073f0d15d660c6cdc4e07afebf68ef3924 diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm index e4bc6c562..a667f4e7e 100644 --- a/FS/FS/Record.pm +++ b/FS/FS/Record.pm @@ -1,61 +1,58 @@ package FS::Record; +use base qw( Exporter ); use strict; -use vars qw( $AUTOLOAD @ISA @EXPORT_OK $DEBUG - %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 vars qw( $AUTOLOAD + %virtual_fields_cache %fk_method_cache + $money_char $lat_lower $lon_upper ); -use Exporter; use Carp qw(carp cluck croak confess); use Scalar::Util qw( blessed ); +use File::Slurp qw( slurp ); use File::CounterFile; -use Locale::Country; use Text::CSV_XS; -use File::Slurp qw( slurp ); use DBI qw(:sql_types); -use DBIx::DBSchema 0.38; -use FS::UID qw(dbh getotaker datasrc driver_name); +use DBIx::DBSchema 0.43; #0.43 for foreign keys +use Locale::Country; +use Locale::Currency; +use NetAddr::IP; # for validation +use FS::UID qw(dbh datasrc driver_name); use FS::CurrentUser; use FS::Schema qw(dbdef); use FS::SearchCache; use FS::Msgcat qw(gettext); -use NetAddr::IP; # for validation -use Data::Dumper; #use FS::Conf; #dependency loop bs, in install_callback below instead use FS::part_virtual_field; use Tie::IxHash; -@ISA = qw(Exporter); - -@encrypt_payby = qw( CARD DCRD CHEK DCHK ); +our @encrypt_payby = qw( CARD DCRD CHEK DCHK ); #export dbdef for now... everything else expects to find it here -@EXPORT_OK = qw( +our @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; -$me = '[FS::Record]'; +our $DEBUG = 0; +our $me = '[FS::Record]'; + +our $nowarn_identical = 0; +our $nowarn_classload = 0; +our $no_update_diff = 0; +our $no_history = 0; -$nowarn_identical = 0; -$nowarn_classload = 0; -$no_update_diff = 0; -$no_check_foreign = 0; +our $no_check_foreign = 1; #well, not inefficiently in perl by default anymore my $rsa_module; my $rsa_loaded; my $rsa_encrypt; my $rsa_decrypt; -$conf = ''; -$conf_encryption = ''; +our $conf = ''; +our $conf_encryption = ''; FS::UID->install_callback( sub { eval "use FS::Conf;"; @@ -76,6 +73,10 @@ FS::UID->install_callback( sub { eval "sub PG_BYTEA { die 'guru meditation #9: calling PG_BYTEA when not running Pg?'; }"; } + foreach my $table ( dbdef->tables ) { + $fk_method_cache{$table} = fk_methods($table); + } + } ); =head1 NAME @@ -123,6 +124,8 @@ FS::Record - Database record objects $error = $record->ut_floatn('column'); $error = $record->ut_number('column'); $error = $record->ut_numbern('column'); + $error = $record->ut_decimal('column'); + $error = $record->ut_decimaln('column'); $error = $record->ut_snumber('column'); $error = $record->ut_snumbern('column'); $error = $record->ut_money('column'); @@ -366,6 +369,9 @@ sub qsearch { my @bind_type = (); my $dbh = dbh; foreach my $stable ( @stable ) { + + carp '->qsearch on cust_main called' if $stable eq 'cust_main' && $DEBUG; + #stop altering the caller's hashref my $record = { %{ shift(@record) || {} } };#and be liberal in receipt my $select = shift @select; @@ -402,7 +408,6 @@ sub qsearch { push @statement, $statement; warn "[debug]$me $statement\n" if $DEBUG > 1 || $debug; - foreach my $field ( grep defined( $record->{$_} ) && $record->{$_} ne '', @real_fields @@ -457,7 +462,13 @@ sub qsearch { # grep defined( $record->{$_} ) && $record->{$_} ne '', @fields # ) or croak "Error executing \"$statement\": ". $sth->errstr; - $sth->execute or croak "Error executing \"$statement\": ". $sth->errstr; + my $ok = $sth->execute; + if (!$ok) { + my $error = "Error executing \"$statement\""; + $error .= ' (' . join(', ', map {"'$_'"} @value) . ')' if @value; + $error .= ': '. $sth->errstr; + croak $error; + } my $table = $stable[0]; my $pkey = ''; @@ -499,7 +510,7 @@ sub qsearch { # Check for encrypted fields and decrypt them. ## only in the local copy, not the cached object if ( $conf_encryption - && eval 'defined(@FS::'. $table . '::encrypted_fields)' ) { + && eval '@FS::'. $table . '::encrypted_fields' ) { foreach my $record (@return) { foreach my $field (eval '@FS::'. $table . '::encrypted_fields') { next if $field eq 'payinfo' @@ -522,6 +533,215 @@ sub qsearch { return @return; } +=item _query + +Construct the SQL statement and parameter-binding list for qsearch. Takes +the qsearch parameters. + +Returns a hash containing: +'table': The primary table name (if there is one). +'statement': The SQL statement itself. +'bind_type': An arrayref of bind types. +'value': An arrayref of parameter values. +'cache': The cache object, if one was passed. + +=cut + +sub _query { + my( @stable, @record, @cache ); + my( @select, @extra_sql, @extra_param, @order_by, @addl_from ); + my @debug = (); + my $cursor = ''; + my %union_options = (); + if ( ref($_[0]) eq 'ARRAY' ) { + my $optlist = shift; + %union_options = @_; + foreach my $href ( @$optlist ) { + push @stable, ( $href->{'table'} or die "table name is required" ); + push @record, ( $href->{'hashref'} || {} ); + push @select, ( $href->{'select'} || '*' ); + push @extra_sql, ( $href->{'extra_sql'} || '' ); + push @extra_param, ( $href->{'extra_param'} || [] ); + push @order_by, ( $href->{'order_by'} || '' ); + push @cache, ( $href->{'cache_obj'} || '' ); + push @addl_from, ( $href->{'addl_from'} || '' ); + push @debug, ( $href->{'debug'} || '' ); + } + die "at least one hashref is required" unless scalar(@stable); + } elsif ( ref($_[0]) eq 'HASH' ) { + my $opt = shift; + $stable[0] = $opt->{'table'} or die "table name is required"; + $record[0] = $opt->{'hashref'} || {}; + $select[0] = $opt->{'select'} || '*'; + $extra_sql[0] = $opt->{'extra_sql'} || ''; + $extra_param[0] = $opt->{'extra_param'} || []; + $order_by[0] = $opt->{'order_by'} || ''; + $cache[0] = $opt->{'cache_obj'} || ''; + $addl_from[0] = $opt->{'addl_from'} || ''; + $debug[0] = $opt->{'debug'} || ''; + } else { + ( $stable[0], + $record[0], + $select[0], + $extra_sql[0], + $cache[0], + $addl_from[0] + ) = @_; + $select[0] ||= '*'; + } + my $cache = $cache[0]; + + my @statement = (); + my @value = (); + my @bind_type = (); + + my $result_table = $stable[0]; + foreach my $stable ( @stable ) { + #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; + my $order_by = shift @order_by; + my $cache = shift @cache; + my $addl_from = shift @addl_from; + my $debug = shift @debug; + + #$stable =~ /^([\w\_]+)$/ or die "Illegal table: $table"; + #for jsearch + $stable =~ /^([\w\s\(\)\.\,\=]+)$/ or die "Illegal table: $stable"; + $stable = $1; + + $result_table = '' if $result_table ne $stable; + + my $table = $cache ? $cache->table : $stable; + my $dbdef_table = dbdef->table($table) + or die "No schema for table $table found - ". + "do you need to run freeside-upgrade?"; + my $pkey = $dbdef_table->primary_key; + + my @real_fields = grep exists($record->{$_}), real_fields($table); + + my $statement .= "SELECT $select FROM $stable"; + $statement .= " $addl_from" if $addl_from; + if ( @real_fields ) { + $statement .= ' WHERE '. join(' AND ', + get_real_fields($table, $record, \@real_fields)); + } + + $statement .= " $extra_sql" if defined($extra_sql); + $statement .= " $order_by" if defined($order_by); + + push @statement, $statement; + + warn "[debug]$me $statement\n" if $DEBUG > 1 || $debug; + + + foreach my $field ( + grep defined( $record->{$_} ) && $record->{$_} ne '', @real_fields + ) { + + my $value = $record->{$field}; + my $op = (ref($value) && $value->{op}) ? $value->{op} : '='; + $value = $value->{'value'} if ref($value); + my $type = dbdef->table($table)->column($field)->type; + + my $bind_type = _bind_type($type, $value); + + #if ( $DEBUG > 2 ) { + # no strict 'refs'; + # %TYPE = map { &{"DBI::$_"}() => $_ } @{ $DBI::EXPORT_TAGS{sql_types} } + # unless keys %TYPE; + # warn " bind_param $bind (for field $field), $value, TYPE $TYPE{$TYPE}\n"; + #} + + push @value, $value; + push @bind_type, $bind_type; + + } + + foreach my $param ( @$extra_param ) { + my $bind_type = { TYPE => SQL_VARCHAR }; + my $value = $param; + if ( ref($param) ) { + $value = $param->[0]; + my $type = $param->[1]; + $bind_type = _bind_type($type, $value); + } + push @value, $value; + push @bind_type, $bind_type; + } + } + + my $statement = join( ' ) UNION ( ', @statement ); + $statement = "( $statement )" if scalar(@statement) > 1; + $statement .= " $union_options{order_by}" if $union_options{order_by}; + + return { + statement => $statement, + bind_type => \@bind_type, + value => \@value, + table => $result_table, + cache => $cache, + }; +} + +# qsearch should eventually use this +sub _from_hashref { + my ($table, $cache, @hashrefs) = @_; + my @return; + # XXX get rid of these string evals at some point + # (when we have time to test it) + # my $class = "FS::$table" if $table; + # if ( $class and $class->isa('FS::Record') ) + # if ( $class->can('new') eq \&new ) + # + if ( $table && eval 'scalar(@FS::'. $table. '::ISA);' ) { + if ( eval 'FS::'. $table. '->can(\'new\')' eq \&new ) { + #derivied class didn't override new method, so this optimization is safe + if ( $cache ) { + @return = map { + new_or_cached( "FS::$table", { %{$_} }, $cache ) + } @hashrefs; + } else { + @return = map { + new( "FS::$table", { %{$_} } ) + } @hashrefs; + } + } else { + #okay, its been tested + # warn "untested code (class FS::$table uses custom new method)"; + @return = map { + eval 'FS::'. $table. '->new( { %{$_} } )'; + } @hashrefs; + } + + # Check for encrypted fields and decrypt them. + ## only in the local copy, not the cached object + if ( $conf_encryption + && eval '@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))); + } + } + } + } else { + cluck "warning: FS::$table not loaded; returning FS::Record objects" + unless $nowarn_classload; + @return = map { + FS::Record->new( $table, { %{$_} } ); + } @hashrefs; + } + return @return; +} + ## makes this easier to read sub get_real_fields { @@ -750,6 +970,11 @@ $record->column is a synonym for $record->get('column'); $record->column('value') is a synonym for $record->set('column','value'); +$record->foreign_table_name calls qsearchs and returns a single +FS::foreign_table record (for tables referenced by a column of this table) or +qsearch and returns an array of FS::foreign_table records (for tables +referenced by a column in the foreign table). + =cut # readable/safe @@ -757,18 +982,46 @@ sub AUTOLOAD { my($self,$value)=@_; my($field)=$AUTOLOAD; $field =~ s/.*://; + + confess "errant AUTOLOAD $field for $self (arg $value)" + unless blessed($self) && $self->can('setfield'); + + #$fk_method_cache{$self->table} ||= fk_methods($self->table); + if ( exists($fk_method_cache{$self->table}->{$field}) ) { + + my $fk_info = $fk_method_cache{$self->table}->{$field}; + my $method = $fk_info->{method} || 'qsearchs'; + my $table = $fk_info->{table} || $field; + my $column = $fk_info->{column}; + my $foreign_column = $fk_info->{references} || $column; + + eval "use FS::$table"; + die $@ if $@; + + carp '->cust_main called' if $table eq 'cust_main' && $DEBUG; + + my $pkey_value = $self->$column(); + my %search = ( $foreign_column => $pkey_value ); + + # FS::Record->$method() ? they're actually just subs :/ + if ( $method eq 'qsearchs' ) { + return $pkey_value ? qsearchs( $table, \%search ) : ''; + } elsif ( $method eq 'qsearch' ) { + return $pkey_value ? qsearch( $table, \%search ) : (); + } else { + die "unknown method $method"; + } + + } + if ( defined($value) ) { - confess "errant AUTOLOAD $field for $self (arg $value)" - unless blessed($self) && $self->can('setfield'); $self->setfield($field,$value); } else { - confess "errant AUTOLOAD $field for $self (no args)" - unless blessed($self) && $self->can('getfield'); $self->getfield($field); } } -# efficient +# efficient (also, old, doesn't support FK stuff) #sub AUTOLOAD { # my $field = $AUTOLOAD; # $field =~ s/.*://; @@ -779,6 +1032,78 @@ sub AUTOLOAD { # } #} +sub fk_methods { + my $table = shift; + + my %hash = (); + + # foreign keys we reference in other tables + foreach my $fk (dbdef->table($table)->foreign_keys) { + + my $method = ''; + if ( scalar( @{$fk->columns} ) == 1 ) { + if ( ! defined($fk->references) + || ! @{$fk->references} + || $fk->columns->[0] eq $fk->references->[0] + ) { + $method = $fk->table; + } else { + #some sort of hint in the table.pm or schema for methods not named + # after their foreign table (well, not a whole lot different than + # just providing a small subroutine...) + } + + if ( $method ) { + $hash{$method} = { #fk_info + 'method' => 'qsearchs', + 'column' => $fk->columns->[0], + #'references' => $fk->references->[0], + }; + } + + } + + } + + # foreign keys referenced in other tables to us + # (alas. why we're cached. still, might this loop better be done once at + # schema load time insetad of every time we AUTOLOAD a method on a new + # class?) + foreach my $f_table ( dbdef->tables ) { + foreach my $fk (dbdef->table($f_table)->foreign_keys) { + + next unless $fk->table eq $table; + + my $method = ''; + if ( scalar( @{$fk->columns} ) == 1 ) { + if ( ! defined($fk->references) + || ! @{$fk->references} + || $fk->columns->[0] eq $fk->references->[0] + ) { + $method = $f_table; + } else { + #some sort of hint in the table.pm or schema for methods not named + # after their foreign table (well, not a whole lot different than + # just providing a small subroutine...) + } + + if ( $method ) { + $hash{$method} = { #fk_info + 'method' => 'qsearch', + 'column' => $fk->columns->[0], #references||column + #'references' => $fk->column->[0], + }; + } + + } + + } + + } + + \%hash; +} + =item hash Returns a list of the column/value pairs, usually for assigning to a new hash. @@ -809,6 +1134,27 @@ sub hashref { $self->{'Hash'}; } +#fallbacks/generics + +sub API_getinfo { + my $self = shift; + +{ ( map { $_=>$self->$_ } $self->fields ), + }; +} + +sub API_insert { + my( $class, %opt ) = @_; + my $table = $class->table; + my $self = $class->new( { map { $_ => $opt{$_} } fields($table) } ); + my $error = $self->insert; + return +{ 'error' => $error } if $error; + my $pkey = $self->pkey; + return +{ 'error' => '', + 'primary_key' => $pkey, + $pkey => $self->$pkey, + }; +} + =item modified Returns true if any of this object's values have been modified with set (or via @@ -900,10 +1246,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 @@ -1037,7 +1385,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 { @@ -1302,7 +1650,7 @@ sub rep { =item check Checks custom fields. Subclasses should still provide a check method to validate -non-custom fields, foreign keys, etc., and call this method via $self->SUPER::check. +non-custom fields, etc., and call this method via $self->SUPER::check. =cut @@ -1418,17 +1766,14 @@ files. Currently only supports a single file named "file". =cut -use Storable qw(thaw); use Data::Dumper; -use MIME::Base64; sub process_batch_import { - my($job, $opt) = ( shift, shift ); + my($job, $opt, $param) = @_; my $table = $opt->{table}; my @pass_params = $opt->{params} ? @{ $opt->{params} } : (); my %formats = %{ $opt->{formats} }; - my $param = thaw(decode_base64(shift)); warn Dumper($param) if $DEBUG; my $files = $param->{'uploaded_files'} @@ -1448,6 +1793,7 @@ sub process_batch_import { format_sep_chars => $opt->{format_sep_chars}, format_fixedlength_formats => $opt->{format_fixedlength_formats}, format_xml_formats => $opt->{format_xml_formats}, + format_asn_formats => $opt->{format_asn_formats}, format_row_callbacks => $opt->{format_row_callbacks}, #per-import job => $job, @@ -1518,6 +1864,7 @@ csv, xls, fixedlength, xml =cut +use Data::Dumper; sub batch_import { my $param = shift; @@ -1530,8 +1877,12 @@ sub batch_import { my $file = $param->{file}; my $params = $param->{params} || {}; - my( $type, $header, $sep_char, $fixedlength_format, - $xml_format, $row_callback, @fields ); + my $custnum_prefix = $conf->config('cust_main-custnum-display_prefix'); + my $custnum_length = $conf->config('cust_main-custnum-display_length') || 8; + + my( $type, $header, $sep_char, + $fixedlength_format, $xml_format, $asn_format, + $parser_opt, $row_callback, @fields ); my $postinsert_callback = ''; $postinsert_callback = $param->{'postinsert_callback'} @@ -1564,11 +1915,21 @@ sub batch_import { ? $param->{'format_fixedlength_formats'}{ $param->{'format'} } : ''; + $parser_opt = + $param->{'format_parser_opts'} + ? $param->{'format_parser_opts'}{ $param->{'format'} } + : {}; + $xml_format = $param->{'format_xml_formats'} ? $param->{'format_xml_formats'}{ $param->{'format'} } : ''; + $asn_format = + $param->{'format_asn_formats'} + ? $param->{'format_asn_formats'}{ $param->{'format'} } + : ''; + $row_callback = $param->{'format_row_callbacks'} ? $param->{'format_row_callbacks'}{ $param->{'format'} } @@ -1608,22 +1969,22 @@ sub batch_import { my $count; my $parser; my @buffer = (); + my $asn_header_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; + $parser_opt->{'binary'} = 1; + $parser_opt->{'sep_char'} = $sep_char if $sep_char; + $parser = Text::CSV_XS->new($parser_opt); } elsif ( $type eq 'fixedlength' ) { eval "use Parse::FixedLength;"; die $@ if $@; - $parser = Parse::FixedLength->new($fixedlength_format); + $parser = Parse::FixedLength->new($fixedlength_format, $parser_opt); - } - else { + } else { die "Unknown file type $type\n"; } @@ -1649,7 +2010,9 @@ sub batch_import { $count++; $row = $header || 0; + } elsif ( $type eq 'xml' ) { + # FS::pay_batch eval "use XML::Simple;"; die $@ if $@; @@ -1665,6 +2028,26 @@ sub batch_import { $rows = $rows->{$_} foreach @$xmlrow; $rows = [ $rows ] if ref($rows) ne 'ARRAY'; $count = @buffer = @$rows; + + } elsif ( $type eq 'asn.1' ) { + + eval "use Convert::ASN1"; + die $@ if $@; + + my $asn = Convert::ASN1->new; + $asn->prepare( $asn_format->{'spec'} ) or die $asn->error; + + $parser = $asn->find( $asn_format->{'macro'} ) or die $asn->error; + + my $data = slurp($file); + my $asn_output = $parser->decode( $data ) + or return "No ". $asn_format->{'macro'}. " found\n"; + + $asn_header_buffer = &{ $asn_format->{'header_buffer'} }( $asn_output ); + + my $rows = &{ $asn_format->{'arrayref'} }( $asn_output ); + $count = @buffer = @$rows; + } else { die "Unknown file type $type\n"; } @@ -1708,6 +2091,7 @@ sub batch_import { while (1) { my @columns = (); + my %hash = %$params; if ( $type eq 'csv' ) { last unless scalar(@buffer); @@ -1744,16 +2128,27 @@ sub batch_import { #warn $z++. ": $_\n" for @columns; } elsif ( $type eq 'xml' ) { + # $parser = [ 'Column0Key', 'Column1Key' ... ] last unless scalar(@buffer); my $row = shift @buffer; @columns = @{ $row }{ @$parser }; + + } elsif ( $type eq 'asn.1' ) { + + last unless scalar(@buffer); + my $row = shift @buffer; + &{ $asn_format->{row_callback} }( $row, $asn_header_buffer ) + if $asn_format->{row_callback}; + foreach my $key ( keys %{ $asn_format->{map} } ) { + $hash{$key} = &{ $asn_format->{map}{$key} }( $row, $asn_header_buffer ); + } + } else { die "Unknown file type $type\n"; } my @later = (); - my %hash = %$params; foreach my $field ( @fields ) { @@ -1769,6 +2164,11 @@ sub batch_import { } + if ( $custnum_prefix && $hash{custnum} =~ /^$custnum_prefix(0*([1-9]\d*))$/ + && length($1) == $custnum_length ) { + $hash{custnum} = $2; + } + #my $table = $param->{table}; my $class = "FS::$table"; @@ -1830,7 +2230,7 @@ sub batch_import { return "Empty file!"; } - $dbh->commit or die $dbh->errstr if $oldAutoCommit;; + $dbh->commit or die $dbh->errstr if $oldAutoCommit; ''; #no error @@ -1856,9 +2256,13 @@ sub _h_statement { my @values = map { _quote( $self->getfield($_), $self->table, $_) } @fields; "INSERT INTO h_". $self->table. " ( ". - join(', ', qw(history_date history_user history_action), @fields ). + join(', ', qw(history_date history_usernum history_action), @fields ). ") VALUES (". - join(', ', $time, dbh->quote(getotaker()), dbh->quote($action), @values). + join(', ', $time, + $FS::CurrentUser::CurrentUser->usernum, + dbh->quote($action), + @values + ). ")" ; } @@ -1889,11 +2293,6 @@ sub unique { #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 } ); @@ -2039,6 +2438,35 @@ sub ut_numbern { ''; } +=item ut_decimal COLUMN[, DIGITS] + +Check/untaint decimal numbers (up to DIGITS decimal places. If there is an +error, returns the error, otherwise returns false. + +=item ut_decimaln COLUMN[, DIGITS] + +Check/untaint decimal numbers. May be null. If there is an error, returns +the error, otherwise returns false. + +=cut + +sub ut_decimal { + my($self, $field, $digits) = @_; + $digits ||= ''; + $self->getfield($field) =~ /^\s*(\d+(\.\d{0,$digits})?)\s*$/ + or return "Illegal or empty (decimal) $field: ".$self->getfield($field); + $self->setfield($field, $1); + ''; +} + +sub ut_decimaln { + my($self, $field, $digits) = @_; + $self->getfield($field) =~ /^\s*(\d*(\.\d{0,$digits})?)\s*$/ + or return "Illegal (decimal) $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 @@ -2048,11 +2476,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); + } + ''; } @@ -2072,6 +2507,41 @@ sub ut_moneyn { $self->ut_money($field); } +=item ut_currencyn COLUMN + +Check/untaint currency indicators, such as USD or EUR. May be null. If there +is an error, returns the error, otherwise returns false. + +=cut + +sub ut_currencyn { + my($self, $field) = @_; + if ($self->getfield($field) eq '') { #can be null + $self->setfield($field, ''); + return ''; + } + $self->ut_currency($field); +} + +=item ut_currency COLUMN + +Check/untaint currency indicators, such as USD or EUR. May not be null. If +there is an error, returns the error, otherwise returns false. + +=cut + +sub ut_currency { + my($self, $field) = @_; + my $value = uc( $self->getfield($field) ); + if ( code2currency($value) ) { + $self->setfield($value); + } else { + return "Unknown currency $value"; + } + + ''; +} + =item ut_text COLUMN Check/untaint text. Alphanumerics, spaces, and the following punctuation @@ -2086,8 +2556,10 @@ sub ut_text { #warn "msgcat ". \&msgcat. "\n"; #warn "notexist ". \¬exist. "\n"; #warn "AUTOLOAD ". \&AUTOLOAD. "\n"; + # \p{Word} = alphanumerics, marks (diacritics), and connectors + # see perldoc perluniprops $self->getfield($field) - =~ /^([\wô \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=\[\]\<\>$money_char]+)$/ + =~ /^([\p{Word} \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=\[\]\<\>$money_char]+)$/ or return gettext('illegal_or_empty_text'). " $field: ". $self->getfield($field); $self->setfield($field,$1); @@ -2174,8 +2646,8 @@ sub ut_alpha_lower { Check/untaint phone numbers. May be null. If there is an error, returns the error, otherwise returns false. -Takes an optional two-letter ISO country code; without it or with unsupported -countries, ut_phonen simply calls ut_alphan. +Takes an optional two-letter ISO 3166-1 alpha-2 country code; without +it or with unsupported countries, ut_phonen simply calls ut_alphan. =cut @@ -2418,10 +2890,9 @@ sub ut_coordn { } - =item ut_domain COLUMN -Check/untaint host and domain names. +Check/untaint host and domain names. May not be null. =cut @@ -2429,11 +2900,27 @@ sub ut_domain { my( $self, $field ) = @_; #$self->getfield($field) =~/^(\w+\.)*\w+$/ $self->getfield($field) =~/^(([\w\-]+\.)*\w+)$/ - or return "Illegal (domain) $field: ". $self->getfield($field); + or return "Illegal (hostname) $field: ". $self->getfield($field); $self->setfield($field,$1); ''; } +=item ut_domainn COLUMN + +Check/untaint host and domain names. May be null. + +=cut + +sub ut_domainn { + my( $self, $field ) = @_; + if ( $self->getfield($field) =~ /^()$/ ) { + $self->setfield($field,''); + ''; + } else { + $self->ut_domain($field); + } +} + =item ut_name COLUMN Check/untaint proper names; allows alphanumerics, spaces and the following @@ -2446,12 +2933,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) =~ /^([\p{Word} \,\.\-\']+)$/ 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. @@ -2560,6 +3066,22 @@ sub ut_enumn { : ''; } +=item ut_flag COLUMN + +Check/untaint a column if it contains either an empty string or 'Y'. This +is the standard form for boolean flags in Freeside. + +=cut + +sub ut_flag { + my( $self, $field ) = @_; + my $value = uc($self->getfield($field)); + if ( $value eq '' or $value eq 'Y' ) { + $self->setfield($field, $value); + return ''; + } + return "Illegal (flag) field $field: $value"; +} =item ut_foreign_key COLUMN FOREIGN_TABLE FOREIGN_COLUMN @@ -2656,7 +3178,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)) { @@ -2686,13 +3208,8 @@ Checks to see if the string is encrypted and returns true or false (1/0) to indi sub is_encrypted { my ($self, $value) = @_; - # Possible Bug - Some work may be required here.... - - if ($value =~ /^M/ && length($value) > 80) { - return 1; - } else { - return 0; - } + # could be more precise about it, but this will do for now + $value =~ /^M/ && length($value) > 80; } =item decrypt($value) @@ -2802,7 +3319,7 @@ sub scalar_sql { defined($scalar) ? $scalar : ''; } -=item count [ WHERE ] +=item count [ WHERE [, PLACEHOLDER ...] ] Convenience method for the common case of "SELECT COUNT(*) FROM table", with optional WHERE. Must be called as method on a class with an @@ -2815,7 +3332,7 @@ sub count { 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); + $self->scalar_sql($sql, @_); } =back @@ -2876,6 +3393,8 @@ sub _quote { my $column_type = $column_obj->type; my $nullable = $column_obj->null; + utf8::upgrade($value); + warn " $table.$column: $value ($column_type". ( $nullable ? ' NULL' : ' NOT NULL' ). ")\n" if $DEBUG > 2; @@ -3028,7 +3547,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 @@ -3049,6 +3568,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