| 609 |
609 |
sub trial_balance {
|
| 610 |
610 |
$main::lxdebug->enter_sub();
|
| 611 |
611 |
|
| 612 |
|
my ($self, $myconfig, $form) = @_;
|
|
612 |
my ($self, $myconfig, $form, %options) = @_;
|
| 613 |
613 |
|
| 614 |
614 |
my $dbh = $form->dbconnect($myconfig);
|
| 615 |
615 |
|
| ... | ... | |
| 668 |
668 |
# $ar_ap_cash_where = qq| AND (a.datepaid>='$form->{fromdate}' AND a.datepaid<='$form->{todate}') |;
|
| 669 |
669 |
}
|
| 670 |
670 |
|
| 671 |
|
# get beginning balances
|
| 672 |
|
$query =
|
| 673 |
|
qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount, c.description
|
| 674 |
|
FROM acc_trans ac
|
| 675 |
|
LEFT JOIN chart c ON (ac.chart_id = c.id)
|
| 676 |
|
$dpt_join
|
| 677 |
|
WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction $acc_cash_where
|
| 678 |
|
$dpt_where
|
| 679 |
|
$project
|
| 680 |
|
GROUP BY c.accno, c.category, c.description |;
|
|
671 |
if ($options{beginning_balances}) {
|
|
672 |
# get beginning balances
|
|
673 |
$query =
|
|
674 |
qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount, c.description
|
|
675 |
FROM acc_trans ac
|
|
676 |
LEFT JOIN chart c ON (ac.chart_id = c.id)
|
|
677 |
$dpt_join
|
|
678 |
WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction $acc_cash_where
|
|
679 |
$dpt_where
|
|
680 |
$project
|
|
681 |
GROUP BY c.accno, c.category, c.description |;
|
| 681 |
682 |
|
| 682 |
|
$sth = prepare_execute_query($form, $dbh, $query, $form->{fromdate});
|
|
683 |
$sth = prepare_execute_query($form, $dbh, $query, $form->{fromdate});
|
| 683 |
684 |
|
| 684 |
|
while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
|
|
685 |
while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
|
| 685 |
686 |
|
| 686 |
|
if ($ref->{amount} != 0 || $form->{all_accounts}) {
|
| 687 |
|
$trb{ $ref->{accno} }{description} = $ref->{description};
|
| 688 |
|
$trb{ $ref->{accno} }{charttype} = 'A';
|
|
687 |
if ($ref->{amount} != 0 || $form->{all_accounts}) {
|
|
688 |
$trb{ $ref->{accno} }{description} = $ref->{description};
|
|
689 |
$trb{ $ref->{accno} }{charttype} = 'A';
|
| 689 |
690 |
|
| 690 |
|
if ($ref->{amount} > 0) {
|
| 691 |
|
$trb{ $ref->{accno} }{haben_eb} = $ref->{amount};
|
| 692 |
|
} else {
|
| 693 |
|
$trb{ $ref->{accno} }{soll_eb} = $ref->{amount} * -1;
|
|
691 |
if ($ref->{amount} > 0) {
|
|
692 |
$trb{ $ref->{accno} }{haben_eb} = $ref->{amount};
|
|
693 |
} else {
|
|
694 |
$trb{ $ref->{accno} }{soll_eb} = $ref->{amount} * -1;
|
|
695 |
}
|
|
696 |
$trb{ $ref->{accno} }{category} = $ref->{category};
|
| 694 |
697 |
}
|
| 695 |
|
$trb{ $ref->{accno} }{category} = $ref->{category};
|
| 696 |
|
}
|
| 697 |
698 |
|
|
699 |
}
|
|
700 |
$sth->finish;
|
| 698 |
701 |
}
|
| 699 |
|
$sth->finish;
|
| 700 |
|
|
| 701 |
702 |
|
| 702 |
703 |
# get headings
|
| 703 |
704 |
$query =
|
| ... | ... | |
| 783 |
784 |
$glwhere)
|
| 784 |
785 |
)|;
|
| 785 |
786 |
$saldowhere .=
|
| 786 |
|
qq| AND ((ac.trans_id IN (SELECT id from ar) AND
|
|
787 |
qq| AND ((ac.trans_id IN (SELECT id from ar) AND
|
| 787 |
788 |
ac.trans_id IN
|
| 788 |
789 |
(
|
| 789 |
790 |
SELECT trans_id
|
| ... | ... | |
| 809 |
810 |
$glsaldowhere)
|
| 810 |
811 |
)|;
|
| 811 |
812 |
$sumwhere .=
|
| 812 |
|
qq| AND ((ac.trans_id IN (SELECT id from ar) AND
|
|
813 |
qq| AND ((ac.trans_id IN (SELECT id from ar) AND
|
| 813 |
814 |
ac.trans_id IN
|
| 814 |
815 |
(
|
| 815 |
816 |
SELECT trans_id
|
| ... | ... | |
| 1115 |
1116 |
foreach my $accno (@headingaccounts) {
|
| 1116 |
1117 |
foreach $ref (@{ $form->{TB} }) {
|
| 1117 |
1118 |
if ($accno eq $ref->{accno}) {
|
| 1118 |
|
$ref->{debit} = $trb{$accno}{debit};
|
| 1119 |
|
$ref->{credit} = $trb{$accno}{credit};
|
| 1120 |
|
$ref->{soll_saldo} = $trb{$accno}{soll_saldo};
|
| 1121 |
|
$ref->{haben_saldo} = $trb{$accno}{haben_saldo};
|
|
1119 |
$ref->{debit} = $trb{$accno}{debit};
|
|
1120 |
$ref->{credit} = $trb{$accno}{credit};
|
|
1121 |
$ref->{soll_saldo} = $trb{$accno}{soll_saldo};
|
|
1122 |
$ref->{haben_saldo} = $trb{$accno}{haben_saldo};
|
| 1122 |
1123 |
$ref->{soll_kumuliert} = $trb{$accno}{soll_kumuliert};
|
| 1123 |
|
$ref->{haben_kumuliert} = $trb{$accno}{haben_kumuliert}; }
|
|
1124 |
$ref->{haben_kumuliert} = $trb{$accno}{haben_kumuliert};
|
|
1125 |
}
|
| 1124 |
1126 |
}
|
| 1125 |
1127 |
}
|
| 1126 |
1128 |
|
Nur beim Erzeugen der Summen-/Saldenliste auch die Eröffnungsbilanzwerte auslesen, weil dies bei Projektbuchungen nicht notwendig ist.