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 //----------------------------------------------------------------------------------------
37 function get_DebtorTrans_allocation_balance($trans_type, $trans_no)
40 $sql = "SELECT (ov_amount+ov_gst+ov_freight+ov_freight_tax-ov_discount-alloc) AS BalToAllocate
41 FROM ".TB_PREF."debtor_trans WHERE trans_no=".db_escape($trans_no)." AND type=".db_escape($trans_type);
42 $result = db_query($sql,"calculate the allocation");
43 $myrow = db_fetch_row($result);
48 //----------------------------------------------------------------------------------------
50 function update_debtor_trans_allocation($trans_type, $trans_no, $alloc)
52 $sql = "UPDATE ".TB_PREF."debtor_trans SET alloc = alloc + $alloc
53 WHERE type=".db_escape($trans_type)." AND trans_no = ".db_escape($trans_no);
54 db_query($sql, "The debtor transaction record could not be modified for the allocation against it");
57 //-------------------------------------------------------------------------------------------------------------
59 function void_cust_allocations($type, $type_no, $date="")
61 return clear_cust_alloctions($type, $type_no, $date);
64 //-------------------------------------------------------------------------------------------------------------
66 function clear_cust_alloctions($type, $type_no, $date="")
68 // clear any allocations for this transaction
69 $sql = "SELECT * FROM ".TB_PREF."cust_allocations
70 WHERE (trans_type_from=".db_escape($type)." AND trans_no_from=".db_escape($type_no).")
71 OR (trans_type_to=".db_escape($type)." AND trans_no_to=".db_escape($type_no).")";
72 $result = db_query($sql, "could not void debtor transactions for type=$type and trans_no=$type_no");
74 while ($row = db_fetch($result))
76 $sql = "UPDATE ".TB_PREF."debtor_trans SET alloc=alloc - " . $row['amt'] . "
77 WHERE (type= " . $row['trans_type_from'] . " AND trans_no=" . $row['trans_no_from'] . ")
78 OR (type=" . $row['trans_type_to'] . " AND trans_no=" . $row['trans_no_to'] . ")";
79 db_query($sql, "could not clear allocation");
80 // 2008-09-20 Joe Hunt
82 exchange_variation($type, $type_no, $row['trans_type_to'], $row['trans_no_to'], $date,
83 $row['amt'], PT_CUSTOMER, true);
84 //////////////////////
88 // remove any allocations for this transaction
89 $sql = "DELETE FROM ".TB_PREF."cust_allocations
90 WHERE (trans_type_from=".db_escape($type)." AND trans_no_from=".db_escape($type_no).")
91 OR (trans_type_to=".db_escape($type)." AND trans_no_to=".db_escape($type_no).")";
93 db_query($sql, "could not void debtor transactions for type=$type and trans_no=$type_no");
95 //----------------------------------------------------------------------------------------
97 function get_alloc_trans_sql($extra_fields=null, $extra_conditions=null, $extra_tables=null)
104 debtor.name AS DebtorName,
106 ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount AS Total,
113 $sql .= ", $extra_fields ";
115 $sql .= " FROM ".TB_PREF."debtor_trans as trans, "
116 .TB_PREF."debtors_master as debtor";
118 $sql .= ",$extra_tables ";
120 $sql .= " WHERE trans.debtor_no=debtor.debtor_no";
122 if ($extra_conditions)
123 $sql .= " AND $extra_conditions ";
129 //-------------------------------------------------------------------------------------------------------------
131 function get_allocatable_from_cust_sql($customer_id, $settled)
136 $settled_sql = " AND (round(ov_amount+ov_gst+ov_freight+ov_freight_tax-ov_discount-alloc,6) > 0)";
139 if ($customer_id != null)
140 $cust_sql = " AND trans.debtor_no = ".db_escape($customer_id);
142 $sql = get_alloc_trans_sql("round(ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount-alloc,6) <= 0 AS settled",
143 "(type=".ST_CUSTPAYMENT." OR type=".ST_CUSTCREDIT." OR type=".ST_BANKDEPOSIT.") AND (trans.ov_amount > 0) " . $settled_sql . $cust_sql);
148 //-------------------------------------------------------------------------------------------------------------
150 function get_allocatable_to_cust_transactions($customer_id, $trans_no=null, $type=null)
152 if ($trans_no != null and $type != null)
154 $sql = get_alloc_trans_sql("amt", "trans.trans_no = alloc.trans_no_to
155 AND trans.type = alloc.trans_type_to
156 AND alloc.trans_no_from=$trans_no
157 AND alloc.trans_type_from=$type
158 AND trans.debtor_no=".db_escape($customer_id),
159 "".TB_PREF."cust_allocations as alloc");
163 $sql = get_alloc_trans_sql(null, "round(ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount-alloc,6) > 0
164 AND trans.type <> " . ST_CUSTPAYMENT . "
165 AND trans.type <> " . ST_BANKDEPOSIT . "
166 AND trans.type <> " . ST_CUSTCREDIT . "
167 AND trans.type <> " . ST_CUSTDELIVERY . "
168 AND trans.debtor_no=".db_escape($customer_id));
171 return db_query($sql." ORDER BY trans_no", "Cannot retreive alloc to transactions");
174 function get_sql_for_customer_allocation_inquiry()
176 $data_after = date2sql($_POST['TransAfterDate']);
177 $date_to = date2sql($_POST['TransToDate']);
188 (trans.ov_amount + trans.ov_gst + trans.ov_freight
189 + trans.ov_freight_tax + trans.ov_discount) AS TotalAmount,
190 trans.alloc AS Allocated,
191 ((trans.type = ".ST_SALESINVOICE.")
192 AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue
194 .TB_PREF."debtor_trans as trans, "
195 .TB_PREF."debtors_master as debtor
196 WHERE debtor.debtor_no = trans.debtor_no
197 AND (trans.ov_amount + trans.ov_gst + trans.ov_freight
198 + trans.ov_freight_tax + trans.ov_discount != 0)
199 AND trans.tran_date >= '$data_after'
200 AND trans.tran_date <= '$date_to'";
202 if ($_POST['customer_id'] != ALL_TEXT)
203 $sql .= " AND trans.debtor_no = ".db_escape($_POST['customer_id']);
205 if (isset($_POST['filterType']) && $_POST['filterType'] != ALL_TEXT)
207 if ($_POST['filterType'] == '1' || $_POST['filterType'] == '2')
209 $sql .= " AND trans.type = ".ST_SALESINVOICE." ";
211 elseif ($_POST['filterType'] == '3')
213 $sql .= " AND trans.type = " . ST_CUSTPAYMENT;
215 elseif ($_POST['filterType'] == '4')
217 $sql .= " AND trans.type = ".ST_CUSTCREDIT." ";
220 if ($_POST['filterType'] == '2')
222 $today = date2sql(Today());
223 $sql .= " AND trans.due_date < '$today'
224 AND (round(abs(trans.ov_amount + "
225 ."trans.ov_gst + trans.ov_freight + "
226 ."trans.ov_freight_tax + trans.ov_discount) - trans.alloc,6) > 0) ";
231 $sql .= " AND trans.type <> ".ST_CUSTDELIVERY." ";
235 if (!check_value('showSettled'))
237 $sql .= " AND (round(abs(trans.ov_amount + trans.ov_gst + "
238 ."trans.ov_freight + trans.ov_freight_tax + "
239 ."trans.ov_discount) - trans.alloc,6) != 0) ";