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