Fixed and optimized On Order in Inventory Items Status and reports

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

diff --git a/includes/db/manufacturing_db.inc b/includes/db/manufacturing_db.inc
index 622b6822740ef35c8b5ac03c804da5889cd05b15..6778cff7d17604166abfff7a4f11f305af01022e 100644 (file)
--- a/includes/db/manufacturing_db.inc
+++ b/includes/db/manufacturing_db.inc
@@ -9,6 +9,109 @@
     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
 ***********************************************************************/
+//----------------------------------------------------------------------------------------
+function get_demand_qty($stock_id, $location)
+{
+       $sql = "SELECT SUM(".TB_PREF."sales_order_details.quantity - ".TB_PREF."sales_order_details.qty_sent) AS QtyDemand
+                               FROM ".TB_PREF."sales_order_details,
+                                       ".TB_PREF."sales_orders
+                               WHERE ".TB_PREF."sales_order_details.order_no=".TB_PREF."sales_orders.order_no AND ";
+       if ($location != "")
+               $sql .= TB_PREF."sales_orders.from_stk_loc ='$location' AND ";
+       $sql .= TB_PREF."sales_order_details.stk_code = '$stock_id'";
+
+    $result = db_query($sql,"No transactions were returned");
+       $row = db_fetch($result);
+       return $row['QtyDemand'];
+}
+
+function get_demand_asm_qty($stock_id, $location)
+{
+       $sql = "SELECT SUM((".TB_PREF."sales_order_details.quantity-".TB_PREF."sales_order_details.qty_sent)*".TB_PREF."bom.quantity)
+                                  AS Dem
+                                  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 ";
+       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];
+       }
+       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
+               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 != "")
+               $sql .= "AND ".TB_PREF."purch_orders.into_stock_location='$location' ";
+       $sql .= "AND ".TB_PREF."purch_order_details.item_code='$stock_id'";
+       $qoo_result = db_query($sql,"could not receive quantity on order for item");
+
+       if (db_num_rows($qoo_result) == 1)
+       {
+               $qoo_row = db_fetch_row($qoo_result);
+               $qoo =  $qoo_row[0];
+       }
+       else
+       {
+               $qoo = 0;
+       }
+       return $qoo;
+}
+
+function get_on_worder_qty($stock_id, $location)
+{
+       $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
+               WHERE ".TB_PREF."wo_requirements.stock_id='$stock_id' ";
+       if ($location != "")
+               $sql .= "AND ".TB_PREF."wo_requirements.loc_code='$location' ";
+       $sql .= "AND ".TB_PREF."workorders.released=1";
+       $qoo_result = db_query($sql,"could not receive quantity on order for item");
+       if (db_num_rows($qoo_result) == 1)
+       {
+               $qoo_row = db_fetch_row($qoo_result);
+               $qoo =  $qoo_row[0];
+       }
+       else
+               $qoo = 0.0;
+       $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
+                       FROM ".TB_PREF."workorders 
+                       WHERE ".TB_PREF."workorders.stock_id='$stock_id' ";
+               if ($location != "")    
+                       $sql .= "AND ".TB_PREF."workorders.loc_code='$location' ";
+               $sql .= "AND ".TB_PREF."workorders.released=1";
+               $qoo_result = db_query($sql,"could not receive quantity on order for item");
+               if (db_num_rows($qoo_result) == 1)
+               {
+                       $qoo_row = db_fetch_row($qoo_result);
+                       $qoo +=  $qoo_row[0];
+               }
+       }
+       return $qoo;
+}
+
 function get_mb_flag($stock_id)
 {
        $sql = "SELECT mb_flag FROM ".TB_PREF."stock_master WHERE stock_id = '" . $stock_id . "'";