Topic: Bank Account Payment Entry / gl/gl_bank.php?NewPayment=Yes

Hello guys,

I was wondering since FA does not have check process in Banking & General Ledger.

What if a extra field was added after reference, something like check no, if we are using checking account a validation can rule(required) can be added to the text box so that the user is forced to insert either a check number or at-least a reference number.
That's idea number one

My second idea is to reuse the reference number to allow text other than the preset data type.

Change Reference to Check no, add validation rule to not process a bank payment if the field is empty.

The same can also be done for journal entry where the source ref must be required.

@joe  @ap.muthu

Re: Bank Account Payment Entry / gl/gl_bank.php?NewPayment=Yes

Monies as journal entries are unallocatable. It can be allocatable only if passed on as credit/debit notes. Hence we keep the reference optional.

Check number can be done through it's own table in an extension or used passively in the memo field.

In FA 2.5 it might be a good feature to include and useful for reconciliation too. Formatted memo fields can be a good way to get the check number parsed out to link to some other table possibly through a trigger (editions beware).

Re: Bank Account Payment Entry / gl/gl_bank.php?NewPayment=Yes

Alright @apmuthu , until the release of 2.5. Meanwhile how can I put validation on the reference field and change the datatype to accept varchar?
thanks

Re: Bank Account Payment Entry / gl/gl_bank.php?NewPayment=Yes

Use the memo / description field instead and make sure the initial prefix to the chq number like CHQ4564334 is used and then parse out all memo entries that start with CHQ and has the first word as say 10 characters long separated by a space of terminated therewith.

Re: Bank Account Payment Entry / gl/gl_bank.php?NewPayment=Yes

Hello @apmuthu hope this finds you well.

I have alter my payment table to add 2 extra column, check_no, source_ref to process my bank payments as all my bank payment require a checks, and cash payment voucher(source_ref). Now when I want to process a payment I get this error below:

What I understand is that the number of data being sent in the database does not correspond to the field required. Would you please tell me where I should add the 2 columns from UI?  I also wanted to put validation to check if a check no is already inserted to refuse duplicates.

Great Thanks!

array_combine(): Both parameters should have an equal number of elements in file: /var/www/html/fms/gl/includes/db/gl_db_banking.inc at line 394DATABASE ERROR : Cannot insert a source bank transaction
error code : 1064
error message : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'for computer services, 1, '2021-01-07', '-10500', '0', 'Cedrick ')' at line 2
sql that failed was : INSERT INTO bank_trans (type, trans_no, bank_act, ref,check_no, source_ref, trans_date, amount, person_type_id, person_id) VALUES (1, 130, '3', '001/2021', payment for computer services, 1, '2021-01-07', '-10500', '0', 'Cedrick ')

Re: Bank Account Payment Entry / gl/gl_bank.php?NewPayment=Yes

Every change in db schema will need to address all INSERT, DELETE and UPDATE MySQL statements thoughout the code.

Re: Bank Account Payment Entry / gl/gl_bank.php?NewPayment=Yes

okay, how do I do that?

Re: Bank Account Payment Entry / gl/gl_bank.php?NewPayment=Yes

cedricktshiyoyo wrote:

Hello @apmuthu hope this finds you well.

I have alter my payment table to add 2 extra column, check_no, source_ref to process my bank payments as all my bank payment require a checks, and cash payment voucher(source_ref). Now when I want to process a payment I get this error below:

What I understand is that the number of data being sent in the database does not correspond to the field required. Would you please tell me where I should add the 2 columns from UI?  I also wanted to put validation to check if a check no is already inserted to refuse duplicates.

Great Thanks!

array_combine(): Both parameters should have an equal number of elements in file: /var/www/html/fms/gl/includes/db/gl_db_banking.inc at line 394DATABASE ERROR : Cannot insert a source bank transaction
error code : 1064
error message : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'for computer services, 1, '2021-01-07', '-10500', '0', 'Cedrick ')' at line 2
sql that failed was : INSERT INTO bank_trans (type, trans_no, bank_act, ref,check_no, source_ref, trans_date, amount, person_type_id, person_id) VALUES (1, 130, '3', '001/2021', payment for computer services, 1, '2021-01-07', '-10500', '0', 'Cedrick ')

I will have a proper look at this but the first thing that stands out for me is the payment for computer services isn't quoted (should be 'payment for computer services') as I guess this is a string?

9 (edited by cedricktshiyoyo Yesterday 04:39:07 pm)

Re: Bank Account Payment Entry / gl/gl_bank.php?NewPayment=Yes

Yeah it is in a string format.
its should pick it right?

But this error tells that the number of data to be inserted is not equal with the value

array_combine(): Both parameters should have an equal number of element

Re: Bank Account Payment Entry / gl/gl_bank.php?NewPayment=Yes

It should quote it so your SQL should be:

INSERT INTO bank_trans (type, trans_no, bank_act, ref,check_no, source_ref, trans_date, amount, person_type_id, person_id) VALUES (1, 130, '3', '001/2021', 'payment for computer services', 1, '2021-01-07', '-10500', '0', 'Cedrick ')

I will fully check the SQL when I can.

Re: Bank Account Payment Entry / gl/gl_bank.php?NewPayment=Yes

Code under gl_banking.php

function create_cart($type, $trans_no)
{
    global $Refs;

    if (isset($_SESSION['pay_items']))
    {
        unset ($_SESSION['pay_items']);
    }

    $cart = new items_cart($type);
    $cart->order_id = $trans_no;

    if ($trans_no) {

        $bank_trans = db_fetch(get_bank_trans($type, $trans_no));
        $_POST['bank_account'] = $bank_trans["bank_act"];
        $_POST['PayType']      = $bank_trans["person_type_id"];
        $cart->reference       = $bank_trans["ref"];
        $_POST['check_no']     = $bank_trans["check_no"];

        if ($bank_trans["person_type_id"] == PT_CUSTOMER)
        {
            $trans = get_customer_trans($trans_no, $type);   
            $_POST['person_id'] = $trans["debtor_no"];
            $_POST['PersonDetailID'] = $trans["branch_code"];
        }
        elseif ($bank_trans["person_type_id"] == PT_SUPPLIER)
        {
            $trans = get_supp_trans($trans_no, $type);
            $_POST['person_id'] = $trans["supplier_id"];
        }
        elseif ($bank_trans["person_type_id"] == PT_MISC)
            $_POST['person_id'] = $bank_trans["person_id"];
        elseif ($bank_trans["person_type_id"] == PT_QUICKENTRY)
            $_POST['person_id'] = $bank_trans["person_id"];
        else
            $_POST['person_id'] = $bank_trans["person_id"];

        $cart->memo_     = get_comments_string($type, $trans_no);
        $cart->tran_date = sql2date($bank_trans['trans_date']);

        $cart->original_amount = $bank_trans['amount'];
        $result = get_gl_trans($type, $trans_no);
        if ($result) {
            while ($row = db_fetch($result)) {
                if (is_bank_account($row['account'])) {
                    // date exchange rate is currenly not stored in bank transaction,
                    // so we have to restore it from original gl amounts
                    $ex_rate = $bank_trans['amount']/$row['amount'];
                } else {
                    $cart->add_gl_item( $row['account'], $row['dimension_id'],
                        $row['dimension2_id'], $row['amount'], $row['memo_']);
                }
            }
        }

        // apply exchange rate
        foreach($cart->gl_items as $line_no => $line)
            $cart->gl_items[$line_no]->amount *= $ex_rate;

    } else {
        $cart->reference = $Refs->get_next($cart->trans_type, null, $cart->tran_date);
        $cart->tran_date = new_doc_date();
        if (!is_date_in_fiscalyear($cart->tran_date))
            $cart->tran_date = end_fiscalyear();
    }

    $_POST['memo_']      = $cart->memo_;
    $_POST['ref']        = $cart->reference;
    $_POST['check_no']   = $cart->check_no;
    $_POST['source_ref'] = $cart->source_ref;
    $_POST['date_']      = $cart->tran_date;

    $_SESSION['pay_items'] = &$cart;
}

Re: Bank Account Payment Entry / gl/gl_bank.php?NewPayment=Yes

// this is for     gl_bank_ui.inc

function display_bank_header(&$order)
{
    global $Ajax;
    $payment = $order->trans_type == ST_BANKPAYMENT;

    $customer_error = false;
    div_start('pmt_header');

    start_outer_table(TABLESTYLE2, "width='90%'"); // outer table

    table_section(1);
   
         date_row(_("Date:"), 'date_', '', true, 0, 0, 0, null, true);

    ref_row(_("Reference:"), 'ref', '', $order->reference, false, $order->trans_type, get_post('date_'));
   // text_row(_("check No:"), 'check_no', $_POST['check_no'], 16, 40);
    text_row(_("Check No:"),  'check_no', $_POST['check_no'], 16, 40); // Newly Added
   
    table_section(2, "33%");

    if (!isset($_POST['PayType']))
    {
        if (isset($_GET['PayType']))
            $_POST['PayType'] = $_GET['PayType'];
        else
            $_POST['PayType'] = "";
    }
    if (!isset($_POST['person_id']))
    {
        if (isset($_GET['PayPerson']))
            $_POST['person_id'] = $_GET['PayPerson'];
        else
            $_POST['person_id'] = "";
    }
    if (isset($_POST['_PayType_update'])) {
        $_POST['person_id'] = '';
        $Ajax->activate('pmt_header');
        $Ajax->activate('code_id');
        $Ajax->activate('pagehelp');
        $Ajax->activate('editors');
        $Ajax->activate('footer');
    }
    payment_person_types_list_row( $payment ? _("Pay To:"):_("From:"),
         'PayType', $_POST['PayType'], true);
    switch ($_POST['PayType'])
    {
        case PT_MISC :
            text_row_ex($payment ?_("To the Order of:"):_("Name:"),
                 'person_id', 20, 50);
           text_row(_("Voucher No:"), 'source_ref', null, 20, 50);    // Newly Added
            break;
        case PT_SUPPLIER :
            supplier_list_row(_("Supplier:"), 'person_id', null, false, true, false, true);
            text_row(_("Voucher No:"), 'source_ref', null, 20, 50);     // Newly added
            break;
        case PT_CUSTOMER :
            customer_list_row(_("Customer:"), 'person_id', null, false, true, false, true);
            text_row(_("Voucher No:"), 'source_ref', null, 20, 50);    //New Line

            if (db_customer_has_branches($_POST['person_id']))
            {
                customer_branches_list_row(_("Branch:"), $_POST['person_id'],
                    'PersonDetailID', null, false, true, true, true);
            }
            else
            {
                $_POST['PersonDetailID'] = ANY_NUMERIC;
                hidden('PersonDetailID');
            }
            $trans = get_customer_habit($_POST['person_id']); // take care of customers on hold
            if ($trans['dissallow_invoices'] != 0)
            {
                if ($payment)
                {
                    $customer_error = true;
                    display_error(_("This customer account is on hold."));
                }
                else
                    display_warning(_("This customer account is on hold."));
            }
            break;

        case PT_QUICKENTRY :
            quick_entries_list_row(_("Type").":", 'person_id', null, ($payment ? QE_PAYMENT : QE_DEPOSIT), true);
            $qid = get_quick_entry(get_post('person_id'));

            if (list_updated('person_id')) {
                unset($_POST['totamount']); // enable default
                $Ajax->activate('footer');
                $Ajax->activate('totamount');
            }
            amount_row($qid['base_desc'].":", 'totamount', price_format($qid['base_amount']),
                 null, "  ".submit('go', _("Go"), false, false, true));
            text_row(_("Voucher No:"), 'source_ref', null, 20, 50);    // New Line
            break;

    }

    table_section(3, "33%");

    if (!$order->order_id && !get_post('bank_account'))
    {
        if ($_POST['PayType'] == PT_CUSTOMER)
            $_POST['bank_account'] = get_default_customer_bank_account($_POST['person_id']);
        elseif ($_POST['PayType'] == PT_SUPPLIER)   
            $_POST['bank_account'] = get_default_supplier_bank_account($_POST['person_id']);
        else
            unset($_POST['bank_account']);
    }       
   
    bank_accounts_list_row( $payment ? _("From:") : _("Into:"), 'bank_account', null, true);
    if ($payment)
        bank_balance_row($_POST['bank_account']);

    $bank_currency = get_bank_account_currency($_POST['bank_account']);

    exchange_rate_display(get_company_currency(), $bank_currency, $_POST['date_']);

    end_outer_table(1); // outer table

    div_end();
    if ($customer_error)
    {
        end_form();
        end_page();
        exit;
    }
}

Re: Bank Account Payment Entry / gl/gl_bank.php?NewPayment=Yes

//----------------------------------------------------------------------------------
//    Add bank payment or deposit to database.
//
//    $from_account - bank account id
//  $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
//  $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, $check_no, $source_ref , $memo_ , $use_transaction=true, $settled_amount=null)
{
    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");

    $do_exchange_variance = false;
    $exchanged = false;
    if ($use_transaction)
        begin_transaction();

    $args = func_get_args(); if (count($args) < 13) $args[] = true;
    $args = (object)array_combine(array('trans_type', 'trans_no', 'from_account', 'items', 'date_',
        'person_type_id', 'person_id', 'person_detail_id', 'ref','check_no','source_ref', 'memo_', 'use_transaction', 'settled_amount'),
        $args);
    hook_db_prewrite($args, $trans_type);

    $aid = 0;
    if ($trans_no) {
        $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();

    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_);
        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,$check_no,$source_ref, $cust_amount);
        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_);
        else
            $supp_amount = $settled_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);
        if ($old_trans)
            move_trans_attachments($trans_type, $old_trans, $trans_no);
    }
    else
    {
           $trans_no = get_next_trans_no($trans_type);
        $do_exchange_variance = $SysPrefs->auto_currency_revaluation();
        if ($do_exchange_variance)
            $trans_no1 = get_next_trans_no(ST_JOURNAL);
    }
    if ($aid != 0)
    {
        $row = get_attachment($aid);
        update_attachment($aid, $row['type_no'], $trans_no, $row['description'],
            $row['filename'], $row['unique_name'], $row['filesize'], $row['filetype']);
    }
    // do the source account postings

    add_bank_trans($trans_type, $trans_no, $from_account, $ref, $check_no, $source_ref,
        $date_, -$total_amount,
        $person_type_id, $person_id,
        $currency,
        "Cannot insert a source bank transaction");
    $total = 0;
    foreach ($items->gl_items as $gl_item)
    {
        $is_bank_to = is_bank_account($gl_item->code_id);

        if ($trans_type == ST_BANKPAYMENT AND $is_bank_to)
        {
            // we don't allow payments to go to a bank account. use transfer for this !
            display_db_error("invalid payment entered. Cannot pay to another bank account", "");
        }

        // do the destination account postings
        $total += add_gl_trans($trans_type, $trans_no, $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, $check_no,$source_ref,
                $date_, $gl_item->amount,
                $person_type_id, $person_id, $currency,
                "Cannot insert a destination bank transaction");
            if ($do_exchange_variance)
            {
                add_exchange_variation($trans_no1, $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_);

        add_gl_tax_details($gl_item->code_id, $trans_type, $trans_no, -$amount,
            $ex_rate, $date_, $memo_);
    }

    // do the source account postings
    add_gl_trans($trans_type, $trans_no, $date_, $bank_gl_account, 0, 0, $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,
            $currency, $person_type_id, $person_id))
        {
               $ref1 = $Refs->get_next(ST_JOURNAL, null, $date_);
            $Refs->save(ST_JOURNAL, $trans_no1, $ref1);
            add_audit_trail(ST_JOURNAL, $trans_no1, $date_);
        }
    }

    add_comments($trans_type, $trans_no, $date_, $memo_);

    $Refs->save($trans_type, $trans_no, $ref, $check_no);
    add_audit_trail($trans_type, $trans_no, $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)
    {
        $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);
    if ($use_transaction)
        commit_transaction();

    return array($trans_type, $trans_no);
}

Re: Bank Account Payment Entry / gl/gl_bank.php?NewPayment=Yes

I have done a lot of looking through the code, and think this is the part that you should change in gl_db_bank_trans.inc:

function add_bank_trans($type, $trans_no, $bank_act, $ref, $check_no, $source_ref, $date_,
    $amount, $person_type_id, $person_id, $currency="", $err_msg="", $rate=0)
{
    $sqlDate = date2sql($date_);

    // convert $amount to the bank's currency
    if ($currency != "")
    {
        $bank_account_currency = get_bank_account_currency($bank_act);
        if ($rate == 0)
            $to_bank_currency = get_exchange_rate_from_to($currency, $bank_account_currency, $date_);
        else
            $to_bank_currency = 1 / $rate;
        $amount_bank = ($amount / $to_bank_currency);
    }
    else
        $amount_bank = $amount;
    $amount_bank = round2($amount_bank, user_price_dec());    

    $sql = "INSERT INTO ".TB_PREF."bank_trans (type, trans_no, bank_act, ref, check_no, source_ref,
        trans_date, amount, person_type_id, person_id) ";

    $sql .= "VALUES ($type, $trans_no, '$bank_act', ".db_escape($ref).", '$sqlDate', ".db_escape($check_no).", ".db_escape($source_ref).", ".db_escape($amount_bank).", ".db_escape($person_type_id).", ". db_escape($person_id).")";

    if ($err_msg == "")
        $err_msg = "The bank transaction could not be inserted";

    db_query($sql, $err_msg);
}

Was the function "write_bank_transaction" something you created? I couldn't find that one in my code?