X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FRP.pm;h=80bd7891f273fb5388781806e4825705c34bcb41;hb=63b3e9b39fe288260a3d6103db86359b3339f21a;hp=0b0000eacc4692ee0f0fd61922e2a5dfd852c733;hpb=4654d9931a51aaa867c425c1fc63618337df67e9;p=kivitendo-erp.git diff --git a/SL/RP.pm b/SL/RP.pm index 0b0000eac..80bd7891f 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(); } @@ -688,7 +727,7 @@ sub trial_balance { 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 + 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 |; @@ -744,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) "; @@ -772,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)"; @@ -1108,10 +1074,10 @@ sub trial_balance { $ref->{soll_saldo} = $form->round_amount(($ref->{haben_saldo} *- 1), 2); $ref->{haben_saldo} = 0; } - } elsif ($ref->{soll_saldo} != 0) { + } else { $ref->{soll_saldo} = $ref->{soll_saldo} - $ref->{beginning_balance}; if ($ref->{soll_saldo} < 0) { - $ref->{haben_saldo} = $form->round_amount(($ref->{haben_saldo} * -1), 2); + $ref->{haben_saldo} = $form->round_amount(($ref->{soll_saldo} * -1), 2); $ref->{soll_saldo} = 0; } } @@ -1645,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); @@ -1668,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}; } @@ -1920,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}; } @@ -1949,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(); }