2 /**********************************************************************
3 Copyright (C) FrontAccounting, LLC.
4 Released under the terms of the GNU General Public License, GPL,
5 as published by the Free Software Foundation, either version 3
6 of the License, or (at your option) any later version.
7 This program is distributed in the hope that it will be useful,
8 but WITHOUT ANY WARRANTY; without even the implied warranty of
9 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
10 See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
11 ***********************************************************************/
12 function get_customer_details($customer_id, $to=null)
16 $todate = date("Y-m-d");
18 $todate = date2sql($to);
19 $past1 = get_company_pref('past_due_days');
21 // removed - debtor_trans.alloc from all summations
23 $value = "IF(".TB_PREF."debtor_trans.type=11 OR ".TB_PREF."debtor_trans.type=12 OR ".TB_PREF."debtor_trans.type=2,
25 "(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + "
26 .TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + "
27 .TB_PREF."debtor_trans.ov_discount)";
28 $due = "IF (".TB_PREF."debtor_trans.type=10,".TB_PREF."debtor_trans.due_date,".TB_PREF."debtor_trans.tran_date)";
29 $sql = "SELECT ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."payment_terms.terms,
30 ".TB_PREF."debtors_master.credit_limit, ".TB_PREF."credit_status.dissallow_invoices, ".TB_PREF."credit_status.reason_description,
32 Sum(".$value.") AS Balance,
34 Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= 0,$value,0)) AS Due,
35 Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $past1,$value,0)) AS Overdue1,
36 Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $past2,$value,0)) AS Overdue2
38 FROM ".TB_PREF."debtors_master,
39 ".TB_PREF."payment_terms,
40 ".TB_PREF."credit_status,
41 ".TB_PREF."debtor_trans
44 ".TB_PREF."debtors_master.payment_terms = ".TB_PREF."payment_terms.terms_indicator
45 AND ".TB_PREF."debtors_master.credit_status = ".TB_PREF."credit_status.id
46 AND ".TB_PREF."debtors_master.debtor_no = $customer_id
47 AND ".TB_PREF."debtor_trans.tran_date <= '$todate'
48 AND ".TB_PREF."debtor_trans.type <> 13
49 AND ".TB_PREF."debtors_master.debtor_no = ".TB_PREF."debtor_trans.debtor_no
52 ".TB_PREF."debtors_master.name,
53 ".TB_PREF."payment_terms.terms,
54 ".TB_PREF."payment_terms.days_before_due,
55 ".TB_PREF."payment_terms.day_in_following_month,
56 ".TB_PREF."debtors_master.credit_limit,
57 ".TB_PREF."credit_status.dissallow_invoices,
58 ".TB_PREF."credit_status.reason_description";
59 $result = db_query($sql,"The customer details could not be retrieved");
61 if (db_num_rows($result) == 0)
64 /*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 */
68 $sql = "SELECT ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."debtors_master.debtor_no, ".TB_PREF."payment_terms.terms,
69 ".TB_PREF."debtors_master.credit_limit, ".TB_PREF."credit_status.dissallow_invoices, ".TB_PREF."credit_status.reason_description
70 FROM ".TB_PREF."debtors_master,
71 ".TB_PREF."payment_terms,
72 ".TB_PREF."credit_status
75 ".TB_PREF."debtors_master.payment_terms = ".TB_PREF."payment_terms.terms_indicator
76 AND ".TB_PREF."debtors_master.credit_status = ".TB_PREF."credit_status.id
77 AND ".TB_PREF."debtors_master.debtor_no = '$customer_id'";
79 $result = db_query($sql,"The customer details could not be retrieved");
87 $customer_record = db_fetch($result);
89 if ($nil_balance == true)
91 $customer_record["Balance"] = 0;
92 $customer_record["Due"] = 0;
93 $customer_record["Overdue1"] = 0;
94 $customer_record["Overdue2"] = 0;
97 return $customer_record;
101 function get_customer($customer_id)
103 $sql = "SELECT * FROM ".TB_PREF."debtors_master WHERE debtor_no=$customer_id";
105 $result = db_query($sql, "could not get customer");
107 return db_fetch($result);
110 function get_customer_name($customer_id)
112 $sql = "SELECT name FROM ".TB_PREF."debtors_master WHERE debtor_no=$customer_id";
114 $result = db_query($sql, "could not get customer");
116 $row = db_fetch_row($result);
121 function get_area_name($id)
123 $sql = "SELECT description FROM ".TB_PREF."areas WHERE area_code=$id";
125 $result = db_query($sql, "could not get sales type");
127 $row = db_fetch_row($result);
131 function get_salesman_name($id)
133 $sql = "SELECT salesman_name FROM ".TB_PREF."salesman WHERE salesman_code=$id";
135 $result = db_query($sql, "could not get sales type");
137 $row = db_fetch_row($result);