From 80061be53f5f8d6415801e6e411a3891efc128c4 Mon Sep 17 00:00:00 2001 From: Joe Date: Sun, 22 Oct 2023 00:10:20 +0200 Subject: [PATCH] Bug 5722: Canceled Sales Orders Show Up In Sales Order Inquiry. Fixed. --- sales/includes/db/cust_trans_db.inc | 6 ++++-- sales/includes/db/sales_order_db.inc | 8 +++++--- sales/inquiry/sales_orders_view.php | 6 +++--- 3 files changed, 12 insertions(+), 8 deletions(-) diff --git a/sales/includes/db/cust_trans_db.inc b/sales/includes/db/cust_trans_db.inc index e8ccbf67..61a0f60a 100644 --- a/sales/includes/db/cust_trans_db.inc +++ b/sales/includes/db/cust_trans_db.inc @@ -303,7 +303,7 @@ function post_void_customer_trans($type, $type_no) } //---------------------------------------------------------------------------------------- -function get_sql_for_customer_inquiry($from, $to, $cust_id = ALL_TEXT, $filter = ALL_TEXT, $show_voided = 0) +function get_sql_for_customer_inquiry($from, $to, $cust_id = ALL_TEXT, $filter = ALL_TEXT, $show_voided = 0, $ref='') { $date_after = date2sql($from); $date_to = date2sql($to); @@ -351,7 +351,9 @@ function get_sql_for_customer_inquiry($from, $to, $cust_id = ALL_TEXT, $filter = WHERE debtor.debtor_no = trans.debtor_no"; // exclude voided transactions and self-balancing (journal) transactions: if (!$show_voided) $sql .= " AND ISNULL(v.date_) AND (trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount) != 0"; - + if ($ref) { + $sql .= " AND trans.reference LIKE ". db_escape("%$ref%"); + } if ($filter == '2') $sql .= " AND ABS(IF(prep_amount, prep_amount, ABS(trans.ov_amount) + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount)-trans.alloc)>" .FLOAT_COMP_DELTA; diff --git a/sales/includes/db/sales_order_db.inc b/sales/includes/db/sales_order_db.inc index 3b146bf4..d144995d 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='', $from='', $to='', $ref='', $location=ALL_TEXT, $customer_id=ALL_TEXT) + $stock_item='', $from='', $to='', $ref='', $location=ALL_TEXT, $customer_id=ALL_TEXT, $show_voided = 0) { if ($filter=='OutstandingOnly') $order_value = 'Sum(line.unit_price*(line.quantity-line.qty_sent)*(1-line.discount_percent))+freight_cost'; @@ -565,8 +565,10 @@ function get_sql_for_sales_orders_view($trans_type, $trans_no, $filter, sorder.branch_code, sorder.customer_ref, sorder.ord_date, - sorder.deliver_to - ORDER BY sorder.order_no DESC"; + sorder.deliver_to"; + if ($show_voided == 0) + $sql .= " HAVING OrderValue != 0"; + $sql .= " ORDER BY sorder.order_no DESC"; return $sql; } diff --git a/sales/inquiry/sales_orders_view.php b/sales/inquiry/sales_orders_view.php index 3d70d125..c3e63ab5 100644 --- a/sales/inquiry/sales_orders_view.php +++ b/sales/inquiry/sales_orders_view.php @@ -252,7 +252,8 @@ if (!$page_nested) customer_list_cells(_("Select a customer: "), 'customer_id', null, true, true); if ($trans_type == ST_SALESQUOTE) check_cells(_("Show All:"), 'show_all'); - +if ($trans_type == ST_SALESORDER) + check_cells(_("Zero values"), 'show_voided'); submit_cells('SearchOrders', _("Search"),'',_('Select documents'), 'default'); hidden('order_view_mode', $_POST['order_view_mode']); hidden('type', $trans_type); @@ -264,8 +265,7 @@ end_table(1); // Orders inquiry table // $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')); + get_post('SelectStockFromList'), get_post('OrdersAfterDate'), get_post('OrdersToDate'), get_post('OrderReference'), get_post('StockLocation'), get_post('customer_id'), check_value('show_voided')); if ($trans_type == ST_SALESORDER) $cols = array( -- 2.30.2