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