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 //----------------------------------------------------------------------------------------
14 function add_cust_allocation($amount, $trans_type_from, $trans_no_from,
15 $trans_type_to, $trans_no_to)
17 $sql = "INSERT INTO ".TB_PREF."cust_allocations (
19 trans_type_from, trans_no_from, trans_no_to, trans_type_to)
20 VALUES ($amount, Now(), ".db_escape($trans_type_from).", ".db_escape($trans_no_from).", ".db_escape($trans_no_to)
21 .", ".db_escape($trans_type_to).")";
23 db_query($sql, "A customer allocation could not be added to the database");
26 //----------------------------------------------------------------------------------------
29 function delete_cust_allocation($trans_id)
31 $sql = "DELETE FROM ".TB_PREF."cust_allocations WHERE id = ".db_escape($trans_id);
32 return db_query($sql, "The existing allocation $trans_id could not be deleted");
35 //----------------------------------------------------------------------------------------
36 // Update debtor trans alloc field according to current status of cust_allocations
38 function update_debtor_trans_allocation($trans_type, $trans_no)
40 $sql = "UPDATE `".TB_PREF."debtor_trans` trans,
41 (SELECT sum(amt) amt from ".TB_PREF."cust_allocations
42 WHERE (trans_type_to=".db_escape($trans_type)." AND trans_no_to=".db_escape($trans_no).")
43 OR (trans_type_from=".db_escape($trans_type)." AND trans_no_from=".db_escape($trans_no).")) allocated
45 trans.alloc=IFNULL(allocated.amt,0)
46 WHERE trans.type=".db_escape($trans_type)." AND trans_no=".db_escape($trans_no);
48 db_query($sql, "The debtor transaction record could not be modified for the allocation against it");
51 //-------------------------------------------------------------------------------------------------------------
53 function void_cust_allocations($type, $type_no, $date="")
55 return clear_cust_alloctions($type, $type_no, $date);
58 //-------------------------------------------------------------------------------------------------------------
60 function clear_cust_alloctions($type, $type_no, $date="")
62 // clear any allocations for this transaction
63 $sql = "SELECT * FROM ".TB_PREF."cust_allocations
64 WHERE (trans_type_from=".db_escape($type)." AND trans_no_from=".db_escape($type_no).")
65 OR (trans_type_to=".db_escape($type)." AND trans_no_to=".db_escape($type_no).")";
66 $result = db_query($sql, "could not void debtor transactions for type=$type and trans_no=$type_no");
68 while ($row = db_fetch($result))
70 $sql = "UPDATE ".TB_PREF."debtor_trans SET alloc=alloc - " . $row['amt'] . "
71 WHERE (type= " . $row['trans_type_from'] . " AND trans_no=" . $row['trans_no_from'] . ")
72 OR (type=" . $row['trans_type_to'] . " AND trans_no=" . $row['trans_no_to'] . ")";
73 db_query($sql, "could not clear allocation");
74 // 2008-09-20 Joe Hunt
76 exchange_variation($type, $type_no, $row['trans_type_to'], $row['trans_no_to'], $date,
77 $row['amt'], PT_CUSTOMER, true);
78 // 2014-01-16 Joe Hunt. Fix for voiding allocated currency sales_invoices.
79 elseif ($type == ST_SALESINVOICE && $row['trans_type_to'] == $type && $row['trans_no_to'] == $type_no)
80 exchange_variation($row['trans_type_from'], $row['trans_no_from'], $row['trans_type_to'], $row['trans_no_to'],
81 sql2date($row['date_alloc']), $row['amt'], PT_CUSTOMER, true);
82 //////////////////////
86 // remove any allocations for this transaction
87 $sql = "DELETE FROM ".TB_PREF."cust_allocations
88 WHERE (trans_type_from=".db_escape($type)." AND trans_no_from=".db_escape($type_no).")
89 OR (trans_type_to=".db_escape($type)." AND trans_no_to=".db_escape($type_no).")";
91 db_query($sql, "could not void debtor transactions for type=$type and trans_no=$type_no");
93 //----------------------------------------------------------------------------------------
95 function get_alloc_trans_sql($extra_fields=null, $extra_conditions=null, $extra_tables=null)
102 debtor.name AS DebtorName,
104 ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount AS Total,
112 $sql .= ", $extra_fields ";
114 $sql .= " FROM ".TB_PREF."debtor_trans as trans, "
115 .TB_PREF."debtors_master as debtor";
117 $sql .= ",$extra_tables ";
119 $sql .= " WHERE trans.debtor_no=debtor.debtor_no";
121 if ($extra_conditions)
122 $sql .= " AND $extra_conditions ";
128 //-------------------------------------------------------------------------------------------------------------
130 function get_allocatable_from_cust_sql($customer_id, $settled)
135 $settled_sql = " AND (round(ov_amount+ov_gst+ov_freight+ov_freight_tax-ov_discount-alloc,6) > 0)";
138 if ($customer_id != null)
139 $cust_sql = " AND trans.debtor_no = ".db_escape($customer_id);
141 $sql = get_alloc_trans_sql("round(ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount-alloc,6) <= 0 AS settled",
142 "(type=".ST_CUSTPAYMENT." OR type=".ST_CUSTCREDIT." OR type=".ST_BANKDEPOSIT.") AND (trans.ov_amount > 0) " . $settled_sql . $cust_sql);
147 //-------------------------------------------------------------------------------------------------------------
149 function get_allocatable_to_cust_transactions($customer_id, $trans_no=null, $type=null)
151 if ($trans_no != null and $type != null)
153 $sql = get_alloc_trans_sql("amt", "trans.trans_no = alloc.trans_no_to
154 AND trans.type = alloc.trans_type_to
155 AND alloc.trans_no_from=".db_escape($trans_no)."
156 AND alloc.trans_type_from=".db_escape($type)."
157 AND trans.debtor_no=".db_escape($customer_id),
158 "".TB_PREF."cust_allocations as alloc");
162 $sql = get_alloc_trans_sql(null, "round(ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount-alloc,6) > 0
163 AND trans.type <> " . ST_CUSTPAYMENT . "
164 AND trans.type <> " . ST_BANKDEPOSIT . "
165 AND trans.type <> " . ST_CUSTCREDIT . "
166 AND trans.type <> " . ST_CUSTDELIVERY . "
167 AND trans.debtor_no=".db_escape($customer_id));
170 return db_query($sql." ORDER BY trans_no", "Cannot retreive alloc to transactions");
173 //-------------------------------------------------------------------------------------------------------------
175 function get_allocatable_from_cust_transactions($customer_id, $trans_no=null, $type=null)
177 if ($trans_no != null and $type != null)
179 $sql = get_alloc_trans_sql("amt", "trans.trans_no = alloc.trans_no_from
180 AND trans.type = alloc.trans_type_from
181 AND alloc.trans_no_to=".db_escape($trans_no)."
182 AND alloc.trans_type_to=".db_escape($type)."
183 AND trans.debtor_no=".db_escape($customer_id),
184 "".TB_PREF."cust_allocations as alloc");
188 $sql = get_alloc_trans_sql(null, "round(ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount-alloc,6) > 0
189 AND trans.type <> " . ST_CUSTPAYMENT . "
190 AND trans.type <> " . ST_BANKDEPOSIT . "
191 AND trans.type <> " . ST_CUSTCREDIT . "
192 AND trans.type <> " . ST_CUSTDELIVERY . "
193 AND trans.debtor_no=".db_escape($customer_id));
196 return db_query($sql." ORDER BY trans_no", "Cannot retreive alloc to transactions");
199 function get_sql_for_customer_allocation_inquiry($from, $to, $customer, $filterType, $settled)
201 $data_after = date2sql($from);
202 $date_to = date2sql($to);
213 (trans.ov_amount + trans.ov_gst + trans.ov_freight
214 + trans.ov_freight_tax + trans.ov_discount) AS TotalAmount,
215 trans.alloc AS Allocated,
216 ((trans.type = ".ST_SALESINVOICE.")
217 AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue,
220 .TB_PREF."debtor_trans as trans, "
221 .TB_PREF."debtors_master as debtor
222 WHERE debtor.debtor_no = trans.debtor_no
223 AND (trans.ov_amount + trans.ov_gst + trans.ov_freight
224 + trans.ov_freight_tax + trans.ov_discount != 0)
225 AND trans.tran_date >= '$data_after'
226 AND trans.tran_date <= '$date_to'";
228 if ($customer != ALL_TEXT)
229 $sql .= " AND trans.debtor_no = ".db_escape($customer);
231 if (isset($filterType) && $filterType != ALL_TEXT)
233 if ($filterType == '1' || $filterType == '2')
235 $sql .= " AND trans.type = ".ST_SALESINVOICE." ";
237 elseif ($filterType == '3')
239 $sql .= " AND trans.type = " . ST_CUSTPAYMENT;
241 elseif ($filterType == '4')
243 $sql .= " AND trans.type = ".ST_CUSTCREDIT." ";
246 if ($filterType == '2')
248 $today = date2sql(Today());
249 $sql .= " AND trans.due_date < '$today'
250 AND (round(abs(trans.ov_amount + "
251 ."trans.ov_gst + trans.ov_freight + "
252 ."trans.ov_freight_tax + trans.ov_discount) - trans.alloc,6) > 0) ";
257 $sql .= " AND trans.type <> ".ST_CUSTDELIVERY." ";
263 $sql .= " AND (round(abs(trans.ov_amount + trans.ov_gst + "
264 ."trans.ov_freight + trans.ov_freight_tax + "
265 ."trans.ov_discount) - trans.alloc,6) != 0) ";
271 function credit_sales_invoice_allocate($invoice_no, $credit_no, $amount, $date)
274 $sql = "SELECT ov_freight+ov_gst+ov_amount+ov_freight_tax as total, alloc FROM ".TB_PREF."debtor_trans
275 WHERE (`type`=".ST_SALESINVOICE." AND trans_no=".db_escape($invoice_no).")";
276 $result = db_query($sql, "can't retrieve invoice totals");
277 $invoice = db_fetch($result);
278 $free = $invoice['total'] - $invoice['alloc'];
280 if ($free < $amount) {
281 // if there is not enough unallocated amount - remove some payment allocations
282 $sql = "SELECT * FROM ".TB_PREF."cust_allocations
283 WHERE (trans_type_to=".ST_SALESINVOICE." AND trans_no_to=".db_escape($invoice_no).")
284 AND trans_type_from <> ".ST_CUSTCREDIT;
285 $result = db_query($sql, "can't retrieve invoice allocations");
287 while(($free < $amount) && ($alloc = db_fetch($result))) {
288 $unalloc = min($alloc['amt'], $amount-$free);
290 delete_cust_allocation($alloc['id']);
291 if ($unalloc < $alloc['amt'])
292 add_cust_allocation($alloc['amt']-$unalloc, $alloc['trans_type_from'],
293 $alloc['trans_no_from'], ST_SALESINVOICE, $invoice_no);
295 update_debtor_trans_allocation($alloc['trans_type_to'], $alloc['trans_no_to']);
296 update_debtor_trans_allocation($alloc['trans_type_from'], $alloc['trans_no_from']);
300 if (floatcmp($free, $amount)<0) {
301 // this should never happen unless sparse credit notices were allocated to
302 // the invoice, or summarized freight costs on credit notes is more than those on invoice.
303 display_error(_("Unsuspected overallocation happened due to sparse credit notes exists for this invoice.
304 Check all credit notes allocated to this invoice for summarized freight charges."));
307 add_cust_allocation($amount, ST_CUSTCREDIT, $credit_no, ST_SALESINVOICE, $invoice_no);
308 update_debtor_trans_allocation(ST_SALESINVOICE, $invoice_no);
309 update_debtor_trans_allocation(ST_CUSTCREDIT, $credit_no);
311 exchange_variation(ST_CUSTCREDIT, $credit_no, ST_SALESINVOICE, $invoice_no, $date,
312 $amount, PT_CUSTOMER);