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