sql-ledger/SL/IR.pm

   1 #=====================================================================
   2 # SQL-Ledger Accounting
   3 # Copyright (C) 2001
   4 #
   5 #  Author: Dieter Simader
   6 #   Email: dsimader@sql-ledger.org
   7 #     Web: http://www.sql-ledger.org
   8 #
   9 #  Contributors:
  10 #
  11 # This program is free software; you can redistribute it and/or modify
  12 # it under the terms of the GNU General Public License as published by
  13 # the Free Software Foundation; either version 2 of the License, or
  14 # (at your option) any later version.
  15 #
  16 # This program is distributed in the hope that it will be useful,
  17 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  18 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  19 # GNU General Public License for more details.
  20 # You should have received a copy of the GNU General Public License
  21 # along with this program; if not, write to the Free Software
  22 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
  23 #======================================================================
  24 #
  25 # Inventory received module
  26 #
  27 #======================================================================
  28
  29 package IR;
  30
  31
  32 sub post_invoice {
  33   my ($self, $myconfig, $form) = @_;
  34
  35   # connect to database, turn off autocommit
  36   my $dbh = $form->dbconnect_noauto($myconfig);
  37
  38   my ($query, $sth, $null, $project_id);
  39   my $exchangerate = 0;
  40
  41   if ($form->{id}) {
  42
  43     &reverse_invoice($dbh, $form);
  44
  45   } else {
  46     my $uid = time;
  47     $uid .= $form->{login};
  48
  49     $query = qq|INSERT INTO ap (invnumber, employee_id)
  50                 VALUES ('$uid', (SELECT id FROM employee
  51                                  WHERE login = '$form->{login}'))|;
  52     $dbh->do($query) || $form->dberror($query);
  53
  54     $query = qq|SELECT id FROM ap
  55                 WHERE invnumber = '$uid'|;
  56     $sth = $dbh->prepare($query);
  57     $sth->execute || $form->dberror($query);
  58
  59     ($form->{id}) = $sth->fetchrow_array;
  60     $sth->finish;
  61   }
  62
  63   map { $form->{$_} =~ s/'/''/g } qw(invnumber ordnumber);
  64
  65   my ($amount, $linetotal, $lastinventoryaccno, $lastexpenseaccno);
  66   my ($netamount, $invoicediff, $expensediff) = (0, 0, 0);
  67
  68   if ($form->{currency} eq $form->{defaultcurrency}) {
  69     $form->{exchangerate} = 1;
  70   } else {
  71     $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'sell');
  72   }
  73
  74   $form->{exchangerate} = ($exchangerate) ? $exchangerate : $form->parse_amount($myconfig, $form->{exchangerate});
  75
  76
  77   for my $i (1 .. $form->{rowcount}) {
  78     $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
  79
  80     if ($form->{"qty_$i"} != 0) {
  81
  82       map { $form->{"${_}_$i"} =~ s/'/''/g } qw(partnumber description unit);
  83
  84       my ($allocated, $taxrate) = (0, 0);
  85       my $taxamount;
  86
  87       $form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
  88       my $fxsellprice = $form->{"sellprice_$i"};
  89
  90       my ($dec) = ($fxsellprice =~ /\.(\d+)/);
  91       $dec = length $dec;
  92       my $decimalplaces = ($dec > 2) ? $dec : 2;
  93
  94
  95       map { $taxrate += $form->{"${_}_rate"} } split / /, $form->{"taxaccounts_$i"};
  96
  97       if ($form->{"inventory_accno_$i"}) {
  98
  99         $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
 100
 101         if ($form->{taxincluded}) {
 102           $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
 103           $form->{"sellprice_$i"} = $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
 104         } else {
 105           $taxamount = $linetotal * $taxrate;
 106         }
 107
 108         $netamount += $linetotal;
 109
 110         if ($taxamount != 0) {
 111           map { $form->{amount}{$form->{id}}{$_} -= $taxamount * $form->{"${_}_rate"} / $taxrate } split / /, $form->{"taxaccounts_$i"};
 112         }
 113
 114         # add purchase to inventory, this one is without the tax!
 115         $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
 116         $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) * $form->{exchangerate};
 117         $linetotal = $form->round_amount($linetotal, 2);
 118
 119         # this is the difference for the inventory
 120         $invoicediff += ($amount - $linetotal);
 121
 122         $form->{amount}{$form->{id}}{$form->{"inventory_accno_$i"}} -= $linetotal;
 123
 124         # adjust and round sellprice
 125         $form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate}, $decimalplaces);
 126
 127
 128         # update parts table
 129         $query = qq|UPDATE parts SET
 130                     lastcost = $form->{"sellprice_$i"},
 131                     onhand = onhand + $form->{"qty_$i"}
 132                     WHERE id = $form->{"id_$i"}|;
 133         $dbh->do($query) || $form->dberror($query);
 134
 135
 136         # check if we sold the item already and
 137         # make an entry for the expense and inventory
 138         $query = qq|SELECT i.id, i.qty, i.allocated, i.trans_id,
 139                     p.inventory_accno_id, p.expense_accno_id, a.transdate
 140                     FROM invoice i, ar a, parts p
 141                     WHERE i.parts_id = p.id
 142                     AND i.parts_id = $form->{"id_$i"}
 143                     AND (i.qty + i.allocated) > 0
 144                     AND i.trans_id = a.id
 145                     ORDER BY transdate|;
 146         $sth = $dbh->prepare($query);
 147         $sth->execute || $form->dberror($query);
 148
 149
 150         my $totalqty = $form->{"qty_$i"};
 151
 152         while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 153
 154           my $qty = $ref->{qty} + $ref->{allocated};
 155
 156           if (($qty - $totalqty) > 0) {
 157             $qty = $totalqty;
 158           }
 159
 160
 161           $linetotal = $form->round_amount($form->{"sellprice_$i"} * $qty, 2);
 162
 163           if ($ref->{allocated} < 0) {
 164             # we have an entry for it already, adjust amount
 165             $form->update_balance($dbh,
 166                                   "acc_trans",
 167                                   "amount",
 168                                   qq|trans_id = $ref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$ref->{transdate}'|,
 169                                   $linetotal);
 170
 171             $form->update_balance($dbh,
 172                                   "acc_trans",
 173                                   "amount",
 174                                   qq|trans_id = $ref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$ref->{transdate}'|,
 175                                   $linetotal * -1);
 176
 177           } else {
 178             # add entry for inventory, this one is for the sold item
 179             if ($linetotal != 0) {
 180               $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 181                           transdate)
 182                           VALUES ($ref->{trans_id}, $ref->{inventory_accno_id},
 183                           $linetotal, '$ref->{transdate}')|;
 184               $dbh->do($query) || $form->dberror($query);
 185
 186               # add expense
 187               $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 188                           transdate)
 189                           VALUES ($ref->{trans_id}, $ref->{expense_accno_id},
 190                           |. ($linetotal * -1) .qq|, '$ref->{transdate}')|;
 191               $dbh->do($query) || $form->dberror($query);
 192             }
 193           }
 194
 195           # update allocated for sold item
 196           $form->update_balance($dbh,
 197                                 "invoice",
 198                                 "allocated",
 199                                 qq|id = $ref->{id}|,
 200                                 $qty * -1);
 201
 202           $allocated += $qty;
 203
 204           last if (($totalqty -= $qty) <= 0);
 205         }
 206
 207         $sth->finish;
 208
 209         $lastinventoryaccno = $form->{"inventory_accno_$i"};
 210
 211       } else {
 212
 213         $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
 214
 215         if ($form->{taxincluded}) {
 216           $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
 217
 218           $form->{"sellprice_$i"} = $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
 219         } else {
 220           $taxamount = $linetotal * $taxrate;
 221         }
 222
 223         $netamount += $linetotal;
 224
 225         if ($taxamount != 0) {
 226           map { $form->{amount}{$form->{id}}{$_} -= $taxamount * $form->{"${_}_rate"} / $taxrate } split / /, $form->{"taxaccounts_$i"};
 227         }
 228
 229         $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
 230         $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) * $form->{exchangerate};
 231         $linetotal = $form->round_amount($linetotal, 2);
 232
 233         # this is the difference for expense
 234         $expensediff += ($amount - $linetotal);
 235
 236         # add amount to expense
 237         $form->{amount}{$form->{id}}{$form->{"expense_accno_$i"}} -= $linetotal;
 238
 239         $lastexpenseaccno = $form->{"expense_accno_$i"};
 240
 241         # adjust and round sellprice
 242         $form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate}, $decimalplaces);
 243
 244         # update lastcost
 245         $query = qq|UPDATE parts SET
 246                     lastcost = $form->{"sellprice_$i"}
 247                     WHERE id = $form->{"id_$i"}|;
 248         $dbh->do($query) || $form->dberror($query);
 249
 250       }
 251
 252       $project_id = 'NULL';
 253       if ($form->{"project_id_$i"}) {
 254         $project_id = $form->{"project_id_$i"};
 255       }
 256       $deliverydate = ($form->{"deliverydate_$i"}) ? qq|'$form->{"deliverydate_$i"}'| : "NULL";
 257
 258       # save detail record in invoice table
 259       $query = qq|INSERT INTO invoice (trans_id, parts_id, description, qty,
 260                   sellprice, fxsellprice, allocated, unit, deliverydate)
 261                   VALUES ($form->{id}, $form->{"id_$i"},
 262                   '$form->{"description_$i"}', |. ($form->{"qty_$i"} * -1) .qq|,
 263                   $form->{"sellprice_$i"}, $fxsellprice, $allocated,
 264                   '$form->{"unit_$i"}', $deliverydate)|;
 265       $dbh->do($query) || $form->dberror($query);
 266
 267     }
 268   }
 269
 270
 271   $form->{datepaid} = $form->{invdate};
 272
 273   # all amounts are in natural state, netamount includes the taxes
 274   # if tax is included, netamount is rounded to 2 decimal places,
 275   # taxes are not
 276
 277   # total payments
 278   for my $i (1 .. $form->{paidaccounts}) {
 279     $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
 280     $form->{paid} += $form->{"paid_$i"};
 281     $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
 282   }
 283
 284   my ($tax, $paiddiff) = (0, 0);
 285
 286   $netamount = $form->round_amount($netamount, 2);
 287
 288   # figure out rounding errors for amount paid and total amount
 289   if ($form->{taxincluded}) {
 290
 291     $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
 292     $paiddiff = $amount - $netamount * $form->{exchangerate};
 293     $netamount = $amount;
 294
 295     foreach my $item (split / /, $form->{taxaccounts}) {
 296       $amount = $form->{amount}{$form->{id}}{$item} * $form->{exchangerate};
 297       $form->{amount}{$form->{id}}{$item} = $form->round_amount($amount, 2);
 298       $amount = $form->{amount}{$form->{id}}{$item} * -1;
 299       $tax += $amount;
 300       $netamount -= $amount;
 301     }
 302
 303     $invoicediff += $paiddiff;
 304     $expensediff += $paiddiff;
 305
 306     ######## this only applies to tax included
 307     if ($lastinventoryaccno) {
 308       $form->{amount}{$form->{id}}{$lastinventoryaccno} -= $invoicediff;
 309     }
 310     if ($lastexpenseaccno) {
 311       $form->{amount}{$form->{id}}{$lastexpenseaccno} -= $expensediff;
 312     }
 313
 314   } else {
 315     $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
 316     $paiddiff = $amount - $netamount * $form->{exchangerate};
 317     $netamount = $amount;
 318     foreach my $item (split / /, $form->{taxaccounts}) {
 319       $form->{amount}{$form->{id}}{$item} = $form->round_amount($form->{amount}{$form->{id}}{$item}, 2);
 320       $amount = $form->round_amount($form->{amount}{$form->{id}}{$item} * $form->{exchangerate} * -1, 2);
 321       $paiddiff += $amount - $form->{amount}{$form->{id}}{$item} * $form->{exchangerate} * -1;
 322       $form->{amount}{$form->{id}}{$item} = $form->round_amount($amount * -1, 2);
 323       $amount = $form->{amount}{$form->{id}}{$item} * -1;
 324       $tax += $amount;
 325     }
 326   }
 327
 328
 329   $form->{amount}{$form->{id}}{$form->{AP}} = $netamount + $tax;
 330
 331   if ($form->{paid} != 0) {
 332     $form->{paid} = $form->round_amount($form->{paid} * $form->{exchangerate} + $paiddiff, 2);
 333   }
 334
 335
 336   # update exchangerate
 337   if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
 338     $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, 0, $form->{exchangerate});
 339   }
 340
 341   # record acc_trans transactions
 342   foreach my $trans_id (keys %{$form->{amount}}) {
 343     foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
 344       if (($form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2)) != 0) {
 345         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 346                     transdate)
 347                     VALUES ($trans_id, (SELECT id FROM chart
 348                                          WHERE accno = '$accno'),
 349                     $form->{amount}{$trans_id}{$accno}, '$form->{invdate}')|;
 350         $dbh->do($query) || $form->dberror($query);
 351       }
 352     }
 353   }
 354
 355   # deduct payment differences from paiddiff
 356   for my $i (1 .. $form->{paidaccounts}) {
 357     if ($form->{"paid_$i"} != 0) {
 358       $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
 359       $paiddiff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
 360     }
 361   }
 362
 363   # force AP entry if 0
 364   $form->{amount}{$form->{id}}{$form->{AP}} = $form->{paid} if ($form->{amount}{$form->{id}}{$form->{AP}} == 0);
 365
 366   # record payments and offsetting AP
 367   for my $i (1 .. $form->{paidaccounts}) {
 368
 369     if ($form->{"paid_$i"} != 0) {
 370       my ($accno) = split /--/, $form->{"AP_paid_$i"};
 371       $form->{"datepaid_$i"} = $form->{invdate} unless ($form->{"datepaid_$i"});
 372       $form->{datepaid} = $form->{"datepaid_$i"};
 373
 374       $amount = ($form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $paiddiff, 2)) * -1;
 375
 376       # record AP
 377
 378       if ($form->{amount}{$form->{id}}{$form->{AP}} != 0) {
 379         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 380                     transdate)
 381                     VALUES ($form->{id}, (SELECT id FROM chart
 382                                         WHERE accno = '$form->{AP}'),
 383                     $amount, '$form->{"datepaid_$i"}')|;
 384         $dbh->do($query) || $form->dberror($query);
 385       }
 386
 387       # record payment
 388
 389       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
 390                   source)
 391                   VALUES ($form->{id}, (SELECT id FROM chart
 392                                       WHERE accno = '$accno'),
 393                   $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
 394                   '$form->{"source_$i"}')|;
 395       $dbh->do($query) || $form->dberror($query);
 396
 397
 398       $exchangerate = 0;
 399
 400       if ($form->{currency} eq $form->{defaultcurrency}) {
 401         $form->{"exchangerate_$i"} = 1;
 402       } else {
 403         $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'sell');
 404
 405         $form->{"exchangerate_$i"} = ($exchangerate) ? $exchangerate : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
 406       }
 407
 408
 409       # exchangerate difference
 410       $form->{fx}{$accno}{$form->{"datepaid_$i"}} += $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $paiddiff;
 411
 412
 413       # gain/loss
 414       $amount = ($form->{"paid_$i"} * $form->{exchangerate}) - ($form->{"paid_$i"} * $form->{"exchangerate_$i"});
 415       if ($amount > 0) {
 416         $form->{fx}{$form->{fxgain_accno}}{$form->{"datepaid_$i"}} += $amount;
 417       } else {
 418         $form->{fx}{$form->{fxloss_accno}}{$form->{"datepaid_$i"}} += $amount;
 419       }
 420
 421       $paiddiff = 0;
 422
 423       # update exchange rate
 424       if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
 425         $form->update_exchangerate($dbh, $form->{currency}, $form->{"datepaid_$i"}, 0, $form->{"exchangerate_$i"});
 426       }
 427     }
 428   }
 429
 430   # record exchange rate differences and gains/losses
 431   foreach my $accno (keys %{$form->{fx}}) {
 432     foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
 433       if (($form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2)) != 0) {
 434
 435         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 436                     transdate, cleared, fx_transaction)
 437                     VALUES ($form->{id}, (SELECT id FROM chart
 438                                         WHERE accno = '$accno'),
 439                     $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1')|;
 440         $dbh->do($query) || $form->dberror($query);
 441       }
 442     }
 443   }
 444
 445
 446   $amount = $netamount + $tax;
 447
 448   # set values which could be empty
 449   $form->{taxincluded} *= 1;
 450   my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
 451   my $duedate = ($form->{duedate}) ? qq|'$form->{duedate}'| : "NULL";
 452
 453   # save AP record
 454   $query = qq|UPDATE ap set
 455               invnumber = '$form->{invnumber}',
 456               ordnumber = '$form->{ordnumber}',
 457               transdate = '$form->{invdate}',
 458               vendor_id = $form->{vendor_id},
 459               amount = $amount,
 460               netamount = $netamount,
 461               paid = $form->{paid},
 462               datepaid = $datepaid,
 463               duedate = $duedate,
 464               invoice = '1',
 465               taxincluded = '$form->{taxincluded}',
 466               notes = '$form->{notes}',
 467               curr = '$form->{currency}'
 468               WHERE id = $form->{id}|;
 469   $dbh->do($query) || $form->dberror($query);
 470
 471   # add shipto
 472   $form->{name} = $form->{vendor};
 473   $form->{name} =~ s/--$form->{vendor_id}//;
 474   $form->add_shipto($dbh, $form->{id});
 475
 476   # delete zero entries
 477   $query = qq|DELETE FROM acc_trans
 478               WHERE amount = 0|;
 479   $dbh->do($query) || $form->dberror($query);
 480
 481   my $rc = $dbh->commit;
 482   $dbh->disconnect;
 483   $rc;
 484
 485 }
 486
 487
 488
 489 sub reverse_invoice {
 490   my ($dbh, $form) = @_;
 491
 492   # reverse inventory items
 493   my $query = qq|SELECT i.parts_id, p.inventory_accno_id, p.expense_accno_id,
 494                  i.qty, i.allocated, i.sellprice
 495                  FROM invoice i, parts p
 496                  WHERE i.parts_id = p.id
 497                  AND i.trans_id = $form->{id}|;
 498   my $sth = $dbh->prepare($query);
 499   $sth->execute || $form->dberror($query);
 500
 501   my $netamount = 0;
 502
 503   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 504     $netamount += $form->round_amount($ref->{sellprice} * $ref->{qty} * -1, 2);
 505
 506     if ($ref->{inventory_accno_id}) {
 507       # update onhand
 508       $form->update_balance($dbh,
 509                             "parts",
 510                             "onhand",
 511                             qq|id = $ref->{parts_id}|,
 512                             $ref->{qty});
 513
 514       # if $ref->{allocated} > 0 than we sold that many items
 515       if ($ref->{allocated} > 0) {
 516
 517         # get references for sold items
 518         $query = qq|SELECT i.id, i.trans_id, i.allocated, a.transdate
 519                     FROM invoice i, ar a
 520                     WHERE i.parts_id = $ref->{parts_id}
 521                     AND i.allocated < 0
 522                     AND i.trans_id = a.id
 523                     ORDER BY transdate DESC|;
 524         my $sth = $dbh->prepare($query);
 525         $sth->execute || $form->dberror($query);
 526
 527         while (my $pthref = $sth->fetchrow_hashref(NAME_lc)) {
 528           my $qty = $ref->{allocated};
 529           if (($ref->{allocated} + $pthref->{allocated}) > 0) {
 530             $qty = $pthref->{allocated} * -1;
 531           }
 532
 533           my $amount = $form->round_amount($ref->{sellprice} * $qty, 2);
 534
 535           #adjust allocated
 536           $form->update_balance($dbh,
 537                                 "invoice",
 538                                 "allocated",
 539                                 qq|id = $pthref->{id}|,
 540                                 $qty);
 541
 542           $form->update_balance($dbh,
 543                                 "acc_trans",
 544                                 "amount",
 545                                 qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$pthref->{transdate}'|,
 546                                 $amount);
 547
 548           $form->update_balance($dbh,
 549                                 "acc_trans",
 550                                 "amount",
 551                                 qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$pthref->{transdate}'|,
 552                                 $amount * -1);
 553
 554           last if (($ref->{allocated} -= $qty) <= 0);
 555         }
 556         $sth->finish;
 557       }
 558     }
 559   }
 560   $sth->finish;
 561
 562   # delete acc_trans
 563   $query = qq|DELETE FROM acc_trans
 564               WHERE trans_id = $form->{id}|;
 565   $dbh->do($query) || $form->dberror($query);
 566
 567   # delete invoice entries
 568   $query = qq|DELETE FROM invoice
 569               WHERE trans_id = $form->{id}|;
 570   $dbh->do($query) || $form->dberror($query);
 571
 572   $query = qq|DELETE FROM shipto
 573               WHERE trans_id = $form->{id}|;
 574   $dbh->do($query) || $form->dberror($query);
 575
 576 }
 577
 578
 579
 580 sub delete_invoice {
 581   my ($self, $myconfig, $form) = @_;
 582
 583   # connect to database
 584   my $dbh = $form->dbconnect_noauto($myconfig);
 585
 586   # check for other foreign currency transactions
 587   $form->delete_exchangerate($dbh) if ($form->{currency} ne $form->{defaultcurrency});
 588
 589   &reverse_invoice($dbh, $form);
 590
 591   # delete zero entries
 592   my $query = qq|DELETE FROM acc_trans
 593                  WHERE amount = 0|;
 594   $dbh->do($query) || $form->dberror($query);
 595
 596   # delete AP record
 597   my $query = qq|DELETE FROM ap
 598                  WHERE id = $form->{id}|;
 599   $dbh->do($query) || $form->dberror($query);
 600
 601   my $rc = $dbh->commit;
 602   $dbh->disconnect;
 603
 604   $rc;
 605
 606 }
 607
 608
 609
 610 sub retrieve_invoice {
 611   my ($self, $myconfig, $form) = @_;
 612
 613   # connect to database
 614   my $dbh = $form->dbconnect_noauto($myconfig);
 615
 616   my $query;
 617
 618   if ($form->{id}) {
 619     # get default accounts and last invoice number
 620     $query = qq|SELECT (SELECT c.accno FROM chart c
 621                         WHERE d.inventory_accno_id = c.id) AS inventory_accno,
 622                        (SELECT c.accno FROM chart c
 623                         WHERE d.income_accno_id = c.id) AS income_accno,
 624                        (SELECT c.accno FROM chart c
 625                         WHERE d.expense_accno_id = c.id) AS expense_accno,
 626                        (SELECT c.accno FROM chart c
 627                         WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
 628                        (SELECT c.accno FROM chart c
 629                         WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
 630                 d.curr AS currencies
 631                 FROM defaults d|;
 632   } else {
 633     $query = qq|SELECT (SELECT c.accno FROM chart c
 634                         WHERE d.inventory_accno_id = c.id) AS inventory_accno,
 635                        (SELECT c.accno FROM chart c
 636                         WHERE d.income_accno_id = c.id) AS income_accno,
 637                        (SELECT c.accno FROM chart c
 638                         WHERE d.expense_accno_id = c.id) AS expense_accno,
 639                        (SELECT c.accno FROM chart c
 640                         WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
 641                        (SELECT c.accno FROM chart c
 642                         WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
 643                 d.ponumber AS invnumber, d.curr AS currencies,
 644                 current_date AS invdate
 645                 FROM defaults d|;
 646   }
 647   my $sth = $dbh->prepare($query);
 648   $sth->execute || $form->dberror($query);
 649
 650   my $ref = $sth->fetchrow_hashref(NAME_lc);
 651   map { $form->{$_} = $ref->{$_} } keys %$ref;
 652   $sth->finish;
 653
 654
 655   if ($form->{id}) {
 656
 657     # retrieve invoice
 658     $query = qq|SELECT a.invnumber, a.transdate AS invdate, a.duedate,
 659                 a.ordnumber, a.paid, a.taxincluded, a.notes, a.curr AS currency
 660                 FROM ap a
 661                 WHERE id = $form->{id}|;
 662     $sth = $dbh->prepare($query);
 663     $sth->execute || $form->dberror($query);
 664
 665     $ref = $sth->fetchrow_hashref(NAME_lc);
 666     map { $form->{$_} = $ref->{$_} } keys %$ref;
 667     $sth->finish;
 668
 669     $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "sell");
 670
 671     # get shipto
 672     $query = qq|SELECT * FROM shipto
 673                 WHERE trans_id = $form->{id}|;
 674     $sth = $dbh->prepare($query);
 675     $sth->execute || $form->dberror($query);
 676
 677     $ref = $sth->fetchrow_hashref(NAME_lc);
 678     map { $form->{$_} = $ref->{$_} } keys %$ref;
 679     $sth->finish;
 680
 681     # retrieve individual items
 682     $query = qq|SELECT c1.accno AS inventory_accno,
 683                        c2.accno AS income_accno,
 684                        c3.accno AS expense_accno,
 685                 p.partnumber, i.description, i.qty, i.fxsellprice AS sellprice,
 686                 i.parts_id AS id, i.unit, p.bin, i.deliverydate,
 687                 pr.projectnumber,
 688                 i.project_id,
 689                 pg.partsgroup
 690                 FROM invoice i
 691                 JOIN parts p ON (i.parts_id = p.id)
 692                 LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
 693                 LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
 694                 LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
 695                 LEFT JOIN project pr ON (i.project_id = pr.id)
 696                 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
 697                 WHERE trans_id = $form->{id}
 698                 ORDER BY i.id|;
 699     $sth = $dbh->prepare($query);
 700     $sth->execute || $form->dberror($query);
 701
 702     while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
 703
 704       # get tax rates for part
 705       $query = qq|SELECT c.accno
 706                   FROM chart c, partstax pt
 707                   WHERE pt.chart_id = c.id
 708                   AND pt.parts_id = $ref->{id}|;
 709       my $sth = $dbh->prepare($query);
 710       $sth->execute || $form->dberror($query);
 711
 712       $ref->{taxaccounts} = "";
 713       my $taxrate = 0;
 714
 715       while (my $ptref = $sth->fetchrow_hashref(NAME_lc)) {
 716         $ref->{taxaccounts} .= "$ptref->{accno} ";
 717         $taxrate += $form->{"$ptref->{accno}_rate"};
 718       }
 719
 720       $sth->finish;
 721       chop $ref->{taxaccounts};
 722
 723       $ref->{qty} *= -1;
 724
 725       push @{ $form->{invoice_details} }, $ref;
 726
 727     }
 728
 729     $sth->finish;
 730
 731   } else {
 732
 733     # up invoice number by 1
 734     $form->{invnumber}++;
 735
 736     # save the new number
 737     $query = qq|UPDATE defaults
 738                 SET ponumber = '$form->{invnumber}'|;
 739     $dbh->do($query) || $form->dberror($query);
 740
 741   }
 742
 743
 744   my $rc = $dbh->commit;
 745   $dbh->disconnect;
 746
 747   $rc;
 748
 749 }
 750
 751
 752
 753 sub get_vendor {
 754   my ($self, $myconfig, $form) = @_;
 755
 756   # connect to database
 757   my $dbh = $form->dbconnect($myconfig);
 758
 759   my $dateformat = $myconfig->{dateformat};
 760   $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
 761
 762   my $duedate = ($form->{invdate}) ? "to_date('$form->{invdate}', '$dateformat')" : "current_date";
 763
 764   $form->{vendor_id} *= 1;
 765   # get vendor
 766   my $query = qq|SELECT taxincluded, terms, email, cc, bcc,
 767                  addr1, addr2, addr3, addr4,
 768                  $duedate + terms AS duedate
 769                  FROM vendor
 770                  WHERE id = $form->{vendor_id}|;
 771   my $sth = $dbh->prepare($query);
 772   $sth->execute || $form->dberror($query);
 773
 774   $ref = $sth->fetchrow_hashref(NAME_lc);
 775   map { $form->{$_} = $ref->{$_} } keys %$ref;
 776   $sth->finish;
 777
 778   # get shipto if we do not convert an order or invoice
 779   if (!$form->{shipto}) {
 780     map { delete $form->{$_} } qw(shiptoname shiptoaddr1 shiptoaddr2 shiptoaddr3 shiptoaddr4 shiptocontact shiptophone shiptofax shiptoemail);
 781
 782     $query = qq|SELECT * FROM shipto
 783                 WHERE trans_id = $form->{vendor_id}|;
 784     $sth = $dbh->prepare($query);
 785     $sth->execute || $form->dberror($query);
 786
 787     $ref = $sth->fetchrow_hashref(NAME_lc);
 788     map { $form->{$_} = $ref->{$_} } keys %$ref;
 789     $sth->finish;
 790   }
 791
 792   # get taxes for vendor
 793   $query = qq|SELECT c.accno
 794               FROM chart c, vendortax v
 795               WHERE v.chart_id = c.id
 796               AND v.vendor_id = $form->{vendor_id}|;
 797   $sth = $dbh->prepare($query);
 798   $sth->execute || $form->dberror($query);
 799
 800   my $vendortax = ();
 801   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
 802     $vendortax{$ref->{accno}} = 1;
 803   }
 804   $sth->finish;
 805
 806
 807   # get tax rates and description
 808   $query = qq|SELECT c.accno, c.description, c.link, t.rate
 809               FROM chart c, tax t
 810               WHERE c.id = t.chart_id
 811               AND c.link LIKE '%CT_tax%'
 812               ORDER BY accno|;
 813   $sth = $dbh->prepare($query);
 814   $sth->execute || $form->dberror($query);
 815
 816   $form->{taxaccounts} = "";
 817   $form->{taxpart} = "";
 818   $form->{taxservice} = "";
 819   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
 820     if ($vendortax{$ref->{accno}}) {
 821       $form->{"$ref->{accno}_rate"} = $ref->{rate};
 822       $form->{"$ref->{accno}_description"} = $ref->{description};
 823       $form->{taxaccounts} .= "$ref->{accno} ";
 824     }
 825
 826     foreach my $item (split /:/, $ref->{link}) {
 827       if ($item =~ /IC_taxpart/) {
 828         $form->{taxpart} .= "$ref->{accno} ";
 829       }
 830
 831       if ($item =~ /IC_taxservice/) {
 832         $form->{taxservice} .= "$ref->{accno} ";
 833       }
 834     }
 835   }
 836   $sth->finish;
 837   chop $form->{taxaccounts};
 838   chop $form->{taxpart};
 839   chop $form->{taxservice};
 840
 841   if (!$form->{id} && $form->{type} !~ /_order/) {
 842     # setup last accounts used
 843     $query = qq|SELECT c.accno, c.description, c.link, c.category
 844                 FROM chart c
 845                 JOIN acc_trans ac ON (ac.chart_id = c.id)
 846                 JOIN ap a ON (a.id = ac.trans_id)
 847                 WHERE a.vendor_id = $form->{vendor_id}
 848                 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
 849                 AND a.id IN (SELECT max(id) FROM ap
 850                              WHERE vendor_id = $form->{vendor_id})|;
 851     $sth = $dbh->prepare($query);
 852     $sth->execute || $form->dberror($query);
 853
 854     my $i = 0;
 855     while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
 856       if ($ref->{category} eq 'E') {
 857         $i++;
 858         $form->{"AP_amount_$i"} = "$ref->{accno}--$ref->{description}";
 859       }
 860       if ($ref->{category} eq 'L') {
 861         $form->{AP} = $form->{AP_1} = "$ref->{accno}--$ref->{description}";
 862       }
 863     }
 864     $sth->finish;
 865     $form->{rowcount} = $i if ($i && !$form->{type});
 866   }
 867
 868   $dbh->disconnect;
 869
 870 }
 871
 872
 873 sub retrieve_item {
 874   my ($self, $myconfig, $form) = @_;
 875
 876   my $i = $form->{rowcount};
 877   my $var;
 878
 879   # don't include assemblies or obsolete parts
 880   my $where = "NOT p.assembly = '1' AND NOT p.obsolete = '1'";
 881
 882   if ($form->{"partnumber_$i"}) {
 883     $var = $form->like(lc $form->{"partnumber_$i"});
 884     $where .= " AND lower(p.partnumber) LIKE '$var'";
 885   }
 886
 887   if ($form->{"description_$i"}) {
 888     $var = $form->like(lc $form->{"description_$i"});
 889     $where .= " AND lower(p.description) LIKE '$var'";
 890   }
 891
 892   if ($form->{"partsgroup_$i"}) {
 893     $var = $form->like(lc $form->{"partsgroup_$i"});
 894     $where .= " AND lower(pg.partsgroup) LIKE '$var'";
 895   }
 896
 897   if ($form->{"description_$i"}) {
 898     $where .= " ORDER BY description";
 899   } else {
 900     $where .= " ORDER BY partnumber";
 901   }
 902
 903   # connect to database
 904   my $dbh = $form->dbconnect($myconfig);
 905
 906   my $query = qq|SELECT p.id, p.partnumber, p.description,
 907                  c1.accno AS inventory_accno,
 908                  c2.accno AS income_accno,
 909                  c3.accno AS expense_accno,
 910                  pg.partsgroup
 911                  FROM parts p
 912                  LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
 913                  LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
 914                  LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
 915                  LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
 916                  WHERE $where|;
 917   my $sth = $dbh->prepare($query);
 918   $sth->execute || $form->dberror($query);
 919
 920   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 921     # get tax rates for part
 922     $query = qq|SELECT c.accno
 923                 FROM chart c
 924                 JOIN partstax pt ON (pt.chart_id = c.id)
 925                 WHERE pt.parts_id = $ref->{id}|;
 926     my $sth = $dbh->prepare($query);
 927     $sth->execute || $form->dberror($query);
 928
 929     $ref->{taxaccounts} = "";
 930     while (my $ptref = $sth->fetchrow_hashref(NAME_lc)) {
 931       $ref->{taxaccounts} .= "$ptref->{accno} ";
 932     }
 933     $sth->finish;
 934     chop $ref->{taxaccounts};
 935
 936     push @{ $form->{item_list} }, $ref;
 937   }
 938
 939   $sth->finish;
 940   $dbh->disconnect;
 941
 942 }
 943
 944
 945
 946 sub vendor_details {
 947   my ($self, $myconfig, $form) = @_;
 948
 949   # connect to database
 950   my $dbh = $form->dbconnect($myconfig);
 951
 952   # get rest for the vendor
 953   my $query = qq|SELECT vendornumber, name, addr1, addr2, addr3, addr4,
 954                  contact, phone as vendorphone, fax as vendorfax, vendornumber
 955                  FROM vendor
 956                  WHERE id = $form->{vendor_id}|;
 957   my $sth = $dbh->prepare($query);
 958   $sth->execute || $form->dberror($query);
 959
 960   $ref = $sth->fetchrow_hashref(NAME_lc);
 961   map { $form->{$_} = $ref->{$_} } keys %$ref;
 962
 963   $sth->finish;
 964   $dbh->disconnect;
 965
 966 }
 967
 968
 969 sub item_links {
 970   my ($self, $myconfig, $form) = @_;
 971
 972   # connect to database
 973   my $dbh = $form->dbconnect($myconfig);
 974
 975   my $query = qq|SELECT accno, description, link
 976                  FROM chart
 977                  WHERE link LIKE '%IC%'
 978                  ORDER BY accno|;
 979   my $sth = $dbh->prepare($query);
 980   $sth->execute || $form->dberror($query);
 981
 982   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 983     foreach my $key (split(/:/, $ref->{link})) {
 984       if ($key =~ /IC/) {
 985         push @{ $form->{IC_links}{$key} }, { accno => $ref->{accno},
 986                                        description => $ref->{description} };
 987       }
 988     }
 989   }
 990
 991   $sth->finish;
 992 }
 993
 994 1;
 995