Revision bed19453
Von Moritz Bunkus vor mehr als 8 Jahren hinzugefügt
| SL/AP.pm | ||
|---|---|---|
| 454 | 454 |
push(@values, $form->{vendor_id});
|
| 455 | 455 |
} elsif ($form->{vendor}) {
|
| 456 | 456 |
$where .= " AND v.name ILIKE ?"; |
| 457 |
push(@values, $form->like($form->{vendor}));
|
|
| 457 |
push(@values, like($form->{vendor}));
|
|
| 458 | 458 |
} |
| 459 | 459 |
if ($form->{"cp_name"}) {
|
| 460 | 460 |
$where .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)"; |
| ... | ... | |
| 470 | 470 |
} |
| 471 | 471 |
if ($form->{invnumber}) {
|
| 472 | 472 |
$where .= " AND a.invnumber ILIKE ?"; |
| 473 |
push(@values, $form->like($form->{invnumber}));
|
|
| 473 |
push(@values, like($form->{invnumber}));
|
|
| 474 | 474 |
} |
| 475 | 475 |
if ($form->{ordnumber}) {
|
| 476 | 476 |
$where .= " AND a.ordnumber ILIKE ?"; |
| 477 |
push(@values, $form->like($form->{ordnumber}));
|
|
| 477 |
push(@values, like($form->{ordnumber}));
|
|
| 478 | 478 |
} |
| 479 | 479 |
if ($form->{notes}) {
|
| 480 | 480 |
$where .= " AND lower(a.notes) LIKE ?"; |
| 481 |
push(@values, $form->like($form->{notes}));
|
|
| 481 |
push(@values, like($form->{notes}));
|
|
| 482 | 482 |
} |
| 483 | 483 |
if ($form->{project_id}) {
|
| 484 | 484 |
$where .= |
| SL/AR.pm | ||
|---|---|---|
| 507 | 507 |
push(@values, $form->{customer_id});
|
| 508 | 508 |
} elsif ($form->{customer}) {
|
| 509 | 509 |
$where .= " AND c.name ILIKE ?"; |
| 510 |
push(@values, $form->like($form->{customer}));
|
|
| 510 |
push(@values, like($form->{customer}));
|
|
| 511 | 511 |
} |
| 512 | 512 |
if ($form->{"cp_name"}) {
|
| 513 | 513 |
$where .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)"; |
| ... | ... | |
| 531 | 531 |
foreach my $column (qw(invnumber ordnumber cusordnumber notes transaction_description)) {
|
| 532 | 532 |
if ($form->{$column}) {
|
| 533 | 533 |
$where .= " AND a.$column ILIKE ?"; |
| 534 |
push(@values, $form->like($form->{$column}));
|
|
| 534 |
push(@values, like($form->{$column}));
|
|
| 535 | 535 |
} |
| 536 | 536 |
} |
| 537 | 537 |
if ($form->{"project_id"}) {
|
| SL/BP.pm | ||
|---|---|---|
| 187 | 187 |
push(@values, conv_i($form->{"${vc}_id"}));
|
| 188 | 188 |
} elsif ($form->{ $vc }) {
|
| 189 | 189 |
$query .= " AND vc.name ILIKE ?"; |
| 190 |
push(@values, $form->like($form->{ $vc }));
|
|
| 190 |
push(@values, like($form->{ $vc }));
|
|
| 191 | 191 |
} |
| 192 | 192 |
foreach my $column (qw(invnumber ordnumber quonumber donumber)) {
|
| 193 | 193 |
if ($form->{$column}) {
|
| 194 | 194 |
$query .= " AND a.$column ILIKE ?"; |
| 195 |
push(@values, $form->like($form->{$column}));
|
|
| 195 |
push(@values, like($form->{$column}));
|
|
| 196 | 196 |
} |
| 197 | 197 |
} |
| 198 | 198 |
|
| SL/DBUtils.pm | ||
|---|---|---|
| 6 | 6 |
our @ISA = qw(Exporter); |
| 7 | 7 |
|
| 8 | 8 |
our @EXPORT = qw(conv_i conv_date conv_dateq do_query selectrow_query do_statement |
| 9 |
dump_query quote_db_date |
|
| 9 |
dump_query quote_db_date like
|
|
| 10 | 10 |
selectfirst_hashref_query selectfirst_array_query |
| 11 | 11 |
selectall_hashref_query selectall_array_query |
| 12 | 12 |
selectall_as_map |
| ... | ... | |
| 386 | 386 |
return ($token, @vals); |
| 387 | 387 |
} |
| 388 | 388 |
|
| 389 |
sub like {
|
|
| 390 |
my ($string) = @_; |
|
| 391 |
|
|
| 392 |
return "%" . SL::Util::trim($string // '') . "%"; |
|
| 393 |
} |
|
| 394 |
|
|
| 389 | 395 |
1; |
| 390 | 396 |
|
| 391 | 397 |
|
| ... | ... | |
| 459 | 465 |
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. |
| 460 | 466 |
Returns 'NULL' if STR is empty. |
| 461 | 467 |
|
| 468 |
=item like STR |
|
| 469 |
|
|
| 470 |
Turns C<STR> into an argument suitable for SQL's C<LIKE> and C<ILIKE> |
|
| 471 |
operators by Trimming the string C<STR> (removes leading and trailing |
|
| 472 |
whitespaces) and prepending and appending C<%>. |
|
| 473 |
|
|
| 462 | 474 |
=back |
| 463 | 475 |
|
| 464 | 476 |
=head2 QUERY FUNCTIONS |
| SL/Form.pm | ||
|---|---|---|
| 3010 | 3010 |
return $thisdate; |
| 3011 | 3011 |
} |
| 3012 | 3012 |
|
| 3013 |
sub like {
|
|
| 3014 |
my ($self, $string) = @_; |
|
| 3015 |
|
|
| 3016 |
return "%" . SL::Util::trim($string // '') . "%"; |
|
| 3017 |
} |
|
| 3018 |
|
|
| 3019 | 3013 |
sub redo_rows {
|
| 3020 | 3014 |
$main::lxdebug->enter_sub(); |
| 3021 | 3015 |
|
| SL/GL.pm | ||
|---|---|---|
| 231 | 231 |
$glwhere .= qq| AND g.reference ILIKE ?|; |
| 232 | 232 |
$arwhere .= qq| AND a.invnumber ILIKE ?|; |
| 233 | 233 |
$apwhere .= qq| AND a.invnumber ILIKE ?|; |
| 234 |
push(@glvalues, $::form->like($form->{reference}));
|
|
| 235 |
push(@arvalues, $::form->like($form->{reference}));
|
|
| 236 |
push(@apvalues, $::form->like($form->{reference}));
|
|
| 234 |
push(@glvalues, like($form->{reference}));
|
|
| 235 |
push(@arvalues, like($form->{reference}));
|
|
| 236 |
push(@apvalues, like($form->{reference}));
|
|
| 237 | 237 |
} |
| 238 | 238 |
|
| 239 | 239 |
if ($form->{department}) {
|
| ... | ... | |
| 250 | 250 |
$glwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)"; |
| 251 | 251 |
$arwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)"; |
| 252 | 252 |
$apwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)"; |
| 253 |
push(@glvalues, $::form->like($form->{source}));
|
|
| 254 |
push(@arvalues, $::form->like($form->{source}));
|
|
| 255 |
push(@apvalues, $::form->like($form->{source}));
|
|
| 253 |
push(@glvalues, like($form->{source}));
|
|
| 254 |
push(@arvalues, like($form->{source}));
|
|
| 255 |
push(@apvalues, like($form->{source}));
|
|
| 256 | 256 |
} |
| 257 | 257 |
|
| 258 | 258 |
# default Datumseinschränkung falls nicht oder falsch übergeben (sollte nie passieren) |
| ... | ... | |
| 280 | 280 |
$glwhere .= " AND g.description ILIKE ?"; |
| 281 | 281 |
$arwhere .= " AND ct.name ILIKE ?"; |
| 282 | 282 |
$apwhere .= " AND ct.name ILIKE ?"; |
| 283 |
push(@glvalues, $::form->like($form->{description}));
|
|
| 284 |
push(@arvalues, $::form->like($form->{description}));
|
|
| 285 |
push(@apvalues, $::form->like($form->{description}));
|
|
| 283 |
push(@glvalues, like($form->{description}));
|
|
| 284 |
push(@arvalues, like($form->{description}));
|
|
| 285 |
push(@apvalues, like($form->{description}));
|
|
| 286 | 286 |
} |
| 287 | 287 |
|
| 288 | 288 |
if ($form->{employee_id}) {
|
| ... | ... | |
| 298 | 298 |
$glwhere .= " AND g.notes ILIKE ?"; |
| 299 | 299 |
$arwhere .= " AND a.notes ILIKE ?"; |
| 300 | 300 |
$apwhere .= " AND a.notes ILIKE ?"; |
| 301 |
push(@glvalues, $::form->like($form->{notes}));
|
|
| 302 |
push(@arvalues, $::form->like($form->{notes}));
|
|
| 303 |
push(@apvalues, $::form->like($form->{notes}));
|
|
| 301 |
push(@glvalues, like($form->{notes}));
|
|
| 302 |
push(@arvalues, like($form->{notes}));
|
|
| 303 |
push(@apvalues, like($form->{notes}));
|
|
| 304 | 304 |
} |
| 305 | 305 |
|
| 306 | 306 |
if ($form->{accno}) {
|
| SL/IC.pm | ||
|---|---|---|
| 591 | 591 |
|
| 592 | 592 |
if ($form->{partnumber}) {
|
| 593 | 593 |
$where .= qq| AND (p.partnumber ILIKE ?)|; |
| 594 |
push(@values, $::form->like($form->{partnumber}));
|
|
| 594 |
push(@values, like($form->{partnumber}));
|
|
| 595 | 595 |
} |
| 596 | 596 |
|
| 597 | 597 |
if ($form->{description}) {
|
| 598 | 598 |
$where .= qq| AND (p.description ILIKE ?)|; |
| 599 |
push(@values, $::form->like($form->{description}));
|
|
| 599 |
push(@values, like($form->{description}));
|
|
| 600 | 600 |
} |
| 601 | 601 |
|
| 602 | 602 |
# retrieve assembly items |
| ... | ... | |
| 652 | 652 |
while (my ($column, $table) = each(%columns)) {
|
| 653 | 653 |
next unless ($form->{"${column}_$i"});
|
| 654 | 654 |
$where .= qq| AND ${table}.${column} ILIKE ?|;
|
| 655 |
push(@values, $::form->like($form->{"${column}_$i"}));
|
|
| 655 |
push(@values, like($form->{"${column}_$i"}));
|
|
| 656 | 656 |
} |
| 657 | 657 |
|
| 658 | 658 |
if ($form->{id}) {
|
| ... | ... | |
| 917 | 917 |
next unless $form->{$_};
|
| 918 | 918 |
$form->{"l_$_"} = '1'; # show the column
|
| 919 | 919 |
push @where_tokens, "$table_prefix{$_}$_ ILIKE ?";
|
| 920 |
push @bind_vars, $::form->like($form->{$_});
|
|
| 920 |
push @bind_vars, like($form->{$_});
|
|
| 921 | 921 |
} |
| 922 | 922 |
|
| 923 | 923 |
foreach (@simple_l_switches) {
|
| ... | ... | |
| 958 | 958 |
# fortunately makemodel doesn't need to be displayed later, so adding a special clause to where_token is sufficient. |
| 959 | 959 |
if ($form->{make}) {
|
| 960 | 960 |
push @where_tokens, 'mv.name ILIKE ?'; |
| 961 |
push @bind_vars, $::form->like($form->{make});
|
|
| 961 |
push @bind_vars, like($form->{make});
|
|
| 962 | 962 |
} |
| 963 | 963 |
if ($form->{model}) {
|
| 964 | 964 |
push @where_tokens, 'mm.model ILIKE ?'; |
| 965 |
push @bind_vars, $::form->like($form->{model});
|
|
| 965 |
push @bind_vars, like($form->{model});
|
|
| 966 | 966 |
} |
| 967 | 967 |
|
| 968 | 968 |
# special case: sorting by partnumber |
| ... | ... | |
| 1156 | 1156 |
next unless ($form->{$column});
|
| 1157 | 1157 |
|
| 1158 | 1158 |
$where .= qq| AND $item ILIKE ?|; |
| 1159 |
push(@where_values, $::form->like($form->{$column}));
|
|
| 1159 |
push(@where_values, like($form->{$column}));
|
|
| 1160 | 1160 |
} |
| 1161 | 1161 |
|
| 1162 | 1162 |
foreach my $item (qw(description serialnumber)) {
|
| 1163 | 1163 |
next unless ($form->{$item});
|
| 1164 | 1164 |
|
| 1165 | 1165 |
$where .= qq| AND (${item} ILIKE ?)|;
|
| 1166 |
push(@where_values, $::form->like($form->{$item}));
|
|
| 1166 |
push(@where_values, like($form->{$item}));
|
|
| 1167 | 1167 |
} |
| 1168 | 1168 |
|
| 1169 | 1169 |
|
| ... | ... | |
| 1197 | 1197 |
foreach my $column (qw(make model)) {
|
| 1198 | 1198 |
next unless ($form->{$column});
|
| 1199 | 1199 |
$where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE $column ILIKE ?|; |
| 1200 |
push(@where_values, $::form->like($form->{$column}));
|
|
| 1200 |
push(@where_values, like($form->{$column}));
|
|
| 1201 | 1201 |
} |
| 1202 | 1202 |
|
| 1203 | 1203 |
$main::lxdebug->leave_sub(); |
| ... | ... | |
| 1411 | 1411 |
|
| 1412 | 1412 |
if ($sortorder eq "all") {
|
| 1413 | 1413 |
$where .= qq| AND (partnumber ILIKE ?) AND (description ILIKE ?)|; |
| 1414 |
push(@values, $::form->like($form->{partnumber}), $::form->like($form->{description}));
|
|
| 1414 |
push(@values, like($form->{partnumber}), like($form->{description}));
|
|
| 1415 | 1415 |
|
| 1416 | 1416 |
} elsif ($sortorder eq "partnumber") {
|
| 1417 | 1417 |
$where .= qq| AND (partnumber ILIKE ?)|; |
| 1418 |
push(@values, $::form->like($form->{partnumber}));
|
|
| 1418 |
push(@values, like($form->{partnumber}));
|
|
| 1419 | 1419 |
|
| 1420 | 1420 |
} elsif ($sortorder eq "description") {
|
| 1421 | 1421 |
$where .= qq| AND (description ILIKE ?)|; |
| 1422 |
push(@values, $::form->like($form->{description}));
|
|
| 1422 |
push(@values, like($form->{description}));
|
|
| 1423 | 1423 |
$order = "description"; |
| 1424 | 1424 |
|
| 1425 | 1425 |
} |
| SL/RP.pm | ||
|---|---|---|
| 1266 | 1266 |
if ($form->{$ct_id}) {
|
| 1267 | 1267 |
$where .= qq| AND (ct.id = | . conv_i($form->{$ct_id}) . qq|)|;
|
| 1268 | 1268 |
} elsif ($form->{ $form->{ct} }) {
|
| 1269 |
$where .= qq| AND (ct.name ILIKE | . $dbh->quote($::form->like($form->{$ct})) . qq|)|;
|
|
| 1269 |
$where .= qq| AND (ct.name ILIKE | . $dbh->quote(like($form->{$ct})) . qq|)|;
|
|
| 1270 | 1270 |
} |
| 1271 | 1271 |
|
| 1272 | 1272 |
my $dpt_join; |
| SL/VK.pm | ||
|---|---|---|
| 112 | 112 |
push(@values, $form->{customer_id});
|
| 113 | 113 |
} elsif ($form->{customer}) {
|
| 114 | 114 |
$where .= " AND ct.name ILIKE ?"; |
| 115 |
push(@values, $form->like($form->{customer}));
|
|
| 115 |
push(@values, like($form->{customer}));
|
|
| 116 | 116 |
} |
| 117 | 117 |
if ($form->{customernumber}) {
|
| 118 | 118 |
$where .= qq| AND ct.customernumber = ? |; |
| SL/WH.pm | ||
|---|---|---|
| 330 | 330 |
|
| 331 | 331 |
if ($filter{partnumber}) {
|
| 332 | 332 |
push @filter_ary, "p.partnumber ILIKE ?"; |
| 333 |
push @filter_vars, $::form->like($filter{partnumber});
|
|
| 333 |
push @filter_vars, like($filter{partnumber});
|
|
| 334 | 334 |
} |
| 335 | 335 |
|
| 336 | 336 |
if ($filter{description}) {
|
| 337 | 337 |
push @filter_ary, "(p.description ILIKE ?)"; |
| 338 |
push @filter_vars, $::form->like($filter{description});
|
|
| 338 |
push @filter_vars, like($filter{description});
|
|
| 339 | 339 |
} |
| 340 | 340 |
|
| 341 | 341 |
if ($filter{chargenumber}) {
|
| 342 | 342 |
push @filter_ary, "i1.chargenumber ILIKE ?"; |
| 343 |
push @filter_vars, $::form->like($filter{chargenumber});
|
|
| 343 |
push @filter_vars, like($filter{chargenumber});
|
|
| 344 | 344 |
} |
| 345 | 345 |
|
| 346 | 346 |
if (trim($form->{bestbefore})) {
|
| ... | ... | |
| 632 | 632 |
|
| 633 | 633 |
if ($filter{partnumber}) {
|
| 634 | 634 |
push @filter_ary, "p.partnumber ILIKE ?"; |
| 635 |
push @filter_vars, $::form->like($filter{partnumber});
|
|
| 635 |
push @filter_vars, like($filter{partnumber});
|
|
| 636 | 636 |
} |
| 637 | 637 |
|
| 638 | 638 |
if ($filter{description}) {
|
| 639 | 639 |
push @filter_ary, "p.description ILIKE ?"; |
| 640 |
push @filter_vars, $::form->like($filter{description});
|
|
| 640 |
push @filter_vars, like($filter{description});
|
|
| 641 | 641 |
} |
| 642 | 642 |
|
| 643 | 643 |
if ($filter{partsid}) {
|
| ... | ... | |
| 647 | 647 |
|
| 648 | 648 |
if ($filter{chargenumber}) {
|
| 649 | 649 |
push @filter_ary, "i.chargenumber ILIKE ?"; |
| 650 |
push @filter_vars, $::form->like($filter{chargenumber});
|
|
| 650 |
push @filter_vars, like($filter{chargenumber});
|
|
| 651 | 651 |
} |
| 652 | 652 |
|
| 653 | 653 |
if (trim($form->{bestbefore})) {
|
| ... | ... | |
| 657 | 657 |
|
| 658 | 658 |
if ($filter{ean}) {
|
| 659 | 659 |
push @filter_ary, "p.ean ILIKE ?"; |
| 660 |
push @filter_vars, $::form->like($filter{ean});
|
|
| 660 |
push @filter_vars, like($filter{ean});
|
|
| 661 | 661 |
} |
| 662 | 662 |
|
| 663 | 663 |
if (trim($filter{date})) {
|
| sql/Pg-upgrade2/erzeugnisnummern.pl | ||
|---|---|---|
| 60 | 60 |
|
| 61 | 61 |
if ( $::form->{filter_partnumber} ) {
|
| 62 | 62 |
$where .= ' AND partnumber ILIKE ?'; |
| 63 |
push(@values, $::form->like( $::form->{filter_partnumber} ));
|
|
| 63 |
push(@values, like( $::form->{filter_partnumber} ));
|
|
| 64 | 64 |
} |
| 65 | 65 |
|
| 66 | 66 |
if ($::form->{filter_description}) {
|
| 67 | 67 |
$where .= ' AND description ILIKE ?'; |
| 68 |
push(@values, $::form->like($::form->{filter_description}));
|
|
| 68 |
push(@values, like($::form->{filter_description}));
|
|
| 69 | 69 |
} |
| 70 | 70 |
|
| 71 | 71 |
if ($::form->{filter_notes}) {
|
| 72 | 72 |
$where .= ' AND notes ILIKE ?'; |
| 73 |
push(@values, $::form->like($::form->{filter_notes}));
|
|
| 73 |
push(@values, like($::form->{filter_notes}));
|
|
| 74 | 74 |
} |
| 75 | 75 |
|
| 76 | 76 |
if ($::form->{filter_ean}) {
|
| 77 | 77 |
$where .= ' AND ean ILIKE ?'; |
| 78 |
push(@values, $::form->like($::form->{filter_ean}));
|
|
| 78 |
push(@values, like($::form->{filter_ean}));
|
|
| 79 | 79 |
} |
| 80 | 80 |
|
| 81 | 81 |
if ($::form->{filter_type} eq 'assembly') {
|
Auch abrufbar als: Unified diff
Funktion »like« von Form nach DBUtils verschoben
Das ist eine Datenbank-Quoting-Funktion und hat daher nichts mehr in
Form zu suchen.