manufacturing/includes/db/work_order_costing_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
  13 function add_wo_costing($workorder_id, $cost_type, $trans_type, $trans_no, $factor=1)
  14 {
  15         $sql = "INSERT INTO ".TB_PREF."wo_costing (workorder_id, cost_type, trans_type, trans_no, factor)
  16                 VALUES (".db_escape($workorder_id).","
  17                 .db_escape($cost_type).",".db_escape($trans_type).","
  18                 .db_escape($trans_no).",".db_escape($factor).")";
  19
  20         db_query($sql, "could not add work order costing");
  21
  22 }
  23
  24 function get_wo_costing($workorder_id)
  25 {
  26         $sql="SELECT * FROM ".TB_PREF."wo_costing WHERE workorder_id=".db_escape($workorder_id);
  27
  28         return db_query($sql, "could not get work order costing");
  29 }
  30
  31 function delete_wo_costing($trans_type, $trans_no)
  32 {
  33         $sql="DELETE FROM ".TB_PREF."wo_costing WHERE trans_type=".db_escape($trans_type)
  34                 ." AND trans_no=".db_escape($trans_no);
  35
  36         db_query($sql, "could not delete work order costing");
  37 }
  38 //--------------------------------------------------------------------------------------
  39
  40 function add_material_cost($stock_id, $qty, $date_, $advanced=false, $woid=0)
  41 {
  42         $m_cost = 0;
  43     $result = get_bom($stock_id);
  44         while ($bom_item = db_fetch($result))
  45         {
  46                 $standard_cost = get_standard_cost($bom_item['component']);
  47                 $m_cost += ($bom_item['quantity'] * $standard_cost);
  48         }
  49         $bom_cost = $m_cost;
  50         $i_cost = 0;
  51         if ($woid != 0 && work_order_has_issues($woid))
  52         {
  53                 $res = get_additional_issues($woid);
  54                 while ($issue = db_fetch($res))
  55                 {
  56                         $standard_cost = get_standard_cost($issue['stock_id']);
  57                         $i_cost += ($issue['qty_issued'] * $standard_cost) / $qty;
  58                 }
  59         }
  60         $sql = "SELECT material_cost, labour_cost, overhead_cost FROM ".TB_PREF."stock_master WHERE stock_id = "
  61                 .db_escape($stock_id);
  62         $result = db_query($sql);
  63         $myrow = db_fetch($result);
  64         $material_cost =  $myrow['material_cost'];
  65         $m_cost += $i_cost;
  66         /*
  67         if ($advanced)
  68         {
  69                 //reduce overhead_cost and labour_cost from price as those will remain as is
  70                 $m_cost = $m_cost - $myrow['labour_cost'] - $myrow['overhead_cost'];
  71         }
  72         */
  73         $qoh = get_qoh_on_date($stock_id);
  74         $cost_adjust = false;
  75         if ($qoh < 0)
  76         {
  77                 if ($qoh + $qty >= 0)
  78                         $cost_adjust = true;
  79                 $qoh = 0;
  80         }
  81         if ($qoh + $qty != 0)
  82         {
  83                 if ($qoh == 0)
  84                         $material_cost = $m_cost;
  85                 else
  86                         $material_cost = ($qoh * $material_cost + $qty * $m_cost) /     ($qoh + $qty);
  87         }
  88
  89         if ($advanced && $cost_adjust)
  90                 adjust_deliveries($stock_id, $bom_cost, $date_);
  91
  92         $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=".db_escape($material_cost)."
  93                     WHERE stock_id=".db_escape($stock_id);
  94         db_query($sql,"The cost details for the inventory item could not be updated");
  95 }
  96
  97 /*
  98         Updates average material cost for item.
  99 */
 100 function update_material_cost($stock_id, $qty, $unit_cost, $date)
 101 {
 102         global $Refs;
 103
 104         $product = get_item($stock_id);
 105         $avg_cost =  $product['material_cost'];
 106
 107         $qoh = get_qoh_on_date($stock_id, null, $date);
 108
 109         // unit_cost*qty is already posted on inventory GL
 110 /*
 111         if ($qoh > 0)
 112         {
 113                  if ($qty<0)
 114                         $qty_done = max(-$qoh, $qty);
 115                  else
 116                         $qty_done = $qty;
 117         } else {
 118                 if ($qty>0)
 119                         $qty_done = max($qoh+$qty, 0);
 120                 else
 121                         $qty_done = 0;
 122         }
 123         $qty_delayed = $qty-$qty_done;
 124 */
 125         if ($qoh < 0)
 126         {
 127                         $qty_delayed = min(-$qoh, $qty);
 128         } else {
 129                         $qty_delayed = 0;
 130         }
 131         $qty_new = $qty - $qty_delayed;
 132
 133         if ($qty_delayed > 0 && ($unit_cost != $avg_cost))
 134         {
 135                 // post $qty_delayed*($avg_cost-$unit_cost)
 136                 $diff = $qty_delayed*($unit_cost-$avg_cost);
 137                 $stock_gl_code = get_item($stock_id);
 138
 139                 $dec = user_price_dec();
 140                 $old_cost = -round2($avg_cost, $dec);
 141                 $new_cost = -round2($unit_cost, $dec);
 142
 143                 $cart = new items_cart(ST_COSTUPDATE);
 144                 $cart->tran_date = $cart->doc_date = $cart->event_date = $date;
 145
 146                 if (!is_date_in_fiscalyear($cart->tran_date))
 147                         $cart->tran_date = end_fiscalyear();
 148
 149                 $cart->reference = $Refs->get_next(ST_COSTUPDATE, null, $cart->tran_date, $date);
 150
 151                 $cart->memo_ = sprintf(_("COGS changed from %s to %s for %d %s of '%s'"), $avg_cost, $unit_cost, $qty_delayed, $item['units'], $stock_id);
 152
 153                 $cart->add_gl_item($item["cogs_account"], $item["dimension_id"], $item["dimension2_id"], -$diff);
 154                 $cart->add_gl_item($item["inventory_account"], 0, 0, $diff);
 155
 156                 write_journal_entries($cart);
 157         }
 158
 159         if ($qty > 0 && ($qoh != -$qty))
 160                 $avg_cost = ($avg_cost*($qoh+$qty_delayed)+$unit_cost*$qty_new)/($qoh+$qty);
 161
 162         $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=".db_escape($avg_cost)."
 163                     WHERE stock_id=".db_escape($stock_id);
 164         db_query($sql,"The cost details for the inventory item could not be updated");
 165 }
 166
 167 function add_overhead_cost($stock_id, $qty, $date_, $costs, $adj_only=false)
 168 {
 169         if ($qty != 0)
 170                 $costs /= $qty;
 171         $sql = "SELECT overhead_cost FROM ".TB_PREF."stock_master WHERE stock_id = "
 172                 .db_escape($stock_id);
 173         $result = db_query($sql);
 174         $myrow = db_fetch($result);
 175         $overhead_cost =  $myrow['overhead_cost'];
 176         $qoh = get_qoh_on_date($stock_id);
 177         if ($qoh < 0)
 178                 $qoh = 0;
 179         if ($adj_only)
 180         {
 181                 if ($qoh>0)
 182                 {
 183                         if ($qoh + $qty != 0)
 184                                 $overhead_cost = ($qoh * $overhead_cost + $qty * $costs) /      ($qoh + $qty);
 185                         elseif ($qty == 0)
 186                                 $overhead_cost = ($qoh * $overhead_cost + $costs) / $qoh;
 187                 }
 188                 else // Journal Entry if QOH is 0/negative
 189                 {
 190                         global $Refs;
 191
 192                         $id = get_next_trans_no(ST_JOURNAL);
 193                         $ref = $Refs->get_next(ST_JOURNAL, null, $date_);
 194
 195                         $stock_gl_code = get_stock_gl_code($stock_id);
 196                         add_journal(ST_JOURNAL, $id, $costs, $date_, get_company_currency(), $ref);
 197                         $memo = "WO Overhead cost settlement JV for zero/negative respository of ".$stock_id;
 198                         //Reverse the inventory effect if $qoh <=0
 199                         add_gl_trans_std_cost(ST_JOURNAL, $id, $date_,
 200                                 $stock_gl_code["inventory_account"],
 201                                 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo,
 202                                 -$costs);
 203                         //GL Posting to inventory adjustment account
 204                         add_gl_trans_std_cost(ST_JOURNAL, $id, $date_,
 205                                 $stock_gl_code["assembly_account"],
 206                                 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo,
 207                                 $costs);
 208
 209                         add_audit_trail(ST_JOURNAL, $id, $date_);
 210                         add_comments(ST_JOURNAL, $id, $date_, $memo);
 211                         $Refs->save(ST_JOURNAL, $id, $ref);
 212                         if ($qty != 0)
 213                                 $overhead_cost = ($qoh * $overhead_cost + $costs) / $qty;
 214                 }
 215         }
 216         else
 217         {
 218                 if ($qoh + $qty != 0)
 219                         $overhead_cost = ($qoh * $overhead_cost + $qty * $costs) /      ($qoh + $qty);
 220         }
 221         $sql = "UPDATE ".TB_PREF."stock_master SET overhead_cost=".db_escape($overhead_cost)."
 222                 WHERE stock_id=".db_escape($stock_id);
 223         db_query($sql,"The cost details for the inventory item could not be updated");
 224 }
 225
 226 function add_labour_cost($stock_id, $qty, $date_, $costs, $adj_only=false)
 227 {
 228         if ($qty != 0)
 229                 $costs /= $qty;
 230         $sql = "SELECT labour_cost FROM ".TB_PREF."stock_master WHERE stock_id = "
 231                 .db_escape($stock_id);
 232         $result = db_query($sql);
 233         $myrow = db_fetch($result);
 234         $labour_cost =  $myrow['labour_cost'];
 235         $qoh = get_qoh_on_date($stock_id);
 236         if ($qoh < 0)
 237                 $qoh = 0;
 238         if ($adj_only)
 239         {
 240                 if ($qoh>0)
 241                 {
 242                         if ($qoh + $qty != 0)
 243                                 $labour_cost = ($qoh * $labour_cost + $qty * $costs) /  ($qoh + $qty);
 244                         elseif ($qty == 0)
 245                                 $labour_cost = ($qoh * $labour_cost + $costs) / $qoh;
 246                 }
 247                 else // Journal Entry if QOH is 0/negative
 248                 {
 249                         global $Refs;
 250
 251                         $id = get_next_trans_no(ST_JOURNAL);
 252                         $ref = $Refs->get_next(ST_JOURNAL, null, $date_);
 253                         add_journal(ST_JOURNAL, $id, $costs, $date_, get_company_currency(), $ref);
 254
 255                         $stock_gl_code = get_stock_gl_code($stock_id);
 256                         $memo = "WO labour cost settlement JV for zero/negative respository of ".$stock_id;
 257                         //Reverse the inventory effect if $qoh <=0
 258                         add_gl_trans_std_cost(ST_JOURNAL, $id, $date_,
 259                                 $stock_gl_code["inventory_account"],
 260                                 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo,
 261                                 -$costs);
 262                         //GL Posting to inventory adjustment account
 263                         add_gl_trans_std_cost(ST_JOURNAL, $id, $date_,
 264                                 $stock_gl_code["assembly_account"],
 265                                 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo,
 266                                 $costs);
 267
 268                         add_audit_trail(ST_JOURNAL, $id, $date_);
 269                         add_comments(ST_JOURNAL, $id, $date_, $memo);
 270                         $Refs->save(ST_JOURNAL, $id, $ref);
 271                         if ($qty != 0)
 272                                 $labour_cost = ($qoh * $labour_cost + $costs) / $qty;
 273                 }
 274         }
 275         else
 276         {
 277                 if ($qoh + $qty != 0)
 278                         $labour_cost = ($qoh * $labour_cost + $qty * $costs) /  ($qoh + $qty);
 279         }
 280         $sql = "UPDATE ".TB_PREF."stock_master SET labour_cost=".db_escape($labour_cost)."
 281                 WHERE stock_id=".db_escape($stock_id);
 282         db_query($sql,"The cost details for the inventory item could not be updated");
 283 }
 284
 285 function add_issue_cost($stock_id, $qty, $date_, $costs, $adj_only=false)
 286 {
 287         if ($qty != 0)
 288                 $costs /= $qty;
 289         $sql = "SELECT material_cost FROM ".TB_PREF."stock_master WHERE stock_id = "
 290                 .db_escape($stock_id);
 291         $result = db_query($sql);
 292         $myrow = db_fetch($result);
 293         $material_cost =  $myrow['material_cost'];
 294         $qoh = get_qoh_on_date($stock_id);
 295         if ($qoh < 0)
 296                 $qoh = 0;
 297         if ($adj_only)
 298         {
 299                 if ($qoh>0)
 300                         $material_cost = ($qoh * $material_cost + $costs) / $qoh;
 301                 else // Journal Entry if QOH is 0/negative
 302                 {
 303                         global $Refs;
 304
 305                         $id = get_next_trans_no(ST_JOURNAL);
 306                         $ref = $Refs->get_next(ST_JOURNAL, null, $date_);
 307                         add_journal(ST_JOURNAL, $id, $costs, $date_, get_company_currency(), $ref);
 308
 309                         $stock_gl_code = get_stock_gl_code($stock_id);
 310                         $memo = "WO Issue settlement JV for zero/negative respository of ".$stock_id;
 311                         //Reverse the inventory effect if $qoh <=0
 312                         add_gl_trans_std_cost(ST_JOURNAL, $id, $date_,
 313                                 $stock_gl_code["inventory_account"],
 314                                 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo,
 315                                 -$costs);
 316                         //GL Posting to inventory adjustment account
 317                         add_gl_trans_std_cost(ST_JOURNAL, $id, $date_,
 318                                 $stock_gl_code["assembly_account"],
 319                                 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo,
 320                                 $costs);
 321
 322                         add_audit_trail(ST_JOURNAL, $id, $date_);
 323                         add_comments(ST_JOURNAL, $id, $date_, $memo);
 324                         $Refs->save(ST_JOURNAL, $id, $ref);
 325                         if ($qty != 0)
 326                                 $material_cost = $costs / $qty;
 327                 }
 328         }
 329         else
 330         {
 331                 if ($qoh + $qty != 0)
 332                         $material_cost = ($qoh * $material_cost + $qty * $costs) /      ($qoh + $qty);
 333         }
 334         $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=material_cost+"
 335                 .db_escape($material_cost)
 336                 ." WHERE stock_id=".db_escape($stock_id);
 337         db_query($sql,"The cost details for the inventory item could not be updated");
 338 }
 339
 340 /*
 341         Create journal entry for WO related costs.
 342 */
 343 function add_wo_costs_journal($wo_id, $amount, $cost_type, $cr_acc, $db_acc, $date, $dim1=0, $dim2=0, $memo = null, $ref= null)
 344 {
 345 //-------- this should be done by single call to write_journal_entries() using items_cart()
 346 //
 347         global $Refs, $wo_cost_types;
 348
 349         begin_transaction();
 350
 351     $journal_id = get_next_trans_no(ST_JOURNAL);
 352
 353     if (!$ref) $ref = $Refs->get_next(ST_JOURNAL, null, $date);
 354
 355         add_gl_trans_std_cost(ST_JOURNAL, $journal_id, $date, $cr_acc,
 356                 0, 0, $wo_cost_types[$cost_type], -$amount);
 357         $is_bank_to = is_bank_account($cr_acc);
 358         if ($is_bank_to)
 359         {
 360                 add_bank_trans(ST_JOURNAL, $journal_id, $is_bank_to, "",
 361                         $date, -$amount, PT_WORKORDER, $wo_id, get_company_currency(),
 362                         "Cannot insert a destination bank transaction");
 363         }
 364         add_journal(ST_JOURNAL, $journal_id, $amount, $date, get_company_currency(), $ref);
 365
 366         add_gl_trans_std_cost(ST_JOURNAL, $journal_id, $date, $db_acc,
 367                 $dim1, $dim2,  $wo_cost_types[$cost_type], $amount);
 368
 369         $Refs->save(ST_JOURNAL, $journal_id, $ref);
 370
 371         add_wo_costing($wo_id, $cost_type, ST_JOURNAL, $journal_id);
 372
 373         add_comments(ST_JOURNAL, $journal_id, $date, $memo);
 374         add_audit_trail(ST_JOURNAL, $journal_id, $date);
 375
 376         commit_transaction();
 377 }
 378
 379 function void_wo_costing($wo_id)
 380 {
 381         $res = get_wo_costing($wo_id);
 382
 383         while($row = db_fetch($res))
 384         {
 385                 // void any related gl trans
 386                 void_gl_trans($row['trans_type'], $row['trans_no'], true);
 387         }
 388
 389         $sql = "DELETE FROM ".TB_PREF."wo_costing WHERE workorder_id=".db_escape($wo_id);
 390         db_query($sql, "could not delete work order costing");
 391 }