X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=gl%2Fincludes%2Fdb%2Fgl_db_banking.inc;h=cbeea72bbcc647691a408729d48182cb43177946;hb=9451db1760036985de791ba24c442801e37f37de;hp=608f4e8ce71c6129fce1e765081acd057911166a;hpb=e9782c04faf09dd040dbc0c908cdb6e6d4f0dcfc;p=fa-stable.git diff --git a/gl/includes/db/gl_db_banking.inc b/gl/includes/db/gl_db_banking.inc index 608f4e8c..cbeea72b 100644 --- a/gl/includes/db/gl_db_banking.inc +++ b/gl/includes/db/gl_db_banking.inc @@ -35,18 +35,34 @@ function add_exchange_variation($trans_no, $date_, $acc_id, $account, } $amount = get_gl_trans_from_to("", $date_, $account); $diff = $amount - $for_amount; - if ($diff != 0) + if (floatcmp($diff,0)) { - if ($trans_no == null) - $trans_no = get_next_trans_no(ST_JOURNAL); + // generate journal entry // FIXME: use cart? + global $Refs; + + begin_transaction(); + $trans_no = get_next_trans_no(ST_JOURNAL); + $ref = $Refs->get_next(ST_JOURNAL); + + add_journal(ST_JOURNAL, $trans_no, $diff, $date_ , $currency, $ref, '', $rate, $date_, $date_); + $Refs->save(ST_JOURNAL, $trans_no, $ref); + add_audit_trail(ST_JOURNAL, $trans_no, $date_); + add_comments(ST_JOURNAL, $trans_no, $date_, $memo); + if ($person_type_id == null) $person_type_id = PT_MISC; add_gl_trans(ST_JOURNAL, $trans_no, $date_, $account, 0, 0, _("Exchange Variance"), - -$diff, null, $person_type_id, $person_id); + $diff, null, $person_type_id, $person_id); + + // positive change in value is positive variance for AR and bank accounts, and negative for AP + $variance_act = ($diff < 0 && $person_id==PT_SUPPLIER) || ($diff > 0 && $person_id != PT_SUPPLIER) + ? 'exchange_diff_act' : 'exchange_diff_neg_act'; add_gl_trans(ST_JOURNAL, $trans_no, $date_, get_company_pref('exchange_diff_act'), 0, 0, - _("Exchange Variance"), $diff, null, $person_type_id, $person_id); + _("Exchange Variance"), -$diff, null, $person_type_id, $person_id); + commit_transaction(); + return true; } - return ($diff != 0); + return false; } //------------- New helper functions for revaluation of customer/supplier currencies 2011-05-08 Joe Hunt. @@ -55,22 +71,28 @@ function add_cust_supp_revaluation($ledger_act, $ex_act, $date, $amount, $person global $Refs; if ($amount == 0) return; + + begin_transaction(); $trans_no = get_next_trans_no(ST_JOURNAL); + $ref = $Refs->get_next(ST_JOURNAL); + + add_journal(ST_JOURNAL, $trans_no, $amount, $date, $currency, $ref, '', $rate, $date, $date); + $Refs->save(ST_JOURNAL, $trans_no, $ref); + add_audit_trail(ST_JOURNAL, $trans_no, $date); + add_gl_trans(ST_JOURNAL, $trans_no, $date, $ledger_act, 0, 0, _("Exchange Variance"), $amount, null, $person_type_id, $person_id); add_gl_trans(ST_JOURNAL, $trans_no, $date, $ex_act, 0, 0, _("Exchange Variance"), -$amount, null, $person_type_id, $person_id); add_comments(ST_JOURNAL, $trans_no, $date, $memo); - $ref = $Refs->get_next(ST_JOURNAL); - $Refs->save(ST_JOURNAL, $trans_no, $ref); - add_audit_trail(ST_JOURNAL, $trans_no, $date); + commit_transaction(); } function get_cust_account_curr_balances($date) { $to = date2sql($date); - $sql = "SELECT SUM(IF(t.type =".ST_CUSTCREDIT." OR t.type = ".ST_CUSTPAYMENT." OR t.type = ".ST_BANKDEPOSIT.", + $sql = "SELECT SUM(IF(t.type =".ST_CUSTCREDIT." OR t.type = ".ST_CUSTPAYMENT." OR t.type = ".ST_BANKDEPOSIT." OR t.type = ".ST_JOURNAL.", -(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount), (t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount))) AS amount, dt.debtor_no, dt.name, dt.curr_code, b.receivables_account @@ -189,14 +211,14 @@ function add_exchange_variation_all($date=null, $ref="", $memo) // function add_bank_transfer($from_account, $to_account, $date_, - $amount, $ref, $memo_, $charge=0) + $amount, $ref, $memo_, $charge=0, $target_amount=0) { global $Refs, $SysPrefs; - + begin_transaction(); - $args = func_get_args(); if (count($args) < 7) $args[] = 0; + $args = func_get_args(); if (count($args) < 8) $args[] = 0; $args = (object)array_combine(array('from_account', 'to_account', 'date_', 'amount', - 'ref', 'memo_', 'charge'), $args); + 'ref', 'memo_', 'charge', 'target_amount'), $args); $args->trans_no = 0; hook_db_prewrite($args, ST_BANKTRANSFER); @@ -233,26 +255,34 @@ function add_bank_transfer($from_account, $to_account, $date_, if ($charge != 0) { /* Now Debit bank charge account with charges */ - $charge_act = get_company_pref('bank_charge_act'); + $charge_act = get_bank_charge_account($from_account); $total += add_gl_trans($trans_type, $trans_no, $date_, $charge_act, 0, 0, $person_id, $charge, $currency); } + + // provide backward compatibility for extension modules (target amount can be not passed) + $to_currency = $target_amount ? $toact['bank_curr_code'] : $currency; + $to_amount = $target_amount ? $target_amount : $amount; + // do the destination account postings $total += add_gl_trans($trans_type, $trans_no, $date_, $to_gl_account, 0, 0, $person_id, - $amount, $currency); + $to_amount, $to_currency); /*Post a balance post if $total != 0 */ - add_gl_balance($trans_type, $trans_no, $date_, -$total); + if ($currency == $to_currency) + add_gl_balance($trans_type, $trans_no, $date_, -$total); + else // in this case those are exchange variances between bank and home rates + add_gl_trans($trans_type, $trans_no, $date_, get_company_pref('exchange_diff_act'), + 0, 0, _("Exchange Variance"), -$total); add_bank_trans($trans_type, $trans_no, $to_account, $ref, - $date_, $amount, PT_MISC, $person_id, - $currency, "Cannot insert a destination bank transaction"); + $date_, $to_amount, PT_MISC, $person_id, + $to_currency, "Cannot insert a destination bank transaction"); if ($SysPrefs->auto_currency_revaluation()) { $currency = get_bank_account_currency($to_account); - - if ($exchanged || add_exchange_variation($trans_no1, $date_, $to_account, $to_gl_account, + if (add_exchange_variation($trans_no1, $date_, $to_account, $to_gl_account, $currency, PT_MISC, $person_id)) $exchanged = true; } @@ -273,21 +303,96 @@ function add_bank_transfer($from_account, $to_account, $date_, return $trans_no; } + +function check_bank_transfer( + $trans_no, $from_account, $to_account, $date_, + $amount, $target_amount=0) +{ + $dbResult = get_bank_trans(ST_BANKTRANSFER, $trans_no); + if (2 != db_num_rows($dbResult)) { + // How are errors handled? Throw an exception would be nice. CP 2014-10 + } + + $old_from = db_fetch($dbResult); + $old_to = db_fetch($dbResult); + if ($old_to['amount'] < 0.0) { + $tmp = $old_from; + $old_from = $old_to; + $old_to = $tmp; + } + // There are four accounts to consider: + // 1) The original from account that is being voided. This results in funds being put back which is always fine. + // 2) The original to account that is being voided. This results in funds being removed which may result in a + // negative balance in the account at some time and therefore needs to be checked. + $problemTransaction = check_bank_account_history(-$old_to['amount'], $old_to['bank_act'], sql2date($old_to['trans_date'])); + if ($problemTransaction) { + // If the destination account is the same as that being edited, it may be that this edit will resolve the + // problem of voiding. + if ($to_account == $old_to['bank_act'] && sql_date_comp($problemTransaction['trans_date'], date2sql($date_)) >= 0) { + $problemTransaction = check_bank_account_history($target_amount-$old_to['amount'], $to_account, $date_); + } + } + if (null != $problemTransaction) { + $problemTransaction['account'] = $old_to['bank_act']; + $problemTransaction['bank_account_name'] = $old_to['bank_account_name']; + return $problemTransaction; + } + + // 3) The edited from account, that is having funds removed which may result in a + // negative balance in the account at some time and therefore needs to be checked. + $problemTransaction = check_bank_account_history(-$amount, $from_account, $date_); + if ($problemTransaction) { + // If the target account is the same as that being edited, it may be that voiding old transfer will + // solve balance problem. + // + if ($from_account == $old_from['bank_act'] && sql_date_comp($problemTransaction['trans_date'], $old_from['trans_date']) >=0 ) { + $problemTransaction = check_bank_account_history(-$amount-$old_from['amount'], $from_account, $date_); + } + } + + if (null != $problemTransaction) { + $problemTransaction['account'] = $old_from['bank_act']; + $problemTransaction['bank_account_name'] = $old_from['bank_account_name']; + return $problemTransaction; + } + // 4) The edited to account, that is having funds added which is always ok. + + return $problemTransaction; + +} + +function update_bank_transfer( + $trans_no, $from_account, $to_account, $date_, + $amount, $ref, $memo_, $charge=0, $target_amount=0) +{ + begin_transaction(); + delete_comments(ST_BANKTRANSFER, $trans_no); + void_bank_trans(ST_BANKTRANSFER, $trans_no, true); + void_gl_trans(ST_BANKTRANSFER, $trans_no, true); + $new_trans_no = add_bank_transfer( + $from_account, $to_account, $date_, $amount, + $ref, $memo_, $charge, $target_amount + ); + commit_transaction(); + return $new_trans_no; +} + //---------------------------------------------------------------------------------- // Add bank payment or deposit to database. // // $from_account - bank account id -// $item - transaction cart (line item's amounts in bank account's currency) +// $items - transaction cart (line amounts in bank account's currency); negative for deposit // $person_type_id - defines type of $person_id identifiers // $person_id - supplier/customer/other id -// $person_detail_id - customer branch id or not used +// $person_detail_id - customer branch id or not used +// $settled_amount - settled amount in AR/AP (if applicable) in customer/supplier currency (always non-negative number) // // returns an array of (inserted trans type, trans no) // // FIXME -revise code for update case // function write_bank_transaction($trans_type, $trans_no, $from_account, $items, $date_, - $person_type_id, $person_id, $person_detail_id, $ref, $memo_, $use_transaction=true) + $person_type_id, $person_id, $person_detail_id, $ref, $memo_, $use_transaction=true, $settled_amount=null) { global $Refs, $SysPrefs; @@ -302,7 +407,7 @@ function write_bank_transaction($trans_type, $trans_no, $from_account, $items, $ $args = func_get_args(); if (count($args) < 11) $args[] = true; $args = (object)array_combine(array('trans_type', 'trans_no', 'from_account', 'items', 'date_', - 'person_type_id', 'person_id', 'person_detail_id', 'ref', 'memo_', 'use_transaction'), + 'person_type_id', 'person_id', 'person_detail_id', 'ref', 'memo_', 'use_transaction', 'settled_amount'), $args); hook_db_prewrite($args, $trans_type); @@ -322,28 +427,34 @@ function write_bank_transaction($trans_type, $trans_no, $from_account, $items, $ if ($person_type_id == PT_CUSTOMER) { // we need to add a customer transaction record - // convert to customer currency - $cust_amount = exchange_from_to($total_amount, $currency, get_customer_currency($person_id), $date_); - // we need to negate it too - $cust_amount = -$cust_amount; + if (!isset($settled_amount)) // leaved for backward/ext compatibility + $cust_amount = exchange_from_to(abs($total_amount), $currency, get_customer_currency($person_id), $date_); + else + $cust_amount = $settled_amount; + + if ($trans_type == ST_BANKPAYMENT) + $cust_amount = -$cust_amount; $trans_no = write_customer_trans($trans_type, 0, $person_id, $person_detail_id, $date_, $ref, $cust_amount); - + move_trans_attachments($trans_type, $old_trans, $trans_no); } elseif ($person_type_id == PT_SUPPLIER) { // we need to add a supplier transaction record // convert to supp currency - $supp_amount = exchange_from_to($total_amount, $currency, get_supplier_currency($person_id), $date_); + if (!isset($settled_amount)) // leaved for for backward/ext compatibility + $supp_amount = exchange_from_to(abs($total_amount), $currency, get_supplier_currency($person_id), $date_); + else + $supp_amount = $settled_amount; - // we need to negate it too - $supp_amount = -$supp_amount; + if ($trans_type == ST_BANKPAYMENT) + $supp_amount = -$supp_amount; $trans_no = write_supp_trans($trans_type, 0, $person_id, $date_, '', $ref, "", $supp_amount, 0, 0); - + move_trans_attachments($trans_type, $old_trans, $trans_no); } else { @@ -425,7 +536,15 @@ function write_bank_transaction($trans_type, $trans_no, $from_account, $items, $ // old transaction can be voided only after new transaction is entered, // otherwise the operation could fail for cash accounts due to temporary negative balance if (@$old_trans) - void_transaction($trans_type, $old_trans, Today(), _("Document reentered.")); + { + $msg = void_transaction($trans_type, $old_trans, Today(), _("Document reentered.")); + if ($msg) + { + display_error($msg); + return false; + } + } + $args->trans_no = $trans_no; hook_db_postwrite($args, $trans_type);