manufacturing/includes/db/work_orders_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         Common entry procedure for all work order types
  14 */
  15 function add_work_order($wo_ref, $loc_code, $units_reqd, $stock_id,
  16         $type, $date_, $required_by, $memo_, $costs, $cr_acc, $labour, $cr_lab_acc)
  17 {
  18         global $Refs;
  19
  20         begin_transaction();
  21         $args = func_get_args();
  22         $args = (object)array_combine(array('wo_ref', 'loc_code', 'units_reqd', 'stock_id',
  23                 'type', 'date_', 'required_by', 'memo_', 'costs', 'cr_acc', 'labour', 'cr_lab_acc'), $args);
  24
  25         $args->woid = 0;
  26         hook_db_prewrite($args, ST_WORKORDER);
  27
  28         if ($type != WO_ADVANCED)
  29                 $required_by = $date_;
  30
  31         $date = date2sql($date_);
  32         $required = date2sql($required_by);
  33         $sql = "INSERT INTO ".TB_PREF."workorders (wo_ref, loc_code, units_reqd, stock_id,
  34                 type, date_, required_by)
  35         VALUES (".db_escape($wo_ref).", ".db_escape($loc_code).", "
  36         .db_escape($units_reqd).", ".db_escape($stock_id).",
  37                 ".db_escape($type).", '$date', ".db_escape($required).")";
  38         db_query($sql, "could not add work order");
  39
  40         $woid = db_insert_id();
  41
  42         add_comments(ST_WORKORDER, $woid, $required_by, $memo_);
  43
  44         $Refs->save(ST_WORKORDER, $woid, $wo_ref);
  45         add_audit_trail(ST_WORKORDER, $woid, $date_);
  46
  47         $args->woid = $woid;
  48         hook_db_postwrite($args, ST_WORKORDER);
  49
  50         if ($type != WO_ADVANCED)
  51         {
  52                 $stockitem = get_item($stock_id);
  53                 release_work_order($woid, $date_, '');
  54                 if ($costs != 0)
  55                         add_wo_costs_journal($woid, $costs, WO_OVERHEAD, $cr_acc, $date_, $stockitem["dimension_id"], $stockitem["dimension2_id"]);
  56
  57                 if ($labour != 0)
  58                         add_wo_costs_journal($woid, $labour, WO_LABOUR, $cr_lab_acc, $date_, $stockitem["dimension_id"], $stockitem["dimension2_id"]);
  59
  60                 if ($type == WO_UNASSEMBLY)
  61                         $units_reqd = -$units_reqd;
  62
  63                 $ref = $Refs->get_next(ST_MANURECEIVE, null, $date_);
  64                 work_order_produce($woid, $ref, $units_reqd, $date_, '', true);
  65         }
  66
  67         commit_transaction();
  68
  69         return $woid;
  70 }
  71
  72 //--------------------------------------------------------------------------------------
  73
  74 function update_work_order($woid, $loc_code, $units_reqd, $stock_id,
  75                                         $date_, $required_by, $memo_)
  76 {
  77         begin_transaction();
  78         $args = func_get_args();
  79         $args = (object)array_combine(array('woid', 'loc_code', 'units_reqd', 'stock_id',
  80                 'date_', 'required_by', 'memo_'), $args);
  81         hook_db_prewrite($args, ST_WORKORDER);
  82
  83         $date = date2sql($date_);
  84         $required = date2sql($required_by);
  85
  86         $sql = "UPDATE ".TB_PREF."workorders SET loc_code=".db_escape($loc_code).",
  87                 units_reqd=".db_escape($units_reqd).", stock_id=".db_escape($stock_id).",
  88                 required_by=".db_escape($required).",
  89                 date_='$date'
  90                 WHERE id = ".db_escape($woid);
  91
  92         db_query($sql, "could not update work order");
  93
  94         update_comments(ST_WORKORDER, $woid, null, $memo_);
  95         add_audit_trail(ST_WORKORDER, $woid, $date_, _("Updated."));
  96
  97         hook_db_postwrite($args, ST_WORKORDER);
  98         commit_transaction();
  99 }
 100
 101 function delete_work_order($woid, $stock_id, $qty, $date)
 102 {
 103         begin_transaction();
 104         hook_db_prevoid(ST_WORKORDER, $woid);
 105
 106         // delete the work order requirements
 107         delete_wo_requirements($woid);
 108
 109         // delete the actual work order
 110         $sql = "DELETE FROM ".TB_PREF."workorders WHERE id=".db_escape($woid);
 111         db_query($sql,"The work order could not be deleted");
 112
 113         delete_comments(ST_WORKORDER, $woid);
 114         add_audit_trail(ST_WORKORDER, $woid, $date, _("Canceled."));
 115
 116         commit_transaction();
 117 }
 118
 119 //--------------------------------------------------------------------------------------
 120
 121 function get_work_order($woid, $allow_null=false)
 122 {
 123     $sql = "SELECT wo.*,st.description As StockItemName,l.location_name,
 124                 l.delivery_address,l.email, l.contact, st.inventory_account, st.wip_account
 125                 FROM ".TB_PREF."workorders wo, "
 126                         .TB_PREF."stock_master st, "
 127                         .TB_PREF."locations l
 128                 WHERE st.stock_id=wo.stock_id
 129                 AND     l.loc_code=wo.loc_code
 130                 AND wo.id=".db_escape($woid)."
 131                 GROUP BY wo.id";
 132
 133         $result = db_query($sql, "The work order issues could not be retrieved");
 134
 135         if (!$allow_null && db_num_rows($result) == 0)
 136         {
 137                 display_db_error("Could not find work order $woid", $sql);
 138                 return false;
 139         }
 140
 141         return db_fetch($result);
 142 }
 143
 144 //--------------------------------------------------------------------------------------
 145
 146 function work_order_has_productions($woid)
 147 {
 148         $sql = "SELECT COUNT(*) FROM ".TB_PREF."wo_manufacture WHERE workorder_id=".db_escape($woid);
 149         $result = db_query($sql, "query work order for productions");
 150
 151         $myrow = db_fetch_row($result);
 152         return ($myrow[0] > 0);
 153 }
 154
 155
 156 //--------------------------------------------------------------------------------------
 157
 158 function work_order_has_issues($woid)
 159 {
 160         $sql = "SELECT COUNT(*) FROM ".TB_PREF."wo_issues WHERE workorder_id=".db_escape($woid);
 161         $result = db_query($sql, "query work order for issues");
 162
 163         $myrow = db_fetch_row($result);
 164         return ($myrow[0] > 0);
 165 }
 166
 167 //--------------------------------------------------------------------------------------
 168
 169 function work_order_has_payments($woid)
 170 {
 171         $result = get_gl_wo_cost_trans($woid);
 172
 173     return (db_num_rows($result) != 0);
 174 }
 175
 176 //--------------------------------------------------------------------------------------
 177
 178 function release_work_order($woid, $releaseDate, $memo_)
 179 {
 180         begin_transaction();
 181
 182         $myrow = get_work_order($woid);
 183         $stock_id = $myrow["stock_id"];
 184
 185         $date = date2sql($releaseDate);
 186
 187         $sql = "UPDATE ".TB_PREF."workorders SET released_date='$date',
 188                 released=1 WHERE id = ".db_escape($woid);
 189         db_query($sql, "could not release work order");
 190
 191         // create wo_requirements records according to current BOM
 192         create_wo_requirements($woid, $stock_id);
 193
 194         add_comments(ST_WORKORDER, $woid, $releaseDate, $memo_);
 195         add_audit_trail(ST_WORKORDER, $woid, sql2date($myrow['date_']), _("Released."));
 196
 197         commit_transaction();
 198 }
 199
 200 //--------------------------------------------------------------------------------------
 201
 202 function close_work_order($woid)
 203 {
 204         $sql = "UPDATE ".TB_PREF."workorders SET closed=1 WHERE id = ".db_escape($woid);
 205         db_query($sql, "could not close work order");
 206 }
 207
 208 //--------------------------------------------------------------------------------------
 209
 210 function work_order_is_closed($woid)
 211 {
 212         $sql = "SELECT closed FROM ".TB_PREF."workorders WHERE id = ".db_escape($woid);
 213         $result = db_query($sql, "could not query work order");
 214         $row = db_fetch_row($result);
 215         return ($row[0] > 0);
 216 }
 217
 218 //--------------------------------------------------------------------------------------
 219 /*
 220         Update finished items quantity in work order, and close order either if all the order is produced,
 221         or on user demand. Returns calculated unit cost on close, or null otherwise.
 222 */
 223 function work_order_update_finished_quantity($woid, $quantity, $date, $force_close=0)
 224 {
 225
 226         $sql = "UPDATE ".TB_PREF."workorders SET units_issued = units_issued + ".db_escape($quantity)."
 227                 WHERE id = ".db_escape($woid). " AND !closed";
 228
 229         db_query($sql, "The work order issued quantity couldn't be updated");
 230
 231         $sql = "UPDATE ".TB_PREF."workorders SET closed = ((units_issued >= units_reqd) OR ".db_escape($force_close).")
 232                 WHERE id = ".db_escape($woid);
 233
 234         db_query($sql, "The work order couldn't be closed");
 235
 236         return db_num_affected_rows();  // returns 1 if WO has been closed
 237 }
 238
 239 //--------------------------------------------------------------------------------------
 240
 241 function void_work_order($woid)
 242 {
 243         begin_transaction();
 244         hook_db_prevoid(ST_WORKORDER, $woid);
 245
 246         $work_order = get_work_order($woid);
 247         if (!($work_order["type"] == WO_ADVANCED))
 248         {
 249                 // restore average product costs
 250                 $date = sql2date($work_order['date_']);
 251                 $qty = $work_order['units_reqd'];
 252
 253                 // FIXME: update_material_cost
 254
 255                 // close workorder
 256                 $sql = "UPDATE ".TB_PREF."workorders SET closed=1,units_reqd=0,units_issued=0 WHERE id = "
 257                         .db_escape($woid);
 258                 db_query($sql, "The work order couldn't be voided");
 259
 260                 // void all related stock moves
 261                 void_stock_move(ST_WORKORDER, $woid);
 262
 263                 // void any related costing records
 264                 void_wo_costing($woid);
 265
 266                 // clear the requirements units received
 267                 void_wo_requirements($woid);
 268         }
 269         else
 270         {
 271                 // void everything inside the work order : issues, productions, payments
 272                 $date = sql2date($work_order['date_']);
 273
 274                 // FIXME: update_material_cost
 275
 276                 $result = get_work_order_productions($woid); // check the produced quantity
 277                 $qty = 0;
 278                 while ($row = db_fetch($result))
 279                 {
 280                         $qty += $row['quantity'];
 281                         // clear the production record
 282                         $sql = "UPDATE ".TB_PREF."wo_manufacture SET quantity=0 WHERE id=".$row['id'];
 283                         db_query($sql, "Cannot void a wo production");
 284
 285                         void_stock_move(ST_MANURECEIVE, $row['id']); // and void the stock moves;
 286                 }
 287                 $result = get_additional_issues($woid); // check the issued quantities
 288                 $cost = 0;
 289                 $issue_no = 0;
 290                 while ($row = db_fetch($result))
 291                 {
 292                         $unit_cost = get_unit_cost($row['stock_id']);
 293                         $icost = $unit_cost * $row['qty_issued'];
 294                         $cost += $icost;
 295                         if ($issue_no == 0)
 296                                 $issue_no = $row['issue_no'];
 297                         // void the actual issue items and their quantities
 298                         $sql = "UPDATE ".TB_PREF."wo_issue_items SET qty_issued = 0 WHERE issue_id="
 299                                 .db_escape($row['id']);
 300                         db_query($sql,"A work order issue item could not be voided");
 301                 }
 302                 if ($issue_no != 0)
 303                         void_stock_move(ST_MANUISSUE, $issue_no); // and void the stock moves
 304
 305                 if ($cost != 0)
 306                         add_issue_cost($work_order['stock_id'], -$qty, $date, $cost);
 307
 308                 $sql = "UPDATE ".TB_PREF."workorders SET closed=1,units_reqd=0,units_issued=0 WHERE id = "
 309                         .db_escape($woid);
 310                 db_query($sql, "The work order couldn't be voided");
 311
 312                 // void all related stock moves
 313                 void_stock_move(ST_WORKORDER, $woid);
 314
 315                 // void any related bank/gl trans
 316                 void_wo_costing($woid);
 317
 318                 // clear the requirements units received
 319                 void_wo_requirements($woid);
 320         }
 321
 322         commit_transaction();
 323 }
 324
 325 function get_sql_for_work_orders($outstanding_only, $stock_id, $location = ALL_TEXT, $order_no='', $order_ref = '', $overdue = false)
 326 {
 327         $sql = "SELECT
 328                 workorder.id,
 329                 workorder.wo_ref,
 330                 workorder.type,
 331                 location.location_name,
 332                 item.description,
 333                 workorder.units_reqd,
 334                 workorder.units_issued,
 335                 workorder.date_,
 336                 workorder.required_by,
 337                 workorder.released_date,
 338                 workorder.closed,
 339                 workorder.released,
 340                 workorder.stock_id,
 341                 unit.decimals
 342                 FROM ".TB_PREF."workorders as workorder,"
 343                         .TB_PREF."stock_master as item,"
 344                         .TB_PREF."item_units as unit,"
 345                         .TB_PREF."locations as location
 346                 WHERE workorder.stock_id=item.stock_id
 347                         AND workorder.loc_code=location.loc_code
 348                         AND item.units=unit.abbr";
 349
 350         if (check_value('OpenOnly') || $outstanding_only != 0)
 351         {
 352                 $sql .= " AND workorder.closed=0";
 353         }
 354
 355         if ($location != ALL_TEXT)
 356         {
 357                 $sql .= " AND workorder.loc_code=".db_escape($location);
 358         }
 359
 360         if ($order_no != '')
 361         {
 362                 $sql .= " AND workorder.id LIKE ".db_escape('%'.$order_no.'%');
 363         }
 364
 365         if ($order_ref != '')
 366         {
 367                 $sql .= " AND workorder.wo_ref LIKE ".db_escape('%'.$order_ref.'%');
 368         }
 369
 370         if ($stock_id != ALL_TEXT)
 371         {
 372                 $sql .= " AND workorder.stock_id=".db_escape($stock_id);
 373         }
 374
 375         if ($overdue)
 376         {
 377                 $Today = date2sql(Today());
 378
 379                 $sql .= " AND workorder.required_by < '$Today' ";
 380         }
 381         $sql .= " ORDER BY workorder.id DESC";
 382         return $sql;
 383 }
 384
 385 function get_sql_for_where_used($stock_id)
 386 {
 387         $sql = "SELECT
 388                         bom.parent,
 389                         workcentre.name As WorkCentreName,
 390                         location.location_name,
 391                         bom.quantity,
 392                         parent.description
 393                         FROM ".TB_PREF."bom as bom, "
 394                                 .TB_PREF."stock_master as parent, "
 395                                 .TB_PREF."workcentres as workcentre, "
 396                                 .TB_PREF."locations as location
 397                         WHERE bom.parent = parent.stock_id
 398                                 AND bom.workcentre_added = workcentre.id
 399                                 AND bom.loc_code = location.loc_code
 400                                 AND bom.component=".db_escape($stock_id);
 401         return $sql;
 402 }
 403 //--------------------------------------------------------------------------------------
 404 function get_gl_wo_cost($woid, $cost_type)
 405 {
 406         $cost = 0;
 407         $result = get_gl_wo_cost_trans($woid, $cost_type);
 408         while ($row = db_fetch($result))
 409                 $cost += -$row['amount'];
 410         return $cost;
 411 }
 412