X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/c497b0352f95a55d204101b70ac771b2dc21ddee..9c5f94c0359a8f2c0dba93b5e9bdef99f332fcc8:/SL/DBUtils.pm

diff --git a/SL/DBUtils.pm b/SL/DBUtils.pm
index 2d9c472af..d060bcb8e 100644
--- a/SL/DBUtils.pm
+++ b/SL/DBUtils.pm
@@ -1,13 +1,16 @@
 package SL::DBUtils;
 
+use SL::Util qw(trim);
+
 require Exporter;
 our @ISA = qw(Exporter);
 
 our @EXPORT = qw(conv_i conv_date conv_dateq do_query selectrow_query do_statement
-             dump_query quote_db_date
+             dump_query quote_db_date like
              selectfirst_hashref_query selectfirst_array_query
              selectall_hashref_query selectall_array_query
              selectall_as_map
+             selectall_ids
              prepare_execute_query prepare_query
              create_sort_spec does_table_exist
              add_token);
@@ -29,7 +32,9 @@ sub conv_b {
 
 sub conv_date {
   my ($value) = @_;
-  return (defined($value) && "$value" ne "") ? $value : undef;
+  return undef if !defined $value;
+  $value = trim($value);
+  return $value eq "" ? undef : $value;
 }
 
 sub conv_dateq {
@@ -221,11 +226,11 @@ sub selectall_as_map {
   my %hash;
   if ('' eq ref $value_col) {
     while (my $ref = $sth->fetchrow_hashref()) {
-      $hash{$ref->{$key_col}} = $ref->{$value_col};
+      $hash{$ref->{$key_col} // ''} = $ref->{$value_col};
     }
   } else {
     while (my $ref = $sth->fetchrow_hashref()) {
-      $hash{$ref->{$key_col}} = { map { $_ => $ref->{$_} } @{ $value_col } };
+      $hash{$ref->{$key_col} // ''} = { map { $_ => $ref->{$_} } @{ $value_col } };
     }
   }
 
@@ -236,6 +241,25 @@ sub selectall_as_map {
   return %hash;
 }
 
+sub selectall_ids {
+  $main::lxdebug->enter_sub(2);
+
+  my ($form, $dbh, $query, $key_col) = splice(@_, 0, 4);
+
+  my $sth = prepare_execute_query($form, $dbh, $query, @_);
+
+  my @ids;
+  while (my $ref = $sth->fetchrow_arrayref()) {
+    push @ids, $ref->[$key_col];
+  }
+
+  $sth->finish;
+
+  $main::lxdebug->leave_sub(2);
+
+  return @ids;
+}
+
 sub create_sort_spec {
   $main::lxdebug->enter_sub(2);
 
@@ -324,9 +348,9 @@ sub add_token {
     id     => \&conv_i,
     bool   => \&conv_b,
     date   => \&conv_date,
-    start  => sub { $_[0] . '%' },
-    end    => sub { '%' . $_[0] },
-    substr => sub { '%' . $_[0] . '%' },
+    start  => sub { trim($_[0]) . '%' },
+    end    => sub { '%' . trim($_[0]) },
+    substr => sub { like($_[0]) },
   );
 
   my $_long_token = sub {
@@ -362,6 +386,12 @@ sub add_token {
   return ($token, @vals);
 }
 
+sub like {
+  my ($string) = @_;
+
+  return "%" . SL::Util::trim($string // '') . "%";
+}
+
 1;
 
 
@@ -369,7 +399,7 @@ __END__
 
 =head1 NAME
 
-SL::DBUTils.pm: All about Databaseconections in Lx
+SL::DBUTils.pm: All about database connections in kivitendo
 
 =head1 SYNOPSIS
 
@@ -402,7 +432,7 @@ Using DBUtils most database procedures can be reduced to defining the query, exe
 
 DBUtils relies heavily on two parameters which have to be passed to almost every function: $form and $dbh.
   - $form is used for error handling only. It can be omitted in theory, but should not.
-  - $dbh is a handle to the databe, as returned by the DBI::connect routine. If you don't have an active connectiong, you can query $form->get_standard_dbh() to get a generic no_auto connection. Don't forget to commit in this case!
+  - $dbh is a handle to the database, as returned by the DBI::connect routine. If you don't have an active connection, you can query $form->get_standard_dbh() to get a generic no_auto connection. Don't forget to commit in this case!
 
 
 Every function here should accomplish the follwing things:
@@ -435,6 +465,12 @@ Database version of conv_date. Quotes STR before returning. Returns 'NULL' if ST
 Treats STR as a database date, quoting it. If STR equals current_date returns an escaped version which is treated as the current date by Postgres.
 Returns 'NULL' if STR is empty.
 
+=item like STR
+
+Turns C<STR> into an argument suitable for SQL's C<LIKE> and C<ILIKE>
+operators by Trimming the string C<STR> (removes leading and trailing
+whitespaces) and prepending and appending C<%>.
+
 =back
 
 =head2 QUERY FUNCTIONS