Merged all main trunk bugfixes up to release 2.0.5

[fa-stable.git] / reporting / rep102.php

diff --git a/reporting/rep102.php b/reporting/rep102.php
index e3bf9d3ff795dd1176fc621881ba971106d175cf..41d6379f85ce709c77d9c868bf8ba60136b799ac 100644 (file)
--- a/reporting/rep102.php
+++ b/reporting/rep102.php
@@ -26,56 +26,16 @@ function get_invoices($costomer_id, $to)
        $PastDueDays2 = 2 * $PastDueDays1;
 
        // Revomed allocated from sql
+    $value = "(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + "
+               .TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + "
+               .TB_PREF."debtor_trans.ov_discount)";
+       $due = "IF (".TB_PREF."debtor_trans.type=10,".TB_PREF."debtor_trans.due_date,".TB_PREF."debtor_trans.tran_date)";
        $sql = "SELECT ".TB_PREF."sys_types.type_name, ".TB_PREF."debtor_trans.type, ".TB_PREF."debtor_trans.reference,
                ".TB_PREF."debtor_trans.tran_date,
-               (".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount) as Balance,
-               IF (".TB_PREF."payment_terms.days_before_due > 0,
-                       CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) >= ".TB_PREF."payment_terms.days_before_due THEN
-                               ".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount
-                       ELSE
-                               0
-                       END,
-
-                       CASE WHEN TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."debtor_trans.tran_date,
-                               INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month -
-                               DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= 0 THEN
-                                       ".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount
-                       ELSE
-                               0
-                       END
-               ) AS Due,
-               IF (".TB_PREF."payment_terms.days_before_due > 0,
-                       CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) > ".TB_PREF."payment_terms.days_before_due
-                               AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $PastDueDays1) THEN
-                                       ".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount
-                       ELSE
-                               0
-                       END,
-
-                       CASE WHEN TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."debtor_trans.tran_date,
-                               INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month -
-                               DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= $PastDueDays1 THEN
-                                       ".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount
-                       ELSE
-                               0
-                       END
-               ) AS Overdue1,
-               IF (".TB_PREF."payment_terms.days_before_due > 0,
-                       CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) > ".TB_PREF."payment_terms.days_before_due
-                               AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $PastDueDays2) THEN
-                                       ".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount
-                       ELSE
-                               0
-                       END,
-
-                       CASE WHEN TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."debtor_trans.tran_date,
-                               INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month -
-                               DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= $PastDueDays2 THEN
-                                       ".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount
-                       ELSE
-                               0
-                       END
-               ) AS Overdue2
+               $value as Balance,
+               IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= 0,$value,0) AS Due,
+               IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $PastDueDays1,$value,0) AS Overdue1,
+               IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $PastDueDays2,$value,0) AS Overdue2
 
                FROM ".TB_PREF."debtors_master,
                        ".TB_PREF."payment_terms,
@@ -88,10 +48,9 @@ function get_invoices($costomer_id, $to)
                        AND ".TB_PREF."debtors_master.debtor_no = ".TB_PREF."debtor_trans.debtor_no
                        AND ".TB_PREF."debtor_trans.debtor_no = $costomer_id
                        AND ".TB_PREF."debtor_trans.tran_date <= '$todate'
-                       AND ABS(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount) > 0.004
+                       AND ABS(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount) > 0.004
                        ORDER BY ".TB_PREF."debtor_trans.tran_date";
 
-
        return db_query($sql, "The customer details could not be retrieved");
 }