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 ***********************************************************************/
13 Common entry procedure for all work order types
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)
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);
26 hook_db_prewrite($args, ST_WORKORDER);
28 if ($type != WO_ADVANCED)
29 $required_by = $date_;
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");
40 $woid = db_insert_id();
42 add_comments(ST_WORKORDER, $woid, $required_by, $memo_);
44 $Refs->save(ST_WORKORDER, $woid, $wo_ref);
45 add_audit_trail(ST_WORKORDER, $woid, $date_);
48 hook_db_postwrite($args, ST_WORKORDER);
50 if ($type != WO_ADVANCED)
52 $stockitem = get_item($stock_id);
53 release_work_order($woid, $date_, '');
55 add_wo_costs_journal($woid, $costs, WO_OVERHEAD, $cr_acc, $date_, $stockitem["dimension_id"], $stockitem["dimension2_id"]);
58 add_wo_costs_journal($woid, $labour, WO_LABOUR, $cr_lab_acc, $date_, $stockitem["dimension_id"], $stockitem["dimension2_id"]);
60 if ($type == WO_UNASSEMBLY)
61 $units_reqd = -$units_reqd;
63 $ref = $Refs->get_next(ST_MANURECEIVE, null, $date_);
64 work_order_produce($woid, $ref, $units_reqd, $date_, '', true);
72 //--------------------------------------------------------------------------------------
74 function update_work_order($woid, $loc_code, $units_reqd, $stock_id,
75 $date_, $required_by, $memo_)
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);
83 $date = date2sql($date_);
84 $required = date2sql($required_by);
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).",
90 WHERE id = ".db_escape($woid);
92 db_query($sql, "could not update work order");
94 update_comments(ST_WORKORDER, $woid, null, $memo_);
95 add_audit_trail(ST_WORKORDER, $woid, $date_, _("Updated."));
97 hook_db_postwrite($args, ST_WORKORDER);
101 function delete_work_order($woid, $stock_id, $qty, $date)
104 hook_db_prevoid(ST_WORKORDER, $woid);
106 // delete the work order requirements
107 delete_wo_requirements($woid);
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");
113 delete_comments(ST_WORKORDER, $woid);
114 add_audit_trail(ST_WORKORDER, $woid, $date, _("Canceled."));
116 commit_transaction();
119 //--------------------------------------------------------------------------------------
121 function get_work_order($woid, $allow_null=false)
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)."
133 $result = db_query($sql, "The work order issues could not be retrieved");
135 if (!$allow_null && db_num_rows($result) == 0)
137 display_db_error("Could not find work order $woid", $sql);
141 return db_fetch($result);
144 //--------------------------------------------------------------------------------------
146 function work_order_has_productions($woid)
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");
151 $myrow = db_fetch_row($result);
152 return ($myrow[0] > 0);
156 //--------------------------------------------------------------------------------------
158 function work_order_has_issues($woid)
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");
163 $myrow = db_fetch_row($result);
164 return ($myrow[0] > 0);
167 //--------------------------------------------------------------------------------------
169 function work_order_has_payments($woid)
171 $result = get_gl_wo_cost_trans($woid);
173 return (db_num_rows($result) != 0);
176 //--------------------------------------------------------------------------------------
178 function release_work_order($woid, $releaseDate, $memo_)
182 $myrow = get_work_order($woid);
183 $stock_id = $myrow["stock_id"];
185 $date = date2sql($releaseDate);
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");
191 // create wo_requirements records according to current BOM
192 create_wo_requirements($woid, $stock_id);
194 add_comments(ST_WORKORDER, $woid, $releaseDate, $memo_);
195 add_audit_trail(ST_WORKORDER, $woid, sql2date($myrow['date_']), _("Released."));
197 commit_transaction();
200 //--------------------------------------------------------------------------------------
202 function close_work_order($woid)
204 $sql = "UPDATE ".TB_PREF."workorders SET closed=1 WHERE id = ".db_escape($woid);
205 db_query($sql, "could not close work order");
208 //--------------------------------------------------------------------------------------
210 function work_order_is_closed($woid)
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);
218 //--------------------------------------------------------------------------------------
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.
223 function work_order_update_finished_quantity($woid, $quantity, $date, $force_close=0)
226 $sql = "UPDATE ".TB_PREF."workorders SET units_issued = units_issued + ".db_escape($quantity)."
227 WHERE id = ".db_escape($woid). " AND !closed";
229 db_query($sql, "The work order issued quantity couldn't be updated");
231 $sql = "UPDATE ".TB_PREF."workorders SET closed = ((units_issued >= units_reqd) OR ".db_escape($force_close).")
232 WHERE id = ".db_escape($woid);
234 db_query($sql, "The work order couldn't be closed");
236 return db_num_affected_rows(); // returns 1 if WO has been closed
239 //--------------------------------------------------------------------------------------
241 function void_work_order($woid)
244 hook_db_prevoid(ST_WORKORDER, $woid);
246 $work_order = get_work_order($woid);
247 if (!($work_order["type"] == WO_ADVANCED))
249 // restore average product costs
250 $date = sql2date($work_order['date_']);
251 $qty = $work_order['units_reqd'];
253 // FIXME: update_material_cost
256 $sql = "UPDATE ".TB_PREF."workorders SET closed=1,units_reqd=0,units_issued=0 WHERE id = "
258 db_query($sql, "The work order couldn't be voided");
260 // void all related stock moves
261 void_stock_move(ST_WORKORDER, $woid);
263 // void any related costing records
264 void_wo_costing($woid);
266 // clear the requirements units received
267 void_wo_requirements($woid);
271 // void everything inside the work order : issues, productions, payments
272 $date = sql2date($work_order['date_']);
274 // FIXME: update_material_cost
276 $result = get_work_order_productions($woid); // check the produced quantity
278 while ($row = db_fetch($result))
280 $qty += $row['quantity'];
281 // clear the production record
282 $sql = "UPDATE ".TB_PREF."wo_manufacture SET quantity=0 WHERE id=".$row['id'];
283 db_query($sql, "Cannot void a wo production");
285 void_stock_move(ST_MANURECEIVE, $row['id']); // and void the stock moves;
287 $result = get_additional_issues($woid); // check the issued quantities
290 while ($row = db_fetch($result))
292 $unit_cost = get_unit_cost($row['stock_id']);
293 $icost = $unit_cost * $row['qty_issued'];
296 $issue_no = $row['issue_no'];
297 // void the actual issue items and their quantities
298 $sql = "UPDATE ".TB_PREF."wo_issue_items SET qty_issued = 0 WHERE issue_id="
299 .db_escape($row['id']);
300 db_query($sql,"A work order issue item could not be voided");
303 void_stock_move(ST_MANUISSUE, $issue_no); // and void the stock moves
306 add_issue_cost($work_order['stock_id'], -$qty, $date, $cost);
308 $sql = "UPDATE ".TB_PREF."workorders SET closed=1,units_reqd=0,units_issued=0 WHERE id = "
310 db_query($sql, "The work order couldn't be voided");
312 // void all related stock moves
313 void_stock_move(ST_WORKORDER, $woid);
315 // void any related bank/gl trans
316 void_wo_costing($woid);
318 // clear the requirements units received
319 void_wo_requirements($woid);
322 commit_transaction();
325 function get_sql_for_work_orders($outstanding_only, $stock_id, $location = ALL_TEXT, $order_no='', $order_ref = '', $overdue = false)
331 location.location_name,
333 workorder.units_reqd,
334 workorder.units_issued,
336 workorder.required_by,
337 workorder.released_date,
342 FROM ".TB_PREF."workorders as workorder,"
343 .TB_PREF."stock_master as item,"
344 .TB_PREF."item_units as unit,"
345 .TB_PREF."locations as location
346 WHERE workorder.stock_id=item.stock_id
347 AND workorder.loc_code=location.loc_code
348 AND item.units=unit.abbr";
350 if (check_value('OpenOnly') || $outstanding_only != 0)
352 $sql .= " AND workorder.closed=0";
355 if ($location != ALL_TEXT)
357 $sql .= " AND workorder.loc_code=".db_escape($location);
362 $sql .= " AND workorder.id LIKE ".db_escape('%'.$order_no.'%');
365 if ($order_ref != '')
367 $sql .= " AND workorder.wo_ref LIKE ".db_escape('%'.$order_ref.'%');
370 if ($stock_id != ALL_TEXT)
372 $sql .= " AND workorder.stock_id=".db_escape($stock_id);
377 $Today = date2sql(Today());
379 $sql .= " AND workorder.required_by < '$Today' ";
381 $sql .= " ORDER BY workorder.id DESC";
385 function get_sql_for_where_used($stock_id)
389 workcentre.name As WorkCentreName,
390 location.location_name,
393 FROM ".TB_PREF."bom as bom, "
394 .TB_PREF."stock_master as parent, "
395 .TB_PREF."workcentres as workcentre, "
396 .TB_PREF."locations as location
397 WHERE bom.parent = parent.stock_id
398 AND bom.workcentre_added = workcentre.id
399 AND bom.loc_code = location.loc_code
400 AND bom.component=".db_escape($stock_id);
403 //--------------------------------------------------------------------------------------
404 function get_gl_wo_cost($woid, $cost_type)
407 $result = get_gl_wo_cost_trans($woid, $cost_type);
408 while ($row = db_fetch($result))
409 $cost += -$row['amount'];