1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
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.
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., 51 Franklin Street, Fifth Floor, Boston,
30 #======================================================================
32 # General ledger backend code
35 # DS. 2000-07-04 Created
36 # DS. 2001-06-12 Changed relations from accno to chart_id
38 #======================================================================
42 use List::Util qw(first);
45 use SL::DATEV qw(:CONSTANTS);
49 use SL::Util qw(trim);
54 sub delete_transaction {
55 my ($self, $myconfig, $form) = @_;
56 $main::lxdebug->enter_sub();
58 SL::DB->client->with_transaction(sub {
59 do_query($form, SL::DB->client->dbh, qq|DELETE FROM gl WHERE id = ?|, conv_i($form->{id}));
61 }) or do { die SL::DB->client->error };
63 $main::lxdebug->leave_sub();
66 sub post_transaction {
67 my ($self, $myconfig, $form) = @_;
68 $main::lxdebug->enter_sub();
70 my $rc = SL::DB->client->with_transaction(\&_post_transaction, $self, $myconfig, $form);
72 $::lxdebug->leave_sub;
76 sub _post_transaction {
77 my ($self, $myconfig, $form) = @_;
78 $main::lxdebug->enter_sub();
80 my ($debit, $credit) = (0, 0);
85 my $dbh = SL::DB->client->dbh;
87 # post the transaction
88 # make up a unique handle and store in reference field
89 # then retrieve the record based on the unique handle to get the id
90 # replace the reference field with the actual variable
91 # add records to acc_trans
93 # if there is a $form->{id} replace the old transaction
94 # delete all acc_trans entries and add the new ones
96 if (!$form->{taxincluded}) {
97 $form->{taxincluded} = 0;
100 my ($query, $sth, @values, $taxkey, $rate, $posted);
104 # delete individual transactions
105 $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
106 @values = (conv_i($form->{id}));
107 do_query($form, $dbh, $query, @values);
110 $query = qq|SELECT nextval('glid')|;
111 ($form->{id}) = selectrow_query($form, $dbh, $query);
114 qq|INSERT INTO gl (id, employee_id) | .
115 qq|VALUES (?, (SELECT id FROM employee WHERE login = ?))|;
116 @values = ($form->{id}, $::myconfig{login});
117 do_query($form, $dbh, $query, @values);
120 $form->{ob_transaction} *= 1;
121 $form->{cb_transaction} *= 1;
125 reference = ?, description = ?, notes = ?,
126 transdate = ?, deliverydate = ?, tax_point = ?, department_id = ?, taxincluded = ?,
127 storno = ?, storno_id = ?, ob_transaction = ?, cb_transaction = ?
130 @values = ($form->{reference}, $form->{description}, $form->{notes},
131 conv_date($form->{transdate}), conv_date($form->{deliverydate}), conv_date($form->{tax_point}), conv_i($form->{department_id}), $form->{taxincluded} ? 't' : 'f',
132 $form->{storno} ? 't' : 'f', conv_i($form->{storno_id}), $form->{ob_transaction} ? 't' : 'f', $form->{cb_transaction} ? 't' : 'f',
133 conv_i($form->{id}));
134 do_query($form, $dbh, $query, @values);
136 # insert acc_trans transactions
137 for $i (1 .. $form->{rowcount}) {
138 ($form->{"tax_id_$i"}) = split(/--/, $form->{"taxchart_$i"});
139 if ($form->{"tax_id_$i"} ne "") {
140 $query = qq|SELECT taxkey, rate FROM tax WHERE id = ?|;
141 ($taxkey, $rate) = selectrow_query($form, $dbh, $query, conv_i($form->{"tax_id_$i"}));
145 my $debit = $form->{"debit_$i"};
146 my $credit = $form->{"credit_$i"};
147 my $tax = $form->{"tax_$i"};
154 $amount = $debit * -1;
159 $project_id = conv_i($form->{"project_id_$i"});
161 # if there is an amount, add the record
164 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
165 source, memo, project_id, taxkey, ob_transaction, cb_transaction, tax_id, chart_link)
166 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, (SELECT link FROM chart WHERE id = ?))|;
167 @values = (conv_i($form->{id}), $form->{"accno_id_$i"}, $amount, conv_date($form->{transdate}),
168 $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"}), $form->{"accno_id_$i"});
169 do_query($form, $dbh, $query, @values);
175 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
176 source, memo, project_id, taxkey, tax_id, chart_link)
177 VALUES (?, (SELECT chart_id FROM tax WHERE id = ?),
178 ?, ?, ?, ?, ?, ?, ?, (SELECT link
180 WHERE id = (SELECT chart_id
183 @values = (conv_i($form->{id}), conv_i($form->{"tax_id_$i"}),
184 $tax, conv_date($form->{transdate}), $form->{"source_$i"},
185 $form->{"memo_$i"}, $project_id, $taxkey, conv_i($form->{"tax_id_$i"}), conv_i($form->{"tax_id_$i"}));
186 do_query($form, $dbh, $query, @values);
190 if ($form->{storno} && $form->{storno_id}) {
191 do_query($form, $dbh, qq|UPDATE gl SET storno = 't' WHERE id = ?|, conv_i($form->{storno_id}));
194 if ($form->{draft_id}) {
195 SL::DB::Manager::Draft->delete_all(where => [ id => delete($form->{draft_id}) ]);
198 # safety check datev export
199 if ($::instance_conf->get_datev_check_on_gl_transaction) {
201 # create datev object
202 my $datev = SL::DATEV->new(
204 trans_id => $form->{id},
207 $datev->generate_datev_data;
209 if ($datev->errors) {
210 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
217 sub all_transactions {
218 my ($self, $myconfig, $form) = @_;
219 $main::lxdebug->enter_sub();
221 my $dbh = SL::DB->client->dbh;
222 my ($query, $sth, $source, $null, $space);
224 my ($glwhere, $arwhere, $apwhere) = ("1 = 1", "1 = 1", "1 = 1");
225 my (@glvalues, @arvalues, @apvalues);
227 if ($form->{reference}) {
228 $glwhere .= qq| AND g.reference ILIKE ?|;
229 $arwhere .= qq| AND a.invnumber ILIKE ?|;
230 $apwhere .= qq| AND a.invnumber ILIKE ?|;
231 push(@glvalues, like($form->{reference}));
232 push(@arvalues, like($form->{reference}));
233 push(@apvalues, like($form->{reference}));
236 if ($form->{department_id}) {
237 $glwhere .= qq| AND g.department_id = ?|;
238 $arwhere .= qq| AND a.department_id = ?|;
239 $apwhere .= qq| AND a.department_id = ?|;
240 push(@glvalues, $form->{department_id});
241 push(@arvalues, $form->{department_id});
242 push(@apvalues, $form->{department_id});
245 if ($form->{source}) {
246 $glwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)";
247 $arwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)";
248 $apwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)";
249 push(@glvalues, like($form->{source}));
250 push(@arvalues, like($form->{source}));
251 push(@apvalues, like($form->{source}));
254 # default Datumseinschränkung falls nicht oder falsch übergeben (sollte nie passieren)
255 $form->{datesort} = 'transdate' unless $form->{datesort} =~ /^(transdate|gldate)$/;
257 if (trim($form->{datefrom})) {
258 $glwhere .= " AND ac.$form->{datesort} >= ?";
259 $arwhere .= " AND ac.$form->{datesort} >= ?";
260 $apwhere .= " AND ac.$form->{datesort} >= ?";
261 push(@glvalues, trim($form->{datefrom}));
262 push(@arvalues, trim($form->{datefrom}));
263 push(@apvalues, trim($form->{datefrom}));
266 if (trim($form->{dateto})) {
267 $glwhere .= " AND ac.$form->{datesort} <= ?";
268 $arwhere .= " AND ac.$form->{datesort} <= ?";
269 $apwhere .= " AND ac.$form->{datesort} <= ?";
270 push(@glvalues, trim($form->{dateto}));
271 push(@arvalues, trim($form->{dateto}));
272 push(@apvalues, trim($form->{dateto}));
275 if (trim($form->{description})) {
276 $glwhere .= " AND g.description ILIKE ?";
277 $arwhere .= " AND ct.name ILIKE ?";
278 $apwhere .= " AND ct.name ILIKE ?";
279 push(@glvalues, like($form->{description}));
280 push(@arvalues, like($form->{description}));
281 push(@apvalues, like($form->{description}));
284 if ($form->{employee_id}) {
285 $glwhere .= " AND g.employee_id = ? ";
286 $arwhere .= " AND a.employee_id = ? ";
287 $apwhere .= " AND a.employee_id = ? ";
288 push(@glvalues, conv_i($form->{employee_id}));
289 push(@arvalues, conv_i($form->{employee_id}));
290 push(@apvalues, conv_i($form->{employee_id}));
293 if (trim($form->{notes})) {
294 $glwhere .= " AND g.notes ILIKE ?";
295 $arwhere .= " AND a.notes ILIKE ?";
296 $apwhere .= " AND a.notes ILIKE ?";
297 push(@glvalues, like($form->{notes}));
298 push(@arvalues, like($form->{notes}));
299 push(@apvalues, like($form->{notes}));
302 if ($form->{accno}) {
303 $glwhere .= " AND c.accno = '$form->{accno}'";
304 $arwhere .= " AND c.accno = '$form->{accno}'";
305 $apwhere .= " AND c.accno = '$form->{accno}'";
308 if ($form->{category} ne 'X') {
309 $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 = ?))|;
310 $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 = ?))|;
311 $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 = ?))|;
312 push(@glvalues, $form->{category});
313 push(@arvalues, $form->{category});
314 push(@apvalues, $form->{category});
317 if ($form->{project_id}) {
318 $glwhere .= qq| AND g.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)|;
320 qq| AND ((a.globalproject_id = ?) OR
321 (a.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)))|;
323 qq| AND ((a.globalproject_id = ?) OR
324 (a.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)))|;
325 my $project_id = conv_i($form->{project_id});
326 push(@glvalues, $project_id);
327 push(@arvalues, $project_id, $project_id);
328 push(@apvalues, $project_id, $project_id);
331 my ($project_columns, $project_join);
332 my ($arap_globalproject_columns, $arap_globalproject_join);
333 my ($gl_globalproject_columns);
334 if ($form->{"l_projectnumbers"}) {
335 $project_columns = qq|, ac.project_id, pr.projectnumber|;
336 $project_join = qq|LEFT JOIN project pr ON (ac.project_id = pr.id)|;
337 $arap_globalproject_columns = qq|, a.globalproject_id, globalpr.projectnumber AS globalprojectnumber|;
338 $arap_globalproject_join = qq|LEFT JOIN project globalpr ON (a.globalproject_id = globalpr.id)|;
339 $gl_globalproject_columns = qq|, NULL AS globalproject_id, '' AS globalprojectnumber|;
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});
347 if ($form->{datefrom}) {
349 qq|SELECT SUM(ac.amount)
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}));
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 'department' => [ qw(department transdate id) ],
366 my %lowered_columns = (
367 'reference' => { 'gl' => 'g.reference', 'arap' => 'a.invnumber', },
368 'source' => { 'gl' => 'ac.source', 'arap' => 'ac.source', },
369 'description' => { 'gl' => 'g.description', 'arap' => 'ct.name', },
372 # sortdir = sort direction (ascending or descending)
373 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
374 my $sortkey = $sort_columns{$form->{sort}} ? $form->{sort} : $form->{datesort}; # default used to be transdate
375 my $sortorder = join ', ', map { "$_ $sortdir" } @{ $sort_columns{$sortkey} };
377 my %columns_for_sorting = ( 'gl' => '', 'arap' => '', );
378 foreach my $spec (@{ $sort_columns{$sortkey} }) {
379 next if ($spec !~ m/^lower_(.*)$/);
382 map { $columns_for_sorting{$_} .= sprintf(', lower(%s) AS lower_%s', $lowered_columns{$column}->{$_}, $column) } qw(gl arap);
387 ac.acc_trans_id, g.id, 'gl' AS type, FALSE AS invoice, g.reference, ac.taxkey, c.link,
388 g.description, ac.transdate, ac.gldate, ac.source, ac.trans_id,
389 ac.amount, c.accno, g.notes, t.chart_id,
390 d.description AS department,
391 CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
392 $project_columns $gl_globalproject_columns
393 $columns_for_sorting{gl}
395 LEFT JOIN employee e ON (g.employee_id = e.id)
396 LEFT JOIN department d ON (g.department_id = d.id),
397 acc_trans ac $project_join, chart c
398 LEFT JOIN tax t ON (t.chart_id = c.id)
400 AND (ac.chart_id = c.id)
401 AND (g.id = ac.trans_id)
405 SELECT ac.acc_trans_id, a.id, 'ar' AS type, a.invoice, a.invnumber, ac.taxkey, c.link,
406 ct.name, ac.transdate, ac.gldate, ac.source, ac.trans_id,
407 ac.amount, c.accno, a.notes, t.chart_id,
408 d.description AS department,
409 CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
410 $project_columns $arap_globalproject_columns
411 $columns_for_sorting{arap}
413 LEFT JOIN employee e ON (a.employee_id = e.id)
414 LEFT JOIN department d ON (a.department_id = d.id)
415 $arap_globalproject_join,
416 acc_trans ac $project_join, customer ct, chart c
417 LEFT JOIN tax t ON (t.chart_id=c.id)
419 AND (ac.chart_id = c.id)
420 AND (a.customer_id = ct.id)
421 AND (a.id = ac.trans_id)
425 SELECT ac.acc_trans_id, a.id, 'ap' AS type, a.invoice, a.invnumber, ac.taxkey, c.link,
426 ct.name, ac.transdate, ac.gldate, ac.source, ac.trans_id,
427 ac.amount, c.accno, a.notes, t.chart_id,
428 d.description AS department,
429 CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
430 $project_columns $arap_globalproject_columns
431 $columns_for_sorting{arap}
433 LEFT JOIN employee e ON (a.employee_id = e.id)
434 LEFT JOIN department d ON (a.department_id = d.id)
435 $arap_globalproject_join,
436 acc_trans ac $project_join, vendor ct, chart c
437 LEFT JOIN tax t ON (t.chart_id=c.id)
439 AND (ac.chart_id = c.id)
440 AND (a.vendor_id = ct.id)
441 AND (a.id = ac.trans_id)
443 ORDER BY $sortorder, acc_trans_id $sortdir|;
444 # ORDER BY gldate DESC, id DESC, acc_trans_id DESC
446 my @values = (@glvalues, @arvalues, @apvalues);
448 # Show all $query in Debuglevel LXDebug::QUERY
449 my $callingdetails = (caller (0))[3];
450 dump_query(LXDebug->QUERY(), "$callingdetails", $query, @values);
452 $sth = prepare_execute_query($form, $dbh, $query, @values);
457 my ($i, $j, $k, $l, $ref, $ref2);
460 while (my $ref0 = $sth->fetchrow_hashref("NAME_lc")) {
462 $trans_id = $ref0->{id};
464 my $source = $ref0->{source};
465 undef($ref0->{source});
467 if ($trans_id != $trans_id2) { # first line of a booking
470 push(@{ $form->{GL} }, $ref);
475 $trans_id2 = $ref->{id};
478 if ($ref->{type} eq "gl") {
479 $ref->{module} = "gl";
483 if ($ref->{type} eq "ap") {
484 if ($ref->{invoice}) {
485 $ref->{module} = "ir";
487 $ref->{module} = "ap";
492 if ($ref->{type} eq "ar") {
493 if ($ref->{invoice}) {
494 $ref->{module} = "is";
496 $ref->{module} = "ar";
500 $ref->{"projectnumbers"} = {};
501 $ref->{"projectnumbers"}->{$ref->{"projectnumber"}} = 1 if ($ref->{"projectnumber"});
502 $ref->{"projectnumbers"}->{$ref->{"globalprojectnumber"}} = 1 if ($ref->{"globalprojectnumber"});
504 $balance = $ref->{amount};
506 # Linenumbers of General Ledger
507 $k = 0; # Debit # AP # Soll
508 $l = 0; # Credit # AR # Haben
509 $i = 0; # Debit Tax # AP_tax # VSt
510 $j = 0; # Credit Tax # AR_tax # USt
512 if ($ref->{chart_id} > 0) { # all tax accounts first line, no line increasing
513 if ($ref->{amount} < 0) {
514 if ($ref->{link} =~ /AR_tax/) {
515 $ref->{credit_tax}{$j} = $ref->{amount};
516 $ref->{credit_tax_accno}{$j} = $ref->{accno};
518 if ($ref->{link} =~ /AP_tax/) {
519 $ref->{debit_tax}{$i} = $ref->{amount} * -1;
520 $ref->{debit_tax_accno}{$i} = $ref->{accno};
523 if ($ref->{link} =~ /AR_tax/) {
524 $ref->{credit_tax}{$j} = $ref->{amount};
525 $ref->{credit_tax_accno}{$j} = $ref->{accno};
527 if ($ref->{link} =~ /AP_tax/) {
528 $ref->{debit_tax}{$i} = $ref->{amount} * -1;
529 $ref->{debit_tax_accno}{$i} = $ref->{accno};
532 } else { #all other accounts first line
534 if ($ref->{amount} < 0) {
535 $ref->{debit}{$k} = $ref->{amount} * -1;
536 $ref->{debit_accno}{$k} = $ref->{accno};
537 $ref->{debit_taxkey}{$k} = $ref->{taxkey};
538 $ref->{ac_transdate}{$k} = $ref->{transdate};
539 $ref->{source}{$k} = $source;
541 $ref->{credit}{$l} = $ref->{amount} * 1;
542 $ref->{credit_accno}{$l} = $ref->{accno};
543 $ref->{credit_taxkey}{$l} = $ref->{taxkey};
544 $ref->{ac_transdate}{$l} = $ref->{transdate};
545 $ref->{source}{$l} = $source;
549 } else { # following lines of a booking, line increasing
552 # $trans_old = $trans_id2; # doesn't seem to be used anymore
553 $trans_id2 = $ref2->{id};
556 (int($balance * 100000) + int(100000 * $ref2->{amount})) / 100000;
558 $ref->{"projectnumbers"}->{$ref2->{"projectnumber"}} = 1 if ($ref2->{"projectnumber"});
559 $ref->{"projectnumbers"}->{$ref2->{"globalprojectnumber"}} = 1 if ($ref2->{"globalprojectnumber"});
561 if ($ref2->{chart_id} > 0) { # all tax accounts, following lines
562 if ($ref2->{amount} < 0) {
563 if ($ref2->{link} =~ /AR_tax/) {
564 if ($ref->{credit_tax_accno}{$j} ne "") {
567 $ref->{credit_tax}{$j} = $ref2->{amount};
568 $ref->{credit_tax_accno}{$j} = $ref2->{accno};
570 if ($ref2->{link} =~ /AP_tax/) {
571 if ($ref->{debit_tax_accno}{$i} ne "") {
574 $ref->{debit_tax}{$i} = $ref2->{amount} * -1;
575 $ref->{debit_tax_accno}{$i} = $ref2->{accno};
578 if ($ref2->{link} =~ /AR_tax/) {
579 if ($ref->{credit_tax_accno}{$j} ne "") {
582 $ref->{credit_tax}{$j} = $ref2->{amount};
583 $ref->{credit_tax_accno}{$j} = $ref2->{accno};
585 if ($ref2->{link} =~ /AP_tax/) {
586 if ($ref->{debit_tax_accno}{$i} ne "") {
589 $ref->{debit_tax}{$i} = $ref2->{amount} * -1;
590 $ref->{debit_tax_accno}{$i} = $ref2->{accno};
593 } else { # all other accounts, following lines
594 if ($ref2->{amount} < 0) {
595 if ($ref->{debit_accno}{$k} ne "") {
598 if ($ref->{source}{$k} ne "") {
603 $ref->{debit}{$k} = $ref2->{amount} * - 1;
604 $ref->{debit_accno}{$k} = $ref2->{accno};
605 $ref->{debit_taxkey}{$k} = $ref2->{taxkey};
606 $ref->{ac_transdate}{$k} = $ref2->{transdate};
607 $ref->{source}{$k} = $source . $space . $ref->{source}{$k};
609 if ($ref->{credit_accno}{$l} ne "") {
612 if ($ref->{source}{$l} ne "") {
617 $ref->{credit}{$l} = $ref2->{amount};
618 $ref->{credit_accno}{$l} = $ref2->{accno};
619 $ref->{credit_taxkey}{$l} = $ref2->{taxkey};
620 $ref->{ac_transdate}{$l} = $ref2->{transdate};
621 $ref->{source}{$l} = $ref->{source}{$l} . $space . $source;
627 push @{ $form->{GL} }, $ref;
630 if ($form->{accno}) {
631 $query = qq|SELECT c.description FROM chart c WHERE c.accno = ?|;
632 ($form->{account_description}) = selectrow_query($form, $dbh, $query, $form->{accno});
635 $main::lxdebug->leave_sub();
639 my ($self, $myconfig, $form) = @_;
640 $main::lxdebug->enter_sub();
642 my ($query, $sth, $ref, @values);
644 my $dbh = SL::DB->client->dbh;
646 $query = qq|SELECT closedto, revtrans FROM defaults|;
647 ($form->{closedto}, $form->{revtrans}) = selectrow_query($form, $dbh, $query);
649 $query = qq|SELECT id, gldate
651 WHERE id = (SELECT max(id) FROM gl)|;
652 ($form->{previous_id}, $form->{previous_gldate}) = selectrow_query($form, $dbh, $query);
656 qq|SELECT g.reference, g.description, g.notes, g.transdate, g.deliverydate, g.tax_point,
657 g.storno, g.storno_id,
658 g.department_id, d.description AS department,
659 e.name AS employee, g.taxincluded, g.gldate,
660 g.ob_transaction, g.cb_transaction
662 LEFT JOIN department d ON (d.id = g.department_id)
663 LEFT JOIN employee e ON (e.id = g.employee_id)
665 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
666 map { $form->{$_} = $ref->{$_} } keys %$ref;
668 # retrieve individual rows
670 qq|SELECT c.accno, t.taxkey AS accnotaxkey, a.amount, a.memo, a.source,
671 a.transdate, a.cleared, a.project_id, p.projectnumber,
672 a.taxkey, t.rate AS taxrate, t.id, a.chart_id,
674 FROM chart c1, tax t1
675 WHERE (t1.id = t.id) AND (c1.id = t.chart_id)) AS taxaccno,
678 WHERE (tk.chart_id = a.chart_id) AND (tk.startdate <= a.transdate)
679 ORDER BY tk.startdate desc LIMIT 1) AS tax_id
681 JOIN chart c ON (c.id = a.chart_id)
682 LEFT JOIN project p ON (p.id = a.project_id)
683 LEFT JOIN tax t ON (t.id = a.tax_id)
684 WHERE (a.trans_id = ?)
685 AND (a.fx_transaction = '0')
686 ORDER BY a.acc_trans_id, a.transdate|;
687 $form->{GL} = selectall_hashref_query($form, $dbh, $query, conv_i($form->{id}));
694 WHERE id = (SELECT MAX(id) FROM gl)
697 ($form->{transdate}) = selectrow_query($form, $dbh, $query);
700 # get tax description
701 $query = qq|SELECT * FROM tax ORDER BY taxkey|;
702 $form->{TAX} = selectall_hashref_query($form, $dbh, $query);
704 # get chart of accounts
706 qq|SELECT c.accno, c.description, c.link, tk.taxkey_id, tk.tax_id
708 LEFT JOIN taxkeys tk ON (tk.id =
711 WHERE (taxkeys.chart_id = c.id)
713 ORDER BY startdate DESC
716 $form->{chart} = selectall_hashref_query($form, $dbh, $query, conv_date($form->{transdate}));
718 $main::lxdebug->leave_sub();
722 my ($self, $form, $myconfig, $id) = @_;
723 $main::lxdebug->enter_sub();
725 my $rc = SL::DB->client->with_transaction(\&_storno, $self, $form, $myconfig, $id);
727 $::lxdebug->leave_sub;
732 my ($self, $form, $myconfig, $id) = @_;
734 my ($query, $new_id, $storno_row, $acc_trans_rows);
735 my $dbh = SL::DB->client->dbh;
737 $query = qq|SELECT nextval('glid')|;
738 ($new_id) = selectrow_query($form, $dbh, $query);
740 $query = qq|SELECT * FROM gl WHERE id = ?|;
741 $storno_row = selectfirst_hashref_query($form, $dbh, $query, $id);
743 $storno_row->{id} = $new_id;
744 $storno_row->{storno_id} = $id;
745 $storno_row->{storno} = 't';
746 $storno_row->{reference} = 'Storno-' . $storno_row->{reference};
748 $query = qq|SELECT id FROM employee WHERE login = ?|;
749 my ($employee_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
750 $storno_row->{employee_id} = $employee_id;
752 delete @$storno_row{qw(itime mtime gldate)};
754 $query = sprintf 'INSERT INTO gl (%s) VALUES (%s)', join(', ', keys %$storno_row), join(', ', map '?', values %$storno_row);
755 do_query($form, $dbh, $query, (values %$storno_row));
757 $query = qq|UPDATE gl SET storno = 't' WHERE id = ?|;
758 do_query($form, $dbh, $query, $id);
760 # now copy acc_trans entries
761 $query = qq|SELECT * FROM acc_trans WHERE trans_id = ?|;
762 my $rowref = selectall_hashref_query($form, $dbh, $query, $id);
764 for my $row (@$rowref) {
765 delete @$row{qw(itime mtime acc_trans_id gldate)};
766 $query = sprintf 'INSERT INTO acc_trans (%s) VALUES (%s)', join(', ', keys %$row), join(', ', map '?', values %$row);
767 $row->{trans_id} = $new_id;
768 $row->{amount} *= -1;
769 do_query($form, $dbh, $query, (values %$row));
775 sub get_chart_balances {
776 my ($self, @chart_ids) = @_;
778 return () unless @chart_ids;
780 my $placeholders = join ', ', ('?') x scalar(@chart_ids);
781 my $query = qq|SELECT chart_id, SUM(amount) AS sum
783 WHERE chart_id IN (${placeholders})
786 my %balances = selectall_as_map($::form, $::form->get_standard_dbh(\%::myconfig), $query, 'chart_id', 'sum', @chart_ids);
791 sub get_active_taxes_for_chart {
792 my ($self, $chart_id, $transdate, $tax_id) = @_;
794 my $chart = SL::DB::Chart->new(id => $chart_id)->load;
795 my $active_taxkey = $chart->get_active_taxkey($transdate);
797 my $where = [ chart_categories => { like => '%' . $chart->category . '%' } ];
799 if ( defined $tax_id && $tax_id >= 0 ) {
800 $where = [ or => [ chart_categories => { like => '%' . $chart->category . '%' },
806 my $taxes = SL::DB::Manager::Tax->get_all(
808 sort_by => 'taxkey, rate',
811 my $default_tax = first { $active_taxkey->tax_id == $_->id } @{ $taxes };
812 $default_tax->{is_default} = 1 if $default_tax;
827 SL::GL - some useful GL functions
833 =item C<get_active_taxes_for_chart> $transdate $tax_id
835 Returns a list of valid taxes for a certain chart.
837 If the optional param transdate exists one entry in the returning list
838 may get the attribute C<is_default> for this specific tax-dependent date.
839 The possible entries are filtered by the charttype of the tax, i.e. only taxes
840 whose chart_categories match the category of the chart will be shown.
842 In the case of existing records, e.g. when opening an old ar record, due to
843 changes in the configurations the desired tax might not be available in the
844 dropdown anymore. If we are loading an old record and know its tax_id (from
845 acc_trans), we can pass $tax_id as the third parameter and be sure that the
846 original tax always appears in the dropdown.
848 The functions returns an array which may be used for building dropdowns in ar/ap/gl code.
862 G. Richardson E<lt>grichardson@kivitec.deE<gt>