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)
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);
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);
25 hook_db_prewrite($args, ST_WORKORDER);
27 add_material_cost($stock_id, $units_reqd, $date_);
29 $date = date2sql($date_);
30 $required = date2sql($required_by);
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");
39 $woid = db_insert_id();
41 add_comments(ST_WORKORDER, $woid, $required_by, $memo_);
43 $Refs->save(ST_WORKORDER, $woid, $wo_ref);
44 add_audit_trail(ST_WORKORDER, $woid, $date_);
47 hook_db_postwrite($args, ST_WORKORDER);
53 //--------------------------------------------------------------------------------------
55 function update_work_order($woid, $loc_code, $units_reqd, $stock_id,
56 $date_, $required_by, $memo_)
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);
64 add_material_cost($_POST['old_stk_id'], -$_POST['old_qty'], $date_);
65 add_material_cost($stock_id, $units_reqd, $date_);
67 $date = date2sql($date_);
68 $required = date2sql($required_by);
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).",
74 WHERE id = ".db_escape($woid);
76 db_query($sql, "could not update work order");
78 update_comments(ST_WORKORDER, $woid, null, $memo_);
79 add_audit_trail(ST_WORKORDER, $woid, $date_, _("Updated."));
81 hook_db_postwrite($args, ST_WORKORDER);
85 function delete_work_order($woid)
88 hook_db_prevoid(ST_WORKORDER, $woid);
90 add_material_cost($_POST['stock_id'], -$_POST['quantity'], $_POST['date_']);
92 // delete the work order requirements
93 delete_wo_requirements($woid);
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");
99 delete_comments(ST_WORKORDER, $woid);
100 add_audit_trail(ST_WORKORDER, $woid, $_POST['date_'], _("Canceled."));
102 commit_transaction();
105 //--------------------------------------------------------------------------------------
107 function get_work_order($woid, $allow_null=false)
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)."
117 $result = db_query($sql, "The work order issues could not be retrieved");
119 if (!$allow_null && db_num_rows($result) == 0)
120 display_db_error("Could not find work order $woid", $sql);
122 return db_fetch($result);
125 //--------------------------------------------------------------------------------------
127 function work_order_has_productions($woid)
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");
132 $myrow = db_fetch_row($result);
133 return ($myrow[0] > 0);
137 //--------------------------------------------------------------------------------------
139 function work_order_has_issues($woid)
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");
144 $myrow = db_fetch_row($result);
145 return ($myrow[0] > 0);
148 //--------------------------------------------------------------------------------------
150 function work_order_has_payments($woid)
152 $result = get_gl_wo_cost_trans($woid);
154 return (db_num_rows($result) != 0);
157 //--------------------------------------------------------------------------------------
159 function release_work_order($woid, $releaseDate, $memo_)
163 $myrow = get_work_order($woid);
164 $stock_id = $myrow["stock_id"];
166 $date = date2sql($releaseDate);
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");
172 // create Work Order Requirements based on the bom
173 create_wo_requirements($woid, $stock_id);
175 add_comments(ST_WORKORDER, $woid, $releaseDate, $memo_);
176 add_audit_trail(ST_WORKORDER, $woid, $myrow['date_'], _("Released."));
178 commit_transaction();
181 //--------------------------------------------------------------------------------------
183 function close_work_order($woid)
185 $sql = "UPDATE ".TB_PREF."workorders SET closed=1 WHERE id = ".db_escape($woid);
186 db_query($sql, "could not close work order");
189 //--------------------------------------------------------------------------------------
191 function work_order_is_closed($woid)
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);
199 //--------------------------------------------------------------------------------------
201 function work_order_update_finished_quantity($woid, $quantity, $force_close=0)
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);
207 db_query($sql, "The work order issued quantity couldn't be updated");
210 //--------------------------------------------------------------------------------------
212 function void_work_order($woid)
215 hook_db_prevoid(ST_WORKORDER, $woid);
217 $work_order = get_work_order($woid);
218 if (!($work_order["type"] == WO_ADVANCED))
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
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
228 add_overhead_cost($work_order['stock_id'], -$qty, $date, $cost);
230 $sql = "UPDATE ".TB_PREF."workorders SET closed=1,units_reqd=0,units_issued=0 WHERE id = "
232 db_query($sql, "The work order couldn't be voided");
234 // void all related stock moves
235 void_stock_move(ST_WORKORDER, $woid);
237 void_wo_costing($woid);
239 // clear the requirements units received
240 void_wo_requirements($woid);
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
249 while ($row = db_fetch($result))
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");
256 void_stock_move(ST_MANURECEIVE, $row['id']); // and void the stock moves;
258 $result = get_additional_issues($woid); // check the issued quantities
261 while ($row = db_fetch($result))
263 $std_cost = get_standard_cost($row['stock_id']);
264 $icost = $std_cost * $row['qty_issued'];
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");
274 void_stock_move(ST_MANUISSUE, $issue_no); // and void the stock moves
276 add_issue_cost($work_order['stock_id'], -$qty, $date, $cost);
278 $cost = get_gl_wo_cost($woid, WO_LABOUR); // get the labour cost and reduce avg cost
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
283 add_overhead_cost($work_order['stock_id'], -$qty, $date, $cost);
285 $sql = "UPDATE ".TB_PREF."workorders SET closed=1,units_reqd=0,units_issued=0 WHERE id = "
287 db_query($sql, "The work order couldn't be voided");
289 // void all related stock moves
290 void_stock_move(ST_WORKORDER, $woid);
292 void_wo_costing($wo);
294 // clear the requirements units received
295 void_wo_requirements($woid);
297 commit_transaction();
300 function get_sql_for_work_orders($outstanding_only, $all_items)
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 .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";
325 if (check_value('OpenOnly') || $outstanding_only != 0)
327 $sql .= " AND workorder.closed=0";
330 if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != $all_items)
332 $sql .= " AND workorder.loc_code=".db_escape($_POST['StockLocation']);
335 if (isset($_POST['OrderNumber']) && $_POST['OrderNumber'] != "")
337 $sql .= " AND workorder.wo_ref LIKE ".db_escape('%'.$_POST['OrderNumber'].'%');
340 if (isset($_POST['SelectedStockItem']) && $_POST['SelectedStockItem'] != $all_items)
342 $sql .= " AND workorder.stock_id=".db_escape($_POST['SelectedStockItem']);
345 if (check_value('OverdueOnly'))
347 $Today = date2sql(Today());
349 $sql .= " AND workorder.required_by < '$Today' ";
354 function get_sql_for_where_used()
358 workcentre.name As WorkCentreName,
359 location.location_name,
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']);
372 //--------------------------------------------------------------------------------------
373 function get_gl_wo_cost($woid, $cost_type)
376 $result = get_gl_wo_cost_trans($woid, $cost_type);
377 while ($row = db_fetch($result))
378 $cost += -$row['amount'];