X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=gl%2Fincludes%2Fdb%2Fgl_db_banking.inc;h=03623b6e3ec1233890aaa85c2619e2f538a4ddf9;hb=4deaa6ba6e084655989c30cfb7d9dd307408c7dc;hp=cd197af797bc576c3c5003e4b46e646485be1dd2;hpb=3b431d909abc53e4a4d712cbafa39ca556409d0e;p=fa-stable.git diff --git a/gl/includes/db/gl_db_banking.inc b/gl/includes/db/gl_db_banking.inc index cd197af7..03623b6e 100644 --- a/gl/includes/db/gl_db_banking.inc +++ b/gl/includes/db/gl_db_banking.inc @@ -20,7 +20,6 @@ function add_exchange_variation($trans_no, $date_, $acc_id, $account, // We have to calculate all the currency accounts belonging to the GL account // upto $date_ and calculate with the exchange rates. And then compare with the GL account balance. - // 2010-02-23 Joe Hunt with help of Ary Wibowo $sql = "SELECT SUM(bt.amount) AS for_amount, ba.bank_curr_code FROM ".TB_PREF."bank_trans bt, ".TB_PREF."bank_accounts ba WHERE ba.id = bt.bank_act AND ba.account_code = ".db_escape($account)." AND bt.trans_date<='".date2sql($date_)."' @@ -44,28 +43,23 @@ function add_exchange_variation($trans_no, $date_, $acc_id, $account, $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_); + add_journal(ST_JOURNAL, $trans_no, $diff, $date_ , get_company_currency(), $ref, '', 1, $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 false; } -//------------- New helper functions for revaluation of customer/supplier currencies 2011-05-08 Joe Hunt. function add_cust_supp_revaluation($ledger_act, $ex_act, $date, $amount, $person_type_id, $person_id, $memo) { global $Refs; @@ -76,7 +70,7 @@ function add_cust_supp_revaluation($ledger_act, $ex_act, $date, $amount, $person $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); + add_journal(ST_JOURNAL, $trans_no, $amount, $date, get_company_currency(), $ref, '', 1, $date, $date); $Refs->save(ST_JOURNAL, $trans_no, $ref); add_audit_trail(ST_JOURNAL, $trans_no, $date); @@ -92,7 +86,7 @@ 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 IN(". implode(',', array(ST_CUSTCREDIT, ST_CUSTPAYMENT, ST_BANKDEPOSIT, 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 @@ -141,7 +135,9 @@ function get_account_home_balance($person_id, $person_type, $rec_account, $to_da function add_exchange_variation_all($date=null, $ref="", $memo) { global $Refs; - begin_transaction(); + + begin_transaction(__FUNCTION__, func_get_args()); + $exchanged = false; $trans_no = get_next_trans_no(ST_JOURNAL); $sql = "SELECT * FROM ".TB_PREF."bank_accounts"; @@ -156,7 +152,7 @@ function add_exchange_variation_all($date=null, $ref="", $memo) { add_comments(ST_JOURNAL, $trans_no, $date, $memo); if ($ref == "") - $ref = $Refs->get_next(ST_JOURNAL); + $ref = $Refs->get_next(ST_JOURNAL, null, $date); $Refs->save(ST_JOURNAL, $trans_no, $ref); add_audit_trail(ST_JOURNAL, $trans_no, $date); } @@ -215,7 +211,8 @@ function add_bank_transfer($from_account, $to_account, $date_, { global $Refs, $SysPrefs; - begin_transaction(); + begin_transaction(__FUNCTION__, func_get_args()); + $args = func_get_args(); if (count($args) < 8) $args[] = 0; $args = (object)array_combine(array('from_account', 'to_account', 'date_', 'amount', 'ref', 'memo_', 'charge', 'target_amount'), $args); @@ -243,8 +240,7 @@ function add_bank_transfer($from_account, $to_account, $date_, add_bank_trans($trans_type, $trans_no, $from_account, $ref, $date_, -($amount + $charge), - PT_MISC, $person_id, $currency, - "Cannot insert a source bank transaction"); + PT_MISC, $person_id, $currency); if ($SysPrefs->auto_currency_revaluation()) { $trans_no1 = get_next_trans_no(ST_JOURNAL); @@ -276,8 +272,7 @@ function add_bank_transfer($from_account, $to_account, $date_, 0, 0, _("Exchange Variance"), -$total); add_bank_trans($trans_type, $trans_no, $to_account, $ref, - $date_, $to_amount, PT_MISC, $person_id, - $to_currency, "Cannot insert a destination bank transaction"); + $date_, $to_amount, PT_MISC, $person_id); if ($SysPrefs->auto_currency_revaluation()) { @@ -288,7 +283,7 @@ function add_bank_transfer($from_account, $to_account, $date_, } if ($exchanged == true) { - $ref1 = $Refs->get_next(ST_JOURNAL); + $ref1 = $Refs->get_next(ST_JOURNAL, null, $date_); $Refs->save(ST_JOURNAL, $trans_no1, $ref1); add_audit_trail(ST_JOURNAL, $trans_no1, $date_); } @@ -304,14 +299,10 @@ function add_bank_transfer($from_account, $to_account, $date_, return $trans_no; } -function check_bank_transfer( - $trans_no, $from_account, $to_account, $date_, +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); @@ -324,33 +315,34 @@ function check_bank_transfer( // 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'])); + $problemTransaction = check_bank_account_history(-$old_to['amount'], $old_to['bank_act'], sql2date($old_from['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 ($to_account == $old_to['bank_act'] && sql_date_comp($problemTransaction['trans_date'], date2sql($date_)) > 0) { + $problemTransaction['amount'] += $amount; + if ($problemTransaction['amount'] >= 0) { + $problemTransaction = null; + } + } + if ($problemTransaction != null) { + $problemTransaction['account'] = $old_to['bank_act']; + $problemTransaction['bank_account_name'] = $old_to['bank_account_name']; + return $problemTransaction; } - } - 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_); - } + $balance_offset = 0; + if ($from_account == $old_from['bank_act'] && sql_date_comp(date2sql($date_), $old_from['trans_date']) >= 0) { + // If the edited from_account is the same as the original transaction, and the new date is later than the old date + // then add the amount from the original from transaction when checking the history to simulate the void that would + // take effect from that date. + $balance_offset = -$old_from['amount']; } - - if (null != $problemTransaction) { + $problemTransaction = check_bank_account_history(-$amount, $from_account, $date_, null, $balance_offset); + if ($problemTransaction != null) { $problemTransaction['account'] = $old_from['bank_act']; $problemTransaction['bank_account_name'] = $old_from['bank_account_name']; return $problemTransaction; @@ -358,16 +350,15 @@ function check_bank_transfer( // 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(); + begin_transaction(__FUNCTION__, func_get_args()); delete_comments(ST_BANKTRANSFER, $trans_no); - void_bank_trans(ST_BANKTRANSFER, $trans_no, true); + void_transaction(ST_BANKTRANSFER, $trans_no, Today(), _("Document reentered.")); void_gl_trans(ST_BANKTRANSFER, $trans_no, true); $new_trans_no = add_bank_transfer( $from_account, $to_account, $date_, $amount, @@ -391,24 +382,22 @@ function update_bank_transfer( // // 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, $settled_amount=null) +function write_bank_transaction(&$cart) { global $Refs, $SysPrefs; - // we can only handle type 1 (payment)and type 2 (deposit) - if ($trans_type != ST_BANKPAYMENT && $trans_type != ST_BANKDEPOSIT) - display_db_error("Invalid type ($trans_type) sent to add_bank_transaction"); + $trans_type = $cart->trans_type; + $trans_no = $cart->order_id; + $from_account = $cart->bank_act; + $person_type_id = $cart->person_type_id; + $person_id = $cart->person_id; + $ref = $cart->reference; $do_exchange_variance = false; $exchanged = false; - if ($use_transaction) - begin_transaction(); - $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', 'settled_amount'), - $args); + begin_transaction(__FUNCTION__, $args = func_get_args()); + hook_db_prewrite($args, $trans_type); $aid = 0; @@ -416,45 +405,48 @@ function write_bank_transaction($trans_type, $trans_no, $from_account, $items, $ $old_trans = $trans_no; $Refs->restore_last($trans_type, $trans_no); $aid = has_attachment($trans_type, $trans_no); - } + } else + $old_trans = false; $currency = get_bank_account_currency($from_account); $bank_gl_account = get_bank_gl_account($from_account); // the gl items are already inversed/negated for type 2 (deposit) - $total_amount = $items->gl_items_total(); + $total_amount = $cart->gl_items_total(); if ($person_type_id == PT_CUSTOMER) { // we need to add a customer transaction record // convert to customer currency - if (!isset($settled_amount)) // leaved for backward/ext compatibility - $cust_amount = exchange_from_to(abs($total_amount), $currency, get_customer_currency($person_id), $date_); + if (!isset($cart->settled_amount)) // leaved for backward/ext compatibility + $cust_amount = exchange_from_to(abs($total_amount), $currency, get_customer_currency($person_id), $cart->tran_date); else - $cust_amount = $settled_amount; + $cust_amount = $cart->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_, + $trans_no = write_customer_trans($trans_type, 0, $person_id, $cart->person_detail_id, $cart->tran_date, $ref, $cust_amount); - move_trans_attachments($trans_type, $old_trans, $trans_no); + if ($old_trans) + 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 - 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_); + if (!isset($cart->settled_amount)) // leaved for for backward/ext compatibility + $supp_amount = exchange_from_to(abs($total_amount), $currency, get_supplier_currency($person_id), $cart->tran_date); else - $supp_amount = $settled_amount; + $supp_amount = $cart->settled_amount; if ($trans_type == ST_BANKPAYMENT) $supp_amount = -$supp_amount; - $trans_no = write_supp_trans($trans_type, 0, $person_id, $date_, '', + $trans_no = write_supp_trans($trans_type, 0, $person_id, $cart->tran_date, '', $ref, "", $supp_amount, 0, 0); - move_trans_attachments($trans_type, $old_trans, $trans_no); + if ($old_trans) + move_trans_attachments($trans_type, $old_trans, $trans_no); } else { @@ -472,12 +464,11 @@ function write_bank_transaction($trans_type, $trans_no, $from_account, $items, $ // do the source account postings add_bank_trans($trans_type, $trans_no, $from_account, $ref, - $date_, -$total_amount, + $cart->tran_date, -$total_amount, $person_type_id, $person_id, - $currency, - "Cannot insert a source bank transaction"); + $currency); $total = 0; - foreach ($items->gl_items as $gl_item) + foreach ($cart->gl_items as $gl_item) { $is_bank_to = is_bank_account($gl_item->code_id); @@ -488,60 +479,65 @@ function write_bank_transaction($trans_type, $trans_no, $from_account, $items, $ } // do the destination account postings - $total += add_gl_trans($trans_type, $trans_no, $date_, $gl_item->code_id, + $total += add_gl_trans($trans_type, $trans_no, $cart->tran_date, $gl_item->code_id, $gl_item->dimension_id, $gl_item->dimension2_id, $gl_item->reference, $gl_item->amount, $currency, $person_type_id, $person_id); if ($is_bank_to) { add_bank_trans($trans_type, $trans_no, $is_bank_to, $ref, - $date_, $gl_item->amount, - $person_type_id, $person_id, $currency, - "Cannot insert a destination bank transaction"); + $cart->tran_date, $gl_item->amount, + $person_type_id, $person_id, $currency); if ($do_exchange_variance) { - add_exchange_variation($trans_no1, $date_, $is_bank_to, $gl_item->code_id, + add_exchange_variation($trans_no1, $cart->tran_date, $is_bank_to, $gl_item->code_id, $currency, $person_type_id, $person_id); } } // store tax details if the gl account is a tax account $amount = $gl_item->amount; - $ex_rate = get_exchange_rate_from_home_currency($currency, $date_); + $ex_rate = get_exchange_rate_from_home_currency($currency, $cart->tran_date); add_gl_tax_details($gl_item->code_id, $trans_type, $trans_no, -$amount, - $ex_rate, $date_, $memo_); + $ex_rate, $cart->tran_date, $cart->memo_); } // do the source account postings - add_gl_trans($trans_type, $trans_no, $date_, $bank_gl_account, 0, 0, $memo_, + add_gl_trans($trans_type, $trans_no, $cart->tran_date, $bank_gl_account, 0, 0, $cart->memo_, -$total, null, $person_type_id, $person_id); if ($do_exchange_variance) { - if ($exchanged || add_exchange_variation($trans_no1, $date_, $from_account, $bank_gl_account, + if ($exchanged || add_exchange_variation($trans_no1, $cart->tran_date, $from_account, $bank_gl_account, $currency, $person_type_id, $person_id)) { - $ref1 = $Refs->get_next(ST_JOURNAL); + $ref1 = $Refs->get_next(ST_JOURNAL, null, $cart->tran_date); $Refs->save(ST_JOURNAL, $trans_no1, $ref1); - add_audit_trail(ST_JOURNAL, $trans_no1, $date_); + add_audit_trail(ST_JOURNAL, $trans_no1, $cart->tran_date); } } - add_comments($trans_type, $trans_no, $date_, $memo_); + add_comments($trans_type, $trans_no, $cart->tran_date, $cart->memo_); $Refs->save($trans_type, $trans_no, $ref); - add_audit_trail($trans_type, $trans_no, $date_); + add_audit_trail($trans_type, $trans_no, $cart->tran_date); // 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.")); + if ($old_trans) + { + $msg = void_transaction($trans_type, $old_trans, Today(), _("Document reentered.")); + if ($msg) + { + display_error($msg); + return false; + } + } - $args->trans_no = $trans_no; + $cart->order_id = $trans_no; hook_db_postwrite($args, $trans_type); - if ($use_transaction) - commit_transaction(); - return array($trans_type, $trans_no); + commit_transaction(); + }