Quick Entries uses Tax Groups instead of Item Tax Types for calculating taxes (Please...

[fa-stable.git] / includes / db / manufacturing_db.inc

diff --git a/includes/db/manufacturing_db.inc b/includes/db/manufacturing_db.inc
index 6778cff7d17604166abfff7a4f11f305af01022e..1153010d725e78cb92f1b7592c999c6c6f7ba435 100644 (file)
--- a/includes/db/manufacturing_db.inc
+++ b/includes/db/manufacturing_db.inc
@@ -22,40 +22,99 @@ 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'];
 }
 
-function get_demand_asm_qty($stock_id, $location)
+$bom_list = array(); 
+$qoh_stock = NULL;
+
+function load_stock_levels($location)
 {
-       $sql = "SELECT SUM((".TB_PREF."sales_order_details.quantity-".TB_PREF."sales_order_details.qty_sent)*".TB_PREF."bom.quantity)
-                                  AS Dem
+       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, $location, $level=0) 
+{
+       global $bom_list, $qoh_stock;
+       $demand = 0.0;
+       if ($level > 10) {
+               display_warning("BOM Too many Manufacturing levels deep $level");
+               return $demand;
+       }
+       // Load all stock levels (stock moves) into $qoh_stock
+       if ($qoh_stock == NULL) {
+               $qoh_stock = array();
+               load_stock_levels($location);
+       }
+       $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++; 
+               // 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, $location, $level+1);
+       }
+       return $demand;
+}
+
+// recursion fixed by Tom Moulton
+function get_demand_asm_qty($stock_id, $location) 
+{
+       $demand_qty = 0.0;
+       $sql = "SELECT ".TB_PREF."sales_order_details.stk_code, SUM(".TB_PREF."sales_order_details.quantity-".TB_PREF."sales_order_details.qty_sent)
+                                  AS Demmand
                                   FROM ".TB_PREF."sales_order_details,
                                                ".TB_PREF."sales_orders,
-                                               ".TB_PREF."bom,
                                                ".TB_PREF."stock_master
-                                  WHERE ".TB_PREF."sales_order_details.stk_code=".TB_PREF."bom.parent AND
-                                  ".TB_PREF."sales_orders.order_no = ".TB_PREF."sales_order_details.order_no AND ";
+                                  WHERE ".TB_PREF."sales_orders.order_no = ".TB_PREF."sales_order_details.order_no AND ";
        if ($location != "")
                $sql .= TB_PREF."sales_orders.from_stk_loc ='$location' AND ";
        $sql .= TB_PREF."sales_order_details.quantity-".TB_PREF."sales_order_details.qty_sent > 0 AND
-                                  ".TB_PREF."bom.component='$stock_id' AND
-                                  ".TB_PREF."stock_master.stock_id=".TB_PREF."bom.parent AND
-                                  (".TB_PREF."stock_master.mb_flag='M' OR ".TB_PREF."stock_master.mb_flag='A')";
-
-    $result = db_query($sql,"No transactions were returned");
-       if (db_num_rows($result)==1)
-       {
-               $row = db_fetch_row($result);
-               $demand_qty = $row[0];
+                                  ".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");
+       while ($row = db_fetch_row($result)) {
+               $demand_qty += stock_demand_manufacture($row[0], $row[1], $stock_id, $location);
        }
-       else
-               $demand_qty = 0.0;
        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 != "")
@@ -77,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
@@ -96,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 != "")    
@@ -151,4 +210,4 @@ function has_bom($item)
 
 //--------------------------------------------------------------------------------------
 
-?>
\ No newline at end of file
+?>