includes/db/inventory_db.inc

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