1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
15 # Contributors: Benjamin Lee <benjaminlee@consultant.com>
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
31 # backend code for reports
33 #======================================================================
39 use List::Util qw(sum);
44 # new implementation of balance sheet
47 # stuff missing from the original implementation:
50 # - proper testing for heading charts
51 # - transmission from $form to TMPL realm is not as clear as i'd like
53 sub get_openbalance_date {
54 my ($closedto, $target) = map { $::locale->parse_date_to_object(\%::myconfig, $_) } @_;
56 return unless $closedto;
58 $closedto->subtract(years => 1) while ($target - $closedto)->is_negative;
59 $closedto->add(days => 1);
60 return $::locale->format_date(\%::myconfig, $closedto);
64 $main::lxdebug->enter_sub();
66 my $myconfig = \%main::myconfig;
67 my $form = $main::form;
68 my $dbh = $::form->get_standard_dbh;
71 my @categories = qw(A C L Q);
73 # if there are any dates construct a where
74 if ($form->{asofdate}) {
75 $form->{period} = $form->{this_period} = conv_dateq($form->{asofdate});
78 # get end of financial year and convert to Date format
79 my ($closedto) = selectfirst_array_query($form, $dbh, 'SELECT closedto FROM defaults');
81 # get date of last opening balance
82 my $startdate = get_openbalance_date($closedto, $form->{asofdate});
84 get_accounts($dbh, $last_period, $startdate, $form->{asofdate}, $form, \@categories);
86 # if there are any compare dates
87 if ($form->{compareasofdate}) {
90 $startdate = get_openbalance_date($closedto, $form->{compareasofdate});
92 get_accounts($dbh, $last_period, $startdate, $form->{compareasofdate}, $form, \@categories);
93 $form->{last_period} = conv_dateq($form->{compareasofdate});
96 # now we got $form->{A}{accno}{ } assets
97 # and $form->{L}{accno}{ } liabilities
98 # and $form->{Q}{accno}{ } equity
99 # build asset accounts
101 my %account = ('A' => { 'ml' => -1 },
102 'L' => { 'ml' => 1 },
103 'Q' => { 'ml' => 1 });
107 foreach my $category (grep { !/C/ } @categories) {
109 $TMPL_DATA->{$category} = [];
110 my $ml = $account{$category}{ml};
112 foreach my $key (sort keys %{ $form->{$category} }) {
114 my $row = { %{ $form->{$category}{$key} } };
116 # if charttype "heading" - calculate this entry, start a new batch of charts belonging to this heading and skip the rest bo the loop
117 # header charts are not real charts. start a sub aggregation with them, but don't calculate anything with them
118 if ($row->{charttype} eq "H") {
119 if ($account{$category}{subtotal} && $form->{l_subtotal}) {
120 $row->{subdescription} = $account{$category}{subdescription};
121 $row->{this} = $account{$category}{subthis} * $ml; # format: $dec, $dash
122 $row->{last} = $account{$category}{sublast} * $ml if $last_period; # format: $dec, $dash
125 $row->{subheader} = 1;
126 $account{$category}{subthis} = $row->{this};
127 $account{$category}{sublast} = $row->{last};
128 $account{$category}{subdescription} = $row->{description};
129 $account{$category}{subtotal} = 1;
134 next unless $form->{l_heading};
137 for my $period (qw(this last)) {
138 next if ($period eq 'last' && !$last_period);
140 $row->{$period} *= $ml;
143 push @{ $TMPL_DATA->{$category} }, $row;
146 # resolve heading/subtotal
147 if ($account{$category}{subtotal} && $form->{l_subtotal}) {
148 $TMPL_DATA->{$category}[-1]{subdescription} = $account{$category}{subdescription};
149 $TMPL_DATA->{$category}[-1]{this} = $account{$category}{subthis} * $ml; # format: $dec, $dash
150 $TMPL_DATA->{$category}[-1]{last} = $account{$category}{sublast} * $ml if $last_period; # format: $dec, $dash
153 $TMPL_DATA->{total}{$category}{this} = sum map { $_->{this} } @{ $TMPL_DATA->{$category} };
154 $TMPL_DATA->{total}{$category}{last} = sum map { $_->{last} } @{ $TMPL_DATA->{$category} };
157 for my $period (qw(this last)) {
158 next if ($period eq 'last' && !$last_period);
160 $form->{E}{$period} = $TMPL_DATA->{total}{A}{$period} - $TMPL_DATA->{total}{L}{$period} - $TMPL_DATA->{total}{Q}{$period};
161 $TMPL_DATA->{total}{Q}{$period} += $form->{E}{$period};
162 $TMPL_DATA->{total}{$period} = $TMPL_DATA->{total}{L}{$period} + $TMPL_DATA->{total}{Q}{$period};
164 $form->{E}{description}='nicht verbuchter Gewinn/Verlust';
165 push @{ $TMPL_DATA->{Q} }, $form->{E};
167 $main::lxdebug->leave_sub();
173 $main::lxdebug->enter_sub();
175 my ($dbh, $last_period, $fromdate, $todate, $form, $categories) = @_;
177 my ($null, $department_id) = split /--/, $form->{department};
181 my $dpt_where_without_arapgl = '';
188 my $dec = $form->{decimalplaces};
190 my $category = qq| AND (| . join(" OR ", map({ "(c.category = " . $dbh->quote($_) . ")" } @{$categories})) . qq|) |;
194 qq|SELECT c.accno, c.description, c.category
196 WHERE (c.charttype = 'H')
200 $sth = prepare_execute_query($form, $dbh, $query);
202 my @headingaccounts = ();
203 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
204 $form->{ $ref->{category} }{ $ref->{accno} }{description} =
205 "$ref->{description}";
206 $form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "H";
207 $form->{ $ref->{category} }{ $ref->{accno} }{accno} = $ref->{accno};
209 push @headingaccounts, $ref->{accno};
214 # if l_ob is selected l_cb is always ignored
215 if ( $form->{l_ob} ) {
216 $where .= ' AND ac.ob_transaction is true '
217 } elsif ( not $form->{l_cb} ) {
218 $where .= ' AND ac.cb_transaction is false ';
222 $fromdate = conv_dateq($fromdate);
223 if ($form->{method} eq 'cash') {
224 $subwhere .= " AND (transdate >= $fromdate)";
225 $glwhere = " AND (ac.transdate >= $fromdate)";
227 $where .= " AND (ac.transdate >= $fromdate)";
232 $todate = conv_dateq($todate);
233 $where .= " AND (ac.transdate <= $todate)";
234 $subwhere .= " AND (transdate <= $todate)";
237 if ($department_id) {
238 $dpt_where = qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|)|;
241 if ($form->{project_id}) {
242 # Diese Bedingung wird derzeit niemals wahr sein, da man in Bericht->Bilanz keine
243 # Projekte auswählen kann
244 $project = qq| AND (ac.project_id = | . conv_i($form->{project_id}, 'NULL') . qq|) |;
247 if ($form->{method} eq 'cash') {
249 qq|SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category
251 JOIN chart c ON (c.id = ac.chart_id)
252 JOIN ar a ON (a.id = ac.trans_id)
260 WHERE (a.chart_link LIKE '%AR_paid%')
264 GROUP BY c.accno, c.description, c.category
268 SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category
270 JOIN chart c ON (c.id = ac.chart_id)
271 JOIN ap a ON (a.id = ac.trans_id)
279 WHERE (a.chart_link LIKE '%AP_paid%')
283 GROUP BY c.accno, c.description, c.category
287 SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category
289 JOIN chart c ON (c.id = ac.chart_id)
290 JOIN gl a ON (a.id = ac.trans_id)
295 AND NOT ((ac.chart_link = 'AR') OR (ac.chart_link = 'AP'))
297 GROUP BY c.accno, c.description, c.category |;
299 if ($form->{project_id}) {
300 # s.o. keine Projektauswahl in Bilanz
305 SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, c.description AS description, c.category
307 JOIN ar a ON (a.id = ac.trans_id)
308 JOIN parts p ON (ac.parts_id = p.id)
309 JOIN chart c on (p.income_accno_id = c.id)
310 -- use transdate from subwhere
311 WHERE (c.category = 'I')
318 WHERE (a.chart_link LIKE '%AR_paid%')
322 GROUP BY c.accno, c.description, c.category
326 SELECT c.accno AS accno, SUM(ac.sellprice) AS amount, c.description AS description, c.category
328 JOIN ap a ON (a.id = ac.trans_id)
329 JOIN parts p ON (ac.parts_id = p.id)
330 JOIN chart c on (p.expense_accno_id = c.id)
331 WHERE (c.category = 'E')
338 WHERE a.chart_link LIKE '%AP_paid%'
342 GROUP BY c.accno, c.description, c.category |;
345 } else { # if ($form->{method} eq 'cash')
346 if ($department_id) {
347 $dpt_where = qq| AND a.department_id = | . conv_i($department_id);
348 $dpt_where_without_arapgl = qq| AND COALESCE((SELECT department_id FROM ar WHERE ar.id=ac.trans_id),
349 (SELECT department_id FROM gl WHERE gl.id=ac.trans_id),
350 (SELECT department_id FROM ap WHERE ap.id=ac.trans_id)) = | . conv_i($department_id);
354 SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category
356 JOIN chart c ON (c.id = ac.chart_id)
358 $dpt_where_without_arapgl
361 GROUP BY c.accno, c.description, c.category |;
363 if ($form->{project_id}) {
364 # s.o. keine Projektauswahl in Bilanz
368 SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, c.description AS description, c.category
370 JOIN ar a ON (a.id = ac.trans_id)
371 JOIN parts p ON (ac.parts_id = p.id)
372 JOIN chart c on (p.income_accno_id = c.id)
373 -- use transdate from subwhere
374 WHERE (c.category = 'I')
378 GROUP BY c.accno, c.description, c.category
382 SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount, c.description AS description, c.category
384 JOIN ap a ON (a.id = ac.trans_id)
385 JOIN parts p ON (ac.parts_id = p.id)
386 JOIN chart c on (p.expense_accno_id = c.id)
387 WHERE (c.category = 'E')
391 GROUP BY c.accno, c.description, c.category |;
399 $sth = prepare_execute_query($form, $dbh, $query);
401 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
403 if ($ref->{category} eq 'C') {
404 $ref->{category} = 'A';
407 # get last heading account
408 @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
412 $form->{ $ref->{category} }{$accno}{last} += $ref->{amount};
414 $form->{ $ref->{category} }{$accno}{this} += $ref->{amount};
418 $form->{ $ref->{category} }{ $ref->{accno} }{accno} = $ref->{accno};
419 $form->{ $ref->{category} }{ $ref->{accno} }{description} = $ref->{description};
420 $form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "A";
423 $form->{ $ref->{category} }{ $ref->{accno} }{last} += $ref->{amount};
425 $form->{ $ref->{category} }{ $ref->{accno} }{this} += $ref->{amount};
430 # remove accounts with zero balance
431 foreach $category (@{$categories}) {
432 foreach $accno (keys %{ $form->{$category} }) {
433 $form->{$category}{$accno}{last} = $form->round_amount($form->{$category}{$accno}{last}, $dec);
434 $form->{$category}{$accno}{this} = $form->round_amount($form->{$category}{$accno}{this}, $dec);
436 delete $form->{$category}{$accno}
437 if ( $form->{$category}{$accno}{this} == 0
438 && $form->{$category}{$accno}{last} == 0);
442 $main::lxdebug->leave_sub();
446 $main::lxdebug->enter_sub();
448 my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_;
450 my ($null, $department_id) = split /--/, $form->{department};
454 my $dpt_where_without_arapgl;
463 $where .= ' AND ac.cb_transaction is false ' unless $form->{l_cb};
466 $fromdate = conv_dateq($fromdate);
467 if ($form->{method} eq 'cash') {
468 $subwhere .= " AND (transdate >= $fromdate)";
469 $glwhere = " AND (ac.transdate >= $fromdate)";
470 $prwhere = " AND (a.transdate >= $fromdate)";
471 $inwhere = " AND (acc.transdate >= $fromdate)";
473 $where .= " AND (ac.transdate >= $fromdate)";
478 $todate = conv_dateq($todate);
479 $subwhere .= " AND (transdate <= $todate)";
480 $where .= " AND (ac.transdate <= $todate)";
481 $prwhere .= " AND (a.transdate <= $todate)";
482 $inwhere .= " AND (acc.transdate <= $todate)";
485 if ($department_id) {
486 $dpt_where = qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
489 if ($form->{project_id}) {
490 $project = qq| AND (ac.project_id = | . conv_i($form->{project_id}) . qq|) |;
494 # GUV patch by Ronny Rentner (Bug 1190)
496 # GUV IST-Versteuerung
498 # Alle tatsaechlichen _Zahlungseingaenge_
499 # im Zeitraum erfassen
500 # (Teilzahlungen werden prozentual auf verschiedene Steuern aufgeteilt)
504 if ($form->{method} eq 'cash') {
507 SELECT SUM( ac.amount * CASE WHEN COALESCE((SELECT amount FROM ar WHERE id = ac.trans_id), 0) != 0 THEN
508 /* ar amount is not zero, so we can divide by amount */
509 (SELECT SUM(acc.amount) * -1
512 AND acc.trans_id = ac.trans_id
513 AND acc.chart_link LIKE '%AR_paid%')
514 / (SELECT amount FROM ar WHERE id = ac.trans_id)
516 /* ar amount is zero, or we are checking with a non-ar-transaction, so we return 0 in both cases as multiplicator of ac.amount */
518 ) AS amount, c.$category
520 LEFT JOIN chart c ON (c.id = ac.chart_id)
521 LEFT JOIN ar ON (ar.id = ac.trans_id)
522 LEFT JOIN taxkeys tk ON (tk.id = (
523 SELECT id FROM taxkeys
524 WHERE chart_id = ac.chart_id
525 AND startdate <= COALESCE(ar.deliverydate,ar.transdate)
526 ORDER BY startdate DESC LIMIT 1
529 WHERE ac.trans_id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE 1=1 $subwhere)
534 SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
536 JOIN chart c ON (c.id = ac.chart_id)
537 JOIN ar a ON (a.id = ac.trans_id)
538 WHERE $where $dpt_where
539 AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AR_paid%') $subwhere)
545 SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
547 JOIN chart c ON (c.id = ac.chart_id)
548 JOIN ap a ON (a.id = ac.trans_id)
549 WHERE $where $dpt_where
550 AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AP_paid%') $subwhere)
556 SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
558 JOIN chart c ON (c.id = ac.chart_id)
559 JOIN gl a ON (a.id = ac.trans_id)
560 WHERE $where $dpt_where $glwhere
561 AND NOT ((ac.chart_link = 'AR') OR (ac.chart_link = 'AP'))
566 if ($form->{project_id}) {
570 SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
572 JOIN ar a ON (a.id = ac.trans_id)
573 JOIN parts p ON (ac.parts_id = p.id)
574 JOIN chart c on (p.income_accno_id = c.id)
575 WHERE (c.category = 'I') $prwhere $dpt_where
576 AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AR_paid%') $subwhere)
582 SELECT SUM(ac.sellprice * chart_category_to_sgn(c.category)) AS amount, c.$category
584 JOIN ap a ON (a.id = ac.trans_id)
585 JOIN parts p ON (ac.parts_id = p.id)
586 JOIN chart c on (p.expense_accno_id = c.id)
587 WHERE (c.category = 'E') $prwhere $dpt_where
588 AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AP_paid%') $subwhere)
594 } else { # if ($form->{method} eq 'cash')
595 if ($department_id) {
596 $dpt_where = qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
597 $dpt_where_without_arapgl = qq| AND COALESCE((SELECT department_id FROM ar WHERE ar.id=ac.trans_id),
598 (SELECT department_id FROM gl WHERE gl.id=ac.trans_id),
599 (SELECT department_id FROM ap WHERE ap.id=ac.trans_id)) = | . conv_i($department_id);
603 SELECT sum(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
605 JOIN chart c ON (c.id = ac.chart_id)
607 $dpt_where_without_arapgl
609 GROUP BY c.$category |;
611 if ($form->{project_id}) {
615 SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
617 JOIN ar a ON (a.id = ac.trans_id)
618 JOIN parts p ON (ac.parts_id = p.id)
619 JOIN chart c on (p.income_accno_id = c.id)
620 WHERE (c.category = 'I')
628 SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
630 JOIN ap a ON (a.id = ac.trans_id)
631 JOIN parts p ON (ac.parts_id = p.id)
632 JOIN chart c on (p.expense_accno_id = c.id)
633 WHERE (c.category = 'E')
637 GROUP BY c.$category |;
645 foreach my $ref (selectall_hashref_query($form, $dbh, $query)) {
646 if ($category eq "pos_bwa") {
648 $form->{ $ref->{$category} }{kumm} += $ref->{amount};
650 $form->{ $ref->{$category} }{jetzt} += $ref->{amount};
653 $form->{ $ref->{$category} } += $ref->{amount};
657 $main::lxdebug->leave_sub();
661 $main::lxdebug->enter_sub();
663 my ($self, $myconfig, $form, %options) = @_;
665 my $dbh = $form->dbconnect($myconfig);
667 my ($query, $sth, $ref);
670 my ($null, $department_id) = split /--/, $form->{department};
671 my @headingaccounts = ();
673 my $dpt_where_without_arapgl;
677 my $invwhere = $where;
679 if ($department_id) {
680 $dpt_where = qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
681 $dpt_where_without_arapgl = qq| AND COALESCE((SELECT department_id FROM ar WHERE ar.id=ac.trans_id),
682 (SELECT department_id FROM gl WHERE gl.id=ac.trans_id),
683 (SELECT department_id FROM ap WHERE ap.id=ac.trans_id)) = | . conv_i($department_id);
686 # project_id only applies to getting transactions
687 # it has nothing to do with a trial balance
688 # but we use the same function to collect information
690 if ($form->{project_id}) {
691 $project = qq| AND (ac.project_id = | . conv_i($form->{project_id}, 'NULL') . qq|) |;
694 my $acc_cash_where = "";
695 # my $ar_cash_where = "";
696 # my $ap_cash_where = "";
699 if ($form->{method} eq "cash") {
701 qq| AND (ac.trans_id IN (
704 WHERE datepaid >= '$form->{fromdate}'
705 AND datepaid <= '$form->{todate}'
711 WHERE datepaid >= '$form->{fromdate}'
712 AND datepaid <= '$form->{todate}'
718 WHERE transdate >= '$form->{fromdate}'
719 AND transdate <= '$form->{todate}'
721 # $ar_ap_cash_where = qq| AND (a.datepaid>='$form->{fromdate}' AND a.datepaid<='$form->{todate}') |;
724 if ($options{beginning_balances}) {
725 foreach my $prefix (qw(from to)) {
726 next if ($form->{"${prefix}date"});
728 my $min_max = $prefix eq 'from' ? 'min' : 'max';
729 $query = qq|SELECT ${min_max}(transdate)
732 $dpt_where_without_arapgl
734 ($form->{"${prefix}date"}) = selectfirst_array_query($form, $dbh, $query);
737 # get beginning balances
739 qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount, c.description
741 LEFT JOIN chart c ON (ac.chart_id = c.id)
742 WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction
743 $dpt_where_without_arapgl
745 GROUP BY c.accno, c.category, c.description |;
747 $sth = prepare_execute_query($form, $dbh, $query, $form->{fromdate});
749 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
751 if ($ref->{amount} != 0 || $form->{all_accounts}) {
752 $trb{ $ref->{accno} }{description} = $ref->{description};
753 $trb{ $ref->{accno} }{charttype} = 'A';
754 $trb{ $ref->{accno} }{beginning_balance} = $ref->{amount};
756 if ($ref->{amount} > 0) {
757 $trb{ $ref->{accno} }{haben_eb} = $ref->{amount};
759 $trb{ $ref->{accno} }{soll_eb} = $ref->{amount} * -1;
761 $trb{ $ref->{accno} }{category} = $ref->{category};
770 qq|SELECT c.accno, c.description, c.category
772 WHERE c.charttype = 'H'
775 $sth = prepare_execute_query($form, $dbh, $query);
777 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
778 $trb{ $ref->{accno} }{description} = $ref->{description};
779 $trb{ $ref->{accno} }{charttype} = 'H';
780 $trb{ $ref->{accno} }{category} = $ref->{category};
782 push @headingaccounts, $ref->{accno};
788 my $saldowhere = " 1 = 1 ";
789 my $sumwhere = " 1 = 1 ";
791 my $sumsubwhere = '';
792 my $saldosubwhere = '';
793 my $glsaldowhere = '';
798 my ($fromdate, $todate);
800 if ($form->{fromdate} || $form->{todate}) {
801 if ($form->{fromdate}) {
802 $fromdate = conv_dateq($form->{fromdate});
803 $tofrom .= " AND (ac.transdate >= $fromdate)";
804 $subwhere .= " AND (ac.transdate >= $fromdate)";
805 $sumsubwhere .= " AND (ac.transdate >= (select date_trunc('year', date $fromdate))) ";
806 $saldosubwhere .= " AND (ac,transdate>=(select date_trunc('year', date $fromdate))) ";
807 $invwhere .= " AND (a.transdate >= $fromdate)";
808 $glsaldowhere .= " AND ac.transdate>=(select date_trunc('year', date $fromdate)) ";
809 $glwhere = " AND (ac.transdate >= $fromdate)";
810 $glsumwhere = " AND (ac.transdate >= (select date_trunc('year', date $fromdate))) ";
812 if ($form->{todate}) {
813 $todate = conv_dateq($form->{todate});
814 $tofrom .= " AND (ac.transdate <= $todate)";
815 $invwhere .= " AND (a.transdate <= $todate)";
816 $saldosubwhere .= " AND (ac.transdate <= $todate)";
817 $sumsubwhere .= " AND (ac.transdate <= $todate)";
818 $subwhere .= " AND (ac.transdate <= $todate)";
819 $glwhere .= " AND (ac.transdate <= $todate)";
820 $glsumwhere .= " AND (ac.transdate <= $todate) ";
821 $glsaldowhere .= " AND (ac.transdate <= $todate) ";
825 if ($form->{method} eq "cash") {
827 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) |;
828 $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) |;
830 $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) |;
832 $where .= $tofrom . " AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL)";
833 $saldowhere .= $glsaldowhere . " AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL)";
834 $sumwhere .= $glsumwhere . " AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL)";
838 SELECT c.accno, c.description, c.category, SUM(ac.amount) AS amount
840 JOIN chart c ON (c.id = ac.chart_id)
842 $dpt_where_without_arapgl
844 GROUP BY c.accno, c.description, c.category |;
846 if ($form->{project_id}) {
848 -- add project transactions from invoice
852 SELECT c.accno, c.description, c.category, SUM(ac.sellprice * ac.qty) AS amount
854 JOIN ar a ON (ac.trans_id = a.id)
855 JOIN parts p ON (ac.parts_id = p.id)
856 JOIN chart c ON (p.income_accno_id = c.id)
860 GROUP BY c.accno, c.description, c.category
864 SELECT c.accno, c.description, c.category, SUM(ac.sellprice * ac.qty) * -1 AS amount
866 JOIN ap a ON (ac.trans_id = a.id)
867 JOIN parts p ON (ac.parts_id = p.id)
868 JOIN chart c ON (p.expense_accno_id = c.id)
872 GROUP BY c.accno, c.description, c.category
876 $query .= qq| ORDER BY accno|;
878 $sth = prepare_execute_query($form, $dbh, $query);
880 # calculate the debit and credit in the period
881 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
882 $trb{ $ref->{accno} }{description} = $ref->{description};
883 $trb{ $ref->{accno} }{charttype} = 'A';
884 $trb{ $ref->{accno} }{category} = $ref->{category};
885 $trb{ $ref->{accno} }{amount} += $ref->{amount};
889 # prepare query for each account
890 my ($q_drcr, $drcr, $q_project_drcr, $project_drcr);
894 (SELECT SUM(ac.amount) * -1
896 JOIN chart c ON (c.id = ac.chart_id)
898 $dpt_where_without_arapgl
901 AND (c.accno = ?)) AS debit,
903 (SELECT SUM(ac.amount)
905 JOIN chart c ON (c.id = ac.chart_id)
907 $dpt_where_without_arapgl
910 AND c.accno = ?) AS credit,
911 (SELECT SUM(ac.amount)
913 JOIN chart c ON (ac.chart_id = c.id)
915 $dpt_where_without_arapgl
917 AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo,
919 (SELECT SUM(ac.amount)
921 JOIN chart c ON (ac.chart_id = c.id)
923 $dpt_where_without_arapgl
926 AND c.accno = ?) AS sum_credit,
928 (SELECT SUM(ac.amount)
930 JOIN chart c ON (ac.chart_id = c.id)
932 $dpt_where_without_arapgl
935 AND c.accno = ?) AS sum_debit,
937 (SELECT max(ac.transdate) FROM acc_trans ac
938 JOIN chart c ON (ac.chart_id = c.id)
940 $dpt_where_without_arapgl
942 AND c.accno = ?) AS last_transaction
947 $drcr = prepare_query($form, $dbh, $q_drcr);
949 if ($form->{project_id}) {
950 # prepare query for each account
953 (SELECT SUM(ac.sellprice * ac.qty) * -1
955 JOIN parts p ON (ac.parts_id = p.id)
956 JOIN ap a ON (ac.trans_id = a.id)
957 JOIN chart c ON (p.expense_accno_id = c.id)
961 AND c.accno = ?) AS debit,
963 (SELECT SUM(ac.sellprice * ac.qty)
965 JOIN parts p ON (ac.parts_id = p.id)
966 JOIN ar a ON (ac.trans_id = a.id)
967 JOIN chart c ON (p.income_accno_id = c.id)
971 AND c.accno = ?) AS credit,
973 (SELECT SUM(ac.amount)
975 JOIN chart c ON (ac.chart_id = c.id)
977 $dpt_where_without_arapgl
979 AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo,
981 (SELECT SUM(ac.amount)
983 JOIN chart c ON (ac.chart_id = c.id)
985 $dpt_where_without_arapgl
988 AND c.accno = ?) AS sum_credit,
990 (SELECT SUM(ac.amount)
992 JOIN chart c ON (ac.chart_id = c.id)
994 $dpt_where_without_arapgl
997 AND c.accno = ?) AS sum_debit,
1000 (SELECT max(ac.transdate) FROM acc_trans ac
1001 JOIN chart c ON (ac.chart_id = c.id)
1003 $dpt_where_without_arapgl
1005 AND c.accno = ?) AS last_transaction
1008 $project_drcr = prepare_query($form, $dbh, $q_project_drcr);
1012 my ($debit, $credit, $saldo, $soll_saldo, $haben_saldo,$soll_kummuliert, $haben_kummuliert, $last_transaction);
1014 foreach my $accno (sort keys %trb) {
1017 $ref->{accno} = $accno;
1018 map { $ref->{$_} = $trb{$accno}{$_} }
1019 qw(description category charttype amount soll_eb haben_eb beginning_balance);
1021 $ref->{balance} = $form->round_amount($balance{ $ref->{accno} }, 2);
1023 if ($trb{$accno}{charttype} eq 'A') {
1026 do_statement($form, $drcr, $q_drcr, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno});
1028 ($debit, $credit, $saldo, $haben_saldo, $soll_saldo) = (0, 0, 0, 0, 0);
1029 my ($soll_kumuliert, $haben_kumuliert) = (0, 0);
1030 $last_transaction = "";
1031 while (($debit, $credit, $saldo, $haben_kumuliert, $soll_kumuliert, $last_transaction) = $drcr->fetchrow_array) {
1032 $ref->{debit} += $debit;
1033 $ref->{credit} += $credit;
1035 $ref->{haben_saldo} += $saldo;
1037 $ref->{soll_saldo} += $saldo * -1;
1039 $ref->{last_transaction} = $last_transaction;
1040 $ref->{soll_kumuliert} = $soll_kumuliert * -1;
1041 $ref->{haben_kumuliert} = $haben_kumuliert;
1045 if ($form->{project_id}) {
1048 do_statement($form, $project_drcr, $q_project_drcr, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno});
1050 ($debit, $credit) = (0, 0);
1051 while (($debit, $credit, $saldo, $haben_kumuliert, $soll_kumuliert, $last_transaction) = $project_drcr->fetchrow_array) {
1052 $ref->{debit} += $debit;
1053 $ref->{credit} += $credit;
1055 $ref->{haben_saldo} += $saldo;
1057 $ref->{soll_saldo} += $saldo * -1;
1059 $ref->{soll_kumuliert} += $soll_kumuliert * -1;
1060 $ref->{haben_kumuliert} += $haben_kumuliert;
1062 $project_drcr->finish;
1065 $ref->{debit} = $form->round_amount($ref->{debit}, 2);
1066 $ref->{credit} = $form->round_amount($ref->{credit}, 2);
1068 if ($ref->{haben_saldo} != 0) {
1069 $ref->{haben_saldo} = $ref->{haben_saldo} + $ref->{beginning_balance};
1070 if ($ref->{haben_saldo} < 0) {
1071 $ref->{soll_saldo} = $form->round_amount(($ref->{haben_saldo} *- 1), 2);
1072 $ref->{haben_saldo} = 0;
1075 $ref->{soll_saldo} = $ref->{soll_saldo} - $ref->{beginning_balance};
1076 if ($ref->{soll_saldo} < 0) {
1077 $ref->{haben_saldo} = $form->round_amount(($ref->{soll_saldo} * -1), 2);
1078 $ref->{soll_saldo} = 0;
1081 $ref->{haben_saldo} = $form->round_amount($ref->{haben_saldo}, 2);
1082 $ref->{soll_saldo} = $form->round_amount($ref->{soll_saldo}, 2);
1083 $ref->{haben_kumuliert} = $form->round_amount($ref->{haben_kumuliert}, 2);
1084 $ref->{soll_kumuliert} = $form->round_amount($ref->{soll_kumuliert}, 2);
1089 @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
1090 $accno = pop @accno;
1092 $trb{$accno}{debit} += $ref->{debit};
1093 $trb{$accno}{credit} += $ref->{credit};
1094 $trb{$accno}{soll_saldo} += $ref->{soll_saldo};
1095 $trb{$accno}{haben_saldo} += $ref->{haben_saldo};
1096 $trb{$accno}{soll_kumuliert} += $ref->{soll_kumuliert};
1097 $trb{$accno}{haben_kumuliert} += $ref->{haben_kumuliert};
1100 push @{ $form->{TB} }, $ref;
1106 # debits and credits for headings
1107 foreach my $accno (@headingaccounts) {
1108 foreach $ref (@{ $form->{TB} }) {
1109 if ($accno eq $ref->{accno}) {
1110 $ref->{debit} = $trb{$accno}{debit};
1111 $ref->{credit} = $trb{$accno}{credit};
1112 $ref->{soll_saldo} = $trb{$accno}{soll_saldo};
1113 $ref->{haben_saldo} = $trb{$accno}{haben_saldo};
1114 $ref->{soll_kumuliert} = $trb{$accno}{soll_kumuliert};
1115 $ref->{haben_kumuliert} = $trb{$accno}{haben_kumuliert};
1120 $main::lxdebug->leave_sub();
1124 $main::lxdebug->enter_sub();
1125 my ($self, $dbh, $form) = @_;
1126 my $arap = $form->{arap} eq "ar" ? "ar" : "ap";
1127 my $query = qq|SELECT invnumber FROM $arap WHERE invnumber LIKE "Storno zu "|;
1128 my $sth = $dbh->prepare($query);
1129 while(my $ref = $sth->fetchrow_hashref()) {
1130 $ref->{invnumer} =~ s/Storno zu //g;
1131 $form->{storno}{$ref->{invnumber}} = 1;
1133 $main::lxdebug->leave_sub();
1137 $main::lxdebug->enter_sub();
1139 my ($self, $myconfig, $form) = @_;
1141 # connect to database
1142 my $dbh = $form->dbconnect($myconfig);
1144 my ($invoice, $arap, $buysell, $ct, $ct_id, $ml);
1146 # falls customer ziehen wir die offene forderungsliste
1147 # anderfalls für die lieferanten die offenen verbindlichkeitne
1148 if ($form->{ct} eq "customer") {
1161 $ct_id = "${ct}_id";
1163 # erweiterung um einen freien zeitraum oder einen stichtag
1164 # mit entsprechender altersstrukturliste (s.a. Bug 1842)
1165 # eine neue variable an der oberfläche eingeführt, somit ist
1166 # todate == freier zeitrau und fordate == stichtag
1168 my ($review_of_aging_list, $todate, $fromdate, $fromwhere, $fordate);
1170 if ($form->{reporttype} eq 'custom') { # altersstrukturliste
1172 # explizit rausschmeissen was man für diesen bericht nicht braucht
1173 delete $form->{fromdate};
1174 delete $form->{todate};
1176 # an der oberfläche ist das tagesaktuelle datum vorausgewählt
1177 # falls es dennoch per Benutzereingabe gelöscht wird, lieber wieder vorbelegen
1178 # ferner muss für die spätere DB-Abfrage muss todate gesetzt sein.
1179 $form->{fordate} = $form->current_date($myconfig) unless ($form->{fordate});
1180 $fordate = conv_dateq($form->{fordate});
1183 if ($form->{review_of_aging_list}) { # falls die liste leer ist, alles anzeigen
1184 if ($form->{review_of_aging_list} =~ m "-") { # .. periode von bis
1185 my @period = split(/-/, $form->{review_of_aging_list}); # ... von periode bis periode
1186 $review_of_aging_list = " AND $period[0] < (date $fordate) - duedate
1187 AND (date $fordate) - duedate < $period[1]";
1189 $form->{review_of_aging_list} =~ s/[^0-9]//g; # größer 120 das substitute ist nur für das '>' zeichen
1190 $review_of_aging_list = " AND $form->{review_of_aging_list} < (date $fordate) - duedate";
1193 } else { # freier zeitraum OHNE review_of_aging_list
1194 $form->{todate} = $form->current_date($myconfig) unless ($form->{todate});
1195 $todate = conv_dateq($form->{todate});
1196 $fromdate = conv_dateq($form->{fromdate});
1197 $fromwhere = ($form->{fromdate} ne "") ? " AND (transdate >= (date $fromdate)) " : "";
1199 my $where = " 1 = 1 ";
1202 if ($form->{$ct_id}) {
1203 $where .= qq| AND (ct.id = | . conv_i($form->{$ct_id}) . qq|)|;
1204 } elsif ($form->{ $form->{ct} }) {
1205 $where .= qq| AND (ct.name ILIKE | . $dbh->quote('%' . $form->{$ct} . '%') . qq|)|;
1210 if ($form->{department}) {
1211 my ($null, $department_id) = split /--/, $form->{department};
1212 $dpt_join = qq| JOIN department d ON (a.department_id = d.id) |;
1213 $where .= qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|)|;
1214 $where_dpt = qq| AND (${arap}.department_id = | . conv_i($department_id, 'NULL') . qq|)|;
1218 SELECT ${ct}.id AS ctid, ${ct}.name,
1219 street, zipcode, city, country, contact, email,
1220 phone as customerphone, fax as customerfax, ${ct}number,
1221 "invnumber", "transdate",
1222 (amount - COALESCE((SELECT sum(amount)*$ml FROM acc_trans WHERE chart_link ilike '%paid%' AND acc_trans.trans_id=${arap}.id AND acc_trans.transdate <= (date $todate)),0)) as "open", "amount",
1223 "duedate", invoice, ${arap}.id, date_part('days', now() - duedate) as overduedays,
1226 WHERE (${arap}.curr = exchangerate.curr)
1227 AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
1229 WHERE ((paid != amount) OR (datepaid > (date $todate) AND datepaid is not null))
1230 AND NOT COALESCE (${arap}.storno, 'f')
1231 AND (${arap}.${ct}_id = ${ct}.id)
1234 AND (transdate <= (date $todate) $fromwhere )
1235 $review_of_aging_list
1236 ORDER BY ctid, transdate, invnumber |;
1238 my $sth_details = prepare_query($form, $dbh, $q_details);
1240 # select outstanding vendors or customers, depends on $ct
1242 qq|SELECT DISTINCT ct.id, ct.name
1243 FROM $ct ct, $arap a
1246 AND (a.${ct_id} = ct.id)
1247 AND ((a.paid != a.amount) OR ((a.datepaid > $todate) AND (datepaid is NOT NULL)))
1248 AND (a.transdate <= $todate $fromwhere)
1251 my $sth = prepare_execute_query($form, $dbh, $query);
1254 # for each company that has some stuff outstanding
1255 while (my ($id) = $sth->fetchrow_array) {
1256 do_statement($form, $sth_details, $q_details, $id);
1258 while (my $ref = $sth_details->fetchrow_hashref("NAME_lc")) {
1259 $ref->{module} = ($ref->{invoice}) ? $invoice : $arap;
1260 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
1261 push @{ $form->{AG} }, $ref;
1264 $sth_details->finish;
1273 $main::lxdebug->leave_sub();
1277 $main::lxdebug->enter_sub();
1279 my ($self, $myconfig, $form) = @_;
1281 # connect to database
1282 my $dbh = $form->dbconnect($myconfig);
1284 my $ct = $form->{ct} eq "customer" ? "customer" : "vendor";
1287 qq|SELECT ct.name, ct.email, ct.cc, ct.bcc
1290 ($form->{ $form->{ct} }, $form->{email}, $form->{cc}, $form->{bcc}) =
1291 selectrow_query($form, $dbh, $query, $form->{"${ct}_id"});
1294 $main::lxdebug->leave_sub();
1298 $main::lxdebug->enter_sub();
1300 my ($self, $myconfig, $form) = @_;
1302 # connect to database
1303 my $dbh = $form->dbconnect($myconfig);
1305 my ($null, $department_id) = split /--/, $form->{department};
1308 my $where = "1 = 1";
1310 if ($department_id) {
1311 $where .= qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
1316 if ($form->{accno}) {
1317 $accno = $form->{accno};
1318 $rate = $form->{"$form->{accno}_rate"};
1319 $accno = qq| AND (ch.accno = | . $dbh->quote($accno) . qq|)|;
1325 if ($form->{db} eq 'ar') {
1326 $table = "customer";
1333 my $arap = lc($ARAP);
1335 my $transdate = "a.transdate";
1337 if ($form->{method} eq 'cash') {
1338 $transdate = "a.datepaid";
1340 my $todate = conv_dateq($form->{todate} ? $form->{todate} : $form->current_date($myconfig));
1347 WHERE (a.chart_link LIKE '%${ARAP}_paid%')
1348 AND (transdate <= $todate)
1353 # if there are any dates construct a where
1354 $where .= " AND ($transdate >= " . conv_dateq($form->{fromdate}) . ") " if ($form->{fromdate});
1355 $where .= " AND ($transdate <= " . conv_dateq($form->{todate}) . ") " if ($form->{todate});
1357 my $ml = ($form->{db} eq 'ar') ? 1 : -1;
1359 my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name));
1360 $sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } qw(id transdate invnumber name netamount tax)));
1363 qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, a.invnumber, n.name, a.netamount,
1364 ac.amount * $ml AS tax
1366 JOIN ${arap} a ON (a.id = ac.trans_id)
1367 JOIN chart ch ON (ch.id = ac.chart_id)
1368 JOIN $table n ON (n.id = a.${table}_id)
1372 AND (a.invoice = '0')
1376 SELECT a.id, '1' AS invoice, $transdate AS transdate, a.invnumber, n.name, i.sellprice * i.qty AS netamount,
1377 i.sellprice * i.qty * $rate * $ml AS tax
1379 JOIN ${arap} a ON (a.id = ac.trans_id)
1380 JOIN chart ch ON (ch.id = ac.chart_id)
1381 JOIN $table n ON (n.id = a.${table}_id)
1382 JOIN ${table}tax t ON (t.${table}_id = n.id)
1383 JOIN invoice i ON (i.trans_id = a.id)
1387 AND (a.invoice = '1')
1388 ORDER BY $sortorder|;
1390 $form->{TR} = selectall_hashref_query($form, $dbh, $query);
1394 $main::lxdebug->leave_sub();
1397 sub paymentaccounts {
1398 $main::lxdebug->enter_sub();
1400 my ($self, $myconfig, $form) = @_;
1402 # connect to database, turn AutoCommit off
1403 my $dbh = $form->dbconnect_noauto($myconfig);
1405 my $ARAP = $form->{db} eq "ar" ? "AR" : "AP";
1407 # get A(R|P)_paid accounts
1409 qq|SELECT accno, description
1411 WHERE link LIKE '%${ARAP}_paid%'|;
1412 $form->{PR} = selectall_hashref_query($form, $dbh, $query);
1416 $main::lxdebug->leave_sub();
1420 $main::lxdebug->enter_sub();
1422 my ($self, $myconfig, $form) = @_;
1424 # connect to database, turn AutoCommit off
1425 my $dbh = $form->dbconnect_noauto($myconfig);
1430 if ($form->{db} eq 'ar') {
1431 $table = 'customer';
1442 if ($form->{department_id}) {
1443 $where = qq| AND (a.department_id = | . conv_i($form->{department_id}, 'NULL') . qq|) |;
1446 if ($form->{fromdate}) {
1447 $where .= " AND (ac.transdate >= " . $dbh->quote($form->{fromdate}) . ") ";
1449 if ($form->{todate}) {
1450 $where .= " AND (ac.transdate <= " . $dbh->quote($form->{todate}) . ") ";
1452 if (!$form->{fx_transaction}) {
1453 $where .= " AND ac.fx_transaction = '0'";
1458 if ($form->{reference}) {
1459 $reference = $dbh->quote('%' . $form->{reference} . '%');
1460 $invnumber = " AND (a.invnumber LIKE $reference)";
1461 $reference = " AND (a.reference LIKE $reference)";
1463 if ($form->{source}) {
1464 $where .= " AND (ac.source ILIKE " . $dbh->quote('%' . $form->{source} . '%') . ") ";
1466 if ($form->{memo}) {
1467 $where .= " AND (ac.memo ILIKE " . $dbh->quote('%' . $form->{memo} . '%') . ") ";
1470 my %sort_columns = (
1471 'transdate' => [ qw(transdate lower_invnumber lower_name) ],
1472 'invnumber' => [ qw(lower_invnumber lower_name transdate) ],
1473 'name' => [ qw(lower_name transdate) ],
1474 'source' => [ qw(lower_source) ],
1475 'memo' => [ qw(lower_memo) ],
1477 my %lowered_columns = (
1478 'invnumber' => { 'gl' => 'a.reference', 'arap' => 'a.invnumber', },
1479 'memo' => { 'gl' => 'ac.memo', 'arap' => 'ac.memo', },
1480 'source' => { 'gl' => 'ac.source', 'arap' => 'ac.source', },
1481 'name' => { 'gl' => 'a.description', 'arap' => 'c.name', },
1484 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
1485 my $sortkey = $sort_columns{$form->{sort}} ? $form->{sort} : 'transdate';
1486 my $sortorder = join ', ', map { "$_ $sortdir" } @{ $sort_columns{$sortkey} };
1489 my %columns_for_sorting = ( 'gl' => '', 'arap' => '', );
1490 foreach my $spec (@{ $sort_columns{$sortkey} }) {
1491 next if ($spec !~ m/^lower_(.*)$/);
1494 map { $columns_for_sorting{$_} .= sprintf(', lower(%s) AS lower_%s', $lowered_columns{$column}->{$_}, $column) } qw(gl arap);
1497 $query = qq|SELECT id, accno, description FROM chart WHERE accno = ?|;
1498 $sth = prepare_query($form, $dbh, $query);
1501 qq|SELECT c.name, a.invnumber, a.ordnumber,
1502 ac.transdate, ac.amount * $ml AS paid, ac.source,
1503 a.invoice, a.id, ac.memo, '${arap}' AS module
1504 $columns_for_sorting{arap}
1506 JOIN $arap a ON (ac.trans_id = a.id)
1507 JOIN $table c ON (c.id = a.${table}_id)
1508 WHERE (ac.chart_id = ?)
1514 SELECT a.description, a.reference, NULL AS ordnumber,
1515 ac.transdate, ac.amount * $ml AS paid, ac.source,
1516 '0' as invoice, a.id, ac.memo, 'gl' AS module
1517 $columns_for_sorting{gl}
1519 JOIN gl a ON (a.id = ac.trans_id)
1520 WHERE (ac.chart_id = ?)
1523 AND (ac.amount * $ml) > 0
1525 ORDER BY $sortorder|;
1526 my $sth_details = prepare_query($form, $dbh, $q_details);
1530 # cycle through each id
1531 foreach my $accno (split(/ /, $form->{paymentaccounts})) {
1532 do_statement($form, $sth, $query, $accno);
1533 my $ref = $sth->fetchrow_hashref();
1534 push(@{ $form->{PR} }, $ref);
1537 $form->{ $ref->{id} } = [] unless ($form->{ $ref->{id} });
1539 do_statement($form, $sth_details, $q_details, $ref->{id}, $ref->{id});
1540 while (my $pr = $sth_details->fetchrow_hashref()) {
1541 push(@{ $form->{ $ref->{id} } }, $pr);
1543 $sth_details->finish();
1548 $main::lxdebug->leave_sub();
1552 $main::lxdebug->enter_sub();
1554 my ($self, $myconfig, $form) = @_;
1556 # connect to database
1557 my $dbh = $form->dbconnect($myconfig);
1559 my $last_period = 0;
1562 qw(1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40);
1564 $form->{decimalplaces} *= 1;
1566 &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, "pos_bwa");
1568 # if there are any compare dates
1570 if ($form->{fromdate} || $form->{todate}) {
1572 if ($form->{fromdate}) {
1573 $form->{fromdate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
1576 $form->{todate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
1579 my $kummfromdate = $form->{comparefromdate};
1580 my $kummtodate = $form->{comparetodate};
1581 &get_accounts_g($dbh, $last_period, $kummfromdate, $kummtodate, $form, "pos_bwa");
1584 my @periods = qw(jetzt kumm);
1585 my @gesamtleistung = qw(1 3);
1586 my @gesamtkosten = qw (10 11 12 13 14 15 16 17 18 20);
1588 qw (rohertrag betriebrohertrag betriebsergebnis neutraleraufwand neutralerertrag ergebnisvorsteuern ergebnis gesamtleistung gesamtkosten);
1590 foreach my $key (@periods) {
1591 $form->{ "$key" . "gesamtleistung" } = 0;
1592 $form->{ "$key" . "gesamtkosten" } = 0;
1594 foreach $category (@categories) {
1596 if (defined($form->{$category}{$key})) {
1597 $form->{"$key$category"} =
1598 $form->format_amount($myconfig,
1599 $form->round_amount($form->{$category}{$key}, 2
1601 $form->{decimalplaces},
1605 foreach my $item (@gesamtleistung) {
1606 $form->{ "$key" . "gesamtleistung" } += $form->{$item}{$key};
1608 $form->{ "$key" . "gesamtleistung" } -= $form->{2}{$key};
1610 foreach my $item (@gesamtkosten) {
1611 $form->{ "$key" . "gesamtkosten" } += $form->{$item}{$key};
1613 $form->{ "$key" . "rohertrag" } =
1614 $form->{ "$key" . "gesamtleistung" } - $form->{4}{$key};
1615 $form->{ "$key" . "betriebrohertrag" } =
1616 $form->{ "$key" . "rohertrag" } + $form->{5}{$key};
1617 $form->{ "$key" . "betriebsergebnis" } =
1618 $form->{ "$key" . "betriebrohertrag" } -
1619 $form->{ "$key" . "gesamtkosten" };
1620 $form->{ "$key" . "neutraleraufwand" } =
1621 $form->{19}{$key} + $form->{30}{$key} + $form->{31}{$key};
1622 $form->{ "$key" . "neutralerertrag" } =
1623 $form->{32}{$key} + $form->{33}{$key} + $form->{34}{$key};
1624 $form->{ "$key" . "ergebnisvorsteuern" } =
1625 $form->{ "$key" . "betriebsergebnis" } -
1626 $form->{ "$key" . "neutraleraufwand" } +
1627 $form->{ "$key" . "neutralerertrag" };
1628 $form->{ "$key" . "ergebnis" } =
1629 $form->{ "$key" . "ergebnisvorsteuern" } - $form->{35}{$key};
1631 if ($form->{ "$key" . "gesamtleistung" } > 0) {
1632 foreach $category (@categories) {
1633 if (defined($form->{$category}{$key})) {
1634 $form->{ "$key" . "gl" . "$category" } =
1635 $form->format_amount(
1637 $form->round_amount(
1638 ($form->{$category}{$key} /
1639 $form->{ "$key" . "gesamtleistung" } * 100
1641 $form->{decimalplaces}
1643 $form->{decimalplaces},
1647 foreach my $item (@ergebnisse) {
1648 $form->{ "$key" . "gl" . "$item" } =
1649 $form->format_amount($myconfig,
1650 $form->round_amount(
1651 ( $form->{ "$key" . "$item" } /
1652 $form->{ "$key" . "gesamtleistung" } * 100
1654 $form->{decimalplaces}
1656 $form->{decimalplaces},
1661 if ($form->{ "$key" . "gesamtkosten" } > 0) {
1662 foreach $category (@categories) {
1663 if (defined($form->{$category}{$key})) {
1664 $form->{ "$key" . "gk" . "$category" } =
1665 $form->format_amount($myconfig,
1666 $form->round_amount(
1667 ($form->{$category}{$key} /
1668 $form->{ "$key" . "gesamtkosten" } * 100
1670 $form->{decimalplaces}
1672 $form->{decimalplaces},
1676 foreach my $item (@ergebnisse) {
1677 $form->{ "$key" . "gk" . "$item" } =
1678 $form->format_amount($myconfig,
1679 $form->round_amount(
1680 ($form->{ "$key" . "$item" } /
1681 $form->{ "$key" . "gesamtkosten" } * 100
1683 $form->{decimalplaces}
1685 $form->{decimalplaces},
1690 if ($form->{10}{$key} > 0) {
1691 foreach $category (@categories) {
1692 if (defined($form->{$category}{$key})) {
1693 $form->{ "$key" . "pk" . "$category" } =
1694 $form->format_amount(
1696 $form->round_amount(
1697 ($form->{$category}{$key} / $form->{10}{$key} * 100),
1698 $form->{decimalplaces}
1700 $form->{decimalplaces},
1704 foreach my $item (@ergebnisse) {
1705 $form->{ "$key" . "pk" . "$item" } =
1706 $form->format_amount($myconfig,
1707 $form->round_amount(
1708 ($form->{ "$key" . "$item" } /
1709 $form->{10}{$key} * 100
1711 $form->{decimalplaces}
1713 $form->{decimalplaces},
1718 if ($form->{4}{$key} > 0) {
1719 foreach $category (@categories) {
1720 if (defined($form->{$category}{$key})) {
1721 $form->{ "$key" . "auf" . "$category" } =
1722 $form->format_amount(
1724 $form->round_amount(
1725 ($form->{$category}{$key} / $form->{4}{$key} * 100),
1726 $form->{decimalplaces}
1728 $form->{decimalplaces},
1732 foreach my $item (@ergebnisse) {
1733 $form->{ "$key" . "auf" . "$item" } =
1734 $form->format_amount($myconfig,
1735 $form->round_amount(
1736 ($form->{ "$key" . "$item" } /
1737 $form->{4}{$key} * 100
1739 $form->{decimalplaces}
1741 $form->{decimalplaces},
1746 foreach my $item (@ergebnisse) {
1747 $form->{ "$key" . "$item" } =
1748 $form->format_amount($myconfig,
1749 $form->round_amount($form->{ "$key" . "$item" },
1750 $form->{decimalplaces}
1752 $form->{decimalplaces},
1759 $main::lxdebug->leave_sub();
1762 sub income_statement {
1763 $main::lxdebug->enter_sub();
1765 my ($self, $myconfig, $form) = @_;
1767 # connect to database
1768 my $dbh = $form->dbconnect($myconfig);
1770 my $last_period = 0;
1771 my @categories_einnahmen = qw(1 2 3 4 5 6 7);
1772 my @categories_ausgaben =
1773 qw(8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31);
1775 my @ergebnisse = qw(sumeura sumeurb guvsumme);
1777 $form->{decimalplaces} *= 1;
1781 &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
1785 foreach my $item (@categories_einnahmen) {
1786 $form->{"eur${item}"} =
1787 $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
1788 $form->{"sumeura"} += $form->{$item};
1790 foreach my $item (@categories_ausgaben) {
1791 $form->{"eur${item}"} =
1792 $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
1793 $form->{"sumeurb"} += $form->{$item};
1796 $form->{"guvsumme"} = $form->{"sumeura"} - $form->{"sumeurb"};
1798 foreach my $item (@ergebnisse) {
1800 $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
1802 $main::lxdebug->leave_sub();