It's much more fun, when you can discuss your problems with others...
You are not logged in. Please login or register.
FrontAccounting forum → Banking and General Ledger → Bank Account Payment Entry / gl/gl_bank.php?NewPayment=Yes
Ah OK. The t.tran_date as trans_date section of your SQL should be either:
t.tran_date as event_dateOR
t.tran_date as doc_dateNot sure which one you are using in your database for the correct date.
Yeah I messed somewhere, I wanted the source ref on journal entry to be unique, to throw error to the user that the source ref is either empty or already used.
something like this on journal entry
if (strlen($_POST['source_ref']) == 0 || $_POST['source_ref'] == "") // new line validation added on sunday november 21 2020
{
display_error(_("The source reference field must be entered.")); // new line validation
if (get_post('currency') != get_company_pref('curr_default'))
set_focus('source_ref'); 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;
}
You could either rename one of those fields or find the code where that field name is and change it, but I think the rest of it is OK.
Will do that,
Also you can update your view to display check and source reference on <b>gl_payment_view.php</b>
start_row();
label_cells(_("From Bank Account"), $from_trans['bank_account_name'], "class='tableheader2'");
if ($show_currencies)
label_cells(_("Currency"), $from_trans['bank_curr_code'], "class='tableheader2'");
label_cells(_("Amount"), number_format2(-$from_trans['amount'], user_price_dec()), "class='tableheader2'", "align=right");
label_cells(_("Date"), sql2date($from_trans['trans_date']), "class='tableheader2'");
end_row();
start_row();
label_cells(_("Pay To"), get_counterparty_name(ST_BANKPAYMENT, $from_trans['trans_no']), "class='tableheader2'", "colspan=$colspan1");
if ($show_currencies)
{
label_cells(_("Settle currency"), $from_trans['settle_curr'], "class='tableheader2'");
label_cells(_("Settled amount"), number_format2($from_trans['settled_amount'], user_price_dec()), "class='tableheader2'");
}
label_cells(_("Payment Type"), $bank_transfer_types[$from_trans['account_type']], "class='tableheader2'");
end_row();
start_row();
label_cells(_("Reference"), $from_trans['ref'], "class='tableheader2'");
label_cells(_("voucher No"), $from_trans['source_ref'], "class='tableheader2'", "align=right");
label_cells(_("Check No"), $from_trans['check_no'], "class='tableheader2'");
end_row();
@tom.horton
I have not succeeded in preventing same source_ref data to be duplicated. When I use the sql UNIQUE constraint, even NULL values will not be accepted. Do you have a better idea to prevent duplication of source_ref values?
Thanks
@tom.horton
I have not succeeded in preventing same source_ref data to be duplicated. When I use the sql UNIQUE constraint, even NULL values will not be accepted. Do you have a better idea to prevent duplication of source_ref values?
Thanks
What SQL and PHP code do you have already?
I have only managed with php to prevent empty field for source_ref
if (strlen($_POST['source_ref']) == 0 || $_POST['source_ref'] == "") // new line validation added on sunday november 21 2020
{
display_error(_("The source reference field must be entered.")); // new line validation
if (get_post('currency') != get_company_pref('curr_default'))
set_focus('source_ref'); 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;
}
I am also getting another error when I deal with journal entry, since we changed the fields for banks_trans, now journal entry gives me this error
Undefined offset: 1 in file: /var/www/html/ced/includes/date_functions.inc at line 386Undefined offset: 2 in file: /var/www/html/ced/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, 1348, '5', '080/2021', '01/18/2021','-400','0000-00-00', '0', 'ETB', 'Cannot insert a destination bank transaction')
OK. I will have a look at the code for you when I can.
In terms of your SQL it looks like it is not in line, and the date format is incorrect (should be YYYY-MM-DD but you have it as MM/DD/YYYY).
For your SQL error you are missing a number of fields. These are as follows:
INSERT INTO bank_trans
(type, trans_no, bank_act, ref, check_no, source_ref, trans_date, amount, person_type_id, person_id)
VALUES
(0, 1348, '5', '080/2021', '<check_no>', '<source_ref>', '2021-01-18','-400','<person_type_id>', '<person_id>')I have marked them with "<>" either side. I have also corrected fields that were showing the wrong format etc.
As for checking for duplicated source_ref data, that could possibly be done by Ajax. It would require a JS file to execute a PHP file to run a MySQL query when you update a text field.
I think I have this right now...
Firstly, make sure the Ajax libraries are included in your file.
For your main PHP file, you need to have the following javascript/Ajax included:
<script>
function add_source_reference()
{
$.ajax({
type: "POST",
data: {
source_ref: $('#source_ref').val(),
},
url: "gl_validation_checks.php",
success: function(data)
{
if(data === 'SOURCEREF_EXISTS')
{
$('#sourceref_res')
.css('color', 'red')
.html("This reference already exists");
}
}
})
}
</script>You need to add an OnExit command to a text field (the "source_ref" field), where this equals "add_source_reference()"
Create a new PHP file called gl_validation_checks.php for example:
<?php
$source_ref = mysql_real_escape_string($_POST['source_ref']);
$sql = "SELECT source_ref FROM bank_trans WHERE source_ref='".$source_ref."'";
$query = mysql_query($sql);
if(mysql_num_rows($query) == 0)
{
echo('NO_SOURCEREF');
}
else
{
echo('SOURCEREF_EXISTS');
}
?>Make sure you include:
<span id="sourceref_res"></span>Including this will indicate where the message will show if there is a duplicate.
I hope this all makes sense.
For your SQL error you are missing a number of fields. These are as follows:
INSERT INTO bank_trans (type, trans_no, bank_act, ref, check_no, source_ref, trans_date, amount, person_type_id, person_id) VALUES (0, 1348, '5', '080/2021', '<check_no>', '<source_ref>', '2021-01-18','-400','<person_type_id>', '<person_id>')I have marked them with "<>" either side. I have also corrected fields that were showing the wrong format etc.
As for checking for duplicated source_ref data, that could possibly be done by Ajax. It would require a JS file to execute a PHP file to run a MySQL query when you update a text field.
Now, that's the thing, journal entry is used not only for bank transactions, but also for other says, I am getting money from CASH ON HAND To Fuel Expense, without necessarily getting money from the bank, which means there is no check involved. This is where i am complicating my life now lol... The Check no field in this case will be empty, which will throw that database error.
I think I have this right now...
Firstly, make sure the Ajax libraries are included in your file.
For your main PHP file, you need to have the following javascript/Ajax included:
<script> function add_source_reference() { $.ajax({ type: "POST", data: { source_ref: $('#source_ref').val(), }, url: "gl_validation_checks.php", success: function(data) { if(data === 'SOURCEREF_EXISTS') { $('#sourceref_res') .css('color', 'red') .html("This reference already exists"); } } }) } </script>You need to add an OnExit command to a text field (the "source_ref" field), where this equals "add_source_reference()"
Create a new PHP file called gl_validation_checks.php for example:
<?php $source_ref = mysql_real_escape_string($_POST['source_ref']); $sql = "SELECT source_ref FROM bank_trans WHERE source_ref='".$source_ref."'"; $query = mysql_query($sql); if(mysql_num_rows($query) == 0) { echo('NO_SOURCEREF'); } else { echo('SOURCEREF_EXISTS'); } ?>
I believe there is already a file for validation check /includes/data_checks.inc , isn't it possible to just include the script in that file?
I believe there is already a file for validation check /includes/data_checks.inc , isn't it possible to just include the script in that file?
Yes that would be better. I did try looking for the best file lol
@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
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
@cedricktshiyoyo Sorry for the delay. Glad you managed to get it sorted.
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')
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.
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', <check_no>, <source_ref>, '01/28/2021','-45', '<person_type_id>', '<person_id>')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".
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
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', <check_no>, <source_ref>, '01/28/2021','-45', '<person_type_id>', '<person_id>')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?
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.
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);
}
FrontAccounting forum → Banking and General Ledger → Bank Account Payment Entry / gl/gl_bank.php?NewPayment=Yes
Powered by PunBB, supported by Informer Technologies, Inc.
Currently installed 4 official extensions. Copyright © 2003–2009 PunBB.