1<?php 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//---------------------------------------------------------------------------------------- 13 14function delete_po($po) 15{ 16 global $Refs; 17 18 begin_transaction(); 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"); 22 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"); 25 26 $Refs->restore_last(ST_PURCHORDER, $po); 27 commit_transaction(); 28} 29 30//---------------------------------------------------------------------------------------- 31 32function add_po(&$po_obj) 33{ 34 global $Refs; 35 36 begin_transaction(); 37 hook_db_prewrite($po_obj, ST_PURCHORDER); 38 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) . ")"; 51 52 db_query($sql, "The purchase order header record could not be inserted"); 53 54 /*Get the auto increment value of the order number created from the sql above */ 55 $po_obj->order_no = db_insert_id(); 56 57 /*Insert the purchase order detail records */ 58 foreach ($po_obj->line_items as $line_no => $po_line) 59 { 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(); 68 } 69 70 $Refs->save(ST_PURCHORDER, $po_obj->order_no, $po_obj->reference); 71 72 //add_comments(ST_PURCHORDER, $po_obj->order_no, $po_obj->orig_order_date, $po_obj->Comments); 73 74 add_audit_trail(ST_PURCHORDER, $po_obj->order_no, $po_obj->orig_order_date); 75 hook_db_postwrite($po_obj, ST_PURCHORDER); 76 commit_transaction(); 77 78 return $po_obj->order_no; 79} 80 81//---------------------------------------------------------------------------------------- 82 83function update_po(&$po_obj) 84{ 85 begin_transaction(); 86 hook_db_prewrite($po_obj, ST_PURCHORDER); 87 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"); 98 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"); 102 103 /*Now Update the purchase order detail records */ 104 foreach ($po_obj->line_items as $po_line) 105 { 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) . ")"; 116 117 db_query($sql, "One of the purchase order detail records could not be updated"); 118 } 119 120 // add_comments(ST_PURCHORDER, $po_obj->order_no, $po_obj->orig_order_date, $po_obj->Comments); 121 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(); 125 126 return $po_obj->order_no; 127} 128 129//---------------------------------------------------------------------------------------- 130 131function read_po_header($order_no, &$order) 132{ 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); 139 140 $result = db_query($sql, "The order cannot be retrieved"); 141 142 if (db_num_rows($result) == 1) 143 { 144 145 $myrow = db_fetch($result); 146 147 $order->trans_type = ST_PURCHORDER; 148 $order->order_no = $order_no; 149 150 $order->set_supplier($myrow["supplier_id"], $myrow["supp_name"], $myrow["curr_code"], 151 $myrow['tax_group_id'], $myrow["tax_included"]); 152 153 $order->credit = get_current_supp_credit($order->supplier_id); 154 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"]; 161 162 return true; 163 } 164 165 display_db_error("FATAL : duplicate purchase order found", "", true); 166 return false; 167} 168 169//---------------------------------------------------------------------------------------- 170 171function read_po_items($order_no, &$order, $open_items_only=false) 172{ 173 /*now populate the line po array with the purchase order details records */ 174 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); 180 181 if ($open_items_only) 182 $sql .= " AND (".TB_PREF."purch_order_details.quantity_ordered > ".TB_PREF."purch_order_details.quantity_received) "; 183 184 $sql .= " ORDER BY po_detail_item"; 185 186 $result = db_query($sql, "The lines on the purchase order cannot be retrieved"); 187 188 if (db_num_rows($result) > 0) 189 { 190 while ($myrow = db_fetch($result)) 191 { 192 $data = get_purchase_data($order->supplier_id, $myrow['item_code']); 193 if ($data !== false) 194 { 195 if ($data['supplier_description'] != "") 196 $myrow['description'] = $data['supplier_description']; 197 //if ($data['suppliers_uom'] != "") 198 // $myrow['units'] = $data['suppliers_uom']; 199 } 200 if (is_null($myrow["units"])) 201 { 202 $units = ""; 203 } 204 else 205 { 206 $units = $myrow["units"]; 207 } 208 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; 218 } 219 } /* line po from purchase order details */ 220 } //end of checks on returned data set 221} 222 223//---------------------------------------------------------------------------------------- 224 225function read_po($order_no, &$order, $open_items_only=false) 226{ 227 $result = read_po_header($order_no, $order); 228 229 if ($result) 230 read_po_items($order_no, $order, $open_items_only); 231} 232 233//---------------------------------------------------------------------------------------- 234 235function get_po_items($order_no) 236{ 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"; 241 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); 244 return $result; 245} 246//---------------------------------------------------------------------------------------- 247 248function get_short_info($stock_id) 249{ 250 $sql = "SELECT description, units, mb_flag 251 FROM ".TB_PREF."stock_master WHERE stock_id = ".db_escape($stock_id); 252 253 return db_query($sql,"The stock details for " . $stock_id . " could not be retrieved"); 254} 255 256function get_sql_for_po_search_completed($supplier_id=ALL_TEXT) 257{ 258 global $order_number, $selected_stock_item;; 259 260 $sql = "SELECT 261 porder.order_no, 262 porder.reference, 263 supplier.supp_name, 264 location.location_name, 265 porder.requisition_no, 266 porder.ord_date, 267 supplier.curr_code, 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 "; 277 278 if (isset($_GET['supplier_id'])) 279 $sql .= "AND supplier.supplier_id=".@$_GET['supplier_id']." "; 280 if (isset($order_number) && $order_number != "") 281 { 282 $sql .= "AND porder.reference LIKE ".db_escape('%'. $order_number . '%'); 283 } 284 else 285 { 286 287 $data_after = date2sql($_POST['OrdersAfterDate']); 288 $date_before = date2sql($_POST['OrdersToDate']); 289 290 $sql .= " AND porder.ord_date >= '$data_after'"; 291 $sql .= " AND porder.ord_date <= '$date_before'"; 292 293 if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != ALL_TEXT) 294 { 295 $sql .= " AND porder.into_stock_location = ".db_escape($_POST['StockLocation']); 296 } 297 if (isset($selected_stock_item)) 298 { 299 $sql .= " AND line.item_code=".db_escape($selected_stock_item); 300 } 301 if ($supplier_id != ALL_TEXT) 302 $sql .= " AND supplier.supplier_id=".db_escape($supplier_id); 303 304 } //end not order number selected 305 306 $sql .= " GROUP BY porder.order_no"; 307 return $sql; 308} 309 310function get_sql_for_po_search($supplier_id=ALL_TEXT) 311{ 312 global $all_items, $order_number, $selected_stock_item;; 313 314 $sql = "SELECT 315 porder.order_no, 316 porder.reference, 317 supplier.supp_name, 318 location.location_name, 319 porder.requisition_no, 320 porder.ord_date, 321 supplier.curr_code, 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 325 FROM " 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) "; 334 335 if (isset($order_number) && $order_number != "") 336 { 337 $sql .= "AND porder.reference LIKE ".db_escape('%'. $order_number . '%'); 338 } 339 else 340 { 341 $data_after = date2sql($_POST['OrdersAfterDate']); 342 $data_before = date2sql($_POST['OrdersToDate']); 343 344 $sql .= " AND porder.ord_date >= '$data_after'"; 345 $sql .= " AND porder.ord_date <= '$data_before'"; 346 347 if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != $all_items) 348 { 349 $sql .= " AND porder.into_stock_location = ".db_escape($_POST['StockLocation']); 350 } 351 352 if (isset($selected_stock_item)) 353 { 354 $sql .= " AND line.item_code=".db_escape($selected_stock_item); 355 } 356 if ($supplier_id != ALL_TEXT) 357 $sql .= " AND supplier.supplier_id=".db_escape($supplier_id); 358 } //end not order number selected 359 360 $sql .= " GROUP BY porder.order_no"; 361 return $sql; 362} 363?>