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

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

diff --git a/sales/includes/db/custalloc_db.inc b/sales/includes/db/custalloc_db.inc
index 618c9397ca61bbdba6879d0faba20367d04727e3..5b7782aecafc5477fa9b71825eb26a7055ff37b3 100644 (file)
--- a/sales/includes/db/custalloc_db.inc
+++ b/sales/includes/db/custalloc_db.inc
@@ -171,5 +171,73 @@ function get_allocatable_to_cust_transactions($customer_id, $trans_no=null, $typ
        return db_query($sql." ORDER BY trans_no", "Cannot retreive alloc to transactions");
 }
 
+function get_sql_for_customer_allocation_inquiry()
+{
+       $data_after = date2sql($_POST['TransAfterDate']);
+       $date_to = date2sql($_POST['TransToDate']);
 
+       $sql = "SELECT 
+               trans.type,
+               trans.trans_no,
+               trans.reference,
+               trans.order_,
+               trans.tran_date,
+               trans.due_date,
+               debtor.name,
+               debtor.curr_code,
+       (trans.ov_amount + trans.ov_gst + trans.ov_freight 
+                       + trans.ov_freight_tax + trans.ov_discount)     AS TotalAmount,
+               trans.alloc AS Allocated,
+               ((trans.type = ".ST_SALESINVOICE.")
+                       AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue
+       FROM "
+                       .TB_PREF."debtor_trans as trans, "
+                       .TB_PREF."debtors_master as debtor
+       WHERE debtor.debtor_no = trans.debtor_no
+                       AND (trans.ov_amount + trans.ov_gst + trans.ov_freight 
+                               + trans.ov_freight_tax + trans.ov_discount != 0)
+               AND trans.tran_date >= '$data_after'
+               AND trans.tran_date <= '$date_to'";
+
+       if ($_POST['customer_id'] != ALL_TEXT)
+               $sql .= " AND trans.debtor_no = ".db_escape($_POST['customer_id']);
+
+       if (isset($_POST['filterType']) && $_POST['filterType'] != ALL_TEXT)
+       {
+               if ($_POST['filterType'] == '1' || $_POST['filterType'] == '2')
+               {
+                       $sql .= " AND trans.type = ".ST_SALESINVOICE." ";
+               }
+               elseif ($_POST['filterType'] == '3')
+               {
+                       $sql .= " AND trans.type = " . ST_CUSTPAYMENT;
+               }
+               elseif ($_POST['filterType'] == '4')
+               {
+                       $sql .= " AND trans.type = ".ST_CUSTCREDIT." ";
+               }
+
+       if ($_POST['filterType'] == '2')
+       {
+               $today =  date2sql(Today());
+               $sql .= " AND trans.due_date < '$today'
+                               AND (round(abs(trans.ov_amount + "
+                               ."trans.ov_gst + trans.ov_freight + "
+                               ."trans.ov_freight_tax + trans.ov_discount) - trans.alloc,6) > 0) ";
+       }
+       }
+       else
+       {
+           $sql .= " AND trans.type <> ".ST_CUSTDELIVERY." ";
+       }
+
+
+       if (!check_value('showSettled'))
+       {
+               $sql .= " AND (round(abs(trans.ov_amount + trans.ov_gst + "
+               ."trans.ov_freight + trans.ov_freight_tax + "
+               ."trans.ov_discount) - trans.alloc,6) != 0) ";
+       }
+       return $sql;
+}
 ?>
\ No newline at end of file