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 $value = "IF(".TB_PREF."debtor_trans.type=11 OR ".TB_PREF."debtor_trans.type=12,
16 "(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + "
17 .TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + "
18 .TB_PREF."debtor_trans.ov_discount)";
20 $sql = "SELECT ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."payment_terms.terms,
21 ".TB_PREF."debtors_master.credit_limit, ".TB_PREF."credit_status.dissallow_invoices, ".TB_PREF."credit_status.reason_description,
23 Sum(".$value.") AS Balance,
25 Sum(IF (".TB_PREF."payment_terms.days_before_due > 0,
26 CASE WHEN (TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date)) >= ".TB_PREF."payment_terms.days_before_due
32 CASE WHEN TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."debtor_trans.tran_date,
33 INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month -
34 DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= 0
42 Sum(IF (".TB_PREF."payment_terms.days_before_due > 0,
43 CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) > ".TB_PREF."payment_terms.days_before_due
44 AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $past1)
51 CASE WHEN (TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."debtor_trans.tran_date,
52 INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month -
53 DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= $past1)
62 Sum(IF (".TB_PREF."payment_terms.days_before_due > 0,
63 CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) > ".TB_PREF."payment_terms.days_before_due
64 AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $past2)
71 CASE WHEN (TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."debtor_trans.tran_date,
72 INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month -
73 DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= $past2)
82 FROM ".TB_PREF."debtors_master,
83 ".TB_PREF."payment_terms,
84 ".TB_PREF."credit_status,
85 ".TB_PREF."debtor_trans
88 ".TB_PREF."debtors_master.payment_terms = ".TB_PREF."payment_terms.terms_indicator
89 AND ".TB_PREF."debtors_master.credit_status = ".TB_PREF."credit_status.id
90 AND ".TB_PREF."debtors_master.debtor_no = $customer_id
91 AND ".TB_PREF."debtor_trans.tran_date <= '$todate'
92 AND ".TB_PREF."debtor_trans.type <> 13
93 AND ".TB_PREF."debtors_master.debtor_no = ".TB_PREF."debtor_trans.debtor_no
96 ".TB_PREF."debtors_master.name,
97 ".TB_PREF."payment_terms.terms,
98 ".TB_PREF."payment_terms.days_before_due,
99 ".TB_PREF."payment_terms.day_in_following_month,
100 ".TB_PREF."debtors_master.credit_limit,
101 ".TB_PREF."credit_status.dissallow_invoices,
102 ".TB_PREF."credit_status.reason_description";
104 $result = db_query($sql,"The customer details could not be retrieved");
106 if (db_num_rows($result) == 0)
109 /*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 */
113 $sql = "SELECT ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."debtors_master.debtor_no, ".TB_PREF."payment_terms.terms,
114 ".TB_PREF."debtors_master.credit_limit, ".TB_PREF."credit_status.dissallow_invoices, ".TB_PREF."credit_status.reason_description
115 FROM ".TB_PREF."debtors_master,
116 ".TB_PREF."payment_terms,
117 ".TB_PREF."credit_status
120 ".TB_PREF."debtors_master.payment_terms = ".TB_PREF."payment_terms.terms_indicator
121 AND ".TB_PREF."debtors_master.credit_status = ".TB_PREF."credit_status.id
122 AND ".TB_PREF."debtors_master.debtor_no = '$customer_id'";
124 $result = db_query($sql,"The customer details could not be retrieved");
129 $nil_balance = false;
132 $customer_record = db_fetch($result);
134 if ($nil_balance == true)
136 $customer_record["Balance"] = 0;
137 $customer_record["Due"] = 0;
138 $customer_record["Overdue1"] = 0;
139 $customer_record["Overdue2"] = 0;
142 return $customer_record;
146 function get_customer_name($customer_id)
148 $sql = "SELECT name FROM ".TB_PREF."debtors_master WHERE debtor_no=$customer_id";
150 $result = db_query($sql, "could not get customer");
152 $row = db_fetch_row($result);
157 function get_area_name($id)
159 $sql = "SELECT description FROM ".TB_PREF."areas WHERE area_code=$id";
161 $result = db_query($sql, "could not get sales type");
163 $row = db_fetch_row($result);
167 function get_salesman_name($id)
169 $sql = "SELECT salesman_name FROM ".TB_PREF."salesman WHERE salesman_code=$id";
171 $result = db_query($sql, "could not get sales type");
173 $row = db_fetch_row($result);