Revision 3c938e03
Von Martin Helmling mh@waldpark.octosoft.eu vor fast 8 Jahren hinzugefügt
| SL/WH.pm | ||
|---|---|---|
| 420 | 420 |
my $sort_order = $form->{order};
|
| 421 | 421 |
|
| 422 | 422 |
$sort_col = $filter{sort} unless $sort_col;
|
| 423 |
$sort_col = 'shippingdate' if $sort_col eq 'date'; |
|
| 423 | 424 |
$sort_order = ($sort_col = 'shippingdate') unless $sort_col; |
| 424 |
$sort_col = 'shippingdate' if $sort_col eq 'date'; |
|
| 425 |
$sort_order = $filter{order} unless $sort_order;
|
|
| 426 |
my $sort_spec = "${sort_col} " . ($sort_order ? " DESC" : " ASC");
|
|
| 425 |
|
|
| 426 |
my %orderspecs = ( |
|
| 427 |
'shippingdate' => ['shippingdate', 'r_itime', 'r_parts_id'], |
|
| 428 |
'bin_to' => ['bin_to', 'r_itime', 'r_parts_id'], |
|
| 429 |
'bin_from' => ['bin_from', 'r_itime', 'r_parts_id'], |
|
| 430 |
'warehouse_to' => ['warehouse_to, r_itime, r_parts_id'], |
|
| 431 |
'warehouse_from' => ['warehouse_from, r_itime, r_parts_id'], |
|
| 432 |
'partnumber' => ['partnumber'], |
|
| 433 |
'partdescription'=> ['partdescription'], |
|
| 434 |
'partunit' => ['partunit, r_itime, r_parts_id'], |
|
| 435 |
'qty' => ['qty, r_itime, r_parts_id'], |
|
| 436 |
'oe_id' => ['oe_id'], |
|
| 437 |
'comment' => ['comment'], |
|
| 438 |
'trans_type' => ['trans_type'], |
|
| 439 |
'employee' => ['employee'], |
|
| 440 |
'projectnumber' => ['projectnumber'], |
|
| 441 |
); |
|
| 442 |
|
|
| 443 |
$sort_order = $filter{order} unless $sort_order;
|
|
| 444 |
my $ASC = ($sort_order ? " DESC" : " ASC"); |
|
| 445 |
my $sort_spec = join("$ASC , ", @{$orderspecs{$sort_col}}). " $ASC";
|
|
| 427 | 446 |
|
| 428 | 447 |
my $where_clause = @filter_ary ? join(" AND ", @filter_ary) . " AND " : '';
|
| 429 | 448 |
|
| ... | ... | |
| 466 | 485 |
}; |
| 467 | 486 |
|
| 468 | 487 |
$form->{l_classification_id} = 'Y';
|
| 488 |
$form->{l_part_type} = 'Y';
|
|
| 489 |
$form->{l_itime} = 'Y';
|
|
| 469 | 490 |
$form->{l_invoice_id} = $form->{l_oe_id} if $form->{l_oe_id};
|
| 470 | 491 |
|
| 471 | 492 |
# build the select clauses. |
| ... | ... | |
| 476 | 497 |
} |
| 477 | 498 |
|
| 478 | 499 |
my $group_clause = join ", ", map { +/^l_/; "r_$'" }
|
| 479 |
( grep( { !/qty$/ and /^l_/ and $form->{$_} eq 'Y' } keys %$form), qw(l_parts_id l_partunit l_shippingdate) );
|
|
| 500 |
( grep( { !/qty$/ and /^l_/ and $form->{$_} eq 'Y' } keys %$form), qw(l_parts_id l_partunit l_shippingdate l_itime) );
|
|
| 480 | 501 |
|
| 481 | 502 |
$where_clause = defined($where_clause) ? $where_clause : ''; |
| 482 | 503 |
|
| 483 | 504 |
my $query = |
| 484 |
qq|SELECT DISTINCT $select{trans}
|
|
| 505 |
qq|SELECT * FROM (SELECT DISTINCT $select{trans}
|
|
| 485 | 506 |
FROM inventory i1 |
| 486 | 507 |
LEFT JOIN inventory i2 ON i1.trans_id = i2.trans_id |
| 487 | 508 |
LEFT JOIN parts p ON i1.parts_id = p.id |
| ... | ... | |
| 529 | 550 |
WHERE $where_clause i1.qty > 0 AND |
| 530 | 551 |
i1.trans_id IN ( SELECT i.trans_id FROM inventory i GROUP BY i.trans_id HAVING COUNT(i.trans_id) = 1 ) |
| 531 | 552 |
GROUP BY $group_clause |
| 532 |
ORDER BY r_${sort_spec}|;
|
|
| 553 |
ORDER BY r_${sort_spec}) AS lines WHERE r_qty>0|;
|
|
| 554 |
|
|
| 555 |
$query .= " LIMIT $filter{limit}" if $filter{limit} ;
|
|
| 556 |
$query .= " OFFSET $filter{offset}" if $filter{offset} ;
|
|
| 533 | 557 |
|
| 534 | 558 |
my $sth = prepare_execute_query($form, $dbh, $query, @filter_vars, @filter_vars, @filter_vars); |
| 535 | 559 |
|
| ... | ... | |
| 700 | 724 |
push @filter_vars, trim($form->{bestbefore});
|
| 701 | 725 |
} |
| 702 | 726 |
|
| 727 |
if ($filter{classification_id}) {
|
|
| 728 |
push @filter_ary, "p.classification_id = ?"; |
|
| 729 |
push @filter_vars, $filter{classification_id};
|
|
| 730 |
} |
|
| 731 |
|
|
| 703 | 732 |
if ($filter{ean}) {
|
| 704 | 733 |
push @filter_ary, "p.ean ILIKE ?"; |
| 705 | 734 |
push @filter_vars, like($filter{ean});
|
| ... | ... | |
| 763 | 792 |
"warehousedescription" => "w.description", |
| 764 | 793 |
"partunit" => "p.unit", |
| 765 | 794 |
"stock_value" => "p.lastcost / COALESCE(pfac.factor, 1)", |
| 795 |
"purchase_price" => "p.lastcost", |
|
| 766 | 796 |
); |
| 767 | 797 |
$form->{l_classification_id} = 'Y';
|
| 768 | 798 |
$form->{l_part_type} = 'Y';
|
| ... | ... | |
| 784 | 814 |
qw(l_parts_id l_qty l_partunit) ); |
| 785 | 815 |
|
| 786 | 816 |
my $query = |
| 787 |
qq|SELECT $select_clause |
|
| 817 |
qq|SELECT * FROM ( SELECT $select_clause
|
|
| 788 | 818 |
FROM inventory i |
| 789 | 819 |
LEFT JOIN parts p ON i.parts_id = p.id |
| 790 | 820 |
LEFT JOIN bin b ON i.bin_id = b.id |
| ... | ... | |
| 792 | 822 |
$joins |
| 793 | 823 |
WHERE $where_clause |
| 794 | 824 |
GROUP BY $group_clause |
| 795 |
ORDER BY $sort_spec|; |
|
| 825 |
ORDER BY $sort_spec ) AS lines WHERE qty<>0|; |
|
| 826 |
|
|
| 827 |
$query .= " LIMIT $filter{limit}" if $filter{limit} ;
|
|
| 828 |
$query .= " OFFSET $filter{offset}" if $filter{offset} ;
|
|
| 796 | 829 |
|
| 797 | 830 |
my $sth = prepare_execute_query($form, $dbh, $query, @filter_vars); |
| 798 | 831 |
|
Auch abrufbar als: Unified diff
Paginierung schneller machen für Lager(Paginierung repariert)
Lagerbuchungen und Lagerbestand optimieren
1. Auf Rose umstellen grosser Aufwand.
2. Roseverhalten nachimplementieren:
2.1. Nur einen Query haben, danach kein herausnehmen von Zeilen
2.2. Dann funktioniert Paginierung mit LIMIT und OFFSET (wie Rose)
2.3. Rose macht ein zu Beginn ein COUNT um die Gesamtmenge zu ermitteln,wir merken uns das nach dem ersten Query
Der Mengenfilter kann schlecht in SQL integriert werden wegen Einheitenumrechnung (eventuell später noch eine PSQL Funktion einbauen)