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, $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 function reopen_work_order($woid)
241 // FIXME: update_material_cost
242 // update_material_cost($work_order['stock_id'], -$work_order['units_issued'], ... );
244 void_gl_trans(ST_WORKORDER, $woid);
246 void_stock_move(ST_WORKORDER, $woid);
248 $sql = "UPDATE ".TB_PREF."workorders SET closed=0 WHERE id = "
251 db_query($sql, "The work order couldn't be reopened");
254 //--------------------------------------------------------------------------------------
256 Void all work order related operations
258 function void_work_order($woid)
261 hook_db_prevoid(ST_WORKORDER, $woid);
263 $work_order = get_work_order($woid);
265 if ($work_order['closed'])
266 reopen_work_order($woid);
268 if ($work_order['units_issued'])
270 $prods = get_work_order_productions($woid);
271 while ($prod = db_fetch($prods))
272 void_work_order_produce($prod['id']);
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']);
280 // void all related stock moves
281 void_stock_move(ST_WORKORDER, $woid);
283 // void any related costing records
284 void_wo_costing($woid);
286 // clear the requirements units received
287 void_wo_requirements($woid);
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 = "
293 db_query($sql, "The work order couldn't be voided");
295 commit_transaction();
298 function get_sql_for_work_orders($outstanding_only, $stock_id, $location = ALL_TEXT, $order_no='', $order_ref = '', $overdue = false)
304 location.location_name,
306 workorder.units_reqd,
307 workorder.units_issued,
309 workorder.required_by,
310 workorder.released_date,
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";
323 if (check_value('OpenOnly') || $outstanding_only != 0)
325 $sql .= " AND workorder.closed=0";
328 if ($location != ALL_TEXT)
330 $sql .= " AND workorder.loc_code=".db_escape($location);
335 $sql .= " AND workorder.id LIKE ".db_escape('%'.$order_no.'%');
338 if ($order_ref != '')
340 $sql .= " AND workorder.wo_ref LIKE ".db_escape('%'.$order_ref.'%');
343 if ($stock_id != ALL_TEXT)
345 $sql .= " AND workorder.stock_id=".db_escape($stock_id);
350 $Today = date2sql(Today());
352 $sql .= " AND workorder.required_by < '$Today' ";
354 $sql .= " ORDER BY workorder.id DESC";
358 function get_sql_for_where_used($stock_id)
362 workcentre.name As WorkCentreName,
363 location.location_name,
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);
376 //--------------------------------------------------------------------------------------
377 function get_gl_wo_cost($woid, $cost_type)
380 $result = get_gl_wo_cost_trans($woid, $cost_type);
381 while ($row = db_fetch($result))
382 $cost += -$row['amount'];