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