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, $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 function reopen_work_order($woid)
 240 {
 241                 // FIXME: update_material_cost
 242 //              update_material_cost($work_order['stock_id'], -$work_order['units_issued'], ... );
 243
 244                 void_gl_trans(ST_WORKORDER, $woid);
 245
 246                 void_stock_move(ST_WORKORDER, $woid);
 247
 248                 $sql = "UPDATE ".TB_PREF."workorders SET closed=0 WHERE id = "
 249                         .db_escape($woid);
 250                 db_query($sql, "The work order couldn't be reopened");
 251 }
 252
 253 //--------------------------------------------------------------------------------------
 254 /*
 255         Void all work order related operations
 256 */
 257 function void_work_order($woid)
 258 {
 259         begin_transaction();
 260         hook_db_prevoid(ST_WORKORDER, $woid);
 261
 262         $work_order = get_work_order($woid);
 263
 264         if ($work_order['closed'])
 265                 reopen_work_order($woid);
 266
 267         if ($work_order['units_issued'])
 268         {
 269                 $prods = get_work_order_productions($woid);
 270                 while ($prod = db_fetch($prods))
 271                         void_work_order_produce($prod['id']);
 272         }
 273
 274         // close workorder
 275         $sql = "UPDATE ".TB_PREF."workorders SET closed=1,units_reqd=0,units_issued=0 WHERE id = "
 276                 .db_escape($woid);
 277         db_query($sql, "The work order couldn't be voided");
 278
 279         // void addtional material issues
 280         $issues = get_work_order_issues($woid);
 281         while($issue = db_fetch($issues))
 282                 void_work_order_issue($issue['issue_no']);
 283
 284         // void all related stock moves
 285         void_stock_move(ST_WORKORDER, $woid);
 286
 287         // void any related costing records
 288         void_wo_costing($woid);
 289
 290         // clear the requirements units received
 291         void_wo_requirements($woid);
 292
 293         commit_transaction();
 294 }
 295
 296 function get_sql_for_work_orders($outstanding_only, $stock_id, $location = ALL_TEXT, $order_no='', $order_ref = '', $overdue = false)
 297 {
 298         $sql = "SELECT
 299                 workorder.id,
 300                 workorder.wo_ref,
 301                 workorder.type,
 302                 location.location_name,
 303                 item.description,
 304                 workorder.units_reqd,
 305                 workorder.units_issued,
 306                 workorder.date_,
 307                 workorder.required_by,
 308                 workorder.released_date,
 309                 workorder.closed,
 310                 workorder.released,
 311                 workorder.stock_id,
 312                 unit.decimals
 313                 FROM ".TB_PREF."workorders as workorder,"
 314                         .TB_PREF."stock_master as item,"
 315                         .TB_PREF."item_units as unit,"
 316                         .TB_PREF."locations as location
 317                 WHERE workorder.stock_id=item.stock_id
 318                         AND workorder.loc_code=location.loc_code
 319                         AND item.units=unit.abbr";
 320
 321         if (check_value('OpenOnly') || $outstanding_only != 0)
 322         {
 323                 $sql .= " AND workorder.closed=0";
 324         }
 325
 326         if ($location != ALL_TEXT)
 327         {
 328                 $sql .= " AND workorder.loc_code=".db_escape($location);
 329         }
 330
 331         if ($order_no != '')
 332         {
 333                 $sql .= " AND workorder.id LIKE ".db_escape('%'.$order_no.'%');
 334         }
 335
 336         if ($order_ref != '')
 337         {
 338                 $sql .= " AND workorder.wo_ref LIKE ".db_escape('%'.$order_ref.'%');
 339         }
 340
 341         if ($stock_id != ALL_TEXT)
 342         {
 343                 $sql .= " AND workorder.stock_id=".db_escape($stock_id);
 344         }
 345
 346         if ($overdue)
 347         {
 348                 $Today = date2sql(Today());
 349
 350                 $sql .= " AND workorder.required_by < '$Today' ";
 351         }
 352         $sql .= " ORDER BY workorder.id DESC";
 353         return $sql;
 354 }
 355
 356 function get_sql_for_where_used($stock_id)
 357 {
 358         $sql = "SELECT
 359                         bom.parent,
 360                         workcentre.name As WorkCentreName,
 361                         location.location_name,
 362                         bom.quantity,
 363                         parent.description
 364                         FROM ".TB_PREF."bom as bom, "
 365                                 .TB_PREF."stock_master as parent, "
 366                                 .TB_PREF."workcentres as workcentre, "
 367                                 .TB_PREF."locations as location
 368                         WHERE bom.parent = parent.stock_id
 369                                 AND bom.workcentre_added = workcentre.id
 370                                 AND bom.loc_code = location.loc_code
 371                                 AND bom.component=".db_escape($stock_id);
 372         return $sql;
 373 }
 374 //--------------------------------------------------------------------------------------
 375 function get_gl_wo_cost($woid, $cost_type)
 376 {
 377         $cost = 0;
 378         $result = get_gl_wo_cost_trans($woid, $cost_type);
 379         while ($row = db_fetch($result))
 380                 $cost += -$row['amount'];
 381         return $cost;
 382 }
 383