use SL::DBUtils;
use Data::Dumper;
use List::Util qw(sum);
-use strict;
-use warnings;
+# use strict;
+# use warnings;
# new implementation of balance sheet
#
# stuff missing from the original implementation:
# - bold stuff
-# - format (2 places, varying signs of negative amounts)
-# - rounding (might not be necessary)
-# - accno and subdescription
+# - subdescription
# - proper testing for heading charts
# - transmission from $form to TMPL realm is not as clear as i'd like
sub balance_sheet {
$form->{period} = $form->{this_period} = conv_dateq($form->{asofdate});
}
- $form->{decimalplaces} *= 1;
- my $dec = $form->{decimalplaces};
-
get_accounts($dbh, $last_period, "", $form->{asofdate}, $form, \@categories);
# if there are any compare dates
foreach my $category (grep { !/C/ } @categories) {
$TMPL_DATA->{$category} = [];
+ my $ml = $account{$category}{ml};
foreach my $key (sort keys %{ $form->{$category} }) {
- my $row = { %{ $form->{$category}{$key} } };
+ my $row = { %{ $form->{$category}{$key} } };
# if charttype "heading" - calculate this entry, start a new batch of charts belonging to this heading and skip the rest bo the loop
+ # header charts are not real charts. start a sub aggregation with them, but don't calculate anything with them
if ($row->{charttype} eq "H") {
if ($account{$category}{subtotal} && $form->{l_subtotal}) {
$row->{subdescription} = $account{$category}{subdescription};
- $row->{this} = $account{$category}{subthis} * $account{$category}{ml}; # format: $dec, $dash
- $row->{last} = $account{$category}{sublast} * $account{$category}{ml} if $last_period; # format: $dec, $dash
+ $row->{this} = $account{$category}{subthis} * $ml; # format: $dec, $dash
+ $row->{last} = $account{$category}{sublast} * $ml if $last_period; # format: $dec, $dash
}
$row->{subheader} = 1;
- $account{$category}{subthis} = $form->{$category}{$key}{this};
- $account{$category}{sublast} = $form->{$category}{$key}{last};
- $account{$category}{subdescription} = $form->{$category}{$key}{description};
+ $account{$category}{subthis} = $row->{this};
+ $account{$category}{sublast} = $row->{last};
+ $account{$category}{subdescription} = $row->{description};
$account{$category}{subtotal} = 1;
- $form->{$category}{$key}{this} = 0;
- $form->{$category}{$key}{last} = 0;
+ $row->{this} = 0;
+ $row->{last} = 0;
next unless $form->{l_heading};
}
- $row->{this} = $form->{$category}{$key}{this} * $account{$category}{ml};
-
- # only add assets
- if ($row->{charttype} eq 'A') {
- $form->{total}{$category}{this} += $row->{this};
- }
-
- if ($last_period) {
- $row->{last} = $form->{$category}{$key}{last} * $account{$category}{ml};
- $form->{total}{$category}{last} += $row->{last};
+ for my $period (qw(this last)) {
+ 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;
} # foreach
+ # resolve heading/subtotal
if ($account{$category}{subtotal} && $form->{l_subtotal}) {
$TMPL_DATA->{$category}[-1]{subdescription} = $account{$category}{subdescription};
- $TMPL_DATA->{$category}[-1]{this} = $account{$category}{subthis} * $account{$category}{ml}; # format: $dec, $dash
- $TMPL_DATA->{$category}[-1]{last} = $account{$category}{sublast} * $account{$category}{ml} if $last_period; # format: $dec, $dash
+ $TMPL_DATA->{$category}[-1]{this} = $account{$category}{subthis} * $ml; # format: $dec, $dash
+ $TMPL_DATA->{$category}[-1]{last} = $account{$category}{sublast} * $ml if $last_period; # format: $dec, $dash
}
$TMPL_DATA->{total}{$category}{this} = sum map { $_->{this} } @{ $TMPL_DATA->{$category} };
sub trial_balance {
$main::lxdebug->enter_sub();
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form, %options) = @_;
my $dbh = $form->dbconnect($myconfig);
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);
+ }
- $sth = prepare_execute_query($form, $dbh, $query, $form->{fromdate});
+ # 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 |;
- while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
+ $sth = prepare_execute_query($form, $dbh, $query, $form->{fromdate});
- if ($ref->{amount} != 0 || $form->{all_accounts}) {
- $trb{ $ref->{accno} }{description} = $ref->{description};
- $trb{ $ref->{accno} }{charttype} = 'A';
+ while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
- if ($ref->{amount} > 0) {
- $trb{ $ref->{accno} }{haben_eb} = $ref->{amount};
- } else {
- $trb{ $ref->{accno} }{soll_eb} = $ref->{amount} * -1;
+ 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;
+ }
+ $trb{ $ref->{accno} }{category} = $ref->{category};
}
- $trb{ $ref->{accno} }{category} = $ref->{category};
- }
+ }
+ $sth->finish;
}
- $sth->finish;
-
# get headings
$query =
$glwhere)
)|;
$saldowhere .=
-qq| AND ((ac.trans_id IN (SELECT id from ar) AND
+ qq| AND ((ac.trans_id IN (SELECT id from ar) AND
ac.trans_id IN
(
SELECT trans_id
$glsaldowhere)
)|;
$sumwhere .=
-qq| AND ((ac.trans_id IN (SELECT id from ar) AND
+ qq| AND ((ac.trans_id IN (SELECT id from ar) AND
ac.trans_id IN
(
SELECT trans_id
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
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
$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);
$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;
+ }
+ } elsif ($ref->{soll_saldo} != 0) {
+ $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->{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);
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};
+ }
}
}
}
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);
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};
}
projects / kivitendo-erp.git / blobdiff