Revision 6daa0eca
Von Sven Schöling vor mehr als 3 Jahren hinzugefügt
| SL/WH.pm | ||
|---|---|---|
| 548 | 548 |
$where_clause = defined($where_clause) ? $where_clause : ''; |
| 549 | 549 |
|
| 550 | 550 |
my $query = |
| 551 |
qq|SELECT * FROM (SELECT DISTINCT $select{trans}
|
|
| 552 |
FROM inventory i1 |
|
| 553 |
LEFT JOIN inventory i2 ON i1.trans_id = i2.trans_id |
|
| 554 |
LEFT JOIN parts p ON i1.parts_id = p.id |
|
| 555 |
LEFT JOIN bin b1 ON i1.bin_id = b1.id |
|
| 556 |
LEFT JOIN bin b2 ON i2.bin_id = b2.id |
|
| 557 |
LEFT JOIN warehouse w1 ON i1.warehouse_id = w1.id |
|
| 558 |
LEFT JOIN warehouse w2 ON i2.warehouse_id = w2.id |
|
| 559 |
LEFT JOIN transfer_type tt ON i1.trans_type_id = tt.id |
|
| 560 |
LEFT JOIN project pr ON i1.project_id = pr.id |
|
| 561 |
LEFT JOIN employee e ON i1.employee_id = e.id |
|
| 562 |
WHERE $where_clause i2.qty = -i1.qty AND i2.qty > 0 AND |
|
| 563 |
i1.trans_id IN ( SELECT i.trans_id FROM inventory i GROUP BY i.trans_id HAVING COUNT(i.trans_id) = 2 ) |
|
| 564 |
GROUP BY $group_clause |
|
| 565 |
|
|
| 566 |
UNION |
|
| 567 |
|
|
| 551 |
qq|SELECT * FROM ( |
|
| 568 | 552 |
SELECT DISTINCT $select{out}
|
| 569 | 553 |
FROM inventory i1 |
| 570 |
LEFT JOIN inventory i2 ON i1.trans_id = i2.trans_id |
|
| 554 |
LEFT JOIN inventory i2 ON i1.trans_id = i2.trans_id AND i1.id = i2.id
|
|
| 571 | 555 |
LEFT JOIN parts p ON i1.parts_id = p.id |
| 572 | 556 |
LEFT JOIN bin b1 ON i1.bin_id = b1.id |
| 573 | 557 |
LEFT JOIN bin b2 ON i2.bin_id = b2.id |
| ... | ... | |
| 576 | 560 |
LEFT JOIN transfer_type tt ON i1.trans_type_id = tt.id |
| 577 | 561 |
LEFT JOIN project pr ON i1.project_id = pr.id |
| 578 | 562 |
LEFT JOIN employee e ON i1.employee_id = e.id |
| 579 |
WHERE $where_clause i1.qty < 0 AND
|
|
| 563 |
WHERE $where_clause i1.qty != 0 AND tt.direction = 'out' AND
|
|
| 580 | 564 |
i1.trans_id IN ( SELECT i.trans_id FROM inventory i GROUP BY i.trans_id HAVING COUNT(i.trans_id) >= 1 ) |
| 581 | 565 |
GROUP BY $group_clause |
| 582 | 566 |
|
| ... | ... | |
| 584 | 568 |
|
| 585 | 569 |
SELECT DISTINCT $select{in}
|
| 586 | 570 |
FROM inventory i1 |
| 587 |
LEFT JOIN inventory i2 ON i1.trans_id = i2.trans_id |
|
| 571 |
LEFT JOIN inventory i2 ON i1.trans_id = i2.trans_id AND i1.id = i2.id
|
|
| 588 | 572 |
LEFT JOIN parts p ON i1.parts_id = p.id |
| 589 | 573 |
LEFT JOIN bin b1 ON i1.bin_id = b1.id |
| 590 | 574 |
LEFT JOIN bin b2 ON i2.bin_id = b2.id |
| ... | ... | |
| 593 | 577 |
LEFT JOIN transfer_type tt ON i1.trans_type_id = tt.id |
| 594 | 578 |
LEFT JOIN project pr ON i1.project_id = pr.id |
| 595 | 579 |
LEFT JOIN employee e ON i1.employee_id = e.id |
| 596 |
WHERE $where_clause i1.qty > 0 AND
|
|
| 580 |
WHERE $where_clause i1.qty != 0 AND tt.direction = 'in' AND
|
|
| 597 | 581 |
i1.trans_id IN ( SELECT i.trans_id FROM inventory i GROUP BY i.trans_id HAVING COUNT(i.trans_id) >= 1 ) |
| 598 | 582 |
GROUP BY $group_clause |
| 599 |
ORDER BY r_${sort_spec}) AS lines WHERE r_qty>0|;
|
|
| 583 |
ORDER BY r_${sort_spec}) AS lines WHERE r_qty != 0|;
|
|
| 600 | 584 |
|
| 601 |
my @all_vars = (@filter_vars,@filter_vars,@filter_vars);
|
|
| 585 |
my @all_vars = (@filter_vars,@filter_vars); |
|
| 602 | 586 |
|
| 603 | 587 |
if ($filter{limit}) {
|
| 604 | 588 |
$query .= " LIMIT ?"; |
Auch abrufbar als: Unified diff
WH: fix: duplizierte Lagerjournalmengen bei produzierten Erzeugnissen
Analog zu odyn e7850d3d61 und b829d12400
Das Lagerjournal wurde ursprünglich in der Annahme gebaut dass eine
Transaktion maximal 2 Einträge hat. Eine Einlagerung und eine
Auslagerung.
Beim Produzieren von Erzeugnissen werden aber mittlerweile in einer
trans_id mehrere Lagerbewegungen zusammengefasst. Der Self-join auf
inventory für eine rechte und eine linke Seite funktioniert damit nicht
mehr.
Eigentlich müsste man das komplett umbauen, dieser Fix sorgt aber
erstmal dafür dass die Mengen stimmen, in dem der selfjoin exakt auf die
gleiche Zeile passiert. Rückbuchungen und Stornos sind damit auch
korrekt verbucht.