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 add_exchange_variation($trans_type, $trans_no, $date_, $acc_id, $account,
13 $currency, $person_type_id=null, $person_id = "")
15 if (is_company_currency($currency))
19 $rate = get_exchange_rate_from_home_currency($currency, $date_);
20 $for_amount = $amount = 0;
21 // We had to split up the SQL statements to retrieve the correct GL counter-parts
22 // 2010-02-19 Joe Hunt with help of Ary Wibowo
23 $sql = "SELECT bt.*, bt.amount AS for_amount, ba.account_code
24 FROM ".TB_PREF."bank_trans bt, ".TB_PREF."bank_accounts ba
25 WHERE ba.id = bt.bank_act AND bt.bank_act=".db_escape($acc_id)." AND bt.trans_date<='".date2sql($date_)."'
26 ORDER BY bt.type, bt.trans_no";
27 $result = db_query($sql, "Transactions for bank account $acc_id could not be calculated");
28 while ($row = db_fetch($result))
30 $for_amount += $row['for_amount'];
32 $sql2 = "SELECT SUM(IF(((gt.amount < 0 AND ".$row['for_amount']." < 0) OR (gt.amount >= 0 AND ".$row['for_amount']." >= 0)),
33 gt.amount, 0)) AS amount FROM ".TB_PREF."gl_trans gt
34 WHERE gt.account = '".$row['account_code']."' AND gt.type = ".$row['type']."
35 AND gt.type_no = ".$row['trans_no']." AND gt.tran_date = '".$row['trans_date']."'";
36 $res = db_query($sql2, "Transactions for GL account ".$row['account_code']." could not be calculated");
37 $row2 = db_fetch_row($res);
40 $diff = $amount - (round2($for_amount * $rate, user_price_dec()));
43 if ($trans_type == null)
44 $trans_type = ST_JOURNAL;
45 if ($trans_no == null)
46 $trans_no = get_next_trans_no($trans_type);
47 if ($person_type_id == null)
48 $person_type_id = PT_MISC;
49 add_gl_trans($trans_type, $trans_no, $date_, $account, 0, 0, _("Exchange Variance"),
50 -$diff, null, $person_type_id, $person_id);
51 add_gl_trans($trans_type, $trans_no, $date_, get_company_pref('exchange_diff_act'), 0, 0,
52 _("Exchange Variance"), $diff, null, $person_type_id, $person_id);
56 function add_exchange_variation_all()
58 $trans_no = get_next_trans_no(ST_JOURNAL);
59 $sql = "SELECT * FROM ".TB_PREF."bank_accounts";
60 $result = db_query($sql, "could not retreive bank accounts");
61 while ($myrow = db_fetch($result))
62 add_exchange_variation(ST_JOURNAL, $trans_no, null, $myrow['id'], $myrow['account_code'],
63 $myrow['currency_code']);
65 //----------------------------------------------------------------------------------
66 // Add bank tranfer to database.
68 // $from_account - source bank account id
69 // $to_account - target bank account id
72 function add_bank_transfer($from_account, $to_account, $date_,
73 $amount, $ref, $memo_, $charge=0)
79 $trans_type = ST_BANKTRANSFER;
81 $currency = get_bank_account_currency($from_account);
83 $trans_no = get_next_trans_no($trans_type);
85 $from_gl_account = get_bank_gl_account($from_account);
86 $to_gl_account = get_bank_gl_account($to_account);
89 // do the source account postings
90 $total += add_gl_trans($trans_type, $trans_no, $date_, $from_gl_account, 0, 0, "",
91 -($amount + $charge), $currency);
93 add_bank_trans($trans_type, $trans_no, $from_account, $ref,
94 $date_, -($amount + $charge),
95 PT_MISC, "", $currency,
96 "Cannot insert a source bank transaction");
98 add_exchange_variation($trans_type, $trans_no, $date_, $from_account, $from_gl_account,
99 $currency, PT_MISC, "");
103 /* Now Debit bank charge account with charges */
104 $charge_act = get_company_pref('bank_charge_act');
105 $total += add_gl_trans($trans_type, $trans_no, $date_,
106 $charge_act, 0, 0, "", $charge, $currency);
108 // do the destination account postings
109 $total += add_gl_trans($trans_type, $trans_no, $date_, $to_gl_account, 0, 0, "",
112 /*Post a balance post if $total != 0 */
113 add_gl_balance($trans_type, $trans_no, $date_, -$total);
115 add_bank_trans($trans_type, $trans_no, $to_account, $ref,
116 $date_, $amount, PT_MISC, "",
117 $currency, "Cannot insert a destination bank transaction");
119 $currency = get_bank_account_currency($to_account);
121 add_exchange_variation($trans_type, $trans_no, $date_, $to_account, $to_gl_account,
122 $currency, PT_MISC, "");
124 add_comments($trans_type, $trans_no, $date_, $memo_);
126 $Refs->save($trans_type, $trans_no, $ref);
127 add_audit_trail($trans_type, $trans_no, $date_);
129 commit_transaction();
134 //----------------------------------------------------------------------------------
135 // Add bank payment or deposit to database.
137 // $from_account - bank account id
138 // $item - transaction cart (line item's amounts in bank account's currency)
139 // $person_type_id - defines type of $person_id identifiers
140 // $person_id - supplier/customer/other id
141 // $person_detail_id - customer branch id or not used
143 // returns an array of (inserted trans type, trans no)
145 function add_bank_transaction($trans_type, $from_account, $items, $date_,
146 $person_type_id, $person_id, $person_detail_id, $ref, $memo_, $use_transaction=true)
150 // we can only handle type 1 (payment)and type 2 (deposit)
151 if ($trans_type != ST_BANKPAYMENT && $trans_type != ST_BANKDEPOSIT)
152 display_db_error("Invalid type ($trans_type) sent to add_bank_transaction");
154 $do_exchange_variance = false;
156 if ($use_transaction)
159 $currency = get_bank_account_currency($from_account);
160 $bank_gl_account = get_bank_gl_account($from_account);
162 // the gl items are already inversed/negated for type 2 (deposit)
163 $total_amount = $items->gl_items_total();
165 if ($person_type_id == PT_CUSTOMER)
167 // we need to add a customer transaction record
169 // convert to customer currency
170 $cust_amount = exchange_from_to($total_amount, $currency, get_customer_currency($person_id), $date_);
171 // we need to negate it too
172 $cust_amount = -$cust_amount;
174 $trans_no = write_customer_trans($trans_type, 0, $person_id, $person_detail_id, $date_,
178 elseif ($person_type_id == PT_SUPPLIER)
180 // we need to add a supplier transaction record
181 // convert to supp currency
182 $supp_amount = exchange_from_to($total_amount, $currency, get_supplier_currency($person_id), $date_);
184 // we need to negate it too
185 $supp_amount = -$supp_amount;
187 $trans_no = add_supp_trans($trans_type, $person_id, $date_, '',
188 $ref, "", $supp_amount, 0, 0);
193 $trans_no = get_next_trans_no($trans_type);
194 $do_exchange_variance = true;
197 // do the source account postings
199 add_bank_trans($trans_type, $trans_no, $from_account, $ref,
200 $date_, -$total_amount,
201 $person_type_id, $person_id,
203 "Cannot insert a source bank transaction");
205 foreach ($items->gl_items as $gl_item)
207 $is_bank_to = is_bank_account($gl_item->code_id);
209 if ($trans_type == ST_BANKPAYMENT AND $is_bank_to)
211 // we don't allow payments to go to a bank account. use transfer for this !
212 display_db_error("invalid payment entered. Cannot pay to another bank account", "");
215 // do the destination account postings
216 $total += add_gl_trans($trans_type, $trans_no, $date_, $gl_item->code_id,
217 $gl_item->dimension_id, $gl_item->dimension2_id, $gl_item->reference,
218 $gl_item->amount, $currency, $person_type_id, $person_id);
222 add_bank_trans($trans_type, $trans_no, $is_bank_to, $ref,
223 $date_, $gl_item->amount,
224 $person_type_id, $person_id, $currency,
225 "Cannot insert a destination bank transaction");
226 if ($do_exchange_variance)
227 add_exchange_variation($trans_type, $trans_no, $date_, $is_bank_to, $gl_item->code_id,
228 $currency, $person_type_id, $person_id);
230 // store tax details if the gl account is a tax account
232 $amount = $gl_item->amount;
233 $ex_rate = get_exchange_rate_from_home_currency($currency, $date_);
235 add_gl_tax_details($gl_item->code_id, $trans_type, $trans_no, -$amount,
236 $ex_rate, $date_, $memo_);
239 // do the source account postings
240 add_gl_trans($trans_type, $trans_no, $date_, $bank_gl_account, 0, 0, $memo_,
241 -$total, null, $person_type_id, $person_id);
243 if ($do_exchange_variance)
244 add_exchange_variation($trans_type, $trans_no, $date_, $from_account, $bank_gl_account,
245 $currency, $person_type_id, $person_id);
247 add_comments($trans_type, $trans_no, $date_, $memo_);
249 $Refs->save($trans_type, $trans_no, $ref);
250 add_audit_trail($trans_type, $trans_no, $date_);
252 if ($use_transaction)
253 commit_transaction();
255 return array($trans_type, $trans_no);
258 //----------------------------------------------------------------------------------------