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 material_cost + labour_cost + overhead_cost AS standard_cost, units, decimals
  90                 FROM ".TB_PREF."stock_master,".TB_PREF."item_units
  91                 WHERE stock_id=".db_escape($stock_id)
  92                 ." AND ".TB_PREF."stock_master.units=".TB_PREF."item_units.abbr";
  93         $result = db_query($sql, "The standard cost cannot be retrieved");
  94
  95         return db_fetch($result);
  96 }
  97
  98 //--------------------------------------------------------------------------------------
  99
 100 function get_standard_cost($stock_id)
 101 {
 102         $sql = "SELECT (material_cost + labour_cost + overhead_cost) AS std_cost
 103                 FROM ".TB_PREF."stock_master s WHERE stock_id=".db_escape($stock_id);
 104         $result = db_query($sql, "The standard cost cannot be retrieved");
 105
 106         $myrow = db_fetch_row($result);
 107
 108         return $myrow[0];
 109 }
 110
 111 //--------------------------------------------------------------------------------------
 112
 113 function is_inventory_item($stock_id)
 114 {
 115         $sql = "SELECT stock_id FROM ".TB_PREF."stock_master
 116                 WHERE stock_id=".db_escape($stock_id)." AND mb_flag <> 'D'";
 117         $result = db_query($sql, "Cannot query is inventory item or not");
 118
 119         return db_num_rows($result) > 0;
 120 }
 121
 122 //-------------------------------------------------------------------
 123
 124 function last_negative_stock_begin_date($stock_id, $to)
 125 {
 126         $to = date2sql($to);
 127         $sql ="SET @q = 0";
 128         db_query($sql);
 129         $sql = "SET @flag = 0";
 130         db_query($sql);
 131         $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
 132                 FROM ".TB_PREF."stock_moves
 133                 WHERE stock_id=".db_escape($stock_id)." AND tran_date<='$to'
 134                 AND qty <> 0
 135                 GROUP BY stock_id ORDER BY tran_date";
 136
 137         $result = db_query($sql, "The dstock moves could not be retrieved");
 138         $row = db_fetch_row($result);
 139         return $row[3];
 140 }
 141
 142 //-------------------------------------------------------------------
 143
 144 function get_already_delivered($stock_id, $location, $trans_no)
 145 {
 146         $sql = "SELECT ".TB_PREF."stock_moves.qty
 147                 FROM ".TB_PREF."stock_moves
 148                 WHERE ".TB_PREF."stock_moves.stock_id = ".db_escape($stock_id)."
 149                 AND ".TB_PREF."stock_moves.loc_code = ".db_escape($location)."
 150                 AND type=".ST_CUSTDELIVERY." AND trans_no=".db_escape($trans_no);
 151         $result = db_query($sql, "Could not get stock moves");
 152         $row = db_fetch_row($result);
 153         return $row[0];
 154 }
 155
 156 function last_negative_stock_trans_id($stock_id, $to)
 157 {
 158         $sql = "SELECT * from ".TB_PREF."stock_moves
 159                 WHERE stock_id=".db_escape($stock_id)."
 160                 AND qty <> 0 order by trans_id asc";
 161
 162         $result = db_query($sql, "The query on stock moves failed.");
 163
 164         $qty = 0;
 165         $flag = 0;
 166         $negative_trans_id = -1;
 167
 168         while ($myrow = db_fetch($result))
 169         {
 170                 $qty += $myrow['qty'];
 171                 if ($qty < 0 && $flag == 0)
 172                 {
 173                         $flag = 1;
 174                         $negative_trans_id = $myrow['trans_id'];
 175                 }
 176                 if ($qty >= 0)
 177                         $flag = 0;
 178         }
 179
 180         if ($flag == 1)
 181                 return $negative_trans_id;
 182         else
 183                 return false;
 184 }
 185
 186 //-------------------------------------------------------------------
 187
 188 function get_deliveries_between($stock_id, $from, $to)
 189 {
 190         $from = date2sql($from);
 191         $to = date2sql($to);
 192         $sql = "SELECT SUM(-qty), SUM(-qty*standard_cost) FROM ".TB_PREF."stock_moves
 193                 WHERE type=".ST_CUSTDELIVERY." AND stock_id=".db_escape($stock_id)." AND
 194                         tran_date>='$from' AND tran_date<='$to' GROUP BY stock_id";
 195
 196         $result = db_query($sql, "The deliveries could not be updated");
 197         return db_fetch_row($result);
 198 }
 199
 200 function get_deliveries_from_trans($stock_id, $from)
 201 {
 202         // -ve qty is delivery either by ST_CUSTDELIVERY or inventory adjustment
 203     //Price for GRN and SUPPCREDIT and std_cost for other trans_types
 204     $sql = "SELECT SUM(-qty), SUM(-qty*IF(type=".ST_SUPPRECEIVE." OR type=".ST_SUPPCREDIT.", price, standard_cost))
 205         FROM ".TB_PREF."stock_moves
 206         WHERE stock_id=".db_escape($stock_id)." AND qty < 0 AND
 207             trans_id>='$from' GROUP BY stock_id";
 208         $result = db_query($sql, "The deliveries could not be updated");
 209         $row = db_fetch_row($result);
 210
 211     $sql = "SELECT IF(type=".ST_SUPPRECEIVE." OR type=".ST_SUPPCREDIT.", price, standard_cost)
 212         FROM ".TB_PREF."stock_moves
 213         WHERE stock_id=".db_escape($stock_id)
 214             ." AND trans_id ='$from'";
 215     $result = db_query($sql, "The deliveries could not be updated");
 216     $cost = db_fetch_row($result);
 217
 218         // Adjusting QOH valuation
 219         $sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves
 220                 WHERE stock_id=".db_escape($stock_id)." AND
 221                         trans_id<'$from' GROUP BY stock_id";
 222         $result = db_query($sql, "The deliveries could not be updated");
 223         $qoh = db_fetch_row($result);
 224
 225         $qty = $row[0] - $qoh[0]; //QOH prior to -ve stock is subtracted
 226         $final_cost = $row[1] - $qoh[0]*$cost[0];
 227
 228         return array($qty,$final_cost);
 229 }
 230
 231 function get_purchases_from_trans($stock_id, $from)
 232 {
 233         // Calculate All inward stock moves i.e. qty > 0
 234         $sql = "SELECT SUM(qty), SUM(qty*standard_cost) FROM ".TB_PREF."stock_moves
 235                 WHERE stock_id=".db_escape($stock_id)." AND qty > 0 AND
 236                         trans_id>'$from' GROUP BY stock_id";
 237         $result = db_query($sql, "Could not get get_purchases_from_trans");
 238         $row = db_fetch_row($result);
 239
 240         return $row;
 241 }
 242
 243 //-------------------------------------------------------------------
 244
 245 function adjust_deliveries($stock_id, $material_cost, $to)
 246 {
 247         global $Refs;
 248
 249         if (!is_inventory_item($stock_id))
 250                 return;
 251
 252         $from = last_negative_stock_trans_id($stock_id, $to);
 253         if ($from == false || $from == -1)
 254                 return;
 255
 256         $row = get_deliveries_from_trans($stock_id, $from);
 257
 258         if ($row == false)
 259                 return;
 260         $old_sales_cost = $row[1];
 261         $new_sales_cost = $row[0] * $material_cost;
 262         $sales_diff = $new_sales_cost - $old_sales_cost;
 263
 264         $row = get_purchases_from_trans($stock_id, $from);
 265         $purchase_diff = 0;
 266         $old_purchase_cost = $new_purchase_cost = 0;
 267         if ($row != false)
 268         {
 269                 $old_purchase_cost = $row[1];
 270                 $new_purchase_cost = $row[0] * $material_cost;
 271                 $purchase_diff = $new_purchase_cost - $old_purchase_cost;
 272         }
 273
 274         $diff =  $sales_diff - $purchase_diff;
 275
 276         if ($diff != 0)
 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                 $cart = new items_cart(ST_COSTUPDATE);
 285                 $cart->tran_date = $cart->doc_date = $cart->event_date = $to;
 286                 if (!is_date_in_fiscalyear($cart->tran_date))
 287                         $cart->tran_date = end_fiscalyear();
 288                 $cart->reference = $Refs->get_next(ST_COSTUPDATE, null, $cart->tran_date, $to);
 289
 290                 $cart->memo_ = _("Cost was ") . $old_cost. _(" changed to ") . $new_cost . _(" for item ")."'$stock_id'";
 291
 292                 $cart->add_gl_item($stock_gl_code["cogs_account"], $stock_gl_code["dimension_id"], $stock_gl_code["dimension2_id"], $diff);
 293                 $cart->add_gl_item($stock_gl_code["inventory_account"], 0, 0, -$diff);
 294
 295                 write_journal_entries($cart);
 296         }
 297 }
 298
 299 function get_stock_gl_code($stock_id)
 300 {
 301         /*Gets the GL Codes relevant to the item account  */
 302         $sql = "SELECT mb_flag, inventory_account, cogs_account,
 303                 adjustment_account, sales_account, assembly_account, dimension_id, dimension2_id FROM
 304                 ".TB_PREF."stock_master WHERE stock_id = ".db_escape($stock_id);
 305
 306         $get = db_query($sql,"retreive stock gl code");
 307         return db_fetch($get);
 308 }
 309
 310 //-----------------------------------------------------------------------------------------
 311
 312 function handle_negative_inventory($stock_id, $quantity, $standard_cost, $date_)
 313 {
 314         //If negative adjustment result in negative or zero inventory
 315         //then difference should be adjusted
 316         $qoh = get_qoh_on_date($stock_id);
 317
 318         if ($qoh + $quantity <= 0 && $qoh > 0) //Positive inventory turning zero/negative
 319         {
 320                 global $Refs;
 321
 322                 $id = get_next_trans_no(ST_JOURNAL);
 323                 $ref = $Refs->get_next(ST_JOURNAL);
 324                 $diff = round($qoh*get_standard_cost($stock_id) + $quantity*$standard_cost, user_price_dec());
 325
 326                 if ($diff != 0)
 327                 {
 328                         begin_transaction();
 329                         add_journal(ST_JOURNAL, $id, $diff, $date_, get_company_currency(), $ref);
 330                         $Refs->save(ST_JOURNAL, $id, $ref);
 331
 332                         $stock_gl_code = get_stock_gl_code($stock_id);
 333                         $memo = _("Zero/negative inventory handling");
 334                         //Reverse the inventory effect if $qoh <=0
 335                         add_gl_trans_std_cost(ST_JOURNAL, $id, $date_,
 336                                 $stock_gl_code["inventory_account"],
 337                                 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo,
 338                                 -$diff);
 339                         //GL Posting to inventory adjustment account
 340                         add_gl_trans_std_cost(ST_JOURNAL, $id, $date_,
 341                                 $stock_gl_code["adjustment_account"],
 342                                 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo,
 343                                 $diff);
 344
 345                         add_audit_trail(ST_JOURNAL, $id, $date_);
 346                         add_comments(ST_JOURNAL, $id, $date_, $memo);
 347                         $Refs->save(ST_JOURNAL, $id, $ref);
 348                         commit_transaction();
 349                 }
 350         }
 351 }
 352
 353 //--------------------------------------------------------------------------------------
 354
 355 // $date_ - display / non-sql date
 356 // $std_cost - in HOME currency
 357 // $price - in $person_id's currency
 358
 359 function add_stock_move($type, $stock_id, $trans_no, $location,
 360     $date_, $reference, $quantity, $std_cost, $person_id=0,
 361     $price=0, $discount_percent=0, $error_msg="")
 362 {
 363         $date = date2sql($date_);
 364
 365         $sql = "INSERT INTO ".TB_PREF."stock_moves (stock_id, trans_no, type, loc_code,
 366                 tran_date, person_id, reference, qty, standard_cost, price,
 367                 discount_percent) VALUES (".db_escape($stock_id)
 368                 .", ".db_escape($trans_no).", ".db_escape($type)
 369                 .",     ".db_escape($location).", '$date', "
 370                 .db_escape($person_id).", ".db_escape($reference).", "
 371                 .db_escape($quantity).", ".db_escape($std_cost).","
 372                 .db_escape($price).", ".db_escape($discount_percent).")";
 373
 374         if ($error_msg == "")
 375                 $error_msg = "The stock movement record cannot be inserted";
 376
 377         db_query($sql, $error_msg);
 378
 379         return db_insert_id();
 380 }
 381
 382 function update_stock_move($type, $trans_no, $stock_id, $cost)
 383 {
 384         $sql = "UPDATE ".TB_PREF."stock_moves SET standard_cost=".db_escape($cost)
 385                         ." WHERE type=".db_escape($type)
 386                         ."      AND trans_no=".db_escape($trans_no)
 387                         ."      AND stock_id=".db_escape($stock_id);
 388         db_query($sql, "The stock movement standard_cost cannot be updated");
 389 }
 390
 391 //--------------------------------------------------------------------------------------------------
 392
 393 function get_stock_moves($type, $type_no)
 394 {
 395         $sql = "SELECT ".TB_PREF."stock_moves.*, ".TB_PREF."stock_master.description, "
 396                 .TB_PREF."stock_master.units,".TB_PREF."locations.location_name,"
 397                 .TB_PREF."stock_master.material_cost + "
 398                         .TB_PREF."stock_master.labour_cost + "
 399                         .TB_PREF."stock_master.overhead_cost AS FixedStandardCost
 400                 FROM ".TB_PREF."stock_moves,".TB_PREF."locations,".TB_PREF."stock_master
 401                 WHERE ".TB_PREF."stock_moves.stock_id = ".TB_PREF."stock_master.stock_id
 402                 AND ".TB_PREF."locations.loc_code=".TB_PREF."stock_moves.loc_code
 403                 AND type=".db_escape($type)." AND trans_no=".db_escape($type_no)." ORDER BY trans_id";
 404
 405         return db_query($sql, "Could not get stock moves");
 406 }
 407
 408 //--------------------------------------------------------------------------------------------------
 409
 410 function void_stock_move($type, $type_no)
 411 {
 412     $sql = "SELECT * from ".TB_PREF."stock_moves WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no);
 413
 414     $result = db_query($sql, "Could not void stock moves");
 415     while ($row = db_fetch($result))
 416     {
 417                 //Skip cost averaging of service items
 418                 if (is_inventory_item($row["stock_id"]))
 419                 {
 420                         // The cost has to be adjusted.
 421                         // Transaction rates are stored either as price or standard_cost depending on types
 422                         $types = array(ST_SUPPCREDIT, ST_SUPPRECEIVE);
 423                         if (in_array($type, $types))
 424                                 $unit_cost = $row["price"];
 425                         else
 426                                 $unit_cost = $row["standard_cost"];
 427
 428                         update_average_material_cost($row["person_id"], $row["stock_id"],
 429                                 $unit_cost, -$row["qty"], sql2date($row["tran_date"]));
 430                 }
 431
 432     }
 433         $sql = "DELETE FROM ".TB_PREF."stock_moves WHERE type=".db_escape($type)
 434                 ."      AND trans_no=".db_escape($type_no);
 435         db_query($sql, "The stock movement cannot be delated");
 436 }
 437
 438 //--------------------------------------------------------------------------------------------------
 439
 440 function get_location_name($loc_code)
 441 {
 442         $sql = "SELECT location_name FROM ".TB_PREF."locations WHERE loc_code="
 443                 .db_escape($loc_code);
 444
 445         $result = db_query($sql, "could not retreive the location name for $loc_code");
 446
 447         if (db_num_rows($result) == 1)
 448         {
 449                 $row = db_fetch_row($result);
 450                 return $row[0];
 451         }
 452
 453         display_db_error("could not retreive the location name for $loc_code", $sql, true);
 454 }