From 82ee22347be2f2ee96d09a9fd0b45710f9552582 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Stephan=20K=C3=B6hler?= Date: Sat, 7 Jan 2006 12:13:46 +0000 Subject: [PATCH] =?utf8?q?Merge=20von=20704,707,730,731=20aus=20unstable:?= =?utf8?q?=20USTVA=20Bugfix=20-704=20=20Diverse=20USTVA=20Bugs=20geloest?= =?utf8?q?=20=20Solve=20Bug:=20=2049:=20UST=20wurde=20berechnet,=20jetzt?= =?utf8?q?=20ausgelesen,=20mit=20Patchen=20von=20Andre=20Schubert=20THX=20?= =?utf8?q?=20Solve=20Bug:=20164:=20Grunds=C3=A4tzliche=20Berechnung=20der?= =?utf8?q?=20UST=20=20Solve=20Bug:=20173:=20Teilbezahlte=20Rechnungen=20be?= =?utf8?q?i=20IST-Versteuerung=20sollten=20jetzt=20klappen,=20THX=20Yvonne?= =?utf8?q?=20Einberger=20=20Vorlagen=20ueberarbeitet,=20sub=20ustva=20und?= =?utf8?q?=20sub=20get=5Faccounts=5Fustva=20in=20ustva.pm=20verlegt.=20=20?= =?utf8?q?Alle=20Variablen=20der=20USTVA=20durchg=C3=A4ngig=20nutzbar=20ge?= =?utf8?q?macht.=20-707=20=20Nachtrag=20zu=20rev=20704.=20USTVA=20Berechnu?= =?utf8?q?ng=20kosmetisch=20ver=C3=A4ndert.=20-730=20=20Nachtrag=20zu=20R7?= =?utf8?q?04=20'Diverse=20USTVA=20Bugs=20geloest'=20-731=20Solved=20Bug=20?= =?utf8?q?237.=20USTVA=20Vorsteuer=20wird=20nicht=20richtig=20berechnet?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- SL/RP.pm | 20 +- SL/USTVA.pm | 330 ++++++++++++++++++++++++++++ bin/mozilla/am.pl | 48 +++- bin/mozilla/ustva.pl | 79 +++---- doc/ustva.html | 8 +- sql/Germany-DATEV-SKR03EU-chart.sql | 7 +- sql/Pg-upgrade-2.1.1-2.1.2.sql | 10 +- sql/liste.sql | 4 +- templates/German-ustva-2004.tex | 21 +- templates/German-ustva-2005.tex | 24 +- templates/German-ustva-2006.tex | 24 +- templates/German-ustva.html | 212 ++++++++++++++++-- 12 files changed, 668 insertions(+), 119 deletions(-) diff --git a/SL/RP.pm b/SL/RP.pm index f7afbb0b1..6c328326c 100644 --- a/SL/RP.pm +++ b/SL/RP.pm @@ -1336,7 +1336,7 @@ sub get_accounts_g { my @accno; my $accno; my $ref; - + #print $query; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); @@ -2506,7 +2506,8 @@ sub ustva { my $last_period = 0; my $category = "pos_ustva"; - my @categories_cent = qw(51r 86r 97r 93r 96 66 43 45 53 62 65 67); + my @categories_cent = qw(51r 511 86r 861 97r 971 93r 931 + 96 66 43 45 53 62 65 67); my @categories_euro = qw(48 51 86 91 97 93 94); $form->{decimalplaces} *= 1; @@ -2544,12 +2545,15 @@ sub ustva { # } # # } - - $form->{"51r"} = $form->{"51"} * 0.16; - $form->{"86r"} = $form->{"86"} * 0.07; - $form->{"97r"} = $form->{"97"} * 0.16; - $form->{"93r"} = $form->{"93"} * 0.07; - $form->{"96"} = $form->{"94"} * 0.16; + + # + # Berechnung der USTVA Formularfelder + # + $form->{"51r"} = $form->{"511"}; + $form->{"86r"} = $form->{"861"}; + $form->{"97r"} = $form->{"971"}; + $form->{"93r"} = $form->{"931"}; + #$form->{"96"} = $form->{"94"} * 0.16; $form->{"43"} = $form->{"51r"} + $form->{"86r"} + $form->{"97r"} + $form->{"93r"} + $form->{"96"}; diff --git a/SL/USTVA.pm b/SL/USTVA.pm index ff6355635..af1896602 100644 --- a/SL/USTVA.pm +++ b/SL/USTVA.pm @@ -556,4 +556,334 @@ sub process_query { $main::lxdebug->leave_sub(); } + +sub ustva { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $last_period = 0; + my $category = "pos_ustva"; + my @categories_cent = qw(511 861 36 80 971 931 98 96 53 74 + 85 65 66 61 62 67 63 64 59 69 39 83 + Z43 Z45 Z53 Z62 Z65 Z67); + + my @categories_euro = qw(41 44 49 43 48 51 86 35 77 76 91 97 93 + 95 94 42 60 45 52 73 84); + + $form->{decimalplaces} *= 1; + + foreach $item (@categories_cent) { + $form->{"$item"} = 0; + } + foreach $item (@categories_euro) { + $form->{"$item"} = 0; + } + + + &get_accounts_ustva($dbh, $last_period, $form->{fromdate}, $form->{todate}, + $form, $category); + + + # + # Berechnung der USTVA Formularfelder + # + $form->{"51r"} = $form->{"511"}; + $form->{"86r"} = $form->{"861"}; + $form->{"97r"} = $form->{"971"}; + $form->{"93r"} = $form->{"931"}; + $form->{"Z43"} = $form->{"511"}+ $form->{"861"} + + $form->{"36"} + $form->{"80"} + + $form->{"971"}+ $form->{"931"} + + $form->{"96"} + $form->{"98"}; + $form->{"Z45"} = $form->{"Z43"}; + $form->{"Z53"} = $form->{"Z43"}; + $form->{"Z62"} = $form->{"Z43"}- $form->{"66"} - + $form->{"61"} - $form->{"62"} - + $form->{"63"} - $form->{"64"} - + $form->{"59"}; + $form->{"Z65"} = $form->{"Z62"}- $form->{"69"}; + $form->{"83"} = $form->{"Z65"}- $form->{"39"}; + + foreach $item (@categories_cent) { + $form->{$item} = + $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2), 2, '0'); + } + + foreach $item (@categories_euro) { + $form->{$item} = + $form->format_amount($myconfig, $form->round_amount($form->{$item}, 0), 0, '0'); + } + + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub get_accounts_ustva { + $main::lxdebug->enter_sub(); + + my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_; + + my ($null, $department_id) = split /--/, $form->{department}; + + my $query; + my $dpt_where; + my $dpt_join; + my $project; + my $where = "1 = 1"; + my $glwhere = ""; + my $subwhere = ""; + my $ARwhere = ""; + my $arwhere = ""; + my $item; + + if ($fromdate) { + if ($form->{method} eq 'cash') { + $subwhere .= " AND transdate >= '$fromdate'"; + $glwhere = " AND ac.transdate >= '$fromdate'"; + $ARwhere .= " AND acc.transdate >= '$fromdate'"; + $where .= " AND ac.transdate >= '$fromdate'"; + + } + } + + if ($todate) { + $where .= " AND ac.transdate <= '$todate'"; + $ARwhere .= " AND acc.transdate <= '$todate'"; + $subwhere .= " AND transdate <= '$todate'"; + } + + if ($department_id) { + $dpt_join = qq| + JOIN department t ON (a.department_id = t.id) + |; + $dpt_where = qq| + AND t.id = $department_id + |; + } + + if ($form->{project_id}) { + $project = qq| + AND ac.project_id = $form->{project_id} + |; + } + + if ($form->{method} eq 'cash') { + + $query = qq| + + SELECT + SUM( ac.amount * + -- Bezahlt / Rechnungssumme + ( + SELECT SUM(acc.amount) + FROM acc_trans acc + INNER JOIN chart c ON (acc.chart_id = c.id AND c.link like '%AR_paid%') + WHERE + 1=1 + $ARwhere + AND acc.trans_id = ac.trans_id + ) + / + ( + select amount from ar where id = ac.trans_id + ) + ) AS amount, + c.pos_ustva + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + --JOIN ar ON (ar.id = ac.trans_id) + where + 1=1 + -- Here no where, please. All Transactions ever should be + -- testet if they are paied in the USTVA report period. + GROUP BY c.pos_ustva + UNION + + SELECT sum(ac.amount) AS amount, + c.$category + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + JOIN ap a ON (a.id = ac.trans_id) + $dpt_join + WHERE $where + $dpt_where + AND ac.trans_id IN + ( + SELECT trans_id + FROM acc_trans + JOIN chart ON (chart_id = id) + WHERE link LIKE '%AP_amount%' + $subwhere + ) + + $project + GROUP BY c.$category + + UNION + + SELECT sum(ac.amount) AS amount, + c.$category + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + JOIN gl a ON (a.id = ac.trans_id) + $dpt_join + WHERE $where + $glwhere + $dpt_from + AND NOT (c.link = 'AR' OR c.link = 'AP') + $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 + 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) { + $dpt_join = qq| + JOIN dpt_trans t ON (t.trans_id = ac.trans_id) + |; + $dpt_where = qq| + AND t.department_id = $department_id + |; + } + + $query = qq| + + SELECT sum(ac.amount) AS amount, + c.$category + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + $dpt_join + WHERE $where + $dpt_where + $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; + my $accno; + my $ref; + #print $query; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + if ($ref->{amount} < 0) { + $ref->{amount} *= -1; + } + if ($category eq "pos_bwa") { + if ($last_period) { + $form->{ $ref->{$category} }{kumm} += $ref->{amount}; + } else { + $form->{ $ref->{$category} }{jetzt} += $ref->{amount}; + } + } else { + $form->{ $ref->{$category} } += $ref->{amount}; + } + } + $sth->finish; + + $main::lxdebug->leave_sub(); +} + + 1; diff --git a/bin/mozilla/am.pl b/bin/mozilla/am.pl index 48b875a57..c936bc19b 100644 --- a/bin/mozilla/am.pl +++ b/bin/mozilla/am.pl @@ -111,14 +111,46 @@ sub account_header { |; $form->{selectustva} = "