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 if ($form->{"l_projectnumbers"}) {
333 $project_columns = qq|, ac.project_id, pr.projectnumber|;
334 $project_join = qq|LEFT JOIN project pr ON (ac.project_id = pr.id)|;
337 if ($form->{accno}) {
338 # get category for account
339 $query = qq|SELECT category FROM chart WHERE accno = ?|;
340 ($form->{ml}) = selectrow_query($form, $dbh, $query, $form->{accno});
342 if ($form->{datefrom}) {
344 qq|SELECT SUM(ac.amount)
346 LEFT JOIN chart c ON (ac.chart_id = c.id)
347 WHERE (c.accno = ?) AND (ac.$form->{datesort} < ?)|;
348 ($form->{balance}) = selectrow_query($form, $dbh, $query, $form->{accno}, conv_date($form->{datefrom}));
354 'transdate' => [ qw(transdate id) ],
355 'gldate' => [ qw(gldate id) ],
356 'reference' => [ qw(lower_reference id) ],
357 'description' => [ qw(lower_description id) ],
358 'accno' => [ qw(accno transdate id) ],
359 'department' => [ qw(department transdate id) ],
361 my %lowered_columns = (
362 'reference' => { 'gl' => 'g.reference', 'arap' => 'a.invnumber', },
363 'source' => { 'gl' => 'ac.source', 'arap' => 'ac.source', },
364 'description' => { 'gl' => 'g.description', 'arap' => 'ct.name', },
367 # sortdir = sort direction (ascending or descending)
368 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
369 my $sortkey = $sort_columns{$form->{sort}} ? $form->{sort} : $form->{datesort}; # default used to be transdate
370 my $sortorder = join ', ', map { "$_ $sortdir" } @{ $sort_columns{$sortkey} };
372 my %columns_for_sorting = ( 'gl' => '', 'arap' => '', );
373 foreach my $spec (@{ $sort_columns{$sortkey} }) {
374 next if ($spec !~ m/^lower_(.*)$/);
377 map { $columns_for_sorting{$_} .= sprintf(', lower(%s) AS lower_%s', $lowered_columns{$column}->{$_}, $column) } qw(gl arap);
382 ac.acc_trans_id, g.id, 'gl' AS type, FALSE AS invoice, g.reference, ac.taxkey, c.link,
383 g.description, ac.transdate, ac.gldate, ac.source, ac.trans_id,
384 ac.amount, c.accno, g.notes, t.chart_id,
385 d.description AS department,
386 CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
388 $columns_for_sorting{gl}
390 LEFT JOIN employee e ON (g.employee_id = e.id)
391 LEFT JOIN department d ON (g.department_id = d.id),
392 acc_trans ac $project_join, chart c
393 LEFT JOIN tax t ON (t.chart_id = c.id)
395 AND (ac.chart_id = c.id)
396 AND (g.id = ac.trans_id)
400 SELECT ac.acc_trans_id, a.id, 'ar' AS type, a.invoice, a.invnumber, ac.taxkey, c.link,
401 ct.name, ac.transdate, ac.gldate, ac.source, ac.trans_id,
402 ac.amount, c.accno, a.notes, t.chart_id,
403 d.description AS department,
404 CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
406 $columns_for_sorting{arap}
408 LEFT JOIN employee e ON (a.employee_id = e.id)
409 LEFT JOIN department d ON (a.department_id = d.id),
410 acc_trans ac $project_join, customer ct, chart c
411 LEFT JOIN tax t ON (t.chart_id=c.id)
413 AND (ac.chart_id = c.id)
414 AND (a.customer_id = ct.id)
415 AND (a.id = ac.trans_id)
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 d.description AS department,
423 CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
425 $columns_for_sorting{arap}
427 LEFT JOIN employee e ON (a.employee_id = e.id)
428 LEFT JOIN department d ON (a.department_id = d.id),
429 acc_trans ac $project_join, vendor ct, chart c
430 LEFT JOIN tax t ON (t.chart_id=c.id)
432 AND (ac.chart_id = c.id)
433 AND (a.vendor_id = ct.id)
434 AND (a.id = ac.trans_id)
436 ORDER BY $sortorder, acc_trans_id $sortdir|;
437 # ORDER BY gldate DESC, id DESC, acc_trans_id DESC
439 my @values = (@glvalues, @arvalues, @apvalues);
441 # Show all $query in Debuglevel LXDebug::QUERY
442 my $callingdetails = (caller (0))[3];
443 dump_query(LXDebug->QUERY(), "$callingdetails", $query, @values);
445 $sth = prepare_execute_query($form, $dbh, $query, @values);
450 my ($i, $j, $k, $l, $ref, $ref2);
453 while (my $ref0 = $sth->fetchrow_hashref("NAME_lc")) {
455 $trans_id = $ref0->{id};
457 my $source = $ref0->{source};
458 undef($ref0->{source});
460 if ($trans_id != $trans_id2) { # first line of a booking
463 push(@{ $form->{GL} }, $ref);
468 $trans_id2 = $ref->{id};
471 if ($ref->{type} eq "gl") {
472 $ref->{module} = "gl";
476 if ($ref->{type} eq "ap") {
477 if ($ref->{invoice}) {
478 $ref->{module} = "ir";
480 $ref->{module} = "ap";
485 if ($ref->{type} eq "ar") {
486 if ($ref->{invoice}) {
487 $ref->{module} = "is";
489 $ref->{module} = "ar";
493 $ref->{"projectnumbers"} = {};
494 $ref->{"projectnumbers"}->{$ref->{"projectnumber"}} = 1 if ($ref->{"projectnumber"});
496 $balance = $ref->{amount};
498 # Linenumbers of General Ledger
499 $k = 0; # Debit # AP # Soll
500 $l = 0; # Credit # AR # Haben
501 $i = 0; # Debit Tax # AP_tax # VSt
502 $j = 0; # Credit Tax # AR_tax # USt
504 if ($ref->{chart_id} > 0) { # all tax accounts first line, no line increasing
505 if ($ref->{amount} < 0) {
506 if ($ref->{link} =~ /AR_tax/) {
507 $ref->{credit_tax}{$j} = $ref->{amount};
508 $ref->{credit_tax_accno}{$j} = $ref->{accno};
510 if ($ref->{link} =~ /AP_tax/) {
511 $ref->{debit_tax}{$i} = $ref->{amount} * -1;
512 $ref->{debit_tax_accno}{$i} = $ref->{accno};
515 if ($ref->{link} =~ /AR_tax/) {
516 $ref->{credit_tax}{$j} = $ref->{amount};
517 $ref->{credit_tax_accno}{$j} = $ref->{accno};
519 if ($ref->{link} =~ /AP_tax/) {
520 $ref->{debit_tax}{$i} = $ref->{amount} * -1;
521 $ref->{debit_tax_accno}{$i} = $ref->{accno};
524 } else { #all other accounts first line
526 if ($ref->{amount} < 0) {
527 $ref->{debit}{$k} = $ref->{amount} * -1;
528 $ref->{debit_accno}{$k} = $ref->{accno};
529 $ref->{debit_taxkey}{$k} = $ref->{taxkey};
530 $ref->{ac_transdate}{$k} = $ref->{transdate};
531 $ref->{source}{$k} = $source;
533 $ref->{credit}{$l} = $ref->{amount} * 1;
534 $ref->{credit_accno}{$l} = $ref->{accno};
535 $ref->{credit_taxkey}{$l} = $ref->{taxkey};
536 $ref->{ac_transdate}{$l} = $ref->{transdate};
537 $ref->{source}{$l} = $source;
541 } else { # following lines of a booking, line increasing
544 # $trans_old = $trans_id2; # doesn't seem to be used anymore
545 $trans_id2 = $ref2->{id};
548 (int($balance * 100000) + int(100000 * $ref2->{amount})) / 100000;
550 $ref->{"projectnumbers"}->{$ref2->{"projectnumber"}} = 1 if ($ref2->{"projectnumber"});
552 if ($ref2->{chart_id} > 0) { # all tax accounts, following lines
553 if ($ref2->{amount} < 0) {
554 if ($ref2->{link} =~ /AR_tax/) {
555 if ($ref->{credit_tax_accno}{$j} ne "") {
558 $ref->{credit_tax}{$j} = $ref2->{amount};
559 $ref->{credit_tax_accno}{$j} = $ref2->{accno};
561 if ($ref2->{link} =~ /AP_tax/) {
562 if ($ref->{debit_tax_accno}{$i} ne "") {
565 $ref->{debit_tax}{$i} = $ref2->{amount} * -1;
566 $ref->{debit_tax_accno}{$i} = $ref2->{accno};
569 if ($ref2->{link} =~ /AR_tax/) {
570 if ($ref->{credit_tax_accno}{$j} ne "") {
573 $ref->{credit_tax}{$j} = $ref2->{amount};
574 $ref->{credit_tax_accno}{$j} = $ref2->{accno};
576 if ($ref2->{link} =~ /AP_tax/) {
577 if ($ref->{debit_tax_accno}{$i} ne "") {
580 $ref->{debit_tax}{$i} = $ref2->{amount} * -1;
581 $ref->{debit_tax_accno}{$i} = $ref2->{accno};
584 } else { # all other accounts, following lines
585 if ($ref2->{amount} < 0) {
586 if ($ref->{debit_accno}{$k} ne "") {
589 if ($ref->{source}{$k} ne "") {
594 $ref->{debit}{$k} = $ref2->{amount} * - 1;
595 $ref->{debit_accno}{$k} = $ref2->{accno};
596 $ref->{debit_taxkey}{$k} = $ref2->{taxkey};
597 $ref->{ac_transdate}{$k} = $ref2->{transdate};
598 $ref->{source}{$k} = $source . $space . $ref->{source}{$k};
600 if ($ref->{credit_accno}{$l} ne "") {
603 if ($ref->{source}{$l} ne "") {
608 $ref->{credit}{$l} = $ref2->{amount};
609 $ref->{credit_accno}{$l} = $ref2->{accno};
610 $ref->{credit_taxkey}{$l} = $ref2->{taxkey};
611 $ref->{ac_transdate}{$l} = $ref2->{transdate};
612 $ref->{source}{$l} = $ref->{source}{$l} . $space . $source;
618 push @{ $form->{GL} }, $ref;
621 if ($form->{accno}) {
622 $query = qq|SELECT c.description FROM chart c WHERE c.accno = ?|;
623 ($form->{account_description}) = selectrow_query($form, $dbh, $query, $form->{accno});
626 $main::lxdebug->leave_sub();
630 my ($self, $myconfig, $form) = @_;
631 $main::lxdebug->enter_sub();
633 my ($query, $sth, $ref, @values);
635 my $dbh = SL::DB->client->dbh;
637 $query = qq|SELECT closedto, revtrans FROM defaults|;
638 ($form->{closedto}, $form->{revtrans}) = selectrow_query($form, $dbh, $query);
640 $query = qq|SELECT id, gldate
642 WHERE id = (SELECT max(id) FROM gl)|;
643 ($form->{previous_id}, $form->{previous_gldate}) = selectrow_query($form, $dbh, $query);
647 qq|SELECT g.reference, g.description, g.notes, g.transdate, g.deliverydate, g.tax_point,
648 g.storno, g.storno_id,
649 g.department_id, d.description AS department,
650 e.name AS employee, g.taxincluded, g.gldate,
651 g.ob_transaction, g.cb_transaction
653 LEFT JOIN department d ON (d.id = g.department_id)
654 LEFT JOIN employee e ON (e.id = g.employee_id)
656 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
657 map { $form->{$_} = $ref->{$_} } keys %$ref;
659 # retrieve individual rows
661 qq|SELECT c.accno, t.taxkey AS accnotaxkey, a.amount, a.memo, a.source,
662 a.transdate, a.cleared, a.project_id, p.projectnumber,
663 a.taxkey, t.rate AS taxrate, t.id, a.chart_id,
665 FROM chart c1, tax t1
666 WHERE (t1.id = t.id) AND (c1.id = t.chart_id)) AS taxaccno,
669 WHERE (tk.chart_id = a.chart_id) AND (tk.startdate <= a.transdate)
670 ORDER BY tk.startdate desc LIMIT 1) AS tax_id
672 JOIN chart c ON (c.id = a.chart_id)
673 LEFT JOIN project p ON (p.id = a.project_id)
674 LEFT JOIN tax t ON (t.id = a.tax_id)
675 WHERE (a.trans_id = ?)
676 AND (a.fx_transaction = '0')
677 ORDER BY a.acc_trans_id, a.transdate|;
678 $form->{GL} = selectall_hashref_query($form, $dbh, $query, conv_i($form->{id}));
685 WHERE id = (SELECT MAX(id) FROM gl)
688 ($form->{transdate}) = selectrow_query($form, $dbh, $query);
691 # get tax description
692 $query = qq|SELECT * FROM tax ORDER BY taxkey|;
693 $form->{TAX} = selectall_hashref_query($form, $dbh, $query);
695 # get chart of accounts
697 qq|SELECT c.accno, c.description, c.link, tk.taxkey_id, tk.tax_id
699 LEFT JOIN taxkeys tk ON (tk.id =
702 WHERE (taxkeys.chart_id = c.id)
704 ORDER BY startdate DESC
707 $form->{chart} = selectall_hashref_query($form, $dbh, $query, conv_date($form->{transdate}));
709 $main::lxdebug->leave_sub();
713 my ($self, $form, $myconfig, $id) = @_;
714 $main::lxdebug->enter_sub();
716 my $rc = SL::DB->client->with_transaction(\&_storno, $self, $form, $myconfig, $id);
718 $::lxdebug->leave_sub;
723 my ($self, $form, $myconfig, $id) = @_;
725 my ($query, $new_id, $storno_row, $acc_trans_rows);
726 my $dbh = SL::DB->client->dbh;
728 $query = qq|SELECT nextval('glid')|;
729 ($new_id) = selectrow_query($form, $dbh, $query);
731 $query = qq|SELECT * FROM gl WHERE id = ?|;
732 $storno_row = selectfirst_hashref_query($form, $dbh, $query, $id);
734 $storno_row->{id} = $new_id;
735 $storno_row->{storno_id} = $id;
736 $storno_row->{storno} = 't';
737 $storno_row->{reference} = 'Storno-' . $storno_row->{reference};
739 $query = qq|SELECT id FROM employee WHERE login = ?|;
740 my ($employee_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
741 $storno_row->{employee_id} = $employee_id;
743 delete @$storno_row{qw(itime mtime gldate)};
745 $query = sprintf 'INSERT INTO gl (%s) VALUES (%s)', join(', ', keys %$storno_row), join(', ', map '?', values %$storno_row);
746 do_query($form, $dbh, $query, (values %$storno_row));
748 $query = qq|UPDATE gl SET storno = 't' WHERE id = ?|;
749 do_query($form, $dbh, $query, $id);
751 # now copy acc_trans entries
752 $query = qq|SELECT * FROM acc_trans WHERE trans_id = ?|;
753 my $rowref = selectall_hashref_query($form, $dbh, $query, $id);
755 for my $row (@$rowref) {
756 delete @$row{qw(itime mtime acc_trans_id gldate)};
757 $query = sprintf 'INSERT INTO acc_trans (%s) VALUES (%s)', join(', ', keys %$row), join(', ', map '?', values %$row);
758 $row->{trans_id} = $new_id;
759 $row->{amount} *= -1;
760 do_query($form, $dbh, $query, (values %$row));
766 sub get_chart_balances {
767 my ($self, @chart_ids) = @_;
769 return () unless @chart_ids;
771 my $placeholders = join ', ', ('?') x scalar(@chart_ids);
772 my $query = qq|SELECT chart_id, SUM(amount) AS sum
774 WHERE chart_id IN (${placeholders})
777 my %balances = selectall_as_map($::form, $::form->get_standard_dbh(\%::myconfig), $query, 'chart_id', 'sum', @chart_ids);
782 sub get_active_taxes_for_chart {
783 my ($self, $chart_id, $transdate, $tax_id) = @_;
785 my $chart = SL::DB::Chart->new(id => $chart_id)->load;
786 my $active_taxkey = $chart->get_active_taxkey($transdate);
788 my $where = [ chart_categories => { like => '%' . $chart->category . '%' } ];
790 if ( defined $tax_id && $tax_id >= 0 ) {
791 $where = [ or => [ chart_categories => { like => '%' . $chart->category . '%' },
797 my $taxes = SL::DB::Manager::Tax->get_all(
799 sort_by => 'taxkey, rate',
802 my $default_tax = first { $active_taxkey->tax_id == $_->id } @{ $taxes };
803 $default_tax->{is_default} = 1 if $default_tax;
818 SL::GL - some useful GL functions
824 =item C<get_active_taxes_for_chart> $transdate $tax_id
826 Returns a list of valid taxes for a certain chart.
828 If the optional param transdate exists one entry in the returning list
829 may get the attribute C<is_default> for this specific tax-dependent date.
830 The possible entries are filtered by the charttype of the tax, i.e. only taxes
831 whose chart_categories match the category of the chart will be shown.
833 In the case of existing records, e.g. when opening an old ar record, due to
834 changes in the configurations the desired tax might not be available in the
835 dropdown anymore. If we are loading an old record and know its tax_id (from
836 acc_trans), we can pass $tax_id as the third parameter and be sure that the
837 original tax always appears in the dropdown.
839 The functions returns an array which may be used for building dropdowns in ar/ap/gl code.
853 G. Richardson E<lt>grichardson@kivitec.deE<gt>