X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FOE.pm;h=5b7c031afade91762466f608237b6d42d7dfce0e;hb=bb374138781bbf3a368d104445aee887494aa381;hp=888f422bea062ce8a3efa55c97906b2d6268c861;hpb=92daa1a66a9ff737c92df86da3c28d663c421032;p=kivitendo-erp.git diff --git a/SL/OE.pm b/SL/OE.pm index 888f422be..5b7c031af 100644 --- a/SL/OE.pm +++ b/SL/OE.pm @@ -35,6 +35,8 @@ package OE; use SL::AM; +use SL::Common; +use SL::DBUtils; sub transactions { $main::lxdebug->enter_sub(); @@ -50,6 +52,15 @@ sub transactions { my ($null, $department_id) = split /--/, $form->{department}; my $department = " AND o.department_id = $department_id" if $department_id; + my @values; + + if ($form->{"project_id"}) { + $department .= + qq|AND ((globalproject_id = ?) OR EXISTS | . + qq| (SELECT * FROM orderitems oi | . + qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|; + push(@values, $form->{"project_id"}, $form->{"project_id"}); + } my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell'; @@ -63,55 +74,19 @@ sub transactions { my $query = qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, o.amount, ct.name, o.netamount, o.$form->{vc}_id, + o.globalproject_id, pr.projectnumber AS globalprojectnumber, ex.$rate AS exchangerate, - o.closed, o.quonumber, o.shippingpoint, o.shipvia, + o.closed, o.delivered, o.quonumber, o.shippingpoint, o.shipvia, e.name AS employee 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 exchangerate ex ON (ex.curr = o.curr AND ex.transdate = o.transdate) + LEFT JOIN project pr ON o.globalproject_id = pr.id WHERE o.quotation = '$quotation' $department|; - # 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 ON (o.id) 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 - 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|; - - if ($warehouse_id && $form->{type} eq 'ship_order') { - $query .= qq| - AND i.warehouse_id = $warehouse_id - AND i.qty >= (oi.qty - oi.ship) - |; - } - - } - if ($form->{"$form->{vc}_id"}) { $query .= qq| AND o.$form->{vc}_id = $form->{"$form->{vc}_id"}|; } else { @@ -125,6 +100,12 @@ sub transactions { $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'"; } + if (($form->{"notdelivered"} || $form->{"delivered"}) && + ($form->{"notdelivered"} ne $form->{"delivered"})) { + $query .= $form->{"delivered"} ? + " AND o.delivered " : " AND NOT o.delivered"; + } + my $sortorder = join ', ', ("o.id", $form->sort_columns(transdate, $ordnumber, name)); $sortorder = $form->{sort} if $form->{sort}; @@ -137,7 +118,10 @@ sub transactions { $query .= " ORDER by $sortorder"; my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth->execute(@values) || + $form->dberror($query . " (" . join(", ", @values) . ")"); + + dump_query(0, "laqje", $query, @values); my %id = (); while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { @@ -173,8 +157,6 @@ sub save { $form->get_employee($dbh); } - $form->{contact_id} = $form->{cp_id}; - $form->{contact_id} *= 1; $form->{payment_id} *= 1; $form->{language_id} *= 1; $form->{shipto_id} *= 1; @@ -239,7 +221,7 @@ sub save { $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship); - if ($form->{"qty_$i"}) { + if ($form->{"id_$i"}) { # get item baseunit $query = qq|SELECT p.unit @@ -343,10 +325,6 @@ sub save { $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"}; - $project_id = 'NULL'; - if ($form->{"projectnumber_$i"}) { - $project_id = $form->{"projectnumber_$i"}; - } $reqdate = ($form->{"reqdate_$i"}) ? qq|'$form->{"reqdate_$i"}'| : "NULL"; @@ -367,7 +345,7 @@ sub save { $query .= qq|$form->{id}, $form->{"id_$i"}, '$form->{"description_$i"}', '$form->{"longdescription_$i"}', $form->{"qty_$i"}, $baseqty, $fxsellprice, $form->{"discount_$i"}, - '$form->{"unit_$i"}', $reqdate, (SELECT id from project where projectnumber = '$project_id'), + '$form->{"unit_$i"}', $reqdate, | . conv_i($form->{"project_id_$i"}, 'NULL') . qq|, '$form->{"serialnumber_$i"}', $form->{"ship_$i"}, '$pricegroup_id', '$form->{"ordnumber_$i"}', '$form->{"transdate_$i"}', '$form->{"cusordnumber_$i"}', '$subtotal')|; $dbh->do($query) || $form->dberror($query); @@ -459,6 +437,7 @@ Message: $form->{message}\r| if $form->{message}; intnotes = '$form->{intnotes}', curr = '$form->{currency}', closed = '$form->{closed}', + delivered = '| . ($form->{delivered} ? "t" : "f") . qq|', proforma = '$form->{proforma}', quotation = '$quotation', department_id = $form->{department_id}, @@ -468,17 +447,14 @@ Message: $form->{message}\r| if $form->{message}; payment_id = $form->{payment_id}, delivery_vendor_id = $form->{delivery_vendor_id}, delivery_customer_id = $form->{delivery_customer_id}, + globalproject_id = | . conv_i($form->{"globalproject_id"}, 'NULL') . qq|, employee_id = $form->{employee_id}, - cp_id = $form->{contact_id} + cp_id = | . conv_i($form->{cp_id}, 'NULL') . qq| WHERE id = $form->{id}|; $dbh->do($query) || $form->dberror($query); $form->{ordtotal} = $amount; - if ($form->{webdav}) { - &webdav_folder($myconfig, $form); - } - # add shipto $form->{name} = $form->{ $form->{vc} }; $form->{name} =~ s/--$form->{"$form->{vc}_id"}//; @@ -505,12 +481,13 @@ Message: $form->{message}\r| if $form->{message}; # adjust onhand &adj_onhand($dbh, $form, $ml * -1); - &adj_inventory($dbh, $myconfig, $form); } my $rc = $dbh->commit; $dbh->disconnect; + Common::webdav_folder($form) if ($main::webdav); + $main::lxdebug->leave_sub(); return $rc; @@ -524,27 +501,6 @@ sub close_orders { my ($self, $myconfig, $form) = @_; - for my $i (1 .. $form->{rowcount}) { - - map { - $form->{"${_}_$i"} = - $form->parse_amount($myconfig, $form->{"${_}_$i"}) - } qw(qty ship); - if ($delete_oe_id) { - $form->{"orderitems_id_$i"} = ""; - } - - if ($form->{"qty_$i"}) { - - # set values to 0 if nothing entered - $form->{"discount_$i"} = - $form->parse_amount($myconfig, $form->{"discount_$i"}); - - $form->{"sellprice_$i"} = - $form->parse_amount($myconfig, $form->{"sellprice_$i"}); - } - } - # get ids from $form map { push @ids, $form->{"ordnumber_$_"} if $form->{"ordnumber_$_"} } (1 .. $form->{rowcount}); @@ -560,6 +516,21 @@ sub close_orders { $main::lxdebug->leave_sub(); } +sub close_order { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + $main::lxdebug->leave_sub() unless ($form->{"id"}); + + my $dbh = $form->dbconnect($myconfig); + do_query($form, $dbh, qq|UPDATE oe SET closed = TRUE where id = ?|, + $form->{"id"}); + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + sub delete { $main::lxdebug->enter_sub(); @@ -708,7 +679,10 @@ sub retrieve { o.curr AS currency, e.name AS employee, o.employee_id, o.$form->{vc}_id, cv.name AS $form->{vc}, o.amount AS invtotal, o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber, - d.description AS department, o.payment_id, o.language_id, o.taxzone_id, o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id + d.description AS department, o.payment_id, o.language_id, o.taxzone_id, + o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id, + o.globalproject_id, + o.delivered FROM oe o JOIN $form->{vc} cv ON (o.$form->{vc}_id = cv.id) LEFT JOIN employee e ON (o.employee_id = e.id) @@ -787,10 +761,9 @@ sub retrieve { my %oid = ('Pg' => 'oid', 'Oracle' => 'rowid'); - my $transdate = "'$form->{transdate}'"; - if (!$transdate) { - $transdate = "current_date"; - } + my $transdate = + $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date"; + if(!$form->{taxzone_id}) { $form->{taxzone_id} = 0; } @@ -869,19 +842,13 @@ sub retrieve { # delete orderitems_id in collective orders, so that they get cloned no matter what delete $ref->{orderitems_id} if (@ids); - #set expense_accno=inventory_accno if they are different => bilanz - $vendor_accno = - ($ref->{expense_accno} != $ref->{inventory_accno}) - ? $ref->{inventory_accno} - : $ref->{expense_accno}; - # get tax rates and description $accno_id = - ($form->{vc} eq "customer") ? $ref->{income_accno} : $vendor_accno; + ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno}; $query = qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber - FROM tax t LEFT JOIN chart c ON (c.id=t.chart_id) - WHERE t.taxkey in (SELECT taxkey_id from chart where accno = '$accno_id') - ORDER BY accno|; + FROM tax t LEFT JOIN chart c on (c.id=t.chart_id) + WHERE t.id in (SELECT tk.tax_id from taxkeys tk where tk.chart_id = (SELECT id from chart WHERE accno='$accno_id') AND startdate<=$transdate ORDER BY startdate desc LIMIT 1) + ORDER BY c.accno|; $stw = $dbh->prepare($query); $stw->execute || $form->dberror($query); $ref->{taxaccounts} = ""; @@ -921,9 +888,7 @@ sub retrieve { $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell"); - if ($form->{webdav}) { - &webdav_folder($myconfig, $form); - } + Common::webdav_folder($form) if ($main::webdav); # get tax zones $query = qq|SELECT id, description @@ -970,6 +935,10 @@ sub order_details { my %oid = ('Pg' => 'oid', 'Oracle' => 'rowid'); + my (@project_ids, %projectnumbers); + + push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"}); + # sort items by partsgroup for $i (1 .. $form->{rowcount}) { $partsgroup = ""; @@ -977,32 +946,30 @@ sub order_details { $partsgroup = $form->{"partsgroup_$i"}; } push @partsgroup, [$i, $partsgroup]; + push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"}); } - # 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(i.qty) AS qty - FROM inventory i - WHERE i.parts_id = ? - AND i.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); - } + if (@project_ids) { + $query = "SELECT id, projectnumber FROM project WHERE id IN (" . + join(", ", map({ "?" } @project_ids)) . ")"; + $sth = $dbh->prepare($query); + $sth->execute(@project_ids) || + $form->dberror($query . " (" . join(", ", @project_ids) . ")"); + while (my $ref = $sth->fetchrow_hashref()) { + $projectnumbers{$ref->{id}} = $ref->{projectnumber}; } + $sth->finish(); } + $form->{"globalprojectnumber"} = + $projectnumbers{$form->{"globalproject_id"}}; + + my @arrays = + qw(runningnumber number description longdescription qty ship unit bin + partnotes serialnumber reqdate sellprice listprice netprice + discount p_discount discount_sub nodiscount_sub + linetotal nodiscount_linetotal tax_rate projectnumber); + my $sameitem = ""; foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) { $i = $item->[0]; @@ -1011,13 +978,12 @@ sub order_details { push(@{ $form->{description} }, qq|$item->[1]|); $sameitem = $item->[1]; - map { push(@{ $form->{$_} }, "") } - qw(runningnumber number qty ship unit bin partnotes serialnumber reqdate sellprice listprice netprice discount linetotal); + map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays)); } $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}); - if ($form->{"qty_$i"} != 0) { + if ($form->{"id_$i"} != 0) { # add number, description and qty to $form->{number}, .... @@ -1093,7 +1059,7 @@ sub order_details { push(@{ $form->{p_discount} }, $form->{"discount_$i"}); $form->{ordtotal} += $linetotal; - $discount_subtotal += $linetotal; + $discount_subtotal += $linetotal; $form->{nodiscount_total} += $nodiscount_linetotal; $nodiscount_subtotal += $nodiscount_linetotal; $form->{discount_total} += $form->parse_amount($myconfig, $discount); @@ -1119,6 +1085,8 @@ sub order_details { push(@{ $form->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2)); + push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}}); + my ($taxamount, $taxbase); my $taxrate = 0; @@ -1171,8 +1139,8 @@ sub order_details { while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) { - map { push(@{ $form->{$_} }, "") } - qw(runningnumber ship bin serialnumber number unit bin qty reqdate sellprice listprice netprice discount linetotal nodiscount_linetotal); + map({ push(@{ $form->{$_} }, "") } + grep({ $_ ne "description" } @arrays)); $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--"; push(@{ $form->{description} }, $sameitem); } @@ -1182,9 +1150,8 @@ sub order_details { ) . qq|, $ref->{partnumber}, $ref->{description}|); - map { push(@{ $form->{$_} }, "") } - qw(number unit qty runningnumber ship bin serialnumber reqdate sellprice listprice netprice discount linetotal nodiscount_linetotal); - + map({ push(@{ $form->{$_} }, "") } + grep({ $_ ne "description" } @arrays)); } $sth->finish; } @@ -1194,16 +1161,16 @@ sub order_details { my $tax = 0; foreach $item (sort keys %taxaccounts) { - push(@{ $form->{taxbase} }, - $form->format_amount($myconfig, $taxbase{$item}, 2)); + push(@{ $form->{taxbase} }, + $form->format_amount($myconfig, $taxbase{$item}, 2)); - $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2); + $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2); - push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2)); - push(@{ $form->{taxdescription} }, $form->{"${item}_description"}); - push(@{ $form->{taxrate} }, - $form->format_amount($myconfig, $form->{"${item}_rate"} * 100)); - push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"}); + push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2)); + push(@{ $form->{taxdescription} }, $form->{"${item}_description"}); + push(@{ $form->{taxrate} }, + $form->format_amount($myconfig, $form->{"${item}_rate"} * 100)); + push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"}); } $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2); $yesdiscount = $form->{nodiscount_total} - $nodiscount; @@ -1226,9 +1193,6 @@ sub order_details { $form->set_payment_options($myconfig, $form->{orddate}); } - # myconfig variables - map { $form->{$_} = $myconfig->{$_} } - (qw(company address tel fax signature businessnumber)); $form->{username} = $myconfig->{name}; $dbh->disconnect; @@ -1256,132 +1220,6 @@ sub project_description { return $_; } -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(); @@ -1460,219 +1298,5 @@ sub adj_onhand { $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(); -} 1;