includes/db/manufacturing_db.inc

   1 <?php
   2 /**********************************************************************
   3     Copyright (C) FrontAccounting, LLC.
   4         Released under the terms of the GNU General Public License, GPL,
   5         as published by the Free Software Foundation, either version 3
   6         of the License, or (at your option) any later version.
   7     This program is distributed in the hope that it will be useful,
   8     but WITHOUT ANY WARRANTY; without even the implied warranty of
   9     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
  10     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
  11 ***********************************************************************/
  12 //----------------------------------------------------------------------------------------
  13 function get_demand_qty($stock_id, $location)
  14 {
  15         $sql = "SELECT SUM(".TB_PREF."sales_order_details.quantity - ".TB_PREF."sales_order_details.qty_sent) AS QtyDemand
  16                                 FROM ".TB_PREF."sales_order_details,
  17                                         ".TB_PREF."sales_orders
  18                                 WHERE ".TB_PREF."sales_order_details.order_no=".TB_PREF."sales_orders.order_no AND ";
  19         if ($location != "")
  20                 $sql .= TB_PREF."sales_orders.from_stk_loc ='$location' AND ";
  21         $sql .= TB_PREF."sales_order_details.stk_code = '$stock_id'";
  22
  23     $result = db_query($sql,"No transactions were returned");
  24         $row = db_fetch($result);
  25         return $row['QtyDemand'];
  26 }
  27
  28 function get_demand_asm_qty($stock_id, $location)
  29 {
  30         $sql = "SELECT SUM((".TB_PREF."sales_order_details.quantity-".TB_PREF."sales_order_details.qty_sent)*".TB_PREF."bom.quantity)
  31                                    AS Dem
  32                                    FROM ".TB_PREF."sales_order_details,
  33                                                 ".TB_PREF."sales_orders,
  34                                                 ".TB_PREF."bom,
  35                                                 ".TB_PREF."stock_master
  36                                    WHERE ".TB_PREF."sales_order_details.stk_code=".TB_PREF."bom.parent AND
  37                                    ".TB_PREF."sales_orders.order_no = ".TB_PREF."sales_order_details.order_no AND ";
  38         if ($location != "")
  39                 $sql .= TB_PREF."sales_orders.from_stk_loc ='$location' AND ";
  40         $sql .= TB_PREF."sales_order_details.quantity-".TB_PREF."sales_order_details.qty_sent > 0 AND
  41                                    ".TB_PREF."bom.component='$stock_id' AND
  42                                    ".TB_PREF."stock_master.stock_id=".TB_PREF."bom.parent AND
  43                                    (".TB_PREF."stock_master.mb_flag='M' OR ".TB_PREF."stock_master.mb_flag='A')";
  44
  45     $result = db_query($sql,"No transactions were returned");
  46         if (db_num_rows($result)==1)
  47         {
  48                 $row = db_fetch_row($result);
  49                 $demand_qty = $row[0];
  50         }
  51         else
  52                 $demand_qty = 0.0;
  53         return $demand_qty;
  54 }
  55
  56 function get_on_porder_qty($stock_id, $location)
  57 {
  58         $sql = "SELECT Sum(".TB_PREF."purch_order_details.quantity_ordered - ".TB_PREF."purch_order_details.quantity_received) AS qoo
  59                 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
  60                 WHERE ".TB_PREF."purch_order_details.item_code='$stock_id' ";
  61         if ($location != "")
  62                 $sql .= "AND ".TB_PREF."purch_orders.into_stock_location='$location' ";
  63         $sql .= "AND ".TB_PREF."purch_order_details.item_code='$stock_id'";
  64         $qoo_result = db_query($sql,"could not receive quantity on order for item");
  65
  66         if (db_num_rows($qoo_result) == 1)
  67         {
  68                 $qoo_row = db_fetch_row($qoo_result);
  69                 $qoo =  $qoo_row[0];
  70         }
  71         else
  72         {
  73                 $qoo = 0;
  74         }
  75         return $qoo;
  76 }
  77
  78 function get_on_worder_qty($stock_id, $location)
  79 {
  80         $sql = "SELECT Sum((".TB_PREF."workorders.units_reqd-".TB_PREF."workorders.units_issued) *
  81                 (".TB_PREF."wo_requirements.units_req-".TB_PREF."wo_requirements.units_issued)) AS qoo
  82                 FROM ".TB_PREF."wo_requirements INNER JOIN ".TB_PREF."workorders
  83                         ON ".TB_PREF."wo_requirements.workorder_id=".TB_PREF."workorders.id
  84                 WHERE ".TB_PREF."wo_requirements.stock_id='$stock_id' ";
  85         if ($location != "")
  86                 $sql .= "AND ".TB_PREF."wo_requirements.loc_code='$location' ";
  87         $sql .= "AND ".TB_PREF."workorders.released=1";
  88         $qoo_result = db_query($sql,"could not receive quantity on order for item");
  89         if (db_num_rows($qoo_result) == 1)
  90         {
  91                 $qoo_row = db_fetch_row($qoo_result);
  92                 $qoo =  $qoo_row[0];
  93         }
  94         else
  95                 $qoo = 0.0;
  96         $flag = get_mb_flag($stock_id);
  97         if ($flag == 'A' || $flag == 'M')
  98         {
  99                 $sql = "SELECT Sum((".TB_PREF."workorders.units_reqd-".TB_PREF."workorders.units_issued)) AS qoo
 100                         FROM ".TB_PREF."workorders
 101                         WHERE ".TB_PREF."workorders.stock_id='$stock_id' ";
 102                 if ($location != "")
 103                         $sql .= "AND ".TB_PREF."workorders.loc_code='$location' ";
 104                 $sql .= "AND ".TB_PREF."workorders.released=1";
 105                 $qoo_result = db_query($sql,"could not receive quantity on order for item");
 106                 if (db_num_rows($qoo_result) == 1)
 107                 {
 108                         $qoo_row = db_fetch_row($qoo_result);
 109                         $qoo +=  $qoo_row[0];
 110                 }
 111         }
 112         return $qoo;
 113 }
 114
 115 function get_mb_flag($stock_id)
 116 {
 117         $sql = "SELECT mb_flag FROM ".TB_PREF."stock_master WHERE stock_id = '" . $stock_id . "'";
 118         $result = db_query($sql, "retreive mb_flag from item");
 119
 120         if (db_num_rows($result) == 0)
 121                 return -1;
 122
 123         $myrow = db_fetch_row($result);
 124         return $myrow[0];
 125 }
 126
 127 //--------------------------------------------------------------------------------------
 128
 129 function get_bom($item)
 130 {
 131         $sql = "SELECT ".TB_PREF."bom.*, ".TB_PREF."locations.location_name, ".TB_PREF."workcentres.name AS WorkCentreDescription,
 132         ".TB_PREF."stock_master.description, ".TB_PREF."stock_master.mb_flag AS ResourceType,
 133         ".TB_PREF."stock_master.material_cost+ ".TB_PREF."stock_master.labour_cost+".TB_PREF."stock_master.overhead_cost AS standard_cost, units,
 134         ".TB_PREF."bom.quantity * (".TB_PREF."stock_master.material_cost+ ".TB_PREF."stock_master.labour_cost+ ".TB_PREF."stock_master.overhead_cost) AS ComponentCost
 135         FROM (".TB_PREF."workcentres, ".TB_PREF."locations, ".TB_PREF."bom) INNER JOIN ".TB_PREF."stock_master ON ".TB_PREF."bom.component = ".TB_PREF."stock_master.stock_id
 136         WHERE ".TB_PREF."bom.parent = '" . $item . "'
 137                 AND ".TB_PREF."workcentres.id=".TB_PREF."bom.workcentre_added
 138                 AND ".TB_PREF."bom.loc_code = ".TB_PREF."locations.loc_code ORDER BY ".TB_PREF."bom.id";
 139
 140         return db_query($sql, "The bill of material could not be retrieved");
 141 }
 142
 143 //--------------------------------------------------------------------------------------
 144
 145 function has_bom($item)
 146 {
 147     $result = get_bom($item);
 148
 149     return (db_num_rows($result) != 0);
 150 }
 151
 152 //--------------------------------------------------------------------------------------
 153
 154 ?>