Revision 94a8c9a8
Von Bernd Bleßmann vor etwa 1 Jahr hinzugefügt
SL/OE.pm | ||
---|---|---|
92 | 92 |
my %billed_amount; |
93 | 93 |
my %billed_netamount; |
94 | 94 |
if ($form->{l_remaining_amount} || $form->{l_remaining_netamount}) { |
95 |
$query = <<''; |
|
96 |
SELECT from_id, ar.amount, ar.netamount FROM ( |
|
95 |
my $arap = $form->{vc} eq "customer" ? "ar" : "ap"; |
|
96 |
|
|
97 |
$query = <<"SQL"; |
|
98 |
SELECT from_id, ${arap}.amount, $arap.netamount FROM ( |
|
97 | 99 |
SELECT from_id, to_id |
98 | 100 |
FROM record_links |
99 |
WHERE from_table = 'oe' AND to_table = 'ar'
|
|
101 |
WHERE from_table = 'oe' AND to_table = '$arap'
|
|
100 | 102 |
UNION |
101 | 103 |
SELECT rl1.from_id, rl2.to_id |
102 | 104 |
FROM record_links rl1 |
103 | 105 |
LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id) |
104 |
WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'
|
|
106 |
WHERE rl1.from_table = 'oe' AND rl2.to_table = '$arap'
|
|
105 | 107 |
UNION |
106 | 108 |
SELECT rl1.from_id, rl3.to_id |
107 | 109 |
FROM record_links rl1 |
108 | 110 |
JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id) |
109 | 111 |
JOIN record_links rl3 ON (rl2.to_table = rl3.from_table AND rl2.to_id = rl3.from_id) |
110 |
WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar' AND rl3.to_table = 'ar'
|
|
112 |
WHERE rl1.from_table = 'oe' AND rl2.to_table = '$arap' AND rl3.to_table = '$arap'
|
|
111 | 113 |
) rl |
112 |
LEFT JOIN ar ON ar.id = rl.to_id |
|
114 |
LEFT JOIN $arap ON $arap.id = rl.to_id |
|
115 |
SQL |
|
113 | 116 |
|
114 | 117 |
for my $ref (@{ selectall_hashref_query($form, $dbh, $query) }) { |
115 | 118 |
$billed_amount{ $ref->{from_id}} += $ref->{amount}; |
Auch abrufbar als: Unified diff
Offene Restbeträge optional auch in Lieferanten-Auftragsbericht anzeigen
Die Häkchen waren auf der EK-Seite da, aber diese wurde in der SQL-Abfrage
nicht berücksichtigt.