Revision 4a648176
Von Udo Spallek vor mehr als 18 Jahren hinzugefügt
| SL/USTVA.pm | ||
|---|---|---|
| 724 | 724 |
|
| 725 | 725 |
UNION |
| 726 | 726 |
|
| 727 |
SELECT sum(ac.amount |
|
| 727 |
SELECT sum( |
|
| 728 |
CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1 |
|
| 729 |
WHEN c.link LIKE '%AP%' THEN ac.amount * 1 |
|
| 730 |
END |
|
| 728 | 731 |
) AS amount, |
| 729 | 732 |
c.$category |
| 730 | 733 |
FROM acc_trans ac |
| ... | ... | |
| 739 | 742 |
|
| 740 | 743 |
|; |
| 741 | 744 |
|
| 742 |
if ($form->{project_id}) {
|
|
| 743 |
|
|
| 744 |
$query .= qq| |
|
| 745 |
|
|
| 746 |
UNION |
|
| 747 |
|
|
| 748 |
SELECT SUM(ac.sellprice * ac.qty) AS amount, |
|
| 749 |
c.$category |
|
| 750 |
FROM invoice ac |
|
| 751 |
JOIN ar a ON (a.id = ac.trans_id) |
|
| 752 |
JOIN parts p ON (ac.parts_id = p.id) |
|
| 753 |
JOIN chart c on (p.income_accno_id = c.id) |
|
| 754 |
$dpt_join |
|
| 755 |
-- use transdate from subwhere |
|
| 756 |
WHERE 1 = 1 $subwhere |
|
| 757 |
AND c.category = 'I' |
|
| 758 |
$dpt_where |
|
| 759 |
AND ac.trans_id IN |
|
| 760 |
( |
|
| 761 |
SELECT trans_id |
|
| 762 |
FROM acc_trans |
|
| 763 |
JOIN chart ON (chart_id = id) |
|
| 764 |
WHERE link LIKE '%AR_paid%' |
|
| 765 |
$subwhere |
|
| 766 |
) |
|
| 767 |
|
|
| 768 |
$project |
|
| 769 |
GROUP BY c.$category |
|
| 770 |
|
|
| 771 |
UNION |
|
| 772 |
|
|
| 773 |
SELECT SUM(ac.sellprice) AS amount, |
|
| 774 |
c.$category |
|
| 775 |
FROM invoice ac |
|
| 776 |
JOIN ap a ON (a.id = ac.trans_id) |
|
| 777 |
JOIN parts p ON (ac.parts_id = p.id) |
|
| 778 |
JOIN chart c on (p.expense_accno_id = c.id) |
|
| 779 |
$dpt_join |
|
| 780 |
WHERE 1 = 1 $subwhere |
|
| 781 |
AND c.category = 'E' |
|
| 782 |
$dpt_where |
|
| 783 |
AND ac.trans_id IN |
|
| 784 |
( |
|
| 785 |
SELECT trans_id |
|
| 786 |
FROM acc_trans |
|
| 787 |
JOIN chart ON (chart_id = id) |
|
| 788 |
WHERE link LIKE '%AP_paid%' |
|
| 789 |
$subwhere |
|
| 790 |
) |
|
| 791 |
|
|
| 792 |
$project |
|
| 793 |
GROUP BY c.$category |
|
| 794 |
|; |
|
| 795 |
} |
|
| 796 |
|
|
| 797 | 745 |
} else {
|
| 798 | 746 |
|
| 799 | 747 |
if ($department_id) {
|
| ... | ... | |
| 807 | 755 |
|
| 808 | 756 |
$query = qq| |
| 809 | 757 |
|
| 810 |
SELECT sum(ac.amount) AS amount, |
|
| 758 |
SELECT sum( |
|
| 759 |
CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1 |
|
| 760 |
WHEN c.link LIKE '%AP%' THEN ac.amount * 1 |
|
| 761 |
END |
|
| 762 |
|
|
| 763 |
) AS amount, |
|
| 811 | 764 |
c.$category |
| 812 | 765 |
FROM acc_trans ac |
| 813 | 766 |
JOIN chart c ON (c.id = ac.chart_id) |
| ... | ... | |
| 817 | 770 |
$project |
| 818 | 771 |
GROUP BY c.$category |
| 819 | 772 |
|; |
| 820 |
|
|
| 821 |
if ($form->{project_id}) {
|
|
| 822 |
|
|
| 823 |
$query .= qq| |
|
| 824 |
|
|
| 825 |
UNION |
|
| 826 |
|
|
| 827 |
SELECT SUM(ac.sellprice * ac.qty) AS amount, |
|
| 828 |
c.$category |
|
| 829 |
FROM invoice ac |
|
| 830 |
JOIN ar a ON (a.id = ac.trans_id) |
|
| 831 |
JOIN parts p ON (ac.parts_id = p.id) |
|
| 832 |
JOIN chart c on (p.income_accno_id = c.id) |
|
| 833 |
$dpt_join |
|
| 834 |
-- use transdate from subwhere |
|
| 835 |
WHERE 1 = 1 $subwhere |
|
| 836 |
AND c.category = 'I' |
|
| 837 |
$dpt_where |
|
| 838 |
$project |
|
| 839 |
GROUP BY c.$category |
|
| 840 |
|
|
| 841 |
UNION |
|
| 842 |
|
|
| 843 |
SELECT SUM(ac.sellprice * ac.qty) * -1 AS amount, |
|
| 844 |
c.$category |
|
| 845 |
FROM invoice ac |
|
| 846 |
JOIN ap a ON (a.id = ac.trans_id) |
|
| 847 |
JOIN parts p ON (ac.parts_id = p.id) |
|
| 848 |
JOIN chart c on (p.expense_accno_id = c.id) |
|
| 849 |
$dpt_join |
|
| 850 |
WHERE 1 = 1 $subwhere |
|
| 851 |
AND c.category = 'E' |
|
| 852 |
$dpt_where |
|
| 853 |
$project |
|
| 854 |
GROUP BY c.$category |
|
| 855 |
|; |
|
| 856 |
|
|
| 857 |
} |
|
| 858 | 773 |
} |
| 859 | 774 |
|
| 860 | 775 |
my @accno; |
Auch abrufbar als: Unified diff
Solved Bug 365: Falsche UStVA bei negativem Vorsteuer-Betrag (Teil 3)