gl/includes/db/gl_db_banking.inc

   1 <?php
   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 = "")
  14 {
  15         if (is_company_currency($currency))
  16                 return;
  17         if ($date_ == null)
  18                 $date_ = Today();
  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.*, SUM(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                 GROUP 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))
  29         {
  30                 $for_amount += $row['for_amount'];
  31
  32                 $sql2 = "SELECT SUM(gt.amount) AS amount FROM ".TB_PREF."gl_trans gt
  33                         WHERE gt.account = ".$row['account_code']." AND gt.type = ".$row['type']."
  34                         AND gt.type_no = ".$row['trans_no']." AND gt.tran_date = '".$row['trans_date']."'
  35                         AND gt.person_type_id = ".$row['person_type_id'];
  36                 $res = db_query($sql2, "Transactions for GL account ".$row['account_code']." could not be calculated");
  37                 $row2 = db_fetch_row($res);
  38                 $amount += $row2[0];
  39         }
  40         $diff = $amount - (round2($for_amount * $rate, user_price_dec()));
  41         if ($diff != 0)
  42         {
  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);
  53         }
  54 }
  55
  56 function add_exchange_variation_all()
  57 {
  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']);
  64 }
  65 //----------------------------------------------------------------------------------
  66 //      Add bank tranfer to database.
  67 //
  68 //      $from_account - source bank account id
  69 //      $to_account   - target bank account id
  70 //
  71
  72 function add_bank_transfer($from_account, $to_account, $date_,
  73         $amount, $ref, $memo_, $charge=0)
  74 {
  75         global $Refs;
  76
  77         begin_transaction();
  78
  79         $trans_type = ST_BANKTRANSFER;
  80
  81         $currency = get_bank_account_currency($from_account);
  82
  83         $trans_no = get_next_trans_no($trans_type);
  84
  85         $from_gl_account = get_bank_gl_account($from_account);
  86         $to_gl_account = get_bank_gl_account($to_account);
  87
  88         $total = 0;
  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);
  92
  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");
  97
  98         add_exchange_variation($trans_type, $trans_no, $date_, $from_account, $from_gl_account,
  99                 $currency, PT_MISC, "");
 100
 101         if ($charge != 0)
 102         {
 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);
 107         }
 108         // do the destination account postings
 109         $total += add_gl_trans($trans_type, $trans_no, $date_, $to_gl_account, 0, 0, "",
 110                 $amount, $currency);
 111
 112         /*Post a balance post if $total != 0 */
 113         add_gl_balance($trans_type, $trans_no, $date_, -$total);
 114
 115         add_bank_trans($trans_type, $trans_no, $to_account, $ref,
 116                 $date_, $amount, PT_MISC, "",
 117                 $currency, "Cannot insert a destination bank transaction");
 118
 119         $currency = get_bank_account_currency($to_account);
 120
 121         add_exchange_variation($trans_type, $trans_no, $date_, $to_account, $to_gl_account,
 122                 $currency, PT_MISC, "");
 123
 124         add_comments($trans_type, $trans_no, $date_, $memo_);
 125
 126         $Refs->save($trans_type, $trans_no, $ref);
 127         add_audit_trail($trans_type, $trans_no, $date_);
 128
 129         commit_transaction();
 130
 131         return $trans_no;
 132 }
 133
 134 //----------------------------------------------------------------------------------
 135 //      Add bank payment or deposit to database.
 136 //
 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
 142 //
 143 // returns an array of (inserted trans type, trans no)
 144
 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)
 147 {
 148         global $Refs;
 149
 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");
 153
 154         $do_exchange_variance = false;
 155
 156         if ($use_transaction)
 157                 begin_transaction();
 158
 159         $currency = get_bank_account_currency($from_account);
 160         $bank_gl_account = get_bank_gl_account($from_account);
 161
 162         // the gl items are already inversed/negated for type 2 (deposit)
 163         $total_amount = $items->gl_items_total();
 164
 165     if ($person_type_id == PT_CUSTOMER)
 166     {
 167         // we need to add a customer transaction record
 168
 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;
 173
 174                 $trans_no = write_customer_trans($trans_type, 0, $person_id, $person_detail_id, $date_,
 175                 $ref, $cust_amount);
 176
 177     }
 178     elseif ($person_type_id == PT_SUPPLIER)
 179     {
 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_);
 183
 184                 // we need to negate it too
 185                 $supp_amount = -$supp_amount;
 186
 187                 $trans_no = add_supp_trans($trans_type, $person_id, $date_, '',
 188                 $ref, "", $supp_amount, 0, 0);
 189
 190     }
 191     else
 192     {
 193         $trans_no = get_next_trans_no($trans_type);
 194         $do_exchange_variance = true;
 195     }
 196
 197         // do the source account postings
 198
 199     add_bank_trans($trans_type, $trans_no, $from_account, $ref,
 200         $date_, -$total_amount,
 201         $person_type_id, $person_id,
 202         $currency,
 203         "Cannot insert a source bank transaction");
 204         $total = 0;
 205         foreach ($items->gl_items as $gl_item)
 206         {
 207                 $is_bank_to = is_bank_account($gl_item->code_id);
 208
 209                 if ($trans_type == ST_BANKPAYMENT AND $is_bank_to)
 210                 {
 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", "");
 213                 }
 214
 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);
 219
 220         if ($is_bank_to)
 221         {
 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);
 229         }
 230                 // store tax details if the gl account is a tax account
 231
 232                 $amount = $gl_item->amount;
 233                 $ex_rate = get_exchange_rate_from_home_currency($currency, $date_);
 234
 235                 add_gl_tax_details($gl_item->code_id, $trans_type, $trans_no, -$amount,
 236                         $ex_rate, $date_, $memo_);
 237         }
 238
 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);
 242
 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);
 246
 247         add_comments($trans_type, $trans_no, $date_, $memo_);
 248
 249         $Refs->save($trans_type, $trans_no, $ref);
 250         add_audit_trail($trans_type, $trans_no, $date_);
 251
 252         if ($use_transaction)
 253                 commit_transaction();
 254
 255         return array($trans_type, $trans_no);
 256 }
 257
 258 //----------------------------------------------------------------------------------------
 259
 260 ?>