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