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);
48 use SL::Util qw(trim);
53 sub delete_transaction {
54 my ($self, $myconfig, $form) = @_;
55 $main::lxdebug->enter_sub();
57 SL::DB->client->with_transaction(sub {
58 do_query($form, SL::DB->client->dbh, qq|DELETE FROM gl WHERE id = ?|, conv_i($form->{id}));
60 }) or do { die SL::DB->client->error };
62 $main::lxdebug->leave_sub();
65 sub post_transaction {
66 my ($self, $myconfig, $form) = @_;
67 $main::lxdebug->enter_sub();
69 my $rc = SL::DB->client->with_transaction(\&_post_transaction, $self, $myconfig, $form);
71 $::lxdebug->leave_sub;
75 sub _post_transaction {
76 my ($self, $myconfig, $form) = @_;
77 $main::lxdebug->enter_sub();
79 my ($debit, $credit) = (0, 0);
84 my $dbh = SL::DB->client->dbh;
86 # post the transaction
87 # make up a unique handle and store in reference field
88 # then retrieve the record based on the unique handle to get the id
89 # replace the reference field with the actual variable
90 # add records to acc_trans
92 # if there is a $form->{id} replace the old transaction
93 # delete all acc_trans entries and add the new ones
95 if (!$form->{taxincluded}) {
96 $form->{taxincluded} = 0;
99 my ($query, $sth, @values, $taxkey, $rate, $posted);
103 # delete individual transactions
104 $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
105 @values = (conv_i($form->{id}));
106 do_query($form, $dbh, $query, @values);
109 $query = qq|SELECT nextval('glid')|;
110 ($form->{id}) = selectrow_query($form, $dbh, $query);
113 qq|INSERT INTO gl (id, employee_id) | .
114 qq|VALUES (?, (SELECT id FROM employee WHERE login = ?))|;
115 @values = ($form->{id}, $::myconfig{login});
116 do_query($form, $dbh, $query, @values);
119 $form->{ob_transaction} *= 1;
120 $form->{cb_transaction} *= 1;
124 reference = ?, description = ?, notes = ?,
125 transdate = ?, department_id = ?, taxincluded = ?,
126 storno = ?, storno_id = ?, ob_transaction = ?, cb_transaction = ?
129 @values = ($form->{reference}, $form->{description}, $form->{notes},
130 conv_date($form->{transdate}), conv_i($form->{department_id}), $form->{taxincluded} ? 't' : 'f',
131 $form->{storno} ? 't' : 'f', conv_i($form->{storno_id}), $form->{ob_transaction} ? 't' : 'f', $form->{cb_transaction} ? 't' : 'f',
132 conv_i($form->{id}));
133 do_query($form, $dbh, $query, @values);
135 # insert acc_trans transactions
136 for $i (1 .. $form->{rowcount}) {
137 ($form->{"tax_id_$i"}) = split(/--/, $form->{"taxchart_$i"});
138 if ($form->{"tax_id_$i"} ne "") {
139 $query = qq|SELECT taxkey, rate FROM tax WHERE id = ?|;
140 ($taxkey, $rate) = selectrow_query($form, $dbh, $query, conv_i($form->{"tax_id_$i"}));
144 my $debit = $form->{"debit_$i"};
145 my $credit = $form->{"credit_$i"};
146 my $tax = $form->{"tax_$i"};
153 $amount = $debit * -1;
158 $project_id = conv_i($form->{"project_id_$i"});
160 # if there is an amount, add the record
163 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
164 source, memo, project_id, taxkey, ob_transaction, cb_transaction, tax_id, chart_link)
165 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, (SELECT link FROM chart WHERE id = ?))|;
166 @values = (conv_i($form->{id}), $form->{"accno_id_$i"}, $amount, conv_date($form->{transdate}),
167 $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"});
168 do_query($form, $dbh, $query, @values);
174 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
175 source, memo, project_id, taxkey, tax_id, chart_link)
176 VALUES (?, (SELECT chart_id FROM tax WHERE id = ?),
177 ?, ?, ?, ?, ?, ?, ?, (SELECT link
179 WHERE id = (SELECT chart_id
182 @values = (conv_i($form->{id}), conv_i($form->{"tax_id_$i"}),
183 $tax, conv_date($form->{transdate}), $form->{"source_$i"},
184 $form->{"memo_$i"}, $project_id, $taxkey, conv_i($form->{"tax_id_$i"}), conv_i($form->{"tax_id_$i"}));
185 do_query($form, $dbh, $query, @values);
189 if ($form->{storno} && $form->{storno_id}) {
190 do_query($form, $dbh, qq|UPDATE gl SET storno = 't' WHERE id = ?|, conv_i($form->{storno_id}));
193 # safety check datev export
194 if ($::instance_conf->get_datev_check_on_gl_transaction) {
195 my $transdate = $::form->{transdate} ? DateTime->from_lxoffice($::form->{transdate}) : undef;
196 $transdate ||= DateTime->today;
198 my $datev = SL::DATEV->new(
199 exporttype => DATEV_ET_BUCHUNGEN,
200 format => DATEV_FORMAT_KNE,
202 trans_id => $form->{id},
207 if ($datev->errors) {
208 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
215 sub all_transactions {
216 my ($self, $myconfig, $form) = @_;
217 $main::lxdebug->enter_sub();
219 my $dbh = SL::DB->client->dbh;
220 my ($query, $sth, $source, $null, $space);
222 my ($glwhere, $arwhere, $apwhere) = ("1 = 1", "1 = 1", "1 = 1");
223 my (@glvalues, @arvalues, @apvalues);
225 if ($form->{reference}) {
226 $glwhere .= qq| AND g.reference ILIKE ?|;
227 $arwhere .= qq| AND a.invnumber ILIKE ?|;
228 $apwhere .= qq| AND a.invnumber ILIKE ?|;
229 push(@glvalues, like($form->{reference}));
230 push(@arvalues, like($form->{reference}));
231 push(@apvalues, like($form->{reference}));
234 if ($form->{department_id}) {
235 $glwhere .= qq| AND g.department_id = ?|;
236 $arwhere .= qq| AND a.department_id = ?|;
237 $apwhere .= qq| AND a.department_id = ?|;
238 push(@glvalues, $form->{department_id});
239 push(@arvalues, $form->{department_id});
240 push(@apvalues, $form->{department_id});
243 if ($form->{source}) {
244 $glwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)";
245 $arwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)";
246 $apwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)";
247 push(@glvalues, like($form->{source}));
248 push(@arvalues, like($form->{source}));
249 push(@apvalues, like($form->{source}));
252 # default Datumseinschränkung falls nicht oder falsch übergeben (sollte nie passieren)
253 $form->{datesort} = 'transdate' unless $form->{datesort} =~ /^(transdate|gldate)$/;
255 if (trim($form->{datefrom})) {
256 $glwhere .= " AND ac.$form->{datesort} >= ?";
257 $arwhere .= " AND ac.$form->{datesort} >= ?";
258 $apwhere .= " AND ac.$form->{datesort} >= ?";
259 push(@glvalues, trim($form->{datefrom}));
260 push(@arvalues, trim($form->{datefrom}));
261 push(@apvalues, trim($form->{datefrom}));
264 if (trim($form->{dateto})) {
265 $glwhere .= " AND ac.$form->{datesort} <= ?";
266 $arwhere .= " AND ac.$form->{datesort} <= ?";
267 $apwhere .= " AND ac.$form->{datesort} <= ?";
268 push(@glvalues, trim($form->{dateto}));
269 push(@arvalues, trim($form->{dateto}));
270 push(@apvalues, trim($form->{dateto}));
273 if (trim($form->{description})) {
274 $glwhere .= " AND g.description ILIKE ?";
275 $arwhere .= " AND ct.name ILIKE ?";
276 $apwhere .= " AND ct.name ILIKE ?";
277 push(@glvalues, like($form->{description}));
278 push(@arvalues, like($form->{description}));
279 push(@apvalues, like($form->{description}));
282 if ($form->{employee_id}) {
283 $glwhere .= " AND g.employee_id = ? ";
284 $arwhere .= " AND a.employee_id = ? ";
285 $apwhere .= " AND a.employee_id = ? ";
286 push(@glvalues, conv_i($form->{employee_id}));
287 push(@arvalues, conv_i($form->{employee_id}));
288 push(@apvalues, conv_i($form->{employee_id}));
291 if (trim($form->{notes})) {
292 $glwhere .= " AND g.notes ILIKE ?";
293 $arwhere .= " AND a.notes ILIKE ?";
294 $apwhere .= " AND a.notes ILIKE ?";
295 push(@glvalues, like($form->{notes}));
296 push(@arvalues, like($form->{notes}));
297 push(@apvalues, like($form->{notes}));
300 if ($form->{accno}) {
301 $glwhere .= " AND c.accno = '$form->{accno}'";
302 $arwhere .= " AND c.accno = '$form->{accno}'";
303 $apwhere .= " AND c.accno = '$form->{accno}'";
306 if ($form->{category} ne 'X') {
307 $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 = ?))|;
308 $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 = ?))|;
309 $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 = ?))|;
310 push(@glvalues, $form->{category});
311 push(@arvalues, $form->{category});
312 push(@apvalues, $form->{category});
315 if ($form->{project_id}) {
316 $glwhere .= qq| AND g.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)|;
318 qq| AND ((a.globalproject_id = ?) OR
319 (a.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)))|;
321 qq| AND ((a.globalproject_id = ?) OR
322 (a.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)))|;
323 my $project_id = conv_i($form->{project_id});
324 push(@glvalues, $project_id);
325 push(@arvalues, $project_id, $project_id);
326 push(@apvalues, $project_id, $project_id);
329 my ($project_columns, $project_join);
330 if ($form->{"l_projectnumbers"}) {
331 $project_columns = qq|, ac.project_id, pr.projectnumber|;
332 $project_join = qq|LEFT JOIN project pr ON (ac.project_id = pr.id)|;
335 if ($form->{accno}) {
336 # get category for account
337 $query = qq|SELECT category FROM chart WHERE accno = ?|;
338 ($form->{ml}) = selectrow_query($form, $dbh, $query, $form->{accno});
340 if ($form->{datefrom}) {
342 qq|SELECT SUM(ac.amount)
344 LEFT JOIN chart c ON (ac.chart_id = c.id)
345 WHERE (c.accno = ?) AND (ac.$form->{datesort} < ?)|;
346 ($form->{balance}) = selectrow_query($form, $dbh, $query, $form->{accno}, conv_date($form->{datefrom}));
352 'transdate' => [ qw(transdate id) ],
353 'gldate' => [ qw(gldate id) ],
354 'reference' => [ qw(lower_reference id) ],
355 'description' => [ qw(lower_description id) ],
356 'accno' => [ qw(accno transdate id) ],
358 my %lowered_columns = (
359 'reference' => { 'gl' => 'g.reference', 'arap' => 'a.invnumber', },
360 'source' => { 'gl' => 'ac.source', 'arap' => 'ac.source', },
361 'description' => { 'gl' => 'g.description', 'arap' => 'ct.name', },
364 # sortdir = sort direction (ascending or descending)
365 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
366 my $sortkey = $sort_columns{$form->{sort}} ? $form->{sort} : $form->{datesort}; # default used to be transdate
367 my $sortorder = join ', ', map { "$_ $sortdir" } @{ $sort_columns{$sortkey} };
369 my %columns_for_sorting = ( 'gl' => '', 'arap' => '', );
370 foreach my $spec (@{ $sort_columns{$sortkey} }) {
371 next if ($spec !~ m/^lower_(.*)$/);
374 map { $columns_for_sorting{$_} .= sprintf(', lower(%s) AS lower_%s', $lowered_columns{$column}->{$_}, $column) } qw(gl arap);
379 ac.acc_trans_id, g.id, 'gl' AS type, FALSE AS invoice, g.reference, ac.taxkey, c.link,
380 g.description, ac.transdate, ac.gldate, ac.source, ac.trans_id,
381 ac.amount, c.accno, g.notes, t.chart_id,
382 CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
384 $columns_for_sorting{gl}
386 LEFT JOIN employee e ON (g.employee_id = e.id),
387 acc_trans ac $project_join, chart c
388 LEFT JOIN tax t ON (t.chart_id = c.id)
390 AND (ac.chart_id = c.id)
391 AND (g.id = ac.trans_id)
395 SELECT ac.acc_trans_id, a.id, 'ar' AS type, a.invoice, a.invnumber, ac.taxkey, c.link,
396 ct.name, ac.transdate, ac.gldate, ac.source, ac.trans_id,
397 ac.amount, c.accno, a.notes, t.chart_id,
398 CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
400 $columns_for_sorting{arap}
402 LEFT JOIN employee e ON (a.employee_id = e.id),
403 acc_trans ac $project_join, customer ct, chart c
404 LEFT JOIN tax t ON (t.chart_id=c.id)
406 AND (ac.chart_id = c.id)
407 AND (a.customer_id = ct.id)
408 AND (a.id = ac.trans_id)
412 SELECT ac.acc_trans_id, a.id, 'ap' AS type, a.invoice, a.invnumber, ac.taxkey, c.link,
413 ct.name, ac.transdate, ac.gldate, ac.source, ac.trans_id,
414 ac.amount, c.accno, a.notes, t.chart_id,
415 CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
417 $columns_for_sorting{arap}
419 LEFT JOIN employee e ON (a.employee_id = e.id),
420 acc_trans ac $project_join, vendor ct, chart c
421 LEFT JOIN tax t ON (t.chart_id=c.id)
423 AND (ac.chart_id = c.id)
424 AND (a.vendor_id = ct.id)
425 AND (a.id = ac.trans_id)
427 ORDER BY $sortorder, acc_trans_id $sortdir|;
428 # ORDER BY gldate DESC, id DESC, acc_trans_id DESC
430 my @values = (@glvalues, @arvalues, @apvalues);
432 # Show all $query in Debuglevel LXDebug::QUERY
433 my $callingdetails = (caller (0))[3];
434 dump_query(LXDebug->QUERY(), "$callingdetails", $query, @values);
436 $sth = prepare_execute_query($form, $dbh, $query, @values);
441 my ($i, $j, $k, $l, $ref, $ref2);
444 while (my $ref0 = $sth->fetchrow_hashref("NAME_lc")) {
446 $trans_id = $ref0->{id};
448 my $source = $ref0->{source};
449 undef($ref0->{source});
451 if ($trans_id != $trans_id2) { # first line of a booking
454 push(@{ $form->{GL} }, $ref);
459 $trans_id2 = $ref->{id};
462 if ($ref->{type} eq "gl") {
463 $ref->{module} = "gl";
467 if ($ref->{type} eq "ap") {
468 if ($ref->{invoice}) {
469 $ref->{module} = "ir";
471 $ref->{module} = "ap";
476 if ($ref->{type} eq "ar") {
477 if ($ref->{invoice}) {
478 $ref->{module} = "is";
480 $ref->{module} = "ar";
484 $ref->{"projectnumbers"} = {};
485 $ref->{"projectnumbers"}->{$ref->{"projectnumber"}} = 1 if ($ref->{"projectnumber"});
487 $balance = $ref->{amount};
489 # Linenumbers of General Ledger
490 $k = 0; # Debit # AP # Soll
491 $l = 0; # Credit # AR # Haben
492 $i = 0; # Debit Tax # AP_tax # VSt
493 $j = 0; # Credit Tax # AR_tax # USt
495 if ($ref->{chart_id} > 0) { # all tax accounts first line, no line increasing
496 if ($ref->{amount} < 0) {
497 if ($ref->{link} =~ /AR_tax/) {
498 $ref->{credit_tax}{$j} = $ref->{amount};
499 $ref->{credit_tax_accno}{$j} = $ref->{accno};
501 if ($ref->{link} =~ /AP_tax/) {
502 $ref->{debit_tax}{$i} = $ref->{amount} * -1;
503 $ref->{debit_tax_accno}{$i} = $ref->{accno};
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 } else { #all other accounts first line
517 if ($ref->{amount} < 0) {
518 $ref->{debit}{$k} = $ref->{amount} * -1;
519 $ref->{debit_accno}{$k} = $ref->{accno};
520 $ref->{debit_taxkey}{$k} = $ref->{taxkey};
521 $ref->{ac_transdate}{$k} = $ref->{transdate};
522 $ref->{source}{$k} = $source;
524 $ref->{credit}{$l} = $ref->{amount} * 1;
525 $ref->{credit_accno}{$l} = $ref->{accno};
526 $ref->{credit_taxkey}{$l} = $ref->{taxkey};
527 $ref->{ac_transdate}{$l} = $ref->{transdate};
528 $ref->{source}{$l} = $source;
532 } else { # following lines of a booking, line increasing
535 # $trans_old = $trans_id2; # doesn't seem to be used anymore
536 $trans_id2 = $ref2->{id};
539 (int($balance * 100000) + int(100000 * $ref2->{amount})) / 100000;
541 $ref->{"projectnumbers"}->{$ref2->{"projectnumber"}} = 1 if ($ref2->{"projectnumber"});
543 if ($ref2->{chart_id} > 0) { # all tax accounts, following lines
544 if ($ref2->{amount} < 0) {
545 if ($ref2->{link} =~ /AR_tax/) {
546 if ($ref->{credit_tax_accno}{$j} ne "") {
549 $ref->{credit_tax}{$j} = $ref2->{amount};
550 $ref->{credit_tax_accno}{$j} = $ref2->{accno};
552 if ($ref2->{link} =~ /AP_tax/) {
553 if ($ref->{debit_tax_accno}{$i} ne "") {
556 $ref->{debit_tax}{$i} = $ref2->{amount} * -1;
557 $ref->{debit_tax_accno}{$i} = $ref2->{accno};
560 if ($ref2->{link} =~ /AR_tax/) {
561 if ($ref->{credit_tax_accno}{$j} ne "") {
564 $ref->{credit_tax}{$j} = $ref2->{amount};
565 $ref->{credit_tax_accno}{$j} = $ref2->{accno};
567 if ($ref2->{link} =~ /AP_tax/) {
568 if ($ref->{debit_tax_accno}{$i} ne "") {
571 $ref->{debit_tax}{$i} = $ref2->{amount} * -1;
572 $ref->{debit_tax_accno}{$i} = $ref2->{accno};
575 } else { # all other accounts, following lines
576 if ($ref2->{amount} < 0) {
577 if ($ref->{debit_accno}{$k} ne "") {
580 if ($ref->{source}{$k} ne "") {
585 $ref->{debit}{$k} = $ref2->{amount} * - 1;
586 $ref->{debit_accno}{$k} = $ref2->{accno};
587 $ref->{debit_taxkey}{$k} = $ref2->{taxkey};
588 $ref->{ac_transdate}{$k} = $ref2->{transdate};
589 $ref->{source}{$k} = $source . $space . $ref->{source}{$k};
591 if ($ref->{credit_accno}{$l} ne "") {
594 if ($ref->{source}{$l} ne "") {
599 $ref->{credit}{$l} = $ref2->{amount};
600 $ref->{credit_accno}{$l} = $ref2->{accno};
601 $ref->{credit_taxkey}{$l} = $ref2->{taxkey};
602 $ref->{ac_transdate}{$l} = $ref2->{transdate};
603 $ref->{source}{$l} = $ref->{source}{$l} . $space . $source;
609 push @{ $form->{GL} }, $ref;
612 if ($form->{accno}) {
613 $query = qq|SELECT c.description FROM chart c WHERE c.accno = ?|;
614 ($form->{account_description}) = selectrow_query($form, $dbh, $query, $form->{accno});
617 $main::lxdebug->leave_sub();
621 my ($self, $myconfig, $form) = @_;
622 $main::lxdebug->enter_sub();
624 my ($query, $sth, $ref, @values);
626 my $dbh = SL::DB->client->dbh;
628 $query = qq|SELECT closedto, revtrans FROM defaults|;
629 ($form->{closedto}, $form->{revtrans}) = selectrow_query($form, $dbh, $query);
631 $query = qq|SELECT id, gldate
633 WHERE id = (SELECT max(id) FROM gl)|;
634 ($form->{previous_id}, $form->{previous_gldate}) = selectrow_query($form, $dbh, $query);
638 qq|SELECT g.reference, g.description, g.notes, g.transdate, g.storno, g.storno_id,
639 g.department_id, d.description AS department,
640 e.name AS employee, g.taxincluded, g.gldate,
641 g.ob_transaction, g.cb_transaction
643 LEFT JOIN department d ON (d.id = g.department_id)
644 LEFT JOIN employee e ON (e.id = g.employee_id)
646 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
647 map { $form->{$_} = $ref->{$_} } keys %$ref;
649 # retrieve individual rows
651 qq|SELECT c.accno, t.taxkey AS accnotaxkey, a.amount, a.memo, a.source,
652 a.transdate, a.cleared, a.project_id, p.projectnumber,
653 a.taxkey, t.rate AS taxrate, t.id, a.chart_id,
655 FROM chart c1, tax t1
656 WHERE (t1.id = t.id) AND (c1.id = t.chart_id)) AS taxaccno,
659 WHERE (tk.chart_id = a.chart_id) AND (tk.startdate <= a.transdate)
660 ORDER BY tk.startdate desc LIMIT 1) AS tax_id
662 JOIN chart c ON (c.id = a.chart_id)
663 LEFT JOIN project p ON (p.id = a.project_id)
664 LEFT JOIN tax t ON (t.id = a.tax_id)
665 WHERE (a.trans_id = ?)
666 AND (a.fx_transaction = '0')
667 ORDER BY a.acc_trans_id, a.transdate|;
668 $form->{GL} = selectall_hashref_query($form, $dbh, $query, conv_i($form->{id}));
675 WHERE id = (SELECT MAX(id) FROM gl)
678 ($form->{transdate}) = selectrow_query($form, $dbh, $query);
681 # get tax description
682 $query = qq|SELECT * FROM tax ORDER BY taxkey|;
683 $form->{TAX} = selectall_hashref_query($form, $dbh, $query);
685 # get chart of accounts
687 qq|SELECT c.accno, c.description, c.link, tk.taxkey_id, tk.tax_id
689 LEFT JOIN taxkeys tk ON (tk.id =
692 WHERE (taxkeys.chart_id = c.id)
694 ORDER BY startdate DESC
697 $form->{chart} = selectall_hashref_query($form, $dbh, $query, conv_date($form->{transdate}));
699 $main::lxdebug->leave_sub();
703 my ($self, $form, $myconfig, $id) = @_;
704 $main::lxdebug->enter_sub();
706 my $rc = SL::DB->client->with_transaction(\&_storno, $self, $form, $myconfig, $id);
708 $::lxdebug->leave_sub;
713 my ($self, $form, $myconfig, $id) = @_;
715 my ($query, $new_id, $storno_row, $acc_trans_rows);
716 my $dbh = SL::DB->client->dbh;
718 $query = qq|SELECT nextval('glid')|;
719 ($new_id) = selectrow_query($form, $dbh, $query);
721 $query = qq|SELECT * FROM gl WHERE id = ?|;
722 $storno_row = selectfirst_hashref_query($form, $dbh, $query, $id);
724 $storno_row->{id} = $new_id;
725 $storno_row->{storno_id} = $id;
726 $storno_row->{storno} = 't';
727 $storno_row->{reference} = 'Storno-' . $storno_row->{reference};
729 $query = qq|SELECT id FROM employee WHERE login = ?|;
730 my ($employee_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
731 $storno_row->{employee_id} = $employee_id;
733 delete @$storno_row{qw(itime mtime gldate)};
735 $query = sprintf 'INSERT INTO gl (%s) VALUES (%s)', join(', ', keys %$storno_row), join(', ', map '?', values %$storno_row);
736 do_query($form, $dbh, $query, (values %$storno_row));
738 $query = qq|UPDATE gl SET storno = 't' WHERE id = ?|;
739 do_query($form, $dbh, $query, $id);
741 # now copy acc_trans entries
742 $query = qq|SELECT * FROM acc_trans WHERE trans_id = ?|;
743 my $rowref = selectall_hashref_query($form, $dbh, $query, $id);
745 for my $row (@$rowref) {
746 delete @$row{qw(itime mtime acc_trans_id gldate)};
747 $query = sprintf 'INSERT INTO acc_trans (%s) VALUES (%s)', join(', ', keys %$row), join(', ', map '?', values %$row);
748 $row->{trans_id} = $new_id;
749 $row->{amount} *= -1;
750 do_query($form, $dbh, $query, (values %$row));
756 sub get_chart_balances {
757 my ($self, @chart_ids) = @_;
759 return () unless @chart_ids;
761 my $placeholders = join ', ', ('?') x scalar(@chart_ids);
762 my $query = qq|SELECT chart_id, SUM(amount) AS sum
764 WHERE chart_id IN (${placeholders})
767 my %balances = selectall_as_map($::form, $::form->get_standard_dbh(\%::myconfig), $query, 'chart_id', 'sum', @chart_ids);
772 sub get_active_taxes_for_chart {
773 my ($self, $chart_id, $transdate) = @_;
775 my $chart = SL::DB::Chart->new(id => $chart_id)->load;
776 my $active_taxkey = $chart->get_active_taxkey($transdate);
777 my $taxes = SL::DB::Manager::Tax->get_all(
778 where => [ chart_categories => { like => '%' . $chart->category . '%' }],
779 sort_by => 'taxkey, rate',
782 my $default_tax = first { $active_taxkey->tax_id == $_->id } @{ $taxes };
783 $default_tax->{is_default} = 1 if $default_tax;