51b78b46b0e47e0ef7dec65bf463b72a07f621c3
[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 income_statement {
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(I E);
47   my $category;
48
49   $form->{decimalplaces} *= 1;
50
51   &get_accounts($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form,
52                 \@categories);
53
54   # if there are any compare dates
55   if ($form->{comparefromdate} || $form->{comparetodate}) {
56     $last_period = 1;
57
58     &get_accounts($dbh, $last_period,
59                   $form->{comparefromdate},
60                   $form->{comparetodate},
61                   $form, \@categories);
62   }
63
64   # disconnect
65   $dbh->disconnect;
66
67   # now we got $form->{I}{accno}{ }
68   # and $form->{E}{accno}{  }
69
70   my %account = (
71                  'I' => { 'label'  => 'income',
72                           'labels' => 'income',
73                           'ml'     => 1
74                  },
75                  'E' => { 'label'  => 'expense',
76                           'labels' => 'expenses',
77                           'ml'     => -1
78                  });
79
80   my $str;
81
82   foreach $category (@categories) {
83
84     foreach $key (sort keys %{ $form->{$category} }) {
85
86       # push description onto array
87
88       $str = ($form->{l_heading}) ? $form->{padding} : "";
89
90       if ($form->{$category}{$key}{charttype} eq "A") {
91         $str .=
92           ($form->{l_accno})
93           ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}"
94           : "$form->{$category}{$key}{description}";
95       }
96       if ($form->{$category}{$key}{charttype} eq "H") {
97         if ($account{$category}{subtotal} && $form->{l_subtotal}) {
98           $dash = "- ";
99           push(@{ $form->{"$account{$category}{label}_account"} },
100                "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}"
101           );
102           push(@{ $form->{"$account{$category}{labels}_this_period"} },
103                $form->format_amount(
104                         $myconfig,
105                         $account{$category}{subthis} * $account{$category}{ml},
106                         $form->{decimalplaces}, $dash
107                ));
108
109           if ($last_period) {
110             push(@{ $form->{"$account{$category}{labels}_last_period"} },
111                  $form->format_amount(
112                         $myconfig,
113                         $account{$category}{sublast} * $account{$category}{ml},
114                         $form->{decimalplaces}, $dash
115                  ));
116           }
117
118         }
119
120         $str =
121           "$form->{br}$form->{bold}$form->{$category}{$key}{description}$form->{endbold}";
122
123         $account{$category}{subthis}        = $form->{$category}{$key}{this};
124         $account{$category}{sublast}        = $form->{$category}{$key}{last};
125         $account{$category}{subdescription} =
126           $form->{$category}{$key}{description};
127         $account{$category}{subtotal} = 1;
128
129         $form->{$category}{$key}{this} = 0;
130         $form->{$category}{$key}{last} = 0;
131
132         next unless $form->{l_heading};
133
134         $dash = " ";
135       }
136
137       push(@{ $form->{"$account{$category}{label}_account"} }, $str);
138
139       if ($form->{$category}{$key}{charttype} eq 'A') {
140         $form->{"total_$account{$category}{labels}_this_period"} +=
141           $form->{$category}{$key}{this} * $account{$category}{ml};
142         $dash = "- ";
143       }
144
145       push(@{ $form->{"$account{$category}{labels}_this_period"} },
146            $form->format_amount(
147                       $myconfig,
148                       $form->{$category}{$key}{this} * $account{$category}{ml},
149                       $form->{decimalplaces}, $dash
150            ));
151
152       # add amount or - for last period
153       if ($last_period) {
154         $form->{"total_$account{$category}{labels}_last_period"} +=
155           $form->{$category}{$key}{last} * $account{$category}{ml};
156
157         push(@{ $form->{"$account{$category}{labels}_last_period"} },
158              $form->format_amount(
159                       $myconfig,
160                       $form->{$category}{$key}{last} * $account{$category}{ml},
161                       $form->{decimalplaces}, $dash
162              ));
163       }
164     }
165
166     $str = ($form->{l_heading}) ? $form->{padding} : "";
167     if ($account{$category}{subtotal} && $form->{l_subtotal}) {
168       push(@{ $form->{"$account{$category}{label}_account"} },
169            "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}"
170       );
171       push(@{ $form->{"$account{$category}{labels}_this_period"} },
172            $form->format_amount(
173                         $myconfig,
174                         $account{$category}{subthis} * $account{$category}{ml},
175                         $form->{decimalplaces}, $dash
176            ));
177
178       if ($last_period) {
179         push(@{ $form->{"$account{$category}{labels}_last_period"} },
180              $form->format_amount(
181                         $myconfig,
182                         $account{$category}{sublast} * $account{$category}{ml},
183                         $form->{decimalplaces}, $dash
184              ));
185       }
186     }
187
188   }
189
190   # totals for income and expenses
191   $form->{total_income_this_period} =
192     $form->round_amount($form->{total_income_this_period},
193                         $form->{decimalplaces});
194   $form->{total_expenses_this_period} =
195     $form->round_amount($form->{total_expenses_this_period},
196                         $form->{decimalplaces});
197
198   # total for income/loss
199   $form->{total_this_period} =
200     $form->{total_income_this_period} - $form->{total_expenses_this_period};
201
202   if ($last_period) {
203
204     # total for income/loss
205     $form->{total_last_period} =
206       $form->format_amount(
207        $myconfig,
208        $form->{total_income_last_period} - $form->{total_expenses_last_period},
209        $form->{decimalplaces},
210        "- ");
211
212     # totals for income and expenses for last_period
213     $form->{total_income_last_period} =
214       $form->format_amount($myconfig,
215                            $form->{total_income_last_period},
216                            $form->{decimalplaces}, "- ");
217     $form->{total_expenses_last_period} =
218       $form->format_amount($myconfig,
219                            $form->{total_expenses_last_period},
220                            $form->{decimalplaces}, "- ");
221
222   }
223
224   $form->{total_income_this_period} =
225     $form->format_amount($myconfig,
226                          $form->{total_income_this_period},
227                          $form->{decimalplaces}, "- ");
228   $form->{total_expenses_this_period} =
229     $form->format_amount($myconfig,
230                          $form->{total_expenses_this_period},
231                          $form->{decimalplaces}, "- ");
232   $form->{total_this_period} =
233     $form->format_amount($myconfig,
234                          $form->{total_this_period},
235                          $form->{decimalplaces}, "- ");
236
237   $main::lxdebug->leave_sub();
238 }
239
240 sub balance_sheet {
241   $main::lxdebug->enter_sub();
242
243   my ($self, $myconfig, $form) = @_;
244
245   # connect to database
246   my $dbh = $form->dbconnect($myconfig);
247
248   my $last_period = 0;
249   my @categories  = qw(A C L Q);
250
251   # if there are any dates construct a where
252   if ($form->{asofdate}) {
253
254     $form->{this_period} = "$form->{asofdate}";
255     $form->{period}      = "$form->{asofdate}";
256
257   }
258
259   $form->{decimalplaces} *= 1;
260
261   &get_accounts($dbh, $last_period, "", $form->{asofdate}, $form,
262                 \@categories);
263
264   # if there are any compare dates
265   if ($form->{compareasofdate}) {
266
267     $last_period = 1;
268     &get_accounts($dbh, $last_period, "", $form->{compareasofdate},
269                   $form, \@categories);
270
271     $form->{last_period} = "$form->{compareasofdate}";
272
273   }
274
275   # disconnect
276   $dbh->disconnect;
277
278   # now we got $form->{A}{accno}{ }    assets
279   # and $form->{L}{accno}{ }           liabilities
280   # and $form->{Q}{accno}{ }           equity
281   # build asset accounts
282
283   my $str;
284   my $key;
285
286   my %account = (
287                  'A' => { 'label'  => 'asset',
288                           'labels' => 'assets',
289                           'ml'     => -1
290                  },
291                  'L' => { 'label'  => 'liability',
292                           'labels' => 'liabilities',
293                           'ml'     => 1
294                  },
295                  'Q' => { 'label'  => 'equity',
296                           'labels' => 'equity',
297                           'ml'     => 1
298                  });
299
300   foreach $category (grep { !/C/ } @categories) {
301
302     foreach $key (sort keys %{ $form->{$category} }) {
303
304       $str = ($form->{l_heading}) ? $form->{padding} : "";
305
306       if ($form->{$category}{$key}{charttype} eq "A") {
307         $str .=
308           ($form->{l_accno})
309           ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}"
310           : "$form->{$category}{$key}{description}";
311       }
312       if ($form->{$category}{$key}{charttype} eq "H") {
313         if ($account{$category}{subtotal} && $form->{l_subtotal}) {
314           $dash = "- ";
315           push(@{ $form->{"$account{$category}{label}_account"} },
316                "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}"
317           );
318           push(@{ $form->{"$account{$category}{label}_this_period"} },
319                $form->format_amount(
320                         $myconfig,
321                         $account{$category}{subthis} * $account{$category}{ml},
322                         $form->{decimalplaces}, $dash
323                ));
324
325           if ($last_period) {
326             push(@{ $form->{"$account{$category}{label}_last_period"} },
327                  $form->format_amount(
328                         $myconfig,
329                         $account{$category}{sublast} * $account{$category}{ml},
330                         $form->{decimalplaces}, $dash
331                  ));
332           }
333         }
334
335         $str =
336           "$form->{bold}$form->{$category}{$key}{description}$form->{endbold}";
337
338         $account{$category}{subthis}        = $form->{$category}{$key}{this};
339         $account{$category}{sublast}        = $form->{$category}{$key}{last};
340         $account{$category}{subdescription} =
341           $form->{$category}{$key}{description};
342         $account{$category}{subtotal} = 1;
343
344         $form->{$category}{$key}{this} = 0;
345         $form->{$category}{$key}{last} = 0;
346
347         next unless $form->{l_heading};
348
349         $dash = " ";
350       }
351
352       # push description onto array
353       push(@{ $form->{"$account{$category}{label}_account"} }, $str);
354
355       if ($form->{$category}{$key}{charttype} eq 'A') {
356         $form->{"total_$account{$category}{labels}_this_period"} +=
357           $form->{$category}{$key}{this} * $account{$category}{ml};
358         $dash = "- ";
359       }
360
361       push(@{ $form->{"$account{$category}{label}_this_period"} },
362            $form->format_amount(
363                       $myconfig,
364                       $form->{$category}{$key}{this} * $account{$category}{ml},
365                       $form->{decimalplaces}, $dash
366            ));
367
368       if ($last_period) {
369         $form->{"total_$account{$category}{labels}_last_period"} +=
370           $form->{$category}{$key}{last} * $account{$category}{ml};
371
372         push(@{ $form->{"$account{$category}{label}_last_period"} },
373              $form->format_amount(
374                       $myconfig,
375                       $form->{$category}{$key}{last} * $account{$category}{ml},
376                       $form->{decimalplaces}, $dash
377              ));
378       }
379     }
380
381     $str = ($form->{l_heading}) ? $form->{padding} : "";
382     if ($account{$category}{subtotal} && $form->{l_subtotal}) {
383       push(@{ $form->{"$account{$category}{label}_account"} },
384            "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}"
385       );
386       push(@{ $form->{"$account{$category}{label}_this_period"} },
387            $form->format_amount(
388                         $myconfig,
389                         $account{$category}{subthis} * $account{$category}{ml},
390                         $form->{decimalplaces}, $dash
391            ));
392
393       if ($last_period) {
394         push(@{ $form->{"$account{$category}{label}_last_period"} },
395              $form->format_amount(
396                         $myconfig,
397                         $account{$category}{sublast} * $account{$category}{ml},
398                         $form->{decimalplaces}, $dash
399              ));
400       }
401     }
402
403   }
404
405   # totals for assets, liabilities
406   $form->{total_assets_this_period} =
407     $form->round_amount($form->{total_assets_this_period},
408                         $form->{decimalplaces});
409   $form->{total_liabilities_this_period} =
410     $form->round_amount($form->{total_liabilities_this_period},
411                         $form->{decimalplaces});
412   $form->{total_equity_this_period} =
413     $form->round_amount($form->{total_equity_this_period},
414                         $form->{decimalplaces});
415
416   # calculate earnings
417   $form->{earnings_this_period} =
418     $form->{total_assets_this_period} -
419     $form->{total_liabilities_this_period} - $form->{total_equity_this_period};
420
421   push(@{ $form->{equity_this_period} },
422        $form->format_amount($myconfig,
423                             $form->{earnings_this_period},
424                             $form->{decimalplaces}, "- "
425        ));
426
427   $form->{total_equity_this_period} =
428     $form->round_amount(
429              $form->{total_equity_this_period} + $form->{earnings_this_period},
430              $form->{decimalplaces});
431
432   # add liability + equity
433   $form->{total_this_period} =
434     $form->format_amount(
435     $myconfig,
436     $form->{total_liabilities_this_period} + $form->{total_equity_this_period},
437     $form->{decimalplaces},
438     "- ");
439
440   if ($last_period) {
441
442     # totals for assets, liabilities
443     $form->{total_assets_last_period} =
444       $form->round_amount($form->{total_assets_last_period},
445                           $form->{decimalplaces});
446     $form->{total_liabilities_last_period} =
447       $form->round_amount($form->{total_liabilities_last_period},
448                           $form->{decimalplaces});
449     $form->{total_equity_last_period} =
450       $form->round_amount($form->{total_equity_last_period},
451                           $form->{decimalplaces});
452
453     # calculate retained earnings
454     $form->{earnings_last_period} =
455       $form->{total_assets_last_period} -
456       $form->{total_liabilities_last_period} -
457       $form->{total_equity_last_period};
458
459     push(@{ $form->{equity_last_period} },
460          $form->format_amount($myconfig,
461                               $form->{earnings_last_period},
462                               $form->{decimalplaces}, "- "
463          ));
464
465     $form->{total_equity_last_period} =
466       $form->round_amount(
467              $form->{total_equity_last_period} + $form->{earnings_last_period},
468              $form->{decimalplaces});
469
470     # add liability + equity
471     $form->{total_last_period} =
472       $form->format_amount($myconfig,
473                            $form->{total_liabilities_last_period} +
474                              $form->{total_equity_last_period},
475                            $form->{decimalplaces},
476                            "- ");
477
478   }
479
480   $form->{total_liabilities_last_period} =
481     $form->format_amount($myconfig,
482                          $form->{total_liabilities_last_period},
483                          $form->{decimalplaces}, "- ")
484     if ($form->{total_liabilities_last_period} != 0);
485
486   $form->{total_equity_last_period} =
487     $form->format_amount($myconfig,
488                          $form->{total_equity_last_period},
489                          $form->{decimalplaces}, "- ")
490     if ($form->{total_equity_last_period} != 0);
491
492   $form->{total_assets_last_period} =
493     $form->format_amount($myconfig,
494                          $form->{total_assets_last_period},
495                          $form->{decimalplaces}, "- ")
496     if ($form->{total_assets_last_period} != 0);
497
498   $form->{total_assets_this_period} =
499     $form->format_amount($myconfig,
500                          $form->{total_assets_this_period},
501                          $form->{decimalplaces}, "- ");
502
503   $form->{total_liabilities_this_period} =
504     $form->format_amount($myconfig,
505                          $form->{total_liabilities_this_period},
506                          $form->{decimalplaces}, "- ");
507
508   $form->{total_equity_this_period} =
509     $form->format_amount($myconfig,
510                          $form->{total_equity_this_period},
511                          $form->{decimalplaces}, "- ");
512
513   $main::lxdebug->leave_sub();
514 }
515
516 sub get_accounts {
517   $main::lxdebug->enter_sub();
518
519   my ($dbh, $last_period, $fromdate, $todate, $form, $categories) = @_;
520
521   my ($null, $department_id) = split /--/, $form->{department};
522
523   my $query;
524   my $dpt_where;
525   my $dpt_join;
526   my $project;
527   my $where    = "1 = 1";
528   my $glwhere  = "";
529   my $subwhere = "";
530   my $item;
531
532   my $category = "AND (";
533   foreach $item (@{$categories}) {
534     $category .= qq|c.category = '$item' OR |;
535   }
536   $category =~ s/OR $/\)/;
537
538   # get headings
539   $query = qq|SELECT c.accno, c.description, c.category
540               FROM chart c
541               WHERE c.charttype = 'H'
542               $category
543               ORDER by c.accno|;
544
545   if ($form->{accounttype} eq 'gifi') {
546     $query = qq|SELECT g.accno, g.description, c.category
547                 FROM gifi g
548                 JOIN chart c ON (c.gifi_accno = g.accno)
549                 WHERE c.charttype = 'H'
550                 $category
551                 ORDER BY g.accno|;
552   }
553
554   $sth = $dbh->prepare($query);
555   $sth->execute || $form->dberror($query);
556
557   my @headingaccounts = ();
558   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
559     $form->{ $ref->{category} }{ $ref->{accno} }{description} =
560       "$ref->{description}";
561     $form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "H";
562     $form->{ $ref->{category} }{ $ref->{accno} }{accno}     = $ref->{accno};
563
564     push @headingaccounts, $ref->{accno};
565   }
566
567   $sth->finish;
568
569   if ($fromdate) {
570     if ($form->{method} eq 'cash') {
571       $subwhere .= " AND transdate >= '$fromdate'";
572       $glwhere = " AND ac.transdate >= '$fromdate'";
573     } else {
574       $where .= " AND ac.transdate >= '$fromdate'";
575     }
576   }
577
578   if ($todate) {
579     $where    .= " AND ac.transdate <= '$todate'";
580     $subwhere .= " AND transdate <= '$todate'";
581   }
582
583   if ($department_id) {
584     $dpt_join = qq|
585                JOIN department t ON (a.department_id = t.id)
586                   |;
587     $dpt_where = qq|
588                AND t.id = $department_id
589                    |;
590   }
591
592   if ($form->{project_id}) {
593     $project = qq|
594                  AND ac.project_id = $form->{project_id}
595                  |;
596   }
597
598   if ($form->{accounttype} eq 'gifi') {
599
600     if ($form->{method} eq 'cash') {
601
602       $query = qq|
603         
604                  SELECT g.accno, sum(ac.amount) AS amount,
605                  g.description, c.category
606                  FROM acc_trans ac
607                  JOIN chart c ON (c.id = ac.chart_id)
608                  JOIN ar a ON (a.id = ac.trans_id)
609                  JOIN gifi g ON (g.accno = c.gifi_accno)
610                  $dpt_join
611                  WHERE $where
612                  $dpt_where
613                  $category
614                  AND ac.trans_id IN
615                    (
616                      SELECT trans_id
617                      FROM acc_trans
618                      JOIN chart ON (chart_id = id)
619                      WHERE link LIKE '%AR_paid%'
620                      $subwhere
621                    )
622                  $project
623                  GROUP BY g.accno, g.description, c.category
624                  
625        UNION ALL
626        
627                  SELECT '' AS accno, SUM(ac.amount) AS amount,
628                  '' AS description, c.category
629                  FROM acc_trans ac
630                  JOIN chart c ON (c.id = ac.chart_id)
631                  JOIN ar a ON (a.id = ac.trans_id)
632                  $dpt_join
633                  WHERE $where
634                  $dpt_where
635                  $category
636                  AND c.gifi_accno = ''
637                  AND ac.trans_id IN
638                    (
639                      SELECT trans_id
640                      FROM acc_trans
641                      JOIN chart ON (chart_id = id)
642                      WHERE link LIKE '%AR_paid%'
643                      $subwhere
644                    )
645                  $project
646                  GROUP BY c.category
647
648        UNION ALL
649
650                  SELECT g.accno, sum(ac.amount) AS amount,
651                  g.description, c.category
652                  FROM acc_trans ac
653                  JOIN chart c ON (c.id = ac.chart_id)
654                  JOIN ap a ON (a.id = ac.trans_id)
655                  JOIN gifi g ON (g.accno = c.gifi_accno)
656                  $dpt_join
657                  WHERE $where
658                  $dpt_where
659                  $category
660                  AND ac.trans_id IN
661                    (
662                      SELECT trans_id
663                      FROM acc_trans
664                      JOIN chart ON (chart_id = id)
665                      WHERE link LIKE '%AP_paid%'
666                      $subwhere
667                    )
668                  $project
669                  GROUP BY g.accno, g.description, c.category
670                  
671        UNION ALL
672        
673                  SELECT '' AS accno, SUM(ac.amount) AS amount,
674                  '' AS description, c.category
675                  FROM acc_trans ac
676                  JOIN chart c ON (c.id = ac.chart_id)
677                  JOIN ap a ON (a.id = ac.trans_id)
678                  $dpt_join
679                  WHERE $where
680                  $dpt_where
681                  $category
682                  AND c.gifi_accno = ''
683                  AND ac.trans_id IN
684                    (
685                      SELECT trans_id
686                      FROM acc_trans
687                      JOIN chart ON (chart_id = id)
688                      WHERE link LIKE '%AP_paid%'
689                      $subwhere
690                    )
691                  $project
692                  GROUP BY c.category
693
694        UNION ALL
695
696 -- add gl
697         
698                  SELECT g.accno, sum(ac.amount) AS amount,
699                  g.description, c.category
700                  FROM acc_trans ac
701                  JOIN chart c ON (c.id = ac.chart_id)
702                  JOIN gifi g ON (g.accno = c.gifi_accno)
703                  JOIN gl a ON (a.id = ac.trans_id)
704                  $dpt_join
705                  WHERE $where
706                  $glwhere
707                  $dpt_where
708                  $category
709                  AND NOT (c.link = 'AR' OR c.link = 'AP')
710                  $project
711                  GROUP BY g.accno, g.description, c.category
712                  
713        UNION ALL
714        
715                  SELECT '' AS accno, SUM(ac.amount) AS amount,
716                  '' AS description, c.category
717                  FROM acc_trans ac
718                  JOIN chart c ON (c.id = ac.chart_id)
719                  JOIN gl a ON (a.id = ac.trans_id)
720                  $dpt_join
721                  WHERE $where
722                  $glwhere
723                  $dpt_where
724                  $category
725                  AND c.gifi_accno = ''
726                  AND NOT (c.link = 'AR' OR c.link = 'AP')
727                  $project
728                  GROUP BY c.category
729                  |;
730
731       if ($form->{project_id}) {
732
733         $query .= qq|
734           
735        UNION ALL
736        
737                  SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
738                  g.description AS description, c.category
739                  FROM invoice ac
740                  JOIN ar a ON (a.id = ac.trans_id)
741                  JOIN parts p ON (ac.parts_id = p.id)
742                  JOIN chart c on (p.income_accno_id = c.id)
743                  JOIN gifi g ON (g.accno = c.gifi_accno)
744                  $dpt_join
745         -- use transdate from subwhere
746                  WHERE 1 = 1 $subwhere
747                  AND c.category = 'I'
748                  $dpt_where
749                  AND ac.trans_id IN
750                    (
751                      SELECT trans_id
752                      FROM acc_trans
753                      JOIN chart ON (chart_id = id)
754                      WHERE link LIKE '%AR_paid%'
755                      $subwhere
756                    )
757                  $project
758                  GROUP BY g.accno, g.description, c.category
759
760        UNION ALL
761        
762                  SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount,
763                  g.description AS description, c.category
764                  FROM invoice ac
765                  JOIN ap a ON (a.id = ac.trans_id)
766                  JOIN parts p ON (ac.parts_id = p.id)
767                  JOIN chart c on (p.expense_accno_id = c.id)
768                  JOIN gifi g ON (g.accno = c.gifi_accno)
769                  $dpt_join
770                  WHERE 1 = 1 $subwhere
771                  AND c.category = 'E'
772                  $dpt_where
773                  AND ac.trans_id IN
774                    (
775                      SELECT trans_id
776                      FROM acc_trans
777                      JOIN chart ON (chart_id = id)
778                      WHERE link LIKE '%AP_paid%'
779                      $subwhere
780                    )
781                  $project
782                  GROUP BY g.accno, g.description, c.category
783                  |;
784       }
785
786     } else {
787
788       if ($department_id) {
789         $dpt_join = qq|
790               JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
791               |;
792         $dpt_where = qq|
793                AND t.department_id = $department_id
794               |;
795
796       }
797
798       $query = qq|
799       
800               SELECT g.accno, SUM(ac.amount) AS amount,
801               g.description, c.category
802               FROM acc_trans ac
803               JOIN chart c ON (c.id = ac.chart_id)
804               JOIN gifi g ON (c.gifi_accno = g.accno)
805               $dpt_join
806               WHERE $where
807               $dpt_from
808               $category
809               $project
810               GROUP BY g.accno, g.description, c.category
811               
812            UNION ALL
813            
814               SELECT '' AS accno, SUM(ac.amount) AS amount,
815               '' AS description, c.category
816               FROM acc_trans ac
817               JOIN chart c ON (c.id = ac.chart_id)
818               $dpt_join
819               WHERE $where
820               $dpt_from
821               $category
822               AND c.gifi_accno = ''
823               $project
824               GROUP BY c.category
825               |;
826
827       if ($form->{project_id}) {
828
829         $query .= qq|
830           
831          UNION ALL
832        
833                  SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
834                  g.description AS description, c.category
835                  FROM invoice ac
836                  JOIN ar a ON (a.id = ac.trans_id)
837                  JOIN parts p ON (ac.parts_id = p.id)
838                  JOIN chart c on (p.income_accno_id = c.id)
839                  JOIN gifi g ON (c.gifi_accno = g.accno)
840                  $dpt_join
841         -- use transdate from subwhere
842                  WHERE 1 = 1 $subwhere
843                  AND c.category = 'I'
844                  $dpt_where
845                  $project
846                  GROUP BY g.accno, g.description, c.category
847
848        UNION ALL
849        
850                  SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount,
851                  g.description AS description, c.category
852                  FROM invoice ac
853                  JOIN ap a ON (a.id = ac.trans_id)
854                  JOIN parts p ON (ac.parts_id = p.id)
855                  JOIN chart c on (p.expense_accno_id = c.id)
856                  JOIN gifi g ON (c.gifi_accno = g.accno)
857                  $dpt_join
858                  WHERE 1 = 1 $subwhere
859                  AND c.category = 'E'
860                  $dpt_where
861                  $project
862                  GROUP BY g.accno, g.description, c.category
863                  |;
864       }
865
866     }
867
868   } else {    # standard account
869
870     if ($form->{method} eq 'cash') {
871
872       $query = qq|
873         
874                  SELECT c.accno, sum(ac.amount) AS amount,
875                  c.description, c.category
876                  FROM acc_trans ac
877                  JOIN chart c ON (c.id = ac.chart_id)
878                  JOIN ar a ON (a.id = ac.trans_id)
879                  $dpt_join
880                  WHERE $where
881                  $dpt_where
882                  $category
883                  AND ac.trans_id IN
884                    (
885                      SELECT trans_id
886                      FROM acc_trans
887                      JOIN chart ON (chart_id = id)
888                      WHERE link LIKE '%AR_paid%'
889                      $subwhere
890                    )
891                      
892                  $project
893                  GROUP BY c.accno, c.description, c.category
894                  
895         UNION ALL
896         
897                  SELECT c.accno, sum(ac.amount) AS amount,
898                  c.description, c.category
899                  FROM acc_trans ac
900                  JOIN chart c ON (c.id = ac.chart_id)
901                  JOIN ap a ON (a.id = ac.trans_id)
902                  $dpt_join
903                  WHERE $where
904                  $dpt_where
905                  $category
906                  AND ac.trans_id IN
907                    (
908                      SELECT trans_id
909                      FROM acc_trans
910                      JOIN chart ON (chart_id = id)
911                      WHERE link LIKE '%AP_paid%'
912                      $subwhere
913                    )
914                      
915                  $project
916                  GROUP BY c.accno, c.description, c.category
917                  
918         UNION ALL
919
920                  SELECT c.accno, sum(ac.amount) AS amount,
921                  c.description, c.category
922                  FROM acc_trans ac
923                  JOIN chart c ON (c.id = ac.chart_id)
924                  JOIN gl a ON (a.id = ac.trans_id)
925                  $dpt_join
926                  WHERE $where
927                  $glwhere
928                  $dpt_from
929                  $category
930                  AND NOT (c.link = 'AR' OR c.link = 'AP')
931                  $project
932                  GROUP BY c.accno, c.description, c.category
933                  |;
934
935       if ($form->{project_id}) {
936
937         $query .= qq|
938           
939          UNION ALL
940        
941                  SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
942                  c.description AS description, c.category
943                  FROM invoice ac
944                  JOIN ar a ON (a.id = ac.trans_id)
945                  JOIN parts p ON (ac.parts_id = p.id)
946                  JOIN chart c on (p.income_accno_id = c.id)
947                  $dpt_join
948         -- use transdate from subwhere
949                  WHERE 1 = 1 $subwhere
950                  AND c.category = 'I'
951                  $dpt_where
952                  AND ac.trans_id IN
953                    (
954                      SELECT trans_id
955                      FROM acc_trans
956                      JOIN chart ON (chart_id = id)
957                      WHERE link LIKE '%AR_paid%'
958                      $subwhere
959                    )
960
961                  $project
962                  GROUP BY c.accno, c.description, c.category
963
964          UNION ALL
965        
966                  SELECT c.accno AS accno, SUM(ac.sellprice) AS amount,
967                  c.description AS description, c.category
968                  FROM invoice ac
969                  JOIN ap a ON (a.id = ac.trans_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 1 = 1 $subwhere
974                  AND c.category = 'E'
975                  $dpt_where
976                  AND ac.trans_id IN
977                    (
978                      SELECT trans_id
979                      FROM acc_trans
980                      JOIN chart ON (chart_id = id)
981                      WHERE link LIKE '%AP_paid%'
982                      $subwhere
983                    )
984
985                  $project
986                  GROUP BY c.accno, c.description, c.category
987                  |;
988       }
989
990     } else {
991
992       if ($department_id) {
993         $dpt_join = qq|
994               JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
995               |;
996         $dpt_where = qq|
997                AND t.department_id = $department_id
998               |;
999       }
1000
1001       $query = qq|
1002       
1003                  SELECT c.accno, sum(ac.amount) AS amount,
1004                  c.description, c.category
1005                  FROM acc_trans ac
1006                  JOIN chart c ON (c.id = ac.chart_id)
1007                  $dpt_join
1008                  WHERE $where
1009                  $dpt_where
1010                  $category
1011                  $project
1012                  GROUP BY c.accno, c.description, c.category
1013                  |;
1014
1015       if ($form->{project_id}) {
1016
1017         $query .= qq|
1018           
1019         UNION ALL
1020        
1021                  SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
1022                  c.description AS description, c.category
1023                  FROM invoice ac
1024                  JOIN ar a ON (a.id = ac.trans_id)
1025                  JOIN parts p ON (ac.parts_id = p.id)
1026                  JOIN chart c on (p.income_accno_id = c.id)
1027                  $dpt_join
1028         -- use transdate from subwhere
1029                  WHERE 1 = 1 $subwhere
1030                  AND c.category = 'I'
1031                  $dpt_where
1032                  $project
1033                  GROUP BY c.accno, c.description, c.category
1034
1035         UNION ALL
1036        
1037                  SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount,
1038                  c.description AS description, c.category
1039                  FROM invoice ac
1040                  JOIN ap a ON (a.id = ac.trans_id)
1041                  JOIN parts p ON (ac.parts_id = p.id)
1042                  JOIN chart c on (p.expense_accno_id = c.id)
1043                  $dpt_join
1044                  WHERE 1 = 1 $subwhere
1045                  AND c.category = 'E'
1046                  $dpt_where
1047                  $project
1048                  GROUP BY c.accno, c.description, c.category
1049                  |;
1050
1051       }
1052     }
1053   }
1054
1055   my @accno;
1056   my $accno;
1057   my $ref;
1058
1059   my $sth = $dbh->prepare($query);
1060   $sth->execute || $form->dberror($query);
1061
1062   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1063
1064     if ($ref->{category} eq 'C') {
1065       $ref->{category} = 'A';
1066     }
1067
1068     # get last heading account
1069     @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
1070     $accno = pop @accno;
1071     if ($accno) {
1072       if ($last_period) {
1073         $form->{ $ref->{category} }{$accno}{last} += $ref->{amount};
1074       } else {
1075         $form->{ $ref->{category} }{$accno}{this} += $ref->{amount};
1076       }
1077     }
1078
1079     $form->{ $ref->{category} }{ $ref->{accno} }{accno}       = $ref->{accno};
1080     $form->{ $ref->{category} }{ $ref->{accno} }{description} =
1081       $ref->{description};
1082     $form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "A";
1083
1084     if ($last_period) {
1085       $form->{ $ref->{category} }{ $ref->{accno} }{last} += $ref->{amount};
1086     } else {
1087       $form->{ $ref->{category} }{ $ref->{accno} }{this} += $ref->{amount};
1088     }
1089   }
1090   $sth->finish;
1091
1092   # remove accounts with zero balance
1093   foreach $category (@{$categories}) {
1094     foreach $accno (keys %{ $form->{$category} }) {
1095       $form->{$category}{$accno}{last} =
1096         $form->round_amount($form->{$category}{$accno}{last},
1097                             $form->{decimalplaces});
1098       $form->{$category}{$accno}{this} =
1099         $form->round_amount($form->{$category}{$accno}{this},
1100                             $form->{decimalplaces});
1101
1102       delete $form->{$category}{$accno}
1103         if (   $form->{$category}{$accno}{this} == 0
1104             && $form->{$category}{$accno}{last} == 0);
1105     }
1106   }
1107
1108   $main::lxdebug->leave_sub();
1109 }
1110
1111 sub get_accounts_g {
1112   $main::lxdebug->enter_sub();
1113
1114   my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_;
1115
1116   my ($null, $department_id) = split /--/, $form->{department};
1117
1118   my $query;
1119   my $dpt_where;
1120   my $dpt_join;
1121   my $project;
1122   my $where    = "1 = 1";
1123   my $glwhere  = "";
1124   my $subwhere = "";
1125   my $item;
1126
1127   if ($fromdate) {
1128     if ($form->{method} eq 'cash') {
1129       $subwhere .= " AND transdate >= '$fromdate'";
1130       $glwhere = " AND ac.transdate >= '$fromdate'";
1131     } else {
1132       $where .= " AND ac.transdate >= '$fromdate'";
1133     }
1134   }
1135
1136   if ($todate) {
1137     $where    .= " AND ac.transdate <= '$todate'";
1138     $subwhere .= " AND transdate <= '$todate'";
1139   }
1140
1141   if ($department_id) {
1142     $dpt_join = qq|
1143                JOIN department t ON (a.department_id = t.id)
1144                   |;
1145     $dpt_where = qq|
1146                AND t.id = $department_id
1147                    |;
1148   }
1149
1150   if ($form->{project_id}) {
1151     $project = qq|
1152                  AND ac.project_id = $form->{project_id}
1153                  |;
1154   }
1155
1156   if ($form->{method} eq 'cash') {
1157
1158     $query = qq|
1159         
1160                  SELECT sum(ac.amount) AS amount,
1161                  c.$category
1162                  FROM acc_trans ac
1163                  JOIN chart c ON (c.id = ac.chart_id)
1164                  JOIN ar a ON (a.id = ac.trans_id)
1165                  $dpt_join
1166                  WHERE $where
1167                  $dpt_where
1168                  AND ac.trans_id IN
1169                    (
1170                      SELECT trans_id
1171                      FROM acc_trans
1172                      JOIN chart ON (chart_id = id)
1173                      WHERE link LIKE '%AR_paid%'
1174                      $subwhere
1175                    )
1176                      
1177                  $project
1178                  GROUP BY c.$category
1179                  
1180         UNION
1181         
1182                  SELECT sum(ac.amount) AS amount,
1183                  c.$category
1184                  FROM acc_trans ac
1185                  JOIN chart c ON (c.id = ac.chart_id)
1186                  JOIN ap a ON (a.id = ac.trans_id)
1187                  $dpt_join
1188                  WHERE $where
1189                  $dpt_where
1190                  AND ac.trans_id IN
1191                    (
1192                      SELECT trans_id
1193                      FROM acc_trans
1194                      JOIN chart ON (chart_id = id)
1195                      WHERE link LIKE '%AP_paid%'
1196                      $subwhere
1197                    )
1198                      
1199                  $project
1200                  GROUP BY c.$category
1201                  
1202         UNION
1203
1204                  SELECT sum(ac.amount) AS amount,
1205                  c.$category
1206                  FROM acc_trans ac
1207                  JOIN chart c ON (c.id = ac.chart_id)
1208                  JOIN gl a ON (a.id = ac.trans_id)
1209                  $dpt_join
1210                  WHERE $where
1211                  $glwhere
1212                  $dpt_from
1213                  AND NOT (c.link = 'AR' OR c.link = 'AP')
1214                  $project
1215                  GROUP BY c.$category
1216                  |;
1217
1218     if ($form->{project_id}) {
1219
1220       $query .= qq|
1221           
1222          UNION
1223        
1224                  SELECT SUM(ac.sellprice * ac.qty) AS amount,
1225                  c.$category
1226                  FROM invoice ac
1227                  JOIN ar a ON (a.id = ac.trans_id)
1228                  JOIN parts p ON (ac.parts_id = p.id)
1229                  JOIN chart c on (p.income_accno_id = c.id)
1230                  $dpt_join
1231         -- use transdate from subwhere
1232                  WHERE 1 = 1 $subwhere
1233                  AND c.category = 'I'
1234                  $dpt_where
1235                  AND ac.trans_id IN
1236                    (
1237                      SELECT trans_id
1238                      FROM acc_trans
1239                      JOIN chart ON (chart_id = id)
1240                      WHERE link LIKE '%AR_paid%'
1241                      $subwhere
1242                    )
1243
1244                  $project
1245                  GROUP BY c.$category
1246
1247          UNION
1248        
1249                  SELECT SUM(ac.sellprice) AS amount,
1250                  c.$category
1251                  FROM invoice ac
1252                  JOIN ap a ON (a.id = ac.trans_id)
1253                  JOIN parts p ON (ac.parts_id = p.id)
1254                  JOIN chart c on (p.expense_accno_id = c.id)
1255                  $dpt_join
1256                  WHERE 1 = 1 $subwhere
1257                  AND c.category = 'E'
1258                  $dpt_where
1259                  AND ac.trans_id IN
1260                    (
1261                      SELECT trans_id
1262                      FROM acc_trans
1263                      JOIN chart ON (chart_id = id)
1264                      WHERE link LIKE '%AP_paid%'
1265                      $subwhere
1266                    )
1267
1268                  $project
1269                  GROUP BY c.$category
1270                  |;
1271     }
1272
1273   } else {
1274
1275     if ($department_id) {
1276       $dpt_join = qq|
1277               JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
1278               |;
1279       $dpt_where = qq|
1280                AND t.department_id = $department_id
1281               |;
1282     }
1283
1284     $query = qq|
1285       
1286                  SELECT sum(ac.amount) AS amount,
1287                  c.$category
1288                  FROM acc_trans ac
1289                  JOIN chart c ON (c.id = ac.chart_id)
1290                  $dpt_join
1291                  WHERE $where
1292                  $dpt_where
1293                  $project
1294                  GROUP BY c.$category
1295                  |;
1296
1297     if ($form->{project_id}) {
1298
1299       $query .= qq|
1300           
1301         UNION
1302        
1303                  SELECT SUM(ac.sellprice * ac.qty) AS amount,
1304                  c.$category
1305                  FROM invoice ac
1306                  JOIN ar a ON (a.id = ac.trans_id)
1307                  JOIN parts p ON (ac.parts_id = p.id)
1308                  JOIN chart c on (p.income_accno_id = c.id)
1309                  $dpt_join
1310         -- use transdate from subwhere
1311                  WHERE 1 = 1 $subwhere
1312                  AND c.category = 'I'
1313                  $dpt_where
1314                  $project
1315                  GROUP BY c.$category
1316
1317         UNION
1318        
1319                  SELECT SUM(ac.sellprice * ac.qty) * -1 AS amount,
1320                  c.$category
1321                  FROM invoice ac
1322                  JOIN ap a ON (a.id = ac.trans_id)
1323                  JOIN parts p ON (ac.parts_id = p.id)
1324                  JOIN chart c on (p.expense_accno_id = c.id)
1325                  $dpt_join
1326                  WHERE 1 = 1 $subwhere
1327                  AND c.category = 'E'
1328                  $dpt_where
1329                  $project
1330                  GROUP BY c.$category
1331                  |;
1332
1333     }
1334   }
1335
1336   my @accno;
1337   my $accno;
1338   my $ref;
1339
1340   my $sth = $dbh->prepare($query);
1341   $sth->execute || $form->dberror($query);
1342
1343   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1344     if ($ref->{amount} < 0) {
1345       $ref->{amount} *= -1;
1346     }
1347     if ($category eq "pos_bwa") {
1348       if ($last_period) {
1349         $form->{ $ref->{$category} }{kumm} += $ref->{amount};
1350       } else {
1351         $form->{ $ref->{$category} }{jetzt} += $ref->{amount};
1352       }
1353     } else {
1354       $form->{ $ref->{$category} } += $ref->{amount};
1355     }
1356   }
1357   $sth->finish;
1358
1359   $main::lxdebug->leave_sub();
1360 }
1361
1362 sub trial_balance {
1363   $main::lxdebug->enter_sub();
1364
1365   my ($self, $myconfig, $form) = @_;
1366
1367   my $dbh = $form->dbconnect($myconfig);
1368
1369   my ($query, $sth, $ref);
1370   my %balance = ();
1371   my %trb     = ();
1372   my ($null, $department_id) = split /--/, $form->{department};
1373   my @headingaccounts = ();
1374   my $dpt_where;
1375   my $dpt_join;
1376   my $project;
1377
1378   my $where    = "1 = 1";
1379   my $invwhere = $where;
1380
1381   if ($department_id) {
1382     $dpt_join = qq|
1383                 JOIN dpt_trans t ON (ac.trans_id = t.trans_id)
1384                   |;
1385     $dpt_where = qq|
1386                 AND t.department_id = $department_id
1387                 |;
1388   }
1389
1390   # project_id only applies to getting transactions
1391   # it has nothing to do with a trial balance
1392   # but we use the same function to collect information
1393
1394   if ($form->{project_id}) {
1395     $project = qq|
1396                 AND ac.project_id = $form->{project_id}
1397                 |;
1398   }
1399
1400   # get beginning balances
1401   if ($form->{fromdate}) {
1402
1403     if ($form->{accounttype} eq 'gifi') {
1404
1405       $query = qq|SELECT g.accno, c.category, SUM(ac.amount) AS amount,
1406                   g.description
1407                   FROM acc_trans ac
1408                   JOIN chart c ON (ac.chart_id = c.id)
1409                   JOIN gifi g ON (c.gifi_accno = g.accno)
1410                   $dpt_join
1411                   WHERE ac.transdate < '$form->{fromdate}'
1412                   $dpt_where
1413                   $project
1414                   GROUP BY g.accno, c.category, g.description
1415                   |;
1416
1417     } else {
1418
1419       $query = qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount,
1420                   c.description
1421                   FROM acc_trans ac
1422                   JOIN chart c ON (ac.chart_id = c.id)
1423                   $dpt_join
1424                   WHERE ac.transdate < '$form->{fromdate}'
1425                   $dpt_where
1426                   $project
1427                   GROUP BY c.accno, c.category, c.description
1428                   |;
1429
1430     }
1431
1432     $sth = $dbh->prepare($query);
1433     $sth->execute || $form->dberror($query);
1434
1435     while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1436       $balance{ $ref->{accno} } = $ref->{amount};
1437
1438       if ($ref->{amount} != 0 && $form->{all_accounts}) {
1439         $trb{ $ref->{accno} }{description} = $ref->{description};
1440         $trb{ $ref->{accno} }{charttype}   = 'A';
1441         $trb{ $ref->{accno} }{category}    = $ref->{category};
1442       }
1443
1444     }
1445     $sth->finish;
1446
1447   }
1448
1449   # get headings
1450   $query = qq|SELECT c.accno, c.description, c.category
1451               FROM chart c
1452               WHERE c.charttype = 'H'
1453               ORDER by c.accno|;
1454
1455   if ($form->{accounttype} eq 'gifi') {
1456     $query = qq|SELECT g.accno, g.description, c.category
1457                 FROM gifi g
1458                 JOIN chart c ON (c.gifi_accno = g.accno)
1459                 WHERE c.charttype = 'H'
1460                 ORDER BY g.accno|;
1461   }
1462
1463   $sth = $dbh->prepare($query);
1464   $sth->execute || $form->dberror($query);
1465
1466   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1467     $trb{ $ref->{accno} }{description} = $ref->{description};
1468     $trb{ $ref->{accno} }{charttype}   = 'H';
1469     $trb{ $ref->{accno} }{category}    = $ref->{category};
1470
1471     push @headingaccounts, $ref->{accno};
1472   }
1473
1474   $sth->finish;
1475
1476   $where = " 1 = 1 ";
1477
1478   if ($form->{fromdate} || $form->{todate}) {
1479     if ($form->{fromdate}) {
1480       $tofrom   .= " AND ac.transdate >= '$form->{fromdate}'";
1481       $subwhere .= " AND transdate >= '$form->{fromdate}'";
1482       $invwhere .= " AND a.transdate >= '$form->{fromdate}'";
1483       $glwhere = " AND ac.transdate >= '$form->{fromdate}'";
1484     }
1485     if ($form->{todate}) {
1486       $tofrom   .= " AND ac.transdate <= '$form->{todate}'";
1487       $invwhere .= " AND a.transdate <= '$form->{todate}'";
1488       $subwhere .= " AND transdate <= '$form->{todate}'";
1489       $glwhere  .= " AND ac.transdate <= '$form->{todate}'";
1490     }
1491   }
1492   if ($form->{eur}) {
1493     $where .= qq| AND ((ac.trans_id in (SELECT id from ar)
1494                   AND ac.trans_id IN
1495                    (
1496                      SELECT trans_id
1497                      FROM acc_trans
1498                      JOIN chart ON (chart_id = id)
1499                      WHERE link LIKE '%AR_paid%'
1500                      $subwhere
1501                    )) OR (ac.trans_id in (SELECT id from ap)
1502                    AND ac.trans_id IN
1503                    (
1504                      SELECT trans_id
1505                      FROM acc_trans
1506                      JOIN chart ON (chart_id = id)
1507                      WHERE link LIKE '%AP_paid%'
1508                      $subwhere
1509                    )) OR (ac.trans_id in (SELECT id from gl)
1510                    $glwhere))|;
1511   } else {
1512     $where .= $tofrom;
1513   }
1514
1515   if ($form->{accounttype} eq 'gifi') {
1516
1517     $query = qq|SELECT g.accno, g.description, c.category,
1518                 SUM(ac.amount) AS amount
1519                 FROM acc_trans ac
1520                 JOIN chart c ON (c.id = ac.chart_id)
1521                 JOIN gifi g ON (c.gifi_accno = g.accno)
1522                 $dpt_join
1523                 WHERE $where
1524                 $dpt_where
1525                 $project
1526                 GROUP BY g.accno, g.description, c.category
1527                 |;
1528
1529     if ($form->{project_id}) {
1530
1531       $query .= qq|
1532
1533         -- add project transactions from invoice
1534         
1535         UNION ALL
1536         
1537                 SELECT g.accno, g.description, c.category,
1538                 SUM(ac.sellprice * ac.qty) AS amount
1539                 FROM invoice ac
1540                 JOIN ar a ON (ac.trans_id = a.id)
1541                 JOIN parts p ON (ac.parts_id = p.id)
1542                 JOIN chart c ON (p.income_accno_id = c.id)
1543                 JOIN gifi g ON (c.gifi_accno = g.accno)
1544                 $dpt_join
1545                 WHERE $invwhere
1546                 $dpt_where
1547                 $project
1548                 GROUP BY g.accno, g.description, c.category
1549
1550         UNION ALL
1551         
1552                 SELECT g.accno, g.description, c.category,
1553                 SUM(ac.sellprice * ac.qty) * -1 AS amount
1554                 FROM invoice ac
1555                 JOIN ap a ON (ac.trans_id = a.id)
1556                 JOIN parts p ON (ac.parts_id = p.id)
1557                 JOIN chart c ON (p.expense_accno_id = c.id)
1558                 JOIN gifi g ON (c.gifi_accno = g.accno)
1559                 $dpt_join
1560                 WHERE $invwhere
1561                 $dpt_where
1562                 $project
1563                 GROUP BY g.accno, g.description, c.category
1564                 |;
1565     }
1566
1567     $query .= qq|
1568                 ORDER BY accno|;
1569
1570   } else {
1571
1572     $query = qq|SELECT c.accno, c.description, c.category,
1573                 SUM(ac.amount) AS amount
1574                 FROM acc_trans ac
1575                 JOIN chart c ON (c.id = ac.chart_id)
1576                 $dpt_join
1577                 WHERE $where
1578                 $dpt_where
1579                 $project
1580                 GROUP BY c.accno, c.description, c.category
1581                 |;
1582
1583     if ($form->{project_id}) {
1584
1585       $query .= qq|
1586
1587         -- add project transactions from invoice
1588         
1589         UNION ALL
1590         
1591                 SELECT c.accno, c.description, c.category,
1592                 SUM(ac.sellprice * ac.qty) AS amount
1593                 FROM invoice ac
1594                 JOIN ar a ON (ac.trans_id = a.id)
1595                 JOIN parts p ON (ac.parts_id = p.id)
1596                 JOIN chart c ON (p.income_accno_id = c.id)
1597                 $dpt_join
1598                 WHERE $invwhere
1599                 $dpt_where
1600                 $project
1601                 GROUP BY c.accno, c.description, c.category
1602
1603         UNION ALL
1604         
1605                 SELECT c.accno, c.description, c.category,
1606                 SUM(ac.sellprice * ac.qty) * -1 AS amount
1607                 FROM invoice ac
1608                 JOIN ap a ON (ac.trans_id = a.id)
1609                 JOIN parts p ON (ac.parts_id = p.id)
1610                 JOIN chart c ON (p.expense_accno_id = c.id)
1611                 $dpt_join
1612                 WHERE $invwhere
1613                 $dpt_where
1614                 $project
1615                 GROUP BY c.accno, c.description, c.category
1616                 |;
1617     }
1618
1619     $query .= qq|
1620                 ORDER BY accno|;
1621
1622   }
1623
1624   $sth = $dbh->prepare($query);
1625   $sth->execute || $form->dberror($query);
1626
1627   # prepare query for each account
1628   $query = qq|SELECT (SELECT SUM(ac.amount) * -1
1629               FROM acc_trans ac
1630               JOIN chart c ON (c.id = ac.chart_id)
1631               $dpt_join
1632               WHERE $where
1633               $dpt_where
1634               $project
1635               AND ac.amount < 0
1636               AND c.accno = ?) AS debit,
1637               
1638              (SELECT SUM(ac.amount)
1639               FROM acc_trans ac
1640               JOIN chart c ON (c.id = ac.chart_id)
1641               $dpt_join
1642               WHERE $where
1643               $dpt_where
1644               $project
1645               AND ac.amount > 0
1646               AND c.accno = ?) AS credit
1647               |;
1648
1649   if ($form->{accounttype} eq 'gifi') {
1650
1651     $query = qq|SELECT (SELECT SUM(ac.amount) * -1
1652                 FROM acc_trans ac
1653                 JOIN chart c ON (c.id = ac.chart_id)
1654                 $dpt_join
1655                 WHERE $where
1656                 $dpt_where
1657                 $project
1658                 AND ac.amount < 0
1659                 AND c.gifi_accno = ?) AS debit,
1660                 
1661                (SELECT SUM(ac.amount)
1662                 FROM acc_trans ac
1663                 JOIN chart c ON (c.id = ac.chart_id)
1664                 $dpt_join
1665                 WHERE $where
1666                 $dpt_where
1667                 $project
1668                 AND ac.amount > 0
1669                 AND c.gifi_accno = ?) AS credit|;
1670
1671   }
1672
1673   $drcr = $dbh->prepare($query);
1674
1675   if ($form->{project_id}) {
1676
1677     # prepare query for each account
1678     $query = qq|SELECT (SELECT SUM(ac.sellprice * ac.qty) * -1
1679               FROM invoice ac
1680               JOIN parts p ON (ac.parts_id = p.id)
1681               JOIN ap a ON (ac.trans_id = a.id)
1682               JOIN chart c ON (p.expense_accno_id = c.id)
1683               $dpt_join
1684               WHERE $invwhere
1685               $dpt_where
1686               $project
1687               AND c.accno = ?) AS debit,
1688               
1689              (SELECT SUM(ac.sellprice * ac.qty)
1690               FROM invoice ac
1691               JOIN parts p ON (ac.parts_id = p.id)
1692               JOIN ar a ON (ac.trans_id = a.id)
1693               JOIN chart c ON (p.income_accno_id = c.id)
1694               $dpt_join
1695               WHERE $invwhere
1696               $dpt_where
1697               $project
1698               AND c.accno = ?) AS credit
1699               |;
1700
1701     $project_drcr = $dbh->prepare($query);
1702
1703   }
1704
1705   # calculate the debit and credit in the period
1706   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1707     $trb{ $ref->{accno} }{description} = $ref->{description};
1708     $trb{ $ref->{accno} }{charttype}   = 'A';
1709     $trb{ $ref->{accno} }{category}    = $ref->{category};
1710     $trb{ $ref->{accno} }{amount} += $ref->{amount};
1711   }
1712   $sth->finish;
1713
1714   my ($debit, $credit);
1715
1716   foreach my $accno (sort keys %trb) {
1717     $ref = ();
1718
1719     $ref->{accno} = $accno;
1720     map { $ref->{$_} = $trb{$accno}{$_} }
1721       qw(description category charttype amount);
1722
1723     $ref->{balance} = $form->round_amount($balance{ $ref->{accno} }, 2);
1724
1725     if ($trb{$accno}{charttype} eq 'A') {
1726
1727       # get DR/CR
1728       $drcr->execute($ref->{accno}, $ref->{accno}) || $form->dberror($query);
1729
1730       ($debit, $credit) = (0, 0);
1731       while (($debit, $credit) = $drcr->fetchrow_array) {
1732         $ref->{debit}  += $debit;
1733         $ref->{credit} += $credit;
1734       }
1735       $drcr->finish;
1736
1737       if ($form->{project_id}) {
1738
1739         # get DR/CR
1740         $project_drcr->execute($ref->{accno}, $ref->{accno})
1741           || $form->dberror($query);
1742
1743         ($debit, $credit) = (0, 0);
1744         while (($debit, $credit) = $project_drcr->fetchrow_array) {
1745           $ref->{debit}  += $debit;
1746           $ref->{credit} += $credit;
1747         }
1748         $project_drcr->finish;
1749       }
1750
1751       $ref->{debit}  = $form->round_amount($ref->{debit},  2);
1752       $ref->{credit} = $form->round_amount($ref->{credit}, 2);
1753
1754     }
1755
1756     # add subtotal
1757     @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
1758     $accno = pop @accno;
1759     if ($accno) {
1760       $trb{$accno}{debit}  += $ref->{debit};
1761       $trb{$accno}{credit} += $ref->{credit};
1762     }
1763
1764     push @{ $form->{TB} }, $ref;
1765
1766   }
1767
1768   $dbh->disconnect;
1769
1770   # debits and credits for headings
1771   foreach $accno (@headingaccounts) {
1772     foreach $ref (@{ $form->{TB} }) {
1773       if ($accno eq $ref->{accno}) {
1774         $ref->{debit}  = $trb{$accno}{debit};
1775         $ref->{credit} = $trb{$accno}{credit};
1776       }
1777     }
1778   }
1779
1780   $main::lxdebug->leave_sub();
1781 }
1782
1783 sub aging {
1784   $main::lxdebug->enter_sub();
1785
1786   my ($self, $myconfig, $form) = @_;
1787
1788   # connect to database
1789   my $dbh     = $form->dbconnect($myconfig);
1790   my $invoice = ($form->{arap} eq 'ar') ? 'is' : 'ir';
1791
1792   $form->{todate} = $form->current_date($myconfig) unless ($form->{todate});
1793
1794   my $where = "1 = 1";
1795   my ($name, $null);
1796
1797   if ($form->{"$form->{ct}_id"}) {
1798     $where .= qq| AND ct.id = $form->{"$form->{ct}_id"}|;
1799   } else {
1800     if ($form->{ $form->{ct} }) {
1801       $name = $form->like(lc $form->{ $form->{ct} });
1802       $where .= qq| AND lower(ct.name) LIKE '$name'| if $form->{ $form->{ct} };
1803     }
1804   }
1805
1806   my $dpt_join;
1807   if ($form->{department}) {
1808     ($null, $department_id) = split /--/, $form->{department};
1809     $dpt_join = qq|
1810                JOIN department d ON (a.department_id = d.id)
1811                   |;
1812
1813     $where .= qq| AND a.department_id = $department_id|;
1814   }
1815
1816   # select outstanding vendors or customers, depends on $ct
1817   my $query = qq|SELECT DISTINCT ct.id, ct.name
1818                  FROM $form->{ct} ct, $form->{arap} a
1819                  $dpt_join
1820                  WHERE $where
1821                  AND a.$form->{ct}_id = ct.id
1822                  AND a.paid != a.amount
1823                  AND (a.transdate <= '$form->{todate}')
1824                  ORDER BY ct.name|;
1825
1826   my $sth = $dbh->prepare($query);
1827   $sth->execute || $form->dberror;
1828
1829   my $buysell = ($form->{arap} eq 'ar') ? 'buy' : 'sell';
1830
1831   # for each company that has some stuff outstanding
1832   while (my ($id) = $sth->fetchrow_array) {
1833
1834     $query = qq|
1835
1836 -- between 0-30 days
1837
1838         SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1839         street, zipcode, city, country, contact, email,
1840         phone as customerphone, fax as customerfax, $form->{ct}number,
1841         "invnumber", "transdate",
1842         (amount - paid) as "c0", 0.00 as "c30", 0.00 as "c60", 0.00 as "c90",
1843         "duedate", invoice, $form->{arap}.id,
1844           (SELECT $buysell FROM exchangerate
1845            WHERE $form->{arap}.curr = exchangerate.curr
1846            AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1847   FROM $form->{arap}, $form->{ct} 
1848         WHERE paid != amount
1849         AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
1850         AND $form->{ct}.id = $id
1851         AND (
1852                 transdate <= (date '$form->{todate}' - interval '0 days') 
1853                 AND transdate >= (date '$form->{todate}' - interval '30 days')
1854             )
1855         
1856         UNION
1857
1858 -- between 31-60 days
1859
1860         SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1861         street, zipcode, city, country, contact, email,
1862         phone as customerphone, fax as customerfax, $form->{ct}number,
1863         "invnumber", "transdate", 
1864         0.00 as "c0", (amount - paid) as "c30", 0.00 as "c60", 0.00 as "c90",
1865         "duedate", invoice, $form->{arap}.id,
1866           (SELECT $buysell FROM exchangerate
1867            WHERE $form->{arap}.curr = exchangerate.curr
1868            AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1869   FROM $form->{arap}, $form->{ct}
1870         WHERE paid != amount 
1871         AND $form->{arap}.$form->{ct}_id = $form->{ct}.id 
1872         AND $form->{ct}.id = $id
1873         AND (
1874                 transdate < (date '$form->{todate}' - interval '30 days') 
1875                 AND transdate >= (date '$form->{todate}' - interval '60 days')
1876                 )
1877
1878         UNION
1879   
1880 -- between 61-90 days
1881
1882         SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1883         street, zipcode, city, country, contact, email,
1884         phone as customerphone, fax as customerfax, $form->{ct}number,
1885         "invnumber", "transdate", 
1886         0.00 as "c0", 0.00 as "c30", (amount - paid) as "c60", 0.00 as "c90",
1887         "duedate", invoice, $form->{arap}.id,
1888           (SELECT $buysell FROM exchangerate
1889            WHERE $form->{arap}.curr = exchangerate.curr
1890            AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1891         FROM $form->{arap}, $form->{ct} 
1892         WHERE paid != amount
1893         AND $form->{arap}.$form->{ct}_id = $form->{ct}.id 
1894         AND $form->{ct}.id = $id
1895         AND (
1896                 transdate < (date '$form->{todate}' - interval '60 days') 
1897                 AND transdate >= (date '$form->{todate}' - interval '90 days')
1898                 )
1899
1900         UNION
1901   
1902 -- over 90 days
1903
1904         SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1905         street, zipcode, city, country, contact, email,
1906         phone as customerphone, fax as customerfax, $form->{ct}number,
1907         "invnumber", "transdate", 
1908         0.00 as "c0", 0.00 as "c30", 0.00 as "c60", (amount - paid) as "c90",
1909         "duedate", invoice, $form->{arap}.id,
1910           (SELECT $buysell FROM exchangerate
1911            WHERE $form->{arap}.curr = exchangerate.curr
1912            AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1913         FROM $form->{arap}, $form->{ct} 
1914         WHERE paid != amount
1915         AND $form->{arap}.$form->{ct}_id = $form->{ct}.id 
1916         AND $form->{ct}.id = $id
1917         AND transdate < (date '$form->{todate}' - interval '90 days') 
1918
1919         ORDER BY
1920   
1921   ctid, transdate, invnumber
1922   
1923                 |;
1924
1925     my $sth = $dbh->prepare($query);
1926     $sth->execute || $form->dberror;
1927
1928     while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1929       $ref->{module} = ($ref->{invoice}) ? $invoice : $form->{arap};
1930       $ref->{exchangerate} = 1 unless $ref->{exchangerate};
1931       push @{ $form->{AG} }, $ref;
1932     }
1933
1934     $sth->finish;
1935
1936   }
1937
1938   $sth->finish;
1939
1940   # disconnect
1941   $dbh->disconnect;
1942
1943   $main::lxdebug->leave_sub();
1944 }
1945
1946 sub get_customer {
1947   $main::lxdebug->enter_sub();
1948
1949   my ($self, $myconfig, $form) = @_;
1950
1951   # connect to database
1952   my $dbh = $form->dbconnect($myconfig);
1953
1954   my $query = qq|SELECT ct.name, ct.email, ct.cc, ct.bcc
1955                  FROM $form->{ct} ct
1956                  WHERE ct.id = $form->{"$form->{ct}_id"}|;
1957   my $sth = $dbh->prepare($query);
1958   $sth->execute || $form->dberror;
1959
1960   ($form->{ $form->{ct} }, $form->{email}, $form->{cc}, $form->{bcc}) =
1961     $sth->fetchrow_array;
1962   $sth->finish;
1963   $dbh->disconnect;
1964
1965   $main::lxdebug->leave_sub();
1966 }
1967
1968 sub get_taxaccounts {
1969   $main::lxdebug->enter_sub();
1970
1971   my ($self, $myconfig, $form) = @_;
1972
1973   # connect to database
1974   my $dbh = $form->dbconnect($myconfig);
1975
1976   # get tax accounts
1977   my $query = qq|SELECT c.accno, c.description, t.rate
1978                  FROM chart c, tax t
1979                  WHERE c.link LIKE '%CT_tax%'
1980                  AND c.id = t.chart_id
1981                  ORDER BY c.accno|;
1982   my $sth = $dbh->prepare($query);
1983   $sth->execute || $form->dberror;
1984
1985   my $ref = ();
1986   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1987     push @{ $form->{taxaccounts} }, $ref;
1988   }
1989   $sth->finish;
1990
1991   # get gifi tax accounts
1992   my $query = qq|SELECT DISTINCT ON (g.accno) g.accno, g.description,
1993                  sum(t.rate) AS rate
1994                  FROM gifi g, chart c, tax t
1995                  WHERE g.accno = c.gifi_accno
1996                  AND c.id = t.chart_id
1997                  AND c.link LIKE '%CT_tax%'
1998                  GROUP BY g.accno, g.description
1999                  ORDER BY accno|;
2000   my $sth = $dbh->prepare($query);
2001   $sth->execute || $form->dberror;
2002
2003   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
2004     push @{ $form->{gifi_taxaccounts} }, $ref;
2005   }
2006   $sth->finish;
2007
2008   $dbh->disconnect;
2009
2010   $main::lxdebug->leave_sub();
2011 }
2012
2013 sub tax_report {
2014   $main::lxdebug->enter_sub();
2015
2016   my ($self, $myconfig, $form) = @_;
2017
2018   # connect to database
2019   my $dbh = $form->dbconnect($myconfig);
2020
2021   my ($null, $department_id) = split /--/, $form->{department};
2022
2023   # build WHERE
2024   my $where = "1 = 1";
2025
2026   if ($department_id) {
2027     $where .= qq|
2028                  AND a.department_id = $department_id
2029                 |;
2030   }
2031
2032   my ($accno, $rate);
2033
2034   if ($form->{accno}) {
2035     if ($form->{accno} =~ /^gifi_/) {
2036       ($null, $accno) = split /_/, $form->{accno};
2037       $rate  = $form->{"$form->{accno}_rate"};
2038       $accno = qq| AND ch.gifi_accno = '$accno'|;
2039     } else {
2040       $accno = $form->{accno};
2041       $rate  = $form->{"$form->{accno}_rate"};
2042       $accno = qq| AND ch.accno = '$accno'|;
2043     }
2044   }
2045   $rate *= 1;
2046
2047   my ($table, $ARAP);
2048
2049   if ($form->{db} eq 'ar') {
2050     $table = "customer";
2051     $ARAP  = "AR";
2052   }
2053   if ($form->{db} eq 'ap') {
2054     $table = "vendor";
2055     $ARAP  = "AP";
2056   }
2057
2058   my $transdate = "a.transdate";
2059
2060   if ($form->{method} eq 'cash') {
2061     $transdate = "a.datepaid";
2062
2063     my $todate =
2064       ($form->{todate}) ? $form->{todate} : $form->current_date($myconfig);
2065
2066     $where .= qq|
2067                  AND ac.trans_id IN
2068                    (
2069                      SELECT trans_id
2070                      FROM acc_trans
2071                      JOIN chart ON (chart_id = id)
2072                      WHERE link LIKE '%${ARAP}_paid%'
2073                      AND transdate <= '$todate'
2074                    )
2075                   |;
2076   }
2077
2078   # if there are any dates construct a where
2079   if ($form->{fromdate} || $form->{todate}) {
2080     if ($form->{fromdate}) {
2081       $where .= " AND $transdate >= '$form->{fromdate}'";
2082     }
2083     if ($form->{todate}) {
2084       $where .= " AND $transdate <= '$form->{todate}'";
2085     }
2086   }
2087
2088   my $ml = ($form->{db} eq 'ar') ? 1 : -1;
2089
2090   my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name));
2091   $sortorder = $form->{sort} unless $sortorder;
2092
2093   $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
2094               a.invnumber, n.name, a.netamount,
2095               ac.amount * $ml AS tax
2096               FROM acc_trans ac
2097             JOIN $form->{db} a ON (a.id = ac.trans_id)
2098             JOIN chart ch ON (ch.id = ac.chart_id)
2099             JOIN $table n ON (n.id = a.${table}_id)
2100               WHERE $where
2101               $accno
2102               AND a.invoice = '0'
2103             UNION
2104               SELECT a.id, '1' AS invoice, $transdate AS transdate,
2105               a.invnumber, n.name, i.sellprice * i.qty AS netamount,
2106               i.sellprice * i.qty * $rate * $ml AS tax
2107               FROM acc_trans ac
2108             JOIN $form->{db} a ON (a.id = ac.trans_id)
2109             JOIN chart ch ON (ch.id = ac.chart_id)
2110             JOIN $table n ON (n.id = a.${table}_id)
2111             JOIN ${table}tax t ON (t.${table}_id = n.id)
2112             JOIN invoice i ON (i.trans_id = a.id)
2113             JOIN partstax p ON (p.parts_id = i.parts_id)
2114               WHERE $where
2115               $accno
2116               AND a.invoice = '1'
2117               ORDER by $sortorder|;
2118
2119   if ($form->{report} =~ /nontaxable/) {
2120
2121     # only gather up non-taxable transactions
2122     $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
2123                 a.invnumber, n.name, a.netamount
2124                 FROM acc_trans ac
2125               JOIN $form->{db} a ON (a.id = ac.trans_id)
2126               JOIN $table n ON (n.id = a.${table}_id)
2127                 WHERE $where
2128                 AND a.invoice = '0'
2129                 AND a.netamount = a.amount
2130               UNION
2131                 SELECT a.id, '1' AS invoice, $transdate AS transdate,
2132                 a.invnumber, n.name, i.sellprice * i.qty AS netamount
2133                 FROM acc_trans ac
2134               JOIN $form->{db} a ON (a.id = ac.trans_id)
2135               JOIN $table n ON (n.id = a.${table}_id)
2136               JOIN invoice i ON (i.trans_id = a.id)
2137                 WHERE $where
2138                 AND a.invoice = '1'
2139                 AND (
2140                   a.${table}_id NOT IN (
2141                         SELECT ${table}_id FROM ${table}tax t (${table}_id)
2142                                        ) OR
2143                   i.parts_id NOT IN (
2144                         SELECT parts_id FROM partstax p (parts_id)
2145                                     )
2146                     )
2147                 GROUP BY a.id, a.invnumber, $transdate, n.name, i.sellprice, i.qty
2148                 ORDER by $sortorder|;
2149   }
2150
2151   my $sth = $dbh->prepare($query);
2152   $sth->execute || $form->dberror($query);
2153
2154   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
2155     push @{ $form->{TR} }, $ref;
2156   }
2157
2158   $sth->finish;
2159   $dbh->disconnect;
2160
2161   $main::lxdebug->leave_sub();
2162 }
2163
2164 sub paymentaccounts {
2165   $main::lxdebug->enter_sub();
2166
2167   my ($self, $myconfig, $form) = @_;
2168
2169   # connect to database, turn AutoCommit off
2170   my $dbh = $form->dbconnect_noauto($myconfig);
2171
2172   my $ARAP = uc $form->{db};
2173
2174   # get A(R|P)_paid accounts
2175   my $query = qq|SELECT c.accno, c.description
2176                  FROM chart c
2177                  WHERE c.link LIKE '%${ARAP}_paid%'|;
2178   my $sth = $dbh->prepare($query);
2179   $sth->execute || $form->dberror($query);
2180
2181   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
2182     push @{ $form->{PR} }, $ref;
2183   }
2184
2185   $sth->finish;
2186   $dbh->disconnect;
2187
2188   $main::lxdebug->leave_sub();
2189 }
2190
2191 sub payments {
2192   $main::lxdebug->enter_sub();
2193
2194   my ($self, $myconfig, $form) = @_;
2195
2196   # connect to database, turn AutoCommit off
2197   my $dbh = $form->dbconnect_noauto($myconfig);
2198
2199   my $ml = 1;
2200   if ($form->{db} eq 'ar') {
2201     $table = 'customer';
2202     $ml    = -1;
2203   }
2204   if ($form->{db} eq 'ap') {
2205     $table = 'vendor';
2206   }
2207
2208   my ($query, $sth);
2209   my $dpt_join;
2210   my $where;
2211
2212   if ($form->{department_id}) {
2213     $dpt_join = qq|
2214                  JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
2215                  |;
2216
2217     $where = qq|
2218                  AND t.department_id = $form->{department_id}
2219                 |;
2220   }
2221
2222   if ($form->{fromdate}) {
2223     $where .= " AND ac.transdate >= '$form->{fromdate}'";
2224   }
2225   if ($form->{todate}) {
2226     $where .= " AND ac.transdate <= '$form->{todate}'";
2227   }
2228   if (!$form->{fx_transaction}) {
2229     $where .= " AND ac.fx_transaction = '0'";
2230   }
2231
2232   my $invnumber;
2233   my $reference;
2234   if ($form->{reference}) {
2235     $reference = $form->like(lc $form->{reference});
2236     $invnumber = " AND lower(a.invnumber) LIKE '$reference'";
2237     $reference = " AND lower(g.reference) LIKE '$reference'";
2238   }
2239   if ($form->{source}) {
2240     my $source = $form->like(lc $form->{source});
2241     $where .= " AND lower(ac.source) LIKE '$source'";
2242   }
2243   if ($form->{memo}) {
2244     my $memo = $form->like(lc $form->{memo});
2245     $where .= " AND lower(ac.memo) LIKE '$memo'";
2246   }
2247
2248   my $sortorder = join ', ',
2249     $form->sort_columns(qw(name invnumber ordnumber transdate source));
2250
2251   # cycle through each id
2252   foreach my $accno (split(/ /, $form->{paymentaccounts})) {
2253
2254     $query = qq|SELECT c.id, c.accno, c.description
2255                 FROM chart c
2256                 WHERE c.accno = '$accno'|;
2257     $sth = $dbh->prepare($query);
2258     $sth->execute || $form->dberror($query);
2259
2260     my $ref = $sth->fetchrow_hashref(NAME_lc);
2261     push @{ $form->{PR} }, $ref;
2262     $sth->finish;
2263
2264     $query = qq|SELECT c.name, a.invnumber, a.ordnumber,
2265                 ac.transdate, ac.amount * $ml AS paid, ac.source,
2266                 a.invoice, a.id, ac.memo, '$form->{db}' AS module
2267                 FROM acc_trans ac
2268                 JOIN $form->{db} a ON (ac.trans_id = a.id)
2269                 JOIN $table c ON (c.id = a.${table}_id)
2270                 $dpt_join
2271                 WHERE ac.chart_id = $ref->{id}
2272                 $where
2273                 $invnumber
2274                 
2275         UNION
2276                 SELECT g.description, g.reference, NULL AS ordnumber,
2277                 ac.transdate, ac.amount * $ml AS paid, ac.source,
2278                 '0' as invoice, g.id, ac.memo, 'gl' AS module
2279                 FROM acc_trans ac
2280                 JOIN gl g ON (g.id = ac.trans_id)
2281                 $dpt_join
2282                 WHERE ac.chart_id = $ref->{id}
2283                 $where
2284                 $reference
2285                 AND (ac.amount * $ml) > 0
2286                 ORDER BY $sortorder|;
2287
2288     $sth = $dbh->prepare($query);
2289     $sth->execute || $form->dberror($query);
2290
2291     while (my $pr = $sth->fetchrow_hashref(NAME_lc)) {
2292       push @{ $form->{ $ref->{id} } }, $pr;
2293     }
2294     $sth->finish;
2295
2296   }
2297
2298   $dbh->disconnect;
2299
2300   $main::lxdebug->leave_sub();
2301 }
2302
2303 sub bwa {
2304   $main::lxdebug->enter_sub();
2305
2306   my ($self, $myconfig, $form) = @_;
2307
2308   # connect to database
2309   my $dbh = $form->dbconnect($myconfig);
2310
2311   my $last_period = 0;
2312   my $category    = "pos_bwa";
2313   my @categories  =
2314     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);
2315
2316   $form->{decimalplaces} *= 1;
2317
2318   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
2319                   $form, $category);
2320
2321   # if there are any compare dates
2322   if ($form->{fromdate} || $form->{todate}) {
2323     $last_period = 1;
2324     if ($form->{fromdate}) {
2325       $form->{fromdate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
2326       $year = $1;
2327     } else {
2328       $form->{todate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
2329       $year = $1;
2330     }
2331     $kummfromdate = $form->{comparefromdate};
2332     $kummtodate   = $form->{comparetodate};
2333     &get_accounts_g($dbh, $last_period, $kummfromdate, $kummtodate, $form,
2334                     $category);
2335   }
2336
2337   @periods        = qw(jetzt kumm);
2338   @gesamtleistung = qw(1 2 3);
2339   @gesamtkosten   = qw (10 11 12 13 14 15 16 17 18 19 20);
2340   @ergebnisse     =
2341     qw (rohertrag betriebrohertrag betriebsergebnis neutraleraufwand neutralerertrag ergebnisvorsteuern ergebnis gesamtleistung gesamtkosten);
2342
2343   foreach $key (@periods) {
2344     $form->{ "$key" . "gesamtleistung" } = 0;
2345     $form->{ "$key" . "gesamtkosten" }   = 0;
2346
2347     foreach $category (@categories) {
2348
2349       if (defined($form->{$category}{$key})) {
2350         $form->{"$key$category"} =
2351           $form->format_amount($myconfig,
2352                                $form->round_amount($form->{$category}{$key}, 2
2353                                ));
2354       }
2355     }
2356     foreach $item (@gesamtleistung) {
2357       $form->{ "$key" . "gesamtleistung" } += $form->{$item}{$key};
2358     }
2359     foreach $item (@gesamtkosten) {
2360       $form->{ "$key" . "gesamtkosten" } += $form->{$item}{$key};
2361     }
2362     $form->{ "$key" . "rohertrag" } =
2363       $form->{ "$key" . "gesamtleistung" } - $form->{4}{$key};
2364     $form->{ "$key" . "betriebrohertrag" } =
2365       $form->{ "$key" . "rohertrag" } + $form->{5}{$key};
2366     $form->{ "$key" . "betriebsergebnis" } =
2367       $form->{ "$key" . "betriebrohertrag" } -
2368       $form->{ "$key" . "gesamtkosten" };
2369     $form->{ "$key" . "neutraleraufwand" } =
2370       $form->{30}{$key} + $form->{31}{$key};
2371     $form->{ "$key" . "neutralertrag" } =
2372       $form->{32}{$key} + $form->{33}{$key} + $form->{34}{$key};
2373     $form->{ "$key" . "ergebnisvorsteuern" } =
2374       $form->{ "$key" . "betriebsergebnis" } -
2375       ($form->{ "$key" . "neutraleraufwand" } +
2376        $form->{ "$key" . "neutralertrag" });
2377     $form->{ "$key" . "ergebnis" } =
2378       $form->{ "$key" . "ergebnisvorsteuern" } + $form->{35}{$key};
2379
2380     if ($form->{ "$key" . "gesamtleistung" } > 0) {
2381       foreach $category (@categories) {
2382         if (defined($form->{$category}{$key})) {
2383           $form->{ "$key" . "gl" . "$category" } =
2384             $form->format_amount(
2385                                $myconfig,
2386                                $form->round_amount(
2387                                  ($form->{$category}{$key} /
2388                                     $form->{ "$key" . "gesamtleistung" } * 100
2389                                  ),
2390                                  2
2391                                ));
2392         }
2393       }
2394       foreach $item (@ergebnisse) {
2395         $form->{ "$key" . "gl" . "$item" } =
2396           $form->format_amount($myconfig,
2397                                $form->round_amount(
2398                                  ( $form->{ "$key" . "$item" } /
2399                                      $form->{ "$key" . "gesamtleistung" } * 100
2400                                  ),
2401                                  2
2402                                ));
2403       }
2404     }
2405
2406     if ($form->{ "$key" . "gesamtkosten" } > 0) {
2407       foreach $category (@categories) {
2408         if (defined($form->{$category}{$key})) {
2409           $form->{ "$key" . "gk" . "$category" } =
2410             $form->format_amount($myconfig,
2411                                  $form->round_amount(
2412                                    ($form->{$category}{$key} /
2413                                       $form->{ "$key" . "gesamtkosten" } * 100
2414                                    ),
2415                                    2
2416                                  ));
2417         }
2418       }
2419       foreach $item (@ergebnisse) {
2420         $form->{ "$key" . "gk" . "$item" } =
2421           $form->format_amount($myconfig,
2422                                $form->round_amount(
2423                                    ($form->{ "$key" . "$item" } /
2424                                       $form->{ "$key" . "gesamtkosten" } * 100
2425                                    ),
2426                                    2
2427                                ));
2428       }
2429     }
2430
2431     if ($form->{10}{$key} > 0) {
2432       foreach $category (@categories) {
2433         if (defined($form->{$category}{$key})) {
2434           $form->{ "$key" . "pk" . "$category" } =
2435             $form->format_amount(
2436                       $myconfig,
2437                       $form->round_amount(
2438                         ($form->{$category}{$key} / $form->{10}{$key} * 100), 2
2439                       ));
2440         }
2441       }
2442       foreach $item (@ergebnisse) {
2443         $form->{ "$key" . "pk" . "$item" } =
2444           $form->format_amount($myconfig,
2445                                $form->round_amount(
2446                                                 ($form->{ "$key" . "$item" } /
2447                                                    $form->{10}{$key} * 100
2448                                                 ),
2449                                                 2
2450                                ));
2451       }
2452     }
2453
2454     if ($form->{4}{$key} > 0) {
2455       foreach $category (@categories) {
2456         if (defined($form->{$category}{$key})) {
2457           $form->{ "$key" . "auf" . "$category" } =
2458             $form->format_amount(
2459                        $myconfig,
2460                        $form->round_amount(
2461                          ($form->{$category}{$key} / $form->{4}{$key} * 100), 2
2462                        ));
2463         }
2464       }
2465       foreach $item (@ergebnisse) {
2466         $form->{ "$key" . "auf" . "$item" } =
2467           $form->format_amount($myconfig,
2468                                $form->round_amount(
2469                                                 ($form->{ "$key" . "$item" } /
2470                                                    $form->{4}{$key} * 100
2471                                                 ),
2472                                                 2
2473                                ));
2474       }
2475     }
2476
2477     foreach $item (@ergebnisse) {
2478       $form->{ "$key" . "$item" } =
2479         $form->format_amount($myconfig,
2480                              $form->round_amount($form->{ "$key" . "$item" }, 2
2481                              ));
2482     }
2483
2484   }
2485   $dbh->disconnect;
2486
2487   $main::lxdebug->leave_sub();
2488 }
2489
2490 sub ustva {
2491   $main::lxdebug->enter_sub();
2492
2493   my ($self, $myconfig, $form) = @_;
2494
2495   # connect to database
2496   my $dbh = $form->dbconnect($myconfig);
2497
2498   my $last_period     = 0;
2499   my $category        = "pos_ustva";
2500   my @categories_cent = qw(51r 86r 97r 93r 96 66 43 45 53 62 65 67);
2501   my @categories_euro = qw(48 51 86 91 97 93 94);
2502   $form->{decimalplaces} *= 1;
2503
2504   foreach $item (@categories_cent) {
2505     $form->{"$item"} = 0;
2506   }
2507   foreach $item (@categories_euro) {
2508     $form->{"$item"} = 0;
2509   }
2510
2511   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
2512                   $form, $category);
2513
2514   #   foreach $item (@categories_cent) {
2515   #     if ($form->{$item}{"jetzt"} > 0) {
2516   #             $form->{$item} = $form->{$item}{"jetzt"};
2517   #             delete $form->{$item}{"jetzt"};
2518   #     }
2519   #   }
2520   #   foreach $item (@categories_euro) {
2521   #     if ($form->{$item}{"jetzt"} > 0) {
2522   #             $form->{$item} = $form->{$item}{"jetzt"};
2523   #             delete $form->{$item}{"jetzt"};
2524   #     }  foreach $item (@categories_cent) {
2525   #     if ($form->{$item}{"jetzt"} > 0) {
2526   #             $form->{$item} = $form->{$item}{"jetzt"};
2527   #             delete $form->{$item}{"jetzt"};
2528   #     }
2529   #   }
2530   #   foreach $item (@categories_euro) {
2531   #     if ($form->{$item}{"jetzt"} > 0) {
2532   #             $form->{$item} = $form->{$item}{"jetzt"};
2533   #             delete $form->{$item}{"jetzt"};
2534   #     }
2535   #   }
2536   #
2537   #    }
2538
2539   $form->{"51r"} = $form->{"51"} * 0.16;
2540   $form->{"86r"} = $form->{"86"} * 0.07;
2541   $form->{"97r"} = $form->{"97"} * 0.16;
2542   $form->{"93r"} = $form->{"93"} * 0.07;
2543   $form->{"96"}  = $form->{"94"} * 0.16;
2544   $form->{"43"}  =
2545     $form->{"51r"} + $form->{"86r"} + $form->{"97r"} + $form->{"93r"} +
2546     $form->{"96"};
2547   $form->{"45"} = $form->{"43"};
2548   $form->{"53"} = $form->{"43"};
2549   $form->{"62"} = $form->{"43"} - $form->{"66"};
2550   $form->{"65"} = $form->{"43"} - $form->{"66"};
2551   $form->{"67"} = $form->{"43"} - $form->{"66"};
2552
2553   foreach $item (@categories_cent) {
2554     $form->{$item} =
2555       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
2556   }
2557
2558   foreach $item (@categories_euro) {
2559     $form->{$item} =
2560       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 0));
2561   }
2562
2563   $dbh->disconnect;
2564
2565   $main::lxdebug->leave_sub();
2566 }
2567
2568 sub income_statement {
2569   $main::lxdebug->enter_sub();
2570
2571   my ($self, $myconfig, $form) = @_;
2572
2573   # connect to database
2574   my $dbh = $form->dbconnect($myconfig);
2575
2576   my $last_period          = 0;
2577   my $category             = "pos_eur";
2578   my @categories_einnahmen = qw(1 2 3 4 5 6 7);
2579   my @categories_ausgaben  =
2580     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);
2581
2582   my @ergebnisse = qw(sumeura sumeurb guvsumme);
2583
2584   $form->{decimalplaces} *= 1;
2585
2586   foreach $item (@categories_einnahmen) {
2587     $form->{$item} = 0;
2588   }
2589   foreach $item (@categories_ausgaben) {
2590     $form->{$item} = 0;
2591   }
2592
2593   foreach $item (@ergebnisse) {
2594     $form->{$item} = 0;
2595   }
2596
2597   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
2598                   $form, $category);
2599
2600   foreach $item (@categories_einnahmen) {
2601     $form->{"eur${item}"} =
2602       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
2603     $form->{"sumeura"} += $form->{$item};
2604   }
2605   foreach $item (@categories_ausgaben) {
2606     $form->{"eur${item}"} =
2607       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
2608     $form->{"sumeurb"} += $form->{$item};
2609   }
2610
2611   $form->{"guvsumme"} = $form->{"sumeura"} - $form->{"sumeurb"};
2612
2613   foreach $item (@ergebnisse) {
2614     $form->{$item} =
2615       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
2616   }
2617   $main::lxdebug->leave_sub();
2618 }
2619 1;
2620