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                         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();
 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();
 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         // FIXME: due to bug in 2.4 db scheme (conflicting wo_ref unique key) we have to fix wo_ref here:
 293         $sql = "UPDATE ".TB_PREF."workorders SET closed=1,units_reqd=0,units_issued=0, wo_ref=CONCAT(wo_ref, ' ', '".uniqid()."') WHERE id = "
 294                 .db_escape($woid);
 295         db_query($sql, "The work order couldn't be voided");
 296
 297         commit_transaction();
 298 }
 299
 300 function get_sql_for_work_orders($outstanding_only, $stock_id, $location = ALL_TEXT, $order_no='', $order_ref = '', $overdue = false)
 301 {
 302         $sql = "SELECT
 303                 workorder.id,
 304                 workorder.wo_ref,
 305                 workorder.type,
 306                 location.location_name,
 307                 item.description,
 308                 workorder.units_reqd,
 309                 workorder.units_issued,
 310                 workorder.date_,
 311                 workorder.required_by,
 312                 workorder.released_date,
 313                 workorder.closed,
 314                 workorder.released,
 315                 workorder.stock_id,
 316                 unit.decimals
 317                 FROM ".TB_PREF."workorders as workorder
 318                         LEFT JOIN ".TB_PREF."voided v ON v.id=workorder.id and v.type=".ST_WORKORDER.","
 319                         .TB_PREF."stock_master as item,"
 320                         .TB_PREF."item_units as unit,"
 321                         .TB_PREF."locations as location
 322                 WHERE ISNULL(v.id)
 323                         AND workorder.stock_id=item.stock_id
 324                         AND workorder.loc_code=location.loc_code
 325                         AND item.units=unit.abbr";
 326
 327         if (check_value('OpenOnly') || $outstanding_only != 0)
 328         {
 329                 $sql .= " AND workorder.closed=0";
 330         }
 331
 332         if ($location != ALL_TEXT)
 333         {
 334                 $sql .= " AND workorder.loc_code=".db_escape($location);
 335         }
 336
 337         if ($order_no != '')
 338         {
 339                 $sql .= " AND workorder.id LIKE ".db_escape('%'.$order_no.'%');
 340         }
 341
 342         if ($order_ref != '')
 343         {
 344                 $sql .= " AND workorder.wo_ref LIKE ".db_escape('%'.$order_ref.'%');
 345         }
 346
 347         if ($stock_id != ALL_TEXT)
 348         {
 349                 $sql .= " AND workorder.stock_id=".db_escape($stock_id);
 350         }
 351
 352         if ($overdue)
 353         {
 354                 $Today = date2sql(Today());
 355
 356                 $sql .= " AND workorder.required_by < '$Today' ";
 357         }
 358         $sql .= " ORDER BY workorder.id DESC";
 359         return $sql;
 360 }
 361
 362 function get_sql_for_where_used($stock_id)
 363 {
 364         $sql = "SELECT
 365                         bom.parent,
 366                         workcentre.name As WorkCentreName,
 367                         location.location_name,
 368                         bom.quantity,
 369                         parent.description
 370                         FROM ".TB_PREF."bom as bom, "
 371                                 .TB_PREF."stock_master as parent, "
 372                                 .TB_PREF."workcentres as workcentre, "
 373                                 .TB_PREF."locations as location
 374                         WHERE bom.parent = parent.stock_id
 375                                 AND bom.workcentre_added = workcentre.id
 376                                 AND bom.loc_code = location.loc_code
 377                                 AND bom.component=".db_escape($stock_id);
 378         return $sql;
 379 }
 380 //--------------------------------------------------------------------------------------
 381 function get_gl_wo_cost($woid, $cost_type)
 382 {
 383         $cost = 0;
 384         $result = get_gl_wo_cost_trans($woid, $cost_type);
 385         while ($row = db_fetch($result))
 386                 $cost += -$row['amount'];
 387         return $cost;
 388 }
 389