3 //----------------------------------------------------------------------------------------
5 // $Total, $Tax, $Freight, $discount all in customer's currency
6 // date_ is display date (non-sql)
8 function add_customer_trans($trans_type, $debtor_no, $BranchNo,
9 $date_, $reference, $Total, $discount=0, $Tax=0, $Freight=0,
10 $sales_type=0, $order_no=0, $ship_via="", $due_date=null,
13 $trans_no = get_next_trans_no($trans_type);
15 $curr = get_customer_currency($debtor_no);
16 $rate = get_exchange_rate_from_home_currency($curr, $date_);
18 $SQLDate = date2sql($date_);
19 $SQLDueDate = date2sql($due_date);
21 $sql = "INSERT INTO ".TB_PREF."debtor_trans (
23 debtor_no, branch_code,
26 order_, ov_amount, ov_discount,
27 ov_gst, ov_freight, rate,
29 VALUES ($trans_no, $trans_type, '$debtor_no', '$BranchNo',
30 '$SQLDate', '$SQLDueDate', '$reference',
31 '$sales_type', $order_no, $Total, $discount, $Tax, $Freight, $rate,
32 '$ship_via', $AllocAmt)";
34 db_query($sql, "The debtor transaction record could not be inserted");
39 //----------------------------------------------------------------------------------------
41 function get_customer_trans($trans_id, $trans_type)
43 $sql = "SELECT ".TB_PREF."debtor_trans.*,
44 ov_amount+ov_gst+ov_freight+ov_discount AS Total,
45 ".TB_PREF."debtors_master.name AS DebtorName, ".TB_PREF."debtors_master.address, ".TB_PREF."debtors_master.email,
46 ".TB_PREF."debtors_master.curr_code, ".TB_PREF."debtors_master.tax_id, ".TB_PREF."debtors_master.payment_terms ";
48 if ($trans_type == systypes::cust_payment())
50 // it's a payment so also get the bank account
51 $sql .= ", ".TB_PREF."bank_accounts.bank_name, ".TB_PREF."bank_accounts.bank_account_name,
52 ".TB_PREF."bank_trans_types.name AS BankTransType ";
55 if ($trans_type == 10 OR $trans_type == 11)
57 // it's an invoice so also get the shipper and salestype
58 $sql .= ", ".TB_PREF."shippers.shipper_name, ".TB_PREF."sales_types.sales_type ";
62 $sql .= " FROM ".TB_PREF."debtor_trans, ".TB_PREF."debtors_master ";
64 if ($trans_type == systypes::cust_payment())
66 // it's a payment so also get the bank account
67 $sql .= ", ".TB_PREF."bank_trans, ".TB_PREF."bank_accounts, ".TB_PREF."bank_trans_types ";
70 if ($trans_type == 10 OR $trans_type == 11)
72 // it's an invoice so also get the shipper, salestypes
73 $sql .= ", ".TB_PREF."shippers, ".TB_PREF."sales_types ";
76 $sql .= " WHERE ".TB_PREF."debtor_trans.trans_no=$trans_id
77 AND ".TB_PREF."debtor_trans.type=$trans_type
78 AND ".TB_PREF."debtor_trans.debtor_no=".TB_PREF."debtors_master.debtor_no";
80 if ($trans_type == systypes::cust_payment())
82 // it's a payment so also get the bank account
83 $sql .= " AND ".TB_PREF."bank_trans.trans_no =$trans_id
84 AND ".TB_PREF."bank_trans.type=$trans_type
85 AND ".TB_PREF."bank_trans_types.id = ".TB_PREF."bank_trans.bank_trans_type_id
86 AND ".TB_PREF."bank_accounts.account_code=".TB_PREF."bank_trans.bank_act ";
88 if ($trans_type == 10 OR $trans_type == 11)
90 // it's an invoice so also get the shipper
91 $sql .= " AND ".TB_PREF."shippers.shipper_id=".TB_PREF."debtor_trans.ship_via
92 AND ".TB_PREF."sales_types.id = ".TB_PREF."debtor_trans.tpe ";
95 $result = db_query($sql, "Cannot retreive a debtor transaction");
97 if (db_num_rows($result) == 0)
99 // can't return nothing
100 display_db_error("no debtor trans found for given params", $sql, true);
104 if (db_num_rows($result) > 1)
106 // can't return multiple
107 display_db_error("duplicate debtor transactions found for given params", $sql, true);
111 return db_fetch($result);
114 //----------------------------------------------------------------------------------------
116 function get_customer_transactions($extra_fields=null, $extra_conditions=null, $extra_tables=null)
118 $sql = "SELECT ".TB_PREF."debtor_trans.*,
119 ov_amount+ov_gst+ov_freight+ov_discount AS Total,
120 ".TB_PREF."debtors_master.name AS DebtorName, ".TB_PREF."debtors_master.address,
121 ".TB_PREF."debtors_master.curr_code ";
124 $sql .= ", $extra_fields ";
126 $sql .= " FROM ".TB_PREF."debtor_trans, ".TB_PREF."debtors_master ";
128 $sql .= ",$extra_tables ";
130 $sql .= " WHERE ".TB_PREF."debtor_trans.debtor_no=".TB_PREF."debtors_master.debtor_no";
132 if ($extra_conditions)
133 $sql .= " AND $extra_conditions ";
135 $sql .= " ORDER BY trans_no";
137 return db_query($sql, "Cannot retreive debtor transactions");
140 //----------------------------------------------------------------------------------------
142 function exists_customer_trans($type, $type_no)
144 $sql = "SELECT trans_no FROM ".TB_PREF."debtor_trans WHERE type=$type
145 AND trans_no=$type_no";
146 $result = db_query($sql, "Cannot retreive a debtor transaction");
148 return (db_num_rows($result) > 0);
151 //----------------------------------------------------------------------------------------
153 // retreives the related sales order for a given trans
155 function get_customer_trans_order($type, $type_no)
157 $sql = "SELECT order_ FROM ".TB_PREF."debtor_trans WHERE type=$type AND trans_no=$type_no";
159 $result = db_query($sql, "The debtor transaction could not be queried");
161 $row = db_fetch_row($result);
166 //----------------------------------------------------------------------------------------
168 function get_customer_details_from_trans($type, $type_no)
170 $sql = "SELECT ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."cust_branch.br_name
171 FROM ".TB_PREF."debtors_master,".TB_PREF."cust_branch,".TB_PREF."debtor_trans
172 WHERE ".TB_PREF."debtor_trans.type=$type AND ".TB_PREF."debtor_trans.trans_no=$type_no
173 AND ".TB_PREF."debtors_master.debtor_no = ".TB_PREF."debtor_trans.debtor_no
174 AND ".TB_PREF."cust_branch.branch_code = ".TB_PREF."debtor_trans.branch_code";
176 $result = db_query($sql, "could not get customer details from trans");
177 return db_fetch($result);
180 //----------------------------------------------------------------------------------------
182 function void_customer_trans($type, $type_no)
184 // clear all values and mark as void
185 $sql = "UPDATE ".TB_PREF."debtor_trans SET ov_amount=0, ov_discount=0, ov_gst=0, ov_freight=0,
186 alloc=0 WHERE type=$type AND trans_no=$type_no";
188 db_query($sql, "could not void debtor transactions for type=$type and trans_no=$type_no");
191 //----------------------------------------------------------------------------------------
193 function post_void_customer_trans($type, $type_no)
199 void_sales_invoice($type, $type_no);
201 case systypes::cust_payment() :
202 void_customer_payment($type, $type_no);
207 //----------------------------------------------------------------------------------------