X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FRP.pm;h=88f4ebe55de72d213780ec5ae35166ced9c30ac1;hb=4e2b549a28f09fdc749f380a3e8c458c4ff53273;hp=ebc0dd68881e82a484dc3dd227aa638c71b15e1f;hpb=a0ad15b238b5cf810795e36b73a9e9d6f51c3029;p=kivitendo-erp.git diff --git a/SL/RP.pm b/SL/RP.pm index ebc0dd688..88f4ebe55 100644 --- a/SL/RP.pm +++ b/SL/RP.pm @@ -37,9 +37,9 @@ package RP; use SL::DBUtils; use Data::Dumper; use List::Util qw(sum); -use strict; -use warnings; +# use warnings; +use strict; # new implementation of balance sheet # readme! @@ -215,6 +215,8 @@ sub get_accounts { } if ($form->{project_id}) { + # Diese Bedingung wird derzeit niemals wahr sein, da man in Bericht->Bilanz keine + # Projekte auswählen kann $project = qq| AND (ac.project_id = | . conv_i($form->{project_id}, 'NULL') . qq|) |; } @@ -276,6 +278,7 @@ sub get_accounts { GROUP BY c.accno, c.description, c.category |; if ($form->{project_id}) { + # s.o. keine Projektauswahl in Bilanz $query .= qq| UNION ALL @@ -325,10 +328,12 @@ sub get_accounts { } } else { # if ($form->{method} eq 'cash') - 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 |; - } + # ich sehe keinen sinn das nochmal explizit ohne conv_i aufzurufen + # bitte prüfen und löschen jan 15.11.2009 + # 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 c.accno, sum(ac.amount) AS amount, c.description, c.category @@ -342,6 +347,7 @@ sub get_accounts { GROUP BY c.accno, c.description, c.category |; if ($form->{project_id}) { + # s.o. keine Projektauswahl in Bilanz $query .= qq| UNION ALL @@ -439,6 +445,7 @@ sub get_accounts_g { my $glwhere = ""; my $prwhere = ""; my $subwhere = ""; + my $inwhere = ""; my $item; if ($fromdate) { @@ -447,6 +454,7 @@ sub get_accounts_g { $subwhere .= " AND (transdate >= $fromdate)"; $glwhere = " AND (ac.transdate >= $fromdate)"; $prwhere = " AND (a.transdate >= $fromdate)"; + $inwhere = " AND (acc.transdate >= $fromdate)"; } else { $where .= " AND (ac.transdate >= $fromdate)"; } @@ -457,6 +465,7 @@ sub get_accounts_g { $subwhere .= " AND (transdate <= $todate)"; $where .= " AND (ac.transdate <= $todate)"; $prwhere .= " AND (a.transdate <= $todate)"; + $inwhere .= " AND (acc.transdate <= $todate)"; } if ($department_id) { @@ -468,9 +477,42 @@ sub get_accounts_g { $project = qq| AND (ac.project_id = | . conv_i($form->{project_id}) . qq|) |; } +# +# GUV patch by Ronny Rentner (Bug 1190) +# +# GUV IST-Versteuerung +# +# Alle tatsaechlichen _Zahlungseingaenge_ +# im Zeitraum erfassen +# (Teilzahlungen werden prozentual auf verschiedene Steuern aufgeteilt) +# +# + if ($form->{method} eq 'cash') { $query = qq| + SELECT SUM( ac.amount * + (SELECT SUM(acc.amount) * -1 + FROM acc_trans acc + INNER JOIN chart c ON (acc.chart_id = c.id AND c.link LIKE '%AR_paid%') + WHERE 1=1 $inwhere AND acc.trans_id = ac.trans_id) + / (SELECT amount FROM ar WHERE id = ac.trans_id) + ) AS amount, c.pos_eur + FROM acc_trans ac + LEFT JOIN chart c ON (c.id = ac.chart_id) + LEFT JOIN ar ON (ar.id = ac.trans_id) + LEFT JOIN taxkeys tk ON (tk.id = ( + SELECT id FROM taxkeys + WHERE chart_id = ac.chart_id + AND startdate <= COALESCE(ar.deliverydate,ar.transdate) + ORDER BY startdate DESC LIMIT 1 + ) + ) + WHERE ac.trans_id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE 1=1 $subwhere) + + GROUP BY c.pos_eur + +/* SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category FROM acc_trans ac JOIN chart c ON (c.id = ac.chart_id) @@ -480,7 +522,7 @@ sub get_accounts_g { 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.amount * chart_category_to_sgn(c.category)) AS amount, c.$category @@ -537,10 +579,10 @@ sub get_accounts_g { } } else { # if ($form->{method} eq 'cash') - if ($department_id) { - $dpt_join = qq| JOIN dpt_trans t ON (t.trans_id = ac.trans_id) |; - $dpt_where = qq| AND (t.department_id = | . conv_i($department_id, 'NULL') . qq|) |; - } + # s.o. jan 15.11.2009 +# if ($department_id) { +# ($dpt_join, $dpt_where) = sql_department($department_id); +# } $query = qq| SELECT sum(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category @@ -588,9 +630,7 @@ sub get_accounts_g { my $accno; my $ref; - my $sth = prepare_execute_query($form, $dbh, $query); - - while ($ref = $sth->fetchrow_hashref("NAME_lc")) { + foreach my $ref (selectall_hashref_query($form, $dbh, $query)) { if ($category eq "pos_bwa") { if ($last_period) { $form->{ $ref->{$category} }{kumm} += $ref->{amount}; @@ -601,7 +641,6 @@ sub get_accounts_g { $form->{ $ref->{$category} } += $ref->{amount}; } } - $sth->finish; $main::lxdebug->leave_sub(); } @@ -609,7 +648,7 @@ sub get_accounts_g { sub trial_balance { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form, %options) = @_; my $dbh = $form->dbconnect($myconfig); @@ -644,40 +683,75 @@ sub trial_balance { if ($form->{method} eq "cash") { - $acc_cash_where = qq| AND (ac.trans_id IN (SELECT id FROM ar WHERE datepaid>='$form->{fromdate}' AND datepaid<='$form->{todate}' UNION SELECT id FROM ap WHERE datepaid>='$form->{fromdate}' AND datepaid<='$form->{todate}' UNION SELECT id FROM gl WHERE transdate>='$form->{fromdate}' AND transdate<='$form->{todate}')) |; + $acc_cash_where = + qq| AND (ac.trans_id IN ( + SELECT id + FROM ar + WHERE datepaid >= '$form->{fromdate}' + AND datepaid <= '$form->{todate}' + + UNION + + SELECT id + FROM ap + WHERE datepaid >= '$form->{fromdate}' + AND datepaid <= '$form->{todate}' + + UNION + + SELECT id + FROM gl + WHERE transdate >= '$form->{fromdate}' + AND transdate <= '$form->{todate}' + )) |; # $ar_ap_cash_where = qq| AND (a.datepaid>='$form->{fromdate}' AND a.datepaid<='$form->{todate}') |; } - # get beginning balances - $query = - qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount, c.description - FROM acc_trans ac - LEFT JOIN chart c ON (ac.chart_id = c.id) - $dpt_join - WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction $acc_cash_where - $dpt_where - $project - GROUP BY c.accno, c.category, c.description |; + if ($options{beginning_balances}) { + foreach my $prefix (qw(from to)) { + next if ($form->{"${prefix}date"}); + + my $min_max = $prefix eq 'from' ? 'min' : 'max'; + $query = qq|SELECT ${min_max}(transdate) + FROM acc_trans ac + $dpt_join + WHERE (1 = 1) + $dpt_where + $project|; + ($form->{"${prefix}date"}) = selectfirst_array_query($form, $dbh, $query); + } + + # get beginning balances + $query = + qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount, c.description + FROM acc_trans ac + LEFT JOIN chart c ON (ac.chart_id = c.id) + $dpt_join + WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction + $dpt_where + $project + GROUP BY c.accno, c.category, c.description |; - $sth = prepare_execute_query($form, $dbh, $query, $form->{fromdate}); + $sth = prepare_execute_query($form, $dbh, $query, $form->{fromdate}); - while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { - if ($ref->{amount} != 0 || $form->{all_accounts}) { - $trb{ $ref->{accno} }{description} = $ref->{description}; - $trb{ $ref->{accno} }{charttype} = 'A'; + if ($ref->{amount} != 0 || $form->{all_accounts}) { + $trb{ $ref->{accno} }{description} = $ref->{description}; + $trb{ $ref->{accno} }{charttype} = 'A'; + $trb{ $ref->{accno} }{beginning_balance} = $ref->{amount}; - if ($ref->{amount} > 0) { - $trb{ $ref->{accno} }{haben_eb} = $ref->{amount}; - } else { - $trb{ $ref->{accno} }{soll_eb} = $ref->{amount} * -1; + if ($ref->{amount} > 0) { + $trb{ $ref->{accno} }{haben_eb} = $ref->{amount}; + } else { + $trb{ $ref->{accno} }{soll_eb} = $ref->{amount} * -1; + } + $trb{ $ref->{accno} }{category} = $ref->{category}; } - $trb{ $ref->{accno} }{category} = $ref->{category}; - } + } + $sth->finish; } - $sth->finish; - # get headings $query = @@ -709,26 +783,27 @@ sub trial_balance { my $glwhere = ''; my $glsumwhere = ''; my $tofrom; + my ($fromdate, $todate); if ($form->{fromdate} || $form->{todate}) { if ($form->{fromdate}) { - my $fromdate = conv_dateq($form->{fromdate}); + $fromdate = conv_dateq($form->{fromdate}); $tofrom .= " AND (ac.transdate >= $fromdate)"; - $subwhere .= " AND (transdate >= $fromdate)"; - $sumsubwhere .= " AND (transdate >= (select date_trunc('year', date $fromdate))) "; - $saldosubwhere .= " AND transdate>=(select date_trunc('year', date $fromdate)) "; + $subwhere .= " AND (ac.transdate >= $fromdate)"; + $sumsubwhere .= " AND (ac.transdate >= (select date_trunc('year', date $fromdate))) "; + $saldosubwhere .= " AND (ac,transdate>=(select date_trunc('year', date $fromdate))) "; $invwhere .= " AND (a.transdate >= $fromdate)"; $glsaldowhere .= " AND ac.transdate>=(select date_trunc('year', date $fromdate)) "; $glwhere = " AND (ac.transdate >= $fromdate)"; $glsumwhere = " AND (ac.transdate >= (select date_trunc('year', date $fromdate))) "; } if ($form->{todate}) { - my $todate = conv_dateq($form->{todate}); + $todate = conv_dateq($form->{todate}); $tofrom .= " AND (ac.transdate <= $todate)"; $invwhere .= " AND (a.transdate <= $todate)"; - $saldosubwhere .= " AND (transdate <= $todate)"; - $sumsubwhere .= " AND (transdate <= $todate)"; - $subwhere .= " AND (transdate <= $todate)"; + $saldosubwhere .= " AND (ac.transdate <= $todate)"; + $sumsubwhere .= " AND (ac.transdate <= $todate)"; + $subwhere .= " AND (ac.transdate <= $todate)"; $glwhere .= " AND (ac.transdate <= $todate)"; $glsumwhere .= " AND (ac.transdate <= $todate) "; $glsaldowhere .= " AND (ac.transdate <= $todate) "; @@ -737,84 +812,10 @@ sub trial_balance { if ($form->{method} eq "cash") { $where .= - qq| AND ((ac.trans_id IN (SELECT id from ar) AND - ac.trans_id IN - ( - SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) - WHERE (link LIKE '%AR_paid%') - $subwhere - ) - ) - OR - (ac.trans_id in (SELECT id from ap) AND - ac.trans_id IN - ( - SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) - WHERE (link LIKE '%AP_paid%') - $subwhere - ) - ) - OR - (ac.trans_id in (SELECT id from gl) - $glwhere) - )|; - $saldowhere .= -qq| AND ((ac.trans_id IN (SELECT id from ar) AND - ac.trans_id IN - ( - SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) - WHERE (link LIKE '%AR_paid%') - $saldosubwhere - ) - ) - OR - (ac.trans_id in (SELECT id from ap) AND - ac.trans_id IN - ( - SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) - WHERE (link LIKE '%AP_paid%') - $saldosubwhere - ) - ) - OR - (ac.trans_id in (SELECT id from gl) - $glsaldowhere) - )|; - $sumwhere .= -qq| AND ((ac.trans_id IN (SELECT id from ar) AND - ac.trans_id IN - ( - SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) - WHERE (link LIKE '%AR_paid%') - $sumsubwhere - ) - ) - OR - (ac.trans_id in (SELECT id from ap) AND - ac.trans_id IN - ( - SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) - WHERE (link LIKE '%AP_paid%') - $sumsubwhere - ) - ) - OR - (ac.trans_id in (SELECT id from gl) - $glsumwhere) - )|; + qq| AND(ac.trans_id IN (SELECT id FROM ar WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM ap WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM gl WHERE transdate>= $fromdate AND transdate<= $todate)) AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL) |; + $saldowhere .= qq| AND(ac.trans_id IN (SELECT id FROM ar WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM ap WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM gl WHERE transdate>= $fromdate AND transdate<= $todate)) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL) |; + $sumwhere .= qq| AND(ac.trans_id IN (SELECT id FROM ar WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM ap WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM gl WHERE transdate>= $fromdate AND transdate<= $todate)) AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL) |; } else { $where .= $tofrom . " AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL)"; $saldowhere .= $glsaldowhere . " AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL)"; @@ -907,7 +908,7 @@ qq| AND ((ac.trans_id IN (SELECT id from ar) AND WHERE $saldowhere $dpt_where $project - AND c.accno = ?) AS saldo, + AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo, (SELECT SUM(ac.amount) FROM acc_trans ac @@ -975,7 +976,7 @@ qq| AND ((ac.trans_id IN (SELECT id from ar) AND WHERE $saldowhere $dpt_where $project - AND c.accno = ?) AS saldo, + AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo, (SELECT SUM(ac.amount) FROM acc_trans ac @@ -1018,7 +1019,7 @@ qq| AND ((ac.trans_id IN (SELECT id from ar) AND $ref->{accno} = $accno; map { $ref->{$_} = $trb{$accno}{$_} } - qw(description category charttype amount soll_eb haben_eb); + qw(description category charttype amount soll_eb haben_eb beginning_balance); $ref->{balance} = $form->round_amount($balance{ $ref->{accno} }, 2); @@ -1066,7 +1067,21 @@ qq| AND ((ac.trans_id IN (SELECT id from ar) AND $ref->{debit} = $form->round_amount($ref->{debit}, 2); $ref->{credit} = $form->round_amount($ref->{credit}, 2); - $ref->{haben_saldo} = $form->round_amount($ref->{haben_saldo}, 2); + + if ($ref->{haben_saldo} != 0) { + $ref->{haben_saldo} = $ref->{haben_saldo} + $ref->{beginning_balance}; + if ($ref->{haben_saldo} < 0) { + $ref->{soll_saldo} = $form->round_amount(($ref->{haben_saldo} *- 1), 2); + $ref->{haben_saldo} = 0; + } + } else { + $ref->{soll_saldo} = $ref->{soll_saldo} - $ref->{beginning_balance}; + if ($ref->{soll_saldo} < 0) { + $ref->{haben_saldo} = $form->round_amount(($ref->{soll_saldo} * -1), 2); + $ref->{soll_saldo} = 0; + } + } + $ref->{haben_saldo} = $form->round_amount($ref->{haben_saldo}, 2); $ref->{soll_saldo} = $form->round_amount($ref->{soll_saldo}, 2); $ref->{haben_kumuliert} = $form->round_amount($ref->{haben_kumuliert}, 2); $ref->{soll_kumuliert} = $form->round_amount($ref->{soll_kumuliert}, 2); @@ -1095,12 +1110,13 @@ qq| AND ((ac.trans_id IN (SELECT id from ar) AND foreach my $accno (@headingaccounts) { foreach $ref (@{ $form->{TB} }) { if ($accno eq $ref->{accno}) { - $ref->{debit} = $trb{$accno}{debit}; - $ref->{credit} = $trb{$accno}{credit}; - $ref->{soll_saldo} = $trb{$accno}{soll_saldo}; - $ref->{haben_saldo} = $trb{$accno}{haben_saldo}; + $ref->{debit} = $trb{$accno}{debit}; + $ref->{credit} = $trb{$accno}{credit}; + $ref->{soll_saldo} = $trb{$accno}{soll_saldo}; + $ref->{haben_saldo} = $trb{$accno}{haben_saldo}; $ref->{soll_kumuliert} = $trb{$accno}{soll_kumuliert}; - $ref->{haben_kumuliert} = $trb{$accno}{haben_kumuliert}; } + $ref->{haben_kumuliert} = $trb{$accno}{haben_kumuliert}; + } } } @@ -1182,7 +1198,7 @@ sub aging { AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate FROM ${arap}, ${ct} WHERE ((paid != amount) OR (datepaid > (date $todate) AND datepaid is not null)) - AND (${arap}.storno IS FALSE) + AND NOT COALESCE (${arap}.storno, 'f') AND (${arap}.${ct}_id = ${ct}.id) AND (${ct}.id = ?) AND (transdate <= (date $todate) $fromwhere ) @@ -1595,7 +1611,7 @@ sub bwa { } my @periods = qw(jetzt kumm); - my @gesamtleistung = qw(1 2 3); + my @gesamtleistung = qw(1 3); my @gesamtkosten = qw (10 11 12 13 14 15 16 17 18 19 20); my @ergebnisse = qw (rohertrag betriebrohertrag betriebsergebnis neutraleraufwand neutralerertrag ergebnisvorsteuern ergebnis gesamtleistung gesamtkosten); @@ -1618,6 +1634,8 @@ sub bwa { foreach my $item (@gesamtleistung) { $form->{ "$key" . "gesamtleistung" } += $form->{$item}{$key}; } + $form->{ "$key" . "gesamtleistung" } -= $form->{2}{$key}; + foreach my $item (@gesamtkosten) { $form->{ "$key" . "gesamtkosten" } += $form->{$item}{$key}; } @@ -1794,26 +1812,26 @@ sub ustva { &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, "pos_ustva"); # foreach $item (@categories_cent) { - # if ($form->{$item}{"jetzt"} > 0) { - # $form->{$item} = $form->{$item}{"jetzt"}; - # delete $form->{$item}{"jetzt"}; - # } + # if ($form->{$item}{"jetzt"} > 0) { + # $form->{$item} = $form->{$item}{"jetzt"}; + # delete $form->{$item}{"jetzt"}; + # } # } # foreach $item (@categories_euro) { - # if ($form->{$item}{"jetzt"} > 0) { - # $form->{$item} = $form->{$item}{"jetzt"}; - # delete $form->{$item}{"jetzt"}; - # } foreach $item (@categories_cent) { - # if ($form->{$item}{"jetzt"} > 0) { - # $form->{$item} = $form->{$item}{"jetzt"}; - # delete $form->{$item}{"jetzt"}; - # } + # if ($form->{$item}{"jetzt"} > 0) { + # $form->{$item} = $form->{$item}{"jetzt"}; + # delete $form->{$item}{"jetzt"}; + # } foreach $item (@categories_cent) { + # if ($form->{$item}{"jetzt"} > 0) { + # $form->{$item} = $form->{$item}{"jetzt"}; + # delete $form->{$item}{"jetzt"}; + # } # } # foreach $item (@categories_euro) { - # if ($form->{$item}{"jetzt"} > 0) { - # $form->{$item} = $form->{$item}{"jetzt"}; - # delete $form->{$item}{"jetzt"}; - # } + # if ($form->{$item}{"jetzt"} > 0) { + # $form->{$item} = $form->{$item}{"jetzt"}; + # delete $form->{$item}{"jetzt"}; + # } # } # # } @@ -1870,28 +1888,20 @@ sub income_statement { $form->{decimalplaces} *= 1; - foreach my $item (@categories_einnahmen) { - $form->{$item} = 0; - } - foreach my $item (@categories_ausgaben) { - $form->{$item} = 0; - } - foreach my $item (@ergebnisse) { - $form->{$item} = 0; - } &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, "pos_eur"); + foreach my $item (@categories_einnahmen) { $form->{"eur${item}"} = - $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2)); + $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2); $form->{"sumeura"} += $form->{$item}; } foreach my $item (@categories_ausgaben) { $form->{"eur${item}"} = - $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2)); + $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2); $form->{"sumeurb"} += $form->{$item}; } @@ -1899,7 +1909,7 @@ sub income_statement { foreach my $item (@ergebnisse) { $form->{$item} = - $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2)); + $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2); } $main::lxdebug->leave_sub(); }