#=====================================================================
# LedgerSMB 
# Small Medium Business Accounting software
# http://www.ledgersmb.org/
# 
# Copyright (C) 2006
# This work contains copyrighted information from a number of sources all used
# with permission.
#
# This file contains source code included with or based on SQL-Ledger which
# is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
# under the GNU General Public License version 2 or, at your option, any later
# version.  For a full list including contact information of contributors,
# maintainers, and copyright holders, see the CONTRIBUTORS file.
#
# Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
# Copyright (C) 2000
#
#  Author: DWS Systems Inc.
#     Web: http://www.sql-ledger.org
#
#  Contributors:
#
#======================================================================
#
# This file has NOT undergone whitespace cleanup.
#
#======================================================================
#
# Inventory Control backend
#
#======================================================================

package IC;


sub get_part {
	my ($self, $myconfig, $form) = @_;

	# connect to db
	my $dbh = $form->{dbh};
	my $i;

	my $query = qq|
		   SELECT p.*, c1.accno AS inventory_accno, 
		          c1.description AS inventory_description, 
		          c2.accno AS income_accno, 
		          c2.description AS income_description,
		          c3.accno AS expense_accno, 
		          c3.description AS expense_description, pg.partsgroup
		     FROM parts p
		LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
		LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
		LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
		LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
		    WHERE p.id = ?|;
	my $sth = $dbh->prepare($query);
	$sth->execute($form->{id}) || $form->dberror($query);
	my $ref = $sth->fetchrow_hashref(NAME_lc);

	# copy to $form variables
	for (keys %$ref) { $form->{$_} = $ref->{$_} }
	$sth->finish;
  
	# part, service item or labor
	$form->{item} = ($form->{inventory_accno_id}) ? 'part' : 'service';
	$form->{item} = 'labor' if ! $form->{income_accno_id};
    
	if ($form->{assembly}) {
		$form->{item} = 'assembly';

		# retrieve assembly items
		$query = qq|
			   SELECT p.id, p.partnumber, p.description,
			          p.sellprice, p.weight, a.qty, a.bom, a.adj,
			          p.unit, p.lastcost, p.listprice, 
			          pg.partsgroup, p.assembly, p.partsgroup_id
			     FROM parts p
			     JOIN assembly a ON (a.parts_id = p.id)
			LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
			    WHERE a.id = ?|;

		$sth = $dbh->prepare($query);
		$sth->execute($form->{id}) || $form->dberror($query);
    
		$form->{assembly_rows} = 0;
		while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
			$form->{assembly_rows}++;
			foreach my $key ( keys %{ $ref } ) {
				$form->{"${key}_$form->{assembly_rows}"} 
					= $ref->{$key};
			}
		}
		$sth->finish;

	}

	# setup accno hash for <option checked> 
	# {amount} is used in create_links
	for (qw(inventory income expense)) { 
		$form->{amount}{"IC_$_"} 
			= { 
				accno => $form->{"${_}_accno"}, 
				description => $form->{"${_}_description"} 
			};
	};


	if ($form->{item} =~ /(part|assembly)/) {
  	
		if ($form->{makemodel} ne "") {
			$query = qq|
				SELECT make, model
				  FROM makemodel
				 WHERE parts_id = ?|;

			$sth = $dbh->prepare($query);
			$sth->execute($form->{id}) || $form->dberror($query);
      
			while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
				push @{ $form->{makemodels} }, $ref;
			}
			$sth->finish;
		}
	}

	# now get accno for taxes
	$query = qq|
		SELECT c.accno FROM chart c, partstax pt
		 WHERE pt.chart_id = c.id AND pt.parts_id = ?|;
  
	$sth = $dbh->prepare($query);
	$sth->execute($form->{id}) || $form->dberror($query);

	while (($key) = $sth->fetchrow_array) {
		$form->{amount}{$key} = $key;
	}

	$sth->finish;

	my $id = $dbh->quote($form->{id});
	# is it an orphan
	$query = qq|
		SELECT parts_id FROM invoice WHERE parts_id = $id
		UNION
		SELECT parts_id FROM orderitems WHERE parts_id = $id
		UNION
		SELECT parts_id FROM assembly WHERE parts_id = $id
		UNION
		SELECT parts_id FROM jcitems WHERE parts_id = $id|;
	($form->{orphaned}) = $dbh->selectrow_array($query);
	$form->{orphaned} = !$form->{orphaned};

	$form->{orphaned} = 0 if $form->{project_id};

	if ($form->{item} eq 'assembly') {
		if ($form->{orphaned}) {
			$form->{orphaned} = !$form->{onhand};
		}
	}

	if ($form->{item} =~ /(part|service)/) {
		# get vendors
		$query = qq|
			  SELECT v.id, v.name, pv.partnumber,
			         pv.lastcost, pv.leadtime, 
			         pv.curr AS vendorcurr
			    FROM partsvendor pv
			    JOIN vendor v ON (v.id = pv.vendor_id)
			   WHERE pv.parts_id = ?
			ORDER BY 2|;
    
		$sth = $dbh->prepare($query);
		$sth->execute($form->{id}) || $form->dberror($query);
    
		while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
			push @{ $form->{vendormatrix} }, $ref;
		}
		$sth->finish;
	}
 
	# get matrix
	if ($form->{item} ne 'labor') {
		$query = qq|
			   SELECT pc.pricebreak, pc.sellprice AS customerprice,
			          pc.curr AS customercurr, pc.validfrom, 
			          pc.validto, c.name, c.id AS cid, 
			          g.pricegroup, g.id AS gid
			     FROM partscustomer pc
			LEFT JOIN customer c ON (c.id = pc.customer_id)
			LEFT JOIN pricegroup g ON (g.id = pc.pricegroup_id)
			    WHERE pc.parts_id = ?
			 ORDER BY c.name, g.pricegroup, pc.pricebreak|;
		$sth = $dbh->prepare($query);
		$sth->execute($form->{id}) || $form->dberror($query);

		while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
			push @{ $form->{customermatrix} }, $ref;
		}
		$sth->finish;
	}

	$form->run_custom_queries('parts', 'SELECT');
  
}


sub save {
	my ($self, $myconfig, $form) = @_;

	($form->{inventory_accno}) = split(/--/, $form->{IC_inventory});
	($form->{expense_accno}) = split(/--/, $form->{IC_expense});
	($form->{income_accno}) = split(/--/, $form->{IC_income});

	my $dbh = $form->{dbh};

	# undo amount formatting
	for (qw(rop weight listprice sellprice lastcost stock)) { 
		$form->{$_} = $form->parse_amount($myconfig, $form->{$_}); 
	}
  
	$form->{makemodel} = (($form->{make_1}) || ($form->{model_1})) ? 1 : 0;

	$form->{assembly} = ($form->{item} eq 'assembly') ? 1 : 0;
	for (qw(alternate obsolete onhand)) { $form->{$_} *= 1 }
  
	my $query;
	my $sth;
	my $i;
	my $null;
	my $vendor_id;
	my $customer_id;
  
	if ($form->{id}) {

		# get old price
		$query = qq|
			SELECT id, listprice, sellprice, lastcost, weight, 
			       project_id
			  FROM parts
			 WHERE id = ?|;
		my $sth = $dbh->prepare($query);
		$sth->execute($form->{id});
		my ($id, $listprice, $sellprice, $lastcost, $weight, 
			$project_id) 
				= $sth->fetchrow_array();

		if ($id) {
      
			if (!$project_id) {
				# if item is part of an assembly 
				# adjust all assemblies
				$query = qq|
					SELECT id, qty, adj
					  FROM assembly
					 WHERE parts_id = ?|;
				$sth = $dbh->prepare($query);
				$sth->execute($form->{id}) || 
					$form->dberror($query);
				while (my ($id, $qty, $adj) 
						= $sth->fetchrow_array) {

					&update_assembly(
						$dbh, $form, $id, $qty, $adj, 
						$listprice * 1, $sellprice * 1,
						$lastcost * 1, $weight * 1);
				}
				$sth->finish;
			}

			if ($form->{item} =~ /(part|service)/) {
				# delete partsvendor records
				$query = qq|
					DELETE FROM partsvendor
					      WHERE parts_id = ?|;
				$sth = $dbh->prepare($query);
				$sth->execute($form->{id})
					|| $form->dberror($query);
			}
       
			if ($form->{item} !~ /(service|labor)/) {
				# delete makemodel records
				$query = qq|
					DELETE FROM makemodel
					      WHERE parts_id = ?|;
				$sth = $dbh->prepare($query);
				$sth->execute($form->{id})
					|| $form->dberror($query);
			}

			if ($form->{item} eq 'assembly') {

				if ($form->{onhand}) {
					&adjust_inventory(
						$dbh, $form, $form->{id}, 
						$form->{onhand} * -1);
				}
	
				if ($form->{orphaned}) {
					# delete assembly records
					$query = qq|
						DELETE FROM assembly
						      WHERE id = ?|;
					$sth = $dbh->prepare($query);
					$sth->execute($form->{id})
						|| $form->dberror($query);
				} else {

					for $i (1 .. 
						$form->{assembly_rows} - 1) {

						# update BOM, A only
						for (qw(bom adj)) { 
							$form->{"${_}_$i"} 
								*= 1; 
						}

						$query = qq|
							UPDATE assembly
							   SET bom = ?,
							       adj = ?
							 WHERE id = ?
							       AND parts_id = ?|;
						$sth = $dbh->prepare($query);
						$sth->execute(
							$form->{"bom_$i"},
							$form->{"adj_$i"},
							$form->{id},
							$form->{"id_$i"}
							)|| $form->dberror(
								$query);
					}
				}

				$form->{onhand} += $form->{stock};

			}

			# delete tax records
			$query = qq|DELETE FROM partstax WHERE parts_id = ?|;

			$sth = $dbh->prepare($query); 
			$sth->execute($form->{id})|| $form->dberror($query);

			# delete matrix
			$query = qq|
				DELETE FROM partscustomer 
				      WHERE parts_id = ?|;
      
			$sth = $dbh->prepare($query); 
			$sth->execute($form->{id})|| $form->dberror($query);
		} else {
			$query = qq|INSERT INTO parts (id) VALUES (?)|;
			$sth = $dbh->prepare($query); 
			$sth->execute($form->{id})|| $form->dberror($query);
		}

	}
  
  
	if (!$form->{id}) {
		my $uid = localtime;
		$uid .= "$$";

		$query = qq|INSERT INTO parts (partnumber) VALUES ('$uid')|;
		$dbh->do($query) || $form->dberror($query);

		$query = qq|SELECT id FROM parts WHERE partnumber = '$uid'|;
		$sth = $dbh->prepare($query);
		$sth->execute || $form->dberror($query);
		($form->{id}) = $sth->fetchrow_array;
		$sth->finish;

		$form->{orphaned} = 1;
		$form->{onhand} = ($form->{stock} * 1) 
			if $form->{item} eq 'assembly';
	}

	my $partsgroup_id;
	($null, $partsgroup_id) = split /--/, $form->{partsgroup};
	$partsgroup_id *= 1;

	$form->{partnumber} = $form->update_defaults(
		$myconfig, "partnumber", $dbh) if ! $form->{partnumber};

	if (!$form->{priceupdate}){
		$form->{priceupdate} = 'now';
	}
	$query = qq|
		UPDATE parts 
		   SET partnumber = ?,
		       description = ?,
		       makemodel = ?,
		       alternate = ?,
		       assembly = ?,
		       listprice = ?,
		       sellprice = ?,
		       lastcost = ?,
		       weight = ?,
		       priceupdate = ?,
		       unit = ?,
		       notes = ?,
		       rop = ?,
		       bin = ?,
		       inventory_accno_id = (SELECT id FROM chart
		                              WHERE accno = ?),
		       income_accno_id = (SELECT id FROM chart
		                           WHERE accno = ?),
		       expense_accno_id = (SELECT id FROM chart
		                            WHERE accno = ?),
		       obsolete = ?,
		       image = ?,
		       drawing = ?,
		       microfiche = ?,
		       partsgroup_id = ?
		 WHERE id = ?|;
	$sth = $dbh->prepare($query);
	$sth->execute(
		$form->{partnumber}, $form->{description}, $form->{makemodel},
		$form->{alternate}, $form->{assembly}, $form->{listprice},
		$form->{sellprice}, $form->{lastcost}, $form->{weight},
		$form->{priceupdate}, $form->{unit}, $form->{notes},
		$form->{rop}, $form->{bin}, $form->{inventory_accno},
		$form->{income_accno}, $form->{expense_accno}, 
		$form->{obsolete}, $form->{image}, $form->{drawing},
		$form->{microfiche}, $partsgroup_id, $form->{id}
		) || $form->dberror($query);

 
	# insert makemodel records
	if ($form->{item} =~ /(part|assembly)/) {
		$query = qq|
			INSERT INTO makemodel (parts_id, make, model)
			     VALUES (?, ?, ?)|;
		$sth = $dbh->prepare($query) || $form->dberror($query);
		for $i (1 .. $form->{makemodel_rows}) {
			if (($form->{"make_$i"} ne "") 
					|| ($form->{"model_$i"} ne "")) {
				$sth->execute(
					$form->{id}, $form->{"make_$i"},
					$form->{"model_$i"}
					) || $form->dberror($query); 
			}
		}
	}


	# insert taxes
      	$query = qq|
		INSERT INTO partstax (parts_id, chart_id)
		     VALUES (?, (SELECT id FROM chart WHERE accno = ?))|;
	$sth = $dbh->prepare($query);
	for (split / /, $form->{taxaccounts}) {
		if ($form->{"IC_tax_$_"}) {
			$sth->execute($form->{id}, $_) 
				|| $form->dberror($query);
		}
	}
  
  
	@a = localtime;
	$a[5] += 1900;
	$a[4]++;
	$a[4] = substr("0$a[4]", -2);
	$a[3] = substr("0$a[3]", -2);
	my $shippingdate = "$a[5]$a[4]$a[3]";
  
	($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
 
	# add assembly records
	if ($form->{item} eq 'assembly' && !$project_id) {
    
		if ($form->{orphaned}) {
			$query = qq|
				INSERT INTO assembly 
				            (id, parts_id, qty, bom, adj)
				     VALUES (?, ?, ?, ?, ?)|;
			$sth = $dbh->prepare($query);
			for $i (1 .. $form->{assembly_rows}) {
				$form->{"qty_$i"} = $form->parse_amount(
					$myconfig, $form->{"qty_$i"});
                if(!$form->{"bom_$i"}){
                    $form->{"bom_$i"} = undef;
                }
            
                if ($form->{"id_$i"} && $form->{"qty_$i"}){
                    $sth->execute(
					    $form->{id}, $form->{"id_$i"},
				    	$form->{"qty_$i"}, $form->{"bom_$i"} || 0,
					    $form->{"adj_$i"}
					        ) || $form->dberror($query);
                }
			}
		}
		# adjust onhand for the parts
		if ($form->{onhand}) {
			&adjust_inventory(
				$dbh, $form, $form->{id}, $form->{onhand});
		}
	}


	# add vendors
	if ($form->{item} ne 'assembly') {
		$updparts{$form->{id}} = 1;
    
		for $i (1 .. $form->{vendor_rows}) {
			if (($form->{"vendor_$i"} ne "") 
						&& $form->{"lastcost_$i"}) {

				($null, $vendor_id) 
					= split /--/, $form->{"vendor_$i"};
	
				for (qw(lastcost leadtime)) { 
					$form->{"${_}_$i"} 
						= $form->parse_amount(
							$myconfig, 
							$form->{"${_}_$i"});
				}
	
				$query = qq|
					INSERT INTO partsvendor 
					            (vendor_id, parts_id, 
					            partnumber, lastcost, 
					            leadtime, curr)
					     VALUES (?, ?, ?, ?, ?, ?)|;
				$sth = $dbh->prepare($query);
				$sth->execute(
					$vendor_id, $form->{id}, 
					$form->{"partnumber_$i"},
					$form->{"lastcost_$i"},
					$form->{"leadtime_$i"}, 
					$form->{"vendorcurr_$i"}
					)|| $form->dberror($query);
			}
		}
	}
  
  
	# add pricematrix
	for $i (1 .. $form->{customer_rows}) {

		for (qw(pricebreak customerprice)) { 
			$form->{"${_}_$i"} = $form->parse_amount(
				$myconfig, $form->{"${_}_$i"});
		}

		if ($form->{"customerprice_$i"}) {

			($null, $customer_id) 
				= split /--/, $form->{"customer_$i"};
			$customer_id *= 1;
      
			($null, $pricegroup_id) 
				= split /--/, $form->{"pricegroup_$i"};
      
			my $validfrom;
			my $validto;
			$validfrom = $form->{"validfrom_$i"} if $form->{"validfrom_$i"};
			$validto = $form->{"validto_$i"} if $form->{"validto_$i"};
			$query = qq|
				INSERT INTO partscustomer 
				            (parts_id, customer_id,
				            pricegroup_id, pricebreak, 
				            sellprice, curr,
				            validfrom, validto)
			             VALUES (?, ?, ?, ?, ?, ?, ?, ?)|;
			$sth = $dbh->prepare($query);
			$sth->execute(
				$form->{id}, $customer_id, $pricegroup_id, 
				$form->{"pricebreak_$i"}, 
				$form->{"customerprice_$i"}, 
				$form->{"customercurr_$i"},
				$validfrom, $validto
				)|| $form->dberror($query);
		}
	}

	my $rc = $dbh->commit;

	$form->run_custom_queries('parts', 'UPDATE');
	$rc;
  
}



sub update_assembly {
	my ($dbh, $form, $id, $qty, $adj, $listprice, $sellprice, $lastcost, 
		$weight) = @_;

	my $formlistprice = $form->{listprice};
	my $formsellprice = $form->{sellprice};
  
	if (!$adj) {
		$formlistprice = $listprice;
		$formsellprice = $sellprice;
	}
  
	my $query = qq|SELECT id, qty, adj FROM assembly WHERE parts_id = ?|;
	my $sth = $dbh->prepare($query);
	$sth->execute($id) || $form->dberror($query);

	$form->{$id} = 1; # Not sure what this is for...
	                  # In fact, we don't seem to use it... Chris T
  
	while (my ($pid, $aqty, $aadj) = $sth->fetchrow_array) {
		&update_assembly($dbh, $form, $pid, $aqty * $qty, $aadj, 
			$listprice, $sellprice, $lastcost, $weight) 
				if !$form->{$pid};
	}
	$sth->finish;
	$qty = $dbh->quote($qty);
	$formlistprice = $dbh->quote($formlistprice - $listprice);
	$formsellprice = $dbh->quote($formsellprice - $sellprice);
	$formlastcost = $dbh->quote($form->{lastcost} - $lastcost);
	$weight = $dbh->quote($form->{weight} - $weight);
	$id = $dbh->quote($id);

	$query = qq|
		UPDATE parts
		   SET listprice = listprice +
		       $qty * cast($formlistprice AS numeric),
		       sellprice = sellprice +
		       $qty * cast($formsellprice AS numeric),
		       lastcost = lastcost +
		       $qty * cast($formlastcost AS numeric),
		       weight = weight +
		       $qty * cast($weight AS numeric)
		 WHERE id = $id|;
	$dbh->do($query) || $form->dberror($query);

	delete $form->{$id};
  
}



sub retrieve_assemblies {
	my ($self, $myconfig, $form) = @_;
  
	# connect to database
	my $dbh = $form->{dbh};

	my $where = '1 = 1';
  
	if ($form->{partnumber} ne "") {
		my $partnumber = $dbh->quote($form->like(
			lc $form->{partnumber}));
		$where .= " AND lower(p.partnumber) LIKE $partnumber";
	}
  
	if ($form->{description} ne "") {
		my $description = $dbh->($form->like(lc $form->{description}));
		$where .= " AND lower(p.description) LIKE $description";
	}
	$where .= qq| AND p.obsolete = '0'
		AND p.project_id IS NULL|;

	my %ordinal = ( 
		'partnumber' => 2,
		'description' => 3,
		'bin' => 4
	);

	my @a = qw(partnumber description bin);
	my $sortorder = $form->sort_order(\@a, \%ordinal);
  
  
	# retrieve assembly items
	my $query = qq|
		  SELECT p.id, p.partnumber, p.description, p.bin, p.onhand, 
		         p.rop
		    FROM parts p
 		   WHERE $where
		         AND p.assembly = '1'
		 ORDER BY $sortorder|;

	my $sth = $dbh->prepare($query);
	$sth->execute || $form->dberror($query);
  
	$query = qq|
		  SELECT sum(p.inventory_accno_id), p.assembly 
		    FROM parts p
		    JOIN assembly a ON (a.parts_id = p.id)
		   WHERE a.id = ?
		GROUP BY p.assembly|;
	my $svh = $dbh->prepare($query) || $form->dberror($query);
  
	my $inh;
	if ($form->{checkinventory}) {
	  	$query = qq|
			SELECT p.id, p.onhand, a.qty
			  FROM parts p
			  JOIN assembly a ON (a.parts_id = p.id)
			 WHERE (p.inventory_accno_id > 0 OR p.assembly)
			       AND p.income_accno_id > 0 AND a.id = ?|;
		$inh = $dbh->prepare($query) || $form->dberror($query);
	}
  
	my %available = ();
	my %required;
	my $ref;
	my $aref;
	my $stock;
	my $howmany;
	my $ok;
  
	while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
		$svh->execute($ref->{id});
		($ref->{inventory}, $ref->{assembly}) = $svh->fetchrow_array;
		$svh->finish;
    
		if ($ref->{inventory} || $ref->{assembly}) {
			$ok = 1;
			if ($form->{checkinventory}) {
				$inh->execute($ref->{id}) 
					|| $form->dberror($query);;
				$ok = 0;
				%required = ();
	
				while ($aref 
					= $inh->fetchrow_hashref(NAME_lc)) {

					$available{$aref->{id}} = 
						(exists $available{$aref->{id}}) 
						? $available{$aref->{id}} 
						: $aref->{onhand};
					$required{$aref->{id}} = $aref->{qty};
	  
					if ($available{$aref->{id}} 
							>= $aref->{qty}) {
	    
						$howmany = 
							($aref->{qty}) 
							? int $available{
								$aref->{id}}
								/$aref->{qty} 
							: 1;
						if ($stock) {
							$stock = 
								($stock 
								  > $howmany) 
								? $howmany 
								: $stock;
						} else {
							$stock = $howmany;
						}
						$ok = 1;

						$available{$aref->{id}} 
							-= $aref->{qty} 
								* $stock;

					} else {
						$ok = 0;
						for (keys %required) { 
							$available{$_} +=
								$required{$_} 
								  * $stock; 
						}
						$stock = 0;
						last;
					}
				}
				$inh->finish;
				$ref->{stock} = $stock;
	
			}
			push @{ $form->{assembly_items} }, $ref if $ok;
		}
	}
	$sth->finish;

	$dbh->commit;
  
}


sub restock_assemblies {
	my ($self, $myconfig, $form) = @_;

	# connect to database
	my $dbh = $form->{dbh};
   
	for my $i (1 .. $form->{rowcount}) {
		$form->{"qty_$i"} = $form->parse_amount(
			$myconfig, $form->{"qty_$i"});

		if ($form->{"qty_$i"}) {
			&adjust_inventory(
				$dbh, $form, $form->{"id_$i"}, 
				$form->{"qty_$i"});
		}
 
	}

	my $rc = $dbh->commit;

	$rc;

}


sub adjust_inventory {
	# Private method.  Do not commit transaction at end of function...
	my ($dbh, $form, $id, $qty) = @_;

	my $query = qq|
		SELECT p.id, p.inventory_accno_id, p.assembly, a.qty
		  FROM parts p
		  JOIN assembly a ON (a.parts_id = p.id)
		 WHERE a.id = ?|;
	my $sth = $dbh->prepare($query);
	$sth->execute($id) || $form->dberror($query);

	while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {

		# is it a service item then loop
		if (! $ref->{inventory_accno_id}) {
			next if ! $ref->{assembly}; 
		}
    
		# adjust parts onhand
		$form->update_balance(
			$dbh, "parts", "onhand", qq|id = $ref->{id}|,
			$qty * $ref->{qty} * -1);
	}

	$sth->finish;

	# update assembly
	$form->update_balance($dbh, "parts", "onhand", qq|id = $id|, $qty);

}


sub delete {
	my ($self, $myconfig, $form) = @_;

	my $dbh = $form->{dbh};

	my $query;

	$query = qq|DELETE FROM parts WHERE id = ?|;
	$sth = $dbh->prepare($query);
	$sth->execute($form->{id}) || $form->dberror($query);

	$query = qq|DELETE FROM partstax WHERE parts_id = ?|;
	$sth = $dbh->prepare($query);
	$sth->execute($form->{id}) || $form->dberror($query);


	if ($form->{item} ne 'assembly') {
		$query = qq|DELETE FROM partsvendor WHERE parts_id = ?|;
		$sth = $dbh->prepare($query);
		$sth->execute($form->{id}) || $form->dberror($query);
	}

	# check if it is a part, assembly or service
	if ($form->{item} ne 'service') {
		$query = qq|DELETE FROM makemodel WHERE parts_id = ?|;
		$sth = $dbh->prepare($query);
		$sth->execute($form->{id}) || $form->dberror($query);
	}

	if ($form->{item} eq 'assembly') {
		$query = qq|DELETE FROM assembly WHERE id = ?|;
		$sth = $dbh->prepare($query);
		$sth->execute($form->{id}) || $form->dberror($query);
	}
  

	$query = qq|DELETE FROM inventory WHERE parts_id = ?|;
	$sth = $dbh->prepare($query);
	$sth->execute($form->{id}) || $form->dberror($query);
  
	$query = qq|DELETE FROM partscustomer WHERE parts_id = ?|;
	$sth = $dbh->prepare($query);
	$sth->execute($form->{id}) || $form->dberror($query);
  
	$query = qq|DELETE FROM translation WHERE trans_id = ?|;
	$sth = $dbh->prepare($query);
	$sth->execute($form->{id}) || $form->dberror($query);

	# commit
	my $rc = $dbh->commit;

	$rc;
  
}


sub assembly_item {
	my ($self, $myconfig, $form) = @_;
	my $dbh = $form->{dbh};

	my $i = $form->{assembly_rows};
	my $var;
	my $null;
	my $where = "p.obsolete = '0'";

	if ($form->{"partnumber_$i"} ne "") {
		$var = $dbh->quote($form->like(lc $form->{"partnumber_$i"}));
		$where .= " AND lower(p.partnumber) LIKE $var";
	}
	if ($form->{"description_$i"} ne "") {
		$var = $dbh->quote($form->like(lc $form->{"description_$i"}));
		$where .= " AND lower(p.description) LIKE $var";
	}
	if ($form->{"partsgroup_$i"} ne "") {
		($null, $var) = split /--/, $form->{"partsgroup_$i"};
		$var = $dbh->quote($var);
		$where .= qq| AND p.partsgroup_id = $var|;
	}

	if ($form->{id}) {
		$where .= " AND p.id != ".$dbh->quote($form->{id});
	}

	if ($form->{"description_$i"} ne "") {
		$where .= " ORDER BY p.description";
	} else {
		$where .= " ORDER BY p.partnumber";
	}


	my $query = qq|
		   SELECT p.id, p.partnumber, p.description, p.sellprice,
		          p.weight, p.onhand, p.unit, p.lastcost,
		          pg.partsgroup, p.partsgroup_id
		     FROM parts p
		LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
		    WHERE $where|;
	my $sth = $dbh->prepare($query);
	$sth->execute || $form->dberror($query);

	while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
		push @{ $form->{item_list} }, $ref;
	}
  
	$sth->finish;
  
}


sub all_parts {
	my ($self, $myconfig, $form) = @_;

	$dbh = $form->{dbh};
	my $where = '1 = 1';
	my $null;
	my $var;
	my $ref;
  
	for (qw(partnumber drawing microfiche)) {
		if ($form->{$_} ne "") {
			$var = $dbh->quote($form->like(lc $form->{$_}));
			$where .= " AND lower(p.$_) LIKE $var";
		}
	}
	# special case for description
	if ($form->{description} ne "") {
		unless ($form->{bought} || $form->{sold} || $form->{onorder} 
			|| $form->{ordered} || $form->{rfq} || $form->{quoted}
		) {

			$var = $dbh->quote($form->like(
				lc $form->{description}));
			$where .= " AND lower(p.description) LIKE $var";
		}
	}
  
	# assembly components
	my $assemblyflds;
	if ($form->{searchitems} eq 'component') {
		$assemblyflds = qq|, p1.partnumber AS assemblypartnumber, 
			a.id AS assembly_id|;
	}

	# special case for serialnumber
	if ($form->{l_serialnumber}) {
		if ($form->{serialnumber} ne "") {
			$var = $dbh->quote(
				$form->like(lc $form->{serialnumber}));
			$where .= " AND lower(i.serialnumber) LIKE $var";
		}
	}

	if (($form->{warehouse} ne "") || $form->{l_warehouse}) {
		$form->{l_warehouse} = 1;
	}
  
	if ($form->{searchitems} eq 'part') {
		$where .= " AND p.inventory_accno_id > 0 AND p.income_accno_id > 0";
	}
	if ($form->{searchitems} eq 'assembly') {
		$form->{bought} = "";
		$where .= " AND p.assembly = '1'";
	}
	if ($form->{searchitems} eq 'service') {
		$where .= " AND p.assembly = '0' AND p.inventory_accno_id IS NULL";
	}
	if ($form->{searchitems} eq 'labor') {
		$where .= " AND p.inventory_accno_id > 0 AND p.income_accno_id IS NULL";
	}

	# items which were never bought, sold or on an order
	if ($form->{itemstatus} eq 'orphaned') {
		$where .= qq| 
			AND p.onhand = 0
			AND p.id NOT IN (SELECT p.id FROM parts p
				           JOIN invoice i 
			                        ON (p.id = i.parts_id))
			AND p.id NOT IN (SELECT p.id FROM parts p
			                  JOIN assembly a 
			                       ON (p.id = a.parts_id))
			AND p.id NOT IN (SELECT p.id FROM parts p
			                   JOIN orderitems o 
			                        ON (p.id = o.parts_id))
			AND p.id NOT IN (SELECT p.id FROM parts p
			                   JOIN jcitems j 
			                        ON (p.id = j.parts_id))|;
	}
  
	if ($form->{itemstatus} eq 'active') {
		 $where .= " AND p.obsolete = '0'";
	}
	if ($form->{itemstatus} eq 'obsolete') {
		$where .= " AND p.obsolete = '1'";
	}
	if ($form->{itemstatus} eq 'onhand') {
		$where .= " AND p.onhand > 0";
	}
	if ($form->{itemstatus} eq 'short') {
		$where .= " AND p.onhand < p.rop";
	}

	my $makemodelflds = qq|, '', ''|;;
	my $makemodeljoin;
  
	if (($form->{make} ne "") || $form->{l_make} || ($form->{model} ne "") 
							|| $form->{l_model}) {
		$makemodelflds = qq|, m.make, m.model|;
	 	$makemodeljoin = qq|LEFT JOIN makemodel m ON (m.parts_id = p.id)|;
    
		if ($form->{make} ne "") {
			$var = $dbh->quote($form->like(lc $form->{make}));
			$where .= " AND lower(m.make) LIKE $var";
		}
		if ($form->{model} ne "") {
			$var = $dbh->quote($form->like(lc $form->{model}));
			$where .= " AND lower(m.model) LIKE $var";
		}
	}
	if ($form->{partsgroup} ne "") {
		($null, $var) = split /--/, $form->{partsgroup};
		$where .= qq| AND p.partsgroup_id = | . $dbh->quote($var);
	}


	my %ordinal = ( 
		'partnumber' => 2,
		'description' => 3,
		'bin' => 6,
		'priceupdate' => 13,
		'drawing' => 15,
		'microfiche' => 16,
		'partsgroup' => 18,
		'make' => 21,
		'model' => 22,
		'assemblypartnumber' => 23
		);
  
	my @a = qw(partnumber description);
	my $sortorder = $form->sort_order(\@a, \%ordinal);

	my $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|;
	my ($curr) = $dbh->selectrow_array($query);
	$curr =~ s/:.*//;
	$curr = $dbh->quote($curr);
	my $flds = qq|
		p.id, p.partnumber, p.description, p.onhand, p.unit,
		p.bin, p.sellprice, p.listprice, p.lastcost, p.rop,
		p.avgcost,
		p.weight, p.priceupdate, p.image, p.drawing, p.microfiche,
		p.assembly, pg.partsgroup, $curr AS curr,
		c1.accno AS inventory, c2.accno AS income, c3.accno AS expense,
		p.notes
		$makemodelflds $assemblyflds
		|;

	$query = qq|
		   SELECT $flds
		     FROM parts p
		LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
		LEFT JOIN chart c1 ON (c1.id = p.inventory_accno_id)
		LEFT JOIN chart c2 ON (c2.id = p.income_accno_id)
		LEFT JOIN chart c3 ON (c3.id = p.expense_accno_id)
		$makemodeljoin
		    WHERE $where
		 ORDER BY $sortorder|;

	# redo query for components report
	if ($form->{searchitems} eq 'component') {
    
		$flds =~ s/p.onhand/a.qty AS onhand/;
    
		$query = qq|
			   SELECT $flds
			     FROM assembly a
			     JOIN parts p ON (a.parts_id = p.id)
			     JOIN parts p1 ON (a.id = p1.id)
			LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
			LEFT JOIN chart c1 ON (c1.id = p.inventory_accno_id)
			LEFT JOIN chart c2 ON (c2.id = p.income_accno_id)
			LEFT JOIN chart c3 ON (c3.id = p.expense_accno_id)
			$makemodeljoin
			    WHERE $where
			 ORDER BY $sortorder|;
	}


	# rebuild query for bought and sold items
	if ($form->{bought} || $form->{sold} || $form->{onorder} 
		|| $form->{ordered} || $form->{rfq} || $form->{quoted}
	) {

		$form->sort_order();
		@a = qw(partnumber description curr employee name 
			serialnumber id);
		push @a, "invnumber" if ($form->{bought} || $form->{sold});
		push @a, "ordnumber" if ($form->{onorder} || $form->{ordered});
		push @a, "quonumber" if ($form->{rfq} || $form->{quoted});

		%ordinal = (
			'partnumber' => 2,
			'description' => 3,
			'serialnumber' => 4,
			'bin' => 7,
			'priceupdate' => 14,
			'partsgroup' => 19,
			'invnumber' => 20,
			'ordnumber' => 21,
			'quonumber' => 22,
			'name' => 24,
			'employee' => 25,
			'curr' => 26,
			'make' => 29,
			'model' => 30
			);
    
		$sortorder = $form->sort_order(\@a, \%ordinal);

		my $union = "";
		$query = "";
  
		if ($form->{bought} || $form->{sold}) {
      
			my $invwhere = "$where";
			my $transdate = 
				($form->{method} eq 'accrual') 
				? "transdate" 
				: "datepaid";
      
			$invwhere .= " AND i.assemblyitem = '0'";
			$invwhere .= " AND a.$transdate >= " . 
				$dbh->quote($form->{transdatefrom})
					if $form->{transdatefrom};
			$invwhere .= " AND a.$transdate <= " . 
				$dbh->quote($form->{transdateto})
					if $form->{transdateto};

			if ($form->{description} ne "") {
				$var = dbh->quote(
					$form->like(lc $form->{description}));
				$invwhere .= 
					" AND lower(i.description) LIKE $var";
			}

			if ($form->{open} || $form->{closed}) {
				if ($form->{open} && $form->{closed}) {
					if ($form->{method} eq 'cash') {
						$invwhere .= 
							" AND a.amount = a.paid";
					}
				} else {
					if ($form->{open}) {
						if ($form->{method} eq 'cash') {
							$invwhere .= 
								" AND a.id = 0";
						} else {
							$invwhere .= 
								" AND NOT a.amount = a.paid";
						}
					} else {
						$invwhere .= 
							" AND a.amount = a.paid";
					}
				}
			} else {
				$invwhere .= " AND a.id = 0";
			}

			my $flds = qq|
				p.id, p.partnumber, i.description, 
				i.serialnumber, i.qty AS onhand, i.unit, p.bin,
				i.sellprice, p.listprice, p.lastcost, p.rop, 
				p.weight, p.avgcost, p.priceupdate, p.image, 
				p.drawing, p.microfiche, p.assembly, 
				pg.partsgroup, a.invnumber, a.ordnumber, 
				a.quonumber, i.trans_id, ct.name, 
				e.name AS employee, a.curr, a.till, p.notes
				$makemodelfld|;


			if ($form->{bought}) {
				my $rflds = $flds;
				$rflds =~ 
					s/i.qty AS onhand/i.qty * -1 AS onhand/;

				$query = qq|
					   SELECT $rflds, 'ir' AS module, 
					          '' AS type,
					          (SELECT sell 
					             FROM exchangerate ex
					            WHERE ex.curr = a.curr
					                  AND ex.transdate 
					                   = a.$transdate) 
					          AS exchangerate, i.discount
					     FROM invoice i
					     JOIN parts p 
					          ON (p.id = i.parts_id)
					     JOIN ap a ON (a.id = i.trans_id)
					     JOIN vendor ct 
					          ON (a.vendor_id = ct.id)
					LEFT JOIN partsgroup pg 
					          ON (p.partsgroup_id = pg.id)
					LEFT JOIN employee e 
					          ON (a.employee_id = e.id)
					$makemodeljoin
					    WHERE $invwhere|;
				$union = "
					UNION ALL";
			}

			if ($form->{sold}) {
				$query .= qq|
					$union
					   SELECT $flds, 'is' AS module, 
					          '' AS type,
					          (SELECT buy 
					             FROM exchangerate ex
					            WHERE ex.curr = a.curr
					                  AND ex.transdate 
					                  = a.$transdate) 
					          AS exchangerate, i.discount
					     FROM invoice i
					     JOIN parts p 
					          ON (p.id = i.parts_id)
					     JOIN ar a ON (a.id = i.trans_id)
					     JOIN customer ct 
					          ON (a.customer_id = ct.id)
					LEFT JOIN partsgroup pg 
					          ON (p.partsgroup_id = pg.id)
					LEFT JOIN employee e 
					          ON (a.employee_id = e.id)
					$makemodeljoin
					    WHERE $invwhere|;
				$union = "
					UNION ALL";
			}
		}

		if ($form->{onorder} || $form->{ordered}) {
			my $ordwhere = "$where
				AND a.quotation = '0'";
			$ordwhere .= " AND a.transdate >= ".
				$dbh->quote($form->{transdatefrom}) 
					if $form->{transdatefrom};
			$ordwhere .= " AND a.transdate <= ".
				$dbh->quote($form->{transdateto}) 
					if $form->{transdateto};

			if ($form->{description} ne "") {
				$var = $dbh->quote(
					$form->like(lc $form->{description}));
				$ordwhere .= " AND lower(i.description) LIKE $var";
			}
      
			if ($form->{open} || $form->{closed}) {
				unless ($form->{open} && $form->{closed}) {
					$ordwhere .= " AND a.closed = '0'" 
						if $form->{open};
					$ordwhere .= " AND a.closed = '1'" 
						if $form->{closed};
				}
			} else {
				$ordwhere .= " AND a.id = 0";
			}

			$flds = qq|
				p.id, p.partnumber, i.description, 
				i.serialnumber, i.qty AS onhand, i.unit, p.bin,
				i.sellprice, p.listprice, p.lastcost, p.rop, 
				p.weight, p.avgcost, p.priceupdate, p.image, 
				p.drawing, p.microfiche, p.assembly,
				pg.partsgroup, '' AS invnumber, a.ordnumber, 
				a.quonumber, i.trans_id, ct.name, 
				e.name AS employee, a.curr, '0' AS till, 
				p.notes
				$makemodelfld|;

			if ($form->{ordered}) {
				$query .= qq|
					$union
					   SELECT $flds, 'oe' AS module, 
					          'sales_order' AS type,
					          (SELECT buy 
					             FROM exchangerate ex
					            WHERE ex.curr = a.curr
					                  AND ex.transdate 
					                  = a.transdate) 
					          AS exchangerate, i.discount
					     FROM orderitems i
					     JOIN parts p ON (i.parts_id = p.id)
					     JOIN oe a ON (i.trans_id = a.id)
					     JOIN customer ct 
					          ON (a.customer_id = ct.id)
					LEFT JOIN partsgroup pg 
					          ON (p.partsgroup_id = pg.id)
					LEFT JOIN employee e 
					          ON (a.employee_id = e.id)
					$makemodeljoin
					    WHERE $ordwhere 
					          AND a.customer_id > 0|;
				$union = "
					UNION ALL";
			}
      
 			if ($form->{onorder}) {
				$flds = qq|
					p.id, p.partnumber, i.description, 
					i.serialnumber, i.qty AS onhand, i.unit,
					p.bin, i.sellprice, p.listprice, 
					p.lastcost, p.rop, p.weight, p.avgcost,
					p.priceupdate, p.image, p.drawing, 
					p.microfiche, p.assembly,
					pg.partsgroup, '' AS invnumber, 
					a.ordnumber, a.quonumber,
					i.trans_id, ct.name,e.name AS employee,
					a.curr, '0' AS till, p.notes
					$makemodelfld|;
		   
				$query .= qq|
					$union
					   SELECT $flds, 'oe' AS module, 
					          'purchase_order' AS type,
					          (SELECT sell 
					             FROM exchangerate ex
					            WHERE ex.curr = a.curr
					                  AND ex.transdate 
					                  = a.transdate) 
					          AS exchangerate, i.discount
					     FROM orderitems i
					     JOIN parts p ON (i.parts_id = p.id)
					     JOIN oe a ON (i.trans_id = a.id)
					     JOIN vendor ct 
					          ON (a.vendor_id = ct.id)
					LEFT JOIN partsgroup pg 
					          ON (p.partsgroup_id = pg.id)
					LEFT JOIN employee e 
					          ON (a.employee_id = e.id)
					$makemodeljoin
					    WHERE $ordwhere
					          AND a.vendor_id > 0|;
			}
    
		}
      
		if ($form->{rfq} || $form->{quoted}) {
			my $quowhere = "$where
				AND a.quotation = '1'";
			$quowhere .= " AND a.transdate >= ".
				$dbh->quote($form->{transdatefrom}) 
					if $form->{transdatefrom};
			$quowhere .= " AND a.transdate <= ".
				$dbh->quote($form->{transdateto}) 
					if $form->{transdateto};

			if ($form->{description} ne "") {
				$var = $dbh->quote(
					$form->like(lc $form->{description}));
				$quowhere .= " AND lower(i.description) LIKE $var";
			}
      
			if ($form->{open} || $form->{closed}) {
				unless ($form->{open} && $form->{closed}) {
					$ordwhere .= " AND a.closed = '0'" 
						if $form->{open};
					$ordwhere .= " AND a.closed = '1'" 
						if $form->{closed};
				}
			} else {
				$ordwhere .= " AND a.id = 0";
			}


			$flds = qq|
				p.id, p.partnumber, i.description, 
				i.serialnumber, i.qty AS onhand, i.unit, p.bin, 
				i.sellprice, p.listprice, p.lastcost, p.rop, 
				p.weight, p.avgcost, p.priceupdate, p.image, 
				p.drawing, p.microfiche, p.assembly,
				pg.partsgroup, '' AS invnumber, a.ordnumber, 
				a.quonumber, i.trans_id, ct.name, 
				e.name AS employee, a.curr, '0' AS till, p.notes
				$makemodelfld|;

			if ($form->{quoted}) {
				$query .= qq|
					$union
					   SELECT $flds, 'oe' AS module, 
					          'sales_quotation' AS type,
					          (SELECT buy 
					             FROM exchangerate ex
					            WHERE ex.curr = a.curr
					                  AND ex.transdate 
					                  = a.transdate) 
					                  AS exchangerate,
					          i.discount
					     FROM orderitems i
					     JOIN parts p ON (i.parts_id = p.id)
					     JOIN oe a ON (i.trans_id = a.id)
					     JOIN customer ct 
					          ON (a.customer_id = ct.id)
					LEFT JOIN partsgroup pg 
					          ON (p.partsgroup_id = pg.id)
					LEFT JOIN employee e 
					          ON (a.employee_id = e.id)
					$makemodeljoin
					    WHERE $quowhere
					          AND a.customer_id > 0|;
				$union = "
					UNION ALL";
			}
      
			if ($form->{rfq}) {
 				$flds = qq|
					p.id, p.partnumber, i.description, 
					i.serialnumber, i.qty AS onhand, 
					i.unit, p.bin, i.sellprice, p.listprice,
					p.lastcost, p.rop, p.weight, p.avgcost,
					p.priceupdate, p.image, p.drawing, 
					p.microfiche, p.assembly,
					pg.partsgroup, '' AS invnumber, 
					a.ordnumber, a.quonumber,
					i.trans_id, ct.name, e.name AS employee,
					a.curr, '0' AS till, p.notes
					$makemodelfld|;

				$query .= qq|
					$union
					   SELECT $flds, 'oe' AS module, 
					          'request_quotation' AS type,
					          (SELECT sell 
					             FROM exchangerate ex
					            WHERE ex.curr = a.curr
					                  AND ex.transdate 
					                  = a.transdate) 
					          AS exchangerate, i.discount
					     FROM orderitems i
					     JOIN parts p ON (i.parts_id = p.id)
					     JOIN oe a ON (i.trans_id = a.id)
					     JOIN vendor ct 
					          ON (a.vendor_id = ct.id)
					LEFT JOIN partsgroup pg 
					          ON (p.partsgroup_id = pg.id)
					LEFT JOIN employee e 
					          ON (a.employee_id = e.id)
					$makemodeljoin
					    WHERE $quowhere
					          AND a.vendor_id > 0|;
			}

		}

		$query .= qq|
			ORDER BY $sortorder|;

	}

	my $sth = $dbh->prepare($query);
	$sth->execute || $form->dberror($query);

	$query = qq|
		  SELECT c.accno
		    FROM chart c
		    JOIN partstax pt ON (pt.chart_id = c.id)
		   WHERE pt.parts_id = ?
		ORDER BY accno|;
	my $pth = $dbh->prepare($query) || $form->dberror($query);
  
	while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
		$pth->execute($ref->{id});
		while (($accno) = $pth->fetchrow_array) {
			$ref->{tax} .= "$accno ";
		}
		$pth->finish;
    
		push @{ $form->{parts} }, $ref;
	}
	$sth->finish;

	@a = ();
  
	# include individual items for assembly
	if (($form->{searchitems} eq 'assembly') && $form->{individual}) {

		if ($form->{sold} || $form->{ordered} || $form->{quoted}) {
			$flds = qq|
				p.id, p.partnumber, p.description, 
				p.onhand AS perassembly, p.unit, p.bin, 
				p.sellprice, p.listprice, p.lastcost, p.rop,
				p.avgcost, p.weight, p.priceupdate, p.image, 
				p.drawing, p.microfiche, p.assembly, 
				pg.partsgroup, p.notes
				$makemodelflds $assemblyflds |;
		} else {
			# replace p.onhand with a.qty AS onhand
			$flds =~ s/p\.onhand/a.qty AS perassembly/;
		}
	
		for (@{ $form->{parts} }) {
			push @a, $_;
			$_->{perassembly} = 1;
			$flds =~ s/p\.onhand*AS perassembly/p\.onhand, a\.qty AS perassembly/;
			push @a, &include_assembly(
				$dbh, $myconfig, $form, $_->{id}, $flds, 
				$makemodeljoin);
			push @a, {id => $_->{id}, assemblyitem => 1};  
		}

		# copy assemblies to $form->{parts}
		@{ $form->{parts} } = @a;
    
	}
    
  
	@a = ();
	if (($form->{warehouse} ne "") || $form->{l_warehouse}) {
    
		if ($form->{warehouse} ne "") {
			my ($desc, $var) = split /--/, $form->{warehouse};
			$var = $dbh->quote($var);
			$desc = $dbh->quote($desc);
			$query = qq|
				SELECT SUM(qty) AS onhand, 
				       $desc AS description
				  FROM inventory
				 WHERE warehouse_id = $var
				       AND parts_id = ?|;
		} else {
			$query = qq|
				  SELECT SUM(i.qty) AS onhand, 
				         w.description AS warehouse
				    FROM inventory i
				    JOIN warehouse w ON (w.id = i.warehouse_id)
				   WHERE i.parts_id = ?
				GROUP BY w.description|;
		}

		$sth = $dbh->prepare($query) || $form->dberror($query);

		for (@{ $form->{parts} }) {

			$sth->execute($_->{id}) || $form->dberror($query);
      
 			if ($form->{warehouse} ne "") {
	
				$ref = $sth->fetchrow_hashref(NAME_lc);
				if ($ref->{onhand} != 0) {
					$_->{onhand} = $ref->{onhand};
					push @a, $_;
				}

			} else {

				push @a, $_;
	
				while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
					if ($ref->{onhand} > 0) {
						push @a, $ref;
					}
				}
			}
      
			$sth->finish;
		}

		@{ $form->{parts} } = @a;

	}

	$dbh->commit;

}


sub include_assembly {
	my ($dbh, $myconfig, $form, $id, $flds, $makemodeljoin) = @_;
  
	$form->{stagger}++;
	if ($form->{stagger} > $form->{pncol}) {
		$form->{pncol} = $form->{stagger};
	}
 
	$form->{$id} = 1;


	my @a = qw(partnumber description bin);
	if ($form->{sort} eq 'partnumber') {
 		$sortorder = "TRUE";
	} else {
		@a = grep !/$form->{sort}/, @a;
		$sortorder = "$form->{sort} $form->{direction}, ". join ',', @a;
	}
  
	@a = ();
	my $query = qq|
		   SELECT $flds
		     FROM parts p
		     JOIN assembly a ON (a.parts_id = p.id)
		LEFT JOIN partsgroup pg ON (pg.id = p.id)
 		LEFT JOIN chart c1 ON (c1.id = p.inventory_accno_id)
		LEFT JOIN chart c2 ON (c2.id = p.income_accno_id)
		LEFT JOIN chart c3 ON (c3.id = p.expense_accno_id)
		 $makemodeljoin
		    WHERE a.id = ?
		 ORDER BY $sortorder|;
	my $sth = $dbh->prepare($query);
	$sth->execute($id) || $form->dberror($query);
  
	while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
		$ref->{assemblyitem} = 1;
		$ref->{stagger} = $form->{stagger};
    
		push @a, $ref;
		if ($ref->{assembly} && !$form->{$ref->{id}}) {
			push @a, &include_assembly(
				$dbh, $myconfig, $form, $ref->{id}, $flds, 
				$makemodeljoin);
			if ($form->{stagger} > $form->{pncol}) {
				$form->{pncol} = $form->{stagger};
			}
		}
	}
	$sth->finish;

	$form->{$id} = 0;
	$form->{stagger}--;

	@a;

}


sub requirements {
	my ($self, $myconfig, $form) = @_;
	my $dbh = $form->{dbh};

	my $null;
	my $var;
	my $ref;
  
	my $where = qq|p.obsolete = '0'|;
	my $dwhere;

	for (qw(partnumber description)) {
		if ($form->{$_} ne "") {
			$var = $dbh->quote($form->like(lc $form->{$_}));
 			$where .= qq| AND lower(p.$_) LIKE $var|;
		}
	}
  
	if ($form->{partsgroup} ne "") {
		($null, $var) = split /--/, $form->{partsgroup};
		$var = $dbh->quote($var);
		$where .= qq| AND p.partsgroup_id = $var|;
	}

	# connect to database

	my ($transdatefrom, $transdateto);
	if ($form->{year}) {
		($transdatefrom, $transdateto) 
			= $form->from_to($form->{year}, '01', 12);
    
		$dwhere = qq| AND a.transdate >= '$transdatefrom'
 			AND a.transdate <= '$transdateto'|;
	}
    
	$query = qq|
		  SELECT p.id, p.partnumber, p.description, sum(i.qty) AS qty, 
		         p.onhand, extract(MONTH FROM a.transdate) AS month,
		         '0' AS so, '0' AS po
		    FROM invoice i
		    JOIN parts p ON (p.id = i.parts_id)
		    JOIN ar a ON (a.id = i.trans_id)
		   WHERE $where $dwhere AND p.inventory_accno_id > 0
		GROUP BY p.id, p.partnumber, p.description, p.onhand,
		         extract(MONTH FROM a.transdate)|;
	my $sth = $dbh->prepare($query);
	$sth->execute || $form->dberror($query);

	my %parts;
	while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
		$parts{$ref->{id}} = $ref;
	}
	$sth->finish;

	my %ofld = ( customer => so, vendor => po );
  
	for (qw(customer vendor)) {
		$query = qq|
			  SELECT p.id, p.partnumber, p.description, 
			         sum(qty) - sum(ship) AS $ofld{$_}, p.onhand,
			         0 AS month
			    FROM orderitems i
			    JOIN parts p ON (p.id = i.parts_id)
			    JOIN oe a ON (a.id = i.trans_id)
			   WHERE $where AND p.inventory_accno_id > 0
			         AND p.assembly = '0' AND a.closed = '0'
			         AND a.${_}_id > 0
			GROUP BY p.id, p.partnumber, p.description, p.onhand,
			         month|;
		$sth = $dbh->prepare($query);
		$sth->execute || $form->dberror($query);

		while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
			if (exists $parts{$ref->{id}}->{$ofld{$_}}) {
				$parts{$ref->{id}}->{$ofld{$_}} 
					+= $ref->{$ofld{$_}};
			} else {
				$parts{$ref->{id}} = $ref;
			}
		}
		$sth->finish;
	}

	# add assemblies from open sales orders
	$query = qq|
		SELECT DISTINCT a.id AS orderid, b.id, i.qty - i.ship AS qty
		  FROM parts p
		  JOIN assembly b ON (b.parts_id = p.id)
		  JOIN orderitems i ON (i.parts_id = b.id)
		  JOIN oe a ON (a.id = i.trans_id)
		 WHERE $where
		       AND (p.inventory_accno_id > 0 OR p.assembly = '1')
		       AND a.closed = '0'|;
	$sth = $dbh->prepare($query);
	$sth->execute || $form->dberror($query);

	while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
		&requirements_assembly(
			$dbh, $form, \%parts, $ref->{id}, $ref->{qty}, $where) 
				if $ref->{qty};
	}
	$sth->finish;

	$dbh->commit;

	for (sort { $parts{$a}->{$form->{sort}} cmp $parts{$b}->{$form->{sort}}}								keys %parts) {
		push @{ $form->{parts} }, $parts{$_};
	}
  
}


sub requirements_assembly {
	my ($dbh, $form, $parts, $id, $qty, $where) = @_;

	# assemblies
	my $query = qq|
		SELECT p.id, p.partnumber, p.description, a.qty * ? AS so, 
		       p.onhand, p.assembly, p.partsgroup_id
		  FROM assembly a 
		  JOIN parts p ON (p.id = a.parts_id)
		 WHERE $where AND a.id = ? AND p.inventory_accno_id > 0
		 
		 UNION
	  
		SELECT p.id, p.partnumber, p.description, a.qty * ? AS so, 
		       p.onhand, p.assembly, p.partsgroup_id
		  FROM assembly a
		  JOIN parts p ON (p.id = a.parts_id)
 		 WHERE a.id = ? AND p.assembly = '1'|;
		 
	my $sth = $dbh->prepare($query);
	$sth->execute($qty, $id, $qty, $id) || $form->dberror($query);

	while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
		if ($ref->{assembly}) {
			&requirements_assembly(
				$dbh, $form, $parts, $ref->{id}, $ref->{so}, 
				$where);
			next;
		}

		if (exists $parts->{$ref->{id}}{so}) {
			$parts->{$ref->{id}}{so} += $ref->{so};
		} else {
			$parts->{$ref->{id}} = $ref;
		}
	}
	$sth->finish;
    
}


sub create_links {
	my ($self, $module, $myconfig, $form) = @_;

	# connect to database
	my $dbh = $form->{dbh};
  
	my $ref;

	my $query = qq|
		SELECT accno, description, link
		  FROM chart
		 WHERE link LIKE ?
		 ORDER BY accno|;
	my $sth = $dbh->prepare($query);
	$sth->execute("%$module%") || $form->dberror($query);

	while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
		foreach my $key (split /:/, $ref->{link}) {
			if ($key =~ /$module/) {
				push @{ $form->{"${module}_links"}{$key} }, 
					{ accno => $ref->{accno},
					description => $ref->{description} };
			}
		}
	}
	$sth->finish;

	if ($form->{item} ne 'assembly') {
		$query = qq|SELECT count(*) FROM vendor|;
		my ($count) = $dbh->selectrow_array($query);

		if ($count < $myconfig->{vclimit}) {
			$query = qq|SELECT id, name FROM vendor ORDER BY name|;
			$sth = $dbh->prepare($query);
			$sth->execute || $form->dberror($query);

			while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
				push @{ $form->{all_vendor} }, $ref;
			}
			$sth->finish;
		}
	}

	# pricegroups, customers
	$query = qq|SELECT count(*) FROM customer|;
	($count) = $dbh->selectrow_array($query);

	if ($count < $myconfig->{vclimit}) {
		$query = qq|SELECT id, name FROM customer ORDER BY name|;
		$sth = $dbh->prepare($query);
		$sth->execute || $form->dberror($query);

		while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
			push @{ $form->{all_customer} }, $ref;
		}
		$sth->finish;
	}

	$query = qq|SELECT id, pricegroup FROM pricegroup ORDER BY pricegroup|;
	$sth = $dbh->prepare($query);
	$sth->execute || $form->dberror($query);

	while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
		push @{ $form->{all_pricegroup} }, $ref;
	}
	$sth->finish;


	if ($form->{id}) {
		$query = qq|
			SELECT value FROM defaults 
			 WHERE setting_key = 'weightunit'|;
		($form->{weightunit}) = $dbh->selectrow_array($query);
		$query = qq|
			SELECT value FROM defaults 
			 WHERE setting_key = 'curr'|;
		($form->{currencies}) = $dbh->selectrow_array($query);

	} else {
		# Dieter: FIXME left joins not working
		$query = qq|
			SELECT (SELECT value FROM defaults 
			         WHERE setting_key = 'weightunit') 
			       AS weightunit,  current_date AS priceupdate,
			       (SELECT value FROM defaults 
			         WHERE setting_key = 'curr') AS currencies,
			       c1.accno AS inventory_accno, 
			       c1.description AS inventory_description,
			       c2.accno AS income_accno, 
			       c2.description AS income_description,
			       c3.accno AS expense_accno, 
			       c3.description AS expense_description
			  FROM chart c1, chart c2, chart c3 
			 WHERE c1.id IN (SELECT value FROM defaults 
			 WHERE setting_key = 'inventory_accno_id')
			       AND c2.id IN (SELECT value FROM defaults
			 WHERE setting_key = 'income_accno_id')
			       AND c3.id IN (SELECT value FROM defaults
			                      WHERE setting_key 
			                            = 'expense_accno_id')|;
		$sth = $dbh->prepare($query);
		$sth->execute || $form->dberror($query);

		$ref = $sth->fetchrow_hashref(NAME_lc);
		for (qw(weightunit priceupdate currencies)) { 
			$form->{$_} = $ref->{$_};
		}
		# setup accno hash, {amount} is used in create_links
		for (qw(inventory income expense)) { 
			$form->{amount}{"IC_$_"} 
				= { 
					accno => $ref->{"${_}_accno"}, 
					description => $ref->{"${_}_description"} 
				}; 
		}
 
		$sth->finish;
	}
  
	$dbh->commit;

}


sub get_warehouses {
	my ($self, $myconfig, $form) = @_;

	my $dbh = $form->{dbh};

	my $query = qq|SELECT id, description FROM warehouse|;

	my $sth = $dbh->prepare($query);
	$sth->execute || $form->dberror($query);

	while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
		push @{ $form->{all_warehouse} }, $ref;
	}
	$sth->finish;

	$dbh->commit;

}

1;