locales
[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       my $fiscal_year_startdate = conv_dateq($self->get_balance_starting_date($form->{fromdate}));
836       # my $date_trunc = "(select date_trunc('year', date $fromdate))";
837       $tofrom        .= " AND (ac.transdate >= $fromdate)";
838       $subwhere      .= " AND (ac.transdate >= $fromdate)";
839       $sumsubwhere   .= " AND (ac.transdate >= $fiscal_year_startdate) ";
840       $saldosubwhere .= " AND (ac.transdate >= $fiscal_year_startdate) ";
841       $invwhere      .= " AND (a.transdate  >= $fromdate)";
842       $glsaldowhere  .= " AND (ac.transdate >= $fiscal_year_startdate) ";
843       $glwhere        = " AND (ac.transdate >= $fromdate)";
844       $glsumwhere     = " AND (ac.transdate >= $fiscal_year_startdate) ";
845     }
846     if ($form->{todate}) {
847       $todate = conv_dateq($form->{todate});
848       $tofrom        .= " AND (ac.transdate <= $todate)";
849       $invwhere      .= " AND (a.transdate <= $todate)";
850       $saldosubwhere .= " AND (ac.transdate <= $todate)";
851       $sumsubwhere   .= " AND (ac.transdate <= $todate)";
852       $subwhere      .= " AND (ac.transdate <= $todate)";
853       $glwhere       .= " AND (ac.transdate <= $todate)";
854       $glsumwhere    .= " AND (ac.transdate <= $todate) ";
855       $glsaldowhere  .= " AND (ac.transdate <= $todate) ";
856    }
857   }
858
859   if ($form->{method} eq "cash") {
860     $where .=
861       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) |;
862     $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) |;
863
864     $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) |;
865   } else {
866     $where .= $tofrom . " AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL)";
867     $saldowhere .= $glsaldowhere . " AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL)";
868     $sumwhere .= $glsumwhere . " AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL)";
869
870     # get all entries before fromdate, which are not yet fetched
871     # TODO dpt_where_without_arapgl and project - project calculation seems bogus anyway
872     # TODO use fiscal_year_startdate for the whole trial balance
873     #      anyway, if the last booking is in a deviating fiscal year, this already improves the query
874     my $fiscal_year_startdate = conv_dateq($self->get_balance_starting_date($form->{fromdate}));
875     $fetch_accounts_before_from = qq|SELECT c.accno, c.description, c.category, SUM(ac.amount) AS amount
876                        FROM acc_trans ac JOIN chart c ON (c.id = ac.chart_id) WHERE 1 = 1 AND (ac.transdate <= $fromdate)
877                        AND (ac.transdate >= $fiscal_year_startdate)
878                        AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL)
879                        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)
880                        AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL))
881                        GROUP BY c.accno, c.description, c.category ORDER BY accno|;
882   }
883
884   $query = qq|
885        SELECT c.accno, c.description, c.category, SUM(ac.amount) AS amount
886        FROM acc_trans ac
887        JOIN chart c ON (c.id = ac.chart_id)
888        $customer_join
889        WHERE $where
890          $dpt_where_without_arapgl
891          $project
892        GROUP BY c.accno, c.description, c.category |;
893
894   if ($form->{project_id}) {
895     $query .= qq|
896       -- add project transactions from invoice
897
898       UNION ALL
899
900       SELECT c.accno, c.description, c.category, SUM(ac.sellprice * ac.qty) AS amount
901       FROM invoice ac
902       JOIN ar a ON (ac.trans_id = a.id)
903       JOIN parts p ON (ac.parts_id = p.id)
904       JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
905       JOIN chart c ON (t.income_accno_id = c.id)
906       WHERE $invwhere
907         $dpt_where
908         $customer_where
909         $project
910       GROUP BY c.accno, c.description, c.category
911
912       UNION ALL
913
914       SELECT c.accno, c.description, c.category, SUM(ac.sellprice * ac.qty) * -1 AS amount
915       FROM invoice ac
916       JOIN ap a ON (ac.trans_id = a.id)
917       JOIN parts p ON (ac.parts_id = p.id)
918       JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
919       JOIN chart c ON (t.expense_accno_id = c.id)
920       WHERE $invwhere
921         $dpt_where
922         $customer_no_union
923         $project
924       GROUP BY c.accno, c.description, c.category
925       |;
926     }
927
928   $query .= qq| ORDER BY accno|;
929
930   $sth = prepare_execute_query($form, $dbh, $query);
931
932   # calculate the debit and credit in the period
933   while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
934     $trb{ $ref->{accno} }{description} = $ref->{description};
935     $trb{ $ref->{accno} }{charttype}   = 'A';
936     $trb{ $ref->{accno} }{category}    = $ref->{category};
937     $trb{ $ref->{accno} }{amount} += $ref->{amount};
938   }
939   $sth->finish;
940
941   if ($form->{method} ne "cash") {  # better eq 'accrual'
942     $sth = prepare_execute_query($form, $dbh, $fetch_accounts_before_from);
943     while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
944       $trb{ $ref->{accno} }{description} = $ref->{description};
945       $trb{ $ref->{accno} }{charttype}   = 'A';
946       $trb{ $ref->{accno} }{category}    = $ref->{category};
947       $trb{ $ref->{accno} }{amount} += $ref->{amount};
948     }
949     $sth->finish;
950   }
951
952   # prepare query for each account
953   my ($q_drcr, $drcr, $q_project_drcr, $project_drcr);
954
955   $q_drcr =
956     qq|SELECT
957          (SELECT SUM(ac.amount) * -1
958           FROM acc_trans ac
959           JOIN chart c ON (c.id = ac.chart_id)
960           $customer_join
961           WHERE $where
962             $dpt_where_without_arapgl
963             $customer_where
964             $project
965           AND (ac.amount < 0)
966           AND (c.accno = ?)) AS debit,
967
968          (SELECT SUM(ac.amount)
969           FROM acc_trans ac
970           JOIN chart c ON (c.id = ac.chart_id)
971           $customer_join
972           WHERE $where
973             $dpt_where_without_arapgl
974             $customer_where
975             $project
976           AND ac.amount > 0
977           AND c.accno = ?) AS credit,
978         (SELECT SUM(ac.amount)
979          FROM acc_trans ac
980          JOIN chart c ON (ac.chart_id = c.id)
981          $customer_join
982          WHERE $saldowhere
983            $dpt_where_without_arapgl
984            $customer_where
985            $project
986          AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo,
987
988         (SELECT SUM(ac.amount)
989          FROM acc_trans ac
990          JOIN chart c ON (ac.chart_id = c.id)
991          $customer_join
992          WHERE $sumwhere
993            $dpt_where_without_arapgl
994            $customer_where
995            $project
996          AND ac.amount > 0
997          AND c.accno = ?) AS sum_credit,
998
999         (SELECT SUM(ac.amount)
1000          FROM acc_trans ac
1001          JOIN chart c ON (ac.chart_id = c.id)
1002          $customer_join
1003          WHERE $sumwhere
1004            $dpt_where_without_arapgl
1005            $customer_where
1006            $project
1007          AND ac.amount < 0
1008          AND c.accno = ?) AS sum_debit,
1009
1010         (SELECT max(ac.transdate) FROM acc_trans ac
1011         JOIN chart c ON (ac.chart_id = c.id)
1012         $customer_join
1013         WHERE $where
1014           $dpt_where_without_arapgl
1015           $customer_where
1016           $project
1017         AND c.accno = ?) AS last_transaction
1018
1019
1020  |;
1021
1022   $drcr = prepare_query($form, $dbh, $q_drcr);
1023
1024   if ($form->{project_id}) {
1025     # prepare query for each account
1026     $q_project_drcr =
1027       qq|SELECT
1028           (SELECT SUM(ac.sellprice * ac.qty) * -1
1029            FROM invoice ac
1030            JOIN parts p ON (ac.parts_id = p.id)
1031            JOIN ap a ON (ac.trans_id = a.id)
1032            JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
1033            JOIN chart c ON (t.expense_accno_id = c.id)
1034            WHERE $invwhere
1035              $dpt_where
1036              $customer_no_union
1037              $project
1038            AND c.accno = ?) AS debit,
1039
1040           (SELECT SUM(ac.sellprice * ac.qty)
1041            FROM invoice ac
1042            JOIN parts p ON (ac.parts_id = p.id)
1043            JOIN ar a ON (ac.trans_id = a.id)
1044            JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
1045            JOIN chart c ON (t.income_accno_id = c.id)
1046            WHERE $invwhere
1047              $dpt_where
1048              $customer_where
1049              $project
1050            AND c.accno = ?) AS credit,
1051
1052         (SELECT SUM(ac.amount)
1053          FROM acc_trans ac
1054          JOIN chart c ON (ac.chart_id = c.id)
1055          $customer_join
1056          WHERE $saldowhere
1057            $dpt_where_without_arapgl
1058            $dpt_where
1059            $customer_where
1060            $project
1061          AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo,
1062
1063         (SELECT SUM(ac.amount)
1064          FROM acc_trans ac
1065          JOIN chart c ON (ac.chart_id = c.id)
1066          $customer_join
1067          WHERE $sumwhere
1068            $dpt_where_without_arapgl
1069            $dpt_where
1070            $customer_where
1071            $project
1072          AND ac.amount > 0
1073          AND c.accno = ?) AS sum_credit,
1074
1075         (SELECT SUM(ac.amount)
1076          FROM acc_trans ac
1077          JOIN chart c ON (ac.chart_id = c.id)
1078          $customer_join
1079          WHERE $sumwhere
1080            $dpt_where
1081            $dpt_where_without_arapgl
1082            $customer_where
1083            $project
1084          AND ac.amount < 0
1085          AND c.accno = ?) AS sum_debit,
1086
1087
1088         (SELECT max(ac.transdate) FROM acc_trans ac
1089         JOIN chart c ON (ac.chart_id = c.id)
1090         $customer_join
1091         WHERE $where
1092           $dpt_where_without_arapgl
1093           $customer_where
1094           $project
1095         AND c.accno = ?) AS last_transaction
1096  |;
1097
1098     $project_drcr = prepare_query($form, $dbh, $q_project_drcr);
1099   }
1100
1101
1102   my ($debit, $credit, $saldo, $soll_saldo, $haben_saldo, $soll_kumuliert, $haben_kumuliert, $last_transaction);
1103
1104   foreach my $accno (sort keys %trb) {
1105     $ref = {};
1106
1107     $ref->{accno} = $accno;
1108     map { $ref->{$_} = $trb{$accno}{$_} }
1109       qw(description category charttype amount soll_eb haben_eb beginning_balance);
1110
1111     $ref->{balance} = $form->round_amount($balance{ $ref->{accno} }, 2);
1112
1113     if ($trb{$accno}{charttype} eq 'A') {
1114
1115       # get DR/CR
1116       do_statement($form, $drcr, $q_drcr, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno});
1117
1118       ($debit, $credit, $saldo, $haben_saldo, $soll_saldo) = (0, 0, 0, 0, 0);
1119       my ($soll_kumuliert, $haben_kumuliert) = (0, 0);
1120       $last_transaction = "";
1121       while (($debit, $credit, $saldo, $haben_kumuliert, $soll_kumuliert, $last_transaction) = $drcr->fetchrow_array) {
1122         $ref->{debit}  += $debit;
1123         $ref->{credit} += $credit;
1124         if ($saldo >= 0) {
1125           $ref->{haben_saldo} += $saldo;
1126         } else {
1127           $ref->{soll_saldo} += $saldo * -1;
1128         }
1129         $ref->{last_transaction} = $last_transaction;
1130         $ref->{soll_kumuliert} = $soll_kumuliert * -1;
1131         $ref->{haben_kumuliert} = $haben_kumuliert;
1132       }
1133       $drcr->finish;
1134
1135       if ($form->{project_id}) {
1136
1137         # get DR/CR
1138         do_statement($form, $project_drcr, $q_project_drcr, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno});
1139
1140         ($debit, $credit) = (0, 0);
1141         while (($debit, $credit, $saldo, $haben_kumuliert, $soll_kumuliert, $last_transaction) = $project_drcr->fetchrow_array) {
1142           $ref->{debit}  += $debit;
1143           $ref->{credit} += $credit;
1144           if ($saldo >= 0) {
1145             $ref->{haben_saldo} += $saldo;
1146           } else {
1147             $ref->{soll_saldo} += $saldo * -1;
1148           }
1149           $ref->{soll_kumuliert} += $soll_kumuliert * -1;
1150           $ref->{haben_kumuliert} += $haben_kumuliert;
1151         }
1152         $project_drcr->finish;
1153       }
1154
1155       $ref->{debit}  = $form->round_amount($ref->{debit},  2);
1156       $ref->{credit} = $form->round_amount($ref->{credit}, 2);
1157
1158       if ($ref->{haben_saldo} != 0) {
1159         $ref->{haben_saldo}  = $ref->{haben_saldo} + $ref->{beginning_balance};
1160         if ($ref->{haben_saldo} < 0) {
1161           $ref->{soll_saldo} = $form->round_amount(($ref->{haben_saldo} *- 1), 2);
1162           $ref->{haben_saldo} = 0;
1163         }
1164       } else {
1165         $ref->{soll_saldo} = $ref->{soll_saldo} - $ref->{beginning_balance};
1166         if ($ref->{soll_saldo} < 0) {
1167           $ref->{haben_saldo} = $form->round_amount(($ref->{soll_saldo} * -1), 2);
1168           $ref->{soll_saldo} = 0;
1169         }
1170      }
1171       $ref->{haben_saldo} = $form->round_amount($ref->{haben_saldo}, 2);
1172       $ref->{soll_saldo} = $form->round_amount($ref->{soll_saldo}, 2);
1173       $ref->{haben_kumuliert}  = $form->round_amount($ref->{haben_kumuliert},  2);
1174       $ref->{soll_kumuliert} = $form->round_amount($ref->{soll_kumuliert}, 2);
1175     }
1176
1177     # add subtotal
1178     my @accno;
1179     @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
1180     $accno = pop @accno;
1181     if ($accno) {
1182       $trb{$accno}{debit}  += $ref->{debit};
1183       $trb{$accno}{credit} += $ref->{credit};
1184       $trb{$accno}{soll_saldo}  += $ref->{soll_saldo};
1185       $trb{$accno}{haben_saldo} += $ref->{haben_saldo};
1186       $trb{$accno}{soll_kumuliert}  += $ref->{soll_kumuliert};
1187       $trb{$accno}{haben_kumuliert} += $ref->{haben_kumuliert};
1188     }
1189
1190     push @{ $form->{TB} }, $ref;
1191
1192   }
1193
1194   # debits and credits for headings
1195   foreach my $accno (@headingaccounts) {
1196     foreach $ref (@{ $form->{TB} }) {
1197       if ($accno eq $ref->{accno}) {
1198         $ref->{debit}           = $trb{$accno}{debit};
1199         $ref->{credit}          = $trb{$accno}{credit};
1200         $ref->{soll_saldo}      = $trb{$accno}{soll_saldo};
1201         $ref->{haben_saldo}     = $trb{$accno}{haben_saldo};
1202         $ref->{soll_kumuliert}  = $trb{$accno}{soll_kumuliert};
1203         $ref->{haben_kumuliert} = $trb{$accno}{haben_kumuliert};
1204       }
1205     }
1206   }
1207
1208   $main::lxdebug->leave_sub();
1209 }
1210
1211 sub aging {
1212   $main::lxdebug->enter_sub();
1213
1214   my ($self, $myconfig, $form) = @_;
1215
1216   # connect to database
1217   my $dbh     = SL::DB->client->dbh;
1218
1219   my ($invoice, $arap, $buysell, $ct, $ct_id, $ml);
1220
1221   # falls customer ziehen wir die offene forderungsliste
1222   # anderfalls für die lieferanten die offenen verbindlichkeitne
1223   if ($form->{ct} eq "customer") {
1224     $invoice = "is";
1225     $arap = "ar";
1226     $buysell = "buy";
1227     $ct = "customer";
1228     $ml = -1;
1229   } else {
1230     $invoice = "ir";
1231     $arap = "ap";
1232     $buysell = "sell";
1233     $ct = "vendor";
1234     $ml = 1;
1235   }
1236   $ct_id = "${ct}_id";
1237
1238   # erweiterung um einen freien zeitraum oder einen stichtag
1239   # mit entsprechender altersstrukturliste (s.a. Bug 1842)
1240   # eine neue variable an der oberfläche eingeführt, somit ist
1241   # todate == freier zeitrau und fordate == stichtag
1242   # duedate_where == nur fällige rechnungen anzeigen
1243
1244   my ($review_of_aging_list, $todate, $fromdate, $fromwhere, $fordate,
1245       $duedate_where);
1246
1247   if ($form->{reporttype} eq 'custom') {  # altersstrukturliste, nur fällige
1248
1249     # explizit rausschmeissen was man für diesen bericht nicht braucht
1250     delete $form->{fromdate};
1251     delete $form->{todate};
1252
1253     # an der oberfläche ist das tagesaktuelle datum vorausgewählt
1254     # falls es dennoch per Benutzereingabe gelöscht wird, lieber wieder vorbelegen
1255     # ferner muss für die spätere DB-Abfrage muss todate gesetzt sein.
1256     $form->{fordate}  = $form->current_date($myconfig) unless ($form->{fordate});
1257     $fordate          = conv_dateq($form->{fordate});
1258     $todate           = $fordate;
1259
1260     if ($form->{review_of_aging_list}) { # falls die liste leer ist, alles anzeigen
1261       if ($form->{review_of_aging_list} =~ m "-") {             # ..  periode von bis
1262         my @period = split(/-/, $form->{review_of_aging_list}); # ... von periode bis periode
1263         $review_of_aging_list = " AND $period[0] <  (date $fordate) - duedate
1264                                   AND (date $fordate) - duedate  < $period[1]";
1265       } else {
1266         $form->{review_of_aging_list} =~ s/[^0-9]//g;   # größer 120 das substitute ist nur für das '>' zeichen
1267         $review_of_aging_list = " AND $form->{review_of_aging_list} < (date $fordate) - duedate";
1268       }
1269     }
1270     $duedate_where = " AND (date $fordate) - duedate >= 0 ";
1271   } else {  # freier zeitraum, nur rechnungsdatum und OHNE review_of_aging_list
1272     $form->{todate}  = $form->current_date($myconfig) unless ($form->{todate});
1273     $todate = conv_dateq($form->{todate});
1274     $fromdate = conv_dateq($form->{fromdate});
1275     $fromwhere = ($form->{fromdate} ne "") ? " AND (transdate >= (date $fromdate)) " : "";
1276   }
1277   my $where = " 1 = 1 ";
1278   my ($name, $null);
1279
1280   if ($form->{$ct_id}) {
1281     $where .= qq| AND (ct.id = | . conv_i($form->{$ct_id}) . qq|)|;
1282   } elsif ($form->{ $form->{ct} }) {
1283     $where .= qq| AND (ct.name ILIKE | . $dbh->quote(like($form->{$ct})) . qq|)|;
1284   }
1285
1286   my $dpt_join;
1287   my $where_dpt;
1288   if ($form->{department}) {
1289     my ($null, $department_id) = split /--/, $form->{department};
1290     $dpt_join = qq| JOIN department d ON (a.department_id = d.id) |;
1291     $where .= qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|)|;
1292     $where_dpt = qq| AND (${arap}.department_id = | . conv_i($department_id, 'NULL') . qq|)|;
1293   }
1294  my $q_details = qq|
1295
1296     SELECT ${ct}.id AS ctid, ${ct}.name,
1297       street, zipcode, city, country, contact, email,
1298       phone as customerphone, fax as customerfax, ${ct}number,
1299       "invnumber", "transdate",
1300       (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",
1301       "duedate", invoice, ${arap}.id, date_part('days', now() - duedate) as overduedays, datepaid, (amount - paid) as current_open,
1302       (SELECT $buysell
1303        FROM exchangerate
1304        WHERE (${arap}.currency_id = exchangerate.currency_id)
1305          AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
1306     FROM ${arap}, ${ct}
1307     WHERE ((paid != amount) OR (datepaid > (date $todate) AND datepaid is not null))
1308       AND NOT COALESCE (${arap}.storno, 'f')
1309       AND (${arap}.${ct}_id = ${ct}.id)
1310       $where_dpt
1311       AND (${ct}.id = ?)
1312       AND (transdate <= (date $todate) $fromwhere )
1313       $review_of_aging_list
1314       $duedate_where
1315     ORDER BY ctid, transdate, invnumber |;
1316
1317   my $sth_details = prepare_query($form, $dbh, $q_details);
1318
1319   # select outstanding vendors or customers, depends on $ct
1320   my $query =
1321     qq|SELECT DISTINCT ct.id, ct.name
1322        FROM $ct ct, $arap a
1323        $dpt_join
1324        WHERE $where
1325          AND (a.${ct_id} = ct.id)
1326          AND ((a.paid != a.amount) OR ((a.datepaid > $todate) AND (datepaid is NOT NULL)))
1327          AND (a.transdate <= $todate $fromwhere)
1328        ORDER BY ct.name|;
1329
1330   my $sth = prepare_execute_query($form, $dbh, $query);
1331
1332   $form->{AG} = [];
1333   # for each company that has some stuff outstanding
1334   while (my ($id) = $sth->fetchrow_array) {
1335     do_statement($form, $sth_details, $q_details, $id);
1336
1337     while (my $ref = $sth_details->fetchrow_hashref("NAME_lc")) {
1338       $ref->{module} = ($ref->{invoice}) ? $invoice : $arap;
1339       $ref->{exchangerate} = 1 unless $ref->{exchangerate};
1340       push @{ $form->{AG} }, $ref;
1341     }
1342
1343     $sth_details->finish;
1344
1345   }
1346
1347   $sth->finish;
1348
1349   $main::lxdebug->leave_sub();
1350 }
1351
1352 sub get_customer {
1353   $main::lxdebug->enter_sub();
1354
1355   my ($self, $myconfig, $form) = @_;
1356
1357   my $dbh = SL::DB->client->dbh;
1358
1359   my $ct = $form->{ct} eq "customer" ? "customer" : "vendor";
1360
1361   my $query =
1362     qq|SELECT ct.name, ct.email, ct.cc, ct.bcc
1363        FROM $ct ct
1364        WHERE ct.id = ?|;
1365   ($form->{ $form->{ct} }, $form->{email}, $form->{cc}, $form->{bcc}) =
1366     selectrow_query($form, $dbh, $query, $form->{"${ct}_id"});
1367
1368   $main::lxdebug->leave_sub();
1369 }
1370
1371 sub tax_report {
1372   $main::lxdebug->enter_sub();
1373
1374   my ($self, $myconfig, $form) = @_;
1375
1376   my $dbh = SL::DB->client->dbh;
1377
1378   my ($null, $department_id) = split /--/, $form->{department};
1379
1380   # build WHERE
1381   my $where = "1 = 1";
1382
1383   if ($department_id) {
1384     $where .= qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
1385   }
1386
1387   my ($accno, $rate);
1388
1389   if ($form->{accno}) {
1390     $accno = $form->{accno};
1391     $rate  = $form->{"$form->{accno}_rate"};
1392     $accno = qq| AND (ch.accno = | . $dbh->quote($accno) . qq|)|;
1393   }
1394   $rate *= 1;
1395
1396   my ($table, $ARAP);
1397
1398   if ($form->{db} eq 'ar') {
1399     $table = "customer";
1400     $ARAP  = "AR";
1401   } else {
1402     $table = "vendor";
1403     $ARAP  = "AP";
1404   }
1405
1406   my $arap = lc($ARAP);
1407
1408   my $transdate = "a.transdate";
1409
1410   if ($form->{method} eq 'cash') {
1411     $transdate = "a.datepaid";
1412
1413     my $todate = conv_dateq($form->{todate} ? $form->{todate} : $form->current_date($myconfig));
1414
1415     $where .= qq|
1416       AND ac.trans_id IN
1417         (
1418           SELECT trans_id
1419           FROM acc_trans a
1420           WHERE (a.chart_link LIKE '%${ARAP}_paid%')
1421           AND (transdate <= $todate)
1422         )
1423       |;
1424   }
1425
1426   # if there are any dates construct a where
1427   $where .= " AND ($transdate >= " . conv_dateq($form->{fromdate}) . ") " if ($form->{fromdate});
1428   $where .= " AND ($transdate <= " . conv_dateq($form->{todate}) . ") " if ($form->{todate});
1429
1430   my $ml = ($form->{db} eq 'ar') ? 1 : -1;
1431
1432   my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name));
1433   $sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } qw(id transdate invnumber name netamount tax)));
1434
1435   my $query =
1436       qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, a.invnumber, n.name, a.netamount,
1437           ac.amount * $ml AS tax
1438          FROM acc_trans ac
1439          JOIN ${arap} a ON (a.id = ac.trans_id)
1440          JOIN chart ch ON (ch.id = ac.chart_id)
1441          JOIN $table n ON (n.id = a.${table}_id)
1442          WHERE
1443            $where
1444            $accno
1445            AND (a.invoice = '0')
1446
1447          UNION
1448
1449          SELECT a.id, '1' AS invoice, $transdate AS transdate, a.invnumber, n.name, i.sellprice * i.qty AS netamount,
1450            i.sellprice * i.qty * $rate * $ml AS tax
1451          FROM acc_trans ac
1452          JOIN ${arap} a ON (a.id = ac.trans_id)
1453          JOIN chart ch ON (ch.id = ac.chart_id)
1454          JOIN $table n ON (n.id = a.${table}_id)
1455          JOIN ${table}tax t ON (t.${table}_id = n.id)
1456          JOIN invoice i ON (i.trans_id = a.id)
1457          WHERE
1458            $where
1459            $accno
1460            AND (a.invoice = '1')
1461          ORDER BY $sortorder|;
1462
1463   $form->{TR} = selectall_hashref_query($form, $dbh, $query);
1464
1465   $main::lxdebug->leave_sub();
1466 }
1467
1468 sub paymentaccounts {
1469   $main::lxdebug->enter_sub();
1470
1471   my ($self, $myconfig, $form) = @_;
1472
1473   # connect to database, turn AutoCommit off
1474   my $dbh = SL::DB->client->dbh;
1475
1476   my $ARAP = $form->{db} eq "ar" ? "AR" : "AP";
1477
1478   # get A(R|P)_paid accounts
1479   my $query =
1480     qq|SELECT accno, description
1481        FROM chart
1482        WHERE link LIKE '%${ARAP}_paid%'|;
1483   $form->{PR} = selectall_hashref_query($form, $dbh, $query);
1484
1485   $main::lxdebug->leave_sub();
1486 }
1487
1488 sub payments {
1489   $main::lxdebug->enter_sub();
1490
1491   my ($self, $myconfig, $form) = @_;
1492
1493   # connect to database, turn AutoCommit off
1494   my $dbh = SL::DB->client->dbh;
1495
1496   my $ml = 1;
1497   my $arap;
1498   my $table;
1499   if ($form->{db} eq 'ar') {
1500     $table = 'customer';
1501     $ml = -1;
1502     $arap = 'ar';
1503   } else {
1504     $table = 'vendor';
1505     $arap = 'ap';
1506   }
1507
1508   my ($query, $sth);
1509   my $where;
1510
1511   if ($form->{department_id}) {
1512     $where = qq| AND (a.department_id = | . conv_i($form->{department_id}, 'NULL') . qq|) |;
1513   }
1514
1515   if ($form->{fromdate}) {
1516     $where .= " AND (ac.transdate >= " . $dbh->quote($form->{fromdate}) . ") ";
1517   }
1518   if ($form->{todate}) {
1519     $where .= " AND (ac.transdate <= " . $dbh->quote($form->{todate}) . ") ";
1520   }
1521   if (!$form->{fx_transaction}) {
1522     $where .= " AND ac.fx_transaction = '0'";
1523   }
1524
1525   my $invnumber;
1526   my $reference;
1527   if ($form->{reference}) {
1528     $reference = $dbh->quote(like($form->{reference}));
1529     $invnumber = " AND (a.invnumber LIKE $reference)";
1530     $reference = " AND (a.reference LIKE $reference)";
1531   }
1532   if ($form->{source}) {
1533     $where .= " AND (ac.source ILIKE " . $dbh->quote(like($form->{source})) . ") ";
1534   }
1535   if ($form->{memo}) {
1536     $where .= " AND (ac.memo ILIKE " . $dbh->quote(like($form->{memo})) . ") ";
1537   }
1538
1539   my %sort_columns =  (
1540     'transdate'    => [ qw(transdate lower_invnumber lower_name) ],
1541     'invnumber'    => [ qw(lower_invnumber lower_name transdate) ],
1542     'name'         => [ qw(lower_name transdate)                 ],
1543     'source'       => [ qw(lower_source)                         ],
1544     'memo'         => [ qw(lower_memo)                           ],
1545     );
1546   my %lowered_columns =  (
1547     'invnumber'       => { 'gl' => 'a.reference',   'arap' => 'a.invnumber', },
1548     'memo'            => { 'gl' => 'ac.memo',       'arap' => 'ac.memo',     },
1549     'source'          => { 'gl' => 'ac.source',     'arap' => 'ac.source',   },
1550     'name'            => { 'gl' => 'a.description', 'arap' => 'c.name',      },
1551     );
1552
1553   my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
1554   my $sortkey   = $sort_columns{$form->{sort}} ? $form->{sort} : 'transdate';
1555   my $sortorder = join ', ', map { "$_ $sortdir" } @{ $sort_columns{$sortkey} };
1556
1557
1558   my %columns_for_sorting = ( 'gl' => '', 'arap' => '', );
1559   foreach my $spec (@{ $sort_columns{$sortkey} }) {
1560     next if ($spec !~ m/^lower_(.*)$/);
1561
1562     my $column = $1;
1563     map { $columns_for_sorting{$_} .= sprintf(', lower(%s) AS lower_%s', $lowered_columns{$column}->{$_}, $column) } qw(gl arap);
1564   }
1565
1566   $query = qq|SELECT id, accno, description FROM chart WHERE accno = ?|;
1567   $sth = prepare_query($form, $dbh, $query);
1568
1569   my $q_details =
1570       qq|SELECT c.name, a.invnumber, a.ordnumber,
1571            ac.transdate, ac.amount * $ml AS paid, ac.source,
1572            a.invoice, a.id, ac.memo, '${arap}' AS module
1573            $columns_for_sorting{arap}
1574          FROM acc_trans ac
1575          JOIN $arap a ON (ac.trans_id = a.id)
1576          JOIN $table c ON (c.id = a.${table}_id)
1577          WHERE (ac.chart_id = ?)
1578            $where
1579            $invnumber
1580
1581          UNION
1582
1583          SELECT a.description, a.reference, NULL AS ordnumber,
1584            ac.transdate, ac.amount * $ml AS paid, ac.source,
1585            '0' as invoice, a.id, ac.memo, 'gl' AS module
1586            $columns_for_sorting{gl}
1587          FROM acc_trans ac
1588          JOIN gl a ON (a.id = ac.trans_id)
1589          WHERE (ac.chart_id = ?)
1590            $where
1591            $reference
1592            AND (ac.amount * $ml) > 0
1593
1594          ORDER BY $sortorder|;
1595   my $sth_details = prepare_query($form, $dbh, $q_details);
1596
1597   $form->{PR} = [];
1598
1599   # cycle through each id
1600   foreach my $accno (split(/ /, $form->{paymentaccounts})) {
1601     do_statement($form, $sth, $query, $accno);
1602     my $ref = $sth->fetchrow_hashref();
1603     push(@{ $form->{PR} }, $ref);
1604     $sth->finish();
1605
1606     $form->{ $ref->{id} } = [] unless ($form->{ $ref->{id} });
1607
1608     do_statement($form, $sth_details, $q_details, $ref->{id}, $ref->{id});
1609     while (my $pr = $sth_details->fetchrow_hashref()) {
1610       push(@{ $form->{ $ref->{id} } }, $pr);
1611     }
1612     $sth_details->finish();
1613   }
1614
1615   $main::lxdebug->leave_sub();
1616 }
1617
1618 sub bwa {
1619   $main::lxdebug->enter_sub();
1620
1621   my ($self, $myconfig, $form) = @_;
1622
1623   my $dbh = SL::DB->client->dbh;
1624
1625   my $last_period = 0;
1626   my $category;
1627   my @categories  =
1628     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);
1629
1630   $form->{decimalplaces} *= 1;
1631
1632   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, "pos_bwa");
1633
1634   # if there are any compare dates
1635   my $year;
1636   if ($form->{fromdate} || $form->{todate}) {
1637     $last_period = 1;
1638     if ($form->{fromdate}) {
1639       $form->{fromdate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
1640       $year = $1;
1641     } else {
1642       $form->{todate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
1643       $year = $1;
1644     }
1645     my $kummfromdate = $form->{comparefromdate};
1646     my $kummtodate   = $form->{comparetodate};
1647     &get_accounts_g($dbh, $last_period, $kummfromdate, $kummtodate, $form, "pos_bwa");
1648   }
1649
1650   my %charts_by_category =
1651     partition_by { $_->{pos_bwa} }
1652     sort_by      { $_->{accno}   }
1653     map          { $form->{charts}->{$_} }
1654     keys %{ $form->{charts} };
1655   $form->{"charts_by_category"} = \%charts_by_category;
1656
1657   $form->{category_names} = AM->get_bwa_categories($myconfig, $form);
1658
1659   my @periods        = qw(jetzt kumm);
1660   my @gesamtleistung = qw(1 3);
1661   my @gesamtkosten   = qw (10 11 12 13 14 15 16 17 18 20);
1662   my @ergebnisse     =
1663     qw (rohertrag betriebrohertrag betriebsergebnis neutraleraufwand neutralerertrag ergebnisvorsteuern ergebnis gesamtleistung gesamtkosten);
1664
1665   foreach my $key (@periods) {
1666     $form->{ "$key" . "gesamtleistung" } = 0;
1667     $form->{ "$key" . "gesamtkosten" }   = 0;
1668
1669     foreach $category (@categories) {
1670
1671       if (defined($form->{$category}{$key})) {
1672         $form->{"$key$category"} =
1673           $form->format_amount($myconfig,
1674                                $form->round_amount($form->{$category}{$key}, 2
1675                                ),
1676                                $form->{decimalplaces},
1677                                '0');
1678       }
1679     }
1680     foreach my $item (@gesamtleistung) {
1681       $form->{ "$key" . "gesamtleistung" } += $form->{$item}{$key};
1682     }
1683     $form->{ "$key" . "gesamtleistung" } -= $form->{2}{$key};
1684
1685     foreach my $item (@gesamtkosten) {
1686       $form->{ "$key" . "gesamtkosten" } += $form->{$item}{$key};
1687     }
1688     $form->{ "$key" . "rohertrag" } =
1689       $form->{ "$key" . "gesamtleistung" } - $form->{4}{$key};
1690     $form->{ "$key" . "betriebrohertrag" } =
1691       $form->{ "$key" . "rohertrag" } + $form->{5}{$key};
1692     $form->{ "$key" . "betriebsergebnis" } =
1693       $form->{ "$key" . "betriebrohertrag" } -
1694       $form->{ "$key" . "gesamtkosten" };
1695     $form->{ "$key" . "neutraleraufwand" } =
1696       $form->{19}{$key} + $form->{30}{$key} + $form->{31}{$key};
1697     $form->{ "$key" . "neutralerertrag" } =
1698       $form->{32}{$key} + $form->{33}{$key} + $form->{34}{$key};
1699     $form->{ "$key" . "ergebnisvorsteuern" } =
1700       $form->{ "$key" . "betriebsergebnis" } -
1701       $form->{ "$key" . "neutraleraufwand" } +
1702       $form->{ "$key" . "neutralerertrag" };
1703     $form->{ "$key" . "ergebnis" } =
1704       $form->{ "$key" . "ergebnisvorsteuern" } - $form->{35}{$key};
1705
1706     if ($form->{ "$key" . "gesamtleistung" } > 0) {
1707       foreach $category (@categories) {
1708         if (defined($form->{$category}{$key})) {
1709           $form->{ "$key" . "gl" . "$category" } =
1710             $form->format_amount(
1711                                $myconfig,
1712                                $form->round_amount(
1713                                  ($form->{$category}{$key} /
1714                                     $form->{ "$key" . "gesamtleistung" } * 100
1715                                  ),
1716                                  $form->{decimalplaces}
1717                                ),
1718                                $form->{decimalplaces},
1719                                '0');
1720         }
1721       }
1722       foreach my $item (@ergebnisse) {
1723         $form->{ "$key" . "gl" . "$item" } =
1724           $form->format_amount($myconfig,
1725                                $form->round_amount(
1726                                  ( $form->{ "$key" . "$item" } /
1727                                      $form->{ "$key" . "gesamtleistung" } * 100
1728                                  ),
1729                                  $form->{decimalplaces}
1730                                ),
1731                                $form->{decimalplaces},
1732                                '0');
1733       }
1734     }
1735
1736     if ($form->{ "$key" . "gesamtkosten" } > 0) {
1737       foreach $category (@categories) {
1738         if (defined($form->{$category}{$key})) {
1739           $form->{ "$key" . "gk" . "$category" } =
1740             $form->format_amount($myconfig,
1741                                  $form->round_amount(
1742                                    ($form->{$category}{$key} /
1743                                       $form->{ "$key" . "gesamtkosten" } * 100
1744                                    ),
1745                                    $form->{decimalplaces}
1746                                  ),
1747                                  $form->{decimalplaces},
1748                                  '0');
1749         }
1750       }
1751       foreach my $item (@ergebnisse) {
1752         $form->{ "$key" . "gk" . "$item" } =
1753           $form->format_amount($myconfig,
1754                                $form->round_amount(
1755                                    ($form->{ "$key" . "$item" } /
1756                                       $form->{ "$key" . "gesamtkosten" } * 100
1757                                    ),
1758                                    $form->{decimalplaces}
1759                                ),
1760                                $form->{decimalplaces},
1761                                '0');
1762       }
1763     }
1764
1765     if ($form->{10}{$key} > 0) {
1766       foreach $category (@categories) {
1767         if (defined($form->{$category}{$key})) {
1768           $form->{ "$key" . "pk" . "$category" } =
1769             $form->format_amount(
1770                         $myconfig,
1771                         $form->round_amount(
1772                           ($form->{$category}{$key} / $form->{10}{$key} * 100),
1773                           $form->{decimalplaces}
1774                         ),
1775                         $form->{decimalplaces},
1776                         '0');
1777         }
1778       }
1779       foreach my $item (@ergebnisse) {
1780         $form->{ "$key" . "pk" . "$item" } =
1781           $form->format_amount($myconfig,
1782                                $form->round_amount(
1783                                                 ($form->{ "$key" . "$item" } /
1784                                                    $form->{10}{$key} * 100
1785                                                 ),
1786                                                 $form->{decimalplaces}
1787                                ),
1788                                $form->{decimalplaces},
1789                                '0');
1790       }
1791     }
1792
1793     if ($form->{4}{$key} > 0) {
1794       foreach $category (@categories) {
1795         if (defined($form->{$category}{$key})) {
1796           $form->{ "$key" . "auf" . "$category" } =
1797             $form->format_amount(
1798                          $myconfig,
1799                          $form->round_amount(
1800                            ($form->{$category}{$key} / $form->{4}{$key} * 100),
1801                            $form->{decimalplaces}
1802                          ),
1803                          $form->{decimalplaces},
1804                          '0');
1805         }
1806       }
1807       foreach my $item (@ergebnisse) {
1808         $form->{ "$key" . "auf" . "$item" } =
1809           $form->format_amount($myconfig,
1810                                $form->round_amount(
1811                                                 ($form->{ "$key" . "$item" } /
1812                                                    $form->{4}{$key} * 100
1813                                                 ),
1814                                                 $form->{decimalplaces}
1815                                ),
1816                                $form->{decimalplaces},
1817                                '0');
1818       }
1819     }
1820
1821     foreach my $item (@ergebnisse) {
1822       $form->{ "$key" . "$item" } =
1823         $form->format_amount($myconfig,
1824                              $form->round_amount($form->{ "$key" . "$item" },
1825                                                  $form->{decimalplaces}
1826                              ),
1827                              $form->{decimalplaces},
1828                              '0');
1829     }
1830
1831   }
1832
1833   $main::lxdebug->leave_sub();
1834 }
1835
1836 sub income_statement {
1837   $main::lxdebug->enter_sub();
1838
1839   my ($self, $myconfig, $form) = @_;
1840
1841   # connect to database
1842   my $dbh = $form->dbconnect($myconfig);
1843
1844   my $last_period          = 0;
1845   my @categories_einnahmen = qw(1 2 3 4 5 6 7);
1846   my @categories_ausgaben  =
1847     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);
1848
1849   my @ergebnisse = qw(sumeura sumeurb guvsumme);
1850
1851   $form->{decimalplaces} *= 1;
1852
1853
1854
1855   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
1856                   $form, "pos_eur");
1857
1858
1859   # add extra information to form to be used by template
1860   my %charts_by_category =
1861     partition_by { $_->{pos_eur} }
1862     sort_by      { $_->{accno}   }
1863     map          { $form->{charts}->{$_} }
1864     keys %{ $form->{charts} };
1865   $form->{"charts_by_category"} = \%charts_by_category;
1866
1867   $form->{"categories_income"}  = \@categories_einnahmen;
1868   $form->{"categories_expense"} = \@categories_ausgaben;
1869
1870   $form->{category_names} = AM->get_eur_categories($myconfig, $form);
1871
1872   my %eur_amounts;
1873
1874   foreach my $item (@categories_einnahmen) {
1875     $eur_amounts{$item} = $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
1876     $form->{"sumeura"} += $form->{$item};
1877   }
1878   foreach my $item (@categories_ausgaben) {
1879     $eur_amounts{$item} = $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
1880     $form->{"sumeurb"} += $form->{$item};
1881   }
1882
1883   $form->{"guvsumme"} = $form->{"sumeura"} - $form->{"sumeurb"};
1884
1885   $form->{eur_amounts} = \%eur_amounts;
1886
1887   foreach my $item (@ergebnisse) {
1888     $form->{$item} =
1889       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
1890   }
1891   $main::lxdebug->leave_sub();
1892 }
1893
1894 sub erfolgsrechnung {
1895   $main::lxdebug->enter_sub();
1896
1897   my ($self, $myconfig, $form) = @_;
1898   $form->{company} = $::instance_conf->get_company;
1899   $form->{address} = $::instance_conf->get_address;
1900   $form->{fromdate} = DateTime->new(year => 2000, month => 1, day => 1)->to_kivitendo unless $form->{fromdate};
1901   $form->{todate} = $form->current_date(%{$myconfig}) unless $form->{todate};
1902
1903   my %categories = (I => "ERTRAG", E => "AUFWAND");
1904   my $fromdate = conv_dateq($form->{fromdate});
1905   my $todate = conv_dateq($form->{todate});
1906   my $department_id = conv_i((split /--/, $form->{department})[1], 'NULL');
1907
1908   $form->{total} = 0;
1909
1910   foreach my $category ('I', 'E') {
1911     my %category = (
1912       name => $categories{$category},
1913       total => 0,
1914       accounts => get_accounts_ch($category)
1915     );
1916     foreach my $account (@{$category{accounts}}) {
1917       $account->{total} = get_total_ch($department_id, $account->{id}, $fromdate, $todate);
1918       $category{total} += $account->{total};
1919       $account->{total} = $form->format_amount($myconfig, $form->round_amount($account->{total}, 2), 2);
1920     }
1921     $form->{total} += $category{total};
1922     $category{total} = $form->format_amount($myconfig, $form->round_amount($category{total}, 2), 2);
1923     push(@{$form->{categories}}, \%category);
1924   }
1925   $form->{total} = $form->format_amount($myconfig, $form->round_amount($form->{total}, 2), 2);
1926
1927   $main::lxdebug->leave_sub();
1928   return {};
1929 }
1930
1931 sub get_accounts_ch {
1932   $main::lxdebug->enter_sub();
1933
1934   my ($category) = @_;
1935   my $inclusion = '' ;
1936
1937   if ($category eq 'I') {
1938     $inclusion = "AND pos_er = NULL OR pos_er = '1'";
1939   } elsif ($category eq 'E') {
1940     $inclusion = "AND pos_er = NULL OR pos_er = '6'";
1941   } else {
1942     $inclusion = "";
1943   }
1944
1945   my $query = qq|
1946     SELECT id, accno, description, category
1947     FROM chart
1948     WHERE category = ? $inclusion
1949     ORDER BY accno
1950   |;
1951   my $accounts = _query($query, $category);
1952
1953   $main::lxdebug->leave_sub();
1954   return $accounts;
1955 }
1956
1957 sub get_total_ch {
1958   $main::lxdebug->enter_sub();
1959
1960   my ($department_id, $chart_id, $fromdate, $todate) = @_;
1961   my $total = 0;
1962   my $query = qq|
1963     SELECT SUM(amount)
1964     FROM acc_trans
1965     WHERE chart_id = ?
1966       AND transdate >= ?
1967       AND transdate <= ?
1968   |;
1969   if ($department_id) {
1970     $query .= qq| AND COALESCE(
1971         (SELECT department_id FROM ar WHERE ar.id=trans_id),
1972         (SELECT department_id FROM gl WHERE gl.id=trans_id),
1973         (SELECT department_id FROM ap WHERE ap.id=trans_id)
1974     ) = ? |;
1975     $total += _query($query, $chart_id, $fromdate, $todate, $department_id)->[0]->{sum};
1976   } else {
1977     $total += _query($query, $chart_id, $fromdate, $todate)->[0]->{sum};
1978   }
1979
1980   $main::lxdebug->leave_sub();
1981   return $total;
1982 }
1983
1984 sub _query {return selectall_hashref_query($::form, $::form->get_standard_dbh, @_);}
1985
1986 1;