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_no, $date_, $acc_id, $account,
  13     $currency, $person_type_id=null, $person_id = "")
  14 {
  15         if (is_company_currency($currency))
  16                 return false;
  17         if ($date_ == null)
  18                 $date_ = Today();
  19         $for_amount = 0;
  20
  21         // We have to calculate all the currency accounts belonging to the GL account
  22         // upto $date_ and calculate with the exchange rates. And then compare with the GL account balance.
  23         // 2010-02-23 Joe Hunt with help of Ary Wibowo
  24         $sql = "SELECT SUM(bt.amount) AS for_amount, ba.bank_curr_code
  25                 FROM ".TB_PREF."bank_trans bt, ".TB_PREF."bank_accounts ba
  26                 WHERE ba.id = bt.bank_act AND ba.account_code = ".db_escape($account)." AND bt.trans_date<='".date2sql($date_)."'
  27                 GROUP BY ba.bank_curr_code";
  28         $result = db_query($sql, "Transactions for bank account $acc_id could not be calculated");
  29         while ($row = db_fetch($result))
  30         {
  31                 if ($row['for_amount'] == 0)
  32                         continue;
  33                 $rate = get_exchange_rate_from_home_currency($row['bank_curr_code'], $date_);
  34                 $for_amount += round2($row['for_amount'] * $rate, user_price_dec());
  35         }
  36         $amount = get_gl_trans_from_to("", $date_, $account);
  37         $diff = $amount - $for_amount;
  38         if ($diff != 0)
  39         {
  40                 if ($trans_no == null)
  41                         $trans_no = get_next_trans_no(ST_JOURNAL);
  42                 if ($person_type_id == null)
  43                         $person_type_id = PT_MISC;
  44                 add_gl_trans(ST_JOURNAL, $trans_no, $date_, $account, 0, 0, _("Exchange Variance"),
  45                 -$diff, null, $person_type_id, $person_id);
  46         add_gl_trans(ST_JOURNAL, $trans_no, $date_, get_company_pref('exchange_diff_act'), 0, 0,
  47                 _("Exchange Variance"), $diff, null, $person_type_id, $person_id);
  48         }
  49         return ($diff != 0);
  50 }
  51
  52 function add_exchange_variation_all($date=null, $ref="", $memo)
  53 {
  54         global $Refs;
  55         begin_transaction();
  56         $exchanged = false;
  57         $trans_no = get_next_trans_no(ST_JOURNAL);
  58         $sql = "SELECT * FROM ".TB_PREF."bank_accounts";
  59         $result = db_query($sql, "could not retreive bank accounts");
  60         while ($myrow = db_fetch($result))
  61         {
  62                 if (add_exchange_variation($trans_no, $date, $myrow['id'], $myrow['account_code'],
  63                         $myrow['bank_curr_code']))
  64                         $exchanged = true;
  65         }
  66         if ($exchanged)
  67         {
  68                 add_comments(ST_JOURNAL, $trans_no, $date, $memo);
  69                 if ($ref == "")
  70                         $ref = $Refs->get_next(ST_JOURNAL);
  71                 $Refs->save(ST_JOURNAL, $trans_no, $ref);
  72                 add_audit_trail(ST_JOURNAL, $trans_no, $date);
  73         }
  74         commit_transaction();
  75         return ($exchanged ? $trans_no : 0);
  76 }
  77 //----------------------------------------------------------------------------------
  78 //      Add bank tranfer to database.
  79 //
  80 //      $from_account - source bank account id
  81 //      $to_account   - target bank account id
  82 //
  83
  84 function add_bank_transfer($from_account, $to_account, $date_,
  85         $amount, $ref, $memo_, $charge=0)
  86 {
  87         global $Refs, $SysPrefs;
  88
  89         begin_transaction();
  90
  91         $trans_type = ST_BANKTRANSFER;
  92
  93         $currency = get_bank_account_currency($from_account);
  94
  95         $trans_no = get_next_trans_no($trans_type);
  96
  97     $fromact = get_bank_account($from_account);
  98     $toact = get_bank_account($to_account);
  99     $person_id = _("From")." ".$fromact['bank_account_name']." "._("To")." ".$toact['bank_account_name'];
 100
 101         $from_gl_account = get_bank_gl_account($from_account);
 102         $to_gl_account = get_bank_gl_account($to_account);
 103
 104         $exchanged = false;
 105         $total = 0;
 106         // do the source account postings
 107     $total += add_gl_trans($trans_type, $trans_no, $date_, $from_gl_account, 0, 0, $person_id,
 108                 -($amount + $charge), $currency);
 109
 110     add_bank_trans($trans_type, $trans_no, $from_account, $ref,
 111                 $date_, -($amount + $charge),
 112                 PT_MISC, $person_id, $currency,
 113                 "Cannot insert a source bank transaction");
 114         if ($SysPrefs->auto_currency_revaluation())
 115         {
 116                 $trans_no1 = get_next_trans_no(ST_JOURNAL);
 117                 if (add_exchange_variation($trans_no1, $date_, $from_account, $from_gl_account,
 118                         $currency, PT_MISC, $person_id))
 119                         $exchanged = true;
 120         }
 121         if ($charge != 0)
 122         {
 123                 /* Now Debit bank charge account with charges */
 124                 $charge_act = get_company_pref('bank_charge_act');
 125                 $total += add_gl_trans($trans_type, $trans_no, $date_,
 126                         $charge_act, 0, 0, $person_id, $charge, $currency);
 127         }
 128         // do the destination account postings
 129         $total += add_gl_trans($trans_type, $trans_no, $date_, $to_gl_account, 0, 0, $person_id,
 130                 $amount, $currency);
 131
 132         /*Post a balance post if $total != 0 */
 133         add_gl_balance($trans_type, $trans_no, $date_, -$total);
 134
 135         add_bank_trans($trans_type, $trans_no, $to_account, $ref,
 136                 $date_, $amount, PT_MISC, $person_id,
 137                 $currency, "Cannot insert a destination bank transaction");
 138
 139         if ($SysPrefs->auto_currency_revaluation())
 140         {
 141                 $currency = get_bank_account_currency($to_account);
 142
 143                 if ($exchanged || add_exchange_variation($trans_no1, $date_, $to_account, $to_gl_account,
 144                         $currency, PT_MISC, $person_id))
 145                 {
 146                         $ref1 = $Refs->get_next(ST_JOURNAL);
 147                         $Refs->save(ST_JOURNAL, $trans_no1, $ref1);
 148                         add_audit_trail(ST_JOURNAL, $trans_no1, $date_);
 149                 }
 150         }
 151         add_comments($trans_type, $trans_no, $date_, $memo_);
 152
 153         $Refs->save($trans_type, $trans_no, $ref);
 154         add_audit_trail($trans_type, $trans_no, $date_);
 155
 156         commit_transaction();
 157
 158         return $trans_no;
 159 }
 160
 161 //----------------------------------------------------------------------------------
 162 //      Add bank payment or deposit to database.
 163 //
 164 //      $from_account - bank account id
 165 //  $item - transaction cart (line item's amounts in bank account's currency)
 166 //  $person_type_id - defines type of $person_id identifiers
 167 //  $person_id  - supplier/customer/other id
 168 // $person_detail_id - customer branch id or not used
 169 //
 170 // returns an array of (inserted trans type, trans no)
 171
 172 function add_bank_transaction($trans_type, $from_account, $items, $date_,
 173         $person_type_id, $person_id, $person_detail_id, $ref, $memo_, $use_transaction=true)
 174 {
 175         global $Refs, $SysPrefs;
 176
 177         // we can only handle type 1 (payment)and type 2 (deposit)
 178         if ($trans_type != ST_BANKPAYMENT && $trans_type != ST_BANKDEPOSIT)
 179                 display_db_error("Invalid type ($trans_type) sent to add_bank_transaction");
 180
 181         $do_exchange_variance = false;
 182         $exchanged = false;
 183         if ($use_transaction)
 184                 begin_transaction();
 185
 186         $currency = get_bank_account_currency($from_account);
 187         $bank_gl_account = get_bank_gl_account($from_account);
 188
 189         // the gl items are already inversed/negated for type 2 (deposit)
 190         $total_amount = $items->gl_items_total();
 191
 192     if ($person_type_id == PT_CUSTOMER)
 193     {
 194         // we need to add a customer transaction record
 195
 196                 // convert to customer currency
 197                 $cust_amount = exchange_from_to($total_amount, $currency, get_customer_currency($person_id), $date_);
 198                 // we need to negate it too
 199                 $cust_amount = -$cust_amount;
 200
 201                 $trans_no = write_customer_trans($trans_type, 0, $person_id, $person_detail_id, $date_,
 202                 $ref, $cust_amount);
 203
 204     }
 205     elseif ($person_type_id == PT_SUPPLIER)
 206     {
 207         // we need to add a supplier transaction record
 208                 // convert to supp currency
 209                 $supp_amount = exchange_from_to($total_amount, $currency, get_supplier_currency($person_id), $date_);
 210
 211                 // we need to negate it too
 212                 $supp_amount = -$supp_amount;
 213
 214                 $trans_no = add_supp_trans($trans_type, $person_id, $date_, '',
 215                 $ref, "", $supp_amount, 0, 0);
 216
 217     }
 218     else
 219     {
 220         $trans_no = get_next_trans_no($trans_type);
 221         $do_exchange_variance = $SysPrefs->auto_currency_revaluation();
 222         if ($do_exchange_variance)
 223                 $trans_no1 = get_next_trans_no(ST_JOURNAL);
 224     }
 225
 226         // do the source account postings
 227
 228     add_bank_trans($trans_type, $trans_no, $from_account, $ref,
 229         $date_, -$total_amount,
 230         $person_type_id, $person_id,
 231         $currency,
 232         "Cannot insert a source bank transaction");
 233         $total = 0;
 234         foreach ($items->gl_items as $gl_item)
 235         {
 236                 $is_bank_to = is_bank_account($gl_item->code_id);
 237
 238                 if ($trans_type == ST_BANKPAYMENT AND $is_bank_to)
 239                 {
 240                         // we don't allow payments to go to a bank account. use transfer for this !
 241                         display_db_error("invalid payment entered. Cannot pay to another bank account", "");
 242                 }
 243
 244         // do the destination account postings
 245         $total += add_gl_trans($trans_type, $trans_no, $date_, $gl_item->code_id,
 246                 $gl_item->dimension_id, $gl_item->dimension2_id, $gl_item->reference,
 247                 $gl_item->amount, $currency, $person_type_id, $person_id);
 248
 249         if ($is_bank_to)
 250         {
 251                 add_bank_trans($trans_type, $trans_no, $is_bank_to, $ref,
 252                         $date_, $gl_item->amount,
 253                         $person_type_id, $person_id, $currency,
 254                         "Cannot insert a destination bank transaction");
 255                 if ($do_exchange_variance)
 256                 {
 257                         add_exchange_variation($trans_no1, $date_, $is_bank_to, $gl_item->code_id,
 258                                 $currency, $person_type_id, $person_id);
 259                 }
 260         }
 261                 // store tax details if the gl account is a tax account
 262
 263                 $amount = $gl_item->amount;
 264                 $ex_rate = get_exchange_rate_from_home_currency($currency, $date_);
 265
 266                 add_gl_tax_details($gl_item->code_id, $trans_type, $trans_no, -$amount,
 267                         $ex_rate, $date_, $memo_);
 268         }
 269
 270         // do the source account postings
 271     add_gl_trans($trans_type, $trans_no, $date_, $bank_gl_account, 0, 0, $memo_,
 272         -$total, null, $person_type_id, $person_id);
 273
 274     if ($do_exchange_variance)
 275     {
 276         if ($exchanged || add_exchange_variation($trans_no1, $date_, $from_account, $bank_gl_account,
 277                 $currency, $person_type_id, $person_id))
 278         {
 279                         $ref1 = $Refs->get_next(ST_JOURNAL);
 280                         $Refs->save(ST_JOURNAL, $trans_no1, $ref1);
 281                         add_audit_trail(ST_JOURNAL, $trans_no1, $date_);
 282                 }
 283         }
 284
 285         add_comments($trans_type, $trans_no, $date_, $memo_);
 286
 287         $Refs->save($trans_type, $trans_no, $ref);
 288         add_audit_trail($trans_type, $trans_no, $date_);
 289
 290         if ($use_transaction)
 291                 commit_transaction();
 292
 293         return array($trans_type, $trans_no);
 294 }
 295
 296 //----------------------------------------------------------------------------------------
 297
 298 //----------------------------------------------------------------------------------
 299 //      Re-Insert bank payment or deposit to database using the transaction number passed.
 300 //
 301 //      $from_account - bank account id
 302 //  $item - transaction cart (line item's amounts in bank account's currency)
 303 //  $person_type_id - defines type of $person_id identifiers
 304 //  $person_id  - supplier/customer/other id
 305 //  $person_detail_id - customer branch id or not used
 306 //
 307 // returns an array of (inserted trans type, trans no)
 308
 309 function reinsert_bank_transaction($trans_type, $trans_no, $from_account, $items, $date_,
 310         $person_type_id, $person_id, $person_detail_id, $ref, $memo_, $use_transaction=true)
 311 {
 312         global $Refs, $SysPrefs;
 313
 314         // we can only handle type 1 (payment)and type 2 (deposit)
 315         if ($trans_type != ST_BANKPAYMENT && $trans_type != ST_BANKDEPOSIT)
 316                 display_db_error("Invalid type ($trans_type) sent to add_bank_transaction");
 317
 318         $do_exchange_variance = false;
 319         $exchanged = false;
 320         if ($use_transaction)
 321                 begin_transaction();
 322
 323         $currency = get_bank_account_currency($from_account);
 324         $bank_gl_account = get_bank_gl_account($from_account);
 325
 326         // the gl items are already inversed/negated for type 2 (deposit)
 327         $total_amount = $items->gl_items_total();
 328
 329     if ($person_type_id == PT_CUSTOMER)
 330     {
 331         // we need to add a customer transaction record
 332
 333                 // convert to customer currency
 334                 $cust_amount = exchange_from_to($total_amount, $currency, get_customer_currency($person_id), $date_);
 335                 // we need to negate it too
 336                 $cust_amount = -$cust_amount;
 337
 338                 $trans_no = reinsert_customer_trans($trans_type, $trans_no, $person_id, $person_detail_id, $date_,
 339                 $ref, $cust_amount);
 340
 341     }
 342     elseif ($person_type_id == PT_SUPPLIER)
 343     {
 344         // we need to add a supplier transaction record
 345                 // convert to supp currency
 346                 $supp_amount = exchange_from_to($total_amount, $currency, get_supplier_currency($person_id), $date_);
 347
 348                 // we need to negate it too
 349                 $supp_amount = -$supp_amount;
 350
 351                 $trans_no = reinsert_supp_trans($trans_type, $trans_no, $person_id, $date_, '',
 352                 $ref, "", $supp_amount, 0, 0);
 353
 354     }
 355     else
 356     {
 357         $do_exchange_variance = $SysPrefs->auto_currency_revaluation();
 358         if ($do_exchange_variance)
 359                 $trans_no1 = get_next_trans_no(ST_JOURNAL);
 360     }
 361
 362         // do the source account postings
 363
 364     add_bank_trans($trans_type, $trans_no, $from_account, $ref,
 365         $date_, -$total_amount,
 366         $person_type_id, $person_id,
 367         $currency,
 368         "Cannot insert a source bank transaction");
 369         $total = 0;
 370         foreach ($items->gl_items as $gl_item)
 371         {
 372                 $is_bank_to = is_bank_account($gl_item->code_id);
 373
 374                 if ($trans_type == ST_BANKPAYMENT AND $is_bank_to)
 375                 {
 376                         // we don't allow payments to go to a bank account. use transfer for this !
 377                         display_db_error("invalid payment entered. Cannot pay to another bank account", "");
 378                 }
 379
 380         // do the destination account postings
 381         $total += add_gl_trans($trans_type, $trans_no, $date_, $gl_item->code_id,
 382                 $gl_item->dimension_id, $gl_item->dimension2_id, $gl_item->reference,
 383                 $gl_item->amount, $currency, $person_type_id, $person_id);
 384
 385         if ($is_bank_to)
 386         {
 387                 add_bank_trans($trans_type, $trans_no, $is_bank_to, $ref,
 388                         $date_, $gl_item->amount,
 389                         $person_type_id, $person_id, $currency,
 390                         "Cannot insert a destination bank transaction");
 391                 if ($do_exchange_variance)
 392                 {
 393                         add_exchange_variation($trans_no1, $date_, $is_bank_to, $gl_item->code_id,
 394                                 $currency, $person_type_id, $person_id);
 395                 }
 396         }
 397                 // store tax details if the gl account is a tax account
 398
 399                 $amount = $gl_item->amount;
 400                 $ex_rate = get_exchange_rate_from_home_currency($currency, $date_);
 401
 402                 add_gl_tax_details($gl_item->code_id, $trans_type, $trans_no, -$amount,
 403                         $ex_rate, $date_, $memo_);
 404         }
 405         // do the source account postings
 406     add_gl_trans($trans_type, $trans_no, $date_, $bank_gl_account, 0, 0, $memo_,
 407         -$total, null, $person_type_id, $person_id);
 408
 409     if ($do_exchange_variance)
 410     {
 411         if ($exchanged || add_exchange_variation($trans_no1, $date_, $from_account, $bank_gl_account,
 412                 $currency, $person_type_id, $person_id))
 413         {
 414                         $ref1 = $Refs->get_next(ST_JOURNAL);
 415                         $Refs->save(ST_JOURNAL, $trans_no1, $ref1);
 416                         add_audit_trail(ST_JOURNAL, $trans_no1, $date_);
 417                 }
 418         }
 419
 420         add_comments($trans_type, $trans_no, $date_, $memo_);
 421
 422         $Refs->save($trans_type, $trans_no, $ref);
 423         add_audit_trail($trans_type, $trans_no, $date_);
 424
 425         if ($use_transaction)
 426                 commit_transaction();
 427
 428         return array($trans_type, $trans_no);
 429 }
 430
 431 //----------------------------------------------------------------------------------------
 432
 433 ?>