-sub get_warehouses {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->dbconnect($myconfig);
-
- # setup warehouses
- 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_warehouses} }, $ref;
- }
- $sth->finish;
-
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-sub save_inventory {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- my ($null, $warehouse_id) = split /--/, $form->{warehouse};
- $warehouse_id *= 1;
-
- my $employee_id;
- ($null, $employee_id) = split /--/, $form->{employee};
-
- my $ml = ($form->{type} eq 'ship_order') ? -1 : 1;
-
- my $dbh = $form->dbconnect_noauto($myconfig);
- my $sth;
- my $wth;
- my $serialnumber;
- my $ship;
-
- $query = qq|SELECT o.serialnumber, o.ship
- FROM orderitems o
- WHERE o.trans_id = ?
- AND o.id = ?
- FOR UPDATE|;
- $sth = $dbh->prepare($query) || $form->dberror($query);
-
- $query = qq|SELECT sum(i.qty)
- FROM inventory i
- WHERE i.parts_id = ?
- AND i.warehouse_id = ?|;
- $wth = $dbh->prepare($query) || $form->dberror($query);
-
- for my $i (1 .. $form->{rowcount} - 1) {
-
- $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 != 0) {
-
- $ship *= $ml;
- $query = qq|INSERT INTO inventory (parts_id, warehouse_id,
- qty, oe_id, orderitems_id, shippingdate, employee_id)
- VALUES ($form->{"id_$i"}, $warehouse_id,
- $ship, $form->{"id"},
- $form->{"orderitems_id_$i"}, '$form->{shippingdate}',
- $employee_id)|;
- $dbh->do($query) || $form->dberror($query);
-
- # 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
- WHERE trans_id = $form->{id}
- AND id = $form->{"orderitems_id_$i"}|;
- $dbh->do($query) || $form->dberror($query);
-
- # update order with ship via
- $query = qq|UPDATE oe SET
- shippingpoint = '$form->{shippingpoint}',
- shipvia = '$form->{shipvia}'
- WHERE id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
-
- # update onhand for parts
- $form->update_balance($dbh, "parts", "onhand",
- qq|id = $form->{"id_$i"}|,
- $form->{"ship_$i"} * $ml);
-
- }
- }
-
- my $rc = $dbh->commit;
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-
- return $rc;
-}
-
-sub adj_onhand {
- $main::lxdebug->enter_sub();
-
- my ($dbh, $form, $ml) = @_;
-
- my $service_units = $form->{service_units};
- my $part_units = $form->{part_units};
-
- my $query = qq|SELECT oi.parts_id, oi.ship, oi.unit, p.inventory_accno_id, p.assembly
- FROM orderitems oi
- JOIN parts p ON (p.id = oi.parts_id)
- WHERE oi.trans_id = $form->{id}|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $query = qq|SELECT sum(p.inventory_accno_id)
- FROM parts p
- JOIN assembly a ON (a.parts_id = p.id)
- WHERE a.id = ?|;
- my $ath = $dbh->prepare($query) || $form->dberror($query);
-
- my $ispa;
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- #print(STDERR "Bin in Schleife $ref->{inventory_accno_id}\n");
-
- 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);
-
- ($ispa) = $sth->fetchrow_array;
- $ath->finish;
-
- next unless $ispa;
-
- }
-
- # get item baseunit
- $query = qq|SELECT p.unit
- FROM parts p
- WHERE p.id = $ref->{parts_id}|;
- my $stw = $dbh->prepare($query);
- $stw->execute || $form->dberror($query);
-
- my ($item_unit) = $stw->fetchrow_array();
- $stw->finish;
-
- if ($ref->{inventory_accno_id}) {
- if (defined($part_units->{$item_unit}->{factor}) && $part_units->{$item_unit}->{factor} ne '' && $part_units->{$item_unit}->{factor} ne '0') {
- $basefactor = $part_units->{$ref->{unit}}->{factor} / $part_units->{$item_unit}->{factor};
- } else {
- $basefactor = 1;
- }
- $baseqty = $ref->{ship} * $basefactor;
- } else {
- if (defined($service_units->{$item_unit}->{factor}) && $service_units->{$item_unit}->{factor} ne '' && $service_units->{$item_unit}->{factor} ne '0') {
- $basefactor = $service_units->{$ref->{unit}}->{factor} / $part_units->{$item_unit}->{factor};
- } else {
- $basefactor = 1;
- }
- $baseqty = $ref->{ship} * $basefactor;
- }
- #print(STDERR "$baseqty Basismenge\n");
-
- # adjust onhand in parts table
- $form->update_balance($dbh, "parts", "onhand",
- qq|id = $ref->{parts_id}|,
- $baseqty * $ml);
- }
- }
-
- $sth->finish;
-
- $main::lxdebug->leave_sub();
-}
-
-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();
-}