SL/GL.pm

   1 #=====================================================================
   2 # LX-Office ERP
   3 # Copyright (C) 2004
   4 # Based on SQL-Ledger Version 2.1.9
   5 # Web http://www.lx-office.org
   6 #
   7 #=====================================================================
   8 # SQL-Ledger Accounting
   9 # Copyright (C) 2001
  10 #
  11 #  Author: Dieter Simader
  12 #   Email: dsimader@sql-ledger.org
  13 #     Web: http://www.sql-ledger.org
  14 #
  15 #  Contributors:
  16 #
  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.
  21 #
  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,
  29 # MA 02110-1335, USA.
  30 #======================================================================
  31 #
  32 # General ledger backend code
  33 #
  34 # CHANGE LOG:
  35 #   DS. 2000-07-04  Created
  36 #   DS. 2001-06-12  Changed relations from accno to chart_id
  37 #
  38 #======================================================================
  39
  40 package GL;
  41
  42 use List::Util qw(first);
  43
  44 use Data::Dumper;
  45 use SL::DATEV qw(:CONSTANTS);
  46 use SL::DBUtils;
  47 use SL::DB::Chart;
  48 use SL::DB::Draft;
  49 use SL::DB::ValidityToken;
  50 use SL::DB::GLTransaction;
  51 use SL::Util qw(trim);
  52 use SL::DB;
  53
  54 use strict;
  55
  56 sub delete_transaction {
  57   my ($self, $myconfig, $form) = @_;
  58   $main::lxdebug->enter_sub();
  59
  60   SL::DB->client->with_transaction(sub {
  61     do_query($form, SL::DB->client->dbh, qq|DELETE FROM gl WHERE id = ?|, conv_i($form->{id}));
  62     1;
  63   }) or do { die SL::DB->client->error };
  64
  65   $main::lxdebug->leave_sub();
  66 }
  67
  68 sub post_transaction {
  69   my ($self, $myconfig, $form) = @_;
  70   $main::lxdebug->enter_sub();
  71
  72   my $rc = SL::DB->client->with_transaction(\&_post_transaction, $self, $myconfig, $form);
  73
  74   $::lxdebug->leave_sub;
  75   return $rc;
  76 }
  77
  78 sub _post_transaction {
  79   my ($self, $myconfig, $form) = @_;
  80   $main::lxdebug->enter_sub();
  81
  82   my $validity_token;
  83   if (!$form->{id}) {
  84     $validity_token = SL::DB::Manager::ValidityToken->fetch_valid_token(
  85       scope => SL::DB::ValidityToken::SCOPE_GL_TRANSACTION_POST(),
  86       token => $form->{form_validity_token},
  87     );
  88
  89     die $::locale->text('The form is not valid anymore.') if !$validity_token;
  90   }
  91
  92   my ($debit, $credit) = (0, 0);
  93   my $project_id;
  94
  95   my $i;
  96
  97   my $dbh = SL::DB->client->dbh;
  98
  99   # post the transaction
 100   # make up a unique handle and store in reference field
 101   # then retrieve the record based on the unique handle to get the id
 102   # replace the reference field with the actual variable
 103   # add records to acc_trans
 104
 105   # if there is a $form->{id} replace the old transaction
 106   # delete all acc_trans entries and add the new ones
 107
 108   if (!$form->{taxincluded}) {
 109     $form->{taxincluded} = 0;
 110   }
 111
 112   my ($query, $sth, @values, $taxkey, $rate, $posted);
 113
 114   if ($form->{id}) {
 115
 116     # delete individual transactions
 117     $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
 118     @values = (conv_i($form->{id}));
 119     do_query($form, $dbh, $query, @values);
 120
 121   } else {
 122     $query = qq|SELECT nextval('glid')|;
 123     ($form->{id}) = selectrow_query($form, $dbh, $query);
 124
 125     $query =
 126       qq|INSERT INTO gl (id, employee_id) | .
 127       qq|VALUES (?, (SELECT id FROM employee WHERE login = ?))|;
 128     @values = ($form->{id}, $::myconfig{login});
 129     do_query($form, $dbh, $query, @values);
 130   }
 131
 132   $form->{ob_transaction} *= 1;
 133   $form->{cb_transaction} *= 1;
 134
 135   $query =
 136     qq|UPDATE gl SET
 137          reference = ?, description = ?, notes = ?,
 138          transdate = ?, deliverydate = ?, tax_point = ?, department_id = ?, taxincluded = ?,
 139          storno = ?, storno_id = ?, ob_transaction = ?, cb_transaction = ?,
 140          transaction_description = ?
 141        WHERE id = ?|;
 142
 143   @values = ($form->{reference}, $form->{description}, $form->{notes},
 144              conv_date($form->{transdate}), conv_date($form->{deliverydate}), conv_date($form->{tax_point}), conv_i($form->{department_id}), $form->{taxincluded} ? 't' : 'f',
 145              $form->{storno} ? 't' : 'f', conv_i($form->{storno_id}), $form->{ob_transaction} ? 't' : 'f', $form->{cb_transaction} ? 't' : 'f',
 146              $form->{transaction_description},
 147              conv_i($form->{id}));
 148   do_query($form, $dbh, $query, @values);
 149
 150   # insert acc_trans transactions
 151   for $i (1 .. $form->{rowcount}) {
 152     ($form->{"tax_id_$i"}) = split(/--/, $form->{"taxchart_$i"});
 153     if ($form->{"tax_id_$i"} ne "") {
 154       $query = qq|SELECT taxkey, rate FROM tax WHERE id = ?|;
 155       ($taxkey, $rate) = selectrow_query($form, $dbh, $query, conv_i($form->{"tax_id_$i"}));
 156     }
 157
 158     my $amount = 0;
 159     my $debit  = $form->{"debit_$i"};
 160     my $credit = $form->{"credit_$i"};
 161     my $tax    = $form->{"tax_$i"};
 162
 163     if ($credit) {
 164       $amount = $credit;
 165       $posted = 0;
 166     }
 167     if ($debit) {
 168       $amount = $debit * -1;
 169       $tax    = $tax * -1;
 170       $posted = 0;
 171     }
 172
 173     $project_id = conv_i($form->{"project_id_$i"});
 174
 175     # if there is an amount, add the record
 176     if ($amount != 0) {
 177       $query =
 178         qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
 179                                   source, memo, project_id, taxkey, ob_transaction, cb_transaction, tax_id, chart_link)
 180            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, (SELECT link FROM chart WHERE id = ?))|;
 181       @values = (conv_i($form->{id}), $form->{"accno_id_$i"}, $amount, conv_date($form->{transdate}),
 182                  $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"});
 183       do_query($form, $dbh, $query, @values);
 184     }
 185
 186     if ($tax != 0) {
 187       # add taxentry
 188       $query =
 189         qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
 190                                   source, memo, project_id, taxkey, tax_id, chart_link)
 191            VALUES (?, (SELECT chart_id FROM tax WHERE id = ?),
 192                    ?, ?, ?, ?, ?, ?, ?, (SELECT link
 193                                          FROM chart
 194                                          WHERE id = (SELECT chart_id
 195                                                      FROM tax
 196                                                      WHERE id = ?)))|;
 197       @values = (conv_i($form->{id}), conv_i($form->{"tax_id_$i"}),
 198                  $tax, conv_date($form->{transdate}), $form->{"source_$i"},
 199                  $form->{"memo_$i"}, $project_id, $taxkey, conv_i($form->{"tax_id_$i"}), conv_i($form->{"tax_id_$i"}));
 200       do_query($form, $dbh, $query, @values);
 201     }
 202   }
 203
 204   if ($form->{storno} && $form->{storno_id}) {
 205     do_query($form, $dbh, qq|UPDATE gl SET storno = 't' WHERE id = ?|, conv_i($form->{storno_id}));
 206   }
 207
 208   if ($form->{draft_id}) {
 209     SL::DB::Manager::Draft->delete_all(where => [ id => delete($form->{draft_id}) ]);
 210   }
 211
 212   # safety check datev export
 213   if ($::instance_conf->get_datev_check_on_gl_transaction) {
 214
 215     # create datev object
 216     my $datev = SL::DATEV->new(
 217       dbh        => $dbh,
 218       trans_id   => $form->{id},
 219     );
 220
 221     $datev->generate_datev_data;
 222
 223     if ($datev->errors) {
 224       die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
 225     }
 226   }
 227
 228   $validity_token->delete if $validity_token;
 229   delete $form->{form_validity_token};
 230
 231   return 1;
 232 }
 233
 234 sub all_transactions {
 235   my ($self, $myconfig, $form) = @_;
 236   $main::lxdebug->enter_sub();
 237
 238   my $dbh = SL::DB->client->dbh;
 239   my ($query, $sth, $source, $null, $space);
 240
 241   my ($glwhere, $arwhere, $apwhere) = ("1 = 1", "1 = 1", "1 = 1");
 242   my (@glvalues, @arvalues, @apvalues);
 243
 244   if ($form->{reference}) {
 245     $glwhere .= qq| AND g.reference ILIKE ?|;
 246     $arwhere .= qq| AND a.invnumber ILIKE ?|;
 247     $apwhere .= qq| AND a.invnumber ILIKE ?|;
 248     push(@glvalues, like($form->{reference}));
 249     push(@arvalues, like($form->{reference}));
 250     push(@apvalues, like($form->{reference}));
 251   }
 252
 253   if ($form->{department_id}) {
 254     $glwhere .= qq| AND g.department_id = ?|;
 255     $arwhere .= qq| AND a.department_id = ?|;
 256     $apwhere .= qq| AND a.department_id = ?|;
 257     push(@glvalues, $form->{department_id});
 258     push(@arvalues, $form->{department_id});
 259     push(@apvalues, $form->{department_id});
 260   }
 261
 262   if ($form->{source}) {
 263     $glwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)";
 264     $arwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)";
 265     $apwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)";
 266     push(@glvalues, like($form->{source}));
 267     push(@arvalues, like($form->{source}));
 268     push(@apvalues, like($form->{source}));
 269   }
 270
 271   # default Datumseinschränkung falls nicht oder falsch übergeben (sollte nie passieren)
 272   $form->{datesort} = 'transdate' unless $form->{datesort} =~ /^(transdate|gldate)$/;
 273
 274   if (trim($form->{datefrom})) {
 275     $glwhere .= " AND ac.$form->{datesort} >= ?";
 276     $arwhere .= " AND ac.$form->{datesort} >= ?";
 277     $apwhere .= " AND ac.$form->{datesort} >= ?";
 278     push(@glvalues, trim($form->{datefrom}));
 279     push(@arvalues, trim($form->{datefrom}));
 280     push(@apvalues, trim($form->{datefrom}));
 281   }
 282
 283   if (trim($form->{dateto})) {
 284     $glwhere .= " AND ac.$form->{datesort} <= ?";
 285     $arwhere .= " AND ac.$form->{datesort} <= ?";
 286     $apwhere .= " AND ac.$form->{datesort} <= ?";
 287     push(@glvalues, trim($form->{dateto}));
 288     push(@arvalues, trim($form->{dateto}));
 289     push(@apvalues, trim($form->{dateto}));
 290   }
 291
 292   if (trim($form->{description})) {
 293     $glwhere .= " AND g.description ILIKE ?";
 294     $arwhere .= " AND ct.name ILIKE ?";
 295     $apwhere .= " AND ct.name ILIKE ?";
 296     push(@glvalues, like($form->{description}));
 297     push(@arvalues, like($form->{description}));
 298     push(@apvalues, like($form->{description}));
 299   }
 300
 301   if ($form->{employee_id}) {
 302     $glwhere .= " AND g.employee_id = ? ";
 303     $arwhere .= " AND a.employee_id = ? ";
 304     $apwhere .= " AND a.employee_id = ? ";
 305     push(@glvalues, conv_i($form->{employee_id}));
 306     push(@arvalues, conv_i($form->{employee_id}));
 307     push(@apvalues, conv_i($form->{employee_id}));
 308   }
 309
 310   if (trim($form->{notes})) {
 311     $glwhere .= " AND g.notes ILIKE ?";
 312     $arwhere .= " AND a.notes ILIKE ?";
 313     $apwhere .= " AND a.notes ILIKE ?";
 314     push(@glvalues, like($form->{notes}));
 315     push(@arvalues, like($form->{notes}));
 316     push(@apvalues, like($form->{notes}));
 317   }
 318
 319   if (trim($form->{transaction_description})) {
 320     $glwhere .= " AND g.transaction_description ILIKE ?";
 321     $arwhere .= " AND a.transaction_description ILIKE ?";
 322     $apwhere .= " AND a.transaction_description ILIKE ?";
 323     push(@glvalues, like($form->{transaction_description}));
 324     push(@arvalues, like($form->{transaction_description}));
 325     push(@apvalues, like($form->{transaction_description}));
 326   }
 327
 328   if ($form->{accno}) {
 329     $glwhere .= " AND c.accno = '$form->{accno}'";
 330     $arwhere .= " AND c.accno = '$form->{accno}'";
 331     $apwhere .= " AND c.accno = '$form->{accno}'";
 332   }
 333
 334   if ($form->{category} ne 'X') {
 335     $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 = ?))|;
 336     $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 = ?))|;
 337     $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 = ?))|;
 338     push(@glvalues, $form->{category});
 339     push(@arvalues, $form->{category});
 340     push(@apvalues, $form->{category});
 341   }
 342
 343   if ($form->{project_id}) {
 344     $glwhere .= qq| AND g.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)|;
 345     $arwhere .=
 346       qq| AND ((a.globalproject_id = ?) OR
 347                (a.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)))|;
 348     $apwhere .=
 349       qq| AND ((a.globalproject_id = ?) OR
 350                (a.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)))|;
 351     my $project_id = conv_i($form->{project_id});
 352     push(@glvalues, $project_id);
 353     push(@arvalues, $project_id, $project_id);
 354     push(@apvalues, $project_id, $project_id);
 355   }
 356
 357   my ($project_columns,            $project_join);
 358   my ($arap_globalproject_columns, $arap_globalproject_join);
 359   my ($gl_globalproject_columns);
 360   if ($form->{"l_projectnumbers"}) {
 361     $project_columns            = qq|, ac.project_id, pr.projectnumber|;
 362     $project_join               = qq|LEFT JOIN project pr ON (ac.project_id = pr.id)|;
 363     $arap_globalproject_columns = qq|, a.globalproject_id, globalpr.projectnumber AS globalprojectnumber|;
 364     $arap_globalproject_join    = qq|LEFT JOIN project globalpr ON (a.globalproject_id = globalpr.id)|;
 365     $gl_globalproject_columns   = qq|, NULL AS globalproject_id, '' AS globalprojectnumber|;
 366   }
 367
 368   if ($form->{accno}) {
 369     # get category for account
 370     $query = qq|SELECT category FROM chart WHERE accno = ?|;
 371     ($form->{ml}) = selectrow_query($form, $dbh, $query, $form->{accno});
 372
 373     if ($form->{datefrom}) {
 374       $query =
 375         qq|SELECT SUM(ac.amount)
 376            FROM acc_trans ac
 377            LEFT JOIN chart c ON (ac.chart_id = c.id)
 378            WHERE (c.accno = ?) AND (ac.$form->{datesort} < ?)|;
 379       ($form->{balance}) = selectrow_query($form, $dbh, $query, $form->{accno}, conv_date($form->{datefrom}));
 380     }
 381   }
 382
 383   my %sort_columns =  (
 384     'id'                      => [ qw(id)                   ],
 385     'transdate'               => [ qw(transdate id)         ],
 386     'gldate'                  => [ qw(gldate id)         ],
 387     'reference'               => [ qw(lower_reference id)   ],
 388     'description'             => [ qw(lower_description id) ],
 389     'accno'                   => [ qw(accno transdate id)   ],
 390     'department'              => [ qw(department transdate id)   ],
 391     'transaction_description' => [ qw(lower_transaction_description id) ],
 392     );
 393   my %lowered_columns =  (
 394     'reference'               => { 'gl' => 'g.reference',               'arap' => 'a.invnumber',               },
 395     'source'                  => { 'gl' => 'ac.source',                 'arap' => 'ac.source',                 },
 396     'description'             => { 'gl' => 'g.description',             'arap' => 'ct.name',                   },
 397     'transaction_description' => { 'gl' => 'g.transaction_description', 'arap' => 'a.transaction_description', },
 398     );
 399
 400   # sortdir = sort direction (ascending or descending)
 401   my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
 402   my $sortkey   = $sort_columns{$form->{sort}} ? $form->{sort} : $form->{datesort};  # default used to be transdate
 403   my $sortorder = join ', ', map { "$_ $sortdir" } @{ $sort_columns{$sortkey} };
 404
 405   my %columns_for_sorting = ( 'gl' => '', 'arap' => '', );
 406   foreach my $spec (@{ $sort_columns{$sortkey} }) {
 407     next if ($spec !~ m/^lower_(.*)$/);
 408
 409     my $column = $1;
 410     map { $columns_for_sorting{$_} .= sprintf(', lower(%s) AS lower_%s', $lowered_columns{$column}->{$_}, $column) } qw(gl arap);
 411   }
 412
 413   $query =
 414     qq|SELECT
 415         ac.acc_trans_id, g.id, 'gl' AS type, FALSE AS invoice, g.reference, ac.taxkey, c.link,
 416         g.description, ac.transdate, ac.gldate, ac.source, ac.trans_id,
 417         ac.amount, c.accno, g.notes, t.chart_id,
 418         d.description AS department, g.transaction_description,
 419         CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
 420         $project_columns $gl_globalproject_columns
 421         $columns_for_sorting{gl}
 422       FROM gl g
 423       LEFT JOIN employee e ON (g.employee_id = e.id)
 424       LEFT JOIN department d ON (g.department_id = d.id),
 425       acc_trans ac $project_join, chart c
 426       LEFT JOIN tax t ON (t.chart_id = c.id)
 427       WHERE $glwhere
 428         AND (ac.chart_id = c.id)
 429         AND (g.id = ac.trans_id)
 430
 431       UNION
 432
 433       SELECT ac.acc_trans_id, a.id, 'ar' AS type, a.invoice, a.invnumber, ac.taxkey, c.link,
 434         ct.name, ac.transdate, ac.gldate, ac.source, ac.trans_id,
 435         ac.amount, c.accno, a.notes, t.chart_id,
 436         d.description AS department, a.transaction_description,
 437         CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
 438         $project_columns $arap_globalproject_columns
 439         $columns_for_sorting{arap}
 440       FROM ar a
 441       LEFT JOIN employee e ON (a.employee_id = e.id)
 442       LEFT JOIN department d ON (a.department_id = d.id)
 443       $arap_globalproject_join,
 444       acc_trans ac $project_join, customer ct, chart c
 445       LEFT JOIN tax t ON (t.chart_id=c.id)
 446       WHERE $arwhere
 447         AND (ac.chart_id = c.id)
 448         AND (a.customer_id = ct.id)
 449         AND (a.id = ac.trans_id)
 450
 451       UNION
 452
 453       SELECT ac.acc_trans_id, a.id, 'ap' AS type, a.invoice, a.invnumber, ac.taxkey, c.link,
 454         ct.name, ac.transdate, ac.gldate, ac.source, ac.trans_id,
 455         ac.amount, c.accno, a.notes, t.chart_id,
 456         d.description AS department, a.transaction_description,
 457         CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
 458         $project_columns $arap_globalproject_columns
 459         $columns_for_sorting{arap}
 460       FROM ap a
 461       LEFT JOIN employee e ON (a.employee_id = e.id)
 462       LEFT JOIN department d ON (a.department_id = d.id)
 463       $arap_globalproject_join,
 464       acc_trans ac $project_join, vendor ct, chart c
 465       LEFT JOIN tax t ON (t.chart_id=c.id)
 466       WHERE $apwhere
 467         AND (ac.chart_id = c.id)
 468         AND (a.vendor_id = ct.id)
 469         AND (a.id = ac.trans_id)
 470
 471       ORDER BY $sortorder, acc_trans_id $sortdir|;
 472 #      ORDER BY gldate DESC, id DESC, acc_trans_id DESC
 473
 474   my @values = (@glvalues, @arvalues, @apvalues);
 475
 476   # Show all $query in Debuglevel LXDebug::QUERY
 477   my $callingdetails = (caller (0))[3];
 478   dump_query(LXDebug->QUERY(), "$callingdetails", $query, @values);
 479
 480   $sth = prepare_execute_query($form, $dbh, $query, @values);
 481   my $trans_id  = "";
 482   my $trans_id2 = "";
 483   my $balance;
 484
 485   my ($i, $j, $k, $l, $ref, $ref2);
 486
 487   $form->{GL} = [];
 488   while (my $ref0 = $sth->fetchrow_hashref("NAME_lc")) {
 489
 490     $trans_id = $ref0->{id};
 491
 492     my $source = $ref0->{source};
 493     undef($ref0->{source});
 494
 495     if ($trans_id != $trans_id2) { # first line of a booking
 496
 497       if ($trans_id2) {
 498         push(@{ $form->{GL} }, $ref);
 499         $balance = 0;
 500       }
 501
 502       $ref       = $ref0;
 503       $trans_id2 = $ref->{id};
 504
 505       # gl
 506       if ($ref->{type} eq "gl") {
 507         $ref->{module} = "gl";
 508       }
 509
 510       # ap
 511       if ($ref->{type} eq "ap") {
 512         if ($ref->{invoice}) {
 513           $ref->{module} = "ir";
 514         } else {
 515           $ref->{module} = "ap";
 516         }
 517       }
 518
 519       # ar
 520       if ($ref->{type} eq "ar") {
 521         if ($ref->{invoice}) {
 522           $ref->{module} = "is";
 523         } else {
 524           $ref->{module} = "ar";
 525         }
 526       }
 527
 528       $ref->{"projectnumbers"} = {};
 529       $ref->{"projectnumbers"}->{$ref->{"projectnumber"}}       = 1 if ($ref->{"projectnumber"});
 530       $ref->{"projectnumbers"}->{$ref->{"globalprojectnumber"}} = 1 if ($ref->{"globalprojectnumber"});
 531
 532       $balance = $ref->{amount};
 533
 534       # Linenumbers of General Ledger
 535       $k       = 0; # Debit      # AP      # Soll
 536       $l       = 0; # Credit     # AR      # Haben
 537       $i       = 0; # Debit Tax  # AP_tax  # VSt
 538       $j       = 0; # Credit Tax # AR_tax  # USt
 539
 540       if ($ref->{chart_id} > 0) { # all tax accounts first line, no line increasing
 541         if ($ref->{amount} < 0) {
 542           if ($ref->{link} =~ /AR_tax/) {
 543             $ref->{credit_tax}{$j}       = $ref->{amount};
 544             $ref->{credit_tax_accno}{$j} = $ref->{accno};
 545          }
 546           if ($ref->{link} =~ /AP_tax/) {
 547             $ref->{debit_tax}{$i}       = $ref->{amount} * -1;
 548             $ref->{debit_tax_accno}{$i} = $ref->{accno};
 549           }
 550         } else {
 551           if ($ref->{link} =~ /AR_tax/) {
 552             $ref->{credit_tax}{$j}       = $ref->{amount};
 553             $ref->{credit_tax_accno}{$j} = $ref->{accno};
 554           }
 555           if ($ref->{link} =~ /AP_tax/) {
 556             $ref->{debit_tax}{$i}       = $ref->{amount} * -1;
 557             $ref->{debit_tax_accno}{$i} = $ref->{accno};
 558           }
 559         }
 560       } else { #all other accounts first line
 561
 562         if ($ref->{amount} < 0) {
 563           $ref->{debit}{$k}        = $ref->{amount} * -1;
 564           $ref->{debit_accno}{$k}  = $ref->{accno};
 565           $ref->{debit_taxkey}{$k} = $ref->{taxkey};
 566           $ref->{ac_transdate}{$k} = $ref->{transdate};
 567           $ref->{source}{$k}       = $source;
 568         } else {
 569           $ref->{credit}{$l}        = $ref->{amount} * 1;
 570           $ref->{credit_accno}{$l}  = $ref->{accno};
 571           $ref->{credit_taxkey}{$l} = $ref->{taxkey};
 572           $ref->{ac_transdate}{$l}  = $ref->{transdate};
 573           $ref->{source}{$l}        = $source;
 574         }
 575       }
 576
 577     } else { # following lines of a booking, line increasing
 578
 579       $ref2      = $ref0;
 580 #      $trans_old = $trans_id2;   # doesn't seem to be used anymore
 581       $trans_id2 = $ref2->{id};
 582
 583       $balance =
 584         (int($balance * 100000) + int(100000 * $ref2->{amount})) / 100000;
 585
 586       $ref->{"projectnumbers"}->{$ref2->{"projectnumber"}}       = 1 if ($ref2->{"projectnumber"});
 587       $ref->{"projectnumbers"}->{$ref2->{"globalprojectnumber"}} = 1 if ($ref2->{"globalprojectnumber"});
 588
 589       if ($ref2->{chart_id} > 0) { # all tax accounts, following lines
 590         if ($ref2->{amount} < 0) {
 591           if ($ref2->{link} =~ /AR_tax/) {
 592             if ($ref->{credit_tax_accno}{$j} ne "") {
 593               $j++;
 594             }
 595             $ref->{credit_tax}{$j}       = $ref2->{amount};
 596             $ref->{credit_tax_accno}{$j} = $ref2->{accno};
 597           }
 598           if ($ref2->{link} =~ /AP_tax/) {
 599             if ($ref->{debit_tax_accno}{$i} ne "") {
 600               $i++;
 601             }
 602             $ref->{debit_tax}{$i}       = $ref2->{amount} * -1;
 603             $ref->{debit_tax_accno}{$i} = $ref2->{accno};
 604           }
 605         } else {
 606           if ($ref2->{link} =~ /AR_tax/) {
 607             if ($ref->{credit_tax_accno}{$j} ne "") {
 608               $j++;
 609             }
 610             $ref->{credit_tax}{$j}       = $ref2->{amount};
 611             $ref->{credit_tax_accno}{$j} = $ref2->{accno};
 612           }
 613           if ($ref2->{link} =~ /AP_tax/) {
 614             if ($ref->{debit_tax_accno}{$i} ne "") {
 615               $i++;
 616             }
 617             $ref->{debit_tax}{$i}       = $ref2->{amount} * -1;
 618             $ref->{debit_tax_accno}{$i} = $ref2->{accno};
 619           }
 620         }
 621       } else { # all other accounts, following lines
 622         if ($ref2->{amount} < 0) {
 623           if ($ref->{debit_accno}{$k} ne "") {
 624             $k++;
 625           }
 626           if ($ref->{source}{$k} ne "") {
 627             $space = " | ";
 628           } else {
 629             $space = "";
 630           }
 631           $ref->{debit}{$k}        = $ref2->{amount} * - 1;
 632           $ref->{debit_accno}{$k}  = $ref2->{accno};
 633           $ref->{debit_taxkey}{$k} = $ref2->{taxkey};
 634           $ref->{ac_transdate}{$k} = $ref2->{transdate};
 635           $ref->{source}{$k}       = $source . $space . $ref->{source}{$k};
 636         } else {
 637           if ($ref->{credit_accno}{$l} ne "") {
 638             $l++;
 639           }
 640           if ($ref->{source}{$l} ne "") {
 641             $space = " | ";
 642           } else {
 643             $space = "";
 644           }
 645           $ref->{credit}{$l}        = $ref2->{amount};
 646           $ref->{credit_accno}{$l}  = $ref2->{accno};
 647           $ref->{credit_taxkey}{$l} = $ref2->{taxkey};
 648           $ref->{ac_transdate}{$l}  = $ref2->{transdate};
 649           $ref->{source}{$l}        = $ref->{source}{$l} . $space . $source;
 650         }
 651       }
 652     }
 653   }
 654
 655   push @{ $form->{GL} }, $ref;
 656   $sth->finish;
 657
 658   if ($form->{accno}) {
 659     $query = qq|SELECT c.description FROM chart c WHERE c.accno = ?|;
 660     ($form->{account_description}) = selectrow_query($form, $dbh, $query, $form->{accno});
 661   }
 662
 663   $main::lxdebug->leave_sub();
 664 }
 665
 666 sub transaction {
 667   my ($self, $myconfig, $form) = @_;
 668   $main::lxdebug->enter_sub();
 669
 670   my ($query, $sth, $ref, @values);
 671
 672   my $dbh = SL::DB->client->dbh;
 673
 674   $query = qq|SELECT closedto, revtrans FROM defaults|;
 675   ($form->{closedto}, $form->{revtrans}) = selectrow_query($form, $dbh, $query);
 676
 677   $query = qq|SELECT id, gldate
 678               FROM gl
 679               WHERE id = (SELECT max(id) FROM gl)|;
 680   ($form->{previous_id}, $form->{previous_gldate}) = selectrow_query($form, $dbh, $query);
 681
 682   if ($form->{id}) {
 683     $query =
 684       qq|SELECT g.reference, g.description, g.notes, g.transdate, g.deliverydate, g.tax_point,
 685            g.storno, g.storno_id,
 686            g.department_id, d.description AS department,
 687            e.name AS employee, g.taxincluded, g.gldate,
 688            g.ob_transaction, g.cb_transaction,
 689            g.transaction_description
 690          FROM gl g
 691          LEFT JOIN department d ON (d.id = g.department_id)
 692          LEFT JOIN employee e ON (e.id = g.employee_id)
 693          WHERE g.id = ?|;
 694     $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
 695     map { $form->{$_} = $ref->{$_} } keys %$ref;
 696
 697     # retrieve individual rows
 698     $query =
 699       qq|SELECT c.accno, t.taxkey AS accnotaxkey, a.amount, a.memo, a.source,
 700            a.transdate, a.cleared, a.project_id, p.projectnumber,
 701            a.taxkey, t.rate AS taxrate, t.id, a.chart_id,
 702            (SELECT c1.accno
 703             FROM chart c1, tax t1
 704             WHERE (t1.id = t.id) AND (c1.id = t.chart_id)) AS taxaccno,
 705            (SELECT tk.tax_id
 706             FROM taxkeys tk
 707             WHERE (tk.chart_id = a.chart_id) AND (tk.startdate <= a.transdate)
 708             ORDER BY tk.startdate desc LIMIT 1) AS tax_id
 709          FROM acc_trans a
 710          JOIN chart c ON (c.id = a.chart_id)
 711          LEFT JOIN project p ON (p.id = a.project_id)
 712          LEFT JOIN tax t ON (t.id = a.tax_id)
 713          WHERE (a.trans_id = ?)
 714            AND (a.fx_transaction = '0')
 715          ORDER BY a.acc_trans_id, a.transdate|;
 716     $form->{GL} = selectall_hashref_query($form, $dbh, $query, conv_i($form->{id}));
 717
 718   } else {
 719     $query =
 720       qq|SELECT COALESCE(
 721            (SELECT transdate
 722             FROM gl
 723             WHERE id = (SELECT MAX(id) FROM gl)
 724             LIMIT 1),
 725            current_date)|;
 726     ($form->{transdate}) = selectrow_query($form, $dbh, $query);
 727   }
 728
 729   # get tax description
 730   $query = qq|SELECT * FROM tax ORDER BY taxkey|;
 731   $form->{TAX} = selectall_hashref_query($form, $dbh, $query);
 732
 733   # get chart of accounts
 734   $query =
 735     qq|SELECT c.accno, c.description, c.link, tk.taxkey_id, tk.tax_id
 736        FROM chart c
 737        LEFT JOIN taxkeys tk ON (tk.id =
 738          (SELECT id
 739           FROM taxkeys
 740           WHERE (taxkeys.chart_id = c.id)
 741             AND (startdate <= ?)
 742           ORDER BY startdate DESC
 743           LIMIT 1))
 744        ORDER BY c.accno|;
 745   $form->{chart} = selectall_hashref_query($form, $dbh, $query, conv_date($form->{transdate}));
 746
 747   $main::lxdebug->leave_sub();
 748 }
 749
 750 sub storno {
 751   my ($self, $form, $myconfig, $id) = @_;
 752   $main::lxdebug->enter_sub();
 753
 754   my $rc = SL::DB->client->with_transaction(\&_storno, $self, $form, $myconfig, $id);
 755
 756   $::lxdebug->leave_sub;
 757   return $rc;
 758 }
 759
 760 sub _storno {
 761   my ($self, $form, $myconfig, $id) = @_;
 762
 763   my ($query, $new_id, $storno_row, $acc_trans_rows);
 764   my $dbh = SL::DB->client->dbh;
 765
 766   $query = qq|SELECT nextval('glid')|;
 767   ($new_id) = selectrow_query($form, $dbh, $query);
 768
 769   $query = qq|SELECT * FROM gl WHERE id = ?|;
 770   $storno_row = selectfirst_hashref_query($form, $dbh, $query, $id);
 771
 772   $storno_row->{id}          = $new_id;
 773   $storno_row->{storno_id}   = $id;
 774   $storno_row->{storno}      = 't';
 775   $storno_row->{reference}   = 'Storno-' . $storno_row->{reference};
 776
 777   $query = qq|SELECT id FROM employee WHERE login = ?|;
 778   my ($employee_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
 779   $storno_row->{employee_id} = $employee_id;
 780
 781   delete @$storno_row{qw(itime mtime gldate)};
 782
 783   $query = sprintf 'INSERT INTO gl (%s) VALUES (%s)', join(', ', keys %$storno_row), join(', ', map '?', values %$storno_row);
 784   do_query($form, $dbh, $query, (values %$storno_row));
 785
 786   $query = qq|UPDATE gl SET storno = 't' WHERE id = ?|;
 787   do_query($form, $dbh, $query, $id);
 788
 789   # now copy acc_trans entries
 790   $query = qq|SELECT * FROM acc_trans WHERE trans_id = ?|;
 791   my $rowref = selectall_hashref_query($form, $dbh, $query, $id);
 792
 793   for my $row (@$rowref) {
 794     delete @$row{qw(itime mtime acc_trans_id gldate)};
 795     $query = sprintf 'INSERT INTO acc_trans (%s) VALUES (%s)', join(', ', keys %$row), join(', ', map '?', values %$row);
 796     $row->{trans_id}   = $new_id;
 797     $row->{amount}    *= -1;
 798     do_query($form, $dbh, $query, (values %$row));
 799   }
 800
 801   if ($form->{workflow_email_journal_id}) {
 802     my $ar_transaction_storno = SL::DB::GLTransaction->new(id => $new_id)->load;
 803     my $email_journal = SL::DB::EmailJournal->new(
 804       id => delete $form->{workflow_email_journal_id}
 805     )->load;
 806     $email_journal->link_to_record_with_attachment(
 807       $ar_transaction_storno,
 808       delete $form->{workflow_email_attachment_id}
 809     );
 810     $form->{callback} = delete $form->{workflow_email_callback};
 811   }
 812
 813   $form->{storno_id} = $id;
 814   return 1;
 815 }
 816
 817 sub get_chart_balances {
 818   my ($self, @chart_ids) = @_;
 819
 820   return () unless @chart_ids;
 821
 822   my $placeholders = join ', ', ('?') x scalar(@chart_ids);
 823   my $query = qq|SELECT chart_id, SUM(amount) AS sum
 824                  FROM acc_trans
 825                  WHERE chart_id IN (${placeholders})
 826                  GROUP BY chart_id|;
 827
 828   my %balances = selectall_as_map($::form, $::form->get_standard_dbh(\%::myconfig), $query, 'chart_id', 'sum', @chart_ids);
 829
 830   return %balances;
 831 }
 832
 833 sub get_active_taxes_for_chart {
 834   my ($self, $chart_id, $transdate, $tax_id) = @_;
 835
 836   my $chart         = SL::DB::Chart->new(id => $chart_id)->load;
 837   my $active_taxkey = $chart->get_active_taxkey($transdate);
 838
 839   my $where = [ chart_categories => { like => '%' . $chart->category . '%' } ];
 840
 841   if ( defined $tax_id && $tax_id >= 0 ) {
 842     $where = [ or => [ chart_categories => { like => '%' . $chart->category . '%' },
 843                        id               => $tax_id
 844                      ]
 845              ];
 846   }
 847
 848   my $taxes         = SL::DB::Manager::Tax->get_all(
 849     where   => $where,
 850     sort_by => 'taxkey, rate',
 851   );
 852
 853   my $default_tax            = first { $active_taxkey->tax_id == $_->id } @{ $taxes };
 854   $default_tax->{is_default} = 1 if $default_tax;
 855
 856   return @{ $taxes };
 857 }
 858
 859 1;
 860
 861 __END__
 862
 863 =pod
 864
 865 =encoding utf8
 866
 867 =head1 NAME
 868
 869 SL::GL - some useful GL functions
 870
 871 =head1 FUNCTIONS
 872
 873 =over 4
 874
 875 =item C<get_active_taxes_for_chart> $transdate $tax_id
 876
 877 Returns a list of valid taxes for a certain chart.
 878
 879 If the optional param transdate exists one entry in the returning list
 880 may get the attribute C<is_default> for this specific tax-dependent date.
 881 The possible entries are filtered by the charttype of the tax, i.e. only taxes
 882 whose chart_categories match the category of the chart will be shown.
 883
 884 In the case of existing records, e.g. when opening an old ar record, due to
 885 changes in the configurations the desired tax might not be available in the
 886 dropdown anymore. If we are loading an old record and know its tax_id (from
 887 acc_trans), we can pass $tax_id as the third parameter and be sure that the
 888 original tax always appears in the dropdown.
 889
 890 The functions returns an array which may be used for building dropdowns in ar/ap/gl code.
 891
 892 =back
 893
 894 =head1 TODO
 895
 896 Nothing here yet.
 897
 898 =head1 BUGS
 899
 900 Nothing here yet.
 901
 902 =head1 AUTHOR
 903
 904 G. Richardson E<lt>grichardson@kivitec.deE<gt>
 905
 906 =cut