From eefe5267ed97b38ac66179141994fc8c9dd09ea8 Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Tue, 13 Aug 2019 11:07:39 +0200 Subject: [PATCH 1/1] Added missing field for bank charges in bank_trans, rewritten bank transaction views to show charges. --- gl/bank_account_reconcile.php | 7 +++ gl/includes/db/gl_db_bank_accounts.inc | 2 +- gl/includes/db/gl_db_bank_trans.inc | 19 ++++---- gl/includes/db/gl_db_banking.inc | 10 ++--- gl/inquiry/bank_inquiry.php | 11 +++-- includes/ui/items_cart.inc | 2 +- includes/ui/ui_view.inc | 8 ++-- .../includes/db/work_order_costing_db.inc | 2 +- purchasing/includes/db/supp_payment_db.inc | 2 +- purchasing/includes/db/supp_trans_db.inc | 2 +- purchasing/includes/db/suppalloc_db.inc | 1 + purchasing/view/view_supp_payment.php | 36 ++++++---------- reporting/rep601.php | 38 +++++++++------- reporting/rep602.php | 43 +++++++++++-------- sales/customer_payments.php | 5 +-- sales/includes/db/cust_trans_db.inc | 12 +++--- sales/includes/db/payment_db.inc | 34 +-------------- sales/view/view_receipt.php | 10 ++--- sql/alter2.5.sql | 19 ++++++++ sql/en_US-demo.sql | 11 ++--- sql/en_US-new.sql | 1 + 21 files changed, 140 insertions(+), 135 deletions(-) diff --git a/gl/bank_account_reconcile.php b/gl/bank_account_reconcile.php index a5738946..ae28a325 100644 --- a/gl/bank_account_reconcile.php +++ b/gl/bank_account_reconcile.php @@ -85,6 +85,12 @@ function fmt_credit($row) return $value>0 ? price_format($value) : ''; } +function bank_fee($row) +{ + $value = -$row["charge"]; + return $value > 0 ? price_format($value) : ''; +} + function fmt_person($trans) { return get_counterparty_name($trans["type"], $trans["trans_no"]); @@ -269,6 +275,7 @@ display_heading($act['bank_account_name']." - ".$act['bank_curr_code']); _("Date") => 'date', _("Debit") => array('align'=>'right', 'fun'=>'fmt_debit'), _("Credit") => array('align'=>'right','insert'=>true, 'fun'=>'fmt_credit'), + _("Bank fees") => array('align'=>'right','insert'=>true, 'fun'=>'bank_fee'), _("Person/Item") => array('fun'=>'fmt_person'), _("Memo") => array('fun'=>'fmt_memo'), array('insert'=>true, 'fun'=>'gl_view'), diff --git a/gl/includes/db/gl_db_bank_accounts.inc b/gl/includes/db/gl_db_bank_accounts.inc index 01feb5d1..12238c40 100644 --- a/gl/includes/db/gl_db_bank_accounts.inc +++ b/gl/includes/db/gl_db_bank_accounts.inc @@ -308,7 +308,7 @@ function get_ending_reconciled($bank_account, $bank_date) function get_sql_for_bank_account_reconcile($bank_account, $date) { $sql = "SELECT bt.type, trans_no, ref, trans_date, - amount, person_id, person_type_id, reconciled, bt.id, c.memo_ + amount, charge, person_id, person_type_id, reconciled, bt.id, c.memo_ FROM ".TB_PREF."bank_trans bt LEFT JOIN ".TB_PREF."comments c ON c.type = bt.type AND c.id = bt.trans_no WHERE bank_act = ".db_escape($bank_account) . " diff --git a/gl/includes/db/gl_db_bank_trans.inc b/gl/includes/db/gl_db_bank_trans.inc index e8d81905..afbd2a2d 100644 --- a/gl/includes/db/gl_db_bank_trans.inc +++ b/gl/includes/db/gl_db_bank_trans.inc @@ -16,7 +16,7 @@ // $date_ is display date (non-sql) function add_bank_trans($type, $trans_no, $bank_act, $ref, $date_, - $amount, $person_type_id, $person_id, $currency="", $rate=0) + $amount, $charge=0, $person_type_id=0, $person_id=0, $currency="", $rate=0) { $sqlDate = date2sql($date_); @@ -35,10 +35,10 @@ function add_bank_trans($type, $trans_no, $bank_act, $ref, $date_, $amount_bank = round2($amount_bank, user_price_dec()); $sql = "INSERT INTO ".TB_PREF."bank_trans (type, trans_no, bank_act, ref, - trans_date, amount, person_type_id, person_id) "; + trans_date, amount, charge, person_type_id, person_id) "; $sql .= "VALUES ($type, $trans_no, '$bank_act', ".db_escape($ref).", '$sqlDate', - ".db_escape($amount_bank).", ".db_escape($person_type_id) + ".db_escape($amount_bank).", ".db_escape($charge).", ".db_escape($person_type_id) .", ". db_escape($person_id).")"; db_query($sql, "The bank transaction could not be inserted"); @@ -51,7 +51,8 @@ function exists_bank_trans($type, $type_no) $sql = "SELECT trans_no FROM ".TB_PREF."bank_trans WHERE type=".db_escape($type) - ." AND trans_no=".db_escape($type_no); + ." AND trans_no=".db_escape($type_no) + ." AND (amount!=0 OR charge!=0)"; $result = db_query($sql, "Cannot retreive a bank transaction"); return (db_num_rows($result) > 0); @@ -99,7 +100,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 + AND (amount != 0 OR charge != 0) ORDER BY trans_date, t.id"; return db_query($sql,"The transactions for '" . $bank_account . "' could not be retrieved"); @@ -110,7 +111,7 @@ function get_bank_trans_for_bank_account($bank_account, $from, $to) function get_balance_before_for_bank_account($bank_account, $from) { $from = date2sql($from); - $sql = "SELECT SUM(amount) + $sql = "SELECT SUM(amount+charge) FROM ".TB_PREF."bank_trans WHERE bank_act=".db_escape($bank_account) . " AND trans_date < '$from'"; @@ -143,7 +144,7 @@ function void_bank_trans($type, $type_no, $nested=false) begin_transaction(__FUNCTION__, func_get_args()); $sql = "UPDATE ".TB_PREF."bank_trans - SET amount=0 + SET amount=0, charge=0 WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no); db_query($sql, "could not void bank transactions for type=$type and trans_no=$type_no"); @@ -185,7 +186,7 @@ function check_bank_account_history($delta_amount, $bank_account, $date=null, $u $balance += $delta_amount; - $sql = "SELECT sum(amount) as amount, trans_date, trans_no, type + $sql = "SELECT SUM(amount+charge) as amount, trans_date, trans_no, type FROM ".TB_PREF."bank_trans WHERE bank_act=".db_escape($bank_account); if ($date) @@ -217,7 +218,7 @@ function check_void_bank_trans($type, $type_no) $moves = get_bank_trans($type, $type_no); while ($trans = db_fetch($moves)) { if ($trans['amount'] > 0) { // skip transfer input part - return check_bank_account_history(-$trans['amount'], $trans['bank_act'], sql2date($trans['trans_date'])) == null; + return check_bank_account_history(-$trans['amount']-$trans['charge'], $trans['bank_act'], sql2date($trans['trans_date'])) == null; } } return true; diff --git a/gl/includes/db/gl_db_banking.inc b/gl/includes/db/gl_db_banking.inc index 03623b6e..f0d85eb9 100644 --- a/gl/includes/db/gl_db_banking.inc +++ b/gl/includes/db/gl_db_banking.inc @@ -239,7 +239,7 @@ function add_bank_transfer($from_account, $to_account, $date_, -($amount + $charge), $currency); add_bank_trans($trans_type, $trans_no, $from_account, $ref, - $date_, -($amount + $charge), + $date_, -$amount, -$charge, PT_MISC, $person_id, $currency); if ($SysPrefs->auto_currency_revaluation()) { @@ -272,7 +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); + $date_, $to_amount, 0, PT_MISC, $person_id); if ($SysPrefs->auto_currency_revaluation()) { @@ -361,7 +361,7 @@ function update_bank_transfer( 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, + $from_account, $to_account, $date_, $amount, $charge, $ref, $memo_, $charge, $target_amount ); commit_transaction(); @@ -464,7 +464,7 @@ function write_bank_transaction(&$cart) // do the source account postings add_bank_trans($trans_type, $trans_no, $from_account, $ref, - $cart->tran_date, -$total_amount, + $cart->tran_date, -$total_amount, 0, $person_type_id, $person_id, $currency); $total = 0; @@ -486,7 +486,7 @@ function write_bank_transaction(&$cart) if ($is_bank_to) { add_bank_trans($trans_type, $trans_no, $is_bank_to, $ref, - $cart->tran_date, $gl_item->amount, + $cart->tran_date, $gl_item->amount, 0, $person_type_id, $person_id, $currency); if ($do_exchange_variance) { diff --git a/gl/inquiry/bank_inquiry.php b/gl/inquiry/bank_inquiry.php index e273099d..7ea2a998 100644 --- a/gl/inquiry/bank_inquiry.php +++ b/gl/inquiry/bank_inquiry.php @@ -69,12 +69,12 @@ 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"), _("Fee"), _("Balance"), _("Person/Item"), _("Memo"), "", ""); table_header($th); $bfw = get_balance_before_for_bank_account($_POST['bank_account'], $_POST['TransAfterDate']); -$credit = $debit = 0; +$credit = $debit = $charges = 0; start_row("class='inquirybg' style='font-weight:bold'"); label_cell(_("Opening Balance")." - ".$_POST['TransAfterDate'], "colspan=4"); display_debit_or_credit_cells($bfw); @@ -94,7 +94,7 @@ while ($myrow = db_fetch($result)) alt_table_row_color($k); - $running_total += $myrow["amount"]; + $running_total += $myrow["amount"]+$myrow["charge"]; $trandate = sql2date($myrow["trans_date"]); label_cell($systypes_array[$myrow["type"]]); @@ -102,6 +102,7 @@ while ($myrow = db_fetch($result)) label_cell(get_trans_view_str($myrow["type"],$myrow["trans_no"],$myrow['ref'])); label_cell($trandate); display_debit_or_credit_cells($myrow["amount"]); + amount_cell(-$myrow['charge']); amount_cell($running_total); label_cell(payment_person_name($myrow["person_type_id"],$myrow["person_id"])); @@ -116,7 +117,8 @@ while ($myrow = db_fetch($result)) $debit += $myrow["amount"]; else $credit += $myrow["amount"]; - + $credit += $myrow["charge"]; + $charges += $myrow["charge"]; if ($j == 12) { $j = 1; @@ -131,6 +133,7 @@ label_cell(_("Ending Balance")." - ". $_POST['TransToDate'], "colspan=4"); amount_cell($debit); amount_cell(-$credit); //display_debit_or_credit_cells($running_total); +amount_cell(-$charges); amount_cell($debit+$credit); label_cell("", "colspan=4"); end_row(); diff --git a/includes/ui/items_cart.inc b/includes/ui/items_cart.inc index fd8c7357..7c9e6ef1 100644 --- a/includes/ui/items_cart.inc +++ b/includes/ui/items_cart.inc @@ -417,7 +417,7 @@ class items_cart // update bank ledger if used foreach($bank_trans as $bank_id => $amount) add_bank_trans($this->trans_type, $this->order_id, $bank_id, $this->reference, - $this->tran_date, $amount, 0, "", $this->currency); + $this->tran_date, $amount, 0, 0, "", $this->currency); // add AP/AR for journal transaction if ($this->trans_type == ST_JOURNAL) diff --git a/includes/ui/ui_view.inc b/includes/ui/ui_view.inc index 40c4966a..c12f3e10 100644 --- a/includes/ui/ui_view.inc +++ b/includes/ui/ui_view.inc @@ -550,7 +550,7 @@ function display_allocations($alloc_result, $total, $title, $show_summary=true) start_table(TABLESTYLE, "width='80%'"); $th = array( _("Type"), _("Number"), _("Date"), _("Total Amount"), - _("Left to Allocate"), _("This Allocation")); + _("Left to Allocate"), _("Discount"), _("This Payment"), _("Allocated")); table_header($th); $k = $total_allocated = 0; @@ -565,19 +565,21 @@ function display_allocations($alloc_result, $total, $title, $show_summary=true) $alloc_row['amt'] = round2($alloc_row['amt'], user_price_dec()); amount_cell(abs($alloc_row['Total'])); amount_cell(abs($alloc_row['Total']) - $alloc_row['alloc']); + amount_cell($alloc_row['discount']); + amount_cell($alloc_row['amt']-$alloc_row['discount']); amount_cell($alloc_row['amt']); end_row(); $total_allocated += $alloc_row['amt']; } start_row(); - label_cell(_("Total Allocated:"), "align=right colspan=5"); + label_cell(_("Total Allocated:"), "align=right colspan=7"); amount_cell($total_allocated); end_row(); if ($show_summary) { start_row(); - label_cell(_("Left to Allocate:"), "align=right colspan=5"); + label_cell(_("Left to Allocate:"), "align=right colspan=7"); $total = round2($total, user_price_dec()); amount_cell($total - $total_allocated); end_row(); diff --git a/manufacturing/includes/db/work_order_costing_db.inc b/manufacturing/includes/db/work_order_costing_db.inc index f610356b..3ca37aa1 100644 --- a/manufacturing/includes/db/work_order_costing_db.inc +++ b/manufacturing/includes/db/work_order_costing_db.inc @@ -139,7 +139,7 @@ function add_wo_costs_journal($wo_id, $amount, $cost_type, $cr_acc, $date, $dim1 if ($is_bank_to) { add_bank_trans(ST_JOURNAL, $journal_id, $is_bank_to, "", - $date, -$amount, PT_WORKORDER, $wo_id, get_company_currency()); + $date, -$amount, 0, PT_WORKORDER, $wo_id, get_company_currency()); } add_journal(ST_JOURNAL, $journal_id, $amount, $date, get_company_currency(), $ref, '', 1, $date, $date); diff --git a/purchasing/includes/db/supp_payment_db.inc b/purchasing/includes/db/supp_payment_db.inc index 62f56a79..b5adb163 100644 --- a/purchasing/includes/db/supp_payment_db.inc +++ b/purchasing/includes/db/supp_payment_db.inc @@ -80,7 +80,7 @@ function write_supp_payment($trans_no, $supplier_id, $bank_account, /*now enter the bank_trans entry */ add_bank_trans(ST_SUPPAYMENT, $payment_id, $bank_account, $ref, - $date_, -($bank_amount + $bank_charge), PT_SUPPLIER, $supplier_id); + $date_, -$bank_amount, -$bank_charge, PT_SUPPLIER, $supplier_id); add_comments(ST_SUPPAYMENT, $payment_id, $date_, $memo_); diff --git a/purchasing/includes/db/supp_trans_db.inc b/purchasing/includes/db/supp_trans_db.inc index f9781392..4a76ed11 100644 --- a/purchasing/includes/db/supp_trans_db.inc +++ b/purchasing/includes/db/supp_trans_db.inc @@ -69,7 +69,7 @@ function get_supp_trans($trans_no, $trans_type=-1, $supplier_id=null) { // it's a payment so also get the bank account $sql .= ", account.bank_name, account.bank_account_name, account.bank_curr_code, - account.account_type AS BankTransType, bank_trans.amount AS bank_amount, + account.account_type AS BankTransType, bank_trans.amount AS bank_amount, bank_trans.charge AS bank_charge, bank_trans.ref "; } diff --git a/purchasing/includes/db/suppalloc_db.inc b/purchasing/includes/db/suppalloc_db.inc index e24c6d46..a6843bb2 100644 --- a/purchasing/includes/db/suppalloc_db.inc +++ b/purchasing/includes/db/suppalloc_db.inc @@ -241,6 +241,7 @@ function get_allocatable_from_supp_transactions($supplier_id, $trans_no=null, $t trans.supplier_id, supplier.address, amt, + alloc.discount, supp_reference FROM ".TB_PREF."supp_trans as trans," .TB_PREF."suppliers as supplier," diff --git a/purchasing/view/view_supp_payment.php b/purchasing/view/view_supp_payment.php index c9a455f1..cfd796a7 100644 --- a/purchasing/view/view_supp_payment.php +++ b/purchasing/view/view_supp_payment.php @@ -50,34 +50,24 @@ echo "
"; start_table(TABLESTYLE2, "width='80%'"); start_row(); -label_cells(_("To Supplier"), $receipt['supplier_name'], "class='tableheader2'"); -label_cells(_("From Bank Account"), $receipt['bank_account_name'], "class='tableheader2'"); -label_cells(_("Date Paid"), sql2date($receipt['tran_date']), "class='tableheader2'"); -end_row(); -start_row(); -if ($show_currencies) - label_cells(_("Payment Currency"), $receipt['bank_curr_code'], "class='tableheader2'"); +$act = $show_currencies ? $receipt['bank_account_name'].' '.$receipt['bank_curr_code'] : $receipt['bank_account_name']; +label_cells(_("From Bank Account"), $act, "class='tableheader2'"); label_cells(_("Amount"), number_format2(-$receipt['bank_amount'], user_price_dec()), "class='tableheader2'"); -if ($receipt['ov_discount'] != 0) - label_cells(_("Discount"), number_format2(-$receipt['ov_discount']*$receipt['rate'], user_price_dec()), "class='tableheader2'"); -else - label_cells(_("Payment Type"), $bank_transfer_types[$receipt['BankTransType']], "class='tableheader2'"); +label_cells(_("Bank Fee"), number_format2(-$receipt['bank_charge'], user_price_dec()), "class='tableheader2'"); end_row(); + start_row(); -if ($show_currencies) -{ - label_cells(_("Supplier's Currency"), $receipt['curr_code'], "class='tableheader2'"); -} -if ($show_both_amounts) - label_cells(_("Amount"), number_format2(-$receipt['Total'], user_price_dec()), "class='tableheader2'"); +$supp = $show_currencies ? $receipt['supplier_name'].' '.$receipt['curr_code'] : $receipt['supplier_name']; +label_cells(_("To Supplier"), $supp, "class='tableheader2'"); label_cells(_("Reference"), $receipt['ref'], "class='tableheader2'"); +label_cells(_("Date Paid"), sql2date($receipt['tran_date']), "class='tableheader2'"); end_row(); -if ($receipt['ov_discount'] != 0) -{ - start_row(); - label_cells(_("Payment Type"), $bank_transfer_types[$receipt['BankTransType']], "class='tableheader2'"); - end_row(); -} + +start_row(); + label_cells(_("Discount"), number_format2(-$receipt['ov_discount']*$receipt['rate'], user_price_dec()), "class='tableheader2'"); + label_cells(_("Tottal settled"), number_format2(-$receipt['Total'], user_price_dec()), "class='tableheader2'"); +end_row(); + comments_display_row(ST_SUPPAYMENT, $trans_no); end_table(1); diff --git a/reporting/rep601.php b/reporting/rep601.php index 1614e116..9acf1ce7 100644 --- a/reporting/rep601.php +++ b/reporting/rep601.php @@ -32,7 +32,7 @@ print_bank_transactions(); function get_bank_balance_to($to, $account) { $to = date2sql($to); - $sql = "SELECT SUM(amount) FROM ".TB_PREF."bank_trans WHERE bank_act='$account' + $sql = "SELECT SUM(amount+charge) FROM ".TB_PREF."bank_trans WHERE bank_act='$account' AND trans_date < '$to'"; $result = db_query($sql, "The starting balance on hand could not be calculated"); $row = db_fetch_row($result); @@ -47,6 +47,7 @@ function get_bank_transactions($from, $to, $account) WHERE bank_act = '$account' AND trans_date >= '$from' AND trans_date <= '$to' + AND (amount !=0 OR charge !=0) ORDER BY trans_date, id"; return db_query($sql,"The transactions for '$account' could not be retrieved"); @@ -72,12 +73,12 @@ function print_bank_transactions() $rep = new FrontReport(_('Bank Statement'), "BankStatement", user_pagesize(), 9, $orientation); $dec = user_price_dec(); - $cols = array(0, 90, 120, 170, 225, 350, 400, 460, 520); + $cols = array(0, 90, 120, 170, 225, 350, 400, 450, 500, 550); - $aligns = array('left', 'left', 'left', 'left', 'left', 'right', 'right', 'right'); + $aligns = array('left', 'left', 'left', 'left', 'left', 'right', 'right', 'right', 'right'); $headers = array(_('Type'), _('#'), _('Reference'), _('Date'), _('Person/Item'), - _('Debit'), _('Credit'), _('Balance')); + _('Debit'), _('Credit'), _('Fee'), _('Balance')); $account = get_bank_account($acc); $act = $account['bank_account_name']." - ".$account['bank_curr_code']." - ".$account['bank_account_number']; @@ -103,13 +104,13 @@ function print_bank_transactions() $rep->TextCol(0, 3, $act); $rep->TextCol(3, 5, _('Opening Balance')); if ($prev_balance > 0.0) - $rep->AmountCol(5, 6, abs($prev_balance), $dec); + $rep->AmountCol(5, 6, $prev_balance, $dec); else - $rep->AmountCol(6, 7, abs($prev_balance), $dec); + $rep->AmountCol(6, 7, -$prev_balance, $dec); $rep->Font(); $total = $prev_balance; $rep->NewLine(2); - $total_debit = $total_credit = 0; + $total_debit = $total_credit = $charges = 0; if ($rows > 0) { // Keep a running total as we loop through @@ -119,7 +120,9 @@ function print_bank_transactions() { if ($zero == 0 && $myrow['amount'] == 0.0) continue; - $total += $myrow['amount']; + $total += $myrow['amount']+$myrow['charge']; + $charges += $myrow['charge']; + $amount = abs($myrow['amount']); $rep->TextCol(0, 1, $systypes_array[$myrow["type"]]); $rep->TextCol(1, 2, $myrow['trans_no']); @@ -128,15 +131,17 @@ function print_bank_transactions() $rep->TextCol(4, 5, get_counterparty_name($myrow["type"], $myrow["trans_no"], false)); if ($myrow['amount'] > 0.0) { - $rep->AmountCol(5, 6, abs($myrow['amount']), $dec); - $total_debit += abs($myrow['amount']); + $rep->AmountCol(5, 6, $amount, $dec); + $total_debit += $amount; } else { - $rep->AmountCol(6, 7, abs($myrow['amount']), $dec); - $total_credit += abs($myrow['amount']); + $rep->AmountCol(6, 7, $amount, $dec); + $total_credit += $amount; } - $rep->AmountCol(7, 8, $total, $dec); + if ($myrow['charge']) + $rep->AmountCol(7, 8, -$myrow['charge'], $dec); + $rep->AmountCol(8, 9, $total, $dec); $rep->NewLine(); if ($rep->row < $rep->bottomMargin + $rep->lineHeight) { @@ -148,17 +153,18 @@ function print_bank_transactions() } // Print totals for the debit and credit columns. - $rep->TextCol(3, 5, _("Total Debit / Credit")); + $rep->TextCol(3, 5, _("Total Debit / Credit / Fees")); $rep->AmountCol(5, 6, $total_debit, $dec); $rep->AmountCol(6, 7, $total_credit, $dec); + $rep->AmountCol(7, 8, -$charges, $dec); $rep->NewLine(2); $rep->Font('bold'); $rep->TextCol(3, 5, _("Ending Balance")); if ($total > 0.0) - $rep->AmountCol(5, 6, abs($total), $dec); + $rep->AmountCol(5, 6, $total, $dec); else - $rep->AmountCol(6, 7, abs($total), $dec); + $rep->AmountCol(6, 7, -$total, $dec); $rep->Font(); $rep->Line($rep->row - $rep->lineHeight + 4); $rep->NewLine(2, 1); diff --git a/reporting/rep602.php b/reporting/rep602.php index dd7c5427..c75efdf3 100644 --- a/reporting/rep602.php +++ b/reporting/rep602.php @@ -33,7 +33,7 @@ print_bank_transactions_reconcile(); function get_bank_balance_to($to, $account) { $to = date2sql($to); - $sql = "SELECT SUM(amount) FROM ".TB_PREF."bank_trans WHERE bank_act='$account' + $sql = "SELECT SUM(amount+charge) FROM ".TB_PREF."bank_trans WHERE bank_act='$account' AND trans_date < '$to'"; $result = db_query($sql, "The starting balance on hand could not be calculated"); $row = db_fetch_row($result); @@ -51,6 +51,7 @@ function get_bank_transactions($from, $to, $account) WHERE trans.bank_act = '$account' AND trans_date >= '$from' AND trans_date <= '$to' + AND (amount !=0 OR charge !=0) ORDER BY trans_date,trans.id"; return db_query($sql,"The transactions for '$account' could not be retrieved"); @@ -73,12 +74,12 @@ function print_bank_transactions_reconcile() $rep = new FrontReport(_('Bank Statement w/Reconcile'), "BankStatementReconcile", user_pagesize(), 9, "L"); $dec = user_price_dec(); - $cols = array(0, 90, 120, 170, 225, 450, 500, 550, 600, 660, 700); + $cols = array(0, 90, 120, 170, 225, 400, 450, 500, 550, 600, 660, 700); - $aligns = array('left', 'left', 'left', 'left', 'left', 'right', 'right', 'right', 'center', 'left'); + $aligns = array('left', 'left', 'left', 'left', 'left', 'right', 'right', 'right', 'right', 'center', 'left'); $headers = array(_('Type'), _('#'), _('Reference'), _('Date'), _('Person/Item'), - _('Debit'), _('Credit'), _('Balance'), _('Reco Date'), _('Narration')); + _('Debit'), _('Credit'), _('Fee Incl.'), _('Balance'), _('Reco Date'), _('Narration')); $account = get_bank_account($acc); $act = $account['bank_account_name']." - ".$account['bank_curr_code']." - ".$account['bank_account_number']; @@ -110,13 +111,15 @@ function print_bank_transactions_reconcile() $rep->NewLine(2); // Keep a running total as we loop through // the transactions. - $total_debit = $total_credit = 0; + $total_debit = $total_credit = $charges = 0; if ($rows > 0) { while ($myrow=db_fetch($trans)) { - $total += $myrow['amount']; + $total += $myrow['amount']+$myrow['charge']; + $charges += $myrow['charge']; + $amount = abs($myrow['amount']); $rep->TextCol(0, 1, $systypes_array[$myrow["type"]]); $rep->TextCol(1, 2, $myrow['trans_no']); @@ -125,18 +128,21 @@ function print_bank_transactions_reconcile() $rep->TextCol(4, 5, payment_person_name($myrow["person_type_id"],$myrow["person_id"], false)); if ($myrow['amount'] > 0.0) { - $rep->AmountCol(5, 6, abs($myrow['amount']), $dec); - $total_debit += abs($myrow['amount']); + $rep->AmountCol(5, 6, $amount, $dec); + $total_debit += $amount; } else { - $rep->AmountCol(6, 7, abs($myrow['amount']), $dec); - $total_credit += abs($myrow['amount']); + $rep->AmountCol(6, 7, $amount, $dec); + $total_credit += $amount; } - $rep->AmountCol(7, 8, $total, $dec); + if ($myrow['charge']) + $rep->AmountCol(7, 8, -$myrow['charge'], $dec); + + $rep->AmountCol(8, 9, $total, $dec); if ($myrow["reconciled"] && $myrow["reconciled"] != '0000-00-00') - $rep->DateCol(8, 9, $myrow["reconciled"], true); - $rep->TextCol(9, 10, $myrow['memo_']); + $rep->DateCol(9, 10, $myrow["reconciled"], true); + $rep->TextCol(10, 11, $myrow['memo_']); $rep->NewLine(); if ($rep->row < $rep->bottomMargin + $rep->lineHeight) { @@ -148,17 +154,18 @@ function print_bank_transactions_reconcile() } // Print totals for the debit and credit columns. - $rep->TextCol(3, 5, _("Total Debit / Credit")); + $rep->TextCol(3, 5, _("Total Debit / Credit / Fees")); $rep->AmountCol(5, 6, $total_debit, $dec); $rep->AmountCol(6, 7, $total_credit, $dec); + $rep->AmountCol(7, 8, -$charges, $dec); $rep->NewLine(2); $rep->Font('bold'); $rep->TextCol(3, 5, _("Ending Balance")); if ($total > 0.0) - $rep->AmountCol(5, 6, abs($total), $dec); + $rep->AmountCol(5, 6, $total, $dec); else - $rep->AmountCol(6, 7, abs($total), $dec); + $rep->AmountCol(6, 7, -$total, $dec); $rep->Font(); $rep->NewLine(2); @@ -174,7 +181,7 @@ function print_bank_transactions_reconcile() // Calculate Bank Balance as per reco $date = date2sql($to); - $sql = "SELECT SUM(IF(reconciled<='$date' AND reconciled !='0000-00-00', amount, 0)) as reconciled, + $sql = "SELECT SUM(IF(reconciled<='$date' AND reconciled !='0000-00-00', amount+charge, 0)) as reconciled, SUM(amount) as books_total FROM ".TB_PREF."bank_trans trans WHERE bank_act=".db_escape($account['id'])." @@ -211,7 +218,7 @@ function print_bank_transactions_reconcile() $rep->NewLine(2); $rep->Line($rep->row - $rep->lineHeight + 4); - $rep->NewLine(2, 1); + $rep->NewLine(2, 1); } $rep->End(); diff --git a/sales/customer_payments.php b/sales/customer_payments.php index 68b0c624..36d704a1 100644 --- a/sales/customer_payments.php +++ b/sales/customer_payments.php @@ -276,11 +276,10 @@ if (isset($_GET['trans_no']) && $_GET['trans_no'] > 0 ) $_POST['BranchID'] = $myrow["branch_code"]; $_POST['bank_account'] = $myrow["bank_act"]; $_POST['ref'] = $myrow["reference"]; - $charge = get_cust_bank_charge(ST_CUSTPAYMENT, $_POST['trans_no']); - $_POST['charge'] = price_format($charge); + $_POST['charge'] = price_format(-$myrow['bank_charge']); $_POST['DateBanked'] = sql2date($myrow['tran_date']); $_POST["amount"] = price_format($myrow['Total'] - $myrow['ov_discount']); - $_POST["bank_amount"] = price_format($myrow['bank_amount']+$charge); + $_POST["bank_amount"] = price_format($myrow['bank_amount']); $_POST["discount"] = price_format($myrow['ov_discount']); $_POST["memo_"] = get_comments_string(ST_CUSTPAYMENT,$_POST['trans_no']); diff --git a/sales/includes/db/cust_trans_db.inc b/sales/includes/db/cust_trans_db.inc index 453a3b3f..cb1b4b64 100644 --- a/sales/includes/db/cust_trans_db.inc +++ b/sales/includes/db/cust_trans_db.inc @@ -135,7 +135,7 @@ function get_customer_trans($trans_id, $trans_type, $customer_id=null) $sql .= ",bank_act,".TB_PREF."bank_accounts.bank_name, ".TB_PREF."bank_accounts.bank_account_name, ".TB_PREF."bank_accounts.account_type AS BankTransType, ".TB_PREF."bank_accounts.bank_curr_code, - ".TB_PREF."bank_trans.amount as bank_amount"; + bt.amount as bank_amount, bt.charge bank_charge"; } if ($trans_type == ST_SALESINVOICE || $trans_type == ST_CUSTCREDIT || $trans_type == ST_CUSTDELIVERY) { @@ -160,7 +160,7 @@ function get_customer_trans($trans_id, $trans_type, $customer_id=null) if ($trans_type == ST_CUSTPAYMENT || $trans_type == ST_BANKDEPOSIT) { // it's a payment so also get the bank account - $sql .= ", ".TB_PREF."bank_trans, ".TB_PREF."bank_accounts"; + $sql .= ", ".TB_PREF."bank_trans bt, ".TB_PREF."bank_accounts"; } if ($trans_type == ST_SALESINVOICE || $trans_type == ST_CUSTCREDIT || $trans_type == ST_CUSTDELIVERY) { @@ -182,10 +182,10 @@ function get_customer_trans($trans_id, $trans_type, $customer_id=null) if ($trans_type == ST_CUSTPAYMENT || $trans_type == ST_BANKDEPOSIT) { // it's a payment so also get the bank account - $sql .= " AND ".TB_PREF."bank_trans.trans_no =".db_escape($trans_id)." - AND ".TB_PREF."bank_trans.type=$trans_type - AND ".TB_PREF."bank_trans.amount != 0 - AND ".TB_PREF."bank_accounts.id=".TB_PREF."bank_trans.bank_act "; + $sql .= " AND bt.trans_no =".db_escape($trans_id)." + AND bt.type=$trans_type + AND bt.amount != 0 + AND ".TB_PREF."bank_accounts.id=bt.bank_act "; } if ($trans_type == ST_SALESINVOICE || $trans_type == ST_CUSTCREDIT || $trans_type == ST_CUSTDELIVERY) { // it's an invoice so also get the shipper diff --git a/sales/includes/db/payment_db.inc b/sales/includes/db/payment_db.inc index 7eb77dfb..18ede1c6 100644 --- a/sales/includes/db/payment_db.inc +++ b/sales/includes/db/payment_db.inc @@ -104,7 +104,7 @@ function write_customer_payment($trans_no, $customer_id, $branch_id, $bank_accou /*now enter the bank_trans entry */ add_bank_trans(ST_CUSTPAYMENT, $payment_no, $bank_account, $ref, - $date_, $bank_amount - $charge, PT_CUSTOMER, $customer_id); + $date_, $bank_amount, -$charge, PT_CUSTOMER, $customer_id); add_comments(ST_CUSTPAYMENT, $payment_no, $date_, $memo_); @@ -132,35 +132,3 @@ function void_customer_payment($type, $type_no) commit_transaction(); } -/* - Retrieve bank charge amount from GL postings for customer payment. - . Bank charge is not stored explicitly in database as of 2.3.xx - . Due to roundings the retrieved charge can differ from the original amount when bank_curr!=home_curr && bank_curr!=cust_curr -*/ -function get_cust_bank_charge($type, $trans_no) -{ - - // restore charge amount from amounts in bank currency if possible, otherwise calculate from GL posting with exchange rate used for amount posting - $sql = "SELECT IF(act.bank_curr_code=home_curr.value, charge.amount, - IF(act.bank_curr_code=debtor.curr_code, -(trans.amount-ar.ov_amount+ar.ov_discount), - IFNULL(charge.amount*trans.amount/pmt.amount, 0))) - FROM ".TB_PREF."bank_trans trans - LEFT JOIN ".TB_PREF."bank_accounts act ON trans.bank_act=act.id - LEFT JOIN ".TB_PREF."sys_prefs charge_act ON charge_act.name='bank_charge_act' - LEFT JOIN ".TB_PREF."sys_prefs home_curr ON home_curr.name='curr_default' - LEFT JOIN ".TB_PREF."gl_trans charge ON charge.type=trans.type AND charge.type_no=trans.trans_no AND charge.account=charge_act.value - LEFT JOIN ".TB_PREF."gl_trans pmt ON pmt.type=trans.type AND pmt.type_no=trans.trans_no AND pmt.account=act.account_code - LEFT JOIN ".TB_PREF."debtors_master debtor ON trans.person_id=debtor.debtor_no AND trans.person_type_id=".PT_CUSTOMER." - LEFT JOIN ".TB_PREF."debtor_trans ar ON trans.type=ar.type AND trans.trans_no=ar.trans_no - WHERE pmt.amount!=0 AND charge.amount!=0 AND trans.amount!=0 - AND trans.type=".db_escape($type)." AND trans.trans_no=".db_escape($trans_no); - - $result = db_query($sql, "cannot retrieve bank charge"); - - if (!db_num_rows($result)) - return 0; - - $myrow = db_fetch($result); - return $myrow['0']; -} - diff --git a/sales/view/view_receipt.php b/sales/view/view_receipt.php index df429975..18d1fa00 100644 --- a/sales/view/view_receipt.php +++ b/sales/view/view_receipt.php @@ -35,6 +35,11 @@ display_heading(sprintf(_("Customer Payment #%d"),$trans_id)); echo "
"; start_table(TABLESTYLE, "width='80%'"); start_row(); +label_cells(_("Into Bank Account"), $receipt['bank_account_name'].' ['.$receipt['bank_curr_code'].']', "class='tableheader2'"); +label_cells(_("Bank Amount"), price_format($receipt['bank_amount']+$receipt['bank_charge']), "class='tableheader2'"); +label_cells(_("Bank Fee"), price_format(abs($receipt['bank_charge'])), "class='tableheader2'"); +end_row(); +start_row(); label_cells(_("From Customer"), $receipt['DebtorName'], "class='tableheader2'"); label_cells(_("Reference"), $receipt['reference'], "class='tableheader2'"); label_cells(_("Date of Deposit"), sql2date($receipt['tran_date']), "class='tableheader2'"); @@ -44,11 +49,6 @@ label_cells(_("Customer Currency"), $receipt['curr_code'], "class='tableheader2' label_cells(_("Amount"), price_format($receipt['Total'] - $receipt['ov_discount']), "class='tableheader2'"); label_cells(_("Discount"), price_format($receipt['ov_discount']), "class='tableheader2'"); end_row(); -start_row(); -label_cells(_("Into Bank Account"), $receipt['bank_account_name'].' ['.$receipt['bank_curr_code'].']', "class='tableheader2'"); -label_cells(_("Bank Amount"), price_format($receipt['bank_amount']), "class='tableheader2'"); -label_cells(_("Payment Type"), $bank_transfer_types[$receipt['BankTransType']], "class='tableheader2'"); -end_row(); comments_display_row(ST_CUSTPAYMENT, $trans_id); end_table(1); diff --git a/sql/alter2.5.sql b/sql/alter2.5.sql index bc8c1de4..42fbc9f4 100644 --- a/sql/alter2.5.sql +++ b/sql/alter2.5.sql @@ -100,3 +100,22 @@ UPDATE `0_supp_allocations` sa LEFT JOIN `0_supp_trans` trans ON trans.`type`=sa.`trans_type_to` AND trans.`trans_no`=sa.`trans_no_to` SET sa.discount=pay.ov_discount WHERE pay.ov_discount != 0 AND pay.ov_amount+pay.ov_discount = trans.ov_amount+trans.ov_gst; + +# bank charge stored in bank_trans +ALTER TABLE `0_bank_trans` ADD COLUMN `charge` double DEFAULT 0 AFTER `amount`; + +UPDATE `0_bank_trans` bt + LEFT JOIN (SELECT trans.type, trans.trans_no, IF(act.bank_curr_code=home_curr.value, charge.amount, + IF(act.bank_curr_code=debtor.curr_code, -(trans.amount-ar.ov_amount+ar.ov_discount), + IFNULL(charge.amount*trans.amount/pmt.amount, 0))) amount + FROM 0_bank_trans trans + LEFT JOIN `0_bank_accounts` act ON trans.bank_act=act.id + LEFT JOIN `0_sys_prefs` charge_act ON charge_act.name='bank_charge_act' + LEFT JOIN `0_sys_prefs` home_curr ON home_curr.name='curr_default' + LEFT JOIN `0_gl_trans` charge ON charge.type=trans.`type` AND charge.type_no=trans.trans_no AND charge.account=charge_act.`value` + LEFT JOIN `0_gl_trans` pmt ON pmt.type=trans.type AND pmt.type_no=trans.trans_no AND pmt.account=act.account_code + LEFT JOIN `0_debtors_master` debtor ON trans.person_id=debtor.debtor_no AND trans.person_type_id=2 + LEFT JOIN `0_debtor_trans` ar ON trans.type=ar.`type` AND trans.trans_no=ar.trans_no + WHERE pmt.amount!=0 AND charge.amount!=0 AND trans.amount!=0) charges ON bt.`type`=charges.`type` AND bt.`trans_no`=charges.`trans_no` + SET bt.charge=IFNULL(-charges.amount,0), + bt.amount=bt.amount+IFNULL(charges.amount,0); diff --git a/sql/en_US-demo.sql b/sql/en_US-demo.sql index 18b69707..f9932181 100644 --- a/sql/en_US-demo.sql +++ b/sql/en_US-demo.sql @@ -149,6 +149,7 @@ CREATE TABLE `0_bank_trans` ( `ref` varchar(40) DEFAULT NULL, `trans_date` date NOT NULL DEFAULT '0000-00-00', `amount` double DEFAULT NULL, + `charge` double DEFAULT NULL, `dimension_id` int(11) NOT NULL DEFAULT '0', `dimension2_id` int(11) NOT NULL DEFAULT '0', `person_type_id` int(11) NOT NULL DEFAULT '0', @@ -164,11 +165,11 @@ CREATE TABLE `0_bank_trans` ( -- Data of table `0_bank_trans` -- INSERT INTO `0_bank_trans` VALUES -('1', '12', '1', '2', '001/2018', '2018-05-10', '6240', '0', '0', '2', '1', NULL), -('2', '12', '2', '2', '002/2018', '2018-05-07', '300', '0', '0', '2', '1', NULL), -('3', '12', '3', '2', '003/2018', '2018-05-07', '0', '0', '0', '2', '1', NULL), -('4', '1', '1', '1', '001/2018', '2018-05-07', '-5', '0', '0', '0', 'Goods received', NULL), -('5', '12', '4', '2', '001/2019', '2019-01-21', '1250', '0', '0', '2', '1', NULL); +('1', '12', '1', '2', '001/2018', '2018-05-10', '6240', '0', '0', '0', '2', '1', NULL), +('2', '12', '2', '2', '002/2018', '2018-05-07', '300', '0', '0', '0', '2', '1', NULL), +('3', '12', '3', '2', '003/2018', '2018-05-07', '0', '0', '0', '0', '2', '1', NULL), +('4', '1', '1', '1', '001/2018', '2018-05-07', '-5', '0', '0', '0', '0', 'Goods received', NULL), +('5', '12', '4', '2', '001/2019', '2019-01-21', '1250', '0', '0', '0', '2', '1', NULL); -- Structure of table `0_bom` -- diff --git a/sql/en_US-new.sql b/sql/en_US-new.sql index e5a50316..7d6b0853 100644 --- a/sql/en_US-new.sql +++ b/sql/en_US-new.sql @@ -111,6 +111,7 @@ CREATE TABLE `0_bank_trans` ( `ref` varchar(40) DEFAULT NULL, `trans_date` date NOT NULL DEFAULT '0000-00-00', `amount` double DEFAULT NULL, + `charge` double DEFAULT NULL, `dimension_id` int(11) NOT NULL DEFAULT '0', `dimension2_id` int(11) NOT NULL DEFAULT '0', `person_type_id` int(11) NOT NULL DEFAULT '0', -- 2.30.2