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 = ?,
128 transaction_description = ?
131 @values = ($form->{reference}, $form->{description}, $form->{notes},
132 conv_date($form->{transdate}), conv_date($form->{deliverydate}), conv_date($form->{tax_point}), conv_i($form->{department_id}), $form->{taxincluded} ? 't' : 'f',
133 $form->{storno} ? 't' : 'f', conv_i($form->{storno_id}), $form->{ob_transaction} ? 't' : 'f', $form->{cb_transaction} ? 't' : 'f',
134 $form->{transaction_description},
135 conv_i($form->{id}));
136 do_query($form, $dbh, $query, @values);
138 # insert acc_trans transactions
139 for $i (1 .. $form->{rowcount}) {
140 ($form->{"tax_id_$i"}) = split(/--/, $form->{"taxchart_$i"});
141 if ($form->{"tax_id_$i"} ne "") {
142 $query = qq|SELECT taxkey, rate FROM tax WHERE id = ?|;
143 ($taxkey, $rate) = selectrow_query($form, $dbh, $query, conv_i($form->{"tax_id_$i"}));
147 my $debit = $form->{"debit_$i"};
148 my $credit = $form->{"credit_$i"};
149 my $tax = $form->{"tax_$i"};
156 $amount = $debit * -1;
161 $project_id = conv_i($form->{"project_id_$i"});
163 # if there is an amount, add the record
166 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
167 source, memo, project_id, taxkey, ob_transaction, cb_transaction, tax_id, chart_link)
168 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, (SELECT link FROM chart WHERE id = ?))|;
169 @values = (conv_i($form->{id}), $form->{"accno_id_$i"}, $amount, conv_date($form->{transdate}),
170 $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"});
171 do_query($form, $dbh, $query, @values);
177 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
178 source, memo, project_id, taxkey, tax_id, chart_link)
179 VALUES (?, (SELECT chart_id FROM tax WHERE id = ?),
180 ?, ?, ?, ?, ?, ?, ?, (SELECT link
182 WHERE id = (SELECT chart_id
185 @values = (conv_i($form->{id}), conv_i($form->{"tax_id_$i"}),
186 $tax, conv_date($form->{transdate}), $form->{"source_$i"},
187 $form->{"memo_$i"}, $project_id, $taxkey, conv_i($form->{"tax_id_$i"}), conv_i($form->{"tax_id_$i"}));
188 do_query($form, $dbh, $query, @values);
192 if ($form->{storno} && $form->{storno_id}) {
193 do_query($form, $dbh, qq|UPDATE gl SET storno = 't' WHERE id = ?|, conv_i($form->{storno_id}));
196 if ($form->{draft_id}) {
197 SL::DB::Manager::Draft->delete_all(where => [ id => delete($form->{draft_id}) ]);
200 # safety check datev export
201 if ($::instance_conf->get_datev_check_on_gl_transaction) {
203 # create datev object
204 my $datev = SL::DATEV->new(
206 trans_id => $form->{id},
209 $datev->generate_datev_data;
211 if ($datev->errors) {
212 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
219 sub all_transactions {
220 my ($self, $myconfig, $form) = @_;
221 $main::lxdebug->enter_sub();
223 my $dbh = SL::DB->client->dbh;
224 my ($query, $sth, $source, $null, $space);
226 my ($glwhere, $arwhere, $apwhere) = ("1 = 1", "1 = 1", "1 = 1");
227 my (@glvalues, @arvalues, @apvalues);
229 if ($form->{reference}) {
230 $glwhere .= qq| AND g.reference ILIKE ?|;
231 $arwhere .= qq| AND a.invnumber ILIKE ?|;
232 $apwhere .= qq| AND a.invnumber ILIKE ?|;
233 push(@glvalues, like($form->{reference}));
234 push(@arvalues, like($form->{reference}));
235 push(@apvalues, like($form->{reference}));
238 if ($form->{department_id}) {
239 $glwhere .= qq| AND g.department_id = ?|;
240 $arwhere .= qq| AND a.department_id = ?|;
241 $apwhere .= qq| AND a.department_id = ?|;
242 push(@glvalues, $form->{department_id});
243 push(@arvalues, $form->{department_id});
244 push(@apvalues, $form->{department_id});
247 if ($form->{source}) {
248 $glwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)";
249 $arwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)";
250 $apwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)";
251 push(@glvalues, like($form->{source}));
252 push(@arvalues, like($form->{source}));
253 push(@apvalues, like($form->{source}));
256 # default Datumseinschränkung falls nicht oder falsch übergeben (sollte nie passieren)
257 $form->{datesort} = 'transdate' unless $form->{datesort} =~ /^(transdate|gldate)$/;
259 if (trim($form->{datefrom})) {
260 $glwhere .= " AND ac.$form->{datesort} >= ?";
261 $arwhere .= " AND ac.$form->{datesort} >= ?";
262 $apwhere .= " AND ac.$form->{datesort} >= ?";
263 push(@glvalues, trim($form->{datefrom}));
264 push(@arvalues, trim($form->{datefrom}));
265 push(@apvalues, trim($form->{datefrom}));
268 if (trim($form->{dateto})) {
269 $glwhere .= " AND ac.$form->{datesort} <= ?";
270 $arwhere .= " AND ac.$form->{datesort} <= ?";
271 $apwhere .= " AND ac.$form->{datesort} <= ?";
272 push(@glvalues, trim($form->{dateto}));
273 push(@arvalues, trim($form->{dateto}));
274 push(@apvalues, trim($form->{dateto}));
277 if (trim($form->{description})) {
278 $glwhere .= " AND g.description ILIKE ?";
279 $arwhere .= " AND ct.name ILIKE ?";
280 $apwhere .= " AND ct.name ILIKE ?";
281 push(@glvalues, like($form->{description}));
282 push(@arvalues, like($form->{description}));
283 push(@apvalues, like($form->{description}));
286 if ($form->{employee_id}) {
287 $glwhere .= " AND g.employee_id = ? ";
288 $arwhere .= " AND a.employee_id = ? ";
289 $apwhere .= " AND a.employee_id = ? ";
290 push(@glvalues, conv_i($form->{employee_id}));
291 push(@arvalues, conv_i($form->{employee_id}));
292 push(@apvalues, conv_i($form->{employee_id}));
295 if (trim($form->{notes})) {
296 $glwhere .= " AND g.notes ILIKE ?";
297 $arwhere .= " AND a.notes ILIKE ?";
298 $apwhere .= " AND a.notes ILIKE ?";
299 push(@glvalues, like($form->{notes}));
300 push(@arvalues, like($form->{notes}));
301 push(@apvalues, like($form->{notes}));
304 if ($form->{accno}) {
305 $glwhere .= " AND c.accno = '$form->{accno}'";
306 $arwhere .= " AND c.accno = '$form->{accno}'";
307 $apwhere .= " AND c.accno = '$form->{accno}'";
310 if ($form->{category} ne 'X') {
311 $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 = ?))|;
312 $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 = ?))|;
313 $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 = ?))|;
314 push(@glvalues, $form->{category});
315 push(@arvalues, $form->{category});
316 push(@apvalues, $form->{category});
319 if ($form->{project_id}) {
320 $glwhere .= qq| AND g.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)|;
322 qq| AND ((a.globalproject_id = ?) OR
323 (a.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)))|;
325 qq| AND ((a.globalproject_id = ?) OR
326 (a.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)))|;
327 my $project_id = conv_i($form->{project_id});
328 push(@glvalues, $project_id);
329 push(@arvalues, $project_id, $project_id);
330 push(@apvalues, $project_id, $project_id);
333 my ($project_columns, $project_join);
334 my ($arap_globalproject_columns, $arap_globalproject_join);
335 my ($gl_globalproject_columns);
336 if ($form->{"l_projectnumbers"}) {
337 $project_columns = qq|, ac.project_id, pr.projectnumber|;
338 $project_join = qq|LEFT JOIN project pr ON (ac.project_id = pr.id)|;
339 $arap_globalproject_columns = qq|, a.globalproject_id, globalpr.projectnumber AS globalprojectnumber|;
340 $arap_globalproject_join = qq|LEFT JOIN project globalpr ON (a.globalproject_id = globalpr.id)|;
341 $gl_globalproject_columns = qq|, NULL AS globalproject_id, '' AS globalprojectnumber|;
344 if ($form->{accno}) {
345 # get category for account
346 $query = qq|SELECT category FROM chart WHERE accno = ?|;
347 ($form->{ml}) = selectrow_query($form, $dbh, $query, $form->{accno});
349 if ($form->{datefrom}) {
351 qq|SELECT SUM(ac.amount)
353 LEFT JOIN chart c ON (ac.chart_id = c.id)
354 WHERE (c.accno = ?) AND (ac.$form->{datesort} < ?)|;
355 ($form->{balance}) = selectrow_query($form, $dbh, $query, $form->{accno}, conv_date($form->{datefrom}));
361 'transdate' => [ qw(transdate id) ],
362 'gldate' => [ qw(gldate id) ],
363 'reference' => [ qw(lower_reference id) ],
364 'description' => [ qw(lower_description id) ],
365 'accno' => [ qw(accno transdate id) ],
366 'department' => [ qw(department transdate id) ],
368 my %lowered_columns = (
369 'reference' => { 'gl' => 'g.reference', 'arap' => 'a.invnumber', },
370 'source' => { 'gl' => 'ac.source', 'arap' => 'ac.source', },
371 'description' => { 'gl' => 'g.description', 'arap' => 'ct.name', },
374 # sortdir = sort direction (ascending or descending)
375 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
376 my $sortkey = $sort_columns{$form->{sort}} ? $form->{sort} : $form->{datesort}; # default used to be transdate
377 my $sortorder = join ', ', map { "$_ $sortdir" } @{ $sort_columns{$sortkey} };
379 my %columns_for_sorting = ( 'gl' => '', 'arap' => '', );
380 foreach my $spec (@{ $sort_columns{$sortkey} }) {
381 next if ($spec !~ m/^lower_(.*)$/);
384 map { $columns_for_sorting{$_} .= sprintf(', lower(%s) AS lower_%s', $lowered_columns{$column}->{$_}, $column) } qw(gl arap);
389 ac.acc_trans_id, g.id, 'gl' AS type, FALSE AS invoice, g.reference, ac.taxkey, c.link,
390 g.description, ac.transdate, ac.gldate, ac.source, ac.trans_id,
391 ac.amount, c.accno, g.notes, t.chart_id,
392 d.description AS department,
393 CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
394 $project_columns $gl_globalproject_columns
395 $columns_for_sorting{gl}
397 LEFT JOIN employee e ON (g.employee_id = e.id)
398 LEFT JOIN department d ON (g.department_id = d.id),
399 acc_trans ac $project_join, chart c
400 LEFT JOIN tax t ON (t.chart_id = c.id)
402 AND (ac.chart_id = c.id)
403 AND (g.id = ac.trans_id)
407 SELECT ac.acc_trans_id, a.id, 'ar' AS type, a.invoice, a.invnumber, ac.taxkey, c.link,
408 ct.name, ac.transdate, ac.gldate, ac.source, ac.trans_id,
409 ac.amount, c.accno, a.notes, t.chart_id,
410 d.description AS department,
411 CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
412 $project_columns $arap_globalproject_columns
413 $columns_for_sorting{arap}
415 LEFT JOIN employee e ON (a.employee_id = e.id)
416 LEFT JOIN department d ON (a.department_id = d.id)
417 $arap_globalproject_join,
418 acc_trans ac $project_join, customer ct, chart c
419 LEFT JOIN tax t ON (t.chart_id=c.id)
421 AND (ac.chart_id = c.id)
422 AND (a.customer_id = ct.id)
423 AND (a.id = ac.trans_id)
427 SELECT ac.acc_trans_id, a.id, 'ap' AS type, a.invoice, a.invnumber, ac.taxkey, c.link,
428 ct.name, ac.transdate, ac.gldate, ac.source, ac.trans_id,
429 ac.amount, c.accno, a.notes, t.chart_id,
430 d.description AS department,
431 CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
432 $project_columns $arap_globalproject_columns
433 $columns_for_sorting{arap}
435 LEFT JOIN employee e ON (a.employee_id = e.id)
436 LEFT JOIN department d ON (a.department_id = d.id)
437 $arap_globalproject_join,
438 acc_trans ac $project_join, vendor ct, chart c
439 LEFT JOIN tax t ON (t.chart_id=c.id)
441 AND (ac.chart_id = c.id)
442 AND (a.vendor_id = ct.id)
443 AND (a.id = ac.trans_id)
445 ORDER BY $sortorder, acc_trans_id $sortdir|;
446 # ORDER BY gldate DESC, id DESC, acc_trans_id DESC
448 my @values = (@glvalues, @arvalues, @apvalues);
450 # Show all $query in Debuglevel LXDebug::QUERY
451 my $callingdetails = (caller (0))[3];
452 dump_query(LXDebug->QUERY(), "$callingdetails", $query, @values);
454 $sth = prepare_execute_query($form, $dbh, $query, @values);
459 my ($i, $j, $k, $l, $ref, $ref2);
462 while (my $ref0 = $sth->fetchrow_hashref("NAME_lc")) {
464 $trans_id = $ref0->{id};
466 my $source = $ref0->{source};
467 undef($ref0->{source});
469 if ($trans_id != $trans_id2) { # first line of a booking
472 push(@{ $form->{GL} }, $ref);
477 $trans_id2 = $ref->{id};
480 if ($ref->{type} eq "gl") {
481 $ref->{module} = "gl";
485 if ($ref->{type} eq "ap") {
486 if ($ref->{invoice}) {
487 $ref->{module} = "ir";
489 $ref->{module} = "ap";
494 if ($ref->{type} eq "ar") {
495 if ($ref->{invoice}) {
496 $ref->{module} = "is";
498 $ref->{module} = "ar";
502 $ref->{"projectnumbers"} = {};
503 $ref->{"projectnumbers"}->{$ref->{"projectnumber"}} = 1 if ($ref->{"projectnumber"});
504 $ref->{"projectnumbers"}->{$ref->{"globalprojectnumber"}} = 1 if ($ref->{"globalprojectnumber"});
506 $balance = $ref->{amount};
508 # Linenumbers of General Ledger
509 $k = 0; # Debit # AP # Soll
510 $l = 0; # Credit # AR # Haben
511 $i = 0; # Debit Tax # AP_tax # VSt
512 $j = 0; # Credit Tax # AR_tax # USt
514 if ($ref->{chart_id} > 0) { # all tax accounts first line, no line increasing
515 if ($ref->{amount} < 0) {
516 if ($ref->{link} =~ /AR_tax/) {
517 $ref->{credit_tax}{$j} = $ref->{amount};
518 $ref->{credit_tax_accno}{$j} = $ref->{accno};
520 if ($ref->{link} =~ /AP_tax/) {
521 $ref->{debit_tax}{$i} = $ref->{amount} * -1;
522 $ref->{debit_tax_accno}{$i} = $ref->{accno};
525 if ($ref->{link} =~ /AR_tax/) {
526 $ref->{credit_tax}{$j} = $ref->{amount};
527 $ref->{credit_tax_accno}{$j} = $ref->{accno};
529 if ($ref->{link} =~ /AP_tax/) {
530 $ref->{debit_tax}{$i} = $ref->{amount} * -1;
531 $ref->{debit_tax_accno}{$i} = $ref->{accno};
534 } else { #all other accounts first line
536 if ($ref->{amount} < 0) {
537 $ref->{debit}{$k} = $ref->{amount} * -1;
538 $ref->{debit_accno}{$k} = $ref->{accno};
539 $ref->{debit_taxkey}{$k} = $ref->{taxkey};
540 $ref->{ac_transdate}{$k} = $ref->{transdate};
541 $ref->{source}{$k} = $source;
543 $ref->{credit}{$l} = $ref->{amount} * 1;
544 $ref->{credit_accno}{$l} = $ref->{accno};
545 $ref->{credit_taxkey}{$l} = $ref->{taxkey};
546 $ref->{ac_transdate}{$l} = $ref->{transdate};
547 $ref->{source}{$l} = $source;
551 } else { # following lines of a booking, line increasing
554 # $trans_old = $trans_id2; # doesn't seem to be used anymore
555 $trans_id2 = $ref2->{id};
558 (int($balance * 100000) + int(100000 * $ref2->{amount})) / 100000;
560 $ref->{"projectnumbers"}->{$ref2->{"projectnumber"}} = 1 if ($ref2->{"projectnumber"});
561 $ref->{"projectnumbers"}->{$ref2->{"globalprojectnumber"}} = 1 if ($ref2->{"globalprojectnumber"});
563 if ($ref2->{chart_id} > 0) { # all tax accounts, following lines
564 if ($ref2->{amount} < 0) {
565 if ($ref2->{link} =~ /AR_tax/) {
566 if ($ref->{credit_tax_accno}{$j} ne "") {
569 $ref->{credit_tax}{$j} = $ref2->{amount};
570 $ref->{credit_tax_accno}{$j} = $ref2->{accno};
572 if ($ref2->{link} =~ /AP_tax/) {
573 if ($ref->{debit_tax_accno}{$i} ne "") {
576 $ref->{debit_tax}{$i} = $ref2->{amount} * -1;
577 $ref->{debit_tax_accno}{$i} = $ref2->{accno};
580 if ($ref2->{link} =~ /AR_tax/) {
581 if ($ref->{credit_tax_accno}{$j} ne "") {
584 $ref->{credit_tax}{$j} = $ref2->{amount};
585 $ref->{credit_tax_accno}{$j} = $ref2->{accno};
587 if ($ref2->{link} =~ /AP_tax/) {
588 if ($ref->{debit_tax_accno}{$i} ne "") {
591 $ref->{debit_tax}{$i} = $ref2->{amount} * -1;
592 $ref->{debit_tax_accno}{$i} = $ref2->{accno};
595 } else { # all other accounts, following lines
596 if ($ref2->{amount} < 0) {
597 if ($ref->{debit_accno}{$k} ne "") {
600 if ($ref->{source}{$k} ne "") {
605 $ref->{debit}{$k} = $ref2->{amount} * - 1;
606 $ref->{debit_accno}{$k} = $ref2->{accno};
607 $ref->{debit_taxkey}{$k} = $ref2->{taxkey};
608 $ref->{ac_transdate}{$k} = $ref2->{transdate};
609 $ref->{source}{$k} = $source . $space . $ref->{source}{$k};
611 if ($ref->{credit_accno}{$l} ne "") {
614 if ($ref->{source}{$l} ne "") {
619 $ref->{credit}{$l} = $ref2->{amount};
620 $ref->{credit_accno}{$l} = $ref2->{accno};
621 $ref->{credit_taxkey}{$l} = $ref2->{taxkey};
622 $ref->{ac_transdate}{$l} = $ref2->{transdate};
623 $ref->{source}{$l} = $ref->{source}{$l} . $space . $source;
629 push @{ $form->{GL} }, $ref;
632 if ($form->{accno}) {
633 $query = qq|SELECT c.description FROM chart c WHERE c.accno = ?|;
634 ($form->{account_description}) = selectrow_query($form, $dbh, $query, $form->{accno});
637 $main::lxdebug->leave_sub();
641 my ($self, $myconfig, $form) = @_;
642 $main::lxdebug->enter_sub();
644 my ($query, $sth, $ref, @values);
646 my $dbh = SL::DB->client->dbh;
648 $query = qq|SELECT closedto, revtrans FROM defaults|;
649 ($form->{closedto}, $form->{revtrans}) = selectrow_query($form, $dbh, $query);
651 $query = qq|SELECT id, gldate
653 WHERE id = (SELECT max(id) FROM gl)|;
654 ($form->{previous_id}, $form->{previous_gldate}) = selectrow_query($form, $dbh, $query);
658 qq|SELECT g.reference, g.description, g.notes, g.transdate, g.deliverydate, g.tax_point,
659 g.storno, g.storno_id,
660 g.department_id, d.description AS department,
661 e.name AS employee, g.taxincluded, g.gldate,
662 g.ob_transaction, g.cb_transaction,
663 g.transaction_description
665 LEFT JOIN department d ON (d.id = g.department_id)
666 LEFT JOIN employee e ON (e.id = g.employee_id)
668 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
669 map { $form->{$_} = $ref->{$_} } keys %$ref;
671 # retrieve individual rows
673 qq|SELECT c.accno, t.taxkey AS accnotaxkey, a.amount, a.memo, a.source,
674 a.transdate, a.cleared, a.project_id, p.projectnumber,
675 a.taxkey, t.rate AS taxrate, t.id, a.chart_id,
677 FROM chart c1, tax t1
678 WHERE (t1.id = t.id) AND (c1.id = t.chart_id)) AS taxaccno,
681 WHERE (tk.chart_id = a.chart_id) AND (tk.startdate <= a.transdate)
682 ORDER BY tk.startdate desc LIMIT 1) AS tax_id
684 JOIN chart c ON (c.id = a.chart_id)
685 LEFT JOIN project p ON (p.id = a.project_id)
686 LEFT JOIN tax t ON (t.id = a.tax_id)
687 WHERE (a.trans_id = ?)
688 AND (a.fx_transaction = '0')
689 ORDER BY a.acc_trans_id, a.transdate|;
690 $form->{GL} = selectall_hashref_query($form, $dbh, $query, conv_i($form->{id}));
697 WHERE id = (SELECT MAX(id) FROM gl)
700 ($form->{transdate}) = selectrow_query($form, $dbh, $query);
703 # get tax description
704 $query = qq|SELECT * FROM tax ORDER BY taxkey|;
705 $form->{TAX} = selectall_hashref_query($form, $dbh, $query);
707 # get chart of accounts
709 qq|SELECT c.accno, c.description, c.link, tk.taxkey_id, tk.tax_id
711 LEFT JOIN taxkeys tk ON (tk.id =
714 WHERE (taxkeys.chart_id = c.id)
716 ORDER BY startdate DESC
719 $form->{chart} = selectall_hashref_query($form, $dbh, $query, conv_date($form->{transdate}));
721 $main::lxdebug->leave_sub();
725 my ($self, $form, $myconfig, $id) = @_;
726 $main::lxdebug->enter_sub();
728 my $rc = SL::DB->client->with_transaction(\&_storno, $self, $form, $myconfig, $id);
730 $::lxdebug->leave_sub;
735 my ($self, $form, $myconfig, $id) = @_;
737 my ($query, $new_id, $storno_row, $acc_trans_rows);
738 my $dbh = SL::DB->client->dbh;
740 $query = qq|SELECT nextval('glid')|;
741 ($new_id) = selectrow_query($form, $dbh, $query);
743 $query = qq|SELECT * FROM gl WHERE id = ?|;
744 $storno_row = selectfirst_hashref_query($form, $dbh, $query, $id);
746 $storno_row->{id} = $new_id;
747 $storno_row->{storno_id} = $id;
748 $storno_row->{storno} = 't';
749 $storno_row->{reference} = 'Storno-' . $storno_row->{reference};
751 $query = qq|SELECT id FROM employee WHERE login = ?|;
752 my ($employee_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
753 $storno_row->{employee_id} = $employee_id;
755 delete @$storno_row{qw(itime mtime gldate)};
757 $query = sprintf 'INSERT INTO gl (%s) VALUES (%s)', join(', ', keys %$storno_row), join(', ', map '?', values %$storno_row);
758 do_query($form, $dbh, $query, (values %$storno_row));
760 $query = qq|UPDATE gl SET storno = 't' WHERE id = ?|;
761 do_query($form, $dbh, $query, $id);
763 # now copy acc_trans entries
764 $query = qq|SELECT * FROM acc_trans WHERE trans_id = ?|;
765 my $rowref = selectall_hashref_query($form, $dbh, $query, $id);
767 for my $row (@$rowref) {
768 delete @$row{qw(itime mtime acc_trans_id gldate)};
769 $query = sprintf 'INSERT INTO acc_trans (%s) VALUES (%s)', join(', ', keys %$row), join(', ', map '?', values %$row);
770 $row->{trans_id} = $new_id;
771 $row->{amount} *= -1;
772 do_query($form, $dbh, $query, (values %$row));
778 sub get_chart_balances {
779 my ($self, @chart_ids) = @_;
781 return () unless @chart_ids;
783 my $placeholders = join ', ', ('?') x scalar(@chart_ids);
784 my $query = qq|SELECT chart_id, SUM(amount) AS sum
786 WHERE chart_id IN (${placeholders})
789 my %balances = selectall_as_map($::form, $::form->get_standard_dbh(\%::myconfig), $query, 'chart_id', 'sum', @chart_ids);
794 sub get_active_taxes_for_chart {
795 my ($self, $chart_id, $transdate, $tax_id) = @_;
797 my $chart = SL::DB::Chart->new(id => $chart_id)->load;
798 my $active_taxkey = $chart->get_active_taxkey($transdate);
800 my $where = [ chart_categories => { like => '%' . $chart->category . '%' } ];
802 if ( defined $tax_id && $tax_id >= 0 ) {
803 $where = [ or => [ chart_categories => { like => '%' . $chart->category . '%' },
809 my $taxes = SL::DB::Manager::Tax->get_all(
811 sort_by => 'taxkey, rate',
814 my $default_tax = first { $active_taxkey->tax_id == $_->id } @{ $taxes };
815 $default_tax->{is_default} = 1 if $default_tax;
830 SL::GL - some useful GL functions
836 =item C<get_active_taxes_for_chart> $transdate $tax_id
838 Returns a list of valid taxes for a certain chart.
840 If the optional param transdate exists one entry in the returning list
841 may get the attribute C<is_default> for this specific tax-dependent date.
842 The possible entries are filtered by the charttype of the tax, i.e. only taxes
843 whose chart_categories match the category of the chart will be shown.
845 In the case of existing records, e.g. when opening an old ar record, due to
846 changes in the configurations the desired tax might not be available in the
847 dropdown anymore. If we are loading an old record and know its tax_id (from
848 acc_trans), we can pass $tax_id as the third parameter and be sure that the
849 original tax always appears in the dropdown.
851 The functions returns an array which may be used for building dropdowns in ar/ap/gl code.
865 G. Richardson E<lt>grichardson@kivitec.deE<gt>