Einführung einer ID-Spalte in acc_trans
[kivitendo-erp.git] / SL / GL.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) 2001
10 #
11 #  Author: Dieter Simader
12 #   Email: dsimader@sql-ledger.org
13 #     Web: http://www.sql-ledger.org
14 #
15 #  Contributors:
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 # General ledger backend code
32 #
33 # CHANGE LOG:
34 #   DS. 2000-07-04  Created
35 #   DS. 2001-06-12  Changed relations from accno to chart_id
36 #
37 #======================================================================
38
39 package GL;
40
41 use Data::Dumper;
42 use SL::DBUtils;
43
44 sub delete_transaction {
45   my ($self, $myconfig, $form) = @_;
46   $main::lxdebug->enter_sub();
47
48   # connect to database
49   my $dbh = $form->dbconnect_noauto($myconfig);
50
51   my @values = (conv_i($form->{id}));
52   do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
53   do_query($form, $dbh, qq|DELETE FROM gl WHERE id = ?|, @values);
54
55   # commit and redirect
56   my $rc = $dbh->commit;
57   $dbh->disconnect;
58   $main::lxdebug->leave_sub();
59
60   $rc;
61
62 }
63
64 sub post_transaction {
65   my ($self, $myconfig, $form) = @_;
66   $main::lxdebug->enter_sub();
67
68   my ($debit, $credit) = (0, 0);
69   my $project_id;
70
71   my $i;
72
73   # connect to database, turn off AutoCommit
74   my $dbh = $form->dbconnect_noauto($myconfig);
75
76   # post the transaction
77   # make up a unique handle and store in reference field
78   # then retrieve the record based on the unique handle to get the id
79   # replace the reference field with the actual variable
80   # add records to acc_trans
81
82   # if there is a $form->{id} replace the old transaction
83   # delete all acc_trans entries and add the new ones
84
85   if (!$form->{taxincluded}) {
86     $form->{taxincluded} = 0;
87   }
88
89   my ($query, $sth);
90
91   if ($form->{id}) {
92
93     # delete individual transactions
94     $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
95     @values = (conv_i($form->{id}));
96     do_query($form, $dbh, $query, @values);
97
98   } else {
99     $query = qq|SELECT nextval('glid')|;
100     ($form->{id}) = selectrow_query($form, $dbh, $query);
101
102     $query =
103       qq|INSERT INTO gl (id, employee_id) | .
104       qq|VALUES (?, (SELECT id FROM employee WHERE login = ?))|;
105     @values = ($form->{id}, $form->{login});
106     do_query($form, $dbh, $query, @values);
107   }
108
109   my ($null, $department_id) = split(/--/, $form->{department});
110   $department_id *= 1;
111
112   $form->{ob_transaction} *= 1;
113   $form->{cb_transaction} *= 1;
114
115   $query =
116     qq|UPDATE gl SET
117          reference = ?, description = ?, notes = ?,
118          transdate = ?, department_id = ?, taxincluded = ?,
119          storno = ?, storno_id = ?, ob_transaction = ?, cb_transaction = ?
120        WHERE id = ?|;
121
122   @values = ($form->{reference}, $form->{description}, $form->{notes},
123              conv_date($form->{transdate}), $department_id, $form->{taxincluded} ? 't' : 'f',
124              $form->{storno} ? 't' : 'f', conv_i($form->{storno_id}), $form->{ob_transaction} ? 't' : 'f', $form->{cb_transaction} ? 't' : 'f',
125              conv_i($form->{id}));
126   do_query($form, $dbh, $query, @values);
127
128   # insert acc_trans transactions
129   for $i (1 .. $form->{rowcount}) {
130     # extract accno
131     my ($accno) = split(/--/, $form->{"accno_$i"});
132     ($form->{"tax_id_$i"}) = split(/--/, $form->{"taxchart_$i"});
133     if ($form->{"tax_id_$i"} ne "") {
134       $query = qq|SELECT taxkey, rate FROM tax WHERE id = ?|;
135       ($taxkey, $rate) = selectrow_query($form, $dbh, $query, conv_i($form->{"tax_id_$i"}));
136     }
137
138     my $amount = 0;
139     my $debit  = $form->{"debit_$i"};
140     my $credit = $form->{"credit_$i"};
141     my $tax    = $form->{"tax_$i"};
142
143     if ($credit) {
144       $amount = $credit;
145       $posted = 0;
146     }
147     if ($debit) {
148       $amount = $debit * -1;
149       $tax    = $tax * -1;
150       $posted = 0;
151     }
152
153     $project_id = conv_i($form->{"project_id_$i"});
154
155     # if there is an amount, add the record
156     if ($amount != 0) {
157       $query =
158         qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
159                                   source, memo, project_id, taxkey, ob_transaction, cb_transaction)
160            VALUES (?, (SELECT id FROM chart WHERE accno = ?),
161                    ?, ?, ?, ?, ?, ?, ?, ?)|;
162       @values = (conv_i($form->{id}), $accno, $amount, conv_date($form->{transdate}),
163                  $form->{"source_$i"}, $form->{"memo_$i"}, $project_id, $taxkey, $form->{ob_transaction} ? 't' : 'f', $form->{cb_transaction} ? 't' : 'f');
164       do_query($form, $dbh, $query, @values);
165     }
166
167     if ($tax != 0) {
168       # add taxentry
169       $query =
170         qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
171                                   source, memo, project_id, taxkey)
172            VALUES (?, (SELECT chart_id FROM tax WHERE id = ?),
173                    ?, ?, ?, ?, ?, ?)|;
174       @values = (conv_i($form->{id}), conv_i($form->{"tax_id_$i"}),
175                  $tax, conv_date($form->{transdate}), $form->{"source_$i"},
176                  $form->{"memo_$i"}, $project_id, $taxkey);
177       do_query($form, $dbh, $query, @values);
178     }
179   }
180
181   if ($form->{storno} && $form->{storno_id}) {
182     do_query($form, $dbh, qq|UPDATE gl SET storno = 't' WHERE id = ?|, conv_i($form->{storno_id}));
183   }
184
185   # commit and redirect
186   my $rc = $dbh->commit;
187   $dbh->disconnect;
188   $main::lxdebug->leave_sub();
189
190   return $rc;
191 }
192
193 sub all_transactions {
194   my ($self, $myconfig, $form) = @_;
195   $main::lxdebug->enter_sub();
196
197   # connect to database
198   my $dbh = $form->dbconnect($myconfig);
199   my ($query, $sth, $source, $null);
200
201   my ($glwhere, $arwhere, $apwhere) = ("1 = 1", "1 = 1", "1 = 1");
202   my (@glvalues, @arvalues, @apvalues);
203
204   if ($form->{reference}) {
205     $glwhere .= qq| AND g.reference ILIKE ?|;
206     $arwhere .= qq| AND a.invnumber ILIKE ?|;
207     $apwhere .= qq| AND a.invnumber ILIKE ?|;
208     push(@glvalues, '%' . $form->{reference} . '%');
209     push(@arvalues, '%' . $form->{reference} . '%');
210     push(@apvalues, '%' . $form->{reference} . '%');
211   }
212
213   if ($form->{department}) {
214     my ($null, $department) = split /--/, $form->{department};
215     $glwhere .= qq| AND g.department_id = ?|;
216     $arwhere .= qq| AND a.department_id = ?|;
217     $apwhere .= qq| AND a.department_id = ?|;
218     push(@glvalues, $department);
219     push(@arvalues, $department);
220     push(@apvalues, $department);
221   }
222
223   if ($form->{source}) {
224     $glwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)";
225     $arwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)";
226     $apwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)";
227     push(@glvalues, '%' . $form->{source} . '%');
228     push(@arvalues, '%' . $form->{source} . '%');
229     push(@apvalues, '%' . $form->{source} . '%');
230   }
231
232   if ($form->{datefrom}) {
233     $glwhere .= " AND ac.transdate >= ?";
234     $arwhere .= " AND ac.transdate >= ?";
235     $apwhere .= " AND ac.transdate >= ?";
236     push(@glvalues, $form->{datefrom});
237     push(@arvalues, $form->{datefrom});
238     push(@apvalues, $form->{datefrom});
239   }
240
241   if ($form->{dateto}) {
242     $glwhere .= " AND ac.transdate <= ?";
243     $arwhere .= " AND ac.transdate <= ?";
244     $apwhere .= " AND ac.transdate <= ?";
245     push(@glvalues, $form->{dateto});
246     push(@arvalues, $form->{dateto});
247     push(@apvalues, $form->{dateto});
248   }
249
250   if ($form->{description}) {
251     $glwhere .= " AND g.description ILIKE ?";
252     $arwhere .= " AND ct.name ILIKE ?";
253     $apwhere .= " AND ct.name ILIKE ?";
254     push(@glvalues, '%' . $form->{description} . '%');
255     push(@arvalues, '%' . $form->{description} . '%');
256     push(@apvalues, '%' . $form->{description} . '%');
257   }
258
259   if ($form->{notes}) {
260     $glwhere .= " AND g.notes ILIKE ?";
261     $arwhere .= " AND a.notes ILIKE ?";
262     $apwhere .= " AND a.notes ILIKE ?";
263     push(@glvalues, '%' . $form->{notes} . '%');
264     push(@arvalues, '%' . $form->{notes} . '%');
265     push(@apvalues, '%' . $form->{notes} . '%');
266   }
267
268   if ($form->{accno}) {
269     $glwhere .= " AND c.accno = '$form->{accno}'";
270     $arwhere .= " AND c.accno = '$form->{accno}'";
271     $apwhere .= " AND c.accno = '$form->{accno}'";
272   }
273
274   if ($form->{category} ne 'X') {
275     $glwhere .= qq| AND g.id in (SELECT trans_id FROM acc_trans ac2 WHERE ac2.chart_id IN (SELECT id FROM chart c2 WHERE c2.category = ?))|;
276     $arwhere .= qq| AND a.id in (SELECT trans_id FROM acc_trans ac2 WHERE ac2.chart_id IN (SELECT id FROM chart c2 WHERE c2.category = ?))|;
277     $apwhere .= qq| AND a.id in (SELECT trans_id FROM acc_trans ac2 WHERE ac2.chart_id IN (SELECT id FROM chart c2 WHERE c2.category = ?))|;
278     push(@glvalues, $form->{category});
279     push(@arvalues, $form->{category});
280     push(@apvalues, $form->{category});
281   }
282
283   if ($form->{project_id}) {
284     $glwhere .= qq| AND g.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)|;
285     $arwhere .=
286       qq| AND ((a.globalproject_id = ?) OR
287                (a.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)))|;
288     $apwhere .=
289       qq| AND ((a.globalproject_id = ?) OR
290                (a.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)))|;
291     my $project_id = conv_i($form->{project_id});
292     push(@glvalues, $project_id);
293     push(@arvalues, $project_id, $project_id);
294     push(@apvalues, $project_id, $project_id);
295   }
296
297   my ($project_columns, %project_join);
298   if ($form->{"l_projectnumbers"}) {
299     $project_columns = qq|, ac.project_id, pr.projectnumber|;
300     $project_join = qq|LEFT JOIN project pr ON (ac.project_id = pr.id)|;
301   }
302
303   if ($form->{accno}) {
304     # get category for account
305     $query = qq|SELECT category FROM chart WHERE accno = ?|;
306     ($form->{ml}) = selectrow_query($form, $dbh, $query, $form->{accno});
307
308     if ($form->{datefrom}) {
309       $query =
310         qq|SELECT SUM(ac.amount)
311            FROM acc_trans ac
312            LEFT JOIN chart c ON (ac.chart_id = c.id)
313            WHERE (c.accno = ?) AND (ac.transdate < ?)|;
314       ($form->{balance}) = selectrow_query($form, $dbh, $query, $form->{accno}, conv_date($form->{datefrom}));
315     }
316   }
317
318   my $false = ($myconfig->{dbdriver} eq 'Pg') ? FALSE: q|'0'|;
319
320   my %sort_columns =  (
321     'id'           => [ qw(id)                   ],
322     'transdate'    => [ qw(transdate id)         ],
323     'reference'    => [ qw(lower_reference id)   ],
324     'description'  => [ qw(lower_description id) ],
325     'accno'        => [ qw(accno transdate id)   ],
326     );
327   my %lowered_columns =  (
328     'reference'       => { 'gl' => 'g.reference',   'arap' => 'a.invnumber', },
329     'source'          => { 'gl' => 'ac.source',     'arap' => 'ac.source',   },
330     'description'     => { 'gl' => 'g.description', 'arap' => 'ct.name',     },
331     );
332
333   my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
334   my $sortkey   = $sort_columns{$form->{sort}} ? $form->{sort} : 'transdate';
335   my $sortorder = join ', ', map { "$_ $sortdir" } @{ $sort_columns{$sortkey} };
336
337   my %columns_for_sorting = ( 'gl' => '', 'arap' => '', );
338   foreach my $spec (@{ $sort_columns{$sortkey} }) {
339     next if ($spec !~ m/^lower_(.*)$/);
340
341     my $column = $1;
342     map { $columns_for_sorting{$_} .= sprintf(', lower(%s) AS lower_%s', $lowered_columns{$column}->{$_}, $column) } qw(gl arap);
343   }
344
345   my $query =
346     qq|SELECT
347         ac.acc_trans_id, g.id, 'gl' AS type, $false AS invoice, g.reference, ac.taxkey, c.link,
348         g.description, ac.transdate, ac.source, ac.trans_id,
349         ac.amount, c.accno, g.notes, t.chart_id,
350         CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
351         $project_columns
352         $columns_for_sorting{gl}
353       FROM gl g, acc_trans ac $project_join, chart c
354       LEFT JOIN tax t ON (t.chart_id = c.id)
355       WHERE $glwhere
356         AND (ac.chart_id = c.id)
357         AND (g.id = ac.trans_id)
358
359       UNION
360
361       SELECT ac.acc_trans_id, a.id, 'ar' AS type, a.invoice, a.invnumber, ac.taxkey, c.link,
362         ct.name, ac.transdate, ac.source, ac.trans_id,
363         ac.amount, c.accno, a.notes, t.chart_id,
364         CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
365         $project_columns
366         $columns_for_sorting{arap}
367       FROM ar a, acc_trans ac $project_join, customer ct, chart c
368       LEFT JOIN tax t ON (t.chart_id=c.id)
369       WHERE $arwhere
370         AND (ac.chart_id = c.id)
371         AND (a.customer_id = ct.id)
372         AND (a.id = ac.trans_id)
373
374       UNION
375
376       SELECT ac.acc_trans_id, a.id, 'ap' AS type, a.invoice, a.invnumber, ac.taxkey, c.link,
377         ct.name, ac.transdate, ac.source, ac.trans_id,
378         ac.amount, c.accno, a.notes, t.chart_id,
379         CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
380         $project_columns
381         $columns_for_sorting{arap}
382       FROM ap a, acc_trans ac $project_join, vendor ct, chart c
383       LEFT JOIN tax t ON (t.chart_id=c.id)
384       WHERE $apwhere
385         AND (ac.chart_id = c.id)
386         AND (a.vendor_id = ct.id)
387         AND (a.id = ac.trans_id)
388
389       ORDER BY $sortorder, acc_trans_id $sortdir|;
390
391   my @values = (@glvalues, @arvalues, @apvalues);
392
393   # Show all $query in Debuglevel LXDebug::QUERY
394   $callingdetails = (caller (0))[3];
395   dump_query(LXDebug::QUERY, "$callingdetails", $query, @values);
396
397   $sth = prepare_execute_query($form, $dbh, $query, @values);
398   my $trans_id  = "";
399   my $trans_id2 = "";
400
401   my ($i, $j, $k, $l, $ref, $ref2);
402
403   $form->{GL} = [];
404   while (my $ref0 = $sth->fetchrow_hashref(NAME_lc)) {
405
406     $trans_id = $ref0->{id};
407
408     my $source = $ref0->{source};
409     undef($ref0->{source});
410
411     if ($trans_id != $trans_id2) { # first line of a booking
412
413       if ($trans_id2) {
414         push(@{ $form->{GL} }, $ref);
415         $balance = 0;
416       }
417
418       $ref       = $ref0;
419       $trans_id2 = $ref->{id};
420
421       # gl
422       if ($ref->{type} eq "gl") {
423         $ref->{module} = "gl";
424       }
425
426       # ap
427       if ($ref->{type} eq "ap") {
428         if ($ref->{invoice}) {
429           $ref->{module} = "ir";
430         } else {
431           $ref->{module} = "ap";
432         }
433       }
434
435       # ar
436       if ($ref->{type} eq "ar") {
437         if ($ref->{invoice}) {
438           $ref->{module} = "is";
439         } else {
440           $ref->{module} = "ar";
441         }
442       }
443
444       $ref->{"projectnumbers"} = {};
445       $ref->{"projectnumbers"}->{$ref->{"projectnumber"}} = 1 if ($ref->{"projectnumber"});
446
447       $balance = $ref->{amount};
448
449       # Linenumbers of General Ledger
450       $k       = 0; # Debit      # AP      # Soll
451       $l       = 0; # Credit     # AR      # Haben
452       $i       = 0; # Debit Tax  # AP_tax  # VSt
453       $j       = 0; # Credit Tax # AR_tax  # USt
454
455       if ($ref->{chart_id} > 0) { # all tax accounts first line, no line increasing
456         if ($ref->{amount} < 0) {
457           if ($ref->{link} =~ /AR_tax/) {
458             $ref->{credit_tax}{$j}       = $ref->{amount};
459             $ref->{credit_tax_accno}{$j} = $ref->{accno};
460          }
461           if ($ref->{link} =~ /AP_tax/) {
462             $ref->{debit_tax}{$i}       = $ref->{amount} * -1;
463             $ref->{debit_tax_accno}{$i} = $ref->{accno};
464           }
465         } else {
466           if ($ref->{link} =~ /AR_tax/) {
467             $ref->{credit_tax}{$j}       = $ref->{amount};
468             $ref->{credit_tax_accno}{$j} = $ref->{accno};
469           }
470           if ($ref->{link} =~ /AP_tax/) {
471             $ref->{debit_tax}{$i}       = $ref->{amount} * -1;
472             $ref->{debit_tax_accno}{$i} = $ref->{accno};
473           }
474         }
475       } else { #all other accounts first line
476
477         if ($ref->{amount} < 0) {
478           $ref->{debit}{$k}        = $ref->{amount} * -1;
479           $ref->{debit_accno}{$k}  = $ref->{accno};
480           $ref->{debit_taxkey}{$k} = $ref->{taxkey};
481           $ref->{ac_transdate}{$k} = $ref->{transdate};
482           $ref->{source}{$k}       = $source;
483         } else {
484           $ref->{credit}{$l}        = $ref->{amount} * 1;
485           $ref->{credit_accno}{$l}  = $ref->{accno};
486           $ref->{credit_taxkey}{$l} = $ref->{taxkey};
487           $ref->{ac_transdate}{$l}  = $ref->{transdate};
488           $ref->{source}{$l}        = $source;
489         }
490       }
491
492     } else { # following lines of a booking, line increasing
493
494       $ref2      = $ref0;
495       $trans_old = $trans_id2;
496       $trans_id2 = $ref2->{id};
497
498       $balance =
499         (int($balance * 100000) + int(100000 * $ref2->{amount})) / 100000;
500
501       $ref->{"projectnumbers"}->{$ref2->{"projectnumber"}} = 1 if ($ref2->{"projectnumber"});
502
503       if ($ref2->{chart_id} > 0) { # all tax accounts, following lines
504         if ($ref2->{amount} < 0) {
505           if ($ref2->{link} =~ /AR_tax/) {
506             if ($ref->{credit_tax_accno}{$j} ne "") {
507               $j++;
508             }
509             $ref->{credit_tax}{$j}       = $ref2->{amount};
510             $ref->{credit_tax_accno}{$j} = $ref2->{accno};
511           }
512           if ($ref2->{link} =~ /AP_tax/) {
513             if ($ref->{debit_tax_accno}{$i} ne "") {
514               $i++;
515             }
516             $ref->{debit_tax}{$i}       = $ref2->{amount} * -1;
517             $ref->{debit_tax_accno}{$i} = $ref2->{accno};
518           }
519         } else {
520           if ($ref2->{link} =~ /AR_tax/) {
521             if ($ref->{credit_tax_accno}{$j} ne "") {
522               $j++;
523             }
524             $ref->{credit_tax}{$j}       = $ref2->{amount};
525             $ref->{credit_tax_accno}{$j} = $ref2->{accno};
526           }
527           if ($ref2->{link} =~ /AP_tax/) {
528             if ($ref->{debit_tax_accno}{$i} ne "") {
529               $i++;
530             }
531             $ref->{debit_tax}{$i}       = $ref2->{amount} * -1;
532             $ref->{debit_tax_accno}{$i} = $ref2->{accno};
533           }
534         }
535       } else { # all other accounts, following lines
536         if ($ref2->{amount} < 0) {
537           if ($ref->{debit_accno}{$k} ne "") {
538             $k++;
539           }
540           if ($ref->{source}{$k} ne "") {
541             $space = " | ";
542           } else {
543             $space = "";
544           }
545           $ref->{debit}{$k}        = $ref2->{amount} * - 1;
546           $ref->{debit_accno}{$k}  = $ref2->{accno};
547           $ref->{debit_taxkey}{$k} = $ref2->{taxkey};
548           $ref->{ac_transdate}{$k} = $ref2->{transdate};
549           $ref->{source}{$k}       = $source . $space . $ref->{source}{$k};
550         } else {
551           if ($ref->{credit_accno}{$l} ne "") {
552             $l++;
553           }
554           if ($ref->{source}{$l} ne "") {
555             $space = " | ";
556           } else {
557             $space = "";
558           }
559           $ref->{credit}{$l}        = $ref2->{amount};
560           $ref->{credit_accno}{$l}  = $ref2->{accno};
561           $ref->{credit_taxkey}{$l} = $ref2->{taxkey};
562           $ref->{ac_transdate}{$l}  = $ref2->{transdate};
563           $ref->{source}{$l}        = $ref->{source}{$l} . $space . $source;
564         }
565       }
566     }
567   }
568
569   push @{ $form->{GL} }, $ref;
570   $sth->finish;
571
572   if ($form->{accno}) {
573     $query = qq|SELECT c.description FROM chart c WHERE c.accno = ?|;
574     ($form->{account_description}) = selectrow_query($form, $dbh, $query, $form->{accno});
575   }
576
577   $dbh->disconnect;
578
579   $main::lxdebug->leave_sub();
580 }
581
582 sub transaction {
583   my ($self, $myconfig, $form) = @_;
584   $main::lxdebug->enter_sub();
585
586   my ($query, $sth, $ref, @values);
587
588   # connect to database
589   my $dbh = $form->dbconnect($myconfig);
590
591   $query = qq|SELECT closedto, revtrans FROM defaults|;
592   ($form->{closedto}, $form->{revtrans}) = selectrow_query($form, $dbh, $query);
593
594   $query = qq|SELECT id, gldate
595               FROM gl
596               WHERE id = (SELECT max(id) FROM gl)|;
597   ($form->{previous_id}, $form->{previous_gldate}) = selectrow_query($form, $dbh, $query);
598
599   if ($form->{id}) {
600     $query =
601       qq|SELECT g.reference, g.description, g.notes, g.transdate, g.storno, g.storno_id,
602            d.description AS department, e.name AS employee, g.taxincluded, g.gldate, 
603          g.ob_transaction, g.cb_transaction
604          FROM gl g
605          LEFT JOIN department d ON (d.id = g.department_id)
606          LEFT JOIN employee e ON (e.id = g.employee_id)
607          WHERE g.id = ?|;
608     $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
609     map { $form->{$_} = $ref->{$_} } keys %$ref;
610
611     # retrieve individual rows
612     $query =
613       qq|SELECT c.accno, t.taxkey AS accnotaxkey, a.amount, a.memo, a.source,
614            a.transdate, a.cleared, a.project_id, p.projectnumber,
615            a.taxkey, t.rate AS taxrate, t.id,
616            (SELECT c1.accno
617             FROM chart c1, tax t1
618             WHERE (t1.id = t.id) AND (c1.id = t.chart_id)) AS taxaccno,
619            (SELECT tk.tax_id
620             FROM taxkeys tk
621             WHERE (tk.chart_id = a.chart_id) AND (tk.startdate <= a.transdate)
622             ORDER BY tk.startdate desc LIMIT 1) AS tax_id
623          FROM acc_trans a
624          JOIN chart c ON (c.id = a.chart_id)
625          LEFT JOIN project p ON (p.id = a.project_id)
626          LEFT JOIN tax t ON
627            (t.id =
628              (SELECT tk.tax_id
629               FROM taxkeys tk
630               WHERE (tk.taxkey_id = a.taxkey) AND
631                 ((CASE WHEN a.chart_id IN
632                     (SELECT chart_id FROM taxkeys WHERE taxkey_id = a.taxkey)
633                   THEN tk.chart_id = a.chart_id
634                   ELSE 1 = 1
635                   END)
636                  OR (c.link LIKE '%tax%'))
637                 AND (startdate <= a.transdate)
638               ORDER BY startdate DESC LIMIT 1))
639          WHERE (a.trans_id = ?)
640            AND (a.fx_transaction = '0')
641          ORDER BY a.acc_trans_id, a.transdate|;
642     $form->{GL} = selectall_hashref_query($form, $dbh, $query, conv_i($form->{id}));
643
644   } else {
645     $query =
646       qq|SELECT COALESCE(
647            (SELECT transdate
648             FROM gl
649             WHERE id = (SELECT MAX(id) FROM gl)
650             LIMIT 1),
651            current_date)|;
652     ($form->{transdate}) = selectrow_query($form, $dbh, $query);
653   }
654
655   # get tax description
656   $query = qq|SELECT * FROM tax ORDER BY taxkey|;
657   $form->{TAX} = selectall_hashref_query($form, $dbh, $query);
658
659   # get chart of accounts
660   $query =
661     qq|SELECT c.accno, c.description, c.link, tk.taxkey_id, tk.tax_id
662        FROM chart c
663        LEFT JOIN taxkeys tk ON (tk.id =
664          (SELECT id
665           FROM taxkeys
666           WHERE (taxkeys.chart_id = c.id)
667             AND (startdate <= ?)
668           ORDER BY startdate DESC
669           LIMIT 1))
670        ORDER BY c.accno|;
671   $form->{chart} = selectall_hashref_query($form, $dbh, $query, conv_date($form->{transdate}));
672
673   $dbh->disconnect;
674
675   $main::lxdebug->leave_sub();
676 }
677
678 sub storno {
679   $main::lxdebug->enter_sub();
680
681   my ($self, $form, $myconfig, $id) = @_;
682
683   my ($query, $new_id, $storno_row, $acc_trans_rows);
684   my $dbh = $form->get_standard_dbh($myconfig);
685
686   $query = qq|SELECT nextval('glid')|;
687   ($new_id) = selectrow_query($form, $dbh, $query);
688
689   $query = qq|SELECT * FROM gl WHERE id = ?|;
690   $storno_row = selectfirst_hashref_query($form, $dbh, $query, $id);
691
692   $storno_row->{id}        = $new_id;
693   $storno_row->{storno_id} = $id;
694   $storno_row->{storno}    = 't';
695   $storno_row->{reference} = 'Storno-' . $storno_row->{reference};
696
697   delete @$storno_row{qw(itime mtime)};
698
699   $query = sprintf 'INSERT INTO gl (%s) VALUES (%s)', join(', ', keys %$storno_row), join(', ', map '?', values %$storno_row);
700   do_query($form, $dbh, $query, (values %$storno_row));
701
702   $query = qq|UPDATE gl SET storno = 't' WHERE id = ?|;
703   do_query($form, $dbh, $query, $id);
704
705   # now copy acc_trans entries
706   $query = qq|SELECT * FROM acc_trans WHERE trans_id = ?|;
707   my $rowref = selectall_hashref_query($form, $dbh, $query, $id); 
708
709   for my $row (@$rowref) {
710     delete @$row{qw(itime mtime)};
711     $query = sprintf 'INSERT INTO acc_trans (%s) VALUES (%s)', join(', ', keys %$row), join(', ', map '?', values %$row);
712     $row->{trans_id}   = $new_id;
713     $row->{amount}    *= -1;
714     do_query($form, $dbh, $query, (values %$row));
715   }
716
717   $dbh->commit;
718
719   $main::lxdebug->leave_sub();
720 }
721
722 sub get_chart_balances {
723   $main::lxdebug->enter_sub();
724
725   my $self     = shift;
726   my %params   = @_;
727
728   Common::check_params(\%params, qw(charts));
729
730   my $myconfig = \%main::myconfig;
731   my $form     = $main::form;
732
733   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
734
735   my @ids      = map { $_->{id} } @{ $params{charts} };
736
737   if (!@ids) {
738     $main::lxdebug->leave_sub();
739     return;
740   }
741
742   my $query = qq|SELECT chart_id, SUM(amount) AS sum
743                  FROM acc_trans
744                  WHERE chart_id IN (| . join(', ', ('?') x scalar(@ids)) . qq|)
745                  GROUP BY chart_id|;
746
747   my %balances = selectall_as_map($form, $dbh, $query, 'chart_id', 'sum', @ids);
748
749   foreach my $chart (@{ $params{charts} }) {
750     $chart->{balance} = $balances{ $chart->{id} } || 0;
751   }
752
753   $main::lxdebug->leave_sub();
754 }
755
756
757 1;