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