remove unnecessary spaces
[kivitendo-erp.git] / SL / RP.pm
1 #=====================================================================
2 # LX-Office ERP
3 # Copyright (C) 2004
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
6 #
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
10 #
11 #  Author: Dieter Simader
12 #   Email: dsimader@sql-ledger.org
13 #     Web: http://www.sql-ledger.org
14 #
15 #  Contributors: Benjamin Lee <benjaminlee@consultant.com>
16 #
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.
21 #
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., 51 Franklin Street, Fifth Floor, Boston,
29 # MA 02110-1335, USA.
30 #======================================================================
31 #
32 # backend code for reports
33 #
34 #======================================================================
35
36 package RP;
37
38 use SL::DBUtils;
39 use Data::Dumper;
40 use SL::DB::Helper::AccountingPeriod qw(get_balance_starting_date);
41 use List::Util qw(sum);
42 use List::UtilsBy qw(partition_by sort_by);
43 use SL::DB;
44
45 # use warnings;
46 use strict;
47
48 # new implementation of balance sheet
49 # readme!
50 #
51 # stuff missing from the original implementation:
52 # - bold stuff
53 # - subdescription
54 # - proper testing for heading charts
55 # - transmission from $form to TMPL realm is not as clear as i'd like
56
57 sub balance_sheet {
58   $main::lxdebug->enter_sub();
59
60   my ($self) = @_;
61
62   my $myconfig = \%main::myconfig;
63   my $form     = $main::form;
64   my $dbh      = $::form->get_standard_dbh;
65
66   my $last_period = 0;
67   my @categories  = qw(A C L Q);
68
69   # if there are any dates construct a where
70   if ($form->{asofdate}) {
71     $form->{period} = $form->{this_period} = conv_dateq($form->{asofdate});
72   }
73
74   # get starting date for calculating balance
75   $form->{this_startdate} = $self->get_balance_starting_date($form->{asofdate});
76
77   get_accounts($dbh, $last_period, $form->{this_startdate}, $form->{asofdate}, $form, \@categories);
78
79   # if there are any compare dates
80   if ($form->{compareasofdate}) {
81     $last_period = 1;
82
83     $form->{last_startdate} = $self->get_balance_starting_date($form->{compareasofdate});
84
85     get_accounts($dbh, $last_period, $form->{last_startdate} , $form->{compareasofdate}, $form, \@categories);
86     $form->{last_period} = conv_dateq($form->{compareasofdate});
87   }
88
89   # now we got $form->{A}{accno}{ }    assets
90   # and $form->{L}{accno}{ }           liabilities
91   # and $form->{Q}{accno}{ }           equity
92   # build asset accounts
93
94   my %account = ('A' => { 'ml'     => -1 },
95                  'L' => { 'ml'     =>  1 },
96                  'Q' => { 'ml'     =>  1 });
97
98   my $TMPL_DATA = {};
99
100   foreach my $category (grep { !/C/ } @categories) {
101
102     $TMPL_DATA->{$category} = [];
103     my $ml  = $account{$category}{ml};
104
105     foreach my $key (sort keys %{ $form->{$category} }) {
106
107       my $row = { %{ $form->{$category}{$key} } };
108
109       # if charttype "heading" - calculate this entry, start a new batch of charts belonging to this heading and skip the rest bo the loop
110       # header charts are not real charts. start a sub aggregation with them, but don't calculate anything with them
111       if ($row->{charttype} eq "H") {
112         if ($account{$category}{subtotal} && $form->{l_subtotal}) {
113           $row->{subdescription} = $account{$category}{subdescription};
114           $row->{this}           = $account{$category}{subthis} * $ml;                   # format: $dec, $dash
115           $row->{last}           = $account{$category}{sublast} * $ml if $last_period;   # format: $dec, $dash
116         }
117
118         $row->{subheader} = 1;
119         $account{$category}{subthis}        = $row->{this};
120         $account{$category}{sublast}        = $row->{last};
121         $account{$category}{subdescription} = $row->{description};
122         $account{$category}{subtotal} = 1;
123
124         $row->{this} = 0;
125         $row->{last} = 0;
126
127         next unless $form->{l_heading};
128       }
129
130       for my $period (qw(this last)) {
131         next if ($period eq 'last' && !$last_period);
132         # only add assets
133         $row->{$period}                    *= $ml;
134       }
135
136       push @{ $TMPL_DATA->{$category} }, $row;
137     } # foreach
138
139     # resolve heading/subtotal
140     if ($account{$category}{subtotal} && $form->{l_subtotal}) {
141       $TMPL_DATA->{$category}[-1]{subdescription} = $account{$category}{subdescription};
142       $TMPL_DATA->{$category}[-1]{this}           = $account{$category}{subthis} * $ml;                   # format: $dec, $dash
143       $TMPL_DATA->{$category}[-1]{last}           = $account{$category}{sublast} * $ml if $last_period;   # format: $dec, $dash
144     }
145
146     $TMPL_DATA->{total}{$category}{this} = sum map { $_->{this} } @{ $TMPL_DATA->{$category} };
147     $TMPL_DATA->{total}{$category}{last} = sum map { $_->{last} } @{ $TMPL_DATA->{$category} };
148   }
149
150   for my $period (qw(this last)) {
151     next if ($period eq 'last' && !$last_period);
152
153     $form->{E}{$period}             = $TMPL_DATA->{total}{A}{$period} - $TMPL_DATA->{total}{L}{$period} - $TMPL_DATA->{total}{Q}{$period};
154     $TMPL_DATA->{total}{Q}{$period}     += $form->{E}{$period};
155     $TMPL_DATA->{total}{$period}    = $TMPL_DATA->{total}{L}{$period} + $TMPL_DATA->{total}{Q}{$period};
156   }
157     $form->{E}{description}='nicht verbuchter Gewinn/Verlust';
158   push @{ $TMPL_DATA->{Q} }, $form->{E};
159
160   $main::lxdebug->leave_sub();
161
162   return $TMPL_DATA;
163 }
164
165 sub get_accounts {
166   $main::lxdebug->enter_sub();
167
168   my ($dbh, $last_period, $fromdate, $todate, $form, $categories) = @_;
169
170   my ($null, $department_id) = split /--/, $form->{department};
171
172   my $query;
173   my $dpt_where = '';
174   my $dpt_where_without_arapgl = '';
175   my $project   = '';
176   my $where     = "1 = 1";
177   my $glwhere   = "";
178   my $subwhere  = "";
179   my $item;
180   my $sth;
181   my $dec = $form->{decimalplaces};
182
183   my $category = qq| AND (| . join(" OR ", map({ "(c.category = " . $dbh->quote($_) . ")" } @{$categories})) . qq|) |;
184
185   # get headings
186   $query =
187     qq|SELECT c.accno, c.description, c.category
188        FROM chart c
189        WHERE (c.charttype = 'H')
190          $category
191        ORDER by c.accno|;
192
193   $sth = prepare_execute_query($form, $dbh, $query);
194
195   my @headingaccounts = ();
196   while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
197     $form->{ $ref->{category} }{ $ref->{accno} }{description} =
198       "$ref->{description}";
199     $form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "H";
200     $form->{ $ref->{category} }{ $ref->{accno} }{accno}     = $ref->{accno};
201
202     push @headingaccounts, $ref->{accno};
203   }
204
205   $sth->finish;
206
207   # filter for opening and closing bookings
208   # if l_ob is selected l_cb is always ignored
209   if ( $last_period ) {
210     # ob/cb-settings for "compared to" balance
211     if ( $form->{l_ob_compared} ) {
212       $where .= ' AND ac.ob_transaction is true  '
213     } elsif ( not $form->{l_cb_compared} ) {
214       $where .= ' AND ac.cb_transaction is false ';
215     };
216   } else {
217     # ob/cb-settings for "as of" balance
218     if ( $form->{l_ob} ) {
219       $where .= ' AND ac.ob_transaction is true  '
220     } elsif ( not $form->{l_cb} ) {
221       $where .= ' AND ac.cb_transaction is false ';
222     };
223   };
224
225
226   if ($fromdate) {
227     $fromdate = conv_dateq($fromdate);
228     if ($form->{method} eq 'cash') {
229       $subwhere .= " AND (transdate >= $fromdate)";
230       $glwhere = " AND (ac.transdate >= $fromdate)";
231     } else {
232       $where .= " AND (ac.transdate >= $fromdate)";
233     }
234   }
235
236   if ($todate) {
237     $todate = conv_dateq($todate);
238     $where    .= " AND (ac.transdate <= $todate)";
239     $subwhere .= " AND (transdate <= $todate)";
240   }
241
242   if ($department_id) {
243     $dpt_where = qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|)|;
244   }
245
246   if ($form->{project_id}) {
247     # Diese Bedingung wird derzeit niemals wahr sein, da man in Bericht->Bilanz keine
248     # Projekte auswählen kann
249     $project = qq| AND (ac.project_id = | . conv_i($form->{project_id}, 'NULL') . qq|) |;
250   }
251
252   if ($form->{method} eq 'cash') {
253     $query =
254       qq|SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category
255          FROM acc_trans ac
256          JOIN chart c ON (c.id = ac.chart_id)
257          JOIN ar a ON (a.id = ac.trans_id)
258          WHERE $where
259            $dpt_where
260            $category
261            AND ac.trans_id IN
262              (
263                SELECT trans_id
264                FROM acc_trans a
265                WHERE (a.chart_link LIKE '%AR_paid%')
266                $subwhere
267              )
268            $project
269          GROUP BY c.accno, c.description, c.category
270
271          UNION ALL
272
273          SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category
274          FROM acc_trans ac
275          JOIN chart c ON (c.id = ac.chart_id)
276          JOIN ap a ON (a.id = ac.trans_id)
277          WHERE $where
278            $dpt_where
279            $category
280            AND ac.trans_id IN
281              (
282                SELECT trans_id
283                FROM acc_trans a
284                WHERE (a.chart_link LIKE '%AP_paid%')
285                $subwhere
286              )
287            $project
288          GROUP BY c.accno, c.description, c.category
289
290          UNION ALL
291
292          SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category
293          FROM acc_trans ac
294          JOIN chart c ON (c.id = ac.chart_id)
295          JOIN gl a ON (a.id = ac.trans_id)
296          WHERE $where
297            $glwhere
298            $dpt_where
299            $category
300              AND NOT ((ac.chart_link = 'AR') OR (ac.chart_link = 'AP'))
301            $project
302          GROUP BY c.accno, c.description, c.category |;
303
304     if ($form->{project_id}) {
305       # s.o. keine Projektauswahl in Bilanz
306       $query .=
307         qq|
308          UNION ALL
309
310          SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, c.description AS description, c.category
311          FROM invoice ac
312          JOIN ar a ON (a.id = ac.trans_id)
313          JOIN parts p ON (ac.parts_id = p.id)
314          JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
315          JOIN chart c on (t.income_accno_id = c.id)
316          -- use transdate from subwhere
317          WHERE (c.category = 'I')
318            $subwhere
319            $dpt_where
320            AND ac.trans_id IN
321              (
322                SELECT trans_id
323                FROM acc_trans a
324                WHERE (a.chart_link LIKE '%AR_paid%')
325                $subwhere
326              )
327            $project
328          GROUP BY c.accno, c.description, c.category
329
330          UNION ALL
331
332          SELECT c.accno AS accno, SUM(ac.sellprice) AS amount, c.description AS description, c.category
333          FROM invoice ac
334          JOIN ap a ON (a.id = ac.trans_id)
335          JOIN parts p ON (ac.parts_id = p.id)
336          JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
337          JOIN chart c on (t.expense_accno_id = c.id)
338          WHERE (c.category = 'E')
339            $subwhere
340            $dpt_where
341            AND ac.trans_id IN
342              (
343                SELECT trans_id
344                FROM acc_trans a
345                WHERE a.chart_link LIKE '%AP_paid%'
346                $subwhere
347              )
348            $project
349          GROUP BY c.accno, c.description, c.category |;
350     }
351
352   } else {                      # if ($form->{method} eq 'cash')
353     if ($department_id) {
354       $dpt_where = qq| AND a.department_id = | . conv_i($department_id);
355       $dpt_where_without_arapgl = qq| AND COALESCE((SELECT department_id FROM ar WHERE ar.id=ac.trans_id),
356                                                    (SELECT department_id FROM gl WHERE gl.id=ac.trans_id),
357                                                    (SELECT department_id FROM ap WHERE ap.id=ac.trans_id)) = | . conv_i($department_id);
358     }
359
360     $query = qq|
361       SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category
362       FROM acc_trans ac
363       JOIN chart c ON (c.id = ac.chart_id)
364       WHERE $where
365         $dpt_where_without_arapgl
366         $category
367         $project
368       GROUP BY c.accno, c.description, c.category |;
369
370     if ($form->{project_id}) {
371       # s.o. keine Projektauswahl in Bilanz
372       $query .= qq|
373       UNION ALL
374
375       SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, c.description AS description, c.category
376       FROM invoice ac
377       JOIN ar a ON (a.id = ac.trans_id)
378       JOIN parts p ON (ac.parts_id = p.id)
379       JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
380       JOIN chart c on (t.income_accno_id = c.id)
381       -- use transdate from subwhere
382       WHERE (c.category = 'I')
383         $subwhere
384         $dpt_where
385         $project
386       GROUP BY c.accno, c.description, c.category
387
388       UNION ALL
389
390       SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount, c.description AS description, c.category
391       FROM invoice ac
392       JOIN ap a ON (a.id = ac.trans_id)
393       JOIN parts p ON (ac.parts_id = p.id)
394       JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
395       JOIN chart c on (t.expense_accno_id = c.id)
396       WHERE (c.category = 'E')
397         $subwhere
398         $dpt_where
399         $project
400       GROUP BY c.accno, c.description, c.category |;
401     }
402   }
403
404   my @accno;
405   my $accno;
406   my $ref;
407
408   $sth = prepare_execute_query($form, $dbh, $query);
409
410   while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
411
412     if ($ref->{category} eq 'C') {
413       $ref->{category} = 'A';
414     }
415
416     # get last heading account
417     @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
418     $accno = pop @accno;
419     if ($accno) {
420       if ($last_period) {
421         $form->{ $ref->{category} }{$accno}{last} += $ref->{amount};
422       } else {
423         $form->{ $ref->{category} }{$accno}{this} += $ref->{amount};
424       }
425     }
426
427     $form->{ $ref->{category} }{ $ref->{accno} }{accno}       = $ref->{accno};
428     $form->{ $ref->{category} }{ $ref->{accno} }{description} = $ref->{description};
429     $form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "A";
430
431     if ($last_period) {
432       $form->{ $ref->{category} }{ $ref->{accno} }{last} += $ref->{amount};
433     } else {
434       $form->{ $ref->{category} }{ $ref->{accno} }{this} += $ref->{amount};
435     }
436   }
437   $sth->finish;
438
439   # remove accounts with zero balance
440   foreach $category (@{$categories}) {
441     foreach $accno (keys %{ $form->{$category} }) {
442       $form->{$category}{$accno}{last} = $form->round_amount($form->{$category}{$accno}{last}, $dec);
443       $form->{$category}{$accno}{this} = $form->round_amount($form->{$category}{$accno}{this}, $dec);
444
445       delete $form->{$category}{$accno}
446         if (   $form->{$category}{$accno}{this} == 0
447             && $form->{$category}{$accno}{last} == 0);
448     }
449   }
450
451   $main::lxdebug->leave_sub();
452 }
453
454 sub get_accounts_g {
455   $main::lxdebug->enter_sub();
456
457   my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_;
458
459   my ($null, $department_id) = split /--/, $form->{department};
460
461   my $query;
462   my $dpt_where;
463   my $dpt_where_without_arapgl;
464   my $project;
465   my $where    = "1 = 1";
466   my $glwhere  = "";
467   my $prwhere  = "";
468   my $subwhere = "";
469   my $inwhere = "";
470   my $item;
471
472   $where .= ' AND ac.cb_transaction is false ' unless $form->{l_cb};
473
474   if ($fromdate) {
475     $fromdate = conv_dateq($fromdate);
476     if ($form->{method} eq 'cash') {
477       $subwhere .= " AND (transdate    >= $fromdate)";
478       $glwhere   = " AND (ac.transdate >= $fromdate)";
479       $prwhere   = " AND (a.transdate  >= $fromdate)";
480       $inwhere   = " AND (acc.transdate >= $fromdate)";
481     } else {
482       $where    .= " AND (ac.transdate >= $fromdate)";
483       # hotfix for projectfilter in guv and bwa
484       # fromdate is otherwise ignored if project is selected
485       $prwhere   = " AND (a.transdate  >= $fromdate)";
486     }
487   }
488
489   if ($todate) {
490     $todate = conv_dateq($todate);
491     $subwhere   .= " AND (transdate    <= $todate)";
492     $where      .= " AND (ac.transdate <= $todate)";
493     $prwhere    .= " AND (a.transdate  <= $todate)";
494     $inwhere    .= " AND (acc.transdate <= $todate)";
495   }
496
497   if ($department_id) {
498     $dpt_where = qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
499   }
500
501   if ($form->{project_id}) {
502     $project = qq| AND (ac.project_id = | . conv_i($form->{project_id}) . qq|) |;
503   }
504
505 #
506 # GUV patch by Ronny Rentner (Bug 1190)
507 #
508 # GUV IST-Versteuerung
509 #
510 # Alle tatsaechlichen _Zahlungseingaenge_
511 # im Zeitraum erfassen
512 # (Teilzahlungen werden prozentual auf verschiedene Steuern aufgeteilt)
513 #
514 #
515
516   if ($form->{method} eq 'cash') {
517     $query =
518       qq|
519        SELECT SUM( ac.amount * CASE WHEN COALESCE((SELECT amount FROM ar a WHERE id = ac.trans_id $dpt_where), 0) != 0 THEN
520             /* ar amount is not zero, so we can divide by amount   */
521                     (SELECT SUM(acc.amount) * -1
522                      FROM acc_trans acc
523                      WHERE 1=1 $inwhere
524                      AND acc.trans_id = ac.trans_id
525                      AND acc.chart_link LIKE '%AR_paid%')
526                   / (SELECT amount FROM ar WHERE id = ac.trans_id)
527             ELSE 0
528             /* 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 */
529             END
530                 ) AS amount, c.$category, c.accno, c.description
531        FROM acc_trans ac
532        LEFT JOIN chart c ON (c.id  = ac.chart_id)
533        LEFT JOIN ar      ON (ar.id = ac.trans_id)
534       WHERE ac.trans_id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE 1=1 $subwhere)
535
536       GROUP BY c.$category, c.accno, c.description
537
538 /*
539        SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
540          FROM acc_trans ac
541          JOIN chart c ON (c.id = ac.chart_id)
542          JOIN ar a ON (a.id = ac.trans_id)
543          WHERE $where $dpt_where
544            AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AR_paid%') $subwhere)
545            $project
546          GROUP BY c.$category, c.accno, c.description
547 */
548          UNION
549
550          SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
551          FROM acc_trans ac
552          JOIN chart c ON (c.id = ac.chart_id)
553          JOIN ap a ON (a.id = ac.trans_id)
554          WHERE $where $dpt_where
555            AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AP_paid%') $subwhere)
556            $project
557          GROUP BY c.$category, c.accno, c.description
558
559          UNION
560
561          SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
562          FROM acc_trans ac
563          JOIN chart c ON (c.id = ac.chart_id)
564          JOIN gl a ON (a.id = ac.trans_id)
565          WHERE $where $dpt_where $glwhere
566            AND NOT ((ac.chart_link = 'AR') OR (ac.chart_link = 'AP'))
567            $project
568          GROUP BY c.$category, c.accno, c.description
569         |;
570
571     if ($form->{project_id}) {
572       $query .= qq|
573          UNION
574
575          SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
576          FROM invoice ac
577          JOIN ar a ON (a.id = ac.trans_id)
578          JOIN parts p ON (ac.parts_id = p.id)
579          JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
580          JOIN chart c on (t.income_accno_id = c.id)
581          WHERE (c.category = 'I') $prwhere $dpt_where
582            AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AR_paid%') $subwhere)
583            $project
584          GROUP BY c.$category, c.accno, c.description
585
586          UNION
587
588          SELECT SUM(ac.sellprice * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
589          FROM invoice ac
590          JOIN ap a ON (a.id = ac.trans_id)
591          JOIN parts p ON (ac.parts_id = p.id)
592          JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
593          JOIN chart c on (t.expense_accno_id = c.id)
594          WHERE (c.category = 'E') $prwhere $dpt_where
595            AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AP_paid%') $subwhere)
596          $project
597          GROUP BY c.$category, c.accno, c.description
598          |;
599     }
600
601   } else {                      # if ($form->{method} eq 'cash')
602     if ($department_id) {
603       $dpt_where = qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
604       $dpt_where_without_arapgl = qq| AND COALESCE((SELECT department_id FROM ar WHERE ar.id=ac.trans_id),
605                                                    (SELECT department_id FROM gl WHERE gl.id=ac.trans_id),
606                                                    (SELECT department_id FROM ap WHERE ap.id=ac.trans_id)) = | . conv_i($department_id);
607     }
608
609     $query = qq|
610         SELECT sum(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
611         FROM acc_trans ac
612         JOIN chart c ON (c.id = ac.chart_id)
613         WHERE $where
614           $dpt_where_without_arapgl
615           $project
616         GROUP BY c.$category, c.accno, c.description |;
617
618     if ($form->{project_id}) {
619       $query .= qq|
620         UNION
621
622         SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
623         FROM invoice ac
624         JOIN ar a ON (a.id = ac.trans_id)
625         JOIN parts p ON (ac.parts_id = p.id)
626         JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
627         JOIN chart c on (t.income_accno_id = c.id)
628         WHERE (c.category = 'I')
629           $prwhere
630           $dpt_where
631           $project
632         GROUP BY c.$category, c.accno, c.description
633
634         UNION
635
636         SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
637         FROM invoice ac
638         JOIN ap a ON (a.id = ac.trans_id)
639         JOIN parts p ON (ac.parts_id = p.id)
640         JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
641         JOIN chart c on (t.expense_accno_id = c.id)
642         WHERE (c.category = 'E')
643           $prwhere
644           $dpt_where
645           $project
646         GROUP BY c.$category, c.accno, c.description |;
647     }
648   }
649
650   my @accno;
651   my $accno;
652   my $ref;
653
654   # store information for chart list in $form->{charts}
655   foreach my $ref (selectall_hashref_query($form, $dbh, $query)) {
656     unless ( defined $form->{charts}->{$ref->{accno}}  ) {
657       # a chart may appear several times in the resulting hashref, init it the first time
658       $form->{charts}->{$ref->{accno}} = { amount      => 0,
659                                            "$category" => $ref->{"$category"},
660                                            accno       => $ref->{accno},
661                                            description => $ref->{description},
662                                          };
663     }
664     if ($category eq "pos_bwa") {
665       if ($last_period) {
666         $form->{ $ref->{$category} }{kumm} += $ref->{amount};
667       } else {
668         $form->{ $ref->{$category} }{jetzt} += $ref->{amount};
669         # only increase chart amount for current period, not last_period
670         $form->{charts}->{$ref->{accno}}->{amount} +=  $ref->{amount},
671       }
672     } else {
673       $form->{ $ref->{$category} } += $ref->{amount};
674       $form->{charts}->{$ref->{accno}}->{amount} +=  $ref->{amount}; # no last_period for eur
675     }
676   }
677
678   $main::lxdebug->leave_sub();
679 }
680
681 sub trial_balance {
682   $main::lxdebug->enter_sub();
683
684   my ($self, $myconfig, $form, %options) = @_;
685
686   my $dbh = SL::DB->client->dbh;
687
688   my ($query, $sth, $ref);
689   my %balance = ();
690   my %trb     = ();
691   my ($null, $department_id) = split /--/, $form->{department};
692   my @headingaccounts = ();
693   my $dpt_where;
694   my $dpt_where_without_arapgl;
695   my ($customer_where, $customer_join, $customer_no_union);
696   my $project;
697
698   my $where    = "1 = 1";
699   my $invwhere = $where;
700
701   if ($department_id) {
702     $dpt_where = qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
703     $dpt_where_without_arapgl = qq| AND COALESCE((SELECT department_id FROM ar WHERE ar.id=ac.trans_id),
704                                                  (SELECT department_id FROM gl WHERE gl.id=ac.trans_id),
705                                                  (SELECT department_id FROM ap WHERE ap.id=ac.trans_id)) = | . conv_i($department_id);
706   }
707   if ($form->{customer_id}) {
708     $customer_join     = qq| JOIN ar a ON (ac.trans_id = a.id) |;
709     $customer_where    = qq| AND (a.customer_id = | . conv_i($form->{customer_id}, 'NULL') . qq|) |;
710     $customer_no_union = qq| AND 1=0 |;
711   }
712
713   # project_id only applies to getting transactions
714   # it has nothing to do with a trial balance
715   # but we use the same function to collect information
716
717   if ($form->{project_id}) {
718     $project = qq| AND (ac.project_id = | . conv_i($form->{project_id}, 'NULL') . qq|) |;
719   }
720
721   my $acc_cash_where = "";
722 #  my $ar_cash_where = "";
723 #  my $ap_cash_where = "";
724
725
726   if ($form->{method} eq "cash") {
727     $acc_cash_where =
728       qq| AND (ac.trans_id IN (
729             SELECT id
730             FROM ar
731             WHERE datepaid >= '$form->{fromdate}'
732               AND datepaid <= '$form->{todate}'
733
734             UNION
735
736             SELECT id
737             FROM ap
738             WHERE datepaid >= '$form->{fromdate}'
739               AND datepaid <= '$form->{todate}'
740
741             UNION
742
743             SELECT id
744             FROM gl
745             WHERE transdate >= '$form->{fromdate}'
746               AND transdate <= '$form->{todate}'
747           )) |;
748 #    $ar_ap_cash_where = qq| AND (a.datepaid>='$form->{fromdate}' AND a.datepaid<='$form->{todate}') |;
749   }
750
751   if ($options{beginning_balances}) {
752     foreach my $prefix (qw(from to)) {
753       next if ($form->{"${prefix}date"});
754
755       my $min_max = $prefix eq 'from' ? 'min' : 'max';
756       $query      = qq|SELECT ${min_max}(transdate)
757                        FROM acc_trans ac
758                        $customer_join
759                        WHERE (1 = 1)
760                          $dpt_where_without_arapgl
761                          $dpt_where
762                          $customer_where
763                          $project|;
764       ($form->{"${prefix}date"}) = selectfirst_array_query($form, $dbh, $query);
765     }
766
767     # get beginning balances
768     $query =
769       qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount, c.description
770           FROM acc_trans ac
771           LEFT JOIN chart c ON (ac.chart_id = c.id)
772           $customer_join
773           WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction
774             $dpt_where_without_arapgl
775             $customer_where
776             $project
777           GROUP BY c.accno, c.category, c.description |;
778
779     $sth = prepare_execute_query($form, $dbh, $query, $form->{fromdate});
780
781     while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
782
783       if ($ref->{amount} != 0 || $form->{all_accounts}) {
784         $trb{ $ref->{accno} }{description} = $ref->{description};
785         $trb{ $ref->{accno} }{charttype}   = 'A';
786         $trb{ $ref->{accno} }{beginning_balance} = $ref->{amount};
787
788         if ($ref->{amount} > 0) {
789           $trb{ $ref->{accno} }{haben_eb}   = $ref->{amount};
790         } else {
791           $trb{ $ref->{accno} }{soll_eb}   = $ref->{amount} * -1;
792         }
793         $trb{ $ref->{accno} }{category}    = $ref->{category};
794       }
795
796     }
797     $sth->finish;
798   }
799
800   # get headings
801   $query =
802     qq|SELECT c.accno, c.description, c.category
803        FROM chart c
804        WHERE c.charttype = 'H'
805        ORDER by c.accno|;
806
807   $sth = prepare_execute_query($form, $dbh, $query);
808
809   while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
810     $trb{ $ref->{accno} }{description} = $ref->{description};
811     $trb{ $ref->{accno} }{charttype}   = 'H';
812     $trb{ $ref->{accno} }{category}    = $ref->{category};
813
814     push @headingaccounts, $ref->{accno};
815   }
816
817   $sth->finish;
818
819   $where = " 1 = 1 ";
820   my $saldowhere    = " 1 = 1 ";
821   my $sumwhere      = " 1 = 1 ";
822   my $subwhere      = '';
823   my $sumsubwhere   = '';
824   my $saldosubwhere = '';
825   my $glsaldowhere  = '';
826   my $glsubwhere    = '';
827   my $glwhere       = '';
828   my $glsumwhere    = '';
829   my $tofrom;
830   my ($fromdate, $todate, $fetch_accounts_before_from);
831
832   if ($form->{fromdate} || $form->{todate}) {
833     if ($form->{fromdate}) {
834       $fromdate = conv_dateq($form->{fromdate});
835       $tofrom        .= " AND (ac.transdate >= $fromdate)";
836       $subwhere      .= " AND (ac.transdate >= $fromdate)";
837       $sumsubwhere   .= " AND (ac.transdate >= (select date_trunc('year', date $fromdate))) ";
838       $saldosubwhere .= " AND (ac,transdate>=(select date_trunc('year', date $fromdate)))  ";
839       $invwhere      .= " AND (a.transdate >= $fromdate)";
840       $glsaldowhere  .= " AND ac.transdate>=(select date_trunc('year', date $fromdate)) ";
841       $glwhere        = " AND (ac.transdate >= $fromdate)";
842       $glsumwhere     = " AND (ac.transdate >= (select date_trunc('year', date $fromdate))) ";
843     }
844     if ($form->{todate}) {
845       $todate = conv_dateq($form->{todate});
846       $tofrom        .= " AND (ac.transdate <= $todate)";
847       $invwhere      .= " AND (a.transdate <= $todate)";
848       $saldosubwhere .= " AND (ac.transdate <= $todate)";
849       $sumsubwhere   .= " AND (ac.transdate <= $todate)";
850       $subwhere      .= " AND (ac.transdate <= $todate)";
851       $glwhere       .= " AND (ac.transdate <= $todate)";
852       $glsumwhere    .= " AND (ac.transdate <= $todate) ";
853       $glsaldowhere  .= " AND (ac.transdate <= $todate) ";
854    }
855   }
856
857   if ($form->{method} eq "cash") {
858     $where .=
859       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) |;
860     $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) |;
861
862     $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) |;
863   } else {
864     $where .= $tofrom . " AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL)";
865     $saldowhere .= $glsaldowhere . " AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL)";
866     $sumwhere .= $glsumwhere . " AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL)";
867
868     # get all entries before fromdate, which are not yet fetched
869     # TODO dpt_where_without_arapgl and project - project calculation seems bogus anyway
870     # TODO use fiscal_year_startdate for the whole trial balance
871     #      anyway, if the last booking is in a deviating fiscal year, this already improves the query
872     my $fiscal_year_startdate = conv_dateq($self->get_balance_starting_date($form->{fromdate}));
873     $fetch_accounts_before_from = qq|SELECT c.accno, c.description, c.category, SUM(ac.amount) AS amount
874                        FROM acc_trans ac JOIN chart c ON (c.id = ac.chart_id) WHERE 1 = 1 AND (ac.transdate <= $fromdate)
875                        AND (ac.transdate >= $fiscal_year_startdate)
876                        AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL)
877                        AND c.accno NOT IN (SELECT c.accno FROM acc_trans ac JOIN chart c ON (c.id = ac.chart_id) WHERE 1 = 1 AND (ac.transdate >= $fromdate) AND (ac.transdate <= $todate)
878                        AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL))
879                        GROUP BY c.accno, c.description, c.category ORDER BY accno|;
880   }
881
882   $query = qq|
883        SELECT c.accno, c.description, c.category, SUM(ac.amount) AS amount
884        FROM acc_trans ac
885        JOIN chart c ON (c.id = ac.chart_id)
886        $customer_join
887        WHERE $where
888          $dpt_where_without_arapgl
889          $project
890        GROUP BY c.accno, c.description, c.category |;
891
892   if ($form->{project_id}) {
893     $query .= qq|
894       -- add project transactions from invoice
895
896       UNION ALL
897
898       SELECT c.accno, c.description, c.category, SUM(ac.sellprice * ac.qty) AS amount
899       FROM invoice ac
900       JOIN ar a ON (ac.trans_id = a.id)
901       JOIN parts p ON (ac.parts_id = p.id)
902       JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
903       JOIN chart c ON (t.income_accno_id = c.id)
904       WHERE $invwhere
905         $dpt_where
906         $customer_where
907         $project
908       GROUP BY c.accno, c.description, c.category
909
910       UNION ALL
911
912       SELECT c.accno, c.description, c.category, SUM(ac.sellprice * ac.qty) * -1 AS amount
913       FROM invoice ac
914       JOIN ap a ON (ac.trans_id = a.id)
915       JOIN parts p ON (ac.parts_id = p.id)
916       JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
917       JOIN chart c ON (t.expense_accno_id = c.id)
918       WHERE $invwhere
919         $dpt_where
920         $customer_no_union
921         $project
922       GROUP BY c.accno, c.description, c.category
923       |;
924     }
925
926   $query .= qq| ORDER BY accno|;
927
928   $sth = prepare_execute_query($form, $dbh, $query);
929
930   # calculate the debit and credit in the period
931   while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
932     $trb{ $ref->{accno} }{description} = $ref->{description};
933     $trb{ $ref->{accno} }{charttype}   = 'A';
934     $trb{ $ref->{accno} }{category}    = $ref->{category};
935     $trb{ $ref->{accno} }{amount} += $ref->{amount};
936   }
937   $sth->finish;
938
939   if ($form->{method} ne "cash") {  # better eq 'accrual'
940     $sth = prepare_execute_query($form, $dbh, $fetch_accounts_before_from);
941     while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
942       $trb{ $ref->{accno} }{description} = $ref->{description};
943       $trb{ $ref->{accno} }{charttype}   = 'A';
944       $trb{ $ref->{accno} }{category}    = $ref->{category};
945       $trb{ $ref->{accno} }{amount} += $ref->{amount};
946     }
947     $sth->finish;
948   }
949
950   # prepare query for each account
951   my ($q_drcr, $drcr, $q_project_drcr, $project_drcr);
952
953   $q_drcr =
954     qq|SELECT
955          (SELECT SUM(ac.amount) * -1
956           FROM acc_trans ac
957           JOIN chart c ON (c.id = ac.chart_id)
958           $customer_join
959           WHERE $where
960             $dpt_where_without_arapgl
961             $customer_where
962             $project
963           AND (ac.amount < 0)
964           AND (c.accno = ?)) AS debit,
965
966          (SELECT SUM(ac.amount)
967           FROM acc_trans ac
968           JOIN chart c ON (c.id = ac.chart_id)
969           $customer_join
970           WHERE $where
971             $dpt_where_without_arapgl
972             $customer_where
973             $project
974           AND ac.amount > 0
975           AND c.accno = ?) AS credit,
976         (SELECT SUM(ac.amount)
977          FROM acc_trans ac
978          JOIN chart c ON (ac.chart_id = c.id)
979          $customer_join
980          WHERE $saldowhere
981            $dpt_where_without_arapgl
982            $customer_where
983            $project
984          AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo,
985
986         (SELECT SUM(ac.amount)
987          FROM acc_trans ac
988          JOIN chart c ON (ac.chart_id = c.id)
989          $customer_join
990          WHERE $sumwhere
991            $dpt_where_without_arapgl
992            $customer_where
993            $project
994          AND ac.amount > 0
995          AND c.accno = ?) AS sum_credit,
996
997         (SELECT SUM(ac.amount)
998          FROM acc_trans ac
999          JOIN chart c ON (ac.chart_id = c.id)
1000          $customer_join
1001          WHERE $sumwhere
1002            $dpt_where_without_arapgl
1003            $customer_where
1004            $project
1005          AND ac.amount < 0
1006          AND c.accno = ?) AS sum_debit,
1007
1008         (SELECT max(ac.transdate) FROM acc_trans ac
1009         JOIN chart c ON (ac.chart_id = c.id)
1010         $customer_join
1011         WHERE $where
1012           $dpt_where_without_arapgl
1013           $customer_where
1014           $project
1015         AND c.accno = ?) AS last_transaction
1016
1017
1018  |;
1019
1020   $drcr = prepare_query($form, $dbh, $q_drcr);
1021
1022   if ($form->{project_id}) {
1023     # prepare query for each account
1024     $q_project_drcr =
1025       qq|SELECT
1026           (SELECT SUM(ac.sellprice * ac.qty) * -1
1027            FROM invoice ac
1028            JOIN parts p ON (ac.parts_id = p.id)
1029            JOIN ap a ON (ac.trans_id = a.id)
1030            JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
1031            JOIN chart c ON (t.expense_accno_id = c.id)
1032            WHERE $invwhere
1033              $dpt_where
1034              $customer_no_union
1035              $project
1036            AND c.accno = ?) AS debit,
1037
1038           (SELECT SUM(ac.sellprice * ac.qty)
1039            FROM invoice ac
1040            JOIN parts p ON (ac.parts_id = p.id)
1041            JOIN ar a ON (ac.trans_id = a.id)
1042            JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
1043            JOIN chart c ON (t.income_accno_id = c.id)
1044            WHERE $invwhere
1045              $dpt_where
1046              $customer_where
1047              $project
1048            AND c.accno = ?) AS credit,
1049
1050         (SELECT SUM(ac.amount)
1051          FROM acc_trans ac
1052          JOIN chart c ON (ac.chart_id = c.id)
1053          $customer_join
1054          WHERE $saldowhere
1055            $dpt_where_without_arapgl
1056            $dpt_where
1057            $customer_where
1058            $project
1059          AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo,
1060
1061         (SELECT SUM(ac.amount)
1062          FROM acc_trans ac
1063          JOIN chart c ON (ac.chart_id = c.id)
1064          $customer_join
1065          WHERE $sumwhere
1066            $dpt_where_without_arapgl
1067            $dpt_where
1068            $customer_where
1069            $project
1070          AND ac.amount > 0
1071          AND c.accno = ?) AS sum_credit,
1072
1073         (SELECT SUM(ac.amount)
1074          FROM acc_trans ac
1075          JOIN chart c ON (ac.chart_id = c.id)
1076          $customer_join
1077          WHERE $sumwhere
1078            $dpt_where
1079            $dpt_where_without_arapgl
1080            $customer_where
1081            $project
1082          AND ac.amount < 0
1083          AND c.accno = ?) AS sum_debit,
1084
1085
1086         (SELECT max(ac.transdate) FROM acc_trans ac
1087         JOIN chart c ON (ac.chart_id = c.id)
1088         $customer_join
1089         WHERE $where
1090           $dpt_where_without_arapgl
1091           $customer_where
1092           $project
1093         AND c.accno = ?) AS last_transaction
1094  |;
1095
1096     $project_drcr = prepare_query($form, $dbh, $q_project_drcr);
1097   }
1098
1099
1100   my ($debit, $credit, $saldo, $soll_saldo, $haben_saldo,$soll_kummuliert, $haben_kummuliert, $last_transaction);
1101
1102   foreach my $accno (sort keys %trb) {
1103     $ref = {};
1104
1105     $ref->{accno} = $accno;
1106     map { $ref->{$_} = $trb{$accno}{$_} }
1107       qw(description category charttype amount soll_eb haben_eb beginning_balance);
1108
1109     $ref->{balance} = $form->round_amount($balance{ $ref->{accno} }, 2);
1110
1111     if ($trb{$accno}{charttype} eq 'A') {
1112
1113       # get DR/CR
1114       do_statement($form, $drcr, $q_drcr, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno});
1115
1116       ($debit, $credit, $saldo, $haben_saldo, $soll_saldo) = (0, 0, 0, 0, 0);
1117       my ($soll_kumuliert, $haben_kumuliert) = (0, 0);
1118       $last_transaction = "";
1119       while (($debit, $credit, $saldo, $haben_kumuliert, $soll_kumuliert, $last_transaction) = $drcr->fetchrow_array) {
1120         $ref->{debit}  += $debit;
1121         $ref->{credit} += $credit;
1122         if ($saldo >= 0) {
1123           $ref->{haben_saldo} += $saldo;
1124         } else {
1125           $ref->{soll_saldo} += $saldo * -1;
1126         }
1127         $ref->{last_transaction} = $last_transaction;
1128         $ref->{soll_kumuliert} = $soll_kumuliert * -1;
1129         $ref->{haben_kumuliert} = $haben_kumuliert;
1130       }
1131       $drcr->finish;
1132
1133       if ($form->{project_id}) {
1134
1135         # get DR/CR
1136         do_statement($form, $project_drcr, $q_project_drcr, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno});
1137
1138         ($debit, $credit) = (0, 0);
1139         while (($debit, $credit, $saldo, $haben_kumuliert, $soll_kumuliert, $last_transaction) = $project_drcr->fetchrow_array) {
1140           $ref->{debit}  += $debit;
1141           $ref->{credit} += $credit;
1142           if ($saldo >= 0) {
1143             $ref->{haben_saldo} += $saldo;
1144           } else {
1145             $ref->{soll_saldo} += $saldo * -1;
1146           }
1147           $ref->{soll_kumuliert} += $soll_kumuliert * -1;
1148           $ref->{haben_kumuliert} += $haben_kumuliert;
1149         }
1150         $project_drcr->finish;
1151       }
1152
1153       $ref->{debit}  = $form->round_amount($ref->{debit},  2);
1154       $ref->{credit} = $form->round_amount($ref->{credit}, 2);
1155
1156       if ($ref->{haben_saldo} != 0) {
1157         $ref->{haben_saldo}  = $ref->{haben_saldo} + $ref->{beginning_balance};
1158         if ($ref->{haben_saldo} < 0) {
1159           $ref->{soll_saldo} = $form->round_amount(($ref->{haben_saldo} *- 1), 2);
1160           $ref->{haben_saldo} = 0;
1161         }
1162       } else {
1163         $ref->{soll_saldo} = $ref->{soll_saldo} - $ref->{beginning_balance};
1164         if ($ref->{soll_saldo} < 0) {
1165           $ref->{haben_saldo} = $form->round_amount(($ref->{soll_saldo} * -1), 2);
1166           $ref->{soll_saldo} = 0;
1167         }
1168      }
1169       $ref->{haben_saldo} = $form->round_amount($ref->{haben_saldo}, 2);
1170       $ref->{soll_saldo} = $form->round_amount($ref->{soll_saldo}, 2);
1171       $ref->{haben_kumuliert}  = $form->round_amount($ref->{haben_kumuliert},  2);
1172       $ref->{soll_kumuliert} = $form->round_amount($ref->{soll_kumuliert}, 2);
1173     }
1174
1175     # add subtotal
1176     my @accno;
1177     @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
1178     $accno = pop @accno;
1179     if ($accno) {
1180       $trb{$accno}{debit}  += $ref->{debit};
1181       $trb{$accno}{credit} += $ref->{credit};
1182       $trb{$accno}{soll_saldo}  += $ref->{soll_saldo};
1183       $trb{$accno}{haben_saldo} += $ref->{haben_saldo};
1184       $trb{$accno}{soll_kumuliert}  += $ref->{soll_kumuliert};
1185       $trb{$accno}{haben_kumuliert} += $ref->{haben_kumuliert};
1186     }
1187
1188     push @{ $form->{TB} }, $ref;
1189
1190   }
1191
1192   # debits and credits for headings
1193   foreach my $accno (@headingaccounts) {
1194     foreach $ref (@{ $form->{TB} }) {
1195       if ($accno eq $ref->{accno}) {
1196         $ref->{debit}           = $trb{$accno}{debit};
1197         $ref->{credit}          = $trb{$accno}{credit};
1198         $ref->{soll_saldo}      = $trb{$accno}{soll_saldo};
1199         $ref->{haben_saldo}     = $trb{$accno}{haben_saldo};
1200         $ref->{soll_kumuliert}  = $trb{$accno}{soll_kumuliert};
1201         $ref->{haben_kumuliert} = $trb{$accno}{haben_kumuliert};
1202       }
1203     }
1204   }
1205
1206   $main::lxdebug->leave_sub();
1207 }
1208
1209 sub aging {
1210   $main::lxdebug->enter_sub();
1211
1212   my ($self, $myconfig, $form) = @_;
1213
1214   # connect to database
1215   my $dbh     = SL::DB->client->dbh;
1216
1217   my ($invoice, $arap, $buysell, $ct, $ct_id, $ml);
1218
1219   # falls customer ziehen wir die offene forderungsliste
1220   # anderfalls für die lieferanten die offenen verbindlichkeitne
1221   if ($form->{ct} eq "customer") {
1222     $invoice = "is";
1223     $arap = "ar";
1224     $buysell = "buy";
1225     $ct = "customer";
1226     $ml = -1;
1227   } else {
1228     $invoice = "ir";
1229     $arap = "ap";
1230     $buysell = "sell";
1231     $ct = "vendor";
1232     $ml = 1;
1233   }
1234   $ct_id = "${ct}_id";
1235
1236   # erweiterung um einen freien zeitraum oder einen stichtag
1237   # mit entsprechender altersstrukturliste (s.a. Bug 1842)
1238   # eine neue variable an der oberfläche eingeführt, somit ist
1239   # todate == freier zeitrau und fordate == stichtag
1240   # duedate_where == nur fällige rechnungen anzeigen
1241
1242   my ($review_of_aging_list, $todate, $fromdate, $fromwhere, $fordate,
1243       $duedate_where);
1244
1245   if ($form->{reporttype} eq 'custom') {  # altersstrukturliste, nur fällige
1246
1247     # explizit rausschmeissen was man für diesen bericht nicht braucht
1248     delete $form->{fromdate};
1249     delete $form->{todate};
1250
1251     # an der oberfläche ist das tagesaktuelle datum vorausgewählt
1252     # falls es dennoch per Benutzereingabe gelöscht wird, lieber wieder vorbelegen
1253     # ferner muss für die spätere DB-Abfrage muss todate gesetzt sein.
1254     $form->{fordate}  = $form->current_date($myconfig) unless ($form->{fordate});
1255     $fordate          = conv_dateq($form->{fordate});
1256     $todate           = $fordate;
1257
1258     if ($form->{review_of_aging_list}) { # falls die liste leer ist, alles anzeigen
1259       if ($form->{review_of_aging_list} =~ m "-") {             # ..  periode von bis
1260         my @period = split(/-/, $form->{review_of_aging_list}); # ... von periode bis periode
1261         $review_of_aging_list = " AND $period[0] <  (date $fordate) - duedate
1262                                   AND (date $fordate) - duedate  < $period[1]";
1263       } else {
1264         $form->{review_of_aging_list} =~ s/[^0-9]//g;   # größer 120 das substitute ist nur für das '>' zeichen
1265         $review_of_aging_list = " AND $form->{review_of_aging_list} < (date $fordate) - duedate";
1266       }
1267     }
1268     $duedate_where = " AND (date $fordate) - duedate >= 0 ";
1269   } else {  # freier zeitraum, nur rechnungsdatum und OHNE review_of_aging_list
1270     $form->{todate}  = $form->current_date($myconfig) unless ($form->{todate});
1271     $todate = conv_dateq($form->{todate});
1272     $fromdate = conv_dateq($form->{fromdate});
1273     $fromwhere = ($form->{fromdate} ne "") ? " AND (transdate >= (date $fromdate)) " : "";
1274   }
1275   my $where = " 1 = 1 ";
1276   my ($name, $null);
1277
1278   if ($form->{$ct_id}) {
1279     $where .= qq| AND (ct.id = | . conv_i($form->{$ct_id}) . qq|)|;
1280   } elsif ($form->{ $form->{ct} }) {
1281     $where .= qq| AND (ct.name ILIKE | . $dbh->quote(like($form->{$ct})) . qq|)|;
1282   }
1283
1284   my $dpt_join;
1285   my $where_dpt;
1286   if ($form->{department}) {
1287     my ($null, $department_id) = split /--/, $form->{department};
1288     $dpt_join = qq| JOIN department d ON (a.department_id = d.id) |;
1289     $where .= qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|)|;
1290     $where_dpt = qq| AND (${arap}.department_id = | . conv_i($department_id, 'NULL') . qq|)|;
1291   }
1292  my $q_details = qq|
1293
1294     SELECT ${ct}.id AS ctid, ${ct}.name,
1295       street, zipcode, city, country, contact, email,
1296       phone as customerphone, fax as customerfax, ${ct}number,
1297       "invnumber", "transdate",
1298       (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",
1299       "duedate", invoice, ${arap}.id, date_part('days', now() - duedate) as overduedays,
1300       (SELECT $buysell
1301        FROM exchangerate
1302        WHERE (${arap}.currency_id = exchangerate.currency_id)
1303          AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
1304     FROM ${arap}, ${ct}
1305     WHERE ((paid != amount) OR (datepaid > (date $todate) AND datepaid is not null))
1306       AND NOT COALESCE (${arap}.storno, 'f')
1307       AND (${arap}.${ct}_id = ${ct}.id)
1308       $where_dpt
1309       AND (${ct}.id = ?)
1310       AND (transdate <= (date $todate) $fromwhere )
1311       $review_of_aging_list
1312       $duedate_where
1313     ORDER BY ctid, transdate, invnumber |;
1314
1315   my $sth_details = prepare_query($form, $dbh, $q_details);
1316
1317   # select outstanding vendors or customers, depends on $ct
1318   my $query =
1319     qq|SELECT DISTINCT ct.id, ct.name
1320        FROM $ct ct, $arap a
1321        $dpt_join
1322        WHERE $where
1323          AND (a.${ct_id} = ct.id)
1324          AND ((a.paid != a.amount) OR ((a.datepaid > $todate) AND (datepaid is NOT NULL)))
1325          AND (a.transdate <= $todate $fromwhere)
1326        ORDER BY ct.name|;
1327
1328   my $sth = prepare_execute_query($form, $dbh, $query);
1329
1330   $form->{AG} = [];
1331   # for each company that has some stuff outstanding
1332   while (my ($id) = $sth->fetchrow_array) {
1333     do_statement($form, $sth_details, $q_details, $id);
1334
1335     while (my $ref = $sth_details->fetchrow_hashref("NAME_lc")) {
1336       $ref->{module} = ($ref->{invoice}) ? $invoice : $arap;
1337       $ref->{exchangerate} = 1 unless $ref->{exchangerate};
1338       push @{ $form->{AG} }, $ref;
1339     }
1340
1341     $sth_details->finish;
1342
1343   }
1344
1345   $sth->finish;
1346
1347   $main::lxdebug->leave_sub();
1348 }
1349
1350 sub get_customer {
1351   $main::lxdebug->enter_sub();
1352
1353   my ($self, $myconfig, $form) = @_;
1354
1355   my $dbh = SL::DB->client->dbh;
1356
1357   my $ct = $form->{ct} eq "customer" ? "customer" : "vendor";
1358
1359   my $query =
1360     qq|SELECT ct.name, ct.email, ct.cc, ct.bcc
1361        FROM $ct ct
1362        WHERE ct.id = ?|;
1363   ($form->{ $form->{ct} }, $form->{email}, $form->{cc}, $form->{bcc}) =
1364     selectrow_query($form, $dbh, $query, $form->{"${ct}_id"});
1365
1366   $main::lxdebug->leave_sub();
1367 }
1368
1369 sub tax_report {
1370   $main::lxdebug->enter_sub();
1371
1372   my ($self, $myconfig, $form) = @_;
1373
1374   my $dbh = SL::DB->client->dbh;
1375
1376   my ($null, $department_id) = split /--/, $form->{department};
1377
1378   # build WHERE
1379   my $where = "1 = 1";
1380
1381   if ($department_id) {
1382     $where .= qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
1383   }
1384
1385   my ($accno, $rate);
1386
1387   if ($form->{accno}) {
1388     $accno = $form->{accno};
1389     $rate  = $form->{"$form->{accno}_rate"};
1390     $accno = qq| AND (ch.accno = | . $dbh->quote($accno) . qq|)|;
1391   }
1392   $rate *= 1;
1393
1394   my ($table, $ARAP);
1395
1396   if ($form->{db} eq 'ar') {
1397     $table = "customer";
1398     $ARAP  = "AR";
1399   } else {
1400     $table = "vendor";
1401     $ARAP  = "AP";
1402   }
1403
1404   my $arap = lc($ARAP);
1405
1406   my $transdate = "a.transdate";
1407
1408   if ($form->{method} eq 'cash') {
1409     $transdate = "a.datepaid";
1410
1411     my $todate = conv_dateq($form->{todate} ? $form->{todate} : $form->current_date($myconfig));
1412
1413     $where .= qq|
1414       AND ac.trans_id IN
1415         (
1416           SELECT trans_id
1417           FROM acc_trans a
1418           WHERE (a.chart_link LIKE '%${ARAP}_paid%')
1419           AND (transdate <= $todate)
1420         )
1421       |;
1422   }
1423
1424   # if there are any dates construct a where
1425   $where .= " AND ($transdate >= " . conv_dateq($form->{fromdate}) . ") " if ($form->{fromdate});
1426   $where .= " AND ($transdate <= " . conv_dateq($form->{todate}) . ") " if ($form->{todate});
1427
1428   my $ml = ($form->{db} eq 'ar') ? 1 : -1;
1429
1430   my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name));
1431   $sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } qw(id transdate invnumber name netamount tax)));
1432
1433   my $query =
1434       qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, a.invnumber, n.name, a.netamount,
1435           ac.amount * $ml AS tax
1436          FROM acc_trans ac
1437          JOIN ${arap} a ON (a.id = ac.trans_id)
1438          JOIN chart ch ON (ch.id = ac.chart_id)
1439          JOIN $table n ON (n.id = a.${table}_id)
1440          WHERE
1441            $where
1442            $accno
1443            AND (a.invoice = '0')
1444
1445          UNION
1446
1447          SELECT a.id, '1' AS invoice, $transdate AS transdate, a.invnumber, n.name, i.sellprice * i.qty AS netamount,
1448            i.sellprice * i.qty * $rate * $ml AS tax
1449          FROM acc_trans ac
1450          JOIN ${arap} a ON (a.id = ac.trans_id)
1451          JOIN chart ch ON (ch.id = ac.chart_id)
1452          JOIN $table n ON (n.id = a.${table}_id)
1453          JOIN ${table}tax t ON (t.${table}_id = n.id)
1454          JOIN invoice i ON (i.trans_id = a.id)
1455          WHERE
1456            $where
1457            $accno
1458            AND (a.invoice = '1')
1459          ORDER BY $sortorder|;
1460
1461   $form->{TR} = selectall_hashref_query($form, $dbh, $query);
1462
1463   $main::lxdebug->leave_sub();
1464 }
1465
1466 sub paymentaccounts {
1467   $main::lxdebug->enter_sub();
1468
1469   my ($self, $myconfig, $form) = @_;
1470
1471   # connect to database, turn AutoCommit off
1472   my $dbh = SL::DB->client->dbh;
1473
1474   my $ARAP = $form->{db} eq "ar" ? "AR" : "AP";
1475
1476   # get A(R|P)_paid accounts
1477   my $query =
1478     qq|SELECT accno, description
1479        FROM chart
1480        WHERE link LIKE '%${ARAP}_paid%'|;
1481   $form->{PR} = selectall_hashref_query($form, $dbh, $query);
1482
1483   $main::lxdebug->leave_sub();
1484 }
1485
1486 sub payments {
1487   $main::lxdebug->enter_sub();
1488
1489   my ($self, $myconfig, $form) = @_;
1490
1491   # connect to database, turn AutoCommit off
1492   my $dbh = SL::DB->client->dbh;
1493
1494   my $ml = 1;
1495   my $arap;
1496   my $table;
1497   if ($form->{db} eq 'ar') {
1498     $table = 'customer';
1499     $ml = -1;
1500     $arap = 'ar';
1501   } else {
1502     $table = 'vendor';
1503     $arap = 'ap';
1504   }
1505
1506   my ($query, $sth);
1507   my $where;
1508
1509   if ($form->{department_id}) {
1510     $where = qq| AND (a.department_id = | . conv_i($form->{department_id}, 'NULL') . qq|) |;
1511   }
1512
1513   if ($form->{fromdate}) {
1514     $where .= " AND (ac.transdate >= " . $dbh->quote($form->{fromdate}) . ") ";
1515   }
1516   if ($form->{todate}) {
1517     $where .= " AND (ac.transdate <= " . $dbh->quote($form->{todate}) . ") ";
1518   }
1519   if (!$form->{fx_transaction}) {
1520     $where .= " AND ac.fx_transaction = '0'";
1521   }
1522
1523   my $invnumber;
1524   my $reference;
1525   if ($form->{reference}) {
1526     $reference = $dbh->quote(like($form->{reference}));
1527     $invnumber = " AND (a.invnumber LIKE $reference)";
1528     $reference = " AND (a.reference LIKE $reference)";
1529   }
1530   if ($form->{source}) {
1531     $where .= " AND (ac.source ILIKE " . $dbh->quote(like($form->{source})) . ") ";
1532   }
1533   if ($form->{memo}) {
1534     $where .= " AND (ac.memo ILIKE " . $dbh->quote(like($form->{memo})) . ") ";
1535   }
1536
1537   my %sort_columns =  (
1538     'transdate'    => [ qw(transdate lower_invnumber lower_name) ],
1539     'invnumber'    => [ qw(lower_invnumber lower_name transdate) ],
1540     'name'         => [ qw(lower_name transdate)                 ],
1541     'source'       => [ qw(lower_source)                         ],
1542     'memo'         => [ qw(lower_memo)                           ],
1543     );
1544   my %lowered_columns =  (
1545     'invnumber'       => { 'gl' => 'a.reference',   'arap' => 'a.invnumber', },
1546     'memo'            => { 'gl' => 'ac.memo',       'arap' => 'ac.memo',     },
1547     'source'          => { 'gl' => 'ac.source',     'arap' => 'ac.source',   },
1548     'name'            => { 'gl' => 'a.description', 'arap' => 'c.name',      },
1549     );
1550
1551   my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
1552   my $sortkey   = $sort_columns{$form->{sort}} ? $form->{sort} : 'transdate';
1553   my $sortorder = join ', ', map { "$_ $sortdir" } @{ $sort_columns{$sortkey} };
1554
1555
1556   my %columns_for_sorting = ( 'gl' => '', 'arap' => '', );
1557   foreach my $spec (@{ $sort_columns{$sortkey} }) {
1558     next if ($spec !~ m/^lower_(.*)$/);
1559
1560     my $column = $1;
1561     map { $columns_for_sorting{$_} .= sprintf(', lower(%s) AS lower_%s', $lowered_columns{$column}->{$_}, $column) } qw(gl arap);
1562   }
1563
1564   $query = qq|SELECT id, accno, description FROM chart WHERE accno = ?|;
1565   $sth = prepare_query($form, $dbh, $query);
1566
1567   my $q_details =
1568       qq|SELECT c.name, a.invnumber, a.ordnumber,
1569            ac.transdate, ac.amount * $ml AS paid, ac.source,
1570            a.invoice, a.id, ac.memo, '${arap}' AS module
1571            $columns_for_sorting{arap}
1572          FROM acc_trans ac
1573          JOIN $arap a ON (ac.trans_id = a.id)
1574          JOIN $table c ON (c.id = a.${table}_id)
1575          WHERE (ac.chart_id = ?)
1576            $where
1577            $invnumber
1578
1579          UNION
1580
1581          SELECT a.description, a.reference, NULL AS ordnumber,
1582            ac.transdate, ac.amount * $ml AS paid, ac.source,
1583            '0' as invoice, a.id, ac.memo, 'gl' AS module
1584            $columns_for_sorting{gl}
1585          FROM acc_trans ac
1586          JOIN gl a ON (a.id = ac.trans_id)
1587          WHERE (ac.chart_id = ?)
1588            $where
1589            $reference
1590            AND (ac.amount * $ml) > 0
1591
1592          ORDER BY $sortorder|;
1593   my $sth_details = prepare_query($form, $dbh, $q_details);
1594
1595   $form->{PR} = [];
1596
1597   # cycle through each id
1598   foreach my $accno (split(/ /, $form->{paymentaccounts})) {
1599     do_statement($form, $sth, $query, $accno);
1600     my $ref = $sth->fetchrow_hashref();
1601     push(@{ $form->{PR} }, $ref);
1602     $sth->finish();
1603
1604     $form->{ $ref->{id} } = [] unless ($form->{ $ref->{id} });
1605
1606     do_statement($form, $sth_details, $q_details, $ref->{id}, $ref->{id});
1607     while (my $pr = $sth_details->fetchrow_hashref()) {
1608       push(@{ $form->{ $ref->{id} } }, $pr);
1609     }
1610     $sth_details->finish();
1611   }
1612
1613   $main::lxdebug->leave_sub();
1614 }
1615
1616 sub bwa {
1617   $main::lxdebug->enter_sub();
1618
1619   my ($self, $myconfig, $form) = @_;
1620
1621   my $dbh = SL::DB->client->dbh;
1622
1623   my $last_period = 0;
1624   my $category;
1625   my @categories  =
1626     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);
1627
1628   $form->{decimalplaces} *= 1;
1629
1630   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, "pos_bwa");
1631
1632   # if there are any compare dates
1633   my $year;
1634   if ($form->{fromdate} || $form->{todate}) {
1635     $last_period = 1;
1636     if ($form->{fromdate}) {
1637       $form->{fromdate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
1638       $year = $1;
1639     } else {
1640       $form->{todate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
1641       $year = $1;
1642     }
1643     my $kummfromdate = $form->{comparefromdate};
1644     my $kummtodate   = $form->{comparetodate};
1645     &get_accounts_g($dbh, $last_period, $kummfromdate, $kummtodate, $form, "pos_bwa");
1646   }
1647
1648   my %charts_by_category =
1649     partition_by { $_->{pos_bwa} }
1650     sort_by      { $_->{accno}   }
1651     map          { $form->{charts}->{$_} }
1652     keys %{ $form->{charts} };
1653   $form->{"charts_by_category"} = \%charts_by_category;
1654
1655   $form->{category_names} = AM->get_bwa_categories($myconfig, $form);
1656
1657   my @periods        = qw(jetzt kumm);
1658   my @gesamtleistung = qw(1 3);
1659   my @gesamtkosten   = qw (10 11 12 13 14 15 16 17 18 20);
1660   my @ergebnisse     =
1661     qw (rohertrag betriebrohertrag betriebsergebnis neutraleraufwand neutralerertrag ergebnisvorsteuern ergebnis gesamtleistung gesamtkosten);
1662
1663   foreach my $key (@periods) {
1664     $form->{ "$key" . "gesamtleistung" } = 0;
1665     $form->{ "$key" . "gesamtkosten" }   = 0;
1666
1667     foreach $category (@categories) {
1668
1669       if (defined($form->{$category}{$key})) {
1670         $form->{"$key$category"} =
1671           $form->format_amount($myconfig,
1672                                $form->round_amount($form->{$category}{$key}, 2
1673                                ),
1674                                $form->{decimalplaces},
1675                                '0');
1676       }
1677     }
1678     foreach my $item (@gesamtleistung) {
1679       $form->{ "$key" . "gesamtleistung" } += $form->{$item}{$key};
1680     }
1681     $form->{ "$key" . "gesamtleistung" } -= $form->{2}{$key};
1682
1683     foreach my $item (@gesamtkosten) {
1684       $form->{ "$key" . "gesamtkosten" } += $form->{$item}{$key};
1685     }
1686     $form->{ "$key" . "rohertrag" } =
1687       $form->{ "$key" . "gesamtleistung" } - $form->{4}{$key};
1688     $form->{ "$key" . "betriebrohertrag" } =
1689       $form->{ "$key" . "rohertrag" } + $form->{5}{$key};
1690     $form->{ "$key" . "betriebsergebnis" } =
1691       $form->{ "$key" . "betriebrohertrag" } -
1692       $form->{ "$key" . "gesamtkosten" };
1693     $form->{ "$key" . "neutraleraufwand" } =
1694       $form->{19}{$key} + $form->{30}{$key} + $form->{31}{$key};
1695     $form->{ "$key" . "neutralerertrag" } =
1696       $form->{32}{$key} + $form->{33}{$key} + $form->{34}{$key};
1697     $form->{ "$key" . "ergebnisvorsteuern" } =
1698       $form->{ "$key" . "betriebsergebnis" } -
1699       $form->{ "$key" . "neutraleraufwand" } +
1700       $form->{ "$key" . "neutralerertrag" };
1701     $form->{ "$key" . "ergebnis" } =
1702       $form->{ "$key" . "ergebnisvorsteuern" } - $form->{35}{$key};
1703
1704     if ($form->{ "$key" . "gesamtleistung" } > 0) {
1705       foreach $category (@categories) {
1706         if (defined($form->{$category}{$key})) {
1707           $form->{ "$key" . "gl" . "$category" } =
1708             $form->format_amount(
1709                                $myconfig,
1710                                $form->round_amount(
1711                                  ($form->{$category}{$key} /
1712                                     $form->{ "$key" . "gesamtleistung" } * 100
1713                                  ),
1714                                  $form->{decimalplaces}
1715                                ),
1716                                $form->{decimalplaces},
1717                                '0');
1718         }
1719       }
1720       foreach my $item (@ergebnisse) {
1721         $form->{ "$key" . "gl" . "$item" } =
1722           $form->format_amount($myconfig,
1723                                $form->round_amount(
1724                                  ( $form->{ "$key" . "$item" } /
1725                                      $form->{ "$key" . "gesamtleistung" } * 100
1726                                  ),
1727                                  $form->{decimalplaces}
1728                                ),
1729                                $form->{decimalplaces},
1730                                '0');
1731       }
1732     }
1733
1734     if ($form->{ "$key" . "gesamtkosten" } > 0) {
1735       foreach $category (@categories) {
1736         if (defined($form->{$category}{$key})) {
1737           $form->{ "$key" . "gk" . "$category" } =
1738             $form->format_amount($myconfig,
1739                                  $form->round_amount(
1740                                    ($form->{$category}{$key} /
1741                                       $form->{ "$key" . "gesamtkosten" } * 100
1742                                    ),
1743                                    $form->{decimalplaces}
1744                                  ),
1745                                  $form->{decimalplaces},
1746                                  '0');
1747         }
1748       }
1749       foreach my $item (@ergebnisse) {
1750         $form->{ "$key" . "gk" . "$item" } =
1751           $form->format_amount($myconfig,
1752                                $form->round_amount(
1753                                    ($form->{ "$key" . "$item" } /
1754                                       $form->{ "$key" . "gesamtkosten" } * 100
1755                                    ),
1756                                    $form->{decimalplaces}
1757                                ),
1758                                $form->{decimalplaces},
1759                                '0');
1760       }
1761     }
1762
1763     if ($form->{10}{$key} > 0) {
1764       foreach $category (@categories) {
1765         if (defined($form->{$category}{$key})) {
1766           $form->{ "$key" . "pk" . "$category" } =
1767             $form->format_amount(
1768                         $myconfig,
1769                         $form->round_amount(
1770                           ($form->{$category}{$key} / $form->{10}{$key} * 100),
1771                           $form->{decimalplaces}
1772                         ),
1773                         $form->{decimalplaces},
1774                         '0');
1775         }
1776       }
1777       foreach my $item (@ergebnisse) {
1778         $form->{ "$key" . "pk" . "$item" } =
1779           $form->format_amount($myconfig,
1780                                $form->round_amount(
1781                                                 ($form->{ "$key" . "$item" } /
1782                                                    $form->{10}{$key} * 100
1783                                                 ),
1784                                                 $form->{decimalplaces}
1785                                ),
1786                                $form->{decimalplaces},
1787                                '0');
1788       }
1789     }
1790
1791     if ($form->{4}{$key} > 0) {
1792       foreach $category (@categories) {
1793         if (defined($form->{$category}{$key})) {
1794           $form->{ "$key" . "auf" . "$category" } =
1795             $form->format_amount(
1796                          $myconfig,
1797                          $form->round_amount(
1798                            ($form->{$category}{$key} / $form->{4}{$key} * 100),
1799                            $form->{decimalplaces}
1800                          ),
1801                          $form->{decimalplaces},
1802                          '0');
1803         }
1804       }
1805       foreach my $item (@ergebnisse) {
1806         $form->{ "$key" . "auf" . "$item" } =
1807           $form->format_amount($myconfig,
1808                                $form->round_amount(
1809                                                 ($form->{ "$key" . "$item" } /
1810                                                    $form->{4}{$key} * 100
1811                                                 ),
1812                                                 $form->{decimalplaces}
1813                                ),
1814                                $form->{decimalplaces},
1815                                '0');
1816       }
1817     }
1818
1819     foreach my $item (@ergebnisse) {
1820       $form->{ "$key" . "$item" } =
1821         $form->format_amount($myconfig,
1822                              $form->round_amount($form->{ "$key" . "$item" },
1823                                                  $form->{decimalplaces}
1824                              ),
1825                              $form->{decimalplaces},
1826                              '0');
1827     }
1828
1829   }
1830
1831   $main::lxdebug->leave_sub();
1832 }
1833
1834 sub income_statement {
1835   $main::lxdebug->enter_sub();
1836
1837   my ($self, $myconfig, $form) = @_;
1838
1839   # connect to database
1840   my $dbh = $form->dbconnect($myconfig);
1841
1842   my $last_period          = 0;
1843   my @categories_einnahmen = qw(1 2 3 4 5 6 7);
1844   my @categories_ausgaben  =
1845     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);
1846
1847   my @ergebnisse = qw(sumeura sumeurb guvsumme);
1848
1849   $form->{decimalplaces} *= 1;
1850
1851
1852
1853   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
1854                   $form, "pos_eur");
1855
1856
1857   # add extra information to form to be used by template
1858   my %charts_by_category =
1859     partition_by { $_->{pos_eur} }
1860     sort_by      { $_->{accno}   }
1861     map          { $form->{charts}->{$_} }
1862     keys %{ $form->{charts} };
1863   $form->{"charts_by_category"} = \%charts_by_category;
1864
1865   $form->{"categories_income"}  = \@categories_einnahmen;
1866   $form->{"categories_expense"} = \@categories_ausgaben;
1867
1868   $form->{category_names} = AM->get_eur_categories($myconfig, $form);
1869
1870   my %eur_amounts;
1871
1872   foreach my $item (@categories_einnahmen) {
1873     $eur_amounts{$item} = $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
1874     $form->{"sumeura"} += $form->{$item};
1875   }
1876   foreach my $item (@categories_ausgaben) {
1877     $eur_amounts{$item} = $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
1878     $form->{"sumeurb"} += $form->{$item};
1879   }
1880
1881   $form->{"guvsumme"} = $form->{"sumeura"} - $form->{"sumeurb"};
1882
1883   $form->{eur_amounts} = \%eur_amounts;
1884
1885   foreach my $item (@ergebnisse) {
1886     $form->{$item} =
1887       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
1888   }
1889   $main::lxdebug->leave_sub();
1890 }
1891
1892 sub erfolgsrechnung {
1893   $main::lxdebug->enter_sub();
1894
1895   my ($self, $myconfig, $form) = @_;
1896   $form->{company} = $::instance_conf->get_company;
1897   $form->{address} = $::instance_conf->get_address;
1898   $form->{fromdate} = DateTime->new(year => 2000, month => 1, day => 1)->to_kivitendo unless $form->{fromdate};
1899   $form->{todate} = $form->current_date(%{$myconfig}) unless $form->{todate};
1900
1901   my %categories = (I => "ERTRAG", E => "AUFWAND");
1902   my $fromdate = conv_dateq($form->{fromdate});
1903   my $todate = conv_dateq($form->{todate});
1904   my $department_id = conv_i((split /--/, $form->{department})[1], 'NULL');
1905
1906   $form->{total} = 0;
1907
1908   foreach my $category ('I', 'E') {
1909     my %category = (
1910       name => $categories{$category},
1911       total => 0,
1912       accounts => get_accounts_ch($category)
1913     );
1914     foreach my $account (@{$category{accounts}}) {
1915       $account->{total} = get_total_ch($department_id, $account->{id}, $fromdate, $todate);
1916       $category{total} += $account->{total};
1917       $account->{total} = $form->format_amount($myconfig, $form->round_amount($account->{total}, 2), 2);
1918     }
1919     $form->{total} += $category{total};
1920     $category{total} = $form->format_amount($myconfig, $form->round_amount($category{total}, 2), 2);
1921     push(@{$form->{categories}}, \%category);
1922   }
1923   $form->{total} = $form->format_amount($myconfig, $form->round_amount($form->{total}, 2), 2);
1924
1925   $main::lxdebug->leave_sub();
1926   return {};
1927 }
1928
1929 sub get_accounts_ch {
1930   $main::lxdebug->enter_sub();
1931
1932   my ($category) = @_;
1933   my $inclusion = '' ;
1934
1935   if ($category eq 'I') {
1936     $inclusion = "AND pos_er = NULL OR pos_er = '1'";
1937   } elsif ($category eq 'E') {
1938     $inclusion = "AND pos_er = NULL OR pos_er = '6'";
1939   } else {
1940     $inclusion = "";
1941   }
1942
1943   my $query = qq|
1944     SELECT id, accno, description, category
1945     FROM chart
1946     WHERE category = ? $inclusion
1947     ORDER BY accno
1948   |;
1949   my $accounts = _query($query, $category);
1950
1951   $main::lxdebug->leave_sub();
1952   return $accounts;
1953 }
1954
1955 sub get_total_ch {
1956   $main::lxdebug->enter_sub();
1957
1958   my ($department_id, $chart_id, $fromdate, $todate) = @_;
1959   my $total = 0;
1960   my $query = qq|
1961     SELECT SUM(amount)
1962     FROM acc_trans
1963     WHERE chart_id = ?
1964       AND transdate >= ?
1965       AND transdate <= ?
1966   |;
1967   if ($department_id) {
1968     $query .= qq| AND COALESCE(
1969         (SELECT department_id FROM ar WHERE ar.id=trans_id),
1970         (SELECT department_id FROM gl WHERE gl.id=trans_id),
1971         (SELECT department_id FROM ap WHERE ap.id=trans_id)
1972     ) = ? |;
1973     $total += _query($query, $chart_id, $fromdate, $todate, $department_id)->[0]->{sum};
1974   } else {
1975     $total += _query($query, $chart_id, $fromdate, $todate)->[0]->{sum};
1976   }
1977
1978   $main::lxdebug->leave_sub();
1979   return $total;
1980 }
1981
1982 sub _query {return selectall_hashref_query($::form, $::form->get_standard_dbh, @_);}
1983
1984 1;