From bed19453fb654f69ad972911a4533ca89f8ae0c9 Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Mon, 18 Apr 2016 10:04:18 +0200 Subject: [PATCH] =?utf8?q?Funktion=20=C2=BBlike=C2=AB=20von=20Form=20nach?= =?utf8?q?=20DBUtils=20verschoben?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Das ist eine Datenbank-Quoting-Funktion und hat daher nichts mehr in Form zu suchen. --- SL/AP.pm | 8 ++++---- SL/AR.pm | 4 ++-- SL/BP.pm | 4 ++-- SL/DBUtils.pm | 14 +++++++++++++- SL/Form.pm | 6 ------ SL/GL.pm | 24 ++++++++++++------------ SL/IC.pm | 24 ++++++++++++------------ SL/RP.pm | 2 +- SL/VK.pm | 2 +- SL/WH.pm | 14 +++++++------- sql/Pg-upgrade2/erzeugnisnummern.pl | 8 ++++---- 11 files changed, 58 insertions(+), 52 deletions(-) diff --git a/SL/AP.pm b/SL/AP.pm index e3dc7b31d..afd4a525e 100644 --- a/SL/AP.pm +++ b/SL/AP.pm @@ -454,7 +454,7 @@ sub ap_transactions { push(@values, $form->{vendor_id}); } elsif ($form->{vendor}) { $where .= " AND v.name ILIKE ?"; - push(@values, $form->like($form->{vendor})); + push(@values, like($form->{vendor})); } if ($form->{"cp_name"}) { $where .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)"; @@ -470,15 +470,15 @@ sub ap_transactions { } if ($form->{invnumber}) { $where .= " AND a.invnumber ILIKE ?"; - push(@values, $form->like($form->{invnumber})); + push(@values, like($form->{invnumber})); } if ($form->{ordnumber}) { $where .= " AND a.ordnumber ILIKE ?"; - push(@values, $form->like($form->{ordnumber})); + push(@values, like($form->{ordnumber})); } if ($form->{notes}) { $where .= " AND lower(a.notes) LIKE ?"; - push(@values, $form->like($form->{notes})); + push(@values, like($form->{notes})); } if ($form->{project_id}) { $where .= diff --git a/SL/AR.pm b/SL/AR.pm index a2cad1196..dba6b5b80 100644 --- a/SL/AR.pm +++ b/SL/AR.pm @@ -507,7 +507,7 @@ sub ar_transactions { push(@values, $form->{customer_id}); } elsif ($form->{customer}) { $where .= " AND c.name ILIKE ?"; - push(@values, $form->like($form->{customer})); + push(@values, like($form->{customer})); } if ($form->{"cp_name"}) { $where .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)"; @@ -531,7 +531,7 @@ sub ar_transactions { foreach my $column (qw(invnumber ordnumber cusordnumber notes transaction_description)) { if ($form->{$column}) { $where .= " AND a.$column ILIKE ?"; - push(@values, $form->like($form->{$column})); + push(@values, like($form->{$column})); } } if ($form->{"project_id"}) { diff --git a/SL/BP.pm b/SL/BP.pm index f7eaf2b5c..867b5ca19 100644 --- a/SL/BP.pm +++ b/SL/BP.pm @@ -187,12 +187,12 @@ sub get_spoolfiles { push(@values, conv_i($form->{"${vc}_id"})); } elsif ($form->{ $vc }) { $query .= " AND vc.name ILIKE ?"; - push(@values, $form->like($form->{ $vc })); + push(@values, like($form->{ $vc })); } foreach my $column (qw(invnumber ordnumber quonumber donumber)) { if ($form->{$column}) { $query .= " AND a.$column ILIKE ?"; - push(@values, $form->like($form->{$column})); + push(@values, like($form->{$column})); } } diff --git a/SL/DBUtils.pm b/SL/DBUtils.pm index e8cd304ee..200f421e6 100644 --- a/SL/DBUtils.pm +++ b/SL/DBUtils.pm @@ -6,7 +6,7 @@ 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 @@ -386,6 +386,12 @@ sub add_token { return ($token, @vals); } +sub like { + my ($string) = @_; + + return "%" . SL::Util::trim($string // '') . "%"; +} + 1; @@ -459,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 into an argument suitable for SQL's C and C +operators by Trimming the string C (removes leading and trailing +whitespaces) and prepending and appending C<%>. + =back =head2 QUERY FUNCTIONS diff --git a/SL/Form.pm b/SL/Form.pm index 5b868ec5e..41cc49f03 100644 --- a/SL/Form.pm +++ b/SL/Form.pm @@ -3010,12 +3010,6 @@ sub current_date { return $thisdate; } -sub like { - my ($self, $string) = @_; - - return "%" . SL::Util::trim($string // '') . "%"; -} - sub redo_rows { $main::lxdebug->enter_sub(); diff --git a/SL/GL.pm b/SL/GL.pm index 1d917bd48..60b54e22d 100644 --- a/SL/GL.pm +++ b/SL/GL.pm @@ -231,9 +231,9 @@ sub all_transactions { $glwhere .= qq| AND g.reference ILIKE ?|; $arwhere .= qq| AND a.invnumber ILIKE ?|; $apwhere .= qq| AND a.invnumber ILIKE ?|; - push(@glvalues, $::form->like($form->{reference})); - push(@arvalues, $::form->like($form->{reference})); - push(@apvalues, $::form->like($form->{reference})); + push(@glvalues, like($form->{reference})); + push(@arvalues, like($form->{reference})); + push(@apvalues, like($form->{reference})); } if ($form->{department}) { @@ -250,9 +250,9 @@ sub all_transactions { $glwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)"; $arwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)"; $apwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)"; - push(@glvalues, $::form->like($form->{source})); - push(@arvalues, $::form->like($form->{source})); - push(@apvalues, $::form->like($form->{source})); + push(@glvalues, like($form->{source})); + push(@arvalues, like($form->{source})); + push(@apvalues, like($form->{source})); } # default Datumseinschränkung falls nicht oder falsch übergeben (sollte nie passieren) @@ -280,9 +280,9 @@ sub all_transactions { $glwhere .= " AND g.description ILIKE ?"; $arwhere .= " AND ct.name ILIKE ?"; $apwhere .= " AND ct.name ILIKE ?"; - push(@glvalues, $::form->like($form->{description})); - push(@arvalues, $::form->like($form->{description})); - push(@apvalues, $::form->like($form->{description})); + push(@glvalues, like($form->{description})); + push(@arvalues, like($form->{description})); + push(@apvalues, like($form->{description})); } if ($form->{employee_id}) { @@ -298,9 +298,9 @@ sub all_transactions { $glwhere .= " AND g.notes ILIKE ?"; $arwhere .= " AND a.notes ILIKE ?"; $apwhere .= " AND a.notes ILIKE ?"; - push(@glvalues, $::form->like($form->{notes})); - push(@arvalues, $::form->like($form->{notes})); - push(@apvalues, $::form->like($form->{notes})); + push(@glvalues, like($form->{notes})); + push(@arvalues, like($form->{notes})); + push(@apvalues, like($form->{notes})); } if ($form->{accno}) { diff --git a/SL/IC.pm b/SL/IC.pm index b707e88cb..8341f1888 100644 --- a/SL/IC.pm +++ b/SL/IC.pm @@ -591,12 +591,12 @@ sub retrieve_assemblies { if ($form->{partnumber}) { $where .= qq| AND (p.partnumber ILIKE ?)|; - push(@values, $::form->like($form->{partnumber})); + push(@values, like($form->{partnumber})); } if ($form->{description}) { $where .= qq| AND (p.description ILIKE ?)|; - push(@values, $::form->like($form->{description})); + push(@values, like($form->{description})); } # retrieve assembly items @@ -652,7 +652,7 @@ sub assembly_item { while (my ($column, $table) = each(%columns)) { next unless ($form->{"${column}_$i"}); $where .= qq| AND ${table}.${column} ILIKE ?|; - push(@values, $::form->like($form->{"${column}_$i"})); + push(@values, like($form->{"${column}_$i"})); } if ($form->{id}) { @@ -917,7 +917,7 @@ sub all_parts { next unless $form->{$_}; $form->{"l_$_"} = '1'; # show the column push @where_tokens, "$table_prefix{$_}$_ ILIKE ?"; - push @bind_vars, $::form->like($form->{$_}); + push @bind_vars, like($form->{$_}); } foreach (@simple_l_switches) { @@ -958,11 +958,11 @@ sub all_parts { # fortunately makemodel doesn't need to be displayed later, so adding a special clause to where_token is sufficient. if ($form->{make}) { push @where_tokens, 'mv.name ILIKE ?'; - push @bind_vars, $::form->like($form->{make}); + push @bind_vars, like($form->{make}); } if ($form->{model}) { push @where_tokens, 'mm.model ILIKE ?'; - push @bind_vars, $::form->like($form->{model}); + push @bind_vars, like($form->{model}); } # special case: sorting by partnumber @@ -1156,14 +1156,14 @@ sub _create_filter_for_priceupdate { next unless ($form->{$column}); $where .= qq| AND $item ILIKE ?|; - push(@where_values, $::form->like($form->{$column})); + push(@where_values, like($form->{$column})); } foreach my $item (qw(description serialnumber)) { next unless ($form->{$item}); $where .= qq| AND (${item} ILIKE ?)|; - push(@where_values, $::form->like($form->{$item})); + push(@where_values, like($form->{$item})); } @@ -1197,7 +1197,7 @@ sub _create_filter_for_priceupdate { foreach my $column (qw(make model)) { next unless ($form->{$column}); $where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE $column ILIKE ?|; - push(@where_values, $::form->like($form->{$column})); + push(@where_values, like($form->{$column})); } $main::lxdebug->leave_sub(); @@ -1411,15 +1411,15 @@ sub get_parts { if ($sortorder eq "all") { $where .= qq| AND (partnumber ILIKE ?) AND (description ILIKE ?)|; - push(@values, $::form->like($form->{partnumber}), $::form->like($form->{description})); + push(@values, like($form->{partnumber}), like($form->{description})); } elsif ($sortorder eq "partnumber") { $where .= qq| AND (partnumber ILIKE ?)|; - push(@values, $::form->like($form->{partnumber})); + push(@values, like($form->{partnumber})); } elsif ($sortorder eq "description") { $where .= qq| AND (description ILIKE ?)|; - push(@values, $::form->like($form->{description})); + push(@values, like($form->{description})); $order = "description"; } diff --git a/SL/RP.pm b/SL/RP.pm index 7bc476663..1154f4eba 100644 --- a/SL/RP.pm +++ b/SL/RP.pm @@ -1266,7 +1266,7 @@ sub aging { if ($form->{$ct_id}) { $where .= qq| AND (ct.id = | . conv_i($form->{$ct_id}) . qq|)|; } elsif ($form->{ $form->{ct} }) { - $where .= qq| AND (ct.name ILIKE | . $dbh->quote($::form->like($form->{$ct})) . qq|)|; + $where .= qq| AND (ct.name ILIKE | . $dbh->quote(like($form->{$ct})) . qq|)|; } my $dpt_join; diff --git a/SL/VK.pm b/SL/VK.pm index f20414c7f..45c3f63e6 100644 --- a/SL/VK.pm +++ b/SL/VK.pm @@ -112,7 +112,7 @@ sub invoice_transactions { push(@values, $form->{customer_id}); } elsif ($form->{customer}) { $where .= " AND ct.name ILIKE ?"; - push(@values, $form->like($form->{customer})); + push(@values, like($form->{customer})); } if ($form->{customernumber}) { $where .= qq| AND ct.customernumber = ? |; diff --git a/SL/WH.pm b/SL/WH.pm index ce8c22500..97f0a7553 100644 --- a/SL/WH.pm +++ b/SL/WH.pm @@ -330,17 +330,17 @@ sub get_warehouse_journal { if ($filter{partnumber}) { push @filter_ary, "p.partnumber ILIKE ?"; - push @filter_vars, $::form->like($filter{partnumber}); + push @filter_vars, like($filter{partnumber}); } if ($filter{description}) { push @filter_ary, "(p.description ILIKE ?)"; - push @filter_vars, $::form->like($filter{description}); + push @filter_vars, like($filter{description}); } if ($filter{chargenumber}) { push @filter_ary, "i1.chargenumber ILIKE ?"; - push @filter_vars, $::form->like($filter{chargenumber}); + push @filter_vars, like($filter{chargenumber}); } if (trim($form->{bestbefore})) { @@ -632,12 +632,12 @@ sub get_warehouse_report { if ($filter{partnumber}) { push @filter_ary, "p.partnumber ILIKE ?"; - push @filter_vars, $::form->like($filter{partnumber}); + push @filter_vars, like($filter{partnumber}); } if ($filter{description}) { push @filter_ary, "p.description ILIKE ?"; - push @filter_vars, $::form->like($filter{description}); + push @filter_vars, like($filter{description}); } if ($filter{partsid}) { @@ -647,7 +647,7 @@ sub get_warehouse_report { if ($filter{chargenumber}) { push @filter_ary, "i.chargenumber ILIKE ?"; - push @filter_vars, $::form->like($filter{chargenumber}); + push @filter_vars, like($filter{chargenumber}); } if (trim($form->{bestbefore})) { @@ -657,7 +657,7 @@ sub get_warehouse_report { if ($filter{ean}) { push @filter_ary, "p.ean ILIKE ?"; - push @filter_vars, $::form->like($filter{ean}); + push @filter_vars, like($filter{ean}); } if (trim($filter{date})) { diff --git a/sql/Pg-upgrade2/erzeugnisnummern.pl b/sql/Pg-upgrade2/erzeugnisnummern.pl index 326122eb8..bd8eb1cd8 100644 --- a/sql/Pg-upgrade2/erzeugnisnummern.pl +++ b/sql/Pg-upgrade2/erzeugnisnummern.pl @@ -60,22 +60,22 @@ sub filter_parts { if ( $::form->{filter_partnumber} ) { $where .= ' AND partnumber ILIKE ?'; - push(@values, $::form->like( $::form->{filter_partnumber} )); + push(@values, like( $::form->{filter_partnumber} )); } if ($::form->{filter_description}) { $where .= ' AND description ILIKE ?'; - push(@values, $::form->like($::form->{filter_description})); + push(@values, like($::form->{filter_description})); } if ($::form->{filter_notes}) { $where .= ' AND notes ILIKE ?'; - push(@values, $::form->like($::form->{filter_notes})); + push(@values, like($::form->{filter_notes})); } if ($::form->{filter_ean}) { $where .= ' AND ean ILIKE ?'; - push(@values, $::form->like($::form->{filter_ean})); + push(@values, like($::form->{filter_ean})); } if ($::form->{filter_type} eq 'assembly') { -- 2.20.1