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