SL/IR.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 # Inventory received module
  32 #
  33 #======================================================================
  34
  35 package IR;
  36
  37 sub post_invoice {
  38   $main::lxdebug->enter_sub();
  39
  40   my ($self, $myconfig, $form) = @_;
  41
  42   # connect to database, turn off autocommit
  43   my $dbh = $form->dbconnect_noauto($myconfig);
  44
  45   my ($query, $sth, $null, $project_id);
  46   my $exchangerate = 0;
  47   my $allocated;
  48   my $taxrate;
  49   my $taxamount;
  50   my $taxdiff;
  51   my $item;
  52
  53   if ($form->{id}) {
  54
  55     &reverse_invoice($dbh, $form);
  56
  57   } else {
  58     my $uid = rand() . time;
  59
  60     $uid .= $form->{login};
  61
  62     $uid = substr($uid, 2, 75);
  63
  64     $query = qq|INSERT INTO ap (invnumber, employee_id)
  65                 VALUES ('$uid', (SELECT e.id FROM employee e
  66                                  WHERE e.login = '$form->{login}'))|;
  67     $dbh->do($query) || $form->dberror($query);
  68
  69     $query = qq|SELECT a.id FROM ap a
  70                 WHERE a.invnumber = '$uid'|;
  71     $sth = $dbh->prepare($query);
  72     $sth->execute || $form->dberror($query);
  73
  74     ($form->{id}) = $sth->fetchrow_array;
  75     $sth->finish;
  76   }
  77
  78   ($null, $form->{contact_id}) = split /--/, $form->{contact};
  79   $form->{contact_id} *= 1;
  80
  81   map { $form->{$_} =~ s/\'/\'\'/g } qw(invnumber ordnumber quonumber);
  82
  83   my ($amount, $linetotal, $lastinventoryaccno, $lastexpenseaccno);
  84   my ($netamount, $invoicediff, $expensediff) = (0, 0, 0);
  85
  86   if ($form->{currency} eq $form->{defaultcurrency}) {
  87     $form->{exchangerate} = 1;
  88   } else {
  89     $exchangerate =
  90       $form->check_exchangerate($myconfig, $form->{currency},
  91                                 $form->{transdate}, 'sell');
  92   }
  93
  94   $form->{exchangerate} =
  95     ($exchangerate)
  96     ? $exchangerate
  97     : $form->parse_amount($myconfig, $form->{exchangerate});
  98
  99   for my $i (1 .. $form->{rowcount}) {
 100     $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
 101
 102     if ($form->{"qty_$i"} != 0) {
 103
 104       map { $form->{"${_}_$i"} =~ s/\'/\'\'/g }
 105         qw(partnumber description unit);
 106
 107       @taxaccounts = split / /, $form->{"taxaccounts_$i"};
 108       $taxdiff     = 0;
 109       $allocated   = 0;
 110       $taxrate     = 0;
 111
 112       $form->{"sellprice_$i"} =
 113         $form->parse_amount($myconfig, $form->{"sellprice_$i"});
 114       my $fxsellprice = $form->{"sellprice_$i"};
 115
 116       my ($dec) = ($fxsellprice =~ /\.(\d+)/);
 117       $dec = length $dec;
 118       my $decimalplaces = ($dec > 2) ? $dec : 2;
 119
 120       map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
 121
 122       if ($form->{"inventory_accno_$i"}) {
 123
 124         $linetotal =
 125           $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
 126
 127         if ($form->{taxincluded}) {
 128           $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
 129           $form->{"sellprice_$i"} =
 130             $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
 131         } else {
 132           $taxamount = $linetotal * $taxrate;
 133         }
 134
 135         $netamount += $linetotal;
 136
 137         if ($form->round_amount($taxrate, 7) == 0) {
 138           if ($form->{taxincluded}) {
 139             foreach $item (@taxaccounts) {
 140               $taxamount =
 141                 $form->round_amount($linetotal * $form->{"${item}_rate"} /
 142                                       (1 + abs($form->{"${item}_rate"})),
 143                                     2);
 144               $taxdiff += $taxamount;
 145               $form->{amount}{ $form->{id} }{$item} -= $taxamount;
 146             }
 147             $form->{amount}{ $form->{id} }{ $taxaccounts[0] } += $taxdiff;
 148           } else {
 149             map {
 150               $form->{amount}{ $form->{id} }{$_} -=
 151                 $linetotal * $form->{"${_}_rate"}
 152             } @taxaccounts;
 153           }
 154         } else {
 155           map {
 156             $form->{amount}{ $form->{id} }{$_} -=
 157               $taxamount * $form->{"${_}_rate"} / $taxrate
 158           } @taxaccounts;
 159         }
 160
 161         # add purchase to inventory, this one is without the tax!
 162         $amount =
 163           $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
 164         $linetotal =
 165           $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
 166           $form->{exchangerate};
 167         $linetotal = $form->round_amount($linetotal, 2);
 168
 169         # this is the difference for the inventory
 170         $invoicediff += ($amount - $linetotal);
 171
 172         $form->{amount}{ $form->{id} }{ $form->{"inventory_accno_$i"} } -=
 173           $linetotal;
 174
 175         # adjust and round sellprice
 176         $form->{"sellprice_$i"} =
 177           $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
 178                               $decimalplaces);
 179
 180         # update parts table
 181         $query = qq|UPDATE parts SET
 182                     lastcost = $form->{"sellprice_$i"}
 183                     WHERE id = $form->{"id_$i"}|;
 184
 185         $dbh->do($query) || $form->dberror($query);
 186
 187         $form->update_balance($dbh, "parts", "onhand",
 188                               qq|id = $form->{"id_$i"}|,
 189                               $form->{"qty_$i"})
 190           unless $form->{shipped};
 191
 192         # check if we sold the item already and
 193         # make an entry for the expense and inventory
 194         $query = qq|SELECT i.id, i.qty, i.allocated, i.trans_id,
 195                     p.inventory_accno_id, p.expense_accno_id, a.transdate
 196                     FROM invoice i, ar a, parts p
 197                     WHERE i.parts_id = p.id
 198                     AND i.parts_id = $form->{"id_$i"}
 199                     AND (i.qty + i.allocated) > 0
 200                     AND i.trans_id = a.id
 201                     ORDER BY transdate|;
 202         $sth = $dbh->prepare($query);
 203         $sth->execute || $form->dberror($query);
 204
 205         my $totalqty = $form->{"qty_$i"};
 206
 207         while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 208
 209           my $qty = $ref->{qty} + $ref->{allocated};
 210
 211           if (($qty - $totalqty) > 0) {
 212             $qty = $totalqty;
 213           }
 214
 215           $linetotal = $form->round_amount($form->{"sellprice_$i"} * $qty, 2);
 216
 217           if ($ref->{allocated} < 0) {
 218
 219             # we have an entry for it already, adjust amount
 220             $form->update_balance(
 221               $dbh,
 222               "acc_trans",
 223               "amount",
 224               qq|trans_id = $ref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$ref->{transdate}'|,
 225               $linetotal);
 226
 227             $form->update_balance(
 228               $dbh,
 229               "acc_trans",
 230               "amount",
 231               qq|trans_id = $ref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$ref->{transdate}'|,
 232               $linetotal * -1);
 233
 234           } else {
 235
 236             # add entry for inventory, this one is for the sold item
 237             if ($linetotal != 0) {
 238               $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 239                           transdate)
 240                           VALUES ($ref->{trans_id}, $ref->{inventory_accno_id},
 241                           $linetotal, '$ref->{transdate}')|;
 242               $dbh->do($query) || $form->dberror($query);
 243
 244               # add expense
 245               $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 246                           transdate, taxkey)
 247                           VALUES ($ref->{trans_id}, $ref->{expense_accno_id},
 248                           | . ($linetotal * -1) . qq|, '$ref->{transdate}',
 249                           (SELECT taxkey from tax WHERE chart_id = $ref->{expense_accno_id}))|;
 250               $dbh->do($query) || $form->dberror($query);
 251             }
 252           }
 253
 254           # update allocated for sold item
 255           $form->update_balance($dbh, "invoice", "allocated",
 256                                 qq|id = $ref->{id}|,
 257                                 $qty * -1);
 258
 259           $allocated += $qty;
 260
 261           last if (($totalqty -= $qty) <= 0);
 262         }
 263
 264         $sth->finish;
 265
 266         $lastinventoryaccno = $form->{"inventory_accno_$i"};
 267
 268       } else {
 269
 270         $linetotal =
 271           $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
 272
 273         if ($form->{taxincluded}) {
 274           $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
 275
 276           $form->{"sellprice_$i"} =
 277             $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
 278         } else {
 279           $taxamount = $linetotal * $taxrate;
 280         }
 281
 282         $netamount += $linetotal;
 283
 284         if ($form->round_amount($taxrate, 7) == 0) {
 285           if ($form->{taxincluded}) {
 286             foreach $item (@taxaccounts) {
 287               $taxamount =
 288                 $linetotal * $form->{"${item}_rate"} /
 289                 (1 + abs($form->{"${item}_rate"}));
 290               $totaltax += $taxamount;
 291               $form->{amount}{ $form->{id} }{$item} -= $taxamount;
 292             }
 293           } else {
 294             map {
 295               $form->{amount}{ $form->{id} }{$_} -=
 296                 $linetotal * $form->{"${_}_rate"}
 297             } @taxaccounts;
 298           }
 299         } else {
 300           map {
 301             $form->{amount}{ $form->{id} }{$_} -=
 302               $taxamount * $form->{"${_}_rate"} / $taxrate
 303           } @taxaccounts;
 304         }
 305
 306         $amount =
 307           $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
 308         $linetotal =
 309           $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
 310           $form->{exchangerate};
 311         $linetotal = $form->round_amount($linetotal, 2);
 312
 313         # this is the difference for expense
 314         $expensediff += ($amount - $linetotal);
 315
 316         # add amount to expense
 317         $form->{amount}{ $form->{id} }{ $form->{"expense_accno_$i"} } -=
 318           $linetotal;
 319
 320         $lastexpenseaccno = $form->{"expense_accno_$i"};
 321
 322         # adjust and round sellprice
 323         $form->{"sellprice_$i"} =
 324           $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
 325                               $decimalplaces);
 326
 327         # update lastcost
 328         $query = qq|UPDATE parts SET
 329                     lastcost = $form->{"sellprice_$i"}
 330                     WHERE id = $form->{"id_$i"}|;
 331
 332         $dbh->do($query) || $form->dberror($query);
 333
 334       }
 335
 336       $project_id = 'NULL';
 337       if ($form->{"projectnumber_$i"}) {
 338         $project_id = $form->{"projectnumber_$i"};
 339       }
 340       $deliverydate =
 341         ($form->{"deliverydate_$i"})
 342         ? qq|'$form->{"deliverydate_$i"}'|
 343         : "NULL";
 344
 345       # save detail record in invoice table
 346       $query = qq|INSERT INTO invoice (trans_id, parts_id, description, qty,
 347                   sellprice, fxsellprice, allocated, unit, deliverydate,
 348                   project_id, serialnumber)
 349                   VALUES ($form->{id}, $form->{"id_$i"},
 350                   '$form->{"description_$i"}', | . ($form->{"qty_$i"} * -1) . qq|,
 351                   $form->{"sellprice_$i"}, $fxsellprice, $allocated,
 352                   '$form->{"unit_$i"}', $deliverydate, (SELECT id FROM project WHERE projectnumber = '$project_id'),
 353                   '$form->{"serialnumber_$i"}')|;
 354       $dbh->do($query) || $form->dberror($query);
 355     }
 356   }
 357
 358   $form->{datepaid} = $form->{invdate};
 359
 360   # all amounts are in natural state, netamount includes the taxes
 361   # if tax is included, netamount is rounded to 2 decimal places,
 362   # taxes are not
 363
 364   # total payments
 365   for my $i (1 .. $form->{paidaccounts}) {
 366     $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
 367     $form->{paid} += $form->{"paid_$i"};
 368     $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
 369   }
 370
 371   my ($tax, $paiddiff) = (0, 0);
 372
 373   $netamount = $form->round_amount($netamount, 2);
 374
 375   # figure out rounding errors for amount paid and total amount
 376   if ($form->{taxincluded}) {
 377
 378     $amount    = $form->round_amount($netamount * $form->{exchangerate}, 2);
 379     $paiddiff  = $amount - $netamount * $form->{exchangerate};
 380     $netamount = $amount;
 381
 382     foreach $item (split / /, $form->{taxaccounts}) {
 383       $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
 384       $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
 385       $amount = $form->{amount}{ $form->{id} }{$item} * -1;
 386       $tax += $amount;
 387       $netamount -= $amount;
 388     }
 389
 390     $invoicediff += $paiddiff;
 391     $expensediff += $paiddiff;
 392
 393     ######## this only applies to tax included
 394     if ($lastinventoryaccno) {
 395       $form->{amount}{ $form->{id} }{$lastinventoryaccno} -= $invoicediff;
 396     }
 397     if ($lastexpenseaccno) {
 398       $form->{amount}{ $form->{id} }{$lastexpenseaccno} -= $expensediff;
 399     }
 400
 401   } else {
 402     $amount    = $form->round_amount($netamount * $form->{exchangerate}, 2);
 403     $paiddiff  = $amount - $netamount * $form->{exchangerate};
 404     $netamount = $amount;
 405     foreach my $item (split / /, $form->{taxaccounts}) {
 406       $form->{amount}{ $form->{id} }{$item} =
 407         $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
 408       $amount =
 409         $form->round_amount(
 410             $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate} * -1,
 411             2);
 412       $paiddiff +=
 413         $amount - $form->{amount}{ $form->{id} }{$item} *
 414         $form->{exchangerate} * -1;
 415       $form->{amount}{ $form->{id} }{$item} =
 416         $form->round_amount($amount * -1, 2);
 417       $amount = $form->{amount}{ $form->{id} }{$item} * -1;
 418       $tax += $amount;
 419     }
 420   }
 421
 422   $form->{amount}{ $form->{id} }{ $form->{AP} } = $netamount + $tax;
 423
 424   if ($form->{paid} != 0) {
 425     $form->{paid} =
 426       $form->round_amount($form->{paid} * $form->{exchangerate} + $paiddiff,
 427                           2);
 428   }
 429
 430   # update exchangerate
 431   if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
 432     $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, 0,
 433                                $form->{exchangerate});
 434   }
 435
 436   # record acc_trans transactions
 437   foreach my $trans_id (keys %{ $form->{amount} }) {
 438     foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
 439       if (
 440           ($form->{amount}{$trans_id}{$accno} =
 441            $form->round_amount($form->{amount}{$trans_id}{$accno}, 2)
 442           ) != 0
 443         ) {
 444         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 445                     transdate, taxkey)
 446                     VALUES ($trans_id, (SELECT c.id FROM chart c
 447                                          WHERE c.accno = '$accno'),
 448                     $form->{amount}{$trans_id}{$accno}, '$form->{invdate}',
 449                     (SELECT taxkey_id  FROM chart WHERE accno = '$accno'))|;
 450         $dbh->do($query) || $form->dberror($query);
 451       }
 452     }
 453   }
 454
 455   # deduct payment differences from paiddiff
 456   for my $i (1 .. $form->{paidaccounts}) {
 457     if ($form->{"paid_$i"} != 0) {
 458       $amount =
 459         $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
 460       $paiddiff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
 461     }
 462   }
 463
 464   # force AP entry if 0
 465   $form->{amount}{ $form->{id} }{ $form->{AP} } = $form->{paid}
 466     if ($form->{amount}{ $form->{id} }{ $form->{AP} } == 0);
 467
 468   # record payments and offsetting AP
 469   for my $i (1 .. $form->{paidaccounts}) {
 470
 471     if ($form->{"paid_$i"} != 0) {
 472       my ($accno) = split /--/, $form->{"AP_paid_$i"};
 473       $form->{"datepaid_$i"} = $form->{invdate}
 474         unless ($form->{"datepaid_$i"});
 475       $form->{datepaid} = $form->{"datepaid_$i"};
 476
 477       $amount = (
 478                  $form->round_amount(
 479                       $form->{"paid_$i"} * $form->{exchangerate} + $paiddiff, 2
 480                  )
 481       ) * -1;
 482
 483       # record AP
 484
 485       if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
 486         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 487                     transdate)
 488                     VALUES ($form->{id}, (SELECT c.id FROM chart c
 489                                         WHERE c.accno = '$form->{AP}'),
 490                     $amount, '$form->{"datepaid_$i"}')|;
 491         $dbh->do($query) || $form->dberror($query);
 492       }
 493
 494       # record payment
 495
 496       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
 497                   source, memo)
 498                   VALUES ($form->{id}, (SELECT c.id FROM chart c
 499                                       WHERE c.accno = '$accno'),
 500                   $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
 501                   '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
 502       $dbh->do($query) || $form->dberror($query);
 503
 504       $exchangerate = 0;
 505
 506       if ($form->{currency} eq $form->{defaultcurrency}) {
 507         $form->{"exchangerate_$i"} = 1;
 508       } else {
 509         $exchangerate =
 510           $form->check_exchangerate($myconfig, $form->{currency},
 511                                     $form->{"datepaid_$i"}, 'sell');
 512
 513         $form->{"exchangerate_$i"} =
 514           ($exchangerate)
 515           ? $exchangerate
 516           : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
 517       }
 518
 519       # exchangerate difference
 520       $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
 521         $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $paiddiff;
 522
 523       # gain/loss
 524       $amount =
 525         ($form->{"paid_$i"} * $form->{exchangerate}) -
 526         ($form->{"paid_$i"} * $form->{"exchangerate_$i"});
 527       if ($amount > 0) {
 528         $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
 529           $amount;
 530       } else {
 531         $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
 532           $amount;
 533       }
 534
 535       $paiddiff = 0;
 536
 537       # update exchange rate
 538       if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
 539         $form->update_exchangerate($dbh, $form->{currency},
 540                                    $form->{"datepaid_$i"},
 541                                    0, $form->{"exchangerate_$i"});
 542       }
 543     }
 544   }
 545
 546   # record exchange rate differences and gains/losses
 547   foreach my $accno (keys %{ $form->{fx} }) {
 548     foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
 549       if (
 550           ($form->{fx}{$accno}{$transdate} =
 551            $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
 552           ) != 0
 553         ) {
 554
 555         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 556                     transdate, cleared, fx_transaction)
 557                     VALUES ($form->{id}, (SELECT c.id FROM chart c
 558                                         WHERE c.accno = '$accno'),
 559                     $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1')|;
 560         $dbh->do($query) || $form->dberror($query);
 561       }
 562     }
 563   }
 564
 565   $amount = $netamount + $tax;
 566
 567   # set values which could be empty
 568   $form->{taxincluded} *= 1;
 569   my $datepaid = ($form->{paid})    ? qq|'$form->{datepaid}'| : "NULL";
 570   my $duedate  = ($form->{duedate}) ? qq|'$form->{duedate}'|  : "NULL";
 571
 572   ($null, $form->{department_id}) = split(/--/, $form->{department});
 573   $form->{department_id} *= 1;
 574
 575   $form->{invnumber} = $form->{id} unless $form->{invnumber};
 576
 577   # save AP record
 578   $query = qq|UPDATE ap set
 579               invnumber = '$form->{invnumber}',
 580               ordnumber = '$form->{ordnumber}',
 581               quonumber = '$form->{quonumber}',
 582               transdate = '$form->{invdate}',
 583               vendor_id = $form->{vendor_id},
 584               amount = $amount,
 585               netamount = $netamount,
 586               paid = $form->{paid},
 587               datepaid = $datepaid,
 588               duedate = $duedate,
 589               invoice = '1',
 590               taxincluded = '$form->{taxincluded}',
 591               notes = '$form->{notes}',
 592               intnotes = '$form->{intnotes}',
 593               curr = '$form->{currency}',
 594               department_id = $form->{department_id},
 595               cp_id = $form->{contact_id}
 596               WHERE id = $form->{id}|;
 597   $dbh->do($query) || $form->dberror($query);
 598
 599   # add shipto
 600   $form->{name} = $form->{vendor};
 601   $form->{name} =~ s/--$form->{vendor_id}//;
 602   $form->add_shipto($dbh, $form->{id});
 603
 604   # delete zero entries
 605   $query = qq|DELETE FROM acc_trans
 606               WHERE amount = 0|;
 607   $dbh->do($query) || $form->dberror($query);
 608
 609   if ($form->{webdav}) {
 610     &webdav_folder($myconfig, $form);
 611   }
 612
 613   my $rc = $dbh->commit;
 614   $dbh->disconnect;
 615
 616   $main::lxdebug->leave_sub();
 617
 618   return $rc;
 619 }
 620
 621 sub reverse_invoice {
 622   $main::lxdebug->enter_sub();
 623
 624   my ($dbh, $form) = @_;
 625
 626   # reverse inventory items
 627   my $query = qq|SELECT i.parts_id, p.inventory_accno_id, p.expense_accno_id,
 628                  i.qty, i.allocated, i.sellprice
 629                  FROM invoice i, parts p
 630                  WHERE i.parts_id = p.id
 631                  AND i.trans_id = $form->{id}|;
 632   my $sth = $dbh->prepare($query);
 633   $sth->execute || $form->dberror($query);
 634
 635   my $netamount = 0;
 636
 637   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 638     $netamount += $form->round_amount($ref->{sellprice} * $ref->{qty} * -1, 2);
 639
 640     if ($ref->{inventory_accno_id}) {
 641
 642       # update onhand
 643       $form->update_balance($dbh, "parts", "onhand", qq|id = $ref->{parts_id}|,
 644                             $ref->{qty});
 645
 646       # if $ref->{allocated} > 0 than we sold that many items
 647       if ($ref->{allocated} > 0) {
 648
 649         # get references for sold items
 650         $query = qq|SELECT i.id, i.trans_id, i.allocated, a.transdate
 651                     FROM invoice i, ar a
 652                     WHERE i.parts_id = $ref->{parts_id}
 653                     AND i.allocated < 0
 654                     AND i.trans_id = a.id
 655                     ORDER BY transdate DESC|;
 656         my $sth = $dbh->prepare($query);
 657         $sth->execute || $form->dberror($query);
 658
 659         while (my $pthref = $sth->fetchrow_hashref(NAME_lc)) {
 660           my $qty = $ref->{allocated};
 661           if (($ref->{allocated} + $pthref->{allocated}) > 0) {
 662             $qty = $pthref->{allocated} * -1;
 663           }
 664
 665           my $amount = $form->round_amount($ref->{sellprice} * $qty, 2);
 666
 667           #adjust allocated
 668           $form->update_balance($dbh, "invoice", "allocated",
 669                                 qq|id = $pthref->{id}|, $qty);
 670
 671           $form->update_balance(
 672             $dbh,
 673             "acc_trans",
 674             "amount",
 675             qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$pthref->{transdate}'|,
 676             $amount);
 677
 678           $form->update_balance(
 679             $dbh,
 680             "acc_trans",
 681             "amount",
 682             qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$pthref->{transdate}'|,
 683             $amount * -1);
 684
 685           last if (($ref->{allocated} -= $qty) <= 0);
 686         }
 687         $sth->finish;
 688       }
 689     }
 690   }
 691   $sth->finish;
 692
 693   # delete acc_trans
 694   $query = qq|DELETE FROM acc_trans
 695               WHERE trans_id = $form->{id}|;
 696   $dbh->do($query) || $form->dberror($query);
 697
 698   # delete invoice entries
 699   $query = qq|DELETE FROM invoice
 700               WHERE trans_id = $form->{id}|;
 701   $dbh->do($query) || $form->dberror($query);
 702
 703   $query = qq|DELETE FROM shipto
 704               WHERE trans_id = $form->{id}|;
 705   $dbh->do($query) || $form->dberror($query);
 706
 707   $main::lxdebug->leave_sub();
 708 }
 709
 710 sub delete_invoice {
 711   $main::lxdebug->enter_sub();
 712
 713   my ($self, $myconfig, $form) = @_;
 714
 715   # connect to database
 716   my $dbh = $form->dbconnect_noauto($myconfig);
 717
 718   &reverse_invoice($dbh, $form);
 719
 720   # delete zero entries
 721   my $query = qq|DELETE FROM acc_trans
 722                  WHERE amount = 0|;
 723   $dbh->do($query) || $form->dberror($query);
 724
 725   # delete AP record
 726   my $query = qq|DELETE FROM ap
 727                  WHERE id = $form->{id}|;
 728   $dbh->do($query) || $form->dberror($query);
 729
 730   my $rc = $dbh->commit;
 731   $dbh->disconnect;
 732
 733   $main::lxdebug->leave_sub();
 734
 735   return $rc;
 736 }
 737
 738 sub retrieve_invoice {
 739   $main::lxdebug->enter_sub();
 740
 741   my ($self, $myconfig, $form) = @_;
 742
 743   # connect to database
 744   my $dbh = $form->dbconnect_noauto($myconfig);
 745
 746   my $query;
 747
 748   if ($form->{id}) {
 749
 750     # get default accounts and last invoice number
 751     $query = qq|SELECT (SELECT c.accno FROM chart c
 752                         WHERE d.inventory_accno_id = c.id) AS inventory_accno,
 753                        (SELECT c.accno FROM chart c
 754                         WHERE d.income_accno_id = c.id) AS income_accno,
 755                        (SELECT c.accno FROM chart c
 756                         WHERE d.expense_accno_id = c.id) AS expense_accno,
 757                        (SELECT c.accno FROM chart c
 758                         WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
 759                        (SELECT c.accno FROM chart c
 760                         WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
 761                 d.curr AS currencies
 762                 FROM defaults d|;
 763   } else {
 764     $query = qq|SELECT (SELECT c.accno FROM chart c
 765                         WHERE d.inventory_accno_id = c.id) AS inventory_accno,
 766                        (SELECT c.accno FROM chart c
 767                         WHERE d.income_accno_id = c.id) AS income_accno,
 768                        (SELECT c.accno FROM chart c
 769                         WHERE d.expense_accno_id = c.id) AS expense_accno,
 770                        (SELECT c.accno FROM chart c
 771                         WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
 772                        (SELECT c.accno FROM chart c
 773                         WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
 774                 d.curr AS currencies,
 775                 current_date AS invdate
 776                 FROM defaults d|;
 777   }
 778   my $sth = $dbh->prepare($query);
 779   $sth->execute || $form->dberror($query);
 780
 781   my $ref = $sth->fetchrow_hashref(NAME_lc);
 782   map { $form->{$_} = $ref->{$_} } keys %$ref;
 783   $sth->finish;
 784
 785   if ($form->{id}) {
 786
 787     # retrieve invoice
 788     $query = qq|SELECT a.cp_id, a.invnumber, a.transdate AS invdate, a.duedate,
 789                 a.ordnumber, a.quonumber, a.paid, a.taxincluded, a.notes,
 790                 a.intnotes, a.curr AS currency
 791                 FROM ap a
 792                 WHERE a.id = $form->{id}|;
 793     $sth = $dbh->prepare($query);
 794     $sth->execute || $form->dberror($query);
 795
 796     $ref = $sth->fetchrow_hashref(NAME_lc);
 797     map { $form->{$_} = $ref->{$_} } keys %$ref;
 798     $sth->finish;
 799
 800     $form->{exchangerate} =
 801       $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
 802                               "sell");
 803
 804     # get shipto
 805     $query = qq|SELECT s.* FROM shipto s
 806                 WHERE s.trans_id = $form->{id}|;
 807     $sth = $dbh->prepare($query);
 808     $sth->execute || $form->dberror($query);
 809
 810     $ref = $sth->fetchrow_hashref(NAME_lc);
 811     map { $form->{$_} = $ref->{$_} } keys %$ref;
 812     $sth->finish;
 813
 814     # retrieve individual items
 815     $query = qq|SELECT c1.accno AS inventory_accno,
 816                        c2.accno AS income_accno,
 817                        c3.accno AS expense_accno,
 818                 p.partnumber, i.description, i.qty, i.fxsellprice AS sellprice,
 819                 i.parts_id AS id, i.unit, p.bin, i.deliverydate,
 820                 pr.projectnumber,
 821                 i.project_id, i.serialnumber,
 822                 pg.partsgroup
 823                 FROM invoice i
 824                 JOIN parts p ON (i.parts_id = p.id)
 825                 LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
 826                 LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
 827                 LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
 828                 LEFT JOIN project pr ON (i.project_id = pr.id)
 829                 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
 830                 WHERE i.trans_id = $form->{id}
 831                 ORDER BY i.id|;
 832     $sth = $dbh->prepare($query);
 833     $sth->execute || $form->dberror($query);
 834
 835     while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 836
 837       #set expense_accno=inventory_accno if they are different => bilanz
 838       $vendor_accno =
 839         ($ref->{expense_accno} != $ref->{inventory_accno})
 840         ? $ref->{inventory_accno}
 841         : $ref->{expense_accno};
 842       $vendor_accno =
 843         ($ref->{inventory_accno})
 844         ? $ref->{inventory_accno}
 845         : $ref->{expense_accno};
 846
 847       # get tax rates and description
 848       $accno_id =
 849         ($form->{vc} eq "customer") ? $ref->{income_accno} : $vendor_accno;
 850       $query = qq|SELECT c.accno, c.description, t.rate, t.taxnumber
 851                  FROM chart c, tax t
 852                  WHERE c.id=t.chart_id AND t.taxkey in (SELECT taxkey_id from chart where accno = '$accno_id')
 853                  ORDER BY accno|;
 854       $stw = $dbh->prepare($query);
 855       $stw->execute || $form->dberror($query);
 856       $ref->{taxaccounts} = "";
 857       while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
 858
 859         #    if ($customertax{$ref->{accno}}) {
 860         $ref->{taxaccounts} .= "$ptr->{accno} ";
 861         if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
 862           $form->{"$ptr->{accno}_rate"}        = $ptr->{rate};
 863           $form->{"$ptr->{accno}_description"} = $ptr->{description};
 864           $form->{"$ptr->{accno}_taxnumber"}   = $ptr->{taxnumber};
 865           $form->{taxaccounts} .= "$ptr->{accno} ";
 866         }
 867
 868       }
 869
 870       chop $ref->{taxaccounts};
 871       push @{ $form->{invoice_details} }, $ref;
 872       $stw->finish;
 873     }
 874     $sth->finish;
 875
 876     if ($form->{webdav}) {
 877       &webdav_folder($myconfig, $form);
 878     }
 879
 880   }
 881
 882   my $rc = $dbh->commit;
 883   $dbh->disconnect;
 884
 885   $main::lxdebug->leave_sub();
 886
 887   return $rc;
 888 }
 889
 890 sub get_vendor {
 891   $main::lxdebug->enter_sub();
 892
 893   my ($self, $myconfig, $form) = @_;
 894
 895   # connect to database
 896   my $dbh = $form->dbconnect($myconfig);
 897
 898   my $dateformat = $myconfig->{dateformat};
 899   $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
 900
 901   my $duedate =
 902     ($form->{invdate})
 903     ? "to_date('$form->{invdate}', '$dateformat')"
 904     : "current_date";
 905
 906   $form->{vendor_id} *= 1;
 907
 908   # get vendor
 909   my $query = qq|SELECT v.name AS vendor, v.creditlimit, v.terms,
 910                  v.email, v.cc, v.bcc, v.language,
 911                  v.street, v.zipcode, v.city, v.country,
 912                  $duedate + v.terms AS duedate, v.notes AS intnotes
 913                  FROM vendor v
 914                  WHERE v.id = $form->{vendor_id}|;
 915   my $sth = $dbh->prepare($query);
 916   $sth->execute || $form->dberror($query);
 917
 918   $ref = $sth->fetchrow_hashref(NAME_lc);
 919   map { $form->{$_} = $ref->{$_} } keys %$ref;
 920   $sth->finish;
 921
 922   $form->{creditremaining} = $form->{creditlimit};
 923   $query = qq|SELECT SUM(a.amount - a.paid)
 924               FROM ap a
 925               WHERE a.vendor_id = $form->{vendor_id}|;
 926   $sth = $dbh->prepare($query);
 927   $sth->execute || $form->dberror($query);
 928
 929   ($form->{creditremaining}) -= $sth->fetchrow_array;
 930
 931   $sth->finish;
 932
 933   $query = qq|SELECT o.amount,
 934                 (SELECT e.sell FROM exchangerate e
 935                  WHERE e.curr = o.curr
 936                  AND e.transdate = o.transdate)
 937               FROM oe o
 938               WHERE o.vendor_id = $form->{vendor_id}
 939               AND o.quotation = '0'
 940               AND o.closed = '0'|;
 941   $sth = $dbh->prepare($query);
 942   $sth->execute || $form->dberror($query);
 943
 944   while (my ($amount, $exch) = $sth->fetchrow_array) {
 945     $exch = 1 unless $exch;
 946     $form->{creditremaining} -= $amount * $exch;
 947   }
 948   $sth->finish;
 949
 950   $form->get_contacts($dbh, $form->{vendor_id});
 951
 952   ($null, $form->{cp_id}) = split /--/, $form->{contact};
 953
 954   # get contact if selected
 955   if ($form->{contact} ne "--" && $form->{contact} ne "") {
 956     $form->get_contact($dbh, $form->{cp_id});
 957   }
 958
 959   # get shipto if we do not convert an order or invoice
 960   if (!$form->{shipto}) {
 961     map { delete $form->{$_} }
 962       qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail);
 963
 964     $query = qq|SELECT s.* FROM shipto s
 965                 WHERE s.trans_id = $form->{vendor_id}|;
 966     $sth = $dbh->prepare($query);
 967     $sth->execute || $form->dberror($query);
 968
 969     $ref = $sth->fetchrow_hashref(NAME_lc);
 970     map { $form->{$_} = $ref->{$_} } keys %$ref;
 971     $sth->finish;
 972   }
 973
 974   # get taxes for vendor
 975   $query = qq|SELECT c.accno
 976               FROM chart c
 977               JOIN vendortax v ON (v.chart_id = c.id)
 978               WHERE v.vendor_id = $form->{vendor_id}|;
 979   $sth = $dbh->prepare($query);
 980   $sth->execute || $form->dberror($query);
 981
 982   my $vendortax = ();
 983   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
 984     $vendortax{ $ref->{accno} } = 1;
 985   }
 986   $sth->finish;
 987
 988   if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
 989
 990     # setup last accounts used
 991     $query = qq|SELECT c.accno, c.description, c.link, c.category
 992                 FROM chart c
 993                 JOIN acc_trans ac ON (ac.chart_id = c.id)
 994                 JOIN ap a ON (a.id = ac.trans_id)
 995                 WHERE a.vendor_id = $form->{vendor_id}
 996                 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
 997                 AND a.id IN (SELECT max(a2.id) FROM ap a2
 998                              WHERE a2.vendor_id = $form->{vendor_id})|;
 999     $sth = $dbh->prepare($query);
1000     $sth->execute || $form->dberror($query);
1001
1002     my $i = 0;
1003     while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1004       if ($ref->{category} eq 'E') {
1005         $i++;
1006         $form->{"AP_amount_$i"} = "$ref->{accno}--$ref->{description}";
1007       }
1008       if ($ref->{category} eq 'L') {
1009         $form->{APselected} = $form->{AP_1} =
1010           "$ref->{accno}--$ref->{description}";
1011       }
1012     }
1013     $sth->finish;
1014     $form->{rowcount} = $i if ($i && !$form->{type});
1015   }
1016
1017   $dbh->disconnect;
1018
1019   $main::lxdebug->leave_sub();
1020 }
1021
1022 sub retrieve_item {
1023   $main::lxdebug->enter_sub();
1024
1025   my ($self, $myconfig, $form) = @_;
1026
1027   my $i = $form->{rowcount};
1028
1029   # don't include assemblies or obsolete parts
1030   my $where = "NOT p.assembly = '1' AND NOT p.obsolete = '1'";
1031
1032   if ($form->{"partnumber_$i"}) {
1033     my $partnumber = $form->like(lc $form->{"partnumber_$i"});
1034     $where .= " AND lower(p.partnumber) LIKE '$partnumber'";
1035   }
1036
1037   if ($form->{"description_$i"}) {
1038     my $description = $form->like(lc $form->{"description_$i"});
1039     $where .= " AND lower(p.description) LIKE '$description'";
1040   }
1041
1042   if ($form->{"partsgroup_$i"}) {
1043     my $partsgroup = $form->like(lc $form->{"partsgroup_$i"});
1044     $where .= " AND lower(pg.partsgroup) LIKE '$partsgroup'";
1045   }
1046
1047   if ($form->{"description_$i"}) {
1048     $where .= " ORDER BY p.description";
1049   } else {
1050     $where .= " ORDER BY p.partnumber";
1051   }
1052
1053   # connect to database
1054   my $dbh = $form->dbconnect($myconfig);
1055
1056   my $query = qq|SELECT p.id, p.partnumber, p.description,
1057                  c1.accno AS inventory_accno,
1058                  c2.accno AS income_accno,
1059                  c3.accno AS expense_accno,
1060                  pg.partsgroup,
1061                  p.lastcost AS sellprice, p.unit, p.bin, p.onhand, p.notes AS partnotes
1062                  FROM parts p
1063                  LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
1064                  LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
1065                  LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
1066                  LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1067                  WHERE $where|;
1068   my $sth = $dbh->prepare($query);
1069   $sth->execute || $form->dberror($query);
1070
1071   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1072
1073     #set expense_accno=inventory_accno if they are different => bilanz
1074     $vendor_accno =
1075       ($ref->{expense_accno} != $ref->{inventory_accno})
1076       ? $ref->{inventory_accno}
1077       : $ref->{expense_accno};
1078     $vendor_accno =
1079       ($ref->{inventory_accno})
1080       ? $ref->{inventory_accno}
1081       : $ref->{expense_accno};
1082
1083     # get tax rates and description
1084     $accno_id =
1085       ($form->{vc} eq "customer") ? $ref->{income_accno} : $vendor_accno;
1086     $query = qq|SELECT c.accno, c.description, t.rate, t.taxnumber
1087               FROM chart c, tax t
1088               WHERE c.id=t.chart_id AND t.taxkey in (SELECT taxkey_id from chart where accno = '$accno_id')
1089               ORDER BY c.accno|;
1090     $stw = $dbh->prepare($query);
1091     $stw->execute || $form->dberror($query);
1092
1093     $ref->{taxaccounts} = "";
1094     while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1095
1096       #    if ($customertax{$ref->{accno}}) {
1097       $ref->{taxaccounts} .= "$ptr->{accno} ";
1098       if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
1099         $form->{"$ptr->{accno}_rate"}        = $ptr->{rate};
1100         $form->{"$ptr->{accno}_description"} = $ptr->{description};
1101         $form->{"$ptr->{accno}_taxnumber"}   = $ptr->{taxnumber};
1102         $form->{taxaccounts} .= "$ptr->{accno} ";
1103       }
1104
1105     }
1106
1107     $stw->finish;
1108     chop $ref->{taxaccounts};
1109
1110     push @{ $form->{item_list} }, $ref;
1111
1112   }
1113
1114   $sth->finish;
1115   $dbh->disconnect;
1116
1117   $main::lxdebug->leave_sub();
1118 }
1119
1120 sub vendor_details {
1121   $main::lxdebug->enter_sub();
1122
1123   my ($self, $myconfig, $form) = @_;
1124
1125   # connect to database
1126   my $dbh = $form->dbconnect($myconfig);
1127
1128   # get contact id, set it if nessessary
1129   ($null, $form->{cp_id}) = split /--/, $form->{contact};
1130
1131   $contact = "";
1132   if ($form->{cp_id}) {
1133     $contact = "and cp.cp_id = $form->{cp_id}";
1134   }
1135
1136   # get rest for the vendor
1137   # fax and phone and email as vendor*
1138   my $query =
1139     qq|SELECT ct.*, cp.*, ct.notes as vendornotes, phone as vendorphone, fax as vendorfax, email as vendoremail
1140                  FROM vendor ct
1141                  LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
1142                  WHERE ct.id = $form->{vendor_id}  $contact order by cp.cp_id limit 1|;
1143   my $sth = $dbh->prepare($query);
1144   $sth->execute || $form->dberror($query);
1145
1146   $ref = $sth->fetchrow_hashref(NAME_lc);
1147
1148   # remove id and taxincluded before copy back
1149   delete @$ref{qw(id taxincluded)};
1150   map { $form->{$_} = $ref->{$_} } keys %$ref;
1151
1152   $sth->finish;
1153   $dbh->disconnect;
1154
1155   $main::lxdebug->leave_sub();
1156 }
1157
1158 sub item_links {
1159   $main::lxdebug->enter_sub();
1160
1161   my ($self, $myconfig, $form) = @_;
1162
1163   # connect to database
1164   my $dbh = $form->dbconnect($myconfig);
1165
1166   my $query = qq|SELECT c.accno, c.description, c.link
1167                  FROM chart c
1168                  WHERE c.link LIKE '%IC%'
1169                  ORDER BY c.accno|;
1170   my $sth = $dbh->prepare($query);
1171   $sth->execute || $form->dberror($query);
1172
1173   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1174     foreach my $key (split(/:/, $ref->{link})) {
1175       if ($key =~ /IC/) {
1176         push @{ $form->{IC_links}{$key} },
1177           { accno       => $ref->{accno},
1178             description => $ref->{description} };
1179       }
1180     }
1181   }
1182
1183   $sth->finish;
1184   $main::lxdebug->leave_sub();
1185 }
1186
1187 sub webdav_folder {
1188   $main::lxdebug->enter_sub();
1189
1190   my ($myconfig, $form) = @_;
1191
1192 SWITCH: {
1193     $path = "webdav/rechnungen/" . $form->{invnumber}, last SWITCH
1194       if ($form->{vc} eq "customer");
1195     $path = "webdav/einkaufsrechnungen/" . $form->{invnumber}, last SWITCH
1196       if ($form->{vc} eq "vendor");
1197   }
1198
1199   if (!-d $path) {
1200     mkdir($path, 0770) or die "can't make directory $!\n";
1201   } else {
1202     if ($form->{id}) {
1203       @files = <$path/*>;
1204       foreach $file (@files) {
1205
1206         $file =~ /\/([^\/]*)$/;
1207         $fname = $1;
1208         $ENV{'SCRIPT_NAME'} =~ /\/([^\/]*)\//;
1209         $lxerp = $1;
1210         $link  = "http://" . $ENV{'SERVER_NAME'} . "/" . $lxerp . "/" . $file;
1211         $form->{WEBDAV}{$fname} = $link;
1212       }
1213     }
1214   }
1215
1216   $main::lxdebug->leave_sub();
1217 }
1218
1219 1;