Der letzte Einkauspreis wurde nicht geladen und daher auch keine MArgenberechnung
[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
984   $drcr = prepare_query($form, $dbh, $q_drcr);
985
986   if ($form->{project_id}) {
987     # prepare query for each account
988     $q_project_drcr =
989       qq|SELECT
990           (SELECT SUM(ac.sellprice * ac.qty) * -1
991            FROM invoice ac
992            JOIN parts p ON (ac.parts_id = p.id)
993            JOIN ap a ON (ac.trans_id = a.id)
994            JOIN chart c ON (p.expense_accno_id = c.id)
995            $dpt_join
996            WHERE $invwhere
997              $dpt_where
998              $project
999            AND c.accno = ?) AS debit,
1000
1001           (SELECT SUM(ac.sellprice * ac.qty)
1002            FROM invoice ac
1003            JOIN parts p ON (ac.parts_id = p.id)
1004            JOIN ar a ON (ac.trans_id = a.id)
1005            JOIN chart c ON (p.income_accno_id = c.id)
1006            $dpt_join
1007            WHERE $invwhere
1008              $dpt_where
1009              $project
1010            AND c.accno = ?) AS credit |;
1011
1012     $project_drcr = prepare_query($form, $dbh, $q_project_drcr);
1013   }
1014
1015   my ($debit, $credit);
1016
1017   foreach my $accno (sort keys %trb) {
1018     $ref = {};
1019
1020     $ref->{accno} = $accno;
1021     map { $ref->{$_} = $trb{$accno}{$_} }
1022       qw(description category charttype amount);
1023
1024     $ref->{balance} = $form->round_amount($balance{ $ref->{accno} }, 2);
1025
1026     if ($trb{$accno}{charttype} eq 'A') {
1027
1028       # get DR/CR
1029       do_statement($form, $drcr, $q_drcr, $ref->{accno}, $ref->{accno});
1030
1031       ($debit, $credit) = (0, 0);
1032       while (($debit, $credit) = $drcr->fetchrow_array) {
1033         $ref->{debit}  += $debit;
1034         $ref->{credit} += $credit;
1035       }
1036       $drcr->finish;
1037
1038       if ($form->{project_id}) {
1039
1040         # get DR/CR
1041         do_statement($form, $project_drcr, $q_project_drcr, $ref->{accno}, $ref->{accno});
1042
1043         ($debit, $credit) = (0, 0);
1044         while (($debit, $credit) = $project_drcr->fetchrow_array) {
1045           $ref->{debit}  += $debit;
1046           $ref->{credit} += $credit;
1047         }
1048         $project_drcr->finish;
1049       }
1050
1051       $ref->{debit}  = $form->round_amount($ref->{debit},  2);
1052       $ref->{credit} = $form->round_amount($ref->{credit}, 2);
1053
1054     }
1055
1056     # add subtotal
1057     @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
1058     $accno = pop @accno;
1059     if ($accno) {
1060       $trb{$accno}{debit}  += $ref->{debit};
1061       $trb{$accno}{credit} += $ref->{credit};
1062     }
1063
1064     push @{ $form->{TB} }, $ref;
1065
1066   }
1067
1068   $dbh->disconnect;
1069
1070   # debits and credits for headings
1071   foreach $accno (@headingaccounts) {
1072     foreach $ref (@{ $form->{TB} }) {
1073       if ($accno eq $ref->{accno}) {
1074         $ref->{debit}  = $trb{$accno}{debit};
1075         $ref->{credit} = $trb{$accno}{credit};
1076       }
1077     }
1078   }
1079
1080   $main::lxdebug->leave_sub();
1081 }
1082
1083 sub get_storno {
1084   $main::lxdebug->enter_sub();
1085   my ($self, $dbh, $form) = @_;
1086   my $arap = $form->{arap} eq "ar" ? "ar" : "ap";
1087   my $query = qq|SELECT invnumber FROM $arap WHERE invnumber LIKE "Storno zu "|;
1088   my $sth =  $dbh->prepare($query);
1089   while(my $ref = $sth->fetchrow_hashref()) {
1090     $ref->{invnumer} =~ s/Storno zu //g;
1091     $form->{storno}{$ref->{invnumber}} = 1;
1092   }
1093   $main::lxdebug->leave_sub();
1094 }
1095
1096 sub aging {
1097   $main::lxdebug->enter_sub();
1098
1099   my ($self, $myconfig, $form) = @_;
1100
1101   # connect to database
1102   my $dbh     = $form->dbconnect($myconfig);
1103
1104   my ($invoice, $arap, $buysell, $ct, $ct_id);
1105
1106   if ($form->{ct} eq "customer") {
1107     $invoice = "is";
1108     $arap = "ar";
1109     $buysell = "buy";
1110     $ct = "customer";
1111   } else {
1112     $invoice = "ir";
1113     $arap = "ap";
1114     $buysell = "sell";
1115     $ct = "vendor";
1116   }
1117   $ct_id = "${ct}_id";
1118
1119   $form->{todate} = $form->current_date($myconfig) unless ($form->{todate});
1120   my $todate = conv_dateq($form->{todate});
1121
1122   my $where = " 1 = 1 ";
1123   my ($name, $null);
1124
1125   if ($form->{$ct_id}) {
1126     $where .= qq| AND (ct.id = | . conv_i($form->{$ct_id}) . qq|)|;
1127   } elsif ($form->{ $form->{ct} }) {
1128     $where .= qq| AND (ct.name ILIKE | . $dbh->quote('%' . $form->{$ct} . '%') . qq|)|;
1129   }
1130
1131   my $dpt_join;
1132   if ($form->{department}) {
1133     ($null, $department_id) = split /--/, $form->{department};
1134     $dpt_join = qq| JOIN department d ON (a.department_id = d.id) |;
1135     $where .= qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|)|;
1136   }
1137
1138   my $q_details = qq|
1139     -- between 0-30 days
1140
1141     SELECT ${ct}.id AS ctid, ${ct}.name,
1142       street, zipcode, city, country, contact, email,
1143       phone as customerphone, fax as customerfax, ${ct}number,
1144       "invnumber", "transdate",
1145       (amount - paid) as "c0", 0.00 as "c30", 0.00 as "c60", 0.00 as "c90",
1146       "duedate", invoice, ${arap}.id,
1147       (SELECT $buysell
1148        FROM exchangerate
1149        WHERE (${arap}.curr = exchangerate.curr)
1150          AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
1151     FROM ${arap}, ${ct}
1152     WHERE (paid != amount)
1153       AND (${arap}.storno IS FALSE)
1154       AND (${arap}.${ct}_id = ${ct}.id)
1155       AND (${ct}.id = ?)
1156       AND (transdate <= (date $todate - interval '0 days'))
1157       AND (transdate >= (date $todate - interval '30 days'))
1158
1159     UNION
1160
1161     -- between 31-60 days
1162
1163     SELECT ${ct}.id AS ctid, ${ct}.name,
1164       street, zipcode, city, country, contact, email,
1165       phone as customerphone, fax as customerfax, ${ct}number,
1166       "invnumber", "transdate",
1167       0.00 as "c0", (amount - paid) as "c30", 0.00 as "c60", 0.00 as "c90",
1168       "duedate", invoice, ${arap}.id,
1169       (SELECT $buysell
1170        FROM exchangerate
1171        WHERE (${arap}.curr = exchangerate.curr)
1172          AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
1173     FROM ${arap}, ${ct}
1174     WHERE (paid != amount)
1175       AND (${arap}.storno IS FALSE)
1176       AND (${arap}.${ct}_id = ${ct}.id)
1177       AND (${ct}.id = ?)
1178       AND (transdate < (date $todate - interval '30 days'))
1179       AND (transdate >= (date $todate - interval '60 days'))
1180
1181     UNION
1182
1183     -- between 61-90 days
1184
1185     SELECT ${ct}.id AS ctid, ${ct}.name,
1186       street, zipcode, city, country, contact, email,
1187       phone as customerphone, fax as customerfax, ${ct}number,
1188       "invnumber", "transdate",
1189       0.00 as "c0", 0.00 as "c30", (amount - paid) as "c60", 0.00 as "c90",
1190       "duedate", invoice, ${arap}.id,
1191       (SELECT $buysell
1192        FROM exchangerate
1193        WHERE (${arap}.curr = exchangerate.curr)
1194          AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
1195     FROM ${arap}, ${ct}
1196     WHERE (paid != amount)
1197       AND (${arap}.storno IS FALSE)
1198       AND (${arap}.${ct}_id = ${ct}.id)
1199       AND (${ct}.id = ?)
1200       AND (transdate < (date $todate - interval '60 days'))
1201       AND (transdate >= (date $todate - interval '90 days'))
1202
1203     UNION
1204
1205     -- over 90 days
1206
1207     SELECT ${ct}.id AS ctid, ${ct}.name,
1208       street, zipcode, city, country, contact, email,
1209       phone as customerphone, fax as customerfax, ${ct}number,
1210       "invnumber", "transdate",
1211       0.00 as "c0", 0.00 as "c30", 0.00 as "c60", (amount - paid) as "c90",
1212       "duedate", invoice, ${arap}.id,
1213       (SELECT $buysell
1214        FROM exchangerate
1215        WHERE (${arap}.curr = exchangerate.curr)
1216        AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
1217     FROM ${arap}, ${ct}
1218     WHERE (paid != amount)
1219       AND (${arap}.storno IS FALSE)
1220       AND (${arap}.${ct}_id = ${ct}.id)
1221       AND (${ct}.id = ?)
1222       AND (transdate < (date $todate - interval '90 days'))
1223
1224     ORDER BY ctid, transdate, invnumber |;
1225
1226   my $sth_details = prepare_query($form, $dbh, $q_details);
1227
1228   # select outstanding vendors or customers, depends on $ct
1229   my $query =
1230     qq|SELECT DISTINCT ct.id, ct.name
1231        FROM $ct ct, $arap a
1232        $dpt_join
1233        WHERE $where
1234          AND (a.${ct_id} = ct.id)
1235          AND (a.paid != a.amount)
1236          AND (a.transdate <= $todate)
1237        ORDER BY ct.name|;
1238
1239   my $sth = prepare_execute_query($form, $dbh, $query);
1240
1241   $form->{AG} = [];
1242   # for each company that has some stuff outstanding
1243   while (my ($id) = $sth->fetchrow_array) {
1244     do_statement($form, $sth_details, $q_details, $id, $id, $id, $id);
1245
1246     while (my $ref = $sth_details->fetchrow_hashref(NAME_lc)) {
1247       $ref->{module} = ($ref->{invoice}) ? $invoice : $arap;
1248       $ref->{exchangerate} = 1 unless $ref->{exchangerate};
1249       push @{ $form->{AG} }, $ref;
1250     }
1251
1252     $sth_details->finish;
1253
1254   }
1255
1256   $sth->finish;
1257
1258   # disconnect
1259   $dbh->disconnect;
1260
1261   $main::lxdebug->leave_sub();
1262 }
1263
1264 sub get_customer {
1265   $main::lxdebug->enter_sub();
1266
1267   my ($self, $myconfig, $form) = @_;
1268
1269   # connect to database
1270   my $dbh = $form->dbconnect($myconfig);
1271
1272   my $ct = $form->{ct} eq "customer" ? "customer" : "vendor";
1273
1274   my $query =
1275     qq|SELECT ct.name, ct.email, ct.cc, ct.bcc
1276        FROM $ct ct
1277        WHERE ct.id = ?|;
1278   ($form->{ $form->{ct} }, $form->{email}, $form->{cc}, $form->{bcc}) =
1279     selectrow_query($form, $dbh, $query, $form->{"${ct}_id"});
1280   $dbh->disconnect;
1281
1282   $main::lxdebug->leave_sub();
1283 }
1284
1285 sub get_taxaccounts {
1286   $main::lxdebug->enter_sub();
1287
1288   my ($self, $myconfig, $form) = @_;
1289
1290   # connect to database
1291   my $dbh = $form->dbconnect($myconfig);
1292
1293   # get tax accounts
1294   my $query =
1295     qq|SELECT c.accno, c.description, t.rate
1296        FROM chart c, tax t
1297        WHERE (c.link LIKE '%CT_tax%') AND (c.id = t.chart_id)
1298        ORDER BY c.accno|;
1299   $form->{taxaccounts} = selectall_hashref_quert($form, $dbh, $query);
1300
1301   $dbh->disconnect;
1302
1303   $main::lxdebug->leave_sub();
1304 }
1305
1306 sub tax_report {
1307   $main::lxdebug->enter_sub();
1308
1309   my ($self, $myconfig, $form) = @_;
1310
1311   # connect to database
1312   my $dbh = $form->dbconnect($myconfig);
1313
1314   my ($null, $department_id) = split /--/, $form->{department};
1315
1316   # build WHERE
1317   my $where = "1 = 1";
1318
1319   if ($department_id) {
1320     $where .= qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
1321   }
1322
1323   my ($accno, $rate);
1324
1325   if ($form->{accno}) {
1326     $accno = $form->{accno};
1327     $rate  = $form->{"$form->{accno}_rate"};
1328     $accno = qq| AND (ch.accno = | . $dbh->quote($accno) . qq|)|;
1329   }
1330   $rate *= 1;
1331
1332   my ($table, $ARAP);
1333
1334   if ($form->{db} eq 'ar') {
1335     $table = "customer";
1336     $ARAP  = "AR";
1337   } else {
1338     $table = "vendor";
1339     $ARAP  = "AP";
1340   }
1341
1342   my $arap = lc($ARAP);
1343
1344   my $transdate = "a.transdate";
1345
1346   if ($form->{method} eq 'cash') {
1347     $transdate = "a.datepaid";
1348
1349     my $todate = conv_dateq($form->{todate} ? $form->{todate} : $form->current_date($myconfig));
1350
1351     $where .= qq|
1352       AND ac.trans_id IN
1353         (
1354           SELECT trans_id
1355           FROM acc_trans
1356           JOIN chart ON (chart_id = id)
1357           WHERE (link LIKE '%${ARAP}_paid%')
1358           AND (transdate <= $todate)
1359         )
1360       |;
1361   }
1362
1363   # if there are any dates construct a where
1364   $where .= " AND ($transdate >= " . conv_dateq($form->{fromdate}) . ") " if ($form->{fromdate});
1365   $where .= " AND ($transdate <= " . conv_dateq($form->{todate}) . ") " if ($form->{todate});
1366
1367   my $ml = ($form->{db} eq 'ar') ? 1 : -1;
1368
1369   my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name));
1370   $sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } qw(id transdate invnumber name netamount tax)));
1371
1372   if ($form->{report} !~ /nontaxable/) {
1373     $query =
1374       qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, a.invnumber, n.name, a.netamount,
1375           ac.amount * $ml AS tax
1376          FROM acc_trans ac
1377          JOIN ${arap} a ON (a.id = ac.trans_id)
1378          JOIN chart ch ON (ch.id = ac.chart_id)
1379          JOIN $table n ON (n.id = a.${table}_id)
1380          WHERE
1381            $where
1382            $accno
1383            AND (a.invoice = '0')
1384
1385          UNION
1386
1387          SELECT a.id, '1' AS invoice, $transdate AS transdate, a.invnumber, n.name, i.sellprice * i.qty AS netamount,
1388            i.sellprice * i.qty * $rate * $ml AS tax
1389          FROM acc_trans ac
1390          JOIN ${arap} a ON (a.id = ac.trans_id)
1391          JOIN chart ch ON (ch.id = ac.chart_id)
1392          JOIN $table n ON (n.id = a.${table}_id)
1393          JOIN ${table}tax t ON (t.${table}_id = n.id)
1394          JOIN invoice i ON (i.trans_id = a.id)
1395          JOIN partstax p ON (p.parts_id = i.parts_id)
1396          WHERE
1397            $where
1398            $accno
1399            AND (a.invoice = '1')
1400          ORDER BY $sortorder|;
1401   } else {
1402     # only gather up non-taxable transactions
1403     $query =
1404       qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, a.invnumber, n.name, a.netamount
1405          FROM acc_trans ac
1406          JOIN ${arap} a ON (a.id = ac.trans_id)
1407          JOIN $table n ON (n.id = a.${table}_id)
1408          WHERE
1409            $where
1410            AND (a.invoice = '0')
1411            AND (a.netamount = a.amount)
1412
1413          UNION
1414
1415          SELECT a.id, '1' AS invoice, $transdate AS transdate, a.invnumber, n.name, i.sellprice * i.qty AS netamount
1416          FROM acc_trans ac
1417          JOIN ${arap} a ON (a.id = ac.trans_id)
1418          JOIN $table n ON (n.id = a.${table}_id)
1419          JOIN invoice i ON (i.trans_id = a.id)
1420          WHERE
1421            $where
1422            AND (a.invoice = '1')
1423            AND (
1424              a.${table}_id NOT IN (SELECT ${table}_id FROM ${table}tax t (${table}_id))
1425              OR
1426              i.parts_id NOT IN (SELECT parts_id FROM partstax p (parts_id))
1427            )
1428          GROUP BY a.id, a.invnumber, $transdate, n.name, i.sellprice, i.qty
1429          ORDER by $sortorder|;
1430   }
1431
1432   $form->{TR} = selectall_hashref_query($form, $dbh, $query);
1433
1434   $dbh->disconnect;
1435
1436   $main::lxdebug->leave_sub();
1437 }
1438
1439 sub paymentaccounts {
1440   $main::lxdebug->enter_sub();
1441
1442   my ($self, $myconfig, $form) = @_;
1443
1444   # connect to database, turn AutoCommit off
1445   my $dbh = $form->dbconnect_noauto($myconfig);
1446
1447   my $ARAP = $form->{db} eq "ar" ? "AR" : "AP";
1448
1449   # get A(R|P)_paid accounts
1450   my $query =
1451     qq|SELECT accno, description
1452        FROM chart
1453        WHERE link LIKE '%${ARAP}_paid%'|;
1454   $form->{PR} = selectall_hashref_query($form, $dbh, $query);
1455
1456   $dbh->disconnect;
1457
1458   $main::lxdebug->leave_sub();
1459 }
1460
1461 sub payments {
1462   $main::lxdebug->enter_sub();
1463
1464   my ($self, $myconfig, $form) = @_;
1465
1466   # connect to database, turn AutoCommit off
1467   my $dbh = $form->dbconnect_noauto($myconfig);
1468
1469   my $ml = 1;
1470   my $arap;
1471   if ($form->{db} eq 'ar') {
1472     $table = 'customer';
1473     $ml = -1;
1474     $arap = 'ar';
1475   } else {
1476     $table = 'vendor';
1477     $arap = 'ap';
1478   }
1479
1480   my ($query, $sth);
1481   my $dpt_join;
1482   my $where;
1483
1484   if ($form->{department_id}) {
1485     $dpt_join = qq| JOIN dpt_trans t ON (t.trans_id = ac.trans_id) |;
1486     $where = qq| AND (t.department_id = | . conv_i($form->{department_id}, 'NULL') . qq|) |;
1487   }
1488
1489   if ($form->{fromdate}) {
1490     $where .= " AND (ac.transdate >= " . $dbh->quote($form->{fromdate}) . ") ";
1491   }
1492   if ($form->{todate}) {
1493     $where .= " AND (ac.transdate <= " . $dbh->quote($form->{todate}) . ") ";
1494   }
1495   if (!$form->{fx_transaction}) {
1496     $where .= " AND ac.fx_transaction = '0'";
1497   }
1498
1499   my $invnumber;
1500   my $reference;
1501   if ($form->{reference}) {
1502     $reference = $dbh->quote('%' . $form->{reference} . '%');
1503     $invnumber = " AND (a.invnumber LIKE $reference)";
1504     $reference = " AND (g.reference LIKE $reference)";
1505   }
1506   if ($form->{source}) {
1507     $where .= " AND (ac.source ILIKE " . $dbh->quote('%' . $form->{source} . '%') . ") ";
1508   }
1509   if ($form->{memo}) {
1510     $where .= " AND (ac.memo ILIKE " . $dbh->quote('%' . $form->{memo} . '%') . ") ";
1511   }
1512
1513   my $sortorder = join(', ', qw(name invnumber ordnumber transdate source));
1514   $sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } qw(transdate invnumber name source memo)));
1515
1516   $query = qq|SELECT id, accno, description FROM chart WHERE accno = ?|;
1517   my $sth = prepare_query($form, $dbh, $query);
1518
1519   my $q_details =
1520       qq|SELECT c.name, a.invnumber, a.ordnumber,
1521            ac.transdate, ac.amount * $ml AS paid, ac.source,
1522            a.invoice, a.id, ac.memo, '${arap}' AS module
1523          FROM acc_trans ac
1524          JOIN $arap a ON (ac.trans_id = a.id)
1525          JOIN $table c ON (c.id = a.${table}_id)
1526          $dpt_join
1527          WHERE (ac.chart_id = ?)
1528            $where
1529            $invnumber
1530
1531          UNION
1532
1533          SELECT g.description, g.reference, NULL AS ordnumber,
1534            ac.transdate, ac.amount * $ml AS paid, ac.source,
1535            '0' as invoice, g.id, ac.memo, 'gl' AS module
1536          FROM acc_trans ac
1537          JOIN gl g ON (g.id = ac.trans_id)
1538          $dpt_join
1539          WHERE (ac.chart_id = ?)
1540            $where
1541            $reference
1542            AND (ac.amount * $ml) > 0
1543
1544          ORDER BY $sortorder|;
1545   my $sth_details = prepare_query($form, $dbh, $q_details);
1546
1547   $form->{PR} = [];
1548
1549   # cycle through each id
1550   foreach my $accno (split(/ /, $form->{paymentaccounts})) {
1551     do_statement($form, $sth, $query, $accno);
1552     my $ref = $sth->fetchrow_hashref();
1553     push(@{ $form->{PR} }, $ref);
1554     $sth->finish();
1555
1556     $form->{ $ref->{id} } = [] unless ($form->{ $ref->{id} });
1557
1558     do_statement($form, $sth_details, $q_details, $ref->{id}, $ref->{id});
1559     while (my $pr = $sth_details->fetchrow_hashref()) {
1560       push(@{ $form->{ $ref->{id} } }, $pr);
1561     }
1562     $sth_details->finish();
1563   }
1564
1565   $dbh->disconnect;
1566
1567   $main::lxdebug->leave_sub();
1568 }
1569
1570 sub bwa {
1571   $main::lxdebug->enter_sub();
1572
1573   my ($self, $myconfig, $form) = @_;
1574
1575   # connect to database
1576   my $dbh = $form->dbconnect($myconfig);
1577
1578   my $last_period = 0;
1579   my $category;
1580   my @categories  =
1581     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);
1582
1583   $form->{decimalplaces} *= 1;
1584
1585   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, "pos_bwa");
1586
1587   # if there are any compare dates
1588   if ($form->{fromdate} || $form->{todate}) {
1589     $last_period = 1;
1590     if ($form->{fromdate}) {
1591       $form->{fromdate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
1592       $year = $1;
1593     } else {
1594       $form->{todate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
1595       $year = $1;
1596     }
1597     $kummfromdate = $form->{comparefromdate};
1598     $kummtodate   = $form->{comparetodate};
1599     &get_accounts_g($dbh, $last_period, $kummfromdate, $kummtodate, $form, "pos_bwa");
1600   }
1601
1602   @periods        = qw(jetzt kumm);
1603   @gesamtleistung = qw(1 2 3);
1604   @gesamtkosten   = qw (10 11 12 13 14 15 16 17 18 19 20);
1605   @ergebnisse     =
1606     qw (rohertrag betriebrohertrag betriebsergebnis neutraleraufwand neutralerertrag ergebnisvorsteuern ergebnis gesamtleistung gesamtkosten);
1607
1608   foreach $key (@periods) {
1609     $form->{ "$key" . "gesamtleistung" } = 0;
1610     $form->{ "$key" . "gesamtkosten" }   = 0;
1611
1612     foreach $category (@categories) {
1613
1614       if (defined($form->{$category}{$key})) {
1615         $form->{"$key$category"} =
1616           $form->format_amount($myconfig,
1617                                $form->round_amount($form->{$category}{$key}, 2
1618                                ),
1619                                $form->{decimalplaces},
1620                                '0');
1621       }
1622     }
1623     foreach $item (@gesamtleistung) {
1624       $form->{ "$key" . "gesamtleistung" } += $form->{$item}{$key};
1625     }
1626     foreach $item (@gesamtkosten) {
1627       $form->{ "$key" . "gesamtkosten" } += $form->{$item}{$key};
1628     }
1629     $form->{ "$key" . "rohertrag" } =
1630       $form->{ "$key" . "gesamtleistung" } - $form->{4}{$key};
1631     $form->{ "$key" . "betriebrohertrag" } =
1632       $form->{ "$key" . "rohertrag" } + $form->{5}{$key};
1633     $form->{ "$key" . "betriebsergebnis" } =
1634       $form->{ "$key" . "betriebrohertrag" } -
1635       $form->{ "$key" . "gesamtkosten" };
1636     $form->{ "$key" . "neutraleraufwand" } =
1637       $form->{30}{$key} + $form->{31}{$key};
1638     $form->{ "$key" . "neutralertrag" } =
1639       $form->{32}{$key} + $form->{33}{$key} + $form->{34}{$key};
1640     $form->{ "$key" . "ergebnisvorsteuern" } =
1641       $form->{ "$key" . "betriebsergebnis" } -
1642       $form->{ "$key" . "neutraleraufwand" } +
1643       $form->{ "$key" . "neutralertrag" };
1644     $form->{ "$key" . "ergebnis" } =
1645       $form->{ "$key" . "ergebnisvorsteuern" } + $form->{35}{$key};
1646
1647     if ($form->{ "$key" . "gesamtleistung" } > 0) {
1648       foreach $category (@categories) {
1649         if (defined($form->{$category}{$key})) {
1650           $form->{ "$key" . "gl" . "$category" } =
1651             $form->format_amount(
1652                                $myconfig,
1653                                $form->round_amount(
1654                                  ($form->{$category}{$key} /
1655                                     $form->{ "$key" . "gesamtleistung" } * 100
1656                                  ),
1657                                  $form->{decimalplaces}
1658                                ),
1659                                $form->{decimalplaces},
1660                                '0');
1661         }
1662       }
1663       foreach $item (@ergebnisse) {
1664         $form->{ "$key" . "gl" . "$item" } =
1665           $form->format_amount($myconfig,
1666                                $form->round_amount(
1667                                  ( $form->{ "$key" . "$item" } /
1668                                      $form->{ "$key" . "gesamtleistung" } * 100
1669                                  ),
1670                                  $form->{decimalplaces}
1671                                ),
1672                                $form->{decimalplaces},
1673                                '0');
1674       }
1675     }
1676
1677     if ($form->{ "$key" . "gesamtkosten" } > 0) {
1678       foreach $category (@categories) {
1679         if (defined($form->{$category}{$key})) {
1680           $form->{ "$key" . "gk" . "$category" } =
1681             $form->format_amount($myconfig,
1682                                  $form->round_amount(
1683                                    ($form->{$category}{$key} /
1684                                       $form->{ "$key" . "gesamtkosten" } * 100
1685                                    ),
1686                                    $form->{decimalplaces}
1687                                  ),
1688                                  $form->{decimalplaces},
1689                                  '0');
1690         }
1691       }
1692       foreach $item (@ergebnisse) {
1693         $form->{ "$key" . "gk" . "$item" } =
1694           $form->format_amount($myconfig,
1695                                $form->round_amount(
1696                                    ($form->{ "$key" . "$item" } /
1697                                       $form->{ "$key" . "gesamtkosten" } * 100
1698                                    ),
1699                                    $form->{decimalplaces}
1700                                ),
1701                                $form->{decimalplaces},
1702                                '0');
1703       }
1704     }
1705
1706     if ($form->{10}{$key} > 0) {
1707       foreach $category (@categories) {
1708         if (defined($form->{$category}{$key})) {
1709           $form->{ "$key" . "pk" . "$category" } =
1710             $form->format_amount(
1711                         $myconfig,
1712                         $form->round_amount(
1713                           ($form->{$category}{$key} / $form->{10}{$key} * 100),
1714                           $form->{decimalplaces}
1715                         ),
1716                         $form->{decimalplaces},
1717                         '0');
1718         }
1719       }
1720       foreach $item (@ergebnisse) {
1721         $form->{ "$key" . "pk" . "$item" } =
1722           $form->format_amount($myconfig,
1723                                $form->round_amount(
1724                                                 ($form->{ "$key" . "$item" } /
1725                                                    $form->{10}{$key} * 100
1726                                                 ),
1727                                                 $form->{decimalplaces}
1728                                ),
1729                                $form->{decimalplaces},
1730                                '0');
1731       }
1732     }
1733
1734     if ($form->{4}{$key} > 0) {
1735       foreach $category (@categories) {
1736         if (defined($form->{$category}{$key})) {
1737           $form->{ "$key" . "auf" . "$category" } =
1738             $form->format_amount(
1739                          $myconfig,
1740                          $form->round_amount(
1741                            ($form->{$category}{$key} / $form->{4}{$key} * 100),
1742                            $form->{decimalplaces}
1743                          ),
1744                          $form->{decimalplaces},
1745                          '0');
1746         }
1747       }
1748       foreach $item (@ergebnisse) {
1749         $form->{ "$key" . "auf" . "$item" } =
1750           $form->format_amount($myconfig,
1751                                $form->round_amount(
1752                                                 ($form->{ "$key" . "$item" } /
1753                                                    $form->{4}{$key} * 100
1754                                                 ),
1755                                                 $form->{decimalplaces}
1756                                ),
1757                                $form->{decimalplaces},
1758                                '0');
1759       }
1760     }
1761
1762     foreach $item (@ergebnisse) {
1763       $form->{ "$key" . "$item" } =
1764         $form->format_amount($myconfig,
1765                              $form->round_amount($form->{ "$key" . "$item" },
1766                                                  $form->{decimalplaces}
1767                              ),
1768                              $form->{decimalplaces},
1769                              '0');
1770     }
1771
1772   }
1773   $dbh->disconnect;
1774
1775   $main::lxdebug->leave_sub();
1776 }
1777
1778 sub ustva {
1779   $main::lxdebug->enter_sub();
1780
1781   my ($self, $myconfig, $form) = @_;
1782
1783   # connect to database
1784   my $dbh = $form->dbconnect($myconfig);
1785
1786   my $last_period     = 0;
1787   my @categories_cent = qw(51r 511 86r 861 97r 971 93r 931
1788     96 66 43 45 53 62 65 67);
1789   my @categories_euro = qw(48 51 86 91 97 93 94);
1790   $form->{decimalplaces} *= 1;
1791
1792   foreach $item (@categories_cent) {
1793     $form->{"$item"} = 0;
1794   }
1795   foreach $item (@categories_euro) {
1796     $form->{"$item"} = 0;
1797   }
1798
1799   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, "pos_ustva");
1800
1801   #   foreach $item (@categories_cent) {
1802   #     if ($form->{$item}{"jetzt"} > 0) {
1803   #             $form->{$item} = $form->{$item}{"jetzt"};
1804   #             delete $form->{$item}{"jetzt"};
1805   #     }
1806   #   }
1807   #   foreach $item (@categories_euro) {
1808   #     if ($form->{$item}{"jetzt"} > 0) {
1809   #             $form->{$item} = $form->{$item}{"jetzt"};
1810   #             delete $form->{$item}{"jetzt"};
1811   #     }  foreach $item (@categories_cent) {
1812   #     if ($form->{$item}{"jetzt"} > 0) {
1813   #             $form->{$item} = $form->{$item}{"jetzt"};
1814   #             delete $form->{$item}{"jetzt"};
1815   #     }
1816   #   }
1817   #   foreach $item (@categories_euro) {
1818   #     if ($form->{$item}{"jetzt"} > 0) {
1819   #             $form->{$item} = $form->{$item}{"jetzt"};
1820   #             delete $form->{$item}{"jetzt"};
1821   #     }
1822   #   }
1823   #
1824   #    }
1825
1826   #
1827   # Berechnung der USTVA Formularfelder
1828   #
1829   $form->{"51r"} = $form->{"511"};
1830   $form->{"86r"} = $form->{"861"};
1831   $form->{"97r"} = $form->{"971"};
1832   $form->{"93r"} = $form->{"931"};
1833
1834   #$form->{"96"}  = $form->{"94"} * 0.16;
1835   $form->{"43"} =
1836     $form->{"51r"} + $form->{"86r"} + $form->{"97r"} + $form->{"93r"} +
1837     $form->{"96"};
1838   $form->{"45"} = $form->{"43"};
1839   $form->{"53"} = $form->{"43"};
1840   $form->{"62"} = $form->{"43"} - $form->{"66"};
1841   $form->{"65"} = $form->{"43"} - $form->{"66"};
1842   $form->{"67"} = $form->{"43"} - $form->{"66"};
1843
1844   foreach $item (@categories_cent) {
1845     $form->{$item} =
1846       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),
1847                            2, '0');
1848   }
1849
1850   foreach $item (@categories_euro) {
1851     $form->{$item} =
1852       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 0),
1853                            0, '0');
1854   }
1855
1856   $dbh->disconnect;
1857
1858   $main::lxdebug->leave_sub();
1859 }
1860
1861 sub income_statement {
1862   $main::lxdebug->enter_sub();
1863
1864   my ($self, $myconfig, $form) = @_;
1865
1866   # connect to database
1867   my $dbh = $form->dbconnect($myconfig);
1868
1869   my $last_period          = 0;
1870   my @categories_einnahmen = qw(1 2 3 4 5 6 7);
1871   my @categories_ausgaben  =
1872     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);
1873
1874   my @ergebnisse = qw(sumeura sumeurb guvsumme);
1875
1876   $form->{decimalplaces} *= 1;
1877
1878   foreach $item (@categories_einnahmen) {
1879     $form->{$item} = 0;
1880   }
1881   foreach $item (@categories_ausgaben) {
1882     $form->{$item} = 0;
1883   }
1884
1885   foreach $item (@ergebnisse) {
1886     $form->{$item} = 0;
1887   }
1888
1889   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
1890                   $form, "pos_eur");
1891
1892   foreach $item (@categories_einnahmen) {
1893     $form->{"eur${item}"} =
1894       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
1895     $form->{"sumeura"} += $form->{$item};
1896   }
1897   foreach $item (@categories_ausgaben) {
1898     $form->{"eur${item}"} =
1899       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
1900     $form->{"sumeurb"} += $form->{$item};
1901   }
1902
1903   $form->{"guvsumme"} = $form->{"sumeura"} - $form->{"sumeurb"};
1904
1905   foreach $item (@ergebnisse) {
1906     $form->{$item} =
1907       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
1908   }
1909   $main::lxdebug->leave_sub();
1910 }
1911 1;