FrontAccounting forum — Bank Account Payment Entry / gl/gl_bank.php?NewPayment=Yes

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

2021-01-28T21:37:59Z

Yeah, i am getting this error from journal entry

finance/gl/gl_journal.php?NewJournal=Yes

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

2021-01-28T21:18:40Z

cedricktshiyoyo wrote:

yeah it may be confusing for sure.
I think you know what I was trying to do recently with bank payments.
1. I have altered bank_trans , added 2 extra fields, check_no & source_ref
2. I have made little changes in gl/gl_bank.php
3. gl/includes/db/gl_db_bank_trans.inc // New code
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).", ".db_escape($check_no).",".db_escape($source_ref).",'$sqlDate',
".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);
}

I don't think there is anything wrong with the above from what I can see. Do you know where this is being called from in this instance where you are getting the error message?

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

2021-01-28T21:14:14Z

And of course the validation to not accept empty field in cheque no... that's all I did

if (strlen($_POST['check_no']) == 0 || $_POST['check_no'] == "") // new line validation added on sunday november 21 2020
{
display_error(_("The cheque no reference field must be entered.")); // new line validation
if (get_post('currency') != get_company_pref('curr_default'))
set_focus('check_no'); if (isset($_POST['_ex_rate']) && !check_num('_ex_rate', 0.000001))
return false; {
} display_error(_("The exchange rate must be numeric and greater than zero."));
set_focus('_ex_rate');
$input_error = 1;
}

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

2021-01-28T21:10:00Z

5. in gl/includes/ui/gl_bank_ui.inc

text_row(_("Cheque No:"), 'check_no', $_POST['check_no'], 16, 40);
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;

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

2021-01-28T21:07:01Z

4. gl/includes/db/gl_db_banking.inc

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) < 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','check_no','source_ref', 'memo_', 'use_transaction', 'settled_amount'),
$args);

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

2021-01-28T21:05:05Z

yeah it may be confusing for sure.

I think you know what I was trying to do recently with bank payments.

1. I have altered bank_trans , added 2 extra fields, check_no & source_ref
2. I have made little changes in gl/gl_bank.php
3. gl/includes/db/gl_db_bank_trans.inc // New code

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).", ".db_escape($check_no).",".db_escape($source_ref).",'$sqlDate',
".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);
}

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

2021-01-28T20:56:52Z

I think it is getting confused somewhere. I would need to see the full code (the whole PHP file) in order to try and find an answer for you I think.

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

2021-01-28T20:52:16Z

INSERT INTO bank_trans (type, trans_no, bank_act, ref, check_no, source_ref, trans_date, amount, person_type_id, person_id) VALUES (0, 1406, '5', '126/2021', , , '01/28/2021','-45', '', '')

Here i am dealing with journal entry, not bank payment. The code above is correct when i process bank payment. but only when happens when i SWITCH TO journal entry.

Are you able to perfom journal entry which involves bank accounts?

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

2021-01-28T20:39:01Z

tom.horton wrote:

It looks like your SQL is trying to insert "ETB" (string) into the "person_type_id" field (an integer). I think it just needs re-aligning again.

yeah, but i have not made changes for gl_journal.php
I think the code is supposed to ignore person_id when the entry type is ST_JOURNAL

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

2021-01-28T20:35:23Z

This is how it should look:

INSERT INTO bank_trans (type, trans_no, bank_act, ref, check_no, source_ref, trans_date, amount, person_type_id, person_id) VALUES (0, 1406, '5', '126/2021', , , '01/28/2021','-45', '', '')

It doesn't look like you have a field for "ETB" to go into?

Also, your date format is wrong. It should be "2021-01-28".

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

2021-01-28T20:26:19Z

It looks like your SQL is trying to insert "ETB" (string) into the "person_type_id" field (an integer). I think it just needs re-aligning again.

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

2021-01-28T20:13:54Z

I was able to solve one of the errors, now on journal entry, I am getting this error. when there is a bank account involved in the process.
Tell me are you able to process journal entries after ALTERING bank_trans table with CHEQUE_NO AND SOURCE_REF?

Undefined offset: 1 in file: /var/www/html/finance/includes/date_functions.inc at line 386Undefined offset: 2 in file: /var/www/html/finance/includes/date_functions.inc at line 386DATABASE ERROR : The bank transaction could not be inserted
error code : 1366
error message : Incorrect integer value: 'ETB' for column 'person_type_id' at row 1
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 (0, 1406, '5', '126/2021', '01/28/2021','-45','0000-00-00', '0', 'ETB', 'Cannot insert a destination bank transaction')

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

2021-01-28T19:01:49Z

@cedricktshiyoyo Sorry for the delay. Glad you managed to get it sorted.

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

2021-01-28T12:16:26Z

Sorry for this error i found the problem, I add forgotten to check_no inside quotes like this

 text_row(_("Cheque no:"), 'check_no', null, 16,30);

Journal Entry
Missing argument 5 for text_row(), called in /var/www/html/finance/gl/includes/ui/gl_journal_ui.inc on line 46 and defined in file: /var/www/html/finance/includes/ui/ui_input.inc at line 631Undefined variable: max in file: /var/www/html/finance/includes/ui/ui_input.inc at line 634

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

2021-01-28T11:59:14Z

@tom.horton hope you are fine.

I was not able to process journal entries or Bank Transfer after modifying the code.
Are you able to process journal entries without any errors?

Journal Entry
Missing argument 5 for text_row(), called in /var/www/html/finance/gl/includes/ui/gl_journal_ui.inc on line 46 and defined in file: /var/www/html/finance/includes/ui/ui_input.inc at line 631Undefined variable: max in file: /var/www/html/finance/includes/ui/ui_input.inc at line 634

You must enter at least one journal line.Undefined index: check_no in file: /var/www/html/finance/gl/gl_journal.php at line 243The source reference field must be entered.The exchange rate must be numeric and greater than zero.Missing argument 5 for text_row(), called in /var/www/html/finance/gl/includes/ui/gl_journal_ui.inc on line 46 and defined in file: /var/www/html/finance/includes/ui/ui_input.inc at line 631Undefined variable: max in file: /var/www/html/finance/includes/ui/ui_input.inc at line 634