Revision 4654d993
Von Philip Reetz vor mehr als 15 Jahren hinzugefügt
| SL/CA.pm | ||
|---|---|---|
| 210 | 210 |
|
| 211 | 211 |
# get last transaction date |
| 212 | 212 |
my $todate = ($form->{todate}) ? " AND ac.transdate <= '$form->{todate}' " : "";
|
| 213 |
$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|;
|
|
| 213 |
$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|;
|
|
| 214 | 214 |
($form->{last_transaction}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
|
| 215 | 215 |
|
| 216 | 216 |
# get old saldo |
| 217 |
$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|;
|
|
| 217 |
$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|;
|
|
| 218 | 218 |
($form->{saldo_old}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno});
|
| 219 | 219 |
|
| 220 | 220 |
#get old balance |
| 221 |
$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|;
|
|
| 221 |
$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|;
|
|
| 222 | 222 |
($form->{old_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno});
|
| 223 | 223 |
|
| 224 |
$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|;
|
|
| 224 |
$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|;
|
|
| 225 | 225 |
($form->{old_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno});
|
| 226 | 226 |
|
| 227 | 227 |
# get current saldo |
| 228 | 228 |
my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : "";
|
| 229 |
$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|;
|
|
| 229 |
$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|;
|
|
| 230 | 230 |
($form->{saldo_new}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
|
| 231 | 231 |
|
| 232 | 232 |
#get current balance |
| 233 | 233 |
my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : "";
|
| 234 |
$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|;
|
|
| 234 |
$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|;
|
|
| 235 | 235 |
($form->{current_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
|
| 236 | 236 |
|
| 237 | 237 |
my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : "";
|
| 238 |
$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|;
|
|
| 238 |
$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|;
|
|
| 239 | 239 |
($form->{current_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
|
| 240 | 240 |
} |
| 241 | 241 |
} |
| SL/RP.pm | ||
|---|---|---|
| 37 | 37 |
use SL::DBUtils; |
| 38 | 38 |
use Data::Dumper; |
| 39 | 39 |
use List::Util qw(sum); |
| 40 |
use strict; |
|
| 41 |
use warnings; |
|
| 40 |
# use strict;
|
|
| 41 |
# use warnings;
|
|
| 42 | 42 |
|
| 43 | 43 |
|
| 44 | 44 |
# new implementation of balance sheet |
| ... | ... | |
| 700 | 700 |
if ($ref->{amount} != 0 || $form->{all_accounts}) {
|
| 701 | 701 |
$trb{ $ref->{accno} }{description} = $ref->{description};
|
| 702 | 702 |
$trb{ $ref->{accno} }{charttype} = 'A';
|
| 703 |
$trb{ $ref->{accno} }{beginning_balance} = $ref->{amount};
|
|
| 703 | 704 |
|
| 704 | 705 |
if ($ref->{amount} > 0) {
|
| 705 | 706 |
$trb{ $ref->{accno} }{haben_eb} = $ref->{amount};
|
| ... | ... | |
| 941 | 942 |
WHERE $saldowhere |
| 942 | 943 |
$dpt_where |
| 943 | 944 |
$project |
| 944 |
AND c.accno = ?) AS saldo, |
|
| 945 |
AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo,
|
|
| 945 | 946 |
|
| 946 | 947 |
(SELECT SUM(ac.amount) |
| 947 | 948 |
FROM acc_trans ac |
| ... | ... | |
| 1009 | 1010 |
WHERE $saldowhere |
| 1010 | 1011 |
$dpt_where |
| 1011 | 1012 |
$project |
| 1012 |
AND c.accno = ?) AS saldo, |
|
| 1013 |
AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo,
|
|
| 1013 | 1014 |
|
| 1014 | 1015 |
(SELECT SUM(ac.amount) |
| 1015 | 1016 |
FROM acc_trans ac |
| ... | ... | |
| 1052 | 1053 |
|
| 1053 | 1054 |
$ref->{accno} = $accno;
|
| 1054 | 1055 |
map { $ref->{$_} = $trb{$accno}{$_} }
|
| 1055 |
qw(description category charttype amount soll_eb haben_eb); |
|
| 1056 |
qw(description category charttype amount soll_eb haben_eb beginning_balance);
|
|
| 1056 | 1057 |
|
| 1057 | 1058 |
$ref->{balance} = $form->round_amount($balance{ $ref->{accno} }, 2);
|
| 1058 | 1059 |
|
| ... | ... | |
| 1100 | 1101 |
|
| 1101 | 1102 |
$ref->{debit} = $form->round_amount($ref->{debit}, 2);
|
| 1102 | 1103 |
$ref->{credit} = $form->round_amount($ref->{credit}, 2);
|
| 1103 |
$ref->{haben_saldo} = $form->round_amount($ref->{haben_saldo}, 2);
|
|
| 1104 |
|
|
| 1105 |
if ($ref->{haben_saldo} != 0) {
|
|
| 1106 |
$ref->{haben_saldo} = $ref->{haben_saldo} + $ref->{beginning_balance};
|
|
| 1107 |
if ($ref->{haben_saldo} < 0) {
|
|
| 1108 |
$ref->{soll_saldo} = $form->round_amount(($ref->{haben_saldo} *- 1), 2);
|
|
| 1109 |
$ref->{haben_saldo} = 0;
|
|
| 1110 |
} |
|
| 1111 |
} elsif ($ref->{soll_saldo} != 0) {
|
|
| 1112 |
$ref->{soll_saldo} = $ref->{soll_saldo} - $ref->{beginning_balance};
|
|
| 1113 |
if ($ref->{soll_saldo} < 0) {
|
|
| 1114 |
$ref->{haben_saldo} = $form->round_amount(($ref->{haben_saldo} * -1), 2);
|
|
| 1115 |
$ref->{soll_saldo} = 0;
|
|
| 1116 |
} |
|
| 1117 |
} |
|
| 1118 |
$ref->{haben_saldo} = $form->round_amount($ref->{haben_saldo}, 2);
|
|
| 1104 | 1119 |
$ref->{soll_saldo} = $form->round_amount($ref->{soll_saldo}, 2);
|
| 1105 | 1120 |
$ref->{haben_kumuliert} = $form->round_amount($ref->{haben_kumuliert}, 2);
|
| 1106 | 1121 |
$ref->{soll_kumuliert} = $form->round_amount($ref->{soll_kumuliert}, 2);
|
| bin/mozilla/ca.pl | ||
|---|---|---|
| 492 | 492 |
|
| 493 | 493 |
CA->all_transactions(\%myconfig, \%$form); |
| 494 | 494 |
|
| 495 |
|
|
| 495 |
$form->{saldo_old} += $form->{beginning_balance};
|
|
| 496 |
$form->{saldo_new} += $form->{beginning_balance};
|
|
| 496 | 497 |
my $saldo_old = format_debit_credit($form->{saldo_old});
|
| 497 | 498 |
my $eb_string = format_debit_credit($form->{beginning_balance});
|
| 498 | 499 |
$form->{balance} = $form->{saldo_old};
|
| ... | ... | |
| 654 | 655 |
}; |
| 655 | 656 |
} |
| 656 | 657 |
|
| 657 |
my $sh = ""; |
|
| 658 |
if ($form->{balance} < 0) {
|
|
| 659 |
$sh = " S"; |
|
| 660 |
$ml = -1; |
|
| 661 |
} elsif ($form->{balance} > 0) {
|
|
| 662 |
$sh = " H"; |
|
| 663 |
$ml = 1; |
|
| 664 |
} |
|
| 665 |
my $data = $form->format_amount(\%myconfig, ($form->{balance} * $ml), 2);
|
|
| 666 |
$data .= $sh; |
|
| 667 |
|
|
| 668 |
$row->{balance}->{data} = $data;
|
|
| 658 |
$row->{balance}->{data} = $form->format_amount(\%myconfig, $form->{balance}, 2, 'DRCR');
|
|
| 669 | 659 |
|
| 670 | 660 |
if ($ca->{index} ne $previous_index) {
|
| 671 | 661 |
# $report->add_data($row_set) if ($row_set); |
| ... | ... | |
| 698 | 688 |
|
| 699 | 689 |
my $row = create_subtotal_row(\%totals, \@columns, \%column_alignment, 'listtotal'); |
| 700 | 690 |
|
| 701 |
my $sh = ""; |
|
| 702 |
if ($form->{balance} < 0) {
|
|
| 703 |
$sh = " S"; |
|
| 704 |
$ml = -1; |
|
| 705 |
} elsif ($form->{balance} > 0) {
|
|
| 706 |
$sh = " H"; |
|
| 707 |
$ml = 1; |
|
| 708 |
} |
|
| 709 |
my $data = $form->format_amount(\%myconfig, ($form->{balance} * $ml), 2);
|
|
| 710 |
$data .= $sh; |
|
| 711 | 691 |
|
| 712 |
$row->{balance}->{data} = $data;
|
|
| 692 |
$row->{balance}->{data} = $form->format_amount(\%myconfig, $form->{balance}, 2, 'DRCR');
|
|
| 713 | 693 |
|
| 714 | 694 |
$report->add_data($row); |
| 715 | 695 |
|
Auch abrufbar als: Unified diff
Berichte Kontenblatt und SuSa auch fuer Zeitraueme ueber eine Jahr hinaus fit gemacht