Revision 2ae29658
Von Udo Spallek vor etwa 18 Jahren hinzugefügt
| SL/USTVA.pm | ||
|---|---|---|
| 637 | 637 |
my $glwhere = ""; |
| 638 | 638 |
my $subwhere = ""; |
| 639 | 639 |
my $ARwhere = ""; |
| 640 |
my $APwhere = ''; |
|
| 640 | 641 |
my $arwhere = ""; |
| 641 | 642 |
my $item; |
| 642 | 643 |
|
| ... | ... | |
| 645 | 646 |
$subwhere .= " AND transdate >= '$fromdate'"; |
| 646 | 647 |
$glwhere = " AND ac.transdate >= '$fromdate'"; |
| 647 | 648 |
$ARwhere .= " AND acc.transdate >= '$fromdate'"; |
| 649 |
$APwhere .= " AND AP.transdate >= '$fromdate'"; |
|
| 648 | 650 |
} |
| 649 | 651 |
$where .= " AND ac.transdate >= '$fromdate'"; |
| 650 | 652 |
} |
| ... | ... | |
| 653 | 655 |
$where .= " AND ac.transdate <= '$todate'"; |
| 654 | 656 |
$ARwhere .= " AND acc.transdate <= '$todate'"; |
| 655 | 657 |
$subwhere .= " AND transdate <= '$todate'"; |
| 658 |
$APwhere .= " AND AP.transdate <= '$todate'"; |
|
| 656 | 659 |
} |
| 657 | 660 |
|
| 658 | 661 |
if ($department_id) {
|
| ... | ... | |
| 669 | 672 |
AND ac.project_id = $form->{project_id}
|
| 670 | 673 |
|; |
| 671 | 674 |
} |
| 675 |
######################################### |
|
| 676 |
# Method eq 'cash' = IST Versteuerung |
|
| 677 |
######################################### |
|
| 672 | 678 |
|
| 673 |
if ($form->{method} eq 'cash') {
|
|
| 679 |
if ($form->{method} eq 'cash') {
|
|
| 674 | 680 |
|
| 675 | 681 |
$query = qq| |
| 676 |
|
|
| 677 | 682 |
SELECT |
| 678 |
SUM( ac.amount * |
|
| 683 |
-- Alle tatsaechlichen Zahlungseingaenge |
|
| 684 |
-- im Voranmeldezeitraum erfassen |
|
| 685 |
-- (Teilzahlungen werden prozentual auf verschiedene Steuern aufgeteilt) |
|
| 686 |
SUM( ac.amount * |
|
| 679 | 687 |
-- Bezahlt / Rechnungssumme |
| 680 | 688 |
( |
| 681 | 689 |
SELECT SUM(acc.amount) |
| ... | ... | |
| 700 | 708 |
-- Here no where, please. All Transactions ever should be |
| 701 | 709 |
-- testet if they are paied in the USTVA report period. |
| 702 | 710 |
GROUP BY c.pos_ustva |
| 703 |
UNION |
|
| 704 |
|
|
| 705 |
SELECT sum(ac.amount) AS amount, |
|
| 706 |
c.$category |
|
| 707 |
FROM acc_trans ac |
|
| 708 |
JOIN chart c ON (c.id = ac.chart_id) |
|
| 709 |
JOIN ap a ON (a.id = ac.trans_id) |
|
| 710 |
$dpt_join |
|
| 711 |
WHERE $where |
|
| 712 |
$dpt_where |
|
| 713 |
AND ac.trans_id IN |
|
| 714 |
( |
|
| 715 |
SELECT trans_id |
|
| 716 |
FROM acc_trans |
|
| 717 |
JOIN chart ON (chart_id = id) |
|
| 718 |
WHERE link LIKE '%AP_amount%' |
|
| 719 |
$subwhere |
|
| 720 |
) |
|
| 721 |
|
|
| 722 |
$project |
|
| 723 |
GROUP BY c.$category |
|
| 724 |
|
|
| 725 |
UNION |
|
| 726 |
|
|
| 727 |
SELECT sum(ac.amount) AS amount, |
|
| 728 |
c.$category |
|
| 729 |
FROM acc_trans ac |
|
| 730 |
JOIN chart c ON (c.id = ac.chart_id) |
|
| 731 |
JOIN gl a ON (a.id = ac.trans_id) |
|
| 732 |
$dpt_join |
|
| 733 |
WHERE $where |
|
| 734 |
$glwhere |
|
| 735 |
$dpt_from |
|
| 736 |
AND NOT (c.link = 'AR' OR c.link = 'AP') |
|
| 737 |
$project |
|
| 738 |
GROUP BY c.$category |
|
| 739 |
|
|
| 740 |
|; |
|
| 741 | 711 |
|
| 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 |
} else {
|
|
| 712 |
UNION -- alle Ausgaben AP erfassen |
|
| 713 |
|
|
| 714 |
SELECT |
|
| 715 |
sum(ac.amount) AS amount, pos_ustva |
|
| 716 |
FROM acc_trans ac |
|
| 717 |
JOIN AP ON (AP.id = ac.trans_id ) |
|
| 718 |
JOIN chart c ON (c.id = ac.chart_id AND pos_ustva NOT LIKE '') |
|
| 719 |
WHERE |
|
| 720 |
1=1 |
|
| 721 |
$APwhere |
|
| 722 |
$dpt_where |
|
| 723 |
$project |
|
| 724 |
GROUP BY pos_ustva |
|
| 725 |
|
|
| 726 |
UNION -- alle Ausgaben und Einnahmen direkter gl Buchungen erfassen |
|
| 727 |
|
|
| 728 |
SELECT sum |
|
| 729 |
( |
|
| 730 |
CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1 |
|
| 731 |
WHEN c.link LIKE '%AP%' THEN ac.amount * 1 |
|
| 732 |
END |
|
| 733 |
) AS amount, c.$category |
|
| 734 |
FROM acc_trans ac |
|
| 735 |
JOIN chart c ON (c.id = ac.chart_id) |
|
| 736 |
JOIN gl a ON (a.id = ac.trans_id) |
|
| 737 |
$dpt_join |
|
| 738 |
WHERE $where |
|
| 739 |
$dpt_from |
|
| 740 |
AND NOT (c.link = 'AR' OR c.link = 'AP') |
|
| 741 |
$project |
|
| 742 |
GROUP BY c.$category |
|
| 743 |
|; |
|
| 744 |
|
|
| 745 |
} else {
|
|
| 746 |
######################################### |
|
| 747 |
# Method eq 'accrual' = Soll Versteuerung |
|
| 748 |
######################################### |
|
| 798 | 749 |
|
| 799 | 750 |
if ($department_id) {
|
| 800 | 751 |
$dpt_join = qq| |
| ... | ... | |
| 806 | 757 |
} |
| 807 | 758 |
|
| 808 | 759 |
$query = qq| |
| 809 |
|
|
| 810 |
SELECT sum(ac.amount) AS amount, |
|
| 811 |
c.$category |
|
| 812 |
FROM acc_trans ac |
|
| 813 |
JOIN chart c ON (c.id = ac.chart_id) |
|
| 814 |
$dpt_join |
|
| 815 |
WHERE $where |
|
| 816 |
$dpt_where |
|
| 817 |
$project |
|
| 818 |
GROUP BY c.$category |
|
| 819 |
|; |
|
| 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 |
} |
|
| 760 |
SELECT sum |
|
| 761 |
( |
|
| 762 |
CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1 |
|
| 763 |
WHEN c.link LIKE '%AP%' THEN ac.amount * 1 |
|
| 764 |
END |
|
| 765 |
) AS amount, c.$category |
|
| 766 |
FROM acc_trans ac |
|
| 767 |
JOIN chart c ON (c.id = ac.chart_id) |
|
| 768 |
$dpt_join |
|
| 769 |
WHERE $where |
|
| 770 |
$dpt_where |
|
| 771 |
$project |
|
| 772 |
GROUP BY c.$category |
|
| 773 |
|; |
|
| 858 | 774 |
} |
| 859 | 775 |
|
| 860 | 776 |
my @accno; |
| 861 | 777 |
my $accno; |
| 862 | 778 |
my $ref; |
| 863 | 779 |
|
| 864 |
#print $query; |
|
| 780 |
# Show all $query in Debuglevel LXDebug::QUERY |
|
| 781 |
$callingdetails = (caller (0))[3]; |
|
| 782 |
$main::lxdebug->message(LXDebug::QUERY, "$callingdetails \$query=\n $query"); |
|
| 783 |
|
|
| 865 | 784 |
my $sth = $dbh->prepare($query); |
| 866 | 785 |
$sth->execute || $form->dberror($query); |
| 867 | 786 |
|
| 868 | 787 |
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 869 |
if ($ref->{amount} < 0) {
|
|
| 788 |
# Bug 365 solved?! |
|
| 789 |
# if ($ref->{amount} < 0) {
|
|
| 870 | 790 |
$ref->{amount} *= -1;
|
| 871 |
} |
|
| 791 |
# }
|
|
| 872 | 792 |
if ($category eq "pos_bwa") {
|
| 873 | 793 |
if ($last_period) {
|
| 874 | 794 |
$form->{ $ref->{$category} }{kumm} += $ref->{amount};
|
Auch abrufbar als: Unified diff
Solve Bug 425: USTVA Vorsteuer calculation was wrong. Tested by balzer:
http://lx-office.org/forum/forum_entry.php?id=3018