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