#=====================================================================
# 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) 2001
#
#  Author: DWS Systems Inc.
#     Web: http://www.sql-ledger.org
#
#  Contributors:
#
#======================================================================
#
# This file has undergone  whitespace cleanup 
# 
#======================================================================
#
# Order entry module
# Quotation
#
#======================================================================

package OE;
use LedgerSMB::Tax;
use LedgerSMB::Sysconfig;


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

	# connect to database
	my $dbh = $form->{dbh};
 
	my $query;
	my $null;
	my $var;
	my $ordnumber = 'ordnumber';
	my $quotation = '0';
	my $department;
  
	my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';

	($form->{transdatefrom}, $form->{transdateto}) = 
		$form->from_to($form->{year}, $form->{month}, $form->{interval})
			if $form->{year} && $form->{month};

	if ($form->{type} =~ /_quotation$/) {
		$quotation = '1';
		$ordnumber = 'quonumber';
	}
  
	my $number = $form->like(lc $form->{$ordnumber});
	my $name = $form->like(lc $form->{$form->{vc}});
	my @dptargs = ();
  
	for (qw(department employee)) {
		if ($form->{$_}) {
			($null, $var) = split /--/, $form->{$_};
			$department .= " AND o.${_}_id = ?";
			push @dptargs, $var;
		}
	}

	if ($form->{vc} ne 'customer'){ # Sanitize $form->{vc}
		$form->{vc} = 'vendor';
	}
	my $query = qq|
		SELECT o.id, o.ordnumber, o.transdate, o.reqdate,
			o.amount, ct.name, o.netamount, o.$form->{vc}_id,
			ex.$rate AS exchangerate, o.closed, o.quonumber, 
			o.shippingpoint, o.shipvia, e.name AS employee, 
			m.name AS manager, o.curr, o.ponumber
		FROM oe o
		JOIN $form->{vc} ct ON (o.$form->{vc}_id = ct.id)
		LEFT JOIN employee e ON (o.employee_id = e.id)
		LEFT JOIN employee m ON (e.managerid = m.id)
		LEFT JOIN exchangerate ex 
			ON (ex.curr = o.curr AND ex.transdate = o.transdate)
		WHERE o.quotation = ?
		$department|;

	my @queryargs = @dptargs;
	unshift @queryargs, $quotation;

	my %ordinal = ( 
		id => 1,
		ordnumber => 2,
		transdate => 3,
		reqdate => 4,
		name => 6,
		quonumber => 11,
		shipvia => 13,
		employee => 14,
		manager => 15,
		curr => 16,
		ponumber => 17);

	my @a = (transdate, $ordnumber, name);
	push @a, "employee" if $form->{l_employee};
	if ($form->{type} !~ /(ship|receive)_order/) {
		push @a, "manager" if $form->{l_manager};
	}
	my $sortorder = $form->sort_order(\@a, \%ordinal);
  
 	# build query if type eq (ship|receive)_order
	if ($form->{type} =~ /(ship|receive)_order/) {
    
		my ($warehouse, $warehouse_id) = split /--/, $form->{warehouse};

		$query =  qq|
			SELECT DISTINCT o.id, o.ordnumber, o.transdate,
				o.reqdate, o.amount, ct.name, o.netamount, 
				o.$form->{vc}_id, ex.$rate AS exchangerate,
		 		o.closed, o.quonumber, o.shippingpoint, 
				o.shipvia, e.name AS employee, o.curr, 
				o.ponumber
			FROM oe o
			JOIN $form->{vc} ct ON (o.$form->{vc}_id = ct.id)
			JOIN orderitems oi ON (oi.trans_id = o.id)
			JOIN parts p ON (p.id = oi.parts_id)|;

		if ($warehouse_id && $form->{type} eq 'ship_order') {
			$query .= qq|
				JOIN inventory i ON (oi.parts_id = i.parts_id)
				|;
		}

		$query .= qq|
			LEFT JOIN employee e ON (o.employee_id = e.id)
			LEFT JOIN exchangerate ex 
				ON (ex.curr = o.curr 
					AND ex.transdate = o.transdate)
			WHERE o.quotation = '0'
			AND (p.inventory_accno_id > 0 OR p.assembly = '1')
			AND oi.qty != oi.ship
			$department|;
		@queryargs = @dptargs; #reset @queryargs
		 
		if ($warehouse_id && $form->{type} eq 'ship_order') {
			$query .= qq| 
				AND i.warehouse_id = ?
				AND ( 
					SELECT SUM(i.qty)
					FROM inventory i
					WHERE oi.parts_id = i.parts_id
					AND i.warehouse_id = ? 
				) > 0|;
			push(@queryargs, $warehouse_id, $warehouse_id);
		}

	}

	if ($form->{"$form->{vc}_id"}) {
		$query .= qq| AND o.$form->{vc}_id = $form->{"$form->{vc}_id"}|;
	} elsif ($form->{$form->{vc}} ne "") {
   	   	$query .= " AND lower(ct.name) LIKE ?";
    		push @queryargs, $name;
  	}	

	if ($form->{$ordnumber} ne "") {
		$query .= " AND lower(?) LIKE ?";
		push @queryargs, $ordnumber, $number;
		$form->{open} = 1;
		$form->{closed} = 1;
	}
	if ($form->{ponumber} ne "") {
		$query .= " AND lower(ponumber) LIKE ?";
		push @queryargs, $form->{ponumber};
	}

	if (!$form->{open} && !$form->{closed}) {
		$query .= " AND o.id = 0";
	} elsif (!($form->{open} && $form->{closed})) {
		$query .= ($form->{open}) ? 
			" AND o.closed = '0'" : " AND o.closed = '1'";
	}

	if ($form->{shipvia} ne "") {
		$var = $form->like(lc $form->{shipvia});
		$query .= " AND lower(o.shipvia) LIKE ?";
		push @queryargs, $var;
	}

	if ($form->{description} ne "") {
		$var = $form->like(lc $form->{description});
		$query .= " AND o.id IN (SELECT DISTINCT trans_id
                             FROM orderitems
			     WHERE lower(description) LIKE '$var')";
		push @queryargs, $var;
	}

	if ($form->{transdatefrom}) {
		$query .= " AND o.transdate >= ?";
		push @queryargs, $form->{transdatefrom};
	}
	if ($form->{transdateto}) {
		$query .= " AND o.transdate <= ?";
		push @queryargs, $form->{transdateto};
	}

	$query .= " ORDER by $sortorder";

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

	my %oid = ();
	while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
		$ref->{exchangerate} = 1 unless $ref->{exchangerate};
		if ($ref->{id} != $oid{id}{$ref->{id}}) {
			push @{ $form->{OE} }, $ref;
			$oid{vc}{$ref->{curr}}{$ref->{"$form->{vc}_id"}}++;
		}
		$oid{id}{$ref->{id}} = $ref->{id};
	}
	$sth->finish;

	$dbh->commit;

	if ($form->{type} =~ /^consolidate_/) {
		@a = ();
		foreach $ref (@{ $form->{OE} }) { 
			push @a, $ref if $oid{vc}{$ref->{curr}}
				{$ref->{"$form->{vc}_id"}} > 1;
		}

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

}



sub save {
	my ($self, $myconfig, $form) = @_;
 
	$form->db_prepare_vars("quonumber", "transdate", "vendor_id",
		"customer_id", "reqdate", "taxincluded", "shippingpoint",
		"shipvia", "currency", "department_id",
		"employee_id", "language_code", "ponumber", "terms");
	# connect to database, turn off autocommit
	my $dbh = $form->{dbh};
	my @queryargs;
	my $quotation;
	my $ordnumber;
	my $numberfld;
	$form->{vc} = ($form->{vc} eq 'customer') ? 'customer': 'vendor';
	if ($form->{type} =~ /_order$/) {
		$quotation = "0";
		$ordnumber = "ordnumber";
		$numberfld = ($form->{vc} eq 'customer') ? "sonumber" : 
			"ponumber";
	} else {
		$quotation = "1";
		$ordnumber = "quonumber";
		$numberfld = ($form->{vc} eq 'customer') ? "sqnumber" : 
			"rfqnumber";
	}

	$form->{"$ordnumber"} = $form->update_defaults(
		$myconfig, $numberfld, $dbh) 
			unless $form->{ordnumber}; 

	
	my $query;
	my $sth;
	my $null;
	my $exchangerate = 0;

	($null, $form->{employee_id}) = split /--/, $form->{employee};
	if (! $form->{employee_id}) {
		($form->{employee}, $form->{employee_id}) = 
			$form->get_employee($dbh);
		$form->{employee} = "$form->{employee}--$form->{employee_id}";
	}

	my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;

	$query = qq|
		SELECT p.assembly, p.project_id
		FROM parts p WHERE p.id = ?|;
	my $pth = $dbh->prepare($query) || $form->dberror($query);

 
	if ($form->{id}) {
		$query = qq|SELECT id FROM oe WHERE id = $form->{id}|;

		if ($dbh->selectrow_array($query)) {
			&adj_onhand($dbh, $form, $ml) 
				if $form->{type} =~ /_order$/;
      
			$query = qq|DELETE FROM orderitems WHERE trans_id = ?|;
			$sth = $dbh->prepare($query); 
			$sth->execute($form->{id}) || $form->dberror($query);

			$query = qq|DELETE FROM shipto WHERE trans_id = ?|;
			$sth = $dbh->prepare($query);
			$sth->execute($form->{id}) || $form->dberror($query);
      
		} else { # id is not in the database
			delete $form->{id}; 
		}
			
	}
 
	my $did_insert = 0;
	if (! $form->{id}) {
		$query = qq|SELECT nextval('id')|;
		$sth = $dbh->prepare($query);
		$sth->execute || $form->dberror($query);
		($form->{id}) = $sth->fetchrow_array;
		$sth->finish;
    
		my $uid = localtime;
		$uid .= "$$";
		if (!$form->{reqdate}){
			$form->{reqdate} = undef;
		}
		if (!$form->{transdate}){
			$form->{transdate} = "now";
		}
 
		if (($form->{closed} ne 't') and ($form->{closed} ne "1")){
			$form->{closed} = 'f';
		}
		# $form->{id} is safe because it is only pulled *from* the db.
		$query = qq|
			INSERT INTO oe 
				(id, ordnumber, quonumber, transdate, vendor_id,
				customer_id, reqdate, shippingpoint, shipvia,
				notes, intnotes, curr, closed, department_id,
				employee_id, language_code, ponumber, terms,
				quotation)
			VALUES 
				($form->{id}, ?, ?, ?, ?,
				?, ?, ?, ?,
				?, ?, ?, ?, ?,
				?, ?, ?, ?, ?)|;
		@queryargs = (
			$form->{ordnumber}, $form->{quonumber},
			$form->{transdate}, $form->{vendor_id},
			$form->{customer_id}, $form->{reqdate},
			$form->{shippingpoint}, $form->{shipvia},
			$form->{notes}, $form->{intnotes}, $form->{currency},
			$form->{closed}, $form->{department_id}, 
			$form->{employee_id}, $form->{language_code}, 
			$form->{ponumber}, $form->{terms}, $quotation);
		$sth = $dbh->prepare($query);
		$sth->execute(@queryargs) || $form->dberror($query);
		$sth->finish;
   
		@queries = $form->run_custom_queries('oe', 'INSERT');

	}

	my $amount;
	my $linetotal;
	my $discount;
	my $project_id;
	my $taxrate;
	my $taxamount;
	my $fxsellprice;
	my %taxbase;
	my @taxaccounts;
	my %taxaccounts;
	my $netamount = 0;

	my $rowcount = $form->{rowcount};
	for my $i (1 .. $rowcount) {
		$form->db_prepare_vars("orderitems_id_$i", "id_$i", 
			"description_$i", "project_id_$i", "ship_$i");

		for (qw(qty ship)) { 
			$form->{"${_}_$i"} = $form->parse_amount(
				$myconfig, $form->{"${_}_$i"}
			); 
		}
     
		$form->{"discount_$i"} = $form->parse_amount(
			$myconfig, $form->{"discount_$i"}
			) / 100;

		$form->{"sellprice_$i"} = $form->parse_amount(
			$myconfig, $form->{"sellprice_$i"}
			);
 
		if ($form->{"qty_$i"}) {
			$pth->execute($form->{"id_$i"});
			$ref = $pth->fetchrow_hashref(NAME_lc);
			for (keys %$ref) { $form->{"${_}_$i"} = $ref->{$_} }
			$pth->finish;

			$fxsellprice = $form->{"sellprice_$i"};

			my ($dec) = ($form->{"sellprice_$i"} =~ /\.(\d+)/);
			$dec = length $dec;
			my $decimalplaces = ($dec > 2) ? $dec : 2;

			$discount = $form->round_amount(
				$form->{"sellprice_$i"} * 
					$form->{"discount_$i"}, 
				$decimalplaces
				);
			$form->{"sellprice_$i"} = $form->round_amount(
				$form->{"sellprice_$i"} - $discount, 
				$decimalplaces
				);

			$linetotal = $form->round_amount(
				$form->{"sellprice_$i"} * $form->{"qty_$i"}, 2
			);
      
			@taxaccounts = Tax::init_taxes($form,
				$form->{"taxaccounts_$i"});
			if ($form->{taxincluded}) {
				$taxamount = Tax::calculate_taxes(\@taxaccounts,
					$form, $linetotal, 1);
				$form->{"sellprice_$i"} = Tax::extract_taxes(\@taxaccounts,
					$form, $form->{"sellprice_$i"});
				$taxbase = Tax::extract_taxes(\@taxaccounts, 
					$form, $linetotal);
			} else {
				$taxamount = Tax::apply_taxes(\@taxaccounts,
					$form, $linetotal);
				$taxbase = $linetotal;
			}
 
 			if (@taxaccounts && $form->round_amount($taxamount, 2) 
 					== 0) {
 				if ($form->{taxincluded}) {
					foreach $item (@taxaccounts) {
						$taxamount = 
							$form->round_amount(
							$item->value, 2);
						$taxaccounts{$item->account} +=
							$taxamount;
						$taxdiff += $taxamount;
						$taxbase{$item->account} +=
							$taxbase;
					}
					$taxaccounts{$taxaccounts[0]->account} 
						+= $taxdiff;
 				} else {
					foreach $item (@taxaccounts) {
						$taxaccounts{$item->account} +=
							$item->value;
						$taxbase{$item->account} +=
							$taxbase;
					}
				}
			} else {
				foreach $item (@taxaccounts) {
					$taxaccounts{$item->account} += 
						$item->value;
					$taxbase{$item->account} += $taxbase;
				}
			}

			$netamount += $form->{"sellprice_$i"} 
				* $form->{"qty_$i"};
      
			if ($form->{"projectnumber_$i"} ne "") {
				($null, $project_id) 
					= split /--/, 
						$form->{"projectnumber_$i"};
      			}
			$project_id = $form->{"project_id_$i"} 
				if $form->{"project_id_$i"};

			if (!$form->{"reqdate_$i"}){
				$form->{"reqdate_$i"} = undef;
			}

			@queryargs = ();
			# save detail record in orderitems table
			$query = qq|INSERT INTO orderitems (|;
			if ($form->{"orderitems_id_$i"}){
				$query .= "id, ";
			}
			$query .= qq|
				trans_id, parts_id, description, qty, sellprice,
				discount, unit, reqdate, project_id, ship, 
				serialnumber, notes)
                   		VALUES (|;
			if ($form->{"orderitems_id_$i"}){
				$query .= "?, ";
				push @queryargs, $form->{"orderitems_id_$i"};
			}
      			$query .= qq| ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
			$sth = $dbh->prepare($query);
			push (@queryargs, 
				$form->{id}, $form->{"id_$i"},
				$form->{"description_$i"}, $form->{"qty_$i"},
				$fxsellprice, $form->{"discount_$i"},
				$form->{"unit_$i"}, $form->{"reqdate_$i"},
				$project_id, $form->{"ship_$i"}, 
				$form->{"serialnumber_$i"}, 
				$form->{"notes_$i"});
			$sth->execute(@queryargs) || $form->dberror($query);

			$form->{"sellprice_$i"} = $fxsellprice;
		}
		$form->{"discount_$i"} *= 100;
	}


	# set values which could be empty
	for (qw(vendor_id customer_id taxincluded closed quotation)) 
		{ $form->{$_} *= 1 }

	# add up the tax
	my $tax = 0;
	for (keys %taxaccounts) { $tax += $taxaccounts{$_} }
  
	$amount = $form->round_amount($netamount + $tax, 2);
	$netamount = $form->round_amount($netamount, 2);

	if ($form->{currency} eq $form->{defaultcurrency}) {
		$form->{exchangerate} = 1;
	} else {
		$exchangerate = $form->check_exchangerate(
			$myconfig, $form->{currency}, $form->{transdate}, 
			($form->{vc} eq 'customer') ? 'buy' : 'sell');
	}
  
	$form->{exchangerate} = ($exchangerate) ? $exchangerate : 
		$form->parse_amount($myconfig, $form->{exchangerate});
  
  
	($null, $form->{department_id}) = split(/--/, $form->{department});
	for (qw(department_id terms)) { $form->{$_} *= 1 }
	if ($did_insert){
		$query = qq|
			UPDATE oe SET 
				amount = ?,
				netamount = ?,
				taxincluded = ?
			WHERE id = ?|;
		@queryargs = ($amount, $netamount, $form->{taxincluded},
				$form->{id});
	} else {
		# save OE record
		$query = qq|
			UPDATE oe set
				ordnumber = ?, 
				quonumber = ?,
				transdate = ?,
				vendor_id = ?,
				customer_id = ?, 
				amount = ?, 
				netamount = ?,
				reqdate = ?,
				taxincluded = ?, 
				shippingpoint = ?, 
				shipvia = ?, 
				notes = ?, 
				intnotes = ?, 
				curr = ?, 
				closed = ?, 
				quotation = ?, 
				department_id = ?, 
				employee_id = ?, 
				language_code = ?, 
				ponumber = ?, 
				terms = ?
			WHERE id = ?|;

		if (!$form->{reqdate}){
			$form->{reqdate} = undef;
		}
	
		@queryargs = ($form->{ordnumber},
			$form->{quonumber}, 
			$form->{transdate}, 
			$form->{vendor_id},
			$form->{customer_id}, 
			$amount, 
			$netamount, 
			$form->{reqdate},
			$form->{taxincluded}, 
			$form->{shippingpoint}, 
			$form->{shipvia},
			$form->{notes}, 
			$form->{intnotes}, 
			$form->{currency},
			$form->{closed}, 
			$quotation, 
			$form->{department_id},
			$form->{employee_id}, 
			$form->{language_code},
			$form->{ponumber}, 
			$form->{terms}, 
			$form->{id});
	}
	$sth = $dbh->prepare($query);
	$sth->execute(@queryargs) || $form->dberror($query);

	if (!$did_insert){
		@queries = $form->run_custom_queries('oe', 'UPDATE');
	}


	$form->{ordtotal} = $amount;

	# add shipto
	$form->{name} = $form->{$form->{vc}};
	$form->{name} =~ s/--$form->{"$form->{vc}_id"}//;
	$form->add_shipto($dbh, $form->{id});

	# save printed, emailed, queued
	$form->save_status($dbh); 
    
	if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
		if ($form->{vc} eq 'customer') {
 			$form->update_exchangerate(
					$dbh, 
					$form->{currency}, 
					$form->{transdate}, 
					$form->{exchangerate}, 
					0);
		}
		if ($form->{vc} eq 'vendor') {
			$form->update_exchangerate(
					$dbh, 
					$form->{currency}, 
					$form->{transdate}, 
					0, 
					$form->{exchangerate});
		}
	}
  

	if ($form->{type} =~ /_order$/) {
		# adjust onhand
		&adj_onhand($dbh, $form, $ml * -1);
		&adj_inventory($dbh, $myconfig, $form);
	}

		my %audittrail = ( 
			tablename	=> 'oe',
			reference	=> ($form->{type} =~ /_order$/) 
				? $form->{ordnumber} : $form->{quonumber},
			formname	=> $form->{type},
			action		=> 'saved',
			id		=> $form->{id} );

	$form->audittrail($dbh, "", \%audittrail);

	$form->save_recurring($dbh, $myconfig);
  
	my $rc = $dbh->commit;

	$rc;
}



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

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

	# delete spool files
	my $query = qq|
		SELECT spoolfile FROM status
		WHERE trans_id = ?
		AND spoolfile IS NOT NULL|;
	$sth = $dbh->prepare($query);
	$sth->execute($form->{id}) || $form->dberror($query);

	my $spoolfile;
	my @spoolfiles = ();

	while (($spoolfile) = $sth->fetchrow_array) {
		push @spoolfiles, $spoolfile;
	}
	$sth->finish;


	$query = qq|
		SELECT o.parts_id, o.ship, p.inventory_accno_id, p.assembly
		FROM orderitems o
		JOIN parts p ON (p.id = o.parts_id)
		WHERE trans_id = ?|;
	$sth = $dbh->prepare($query);
	$sth->execute($form->{id}) || $form->dberror($query);

	if ($form->{type} =~ /_order$/) {
		$ml = ($form->{type} eq 'purchase_order') ? -1 : 1;
		while (my ($id, $ship, $inv, $assembly) 
				= $sth->fetchrow_array) {
			$form->update_balance(
				$dbh,
				"parts",
				"onhand",
				"id = $id",
				$ship * $ml) 
					if ($inv || $assembly);
		}
	}
	$sth->finish;

	# delete inventory
	$query = qq|DELETE FROM inventory WHERE trans_id = ?|;
	$sth = $dbh->prepare($query);
	$sth->execute($form->{id}) || $form->dberror($query);
	$sth->finish;
  
	# delete status entries
	$query = qq|DELETE FROM status WHERE trans_id = ?|;
	$sth = $dbh->prepare($query);
	$sth->execute($form->{id}) || $form->dberror($query);
	$sth->finish;
  
	# delete OE record
	$query = qq|DELETE FROM oe WHERE id = ?|;
	$sth = $dbh->prepare($query);
	$sth->execute($form->{id}) || $form->dberror($query);
	$sth->finish;

	# delete individual entries
	$query = qq|DELETE FROM orderitems WHERE trans_id = ?|;
	$sth->finish;

	$query = qq|DELETE FROM shipto WHERE trans_id = ?|;
	$sth = $dbh->prepare($query);
	$sth->execute($form->{id}) || $form->dberror($query);
	$sth->finish;
  
	my %audittrail = ( 
			tablename	=> 'oe',
			reference	=> ($form->{type} =~ /_order$/) 
				? $form->{ordnumber} : $form->{quonumber},
			formname	=> $form->{type},
		     	action		=> 'deleted',
		     	id		=> $form->{id} );

	$form->audittrail($dbh, "", \%audittrail);
  
	my $rc = $dbh->commit;

	if ($rc) {
		foreach $spoolfile (@spoolfiles) {
			unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile" if $spoolfile;
		}
  	}
  
	$rc;
  
}



sub retrieve {
	use LedgerSMB::PriceMatrix;
	my ($self, $myconfig, $form) = @_;
  
  # connect to database
	my $dbh = $form->{dbh};

	my $query;
	my $sth;
	my $var;
	my $ref;

	$query = qq|
		SELECT value, current_date FROM defaults
		 WHERE setting_key = 'curr'|;
	($form->{currencies}, $form->{transdate}) = 
		$dbh->selectrow_array($query);
  
	if ($form->{id}) {
    
		# retrieve order
		$query = qq|
			SELECT o.ordnumber, o.transdate, o.reqdate, o.terms,
                		o.taxincluded, o.shippingpoint, o.shipvia, 
				o.notes, o.intnotes, o.curr AS currency, 
				e.name AS employee, o.employee_id,
				o.$form->{vc}_id, vc.name AS $form->{vc}, 
				o.amount AS invtotal, o.closed, o.reqdate, 
				o.quonumber, o.department_id, 
				d.description AS department, o.language_code, 
				o.ponumber
			FROM oe o
			JOIN $form->{vc} vc ON (o.$form->{vc}_id = vc.id)
			LEFT JOIN employee e ON (o.employee_id = e.id)
			LEFT JOIN department d ON (o.department_id = d.id)
			WHERE o.id = ?|;
		$sth = $dbh->prepare($query);
		$sth->execute($form->{id}) || $form->dberror($query);

		$ref = $sth->fetchrow_hashref(NAME_lc);
		for (keys %$ref) { $form->{$_} = $ref->{$_} }
		$sth->finish;
   
		$query = qq|SELECT * FROM shipto WHERE trans_id = ?|;
		$sth = $dbh->prepare($query);
		$sth->execute($form->{id}) || $form->dberror($query);

		$ref = $sth->fetchrow_hashref(NAME_lc);
		for (keys %$ref) { $form->{$_} = $ref->{$_} }
		$sth->finish;

		# get printed, emailed and queued
		$query = qq|
			SELECT s.printed, s.emailed, s.spoolfile, s.formname
			FROM status s
			WHERE s.trans_id = ?|;
		$sth = $dbh->prepare($query);
		$sth->execute($form->{id}) || $form->dberror($query);

		while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
			$form->{printed} .= "$ref->{formname} " 
				if $ref->{printed};
			$form->{emailed} .= "$ref->{formname} " 
				if $ref->{emailed};
			$form->{queued} .= "$ref->{formname} $ref->{spoolfile} "
				if $ref->{spoolfile};
		}
		$sth->finish;
		for (qw(printed emailed queued)) { $form->{$_} =~ s/ +$//g }


		# retrieve individual items
		$query = qq|
			SELECT o.id AS orderitems_id, p.partnumber, p.assembly, 
				o.description, o.qty, o.sellprice, 
				o.parts_id AS id, o.unit, o.discount, p.bin,
				o.reqdate, o.project_id, o.ship, o.serialnumber,
				o.notes, pr.projectnumber, pg.partsgroup, 
				p.partsgroup_id, p.partnumber AS sku,
				p.listprice, p.lastcost, p.weight, p.onhand,
				p.inventory_accno_id, p.income_accno_id, 
				p.expense_accno_id, t.description 
					AS partsgrouptranslation
			FROM orderitems o
			JOIN parts p ON (o.parts_id = p.id)
			LEFT JOIN project pr ON (o.project_id = pr.id)
			LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
			LEFT JOIN translation t 
				ON (t.trans_id = p.partsgroup_id 
					AND t.language_code = ?)
			WHERE o.trans_id = ?
			ORDER BY o.id|;
		$sth = $dbh->prepare($query);
		$sth->execute($form->{language_code}, $form->{id}) 
			|| $form->dberror($query);

		# foreign exchange rates
		&exchangerate_defaults($dbh, $form);

		# query for price matrix
		my $pmh = PriceMatrix::price_matrix_query($dbh, $form);
    
		# taxes
		$query = qq|
			SELECT c.accno FROM chart c
			JOIN partstax pt ON (pt.chart_id = c.id)
			WHERE pt.parts_id = ?|;
		my $tth = $dbh->prepare($query) || $form->dberror($query);
   
		my $taxrate;
		my $ptref;
		my $sellprice;
		my $listprice;
    
		while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

			($decimalplaces) = ($ref->{sellprice} =~ /\.(\d+)/);
			$decimalplaces = length $decimalplaces;
			$decimalplaces = ($decimalplaces > 2) ? 
				$decimalplaces : 2;

			$tth->execute($ref->{id});
			$ref->{taxaccounts} = "";
			$taxrate = 0;
      
			while ($ptref = $tth->fetchrow_hashref(NAME_lc)) {
				$ref->{taxaccounts} .= "$ptref->{accno} ";
				$taxrate += $form->{"$ptref->{accno}_rate"};
			}
			$tth->finish;
			chop $ref->{taxaccounts};

			# preserve price
			$sellprice = $ref->{sellprice};
      
			# multiply by exchangerate
			$ref->{sellprice} = $form->round_amount(
				$ref->{sellprice} * $form->{$form->{currency}}, 
				$decimalplaces
				);
      
			for (qw(listprice lastcost)) { 
				$ref->{$_} = $form->round_amount(
					$ref->{$_} / $form->{$form->{currency}},
					$decimalplaces
					); 
			}
      
			# partnumber and price matrix
			PriceMatrix::price_matrix(
				$pmh, $ref, $form->{transdate}, $decimalplaces, 
				$form, $myconfig);

			$ref->{sellprice} = $sellprice;

			$ref->{partsgroup} = $ref->{partsgrouptranslation} 
				if $ref->{partsgrouptranslation};
      
			push @{ $form->{form_details} }, $ref;
      
		}
		$sth->finish;

		# get recurring transaction
		$form->get_recurring;

		@queries = $form->run_custom_queries('oe', 'SELECT');
		$form->{dbh}->commit;
	} else {

		# get last name used
		$form->lastname_used($myconfig, $dbh, $form->{vc}) 
			unless $form->{"$form->{vc}_id"};

		delete $form->{notes};

	}

	$dbh->commit;

}


sub exchangerate_defaults {
	my ($dbh2, $form) = @_;
	$dbh = $form->{dbh};
	my $var;
	my $buysell = ($form->{vc} eq "customer") ? "buy" : "sell";
  
	# get default currencies
	my $query = qq|
		SELECT substr(value,1,3), value FROM defaults
		 WHERE setting_key = 'curr'|;
	($form->{defaultcurrency}, $form->{currencies}) 
		= $dbh->selectrow_array($query);

	$query = qq|
		SELECT $buysell
		FROM exchangerate
		WHERE curr = ?
		AND transdate = ?|;
	my $eth1 = $dbh->prepare($query) || $form->dberror($query);
	$query = qq~
		SELECT max(transdate || ' ' || $buysell || ' ' || curr)
		FROM exchangerate
		WHERE curr = ?~;
	my $eth2 = $dbh->prepare($query) || $form->dberror($query);

	# get exchange rates for transdate or max
	foreach $var (split /:/, substr($form->{currencies},4)) {
		$eth1->execute($var, $form->{transdate});
		($form->{$var}) = $eth1->fetchrow_array;
		if (! $form->{$var} ) {
			$eth2->execute($var);
      
			($form->{$var}) = $eth2->fetchrow_array;
			($null, $form->{$var}) = split / /, $form->{$var};
			$form->{$var} = 1 unless $form->{$var};
			$eth2->finish;
		}
		$eth1->finish;
	}

	$form->{$form->{currency}} = $form->{exchangerate} 
		if $form->{exchangerate};
	$form->{$form->{currency}} ||= 1;
	$form->{$form->{defaultcurrency}} = 1;
      
}


sub order_details {
	use LedgerSMB::CP;
	my ($self, $myconfig, $form) = @_;

	# connect to database
	my $dbh = $form->{dbh};
	my $query;
	my $sth;
    
	my $item;
	my $i;
	my @sortlist = ();
	my $projectnumber;
	my $projectdescription;
	my $projectnumber_id;
	my $translation;
	my $partsgroup;

	my @queryargs;
  
	my @taxaccounts;
	my %taxaccounts; # I don't think this works.
	my $tax;
	my $taxrate;
	my $taxamount;

	my %translations;

	my $language_code = $form->{dbh}->quote($form->{language_code});
	$query = qq|
		SELECT p.description, t.description
		FROM project p
		LEFT JOIN translation t ON (t.trans_id = p.id AND 
			t.language_code = $language_code)
	       WHERE id = ?|;
	my $prh = $dbh->prepare($query) || $form->dberror($query);

	$query = qq|
		SELECT inventory_accno_id, income_accno_id,
		expense_accno_id, assembly FROM parts
		WHERE id = ?|;
	my $pth = $dbh->prepare($query) || $form->dberror($query);
  
	my $sortby;
  
	# sort items by project and partsgroup
	for $i (1 .. $form->{rowcount}) {

		if ($form->{"id_$i"}) {
			# account numbers
			$pth->execute($form->{"id_$i"});
			$ref = $pth->fetchrow_hashref(NAME_lc);
			for (keys %$ref) { $form->{"${_}_$i"} = $ref->{$_} }
			$pth->finish;

			$projectnumber_id = 0;
			$projectnumber = "";
			$form->{partsgroup} = "";
			$form->{projectnumber} = "";

			if ($form->{groupprojectnumber} 
					|| $form->{grouppartsgroup}) {

				$inventory_accno_id = 
					($form->{"inventory_accno_id_$i"} || 
					$form->{"assembly_$i"}) ? "1" : "";

				if ($form->{groupprojectnumber}) {
					($projectnumber, $projectnumber_id) = 
						split /--/, 
						$form->{"projectnumber_$i"};
				}
				if ($form->{grouppartsgroup}) {
					($form->{partsgroup}) = split /--/, 
						$form->{"partsgroup_$i"};
				}

				if ($projectnumber_id && 
						$form->{groupprojectnumber}) {
					if ($translation{$projectnumber_id}) {
						$form->{projectnumber} = 
							$translation{$projectnumber_id};
					} else {
						# get project description
						$prh->execute(
							$projectnumber_id);
	    					($projectdescription, 
							$translation) = 
							$prh->fetchrow_array;
						$prh->finish;

						$form->{projectnumber} =
							($translation) ? 
							"$projectnumber, \n" .
							"$translation" 
							: "$projectnumber, \n" .
							"$projectdescription";

						$translation{$projectnumber_id}
							= $form->{projectnumber};
					}
				}

				if ($form->{grouppartsgroup} 
						&& $form->{partsgroup}) {
					$form->{projectnumber} .= " / " 
						if $projectnumber_id;
	  				$form->{projectnumber} .= 
						$form->{partsgroup};
				}

				$form->format_string(projectnumber);

			}

			$sortby = qq|$projectnumber$form->{partsgroup}|;

			if ($form->{sortby} ne 'runningnumber') {
				for (qw(partnumber description bin)) {
					$sortby .= $form->{"${_}_$i"} 
						if $form->{sortby} eq $_;
				}
			}

			push @sortlist, [ $i, 
				"$projectnumber$form->{partsgroup}".
					"$inventory_accno_id", 
				$form->{projectnumber}, $projectnumber_id, 
				$form->{partsgroup}, $sortby ];
		}

	}

	delete $form->{projectnumber};
  
	# sort the whole thing by project and group
	@sortlist = sort { $a->[5] cmp $b->[5] } @sortlist;
  

	# if there is a warehouse limit picking
	if ($form->{warehouse_id} && $form->{formname} =~ 
			/(pick|packing)_list/) {
		# run query to check for inventory
		$query = qq|
			SELECT sum(qty) AS qty FROM inventory
			WHERE parts_id = ? AND warehouse_id = ?|;
		$sth = $dbh->prepare($query) || $form->dberror($query);

		for $i (1 .. $form->{rowcount}) {
			$sth->execute($form->{"id_$i"}, $form->{warehouse_id}) 
				|| $form->dberror;

			($qty) = $sth->fetchrow_array;
			$sth->finish;

			$form->{"qty_$i"} = 0 if $qty == 0;
      
			if ($form->parse_amount($myconfig, $form->{"ship_$i"}) 
				> $qty) {
				$form->{"ship_$i"} = 
					$form->format_amount($myconfig, $qty);
			}
		}
	}
    

	my $runningnumber = 1;
	my $sameitem = "";
	my $subtotal;
	my $k = scalar @sortlist;
	my $j = 0;

	foreach $item (@sortlist) {
		$i = $item->[0];
		$j++;

		if ($form->{groupprojectnumber} || $form->{grouppartsgroup}) {
			if ($item->[1] ne $sameitem) {
				$sameitem = $item->[1];
	
				$ok = 0;
	
				if ($form->{groupprojectnumber}) {
					$ok = $form->{"projectnumber_$i"};
				}
				if ($form->{grouppartsgroup}) {
					$ok = $form->{"partsgroup_$i"} 
						unless $ok;
				}

				if ($ok) {
					if ($form->{"inventory_accno_id_$i"} 
						|| $form->{"assembly_$i"}) {

						push(@{ $form->{part} }, "");
						push(@{ $form->{service} }, 
							NULL);
					} else {
						push(@{ $form->{part} }, NULL);
						push(@{ $form->{service} }, "");
					}

					push(@{ $form->{description} }, 
						$item->[2]);
					for (
						qw(taxrates runningnumber 
						number sku qty ship unit bin 
						serialnumber requiredate 
						projectnumber sellprice 
						listprice netprice discount 
						discountrate linetotal weight 
						itemnotes)
					) { 
						push(@{ $form->{$_} }, "");
					}
					push(@{ $form->{lineitems} }, 
						{ amount => 0, tax => 0 });
				}
			}
		}


		$form->{"qty_$i"} = $form->parse_amount(
			$myconfig, $form->{"qty_$i"});
		$form->{"ship_$i"} = $form->parse_amount(
			$myconfig, $form->{"ship_$i"});
    
		if ($form->{"qty_$i"}) {

			$form->{totalqty} += $form->{"qty_$i"};
			$form->{totalship} += $form->{"ship_$i"};
			$form->{totalweight} += ($form->{"weight_$i"} 
				* $form->{"qty_$i"});
			$form->{totalweightship} += ($form->{"weight_$i"} 
				* $form->{"ship_$i"});

			# add number, description and qty to $form->{number}
			push(@{ $form->{runningnumber} }, $runningnumber++);
			push(@{ $form->{number} }, 
				qq|$form->{"partnumber_$i"}|);
			push(@{ $form->{sku} }, qq|$form->{"sku_$i"}|);
			push(@{ $form->{description} }, 
				qq|$form->{"description_$i"}|);
			push(@{ $form->{itemnotes} }, $form->{"notes_$i"});
			push(@{ $form->{qty} }, $form->format_amount(
				$myconfig, $form->{"qty_$i"}));
			push(@{ $form->{ship} }, $form->format_amount(
				$myconfig, $form->{"ship_$i"}));
			push(@{ $form->{unit} }, qq|$form->{"unit_$i"}|);
			push(@{ $form->{bin} }, qq|$form->{"bin_$i"}|);
			push(@{ $form->{serialnumber} }, 
				qq|$form->{"serialnumber_$i"}|);
			push(@{ $form->{requiredate} }, 
				qq|$form->{"reqdate_$i"}|);
			push(@{ $form->{projectnumber} }, 
				qq|$form->{"projectnumber_$i"}|);
      
			push(@{ $form->{sellprice} }, $form->{"sellprice_$i"});
 
			push(@{ $form->{listprice} }, $form->{"listprice_$i"});
      
			push(@{ $form->{weight} }, $form->format_amount(
				$myconfig, 
				$form->{"weight_$i"} * $form->{"ship_$i"}));

			my $sellprice = $form->parse_amount(
				$myconfig, $form->{"sellprice_$i"});
			my ($dec) = ($sellprice =~ /\.(\d+)/);
			$dec = length $dec;
			my $decimalplaces = ($dec > 2) ? $dec : 2;
      
			my $discount = $form->round_amount(
				$sellprice * $form->parse_amount(
					$myconfig, 
					$form->{"discount_$i"}) / 100, 
					$decimalplaces);

			# keep a netprice as well, (sellprice - discount)
			$form->{"netprice_$i"} = $sellprice - $discount;

			my $linetotal = $form->round_amount(
				$form->{"qty_$i"} * $form->{"netprice_$i"}, 2);

			if ($form->{"inventory_accno_id_$i"} 
						|| $form->{"assembly_$i"}) {

				push(@{ $form->{part} }, $form->{"sku_$i"});
				push(@{ $form->{service} }, NULL);
				$form->{totalparts} += $linetotal;
			} else {
				push(@{ $form->{service} }, $form->{"sku_$i"});
				push(@{ $form->{part} }, NULL);
				$form->{totalservices} += $linetotal;
			}

			push(@{ $form->{netprice} }, 
				($form->{"netprice_$i"})
				? $form->format_amount(
					$myconfig, 
					$form->{"netprice_$i"}, 
					$decimalplaces) 
				: " ");
      
			$discount = ($discount) 
				? $form->format_amount(
					$myconfig, 
					$discount * -1, 
					$decimalplaces) 
				: " ";

			push(@{ $form->{discount} }, $discount);
 			push(@{ $form->{discountrate} }, 
				$form->format_amount($myconfig, 
				$form->{"discount_$i"}));
      
			$form->{ordtotal} += $linetotal;

			# this is for the subtotals for grouping
			$subtotal += $linetotal;

			$form->{"linetotal_$i"} = $form->format_amount(
				$myconfig, $linetotal, 2);
			push(@{ $form->{linetotal} }, $form->{"linetotal_$i"});
      
			@taxaccounts = Tax::init_taxes($form, 
				$form->{"taxaccounts_$i"});

			my $ml = 1;
			my @taxrates = ();
      
			$tax = 0;
      
			$taxamount = Tax::calculate_taxes(\@taxaccounts,
				$form, $linetotal, 1);
			$taxbase = Tax::extract_taxes(\@taxaccounts,
				$form, $linetotal);
			foreach $item (@taxaccounts) {
				push @taxrates, Math::BigFloat->new(100) *
					$item->rate;
				if ($form->{taxincluded}) {
					$taxaccounts{$item->account} += 
						$item->value;
					$taxbase{$item->account} += $taxbase;
				} else {
					Tax::apply_taxes(\@taxaccounts, $form,
						$linetotal);
					$taxbase{$item->account} += $linetotal;
					$taxaccounts{$item->account} += 
						$item->value;
				}
			}
			if ($form->{taxincluded}) {
				$tax += Tax::calculate_taxes(\@taxaccounts,
					$form, $linetotal, 1);
			} else {
				$tax += Tax::calculate_taxes(\@taxaccounts, 
					$form, $linetotal, 0);
			}

			push(@{ $form->{lineitems} }, 
				{ amount => $linetotal, 
				tax => $form->round_amount($tax, 2) });
			push(@{ $form->{taxrates} }, 
				join ' ', sort { $a <=> $b } @taxrates);
	
			if ($form->{"assembly_$i"}) {
				$form->{stagger} = -1;
				&assembly_details($myconfig, 
					$form, $dbh, $form->{"id_$i"}, 
					$oid{$myconfig->{dbdriver}}, 
					$form->{"qty_$i"});
			}

		}

		# add subtotal
		if ($form->{groupprojectnumber} || $form->{grouppartsgroup}) {
			if ($subtotal) {
				if ($j < $k) {
					# look at next item
					if ($sortlist[$j]->[1] ne $sameitem) {

						if ($form->{"inventory_accno_id_$i"} 
						|| $form->{"assembly_$i"}) {

							push(@{ $form->{part} },
								"");
							push(@{ 
								$form->{service}
								}, NULL);
						} else {
							push(@{ 
								$form->{service}
								}, "");
							push(@{ $form->{part} },
								NULL);
						}

						for (qw(
							taxrates runningnumber 
							number sku qty ship unit
							bin serialnumber 
							requiredate 
							projectnumber sellprice 
							listprice netprice 
							discount discountrate 
							weight itemnotes)
						) { 

							push(@{ $form->{$_} }, 
								""); 
						}

						push(@{ $form->{description} },
							$form->{groupsubtotaldescription});

						push(@{ $form->{lineitems} }, 
							{ amount => 0, 
								tax => 0 });

						if ($form->{groupsubtotaldescription} 
						ne "") {
							push(@{ 
								$form->{linetotal} 
								}, 
								$form->format_amount($myconfig, $subtotal, 2));
						} else {
							push(@{ 
								$form->{linetotal} 
								}, "");
						}
						$subtotal = 0;
					}

				} else {

					# got last item
					if ($form->{groupsubtotaldescription} 
					ne "") {
	    
						if ($form->{"inventory_accno_id_$i"} 
						|| $form->{"assembly_$i"}) {
							push(@{ $form->{part} },
								"");
							push(@{ 
								$form->{service}
								}, NULL);
						} else { 
							push(@{ 
								$form->{service}
								}, "");
							push(@{ $form->{part} },
								NULL);
						} 
	    
						for (qw(
							taxrates runningnumber 
							number sku qty ship unit
							bin serialnumber 
							requiredate 
							projectnumber sellprice 
							listprice netprice 
							discount discountrate 
							weight itemnotes)
						) { 

							push(@{ $form->{$_} }, 
								"");
						}

						push(@{ $form->{description} }, 
							$form->{groupsubtotaldescription});

						push(@{ $form->{linetotal} }, 
							$form->format_amount(
								$myconfig, 
								$subtotal, 
							2));
						push(@{ $form->{lineitems} }, 
							{ amount => 0, 
								tax => 0 });
					}
				}
			}
		}
	}


	$tax = 0;

	foreach $item (sort keys %taxaccounts) {
		if ($form->round_amount($taxaccounts{$item}, 2)) {
			$tax += $taxamount = $form->round_amount(
				$taxaccounts{$item}, 2);

			push(@{ $form->{taxbaseinclusive} }, 
				$form->{"${item}_taxbaseinclusive"} 
					= $form->round_amount(
						$taxbase{$item} + $tax, 2));
			push(@{ $form->{taxbase} }, 
				$form->{"${item}_taxbase"} 
					= $form->format_amount($myconfig, 
						$taxbase{$item}, 2));
			push(@{ $form->{tax} }, 
				$form->{"${item}_tax"} 
					= $form->format_amount($myconfig, 
						$taxamount, 2));
      
			push(@{ $form->{taxdescription} }, 
				$form->{"${item}_description"});
      
			$form->{"${item}_taxrate"} = 
				$form->format_amount($myconfig, 
					$form->{"${item}_rate"} * 100);

			push(@{ $form->{taxrate} }, $form->{"${item}_taxrate"});
      
			push(@{ $form->{taxnumber} }, 
				$form->{"${item}_taxnumber"});
		}
	}

	# adjust taxes for lineitems
	my $total = 0;
	for (@{ $form->{lineitems} }) {
		$total += $_->{tax};
	}
	if ($form->round_amount($total,2) != $form->round_amount($tax,2)) {
		# get largest amount
		for (reverse sort { $a->{tax} <=> $b->{tax} } 
		@{ $form->{lineitems} }) {

			$_->{tax} -= $total - $tax;
			last;
		}
	}
	$i = 1;
	for (@{ $form->{lineitems} }) {
		push(@{ $form->{linetax} }, 
			$form->format_amount($myconfig, $_->{tax}, 2, ""));
	}
    
  
	for (qw(totalparts totalservices)) { 
		$form->{$_} = $form->format_amount($myconfig, $form->{$_}, 2); 
	}
	for (qw(totalqty totalship totalweight)) { 
		$form->{$_} = $form->format_amount($myconfig, $form->{$_});
	}
	$form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 
		2);
	$form->{ordtotal} = ($form->{taxincluded}) 
		? $form->{ordtotal} 
		: $form->{ordtotal} + $tax;

	my $c;
	if ($form->{language_code} ne "") {
  		$c = new CP $form->{language_code};
	} else {
		$c = new CP $myconfig->{countrycode};
	}
	$c->init;
	my $whole;
	($whole, $form->{decimal}) = split /\./, $form->{ordtotal};
	$form->{decimal} .= "00";
	$form->{decimal} = substr($form->{decimal}, 0, 2);

	$form->{text_decimal} = $c->num2text($form->{decimal} * 1);
	$form->{text_amount} = $c->num2text($whole);
	$form->{integer_amount} = $form->format_amount($myconfig, $whole);

	# format amounts
	$form->{quototal} = $form->{ordtotal} = 
		$form->format_amount($myconfig, $form->{ordtotal}, 2);

	$form->format_string(qw(text_amount text_decimal));

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

}


sub assembly_details {
	my ($myconfig, $form, $dbh, $id, $oid, $qty) = @_;

	my $sm = "";
	my $spacer;

	$form->{stagger}++;
	if ($form->{format} eq 'html') {
		$spacer = "&nbsp;" x (3 * ($form->{stagger} - 1)) 
				if $form->{stagger} > 1;
	}
	if ($form->{format} =~ /(postscript|pdf)/) {
  		if ($form->{stagger} > 1) {
			$spacer = ($form->{stagger} - 1) * 3;
			$spacer = '\rule{'.$spacer.'mm}{0mm}';
		}
	}

	# get parts and push them onto the stack
	my $sortorder = "";
  
	if ($form->{grouppartsgroup}) {
		$sortorder = qq|ORDER BY pg.partsgroup, a.id|;
	} else {
		$sortorder = qq|ORDER BY a.id|;
	}
  
	my $where = ($form->{formname} eq 'work_order') 
		? "1 = 1" 
		: "a.bom = '1'";
  
	my $query = qq|
		SELECT p.partnumber, p.description, p.unit, a.qty, 
			pg.partsgroup, p.partnumber AS sku, p.assembly, p.id, 
			p.bin
		FROM assembly a
		JOIN parts p ON (a.parts_id = p.id)
		LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
		WHERE $where
		AND a.id = ?
		$sortorder|;
	my $sth = $dbh->prepare($query);
	$sth->execute($id) || $form->dberror($query);

	while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
   
		for (qw(partnumber description partsgroup)) {
			$form->{"a_$_"} = $ref->{$_};
			$form->format_string("a_$_");
		}
   
		if ($form->{grouppartsgroup} && $ref->{partsgroup} ne $sm) {
      			for (qw(
				taxrates number sku unit qty runningnumber ship 
				bin serialnumber requiredate projectnumber 
				sellprice listprice netprice discount 
				discountrate linetotal weight itemnotes)
			) { 

				push(@{ $form->{$_} }, "");
			}
			$sm = ($form->{"a_partsgroup"}) 
				? $form->{"a_partsgroup"} : "";
			push(@{ $form->{description} }, "$spacer$sm");
      
			push(@{ $form->{lineitems} }, 
				{ amount => 0, tax => 0 });
      
		}
    
		if ($form->{stagger}) {
     
			push(@{ $form->{description} }, 
				qq|$spacer$form->{"a_partnumber"}, |.
				qq|$form->{"a_description"}|);

			for (qw(
				taxrates number sku runningnumber ship 
				serialnumber requiredate projectnumber 
				sellprice listprice netprice discount 
				discountrate linetotal weight itemnotes)
			) { 

				push(@{ $form->{$_} }, "") 
			}
      
		} else {
      
			push(@{ $form->{description} }, 
				qq|$form->{"a_description"}|);
			push(@{ $form->{sku} }, $form->{"a_partnumber"});
			push(@{ $form->{number} }, $form->{"a_partnumber"});
      
			for (qw(
				taxrates runningnumber ship serialnumber 
				requiredate projectnumber sellprice listprice 
				netprice discount discountrate linetotal weight 
				itemnotes)
			) { 

				push(@{ $form->{$_} }, "") 
			}
      
		}

		push(@{ $form->{lineitems} }, { amount => 0, tax => 0 });
      
		push(@{ $form->{qty} }, $form->format_amount(
			$myconfig, $ref->{qty} * $qty));

		for (qw(unit bin)) {
			$form->{"a_$_"} = $ref->{$_};
			$form->format_string("a_$_");
			push(@{ $form->{$_} }, $form->{"a_$_"});
		}

		if ($ref->{assembly} && $form->{formname} eq 'work_order') {
			&assembly_details(
				$myconfig, $form, $dbh, $ref->{id}, $oid, 
				$ref->{qty} * $qty);
		}
    
	}
	$sth->finish;

	$form->{stagger}--;
  
}


sub project_description {
	my ($self, $dbh, $id) = @_;

	my $query = qq|
		SELECT description
		FROM project
		WHERE id = $id|;
	($_) = $dbh->selectrow_array($query);
  
	$_;

}


sub get_warehouses {
	my ($self, $myconfig, $form) = @_;
  
	my $dbh = $form->{dbh};
	# setup warehouses
	my $query = qq|
		SELECT id, description
		FROM warehouse
		ORDER BY 2|;

	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;

}


sub save_inventory {
	my ($self, $myconfig, $form) = @_;
  
	my ($null, $warehouse_id) = split /--/, $form->{warehouse};
	$warehouse_id *= 1;

	my $ml = ($form->{type} eq 'ship_order') ? -1 : 1;
  
	my $dbh = $form->{dbh};
	my $sth;
	my $wth;
	my $serialnumber;
	my $ship;
  
	my ($null, $employee_id) = split /--/, $form->{employee};
	($null, $employee_id) = $form->get_employee($dbh) if ! $employee_id;
 
	$query = qq|
		SELECT serialnumber, ship
		FROM orderitems
		WHERE trans_id = ?
		AND id = ?
		FOR UPDATE|;
	$sth = $dbh->prepare($query) || $form->dberror($query);

	$query = qq|
		SELECT sum(qty)
		FROM inventory
		WHERE parts_id = ?
		AND warehouse_id = ?|;
	$wth = $dbh->prepare($query) || $form->dberror($query);
  

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

		$ship = (abs($form->{"ship_$i"}) > abs($form->{"qty_$i"})) 
			? $form->{"qty_$i"} 
			: $form->{"ship_$i"};
    
		if ($warehouse_id && $form->{type} eq 'ship_order') {

			$wth->execute($form->{"id_$i"}, $warehouse_id) 
				|| $form->dberror;

			($qty) = $wth->fetchrow_array;
			$wth->finish;

			if ($ship > $qty) {
				$ship = $qty;
			}
		}

    
		if ($ship) {

			if (!$form->{shippingdate}){
				$form->{shippingdate} = undef;
			}

			$ship *= $ml;
			$query = qq|
				INSERT INTO inventory 
					(parts_id, warehouse_id, qty, trans_id, 
					orderitems_id, shippingdate, 
					employee_id)
				VALUES 
					(?, ?, ?, ?, ?, ?, ?)|;
			$sth2 = $dbh->prepare($query);
			$sth2->execute(
					$form->{"id_$i"}, $warehouse_id,
					$ship, $form->{"id"},
					$form->{"orderitems_id_$i"},
					$form->{shippingdate},
					$employee_id
				) || $form->dberror($query);
			$sth2->finish;
     
			# add serialnumber, ship to orderitems
			$sth->execute(
				$form->{id}, $form->{"orderitems_id_$i"}) 
					|| $form->dberror;
			($serialnumber, $ship) = $sth->fetchrow_array;
			$sth->finish;

			$serialnumber .= " " if $serialnumber;
			$serialnumber .= qq|$form->{"serialnumber_$i"}|;
			$ship += $form->{"ship_$i"};

			$query = qq|
				UPDATE orderitems SET
					serialnumber = '$serialnumber',
					ship = $ship,
					reqdate = '$form->{shippingdate}'
					WHERE trans_id = $form->{id}
				AND id = $form->{"orderitems_id_$i"}|;
 			$sth2 = $dbh->prepare($query); 
			$sth2->execute(
					$serialnumber, $ship, 
					$form->{shippingdate}, $form->{id},
					$form->{"orderitems_id_$i"}
				) || $form->dberror($query);
			$sth2->finish;
      
			# update order with ship via
			$query = qq|
				UPDATE oe SET
					shippingpoint = ?,
					shipvia = ?
				WHERE id = ?|;
			$sth2 = $dbh->prepare($query);
			$sth2->execute(
				$form->{shippingpoint}, $form->{shipvia},
				$form->{id}
				) || $form->dberror($query);
			$sth2->finish;

			# update onhand for parts
			$form->update_balance($dbh,
				"parts",
				"onhand",
				qq|id = $form->{"id_$i"}|,
				$form->{"ship_$i"} * $ml);

		}
	}

	my $rc = $dbh->commit;

	$rc;

}


sub adj_onhand {
	my ($dbh, $form, $ml) = @_;

	my $query = qq|
		SELECT oi.parts_id, oi.ship, p.inventory_accno_id, p.assembly
		FROM orderitems oi
		JOIN parts p ON (p.id = oi.parts_id)
		WHERE oi.trans_id = ?|;
	my $sth = $dbh->prepare($query);
	$sth->execute($form->{id}) || $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 $ath = $dbh->prepare($query) || $form->dberror($query);

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

		if ($ref->{inventory_accno_id} || $ref->{assembly}) {

			# do not update if assembly consists of all services
			if ($ref->{assembly}) {
				$ath->execute($ref->{parts_id}) 
					|| $form->dberror($query);

				my ($inv, $assembly) = $ath->fetchrow_array;
				$ath->finish;

				next unless ($inv || $assembly);
	
		}

		# adjust onhand in parts table
		$form->update_balance($dbh,
			"parts",
			"onhand",
			qq|id = $ref->{parts_id}|,
			$ref->{ship} * $ml);
		}
	}
  
	$sth->finish;

}


sub adj_inventory {
	my ($dbh, $myconfig, $form) = @_;

  
	# increase/reduce qty in inventory table
	my $query = qq|
		SELECT oi.id, oi.parts_id, oi.ship
		FROM orderitems oi
		WHERE oi.trans_id = ?|;
	my $sth = $dbh->prepare($query);
	$sth->execute($form->{id}) || $form->dberror($query);

	my $id = $dbh->quote($form->{id});
	$query = qq|
		SELECT qty,
			(SELECT SUM(qty) FROM inventory
			WHERE trans_id = $id
			AND orderitems_id = ?) AS total
		FROM inventory
		WHERE trans_id = $id
		AND orderitems_id = ?|;
	my $ith = $dbh->prepare($query) || $form->dberror($query);
  
	my $qty;
	my $ml = ($form->{type} =~ /(ship|sales)_order/) ? -1 : 1;
  
	while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {

		$ith->execute($ref->{id}, $ref->{id}) || $form->dberror($query);

		my $ship = $ref->{ship};
		while (my $inv = $ith->fetchrow_hashref(NAME_lc)) {

			if (($qty = (($inv->{total} * $ml) - $ship)) >= 0) {
				$qty = $inv->{qty} * $ml 
					if ($qty > ($inv->{qty} * $ml));
	
				$form->update_balance($dbh,
					"inventory",
					"qty",
 					qq|$oid{$myconfig->{dbdriver}} |.
						qq|= $inv->{oid}|,
					$qty * -1 * $ml);
				$ship -= $qty;
			}
		}
		$ith->finish;

	}
	$sth->finish;

	# delete inventory entries if qty = 0
	$query = qq|
		DELETE FROM inventory
		WHERE trans_id = ?
		AND qty = 0|;
	$sth = $dbh->prepare($query);
	$sth->execute($form->{id}) || $form->dberror($query);

}


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

	my $where;
	my $query;
	my $null;
	my $fromwarehouse_id;
	my $towarehouse_id;
	my $var;
  
	my $dbh = $form->{dbh};
  
	if ($form->{partnumber} ne "") {
		$var = $dbh->quote($form->like(lc $form->{partnumber}));
		$where .= "
			AND lower(p.partnumber) LIKE '$var'";
	}
	if ($form->{description} ne "") {
		$var = $dbh->quote($form->like(lc $form->{description}));
		$where .= "
			AND lower(p.description) LIKE '$var'";
	}
	if ($form->{partsgroup} ne "") {
		($null, $var) = split /--/, $form->{partsgroup};
		$var = $dbh->quote($var);
		$where .= "
			AND pg.id = $var";
	}


	($null, $fromwarehouse_id) = split /--/, $form->{fromwarehouse};
	$fromwarehouse_id = $dbh->quote($fromwarehouse_id);
  
	($null, $towarehouse_id) = split /--/, $form->{towarehouse};
	$towarehouse_id = $dbh->quote($towarehouse_id);

	my %ordinal = ( 
		partnumber => 2,
		description => 3,
		partsgroup => 5,
		warehouse => 6,
		);

	my @a = (partnumber, warehouse);
	my $sortorder = $form->sort_order(\@a, \%ordinal);
  
	if ($fromwarehouse_id) {
		if ($towarehouse_id) {
			$where .= "
				AND NOT i.warehouse_id = $towarehouse_id";
		}
		$query = qq|
			SELECT p.id, p.partnumber, p.description,
				sum(i.qty) * 2 AS onhand, sum(i.qty) AS qty,
				pg.partsgroup, w.description AS warehouse, 
				i.warehouse_id
			FROM inventory i
			JOIN parts p ON (p.id = i.parts_id)
			LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
			JOIN warehouse w ON (w.id = i.warehouse_id)
			WHERE i.warehouse_id = $fromwarehouse_id
			$where
			GROUP BY p.id, p.partnumber, p.description, 
				pg.partsgroup, w.description, i.warehouse_id 
			ORDER BY $sortorder|;
	} else {
		if ($towarehouse_id) {
			$query = qq|
				SELECT p.id, p.partnumber, p.description,
					p.onhand, 
						(SELECT SUM(qty) 
						FROM inventory i 
						WHERE i.parts_id = p.id) AS qty,
					pg.partsgroup, '' AS warehouse, 
					0 AS warehouse_id
				FROM parts p
				LEFT JOIN partsgroup pg 
					ON (p.partsgroup_id = pg.id)
				WHERE p.onhand > 0 
					$where
				UNION|;
		}

		$query .= qq|
			SELECT p.id, p.partnumber, p.description,
				sum(i.qty) * 2 AS onhand, sum(i.qty) AS qty,
				pg.partsgroup, w.description AS warehouse, 
				i.warehouse_id
			FROM inventory i
			JOIN parts p ON (p.id = i.parts_id)
			LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
			JOIN warehouse w ON (w.id = i.warehouse_id)
			WHERE i.warehouse_id != $towarehouse_id
				$where
			GROUP BY p.id, p.partnumber, p.description, 
				pg.partsgroup, w.description, i.warehouse_id
			ORDER BY $sortorder|;
	}

	my $sth = $dbh->prepare($query);
	$sth->execute || $form->dberror($query);
  
	while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
		$ref->{qty} = $ref->{onhand} - $ref->{qty};
		push @{ $form->{all_inventory} }, $ref if $ref->{qty} > 0;
	}
	$sth->finish;

	$dbh->commit;
}


sub transfer {
	my ($self, $myconfig, $form) = @_;
  
	my $dbh = $form->{dbh};
  
	($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
  
 	my @a = localtime;
	$a[5] += 1900;
	$a[4]++;
	$a[4] = substr("0$a[4]", -2);
	$a[3] = substr("0$a[3]", -2);
	$shippingdate = "$a[5]$a[4]$a[3]";

	my %total = ();

	

	my $query = qq|
		INSERT INTO inventory
			(warehouse_id, parts_id, qty, shippingdate, employee_id)
		VALUES (?, ?, ?, '$shippingdate', $form->{employee_id})|;
	$sth = $dbh->prepare($query) || $form->dberror($query);

	my $qty;
  
	for my $i (1 .. $form->{rowcount}) {
		$qty = $form->parse_amount($myconfig, $form->{"transfer_$i"});

		$qty = $form->{"qty_$i"} if ($qty > $form->{"qty_$i"});

		if ($qty > 0) {
			# to warehouse
			if ($form->{warehouse_id}) {
				$sth->execute($form->{warehouse_id}, 
					$form->{"id_$i"}, $qty, 
					$shippingdate, $form->{employee_id}) 
						|| $form->dberror;
				$sth->finish;
			}
      
			# from warehouse
			if ($form->{"warehouse_id_$i"}) {
				$sth->execute($form->{"warehouse_id_$i"}, 
					$form->{"id_$i"}, $qty * -1) 
						|| $form->dberror;
				$sth->finish;
			}
		}
	}

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

	$rc;

}


sub get_soparts {
	my ($self, $myconfig, $form) = @_;
  
	# connect to database
	my $dbh = $form->{dbh};
  
	my $id;
	my $ref;
  
	# store required items from selected sales orders
	my $query = qq|
		SELECT p.id, oi.qty - oi.ship AS required, p.assembly
		FROM orderitems oi
		JOIN parts p ON (p.id = oi.parts_id)
		WHERE oi.trans_id = ?|;
	my $sth = $dbh->prepare($query) || $form->dberror($query);
  
	for (my $i = 1; $i <= $form->{rowcount}; $i++) {

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

			$sth->execute($form->{"ndx_$i"});
      
			while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
				&add_items_required(
					"", $dbh, $form, $ref->{id}, 
					$ref->{required}, $ref->{assembly});
			}
			$sth->finish;
		}

	}

	$query = qq|SELECT current_date|;
	($form->{transdate}) = $dbh->selectrow_array($query);
  
	# foreign exchange rates
	&exchangerate_defaults($dbh, $form);

	$dbh->commit;

}


sub add_items_required {
	my ($self, $dbh, $form, $parts_id, $required, $assembly) = @_;
  
	my $query;
	my $sth;
	my $ref;

	if ($assembly) {
		$query = qq|
			SELECT p.id, a.qty, p.assembly 
			FROM assembly a
			JOIN parts p ON (p.id = a.parts_id)
			WHERE a.id = ?|;
		$sth = $dbh->prepare($query);
 		$sth->execute || $form->dberror($query);
    
		while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
			&add_items_required(
				"", $dbh, $form, $ref->{id}, 
				$required * $ref->{qty}, $ref->{assembly});
		}
		$sth->finish;
    
	} else {

		$query = qq|
			SELECT partnumber, description, lastcost
			FROM parts
			WHERE id = ?|;
		$sth = $dbh->prepare($query);
		$sth->execute($parts_id) || $form->dberror($query);
		$ref = $sth->fetchrow_hashref(NAME_lc);
		for (keys %$ref) { 
			$form->{orderitems}{$parts_id}{$_} = $ref->{$_};
		}
		$sth->finish;

		$form->{orderitems}{$parts_id}{required} += $required;

		$query = qq|
			SELECT pv.partnumber, pv.leadtime, pv.lastcost, pv.curr,
				pv.vendor_id, v.name
			FROM partsvendor pv
			JOIN vendor v ON (v.id = pv.vendor_id)
			WHERE pv.parts_id = ?|;
		$sth = $dbh->prepare($query) || $form->dberror($query);

		# get cost and vendor
		$sth->execute($parts_id);

		while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
			for (keys %$ref) { 
				$form->{orderitems}{$parts_id}{partsvendor}
					{$ref->{vendor_id}}{$_} = $ref->{$_};
			}
		}
		$sth->finish;
    
	}

}


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

	my $i;
	my %a;
	my $query;
	my $sth;
  
	for ($i = 1; $i <= $form->{rowcount}; $i++) {
		for (qw(qty lastcost)) { 
			$form->{"${_}_$i"} = $form->parse_amount(
				$myconfig, $form->{"${_}_$i"}); 
		}
    
		if ($form->{"qty_$i"}) {
			($vendor, $vendor_id) = 
				split /--/, $form->{"vendor_$i"};
			if ($vendor_id) {
				$a{$vendor_id}{$form->{"id_$i"}}{qty} 
					+= $form->{"qty_$i"};
				for (qw(curr lastcost)) { 
					$a{$vendor_id}{$form->{"id_$i"}}{$_} 
						= $form->{"${_}_$i"};
				}
			}
		}
	}

	# connect to database
	my $dbh = $form->{dbh};
  
	# foreign exchange rates
	&exchangerate_defaults($dbh, $form);

	my $amount;
	my $netamount;
	my $curr = "";
	my %tax;
	my $taxincluded = 0;
	my $vendor_id;

	my $description;
	my $unit;

	my $sellprice;
  
	foreach $vendor_id (keys %a) {
    
		%tax = ();
    
		$query = qq|
			SELECT v.curr, v.taxincluded, t.rate, c.accno
			FROM vendor v
			LEFT JOIN vendortax vt ON (v.id = vt.vendor_id)
			LEFT JOIN tax t ON (t.chart_id = vt.chart_id)
			LEFT JOIN chart c ON (c.id = t.chart_id)
			WHERE v.id = ?|;
		$sth = $dbh->prepare($query);
		$sth->execute($vendor_id) || $form->dberror($query);
		while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
			$curr = $ref->{curr};
			$taxincluded = $ref->{taxincluded};
			$tax{$ref->{accno}} = $ref->{rate};
		}
		$sth->finish;  

		$curr ||= $form->{defaultcurrency};
		$taxincluded *= 1;
    
		my $uid = localtime;
		$uid .= "$$";

		# TODO:  Make this function insert as much as possible
		$query = qq|
			INSERT INTO oe (ordnumber)
			VALUES ('$uid')|;
		$dbh->do($query) || $form->dberror($query);
   
		$query = qq|SELECT id FROM oe WHERE ordnumber = '$uid'|;
		$sth = $dbh->prepare($query);
		$sth->execute || $form->dberror($query);
		my ($id) = $sth->fetchrow_array;
		$sth->finish;

		$amount = 0;
		$netamount = 0;
    
		foreach my $parts_id (keys %{ $a{$vendor_id} }) {

			if (($form->{$curr} 
			* $form->{$a{$vendor_id}{$parts_id}{curr}}) > 0) {

				$sellprice = $a{$vendor_id}{$parts_id}{lastcost}
					/ $form->{$curr} 
					* $form->{$a{$vendor_id}{$parts_id}
						{curr}};
			} else {
				$sellprice = 
					$a{$vendor_id}{$parts_id}{lastcost};
			}
			$sellprice = $form->round_amount($sellprice, 2);
      
			my $linetotal = $form->round_amount(
				$sellprice * $a{$vendor_id}{$parts_id}{qty}, 2);
       
			$query = qq|
				SELECT p.description, p.unit, c.accno 
				FROM parts p
				LEFT JOIN partstax pt ON (p.id = pt.parts_id)
				LEFT JOIN chart c ON (c.id = pt.chart_id)
				WHERE p.id = ?|;
			$sth = $dbh->prepare($query);
			$sth->execute($parts_id) || $form->dberror($query);
      
			my $rate = 0;
			my $taxes = '';
			while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
				$description = $ref->{description};
				$unit = $ref->{unit};
				$rate += $tax{$ref->{accno}};
				$taxes .= "$ref->{accno} ";
			}
 			$sth->finish;
			chop $taxes;
			my @taxaccounts = Tax::init_taxes($form, $taxes);

			$netamount += $linetotal;
			if ($taxincluded) {
				$amount += $linetotal;
			} else {
				$amount += $form->round_amount(
					Tax::apply_taxes(\@taxaccounts, $form,
					$linetotal), 2);
			}
	
      
			$query = qq|
				INSERT INTO orderitems 
					(trans_id, parts_id, description,
					qty, ship, sellprice, unit) 
				VALUES
					(?, ?, ?, ?, 0, ?, ?)|;
			$sth = $dbh->prepare($query);
			$sth->execute(
				$id, $parts_id, $description, 
				$a{vendor_id}{parts_id}{qty}, $sellprice,
				$unit
				) || $form->dberror($query);

		}

		my $ordnumber = $form->update_defaults($myconfig, 'ponumber');

		my $null;
		my $employee_id;
		my $department_id;
    
		($null, $employee_id) = $form->get_employee($dbh);
		($null, $department_id) = split /--/, $form->{department};
		$department_id *= 1;
    
		$query = qq|
			UPDATE oe SET
				ordnumber = ?,
				transdate = current_date,
				vendor_id = ?,
				customer_id = 0,
				amount = ?,
				netamount = ?,
				taxincluded = ?,
				curr = ?,
				employee_id = ?,
				department_id = ?,
				ponumber = ?
			WHERE id = ?|;
		$sth = $dbh->prepare($query);
		$sth->execute(
				$ordnumber, $vendor_id, $amount, $netamount,
				$taxincluded, $curr, $employee_id, 
				$department_id, $form->{ponumber}, $id
			) || $form->dberror($query);
    
	}

	my $rc = $dbh->commit;

	$rc;
  
}


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

	# connect to database
	my $dbh = $form->{dbh};
  
	my $i;
	my $id;
	my $ref;
	my %oe = ();
  
	my $query = qq|SELECT * FROM oe WHERE id = ?|;
	my $sth = $dbh->prepare($query) || $form->dberror($query);

	for ($i = 1; $i <= $form->{rowcount}; $i++) {
		# retrieve order
		if ($form->{"ndx_$i"}) {
			$sth->execute($form->{"ndx_$i"});
      
			$ref = $sth->fetchrow_hashref(NAME_lc);
			$ref->{ndx} = $i;
			$oe{oe}{$ref->{curr}}{$ref->{id}} = $ref;

			$oe{vc}{$ref->{curr}}{$ref->{"$form->{vc}_id"}}++;
			$sth->finish;
		}
	}

	$query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
	$sth = $dbh->prepare($query) || $form->dberror($query);

	foreach $curr (keys %{ $oe{oe} }) {
    
		foreach $id (sort { 
					$oe{oe}{$curr}{$a}->{ndx} 
					<=> $oe{oe}{$curr}{$b}->{ndx} } 
				keys %{ $oe{oe}{$curr} }) {

			# retrieve order
			$vc_id = $oe{oe}{$curr}{$id}->{"$form->{vc}_id"};

			if ($oe{vc}{$oe{oe}{$curr}{$id}->{curr}}{$vc_id} > 1) {

				push @{ $oe{orders}{$curr}{$vc_id} }, $id;

				$sth->execute($id);
				while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
					push @{ $oe{orderitems}{$curr}{$id} }, 
						$ref;
				}
				$sth->finish;  

			}
		}
	}

  
	my $ordnumber = $form->{ordnumber};
	my $numberfld = ($form->{vc} eq 'customer') ? 'sonumber' : 'ponumber';
  
	my ($department, $department_id) = $form->{department};
	$department_id *= 1;
  
	my $uid = localtime;
	$uid .= "$$";

	my @orderitems = ();
 
	foreach $curr (keys %{ $oe{orders} }) {
    
		foreach $vc_id (sort { 
					$a <=> $b 
				} keys %{ $oe{orders}{$curr} }) {

  			# the orders
			@orderitems = ();
			$form->{customer_id} = $form->{vendor_id} = 0;
			$form->{"$form->{vc}_id"} = $vc_id;
			$amount = 0;
			$netamount = 0;
      
			foreach $id (@{ $oe{orders}{$curr}{$vc_id} }) {

				# header
				$ref = $oe{oe}{$curr}{$id};
	
				$amount += $ref->{amount};
				$netamount += $ref->{netamount};

				$id = $dbh->quore($id);
				foreach $item 
					(@{ $oe{orderitems}{$curr}{$id} }) {

					push @orderitems, $item;
				}

				# close order
				$query = qq|
					UPDATE oe SET
						closed = '1'
					WHERE id = $id|;
				$dbh->do($query) || $form->dberror($query);

				# reset shipped
				$query = qq|
					UPDATE orderitems SET
						ship = 0
					WHERE trans_id = $id|;
				$dbh->do($query) || $form->dberror($query);  
			}

			$ordnumber ||= $form->update_defaults(
				$myconfig, $numberfld, $dbh);

			#fixme:  Change this
			$query = qq|
				INSERT INTO oe (ordnumber) VALUES ($uid)|;
			$dbh->do($query) || $form->dberror($query);

			$query = qq|
				SELECT id
				FROM oe
				WHERE ordnumber = '$uid'|;
			($id) = $dbh->selectrow_array($query);

			$ref->{employee_id} *= 1;
      
			$query = qq|
				UPDATE oe SET
					ordnumber = |.$dbh->quote($ordnumber).qq|,
					transdate = current_date,
					vendor_id = $form->{vendor_id},
					customer_id = $form->{customer_id},
					amount = $amount,
					netamount = $netamount,
					reqdate = |.$form->dbquote($ref->{reqdate}, SQL_DATE).qq|,
					taxincluded = '$ref->{taxincluded}',
					shippingpoint = |.$dbh->quote($ref->{shippingpoint}).qq|,
					notes = |.$dbh->quote($ref->{notes}).qq|,
					curr = '$curr',
					employee_id = $ref->{employee_id},
					intnotes = |.$dbh->quote($ref->{intnotes}).qq|,
					shipvia = |.$dbh->quote($ref->{shipvia}).qq|,
					language_code = '$ref->{language_code}',
					ponumber = |.$dbh->quote($form->{ponumber}).qq|,
					department_id = $department_id
				WHERE id = $id|;
			$sth = $dbh->prepare($query);
			$sth->execute (
				$department_id, $id
				) || $form->dberror($query);
	  

			# add items
			foreach $item (@orderitems) {
				for (qw(
					qty sellprice discount project_id ship)
				) { 
					$item->{$_} *= 1;
				}
			$query = qq|
				INSERT INTO orderitems 
					(trans_id, parts_id, description,
					qty, sellprice, discount, unit, reqdate,
					project_id, ship, serialnumber, notes)
				VALUES 
					(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;

			$sth = $dbh->prepare($query);
			$sth->execute(
				$id, $item->{parts_id}, $item->{description},
				$item->{qty}, $item->{sellprice}, 
				$item->{discount}, $item->{unit},
				$form->{reqdate}, $item->{project_id},
				$item->{ship}, $item->{serialnumber}, 
				$item->{notes}
				) || $form->dberror($query);
		    
			}
		}
	}


	$rc = $dbh->commit;

	$rc;
  
}


1;