From ae115f787d84c11c6140137b054261a398b2f077 Mon Sep 17 00:00:00 2001 From: Joe Date: Mon, 5 Jan 2015 16:58:24 +0100 Subject: [PATCH 1/1] Cleanup in db_pager get_sql and testing it. --- admin/view_print_transaction.php | 4 +-- admin/void_transaction.php | 2 +- dimensions/inquiry/search_dimensions.php | 2 +- gl/bank_account_reconcile.php | 2 +- gl/manage/exchange_rates.php | 6 ++-- manufacturing/includes/db/work_orders_db.inc | 2 +- manufacturing/inquiry/where_used_inquiry.php | 2 +- manufacturing/search_work_orders.php | 4 +-- purchasing/includes/db/po_db.inc | 15 ++++------ purchasing/inquiry/po_search.php | 9 ++---- purchasing/inquiry/po_search_completed.php | 5 ++-- .../inquiry/supplier_allocation_inquiry.php | 4 +-- purchasing/inquiry/supplier_inquiry.php | 2 +- sales/includes/db/cust_trans_db.inc | 4 +-- sales/includes/db/sales_order_db.inc | 9 +++--- sales/inquiry/sales_deliveries_view.php | 2 +- sales/inquiry/sales_orders_view.php | 29 ++----------------- 17 files changed, 35 insertions(+), 68 deletions(-) diff --git a/admin/view_print_transaction.php b/admin/view_print_transaction.php index 5b283b41..a386cbfd 100644 --- a/admin/view_print_transaction.php +++ b/admin/view_print_transaction.php @@ -113,11 +113,11 @@ function handle_search() if (check_valid_entries()==true) { $trans_ref = false; - $sql = get_sql_for_view_transactions($_POST['filterType'], $_POST['FromTransNo'], $_POST['ToTransNo'], $trans_ref); + $sql = get_sql_for_view_transactions(get_post('filterType'), get_post('FromTransNo'), get_post('ToTransNo'), $trans_ref); if ($sql == "") return; - $print_type = $_POST['filterType']; + $print_type = get_post('filterType'); $print_out = ($print_type == ST_SALESINVOICE || $print_type == ST_CUSTCREDIT || $print_type == ST_CUSTDELIVERY || $print_type == ST_PURCHORDER || $print_type == ST_SALESORDER || $print_type == ST_SALESQUOTE || $print_type == ST_CUSTPAYMENT || $print_type == ST_SUPPAYMENT || $print_type == ST_WORKORDER); diff --git a/admin/void_transaction.php b/admin/void_transaction.php index eff9a422..a77cbce9 100644 --- a/admin/void_transaction.php +++ b/admin/void_transaction.php @@ -173,7 +173,7 @@ function voiding_controls() end_table(1); $trans_ref = false; - $sql = get_sql_for_view_transactions($_POST['filterType'], $_POST['FromTransNo'], $_POST['ToTransNo'], $trans_ref); + $sql = get_sql_for_view_transactions(get_post('filterType'), get_post('FromTransNo'), get_post('ToTransNo'), $trans_ref); if ($sql == "") return; diff --git a/dimensions/inquiry/search_dimensions.php b/dimensions/inquiry/search_dimensions.php index 95ff4aeb..ea1781a1 100644 --- a/dimensions/inquiry/search_dimensions.php +++ b/dimensions/inquiry/search_dimensions.php @@ -122,7 +122,7 @@ function edit_link($row) } $sql = get_sql_for_search_dimensions($dim, $_POST['FromDate'], $_POST['ToDate'], - $_POST['OrderNumber'], $_POST['type_'], $_POST['OpenOnly'], $_POST['OverdueOnly']); + $_POST['OrderNumber'], $_POST['type_'], check_value('OpenOnly'), check_value('OverdueOnly')); $cols = array( _("#") => array('fun'=>'view_link'), diff --git a/gl/bank_account_reconcile.php b/gl/bank_account_reconcile.php index 668143d0..a6d8dd65 100644 --- a/gl/bank_account_reconcile.php +++ b/gl/bank_account_reconcile.php @@ -221,7 +221,7 @@ echo "
"; if (!isset($_POST['bank_account'])) $_POST['bank_account'] = ""; -$sql = get_sql_for_bank_account_reconcile($_POST['bank_account'], get_post('reconcile_date')); +$sql = get_sql_for_bank_account_reconcile(get_post('bank_account'), get_post('reconcile_date')); $act = get_bank_account($_POST["bank_account"]); display_heading($act['bank_account_name']." - ".$act['bank_curr_code']); diff --git a/gl/manage/exchange_rates.php b/gl/manage/exchange_rates.php index c96c22a4..e2e989a1 100644 --- a/gl/manage/exchange_rates.php +++ b/gl/manage/exchange_rates.php @@ -187,9 +187,9 @@ if ($_POST['curr_abrev'] != get_global_curr_code()) $selected_id = ""; } -set_global_curr_code($_POST['curr_abrev']); +set_global_curr_code(get_post('curr_abrev')); -$sql = get_sql_for_exchange_rates($_POST['curr_abrev']); +$sql = get_sql_for_exchange_rates(get_post('curr_abrev')); $cols = array( _("Date to Use From") => 'date', @@ -199,7 +199,7 @@ $cols = array( ); $table =& new_db_pager('orders_tbl', $sql, $cols); -if (is_company_currency($_POST['curr_abrev'])) +if (is_company_currency(get_post('curr_abrev'))) { display_note(_("The selected currency is the company currency."), 2); diff --git a/manufacturing/includes/db/work_orders_db.inc b/manufacturing/includes/db/work_orders_db.inc index 12332c78..35c00d39 100644 --- a/manufacturing/includes/db/work_orders_db.inc +++ b/manufacturing/includes/db/work_orders_db.inc @@ -337,7 +337,7 @@ function get_sql_for_work_orders($outstanding_only, $stock_id, $location = ALL_T $sql .= " AND workorder.wo_ref LIKE ".db_escape('%'.$order.'%'); } - if ($stock_id != '') + if ($stock_id != ALL_TEXT) { $sql .= " AND workorder.stock_id=".db_escape($stock_id); } diff --git a/manufacturing/inquiry/where_used_inquiry.php b/manufacturing/inquiry/where_used_inquiry.php index f1278476..62f7d91b 100644 --- a/manufacturing/inquiry/where_used_inquiry.php +++ b/manufacturing/inquiry/where_used_inquiry.php @@ -37,7 +37,7 @@ function select_link($row) "/manufacturing/manage/bom_edit.php?stock_id=" . $row["parent"]); } -$sql = get_sql_for_where_used($_POST['stock_id']); +$sql = get_sql_for_where_used(get_post('stock_id')); $cols = array( _("Parent Item") => array('fun'=>'select_link'), diff --git a/manufacturing/search_work_orders.php b/manufacturing/search_work_orders.php index 9becd5b1..106114af 100644 --- a/manufacturing/search_work_orders.php +++ b/manufacturing/search_work_orders.php @@ -153,8 +153,8 @@ function dec_amount($row, $amount) return number_format2($amount, $row['decimals']); } -$sql = get_sql_for_work_orders($outstanding_only, $_POST['SelectedStockItem'], $_POST['StockLocation'], - $_POST['OrderNumber'], check_value('OverdueOnly')); +$sql = get_sql_for_work_orders($outstanding_only, get_post('SelectedStockItem'), get_post('StockLocation'), get_post('OrderNumber'), + check_value('OverdueOnly')); $cols = array( _("#") => array('fun'=>'view_link', 'ord'=>''), diff --git a/purchasing/includes/db/po_db.inc b/purchasing/includes/db/po_db.inc index 30978da9..51085142 100644 --- a/purchasing/includes/db/po_db.inc +++ b/purchasing/includes/db/po_db.inc @@ -293,7 +293,6 @@ function get_short_info($stock_id) function get_sql_for_po_search_completed($from, $to, $supplier_id=ALL_TEXT, $location=ALL_TEXT, $order_number = '', $stock_id = '') { - $sql = "SELECT porder.order_no, porder.reference, @@ -314,7 +313,7 @@ function get_sql_for_po_search_completed($from, $to, $supplier_id=ALL_TEXT, $loc if ($supplier_id != ALL_TEXT) $sql .= "AND supplier.supplier_id=".$supplier_id." "; - if (isset($order_number) && $order_number != "") + if ($order_number != "") { $sql .= "AND porder.reference LIKE ".db_escape('%'. $order_number . '%'); } @@ -331,7 +330,7 @@ function get_sql_for_po_search_completed($from, $to, $supplier_id=ALL_TEXT, $loc { $sql .= " AND porder.into_stock_location = ".db_escape($location); } - if (isset($selected_stock_item)) + if ($stock_id !== '') { $sql .= " AND line.item_code=".db_escape($stock_id); } @@ -344,10 +343,8 @@ function get_sql_for_po_search_completed($from, $to, $supplier_id=ALL_TEXT, $loc return $sql; } -function get_sql_for_po_search($from, $to, $supplier_id=ALL_TEXT, $location=ALL_TEXT) +function get_sql_for_po_search($from, $to, $supplier_id=ALL_TEXT, $location=ALL_TEXT, $order_number='', $stock_id='') { - global $all_items, $order_number, $selected_stock_item;; - $sql = "SELECT porder.order_no, porder.reference, @@ -369,7 +366,7 @@ function get_sql_for_po_search($from, $to, $supplier_id=ALL_TEXT, $location=ALL_ AND location.loc_code = porder.into_stock_location AND (line.quantity_ordered > line.quantity_received) "; - if (isset($order_number) && $order_number != "") + if ($order_number != "") { $sql .= "AND porder.reference LIKE ".db_escape('%'. $order_number . '%'); } @@ -386,9 +383,9 @@ function get_sql_for_po_search($from, $to, $supplier_id=ALL_TEXT, $location=ALL_ $sql .= " AND porder.into_stock_location = ".db_escape($location); } - if (isset($selected_stock_item)) + if ($stock_id != '') { - $sql .= " AND line.item_code=".db_escape($selected_stock_item); + $sql .= " AND line.item_code=".db_escape($stock_id); } if ($supplier_id != ALL_TEXT) $sql .= " AND supplier.supplier_id=".db_escape($supplier_id); diff --git a/purchasing/inquiry/po_search.php b/purchasing/inquiry/po_search.php index f7419b02..f3203f63 100644 --- a/purchasing/inquiry/po_search.php +++ b/purchasing/inquiry/po_search.php @@ -107,14 +107,9 @@ function check_overdue($row) } //--------------------------------------------------------------------------------------------- -if (isset($_POST['order_number']) && ($_POST['order_number'] != "")) -{ - $order_number = $_POST['order_number']; -} - //figure out the sql required from the inputs available -$sql = get_sql_for_po_search($_POST['OrdersAfterDate'], $_POST['OrdersToDate'], $_POST['supplier_id'], - $_POST['StockLocation']); +$sql = get_sql_for_po_search(get_post('OrdersAfterDate'), get_post('OrdersToDate'), get_post('supplier_id'), get_post('StockLocation'), + $_POST['order_number'], get_post('SelectStockFromList')); //$result = db_query($sql,"No orders were returned"); diff --git a/purchasing/inquiry/po_search_completed.php b/purchasing/inquiry/po_search_completed.php index 3d3acbe2..cb67ab7d 100644 --- a/purchasing/inquiry/po_search_completed.php +++ b/purchasing/inquiry/po_search_completed.php @@ -26,7 +26,7 @@ page(_($help_context = "Search Purchase Orders"), false, false, "", $js); if (isset($_GET['order_number'])) { - $order_number = $_GET['order_number']; + $_POST['order_number'] = $_GET['order_number']; } //----------------------------------------------------------------------------------- @@ -119,8 +119,7 @@ function prt_link($row) //--------------------------------------------------------------------------------------------- $sql = get_sql_for_po_search_completed(get_post('OrdersAfterDate'), get_post('OrdersToDate'), - get_post('supplier_id') !== '' ? get_post('supplier_id') : ALL_TEXT, - get_post('StockLocation'), get_post('order_number'), get_post('SelectStockFromList')); + get_post('supplier_id'), get_post('StockLocation'), get_post('order_number'), get_post('SelectStockFromList')); $cols = array( _("#") => array('fun'=>'trans_view', 'ord'=>''), diff --git a/purchasing/inquiry/supplier_allocation_inquiry.php b/purchasing/inquiry/supplier_allocation_inquiry.php index 3c97c51c..62dddd94 100644 --- a/purchasing/inquiry/supplier_allocation_inquiry.php +++ b/purchasing/inquiry/supplier_allocation_inquiry.php @@ -120,8 +120,8 @@ function fmt_credit($row) } //------------------------------------------------------------------------------------------------ -$sql = get_sql_for_supplier_allocation_inquiry($_POST['TransAfterDate'],$_POST['TransToDate'], - $_POST['filterType'], $_POST['supplier_id'], check_value('showSettled')); +$sql = get_sql_for_supplier_allocation_inquiry(get_post('TransAfterDate'),get_post('TransToDate'), + get_post('filterType'), get_post('supplier_id'), check_value('showSettled')); $cols = array( _("Type") => array('fun'=>'systype_name'), diff --git a/purchasing/inquiry/supplier_inquiry.php b/purchasing/inquiry/supplier_inquiry.php index 5f58bd9f..d89d5d35 100644 --- a/purchasing/inquiry/supplier_inquiry.php +++ b/purchasing/inquiry/supplier_inquiry.php @@ -167,7 +167,7 @@ function edit_link($row) } //------------------------------------------------------------------------------------------------ -$sql = get_sql_for_supplier_inquiry($_POST['filterType'], $_POST['TransAfterDate'], $_POST['TransToDate'], $_POST['supplier_id']); +$sql = get_sql_for_supplier_inquiry(get_post('filterType'), get_post('TransAfterDate'), get_post('TransToDate'), get_post('supplier_id')); $cols = array( _("Type") => array('fun'=>'systype_name', 'ord'=>''), diff --git a/sales/includes/db/cust_trans_db.inc b/sales/includes/db/cust_trans_db.inc index 1f3e42ca..f9c12a4d 100644 --- a/sales/includes/db/cust_trans_db.inc +++ b/sales/includes/db/cust_trans_db.inc @@ -401,7 +401,7 @@ function get_sql_for_sales_deliveries_view($from, $to, $customer_id, $stock_item //figure out the sql required from the inputs available if ($delivery) { - $sql .= " AND trans.trans_no LIKE %".db_escape($delivery); + $sql .= " AND trans.trans_no LIKE ".db_escape('%' . $delivery . '%'); $sql .= " GROUP BY trans.trans_no"; } else @@ -409,7 +409,7 @@ function get_sql_for_sales_deliveries_view($from, $to, $customer_id, $stock_item $sql .= " AND trans.tran_date >= '".date2sql($from)."'"; $sql .= " AND trans.tran_date <= '".date2sql($to)."'"; - if ($stock_item) + if ($stock_item != ALL_TEXT) $sql .= " AND line.stock_id=".db_escape($stock_item)." "; if ($location != ALL_TEXT) diff --git a/sales/includes/db/sales_order_db.inc b/sales/includes/db/sales_order_db.inc index 33447380..e851f747 100644 --- a/sales/includes/db/sales_order_db.inc +++ b/sales/includes/db/sales_order_db.inc @@ -465,7 +465,7 @@ function get_branch_to_order($customer_id, $branch_id) { PrepaidOrders */ function get_sql_for_sales_orders_view($trans_type, $trans_no, $filter, - $stock_item=null, $from='', $to='', $ref='', $location='', $customer_id=ALL_TEXT) + $stock_item='', $from='', $to='', $ref='', $location=ALL_TEXT, $customer_id=ALL_TEXT) { $sql = "SELECT @@ -515,8 +515,7 @@ function get_sql_for_sales_orders_view($trans_type, $trans_no, $filter, elseif ($ref != "") { // search orders with reference like - $number_like = "%".$ref."%"; - $sql .= " AND sorder.reference LIKE ".db_escape($number_like); + $sql .= " AND sorder.reference LIKE ".db_escape('%' . $ref . '%'); // ." GROUP BY sorder.order_no"; } else // ... or select inquiry constraints @@ -536,10 +535,10 @@ function get_sql_for_sales_orders_view($trans_type, $trans_no, $filter, //if ($selected_customer != -1) // $sql .= " AND sorder.debtor_no=".db_escape($selected_customer); - if (isset($stock_item)) + if ($stock_item != ALL_TEXT) $sql .= " AND line.stk_code=".db_escape($stock_item); - if ($location) + if ($location != ALL_TEXT) $sql .= " AND sorder.from_stk_loc = ".db_escape($location); if ($filter=='OutstandingOnly') diff --git a/sales/inquiry/sales_deliveries_view.php b/sales/inquiry/sales_deliveries_view.php index f6883b53..bc1f3a99 100644 --- a/sales/inquiry/sales_deliveries_view.php +++ b/sales/inquiry/sales_deliveries_view.php @@ -162,7 +162,7 @@ function check_overdue($row) $row["Outstanding"]!=0; } //------------------------------------------------------------------------------------------------ -$sql = get_sql_for_sales_deliveries_view(get_post('DeliveryAfterDate'), get_post('DeliveryToDate'), get_post('customer_id'), +$sql = get_sql_for_sales_deliveries_view(get_post('DeliveryAfterDate'), get_post('DeliveryToDate'), get_post('customer_id'), get_post('SelectStockFromList'), get_post('StockLocation'), get_post('DeliveryNumber'), get_post('OutstandingOnly')); $cols = array( diff --git a/sales/inquiry/sales_orders_view.php b/sales/inquiry/sales_orders_view.php index 18b95256..a9f8cf75 100644 --- a/sales/inquiry/sales_orders_view.php +++ b/sales/inquiry/sales_orders_view.php @@ -76,29 +76,6 @@ if ($use_popup_windows) if (user_use_date_picker()) $js .= get_js_date_picker(); page($_SESSION['page_title'], false, false, "", $js); -/* -if (isset($_GET['selected_customer'])) -{ - $selected_customer = $_GET['selected_customer']; -} -elseif (isset($_POST['selected_customer'])) -{ - $selected_customer = $_POST['selected_customer']; -} -else - $selected_customer = -1; -*/ -//--------------------------------------------------------------------------------------------- - -if (isset($_POST['SelectStockFromList']) && ($_POST['SelectStockFromList'] != "") && - ($_POST['SelectStockFromList'] != ALL_TEXT)) -{ - $selected_stock_item = $_POST['SelectStockFromList']; -} -else -{ - unset($selected_stock_item); -} //--------------------------------------------------------------------------------------------- // Query format functions // @@ -267,9 +244,9 @@ end_table(1); //--------------------------------------------------------------------------------------------- // Orders inquiry table // -$sql = get_sql_for_sales_orders_view($trans_type, $_POST['OrderNumber'], $_POST['order_view_mode'], - @$selected_stock_item, @$_POST['OrdersAfterDate'], @$_POST['OrdersToDate'], @$_POST['OrderReference'], $_POST['StockLocation'], - get_post('customer_id') !== '' ? get_post('customer_id') : ALL_TEXT); +$sql = get_sql_for_sales_orders_view($trans_type, get_post('OrderNumber'), get_post('order_view_mode'), + get_post('SelectStockFromList'), get_post('OrdersAfterDate'), get_post('OrdersToDate'), get_post('OrderReference'), get_post('StockLocation'), + get_post('customer_id')); if ($trans_type == ST_SALESORDER) $cols = array( -- 2.30.2