3 function get_supplier_details($supplier_id, $to=null)
7 $todate = date("Y-m-d");
9 $todate = date2sql($to);
10 $past1 = get_company_pref('past_due_days');
12 // removed - supp_trans.alloc from all summations
14 $sql = "SELECT ".TB_PREF."suppliers.supp_name, ".TB_PREF."suppliers.curr_code, ".TB_PREF."payment_terms.terms,
16 Sum(".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount) AS Balance,
18 Sum(IF (".TB_PREF."payment_terms.days_before_due > 0,
19 CASE WHEN (TO_DAYS('$todate') - TO_DAYS(".TB_PREF."supp_trans.tran_date)) >= ".TB_PREF."payment_terms.days_before_due
21 ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount
26 CASE WHEN TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."supp_trans.tran_date,
27 INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month -
28 DAYOFMONTH(".TB_PREF."supp_trans.tran_date)) DAY)) >= 0
30 ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount
37 Sum(IF (".TB_PREF."payment_terms.days_before_due > 0,
38 CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."supp_trans.tran_date) > ".TB_PREF."payment_terms.days_before_due
39 AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."supp_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $past1)
41 ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount
46 CASE WHEN (TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."supp_trans.tran_date,
47 INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month -
48 DAYOFMONTH(".TB_PREF."supp_trans.tran_date)) DAY)) >= $past1)
50 ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount
57 Sum(IF (".TB_PREF."payment_terms.days_before_due > 0,
58 CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."supp_trans.tran_date) > ".TB_PREF."payment_terms.days_before_due
59 AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."supp_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $past2)
61 ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount
66 CASE WHEN (TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."supp_trans.tran_date,
67 INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month -
68 DAYOFMONTH(".TB_PREF."supp_trans.tran_date)) DAY)) >= $past2)
70 ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount
77 FROM ".TB_PREF."suppliers,
78 ".TB_PREF."payment_terms,
82 ".TB_PREF."suppliers.payment_terms = ".TB_PREF."payment_terms.terms_indicator
83 AND ".TB_PREF."suppliers.supplier_id = $supplier_id
84 AND ".TB_PREF."supp_trans.tran_date <= '$todate'
85 AND ".TB_PREF."suppliers.supplier_id = ".TB_PREF."supp_trans.supplier_id
88 ".TB_PREF."suppliers.supp_name,
89 ".TB_PREF."payment_terms.terms,
90 ".TB_PREF."payment_terms.days_before_due,
91 ".TB_PREF."payment_terms.day_in_following_month";
93 $result = db_query($sql,"The customer details could not be retrieved");
95 if (db_num_rows($result) == 0)
98 /*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 */
102 $sql = "SELECT ".TB_PREF."suppliers.supp_name, ".TB_PREF."suppliers.curr_code, ".TB_PREF."suppliers.supplier_id, ".TB_PREF."payment_terms.terms
103 FROM ".TB_PREF."suppliers,
104 ".TB_PREF."payment_terms
106 ".TB_PREF."suppliers.payment_terms = ".TB_PREF."payment_terms.terms_indicator
107 AND ".TB_PREF."suppliers.supplier_id = '$supplier_id'";
109 $result = db_query($sql,"The customer details could not be retrieved");
114 $nil_balance = false;
117 $supp = db_fetch($result);
119 if ($nil_balance == true)
121 $supp["Balance"] = 0;
123 $supp["Overdue1"] = 0;
124 $supp["Overdue2"] = 0;
131 function get_supplier($supplier_id)
133 $sql = "SELECT * FROM ".TB_PREF."suppliers WHERE supplier_id=$supplier_id";
135 $result = db_query($sql, "could not get supplier");
137 return db_fetch($result);
140 function get_supplier_name($supplier_id)
142 $sql = "SELECT supp_name AS name FROM ".TB_PREF."suppliers WHERE supplier_id=$supplier_id";
144 $result = db_query($sql, "could not get supplier");
146 $row = db_fetch_row($result);
151 function get_supplier_accounts($supplier_id)
153 $sql = "SELECT payable_account,purchase_account,payment_discount_account FROM ".TB_PREF."suppliers WHERE supplier_id=$supplier_id";
155 $result = db_query($sql, "could not get supplier");
157 return db_fetch($result);