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