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 //----------------------------------------------------------------------------------------
14 function delete_po($po)
19 hook_db_prevoid($po, ST_PURCHORDER);
20 $sql = "DELETE FROM ".TB_PREF."purch_orders WHERE order_no=".db_escape($po);
21 db_query($sql, "The order header could not be deleted");
23 $sql = "DELETE FROM ".TB_PREF."purch_order_details WHERE order_no =".db_escape($po);
24 db_query($sql, "The order detail lines could not be deleted");
26 $Refs->restore_last(ST_PURCHORDER, $po);
30 //----------------------------------------------------------------------------------------
32 function add_po(&$po_obj)
37 hook_db_prewrite($po_obj, ST_PURCHORDER);
39 /*Insert to purchase order header record */
40 $sql = "INSERT INTO ".TB_PREF."purch_orders (supplier_id, Comments, ord_date, reference,
41 requisition_no, into_stock_location, delivery_address, total, tax_included) VALUES(";
42 $sql .= db_escape($po_obj->supplier_id) . "," .
43 db_escape($po_obj->Comments) . ",'" .
44 date2sql($po_obj->orig_order_date) . "', " .
45 db_escape($po_obj->reference) . ", " .
46 db_escape($po_obj->supp_ref) . ", " .
47 db_escape($po_obj->Location) . ", " .
48 db_escape($po_obj->delivery_address) . ", " .
49 db_escape($po_obj->get_trans_total()). ", " .
50 db_escape($po_obj->tax_included) . ")";
52 db_query($sql, "The purchase order header record could not be inserted");
54 /*Get the auto increment value of the order number created from the sql above */
55 $po_obj->order_no = db_insert_id();
57 /*Insert the purchase order detail records */
58 foreach ($po_obj->line_items as $line_no => $po_line)
60 $sql = "INSERT INTO ".TB_PREF."purch_order_details (order_no, item_code, description, delivery_date, unit_price, quantity_ordered) VALUES (";
61 $sql .= $po_obj->order_no . ", " . db_escape($po_line->stock_id). "," .
62 db_escape($po_line->item_description). ",'" .
63 date2sql($po_line->req_del_date) . "'," .
64 db_escape($po_line->price) . ", " .
65 db_escape($po_line->quantity). ")";
66 db_query($sql, "One of the purchase order detail records could not be inserted");
67 $po_obj->line_items[$line_no]->po_detail_rec = db_insert_id();
70 $Refs->save(ST_PURCHORDER, $po_obj->order_no, $po_obj->reference);
72 //add_comments(ST_PURCHORDER, $po_obj->order_no, $po_obj->orig_order_date, $po_obj->Comments);
74 add_audit_trail(ST_PURCHORDER, $po_obj->order_no, $po_obj->orig_order_date);
75 hook_db_postwrite($po_obj, ST_PURCHORDER);
78 return $po_obj->order_no;
81 //----------------------------------------------------------------------------------------
83 function update_po(&$po_obj)
86 hook_db_prewrite($po_obj, ST_PURCHORDER);
88 /*Update the purchase order header with any changes */
89 $sql = "UPDATE ".TB_PREF."purch_orders SET Comments=" . db_escape($po_obj->Comments) . ",
90 requisition_no= ". db_escape( $po_obj->supp_ref). ",
91 into_stock_location=" . db_escape($po_obj->Location). ",
92 ord_date='" . date2sql($po_obj->orig_order_date) . "',
93 delivery_address=" . db_escape($po_obj->delivery_address).",
94 total=". db_escape($po_obj->get_trans_total()).",
95 tax_included=". db_escape($po_obj->tax_included);
96 $sql .= " WHERE order_no = " . $po_obj->order_no;
97 db_query($sql, "The purchase order could not be updated");
99 $sql = "DELETE FROM ".TB_PREF."purch_order_details WHERE order_no="
100 .db_escape($po_obj->order_no);
101 db_query($sql, "could not delete old purch order details");
103 /*Now Update the purchase order detail records */
104 foreach ($po_obj->line_items as $po_line)
106 $sql = "INSERT INTO ".TB_PREF."purch_order_details (po_detail_item, order_no, item_code,
107 description, delivery_date, unit_price, quantity_ordered, quantity_received) VALUES ("
108 .db_escape($po_line->po_detail_rec ? $po_line->po_detail_rec : 0). ","
109 .$po_obj->order_no . ","
110 .db_escape($po_line->stock_id). ","
111 .db_escape($po_line->item_description). ",'"
112 .date2sql($po_line->req_del_date) . "',"
113 .db_escape($po_line->price) . ", "
114 .db_escape($po_line->quantity) . ", "
115 .db_escape($po_line->qty_received) . ")";
117 db_query($sql, "One of the purchase order detail records could not be updated");
120 // add_comments(ST_PURCHORDER, $po_obj->order_no, $po_obj->orig_order_date, $po_obj->Comments);
122 add_audit_trail($po_obj->trans_type, $po_obj->order_no, Today(), _("Updated."));
123 hook_db_postwrite($po_obj, ST_PURCHORDER);
124 commit_transaction();
126 return $po_obj->order_no;
129 //----------------------------------------------------------------------------------------
131 function read_po_header($order_no, &$order)
133 $sql = "SELECT ".TB_PREF."purch_orders.*, ".TB_PREF."suppliers.supp_name, ".TB_PREF."suppliers.tax_group_id,
134 ".TB_PREF."suppliers.curr_code, ".TB_PREF."locations.location_name
135 FROM ".TB_PREF."purch_orders, ".TB_PREF."suppliers, ".TB_PREF."locations
136 WHERE ".TB_PREF."purch_orders.supplier_id = ".TB_PREF."suppliers.supplier_id
137 AND ".TB_PREF."locations.loc_code = into_stock_location
138 AND ".TB_PREF."purch_orders.order_no = ".db_escape($order_no);
140 $result = db_query($sql, "The order cannot be retrieved");
142 if (db_num_rows($result) == 1)
145 $myrow = db_fetch($result);
147 $order->trans_type = ST_PURCHORDER;
148 $order->order_no = $order_no;
150 $order->set_supplier($myrow["supplier_id"], $myrow["supp_name"], $myrow["curr_code"],
151 $myrow['tax_group_id'], $myrow["tax_included"]);
153 $order->credit = get_current_supp_credit($order->supplier_id);
155 $order->orig_order_date = sql2date($myrow["ord_date"]);
156 $order->Comments = nl2br($myrow["comments"]);
157 $order->Location = $myrow["into_stock_location"];
158 $order->supp_ref = $myrow["requisition_no"];
159 $order->reference = $myrow["reference"];
160 $order->delivery_address = $myrow["delivery_address"];
165 display_db_error("FATAL : duplicate purchase order found", "", true);
169 //----------------------------------------------------------------------------------------
171 function read_po_items($order_no, &$order, $open_items_only=false)
173 /*now populate the line po array with the purchase order details records */
175 $sql = "SELECT ".TB_PREF."purch_order_details.*, units
176 FROM ".TB_PREF."purch_order_details
177 LEFT JOIN ".TB_PREF."stock_master
178 ON ".TB_PREF."purch_order_details.item_code=".TB_PREF."stock_master.stock_id
179 WHERE order_no =".db_escape($order_no);
181 if ($open_items_only)
182 $sql .= " AND (".TB_PREF."purch_order_details.quantity_ordered > ".TB_PREF."purch_order_details.quantity_received) ";
184 $sql .= " ORDER BY po_detail_item";
186 $result = db_query($sql, "The lines on the purchase order cannot be retrieved");
188 if (db_num_rows($result) > 0)
190 while ($myrow = db_fetch($result))
192 $data = get_purchase_data($order->supplier_id, $myrow['item_code']);
195 if ($data['supplier_description'] != "")
196 $myrow['description'] = $data['supplier_description'];
197 //if ($data['suppliers_uom'] != "")
198 // $myrow['units'] = $data['suppliers_uom'];
200 if (is_null($myrow["units"]))
206 $units = $myrow["units"];
209 if ($order->add_to_order($order->lines_on_order, $myrow["item_code"],
210 $myrow["quantity_ordered"],$myrow["description"],
211 $myrow["unit_price"],$units, sql2date($myrow["delivery_date"]),
212 $myrow["qty_invoiced"], $myrow["quantity_received"])) {
213 $newline = &$order->line_items[$order->lines_on_order-1];
214 $newline->po_detail_rec = $myrow["po_detail_item"];
215 $newline->standard_cost = $myrow["std_cost_unit"]; /*Needed for receiving goods and GL interface */
216 // set for later GRN edition
217 // $newline->receive_qty = $newline->quantity - $newline->qty_dispatched;
219 } /* line po from purchase order details */
220 } //end of checks on returned data set
223 //----------------------------------------------------------------------------------------
225 function read_po($order_no, &$order, $open_items_only=false)
227 $result = read_po_header($order_no, $order);
230 read_po_items($order_no, $order, $open_items_only);
233 //----------------------------------------------------------------------------------------
235 function get_po_items($order_no)
237 $sql = "SELECT item_code, quantity_ordered, quantity_received, qty_invoiced
238 FROM ".TB_PREF."purch_order_details
239 WHERE order_no=".db_escape($order_no)
240 ." ORDER BY po_detail_item";
242 $result = db_query($sql, "could not query purch order details");
243 check_db_error("Could not check that the details of the purchase order had not been changed by another user ", $sql);
246 //----------------------------------------------------------------------------------------
248 function get_short_info($stock_id)
250 $sql = "SELECT description, units, mb_flag
251 FROM ".TB_PREF."stock_master WHERE stock_id = ".db_escape($stock_id);
253 return db_query($sql,"The stock details for " . $stock_id . " could not be retrieved");
256 function get_sql_for_po_search_completed($supplier_id=ALL_TEXT)
258 global $order_number, $selected_stock_item;;
264 location.location_name,
265 porder.requisition_no,
268 Sum(line.unit_price*line.quantity_ordered) AS OrderValue,
269 porder.into_stock_location
270 FROM ".TB_PREF."purch_orders as porder, "
271 .TB_PREF."purch_order_details as line, "
272 .TB_PREF."suppliers as supplier, "
273 .TB_PREF."locations as location
274 WHERE porder.order_no = line.order_no
275 AND porder.supplier_id = supplier.supplier_id
276 AND location.loc_code = porder.into_stock_location ";
278 if (isset($_GET['supplier_id']))
279 $sql .= "AND supplier.supplier_id=".@$_GET['supplier_id']." ";
280 if (isset($order_number) && $order_number != "")
282 $sql .= "AND porder.reference LIKE ".db_escape('%'. $order_number . '%');
287 $data_after = date2sql($_POST['OrdersAfterDate']);
288 $date_before = date2sql($_POST['OrdersToDate']);
290 $sql .= " AND porder.ord_date >= '$data_after'";
291 $sql .= " AND porder.ord_date <= '$date_before'";
293 if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != ALL_TEXT)
295 $sql .= " AND porder.into_stock_location = ".db_escape($_POST['StockLocation']);
297 if (isset($selected_stock_item))
299 $sql .= " AND line.item_code=".db_escape($selected_stock_item);
301 if ($supplier_id != ALL_TEXT)
302 $sql .= " AND supplier.supplier_id=".db_escape($supplier_id);
304 } //end not order number selected
306 $sql .= " GROUP BY porder.order_no";
310 function get_sql_for_po_search($supplier_id=ALL_TEXT)
312 global $all_items, $order_number, $selected_stock_item;;
318 location.location_name,
319 porder.requisition_no,
322 Sum(line.unit_price*line.quantity_ordered) AS OrderValue,
323 Sum(line.delivery_date < '". date2sql(Today()) ."'
324 AND (line.quantity_ordered > line.quantity_received)) As OverDue
326 .TB_PREF."purch_orders as porder, "
327 .TB_PREF."purch_order_details as line, "
328 .TB_PREF."suppliers as supplier, "
329 .TB_PREF."locations as location
330 WHERE porder.order_no = line.order_no
331 AND porder.supplier_id = supplier.supplier_id
332 AND location.loc_code = porder.into_stock_location
333 AND (line.quantity_ordered > line.quantity_received) ";
335 if (isset($order_number) && $order_number != "")
337 $sql .= "AND porder.reference LIKE ".db_escape('%'. $order_number . '%');
341 $data_after = date2sql($_POST['OrdersAfterDate']);
342 $data_before = date2sql($_POST['OrdersToDate']);
344 $sql .= " AND porder.ord_date >= '$data_after'";
345 $sql .= " AND porder.ord_date <= '$data_before'";
347 if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != $all_items)
349 $sql .= " AND porder.into_stock_location = ".db_escape($_POST['StockLocation']);
352 if (isset($selected_stock_item))
354 $sql .= " AND line.item_code=".db_escape($selected_stock_item);
356 if ($supplier_id != ALL_TEXT)
357 $sql .= " AND supplier.supplier_id=".db_escape($supplier_id);
358 } //end not order number selected
360 $sql .= " GROUP BY porder.order_no";