#export dbdef for now... everything else expects to find it here
our @EXPORT_OK = qw(
dbh fields hfields qsearch qsearchs dbdef jsearch
- str2time_sql str2time_sql_closing regexp_sql not_regexp_sql concat_sql
+ str2time_sql str2time_sql_closing regexp_sql not_regexp_sql
+ concat_sql group_concat_sql
midnight_sql
);
get_real_fields($table, $record, \@real_fields));
}
- if( defined($extra_sql) ){
- if( $statement =~ /\sWHERE\s/i ){
- if( $extra_sql =~ /^\s*AND /i ){
- # correct format
- }elsif($extra_sql =~ /^\s*WHERE /i ){
- $extra_sql =~ s/WHERE /AND /i;
- }else{
- $extra_sql = " AND $extra_sql";
- }
- }else{
- if( $extra_sql =~ /^\s*AND /i ){
- $extra_sql =~ s/AND /WHERE /i;
- }elsif($extra_sql =~ /^\s*WHERE /i ){
- # correct format
- }else{
- $extra_sql = " WHERE $extra_sql";
- }
- }
- $statement .= " $extra_sql";
- }
+ $statement .= " $extra_sql" if defined($extra_sql);
$statement .= " $order_by" if defined($order_by);
push @statement, $statement;
return @return;
}
-## makes this easier to read
-
sub get_real_fields {
my $table = shift;
my $record = shift;
my $real_fields = shift;
- ## this huge map was previously inline, just broke it out to help read the qsearch method, should be optimized for readability
- return (
- map {
+ ## could be optimized more for readability
+ return (
+ map {
my $op = '=';
my $column = $_;
+ my $table_column = "$table.$column";
my $type = dbdef->table($table)->column($column)->type;
my $value = $record->{$column};
$value = $value->{'value'} if ref($value);
- if ( ref($record->{$_}) ) {
- $op = $record->{$_}{'op'} if $record->{$_}{'op'};
+
+ if ( ref($record->{$column}) ) {
+ $op = $record->{$column}{'op'} if $record->{$column}{'op'};
#$op = 'LIKE' if $op =~ /^ILIKE$/i && driver_name ne 'Pg';
if ( uc($op) eq 'ILIKE' ) {
$op = 'LIKE';
- $record->{$_}{'value'} = lc($record->{$_}{'value'});
- $column = "LOWER($_)";
+ $record->{$column}{'value'} = lc($record->{$column}{'value'});
+ $table_column = "LOWER($table_column)";
}
- $record->{$_} = $record->{$_}{'value'}
+ $record->{$column} = $record->{$column}{'value'}
}
- if ( ! defined( $record->{$_} ) || $record->{$_} eq '' ) {
+ if ( ! defined( $record->{$column} ) || $record->{$column} eq '' ) {
if ( $op eq '=' ) {
if ( driver_name eq 'Pg' ) {
if ( $type =~ /(int|numeric|real|float4|(big)?serial)/i ) {
- qq-( $column IS NULL )-;
+ qq-( $table_column IS NULL )-;
} else {
- qq-( $column IS NULL OR $column = '' )-;
+ qq-( $table_column IS NULL OR $table_column = '' )-;
}
} else {
- qq-( $column IS NULL OR $column = "" )-;
+ qq-( $table_column IS NULL OR $table_column = "" )-;
}
} elsif ( $op eq '!=' ) {
if ( driver_name eq 'Pg' ) {
if ( $type =~ /(int|numeric|real|float4|(big)?serial)/i ) {
- qq-( $column IS NOT NULL )-;
+ qq-( $table_column IS NOT NULL )-;
} else {
- qq-( $column IS NOT NULL AND $column != '' )-;
+ qq-( $table_column IS NOT NULL AND $table_column != '' )-;
}
} else {
- qq-( $column IS NOT NULL AND $column != "" )-;
+ qq-( $table_column IS NOT NULL AND $table_column != "" )-;
}
} else {
if ( driver_name eq 'Pg' ) {
- qq-( $column $op '' )-;
+ qq-( $table_column $op '' )-;
} else {
- qq-( $column $op "" )-;
+ qq-( $table_column $op "" )-;
}
}
} elsif ( $op eq '!=' ) {
- qq-( $column IS NULL OR $column != ? )-;
+ qq-( $table_column IS NULL OR $table_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
#} elsif ( $op eq 'APPROX=' && _is_fs_float( $type, $value ) ) {
- # ( "$column <= ?", "$column >= ?" );
+ # ( "$table_column <= ?", "$table_column >= ?" );
} else {
- "$column $op ?";
+ "$table_column $op ?";
}
- } @{ $real_fields } );
+
+ } @{ $real_fields }
+ );
}
=item by_key PRIMARY_KEY_VALUE
=item params
-Listref of field names for static fields. They will be given values from the
+Arrayref of field names for static fields. They will be given values from the
PARAMS hashref and passed as a "params" hashref to batch_import.
=item formats
=back
-PARAMS is a base64-encoded Storable string containing the POSTed data as
-a hash ref. It normally contains at least one field, "uploaded files",
-generated by /elements/file-upload.html and containing the list of uploaded
-files. Currently only supports a single file named "file".
+PARAMS is a hashref (or base64-encoded Storable hashref) containing the
+POSTed data. It must contain the field "uploaded files", generated by
+/elements/file-upload.html and containing the list of uploaded 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'}
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
sub ut_foreign_key {
my( $self, $field, $table, $foreign ) = @_;
- return '' if $no_check_foreign;
+ return $self->ut_number($field) if $no_check_foreign;
qsearchs($table, { $foreign => $self->getfield($field) })
or return "Can't find ". $self->table. ".$field ". $self->getfield($field).
" in $table.$foreign";
if ( $self->$field() ) {
- return "Access denied"
+ return 'Access denied to agent '. $self->$field()
unless $curuser->agentnum($self->$field());
} else {
- return "Access denied"
+ return 'Access denied to global'
unless grep $curuser->access_right($_), @$null_acl;
}
$table = $something->table;
} else {
$table = $something;
- $something = "FS::$table";
+ #$something = "FS::$table";
}
return (real_fields($table));
}
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
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, @_);
+}
+
+=item row_exists [ WHERE [, PLACEHOLDER ...] ]
+
+Convenience method for the common case of "SELECT 1 FROM table ... LIMIT 1"
+with optional (but almost always needed) WHERE.
+
+=cut
+
+sub row_exists {
+ my($self, $where) = (shift, shift);
+ my $table = $self->table or die 'row_exists called on object of class '.ref($self);
+ my $sql = "SELECT 1 FROM $table";
+ $sql .= " WHERE $where" if $where;
+ $sql .= " LIMIT 1";
+ $self->scalar_sql($sql, @_);
}
=back
}
+=item group_concat_sql COLUMN, DELIMITER
+
+Returns an SQL expression to concatenate an aggregate column, using
+GROUP_CONCAT() for mysql and array_to_string() and array_agg() for Pg.
+
+=cut
+
+sub group_concat_sql {
+ my ($col, $delim) = @_;
+ $delim = dbh->quote($delim);
+ if ( driver_name() =~ /^mysql/i ) {
+ # DISTINCT(foo) is valid as $col
+ return "GROUP_CONCAT($col SEPARATOR $delim)";
+ } else {
+ return "array_to_string(array_agg($col), $delim)";
+ }
+}
+
=item midnight_sql DATE
Returns an SQL expression to convert DATE (a unix timestamp) to midnight
projects / freeside.git / blobdiff