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