Customer Transactions, Allocations and Supplier Transactions, Allocations has got...

[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 42eaff59536c7bdcd2f5ccf4c309ec65718b714d..ea7db7da363fe2fc1f5cd07b4418df3de18cc87d 100644 (file)
--- a/sales/includes/db/custalloc_db.inc
+++ b/sales/includes/db/custalloc_db.inc
@@ -98,7 +98,7 @@ function clear_cust_alloctions($type, $type_no, $person_id=null, $date="")
 }
 //-------------------------------------------------------------------------------------------------------------
 
-function get_allocatable_from_cust_sql($customer_id=null, $settled)
+function get_allocatable_from_cust_sql($customer_id, $settled)
 {
        $sql = "SELECT
                trans.type,
@@ -112,18 +112,19 @@ function get_allocatable_from_cust_sql($customer_id=null, $settled)
                trans.due_date,
                debtor.address,
                trans.version,
-               round(abs(ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount-alloc),6) <= 0 AS settled,
+               round(abs(ov_amount)+ov_gst+ov_freight+ov_freight_tax+ov_discount-alloc,6) <= 0 AS settled,
                trans.debtor_no
 
         FROM "
                .TB_PREF."debtor_trans as trans, "
                .TB_PREF."debtors_master as debtor"
        ." WHERE trans.debtor_no=debtor.debtor_no
-               AND (((type=".ST_CUSTPAYMENT." OR type=".ST_BANKDEPOSIT.") AND (trans.ov_amount > 0))"
-               ." OR ( (type=".ST_CUSTCREDIT. " OR type=".ST_JOURNAL. ") AND (ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount)>0))";
+               AND (((type=".ST_CUSTPAYMENT." OR type=".ST_BANKDEPOSIT.") AND (trans.ov_amount > 0))
+                OR (type=".ST_CUSTCREDIT. " AND (ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount)>0)
+                OR (type=".ST_JOURNAL. " AND (trans.ov_amount < 0)))";
 
        if (!$settled)
-               $sql .= " AND (round(abs(ov_amount+ov_gst+ov_freight+ov_freight_tax-ov_discount-alloc),6) > 0)";
+               $sql .= " AND round(abs(ov_amount+ov_gst)+ov_freight+ov_freight_tax+ov_discount-alloc, 6) > 0";
 
        if ($customer_id != null)
                $sql .= " AND trans.debtor_no = ".db_escape($customer_id);
@@ -216,7 +217,7 @@ function get_allocatable_to_cust_transactions($customer_id = null, $trans_no=nul
                                        AND round(-IF(prep_amount, prep_amount, ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount)-alloc,6) > 0
                                        OR
                                        trans.type = '". ST_JOURNAL."'
-                                       AND ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount<0
+                                       AND ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount>0
                                        OR
                                        trans.type = '". ST_BANKPAYMENT."'
                                        AND ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount>0
@@ -264,7 +265,7 @@ function get_allocatable_from_cust_transactions($customer_id, $trans_no=null, $t
        }
        else
        {
-               $sql .= " AND round(ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount-alloc,6) > 0
+               $sql .= " AND round(abs(ov_amount)+ov_gst+ov_freight+ov_freight_tax+ov_discount-alloc,6) > 0
                        AND trans.type NOT IN (".implode(',',array(ST_CUSTPAYMENT,ST_BANKDEPOSIT,ST_CUSTCREDIT,ST_CUSTDELIVERY)).")";
                $sql .= " GROUP BY type, trans_no";
        }
@@ -321,14 +322,18 @@ function get_sql_for_customer_allocation_inquiry($from, $to, $customer, $filterT
                {
                        $sql .= " AND trans.type = ".ST_CUSTCREDIT." ";
                }
+               elseif ($filterType == '6')
+               {
+                       $sql .= " AND trans.type = ".ST_JOURNAL." ";
+               }
 
        if ($filterType == '2')
        {
                $today =  date2sql(Today());
                $sql .= " AND trans.due_date < '$today'
-                               AND (round(abs(trans.ov_amount + "
+                               AND (round(abs(trans.ov_amount) + "
                                ."trans.ov_gst + trans.ov_freight + "
-                               ."trans.ov_freight_tax + trans.ov_discount) - trans.alloc,6) > 0) ";
+                               ."trans.ov_freight_tax + trans.ov_discount - trans.alloc,6) > 0) ";
        }
        }
        else
@@ -339,9 +344,9 @@ function get_sql_for_customer_allocation_inquiry($from, $to, $customer, $filterT
 
        if (!$settled)
        {
-               $sql .= " AND (round(IF(trans.prep_amount,trans.prep_amount, abs(trans.ov_amount + trans.ov_gst + "
+               $sql .= " AND (ROUND(IF(trans.prep_amount,trans.prep_amount, ABS(trans.ov_amount) + trans.ov_gst + "
                ."trans.ov_freight + trans.ov_freight_tax + "
-               ."trans.ov_discount)) - trans.alloc,6) != 0) ";
+               ."trans.ov_discount) - trans.alloc,6) != 0) ";
        }
        return $sql;
 }