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 $sql = "UPDATE ".TB_PREF."workorders SET closed=1,units_reqd=0,units_issued=0 WHERE id = "
294 db_query($sql, "The work order couldn't be voided");
296 commit_transaction();
299 function get_sql_for_work_orders($outstanding_only, $stock_id, $location = ALL_TEXT, $order_no='', $order_ref = '', $overdue = false)
305 location.location_name,
307 workorder.units_reqd,
308 workorder.units_issued,
310 workorder.required_by,
311 workorder.released_date,
316 FROM ".TB_PREF."workorders as workorder
317 LEFT JOIN ".TB_PREF."voided v ON v.id=workorder.id and v.type=".ST_WORKORDER.","
318 .TB_PREF."stock_master as item,"
319 .TB_PREF."item_units as unit,"
320 .TB_PREF."locations as location
322 AND workorder.stock_id=item.stock_id
323 AND workorder.loc_code=location.loc_code
324 AND item.units=unit.abbr";
326 if (check_value('OpenOnly') || $outstanding_only != 0)
328 $sql .= " AND workorder.closed=0";
331 if ($location != ALL_TEXT)
333 $sql .= " AND workorder.loc_code=".db_escape($location);
338 $sql .= " AND workorder.id LIKE ".db_escape('%'.$order_no.'%');
341 if ($order_ref != '')
343 $sql .= " AND workorder.wo_ref LIKE ".db_escape('%'.$order_ref.'%');
346 if ($stock_id != ALL_TEXT)
348 $sql .= " AND workorder.stock_id=".db_escape($stock_id);
353 $Today = date2sql(Today());
355 $sql .= " AND workorder.required_by < '$Today' ";
357 $sql .= " ORDER BY workorder.id DESC";
361 function get_sql_for_where_used($stock_id)
365 workcentre.name As WorkCentreName,
366 location.location_name,
369 FROM ".TB_PREF."bom as bom, "
370 .TB_PREF."stock_master as parent, "
371 .TB_PREF."workcentres as workcentre, "
372 .TB_PREF."locations as location
373 WHERE bom.parent = parent.stock_id
374 AND bom.workcentre_added = workcentre.id
375 AND bom.loc_code = location.loc_code
376 AND bom.component=".db_escape($stock_id);
379 //--------------------------------------------------------------------------------------
380 function get_gl_wo_cost($woid, $cost_type)
383 $result = get_gl_wo_cost_trans($woid, $cost_type);
384 while ($row = db_fetch($result))
385 $cost += -$row['amount'];