+}
+
+
+sub adj_inventory {
+ my ($dbh, $myconfig, $form) = @_;
+
+ my %oid = ( 'Pg' => 'oid',
+ 'PgPP' => 'oid',
+ 'Oracle' => 'rowid',
+ 'DB2' => '1=1'
+ );
+
+ # increase/reduce qty in inventory table
+ my $query = qq|SELECT oi.id, oi.parts_id, oi.ship
+ FROM orderitems oi
+ WHERE oi.trans_id = $form->{id}|;
+ my $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ $query = qq|SELECT $oid{$myconfig->{dbdriver}} AS oid, qty,
+ (SELECT SUM(qty) FROM inventory
+ WHERE oe_id = $form->{id}
+ AND orderitems_id = ?) AS total
+ FROM inventory
+ WHERE oe_id = $form->{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);
+
+ while (my $inv = $ith->fetchrow_hashref(NAME_lc)) {
+
+ if (($qty = (($inv->{total} * $ml) - $ref->{ship})) >= 0) {
+ $qty = $inv->{qty} if ($qty > ($inv->{qty} * $ml));
+
+ $form->update_balance($dbh,
+ "inventory",
+ "qty",
+ qq|$oid{$myconfig->{dbdriver}} = $inv->{oid}|,
+ $qty * -1 * $ml);
+ }
+ }
+ $ith->finish;
+
+ }
+ $sth->finish;
+
+ # delete inventory entries if qty = 0
+ $query = qq|DELETE FROM inventory
+ WHERE oe_id = $form->{id}
+ AND qty = 0|;
+ $dbh->do($query) || $form->dberror($query);
+
+}
+
+
+sub get_inventory {
+ my ($self, $myconfig, $form) = @_;
+
+ my ($null, $warehouse_id) = split /--/, $form->{warehouse};
+ $warehouse_id *= 1;
+
+ my $dbh = $form->dbconnect($myconfig);
+
+ my $query = qq|SELECT p.id, p.partnumber, p.description, p.onhand,
+ pg.partsgroup
+ FROM parts p
+ LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+ WHERE p.onhand > 0|;
+
+ if ($form->{partnumber}) {
+ $var = $form->like(lc $form->{partnumber});
+ $query .= "
+ AND lower(p.partnumber) LIKE '$var'";
+ }
+ if ($form->{description}) {
+ $var = $form->like(lc $form->{description});
+ $query .= "
+ AND lower(p.description) LIKE '$var'";
+ }
+ if ($form->{partsgroup}) {
+ $var = $form->like(lc $form->{partsgroup});
+ $query .= "
+ AND lower(pg.partsgroup) LIKE '$var'";
+ }
+
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+
+ $query = qq|SELECT sum(i.qty), w.description, w.id
+ FROM inventory i
+ LEFT JOIN warehouse w ON (w.id = i.warehouse_id)
+ WHERE i.parts_id = ?
+ AND i.warehouse_id != $warehouse_id
+ GROUP BY w.description, w.id|;
+ $wth = $dbh->prepare($query) || $form->dberror($query);
+
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+
+ $wth->execute($ref->{id}) || $form->dberror;
+
+ while (($qty, $warehouse, $warehouse_id) = $wth->fetchrow_array) {
+ push @{ $form->{all_inventory} }, {'id' => $ref->{id},
+ 'partnumber' => $ref->{partnumber},
+ 'description' => $ref->{description},
+ 'partsgroup' => $ref->{partsgroup},
+ 'qty' => $qty,
+ 'warehouse_id' => $warehouse_id,
+ 'warehouse' => $warehouse} if $qty > 0;
+ }
+ $wth->finish;
+ }
+ $sth->finish;
+
+ $dbh->disconnect;
+
+ # sort inventory
+ @{ $form->{all_inventory} } = sort { $a->{$form->{sort}} cmp $b->{$form->{sort}} } @{ $form->{all_inventory} };
+
+}
+
+
+sub transfer {
+ my ($self, $myconfig, $form) = @_;
+
+ my $dbh = $form->dbconnect_noauto($myconfig);
+
+ my $query = qq|INSERT INTO inventory
+ (warehouse_id, parts_id, qty, shippingdate, employee_id)
+ VALUES (?, ?, ?, ?, ?)|;
+ $sth = $dbh->prepare($query) || $form->dberror($query);
+
+ ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
+
+ my @a = localtime; $a[5] += 1900; $a[4]++;
+ $shippingdate = "$a[5]-$a[4]-$a[3]";
+
+ 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) {
+ # to warehouse
+ $sth->execute($form->{warehouse_id}, $form->{"id_$i"}, $qty, $shippingdate, $form->{employee_id}) || $form->dberror;
+
+ $sth->finish;
+
+ # from warehouse
+ $sth->execute($form->{"warehouse_id_$i"}, $form->{"id_$i"}, $qty * -1, $shippingdate, $form->{employee_id}) || $form->dberror;
+
+ $sth->finish;
+ }
+ }
+
+ my $rc = $dbh->commit;
+ $dbh->disconnect;
+
+ $rc;