Moved all SQL statements from PHP files into relevant *_db.inc files.

[fa-stable.git] / sales / includes / db / sales_order_db.inc

diff --git a/sales/includes/db/sales_order_db.inc b/sales/includes/db/sales_order_db.inc
index 0d8c618aaae685396a96ceb2c3951214febd3256..299240c9148e34875623788cdc44092d5dfcfbd0 100644 (file)
--- a/sales/includes/db/sales_order_db.inc
+++ b/sales/includes/db/sales_order_db.inc
@@ -474,4 +474,78 @@ function get_branch_to_order($customer_id, $branch_id) {
 
            return db_query($sql,"Customer Branch Record Retreive");
 }
+
+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;
+}
 ?>
\ No newline at end of file