Revision 4a648176
Von Udo Spallek vor mehr als 18 Jahren hinzugefügt
| SL/USTVA.pm | ||
|---|---|---|
|
|
||
|
UNION
|
||
|
|
||
|
SELECT sum(ac.amount
|
||
|
SELECT sum(
|
||
|
CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1
|
||
|
WHEN c.link LIKE '%AP%' THEN ac.amount * 1
|
||
|
END
|
||
|
) AS amount,
|
||
|
c.$category
|
||
|
FROM acc_trans ac
|
||
| ... | ... | |
|
|
||
|
|;
|
||
|
|
||
|
if ($form->{project_id}) {
|
||
|
|
||
|
$query .= qq|
|
||
|
|
||
|
UNION
|
||
|
|
||
|
SELECT SUM(ac.sellprice * ac.qty) AS amount,
|
||
|
c.$category
|
||
|
FROM invoice ac
|
||
|
JOIN ar a ON (a.id = ac.trans_id)
|
||
|
JOIN parts p ON (ac.parts_id = p.id)
|
||
|
JOIN chart c on (p.income_accno_id = c.id)
|
||
|
$dpt_join
|
||
|
-- use transdate from subwhere
|
||
|
WHERE 1 = 1 $subwhere
|
||
|
AND c.category = 'I'
|
||
|
$dpt_where
|
||
|
AND ac.trans_id IN
|
||
|
(
|
||
|
SELECT trans_id
|
||
|
FROM acc_trans
|
||
|
JOIN chart ON (chart_id = id)
|
||
|
WHERE link LIKE '%AR_paid%'
|
||
|
$subwhere
|
||
|
)
|
||
|
|
||
|
$project
|
||
|
GROUP BY c.$category
|
||
|
|
||
|
UNION
|
||
|
|
||
|
SELECT SUM(ac.sellprice) AS amount,
|
||
|
c.$category
|
||
|
FROM invoice ac
|
||
|
JOIN ap a ON (a.id = ac.trans_id)
|
||
|
JOIN parts p ON (ac.parts_id = p.id)
|
||
|
JOIN chart c on (p.expense_accno_id = c.id)
|
||
|
$dpt_join
|
||
|
WHERE 1 = 1 $subwhere
|
||
|
AND c.category = 'E'
|
||
|
$dpt_where
|
||
|
AND ac.trans_id IN
|
||
|
(
|
||
|
SELECT trans_id
|
||
|
FROM acc_trans
|
||
|
JOIN chart ON (chart_id = id)
|
||
|
WHERE link LIKE '%AP_paid%'
|
||
|
$subwhere
|
||
|
)
|
||
|
|
||
|
$project
|
||
|
GROUP BY c.$category
|
||
|
|;
|
||
|
}
|
||
|
|
||
|
} else {
|
||
|
|
||
|
if ($department_id) {
|
||
| ... | ... | |
|
|
||
|
$query = qq|
|
||
|
|
||
|
SELECT sum(ac.amount) AS amount,
|
||
|
SELECT sum(
|
||
|
CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1
|
||
|
WHEN c.link LIKE '%AP%' THEN ac.amount * 1
|
||
|
END
|
||
|
|
||
|
) AS amount,
|
||
|
c.$category
|
||
|
FROM acc_trans ac
|
||
|
JOIN chart c ON (c.id = ac.chart_id)
|
||
| ... | ... | |
|
$project
|
||
|
GROUP BY c.$category
|
||
|
|;
|
||
|
|
||
|
if ($form->{project_id}) {
|
||
|
|
||
|
$query .= qq|
|
||
|
|
||
|
UNION
|
||
|
|
||
|
SELECT SUM(ac.sellprice * ac.qty) AS amount,
|
||
|
c.$category
|
||
|
FROM invoice ac
|
||
|
JOIN ar a ON (a.id = ac.trans_id)
|
||
|
JOIN parts p ON (ac.parts_id = p.id)
|
||
|
JOIN chart c on (p.income_accno_id = c.id)
|
||
|
$dpt_join
|
||
|
-- use transdate from subwhere
|
||
|
WHERE 1 = 1 $subwhere
|
||
|
AND c.category = 'I'
|
||
|
$dpt_where
|
||
|
$project
|
||
|
GROUP BY c.$category
|
||
|
|
||
|
UNION
|
||
|
|
||
|
SELECT SUM(ac.sellprice * ac.qty) * -1 AS amount,
|
||
|
c.$category
|
||
|
FROM invoice ac
|
||
|
JOIN ap a ON (a.id = ac.trans_id)
|
||
|
JOIN parts p ON (ac.parts_id = p.id)
|
||
|
JOIN chart c on (p.expense_accno_id = c.id)
|
||
|
$dpt_join
|
||
|
WHERE 1 = 1 $subwhere
|
||
|
AND c.category = 'E'
|
||
|
$dpt_where
|
||
|
$project
|
||
|
GROUP BY c.$category
|
||
|
|;
|
||
|
|
||
|
}
|
||
|
}
|
||
|
|
||
|
my @accno;
|
||
Auch abrufbar als: Unified diff
Solved Bug 365: Falsche UStVA bei negativem Vorsteuer-Betrag (Teil 3)