X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;ds=sidebyside;f=manufacturing%2Fincludes%2Fdb%2Fwork_orders_db.inc;h=2aed7c1912876680a39523f78f77e181dc342497;hb=6b1153e8b02b69604716ba0fa2d6fd5f7de133a4;hp=a88f96d93804a66585a70cf2441eee654198c894;hpb=be1b322fa59f83bb8073ddd815b57ec697c47a45;p=fa-stable.git diff --git a/manufacturing/includes/db/work_orders_db.inc b/manufacturing/includes/db/work_orders_db.inc index a88f96d9..2aed7c19 100644 --- a/manufacturing/includes/db/work_orders_db.inc +++ b/manufacturing/includes/db/work_orders_db.inc @@ -11,7 +11,7 @@ ***********************************************************************/ //-------------------------------------------------------------------------------------- -function add_material_cost($stock_id, $qty, $date_) +function add_material_cost($stock_id, $qty, $date_, $advanced=false) { $m_cost = 0; $result = get_bom($stock_id); @@ -20,28 +20,41 @@ function add_material_cost($stock_id, $qty, $date_) $standard_cost = get_standard_cost($bom_item['component']); $m_cost += ($bom_item['quantity'] * $standard_cost); } - $dec = user_price_dec(); - $m_cost = price_decimal_format($m_cost, $dec); - $sql = "SELECT material_cost FROM ".TB_PREF."stock_master WHERE stock_id = " + $bom_cost = $m_cost; + + $sql = "SELECT material_cost, labour_cost, overhead_cost FROM ".TB_PREF."stock_master WHERE stock_id = " .db_escape($stock_id); $result = db_query($sql); $myrow = db_fetch($result); $material_cost = $myrow['material_cost']; - $qoh = get_qoh_on_date($stock_id, null, $date_); - if ($qoh + $qty <= 0) - $material_cost = 0; - else + + if ($advanced) + { + //reduce overhead_cost and labour_cost from price as those will remain as is + $m_cost = $m_cost - $myrow['labour_cost'] - $myrow['overhead_cost']; + } + + $qoh = get_qoh_on_date($stock_id); + $cost_adjust = false; + if ($qoh < 0) + { + if ($qoh + $qty >= 0) + $cost_adjust = true; + $qoh = 0; + } + if ($qoh + $qty != 0) $material_cost = ($qoh * $material_cost + $qty * $m_cost) / ($qoh + $qty); - $material_cost = round2($material_cost, $dec); - $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=$material_cost - WHERE stock_id=".db_escape($stock_id); + + if ($advanced && $cost_adjust) // new 2010-02-10 + 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"); } -function add_overhead_cost($stock_id, $qty, $date_, $costs) +function add_overhead_cost($stock_id, $qty, $date_, $costs, $adj_only=false) { - $dec = user_price_dec(); - $costs = price_decimal_format($costs, $dec); if ($qty != 0) $costs /= $qty; $sql = "SELECT overhead_cost FROM ".TB_PREF."stock_master WHERE stock_id = " @@ -49,21 +62,52 @@ function add_overhead_cost($stock_id, $qty, $date_, $costs) $result = db_query($sql); $myrow = db_fetch($result); $overhead_cost = $myrow['overhead_cost']; - $qoh = get_qoh_on_date($stock_id, null, $date_); - if ($qoh + $qty <= 0) - $overhead_cost = 0; + $qoh = get_qoh_on_date($stock_id); + if ($qoh < 0) + $qoh = 0; + if ($adj_only) + { + if ($qty != 0) + $costs = $qty * $costs; + if ($qoh>0) + $overhead_cost = ($qoh * $overhead_cost + $costs) / $qoh; + else // Journal Entry if QOH is 0/negative + { + global $Refs; + + $id = get_next_trans_no(ST_JOURNAL); + $ref = $Refs->get_next(ST_JOURNAL); + + $stock_gl_code = get_stock_gl_code($stock_id); + $memo = "WO Overhead cost settlement JV for zero/negative respository of ".$stock_id; + //Reverse the inventory effect if $qoh <=0 + add_gl_trans_std_cost(ST_JOURNAL, $id, $date_, + $stock_gl_code["inventory_account"], + $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo, + -$costs); + //GL Posting to inventory adjustment account + add_gl_trans_std_cost(ST_JOURNAL, $id, $date_, + $stock_gl_code["adjustment_account"], + $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo, + $costs); + + add_audit_trail(ST_JOURNAL, $id, $date_); + add_comments(ST_JOURNAL, $id, $date_, $memo); + $Refs->save(ST_JOURNAL, $id, $ref); + } + } else - $overhead_cost = ($qoh * $overhead_cost + $qty * $costs) / ($qoh + $qty); - $overhead_cost = round2($overhead_cost, $dec); + { + if ($qoh + $qty != 0) + $overhead_cost = ($qoh * $overhead_cost + $qty * $costs) / ($qoh + $qty); + } $sql = "UPDATE ".TB_PREF."stock_master SET overhead_cost=".db_escape($overhead_cost)." WHERE stock_id=".db_escape($stock_id); db_query($sql,"The cost details for the inventory item could not be updated"); } -function add_labour_cost($stock_id, $qty, $date_, $costs) +function add_labour_cost($stock_id, $qty, $date_, $costs, $adj_only=false) { - $dec = user_price_dec(); - $costs = price_decimal_format($costs, $dec); if ($qty != 0) $costs /= $qty; $sql = "SELECT labour_cost FROM ".TB_PREF."stock_master WHERE stock_id = " @@ -71,18 +115,51 @@ function add_labour_cost($stock_id, $qty, $date_, $costs) $result = db_query($sql); $myrow = db_fetch($result); $labour_cost = $myrow['labour_cost']; - $qoh = get_qoh_on_date($stock_id, null, $date_); - if ($qoh + $qty <= 0) - $labour_cost = 0; + $qoh = get_qoh_on_date($stock_id); + if ($qoh < 0) + $qoh = 0; + if ($adj_only) + { + if ($qty != 0) + $costs = $qty * $costs; + if ($qoh>0) + $labour_cost = ($qoh * $labour_cost + $costs) / $qoh; + else // Journal Entry if QOH is 0/negative + { + global $Refs; + + $id = get_next_trans_no(ST_JOURNAL); + $ref = $Refs->get_next(ST_JOURNAL); + + $stock_gl_code = get_stock_gl_code($stock_id); + $memo = "WO labour cost settlement JV for zero/negative respository of ".$stock_id; + //Reverse the inventory effect if $qoh <=0 + add_gl_trans_std_cost(ST_JOURNAL, $id, $date_, + $stock_gl_code["inventory_account"], + $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo, + -$costs); + //GL Posting to inventory adjustment account + add_gl_trans_std_cost(ST_JOURNAL, $id, $date_, + $stock_gl_code["adjustment_account"], + $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo, + $costs); + + add_audit_trail(ST_JOURNAL, $id, $date_); + add_comments(ST_JOURNAL, $id, $date_, $memo); + $Refs->save(ST_JOURNAL, $id, $ref); + } + } else - $labour_cost = ($qoh * $labour_cost + $qty * $costs) / ($qoh + $qty); - $labour_cost = round2($labour_cost, $dec); + { + if ($qoh + $qty != 0) + $labour_cost = ($qoh * $labour_cost + $qty * $costs) / ($qoh + $qty); + } $sql = "UPDATE ".TB_PREF."stock_master SET labour_cost=".db_escape($labour_cost)." WHERE stock_id=".db_escape($stock_id); db_query($sql,"The cost details for the inventory item could not be updated"); } -function add_issue_cost($stock_id, $qty, $date_, $costs) +function add_issue_cost($stock_id, $qty, $date_, $costs, $adj_only=false) { if ($qty != 0) $costs /= $qty; @@ -91,14 +168,45 @@ function add_issue_cost($stock_id, $qty, $date_, $costs) $result = db_query($sql); $myrow = db_fetch($result); $material_cost = $myrow['material_cost']; - $dec = user_price_dec(); - $material_cost = price_decimal_format($material_cost, $dec); - $qoh = get_qoh_on_date($stock_id, null, $date_); - if ($qoh + $qty <= 0) - $material_cost = 0; + $qoh = get_qoh_on_date($stock_id); + if ($qoh < 0) + $qoh = 0; + if ($adj_only) + { + if ($qty != 0) + $costs = $qty * $costs; + if ($qoh>0) + $material_cost = $costs / $qoh; + else // Journal Entry if QOH is 0/negative + { + global $Refs; + + $id = get_next_trans_no(ST_JOURNAL); + $ref = $Refs->get_next(ST_JOURNAL); + + $stock_gl_code = get_stock_gl_code($stock_id); + $memo = "WO Issue settlement JV for zero/negative respository of ".$stock_id; + //Reverse the inventory effect if $qoh <=0 + add_gl_trans_std_cost(ST_JOURNAL, $id, $date_, + $stock_gl_code["inventory_account"], + $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo, + -$costs); + //GL Posting to inventory adjustment account + add_gl_trans_std_cost(ST_JOURNAL, $id, $date_, + $stock_gl_code["adjustment_account"], + $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo, + $costs); + + add_audit_trail(ST_JOURNAL, $id, $date_); + add_comments(ST_JOURNAL, $id, $date_, $memo); + $Refs->save(ST_JOURNAL, $id, $ref); + } + } else - $material_cost = ($qty * $costs) / ($qoh + $qty); - $material_cost = round2($material_cost, $dec); + { + if ($qoh + $qty != 0) + $material_cost = ($qty * $costs) / ($qoh + $qty); + } $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=material_cost+" .db_escape($material_cost) ." WHERE stock_id=".db_escape($stock_id); @@ -114,8 +222,11 @@ function add_work_order($wo_ref, $loc_code, $units_reqd, $stock_id, return add_work_order_quick($wo_ref, $loc_code, $units_reqd, $stock_id, $type, $date_, $memo_, $costs, $cr_acc, $labour, $cr_lab_acc); begin_transaction(); - - add_material_cost($stock_id, $units_reqd, $date_); + $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); + $args->woid = 0; + hook_db_prewrite($args, ST_WORKORDER); $date = date2sql($date_); $required = date2sql($required_by); @@ -134,6 +245,8 @@ function add_work_order($wo_ref, $loc_code, $units_reqd, $stock_id, $Refs->save(ST_WORKORDER, $woid, $wo_ref); add_audit_trail(ST_WORKORDER, $woid, $date_); + $args->woid = $woid; + hook_db_postwrite($args, ST_WORKORDER); commit_transaction(); return $woid; @@ -145,9 +258,10 @@ function update_work_order($woid, $loc_code, $units_reqd, $stock_id, $date_, $required_by, $memo_) { begin_transaction(); - - add_material_cost($_POST['old_stk_id'], -$_POST['old_qty'], $date_); - add_material_cost($stock_id, $units_reqd, $date_); + $args = func_get_args(); + $args = (object)array_combine(array('woid', 'loc_code', 'units_reqd', 'stock_id', + 'date_', 'required_by', 'memo_'), $args); + hook_db_prewrite($args, ST_WORKORDER); $date = date2sql($date_); $required = date2sql($required_by); @@ -163,14 +277,14 @@ function update_work_order($woid, $loc_code, $units_reqd, $stock_id, update_comments(ST_WORKORDER, $woid, null, $memo_); add_audit_trail(ST_WORKORDER, $woid, $date_, _("Updated.")); + hook_db_postwrite($args, ST_WORKORDER); commit_transaction(); } function delete_work_order($woid) { begin_transaction(); - - add_material_cost($_POST['stock_id'], -$_POST['quantity'], $_POST['date_']); + hook_db_prevoid(ST_WORKORDER, $woid); // delete the work order requirements delete_wo_requirements($woid); @@ -189,13 +303,13 @@ function delete_work_order($woid) function get_work_order($woid, $allow_null=false) { - $sql = "SELECT ".TB_PREF."workorders.*, ".TB_PREF."stock_master.description As StockItemName, - ".TB_PREF."locations.location_name, ".TB_PREF."locations.delivery_address - FROM ".TB_PREF."workorders, ".TB_PREF."stock_master, ".TB_PREF."locations - WHERE ".TB_PREF."stock_master.stock_id=".TB_PREF."workorders.stock_id - AND ".TB_PREF."locations.loc_code=".TB_PREF."workorders.loc_code - AND ".TB_PREF."workorders.id=".db_escape($woid)." - GROUP BY ".TB_PREF."workorders.id"; + $sql = "SELECT wo.*,st.description As StockItemName,l.location_name, + l.delivery_address,l.email, l.contact + FROM ".TB_PREF."workorders wo, ".TB_PREF."stock_master st, ".TB_PREF."locations l + WHERE st.stock_id=wo.stock_id + AND l.loc_code=wo.loc_code + AND wo.id=".db_escape($woid)." + GROUP BY wo.id"; $result = db_query($sql, "The work order issues could not be retrieved"); @@ -294,33 +408,167 @@ function work_order_update_finished_quantity($woid, $quantity, $force_close=0) function void_work_order($woid) { - $work_order = get_work_order($woid); + begin_transaction(); + hook_db_prevoid(ST_WORKORDER, $woid); + $work_order = get_work_order($woid); if (!($work_order["type"] == WO_ADVANCED)) { - begin_transaction(); - - $sql = "UPDATE ".TB_PREF."workorders SET closed=1,units_issued=0 WHERE id = " + $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"); // void all related stock moves void_stock_move(ST_WORKORDER, $woid); - // void any related gl trans - void_gl_trans(ST_WORKORDER, $woid, true); + // void any related bank/gl trans + void_bank_trans(ST_WORKORDER, $woid, true); // clear the requirements units received void_wo_requirements($woid); - - commit_transaction(); } else { // void everything inside the work order : issues, productions, payments + $date = sql2date($work_order['date_']); + + $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_); + } + + $result = get_work_order_issues($woid); + $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_); + } + + //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); + if ($cost != 0) + add_overhead_cost($work_order['stock_id'], 1, $date, -$cost, true); + + $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"); + + // void all related stock moves + void_stock_move(ST_WORKORDER, $woid); + + // void any related bank/gl trans + void_bank_trans(ST_WORKORDER, $woid, true); + + // clear the requirements units received + void_wo_requirements($woid); } + commit_transaction(); } +function get_sql_for_work_orders($outstanding_only, $all_items) +{ + $sql = "SELECT + workorder.id, + workorder.wo_ref, + workorder.type, + location.location_name, + item.description, + workorder.units_reqd, + workorder.units_issued, + workorder.date_, + workorder.required_by, + workorder.released_date, + workorder.closed, + workorder.released, + workorder.stock_id, + unit.decimals + FROM ".TB_PREF."workorders as workorder," + .TB_PREF."stock_master as item," + .TB_PREF."item_units as unit," + .TB_PREF."locations as location + WHERE workorder.stock_id=item.stock_id + AND workorder.loc_code=location.loc_code + AND item.units=unit.abbr"; + + if (check_value('OpenOnly') || $outstanding_only != 0) + { + $sql .= " AND workorder.closed=0"; + } + + if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != $all_items) + { + $sql .= " AND workorder.loc_code=".db_escape($_POST['StockLocation']); + } + + if (isset($_POST['OrderNumber']) && $_POST['OrderNumber'] != "") + { + $sql .= " AND workorder.wo_ref LIKE ".db_escape('%'.$_POST['OrderNumber'].'%'); + } + + if (isset($_POST['SelectedStockItem']) && $_POST['SelectedStockItem'] != $all_items) + { + $sql .= " AND workorder.stock_id=".db_escape($_POST['SelectedStockItem']); + } + + if (check_value('OverdueOnly')) + { + $Today = date2sql(Today()); + + $sql .= " AND workorder.required_by < '$Today' "; + } + return $sql; +} + +function get_sql_for_where_used() +{ + $sql = "SELECT + bom.parent, + workcentre.name As WorkCentreName, + location.location_name, + bom.quantity, + parent.description + FROM ".TB_PREF."bom as bom, " + .TB_PREF."stock_master as parent, " + .TB_PREF."workcentres as workcentre, " + .TB_PREF."locations as location + WHERE bom.parent = parent.stock_id + AND bom.workcentre_added = workcentre.id + AND bom.loc_code = location.loc_code + AND bom.component=".db_escape($_POST['stock_id']); + return $sql; +} //-------------------------------------------------------------------------------------- +function get_gl_wo_cost($woid, $cost_type) +{ + $cost = 0; + $result = get_gl_wo_cost_trans($woid, $cost_type); + while ($row = db_fetch($result)) + $cost += -$row['amount']; + return $cost; +} ?> \ No newline at end of file