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 function add_work_order($wo_ref, $loc_code, $units_reqd, $stock_id,
  13         $type, $date_, $required_by, $memo_, $costs, $cr_acc, $labour, $cr_lab_acc)
  14 {
  15         global $Refs;
  16
  17         if (!($type == WO_ADVANCED))
  18                 return add_work_order_quick($wo_ref, $loc_code, $units_reqd, $stock_id, $type, $date_, $memo_, $costs, $cr_acc, $labour, $cr_lab_acc);
  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         $args->woid = 0;
  25         hook_db_prewrite($args, ST_WORKORDER);
  26
  27         add_material_cost($stock_id, $units_reqd, $date_);
  28
  29         $date = date2sql($date_);
  30         $required = date2sql($required_by);
  31
  32         $sql = "INSERT INTO ".TB_PREF."workorders (wo_ref, loc_code, units_reqd, stock_id,
  33                 type, date_, required_by)
  34         VALUES (".db_escape($wo_ref).", ".db_escape($loc_code).", "
  35         .db_escape($units_reqd).", ".db_escape($stock_id).",
  36                 ".db_escape($type).", '$date', ".db_escape($required).")";
  37         db_query($sql, "could not add work order");
  38
  39         $woid = db_insert_id();
  40
  41         add_comments(ST_WORKORDER, $woid, $required_by, $memo_);
  42
  43         $Refs->save(ST_WORKORDER, $woid, $wo_ref);
  44         add_audit_trail(ST_WORKORDER, $woid, $date_);
  45
  46         $args->woid = $woid;
  47         hook_db_postwrite($args, ST_WORKORDER);
  48         commit_transaction();
  49
  50         return $woid;
  51 }
  52
  53 //--------------------------------------------------------------------------------------
  54
  55 function update_work_order($woid, $loc_code, $units_reqd, $stock_id,
  56                                         $date_, $required_by, $memo_)
  57 {
  58         begin_transaction();
  59         $args = func_get_args();
  60         $args = (object)array_combine(array('woid', 'loc_code', 'units_reqd', 'stock_id',
  61                 'date_', 'required_by', 'memo_'), $args);
  62         hook_db_prewrite($args, ST_WORKORDER);
  63
  64         add_material_cost($_POST['old_stk_id'], -$_POST['old_qty'], $date_);
  65         add_material_cost($stock_id, $units_reqd, $date_);
  66
  67         $date = date2sql($date_);
  68         $required = date2sql($required_by);
  69
  70         $sql = "UPDATE ".TB_PREF."workorders SET loc_code=".db_escape($loc_code).",
  71                 units_reqd=".db_escape($units_reqd).", stock_id=".db_escape($stock_id).",
  72                 required_by=".db_escape($required).",
  73                 date_='$date'
  74                 WHERE id = ".db_escape($woid);
  75
  76         db_query($sql, "could not update work order");
  77
  78         update_comments(ST_WORKORDER, $woid, null, $memo_);
  79         add_audit_trail(ST_WORKORDER, $woid, $date_, _("Updated."));
  80
  81         hook_db_postwrite($args, ST_WORKORDER);
  82         commit_transaction();
  83 }
  84
  85 function delete_work_order($woid)
  86 {
  87         begin_transaction();
  88         hook_db_prevoid(ST_WORKORDER, $woid);
  89
  90         add_material_cost($_POST['stock_id'], -$_POST['quantity'], $_POST['date_']);
  91
  92         // delete the work order requirements
  93         delete_wo_requirements($woid);
  94
  95         // delete the actual work order
  96         $sql = "DELETE FROM ".TB_PREF."workorders WHERE id=".db_escape($woid);
  97         db_query($sql,"The work order could not be deleted");
  98
  99         delete_comments(ST_WORKORDER, $woid);
 100         add_audit_trail(ST_WORKORDER, $woid, $_POST['date_'], _("Canceled."));
 101
 102         commit_transaction();
 103 }
 104
 105 //--------------------------------------------------------------------------------------
 106
 107 function get_work_order($woid, $allow_null=false)
 108 {
 109     $sql = "SELECT wo.*,st.description As StockItemName,l.location_name,
 110                 l.delivery_address,l.email, l.contact
 111                 FROM ".TB_PREF."workorders wo, ".TB_PREF."stock_master st, ".TB_PREF."locations l
 112                 WHERE st.stock_id=wo.stock_id
 113                 AND     l.loc_code=wo.loc_code
 114                 AND wo.id=".db_escape($woid)."
 115                 GROUP BY wo.id";
 116
 117         $result = db_query($sql, "The work order issues could not be retrieved");
 118
 119         if (!$allow_null && db_num_rows($result) == 0)
 120                 display_db_error("Could not find work order $woid", $sql);
 121
 122         return db_fetch($result);
 123 }
 124
 125 //--------------------------------------------------------------------------------------
 126
 127 function work_order_has_productions($woid)
 128 {
 129         $sql = "SELECT COUNT(*) FROM ".TB_PREF."wo_manufacture WHERE workorder_id=".db_escape($woid);
 130         $result = db_query($sql, "query work order for productions");
 131
 132         $myrow = db_fetch_row($result);
 133         return ($myrow[0] > 0);
 134 }
 135
 136
 137 //--------------------------------------------------------------------------------------
 138
 139 function work_order_has_issues($woid)
 140 {
 141         $sql = "SELECT COUNT(*) FROM ".TB_PREF."wo_issues WHERE workorder_id=".db_escape($woid);
 142         $result = db_query($sql, "query work order for issues");
 143
 144         $myrow = db_fetch_row($result);
 145         return ($myrow[0] > 0);
 146 }
 147
 148 //--------------------------------------------------------------------------------------
 149
 150 function work_order_has_payments($woid)
 151 {
 152         $result = get_gl_wo_cost_trans($woid);
 153
 154     return (db_num_rows($result) != 0);
 155 }
 156
 157 //--------------------------------------------------------------------------------------
 158
 159 function release_work_order($woid, $releaseDate, $memo_)
 160 {
 161         begin_transaction();
 162
 163         $myrow = get_work_order($woid);
 164         $stock_id = $myrow["stock_id"];
 165
 166         $date = date2sql($releaseDate);
 167
 168         $sql = "UPDATE ".TB_PREF."workorders SET released_date='$date',
 169                 released=1 WHERE id = ".db_escape($woid);
 170         db_query($sql, "could not release work order");
 171
 172         // create Work Order Requirements based on the bom
 173         create_wo_requirements($woid, $stock_id);
 174
 175         add_comments(ST_WORKORDER, $woid, $releaseDate, $memo_);
 176         add_audit_trail(ST_WORKORDER, $woid, sql2date($myrow['date_']), _("Released."));
 177
 178         commit_transaction();
 179 }
 180
 181 //--------------------------------------------------------------------------------------
 182
 183 function close_work_order($woid)
 184 {
 185         $sql = "UPDATE ".TB_PREF."workorders SET closed=1 WHERE id = ".db_escape($woid);
 186         db_query($sql, "could not close work order");
 187 }
 188
 189 //--------------------------------------------------------------------------------------
 190
 191 function work_order_is_closed($woid)
 192 {
 193         $sql = "SELECT closed FROM ".TB_PREF."workorders WHERE id = ".db_escape($woid);
 194         $result = db_query($sql, "could not query work order");
 195         $row = db_fetch_row($result);
 196         return ($row[0] > 0);
 197 }
 198
 199 //--------------------------------------------------------------------------------------
 200
 201 function work_order_update_finished_quantity($woid, $quantity, $force_close=0)
 202 {
 203         $sql = "UPDATE ".TB_PREF."workorders SET units_issued = units_issued + ".db_escape($quantity).",
 204                 closed = ((units_issued >= units_reqd) OR ".db_escape($force_close).")
 205                 WHERE id = ".db_escape($woid);
 206
 207         db_query($sql, "The work order issued quantity couldn't be updated");
 208 }
 209
 210 //--------------------------------------------------------------------------------------
 211
 212 function void_work_order($woid)
 213 {
 214         begin_transaction();
 215         hook_db_prevoid(ST_WORKORDER, $woid);
 216
 217         $work_order = get_work_order($woid);
 218         if (!($work_order["type"] == WO_ADVANCED))
 219         {
 220                 $date = sql2date($work_order['date_']);
 221                 $qty = $work_order['units_reqd'];
 222                 add_material_cost($work_order['stock_id'], -$qty, $date); // remove avg. cost for qty
 223                 $cost = get_gl_wo_cost($woid, WO_LABOUR); // get the labour cost and reduce avg cost
 224                 if ($cost != 0)
 225                         add_labour_cost($work_order['stock_id'], -$qty, $date, $cost);
 226                 $cost = get_gl_wo_cost($woid, WO_OVERHEAD); // get the overhead cost and reduce avg cost
 227                 if ($cost != 0)
 228                         add_overhead_cost($work_order['stock_id'], -$qty, $date, $cost);
 229
 230                 $sql = "UPDATE ".TB_PREF."workorders SET closed=1,units_reqd=0,units_issued=0 WHERE id = "
 231                         .db_escape($woid);
 232                 db_query($sql, "The work order couldn't be voided");
 233
 234                 // void all related stock moves
 235                 void_stock_move(ST_WORKORDER, $woid);
 236
 237                 void_wo_costing($woid);
 238
 239                 // clear the requirements units received
 240                 void_wo_requirements($woid);
 241         }
 242         else
 243         {
 244                 // void everything inside the work order : issues, productions, payments
 245                 $date = sql2date($work_order['date_']);
 246                 add_material_cost($work_order['stock_id'], -$work_order['units_reqd'], $date); // remove avg. cost for qty
 247                 $result = get_work_order_productions($woid); // check the produced quantity
 248                 $qty = 0;
 249                 while ($row = db_fetch($result))
 250                 {
 251                         $qty += $row['quantity'];
 252                         // clear the production record
 253                         $sql = "UPDATE ".TB_PREF."wo_manufacture SET quantity=0 WHERE id=".$$row['id'];
 254                         db_query($sql, "Cannot void a wo production");
 255
 256                         void_stock_move(ST_MANURECEIVE, $row['id']); // and void the stock moves;
 257                 }
 258                 $result = get_additional_issues($woid); // check the issued quantities
 259                 $cost = 0;
 260                 $issue_no = 0;
 261                 while ($row = db_fetch($result))
 262                 {
 263                         $std_cost = get_standard_cost($row['stock_id']);
 264                         $icost = $std_cost * $row['qty_issued'];
 265                         $cost += $icost;
 266                         if ($issue_no == 0)
 267                                 $issue_no = $row['issue_no'];
 268                         // void the actual issue items and their quantities
 269                         $sql = "UPDATE ".TB_PREF."wo_issue_items SET qty_issued = 0 WHERE issue_id="
 270                                 .db_escape($row['id']);
 271                         db_query($sql,"A work order issue item could not be voided");
 272                 }
 273                 if ($issue_no != 0)
 274                         void_stock_move(ST_MANUISSUE, $issue_no); // and void the stock moves
 275                 if ($cost != 0)
 276                         add_issue_cost($work_order['stock_id'], -$qty, $date, $cost);
 277
 278                 $cost = get_gl_wo_cost($woid, WO_LABOUR); // get the labour cost and reduce avg cost
 279                 if ($cost != 0)
 280                         add_labour_cost($work_order['stock_id'], -$qty, $date, $cost);
 281                 $cost = get_gl_wo_cost($woid, WO_OVERHEAD); // get the overhead cost and reduce avg cost
 282                 if ($cost != 0)
 283                         add_overhead_cost($work_order['stock_id'], -$qty, $date, $cost);
 284
 285                 $sql = "UPDATE ".TB_PREF."workorders SET closed=1,units_reqd=0,units_issued=0 WHERE id = "
 286                         .db_escape($woid);
 287                 db_query($sql, "The work order couldn't be voided");
 288
 289                 // void all related stock moves
 290                 void_stock_move(ST_WORKORDER, $woid);
 291
 292                 void_wo_costing($wo);
 293
 294                 // clear the requirements units received
 295                 void_wo_requirements($woid);
 296         }
 297         commit_transaction();
 298 }
 299
 300 function get_sql_for_work_orders($outstanding_only, $all_items)
 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                         .TB_PREF."stock_master as item,"
 319                         .TB_PREF."item_units as unit,"
 320                         .TB_PREF."locations as location
 321                 WHERE workorder.stock_id=item.stock_id
 322                         AND workorder.loc_code=location.loc_code
 323                         AND item.units=unit.abbr";
 324
 325         if (check_value('OpenOnly') || $outstanding_only != 0)
 326         {
 327                 $sql .= " AND workorder.closed=0";
 328         }
 329
 330         if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != $all_items)
 331         {
 332                 $sql .= " AND workorder.loc_code=".db_escape($_POST['StockLocation']);
 333         }
 334
 335         if (isset($_POST['OrderNumber']) && $_POST['OrderNumber'] != "")
 336         {
 337                 $sql .= " AND workorder.wo_ref LIKE ".db_escape('%'.$_POST['OrderNumber'].'%');
 338         }
 339
 340         if (isset($_POST['SelectedStockItem']) && $_POST['SelectedStockItem'] != $all_items)
 341         {
 342                 $sql .= " AND workorder.stock_id=".db_escape($_POST['SelectedStockItem']);
 343         }
 344
 345         if (check_value('OverdueOnly'))
 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()
 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($_POST['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
 382 ?>