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