3 function get_customer_details($customer_id, $to=null)
7 $todate = date("Y-m-d");
9 $todate = date2sql($to);
10 $past1 = get_company_pref('past_due_days');
12 // removed - debtor_trans.alloc from all summations
14 $sql = "SELECT ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."payment_terms.terms,
15 ".TB_PREF."debtors_master.credit_limit, ".TB_PREF."credit_status.dissallow_invoices, ".TB_PREF."credit_status.reason_description,
17 Sum(".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,
19 Sum(IF (".TB_PREF."payment_terms.days_before_due > 0,
20 CASE WHEN (TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date)) >= ".TB_PREF."payment_terms.days_before_due
22 ".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount
25 CASE WHEN TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."debtor_trans.tran_date,
26 INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month -
27 DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= 0
29 ".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount
36 Sum(IF (".TB_PREF."payment_terms.days_before_due > 0,
37 CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) > ".TB_PREF."payment_terms.days_before_due
38 AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $past1)
40 ".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount
45 CASE WHEN (TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."debtor_trans.tran_date,
46 INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month -
47 DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= $past1)
49 ".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount
56 Sum(IF (".TB_PREF."payment_terms.days_before_due > 0,
57 CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) > ".TB_PREF."payment_terms.days_before_due
58 AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $past2)
60 ".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount
65 CASE WHEN (TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."debtor_trans.tran_date,
66 INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month -
67 DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= $past2)
69 ".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount
76 FROM ".TB_PREF."debtors_master,
77 ".TB_PREF."payment_terms,
78 ".TB_PREF."credit_status,
79 ".TB_PREF."debtor_trans
82 ".TB_PREF."debtors_master.payment_terms = ".TB_PREF."payment_terms.terms_indicator
83 AND ".TB_PREF."debtors_master.credit_status = ".TB_PREF."credit_status.id
84 AND ".TB_PREF."debtors_master.debtor_no = $customer_id
85 AND ".TB_PREF."debtor_trans.tran_date <= '$todate'
86 AND ".TB_PREF."debtors_master.debtor_no = ".TB_PREF."debtor_trans.debtor_no
89 ".TB_PREF."debtors_master.name,
90 ".TB_PREF."payment_terms.terms,
91 ".TB_PREF."payment_terms.days_before_due,
92 ".TB_PREF."payment_terms.day_in_following_month,
93 ".TB_PREF."debtors_master.credit_limit,
94 ".TB_PREF."credit_status.dissallow_invoices,
95 ".TB_PREF."credit_status.reason_description";
97 $result = db_query($sql,"The customer details could not be retrieved");
99 if (db_num_rows($result) == 0)
102 /*Because there is no balance - so just retrieve the header information about the customer - the choice is do one query to get the balance and transactions for those customers who have a balance and two queries for those who don't have a balance OR always do two queries - I opted for the former */
106 $sql = "SELECT ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."debtors_master.debtor_no, ".TB_PREF."payment_terms.terms,
107 ".TB_PREF."debtors_master.credit_limit, ".TB_PREF."credit_status.dissallow_invoices, ".TB_PREF."credit_status.reason_description
108 FROM ".TB_PREF."debtors_master,
109 ".TB_PREF."payment_terms,
110 ".TB_PREF."credit_status
113 ".TB_PREF."debtors_master.payment_terms = ".TB_PREF."payment_terms.terms_indicator
114 AND ".TB_PREF."debtors_master.credit_status = ".TB_PREF."credit_status.id
115 AND ".TB_PREF."debtors_master.debtor_no = '$customer_id'";
117 $result = db_query($sql,"The customer details could not be retrieved");
122 $nil_balance = false;
125 $customer_record = db_fetch($result);
127 if ($nil_balance == true)
129 $customer_record["Balance"] = 0;
130 $customer_record["Due"] = 0;
131 $customer_record["Overdue1"] = 0;
132 $customer_record["Overdue2"] = 0;
135 return $customer_record;
139 function get_customer_name($customer_id)
141 $sql = "SELECT name FROM ".TB_PREF."debtors_master WHERE debtor_no=$customer_id";
143 $result = db_query($sql, "could not get customer");
145 $row = db_fetch_row($result);
150 function get_area_name($id)
152 $sql = "SELECT description FROM ".TB_PREF."areas WHERE area_code=$id";
154 $result = db_query($sql, "could not get sales type");
156 $row = db_fetch_row($result);
160 function get_salesman_name($id)
162 $sql = "SELECT salesman_name FROM ".TB_PREF."salesman WHERE salesman_code=$id";
164 $result = db_query($sql, "could not get sales type");
166 $row = db_fetch_row($result);