From 50d1a68af70dfcadbb3896798e8211881457e3fa Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Wed, 23 Dec 2015 21:57:33 +0100 Subject: [PATCH] Further changes in manufacturing, added unit_cost in issues and requirements --- admin/inst_module.php | 6 + includes/db/inventory_db.inc | 38 +++-- includes/ui/ui_lists.inc | 2 +- .../includes/db/work_order_costing_db.inc | 82 ++++++++++- .../includes/db/work_order_issues_db.inc | 59 ++++---- .../db/work_order_produce_items_db.inc | 117 +++++++++------ .../db/work_order_requirements_db.inc | 51 ++++--- manufacturing/includes/db/work_orders_db.inc | 136 +++++++++++------- .../includes/db/work_orders_quick_db.inc | 69 ++++----- manufacturing/manage/bom_edit.php | 5 +- manufacturing/view/wo_issue_view.php | 3 +- manufacturing/work_order_entry.php | 5 +- .../includes/html_entity_decode_php4.php | 5 +- sql/alter2.4rc1.sql | 3 + sql/en_US-demo.sql | 3 +- sql/en_US-new.sql | 3 +- 16 files changed, 364 insertions(+), 223 deletions(-) diff --git a/admin/inst_module.php b/admin/inst_module.php index 886ecc91..2309292b 100644 --- a/admin/inst_module.php +++ b/admin/inst_module.php @@ -212,6 +212,12 @@ if (get_post('Refresh')) { foreach($exts as $i => $ext) { if ($ext['package'] && ($ext['active'] ^ check_value('Active'.$i))) { + if (check_value('Active'.$i) && !check_src_ext_version($ext['version'])) + { + display_warning(sprintf(_("Package '%s' is incompatible with current application version and cannot be activated.\n") + . _("Check Install/Activate page for newer package version."), $ext['name'])); + continue; + } $activated = activate_hooks($ext['package'], $comp, !$ext['active']); // change active state if ($activated !== null) diff --git a/includes/db/inventory_db.inc b/includes/db/inventory_db.inc index ad9fbb7b..6d562cf8 100644 --- a/includes/db/inventory_db.inc +++ b/includes/db/inventory_db.inc @@ -152,7 +152,10 @@ function get_already_delivered($stock_id, $location, $trans_no) $row = db_fetch_row($result); return $row[0]; } - +/* + FIXME: $to ? + Returns start move_id in latest negative status period for $stock_id +*/ function last_negative_stock_trans_id($stock_id, $to) { $sql = "SELECT * from ".TB_PREF."stock_moves @@ -197,21 +200,24 @@ function get_deliveries_between($stock_id, $from, $to) return db_fetch_row($result); } -function get_deliveries_from_trans($stock_id, $from) +/* + Returns quantity and total cost of $stock_id sales, entered after record with $move_id +*/ +function get_deliveries_from_trans($stock_id, $move_id) { // -ve qty is delivery either by ST_CUSTDELIVERY or inventory adjustment //Price for GRN and SUPPCREDIT and std_cost for other trans_types $sql = "SELECT SUM(-qty), SUM(-qty*IF(type=".ST_SUPPRECEIVE." OR type=".ST_SUPPCREDIT.", price, standard_cost)) FROM ".TB_PREF."stock_moves WHERE stock_id=".db_escape($stock_id)." AND qty < 0 AND - trans_id>='$from' GROUP BY stock_id"; + trans_id>='$move_id' GROUP BY stock_id"; $result = db_query($sql, "The deliveries could not be updated"); $row = db_fetch_row($result); $sql = "SELECT IF(type=".ST_SUPPRECEIVE." OR type=".ST_SUPPCREDIT.", price, standard_cost) FROM ".TB_PREF."stock_moves WHERE stock_id=".db_escape($stock_id) - ." AND trans_id ='$from'"; + ." AND trans_id ='$move_id'"; $result = db_query($sql, "The deliveries could not be updated"); $cost = db_fetch_row($result); @@ -219,7 +225,7 @@ function get_deliveries_from_trans($stock_id, $from) $sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves WHERE stock_id=".db_escape($stock_id)." AND - trans_id<'$from' GROUP BY stock_id"; + trans_id<'$move_id' GROUP BY stock_id"; $result = db_query($sql, "The deliveries could not be updated"); $qoh = db_fetch_row($result); @@ -229,13 +235,16 @@ function get_deliveries_from_trans($stock_id, $from) return array($qty,$final_cost); } -function get_purchases_from_trans($stock_id, $from) +/* + Returns quantity and total cost of $stock_id purchases, entered after record with $move_id +*/ +function get_purchases_from_trans($stock_id, $move_id) { // Calculate All inward stock moves i.e. qty > 0 $sql = "SELECT SUM(qty), SUM(qty*standard_cost) FROM ".TB_PREF."stock_moves WHERE stock_id=".db_escape($stock_id)." AND qty > 0 AND - trans_id>'$from' GROUP BY stock_id"; + trans_id>'$move_id' GROUP BY stock_id"; $result = db_query($sql, "Could not get get_purchases_from_trans"); $row = db_fetch_row($result); @@ -243,7 +252,10 @@ function get_purchases_from_trans($stock_id, $from) } //------------------------------------------------------------------- - +/* + This routine fixes stock and COGS balances for all $stock_id sales made during negative inventory status. + This is called when delivery is received causing inventory status to be positive again. +*/ function adjust_deliveries($stock_id, $material_cost, $to) { global $Refs; @@ -251,19 +263,19 @@ function adjust_deliveries($stock_id, $material_cost, $to) if (!is_inventory_item($stock_id)) return; - $from = last_negative_stock_trans_id($stock_id, $to); - if ($from == false || $from == -1) + $move_id = last_negative_stock_trans_id($stock_id, $to); + if ($move_id == false || $move_id == -1) return; - $row = get_deliveries_from_trans($stock_id, $from); - + $row = get_deliveries_from_trans($stock_id, $move_id); + if ($row == false) return; $old_sales_cost = $row[1]; $new_sales_cost = $row[0] * $material_cost; $sales_diff = $new_sales_cost - $old_sales_cost; - $row = get_purchases_from_trans($stock_id, $from); + $row = get_purchases_from_trans($stock_id, $move_id); $purchase_diff = 0; $old_purchase_cost = $new_purchase_cost = 0; if ($row != false) diff --git a/includes/ui/ui_lists.inc b/includes/ui/ui_lists.inc index fccfaaff..95e8ea16 100644 --- a/includes/ui/ui_lists.inc +++ b/includes/ui/ui_lists.inc @@ -989,7 +989,7 @@ function stock_component_items_list_cells($label, $name, $parent_stock_id, echo "$label\n"; $parent = db_escape($parent_stock_id); echo stock_items_list($name, $selected_id, $all_option, $submit_on_change, - array('where'=>array("stock_id != $parent"), 'cells'=>true, 'parent'=> $parent_stock_id), $editkey, "component"); + array('where'=>array("stock_id != '$parent_stock_id'"), 'cells'=>true, 'parent'=> $parent_stock_id), $editkey, "component"); } //------------------------------------------------------------------------------------ diff --git a/manufacturing/includes/db/work_order_costing_db.inc b/manufacturing/includes/db/work_order_costing_db.inc index 6ad3a3c9..efc6f405 100644 --- a/manufacturing/includes/db/work_order_costing_db.inc +++ b/manufacturing/includes/db/work_order_costing_db.inc @@ -47,8 +47,6 @@ function add_material_cost($stock_id, $qty, $date_, $advanced=false, $woid=0) $m_cost += ($bom_item['quantity'] * $standard_cost); } $bom_cost = $m_cost; - // new 2015.10.15 - // additilnal costs. $i_cost = 0; if ($woid != 0 && work_order_has_issues($woid)) { @@ -83,19 +81,89 @@ function add_material_cost($stock_id, $qty, $date_, $advanced=false, $woid=0) if ($qoh + $qty != 0) { if ($qoh == 0) - $material_cost = $m_cost; // corrected 27.10.2014 + $material_cost = $m_cost; else $material_cost = ($qoh * $material_cost + $qty * $m_cost) / ($qoh + $qty); } - - if ($advanced && $cost_adjust) // new 2010-02-10 - adjust_deliveries($stock_id, $bom_cost, $date_); - + + if ($advanced && $cost_adjust) + adjust_deliveries($stock_id, $bom_cost, $date_); + $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=".db_escape($material_cost)." WHERE stock_id=".db_escape($stock_id); db_query($sql,"The cost details for the inventory item could not be updated"); } +/* + Updates average material cost for item. +*/ +function update_material_cost($stock_id, $qty, $unit_cost, $date) +{ + global $Refs; + + $product = get_item($stock_id); + $avg_cost = $product['material_cost']; + + $qoh = get_qoh_on_date($stock_id, null, $date); + + // unit_cost*qty is already posted on inventory GL +/* + if ($qoh > 0) + { + if ($qty<0) + $qty_done = max(-$qoh, $qty); + else + $qty_done = $qty; + } else { + if ($qty>0) + $qty_done = max($qoh+$qty, 0); + else + $qty_done = 0; + } + $qty_delayed = $qty-$qty_done; +*/ + if ($qoh < 0) + { + $qty_delayed = min(-$qoh, $qty); + } else { + $qty_delayed = 0; + } + $qty_new = $qty - $qty_delayed; + + if ($qty_delayed > 0 && ($unit_cost != $avg_cost)) + { + // post $qty_delayed*($avg_cost-$unit_cost) + $diff = $qty_delayed*($unit_cost-$avg_cost); + $stock_gl_code = get_item($stock_id); + + $dec = user_price_dec(); + $old_cost = -round2($avg_cost, $dec); + $new_cost = -round2($unit_cost, $dec); + + $cart = new items_cart(ST_COSTUPDATE); + $cart->tran_date = $cart->doc_date = $cart->event_date = $date; + + if (!is_date_in_fiscalyear($cart->tran_date)) + $cart->tran_date = end_fiscalyear(); + + $cart->reference = $Refs->get_next(ST_COSTUPDATE, null, $cart->tran_date, $date); + + $cart->memo_ = sprintf(_("COGS changed from %s to %s for %d %s of '%s'"), $avg_cost, $unit_cost, $qty_delayed, $item['units'], $stock_id); + + $cart->add_gl_item($item["cogs_account"], $item["dimension_id"], $item["dimension2_id"], -$diff); + $cart->add_gl_item($item["inventory_account"], 0, 0, $diff); + + write_journal_entries($cart); + } + + if ($qty > 0 && ($qoh != -$qty)) + $avg_cost = ($avg_cost*($qoh+$qty_delayed)+$unit_cost*$qty_new)/($qoh+$qty); + + $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=".db_escape($avg_cost)." + WHERE stock_id=".db_escape($stock_id); + db_query($sql,"The cost details for the inventory item could not be updated"); +} + function add_overhead_cost($stock_id, $qty, $date_, $costs, $adj_only=false) { if ($qty != 0) diff --git a/manufacturing/includes/db/work_order_issues_db.inc b/manufacturing/includes/db/work_order_issues_db.inc index 29bc02dd..4fe8b13d 100644 --- a/manufacturing/includes/db/work_order_issues_db.inc +++ b/manufacturing/includes/db/work_order_issues_db.inc @@ -23,23 +23,6 @@ function add_work_order_issue($woid, $ref, $to_work_order, $items, $location, $w $args->trans_no = 0; hook_db_prewrite($args, ST_MANUISSUE); - $details = get_work_order($woid); - - if (strlen($details[0]) == 0) - { - echo _("The order number sent is not valid."); - cancel_transaction(); - exit; - } - - if (work_order_is_closed($woid)) - { - display_error("UNEXPECTED : Issuing items for a closed Work Order"); - cancel_transaction(); - exit; - } - - // insert the actual issue $sql = "INSERT INTO ".TB_PREF."wo_issues (workorder_id, reference, issue_date, loc_code, workcentre_id) VALUES (".db_escape($woid).", ".db_escape($ref).", '" . date2sql($date_) . "', ".db_escape($location).", ".db_escape($workcentre).")"; @@ -49,6 +32,8 @@ function add_work_order_issue($woid, $ref, $to_work_order, $items, $location, $w $issue_total = $total_cost = 0; + $wo = get_work_order($woid); + foreach ($items as $item) { @@ -61,33 +46,40 @@ function add_work_order_issue($woid, $ref, $to_work_order, $items, $location, $w add_stock_move(ST_MANUISSUE, $item->stock_id, $number, $location, $date_, $memo_, -$item->quantity, $standard_cost); - $sql = "INSERT INTO ".TB_PREF."wo_issue_items (issue_id, stock_id, qty_issued) - VALUES (".db_escape($number).", ".db_escape($item->stock_id).", " - .db_escape($item->quantity).")"; + $sql = "INSERT INTO ".TB_PREF."wo_issue_items (issue_id, stock_id, qty_issued, unit_cost) + SELECT ".db_escape($number).",".db_escape($item->stock_id).",".db_escape($item->quantity).", material_cost + FROM ".TB_PREF."stock_master + WHERE stock_id=".db_escape($item->stock_id); + db_query($sql,"A work order issue item could not be added"); $standard_cost = get_standard_cost($item->stock_id); $issue_cost = $standard_cost * $item->quantity; - $issue = get_stock_gl_code($item->stock_id); + $stockitem = get_item($item->stock_id); // Compatibility for Service Items - if (!is_service($issue["mb_flag"])) - $ivaccount = $issue["inventory_account"]; + if (!is_service($stockitem["mb_flag"])) + $ivaccount = $stockitem["inventory_account"]; else - $ivaccount = $issue["assembly_account"]; + $ivaccount = $stockitem["assembly_account"]; $total_cost += add_gl_trans_std_cost(ST_WORKORDER, $woid, $date_, $ivaccount, 0, 0, - $date_.": "._("Issue of")." ".$stockitem["description"], -$issue_cost); + $date_.": "._("Issue of")." ".$stockitem["description"], -$issue_cost); $issue_total += $issue_cost; - } - if ($issue_total != 0) - // Apply cost to QOH as adjustment only - add_issue_cost($details['stock_id'], $details['units_reqd'], $date_, $issue_total, true); - $issue = get_stock_gl_code($details['stock_id']); - $stockitem = get_item($details['stock_id']); - add_gl_trans_std_cost(ST_WORKORDER, $woid, $date_, $issue["inventory_account"], - 0, 0, $date_.": "._("Issue to")." ".$stockitem["description"], -$total_cost); + } + + if ($issue_total != 0) // Apply cost to QOH as adjustment only + add_issue_cost($wo['stock_id'], $wo['units_reqd'], $date_, $issue_total, true); + + $stockitem = get_item($wo['stock_id']); + $wip_account = get_company_pref('wip_act'); + + if (!$wip_account) // backward compatibility + $wip_account = $stockitem["inventory_account"]; + + add_gl_trans_std_cost(ST_WORKORDER, $woid, $date_, $wip_account, + 0, 0, $date_.": "._("Issue to")." ".$stockitem["description"], -$total_cost); if ($memo_) add_comments(ST_MANUISSUE, $number, $date_, $memo_); @@ -97,6 +89,7 @@ function add_work_order_issue($woid, $ref, $to_work_order, $items, $location, $w $args->trans_no = $number; hook_db_postwrite($args, ST_MANUISSUE); + commit_transaction(); } diff --git a/manufacturing/includes/db/work_order_produce_items_db.inc b/manufacturing/includes/db/work_order_produce_items_db.inc index d2bbe0bd..9ce682b3 100644 --- a/manufacturing/includes/db/work_order_produce_items_db.inc +++ b/manufacturing/includes/db/work_order_produce_items_db.inc @@ -14,26 +14,14 @@ function work_order_produce($woid, $ref, $quantity, $date_, $memo_, $close_wo) global $Refs; begin_transaction(); + $args = func_get_args(); $args = (object)array_combine(array('woid', 'ref', 'quantity', 'date_', 'memo_','close_wo'), $args); $args->trans_no = 0; hook_db_prewrite($args, ST_MANURECEIVE); - $details = get_work_order($woid); - - if (strlen($details[0]) == 0) - { - echo _("The order number sent is not valid."); - exit; - } - - if (work_order_is_closed($woid)) - { - display_error("UNEXPECTED : Producing Items for a closed Work Order"); - cancel_transaction(); - exit; - } + $product = get_work_order($woid); $date = date2sql($date_); @@ -46,38 +34,52 @@ function work_order_produce($woid, $ref, $quantity, $date_, $memo_, $close_wo) $id = db_insert_id(); // ------------------------------------------------------------------------- + // insert -ve and update averaged component unit cost for BOM usage (in wo_requirements) + work_order_receive_costs($woid, $product["stock_id"], $quantity, $date_, $id); - work_order_quick_costs($woid, $details["stock_id"], $quantity, $date_, $id); - // ------------------------------------------------------------------------- + // update wo quantity and close wo if requested (or finished) + $closed = work_order_update_finished_quantity($woid, $quantity, $date_, $close_wo); - // Stamp BOM cost to finished item - $m_cost = 0; - $result = get_bom($details["stock_id"]); - while ($bom_item = db_fetch($result)) - { - $standard_cost = get_standard_cost($bom_item['component']); - $m_cost += ($bom_item['quantity'] * $standard_cost); - } - // new 2015.10.15 - // additilnal costs. - if (work_order_has_issues($woid)) + // unit_cost is known when WO is finished, then generate +ve for all items + if ($closed) { - $res = get_additional_issues($woid); - while ($issue = db_fetch($res)) + // 1. calculate sums of material/labour/overhead costs + + // sum collected BOM material & labour costs (no way for separate overhead here for now - needs flag in bom or stock_master) + $bom = get_wo_requirements($woid); + $m_cost = $l_cost = 0; + while ($component = db_fetch($bom)) { - $standard_cost = get_standard_cost($issue['stock_id']); - $m_cost += ($issue['qty_issued'] * $standard_cost) / $quantity; + if (!is_service($component["mb_flag"])) + $m_cost += $component['unit_cost']*$component['units_issued']; + else + $l_cost += $component['unit_cost']*$component['units_issued']; } - } - $m_cost += (get_gl_wo_cost($woid, WO_LABOUR) / $quantity); - $m_cost += (get_gl_wo_cost($woid, WO_OVERHEAD) / $quantity); - // insert a +ve stock move for the item being manufactured - // negative means "unproduce" or unassemble - add_stock_move(ST_MANURECEIVE, $details["stock_id"], $id, - $details["loc_code"], $date_, $ref, $quantity, $m_cost); - // update wo quantity and close wo if requested - work_order_update_finished_quantity($woid, $quantity, $close_wo); + // add additional material issues + $issues = get_additional_issues($woid); + while ($issue = db_fetch($issues)) + { + if (!is_service($issue["mb_flag"])) + $m_cost += $issue['unit_cost']*$issue['qty_issued']; + else + $l_cost += $issue['unit_cost']*$issue['qty_issued']; + } + + // and additional costs + $o_cost = get_gl_wo_cost($woid, WO_OVERHEAD); + $l_cost += get_gl_wo_cost($woid, WO_LABOUR); + + $unit_cost = ($o_cost+$m_cost+$l_cost)/($product['units_issued']+$quantity); + + update_material_cost($product['stock_id'], $product['units_issued']+$quantity, $unit_cost, $date_); + // FIXME: ? +// update_labour_cost(stock_id, qty, unit_cost); +// update_overheads_cost(stock_id, qty, unit_cost); + + add_stock_move(ST_WORKORDER, $product["stock_id"], $woid, + $product["loc_code"], $date_, $ref, $product['units_issued']+$quantity, $unit_cost); + } if ($memo_) add_comments(ST_MANURECEIVE, $id, $date_, $memo_); @@ -87,9 +89,44 @@ function work_order_produce($woid, $ref, $quantity, $date_, $memo_, $close_wo) $args->trans_no = $id; hook_db_postwrite($args, ST_MANURECEIVE); + commit_transaction(); } +/* + Process component usage: generate and post stock move, update average component cost. +*/ +function work_order_receive_costs($woid, $stock_id, $quantity, $date_, $rcv_no) +{ + $result = get_wo_requirements($woid); + + // credit all the components + $total_cost = 0; + while ($bom_item = db_fetch($result)) + { + + $bom_cost = $bom_item["ComponentCost"] * $quantity; + + update_wo_requirement_issued($bom_item['id'], $bom_item["units_req"] * $quantity, $bom_item["ComponentCost"]); + + // insert a -ve stock move for each item + add_stock_move(ST_MANURECEIVE, $bom_item["stock_id"], $rcv_no, + $bom_item["loc_code"], $date_, "", -$bom_item["units_req"] * $quantity, $bom_item["ComponentCost"], 0); + + if (!is_service($bom_item["mb_flag"])) + $ivaccount = $bom_item["inventory_account"]; + else + $ivaccount = $bom_item["assembly_account"]; + + $memo = $date_.": ".$bom_item["units_req"] ." * ".$bom_item["description"]; + $total_cost += add_gl_trans_std_cost(ST_WORKORDER, $woid, $date_, $ivaccount, 0, 0, + $memo, -$bom_cost); + } + + add_gl_trans_std_cost(ST_WORKORDER, $woid, $date_, get_company_pref('wip_act'), + 0, 0, $memo, -$total_cost); +} + //-------------------------------------------------------------------------------------------- function get_work_order_produce($id) diff --git a/manufacturing/includes/db/work_order_requirements_db.inc b/manufacturing/includes/db/work_order_requirements_db.inc index 9422961f..395ad653 100644 --- a/manufacturing/includes/db/work_order_requirements_db.inc +++ b/manufacturing/includes/db/work_order_requirements_db.inc @@ -12,7 +12,10 @@ function get_wo_requirements($woid) { $sql = "SELECT req.*, item.description, item.mb_flag, loc.location_name, - center.name AS WorkCentreDescription + center.name AS WorkCentreDescription, + (item.material_cost+item.overhead_cost+item.labour_cost) as ComponentCost, + item.inventory_account, + item.assembly_account FROM (".TB_PREF."wo_requirements req," .TB_PREF."locations loc," .TB_PREF."workcentres center) @@ -24,25 +27,27 @@ function get_wo_requirements($woid) return db_query($sql, "The work order requirements could not be retrieved"); } +function get_requirements_costs($woid) +{ + $sql = "SELECT SUM(units_req*unit_cost) cost + FROM ".TB_PREF."wo_requirements + WHERE workorder_id=".db_escape($woid); + + $result = db_query($sql, "The work order requirements costs could not be retrieved"); + $costs = db_fetch($result); + + return $costs['cost'] ? $costs['cost'] : 0; +} + //-------------------------------------------------------------------------------------- function create_wo_requirements($woid, $stock_id) { - // create Work Order Requirements based on the bom - $result = get_bom($stock_id); - - while ($myrow = db_fetch($result)) - { + $sql = "INSERT INTO ".TB_PREF."wo_requirements (workorder_id, stock_id, workcentre, units_req, loc_code) + SELECT ".db_escape($woid).", component, workcentre_added, quantity, loc_code + FROM ".TB_PREF."bom WHERE parent=".db_escape($stock_id); - $sql = "INSERT INTO ".TB_PREF."wo_requirements (workorder_id, stock_id, workcentre, units_req, loc_code) - VALUES (".db_escape($woid).", '" . - $myrow["component"] . "', '" . - $myrow["workcentre_added"] . "', '" . - $myrow["quantity"] . "', '" . - $myrow["loc_code"] . "')"; - - db_query($sql, "The work order requirements could not be added"); - } + db_query($sql, "The work order requirements could not be added"); } //-------------------------------------------------------------------------------------- @@ -55,11 +60,15 @@ function delete_wo_requirements($woid) //-------------------------------------------------------------------------------------- - -function update_wo_requirement_issued($woid, $stock_id, $quantity) +/* + Update total component units issued and average component cost +*/ +function update_wo_requirement_issued($id, $quantity, $cost) { - $sql = "UPDATE ".TB_PREF."wo_requirements SET units_issued = units_issued + ".db_escape($quantity)." - WHERE workorder_id = ".db_escape($woid)." AND stock_id = ".db_escape($stock_id); + $sql = "UPDATE ".TB_PREF."wo_requirements SET + unit_cost = (units_issued*unit_cost+".$quantity*$cost.")/(units_issued+".$quantity."), + units_issued = units_issued + ".db_escape($quantity)." + WHERE id = ".db_escape($id); db_query($sql, "The work requirements issued quantity couldn't be updated"); } @@ -68,8 +77,8 @@ function update_wo_requirement_issued($woid, $stock_id, $quantity) function void_wo_requirements($woid) { - $sql = "UPDATE ".TB_PREF."wo_requirements SET units_issued = 0 WHERE workorder_id = " - .db_escape($woid); + $sql = "UPDATE ".TB_PREF."wo_requirements SET units_issued = 0 + WHERE workorder_id = ".db_escape($woid); db_query($sql, "The work requirements issued quantity couldn't be voided"); } diff --git a/manufacturing/includes/db/work_orders_db.inc b/manufacturing/includes/db/work_orders_db.inc index d59522cf..8dcaaed3 100644 --- a/manufacturing/includes/db/work_orders_db.inc +++ b/manufacturing/includes/db/work_orders_db.inc @@ -9,25 +9,33 @@ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the License here . ***********************************************************************/ -//-------------------------------------------------------------------------------------- - +/* + Common entry procedure for all work order types +*/ function add_work_order($wo_ref, $loc_code, $units_reqd, $stock_id, $type, $date_, $required_by, $memo_, $costs, $cr_acc, $labour, $cr_lab_acc) { - global $Refs; - if (!($type == WO_ADVANCED)) return add_work_order_quick($wo_ref, $loc_code, $units_reqd, $stock_id, $type, $date_, $memo_, $costs, $cr_acc, $labour, $cr_lab_acc); + else + return add_work_order_advanced($wo_ref, $loc_code, $units_reqd, $stock_id, $date_, $required_by, $memo_, $costs, $cr_acc, $labour, $cr_lab_acc); +} + +/* + Advanced wrok order entry. +*/ +function add_work_order_advanced($wo_ref, $loc_code, $units_reqd, $stock_id, + $date_, $required_by, $memo_, $costs, $cr_acc, $labour, $cr_lab_acc) +{ + global $Refs; begin_transaction(); $args = func_get_args(); $args = (object)array_combine(array('wo_ref', 'loc_code', 'units_reqd', 'stock_id', - 'type', 'date_', 'required_by', 'memo_', 'costs', 'cr_acc', 'labour', 'cr_lab_acc'), $args); + 'date_', 'required_by', 'memo_', 'costs', 'cr_acc', 'labour', 'cr_lab_acc'), $args); $args->woid = 0; hook_db_prewrite($args, ST_WORKORDER); - add_material_cost($stock_id, $units_reqd, $date_); - $date = date2sql($date_); $required = date2sql($required_by); @@ -35,7 +43,7 @@ function add_work_order($wo_ref, $loc_code, $units_reqd, $stock_id, type, date_, required_by) VALUES (".db_escape($wo_ref).", ".db_escape($loc_code).", " .db_escape($units_reqd).", ".db_escape($stock_id).", - ".db_escape($type).", '$date', ".db_escape($required).")"; + ".WO_ADVANCED.", '$date', ".db_escape($required).")"; db_query($sql, "could not add work order"); $woid = db_insert_id(); @@ -47,6 +55,7 @@ function add_work_order($wo_ref, $loc_code, $units_reqd, $stock_id, $args->woid = $woid; hook_db_postwrite($args, ST_WORKORDER); + commit_transaction(); return $woid; @@ -55,7 +64,7 @@ function add_work_order($wo_ref, $loc_code, $units_reqd, $stock_id, //-------------------------------------------------------------------------------------- function update_work_order($woid, $loc_code, $units_reqd, $stock_id, - $date_, $required_by, $memo_, $old_stock_id, $old_qty) + $date_, $required_by, $memo_) { begin_transaction(); $args = func_get_args(); @@ -63,9 +72,6 @@ function update_work_order($woid, $loc_code, $units_reqd, $stock_id, 'date_', 'required_by', 'memo_'), $args); hook_db_prewrite($args, ST_WORKORDER); - add_material_cost($old_stock_id, -$old_qty, $date_); - add_material_cost($stock_id, $units_reqd, $date_); - $date = date2sql($date_); $required = date2sql($required_by); @@ -89,8 +95,6 @@ function delete_work_order($woid, $stock_id, $qty, $date) begin_transaction(); hook_db_prevoid(ST_WORKORDER, $woid); - add_material_cost($stock_id, -$qty, $date); - // delete the work order requirements delete_wo_requirements($woid); @@ -119,7 +123,10 @@ function get_work_order($woid, $allow_null=false) $result = db_query($sql, "The work order issues could not be retrieved"); if (!$allow_null && db_num_rows($result) == 0) + { display_db_error("Could not find work order $woid", $sql); + return false; + } return db_fetch($result); } @@ -171,7 +178,7 @@ function release_work_order($woid, $releaseDate, $memo_) released=1 WHERE id = ".db_escape($woid); db_query($sql, "could not release work order"); - // create Work Order Requirements based on the bom + // create wo_requirements records according to current BOM create_wo_requirements($woid, $stock_id); add_comments(ST_WORKORDER, $woid, $releaseDate, $memo_); @@ -199,14 +206,24 @@ function work_order_is_closed($woid) } //-------------------------------------------------------------------------------------- - -function work_order_update_finished_quantity($woid, $quantity, $force_close=0) +/* + Update finished items quantity in work order, and close order either if all the order is produced, + or on user demand. Returns calculated unit cost on close, or null otherwise. +*/ +function work_order_update_finished_quantity($woid, $quantity, $date, $force_close=0) { - $sql = "UPDATE ".TB_PREF."workorders SET units_issued = units_issued + ".db_escape($quantity).", - closed = ((units_issued >= units_reqd) OR ".db_escape($force_close).") - WHERE id = ".db_escape($woid); + + $sql = "UPDATE ".TB_PREF."workorders SET units_issued = units_issued + ".db_escape($quantity)." + WHERE id = ".db_escape($woid). " AND !closed"; db_query($sql, "The work order issued quantity couldn't be updated"); + + $sql = "UPDATE ".TB_PREF."workorders SET closed = ((units_issued >= units_reqd) OR ".db_escape($force_close).") + WHERE id = ".db_escape($woid); + + db_query($sql, "The work order couldn't be closed"); + + return db_num_affected_rows(); // returns 1 if WO has been closed } //-------------------------------------------------------------------------------------- @@ -219,6 +236,18 @@ function void_work_order($woid) $work_order = get_work_order($woid); if (!($work_order["type"] == WO_ADVANCED)) { + // restore average product costs + $date = sql2date($work_order['date_']); + $qty = $work_order['units_reqd']; + add_material_cost($work_order['stock_id'], -$qty, $date); // remove avg. cost for qty + $cost = get_gl_wo_cost($woid, WO_LABOUR); // get the labour cost and reduce avg cost + if ($cost != 0) + add_labour_cost($work_order['stock_id'], -$qty, $date, $cost); + $cost = get_gl_wo_cost($woid, WO_OVERHEAD); // get the overhead cost and reduce avg cost + if ($cost != 0) + add_overhead_cost($work_order['stock_id'], -$qty, $date, $cost); + + // close workorder $sql = "UPDATE ".TB_PREF."workorders SET closed=1,units_reqd=0,units_issued=0 WHERE id = " .db_escape($woid); db_query($sql, "The work order couldn't be voided"); @@ -226,8 +255,8 @@ function void_work_order($woid) // void all related stock moves void_stock_move(ST_WORKORDER, $woid); - // void any related bank/gl trans - void_bank_trans(ST_WORKORDER, $woid, true); + // void any related costing records + void_wo_costing($woid); // clear the requirements units received void_wo_requirements($woid); @@ -236,46 +265,43 @@ function void_work_order($woid) { // void everything inside the work order : issues, productions, payments $date = sql2date($work_order['date_']); - + + add_material_cost($work_order['stock_id'], -$work_order['units_reqd'], $date); // remove avg. cost for qty $result = get_work_order_productions($woid); // check the produced quantity + $qty = 0; while ($row = db_fetch($result)) { - void_work_order_produce($row['id']); - - //Post voided entry if not prevoided explicitly - $void_entry = get_voided_entry(ST_MANURECEIVE, $row['id']); - if ($void_entry) - continue; - $memo_ = _("Voiding Work Order Trans # ").$woid; - add_audit_trail(ST_MANURECEIVE, $row['id'], today(), _("Voided.")."\n".$memo_); - add_voided_entry(ST_MANURECEIVE, $row['id'], today(), $memo_); - } + $qty += $row['quantity']; + // clear the production record + $sql = "UPDATE ".TB_PREF."wo_manufacture SET quantity=0 WHERE id=".$row['id']; + db_query($sql, "Cannot void a wo production"); - $result = get_work_order_issues($woid); + void_stock_move(ST_MANURECEIVE, $row['id']); // and void the stock moves; + } + $result = get_additional_issues($woid); // check the issued quantities $cost = 0; + $issue_no = 0; while ($row = db_fetch($result)) { - void_work_order_issue($row['issue_no']); - - //Post voided entry if not prevoided explicitly - $void_entry = get_voided_entry(ST_MANUISSUE, $row['issue_no']); - if ($void_entry) - continue; - $memo_ = _("Voiding Work Order Trans # ").$woid; - add_audit_trail(ST_MANUISSUE, $row['issue_no'], today(), _("Voided.")."\n".$memo_); - add_voided_entry(ST_MANUISSUE, $row['issue_no'], today(), $memo_); - } + $std_cost = get_standard_cost($row['stock_id']); + $icost = $std_cost * $row['qty_issued']; + $cost += $icost; + if ($issue_no == 0) + $issue_no = $row['issue_no']; + // void the actual issue items and their quantities + $sql = "UPDATE ".TB_PREF."wo_issue_items SET qty_issued = 0 WHERE issue_id=" + .db_escape($row['id']); + db_query($sql,"A work order issue item could not be voided"); + } + if ($issue_no != 0) + void_stock_move(ST_MANUISSUE, $issue_no); // and void the stock moves - //Adust avg labour cost - $cost = get_gl_wo_cost($woid, WO_LABOUR); if ($cost != 0) - add_labour_cost($work_order['stock_id'], 1, $date, -$cost, true); - - //Adust avg overhead cost - $cost = get_gl_wo_cost($woid, WO_OVERHEAD); + add_issue_cost($work_order['stock_id'], -$qty, $date, $cost); + if ($cost != 0) - add_overhead_cost($work_order['stock_id'], 1, $date, -$cost, true); - + add_overhead_cost($work_order['stock_id'], -$qty, $date, $cost); + $sql = "UPDATE ".TB_PREF."workorders SET closed=1,units_reqd=0,units_issued=0 WHERE id = " .db_escape($woid); db_query($sql, "The work order couldn't be voided"); @@ -284,11 +310,12 @@ function void_work_order($woid) void_stock_move(ST_WORKORDER, $woid); // void any related bank/gl trans - void_bank_trans(ST_WORKORDER, $woid, true); + void_wo_costing($woid); // clear the requirements units received void_wo_requirements($woid); } + commit_transaction(); } @@ -348,6 +375,7 @@ function get_sql_for_work_orders($outstanding_only, $stock_id, $location = ALL_T $sql .= " AND workorder.required_by < '$Today' "; } + $sql .= " ORDER BY workorder.id DESC"; return $sql; } @@ -376,6 +404,6 @@ function get_gl_wo_cost($woid, $cost_type) $result = get_gl_wo_cost_trans($woid, $cost_type); while ($row = db_fetch($result)) $cost += -$row['amount']; - return $cost; + return $cost; } diff --git a/manufacturing/includes/db/work_orders_quick_db.inc b/manufacturing/includes/db/work_orders_quick_db.inc index 73b16e62..14570f18 100644 --- a/manufacturing/includes/db/work_orders_quick_db.inc +++ b/manufacturing/includes/db/work_orders_quick_db.inc @@ -9,8 +9,10 @@ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the License here . ***********************************************************************/ -//-------------------------------------------------------------------------------------- - +/* + Quick work order entry, $type is either WO_ASSEMBLY, or WO_UNASSEMBLY + All changes in material and manufactured item stock are made during work order entry. +*/ function add_work_order_quick($wo_ref, $loc_code, $units_reqd, $stock_id, $type, $date_, $memo_, $costs, $cr_acc, $labour, $cr_lab_acc) { @@ -68,7 +70,7 @@ function add_work_order_quick($wo_ref, $loc_code, $units_reqd, $stock_id, $type, // insert a -ve stock move for each item $UnitCost = get_standard_cost($bom_item["component"]); add_stock_move(ST_WORKORDER, $bom_item["component"], $woid, - $bom_item["loc_code"], $date_, $wo_ref, -$item_quantity, $UnitCost, 0, 1, $UnitCost); + $bom_item["loc_code"], $date_, $wo_ref, -$item_quantity, $UnitCost, $UnitCost); } // ------------------------------------------------------------------------- @@ -93,7 +95,7 @@ function add_work_order_quick($wo_ref, $loc_code, $units_reqd, $stock_id, $type, // ------------------------------------------------------------------------- - work_order_quick_costs($woid, $stock_id, $units_reqd, $date_, 0, $costs, $cr_acc, $labour, $cr_lab_acc); + work_order_quick_costs($woid, $stock_id, $units_reqd, $date_, $costs, $cr_acc, $labour, $cr_lab_acc); // ------------------------------------------------------------------------- @@ -104,76 +106,57 @@ function add_work_order_quick($wo_ref, $loc_code, $units_reqd, $stock_id, $type, $args->woid = $woid; hook_db_postwrite($args, ST_WORKORDER); + commit_transaction(); return $woid; } //-------------------------------------------------------------------------------------- -function work_order_quick_costs($woid, $stock_id, $units_reqd, $date_, $advanced=0, $costs=0, $cr_acc="", $labour=0, $cr_lab_acc="") +function work_order_quick_costs($woid, $stock_id, $units_reqd, $date_, $costs, $cr_acc, $labour, $cr_lab_acc) { - //global $wo_cost_types; $result = get_bom($stock_id); // credit all the components $total_cost = 0; - $total_material_cost = 0; while ($bom_item = db_fetch($result)) { $bom_accounts = get_stock_gl_code($bom_item["component"]); - $bom_cost = $bom_item["ComponentCost"] * $units_reqd; + $bom_cost = $bom_item["ComponentCost"] * $units_reqd; // ?? * $bom_item["quantity"] $memo = $bom_item["quantity"] ." * ".$bom_item["description"]; - if ($advanced) - { - $memo = $date_.": ".$memo; - update_wo_requirement_issued($woid, $bom_item['component'], $bom_item["quantity"] * $units_reqd); - // insert a -ve stock move for each item - $UnitCost = get_standard_cost($bom_item["component"]); - add_stock_move(ST_MANURECEIVE, $bom_item["component"], $advanced, - $bom_item["loc_code"], $date_, "", -$bom_item["quantity"] * $units_reqd, $UnitCost, 0, 1, $UnitCost); - } - //Compatibility for Service Items + if (!is_service($bom_accounts["mb_flag"])) $ivaccount = $bom_accounts["inventory_account"]; else - $ivaccount = $bom_accounts["assembly_account"]; // changed 2015.10.14 by Petros . + $ivaccount = $bom_accounts["assembly_account"]; $total_cost += add_gl_trans_std_cost(ST_WORKORDER, $woid, $date_, $ivaccount, 0, 0, $memo, -$bom_cost); - $total_material_cost += $bom_cost; } - if ($advanced) - { - //Material cost added at time of production as per BOM at the time and in adjustment mode. - add_material_cost($stock_id, $units_reqd, $date_, true, $woid); - // $wo = get_work_order($woid); - // also take the additional issues - // moved to work_order_issues_db.inc - } // credit additional costs - $item_accounts = get_stock_gl_code($stock_id); + + $stockitem = get_item($stock_id); + // credit additional costs if ($costs != 0.0) { - add_wo_costs_journal($woid, $costs, WO_OVERHEAD, $cr_acc, $item_accounts["assembly_account"], - $date_, $item_accounts["dimension_id"], $item_accounts["dimension2_id"]); + add_wo_costs_journal($woid, $costs, WO_OVERHEAD, $cr_acc, $stockitem["assembly_account"], + $date_, $stockitem["dimension_id"], $stockitem["dimension2_id"]); } - if ($labour != 0.0) // only for quick + + if ($labour != 0.0) { - add_wo_costs_journal($woid, $labour, WO_LABOUR, $cr_lab_acc, $item_accounts["assembly_account"], - $date_, $item_accounts["dimension_id"], $item_accounts["dimension2_id"]); + add_wo_costs_journal($woid, $labour, WO_LABOUR, $cr_lab_acc, $stockitem["assembly_account"], + $date_, $stockitem["dimension_id"], $stockitem["dimension2_id"]); } + // debit total components $total_cost - $stockitem = get_item($stock_id); $memo = _("Produced")." ".$units_reqd. " * ".$stockitem["description"]; - if ($advanced) - $memo = $date_.": ".$memo; - add_gl_trans_std_cost(ST_WORKORDER, $woid, $date_, $item_accounts["inventory_account"], + + add_gl_trans_std_cost(ST_WORKORDER, $woid, $date_, $stockitem["inventory_account"], 0, 0, $memo, -$total_cost); - if (!$advanced) - { - $UnitWOCost = ($total_material_cost + $costs + $labour)/$units_reqd; - update_stock_move(ST_WORKORDER, $woid, $stock_id, $UnitWOCost); - } + + $UnitWOCost = ($total_cost + $costs + $labour)/$units_reqd; + update_stock_move(ST_WORKORDER, $woid, $stock_id, $UnitWOCost); } diff --git a/manufacturing/manage/bom_edit.php b/manufacturing/manage/bom_edit.php index 5d60f317..35230ddb 100644 --- a/manufacturing/manage/bom_edit.php +++ b/manufacturing/manage/bom_edit.php @@ -143,7 +143,10 @@ start_row(); stock_manufactured_items_list_cells(_("Select a manufacturable item:"), 'stock_id', null, false, true); end_row(); if (list_updated('stock_id')) +{ + $selected_id = -1; $Ajax->activate('_page_body'); +} end_table(); br(); @@ -192,7 +195,7 @@ start_form(); echo ""; end_row(); } - hidden('stock_id', $selected_parent); +// hidden('stock_id', $selected_parent); locations_list_row(_("Location to Draw From:"), 'loc_code', null); workcenter_list_row(_("Work Centre Added:"), 'workcentre_added', null); diff --git a/manufacturing/view/wo_issue_view.php b/manufacturing/view/wo_issue_view.php index 0738af37..ad0dfda9 100644 --- a/manufacturing/view/wo_issue_view.php +++ b/manufacturing/view/wo_issue_view.php @@ -73,7 +73,7 @@ function display_wo_issue_details($issue_no) else { start_table(TABLESTYLE); - $th = array(_("Component"), _("Quantity"), _("Units")); + $th = array(_("Component"), _("Quantity"), _("Units"), _("Unit Cost")); table_header($th); @@ -90,6 +90,7 @@ function display_wo_issue_details($issue_no) label_cell($myrow["stock_id"] . " - " . $myrow["description"]); qty_cell($myrow["qty_issued"], false, get_qty_dec($myrow["stock_id"])); label_cell($myrow["units"]); + amount_cell($myrow["unit_cost"]); end_row();; $j++; diff --git a/manufacturing/work_order_entry.php b/manufacturing/work_order_entry.php index be602d93..cafdf211 100644 --- a/manufacturing/work_order_entry.php +++ b/manufacturing/work_order_entry.php @@ -259,8 +259,7 @@ if (isset($_POST['UPDATE_ITEM']) && can_process()) { update_work_order($selected_id, $_POST['StockLocation'], input_num('quantity'), - $_POST['stock_id'], $_POST['date_'], $_POST['RequDate'], $_POST['memo_'], - $_POST['old_stk_id'], $_POST['old_qty']); + $_POST['stock_id'], $_POST['date_'], $_POST['RequDate'], $_POST['memo_']); new_doc_date($_POST['date_']); meta_forward($_SERVER['PHP_SELF'], "UpdatedID=$selected_id"); } @@ -355,8 +354,6 @@ if (isset($selected_id)) hidden('released', $_POST['released']); hidden('released_date', $_POST['released_date']); hidden('selected_id', $selected_id); - hidden('old_qty', $myrow["units_reqd"]); - hidden('old_stk_id', $myrow["stock_id"]); label_row(_("Reference:"), $_POST['wo_ref']); label_row(_("Type:"), $wo_types_array[$_POST['type']]); diff --git a/reporting/includes/html_entity_decode_php4.php b/reporting/includes/html_entity_decode_php4.php index df1c7555..9b90e4bf 100644 --- a/reporting/includes/html_entity_decode_php4.php +++ b/reporting/includes/html_entity_decode_php4.php @@ -332,10 +332,9 @@ function html_entity_decode_php4($text_to_convert) { "<" => "<" ); $return_text = strtr($text_to_convert, $htmlentities_table); - - // 07.11.2014, from php 5.3.0 fixed deprecated preg_replace with the /e flag. Joe + if (version_compare(PHP_VERSION, '5.3.0') >= 0 && function_exists("preg_replace_callback")) - { + { $return_text = preg_replace_callback('~&#x([0-9a-f]+);~i', function ($m){ return chr(hexdec($m[1]));}, $return_text); $return_text = preg_replace_callback('~&#([0-9]+);~', function ($m){ return chr($m[1]);}, $return_text); } diff --git a/sql/alter2.4rc1.sql b/sql/alter2.4rc1.sql index 5753ab79..47ba2a17 100644 --- a/sql/alter2.4rc1.sql +++ b/sql/alter2.4rc1.sql @@ -25,3 +25,6 @@ INSERT IGNORE INTO `0_sys_prefs` VALUES ('use_manufacturing','setup.company', 'tinyint', 1, '1'), ('use_fixed_assets','setup.company', 'tinyint', 1, '1'), ('wip_act', 'glsetup.manuf', 'varchar', '15', ''); + +ALTER TABLE `0_wo_issue_items` ADD COLUMN `unit_cost` double NOT NULL default '0' AFTER `qty_issued`; +ALTER TABLE `0_wo_requirements` CHANGE COLUMN `std_cost` `unit_cost` double NOT NULL default '0'; diff --git a/sql/en_US-demo.sql b/sql/en_US-demo.sql index 36b006c8..560d354c 100644 --- a/sql/en_US-demo.sql +++ b/sql/en_US-demo.sql @@ -2521,6 +2521,7 @@ CREATE TABLE IF NOT EXISTS `0_wo_issue_items` ( `stock_id` varchar(40) DEFAULT NULL, `issue_id` int(11) DEFAULT NULL, `qty_issued` double DEFAULT NULL, + `unit_cost` double NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB; @@ -2562,7 +2563,7 @@ CREATE TABLE IF NOT EXISTS `0_wo_requirements` ( `stock_id` char(20) NOT NULL DEFAULT '', `workcentre` int(11) NOT NULL DEFAULT '0', `units_req` double NOT NULL DEFAULT '1', - `std_cost` double NOT NULL DEFAULT '0', + `unit_cost` double NOT NULL DEFAULT '0', `loc_code` char(5) NOT NULL DEFAULT '', `units_issued` double NOT NULL DEFAULT '0', PRIMARY KEY (`id`), diff --git a/sql/en_US-new.sql b/sql/en_US-new.sql index 46efaf0f..a7d345ea 100644 --- a/sql/en_US-new.sql +++ b/sql/en_US-new.sql @@ -2228,6 +2228,7 @@ CREATE TABLE IF NOT EXISTS `0_wo_issue_items` ( `stock_id` varchar(40) default NULL, `issue_id` int(11) default NULL, `qty_issued` double default NULL, + `unit_cost` double NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB; @@ -2269,7 +2270,7 @@ CREATE TABLE IF NOT EXISTS `0_wo_requirements` ( `stock_id` char(20) NOT NULL default '', `workcentre` int(11) NOT NULL default '0', `units_req` double NOT NULL default '1', - `std_cost` double NOT NULL default '0', + `unit_cost` double NOT NULL default '0', `loc_code` char(5) NOT NULL default '', `units_issued` double NOT NULL default '0', PRIMARY KEY (`id`), -- 2.30.2