SL/AP.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., 675 Mass Ave, Cambridge, MA 02139, USA.
  29 #======================================================================
  30 #
  31 # Accounts Payables database backend routines
  32 #
  33 #======================================================================
  34
  35 package AP;
  36
  37 sub post_transaction {
  38   $main::lxdebug->enter_sub();
  39
  40   my ($self, $myconfig, $form) = @_;
  41
  42   # connect to database
  43   my $dbh = $form->dbconnect_noauto($myconfig);
  44
  45   my ($null, $taxrate, $amount);
  46   my $exchangerate = 0;
  47
  48   ($null, $form->{department_id}) = split(/--/, $form->{department});
  49   $form->{department_id} *= 1;
  50
  51   if ($form->{currency} eq $form->{defaultcurrency}) {
  52     $form->{exchangerate} = 1;
  53   } else {
  54     $exchangerate =
  55       $form->check_exchangerate($myconfig, $form->{currency},
  56                                 $form->{transdate}, 'sell');
  57
  58     $form->{exchangerate} =
  59       ($exchangerate)
  60       ? $exchangerate
  61       : $form->parse_amount($myconfig, $form->{exchangerate});
  62   }
  63
  64   for $i (1 .. $form->{rowcount}) {
  65     $form->{AP_amounts}{"amount_$i"} =
  66       (split(/--/, $form->{"AP_amount_$i"}))[0];
  67   }
  68   ($form->{AP_amounts}{payables}) = split(/--/, $form->{APselected});
  69   ($form->{AP}{payables})         = split(/--/, $form->{APselected});
  70
  71   # reverse and parse amounts
  72   for my $i (1 .. $form->{rowcount}) {
  73     $form->{"amount_$i"} =
  74       $form->round_amount(
  75                          $form->parse_amount($myconfig, $form->{"amount_$i"}) *
  76                            $form->{exchangerate} * -1,
  77                          2);
  78     $amount += ($form->{"amount_$i"} * -1);
  79
  80     # parse tax_$i for later
  81     $form->{"tax_$i"} = $form->parse_amount($myconfig, $form->{"tax_$i"}) * -1;
  82   }
  83
  84   # this is for ap
  85   $form->{amount} = $amount;
  86
  87   # taxincluded doesn't make sense if there is no amount
  88   $form->{taxincluded} = 0 if ($form->{amount} == 0);
  89
  90   for $i (1 .. $form->{rowcount}) {
  91    ($form->{"tax_id_$i"}, $NULL) = split /--/, $form->{"taxchart_$i"};
  92
  93     $query = qq|SELECT c.accno, t.taxkey, t.rate
  94             FROM tax t LEFT JOIN chart c on (c.id=t.chart_id)
  95             WHERE t.id=$form->{"tax_id_$i"}
  96             ORDER BY c.accno|;
  97     $sth = $dbh->prepare($query);
  98     $sth->execute || $form->dberror($query);
  99     ($form->{AP_amounts}{"tax_$i"}, $form->{"taxkey_$i"}, $form->{"taxrate_$i"}) =
 100       $sth->fetchrow_array;
 101     $form->{AP_amounts}{"tax_$i"}{taxkey}    = $form->{"taxkey_$i"};
 102     $form->{AP_amounts}{"amount_$i"}{taxkey} = $form->{"taxkey_$i"};
 103
 104     $sth->finish;
 105     if ($form->{taxincluded} *= 1) {
 106       if (!$form->{"korrektur_$i"}) {
 107         $tax =
 108           $form->{"amount_$i"} -
 109           ($form->{"amount_$i"} / ($form->{"taxrate_$i"} + 1));
 110       } else {
 111         $tax = $form->{"tax_$i"};
 112       }
 113       $amount = $form->{"amount_$i"} - $tax;
 114       $form->{"amount_$i"} = $form->round_amount($amount, 2);
 115       $diff += $amount - $form->{"amount_$i"};
 116       $form->{"tax_$i"} = $form->round_amount($tax, 2);
 117       $form->{netamount} += $form->{"amount_$i"};
 118     } else {
 119       if (!$form->{"korrektur_$i"}) {
 120         $form->{"tax_$i"} = $form->{"amount_$i"} * $form->{"taxrate_$i"};
 121       } else {
 122         $tax = $form->{"tax_$i"};
 123       }
 124       $form->{"tax_$i"} =
 125         $form->round_amount($form->{"tax_$i"} * $form->{exchangerate}, 2);
 126       $form->{netamount} += $form->{"amount_$i"};
 127     }
 128     $form->{total_tax} += $form->{"tax_$i"} * -1;
 129   }
 130
 131   # adjust paidaccounts if there is no date in the last row
 132   $form->{paidaccounts}-- unless ($form->{"datepaid_$form->{paidaccounts}"});
 133
 134   $form->{invpaid} = 0;
 135   $form->{netamount} *= -1;
 136
 137   # add payments
 138   for my $i (1 .. $form->{paidaccounts}) {
 139     $form->{"paid_$i"} =
 140       $form->round_amount($form->parse_amount($myconfig, $form->{"paid_$i"}),
 141                           2);
 142
 143     $form->{invpaid} += $form->{"paid_$i"};
 144     $form->{datepaid} = $form->{"datepaid_$i"};
 145
 146   }
 147
 148   $form->{invpaid} =
 149     $form->round_amount($form->{invpaid} * $form->{exchangerate}, 2);
 150
 151   # store invoice total, this goes into ap table
 152   $form->{invtotal} = $form->{netamount} + $form->{total_tax};
 153
 154   # amount for total AP
 155   $form->{payables} = $form->{invtotal};
 156
 157   my ($query, $sth);
 158
 159   # if we have an id delete old records
 160   if ($form->{id}) {
 161
 162     # delete detail records
 163     $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
 164
 165     $dbh->do($query) || $form->dberror($query);
 166
 167   } else {
 168     my $uid = rand() . time;
 169
 170     $uid .= $form->{login};
 171
 172     $uid = substr($uid, 2, 75);
 173
 174     $query = qq|INSERT INTO ap (invnumber, employee_id)
 175                 VALUES ('$uid', (SELECT e.id FROM employee e
 176                                  WHERE e.login = '$form->{login}') )|;
 177     $dbh->do($query) || $form->dberror($query);
 178
 179     $query = qq|SELECT a.id FROM ap a
 180                 WHERE a.invnumber = '$uid'|;
 181     $sth = $dbh->prepare($query);
 182     $sth->execute || $form->dberror($query);
 183
 184     ($form->{id}) = $sth->fetchrow_array;
 185     $sth->finish;
 186
 187   }
 188
 189   $form->{invnumber} = $form->{id} unless $form->{invnumber};
 190
 191   # escape '
 192   map { $form->{$_} =~ s/\'/\'\'/g } qw(invnumber ordnumber notes);
 193
 194   $form->{datepaid} = $form->{transdate} unless ($form->{datepaid});
 195   my $datepaid = ($form->{invpaid} != 0) ? qq|'$form->{datepaid}'| : 'NULL';
 196
 197   $query = qq|UPDATE ap SET
 198               invnumber = '$form->{invnumber}',
 199               transdate = '$form->{transdate}',
 200               ordnumber = '$form->{ordnumber}',
 201               vendor_id = $form->{vendor_id},
 202               taxincluded = '$form->{taxincluded}',
 203               amount = $form->{invtotal},
 204               duedate = '$form->{duedate}',
 205               paid = $form->{invpaid},
 206               datepaid = $datepaid,
 207               netamount = $form->{netamount},
 208               curr = '$form->{currency}',
 209               notes = '$form->{notes}',
 210               department_id = $form->{department_id}
 211               WHERE id = $form->{id}
 212              |;
 213   $dbh->do($query) || $form->dberror($query);
 214
 215   # update exchangerate
 216   if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
 217     $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0,
 218                                $form->{exchangerate});
 219   }
 220
 221   # add individual transactions
 222   for $i (1 .. $form->{rowcount}) {
 223     if ($form->{"amount_$i"} != 0) {
 224       $project_id = 'NULL';
 225       if ("amount_$i" =~ /amount_/) {
 226         if ($form->{"project_id_$i"} && $form->{"projectnumber_$i"}) {
 227           $project_id = $form->{"project_id_$i"};
 228         }
 229       }
 230       if ("amount_$i" =~ /amount/) {
 231         $taxkey = $form->{AP_amounts}{"amount_$i"}{taxkey};
 232       }
 233
 234       # insert detail records in acc_trans
 235       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
 236                                          project_id, taxkey)
 237                   VALUES ($form->{id}, (SELECT c.id FROM chart c
 238                                          WHERE c.accno = '$form->{AP_amounts}{"amount_$i"}'),
 239                     $form->{"amount_$i"}, '$form->{transdate}', $project_id, '$taxkey')|;
 240       $dbh->do($query) || $form->dberror($query);
 241
 242       if ($form->{"tax_$i"} != 0) {
 243
 244         # insert detail records in acc_trans
 245         $query =
 246           qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
 247                                           project_id, taxkey)
 248                     VALUES ($form->{id}, (SELECT c.id FROM chart c
 249                                           WHERE c.accno = '$form->{AP_amounts}{"tax_$i"}'),
 250                     $form->{"tax_$i"}, '$form->{transdate}', $project_id, '$taxkey')|;
 251         $dbh->do($query) || $form->dberror($query);
 252       }
 253
 254     }
 255   }
 256
 257   # add payables
 258   $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
 259                                       project_id)
 260               VALUES ($form->{id}, (SELECT c.id FROM chart c
 261                                     WHERE c.accno = '$form->{AP_amounts}{payables}'),
 262               $form->{payables}, '$form->{transdate}', $project_id)|;
 263   $dbh->do($query) || $form->dberror($query);
 264
 265   # if there is no amount but a payment record a payable
 266   if ($form->{amount} == 0 && $form->{invtotal} == 0) {
 267     $form->{payables} = $form->{invpaid};
 268   }
 269
 270   # add paid transactions
 271   for my $i (1 .. $form->{paidaccounts}) {
 272     if ($form->{"paid_$i"} != 0) {
 273
 274       $exchangerate = 0;
 275       if ($form->{currency} eq $form->{defaultcurrency}) {
 276         $form->{"exchangerate_$i"} = 1;
 277       } else {
 278         $exchangerate =
 279           $form->check_exchangerate($myconfig, $form->{currency},
 280                                     $form->{"datepaid_$i"}, 'sell');
 281
 282         $form->{"exchangerate_$i"} =
 283           ($exchangerate)
 284           ? $exchangerate
 285           : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
 286       }
 287       $form->{"AP_paid_$i"} =~ s/\"//g;
 288
 289       # get paid account
 290
 291       ($form->{AP}{"paid_$i"}) = split(/--/, $form->{"AP_paid_$i"});
 292       $form->{"datepaid_$i"} = $form->{transdate}
 293         unless ($form->{"datepaid_$i"});
 294
 295       # if there is no amount and invtotal is zero there is no exchangerate
 296       if ($form->{amount} == 0 && $form->{invtotal} == 0) {
 297         $form->{exchangerate} = $form->{"exchangerate_$i"};
 298       }
 299
 300       $amount =
 301         $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} * -1,
 302                             2);
 303       if ($form->{payables}) {
 304         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 305                     transdate)
 306                     VALUES ($form->{id},
 307                            (SELECT c.id FROM chart c
 308                             WHERE c.accno = '$form->{AP}{payables}'),
 309                     $amount, '$form->{"datepaid_$i"}')|;
 310         $dbh->do($query) || $form->dberror($query);
 311       }
 312       $form->{payables} = $amount;
 313
 314       $form->{"memo_$i"} =~ s/\'/\'\'/g;
 315
 316       # add payment
 317       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 318                   transdate, source, memo)
 319                   VALUES ($form->{id},
 320                          (SELECT c.id FROM chart c
 321                           WHERE c.accno = '$form->{AP}{"paid_$i"}'),
 322                   $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
 323                   '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
 324       $dbh->do($query) || $form->dberror($query);
 325
 326       # add exchange rate difference
 327       $amount =
 328         $form->round_amount(
 329                          $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1),
 330                          2);
 331       if ($amount != 0) {
 332         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 333                     transdate, fx_transaction, cleared)
 334                     VALUES ($form->{id},
 335                            (SELECT c.id FROM chart c
 336                             WHERE c.accno = '$form->{AP}{"paid_$i"}'),
 337                     $amount, '$form->{"datepaid_$i"}', '1', '0')|;
 338
 339         $dbh->do($query) || $form->dberror($query);
 340       }
 341
 342       # exchangerate gain/loss
 343       $amount =
 344         $form->round_amount(
 345                         $form->{"paid_$i"} *
 346                           ($form->{exchangerate} - $form->{"exchangerate_$i"}),
 347                         2);
 348
 349       if ($amount != 0) {
 350         $accno = ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno};
 351         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 352                     transdate, fx_transaction, cleared)
 353                     VALUES ($form->{id}, (SELECT c.id FROM chart c
 354                                           WHERE c.accno = '$accno'),
 355                     $amount, '$form->{"datepaid_$i"}', '1', '0')|;
 356         $dbh->do($query) || $form->dberror($query);
 357       }
 358
 359       # update exchange rate record
 360       if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
 361         $form->update_exchangerate($dbh, $form->{currency},
 362                                    $form->{"datepaid_$i"},
 363                                    0, $form->{"exchangerate_$i"});
 364       }
 365     }
 366   }
 367
 368   my $rc = $dbh->commit;
 369   $dbh->disconnect;
 370
 371   $main::lxdebug->leave_sub();
 372
 373   return $rc;
 374 }
 375
 376 sub delete_transaction {
 377   $main::lxdebug->enter_sub();
 378
 379   my ($self, $myconfig, $form, $spool) = @_;
 380
 381   # connect to database
 382   my $dbh = $form->dbconnect_noauto($myconfig);
 383
 384   my $query = qq|DELETE FROM ap WHERE id = $form->{id}|;
 385   $dbh->do($query) || $form->dberror($query);
 386
 387   $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
 388   $dbh->do($query) || $form->dberror($query);
 389
 390   # commit and redirect
 391   my $rc = $dbh->commit;
 392   $dbh->disconnect;
 393
 394   $main::lxdebug->leave_sub();
 395
 396   return $rc;
 397 }
 398
 399 sub ap_transactions {
 400   $main::lxdebug->enter_sub();
 401
 402   my ($self, $myconfig, $form) = @_;
 403
 404   # connect to database
 405   my $dbh = $form->dbconnect($myconfig);
 406
 407   my $query = qq|SELECT a.id, a.invnumber, a.transdate, a.duedate,
 408                  a.amount, a.paid, a.ordnumber, v.name, a.invoice,
 409                  a.netamount, a.datepaid, a.notes, e.name AS employee
 410                  FROM ap a
 411               JOIN vendor v ON (a.vendor_id = v.id)
 412               LEFT JOIN employee e ON (a.employee_id = e.id)|;
 413
 414   my $where = "1 = 1";
 415
 416   if ($form->{vendor_id}) {
 417     $where .= " AND a.vendor_id = $form->{vendor_id}";
 418   } else {
 419     if ($form->{vendor}) {
 420       my $vendor = $form->like(lc $form->{vendor});
 421       $where .= " AND lower(v.name) LIKE '$vendor'";
 422     }
 423   }
 424   if ($form->{department}) {
 425     my ($null, $department_id) = split /--/, $form->{department};
 426     $where .= " AND a.department_id = $department_id";
 427   }
 428   if ($form->{invnumber}) {
 429     my $invnumber = $form->like(lc $form->{invnumber});
 430     $where .= " AND lower(a.invnumber) LIKE '$invnumber'";
 431   }
 432   if ($form->{ordnumber}) {
 433     my $ordnumber = $form->like(lc $form->{ordnumber});
 434     $where .= " AND lower(a.ordnumber) LIKE '$ordnumber'";
 435   }
 436   if ($form->{notes}) {
 437     my $notes = $form->like(lc $form->{notes});
 438     $where .= " AND lower(a.notes) LIKE '$notes'";
 439   }
 440
 441   $where .= " AND a.transdate >= '$form->{transdatefrom}'"
 442     if $form->{transdatefrom};
 443   $where .= " AND a.transdate <= '$form->{transdateto}'"
 444     if $form->{transdateto};
 445   if ($form->{open} || $form->{closed}) {
 446     unless ($form->{open} && $form->{closed}) {
 447       $where .= " AND a.amount <> a.paid" if ($form->{open});
 448       $where .= " AND a.amount = a.paid"  if ($form->{closed});
 449     }
 450   }
 451
 452   my @a = (transdate, invnumber, name);
 453   push @a, "employee" if $self->{l_employee};
 454   my $sortorder = join ', ', $form->sort_columns(@a);
 455   $sortorder = $form->{sort} if $form->{sort};
 456
 457   $query .= "WHERE $where
 458              ORDER by $sortorder";
 459
 460   my $sth = $dbh->prepare($query);
 461   $sth->execute || $form->dberror($query);
 462
 463   while (my $ap = $sth->fetchrow_hashref(NAME_lc)) {
 464     push @{ $form->{AP} }, $ap;
 465   }
 466
 467   $sth->finish;
 468   $dbh->disconnect;
 469
 470   $main::lxdebug->leave_sub();
 471 }
 472
 473 sub get_transdate {
 474   $main::lxdebug->enter_sub();
 475
 476   my ($self, $myconfig, $form) = @_;
 477
 478   # connect to database
 479   my $dbh = $form->dbconnect($myconfig);
 480
 481   my $query =
 482     "SELECT COALESCE(" .
 483     "  (SELECT transdate FROM gl WHERE id = " .
 484     "    (SELECT MAX(id) FROM gl) LIMIT 1), " .
 485     "  current_date)";
 486   ($form->{transdate}) = $dbh->selectrow_array($query);
 487
 488   $dbh->disconnect;
 489
 490   $main::lxdebug->leave_sub();
 491 }
 492
 493 1;
 494