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 calculation 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         if (empty($qoh_stock[$stock_id])) $stock_qty = 0;
  65         else $stock_qty = $qoh_stock[$stock_id];
  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                                         ".TB_PREF."sales_orders.trans_type=".ST_SALESORDER." AND
 107                                         ".TB_PREF."sales_orders.trans_type=".TB_PREF."sales_order_details.trans_type AND ";
 108         if ($location != "")
 109                 $sql .= TB_PREF."sales_orders.from_stk_loc =".db_escape($location)." AND ";
 110         $sql .= TB_PREF."sales_order_details.quantity-".TB_PREF."sales_order_details.qty_sent > 0 AND
 111                                    ".TB_PREF."stock_master.stock_id=".TB_PREF."sales_order_details.stk_code AND
 112                                    ".TB_PREF."stock_master.mb_flag='M'
 113                                    GROUP BY ".TB_PREF."sales_order_details.stk_code";
 114     $result = db_query($sql, "No transactions were returned");
 115         while ($row = db_fetch_row($result)) {
 116                 $demand_qty += stock_demand_manufacture($row[0], $row[1], $stock_id, $location);
 117         }
 118         return $demand_qty;
 119 }
 120
 121 function get_on_porder_qty($stock_id, $location)
 122 {
 123         $sql = "SELECT SUM(".TB_PREF."purch_order_details.quantity_ordered - "
 124                 .TB_PREF."purch_order_details.quantity_received) AS qoo
 125                 FROM ".TB_PREF."purch_order_details INNER JOIN "
 126                         .TB_PREF."purch_orders ON ".TB_PREF."purch_order_details.order_no=".TB_PREF."purch_orders.order_no
 127                 WHERE ".TB_PREF."purch_order_details.item_code=".db_escape($stock_id)." ";
 128         if ($location != "")
 129                 $sql .= "AND ".TB_PREF."purch_orders.into_stock_location=".db_escape($location)." ";
 130         $sql .= "AND ".TB_PREF."purch_order_details.item_code=".db_escape($stock_id);
 131         $qoo_result = db_query($sql,"could not receive quantity on order for item");
 132
 133         if (db_num_rows($qoo_result) == 1)
 134         {
 135                 $qoo_row = db_fetch_row($qoo_result);
 136                 $qoo =  $qoo_row[0];
 137         }
 138         else
 139         {
 140                 $qoo = 0;
 141         }
 142         return $qoo;
 143 }
 144
 145 function get_on_worder_qty($stock_id, $location)
 146 {
 147         $sql = "SELECT SUM((".TB_PREF."workorders.units_reqd-".TB_PREF."workorders.units_issued) *
 148                 (".TB_PREF."wo_requirements.units_req-".TB_PREF."wo_requirements.units_issued)) AS qoo
 149                 FROM ".TB_PREF."wo_requirements INNER JOIN ".TB_PREF."workorders
 150                         ON ".TB_PREF."wo_requirements.workorder_id=".TB_PREF."workorders.id
 151                 WHERE ".TB_PREF."wo_requirements.stock_id=".db_escape($stock_id)." ";
 152         if ($location != "")
 153                 $sql .= "AND ".TB_PREF."wo_requirements.loc_code=".db_escape($location)." ";
 154         $sql .= "AND ".TB_PREF."workorders.released=1";
 155         $qoo_result = db_query($sql,"could not receive quantity on order for item");
 156         if (db_num_rows($qoo_result) == 1)
 157         {
 158                 $qoo_row = db_fetch_row($qoo_result);
 159                 $qoo =  $qoo_row[0];
 160         }
 161         else
 162                 $qoo = 0.0;
 163         $flag = get_mb_flag($stock_id);
 164         if ($flag == 'M')
 165         {
 166                 $sql = "SELECT SUM((".TB_PREF."workorders.units_reqd-".TB_PREF."workorders.units_issued)) AS qoo
 167                         FROM ".TB_PREF."workorders
 168                         WHERE ".TB_PREF."workorders.stock_id=".db_escape($stock_id)." ";
 169                 if ($location != "")
 170                         $sql .= "AND ".TB_PREF."workorders.loc_code=".db_escape($location)." ";
 171                 $sql .= "AND ".TB_PREF."workorders.released=1";
 172                 $qoo_result = db_query($sql,"could not receive quantity on order for item");
 173                 if (db_num_rows($qoo_result) == 1)
 174                 {
 175                         $qoo_row = db_fetch_row($qoo_result);
 176                         $qoo +=  $qoo_row[0];
 177                 }
 178         }
 179         return $qoo;
 180 }
 181
 182 function get_mb_flag($stock_id)
 183 {
 184         $sql = "SELECT mb_flag FROM ".TB_PREF."stock_master WHERE stock_id = "
 185                 .db_escape($stock_id);
 186         $result = db_query($sql, "retreive mb_flag from item");
 187
 188         if (db_num_rows($result) == 0)
 189                 return -1;
 190
 191         $myrow = db_fetch_row($result);
 192         return $myrow[0];
 193 }
 194
 195 //--------------------------------------------------------------------------------------
 196
 197 function add_bom($selected_parent, $component, $workcentre_added, $loc_code, $quantity)
 198 {
 199         $sql = "INSERT INTO ".TB_PREF."bom (parent, component, workcentre_added, loc_code, quantity)
 200                 VALUES (".db_escape($selected_parent).", ".db_escape($component) . ","
 201                 .db_escape($workcentre_added) . ", ".db_escape($loc_code) . ", "
 202                 . $quantity . ")";
 203
 204         db_query($sql,"check failed");
 205 }
 206 //--------------------------------------------------------------------------------------
 207
 208 function update_bom($selected_parent, $selected_component, $workcentre_added, $loc_code, $quantity)
 209 {
 210         $sql = "UPDATE ".TB_PREF."bom SET workcentre_added=".db_escape($workcentre_added)
 211          . ",loc_code=".db_escape($loc_code) . ",
 212                 quantity= " . $quantity . "
 213                 WHERE parent=".db_escape($selected_parent) . "
 214                 AND id=".db_escape($selected_component);
 215         check_db_error("Could not update this bom component", $sql);
 216
 217         db_query($sql,"could not update bom");
 218 }
 219
 220 function delete_bom($selected_id)
 221 {
 222         $sql = "DELETE FROM ".TB_PREF."bom WHERE id=".db_escape($selected_id);
 223         db_query($sql,"Could not delete this bom components");
 224 }
 225
 226 function get_bom($item)
 227 {
 228         $sql = "SELECT ".TB_PREF."bom.*, ".TB_PREF."locations.location_name, ".TB_PREF."workcentres.name AS WorkCentreDescription,
 229         ".TB_PREF."stock_master.description, ".TB_PREF."stock_master.mb_flag AS ResourceType,
 230         ".TB_PREF."stock_master.material_cost+ ".TB_PREF."stock_master.labour_cost+".TB_PREF."stock_master.overhead_cost AS standard_cost, units,
 231         ".TB_PREF."bom.quantity * (".TB_PREF."stock_master.material_cost+ ".TB_PREF."stock_master.labour_cost+ ".TB_PREF."stock_master.overhead_cost) AS ComponentCost
 232         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
 233         WHERE ".TB_PREF."bom.parent = ".db_escape($item)."
 234                 AND ".TB_PREF."workcentres.id=".TB_PREF."bom.workcentre_added
 235                 AND ".TB_PREF."bom.loc_code = ".TB_PREF."locations.loc_code ORDER BY ".TB_PREF."bom.id";
 236
 237         return db_query($sql, "The bill of material could not be retrieved");
 238 }
 239
 240 //--------------------------------------------------------------------------------------
 241
 242 function get_component_from_bom($selected_id)
 243 {
 244         $sql = "SELECT ".TB_PREF."bom.*,".TB_PREF."stock_master.description FROM "
 245                 .TB_PREF."bom,".TB_PREF."stock_master
 246                 WHERE id=".db_escape($selected_id)."
 247                 AND ".TB_PREF."stock_master.stock_id=".TB_PREF."bom.component";
 248
 249         $result = db_query($sql, "could not get bom");
 250         return db_fetch($result);
 251 }
 252 //--------------------------------------------------------------------------------------
 253
 254 function has_bom($item)
 255 {
 256     $result = get_bom($item);
 257
 258     return (db_num_rows($result) != 0);
 259 }
 260
 261 //--------------------------------------------------------------------------------------
 262
 263 function is_component_already_on_bom($component, $workcentre_added, $loc_code, $selected_parent)
 264 {
 265         $sql = "SELECT component FROM ".TB_PREF."bom
 266                 WHERE parent=".db_escape($selected_parent)."
 267                 AND component=".db_escape($component) . "
 268                 AND workcentre_added=".db_escape($workcentre_added) . "
 269                 AND loc_code=".db_escape($loc_code);
 270         $result = db_query($sql,"check failed");
 271
 272         return (db_num_rows($result) > 0);
 273 }
 274
 275 //--------------------------------------------------------------------------------------
 276
 277 function check_for_recursive_bom($ultimate_parent, $component_to_check)
 278 {
 279
 280         /* returns true ie 1 if the bom contains the parent part as a component
 281         ie the bom is recursive otherwise false ie 0 */
 282
 283         $sql = "SELECT component FROM ".TB_PREF."bom WHERE parent=".db_escape($component_to_check);
 284         $result = db_query($sql,"could not check recursive bom");
 285
 286         if ($result != 0)
 287         {
 288                 while ($myrow = db_fetch_row($result))
 289                 {
 290                         if ($myrow[0] == $ultimate_parent)
 291                         {
 292                                 return 1;
 293                         }
 294
 295                         if (check_for_recursive_bom($ultimate_parent, $myrow[0]))
 296                         {
 297                                 return 1;
 298                         }
 299                 } //(while loop)
 300         } //end if $result is true
 301
 302         return 0;
 303 } //end of function check_for_recursive_bom
 304