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 - "
  16                 .TB_PREF."sales_order_details.qty_sent) AS QtyDemand
  17                         FROM ".TB_PREF."sales_order_details,
  18                                         ".TB_PREF."sales_orders
  19                                 WHERE ".TB_PREF."sales_order_details.order_no="
  20                                 .TB_PREF."sales_orders.order_no AND ".TB_PREF."sales_orders.trans_type=".ST_SALESORDER." AND
  21                                 ".TB_PREF."sales_orders.trans_type=".TB_PREF."sales_order_details.trans_type AND ";
  22         if ($location != "")
  23                 $sql .= TB_PREF."sales_orders.from_stk_loc =".db_escape($location)." AND ";
  24         $sql .= TB_PREF."sales_order_details.stk_code = ".db_escape($stock_id);
  25
  26     $result = db_query($sql,"No transactions were returned");
  27         $row = db_fetch($result);
  28         if ($row === false)
  29                 return 0;
  30         return $row['QtyDemand'];
  31 }
  32
  33 $bom_list = array();
  34 $qoh_stock = NULL;
  35
  36 function load_stock_levels($location)
  37 {
  38         global $qoh_stock;
  39         $date = date2sql(Today());
  40
  41         $sql = "SELECT stock_id, SUM(qty) FROM ".TB_PREF."stock_moves WHERE tran_date <= '$date'";
  42         if ($location != '') $sql .= " AND loc_code = ".db_escape($location);
  43         $sql .= " GROUP BY stock_id";
  44         $result = db_query($sql, "QOH calulcation failed");
  45         while ($row = db_fetch($result)) {
  46                 $qoh_stock[$row[0]] = $row[1];
  47         }
  48 }
  49
  50 // recursion fixed by Tom Moulton. Max 10 recursion levels.
  51 function stock_demand_manufacture($stock_id, $qty, $demand_id, $location, $level=0)
  52 {
  53         global $bom_list, $qoh_stock;
  54         $demand = 0.0;
  55         if ($level > 10) {
  56                 display_warning("BOM Too many Manufacturing levels deep $level");
  57                 return $demand;
  58         }
  59         // Load all stock levels (stock moves) into $qoh_stock
  60         if ($qoh_stock == NULL) {
  61                 $qoh_stock = array();
  62                 load_stock_levels($location);
  63         }
  64         $stock_qty = $qoh_stock[$stock_id];
  65         if ($stock_qty == NULL) $stock_qty = 0;
  66         if ($qty <= $stock_qty) return $demand;
  67         $bom = $bom_list[$stock_id];
  68         if ($bom == NULL) {
  69                 $sql = "SELECT parent, component, quantity FROM "
  70                         .TB_PREF."bom WHERE parent = ".db_escape($stock_id);
  71                 if ($location != "") $sql .= " AND loc_code = ".db_escape($location);
  72                 $result = db_query($sql, "Could not search bom");
  73                 $bom = array();
  74                 // Even if we get no results, remember that fact
  75                 $bom[] = array($stock_id, '', 0);
  76                 while ($row = db_fetch_row($result)) {
  77                         $bom[] = array($row[0], $row[1], $row[2]);
  78                 }
  79                 db_free_result($result);
  80                 $bom_list[$stock_id] = $bom;
  81         }
  82         $len = count($bom);
  83         $i = 0;
  84         while ($i < $len) {
  85                 $row = $bom[$i];
  86                 $i++;
  87                 // Ignore the dummy entry
  88                 if ($row[1] == '') continue;
  89                 $q = $qty * $row[2];
  90                 if ($row[1] == $demand_id) $demand += $q;
  91                 $demand += stock_demand_manufacture($row[1], $q, $demand_id, $location, $level+1);
  92         }
  93         return $demand;
  94 }
  95
  96 // recursion fixed by Tom Moulton
  97 function get_demand_asm_qty($stock_id, $location)
  98 {
  99         $demand_qty = 0.0;
 100         $sql = "SELECT ".TB_PREF."sales_order_details.stk_code, SUM(".TB_PREF."sales_order_details.quantity-".TB_PREF."sales_order_details.qty_sent)
 101                                    AS Demmand
 102                                    FROM ".TB_PREF."sales_order_details,
 103                                                 ".TB_PREF."sales_orders,
 104                                                 ".TB_PREF."stock_master
 105                                    WHERE ".TB_PREF."sales_orders.order_no = ".TB_PREF."sales_order_details.order_no AND ";
 106         if ($location != "")
 107                 $sql .= TB_PREF."sales_orders.from_stk_loc =".db_escape($location)." AND ";
 108         $sql .= TB_PREF."sales_order_details.quantity-".TB_PREF."sales_order_details.qty_sent > 0 AND
 109                                    ".TB_PREF."stock_master.stock_id=".TB_PREF."sales_order_details.stk_code AND
 110                                    (".TB_PREF."stock_master.mb_flag='M' OR ".TB_PREF."stock_master.mb_flag='A')
 111                                    GROUP BY ".TB_PREF."sales_order_details.stk_code";
 112     $result = db_query($sql, "No transactions were returned");
 113         while ($row = db_fetch_row($result)) {
 114                 $demand_qty += stock_demand_manufacture($row[0], $row[1], $stock_id, $location);
 115         }
 116         return $demand_qty;
 117 }
 118
 119 function get_on_porder_qty($stock_id, $location)
 120 {
 121         $sql = "SELECT SUM(".TB_PREF."purch_order_details.quantity_ordered - "
 122                 .TB_PREF."purch_order_details.quantity_received) AS qoo
 123                 FROM ".TB_PREF."purch_order_details INNER JOIN "
 124                         .TB_PREF."purch_orders ON ".TB_PREF."purch_order_details.order_no=".TB_PREF."purch_orders.order_no
 125                 WHERE ".TB_PREF."purch_order_details.item_code=".db_escape($stock_id)." ";
 126         if ($location != "")
 127                 $sql .= "AND ".TB_PREF."purch_orders.into_stock_location=".db_escape($location)." ";
 128         $sql .= "AND ".TB_PREF."purch_order_details.item_code=".db_escape($stock_id);
 129         $qoo_result = db_query($sql,"could not receive quantity on order for item");
 130
 131         if (db_num_rows($qoo_result) == 1)
 132         {
 133                 $qoo_row = db_fetch_row($qoo_result);
 134                 $qoo =  $qoo_row[0];
 135         }
 136         else
 137         {
 138                 $qoo = 0;
 139         }
 140         return $qoo;
 141 }
 142
 143 function get_on_worder_qty($stock_id, $location)
 144 {
 145         $sql = "SELECT SUM((".TB_PREF."workorders.units_reqd-".TB_PREF."workorders.units_issued) *
 146                 (".TB_PREF."wo_requirements.units_req-".TB_PREF."wo_requirements.units_issued)) AS qoo
 147                 FROM ".TB_PREF."wo_requirements INNER JOIN ".TB_PREF."workorders
 148                         ON ".TB_PREF."wo_requirements.workorder_id=".TB_PREF."workorders.id
 149                 WHERE ".TB_PREF."wo_requirements.stock_id=".db_escape($stock_id)." ";
 150         if ($location != "")
 151                 $sql .= "AND ".TB_PREF."wo_requirements.loc_code=".db_escape($location)." ";
 152         $sql .= "AND ".TB_PREF."workorders.released=1";
 153         $qoo_result = db_query($sql,"could not receive quantity on order for item");
 154         if (db_num_rows($qoo_result) == 1)
 155         {
 156                 $qoo_row = db_fetch_row($qoo_result);
 157                 $qoo =  $qoo_row[0];
 158         }
 159         else
 160                 $qoo = 0.0;
 161         $flag = get_mb_flag($stock_id);
 162         if ($flag == 'A' || $flag == 'M')
 163         {
 164                 $sql = "SELECT SUM((".TB_PREF."workorders.units_reqd-".TB_PREF."workorders.units_issued)) AS qoo
 165                         FROM ".TB_PREF."workorders
 166                         WHERE ".TB_PREF."workorders.stock_id=".db_escape($stock_id)." ";
 167                 if ($location != "")
 168                         $sql .= "AND ".TB_PREF."workorders.loc_code=".db_escape($location)." ";
 169                 $sql .= "AND ".TB_PREF."workorders.released=1";
 170                 $qoo_result = db_query($sql,"could not receive quantity on order for item");
 171                 if (db_num_rows($qoo_result) == 1)
 172                 {
 173                         $qoo_row = db_fetch_row($qoo_result);
 174                         $qoo +=  $qoo_row[0];
 175                 }
 176         }
 177         return $qoo;
 178 }
 179
 180 function get_mb_flag($stock_id)
 181 {
 182         $sql = "SELECT mb_flag FROM ".TB_PREF."stock_master WHERE stock_id = "
 183                 .db_escape($stock_id);
 184         $result = db_query($sql, "retreive mb_flag from item");
 185
 186         if (db_num_rows($result) == 0)
 187                 return -1;
 188
 189         $myrow = db_fetch_row($result);
 190         return $myrow[0];
 191 }
 192
 193 //--------------------------------------------------------------------------------------
 194
 195 function get_bom($item)
 196 {
 197         $sql = "SELECT ".TB_PREF."bom.*, ".TB_PREF."locations.location_name, ".TB_PREF."workcentres.name AS WorkCentreDescription,
 198         ".TB_PREF."stock_master.description, ".TB_PREF."stock_master.mb_flag AS ResourceType,
 199         ".TB_PREF."stock_master.material_cost+ ".TB_PREF."stock_master.labour_cost+".TB_PREF."stock_master.overhead_cost AS standard_cost, units,
 200         ".TB_PREF."bom.quantity * (".TB_PREF."stock_master.material_cost+ ".TB_PREF."stock_master.labour_cost+ ".TB_PREF."stock_master.overhead_cost) AS ComponentCost
 201         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
 202         WHERE ".TB_PREF."bom.parent = ".db_escape($item)."
 203                 AND ".TB_PREF."workcentres.id=".TB_PREF."bom.workcentre_added
 204                 AND ".TB_PREF."bom.loc_code = ".TB_PREF."locations.loc_code ORDER BY ".TB_PREF."bom.id";
 205
 206         return db_query($sql, "The bill of material could not be retrieved");
 207 }
 208
 209 //--------------------------------------------------------------------------------------
 210
 211 function has_bom($item)
 212 {
 213     $result = get_bom($item);
 214
 215     return (db_num_rows($result) != 0);
 216 }
 217
 218 //--------------------------------------------------------------------------------------
 219
 220 ?>