+ $statement .= " $addl_from" if $addl_from;
+ if ( @real_fields or @virtual_fields ) {
+ $statement .= ' WHERE '. join(' AND ',
+ get_real_fields($table, $record, \@real_fields) ,
+ get_virtual_fields($table, $pkey, $record, \@virtual_fields),
+ );
+ }
+
+ $statement .= " $extra_sql" if defined($extra_sql);
+ $statement .= " $order_by" if defined($order_by);
+
+ warn "[debug]$me $statement\n" if $DEBUG > 1 || $debug;
+ my $sth = $dbh->prepare($statement)
+ or croak "$dbh->errstr doing $statement";
+
+ my $bind = 1;
+
+ foreach my $field (
+ grep defined( $record->{$_} ) && $record->{$_} ne '', @real_fields
+ ) {
+ if ( $record->{$field} =~ /^\d+(\.\d+)?$/
+ && dbdef->table($table)->column($field)->type =~ /(int|(big)?serial)/i
+ ) {
+ $sth->bind_param($bind++, $record->{$field}, { TYPE => SQL_INTEGER } );
+ } else {
+ $sth->bind_param($bind++, $record->{$field}, { TYPE => SQL_VARCHAR } );
+ }
+ }
+
+# $sth->execute( map $record->{$_},
+# grep defined( $record->{$_} ) && $record->{$_} ne '', @fields
+# ) or croak "Error executing \"$statement\": ". $sth->errstr;
+
+ $sth->execute or croak "Error executing \"$statement\": ". $sth->errstr;
+
+ if ( eval 'scalar(@FS::'. $table. '::ISA);' ) {
+ @virtual_fields = "FS::$table"->virtual_fields;
+ } else {
+ cluck "warning: FS::$table not loaded; virtual fields not returned either";
+ @virtual_fields = ();
+ }
+
+ my %result;
+ tie %result, "Tie::IxHash";
+ my @stuff = @{ $sth->fetchall_arrayref( {} ) };
+ if ( $pkey && scalar(@stuff) && $stuff[0]->{$pkey} ) {
+ %result = map { $_->{$pkey}, $_ } @stuff;
+ } else {
+ @result{@stuff} = @stuff;
+ }
+
+ $sth->finish;
+
+ if ( keys(%result) and @virtual_fields ) {
+ $statement =
+ "SELECT virtual_field.recnum, part_virtual_field.name, ".
+ "virtual_field.value ".
+ "FROM part_virtual_field JOIN virtual_field USING (vfieldpart) ".
+ "WHERE part_virtual_field.dbtable = '$table' AND ".
+ "virtual_field.recnum IN (".
+ join(',', keys(%result)). ") AND part_virtual_field.name IN ('".
+ join(q!', '!, @virtual_fields) . "')";
+ warn "[debug]$me $statement\n" if $DEBUG > 1;
+ $sth = $dbh->prepare($statement) or croak "$dbh->errstr doing $statement";
+ $sth->execute or croak "Error executing \"$statement\": ". $sth->errstr;
+
+ foreach (@{ $sth->fetchall_arrayref({}) }) {
+ my $recnum = $_->{recnum};
+ my $name = $_->{name};
+ my $value = $_->{value};
+ if (exists($result{$recnum})) {
+ $result{$recnum}->{$name} = $value;
+ }
+ }
+ }
+ my @return;
+ if ( 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 )
+ } values(%result);
+ } else {
+ @return = map {
+ new( "FS::$table", { %{$_} } )
+ } values(%result);
+ }
+ } else {
+ #okay, its been tested
+ # warn "untested code (class FS::$table uses custom new method)";
+ @return = map {
+ eval 'FS::'. $table. '->new( { %{$_} } )';
+ } values(%result);
+ }
+
+ # Check for encrypted fields and decrypt them.
+ ## only in the local copy, not the cached object
+ if ( $conf && $conf->exists('encryption') # $conf doesn't exist when doing
+ # the initial search for
+ # access_user
+ && eval 'defined(@FS::'. $table . '::encrypted_fields)') {
+ foreach my $record (@return) {
+ foreach my $field (eval '@FS::'. $table . '::encrypted_fields') {
+ # 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";
+ @return = map {
+ FS::Record->new( $table, { %{$_} } );
+ } values(%result);
+ }
+ return @return;
+}
+
+## makes this easier to read
+
+sub get_virtual_fields {
+ my $table = shift;
+ my $pkey = shift;
+ my $record = shift;
+ my $virtual_fields = shift;
+
+ return
+ ( map {
+ my $op = '=';
+ my $column = $_;
+ if ( ref($record->{$_}) ) {
+ $op = $record->{$_}{'op'} if $record->{$_}{'op'};
+ if ( uc($op) eq 'ILIKE' ) {
+ $op = 'LIKE';
+ $record->{$_}{'value'} = lc($record->{$_}{'value'});
+ $column = "LOWER($_)";
+ }
+ $record->{$_} = $record->{$_}{'value'};
+ }
+
+ # ... EXISTS ( SELECT name, value FROM part_virtual_field
+ # JOIN virtual_field
+ # ON part_virtual_field.vfieldpart = virtual_field.vfieldpart
+ # WHERE recnum = svc_acct.svcnum
+ # AND (name, value) = ('egad', 'brain') )
+
+ my $value = $record->{$_};
+
+ my $subq;
+
+ $subq = ($value ? 'EXISTS ' : 'NOT EXISTS ') .
+ "( SELECT part_virtual_field.name, virtual_field.value ".
+ "FROM part_virtual_field JOIN virtual_field ".
+ "ON part_virtual_field.vfieldpart = virtual_field.vfieldpart ".
+ "WHERE virtual_field.recnum = ${table}.${pkey} ".
+ "AND part_virtual_field.name = '${column}'".
+ ($value ?
+ " AND virtual_field.value ${op} '${value}'"
+ : "") . ")";
+ $subq;
+
+ } @{ $virtual_fields } ) ;
+}
+
+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 {