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