+
+function get_sql_for_sales_orders_view($selected_customer, $trans_type)
+{
+ global $selected_stock_item;
+
+ $sql = "SELECT
+ sorder.order_no,
+ sorder.reference,
+ debtor.name,
+ branch.br_name,"
+ .($_POST['order_view_mode']=='InvoiceTemplates'
+ || $_POST['order_view_mode']=='DeliveryTemplates' ?
+ "sorder.comments, " : "sorder.customer_ref, ")
+ ."sorder.ord_date,
+ sorder.delivery_date,
+ sorder.deliver_to,
+ Sum(line.unit_price*line.quantity*(1-line.discount_percent))+freight_cost AS OrderValue,
+ sorder.type,
+ debtor.curr_code,
+ Sum(line.qty_sent) AS TotDelivered,
+ Sum(line.quantity) AS TotQuantity
+ FROM ".TB_PREF."sales_orders as sorder, "
+ .TB_PREF."sales_order_details as line, "
+ .TB_PREF."debtors_master as debtor, "
+ .TB_PREF."cust_branch as branch
+ WHERE sorder.order_no = line.order_no
+ AND sorder.trans_type = line.trans_type
+ AND sorder.trans_type = $trans_type
+ AND sorder.debtor_no = debtor.debtor_no
+ AND sorder.branch_code = branch.branch_code
+ AND debtor.debtor_no = branch.debtor_no";
+
+ if (isset($_POST['OrderNumber']) && $_POST['OrderNumber'] != "")
+ {
+ // search orders with number like
+ $number_like = "%".$_POST['OrderNumber'];
+ $sql .= " AND sorder.order_no LIKE ".db_escape($number_like)
+ ." GROUP BY sorder.order_no";
+ }
+ else // ... or select inquiry constraints
+ {
+ if ($_POST['order_view_mode']!='DeliveryTemplates' && $_POST['order_view_mode']!='InvoiceTemplates')
+ {
+ $date_after = date2sql($_POST['OrdersAfterDate']);
+ $date_before = date2sql($_POST['OrdersToDate']);
+
+ $sql .= " AND sorder.ord_date >= '$date_after'"
+ ." AND sorder.ord_date <= '$date_before'";
+ }
+ if ($trans_type == ST_SALESQUOTE && !check_value('show_all'))
+ $sql .= " AND sorder.delivery_date >= '".date2sql(Today())."'";
+ if ($selected_customer != -1)
+ $sql .= " AND sorder.debtor_no=".db_escape($selected_customer);
+
+ if (isset($selected_stock_item))
+ $sql .= " AND line.stk_code=".db_escape($selected_stock_item);
+
+ if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != ALL_TEXT)
+ $sql .= " AND sorder.from_stk_loc = ".db_escape($_POST['StockLocation']);
+
+ if ($_POST['order_view_mode']=='OutstandingOnly')
+ $sql .= " AND line.qty_sent < line.quantity";
+ elseif ($_POST['order_view_mode']=='InvoiceTemplates' || $_POST['order_view_mode']=='DeliveryTemplates')
+ $sql .= " AND sorder.type=1";
+
+ $sql .= " GROUP BY sorder.order_no,
+ sorder.debtor_no,
+ sorder.branch_code,
+ sorder.customer_ref,
+ sorder.ord_date,
+ sorder.deliver_to";
+ }
+ return $sql;
+}