Revision d72df9dc
Von Udo Spallek vor fast 18 Jahren hinzugefügt
| SL/USTVA.pm | ||
|---|---|---|
| 715 | 715 |
( |
| 716 | 716 |
SELECT SUM(acc.amount) |
| 717 | 717 |
FROM acc_trans acc |
| 718 |
INNER JOIN chart c ON (acc.chart_id = c.id AND c.link like '%AR_paid%') |
|
| 718 |
INNER JOIN chart c ON (acc.chart_id = c.id |
|
| 719 |
AND c.link like '%AR_paid%') |
|
| 719 | 720 |
WHERE |
| 720 | 721 |
1=1 |
| 721 | 722 |
$ARwhere |
| ... | ... | |
| 728 | 729 |
) AS amount, |
| 729 | 730 |
tk.pos_ustva |
| 730 | 731 |
FROM acc_trans ac |
| 731 |
LEFT JOIN chart c ON (c.id = ac.chart_id) |
|
| 732 |
LEFT JOIN ar ON (ar.id = ac.trans_id) |
|
| 732 |
LEFT JOIN chart c ON (c.id = ac.chart_id)
|
|
| 733 |
LEFT JOIN ar ON (ar.id = ac.trans_id)
|
|
| 733 | 734 |
LEFT JOIN taxkeys tk ON ( |
| 734 | 735 |
tk.id = ( |
| 735 | 736 |
SELECT id FROM taxkeys |
| 736 |
WHERE chart_id=ac.chart_id |
|
| 737 |
AND taxkey_id=ac.taxkey |
|
| 738 |
|
|
| 737 |
WHERE chart_id = ac.chart_id |
|
| 738 |
AND taxkey_id = ac.taxkey |
|
| 739 | 739 |
AND startdate <= COALESCE(ar.deliverydate, ar.transdate) |
| 740 | 740 |
ORDER BY startdate DESC LIMIT 1 |
| 741 | 741 |
) |
| ... | ... | |
| 749 | 749 |
UNION -- alle Ausgaben AP erfassen |
| 750 | 750 |
|
| 751 | 751 |
SELECT |
| 752 |
sum(ac.amount) AS amount, pos_ustva |
|
| 752 |
sum(ac.amount) AS amount, |
|
| 753 |
tk.pos_ustva |
|
| 753 | 754 |
FROM acc_trans ac |
| 754 | 755 |
JOIN AP ON (AP.id = ac.trans_id ) |
| 755 |
JOIN chart c ON (c.id = ac.chart_id AND pos_ustva NOT LIKE '') |
|
| 756 |
JOIN chart c ON (c.id = ac.chart_id) |
|
| 757 |
LEFT JOIN taxkeys tk ON ( |
|
| 758 |
tk.id = ( |
|
| 759 |
SELECT id FROM taxkeys |
|
| 760 |
WHERE chart_id=ac.chart_id |
|
| 761 |
AND taxkey_id=ac.taxkey |
|
| 762 |
AND startdate <= COALESCE(AP.transdate) |
|
| 763 |
ORDER BY startdate DESC LIMIT 1 |
|
| 764 |
) |
|
| 765 |
) |
|
| 756 | 766 |
WHERE |
| 757 | 767 |
1=1 |
| 758 | 768 |
$APwhere |
| 759 | 769 |
$dpt_where |
| 760 | 770 |
$project |
| 761 |
GROUP BY pos_ustva |
|
| 771 |
GROUP BY tk.pos_ustva
|
|
| 762 | 772 |
|
| 763 | 773 |
UNION -- alle Ausgaben und Einnahmen direkter gl Buchungen erfassen |
| 764 | 774 |
|
| 765 | 775 |
SELECT sum |
| 766 |
( |
|
| 767 |
CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1 |
|
| 768 |
WHEN c.link LIKE '%AP%' THEN ac.amount * 1 |
|
| 769 |
END |
|
| 770 |
) AS amount, c.$category |
|
| 776 |
( - ac.amount) AS amount, |
|
| 777 |
tk.pos_ustva |
|
| 771 | 778 |
FROM acc_trans ac |
| 772 | 779 |
JOIN chart c ON (c.id = ac.chart_id) |
| 773 | 780 |
JOIN gl a ON (a.id = ac.trans_id) |
| 781 |
LEFT JOIN taxkeys tk ON ( |
|
| 782 |
tk.id = ( |
|
| 783 |
SELECT id FROM taxkeys |
|
| 784 |
WHERE chart_id=ac.chart_id |
|
| 785 |
AND taxkey_id=ac.taxkey |
|
| 786 |
|
|
| 787 |
AND startdate <= COALESCE(ac.transdate) |
|
| 788 |
ORDER BY startdate DESC LIMIT 1 |
|
| 789 |
) |
|
| 790 |
) |
|
| 791 |
|
|
| 774 | 792 |
$dpt_join |
| 775 | 793 |
WHERE $where |
| 776 | 794 |
$dpt_from |
| 777 |
AND NOT (c.link = 'AR' OR c.link = 'AP') |
|
| 778 | 795 |
$project |
| 779 |
GROUP BY c.$category
|
|
| 796 |
GROUP BY tk.pos_ustva
|
|
| 780 | 797 |
|; |
| 781 | 798 |
|
| 782 | 799 |
} else {
|
Auch abrufbar als: Unified diff
Bug 526 solved. USTVA: Die Abfrage der Dialogbuchungen war falsch.