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