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