X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FRP.pm;h=84dd73e22c72b92f2f23708cc02396259330f1cd;hb=f9c7abfae65b79945beb7e9260942bc94876248a;hp=72bbcffd30fbef4b73b89847cd1b3de4697237a4;hpb=72d93b0b6cbca4fc4dabb116f20c1b88a22fde88;p=kivitendo-erp.git diff --git a/SL/RP.pm b/SL/RP.pm index 72bbcffd3..84dd73e22 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! @@ -49,12 +49,23 @@ use List::Util qw(sum); # - subdescription # - proper testing for heading charts # - transmission from $form to TMPL realm is not as clear as i'd like + +sub get_openbalance_date { + my ($closedto, $target) = map { $::locale->parse_date_to_object(\%::myconfig, $_) } @_; + + return unless $closedto; + + $closedto->subtract(years => 1) while ($target - $closedto)->is_negative; + $closedto->add(days => 1); + return $::locale->format_date(\%::myconfig, $closedto); +} + sub balance_sheet { $main::lxdebug->enter_sub(); my $myconfig = \%main::myconfig; my $form = $main::form; - my $dbh = $form->get_standard_dbh($myconfig); + my $dbh = $::form->get_standard_dbh; my $last_period = 0; my @categories = qw(A C L Q); @@ -64,12 +75,21 @@ sub balance_sheet { $form->{period} = $form->{this_period} = conv_dateq($form->{asofdate}); } - get_accounts($dbh, $last_period, "", $form->{asofdate}, $form, \@categories); + # get end of financial year and convert to Date format + my ($closedto) = selectfirst_array_query($form, $dbh, 'SELECT closedto FROM defaults'); + + # get date of last opening balance + my $startdate = get_openbalance_date($closedto, $form->{asofdate}); + + get_accounts($dbh, $last_period, $startdate, $form->{asofdate}, $form, \@categories); # if there are any compare dates if ($form->{compareasofdate}) { $last_period = 1; - get_accounts($dbh, $last_period, "", $form->{compareasofdate}, $form, \@categories); + + $startdate = get_openbalance_date($closedto, $form->{compareasofdate}); + + get_accounts($dbh, $last_period, $startdate, $form->{compareasofdate}, $form, \@categories); $form->{last_period} = conv_dateq($form->{compareasofdate}); } @@ -118,7 +138,6 @@ sub balance_sheet { next if ($period eq 'last' && !$last_period); # only add assets $row->{$period} *= $ml; - $form->{total}{$category}{$period} += $row->{$period}; # if ($row->{charttype} eq 'A') { # why?? } push @{ $TMPL_DATA->{$category} }, $row; @@ -138,12 +157,11 @@ sub balance_sheet { for my $period (qw(this last)) { next if ($period eq 'last' && !$last_period); - $form->{E}{$period} = $form->{total}{A}{$period} - $form->{total}{L}{$period} - $form->{total}{Q}{$period}; - $form->{total}{Q}{$period} += $form->{E}{$period}; - $TMPL_DATA->{total}{Q}{$period} = $form->{total}{Q}{$period}; - $TMPL_DATA->{total}{$period} = $form->{total}{L}{$period} + $form->{total}{Q}{$period}; + $form->{E}{$period} = $TMPL_DATA->{total}{A}{$period} - $TMPL_DATA->{total}{L}{$period} - $TMPL_DATA->{total}{Q}{$period}; + $TMPL_DATA->{total}{Q}{$period} += $form->{E}{$period}; + $TMPL_DATA->{total}{$period} = $TMPL_DATA->{total}{L}{$period} + $TMPL_DATA->{total}{Q}{$period}; } - + $form->{E}{description}='nicht verbuchter Gewinn/Verlust'; push @{ $TMPL_DATA->{Q} }, $form->{E}; $main::lxdebug->leave_sub(); @@ -215,6 +233,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|) |; } @@ -231,8 +251,8 @@ sub get_accounts { AND ac.trans_id IN ( SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) + FROM acc_trans a + JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AR_paid%') $subwhere ) @@ -252,8 +272,8 @@ sub get_accounts { AND ac.trans_id IN ( SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) + FROM acc_trans a + JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AP_paid%') $subwhere ) @@ -276,6 +296,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 @@ -293,8 +314,8 @@ sub get_accounts { AND ac.trans_id IN ( SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) + FROM acc_trans a + JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AR_paid%') $subwhere ) @@ -315,8 +336,8 @@ sub get_accounts { AND ac.trans_id IN ( SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) + FROM acc_trans a + JOIN chart c ON (a.chart_id = c.id) WHERE link LIKE '%AP_paid%' $subwhere ) @@ -325,10 +346,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 +365,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 +463,7 @@ sub get_accounts_g { my $glwhere = ""; my $prwhere = ""; my $subwhere = ""; + my $inwhere = ""; my $item; if ($fromdate) { @@ -447,6 +472,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 +483,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,19 +495,52 @@ 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) JOIN ar 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 '%AR_paid%') $subwhere) + AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.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 @@ -489,7 +549,7 @@ sub get_accounts_g { 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_paid%') $subwhere) + AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AP_paid%') $subwhere) $project GROUP BY c.$category @@ -517,7 +577,7 @@ sub get_accounts_g { JOIN chart c on (p.income_accno_id = c.id) $dpt_join WHERE (c.category = 'I') $prwhere $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) + AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AR_paid%') $subwhere) $project GROUP BY c.$category @@ -530,17 +590,17 @@ sub get_accounts_g { JOIN chart c on (p.expense_accno_id = c.id) $dpt_join WHERE (c.category = 'E') $prwhere $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) + AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AP_paid%') $subwhere) $project GROUP BY c.$category |; } } 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 +648,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 +659,6 @@ sub get_accounts_g { $form->{ $ref->{$category} } += $ref->{amount}; } } - $sth->finish; $main::lxdebug->leave_sub(); } @@ -688,7 +745,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 +801,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 +830,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 +1092,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; } } @@ -1216,9 +1200,19 @@ sub aging { $dpt_join = qq| JOIN department d ON (a.department_id = d.id) |; $where .= qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|)|; } + my $review_of_aging_list; + if ($form->{review_of_aging_list}) { + if ($form->{review_of_aging_list} =~ m "-"){ + my @period = split(/-/, $form->{review_of_aging_list}); + $review_of_aging_list = " AND $period[0] < date_part('days', now() - duedate) + AND date_part('days', now() - duedate) < $period[1]"; + } else { + $form->{review_of_aging_list} =~ s/[^0-9]//g; + $review_of_aging_list = " AND $form->{review_of_aging_list} < date_part('days', now() - duedate)"; + } + } my $q_details = qq| - -- between 0-30 days SELECT ${ct}.id AS ctid, ${ct}.name, street, zipcode, city, country, contact, email, @@ -1232,11 +1226,11 @@ 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 ) - + $review_of_aging_list ORDER BY ctid, transdate, invnumber |; my $sth_details = prepare_query($form, $dbh, $q_details); @@ -1368,8 +1362,8 @@ sub tax_report { AND ac.trans_id IN ( SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) + FROM acc_trans a + JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%${ARAP}_paid%') AND (transdate <= $todate) ) @@ -1846,26 +1840,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"}; + # } # } # # } @@ -1922,28 +1916,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}; } @@ -1951,7 +1937,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(); }