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