From 0d191c7bff92b9a9bbc44dab48855477edb99ebf Mon Sep 17 00:00:00 2001 From: Cambell Prince Date: Fri, 9 Jan 2015 20:03:54 +0100 Subject: [PATCH] Added Bank Transfer edition. --- gl/bank_transfer.php | 133 ++++++++++++++++++++++------ gl/includes/db/gl_db_bank_trans.inc | 6 +- gl/includes/db/gl_db_banking.inc | 76 +++++++++++++++- gl/inquiry/bank_inquiry.php | 11 ++- 4 files changed, 192 insertions(+), 34 deletions(-) diff --git a/gl/bank_transfer.php b/gl/bank_transfer.php index 83d3867e..a11352a6 100644 --- a/gl/bank_transfer.php +++ b/gl/bank_transfer.php @@ -25,7 +25,14 @@ if ($use_popup_windows) $js .= get_js_open_window(800, 500); if (user_use_date_picker()) $js .= get_js_date_picker(); -page(_($help_context = "Transfer between Bank Accounts"), false, false, "", $js); + +if (isset($_GET['ModifyTransfer'])) { + $_SESSION['page_title'] = _($help_context = "Modify Bank Account Transfer"); +} else { + $_SESSION['page_title'] = _($help_context = "Bank Account Transfer Entry"); +} + +page($_SESSION['page_title'], false, false, "", $js); check_db_has_bank_accounts(_("There are no bank accounts defined in the system.")); @@ -51,10 +58,42 @@ if (isset($_POST['_DatePaid_changed'])) { //---------------------------------------------------------------------------------------- -function gl_payment_controls() +function gl_payment_controls($trans_no) { global $Refs; + if (!in_ajax()) { + if ($trans_no) { + $result = get_bank_trans(ST_BANKTRANSFER, $trans_no); + + if (db_num_rows($result) != 2) + display_db_error("Bank transfer does not contain two records"); + + $trans1 = db_fetch($result); + $trans2 = db_fetch($result); + + if ($trans1["amount"] < 0) { + $from_trans = $trans1; // from trans is the negative one + $to_trans = $trans2; + } else { + $from_trans = $trans2; + $to_trans = $trans1; + } + $_POST['DatePaid'] = sql2date($to_trans['trans_date']); + $_POST['ref'] = $to_trans['ref']; + $_POST['memo_'] = get_comments_string($to_trans['type'], $trans_no); + $_POST['FromBankAccount'] = $from_trans['bank_act']; + $_POST['ToBankAccount'] = $to_trans['bank_act']; + $_POST['target_amount'] = price_format($to_trans['amount']); + $_POST['amount'] = price_format(-$from_trans['amount']); + } else { + $_POST['ref'] = $Refs->get_next(ST_BANKTRANSFER); + $_POST['memo_'] = ''; + $_POST['FromBankAccount'] = 0; + $_POST['ToBankAccount'] = 0; + $_POST['amount'] = 0; + } + } $home_currency = get_company_currency(); start_form(); @@ -76,7 +115,7 @@ function gl_payment_controls() } date_row(_("Transfer Date:"), 'DatePaid', '', true, 0, 0, 0, null, true); - ref_row(_("Reference:"), 'ref', '', $Refs->get_next(ST_BANKTRANSFER)); + ref_row(_("Reference:"), 'ref', '', $_POST['ref']); table_section(2); @@ -99,16 +138,21 @@ function gl_payment_controls() end_outer_table(1); // outer table - submit_center('AddPayment',_("Enter Transfer"), true, '', 'default'); + if ($trans_no) { + hidden('_trans_no', $trans_no); + submit_center('submit', _("Modify Transfer"), true, '', 'default'); + } else { + submit_center('submit', _("Enter Transfer"), true, '', 'default'); + } end_form(); } //---------------------------------------------------------------------------------------- -function check_valid_entries() +function check_valid_entries($trans_no) { - global $Refs; + global $Refs, $systypes_array; if (!is_date($_POST['DatePaid'])) { @@ -139,18 +183,42 @@ function check_valid_entries() $amnt_tr = input_num('charge') + input_num('amount'); - if ($limit !== null && floatcmp($limit, $amnt_tr) < 0) - { - display_error(sprintf(_("The total bank amount exceeds allowed limit (%s) for source account."), price_format($limit))); + if ($trans_no) { + $problemTransaction = check_bank_transfer( $trans_no, $_POST['FromBankAccount'], $_POST['ToBankAccount'], $_POST['DatePaid'], + $amnt_tr, input_num('target_amount', $amnt_tr)); + + if ($problemTransaction != null ) { + if (!array_key_exists('trans_no', $problemTransaction)) { + display_error(sprintf( + _("This bank transfer change would result in exceeding authorized overdraft limit (%s) of the account '%s'"), + price_format(-$problemTransaction['amount']), $problemTransaction['bank_account_name'] + )); + } else { + display_error(sprintf( + _("This bank transfer change would result in exceeding authorized overdraft limit on '%s' for transaction: %s #%s on %s."), + $problemTransaction['bank_account_name'], $systypes_array[$problemTransaction['type']], + $problemTransaction['trans_no'], sql2date($problemTransaction['trans_date']) + )); + } set_focus('amount'); return false; - } - if ($trans = check_bank_account_history(-$amnt_tr, $_POST['FromBankAccount'], $_POST['DatePaid'])) { - - display_error(sprintf(_("The bank transaction would result in exceed of authorized overdraft limit for transaction: %s #%s on %s."), - $systypes_array[$trans['type']], $trans['trans_no'], sql2date($trans['trans_date']))); - set_focus('amount'); - $input_error = 1; + } + } else { + if (null != ($problemTransaction = check_bank_account_history(-$amnt_tr, $_POST['FromBankAccount'], $_POST['DatePaid']))) { + if (!array_key_exists('trans_no', $problemTransaction)) { + display_error(sprintf( + _("This bank transfer would result in exceeding authorized overdraft limit of the account (%s)"), + price_format(-$problemTransaction['amount']) + )); + } else { + display_error(sprintf( + _("This bank transfer would result in exceeding authorized overdraft limit for transaction: %s #%s on %s."), + $systypes_array[$problemTransaction['type']], $problemTransaction['trans_no'], sql2date($problemTransaction['trans_date']) + )); + } + set_focus('amount'); + return false; + } } if (isset($_POST['charge']) && !check_num('charge', 0)) @@ -171,8 +239,7 @@ function check_valid_entries() return false; } - if (!is_new_reference($_POST['ref'], ST_BANKTRANSFER)) - { + if (! $trans_no && ! is_new_reference($_POST['ref'], ST_BANKTRANSFER)) { display_error(_("The entered reference is already in use.")); set_focus('ref'); return false; @@ -208,25 +275,35 @@ function check_valid_entries() //---------------------------------------------------------------------------------------- -function handle_add_deposit() +function bank_transfer_handle_submit() { - new_doc_date($_POST['DatePaid']); - $trans_no = add_bank_transfer($_POST['FromBankAccount'], $_POST['ToBankAccount'], - $_POST['DatePaid'], input_num('amount'), $_POST['ref'], $_POST['memo_'], input_num('charge'), input_num('target_amount')); + $trans_no = array_key_exists('_trans_no', $_POST) ? $_POST['_trans_no'] : null; + if ($trans_no) { + $trans_no = update_bank_transfer($trans_no, $_POST['FromBankAccount'], $_POST['ToBankAccount'], $_POST['DatePaid'], input_num('amount'), $_POST['ref'], $_POST['memo_'], input_num('charge'), input_num('target_amount')); + } else { + new_doc_date($_POST['DatePaid']); + $trans_no = add_bank_transfer($_POST['FromBankAccount'], $_POST['ToBankAccount'], $_POST['DatePaid'], input_num('amount'), $_POST['ref'], $_POST['memo_'], input_num('charge'), input_num('target_amount')); + } meta_forward($_SERVER['PHP_SELF'], "AddedID=$trans_no"); } //---------------------------------------------------------------------------------------- -if (isset($_POST['AddPayment'])) -{ - if (check_valid_entries() == true) - { - handle_add_deposit(); +$trans_no = ''; +if (!$trans_no && isset($_POST['_trans_no'])) { + $trans_no = $_POST['_trans_no']; +} +if (!$trans_no && isset($_GET['trans_no'])) { + $trans_no = $_GET["trans_no"]; +} + +if (isset($_POST['submit'])) { + if (check_valid_entries($trans_no) == true) { + bank_transfer_handle_submit(); } } -gl_payment_controls(); +gl_payment_controls($trans_no); end_page(); diff --git a/gl/includes/db/gl_db_bank_trans.inc b/gl/includes/db/gl_db_bank_trans.inc index 7fec329d..c3725611 100644 --- a/gl/includes/db/gl_db_bank_trans.inc +++ b/gl/includes/db/gl_db_bank_trans.inc @@ -102,6 +102,7 @@ function get_bank_trans_for_bank_account($bank_account, $from, $to) AND ISNULL(v.date_) AND trans_date >= '$from' AND trans_date <= '$to' + AND amount != 0 ORDER BY trans_date, t.id"; return db_query($sql,"The transactions for '" . $bank_account . "' could not be retrieved"); @@ -178,7 +179,7 @@ function check_bank_account_history($delta_amount, $bank_account, $date=null, $u return null; // unlimited account if (floatcmp($balance, -$delta_amount) < 0) - return array('amount' => $balance - $delta_amount, 'trans_date'=> $date); + return array('amount' => $balance + $delta_amount, 'trans_date'=> date2sql($date)); $balance += $delta_amount; @@ -195,7 +196,10 @@ function check_bank_account_history($delta_amount, $bank_account, $date=null, $u while ($trans = db_fetch($history)) { $balance += $trans['amount']; if ($balance < 0) + { + $trans['amount'] = $balance; return $trans; + } } return null; diff --git a/gl/includes/db/gl_db_banking.inc b/gl/includes/db/gl_db_banking.inc index 964eac0c..8df094b0 100644 --- a/gl/includes/db/gl_db_banking.inc +++ b/gl/includes/db/gl_db_banking.inc @@ -193,7 +193,7 @@ function add_bank_transfer($from_account, $to_account, $date_, $amount, $ref, $memo_, $charge=0, $target_amount=0) { global $Refs, $SysPrefs; - + begin_transaction(); $args = func_get_args(); if (count($args) < 8) $args[] = 0; $args = (object)array_combine(array('from_account', 'to_account', 'date_', 'amount', @@ -282,6 +282,80 @@ 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. // diff --git a/gl/inquiry/bank_inquiry.php b/gl/inquiry/bank_inquiry.php index 86439790..e25e7ba8 100644 --- a/gl/inquiry/bank_inquiry.php +++ b/gl/inquiry/bank_inquiry.php @@ -14,6 +14,7 @@ $path_to_root="../.."; include_once($path_to_root . "/includes/session.inc"); include_once($path_to_root . "/includes/date_functions.inc"); +include_once($path_to_root . "/includes/db_pager.inc"); include_once($path_to_root . "/includes/ui.inc"); include_once($path_to_root . "/includes/data_checks.inc"); @@ -68,7 +69,7 @@ display_heading($act['bank_account_name']." - ".$act['bank_curr_code']); start_table(TABLESTYLE); $th = array(_("Type"), _("#"), _("Reference"), _("Date"), - _("Debit"), _("Credit"), _("Balance"), _("Person/Item"), _("Memo"), ""); + _("Debit"), _("Credit"), _("Balance"), _("Person/Item"), _("Memo"), "", ""); table_header($th); $bfw = get_balance_before_for_bank_account($_POST['bank_account'], $_POST['TransAfterDate']); @@ -78,7 +79,7 @@ start_row("class='inquirybg' style='font-weight:bold'"); label_cell(_("Opening Balance")." - ".$_POST['TransAfterDate'], "colspan=4"); display_debit_or_credit_cells($bfw); label_cell(""); -label_cell("", "colspan=2"); +label_cell("", "colspan=4"); end_row(); $running_total = $bfw; @@ -105,6 +106,9 @@ while ($myrow = db_fetch($result)) label_cell(get_counterparty_name($myrow["type"], $myrow["trans_no"])); label_cell(get_comments_string($myrow["type"], $myrow["trans_no"])); label_cell(get_gl_view_str($myrow["type"], $myrow["trans_no"])); + + label_cell(trans_editor_link($myrow["type"], $myrow["trans_no"])); + end_row(); if ($myrow["amount"] > 0 ) $debit += $myrow["amount"]; @@ -126,8 +130,7 @@ amount_cell($debit); amount_cell(-$credit); //display_debit_or_credit_cells($running_total); amount_cell($debit+$credit); -label_cell(""); -label_cell("", "colspan=2"); +label_cell("", "colspan=4"); end_row(); end_table(2); div_end(); -- 2.30.2