Revision 4654d993
Von Philip Reetz vor fast 16 Jahren hinzugefügt
| SL/CA.pm | ||
|---|---|---|
|
|
||
|
# get last transaction date
|
||
|
my $todate = ($form->{todate}) ? " AND ac.transdate <= '$form->{todate}' " : "";
|
||
|
$query = qq|SELECT max(ac.transdate) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ? $acc_cash_where|;
|
||
|
$query = qq|SELECT max(ac.transdate) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? $acc_cash_where|;
|
||
|
($form->{last_transaction}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
|
||
|
|
||
|
# get old saldo
|
||
|
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? $acc_cash_where|;
|
||
|
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|;
|
||
|
($form->{saldo_old}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno});
|
||
|
|
||
|
#get old balance
|
||
|
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|;
|
||
|
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|;
|
||
|
($form->{old_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno});
|
||
|
|
||
|
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|;
|
||
|
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|;
|
||
|
($form->{old_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno});
|
||
|
|
||
|
# get current saldo
|
||
|
my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : "";
|
||
|
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ? $acc_cash_where|;
|
||
|
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|;
|
||
|
($form->{saldo_new}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
|
||
|
|
||
|
#get current balance
|
||
|
my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : "";
|
||
|
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|;
|
||
|
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|;
|
||
|
($form->{current_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
|
||
|
|
||
|
my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : "";
|
||
|
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|;
|
||
|
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|;
|
||
|
($form->{current_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
|
||
|
}
|
||
|
}
|
||
| SL/RP.pm | ||
|---|---|---|
|
use SL::DBUtils;
|
||
|
use Data::Dumper;
|
||
|
use List::Util qw(sum);
|
||
|
use strict;
|
||
|
use warnings;
|
||
|
# use strict;
|
||
|
# use warnings;
|
||
|
|
||
|
|
||
|
# new implementation of balance sheet
|
||
| ... | ... | |
|
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};
|
||
| ... | ... | |
|
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);
|
||
| bin/mozilla/ca.pl | ||
|---|---|---|
|
|
||
|
CA->all_transactions(\%myconfig, \%$form);
|
||
|
|
||
|
|
||
|
$form->{saldo_old} += $form->{beginning_balance};
|
||
|
$form->{saldo_new} += $form->{beginning_balance};
|
||
|
my $saldo_old = format_debit_credit($form->{saldo_old});
|
||
|
my $eb_string = format_debit_credit($form->{beginning_balance});
|
||
|
$form->{balance} = $form->{saldo_old};
|
||
| ... | ... | |
|
};
|
||
|
}
|
||
|
|
||
|
my $sh = "";
|
||
|
if ($form->{balance} < 0) {
|
||
|
$sh = " S";
|
||
|
$ml = -1;
|
||
|
} elsif ($form->{balance} > 0) {
|
||
|
$sh = " H";
|
||
|
$ml = 1;
|
||
|
}
|
||
|
my $data = $form->format_amount(\%myconfig, ($form->{balance} * $ml), 2);
|
||
|
$data .= $sh;
|
||
|
|
||
|
$row->{balance}->{data} = $data;
|
||
|
$row->{balance}->{data} = $form->format_amount(\%myconfig, $form->{balance}, 2, 'DRCR');
|
||
|
|
||
|
if ($ca->{index} ne $previous_index) {
|
||
|
# $report->add_data($row_set) if ($row_set);
|
||
| ... | ... | |
|
|
||
|
my $row = create_subtotal_row(\%totals, \@columns, \%column_alignment, 'listtotal');
|
||
|
|
||
|
my $sh = "";
|
||
|
if ($form->{balance} < 0) {
|
||
|
$sh = " S";
|
||
|
$ml = -1;
|
||
|
} elsif ($form->{balance} > 0) {
|
||
|
$sh = " H";
|
||
|
$ml = 1;
|
||
|
}
|
||
|
my $data = $form->format_amount(\%myconfig, ($form->{balance} * $ml), 2);
|
||
|
$data .= $sh;
|
||
|
|
||
|
$row->{balance}->{data} = $data;
|
||
|
$row->{balance}->{data} = $form->format_amount(\%myconfig, $form->{balance}, 2, 'DRCR');
|
||
|
|
||
|
$report->add_data($row);
|
||
|
|
||
Auch abrufbar als: Unified diff
Berichte Kontenblatt und SuSa auch fuer Zeitraueme ueber eine Jahr hinaus fit gemacht