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         $sql = "SELECT IF(s.mb_flag='D', 0, material_cost + labour_cost + overhead_cost) AS std_cost
  69                 FROM ".TB_PREF."stock_master s WHERE stock_id=".db_escape($stock_id);
  70         $result = db_query($sql, "The standard cost cannot be retrieved");
  71
  72         $myrow = db_fetch_row($result);
  73
  74         return $myrow[0];
  75 }
  76
  77 //--------------------------------------------------------------------------------------
  78
  79 function is_inventory_item($stock_id)
  80 {
  81         $sql = "SELECT stock_id FROM ".TB_PREF."stock_master
  82                 WHERE stock_id=".db_escape($stock_id)." AND mb_flag <> 'D'";
  83         $result = db_query($sql, "Cannot query is inventory item or not");
  84
  85         return db_num_rows($result) > 0;
  86 }
  87
  88 //-------------------------------------------------------------------
  89
  90 function last_negative_stock_begin_date($stock_id, $to)
  91 {
  92         $to = date2sql($to);
  93         $sql ="SET @q = 0";
  94         db_query($sql);
  95         $sql = "SET @flag = 0";
  96         db_query($sql);
  97         $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
  98                 FROM ".TB_PREF."stock_moves
  99                 WHERE stock_id=".db_escape($stock_id)." AND tran_date<='$to'
 100                 AND qty <> 0
 101                 GROUP BY stock_id ORDER BY tran_date";
 102
 103         $result = db_query($sql, "The dstock moves could not be retrieved");
 104         $row = db_fetch_row($result);
 105         return $row[3];
 106 }
 107
 108 //-------------------------------------------------------------------
 109 // Newly written
 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         return $negative_trans_id;
 148 }
 149
 150 //-------------------------------------------------------------------
 151
 152 function get_deliveries_between($stock_id, $from, $to)
 153 {
 154         $from = date2sql($from);
 155         $to = date2sql($to);
 156         $sql = "SELECT SUM(-qty), SUM(-qty*standard_cost) FROM ".TB_PREF."stock_moves
 157                 WHERE type=".ST_CUSTDELIVERY." AND stock_id=".db_escape($stock_id)." AND
 158                         tran_date>='$from' AND tran_date<='$to' GROUP BY stock_id";
 159
 160         $result = db_query($sql, "The deliveries could not be updated");
 161         return db_fetch_row($result);
 162 }
 163
 164 //Newly written
 165 function get_deliveries_from_trans($stock_id, $from)
 166 {
 167         // -ve qty is delivery either by ST_CUSTDELIVERY or inventory adjustment
 168         $sql = "SELECT SUM(-qty), SUM(-qty*standard_cost) FROM ".TB_PREF."stock_moves
 169                 WHERE stock_id=".db_escape($stock_id)." AND qty < 0 AND
 170                         trans_id>='$from' GROUP BY stock_id";
 171         $result = db_query($sql, "The deliveries could not be updated");
 172         $row = db_fetch_row($result);
 173
 174         display_notification('Row0 - '.$row[0].' Row1- '.$row[1]);
 175
 176         //return $row;
 177
 178         // Get Std cost of previsous transaction before the cut-over delivery
 179         // This is useful to get inventory valuation
 180         $prev_trans = $from - 1;
 181         display_notification('From - '.$from.' Prev- '.$prev_trans);
 182         $sql = "SELECT standard_cost FROM ".TB_PREF."stock_moves
 183                 WHERE stock_id=".db_escape($stock_id)." AND
 184                         trans_id ='$prev_trans'";
 185         $result = db_query($sql, "The deliveries could not be updated");
 186         $cost = db_fetch_row($result);
 187
 188         display_notification('Last Delivery Cost - '.$cost[0]);
 189
 190         // Adjusting QOH valuation
 191         $sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves
 192                 WHERE stock_id=".db_escape($stock_id)." AND
 193                         trans_id<'$from' GROUP BY stock_id";
 194         $result = db_query($sql, "The deliveries could not be updated");
 195         $qoh = db_fetch_row($result);
 196
 197         display_notification('QOH before last delivery - '.$qoh[0]);
 198
 199         $qty = $row[0] - $qoh[0]; //Qoh is minus from delivered in -ve
 200         $final_cost = $row[1] - $qoh[0]*$cost[0];
 201
 202         display_notification('Qty - '.$qty.' cost- '.$final_cost);
 203
 204         return array($qty,$final_cost);
 205 }
 206
 207 //Newly written
 208 function get_purchases_from_trans($stock_id, $from)
 209 {
 210         // Calculate All inward stock moves i.e. qty > 0
 211         $sql = "SELECT SUM(qty), SUM(qty*standard_cost) FROM ".TB_PREF."stock_moves
 212                 WHERE stock_id=".db_escape($stock_id)." AND qty > 0 AND
 213                         trans_id>'$from' GROUP BY stock_id";
 214         $result = db_query($sql, "The deliveries could not be updated");
 215         $row = db_fetch_row($result);
 216
 217         display_notification('Purchase Qty - '.$row[0].' Cost- '.$row[1]);
 218
 219         return $row;
 220 }
 221
 222 //-------------------------------------------------------------------
 223 /* Original Code V0 Leave as is a while
 224 function adjust_deliveries_v0($stock_id, $material_cost, $to)
 225 {
 226         if (!is_inventory_item($stock_id))
 227                 return;
 228         $from = last_negative_stock_begin_date($stock_id, $to);
 229         if ($from == false || $from == "")
 230                 return;
 231         $from = sql2date($from);
 232         $row = get_deliveries_between($stock_id, $from, $to);
 233         if ($row == false)
 234                 return;
 235         $old_cost = $row[1];
 236         $new_cost = $row[0] * $material_cost;
 237         $diff = $new_cost - $old_cost;
 238         if ($diff != 0)
 239         {
 240                 $update_no = get_next_trans_no(ST_COSTUPDATE);
 241                 if (!is_date_in_fiscalyear($to))
 242                         $to = end_fiscalyear();
 243
 244                 $stock_gl_code = get_stock_gl_code($stock_id);
 245
 246                 $memo_ = sprintf(_("Cost was %s changed to %s for item '%s'"),
 247                         $old_cost, $new_cost, $stock_id);
 248                 add_gl_trans_std_cost(ST_COSTUPDATE, $update_no, $to, $stock_gl_code["cogs_account"],
 249                         $stock_gl_code["dimension_id"], $stock_gl_code["dimension2_id"], $memo_, $diff);
 250
 251                 add_gl_trans_std_cost(ST_COSTUPDATE, $update_no, $to, $stock_gl_code["inventory_account"],
 252                         0, 0, $memo_, -$diff);
 253                 add_audit_trail(ST_COSTUPDATE, $update_no, $to);
 254         }
 255 }
 256 */
 257 //New written function
 258 function adjust_deliveries($stock_id, $material_cost, $to)
 259 {
 260         if (!is_inventory_item($stock_id))
 261                 return;
 262
 263         $from = last_negative_stock_trans_id($stock_id, $to);
 264         if ($from == false || $from == "")
 265                 return;
 266
 267         $row = get_deliveries_from_trans($stock_id, $from);
 268
 269         if ($row == false)
 270                 return;
 271         $old_sales_cost = $row[1];
 272         $new_sales_cost = $row[0] * $material_cost;
 273         $sales_diff = $new_sales_cost - $old_sales_cost;
 274
 275         $row = get_purchases_from_trans($stock_id, $from);
 276         $purchase_diff = 0;
 277         $old_purchase_cost = 0;
 278         if ($row != false)
 279         {
 280                 $old_purchase_cost = $row[1];
 281                 $new_purchase_cost = $row[0] * $material_cost;
 282                 $purchase_diff = $new_purchase_cost - $old_purchase_cost;
 283         }
 284
 285         $diff =  $sales_diff - $purchase_diff;
 286
 287         if ($diff != 0)
 288         {
 289                 $update_no = get_next_trans_no(ST_COSTUPDATE);
 290                 if (!is_date_in_fiscalyear($to))
 291                         $to = end_fiscalyear();
 292
 293                 $stock_gl_code = get_stock_gl_code($stock_id);
 294
 295                 $dec = user_price_dec();
 296                 $old_cost = -round2($old_sales_cost-$old_purchase_cost,$dec);
 297                 $new_cost = -round2($new_sales_cost-$new_purchase_cost,$dec);
 298
 299                 $memo_ = _("Cost was ") . $old_cost. _(" changed to ") . $new_cost . _(" for item ")."'$stock_id'";
 300                 add_gl_trans_std_cost(ST_COSTUPDATE, $update_no, $to, $stock_gl_code["cogs_account"],
 301                         $stock_gl_code["dimension_id"], $stock_gl_code["dimension2_id"], $memo_, $diff);
 302
 303                 add_gl_trans_std_cost(ST_COSTUPDATE, $update_no, $to, $stock_gl_code["inventory_account"], 0, 0, $memo_,
 304                         -$diff);
 305                 add_audit_trail(ST_COSTUPDATE, $update_no, $to);
 306         }
 307 }
 308
 309 function get_stock_gl_code($stock_id)
 310 {
 311         /*Gets the GL Codes relevant to the item account  */
 312
 313         $sql = "SELECT inventory_account, cogs_account,
 314                 adjustment_account, sales_account, assembly_account, dimension_id, dimension2_id FROM
 315                 ".TB_PREF."stock_master WHERE stock_id = ".db_escape($stock_id);
 316
 317         $get = db_query($sql,"retreive stock gl code");
 318         return db_fetch($get);
 319 }
 320
 321 //--------------------------------------------------------------------------------------
 322
 323 // $date_ - display / non-sql date
 324 // $std_cost - in HOME currency
 325 // $show_or_hide - wil this move be visible in reports, etc
 326 // $price - in $person_id's currency
 327
 328 function add_stock_move($type, $stock_id, $trans_no, $location,
 329     $date_, $reference, $quantity, $std_cost, $person_id=0, $show_or_hide=1,
 330     $price=0, $discount_percent=0, $error_msg="")
 331 {
 332         // do not add a stock move if it's a non-inventory item
 333         if (!is_inventory_item($stock_id))
 334                 return null;
 335
 336         $date = date2sql($date_);
 337
 338         $sql = "INSERT INTO ".TB_PREF."stock_moves (stock_id, trans_no, type, loc_code,
 339                 tran_date, person_id, reference, qty, standard_cost, visible, price,
 340                 discount_percent) VALUES (".db_escape($stock_id)
 341                 .", ".db_escape($trans_no).", ".db_escape($type)
 342                 .",     ".db_escape($location).", '$date', "
 343                 .db_escape($person_id).", ".db_escape($reference).", "
 344                 .db_escape($quantity).", ".db_escape($std_cost).","
 345                 .db_escape($show_or_hide).", ".db_escape($price).", "
 346                 .db_escape($discount_percent).")";
 347
 348         if ($error_msg == "")
 349                 $error_msg = "The stock movement record cannot be inserted";
 350
 351         db_query($sql, $error_msg);
 352
 353         return db_insert_id();
 354 }
 355
 356 function update_stock_move_pid($type, $stock_id, $from, $to, $pid, $cost)
 357 {
 358         $from = date2sql($from);
 359         $to = date2sql($to);
 360                 $sql = "UPDATE ".TB_PREF."stock_moves SET standard_cost=".db_escape($cost)
 361                         ." WHERE type=".db_escape($type)
 362                         ."      AND stock_id=".db_escape($stock_id)
 363                         ."  AND tran_date>='$from' AND tran_date<='$to'
 364                         AND person_id = ".db_escape($pid);
 365         db_query($sql, "The stock movement standard_cost cannot be updated");
 366 }
 367
 368 //--------------------------------------------------------------------------------------------------
 369
 370 function get_stock_moves($type, $type_no, $visible=false)
 371 {
 372         $sql = "SELECT ".TB_PREF."stock_moves.*, ".TB_PREF."stock_master.description, "
 373                 .TB_PREF."stock_master.units,".TB_PREF."locations.location_name,"
 374                 .TB_PREF."stock_master.material_cost + "
 375                         .TB_PREF."stock_master.labour_cost + "
 376                         .TB_PREF."stock_master.overhead_cost AS FixedStandardCost
 377                 FROM ".TB_PREF."stock_moves,".TB_PREF."locations,".TB_PREF."stock_master
 378                 WHERE ".TB_PREF."stock_moves.stock_id = ".TB_PREF."stock_master.stock_id
 379                 AND ".TB_PREF."locations.loc_code=".TB_PREF."stock_moves.loc_code
 380                 AND type=".db_escape($type)." AND trans_no=".db_escape($type_no)." ORDER BY trans_id";
 381         if ($visible)
 382                 $sql .= " AND ".TB_PREF."stock_moves.visible=1";
 383
 384         return db_query($sql, "Could not get stock moves");
 385 }
 386
 387 //--------------------------------------------------------------------------------------------------
 388
 389 function void_stock_move($type, $type_no)
 390 {
 391     //Chaitanya : Reversing stock move rather than voiding as it is hazardous to lose stock movement trail with respect to costing
 392     /*$sql = "UPDATE ".TB_PREF."stock_moves SET qty=0, price=0, discount_percent=0,
 393         standard_cost=0    WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no);
 394
 395     db_query($sql, "Could not void stock moves"); */
 396
 397     $sql = "SELECT * from ".TB_PREF."stock_moves WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no);
 398     $result = db_query($sql, "Could not void stock moves");
 399     while ($row = db_fetch($result))
 400     {
 401         add_stock_move($type, $row["stock_id"], $type_no, $row["loc_code"],
 402             sql2date($row["tran_date"]), $row["reference"], -$row["qty"], $row["standard_cost"], $row["person_id"], $row["visible"],
 403             $row["price"], $row["discount_percent"]);
 404
 405         // If there is cost difference, then cost has to be adjusted.
 406         // IMP : Adjusting cost is suitable instead of normal averaging as it will not work properly for manufactured items. Reason being Avg cost is suitable only for purchased items
 407
 408         $curr_std_cost = get_standard_cost($row["stock_id"]);
 409
 410         //Chaitanya : Transaction rates are stored either as price or standard_cost depending
 411         //on types
 412         $types = array(ST_SUPPCREDIT);
 413         if (in_array($type,$types))
 414             $trans_rate = $row["price"];
 415         else
 416             $trans_rate = $row["standard_cost"];
 417
 418         if ($curr_std_cost != $trans_rate)
 419         {
 420             $cost_diff = $trans_rate - $curr_std_cost;
 421             update_average_material_cost(0, $row["stock_id"],
 422                 $cost_diff, -$row["qty"], sql2date($row["tran_date"]), true);
 423         }
 424
 425     }
 426 }
 427
 428 //--------------------------------------------------------------------------------------------------
 429
 430 function get_location_name($loc_code)
 431 {
 432         $sql = "SELECT location_name FROM ".TB_PREF."locations WHERE loc_code="
 433                 .db_escape($loc_code);
 434
 435         $result = db_query($sql, "could not retreive the location name for $loc_code");
 436
 437         if (db_num_rows($result) == 1)
 438         {
 439                 $row = db_fetch_row($result);
 440                 return $row[0];
 441         }
 442
 443         display_db_error("could not retreive the location name for $loc_code", $sql, true);
 444 }
 445
 446 //--------------------------------------------------------------------------------------------------
 447
 448
 449 ?>