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