includes/db/inventory_db.inc

   1 <?php
   2
   3 function get_qoh_on_date($stock_id, $location=null, $date_=null)
   4 {
   5         if ($date_ == null)
   6                 $date_ = Today();
   7
   8         $date = date2sql($date_);
   9
  10         $sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves
  11                 WHERE stock_id='$stock_id'
  12                 AND tran_date <= '$date'";
  13
  14         if ($location != null)
  15                 $sql .= " AND loc_code = '$location'";
  16
  17         $result = db_query($sql, "QOH calulcation failed");
  18
  19         $myrow = db_fetch_row($result);
  20
  21         return $myrow[0];
  22 }
  23
  24 //--------------------------------------------------------------------------------------
  25
  26 function get_item_edit_info($stock_id)
  27 {
  28         $sql = "SELECT material_cost + labour_cost + overhead_cost AS standard_cost, units
  29                 FROM ".TB_PREF."stock_master WHERE stock_id='$stock_id'";
  30         $result = db_query($sql, "The standard cost cannot be retrieved");
  31
  32         return db_fetch($result);
  33 }
  34
  35 //--------------------------------------------------------------------------------------
  36
  37 function get_standard_cost($stock_id)
  38 {
  39         $sql = "SELECT material_cost + labour_cost + overhead_cost AS std_cost
  40                 FROM ".TB_PREF."stock_master WHERE stock_id='$stock_id'";
  41         $result = db_query($sql, "The standard cost cannot be retrieved");
  42
  43         $myrow = db_fetch_row($result);
  44
  45         return $myrow[0];
  46 }
  47
  48 //--------------------------------------------------------------------------------------
  49
  50 function is_inventory_item($stock_id)
  51 {
  52         $sql = "SELECT stock_id FROM ".TB_PREF."stock_master
  53                 WHERE stock_id='$stock_id' AND mb_flag <> 'D'";
  54         $result = db_query($sql, "Cannot query is inventory item or not");
  55
  56         return db_num_rows($result) > 0;
  57 }
  58
  59 //-------------------------------------------------------------------
  60
  61 Function get_stock_gl_code($stock_id)
  62 {
  63         /*Gets the GL Codes relevant to the item account  */
  64
  65         $sql = "SELECT inventory_account, cogs_account,
  66                 adjustment_account, sales_account, assembly_account, dimension_id, dimension2_id FROM
  67                 ".TB_PREF."stock_master WHERE stock_id = '$stock_id'";
  68
  69         $get = db_query($sql,"retreive stock gl code");
  70         return db_fetch($get);
  71 }
  72
  73 //--------------------------------------------------------------------------------------
  74
  75 // $date_ - display / non-sql date
  76 // $std_cost - in HOME currency
  77 // $show_or_hide - wil this move be visible in reports, etc
  78 // $price - in $person_id's currency
  79
  80 function add_stock_move($type, $stock_id, $trans_no, $location,
  81     $date_, $reference, $quantity, $std_cost, $person_id=null, $show_or_hide=1,
  82     $price=0, $discount_percent=0, $error_msg="")
  83 {
  84         // do not add a stock move if it's a non-inventory item
  85         if (!is_inventory_item($stock_id))
  86                 return null;
  87
  88         $date = date2sql($date_);
  89
  90         $sql = "INSERT INTO ".TB_PREF."stock_moves (stock_id, trans_no, type, loc_code,
  91                 tran_date, person_id, reference, qty, standard_cost, visible, price,
  92                 discount_percent) VALUES ('$stock_id', $trans_no, $type,
  93                 '$location', '$date', '$person_id', '$reference', $quantity, $std_cost,
  94                 $show_or_hide, $price, $discount_percent)";
  95
  96         if ($error_msg == "")
  97                 $error_msg = "The stock movement record cannot be inserted";
  98
  99         db_query($sql, $error_msg);
 100
 101         return db_insert_id();
 102 }
 103
 104 //--------------------------------------------------------------------------------------------------
 105
 106 function get_stock_moves($type, $type_no, $visible=false)
 107 {
 108         $sql = "SELECT ".TB_PREF."stock_moves.*, ".TB_PREF."stock_master.description, ".TB_PREF."stock_master.units,
 109                 ".TB_PREF."locations.location_name,
 110                 ".TB_PREF."stock_master.material_cost + ".TB_PREF."stock_master.labour_cost + ".TB_PREF."stock_master.overhead_cost AS FixedStandardCost
 111                 FROM ".TB_PREF."stock_moves,".TB_PREF."locations,".TB_PREF."stock_master
 112                 WHERE ".TB_PREF."stock_moves.stock_id = ".TB_PREF."stock_master.stock_id
 113                 AND ".TB_PREF."locations.loc_code=".TB_PREF."stock_moves.loc_code
 114                 AND type=$type AND trans_no=$type_no ORDER BY trans_id";
 115         if ($visible)
 116                 $sql .= " AND ".TB_PREF."stock_moves.visible=1";
 117
 118         return db_query($sql, "Could not get stock moves");
 119 }
 120
 121 //--------------------------------------------------------------------------------------------------
 122
 123 function void_stock_move($type, $type_no)
 124 {
 125         $sql = "UPDATE ".TB_PREF."stock_moves SET qty=0, price=0, discount_percent=0,
 126                 standard_cost=0 WHERE type=$type AND trans_no=$type_no";
 127
 128         db_query($sql, "Could not void stock moves");
 129 }
 130
 131 //--------------------------------------------------------------------------------------------------
 132
 133 function get_location_name($loc_code)
 134 {
 135         $sql = "SELECT location_name FROM ".TB_PREF."locations WHERE loc_code='$loc_code'";
 136
 137         $result = db_query($sql, "could not retreive the location name for $loc_code");
 138
 139         if (db_num_rows($result) == 1)
 140         {
 141                 $row = db_fetch_row($result);
 142                 return $row[0];
 143         }
 144
 145         display_db_error("could not retreive the location name for $loc_code", $sql, true);
 146 }
 147
 148 //--------------------------------------------------------------------------------------------------
 149
 150
 151 ?>