X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=manufacturing%2Fincludes%2Fdb%2Fwork_orders_db.inc;h=1271f611428277a046f8b70d5c52e1a231d9ec32;hb=279d7962a249da9cbc3074360e3a68a23a31145e;hp=122179f0c0954fc757a2fc6bc26007aaf9cceaee;hpb=80dd97a37f674cc3691fa04af4c29607067566b2;p=fa-stable.git diff --git a/manufacturing/includes/db/work_orders_db.inc b/manufacturing/includes/db/work_orders_db.inc index 122179f0..1271f611 100644 --- a/manufacturing/includes/db/work_orders_db.inc +++ b/manufacturing/includes/db/work_orders_db.inc @@ -20,53 +20,65 @@ function add_material_cost($stock_id, $qty, $date_) $standard_cost = get_standard_cost($bom_item['component']); $m_cost += ($bom_item['quantity'] * $standard_cost); } - $sql = "SELECT material_cost FROM ".TB_PREF."stock_master WHERE stock_id = '$stock_id'"; + $dec = user_price_dec(); + price_decimal_format($m_cost, $dec); + $sql = "SELECT material_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 ($qoh < 0) + $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='$stock_id'"; + 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) { + $dec = user_price_dec(); + price_decimal_format($costs, $dec); if ($qty != 0) $costs /= $qty; - $sql = "SELECT overhead_cost FROM ".TB_PREF."stock_master WHERE stock_id = '$stock_id'"; + $sql = "SELECT overhead_cost FROM ".TB_PREF."stock_master WHERE stock_id = " + .db_escape($stock_id); $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; - else + if ($qoh < 0) + $qoh = 0; + if ($qoh + $qty != 0) $overhead_cost = ($qoh * $overhead_cost + $qty * $costs) / ($qoh + $qty); - $sql = "UPDATE ".TB_PREF."stock_master SET overhead_cost=$overhead_cost - WHERE stock_id='$stock_id'"; + $overhead_cost = round2($overhead_cost, $dec); + $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) { + $dec = user_price_dec(); + price_decimal_format($costs, $dec); if ($qty != 0) $costs /= $qty; - $sql = "SELECT labour_cost FROM ".TB_PREF."stock_master WHERE stock_id = '$stock_id'"; + $sql = "SELECT labour_cost FROM ".TB_PREF."stock_master WHERE stock_id = " + .db_escape($stock_id); $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; - else + if ($qoh < 0) + $qoh = 0; + if ($qoh + $qty != 0) $labour_cost = ($qoh * $labour_cost + $qty * $costs) / ($qoh + $qty); - $sql = "UPDATE ".TB_PREF."stock_master SET labour_cost=$labour_cost - WHERE stock_id='$stock_id'"; + $labour_cost = round2($labour_cost, $dec); + $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"); } @@ -74,17 +86,22 @@ function add_issue_cost($stock_id, $qty, $date_, $costs) { if ($qty != 0) $costs /= $qty; - $sql = "SELECT material_cost FROM ".TB_PREF."stock_master WHERE stock_id = '$stock_id'"; + $sql = "SELECT material_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']; + $dec = user_price_dec(); + price_decimal_format($material_cost, $dec); $qoh = get_qoh_on_date($stock_id, null, $date_); - if ($qoh + $qty <= 0) - $material_cost = 0; - else + if ($qoh < 0) + $qoh = 0; + if ($qoh + $qty != 0) $material_cost = ($qty * $costs) / ($qoh + $qty); - $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=material_cost+$material_cost - WHERE stock_id='$stock_id'"; + $material_cost = round2($material_cost, $dec); + $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=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"); } @@ -105,8 +122,9 @@ function add_work_order($wo_ref, $loc_code, $units_reqd, $stock_id, $sql = "INSERT INTO ".TB_PREF."workorders (wo_ref, loc_code, units_reqd, stock_id, type, date_, required_by) - VALUES (".db_escape($wo_ref).", ".db_escape($loc_code).", $units_reqd, '$stock_id', - $type, '$date', '$required')"; + VALUES (".db_escape($wo_ref).", ".db_escape($loc_code).", " + .db_escape($units_reqd).", ".db_escape($stock_id).", + ".db_escape($type).", '$date', ".db_escape($required).")"; db_query($sql, "could not add work order"); $woid = db_insert_id(); @@ -135,10 +153,10 @@ function update_work_order($woid, $loc_code, $units_reqd, $stock_id, $required = date2sql($required_by); $sql = "UPDATE ".TB_PREF."workorders SET loc_code=".db_escape($loc_code).", - units_reqd=$units_reqd, stock_id='$stock_id', - required_by='$required', + units_reqd=".db_escape($units_reqd).", stock_id=".db_escape($stock_id).", + required_by=".db_escape($required).", date_='$date' - WHERE id = $woid"; + WHERE id = ".db_escape($woid); db_query($sql, "could not update work order"); @@ -158,7 +176,7 @@ function delete_work_order($woid) delete_wo_requirements($woid); // delete the actual work order - $sql = "DELETE FROM ".TB_PREF."workorders WHERE id=$woid"; + $sql = "DELETE FROM ".TB_PREF."workorders WHERE id=".db_escape($woid); db_query($sql,"The work order could not be deleted"); delete_comments(ST_WORKORDER, $woid); @@ -176,7 +194,7 @@ function get_work_order($woid, $allow_null=false) 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=$woid + AND ".TB_PREF."workorders.id=".db_escape($woid)." GROUP BY ".TB_PREF."workorders.id"; $result = db_query($sql, "The work order issues could not be retrieved"); @@ -191,7 +209,7 @@ function get_work_order($woid, $allow_null=false) function work_order_has_productions($woid) { - $sql = "SELECT COUNT(*) FROM ".TB_PREF."wo_manufacture WHERE workorder_id=$woid"; + $sql = "SELECT COUNT(*) FROM ".TB_PREF."wo_manufacture WHERE workorder_id=".db_escape($woid); $result = db_query($sql, "query work order for productions"); $myrow = db_fetch_row($result); @@ -203,7 +221,7 @@ function work_order_has_productions($woid) function work_order_has_issues($woid) { - $sql = "SELECT COUNT(*) FROM ".TB_PREF."wo_issues WHERE workorder_id=$woid"; + $sql = "SELECT COUNT(*) FROM ".TB_PREF."wo_issues WHERE workorder_id=".db_escape($woid); $result = db_query($sql, "query work order for issues"); $myrow = db_fetch_row($result); @@ -231,7 +249,7 @@ function release_work_order($woid, $releaseDate, $memo_) $date = date2sql($releaseDate); $sql = "UPDATE ".TB_PREF."workorders SET released_date='$date', - released=1 WHERE id = $woid"; + released=1 WHERE id = ".db_escape($woid); db_query($sql, "could not release work order"); // create Work Order Requirements based on the bom @@ -247,7 +265,7 @@ function release_work_order($woid, $releaseDate, $memo_) function close_work_order($woid) { - $sql = "UPDATE ".TB_PREF."workorders SET closed=1 WHERE id = $woid"; + $sql = "UPDATE ".TB_PREF."workorders SET closed=1 WHERE id = ".db_escape($woid); db_query($sql, "could not close work order"); } @@ -255,7 +273,7 @@ function close_work_order($woid) function work_order_is_closed($woid) { - $sql = "SELECT closed FROM ".TB_PREF."workorders WHERE id = $woid"; + $sql = "SELECT closed FROM ".TB_PREF."workorders WHERE id = ".db_escape($woid); $result = db_query($sql, "could not query work order"); $row = db_fetch_row($result); return ($row[0] > 0); @@ -265,9 +283,9 @@ function work_order_is_closed($woid) function work_order_update_finished_quantity($woid, $quantity, $force_close=0) { - $sql = "UPDATE ".TB_PREF."workorders SET units_issued = units_issued + $quantity, - closed = ((units_issued >= units_reqd) OR $force_close) - WHERE id = $woid"; + $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); db_query($sql, "The work order issued quantity couldn't be updated"); } @@ -276,13 +294,23 @@ function work_order_update_finished_quantity($woid, $quantity, $force_close=0) function void_work_order($woid) { - $work_order = get_work_order($woid); + begin_transaction(); + $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 = $woid"; + $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); + + $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 @@ -293,15 +321,146 @@ function void_work_order($woid) // 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_']); + 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)) + { + $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"); + + 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)) + { + $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 + if ($cost != 0) + add_issue_cost($work_order['stock_id'], -$qty, $date, $cost); + + $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); + + $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); + + // 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