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 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
130 AND st.stock_id=wo.stock_id
131 AND l.loc_code=wo.loc_code
132 AND wo.id=".db_escape($woid)."
135 $result = db_query($sql, "The work order could not be retrieved");
137 if (!$allow_null && db_num_rows($result) == 0)
139 display_db_error("Could not find work order ".(int)$woid, $sql);
143 return db_fetch($result);
146 //--------------------------------------------------------------------------------------
148 function work_order_has_productions($woid)
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");
153 $myrow = db_fetch_row($result);
154 return ($myrow[0] > 0);
158 //--------------------------------------------------------------------------------------
160 function work_order_has_issues($woid)
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");
165 $myrow = db_fetch_row($result);
166 return ($myrow[0] > 0);
169 //--------------------------------------------------------------------------------------
171 function work_order_has_payments($woid)
173 $result = get_gl_wo_cost_trans($woid);
175 return (db_num_rows($result) != 0);
178 //--------------------------------------------------------------------------------------
180 function release_work_order($woid, $releaseDate, $memo_)
184 $myrow = get_work_order($woid);
185 $stock_id = $myrow["stock_id"];
187 $date = date2sql($releaseDate);
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");
193 // create wo_requirements records according to current BOM
194 create_wo_requirements($woid, $stock_id);
196 add_comments(ST_WORKORDER, $woid, $releaseDate, $memo_);
197 add_audit_trail(ST_WORKORDER, $woid, sql2date($myrow['date_']), _("Released."));
199 commit_transaction();
202 //--------------------------------------------------------------------------------------
204 function close_work_order($woid)
206 $sql = "UPDATE ".TB_PREF."workorders SET closed=1 WHERE id = ".db_escape($woid);
207 db_query($sql, "could not close work order");
210 //--------------------------------------------------------------------------------------
212 function work_order_is_closed($woid)
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);
220 //--------------------------------------------------------------------------------------
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.
225 function work_order_update_finished_quantity($woid, $quantity, $force_close=0)
228 $sql = "UPDATE ".TB_PREF."workorders SET units_issued = units_issued + ".db_escape($quantity)."
229 WHERE id = ".db_escape($woid). " AND !closed";
231 db_query($sql, "The work order issued quantity couldn't be updated");
233 $sql = "UPDATE ".TB_PREF."workorders SET closed = ((units_issued >= units_reqd) OR ".db_escape($force_close).")
234 WHERE id = ".db_escape($woid);
236 db_query($sql, "The work order couldn't be closed");
238 return db_num_affected_rows(); // returns 1 if WO has been closed
241 function reopen_work_order($woid)
243 // FIXME: update_material_cost
244 // update_material_cost($work_order['stock_id'], -$work_order['units_issued'], ... );
246 void_gl_trans(ST_WORKORDER, $woid);
248 void_stock_move(ST_WORKORDER, $woid);
250 $sql = "UPDATE ".TB_PREF."workorders SET closed=0 WHERE id = "
253 db_query($sql, "The work order couldn't be reopened");
256 //--------------------------------------------------------------------------------------
258 Void all work order related operations
260 function void_work_order($woid)
263 hook_db_prevoid(ST_WORKORDER, $woid);
265 $work_order = get_work_order($woid);
267 if ($work_order['closed'])
268 reopen_work_order($woid);
270 if ($work_order['units_issued'])
272 $prods = get_work_order_productions($woid);
273 while ($prod = db_fetch($prods))
274 void_work_order_produce($prod['id']);
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']);
282 // void all related stock moves
283 void_stock_move(ST_WORKORDER, $woid);
285 // void any related costing records
286 void_wo_costing($woid);
288 // clear the requirements units received
289 void_wo_requirements($woid);
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 = "
295 db_query($sql, "The work order couldn't be voided");
297 commit_transaction();
300 function get_sql_for_work_orders($outstanding_only, $stock_id, $location = ALL_TEXT, $order_no='', $order_ref = '', $overdue = false)
306 location.location_name,
308 workorder.units_reqd,
309 workorder.units_issued,
311 workorder.required_by,
312 workorder.released_date,
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
323 AND workorder.stock_id=item.stock_id
324 AND workorder.loc_code=location.loc_code
325 AND item.units=unit.abbr";
327 if (check_value('OpenOnly') || $outstanding_only != 0)
329 $sql .= " AND workorder.closed=0";
332 if ($location != ALL_TEXT)
334 $sql .= " AND workorder.loc_code=".db_escape($location);
339 $sql .= " AND workorder.id LIKE ".db_escape('%'.$order_no.'%');
342 if ($order_ref != '')
344 $sql .= " AND workorder.wo_ref LIKE ".db_escape('%'.$order_ref.'%');
347 if ($stock_id != ALL_TEXT)
349 $sql .= " AND workorder.stock_id=".db_escape($stock_id);
354 $Today = date2sql(Today());
356 $sql .= " AND workorder.required_by < '$Today' ";
358 $sql .= " ORDER BY workorder.id DESC";
362 function get_sql_for_where_used($stock_id)
366 workcentre.name As WorkCentreName,
367 location.location_name,
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);
380 //--------------------------------------------------------------------------------------
381 function get_gl_wo_cost($woid, $cost_type)
384 $result = get_gl_wo_cost_trans($woid, $cost_type);
385 while ($row = db_fetch($result))
386 $cost += -$row['amount'];