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