sql-ledger/old/sql-ledger/SL/IC.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 Control backend
  26 #
  27 #======================================================================
  28
  29 package IC;
  30
  31
  32 sub get_part {
  33   my ($self, $myconfig, $form) = @_;
  34
  35   # connect to db
  36   my $dbh = $form->dbconnect($myconfig);
  37
  38   my $query = qq|SELECT p.*,
  39                  c1.accno AS inventory_accno,
  40                  c2.accno AS income_accno,
  41                  c3.accno AS expense_accno,
  42                  pg.partsgroup
  43                  FROM parts p
  44                  LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
  45                  LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
  46                  LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
  47                  LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
  48                  WHERE p.id = $form->{id}|;
  49   my $sth = $dbh->prepare($query);
  50   $sth->execute || $form->dberror($query);
  51   my $ref = $sth->fetchrow_hashref(NAME_lc);
  52
  53   # copy to $form variables
  54   map { $form->{$_} = $ref->{$_} } ( keys %{ $ref } );
  55
  56   $sth->finish;
  57
  58   my %oid = ('Pg'       => 'a.oid',
  59              'Oracle'   => 'a.rowid'
  60             );
  61
  62
  63   # part or service item
  64   $form->{item} = ($form->{inventory_accno}) ? 'part' : 'service';
  65   if ($form->{assembly}) {
  66     $form->{item} = 'assembly';
  67
  68     # retrieve assembly items
  69     $query = qq|SELECT p.id, p.partnumber, p.description,
  70                 p.sellprice, p.weight, a.qty, a.bom, p.unit,
  71                 pg.partsgroup
  72                 FROM parts p
  73                 JOIN assembly a ON (a.parts_id = p.id)
  74                 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
  75                 WHERE a.id = $form->{id}
  76                 ORDER BY $oid{$myconfig->{dbdriver}}|;
  77
  78     $sth = $dbh->prepare($query);
  79     $sth->execute || $form->dberror($query);
  80
  81     $form->{assembly_rows} = 0;
  82     while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  83       $form->{assembly_rows}++;
  84       foreach my $key ( keys %{ $ref } ) {
  85         $form->{"${key}_$form->{assembly_rows}"} = $ref->{$key};
  86       }
  87     }
  88     $sth->finish;
  89
  90   }
  91
  92   # setup accno hash for <option checked> {amount} is used in create_links
  93   $form->{amount}{IC} = $form->{inventory_accno};
  94   $form->{amount}{IC_income} = $form->{income_accno};
  95   $form->{amount}{IC_sale} = $form->{income_accno};
  96   $form->{amount}{IC_expense} = $form->{expense_accno};
  97   $form->{amount}{IC_cogs} = $form->{expense_accno};
  98
  99
 100   if ($form->{item} ne 'service') {
 101     # get makes
 102     if ($form->{makemodel}) {
 103       $query = qq|SELECT name FROM makemodel
 104                   WHERE parts_id = $form->{id}|;
 105
 106       $sth = $dbh->prepare($query);
 107       $sth->execute || $form->dberror($query);
 108
 109       my $i = 1;
 110       while (($form->{"make_$i"}, $form->{"model_$i"}) = split(/:/, $sth->fetchrow_array)) {
 111         $i++;
 112       }
 113       $sth->finish;
 114       $form->{makemodel_rows} = $i - 1;
 115
 116     }
 117   }
 118
 119   # now get accno for taxes
 120   $query = qq|SELECT c.accno
 121               FROM chart c, partstax pt
 122               WHERE pt.chart_id = c.id
 123               AND pt.parts_id = $form->{id}|;
 124
 125   $sth = $dbh->prepare($query);
 126   $sth->execute || $form->dberror($query);
 127
 128   while (($key) = $sth->fetchrow_array) {
 129     $form->{amount}{$key} = $key;
 130   }
 131
 132   $sth->finish;
 133
 134   # is it an orphan
 135   $query = qq|SELECT parts_id
 136               FROM invoice
 137               WHERE parts_id = $form->{id}
 138               UNION
 139               SELECT parts_id
 140               FROM orderitems
 141               WHERE parts_id = $form->{id}
 142               UNION
 143               SELECT parts_id
 144               FROM assembly
 145               WHERE parts_id = $form->{id}|;
 146   $sth = $dbh->prepare($query);
 147   $sth->execute || $form->dberror($query);
 148
 149   ($form->{orphaned}) = $sth->fetchrow_array;
 150   $form->{orphaned} = !$form->{orphaned};
 151   $sth->finish;
 152
 153   $dbh->disconnect;
 154
 155 }
 156
 157
 158
 159 sub save {
 160   my ($self, $myconfig, $form) = @_;
 161
 162   ($form->{inventory_accno}) = split(/--/, $form->{IC});
 163   ($form->{expense_accno}) = split(/--/, $form->{IC_expense});
 164   ($form->{income_accno}) = split(/--/, $form->{IC_income});
 165
 166   # connect to database, turn off AutoCommit
 167   my $dbh = $form->dbconnect_noauto($myconfig);
 168
 169   # save the part
 170   # make up a unique handle and store in partnumber field
 171   # then retrieve the record based on the unique handle to get the id
 172   # replace the partnumber field with the actual variable
 173   # add records for makemodel
 174
 175   # if there is a $form->{id} then replace the old entry
 176   # delete all makemodel entries and add the new ones
 177
 178   # escape '
 179   map { $form->{$_} =~ s/'/''/g } qw(partnumber description notes unit bin);
 180
 181   # undo amount formatting
 182   map { $form->{$_} = $form->parse_amount($myconfig, $form->{$_}) } qw(rop weight listprice sellprice lastcost stock);
 183
 184   # set date to NULL if nothing entered
 185   $form->{priceupdate} = ($form->{priceupdate}) ? qq|'$form->{priceupdate}'| : "NULL";
 186
 187   $form->{makemodel} = (($form->{make_1}) || ($form->{model_1})) ? 1 : 0;
 188
 189   $form->{alternate} = 0;
 190   $form->{assembly} = ($form->{item} eq 'assembly') ? 1 : 0;
 191   $form->{obsolete} *= 1;
 192   $form->{onhand} *= 1;
 193
 194   my ($query, $sth);
 195
 196   if ($form->{id}) {
 197
 198     # get old price
 199     $query = qq|SELECT sellprice, weight
 200                 FROM parts
 201                 WHERE id = $form->{id}|;
 202     $sth = $dbh->prepare($query);
 203     $sth->execute || $form->dberror($query);
 204
 205     my ($sellprice, $weight) = $sth->fetchrow_array;
 206     $sth->finish;
 207
 208     # if item is part of an assembly adjust all assemblies
 209     $query = qq|SELECT id, qty
 210                 FROM assembly
 211                 WHERE parts_id = $form->{id}|;
 212     $sth = $dbh->prepare($query);
 213     $sth->execute || $form->dberror($query);
 214
 215     while (my ($id, $qty) = $sth->fetchrow_array) {
 216       &update_assembly($dbh, $form, $id, $qty * 1, $sellprice * 1, $weight * 1);
 217     }
 218     $sth->finish;
 219
 220
 221     if ($form->{item} ne 'service') {
 222       # delete makemodel records
 223       $query = qq|DELETE FROM makemodel
 224                   WHERE parts_id = $form->{id}|;
 225       $dbh->do($query) || $form->dberror($query);
 226     }
 227
 228     if ($form->{item} eq 'assembly') {
 229       if ($form->{onhand} != 0) {
 230         &adjust_inventory($dbh, $form, $form->{id}, $form->{onhand} * -1);
 231       }
 232
 233       if ($form->{orphaned}) {
 234         # delete assembly records
 235         $query = qq|DELETE FROM assembly
 236                     WHERE id = $form->{id}|;
 237         $dbh->do($query) || $form->dberror($query);
 238       } else {
 239         # update BOM only
 240         $query = qq|UPDATE assembly
 241                     SET bom = ?
 242                     WHERE id = ?
 243                     AND parts_id = ?|;
 244         $sth = $dbh->prepare($query);
 245
 246         for $i (1 .. $form->{assembly_rows} - 1) {
 247           $sth->execute(($form->{"bom_$i"}) ? '1' : '0', $form->{id}, $form->{"id_$i"}) || $form->dberror($query);
 248         }
 249         $sth->finish;
 250       }
 251
 252       $form->{onhand} += $form->{stock};
 253     }
 254
 255     # delete tax records
 256     $query = qq|DELETE FROM partstax
 257                 WHERE parts_id = $form->{id}|;
 258     $dbh->do($query) || $form->dberror($query);
 259
 260   } else {
 261     my $uid = time;
 262     $uid .= $form->{login};
 263
 264     $query = qq|INSERT INTO parts (partnumber)
 265                 VALUES ('$uid')|;
 266     $dbh->do($query) || $form->dberror($query);
 267
 268     $query = qq|SELECT id FROM parts
 269                 WHERE partnumber = '$uid'|;
 270     $sth = $dbh->prepare($query);
 271     $sth->execute || $form->dberror($query);
 272
 273     ($form->{id}) = $sth->fetchrow_array;
 274     $sth->finish;
 275
 276     $form->{orphaned} = 1;
 277     $form->{onhand} = ($form->{stock} * 1) if $form->{item} eq 'assembly';
 278
 279   }
 280
 281   my $partsgroup_id = 0;
 282   if ($form->{partsgroup}) {
 283     my $partsgroup = lc $form->{partsgroup};
 284     $query = qq|SELECT DISTINCT id FROM partsgroup
 285                 WHERE lower(partsgroup) = '$partsgroup'|;
 286     $sth = $dbh->prepare($query);
 287     $sth->execute || $form->dberror($query);
 288
 289     ($partsgroup_id) = $sth->fetchrow_array;
 290     $sth->finish;
 291
 292     if (!$partsgroup_id) {
 293       $query = qq|INSERT INTO partsgroup (partsgroup)
 294                   VALUES ('$form->{partsgroup}')|;
 295       $dbh->do($query) || $form->dberror($query);
 296
 297       $query = qq|SELECT id FROM partsgroup
 298                   WHERE partsgroup = '$form->{partsgroup}'|;
 299       $sth = $dbh->prepare($query);
 300       $sth->execute || $form->dberror($query);
 301
 302       ($partsgroup_id) = $sth->fetchrow_array;
 303       $sth->finish;
 304     }
 305   }
 306
 307
 308   $query = qq|UPDATE parts SET
 309               partnumber = '$form->{partnumber}',
 310               description = '$form->{description}',
 311               makemodel = '$form->{makemodel}',
 312               alternate = '$form->{alternate}',
 313               assembly = '$form->{assembly}',
 314               listprice = $form->{listprice},
 315               sellprice = $form->{sellprice},
 316               lastcost = $form->{lastcost},
 317               weight = $form->{weight},
 318               priceupdate = $form->{priceupdate},
 319               unit = '$form->{unit}',
 320               notes = '$form->{notes}',
 321               rop = $form->{rop},
 322               bin = '$form->{bin}',
 323               inventory_accno_id = (SELECT id FROM chart
 324                                     WHERE accno = '$form->{inventory_accno}'),
 325               income_accno_id = (SELECT id FROM chart
 326                                  WHERE accno = '$form->{income_accno}'),
 327               expense_accno_id = (SELECT id FROM chart
 328                                   WHERE accno = '$form->{expense_accno}'),
 329               obsolete = '$form->{obsolete}',
 330               image = '$form->{image}',
 331               drawing = '$form->{drawing}',
 332               microfiche = '$form->{microfiche}',
 333               partsgroup_id = $partsgroup_id
 334               WHERE id = $form->{id}|;
 335   $dbh->do($query) || $form->dberror($query);
 336
 337
 338   # insert makemodel records
 339   unless ($form->{item} eq 'service') {
 340     for my $i (1 .. $form->{makemodel_rows}) {
 341       # put make and model together
 342       if (($form->{"make_$i"}) || ($form->{"model_$i"})) {
 343         map { $form->{"${_}_$i"} =~ s/'/''/g } qw(make model);
 344
 345         $query = qq|INSERT INTO makemodel (parts_id, name)
 346                     VALUES ($form->{id},
 347                     '$form->{"make_$i"}:$form->{"model_$i"}')|;
 348         $dbh->do($query) || $form->dberror($query);
 349       }
 350     }
 351   }
 352
 353
 354   # insert taxes
 355   foreach $item (split / /, $form->{taxaccounts}) {
 356     if ($form->{"IC_tax_$item"}) {
 357       $query = qq|INSERT INTO partstax (parts_id, chart_id)
 358                   VALUES ($form->{id},
 359                           (SELECT id
 360                            FROM chart
 361                            WHERE accno = '$item'))|;
 362       $dbh->do($query) || $form->dberror($query);
 363     }
 364   }
 365
 366   # add assembly records
 367   if ($form->{item} eq 'assembly') {
 368
 369     if ($form->{orphaned}) {
 370       for my $i (1 .. $form->{assembly_rows}) {
 371         $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
 372
 373         if ($form->{"qty_$i"} != 0) {
 374           $form->{"bom_$i"} *= 1;
 375           $query = qq|INSERT INTO assembly (id, parts_id, qty, bom)
 376                       VALUES ($form->{id}, $form->{"id_$i"},
 377                       $form->{"qty_$i"}, '$form->{"bom_$i"}')|;
 378           $dbh->do($query) || $form->dberror($query);
 379         }
 380       }
 381     }
 382
 383     # adjust onhand for the assembly
 384     if ($form->{onhand} != 0) {
 385       &adjust_inventory($dbh, $form, $form->{id}, $form->{onhand});
 386     }
 387
 388   }
 389
 390
 391   # commit
 392   my $rc = $dbh->commit;
 393   $dbh->disconnect;
 394
 395   $rc;
 396
 397 }
 398
 399
 400
 401 sub update_assembly {
 402   my ($dbh, $form, $id, $qty, $sellprice, $weight) = @_;
 403
 404   my $query = qq|SELECT id, qty
 405                  FROM assembly
 406                  WHERE parts_id = $id|;
 407   my $sth = $dbh->prepare($query);
 408   $sth->execute || $form->dberror($query);
 409
 410   while (my ($pid, $aqty) = $sth->fetchrow_array) {
 411     &update_assembly($dbh, $form, $pid, $aqty * $qty, $sellprice, $weight);
 412   }
 413   $sth->finish;
 414
 415   $query = qq|UPDATE parts
 416               SET sellprice = sellprice +
 417                   $qty * ($form->{sellprice} - $sellprice),
 418                   weight = weight +
 419                   $qty * ($form->{weight} - $weight)
 420               WHERE id = $id|;
 421   $dbh->do($query) || $form->dberror($query);
 422
 423 }
 424
 425
 426
 427 sub retrieve_assemblies {
 428   my ($self, $myconfig, $form) = @_;
 429
 430   # connect to database
 431   my $dbh = $form->dbconnect($myconfig);
 432
 433   my $where = '1 = 1';
 434
 435   if ($form->{partnumber}) {
 436     my $partnumber = $form->like(lc $form->{partnumber});
 437     $where .= " AND lower(p.partnumber) LIKE '$partnumber'";
 438   }
 439
 440   if ($form->{description}) {
 441     my $description = $form->like(lc $form->{description});
 442     $where .= " AND lower(p.description) LIKE '$description'";
 443   }
 444   $where .= " AND NOT p.obsolete = '1'";
 445
 446   # retrieve assembly items
 447   my $query = qq|SELECT p.id, p.partnumber, p.description,
 448                  p.bin, p.onhand, p.rop,
 449                    (SELECT sum(p2.inventory_accno_id)
 450                     FROM parts p2, assembly a
 451                     WHERE p2.id = a.parts_id
 452                     AND a.id = p.id) AS inventory
 453                  FROM parts p
 454                  WHERE $where
 455                  AND assembly = '1'|;
 456
 457   my $sth = $dbh->prepare($query);
 458   $sth->execute || $form->dberror($query);
 459
 460   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 461     push @{ $form->{assembly_items} }, $ref if $ref->{inventory};
 462   }
 463   $sth->finish;
 464
 465   $dbh->disconnect;
 466
 467 }
 468
 469
 470 sub restock_assemblies {
 471   my ($self, $myconfig, $form) = @_;
 472
 473   # connect to database
 474   my $dbh = $form->dbconnect_noauto($myconfig);
 475
 476   for my $i (1 .. $form->{rowcount}) {
 477
 478     $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
 479
 480     if ($form->{"qty_$i"} != 0) {
 481       &adjust_inventory($dbh, $form, $form->{"id_$i"}, $form->{"qty_$i"});
 482     }
 483
 484   }
 485
 486   my $rc = $dbh->commit;
 487   $dbh->disconnect;
 488
 489   $rc;
 490
 491 }
 492
 493
 494 sub adjust_inventory {
 495   my ($dbh, $form, $id, $qty) = @_;
 496
 497   my $query = qq|SELECT p.id, p.inventory_accno_id, p.assembly, a.qty
 498                  FROM parts p
 499                  JOIN assembly a ON (a.parts_id = p.id)
 500                  WHERE a.id = $id|;
 501   my $sth = $dbh->prepare($query);
 502   $sth->execute || $form->dberror($query);
 503
 504   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 505
 506     my $allocate = $qty * $ref->{qty};
 507
 508     # is it a service item, then loop
 509     $ref->{inventory_accno_id} *= 1;
 510     next if (($ref->{inventory_accno_id} == 0) && !$ref->{assembly});
 511
 512     # adjust parts onhand
 513     $form->update_balance($dbh,
 514                           "parts",
 515                           "onhand",
 516                           qq|id = $ref->{id}|,
 517                           $allocate * -1);
 518   }
 519
 520   $sth->finish;
 521
 522   # update assembly
 523   $form->update_balance($dbh,
 524                         "parts",
 525                         "onhand",
 526                         qq|id = $id|,
 527                         $qty);
 528
 529 }
 530
 531
 532 sub delete {
 533   my ($self, $myconfig, $form) = @_;
 534
 535   # connect to database, turn off AutoCommit
 536   my $dbh = $form->dbconnect_noauto($myconfig);
 537
 538   if ($form->{item} eq 'assembly' && $form->{onhand} != 0) {
 539     # adjust onhand for the assembly
 540     &adjust_inventory($dbh, $form, $form->{id}, $form->{onhand} * -1);
 541   }
 542
 543   my $query = qq|DELETE FROM parts
 544                  WHERE id = $form->{id}|;
 545   $dbh->do($query) || $form->dberror($query);
 546
 547   $query = qq|DELETE FROM partstax
 548               WHERE parts_id = $form->{id}|;
 549   $dbh->do($query) || $form->dberror($query);
 550
 551   # check if it is a part, assembly or service
 552   if ($form->{item} eq 'part') {
 553     $query = qq|DELETE FROM makemodel
 554                 WHERE parts_id = $form->{id}|;
 555     $dbh->do($query) || $form->dberror($query);
 556   }
 557
 558   if ($form->{item} eq 'assembly') {
 559     $query = qq|DELETE FROM assembly
 560                 WHERE id = $form->{id}|;
 561     $dbh->do($query) || $form->dberror($query);
 562   }
 563
 564   if ($form->{item} eq 'alternate') {
 565     $query = qq|DELETE FROM alternate
 566                 WHERE id = $form->{id}|;
 567     $dbh->do($query) || $form->dberror($query);
 568   }
 569
 570   # commit
 571   my $rc = $dbh->commit;
 572   $dbh->disconnect;
 573
 574   $rc;
 575
 576 }
 577
 578
 579 sub assembly_item {
 580   my ($self, $myconfig, $form) = @_;
 581
 582   my $i = $form->{assembly_rows};
 583   my $var;
 584   my $where = "1 = 1";
 585
 586   if ($form->{"partnumber_$i"}) {
 587     $var = $form->like(lc $form->{"partnumber_$i"});
 588     $where .= " AND lower(p.partnumber) LIKE '$var'";
 589   }
 590   if ($form->{"description_$i"}) {
 591     $var = $form->like(lc $form->{"description_$i"});
 592     $where .= " AND lower(p.description) LIKE '$var'";
 593   }
 594   if ($form->{"partsgroup_$i"}) {
 595     $var = $form->like(lc $form->{"partsgroup_$i"});
 596     $where .= " AND lower(pg.partsgroup) LIKE '$var'";
 597   }
 598
 599   if ($form->{id}) {
 600     $where .= " AND NOT p.id = $form->{id}";
 601   }
 602
 603   if ($partnumber) {
 604     $where .= " ORDER BY p.partnumber";
 605   } else {
 606     $where .= " ORDER BY p.description";
 607   }
 608
 609   # connect to database
 610   my $dbh = $form->dbconnect($myconfig);
 611
 612   my $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
 613                  p.weight, p.onhand, p.unit,
 614                  pg.partsgroup
 615                  FROM parts p
 616                  LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
 617                  WHERE $where|;
 618   my $sth = $dbh->prepare($query);
 619   $sth->execute || $form->dberror($query);
 620
 621   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 622     push @{ $form->{item_list} }, $ref;
 623   }
 624
 625   $sth->finish;
 626   $dbh->disconnect;
 627
 628 }
 629
 630
 631 sub all_parts {
 632   my ($self, $myconfig, $form) = @_;
 633
 634   my $where = '1 = 1';
 635   my $var;
 636
 637   foreach my $item (qw(partnumber drawing microfiche)) {
 638     if ($form->{$item}) {
 639       $var = $form->like(lc $form->{$item});
 640       $where .= " AND lower(p.$item) LIKE '$var'";
 641     }
 642   }
 643   # special case for description
 644   if ($form->{description}) {
 645     unless ($form->{bought} || $form->{sold} || $form->{onorder} || $form->{ordered}) {
 646       $var = $form->like(lc $form->{description});
 647       $where .= " AND lower(p.description) LIKE '$var'";
 648     }
 649   }
 650
 651   if ($form->{searchitems} eq 'part') {
 652     $where .= " AND p.inventory_accno_id > 0";
 653   }
 654   if ($form->{searchitems} eq 'assembly') {
 655     $form->{bought} = "";
 656     $where .= " AND p.assembly = '1'";
 657   }
 658   if ($form->{searchitems} eq 'service') {
 659     $where .= " AND p.inventory_accno_id IS NULL AND NOT p.assembly = '1'";
 660     # irrelevant for services
 661     $form->{make} = $form->{model} = "";
 662   }
 663
 664   # items which were never bought, sold or on an order
 665   if ($form->{itemstatus} eq 'orphaned') {
 666     $form->{onhand} = $form->{short} = 0;
 667     $form->{bought} = $form->{sold} = 0;
 668     $form->{onorder} = $form->{ordered} = 0;
 669     $form->{transdatefrom} = $form->{transdateto} = "";
 670
 671     $where .= " AND p.onhand = 0
 672                 AND p.id NOT IN (SELECT p.id FROM parts p, invoice i
 673                                  WHERE p.id = i.parts_id)
 674                 AND p.id NOT IN (SELECT p.id FROM parts p, assembly a
 675                                  WHERE p.id = a.parts_id)
 676                 AND p.id NOT IN (SELECT p.id FROM parts p, orderitems o
 677                                  WHERE p.id = o.parts_id)";
 678   }
 679
 680   if ($form->{itemstatus} eq 'active') {
 681     $where .= " AND p.obsolete = '0'";
 682   }
 683   if ($form->{itemstatus} eq 'obsolete') {
 684     $where .= " AND p.obsolete = '1'";
 685     $form->{onhand} = $form->{short} = 0;
 686   }
 687   if ($form->{itemstatus} eq 'onhand') {
 688     $where .= " AND p.onhand > 0";
 689   }
 690   if ($form->{itemstatus} eq 'short') {
 691     $where .= " AND p.onhand < 0";
 692   }
 693
 694   if ($form->{make}) {
 695     $var = $form->like(lc $form->{make}).":%";
 696     $where .= " AND p.id IN (SELECT DISTINCT ON (m.parts_id) m.parts_id
 697                            FROM makemodel m WHERE lower(m.name) LIKE '$var')";
 698   }
 699   if ($form->{model}) {
 700     $var = "%:".$form->like($form->{model});
 701     $where .= " AND p.id IN (SELECT DISTINCT ON (m.parts_id) m.parts_id
 702                            FROM makemodel m WHERE lower(m.name) LIKE '$var')";
 703   }
 704   if ($form->{partsgroup}) {
 705     $var = $form->like(lc $form->{partsgroup});
 706     $where .= " AND lower(pg.partsgroup) LIKE '$var'";
 707
 708   }
 709
 710   # connect to database
 711   my $dbh = $form->dbconnect($myconfig);
 712
 713
 714   my $sortorder = join ', ', $form->sort_columns(qw(partnumber description bin priceupdate partsgroup));
 715   $sortorder = $form->{sort} unless $sortorder;
 716
 717   my $query = qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit,
 718                  p.bin, p.sellprice, p.listprice, p.lastcost, p.rop, p.weight,
 719                  p.priceupdate, p.image, p.drawing, p.microfiche,
 720                  pg.partsgroup
 721                  FROM parts p
 722                  LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
 723                  WHERE $where
 724                  ORDER BY $sortorder|;
 725
 726   # rebuild query for bought and sold items
 727   if ($form->{bought} || $form->{sold} || $form->{onorder} || $form->{ordered}) {
 728
 729     my $union = "";
 730     $query = "";
 731
 732     if ($form->{bought} || $form->{sold}) {
 733
 734       my $invwhere = "$where";
 735       $invwhere .= " AND i.assemblyitem = '0'";
 736       $invwhere .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
 737       $invwhere .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
 738
 739       if ($form->{description}) {
 740         $var = $form->like(lc $form->{description});
 741         $invwhere .= " AND lower(i.description) LIKE '$var'";
 742       }
 743
 744       my $flds = qq|p.id, p.partnumber, i.description,
 745                     i.qty AS onhand, i.unit, p.bin, i.sellprice,
 746                     p.listprice, p.lastcost, p.rop, p.weight,
 747                     p.priceupdate, p.image, p.drawing, p.microfiche,
 748                     pg.partsgroup,
 749                     a.invnumber, a.ordnumber, i.trans_id|;
 750
 751       if ($form->{bought}) {
 752         $query = qq|
 753                     SELECT $flds, 'ir' AS module, '' AS type,
 754                     1 AS exchangerate
 755                     FROM parts p
 756                     JOIN invoice i ON (i.parts_id = p.id)
 757                     JOIN ap a ON (i.trans_id = a.id)
 758                     LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
 759                     WHERE $invwhere|;
 760         $union = "
 761                   UNION";
 762       }
 763
 764       if ($form->{sold}) {
 765         $query .= qq|$union
 766                      SELECT $flds, 'is' AS module, '' AS type,
 767                      1 As exchangerate
 768                      FROM parts p
 769                      JOIN invoice i ON (i.parts_id = p.id)
 770                      JOIN ar a ON (i.trans_id = a.id)
 771                      LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
 772                      WHERE $invwhere|;
 773         $union = "
 774                   UNION";
 775       }
 776     }
 777
 778     if ($form->{onorder} || $form->{ordered}) {
 779       my $ordwhere = "$where";
 780       $ordwhere .= " AND o.closed = '0'" unless $form->{closed};
 781
 782       $ordwhere .= " AND o.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
 783       $ordwhere .= " AND o.transdate <= '$form->{transdateto}'" if $form->{transdateto};
 784
 785       if ($form->{description}) {
 786         $var = $form->like(lc $form->{description});
 787         $ordwhere .= " AND lower(oi.description) LIKE '$var'";
 788       }
 789
 790       $flds = qq|p.id, p.partnumber, oi.description,
 791                  oi.qty AS onhand, oi.unit, p.bin, oi.sellprice,
 792                  p.listprice, p.lastcost, p.rop, p.weight,
 793                  p.priceupdate, p.image, p.drawing, p.microfiche,
 794                  pg.partsgroup,
 795                  '' AS invnumber, o.ordnumber, oi.trans_id|;
 796
 797       if ($form->{ordered}) {
 798         $query .= qq|$union
 799                      SELECT $flds, 'oe' AS module, 'sales_order' AS type,
 800                     (SELECT buy FROM exchangerate ex
 801                      WHERE ex.curr = o.curr
 802                      AND ex.transdate = o.transdate) AS exchangerate
 803                      FROM parts p
 804                      JOIN orderitems oi ON (oi.parts_id = p.id)
 805                      JOIN oe o ON (oi.trans_id = o.id)
 806                      LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
 807                      WHERE $ordwhere
 808                      AND o.customer_id > 0|;
 809         $union = "
 810                   UNION";
 811       }
 812
 813       if ($form->{onorder}) {
 814         $flds = qq|p.id, p.partnumber, oi.description,
 815                    oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice,
 816                    p.listprice, p.lastcost, p.rop, p.weight,
 817                    p.priceupdate, p.image, p.drawing, p.microfiche,
 818                    pg.partsgroup,
 819                    '' AS invnumber, o.ordnumber, oi.trans_id|;
 820
 821         $query .= qq|$union
 822                     SELECT $flds, 'oe' AS module, 'purchase_order' AS type,
 823                     (SELECT sell FROM exchangerate ex
 824                      WHERE ex.curr = o.curr
 825                      AND ex.transdate = o.transdate) AS exchangerate
 826                     FROM parts p
 827                     JOIN orderitems oi ON (oi.parts_id = p.id)
 828                     JOIN oe o ON (oi.trans_id = o.id)
 829                     LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
 830                     WHERE $ordwhere
 831                     AND o.vendor_id > 0|;
 832       }
 833
 834     }
 835
 836     $query .= qq|
 837                  ORDER BY $sortorder|;
 838
 839   }
 840
 841   my $sth = $dbh->prepare($query);
 842   $sth->execute || $form->dberror($query);
 843
 844   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 845     push @{ $form->{parts} }, $ref;
 846   }
 847
 848   $sth->finish;
 849
 850
 851   # include individual items for assemblies
 852   if ($form->{searchitems} eq 'assembly' && $form->{bom}) {
 853     foreach $item (@{ $form->{parts} }) {
 854       push @assemblies, $item;
 855       $query = qq|SELECT p.id, p.partnumber, p.description, a.qty AS onhand,
 856                   p.unit, p.bin,
 857                   p.sellprice, p.listprice, p.lastcost,
 858                   p.rop, p.weight, p.priceupdate,
 859                   p.image, p.drawing, p.microfiche
 860                   FROM parts p
 861                   JOIN assembly a ON (p.id = a.parts_id)
 862                   WHERE a.id = $item->{id}|;
 863
 864       $sth = $dbh->prepare($query);
 865       $sth->execute || $form->dberror($query);
 866
 867       while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 868         $ref->{assemblyitem} = 1;
 869         push @assemblies, $ref;
 870       }
 871       $sth->finish;
 872
 873       push @assemblies, {id => $item->{id}};
 874
 875     }
 876
 877     # copy assemblies to $form->{parts}
 878     @{ $form->{parts} } = @assemblies;
 879
 880   }
 881
 882   $dbh->disconnect;
 883
 884 }
 885
 886
 887 sub create_links {
 888   my ($self, $module, $myconfig, $form) = @_;
 889
 890   # connect to database
 891   my $dbh = $form->dbconnect($myconfig);
 892
 893   my $query = qq|SELECT accno, description, link
 894                  FROM chart
 895                  WHERE link LIKE '%$module%'
 896                  ORDER BY accno|;
 897   my $sth = $dbh->prepare($query);
 898   $sth->execute || $form->dberror($query);
 899
 900   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 901     foreach my $key (split(/:/, $ref->{link})) {
 902       if ($key =~ /$module/) {
 903         push @{ $form->{"${module}_links"}{$key} }, { accno => $ref->{accno},
 904                                       description => $ref->{description} };
 905       }
 906     }
 907   }
 908
 909   $sth->finish;
 910
 911   if ($form->{id}) {
 912     $query = qq|SELECT weightunit
 913                 FROM defaults|;
 914     $sth = $dbh->prepare($query);
 915     $sth->execute || $form->dberror($query);
 916
 917     ($form->{weightunit}) = $sth->fetchrow_array;
 918     $sth->finish;
 919
 920   } else {
 921     $query = qq|SELECT weightunit, current_date
 922                 FROM defaults|;
 923     $sth = $dbh->prepare($query);
 924     $sth->execute || $form->dberror($query);
 925
 926     ($form->{weightunit}, $form->{priceupdate}) = $sth->fetchrow_array;
 927     $sth->finish;
 928   }
 929
 930   $dbh->disconnect;
 931
 932 }
 933
 934
 935 1;
 936