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 (trim($form->{transaction_description})) {
305 $glwhere .= " AND g.transaction_description ILIKE ?";
306 $arwhere .= " AND a.transaction_description ILIKE ?";
307 $apwhere .= " AND a.transaction_description ILIKE ?";
308 push(@glvalues, like($form->{transaction_description}));
309 push(@arvalues, like($form->{transaction_description}));
310 push(@apvalues, like($form->{transaction_description}));
313 if ($form->{accno}) {
314 $glwhere .= " AND c.accno = '$form->{accno}'";
315 $arwhere .= " AND c.accno = '$form->{accno}'";
316 $apwhere .= " AND c.accno = '$form->{accno}'";
319 if ($form->{category} ne 'X') {
320 $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 = ?))|;
321 $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 = ?))|;
322 $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 = ?))|;
323 push(@glvalues, $form->{category});
324 push(@arvalues, $form->{category});
325 push(@apvalues, $form->{category});
328 if ($form->{project_id}) {
329 $glwhere .= qq| AND g.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)|;
331 qq| AND ((a.globalproject_id = ?) OR
332 (a.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)))|;
334 qq| AND ((a.globalproject_id = ?) OR
335 (a.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)))|;
336 my $project_id = conv_i($form->{project_id});
337 push(@glvalues, $project_id);
338 push(@arvalues, $project_id, $project_id);
339 push(@apvalues, $project_id, $project_id);
342 my ($project_columns, $project_join);
343 my ($arap_globalproject_columns, $arap_globalproject_join);
344 my ($gl_globalproject_columns);
345 if ($form->{"l_projectnumbers"}) {
346 $project_columns = qq|, ac.project_id, pr.projectnumber|;
347 $project_join = qq|LEFT JOIN project pr ON (ac.project_id = pr.id)|;
348 $arap_globalproject_columns = qq|, a.globalproject_id, globalpr.projectnumber AS globalprojectnumber|;
349 $arap_globalproject_join = qq|LEFT JOIN project globalpr ON (a.globalproject_id = globalpr.id)|;
350 $gl_globalproject_columns = qq|, NULL AS globalproject_id, '' AS globalprojectnumber|;
353 if ($form->{accno}) {
354 # get category for account
355 $query = qq|SELECT category FROM chart WHERE accno = ?|;
356 ($form->{ml}) = selectrow_query($form, $dbh, $query, $form->{accno});
358 if ($form->{datefrom}) {
360 qq|SELECT SUM(ac.amount)
362 LEFT JOIN chart c ON (ac.chart_id = c.id)
363 WHERE (c.accno = ?) AND (ac.$form->{datesort} < ?)|;
364 ($form->{balance}) = selectrow_query($form, $dbh, $query, $form->{accno}, conv_date($form->{datefrom}));
370 'transdate' => [ qw(transdate id) ],
371 'gldate' => [ qw(gldate id) ],
372 'reference' => [ qw(lower_reference id) ],
373 'description' => [ qw(lower_description id) ],
374 'accno' => [ qw(accno transdate id) ],
375 'department' => [ qw(department transdate id) ],
376 'transaction_description' => [ qw(lower_transaction_description id) ],
378 my %lowered_columns = (
379 'reference' => { 'gl' => 'g.reference', 'arap' => 'a.invnumber', },
380 'source' => { 'gl' => 'ac.source', 'arap' => 'ac.source', },
381 'description' => { 'gl' => 'g.description', 'arap' => 'ct.name', },
382 'transaction_description' => { 'gl' => 'g.transaction_description', 'arap' => 'a.transaction_description', },
385 # sortdir = sort direction (ascending or descending)
386 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
387 my $sortkey = $sort_columns{$form->{sort}} ? $form->{sort} : $form->{datesort}; # default used to be transdate
388 my $sortorder = join ', ', map { "$_ $sortdir" } @{ $sort_columns{$sortkey} };
390 my %columns_for_sorting = ( 'gl' => '', 'arap' => '', );
391 foreach my $spec (@{ $sort_columns{$sortkey} }) {
392 next if ($spec !~ m/^lower_(.*)$/);
395 map { $columns_for_sorting{$_} .= sprintf(', lower(%s) AS lower_%s', $lowered_columns{$column}->{$_}, $column) } qw(gl arap);
400 ac.acc_trans_id, g.id, 'gl' AS type, FALSE AS invoice, g.reference, ac.taxkey, c.link,
401 g.description, ac.transdate, ac.gldate, ac.source, ac.trans_id,
402 ac.amount, c.accno, g.notes, t.chart_id,
403 d.description AS department, g.transaction_description,
404 CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
405 $project_columns $gl_globalproject_columns
406 $columns_for_sorting{gl}
408 LEFT JOIN employee e ON (g.employee_id = e.id)
409 LEFT JOIN department d ON (g.department_id = d.id),
410 acc_trans ac $project_join, chart c
411 LEFT JOIN tax t ON (t.chart_id = c.id)
413 AND (ac.chart_id = c.id)
414 AND (g.id = ac.trans_id)
418 SELECT ac.acc_trans_id, a.id, 'ar' AS type, a.invoice, a.invnumber, ac.taxkey, c.link,
419 ct.name, ac.transdate, ac.gldate, ac.source, ac.trans_id,
420 ac.amount, c.accno, a.notes, t.chart_id,
421 d.description AS department, a.transaction_description,
422 CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
423 $project_columns $arap_globalproject_columns
424 $columns_for_sorting{arap}
426 LEFT JOIN employee e ON (a.employee_id = e.id)
427 LEFT JOIN department d ON (a.department_id = d.id)
428 $arap_globalproject_join,
429 acc_trans ac $project_join, customer ct, chart c
430 LEFT JOIN tax t ON (t.chart_id=c.id)
432 AND (ac.chart_id = c.id)
433 AND (a.customer_id = ct.id)
434 AND (a.id = ac.trans_id)
438 SELECT ac.acc_trans_id, a.id, 'ap' AS type, a.invoice, a.invnumber, ac.taxkey, c.link,
439 ct.name, ac.transdate, ac.gldate, ac.source, ac.trans_id,
440 ac.amount, c.accno, a.notes, t.chart_id,
441 d.description AS department, a.transaction_description,
442 CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
443 $project_columns $arap_globalproject_columns
444 $columns_for_sorting{arap}
446 LEFT JOIN employee e ON (a.employee_id = e.id)
447 LEFT JOIN department d ON (a.department_id = d.id)
448 $arap_globalproject_join,
449 acc_trans ac $project_join, vendor ct, chart c
450 LEFT JOIN tax t ON (t.chart_id=c.id)
452 AND (ac.chart_id = c.id)
453 AND (a.vendor_id = ct.id)
454 AND (a.id = ac.trans_id)
456 ORDER BY $sortorder, acc_trans_id $sortdir|;
457 # ORDER BY gldate DESC, id DESC, acc_trans_id DESC
459 my @values = (@glvalues, @arvalues, @apvalues);
461 # Show all $query in Debuglevel LXDebug::QUERY
462 my $callingdetails = (caller (0))[3];
463 dump_query(LXDebug->QUERY(), "$callingdetails", $query, @values);
465 $sth = prepare_execute_query($form, $dbh, $query, @values);
470 my ($i, $j, $k, $l, $ref, $ref2);
473 while (my $ref0 = $sth->fetchrow_hashref("NAME_lc")) {
475 $trans_id = $ref0->{id};
477 my $source = $ref0->{source};
478 undef($ref0->{source});
480 if ($trans_id != $trans_id2) { # first line of a booking
483 push(@{ $form->{GL} }, $ref);
488 $trans_id2 = $ref->{id};
491 if ($ref->{type} eq "gl") {
492 $ref->{module} = "gl";
496 if ($ref->{type} eq "ap") {
497 if ($ref->{invoice}) {
498 $ref->{module} = "ir";
500 $ref->{module} = "ap";
505 if ($ref->{type} eq "ar") {
506 if ($ref->{invoice}) {
507 $ref->{module} = "is";
509 $ref->{module} = "ar";
513 $ref->{"projectnumbers"} = {};
514 $ref->{"projectnumbers"}->{$ref->{"projectnumber"}} = 1 if ($ref->{"projectnumber"});
515 $ref->{"projectnumbers"}->{$ref->{"globalprojectnumber"}} = 1 if ($ref->{"globalprojectnumber"});
517 $balance = $ref->{amount};
519 # Linenumbers of General Ledger
520 $k = 0; # Debit # AP # Soll
521 $l = 0; # Credit # AR # Haben
522 $i = 0; # Debit Tax # AP_tax # VSt
523 $j = 0; # Credit Tax # AR_tax # USt
525 if ($ref->{chart_id} > 0) { # all tax accounts first line, no line increasing
526 if ($ref->{amount} < 0) {
527 if ($ref->{link} =~ /AR_tax/) {
528 $ref->{credit_tax}{$j} = $ref->{amount};
529 $ref->{credit_tax_accno}{$j} = $ref->{accno};
531 if ($ref->{link} =~ /AP_tax/) {
532 $ref->{debit_tax}{$i} = $ref->{amount} * -1;
533 $ref->{debit_tax_accno}{$i} = $ref->{accno};
536 if ($ref->{link} =~ /AR_tax/) {
537 $ref->{credit_tax}{$j} = $ref->{amount};
538 $ref->{credit_tax_accno}{$j} = $ref->{accno};
540 if ($ref->{link} =~ /AP_tax/) {
541 $ref->{debit_tax}{$i} = $ref->{amount} * -1;
542 $ref->{debit_tax_accno}{$i} = $ref->{accno};
545 } else { #all other accounts first line
547 if ($ref->{amount} < 0) {
548 $ref->{debit}{$k} = $ref->{amount} * -1;
549 $ref->{debit_accno}{$k} = $ref->{accno};
550 $ref->{debit_taxkey}{$k} = $ref->{taxkey};
551 $ref->{ac_transdate}{$k} = $ref->{transdate};
552 $ref->{source}{$k} = $source;
554 $ref->{credit}{$l} = $ref->{amount} * 1;
555 $ref->{credit_accno}{$l} = $ref->{accno};
556 $ref->{credit_taxkey}{$l} = $ref->{taxkey};
557 $ref->{ac_transdate}{$l} = $ref->{transdate};
558 $ref->{source}{$l} = $source;
562 } else { # following lines of a booking, line increasing
565 # $trans_old = $trans_id2; # doesn't seem to be used anymore
566 $trans_id2 = $ref2->{id};
569 (int($balance * 100000) + int(100000 * $ref2->{amount})) / 100000;
571 $ref->{"projectnumbers"}->{$ref2->{"projectnumber"}} = 1 if ($ref2->{"projectnumber"});
572 $ref->{"projectnumbers"}->{$ref2->{"globalprojectnumber"}} = 1 if ($ref2->{"globalprojectnumber"});
574 if ($ref2->{chart_id} > 0) { # all tax accounts, following lines
575 if ($ref2->{amount} < 0) {
576 if ($ref2->{link} =~ /AR_tax/) {
577 if ($ref->{credit_tax_accno}{$j} ne "") {
580 $ref->{credit_tax}{$j} = $ref2->{amount};
581 $ref->{credit_tax_accno}{$j} = $ref2->{accno};
583 if ($ref2->{link} =~ /AP_tax/) {
584 if ($ref->{debit_tax_accno}{$i} ne "") {
587 $ref->{debit_tax}{$i} = $ref2->{amount} * -1;
588 $ref->{debit_tax_accno}{$i} = $ref2->{accno};
591 if ($ref2->{link} =~ /AR_tax/) {
592 if ($ref->{credit_tax_accno}{$j} ne "") {
595 $ref->{credit_tax}{$j} = $ref2->{amount};
596 $ref->{credit_tax_accno}{$j} = $ref2->{accno};
598 if ($ref2->{link} =~ /AP_tax/) {
599 if ($ref->{debit_tax_accno}{$i} ne "") {
602 $ref->{debit_tax}{$i} = $ref2->{amount} * -1;
603 $ref->{debit_tax_accno}{$i} = $ref2->{accno};
606 } else { # all other accounts, following lines
607 if ($ref2->{amount} < 0) {
608 if ($ref->{debit_accno}{$k} ne "") {
611 if ($ref->{source}{$k} ne "") {
616 $ref->{debit}{$k} = $ref2->{amount} * - 1;
617 $ref->{debit_accno}{$k} = $ref2->{accno};
618 $ref->{debit_taxkey}{$k} = $ref2->{taxkey};
619 $ref->{ac_transdate}{$k} = $ref2->{transdate};
620 $ref->{source}{$k} = $source . $space . $ref->{source}{$k};
622 if ($ref->{credit_accno}{$l} ne "") {
625 if ($ref->{source}{$l} ne "") {
630 $ref->{credit}{$l} = $ref2->{amount};
631 $ref->{credit_accno}{$l} = $ref2->{accno};
632 $ref->{credit_taxkey}{$l} = $ref2->{taxkey};
633 $ref->{ac_transdate}{$l} = $ref2->{transdate};
634 $ref->{source}{$l} = $ref->{source}{$l} . $space . $source;
640 push @{ $form->{GL} }, $ref;
643 if ($form->{accno}) {
644 $query = qq|SELECT c.description FROM chart c WHERE c.accno = ?|;
645 ($form->{account_description}) = selectrow_query($form, $dbh, $query, $form->{accno});
648 $main::lxdebug->leave_sub();
652 my ($self, $myconfig, $form) = @_;
653 $main::lxdebug->enter_sub();
655 my ($query, $sth, $ref, @values);
657 my $dbh = SL::DB->client->dbh;
659 $query = qq|SELECT closedto, revtrans FROM defaults|;
660 ($form->{closedto}, $form->{revtrans}) = selectrow_query($form, $dbh, $query);
662 $query = qq|SELECT id, gldate
664 WHERE id = (SELECT max(id) FROM gl)|;
665 ($form->{previous_id}, $form->{previous_gldate}) = selectrow_query($form, $dbh, $query);
669 qq|SELECT g.reference, g.description, g.notes, g.transdate, g.deliverydate, g.tax_point,
670 g.storno, g.storno_id,
671 g.department_id, d.description AS department,
672 e.name AS employee, g.taxincluded, g.gldate,
673 g.ob_transaction, g.cb_transaction,
674 g.transaction_description
676 LEFT JOIN department d ON (d.id = g.department_id)
677 LEFT JOIN employee e ON (e.id = g.employee_id)
679 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
680 map { $form->{$_} = $ref->{$_} } keys %$ref;
682 # retrieve individual rows
684 qq|SELECT c.accno, t.taxkey AS accnotaxkey, a.amount, a.memo, a.source,
685 a.transdate, a.cleared, a.project_id, p.projectnumber,
686 a.taxkey, t.rate AS taxrate, t.id, a.chart_id,
688 FROM chart c1, tax t1
689 WHERE (t1.id = t.id) AND (c1.id = t.chart_id)) AS taxaccno,
692 WHERE (tk.chart_id = a.chart_id) AND (tk.startdate <= a.transdate)
693 ORDER BY tk.startdate desc LIMIT 1) AS tax_id
695 JOIN chart c ON (c.id = a.chart_id)
696 LEFT JOIN project p ON (p.id = a.project_id)
697 LEFT JOIN tax t ON (t.id = a.tax_id)
698 WHERE (a.trans_id = ?)
699 AND (a.fx_transaction = '0')
700 ORDER BY a.acc_trans_id, a.transdate|;
701 $form->{GL} = selectall_hashref_query($form, $dbh, $query, conv_i($form->{id}));
708 WHERE id = (SELECT MAX(id) FROM gl)
711 ($form->{transdate}) = selectrow_query($form, $dbh, $query);
714 # get tax description
715 $query = qq|SELECT * FROM tax ORDER BY taxkey|;
716 $form->{TAX} = selectall_hashref_query($form, $dbh, $query);
718 # get chart of accounts
720 qq|SELECT c.accno, c.description, c.link, tk.taxkey_id, tk.tax_id
722 LEFT JOIN taxkeys tk ON (tk.id =
725 WHERE (taxkeys.chart_id = c.id)
727 ORDER BY startdate DESC
730 $form->{chart} = selectall_hashref_query($form, $dbh, $query, conv_date($form->{transdate}));
732 $main::lxdebug->leave_sub();
736 my ($self, $form, $myconfig, $id) = @_;
737 $main::lxdebug->enter_sub();
739 my $rc = SL::DB->client->with_transaction(\&_storno, $self, $form, $myconfig, $id);
741 $::lxdebug->leave_sub;
746 my ($self, $form, $myconfig, $id) = @_;
748 my ($query, $new_id, $storno_row, $acc_trans_rows);
749 my $dbh = SL::DB->client->dbh;
751 $query = qq|SELECT nextval('glid')|;
752 ($new_id) = selectrow_query($form, $dbh, $query);
754 $query = qq|SELECT * FROM gl WHERE id = ?|;
755 $storno_row = selectfirst_hashref_query($form, $dbh, $query, $id);
757 $storno_row->{id} = $new_id;
758 $storno_row->{storno_id} = $id;
759 $storno_row->{storno} = 't';
760 $storno_row->{reference} = 'Storno-' . $storno_row->{reference};
762 $query = qq|SELECT id FROM employee WHERE login = ?|;
763 my ($employee_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
764 $storno_row->{employee_id} = $employee_id;
766 delete @$storno_row{qw(itime mtime gldate)};
768 $query = sprintf 'INSERT INTO gl (%s) VALUES (%s)', join(', ', keys %$storno_row), join(', ', map '?', values %$storno_row);
769 do_query($form, $dbh, $query, (values %$storno_row));
771 $query = qq|UPDATE gl SET storno = 't' WHERE id = ?|;
772 do_query($form, $dbh, $query, $id);
774 # now copy acc_trans entries
775 $query = qq|SELECT * FROM acc_trans WHERE trans_id = ?|;
776 my $rowref = selectall_hashref_query($form, $dbh, $query, $id);
778 for my $row (@$rowref) {
779 delete @$row{qw(itime mtime acc_trans_id gldate)};
780 $query = sprintf 'INSERT INTO acc_trans (%s) VALUES (%s)', join(', ', keys %$row), join(', ', map '?', values %$row);
781 $row->{trans_id} = $new_id;
782 $row->{amount} *= -1;
783 do_query($form, $dbh, $query, (values %$row));
789 sub get_chart_balances {
790 my ($self, @chart_ids) = @_;
792 return () unless @chart_ids;
794 my $placeholders = join ', ', ('?') x scalar(@chart_ids);
795 my $query = qq|SELECT chart_id, SUM(amount) AS sum
797 WHERE chart_id IN (${placeholders})
800 my %balances = selectall_as_map($::form, $::form->get_standard_dbh(\%::myconfig), $query, 'chart_id', 'sum', @chart_ids);
805 sub get_active_taxes_for_chart {
806 my ($self, $chart_id, $transdate, $tax_id) = @_;
808 my $chart = SL::DB::Chart->new(id => $chart_id)->load;
809 my $active_taxkey = $chart->get_active_taxkey($transdate);
811 my $where = [ chart_categories => { like => '%' . $chart->category . '%' } ];
813 if ( defined $tax_id && $tax_id >= 0 ) {
814 $where = [ or => [ chart_categories => { like => '%' . $chart->category . '%' },
820 my $taxes = SL::DB::Manager::Tax->get_all(
822 sort_by => 'taxkey, rate',
825 my $default_tax = first { $active_taxkey->tax_id == $_->id } @{ $taxes };
826 $default_tax->{is_default} = 1 if $default_tax;
841 SL::GL - some useful GL functions
847 =item C<get_active_taxes_for_chart> $transdate $tax_id
849 Returns a list of valid taxes for a certain chart.
851 If the optional param transdate exists one entry in the returning list
852 may get the attribute C<is_default> for this specific tax-dependent date.
853 The possible entries are filtered by the charttype of the tax, i.e. only taxes
854 whose chart_categories match the category of the chart will be shown.
856 In the case of existing records, e.g. when opening an old ar record, due to
857 changes in the configurations the desired tax might not be available in the
858 dropdown anymore. If we are loading an old record and know its tax_id (from
859 acc_trans), we can pass $tax_id as the third parameter and be sure that the
860 original tax always appears in the dropdown.
862 The functions returns an array which may be used for building dropdowns in ar/ap/gl code.
876 G. Richardson E<lt>grichardson@kivitec.deE<gt>