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(__FUNCTION__, func_get_args());
  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(__FUNCTION__, func_get_args());
  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(__FUNCTION__, func_get_args());
 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                         LEFT JOIN ".TB_PREF."voided v ON v.id=wo.id and v.type=".ST_WORKORDER.","
 127                         .TB_PREF."stock_master st, "
 128                         .TB_PREF."locations l
 129                 WHERE ISNULL(v.id)
 130                 AND st.stock_id=wo.stock_id
 131                 AND     l.loc_code=wo.loc_code
 132                 AND wo.id=".db_escape($woid)."
 133                 GROUP BY wo.id";
 134
 135         $result = db_query($sql, "The work order could not be retrieved");
 136
 137         if (!$allow_null && db_num_rows($result) == 0)
 138         {
 139                 display_db_error("Could not find work order ".(int)$woid, $sql);
 140                 return false;
 141         }
 142
 143         return db_fetch($result);
 144 }
 145
 146 //--------------------------------------------------------------------------------------
 147
 148 function work_order_has_productions($woid)
 149 {
 150         $sql = "SELECT COUNT(*) FROM ".TB_PREF."wo_manufacture WHERE workorder_id=".db_escape($woid);
 151         $result = db_query($sql, "query work order for productions");
 152
 153         $myrow = db_fetch_row($result);
 154         return ($myrow[0] > 0);
 155 }
 156
 157
 158 //--------------------------------------------------------------------------------------
 159
 160 function work_order_has_issues($woid)
 161 {
 162         $sql = "SELECT COUNT(*) FROM ".TB_PREF."wo_issues WHERE workorder_id=".db_escape($woid);
 163         $result = db_query($sql, "query work order for issues");
 164
 165         $myrow = db_fetch_row($result);
 166         return ($myrow[0] > 0);
 167 }
 168
 169 //--------------------------------------------------------------------------------------
 170
 171 function work_order_has_payments($woid)
 172 {
 173         $result = get_gl_wo_cost_trans($woid);
 174
 175     return (db_num_rows($result) != 0);
 176 }
 177
 178 //--------------------------------------------------------------------------------------
 179
 180 function release_work_order($woid, $releaseDate, $memo_)
 181 {
 182         begin_transaction(__FUNCTION__, func_get_args());
 183
 184         $myrow = get_work_order($woid);
 185         $stock_id = $myrow["stock_id"];
 186
 187         $date = date2sql($releaseDate);
 188
 189         $sql = "UPDATE ".TB_PREF."workorders SET released_date='$date',
 190                 released=1 WHERE id = ".db_escape($woid);
 191         db_query($sql, "could not release work order");
 192
 193         // create wo_requirements records according to current BOM
 194         create_wo_requirements($woid, $stock_id);
 195
 196         add_comments(ST_WORKORDER, $woid, $releaseDate, $memo_);
 197         add_audit_trail(ST_WORKORDER, $woid, sql2date($myrow['date_']), _("Released."));
 198
 199         commit_transaction();
 200 }
 201
 202 //--------------------------------------------------------------------------------------
 203
 204 function close_work_order($woid)
 205 {
 206         $sql = "UPDATE ".TB_PREF."workorders SET closed=1 WHERE id = ".db_escape($woid);
 207         db_query($sql, "could not close work order");
 208 }
 209
 210 //--------------------------------------------------------------------------------------
 211
 212 function work_order_is_closed($woid)
 213 {
 214         $sql = "SELECT closed FROM ".TB_PREF."workorders WHERE id = ".db_escape($woid);
 215         $result = db_query($sql, "could not query work order");
 216         $row = db_fetch_row($result);
 217         return ($row[0] > 0);
 218 }
 219
 220 //--------------------------------------------------------------------------------------
 221 /*
 222         Update finished items quantity in work order, and close order either if all the order is produced,
 223         or on user demand. Returns calculated unit cost on close, or null otherwise.
 224 */
 225 function work_order_update_finished_quantity($woid, $quantity, $force_close=0)
 226 {
 227
 228         $sql = "UPDATE ".TB_PREF."workorders SET units_issued = units_issued + ".db_escape($quantity)."
 229                 WHERE id = ".db_escape($woid). " AND !closed";
 230
 231         db_query($sql, "The work order issued quantity couldn't be updated");
 232
 233         $sql = "UPDATE ".TB_PREF."workorders SET closed = ((units_issued >= units_reqd) OR ".db_escape($force_close).")
 234                 WHERE id = ".db_escape($woid);
 235
 236         db_query($sql, "The work order couldn't be closed");
 237
 238         return db_num_affected_rows();  // returns 1 if WO has been closed
 239 }
 240
 241 function reopen_work_order($woid)
 242 {
 243                 // FIXME: update_material_cost
 244 //              update_material_cost($work_order['stock_id'], -$work_order['units_issued'], ... );
 245
 246                 void_gl_trans(ST_WORKORDER, $woid);
 247
 248                 void_stock_move(ST_WORKORDER, $woid);
 249
 250                 $sql = "UPDATE ".TB_PREF."workorders SET closed=0 WHERE id = "
 251                         .db_escape($woid);
 252
 253                 db_query($sql, "The work order couldn't be reopened");
 254 }
 255
 256 //--------------------------------------------------------------------------------------
 257 /*
 258         Void all work order related operations
 259 */
 260 function void_work_order($woid)
 261 {
 262         begin_transaction(__FUNCTION__, func_get_args());
 263         hook_db_prevoid(ST_WORKORDER, $woid);
 264
 265         $work_order = get_work_order($woid);
 266
 267         if ($work_order['closed'])
 268                 reopen_work_order($woid);
 269
 270         if ($work_order['units_issued'])
 271         {
 272                 $prods = get_work_order_productions($woid);
 273                 while ($prod = db_fetch($prods))
 274                         void_work_order_produce($prod['id']);
 275         }
 276
 277         // void addtional material issues
 278         $issues = get_work_order_issues($woid);
 279         while($issue = db_fetch($issues))
 280                 void_work_order_issue($issue['issue_no']);
 281
 282         // void all related stock moves
 283         void_stock_move(ST_WORKORDER, $woid);
 284
 285         // void any related costing records
 286         void_wo_costing($woid);
 287
 288         // clear the requirements units received
 289         void_wo_requirements($woid);
 290
 291         // close workorder
 292         $sql = "UPDATE ".TB_PREF."workorders SET closed=1,units_reqd=0,units_issued=0 WHERE id = "
 293                 .db_escape($woid);
 294         db_query($sql, "The work order couldn't be voided");
 295
 296         commit_transaction();
 297 }
 298
 299 function get_sql_for_work_orders($outstanding_only, $stock_id, $location = ALL_TEXT, $order_no='', $order_ref = '', $overdue = false)
 300 {
 301         $sql = "SELECT
 302                 workorder.id,
 303                 workorder.wo_ref,
 304                 workorder.type,
 305                 location.location_name,
 306                 item.description,
 307                 workorder.units_reqd,
 308                 workorder.units_issued,
 309                 workorder.date_,
 310                 workorder.required_by,
 311                 workorder.released_date,
 312                 workorder.closed,
 313                 workorder.released,
 314                 workorder.stock_id,
 315                 unit.decimals
 316                 FROM ".TB_PREF."workorders as workorder
 317                         LEFT JOIN ".TB_PREF."voided v ON v.id=workorder.id and v.type=".ST_WORKORDER.","
 318                         .TB_PREF."stock_master as item,"
 319                         .TB_PREF."item_units as unit,"
 320                         .TB_PREF."locations as location
 321                 WHERE ISNULL(v.id)
 322                         AND workorder.stock_id=item.stock_id
 323                         AND workorder.loc_code=location.loc_code
 324                         AND item.units=unit.abbr";
 325
 326         if (check_value('OpenOnly') || $outstanding_only != 0)
 327         {
 328                 $sql .= " AND workorder.closed=0";
 329         }
 330
 331         if ($location != ALL_TEXT)
 332         {
 333                 $sql .= " AND workorder.loc_code=".db_escape($location);
 334         }
 335
 336         if ($order_no != '')
 337         {
 338                 $sql .= " AND workorder.id LIKE ".db_escape('%'.$order_no.'%');
 339         }
 340
 341         if ($order_ref != '')
 342         {
 343                 $sql .= " AND workorder.wo_ref LIKE ".db_escape('%'.$order_ref.'%');
 344         }
 345
 346         if ($stock_id != ALL_TEXT)
 347         {
 348                 $sql .= " AND workorder.stock_id=".db_escape($stock_id);
 349         }
 350
 351         if ($overdue)
 352         {
 353                 $Today = date2sql(Today());
 354
 355                 $sql .= " AND workorder.required_by < '$Today' ";
 356         }
 357         $sql .= " ORDER BY workorder.id DESC";
 358         return $sql;
 359 }
 360
 361 function get_sql_for_where_used($stock_id)
 362 {
 363         $sql = "SELECT
 364                         bom.parent,
 365                         workcentre.name As WorkCentreName,
 366                         location.location_name,
 367                         bom.quantity,
 368                         parent.description
 369                         FROM ".TB_PREF."bom as bom, "
 370                                 .TB_PREF."stock_master as parent, "
 371                                 .TB_PREF."workcentres as workcentre, "
 372                                 .TB_PREF."locations as location
 373                         WHERE bom.parent = parent.stock_id
 374                                 AND bom.workcentre_added = workcentre.id
 375                                 AND bom.loc_code = location.loc_code
 376                                 AND bom.component=".db_escape($stock_id);
 377         return $sql;
 378 }
 379 //--------------------------------------------------------------------------------------
 380 function get_gl_wo_cost($woid, $cost_type)
 381 {
 382         $cost = 0;
 383         $result = get_gl_wo_cost_trans($woid, $cost_type);
 384         while ($row = db_fetch($result))
 385                 $cost += -$row['amount'];
 386         return $cost;
 387 }
 388