includes/db/inventory_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 function get_qoh_on_date($stock_id, $location=null, $date_=null)
  13 {
  14     if ($date_ == null)
  15         $date_ = Today();
  16
  17     $date = date2sql($date_);
  18
  19     $sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves
  20             WHERE stock_id=".db_escape($stock_id)."
  21             AND tran_date <= '$date'";
  22
  23     if ($location != null)
  24         $sql .= " AND loc_code = ".db_escape($location);
  25
  26     $result = db_query($sql, "QOH calculation failed");
  27
  28     $myrow = db_fetch_row($result);
  29
  30     $qoh =  $myrow[0];
  31                 return $qoh ? $qoh : 0;
  32 }
  33
  34 /**
  35 *       Check whether change in stock on date would not cause negative qoh in stock history.
  36 *       Returns null on success or max. available quantity with respective date otherwise.
  37 *   Running balance is checked on daily basis only, as we do not control time of transaction.
  38 *
  39 *       $delta_qty - tested change in stock qty at $date.
  40 *       $date - check date; when set to null checks all the stock history.
  41 **/
  42
  43 function check_negative_stock($stock_id, $delta_qty, $location=null, $date=null)
  44 {
  45
  46         if ($delta_qty >= 0)
  47                  return null;   // qty increese is always safe
  48
  49         if (!isset($date))
  50                 $date = Today();
  51
  52         $date = date2sql($date);
  53
  54         // check stock status on date
  55     $sql = "SELECT SUM(qty) qty, '$date' tran_date FROM ".TB_PREF."stock_moves
  56             WHERE stock_id=".db_escape($stock_id)."
  57             AND tran_date <= '$date'";
  58
  59     if ($location)
  60         $sql .= " AND loc_code = ".db_escape($location);
  61
  62     $result = db_query($sql, "QOH calculation failed");
  63     $qos = db_fetch_assoc($result);
  64
  65         // check also all stock changes after the date to avoid negative stock in future
  66         $sql = TB_PREF."stock_moves WHERE stock_id=".db_escape($stock_id) . " AND tran_date > '$date'";
  67
  68         if ($location)
  69                 $sql .= " AND loc_code=".db_escape($location);
  70
  71         $rt = running_total_sql($sql, 'qty', 'tran_date');
  72
  73         $sql = "SELECT  {$qos['qty']}+total qty, tran_date FROM ($rt) stock_status ORDER by total, tran_date";
  74         $history = db_query($sql, 'cannot check stock history');
  75         $min_qos = db_fetch($history);
  76
  77         if ($min_qos && ($min_qos['qty'] < $qos['qty']))
  78                 $qos = $min_qos;
  79
  80         return  -$delta_qty > $qos['qty'] ? $qos : null;
  81 }
  82
  83 //--------------------------------------------------------------------------------------
  84
  85 function get_item_edit_info($stock_id)
  86 {
  87         $sql = "SELECT material_cost + labour_cost + overhead_cost AS standard_cost, units, decimals
  88                 FROM ".TB_PREF."stock_master,".TB_PREF."item_units
  89                 WHERE stock_id=".db_escape($stock_id)
  90                 ." AND ".TB_PREF."stock_master.units=".TB_PREF."item_units.abbr";
  91         $result = db_query($sql, "The standard cost cannot be retrieved");
  92
  93         return db_fetch($result);
  94 }
  95
  96 //--------------------------------------------------------------------------------------
  97
  98 function get_standard_cost($stock_id)
  99 {
 100         $sql = "SELECT (material_cost + labour_cost + overhead_cost) AS std_cost
 101                 FROM ".TB_PREF."stock_master s WHERE stock_id=".db_escape($stock_id);
 102         $result = db_query($sql, "The standard cost cannot be retrieved");
 103
 104         $myrow = db_fetch_row($result);
 105
 106         return $myrow[0];
 107 }
 108
 109 //--------------------------------------------------------------------------------------
 110
 111 function is_inventory_item($stock_id)
 112 {
 113         $sql = "SELECT stock_id FROM ".TB_PREF."stock_master
 114                 WHERE stock_id=".db_escape($stock_id)." AND mb_flag <> 'D'";
 115         $result = db_query($sql, "Cannot query is inventory item or not");
 116
 117         return db_num_rows($result) > 0;
 118 }
 119
 120 //-------------------------------------------------------------------
 121
 122 function last_negative_stock_begin_date($stock_id, $to)
 123 {
 124         $to = date2sql($to);
 125         $sql ="SET @q = 0";
 126         db_query($sql);
 127         $sql = "SET @flag = 0";
 128         db_query($sql);
 129         $sql = "SELECT SUM(qty), @q:= @q + qty, IF(@q < 0 AND @flag=0, @flag:=1,@flag:=0), IF(@q < 0 AND @flag=1, tran_date,'') AS begin_date
 130                 FROM ".TB_PREF."stock_moves
 131                 WHERE stock_id=".db_escape($stock_id)." AND tran_date<='$to'
 132                 AND qty <> 0
 133                 GROUP BY stock_id ORDER BY tran_date";
 134
 135         $result = db_query($sql, "The dstock moves could not be retrieved");
 136         $row = db_fetch_row($result);
 137         return $row[3];
 138 }
 139
 140 //-------------------------------------------------------------------
 141
 142 function get_already_delivered($stock_id, $location, $trans_no)
 143 {
 144         $sql = "SELECT ".TB_PREF."stock_moves.qty
 145                 FROM ".TB_PREF."stock_moves
 146                 WHERE ".TB_PREF."stock_moves.stock_id = ".db_escape($stock_id)."
 147                 AND ".TB_PREF."stock_moves.loc_code = ".db_escape($location)."
 148                 AND type=".ST_CUSTDELIVERY." AND trans_no=".db_escape($trans_no);
 149         $result = db_query($sql, "Could not get stock moves");
 150         $row = db_fetch_row($result);
 151         return $row[0];
 152 }
 153
 154 function last_negative_stock_trans_id($stock_id, $to)
 155 {
 156         $sql = "SELECT * from ".TB_PREF."stock_moves
 157                 WHERE stock_id=".db_escape($stock_id)."
 158                 AND qty <> 0 order by trans_id asc";
 159
 160         $result = db_query($sql, "The query on stock moves failed.");
 161
 162         $qty = 0;
 163         $flag = 0;
 164         $negative_trans_id = -1;
 165
 166         while ($myrow = db_fetch($result))
 167         {
 168                 $qty += $myrow['qty'];
 169                 if ($qty < 0 && $flag == 0)
 170                 {
 171                         $flag = 1;
 172                         $negative_trans_id = $myrow['trans_id'];
 173                 }
 174                 if ($qty >= 0)
 175                         $flag = 0;
 176         }
 177
 178         if ($flag == 1)
 179                 return $negative_trans_id;
 180         else
 181                 return false;
 182 }
 183
 184 //-------------------------------------------------------------------
 185
 186 function get_deliveries_between($stock_id, $from, $to)
 187 {
 188         $from = date2sql($from);
 189         $to = date2sql($to);
 190         $sql = "SELECT SUM(-qty), SUM(-qty*standard_cost) FROM ".TB_PREF."stock_moves
 191                 WHERE type=".ST_CUSTDELIVERY." AND stock_id=".db_escape($stock_id)." AND
 192                         tran_date>='$from' AND tran_date<='$to' GROUP BY stock_id";
 193
 194         $result = db_query($sql, "The deliveries could not be updated");
 195         return db_fetch_row($result);
 196 }
 197
 198 function get_deliveries_from_trans($stock_id, $from)
 199 {
 200         // -ve qty is delivery either by ST_CUSTDELIVERY or inventory adjustment
 201     //Price for GRN and SUPPCREDIT and std_cost for other trans_types
 202     $sql = "SELECT SUM(-qty), SUM(-qty*IF(type=".ST_SUPPRECEIVE." OR type=".ST_SUPPCREDIT.", price, standard_cost))
 203         FROM ".TB_PREF."stock_moves
 204         WHERE stock_id=".db_escape($stock_id)." AND qty < 0 AND
 205             trans_id>='$from' GROUP BY stock_id";
 206         $result = db_query($sql, "The deliveries could not be updated");
 207         $row = db_fetch_row($result);
 208
 209     $sql = "SELECT IF(type=".ST_SUPPRECEIVE." OR type=".ST_SUPPCREDIT.", price, standard_cost)
 210         FROM ".TB_PREF."stock_moves
 211         WHERE stock_id=".db_escape($stock_id)
 212             ." AND trans_id ='$from'";
 213     $result = db_query($sql, "The deliveries could not be updated");
 214     $cost = db_fetch_row($result);
 215
 216         // Adjusting QOH valuation
 217         $sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves
 218                 WHERE stock_id=".db_escape($stock_id)." AND
 219                         trans_id<'$from' GROUP BY stock_id";
 220         $result = db_query($sql, "The deliveries could not be updated");
 221         $qoh = db_fetch_row($result);
 222
 223         $qty = $row[0] - $qoh[0]; //QOH prior to -ve stock is subtracted
 224         $final_cost = $row[1] - $qoh[0]*$cost[0];
 225
 226         return array($qty,$final_cost);
 227 }
 228
 229 function get_purchases_from_trans($stock_id, $from)
 230 {
 231         // Calculate All inward stock moves i.e. qty > 0
 232         $sql = "SELECT SUM(qty), SUM(qty*standard_cost) FROM ".TB_PREF."stock_moves
 233                 WHERE stock_id=".db_escape($stock_id)." AND qty > 0 AND
 234                         trans_id>'$from' GROUP BY stock_id";
 235         $result = db_query($sql, "Could not get get_purchases_from_trans");
 236         $row = db_fetch_row($result);
 237
 238         return $row;
 239 }
 240
 241 //-------------------------------------------------------------------
 242
 243 function adjust_deliveries($stock_id, $material_cost, $to)
 244 {
 245         if (!is_inventory_item($stock_id))
 246                 return;
 247
 248         $from = last_negative_stock_trans_id($stock_id, $to);
 249         if ($from == false || $from == -1)
 250                 return;
 251
 252         $row = get_deliveries_from_trans($stock_id, $from);
 253
 254         if ($row == false)
 255                 return;
 256         $old_sales_cost = $row[1];
 257         $new_sales_cost = $row[0] * $material_cost;
 258         $sales_diff = $new_sales_cost - $old_sales_cost;
 259
 260         $row = get_purchases_from_trans($stock_id, $from);
 261         $purchase_diff = 0;
 262         $old_purchase_cost = 0;
 263         if ($row != false)
 264         {
 265                 $old_purchase_cost = $row[1];
 266                 $new_purchase_cost = $row[0] * $material_cost;
 267                 $purchase_diff = $new_purchase_cost - $old_purchase_cost;
 268         }
 269
 270         $diff =  $sales_diff - $purchase_diff;
 271
 272         if ($diff != 0)
 273         {
 274                 $update_no = get_next_trans_no(ST_COSTUPDATE);
 275                 if (!is_date_in_fiscalyear($to))
 276                         $to = end_fiscalyear();
 277
 278                 $stock_gl_code = get_stock_gl_code($stock_id);
 279
 280                 $dec = user_price_dec();
 281                 $old_cost = -round2($old_sales_cost-$old_purchase_cost,$dec);
 282                 $new_cost = -round2($new_sales_cost-$new_purchase_cost,$dec);
 283
 284                 $memo_ = sprintf(_("Cost was %s changed to %s x quantity on hand for item '%s'"),
 285                         number_format2($old_cost, 2), number_format2($new_cost, 2), $stock_id);
 286                 add_gl_trans_std_cost(ST_COSTUPDATE, $update_no, $to, $stock_gl_code["cogs_account"],
 287                         $stock_gl_code["dimension_id"], $stock_gl_code["dimension2_id"], $memo_, $diff);
 288
 289                 add_gl_trans_std_cost(ST_COSTUPDATE, $update_no, $to, $stock_gl_code["inventory_account"], 0, 0, $memo_,
 290                         -$diff);
 291                 add_audit_trail(ST_COSTUPDATE, $update_no, $to);
 292         }
 293 }
 294
 295 function get_stock_gl_code($stock_id)
 296 {
 297         /*Gets the GL Codes relevant to the item account  */
 298         $sql = "SELECT mb_flag, inventory_account, cogs_account,
 299                 adjustment_account, sales_account, assembly_account, dimension_id, dimension2_id FROM
 300                 ".TB_PREF."stock_master WHERE stock_id = ".db_escape($stock_id);
 301
 302         $get = db_query($sql,"retreive stock gl code");
 303         return db_fetch($get);
 304 }
 305
 306 //-----------------------------------------------------------------------------------------
 307
 308 function handle_negative_inventory($stock_id, $quantity, $standard_cost, $date_)
 309 {
 310         //If negative adjustment result in negative or zero inventory
 311         //then difference should be adjusted
 312         $qoh = get_qoh_on_date($stock_id);
 313
 314         if ($qoh + $quantity <= 0 && $qoh > 0) //Positive inventory turning zero/negative
 315         {
 316                 global $Refs;
 317
 318                 $id = get_next_trans_no(ST_JOURNAL);
 319                 $ref = $Refs->get_next(ST_JOURNAL);
 320                 $diff = round($qoh*get_standard_cost($stock_id) + $quantity*$standard_cost, user_price_dec());
 321
 322                 if ($diff != 0)
 323                 {
 324                         $stock_gl_code = get_stock_gl_code($stock_id);
 325                         $memo = _("Zero/negative inventory handling");
 326                         //Reverse the inventory effect if $qoh <=0
 327                         add_gl_trans_std_cost(ST_JOURNAL, $id, $date_,
 328                                 $stock_gl_code["inventory_account"],
 329                                 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo,
 330                                 -$diff);
 331                         //GL Posting to inventory adjustment account
 332                         add_gl_trans_std_cost(ST_JOURNAL, $id, $date_,
 333                                 $stock_gl_code["adjustment_account"],
 334                                 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo,
 335                                 $diff);
 336
 337                         add_audit_trail(ST_JOURNAL, $id, $date_);
 338                         add_comments(ST_JOURNAL, $id, $date_, $memo);
 339                         $Refs->save(ST_JOURNAL, $id, $ref);
 340                 }
 341         }
 342 }
 343
 344 //--------------------------------------------------------------------------------------
 345
 346 // $date_ - display / non-sql date
 347 // $std_cost - in HOME currency
 348 // $show_or_hide - wil this move be visible in reports, etc
 349 // $price - in $person_id's currency
 350
 351 function add_stock_move($type, $stock_id, $trans_no, $location,
 352     $date_, $reference, $quantity, $std_cost, $person_id=0, $show_or_hide=1,
 353     $price=0, $discount_percent=0, $error_msg="")
 354 {
 355         $date = date2sql($date_);
 356
 357         $sql = "INSERT INTO ".TB_PREF."stock_moves (stock_id, trans_no, type, loc_code,
 358                 tran_date, person_id, reference, qty, standard_cost, visible, price,
 359                 discount_percent) VALUES (".db_escape($stock_id)
 360                 .", ".db_escape($trans_no).", ".db_escape($type)
 361                 .",     ".db_escape($location).", '$date', "
 362                 .db_escape($person_id).", ".db_escape($reference).", "
 363                 .db_escape($quantity).", ".db_escape($std_cost).","
 364                 .db_escape($show_or_hide).", "
 365                 .db_escape($price).", ".db_escape($discount_percent).")";
 366
 367         if ($error_msg == "")
 368                 $error_msg = "The stock movement record cannot be inserted";
 369
 370         db_query($sql, $error_msg);
 371
 372         return db_insert_id();
 373 }
 374
 375 function update_stock_move($type, $trans_no, $stock_id, $cost)
 376 {
 377         $sql = "UPDATE ".TB_PREF."stock_moves SET standard_cost=".db_escape($cost)
 378                         ." WHERE type=".db_escape($type)
 379                         ."      AND trans_no=".db_escape($trans_no)
 380                         ."      AND stock_id=".db_escape($stock_id);
 381         db_query($sql, "The stock movement standard_cost cannot be updated");
 382 }
 383
 384 //--------------------------------------------------------------------------------------------------
 385
 386 function get_stock_moves($type, $type_no, $visible=false)
 387 {
 388         $sql = "SELECT ".TB_PREF."stock_moves.*, ".TB_PREF."stock_master.description, "
 389                 .TB_PREF."stock_master.units,".TB_PREF."locations.location_name,"
 390                 .TB_PREF."stock_master.material_cost + "
 391                         .TB_PREF."stock_master.labour_cost + "
 392                         .TB_PREF."stock_master.overhead_cost AS FixedStandardCost
 393                 FROM ".TB_PREF."stock_moves,".TB_PREF."locations,".TB_PREF."stock_master
 394                 WHERE ".TB_PREF."stock_moves.stock_id = ".TB_PREF."stock_master.stock_id
 395                 AND ".TB_PREF."locations.loc_code=".TB_PREF."stock_moves.loc_code
 396                 AND type=".db_escape($type)." AND trans_no=".db_escape($type_no)." ORDER BY trans_id";
 397         if ($visible)
 398                 $sql .= " AND ".TB_PREF."stock_moves.visible=1";
 399
 400         return db_query($sql, "Could not get stock moves");
 401 }
 402
 403 //--------------------------------------------------------------------------------------------------
 404
 405 function void_stock_move($type, $type_no)
 406 {
 407     $sql = "SELECT * from ".TB_PREF."stock_moves WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no);
 408
 409     $result = db_query($sql, "Could not void stock moves");
 410     while ($row = db_fetch($result))
 411     {
 412                 //Skip cost averaging of service items
 413                 if (is_inventory_item($row["stock_id"]))
 414                 {
 415                         // The cost has to be adjusted.
 416                         // Transaction rates are stored either as price or standard_cost depending on types
 417                         $types = array(ST_SUPPCREDIT, ST_SUPPRECEIVE);
 418                         if (in_array($type, $types))
 419                                 $unit_cost = $row["price"];
 420                         else
 421                                 $unit_cost = $row["standard_cost"];
 422
 423                         update_average_material_cost($row["person_id"], $row["stock_id"],
 424                                 $unit_cost, -$row["qty"], sql2date($row["tran_date"]));
 425                 }
 426
 427     }
 428         $sql = "DELETE FROM ".TB_PREF."stock_moves WHERE type=".db_escape($type)
 429                 ."      AND trans_no=".db_escape($type_no);
 430         db_query($sql, "The stock movement cannot be delated");
 431 }
 432
 433 //--------------------------------------------------------------------------------------------------
 434
 435 function get_location_name($loc_code)
 436 {
 437         $sql = "SELECT location_name FROM ".TB_PREF."locations WHERE loc_code="
 438                 .db_escape($loc_code);
 439
 440         $result = db_query($sql, "could not retreive the location name for $loc_code");
 441
 442         if (db_num_rows($result) == 1)
 443         {
 444                 $row = db_fetch_row($result);
 445                 return $row[0];
 446         }
 447
 448         display_db_error("could not retreive the location name for $loc_code", $sql, true);
 449 }