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   # reverse and parse amounts
  65   for my $i (1 .. 1) {
  66     $form->{"amount_$i"} =
  67       $form->round_amount(
  68                          $form->parse_amount($myconfig, $form->{"amount_$i"}) *
  69                            $form->{exchangerate} * -1,
  70                          2);
  71     $amount += ($form->{"amount_$i"} * -1);
  72   }
  73
  74   # this is for ap
  75   $form->{amount} = $amount;
  76
  77   # taxincluded doesn't make sense if there is no amount
  78   $form->{taxincluded} = 0 if ($form->{amount} == 0);
  79
  80   $query =
  81     qq| SELECT c.accno, t.rate FROM chart c, tax t where c.id=t.chart_id AND t.taxkey=$form->{taxkey}|;
  82   $sth = $dbh->prepare($query);
  83   $sth->execute || $form->dberror($query);
  84   ($form->{AP}{"tax"}, $form->{taxrate}) = $sth->fetchrow_array;
  85   $sth->finish;
  86
  87   $formtax = $form->parse_amount($myconfig, $form->{"tax"});
  88
  89   $form->{"tax"} = $form->{amount} * $form->{taxrate};
  90   $form->{"tax"} =
  91     $form->round_amount($form->{"tax"} * $form->{exchangerate}, 2) * -1;
  92
  93   if ($form->{taxcheck}) {
  94     $form->{"tax"} = $formtax * -1;
  95   }
  96
  97   $form->{total_tax} += ($form->{"tax"} * -1);
  98
  99   # adjust paidaccounts if there is no date in the last row
 100   $form->{paidaccounts}-- unless ($form->{"datepaid_$form->{paidaccounts}"});
 101
 102   $form->{invpaid} = 0;
 103
 104   # add payments
 105   for my $i (1 .. $form->{paidaccounts}) {
 106     $form->{"paid_$i"} =
 107       $form->round_amount($form->parse_amount($myconfig, $form->{"paid_$i"}),
 108                           2);
 109
 110     $form->{invpaid} += $form->{"paid_$i"};
 111     $form->{datepaid} = $form->{"datepaid_$i"};
 112
 113   }
 114
 115   $form->{invpaid} =
 116     $form->round_amount($form->{invpaid} * $form->{exchangerate}, 2);
 117
 118   if ($form->{taxincluded} *= 1) {
 119     for $i (1 .. 1) {
 120       $tax =
 121         $form->{"amount_$i"} - ($form->{"amount_$i"} / ($form->{taxrate} + 1));
 122       if ($form->{taxcheck}) {
 123         $tax = $formtax * -1;
 124       }
 125       $amount = $form->{"amount_$i"} - $tax;
 126       $form->{"amount_$i"} = $form->round_amount($amount, 2);
 127       $diff += $amount - $form->{"amount_$i"};
 128       $form->{tax} = $form->round_amount($tax, 2);
 129       $form->{total_tax} = $form->{tax} * -1;
 130     }
 131     print(STDERR "Steuer $form->{tax}\n");
 132     print(STDERR "TotalSteuer $form->{total_tax}\n");
 133
 134     # deduct taxes from amount
 135     # $form->{amount} -= $form->{total_tax};
 136     # deduct difference from amount_1
 137     # $form->{amount_1} += $form->round_amount($diff, 2);
 138     $form->{amount} = $form->{amount_1} * -1;
 139   }
 140
 141   $form->{netamount} = $form->{amount};
 142
 143   # store invoice total, this goes into ap table
 144   $form->{invtotal} = $form->{amount} + $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   foreach my $item (keys %{ $form->{AP} }) {
 215     if ($form->{$item} != 0) {
 216       $project_id = 'NULL';
 217       if ($item =~ /amount_/) {
 218         if ($form->{"project_id_$'"} && $form->{"projectnumber_$'"}) {
 219           $project_id = $form->{"project_id_$'"};
 220         }
 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}{$item}'),
 228                   $form->{$item}, '$form->{transdate}', $project_id, '$form->{taxkey}')|;
 229       $dbh->do($query) || $form->dberror($query);
 230
 231     }
 232   }
 233
 234   # if there is no amount but a payment record a payable
 235   if ($form->{amount} == 0 && $form->{invtotal} == 0) {
 236     $form->{payables} = $form->{invpaid};
 237   }
 238
 239   # add paid transactions
 240   for my $i (1 .. $form->{paidaccounts}) {
 241     if ($form->{"paid_$i"} != 0) {
 242
 243       $exchangerate = 0;
 244       if ($form->{currency} eq $form->{defaultcurrency}) {
 245         $form->{"exchangerate_$i"} = 1;
 246       } else {
 247         $exchangerate =
 248           $form->check_exchangerate($myconfig, $form->{currency},
 249                                     $form->{"datepaid_$i"}, 'sell');
 250
 251         $form->{"exchangerate_$i"} =
 252           ($exchangerate)
 253           ? $exchangerate
 254           : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
 255       }
 256       $form->{"AP_paid_$i"} =~ s/\"//g;
 257
 258       # get paid account
 259
 260       ($form->{AP}{"paid_$i"}) = split(/--/, $form->{"AP_paid_$i"});
 261       $form->{"datepaid_$i"} = $form->{transdate}
 262         unless ($form->{"datepaid_$i"});
 263
 264       # if there is no amount and invtotal is zero there is no exchangerate
 265       if ($form->{amount} == 0 && $form->{invtotal} == 0) {
 266         $form->{exchangerate} = $form->{"exchangerate_$i"};
 267       }
 268
 269       $amount =
 270         $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} * -1,
 271                             2);
 272       if ($form->{payables}) {
 273         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 274                     transdate)
 275                     VALUES ($form->{id},
 276                            (SELECT c.id FROM chart c
 277                             WHERE c.accno = '$form->{AP}{payables}'),
 278                     $amount, '$form->{"datepaid_$i"}')|;
 279         $dbh->do($query) || $form->dberror($query);
 280       }
 281       $form->{payables} = $amount;
 282
 283       $form->{"memo_$i"} =~ s/\'/\'\'/g;
 284
 285       # add payment
 286       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 287                   transdate, source, memo)
 288                   VALUES ($form->{id},
 289                          (SELECT c.id FROM chart c
 290                           WHERE c.accno = '$form->{AP}{"paid_$i"}'),
 291                   $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
 292                   '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
 293       $dbh->do($query) || $form->dberror($query);
 294
 295       # add exchange rate difference
 296       $amount =
 297         $form->round_amount(
 298                          $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1),
 299                          2);
 300       if ($amount != 0) {
 301         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 302                     transdate, fx_transaction, cleared)
 303                     VALUES ($form->{id},
 304                            (SELECT c.id FROM chart c
 305                             WHERE c.accno = '$form->{AP}{"paid_$i"}'),
 306                     $amount, '$form->{"datepaid_$i"}', '1', '0')|;
 307
 308         $dbh->do($query) || $form->dberror($query);
 309       }
 310
 311       # exchangerate gain/loss
 312       $amount =
 313         $form->round_amount(
 314                         $form->{"paid_$i"} *
 315                           ($form->{exchangerate} - $form->{"exchangerate_$i"}),
 316                         2);
 317
 318       if ($amount != 0) {
 319         $accno = ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno};
 320         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 321                     transdate, fx_transaction, cleared)
 322                     VALUES ($form->{id}, (SELECT c.id FROM chart c
 323                                           WHERE c.accno = '$accno'),
 324                     $amount, '$form->{"datepaid_$i"}', '1', '0')|;
 325         $dbh->do($query) || $form->dberror($query);
 326       }
 327
 328       # update exchange rate record
 329       if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
 330         $form->update_exchangerate($dbh, $form->{currency},
 331                                    $form->{"datepaid_$i"},
 332                                    0, $form->{"exchangerate_$i"});
 333       }
 334     }
 335   }
 336
 337   my $rc = $dbh->commit;
 338   $dbh->disconnect;
 339
 340   $main::lxdebug->leave_sub();
 341
 342   return $rc;
 343 }
 344
 345 sub delete_transaction {
 346   $main::lxdebug->enter_sub();
 347
 348   my ($self, $myconfig, $form, $spool) = @_;
 349
 350   # connect to database
 351   my $dbh = $form->dbconnect_noauto($myconfig);
 352
 353   my $query = qq|DELETE FROM ap WHERE id = $form->{id}|;
 354   $dbh->do($query) || $form->dberror($query);
 355
 356   $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
 357   $dbh->do($query) || $form->dberror($query);
 358
 359   # commit and redirect
 360   my $rc = $dbh->commit;
 361   $dbh->disconnect;
 362
 363   $main::lxdebug->leave_sub();
 364
 365   return $rc;
 366 }
 367
 368 sub ap_transactions {
 369   $main::lxdebug->enter_sub();
 370
 371   my ($self, $myconfig, $form) = @_;
 372
 373   # connect to database
 374   my $dbh = $form->dbconnect($myconfig);
 375
 376   my $query = qq|SELECT a.id, a.invnumber, a.transdate, a.duedate,
 377                  a.amount, a.paid, a.ordnumber, v.name, a.invoice,
 378                  a.netamount, a.datepaid, a.notes, e.name AS employee
 379                  FROM ap a
 380               JOIN vendor v ON (a.vendor_id = v.id)
 381               LEFT JOIN employee e ON (a.employee_id = e.id)|;
 382
 383   my $where = "1 = 1";
 384
 385   if ($form->{vendor_id}) {
 386     $where .= " AND a.vendor_id = $form->{vendor_id}";
 387   } else {
 388     if ($form->{vendor}) {
 389       my $vendor = $form->like(lc $form->{vendor});
 390       $where .= " AND lower(v.name) LIKE '$vendor'";
 391     }
 392   }
 393   if ($form->{department}) {
 394     my ($null, $department_id) = split /--/, $form->{department};
 395     $where .= " AND a.department_id = $department_id";
 396   }
 397   if ($form->{invnumber}) {
 398     my $invnumber = $form->like(lc $form->{invnumber});
 399     $where .= " AND lower(a.invnumber) LIKE '$invnumber'";
 400   }
 401   if ($form->{ordnumber}) {
 402     my $ordnumber = $form->like(lc $form->{ordnumber});
 403     $where .= " AND lower(a.ordnumber) LIKE '$ordnumber'";
 404   }
 405   if ($form->{notes}) {
 406     my $notes = $form->like(lc $form->{notes});
 407     $where .= " AND lower(a.notes) LIKE '$notes'";
 408   }
 409
 410   $where .= " AND a.transdate >= '$form->{transdatefrom}'"
 411     if $form->{transdatefrom};
 412   $where .= " AND a.transdate <= '$form->{transdateto}'"
 413     if $form->{transdateto};
 414   if ($form->{open} || $form->{closed}) {
 415     unless ($form->{open} && $form->{closed}) {
 416       $where .= " AND a.amount <> a.paid" if ($form->{open});
 417       $where .= " AND a.amount = a.paid"  if ($form->{closed});
 418     }
 419   }
 420
 421   my @a = (transdate, invnumber, name);
 422   push @a, "employee" if $self->{l_employee};
 423   my $sortorder = join ', ', $form->sort_columns(@a);
 424   $sortorder = $form->{sort} unless $sortorder;
 425
 426   $query .= "WHERE $where
 427              ORDER by $sortorder";
 428
 429   my $sth = $dbh->prepare($query);
 430   $sth->execute || $form->dberror($query);
 431
 432   while (my $ap = $sth->fetchrow_hashref(NAME_lc)) {
 433     push @{ $form->{AP} }, $ap;
 434   }
 435
 436   $sth->finish;
 437   $dbh->disconnect;
 438
 439   $main::lxdebug->leave_sub();
 440 }
 441
 442 1;
 443