-sub adj_inventory {
- $main::lxdebug->enter_sub();
-
- my ($dbh, $myconfig, $form) = @_;
-
- my %oid = ('Pg' => 'oid',
- 'Oracle' => 'rowid');
-
- # 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);
-
- $main::lxdebug->leave_sub();
-}
-
-sub get_inventory {
- $main::lxdebug->enter_sub();
-
- 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 NOT 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} };
-
- $main::lxdebug->leave_sub();
-
- return @{ $form->{all_inventory} };
-}
-
-sub transfer {
- $main::lxdebug->enter_sub();
-
- 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->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;
-
- $main::lxdebug->leave_sub();
-
- return $rc;
-}
-
-sub webdav_folder {
- $main::lxdebug->enter_sub();
-
- my ($myconfig, $form) = @_;
-
-SWITCH: {
- $path = "webdav/angebote/" . $form->{quonumber}, last SWITCH
- if ($form->{type} eq "sales_quotation");
- $path = "webdav/bestellungen/" . $form->{ordnumber}, last SWITCH
- if ($form->{type} eq "sales_order");
- $path = "webdav/anfragen/" . $form->{quonumber}, last SWITCH
- if ($form->{type} eq "request_quotation");
- $path = "webdav/lieferantenbestellungen/" . $form->{ordnumber}, last SWITCH
- if ($form->{type} eq "purchase_order");
- }
-
- if (!-d $path) {
- mkdir($path, 0770) or die "can't make directory $!\n";
- } else {
- if ($form->{id}) {
- @files = <$path/*>;
- foreach $file (@files) {
- $file =~ /\/([^\/]*)$/;
- $fname = $1;
- $ENV{'SCRIPT_NAME'} =~ /\/([^\/]*)\//;
- $lxerp = $1;
- $link = "http://" . $ENV{'SERVER_NAME'} . "/" . $lxerp . "/" . $file;
- $form->{WEBDAV}{$fname} = $link;
- }
- }
- }
-
- $main::lxdebug->leave_sub();
-}