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