+
+function get_sql_for_sales_orders_view($selected_customer, $trans_type, $trans_no, $filter,
+ $stock_item=null, $from='', $to='', $ref='', $location='', $customer_id=ALL_TEXT)
+{
+
+ $sql = "SELECT
+ sorder.order_no,
+ sorder.reference,
+ debtor.name,
+ branch.br_name,"
+ .($filter=='InvoiceTemplates'
+ || $filter=='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 = ".db_escape($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($trans_no) && $trans_no != "")
+ {
+ // search orders with number like
+ $number_like = "%".$trans_no;
+ $sql .= " AND sorder.order_no LIKE ".db_escape($number_like);
+// ." GROUP BY sorder.order_no";
+ }
+ elseif ($ref != "")
+ {
+ // search orders with reference like
+ $number_like = "%".$ref."%";
+ $sql .= " AND sorder.reference LIKE ".db_escape($number_like);
+// ." GROUP BY sorder.order_no";
+ }
+ else // ... or select inquiry constraints
+ {
+ if ($filter!='DeliveryTemplates' && $filter!='InvoiceTemplates' && $filter!='OutstandingOnly')
+ {
+ $date_after = date2sql($from);
+ $date_before = date2sql($to);
+
+ $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())."' AND line.qty_sent=0"; // show only outstanding, not realized quotes
+
+ if ($selected_customer != -1)
+ $sql .= " AND sorder.debtor_no=".db_escape($selected_customer);
+
+ if (isset($stock_item))
+ $sql .= " AND line.stk_code=".db_escape($stock_item);
+
+ if ($location)
+ $sql .= " AND sorder.from_stk_loc = ".db_escape($location);
+
+ if ($filter=='OutstandingOnly')
+ $sql .= " AND line.qty_sent < line.quantity";
+
+ elseif ($filter=='InvoiceTemplates' || $filter=='DeliveryTemplates')
+ $sql .= " AND sorder.type=1";
+
+ //Chaiatanya : New Filter
+ if ($customer_id != ALL_TEXT)
+ $sql .= " AND sorder.debtor_no = ".db_escape($customer_id);
+
+ $sql .= " GROUP BY sorder.order_no,
+ sorder.debtor_no,
+ sorder.branch_code,
+ sorder.customer_ref,
+ sorder.ord_date,
+ sorder.deliver_to";
+ return $sql;
+}
+?>