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         // new 2015.10.15
  51         // additilnal costs.
  52         $i_cost = 0;
  53         if ($woid != 0 && work_order_has_issues($woid))
  54         {
  55                 $res = get_additional_issues($woid);
  56                 while ($issue = db_fetch($res))
  57                 {
  58                         $standard_cost = get_standard_cost($issue['stock_id']);
  59                         $i_cost += ($issue['qty_issued'] * $standard_cost) / $qty;
  60                 }
  61         }
  62         $sql = "SELECT material_cost, labour_cost, overhead_cost FROM ".TB_PREF."stock_master WHERE stock_id = "
  63                 .db_escape($stock_id);
  64         $result = db_query($sql);
  65         $myrow = db_fetch($result);
  66         $material_cost =  $myrow['material_cost'];
  67         $m_cost += $i_cost;
  68         /*
  69         if ($advanced)
  70         {
  71                 //reduce overhead_cost and labour_cost from price as those will remain as is
  72                 $m_cost = $m_cost - $myrow['labour_cost'] - $myrow['overhead_cost'];
  73         }
  74         */
  75         $qoh = get_qoh_on_date($stock_id);
  76         $cost_adjust = false;
  77         if ($qoh < 0)
  78         {
  79                 if ($qoh + $qty >= 0)
  80                         $cost_adjust = true;
  81                 $qoh = 0;
  82         }
  83         if ($qoh + $qty != 0)
  84         {
  85                 if ($qoh == 0)
  86                         $material_cost = $m_cost; // corrected 27.10.2014
  87                 else
  88                         $material_cost = ($qoh * $material_cost + $qty * $m_cost) /     ($qoh + $qty);
  89         }
  90
  91         if ($advanced && $cost_adjust) // new 2010-02-10
  92                 adjust_deliveries($stock_id, $bom_cost, $date_);
  93
  94         $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=".db_escape($material_cost)."
  95                     WHERE stock_id=".db_escape($stock_id);
  96         db_query($sql,"The cost details for the inventory item could not be updated");
  97 }
  98
  99 function add_overhead_cost($stock_id, $qty, $date_, $costs, $adj_only=false)
 100 {
 101         if ($qty != 0)
 102                 $costs /= $qty;
 103         $sql = "SELECT overhead_cost FROM ".TB_PREF."stock_master WHERE stock_id = "
 104                 .db_escape($stock_id);
 105         $result = db_query($sql);
 106         $myrow = db_fetch($result);
 107         $overhead_cost =  $myrow['overhead_cost'];
 108         $qoh = get_qoh_on_date($stock_id);
 109         if ($qoh < 0)
 110                 $qoh = 0;
 111         if ($adj_only)
 112         {
 113                 if ($qoh>0)
 114                 {
 115                         if ($qoh + $qty != 0)
 116                                 $overhead_cost = ($qoh * $overhead_cost + $qty * $costs) /      ($qoh + $qty);
 117                         elseif ($qty == 0)
 118                                 $overhead_cost = ($qoh * $overhead_cost + $costs) / $qoh;
 119                 }
 120                 else // Journal Entry if QOH is 0/negative
 121                 {
 122                         global $Refs;
 123
 124                         $id = get_next_trans_no(ST_JOURNAL);
 125                         $ref = $Refs->get_next(ST_JOURNAL, null, $date_);
 126
 127                         $stock_gl_code = get_stock_gl_code($stock_id);
 128                         add_journal(ST_JOURNAL, $id, $costs, $date_, get_company_currency(), $ref);
 129                         $memo = "WO Overhead cost settlement JV for zero/negative respository of ".$stock_id;
 130                         //Reverse the inventory effect if $qoh <=0
 131                         add_gl_trans_std_cost(ST_JOURNAL, $id, $date_,
 132                                 $stock_gl_code["inventory_account"],
 133                                 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo,
 134                                 -$costs);
 135                         //GL Posting to inventory adjustment account
 136                         add_gl_trans_std_cost(ST_JOURNAL, $id, $date_,
 137                                 $stock_gl_code["assembly_account"],
 138                                 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo,
 139                                 $costs);
 140
 141                         add_audit_trail(ST_JOURNAL, $id, $date_);
 142                         add_comments(ST_JOURNAL, $id, $date_, $memo);
 143                         $Refs->save(ST_JOURNAL, $id, $ref);
 144                         if ($qty != 0)
 145                                 $overhead_cost = ($qoh * $overhead_cost + $costs) / $qty;
 146                 }
 147         }
 148         else
 149         {
 150                 if ($qoh + $qty != 0)
 151                         $overhead_cost = ($qoh * $overhead_cost + $qty * $costs) /      ($qoh + $qty);
 152         }
 153         $sql = "UPDATE ".TB_PREF."stock_master SET overhead_cost=".db_escape($overhead_cost)."
 154                 WHERE stock_id=".db_escape($stock_id);
 155         db_query($sql,"The cost details for the inventory item could not be updated");
 156 }
 157
 158 function add_labour_cost($stock_id, $qty, $date_, $costs, $adj_only=false)
 159 {
 160         if ($qty != 0)
 161                 $costs /= $qty;
 162         $sql = "SELECT labour_cost FROM ".TB_PREF."stock_master WHERE stock_id = "
 163                 .db_escape($stock_id);
 164         $result = db_query($sql);
 165         $myrow = db_fetch($result);
 166         $labour_cost =  $myrow['labour_cost'];
 167         $qoh = get_qoh_on_date($stock_id);
 168         if ($qoh < 0)
 169                 $qoh = 0;
 170         if ($adj_only)
 171         {
 172                 if ($qoh>0)
 173                 {
 174                         if ($qoh + $qty != 0)
 175                                 $labour_cost = ($qoh * $labour_cost + $qty * $costs) /  ($qoh + $qty);
 176                         elseif ($qty == 0)
 177                                 $labour_cost = ($qoh * $labour_cost + $costs) / $qoh;
 178                 }
 179                 else // Journal Entry if QOH is 0/negative
 180                 {
 181                         global $Refs;
 182
 183                         $id = get_next_trans_no(ST_JOURNAL);
 184                         $ref = $Refs->get_next(ST_JOURNAL, null, $date_);
 185                         add_journal(ST_JOURNAL, $id, $costs, $date_, get_company_currency(), $ref);
 186
 187                         $stock_gl_code = get_stock_gl_code($stock_id);
 188                         $memo = "WO labour cost settlement JV for zero/negative respository of ".$stock_id;
 189                         //Reverse the inventory effect if $qoh <=0
 190                         add_gl_trans_std_cost(ST_JOURNAL, $id, $date_,
 191                                 $stock_gl_code["inventory_account"],
 192                                 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo,
 193                                 -$costs);
 194                         //GL Posting to inventory adjustment account
 195                         add_gl_trans_std_cost(ST_JOURNAL, $id, $date_,
 196                                 $stock_gl_code["assembly_account"],
 197                                 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo,
 198                                 $costs);
 199
 200                         add_audit_trail(ST_JOURNAL, $id, $date_);
 201                         add_comments(ST_JOURNAL, $id, $date_, $memo);
 202                         $Refs->save(ST_JOURNAL, $id, $ref);
 203                         if ($qty != 0)
 204                                 $labour_cost = ($qoh * $labour_cost + $costs) / $qty;
 205                 }
 206         }
 207         else
 208         {
 209                 if ($qoh + $qty != 0)
 210                         $labour_cost = ($qoh * $labour_cost + $qty * $costs) /  ($qoh + $qty);
 211         }
 212         $sql = "UPDATE ".TB_PREF."stock_master SET labour_cost=".db_escape($labour_cost)."
 213                 WHERE stock_id=".db_escape($stock_id);
 214         db_query($sql,"The cost details for the inventory item could not be updated");
 215 }
 216
 217 function add_issue_cost($stock_id, $qty, $date_, $costs, $adj_only=false)
 218 {
 219         if ($qty != 0)
 220                 $costs /= $qty;
 221         $sql = "SELECT material_cost FROM ".TB_PREF."stock_master WHERE stock_id = "
 222                 .db_escape($stock_id);
 223         $result = db_query($sql);
 224         $myrow = db_fetch($result);
 225         $material_cost =  $myrow['material_cost'];
 226         $qoh = get_qoh_on_date($stock_id);
 227         if ($qoh < 0)
 228                 $qoh = 0;
 229         if ($adj_only)
 230         {
 231                 if ($qoh>0)
 232                         $material_cost = ($qoh * $material_cost + $costs) / $qoh;
 233                 else // Journal Entry if QOH is 0/negative
 234                 {
 235                         global $Refs;
 236
 237                         $id = get_next_trans_no(ST_JOURNAL);
 238                         $ref = $Refs->get_next(ST_JOURNAL, null, $date_);
 239                         add_journal(ST_JOURNAL, $id, $costs, $date_, get_company_currency(), $ref);
 240
 241                         $stock_gl_code = get_stock_gl_code($stock_id);
 242                         $memo = "WO Issue settlement JV for zero/negative respository of ".$stock_id;
 243                         //Reverse the inventory effect if $qoh <=0
 244                         add_gl_trans_std_cost(ST_JOURNAL, $id, $date_,
 245                                 $stock_gl_code["inventory_account"],
 246                                 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo,
 247                                 -$costs);
 248                         //GL Posting to inventory adjustment account
 249                         add_gl_trans_std_cost(ST_JOURNAL, $id, $date_,
 250                                 $stock_gl_code["assembly_account"],
 251                                 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo,
 252                                 $costs);
 253
 254                         add_audit_trail(ST_JOURNAL, $id, $date_);
 255                         add_comments(ST_JOURNAL, $id, $date_, $memo);
 256                         $Refs->save(ST_JOURNAL, $id, $ref);
 257                         if ($qty != 0)
 258                                 $material_cost = $costs / $qty;
 259                 }
 260         }
 261         else
 262         {
 263                 if ($qoh + $qty != 0)
 264                         $material_cost = ($qoh * $material_cost + $qty * $costs) /      ($qoh + $qty);
 265         }
 266         $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=material_cost+"
 267                 .db_escape($material_cost)
 268                 ." WHERE stock_id=".db_escape($stock_id);
 269         db_query($sql,"The cost details for the inventory item could not be updated");
 270 }
 271
 272 /*
 273         Create journal entry for WO related costs.
 274 */
 275 function add_wo_costs_journal($wo_id, $amount, $cost_type, $cr_acc, $db_acc, $date, $dim1=0, $dim2=0, $memo = null, $ref= null)
 276 {
 277 //-------- this should be done by single call to write_journal_entries() using items_cart()
 278 //
 279         global $Refs, $wo_cost_types;
 280
 281         begin_transaction();
 282
 283     $journal_id = get_next_trans_no(ST_JOURNAL);
 284
 285     if (!$ref) $ref = $Refs->get_next(ST_JOURNAL, null, $date);
 286
 287         add_gl_trans_std_cost(ST_JOURNAL, $journal_id, $date, $cr_acc,
 288                 0, 0, $wo_cost_types[$cost_type], -$amount);
 289         $is_bank_to = is_bank_account($cr_acc);
 290         if ($is_bank_to)
 291         {
 292                 add_bank_trans(ST_JOURNAL, $journal_id, $is_bank_to, "",
 293                         $date, -$amount, PT_WORKORDER, $wo_id, get_company_currency(),
 294                         "Cannot insert a destination bank transaction");
 295         }
 296         add_journal(ST_JOURNAL, $journal_id, $amount, $date, get_company_currency(), $ref);
 297
 298         add_gl_trans_std_cost(ST_JOURNAL, $journal_id, $date, $db_acc,
 299                 $dim1, $dim2,  $wo_cost_types[$cost_type], $amount);
 300
 301         $Refs->save(ST_JOURNAL, $journal_id, $ref);
 302
 303         add_wo_costing($wo_id, $cost_type, ST_JOURNAL, $journal_id);
 304
 305         add_comments(ST_JOURNAL, $journal_id, $date, $memo);
 306         add_audit_trail(ST_JOURNAL, $journal_id, $date);
 307
 308         commit_transaction();
 309 }
 310
 311 function void_wo_costing($wo_id)
 312 {
 313         $res = get_wo_costing($wo_id);
 314
 315         while($row = db_fetch($res))
 316         {
 317                 // void any related gl trans
 318                 void_gl_trans($row['trans_type'], $row['trans_no'], true);
 319         }
 320
 321         $sql = "DELETE FROM ".TB_PREF."wo_costing WHERE workorder_id=".db_escape($wo_id);
 322         db_query($sql, "could not delete work order costing");
 323 }