X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;ds=sidebyside;f=includes%2Fdb%2Fmanufacturing_db.inc;h=1153010d725e78cb92f1b7592c999c6c6f7ba435;hb=31c79a2d7cc29c18ac1bfa92cf070857a5b9fe98;hp=4e0a6a36f1de59e7d542e1e60b5d8b530f0f46c6;hpb=d928c081fb1813be3028153e5798c220e5ca370c;p=fa-stable.git diff --git a/includes/db/manufacturing_db.inc b/includes/db/manufacturing_db.inc index 4e0a6a36..1153010d 100644 --- a/includes/db/manufacturing_db.inc +++ b/includes/db/manufacturing_db.inc @@ -22,33 +22,69 @@ function get_demand_qty($stock_id, $location) $result = db_query($sql,"No transactions were returned"); $row = db_fetch($result); + if ($row === false) + return 0; return $row['QtyDemand']; } +$bom_list = array(); +$qoh_stock = NULL; + +function load_stock_levels($location) +{ + global $qoh_stock; + $date = date2sql(Today()); + + $sql = "SELECT stock_id, SUM(qty) FROM ".TB_PREF."stock_moves WHERE tran_date <= '$date'"; + if ($location != '') $sql .= " AND loc_code = '$location'"; + $sql .= " GROUP BY stock_id"; + $result = db_query($sql, "QOH calulcation failed"); + while ($row = db_fetch($result)) { + $qoh_stock[$row[0]] = $row[1]; + } +} + // recursion fixed by Tom Moulton. Max 10 recursion levels. -function stock_demand_manufacture($stock_id, $qty, $demand_id, $level=0) +function stock_demand_manufacture($stock_id, $qty, $demand_id, $location, $level=0) { + global $bom_list, $qoh_stock; $demand = 0.0; if ($level > 10) { - display_notification("BOM Too many Manufacturing levels deep $level"); + display_warning("BOM Too many Manufacturing levels deep $level"); return $demand; } - $sql = "SELECT parent, component, quantity FROM ".TB_PREF."bom WHERE parent = '$stock_id'"; - $result = db_query($sql, "Could not search bom"); - $bom = array(); - while ($row = db_fetch_row($result)) { - $bom[] = array($row[0], $row[1], $row[2]); + // Load all stock levels (stock moves) into $qoh_stock + if ($qoh_stock == NULL) { + $qoh_stock = array(); + load_stock_levels($location); } - db_free_result($result); + $stock_qty = $qoh_stock[$stock_id]; + if ($stock_qty == NULL) $stock_qty = 0; + if ($qty <= $stock_qty) return $demand; + $bom = $bom_list[$stock_id]; + if ($bom == NULL) { + $sql = "SELECT parent, component, quantity FROM ".TB_PREF."bom WHERE parent = '$stock_id'"; + if ($location != "") $sql .= " AND loc_code = '$location'"; + $result = db_query($sql, "Could not search bom"); + $bom = array(); + // Even if we get no results, remember that fact + $bom[] = array($stock_id, '', 0); + while ($row = db_fetch_row($result)) { + $bom[] = array($row[0], $row[1], $row[2]); + } + db_free_result($result); + $bom_list[$stock_id] = $bom; + } $len = count($bom); $i = 0; while ($i < $len) { $row = $bom[$i]; - $i++; + $i++; + // Ignore the dummy entry + if ($row[1] == '') continue; $q = $qty * $row[2]; - if ($row[1] == $demand_id) - $demand += $q; - $demand += stock_demand_manufacture($row[1], $q, $demand_id, $level+1); + if ($row[1] == $demand_id) $demand += $q; + $demand += stock_demand_manufacture($row[1], $q, $demand_id, $location, $level+1); } return $demand; } @@ -69,16 +105,16 @@ function get_demand_asm_qty($stock_id, $location) ".TB_PREF."stock_master.stock_id=".TB_PREF."sales_order_details.stk_code AND (".TB_PREF."stock_master.mb_flag='M' OR ".TB_PREF."stock_master.mb_flag='A') GROUP BY ".TB_PREF."sales_order_details.stk_code"; - $result = db_query($sql, "No transactions were returned"); + $result = db_query($sql, "No transactions were returned"); while ($row = db_fetch_row($result)) { - $demand_qty += stock_demand_manufacture($row[0], $row[1], $stock_id); + $demand_qty += stock_demand_manufacture($row[0], $row[1], $stock_id, $location); } return $demand_qty; } function get_on_porder_qty($stock_id, $location) { - $sql = "SELECT Sum(".TB_PREF."purch_order_details.quantity_ordered - ".TB_PREF."purch_order_details.quantity_received) AS qoo + $sql = "SELECT SUM(".TB_PREF."purch_order_details.quantity_ordered - ".TB_PREF."purch_order_details.quantity_received) AS qoo FROM ".TB_PREF."purch_order_details INNER JOIN ".TB_PREF."purch_orders ON ".TB_PREF."purch_order_details.order_no=".TB_PREF."purch_orders.order_no WHERE ".TB_PREF."purch_order_details.item_code='$stock_id' "; if ($location != "") @@ -100,7 +136,7 @@ function get_on_porder_qty($stock_id, $location) function get_on_worder_qty($stock_id, $location) { - $sql = "SELECT Sum((".TB_PREF."workorders.units_reqd-".TB_PREF."workorders.units_issued) * + $sql = "SELECT SUM((".TB_PREF."workorders.units_reqd-".TB_PREF."workorders.units_issued) * (".TB_PREF."wo_requirements.units_req-".TB_PREF."wo_requirements.units_issued)) AS qoo FROM ".TB_PREF."wo_requirements INNER JOIN ".TB_PREF."workorders ON ".TB_PREF."wo_requirements.workorder_id=".TB_PREF."workorders.id @@ -119,7 +155,7 @@ function get_on_worder_qty($stock_id, $location) $flag = get_mb_flag($stock_id); if ($flag == 'A' || $flag == 'M') { - $sql = "SELECT Sum((".TB_PREF."workorders.units_reqd-".TB_PREF."workorders.units_issued)) AS qoo + $sql = "SELECT SUM((".TB_PREF."workorders.units_reqd-".TB_PREF."workorders.units_issued)) AS qoo FROM ".TB_PREF."workorders WHERE ".TB_PREF."workorders.stock_id='$stock_id' "; if ($location != "")