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