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