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     return $myrow[0];
  49 }
  50
  51 //--------------------------------------------------------------------------------------
  52
  53 function get_item_edit_info($stock_id)
  54 {
  55         $sql = "SELECT material_cost + labour_cost + overhead_cost AS standard_cost, units, decimals
  56                 FROM ".TB_PREF."stock_master,".TB_PREF."item_units
  57                 WHERE stock_id=".db_escape($stock_id)
  58                 ." AND ".TB_PREF."stock_master.units=".TB_PREF."item_units.abbr";
  59         $result = db_query($sql, "The standard cost cannot be retrieved");
  60
  61         return db_fetch($result);
  62 }
  63
  64 //--------------------------------------------------------------------------------------
  65
  66 function get_standard_cost($stock_id)
  67 {
  68         //Chaitanya : Compatibility with service items
  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 // Newly written
 111
 112 function get_already_delivered($stock_id, $location, $trans_no)
 113 {
 114         $sql = "SELECT ".TB_PREF."stock_moves.qty
 115                 FROM ".TB_PREF."stock_moves
 116                 WHERE ".TB_PREF."stock_moves.stock_id = ".db_escape($stock_id)."
 117                 AND ".TB_PREF."stock_moves.loc_code = ".db_escape($location)."
 118                 AND type=".ST_CUSTDELIVERY." AND trans_no=".db_escape($trans_no);
 119         $result = db_query($sql, "Could not get stock moves");
 120         $row = db_fetch_row($result);
 121         return $row[0];
 122 }
 123
 124 function last_negative_stock_trans_id($stock_id, $to)
 125 {
 126         $sql = "SELECT * from ".TB_PREF."stock_moves
 127                 WHERE stock_id=".db_escape($stock_id)."
 128                 AND qty <> 0 order by trans_id asc";
 129
 130         $result = db_query($sql, "The query on stock moves failed.");
 131
 132         $qty = 0;
 133         $flag = 0;
 134         $negative_trans_id = 1;
 135
 136         while ($myrow = db_fetch($result))
 137         {
 138                 $qty += $myrow['qty'];
 139                 if ($qty < 0 && $flag == 0)
 140                 {
 141                         $flag = 1;
 142                         $negative_trans_id = $myrow['trans_id'];
 143                 }
 144                 if ($qty >= 0)
 145                         $flag = 0;
 146         }
 147
 148         return $negative_trans_id;
 149 }
 150
 151 //-------------------------------------------------------------------
 152
 153 function get_deliveries_between($stock_id, $from, $to)
 154 {
 155         $from = date2sql($from);
 156         $to = date2sql($to);
 157         $sql = "SELECT SUM(-qty), SUM(-qty*standard_cost) FROM ".TB_PREF."stock_moves
 158                 WHERE type=".ST_CUSTDELIVERY." AND stock_id=".db_escape($stock_id)." AND
 159                         tran_date>='$from' AND tran_date<='$to' GROUP BY stock_id";
 160
 161         $result = db_query($sql, "The deliveries could not be updated");
 162         return db_fetch_row($result);
 163 }
 164
 165 //Newly written
 166 function get_deliveries_from_trans($stock_id, $from)
 167 {
 168         // -ve qty is delivery either by ST_CUSTDELIVERY or inventory adjustment
 169         $sql = "SELECT SUM(-qty), SUM(-qty*standard_cost) FROM ".TB_PREF."stock_moves
 170                 WHERE stock_id=".db_escape($stock_id)." AND qty < 0 AND
 171                         trans_id>='$from' GROUP BY stock_id";
 172         $result = db_query($sql, "The deliveries could not be updated");
 173         $row = db_fetch_row($result);
 174
 175         display_notification('Row0 - '.$row[0].' Row1- '.$row[1]);
 176
 177         //return $row;
 178
 179         // Get Std cost of previsous transaction before the cut-over delivery
 180         // This is useful to get inventory valuation
 181         //Chaitanya : Corrected
 182         /*$sql = "SELECT max( `trans_id` )
 183                         FROM ".TB_PREF."stock_moves
 184                         WHERE stock_id = ".db_escape($stock_id)."
 185                         AND trans_id<'$from'";
 186         $result = db_query($sql, "The deliveries could not be updated");
 187         $trans = db_fetch_row($result);
 188         $prev_trans = $trans[0];*/
 189
 190         display_notification('From - '.$from);
 191         $sql = "SELECT standard_cost FROM ".TB_PREF."stock_moves
 192                 WHERE stock_id=".db_escape($stock_id)
 193                         ." AND trans_id ='$from'";
 194         $result = db_query($sql, "The deliveries could not be updated");
 195         $cost = db_fetch_row($result);
 196
 197         display_notification('Last Delivery Cost - '.$cost[0]);
 198
 199         // Adjusting QOH valuation
 200         $sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves
 201                 WHERE stock_id=".db_escape($stock_id)." AND
 202                         trans_id<'$from' GROUP BY stock_id";
 203         $result = db_query($sql, "The deliveries could not be updated");
 204         $qoh = db_fetch_row($result);
 205
 206         display_notification('QOH before last delivery - '.$qoh[0]);
 207
 208         $qty = $row[0] - $qoh[0]; //Qoh is minus from delivered in -ve
 209         $final_cost = $row[1] - $qoh[0]*$cost[0];
 210
 211         display_notification('Qty - '.$qty.' cost- '.$final_cost);
 212
 213         return array($qty,$final_cost);
 214 }
 215
 216 //Newly written
 217 function get_purchases_from_trans($stock_id, $from)
 218 {
 219         // Calculate All inward stock moves i.e. qty > 0
 220         $sql = "SELECT SUM(qty), SUM(qty*standard_cost) FROM ".TB_PREF."stock_moves
 221                 WHERE stock_id=".db_escape($stock_id)." AND qty > 0 AND
 222                         trans_id>'$from' GROUP BY stock_id";
 223         display_notification($sql);
 224         $result = db_query($sql, "Could not get get_purchases_from_trans");
 225         $row = db_fetch_row($result);
 226
 227         display_notification('Purchase Qty - '.$row[0].' Cost- '.$row[1]);
 228
 229         return $row;
 230 }
 231
 232 //-------------------------------------------------------------------
 233 /* Original Code V0 Leave as is a while
 234 function adjust_deliveries_v0($stock_id, $material_cost, $to)
 235 {
 236         if (!is_inventory_item($stock_id))
 237                 return;
 238         $from = last_negative_stock_begin_date($stock_id, $to);
 239         if ($from == false || $from == "")
 240                 return;
 241         $from = sql2date($from);
 242         $row = get_deliveries_between($stock_id, $from, $to);
 243         if ($row == false)
 244                 return;
 245         $old_cost = $row[1];
 246         $new_cost = $row[0] * $material_cost;
 247         $diff = $new_cost - $old_cost;
 248         if ($diff != 0)
 249         {
 250                 $update_no = get_next_trans_no(ST_COSTUPDATE);
 251                 if (!is_date_in_fiscalyear($to))
 252                         $to = end_fiscalyear();
 253
 254                 $stock_gl_code = get_stock_gl_code($stock_id);
 255
 256                 $memo_ = sprintf(_("Cost was %s changed to %s for item '%s'"),
 257                         $old_cost, $new_cost, $stock_id);
 258                 add_gl_trans_std_cost(ST_COSTUPDATE, $update_no, $to, $stock_gl_code["cogs_account"],
 259                         $stock_gl_code["dimension_id"], $stock_gl_code["dimension2_id"], $memo_, $diff);
 260
 261                 add_gl_trans_std_cost(ST_COSTUPDATE, $update_no, $to, $stock_gl_code["inventory_account"],
 262                         0, 0, $memo_, -$diff);
 263                 add_audit_trail(ST_COSTUPDATE, $update_no, $to);
 264         }
 265 }
 266 */
 267 //New written function
 268 function adjust_deliveries($stock_id, $material_cost, $to)
 269 {
 270         if (!is_inventory_item($stock_id))
 271                 return;
 272
 273         $from = last_negative_stock_trans_id($stock_id, $to);
 274         if ($from == false || $from == "")
 275                 return;
 276
 277         $row = get_deliveries_from_trans($stock_id, $from);
 278
 279         if ($row == false)
 280                 return;
 281         $old_sales_cost = $row[1];
 282         $new_sales_cost = $row[0] * $material_cost;
 283         $sales_diff = $new_sales_cost - $old_sales_cost;
 284
 285         display_notification(_("new_sales_cost").' - '.$row[0] .' * '. $material_cost);
 286         display_notification(_("new_sales_cost").' - '.$new_sales_cost.' old_sales_cost- '.$old_sales_cost);
 287         display_notification(_("Sales Diff").' - '.$sales_diff);
 288
 289         $row = get_purchases_from_trans($stock_id, $from);
 290         $purchase_diff = 0;
 291         $old_purchase_cost = 0;
 292         if ($row != false)
 293         {
 294                 $old_purchase_cost = $row[1];
 295                 $new_purchase_cost = $row[0] * $material_cost;
 296                 $purchase_diff = $new_purchase_cost - $old_purchase_cost;
 297         }
 298
 299         display_notification(_("new_purchase_cost").' - '.$new_purchase_cost.' old_purchase_cost- '.$old_purchase_cost);
 300         display_notification(_("purchase_diff").' - '.$purchase_diff);
 301
 302         $diff =  $sales_diff - $purchase_diff;
 303
 304         display_notification(_("Final diff").' - '.$diff);
 305
 306         if ($diff != 0)
 307         {
 308                 $update_no = get_next_trans_no(ST_COSTUPDATE);
 309                 if (!is_date_in_fiscalyear($to))
 310                         $to = end_fiscalyear();
 311
 312                 $stock_gl_code = get_stock_gl_code($stock_id);
 313
 314                 $dec = user_price_dec();
 315                 $old_cost = -round2($old_sales_cost-$old_purchase_cost,$dec);
 316                 $new_cost = -round2($new_sales_cost-$new_purchase_cost,$dec);
 317
 318                 $memo_ = _("Cost was ") . $old_cost. _(" changed to ") . $new_cost . _(" for item ")."'$stock_id'";
 319                 add_gl_trans_std_cost(ST_COSTUPDATE, $update_no, $to, $stock_gl_code["cogs_account"],
 320                         $stock_gl_code["dimension_id"], $stock_gl_code["dimension2_id"], $memo_, $diff);
 321
 322                 add_gl_trans_std_cost(ST_COSTUPDATE, $update_no, $to, $stock_gl_code["inventory_account"], 0, 0, $memo_,
 323                         -$diff);
 324                 add_audit_trail(ST_COSTUPDATE, $update_no, $to);
 325         }
 326 }
 327
 328 function get_stock_gl_code($stock_id)
 329 {
 330         /*Gets the GL Codes relevant to the item account  */
 331         //Chaitanya : Updated to also provide mb_flag
 332         $sql = "SELECT mb_flag, inventory_account, cogs_account,
 333                 adjustment_account, sales_account, assembly_account, dimension_id, dimension2_id FROM
 334                 ".TB_PREF."stock_master WHERE stock_id = ".db_escape($stock_id);
 335
 336         $get = db_query($sql,"retreive stock gl code");
 337         return db_fetch($get);
 338 }
 339
 340 //-----------------------------------------------------------------------------------------
 341 //Chaitanya : New Function handle negative stock effect
 342 //Called in add_stock_move
 343 function handle_negative_inventory($stock_id, $quantity, $standard_cost, $date_)
 344 {
 345         //Chaitanya : If negative adjustment result in negative or zero inventory
 346         //then difference should be adjusted
 347         $qoh = get_qoh_on_date($stock_id);
 348
 349         if ($qoh + $quantity <= 0 && $qoh > 0) //Positive inventory turning zero/negative
 350         {
 351                 global $Refs;
 352
 353                 $id = get_next_trans_no(ST_JOURNAL);
 354                 $ref = $Refs->get_next(ST_JOURNAL);
 355                 $diff = get_standard_cost($stock_id) - $standard_cost;
 356
 357                 if ($diff !=0)
 358                 {
 359                         $stock_gl_code = get_stock_gl_code($stock_id);
 360                         $memo = _("Zero/negative inventory handling");
 361                         //Reverse the inventory effect if $qoh <=0
 362                         add_gl_trans_std_cost(ST_JOURNAL, $id, $date_,
 363                                 $stock_gl_code["inventory_account"],
 364                                 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo,
 365                                 -$qoh * $diff);
 366                         //GL Posting to inventory adjustment account
 367                         add_gl_trans_std_cost(ST_JOURNAL, $id, $date_,
 368                                 $stock_gl_code["adjustment_account"],
 369                                 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo,
 370                                 $qoh * $diff);
 371
 372                         add_audit_trail(ST_JOURNAL, $id, $date_);
 373                         add_comments(ST_JOURNAL, $id, $date_, $memo);
 374                         $Refs->save(ST_JOURNAL, $id, $ref);
 375                 }
 376         }
 377 }
 378
 379 //--------------------------------------------------------------------------------------
 380
 381 // $date_ - display / non-sql date
 382 // $std_cost - in HOME currency
 383 // $show_or_hide - wil this move be visible in reports, etc
 384 // $price - in $person_id's currency
 385
 386 function add_stock_move($type, $stock_id, $trans_no, $location,
 387     $date_, $reference, $quantity, $std_cost, $person_id=0, $show_or_hide=1,
 388     $price=0, $discount_percent=0, $error_msg="")
 389 {
 390         // Chaitanya : Removed following restriction considering WO issues
 391         // Voiding issues and productions with Service items can not get the
 392         // Service items compatibility
 393         // do not add a stock move if it's a non-inventory item
 394         //if (!is_inventory_item($stock_id))
 395                 //return null;
 396
 397         $date = date2sql($date_);
 398
 399         $sql = "INSERT INTO ".TB_PREF."stock_moves (stock_id, trans_no, type, loc_code,
 400                 tran_date, person_id, reference, qty, standard_cost, visible, price,
 401                 discount_percent) VALUES (".db_escape($stock_id)
 402                 .", ".db_escape($trans_no).", ".db_escape($type)
 403                 .",     ".db_escape($location).", '$date', "
 404                 .db_escape($person_id).", ".db_escape($reference).", "
 405                 .db_escape($quantity).", ".db_escape($std_cost).","
 406                 .db_escape($show_or_hide).", "
 407                 .db_escape($price).", ".db_escape($discount_percent).")";
 408
 409         if ($error_msg == "")
 410                 $error_msg = "The stock movement record cannot be inserted";
 411
 412         db_query($sql, $error_msg);
 413
 414         return db_insert_id();
 415 }
 416
 417 //Chaitanya : Added function
 418 function update_stock_move($type, $trans_no, $stock_id, $cost)
 419 {
 420         $sql = "UPDATE ".TB_PREF."stock_moves SET standard_cost=".db_escape($cost)
 421                         ." WHERE type=".db_escape($type)
 422                         ."      AND trans_no=".db_escape($trans_no)
 423                         ."      AND stock_id=".db_escape($stock_id);
 424         db_query($sql, "The stock movement standard_cost cannot be updated");
 425 }
 426
 427 function update_stock_move_pid($type, $type_no, $stock_id, $pid, $cost)
 428 {
 429         $sql = "UPDATE ".TB_PREF."stock_moves SET standard_cost=".db_escape($cost)
 430                 ." WHERE type=".db_escape($type)
 431                 ."      AND trans_no=".db_escape($type_no)
 432                 ."      AND stock_id=".db_escape($stock_id)
 433                 ."  AND person_id = ".db_escape($pid);
 434         db_query($sql, "The stock movement standard_cost cannot be updated");
 435 }
 436
 437 //--------------------------------------------------------------------------------------------------
 438
 439 function get_stock_moves($type, $type_no, $visible=false)
 440 {
 441         $sql = "SELECT ".TB_PREF."stock_moves.*, ".TB_PREF."stock_master.description, "
 442                 .TB_PREF."stock_master.units,".TB_PREF."locations.location_name,"
 443                 .TB_PREF."stock_master.material_cost + "
 444                         .TB_PREF."stock_master.labour_cost + "
 445                         .TB_PREF."stock_master.overhead_cost AS FixedStandardCost
 446                 FROM ".TB_PREF."stock_moves,".TB_PREF."locations,".TB_PREF."stock_master
 447                 WHERE ".TB_PREF."stock_moves.stock_id = ".TB_PREF."stock_master.stock_id
 448                 AND ".TB_PREF."locations.loc_code=".TB_PREF."stock_moves.loc_code
 449                 AND type=".db_escape($type)." AND trans_no=".db_escape($type_no)." ORDER BY trans_id";
 450         if ($visible)
 451                 $sql .= " AND ".TB_PREF."stock_moves.visible=1";
 452
 453         return db_query($sql, "Could not get stock moves");
 454 }
 455
 456 //--------------------------------------------------------------------------------------------------
 457
 458 function void_stock_move($type, $type_no)
 459 {
 460     //Chaitanya : Reversing stock move rather than voiding as it is hazardous to lose stock movement trail with respect to costing
 461     /*$sql = "UPDATE ".TB_PREF."stock_moves SET qty=0, price=0, discount_percent=0,
 462         standard_cost=0 WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no);
 463
 464     db_query($sql, "Could not void stock moves"); */
 465
 466     $sql = "SELECT * from ".TB_PREF."stock_moves WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no);
 467     $result = db_query($sql, "Could not void stock moves");
 468     while ($row = db_fetch($result))
 469     {
 470                 //Skip cost averaging of service items
 471                 if (is_inventory_item($row["stock_id"]))
 472                 {
 473                         // The cost has to be adjusted.
 474                         //Chaitanya : Transaction rates are stored either as price or standard_cost depending
 475                         //on types
 476                         $types = array(ST_SUPPCREDIT);
 477                         if (in_array($type,$types))
 478                                 $trans_rate = $row["price"];
 479                         else
 480                                 $trans_rate = $row["standard_cost"];
 481
 482                         update_average_material_cost(0, $row["stock_id"],
 483                                 $trans_rate, -$row["qty"], sql2date($row["tran_date"]));
 484                 }
 485
 486                 //Post stock move for service items also
 487         add_stock_move($type, $row["stock_id"], $type_no, $row["loc_code"],
 488                 sql2date($row["tran_date"]), $row["reference"], -$row["qty"]
 489                         , $row["standard_cost"], $row["person_id"], $row["visible"],
 490                 $row["price"], $row["discount_percent"]);
 491     }
 492 }
 493
 494 //--------------------------------------------------------------------------------------------------
 495
 496 function get_location_name($loc_code)
 497 {
 498         $sql = "SELECT location_name FROM ".TB_PREF."locations WHERE loc_code="
 499                 .db_escape($loc_code);
 500
 501         $result = db_query($sql, "could not retreive the location name for $loc_code");
 502
 503         if (db_num_rows($result) == 1)
 504         {
 505                 $row = db_fetch_row($result);
 506                 return $row[0];
 507         }
 508
 509         display_db_error("could not retreive the location name for $loc_code", $sql, true);
 510 }
 511
 512 //--------------------------------------------------------------------------------------------------
 513
 514
 515 ?>