Further cleanups in sql statements.

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

diff --git a/sales/includes/db/customers_db.inc b/sales/includes/db/customers_db.inc
index 3f7e6598c0f939dea5cf84fa962729f449048ea6..74456aad153ac1193c0838db67ac76dcd5633a46 100644 (file)
--- a/sales/includes/db/customers_db.inc
+++ b/sales/includes/db/customers_db.inc
@@ -77,35 +77,30 @@ function get_customer_details($customer_id, $to=null, $all=true)
        $value = "IFNULL($sign*(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount - 
                trans.alloc),0)";
        $due = "IF (trans.type=10, trans.due_date, trans.tran_date)";
-    $sql = "SELECT ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."payment_terms.terms,
-               ".TB_PREF."debtors_master.credit_limit, ".TB_PREF."credit_status.dissallow_invoices, ".TB_PREF."credit_status.reason_description,
-
-               Sum(IFNULL($value,0)) AS Balance,
-               Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > 0,$value,0)) AS Due,
-               Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $past1,$value,0)) AS Overdue1,
-               Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $past2,$value,0)) AS Overdue2
-
-               FROM ".TB_PREF."debtors_master
-                        LEFT JOIN ".TB_PREF."debtor_trans trans ON 
-                        trans.tran_date <= '$todate' AND ".TB_PREF."debtors_master.debtor_no = trans.debtor_no AND trans.type <> 13
-,
-                        ".TB_PREF."payment_terms,
-                        ".TB_PREF."credit_status
-
-               WHERE
-                        ".TB_PREF."debtors_master.payment_terms = ".TB_PREF."payment_terms.terms_indicator
-                        AND ".TB_PREF."debtors_master.credit_status = ".TB_PREF."credit_status.id
-                        AND ".TB_PREF."debtors_master.debtor_no = ".db_escape($customer_id)." ";
+    $sql = "SELECT debtor.name, debtor.curr_code, terms.terms, debtor.credit_limit,
+                       credit_status.dissallow_invoices, credit_status.reason_description,
+                               Sum(IFNULL($value,0)) AS Balance,
+                               Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > 0,$value,0)) AS Due,
+                               Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $past1,$value,0)) AS Overdue1,
+                               Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $past2,$value,0)) AS Overdue2
+                       FROM ".TB_PREF."debtors_master debtor
+                                LEFT JOIN ".TB_PREF."debtor_trans trans ON trans.tran_date <= '$todate' AND debtor.debtor_no = trans.debtor_no AND trans.type <> 13,"
+                                .TB_PREF."payment_terms terms,"
+                                .TB_PREF."credit_status credit_status
+                       WHERE
+                                       debtor.payment_terms = terms.terms_indicator
+                               AND debtor.credit_status = credit_status.id
+                               AND debtor.debtor_no = ".db_escape($customer_id);
        if (!$all)
-               $sql .= "AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount - trans.alloc) > ".FLOAT_COMP_DELTA." ";  
-       $sql .= "GROUP BY
-                         ".TB_PREF."debtors_master.name,
-                         ".TB_PREF."payment_terms.terms,
-                         ".TB_PREF."payment_terms.days_before_due,
-                         ".TB_PREF."payment_terms.day_in_following_month,
-                         ".TB_PREF."debtors_master.credit_limit,
-                         ".TB_PREF."credit_status.dissallow_invoices,
-                         ".TB_PREF."credit_status.reason_description";
+               $sql .= " AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount - trans.alloc) > ".FLOAT_COMP_DELTA;
+       $sql .= " GROUP BY
+                               debtor.name,
+                               terms.terms,
+                               terms.days_before_due,
+                               terms.day_in_following_month,
+                               debtor.credit_limit,
+                               credit_status.dissallow_invoices,
+                               credit_status.reason_description";
     $result = db_query($sql,"The customer details could not be retrieved");
 
     $customer_record = db_fetch($result);
@@ -137,11 +132,11 @@ function get_customer_name($customer_id)
 
 function get_customer_habit($customer_id)
 {
-       $sql = "SELECT ".TB_PREF."debtors_master.pymt_discount,
-               ".TB_PREF."credit_status.dissallow_invoices
-               FROM ".TB_PREF."debtors_master, ".TB_PREF."credit_status
-               WHERE ".TB_PREF."debtors_master.credit_status = ".TB_PREF."credit_status.id
-                       AND ".TB_PREF."debtors_master.debtor_no = ".db_escape($customer_id);
+       $sql = "SELECT debtor.pymt_discount, credit_status.dissallow_invoices
+                       FROM ".TB_PREF."debtors_master debtor,"
+                               .TB_PREF."credit_status credit_status
+                       WHERE debtor.credit_status = credit_status.id
+                       AND debtor.debtor_no = ".db_escape($customer_id);
 
        $result = db_query($sql, "could not query customers");
 
@@ -189,7 +184,8 @@ function get_customer_by_ref($reference)
 
 function get_customer_currency($customer_id=null, $branch_id=null)
 {
-    $sql = "SELECT curr_code FROM ".TB_PREF."debtors_master cust
+    $sql = "SELECT curr_code
+       FROM ".TB_PREF."debtors_master cust
                LEFT JOIN ".TB_PREF."cust_branch branch ON branch.debtor_no=cust.debtor_no
        WHERE " .(isset($branch_id) ? "branch_code = ".db_escape($branch_id) : "cust.debtor_no = ".db_escape($customer_id));