26 Reply by tom.horton

(54 replies, posted in Banking and General Ledger)

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.

Go to forum: Banking and General Ledger

27 Reply by tom.horton

(54 replies, posted in Banking and General Ledger)

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).

Go to forum: Banking and General Ledger

28 Reply by tom.horton

(54 replies, posted in Banking and General Ledger)

cedricktshiyoyo wrote:

@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?

Go to forum: Banking and General Ledger

29 Reply by tom.horton

(6 replies, posted in Wish List)

Hi Joe

That would be great.

We've sort of done as you've said, so our "Bank" field is:

ABC Bank plc. - Sort Code 12-34-56

This works out well on the invoice, and displays on the system clearly.

Looking forward to v2.5!

Go to forum: Wish List

30 Reply by tom.horton

(6 replies, posted in Wish List)

I will follow the thread. Thank you

Go to forum: Wish List

31 Reply by tom.horton

(2 replies, posted in Installation)

I notice that there is an option to reset password which may be of use in the future if you want to use it. Go to the config.php file and find the line:

$allow_password_reset = false;

Change that to true and you will have the password reset option for you in future.

Go to forum: Installation

32 Topic by tom.horton

(6 replies, posted in Wish List)

It would be really useful (especially for us as a UK user) if there was more than just the one field for bank accounts for suppliers.

We use a sort code (also known as a routing number, or ABA) and account number to make domestic payments, but could also use the SWIFT/BIC and IBAN for international.

This format would also be useful for company bank accounts when displayed on invoices too.

Go to forum: Wish List

33 Reply by tom.horton

(7 replies, posted in Reporting)

To "spell out" the number entered, you could always use this PHP function:

$f = new NumberFormatter("en", NumberFormatter::SPELLOUT);
$f->setTextAttribute(NumberFormatter::DEFAULT_RULESET, "%spellout-numbering-verbose");
echo $f->format($total_amount);

You can see there is the option for the script to pickup a default language as well ("en", NumberFormatter::SPELLOUT) so if FA has the same "standard" letters set as PHP for language settings, it will write the wording in that language.

Hope this helps.

Go to forum: Reporting

34 Reply by tom.horton

(10 replies, posted in Report Bugs here)

Definitely worth logging to see if a better solution can be found.

Go to forum: Report Bugs here

35 Reply by tom.horton

(10 replies, posted in Report Bugs here)

I have managed to isolate the problem. It seems that FA is picking up that a session is still active, from the following code:

if (isset($_SESSION['timeout'])) {
      include($path_to_root . "/access/login.php");
      exit;
} else
      login_fail();
}

This is despite you being logged out.

The only way I managed to fix it myself was by adding another condition to the IF statement:

if (isset($_SESSION['timeout']) && ($_SESSION['timeout'] == '')) {
      include($path_to_root . "/access/login.php");
      exit;
} else 
     login_fail();
}

There may be a better solution, but this was the only way I could get the system to pick up an error and show the user.

Hope this helps.

Go to forum: Report Bugs here

36 Reply by tom.horton

(54 replies, posted in Banking and General Ledger)

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.

Go to forum: Banking and General Ledger

37 Reply by tom.horton

(54 replies, posted in Banking and General Ledger)

Ah OK. The t.tran_date as trans_date section of your SQL should be either:

t.tran_date as event_date

OR

t.tran_date as doc_date

Not sure which one you are using in your database for the correct date.

Go to forum: Banking and General Ledger

38 Reply by tom.horton

(54 replies, posted in Banking and General Ledger)

How does your journal table look in your database? I ran the SQL and seems to run OK for me

Go to forum: Banking and General Ledger

39 Reply by tom.horton

(54 replies, posted in Banking and General Ledger)

Good news!

Go to forum: Banking and General Ledger

40 Reply by tom.horton

(54 replies, posted in Banking and General Ledger)

I just noticed in your other post, you put your code for the above and notice a typo in the SQL:

$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).")";

You haven't fully quoted the $sqlDate field... so should look:

$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).")";

Go to forum: Banking and General Ledger

41 Reply by tom.horton

(54 replies, posted in Banking and General Ledger)

I just noticed that one of the fields in the SQL was out of order, which is close to where it was saying:

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 '', '-500', '0', 'Gregory')' at line 2

Can you update your code below and let me know if it works:

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);
}

If not, I will try something on my development system.

Go to forum: Banking and General Ledger

42 Reply by tom.horton

(54 replies, posted in Banking and General Ledger)

OK. Can you tell me the exact structure of your payment table your wanting the record to be added to?

Go to forum: Banking and General Ledger

43 Reply by tom.horton

(54 replies, posted in Banking and General Ledger)

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?

Go to forum: Banking and General Ledger

44 Reply by tom.horton

(54 replies, posted in Banking and General Ledger)

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.

Go to forum: Banking and General Ledger

45 Reply by tom.horton

(54 replies, posted in Banking and General Ledger)

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?

Go to forum: Banking and General Ledger

46 Reply by tom.horton

(2 replies, posted in Accounts Receivable)

I would run a GL Inquiry on the Debtors Control Account. If anything has been settled it should show up on there (I would normally see a "Sales Invoice" entry and a "Bank Deposit" entry).

Go to forum: Accounts Receivable

47 Reply by tom.horton

(19 replies, posted in Fixed Assets)

Excellent. Glad to help.

Best Regards



Tom

Go to forum: Fixed Assets

48 Reply by tom.horton

(19 replies, posted in Fixed Assets)

Yes I believe so. I tried purchasing another asset with a purchase date of today (10th January 2021), but a depreciation start date of 31st March 2020.

I managed to copy your error by trying to start the depreciation process from the purchase date, and using the current fiscal year.

I was able to stop the error by changing the fiscal year to the 2020 fiscal year.

Hope this helps.

Go to forum: Fixed Assets

49 Reply by tom.horton

(19 replies, posted in Fixed Assets)

OK. Have you processed the depreciation for every accounting period since? e.g. 2018, 2019 etc.

I found there was a problem if you missed a fiscal year, for example.

Go to forum: Fixed Assets

50 Reply by tom.horton

(19 replies, posted in Fixed Assets)

What date did you put for the asset purchase?

Go to forum: Fixed Assets