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