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