FrontAccounting forum — Bank Statement Report

Re: Bank Statement Report

null@example.com (rafat) — Thu, 06 Feb 2020 15:50:21 +0000

Just downloaded 2.4.8 and I was expecting this issue will be resolved. but it seems not.
I cant understand why Braath Waate pull request is not incorporated in the core yet. It works nicely.
Is there a reason?

Re: Bank Statement Report

null@example.com (apmuthu) — Mon, 01 Jul 2019 17:11:08 +0000

The first two parts are only re-phrased.
Ordinarily there should be no duplicates.
Besides, the tables referred to in each select are different.

But... it is possible that more than one record across the selects may refer to the same transaction causing duplicates.

Re: Bank Statement Report

null@example.com (Nibbik) — Mon, 01 Jul 2019 16:31:18 +0000

@apmuthu
Thank you for your comment, but please help me: I don't understand fully.
How can a duplicate line exist, as the combination $trans_type and $trans_no together is a unique identifier for a transaction? Within one transaction, if a counterparty is either a debtor or a supplier, then person_type_id cannot be 0.

By the way: first two parts are original, I only added third part for the case where counterpart is not known in debtor/supplier-administrations but is only mentioned as "miscellaneous" - in this case the original routine would not show the counterpart at all.

Yours sincerely, etc. Nibbik

Re: Bank Statement Report

null@example.com (apmuthu) — Mon, 01 Jul 2019 16:02:54 +0000

There may be duplicate lines - maybe a DISTINCT is needed somewhere. Try to make the INNER JOIN using an ON statement rather than in WHERE like:

        case ST_BANKPAYMENT :
        case ST_BANKDEPOSIT :
            $sql = "SELECT trans.debtor_no as person_id, 
            CONVERT(debtor.name using utf8) as name
            FROM ".TB_PREF."debtor_trans trans INNER JOIN ".TB_PREF."debtors_master debtor USING (debtor_no)
            WHERE trans_no=".db_escape($trans_no)." AND type=".db_escape($trans_type)
            ."
            UNION
                SELECT trans.supplier_id as person_id, 
            CONVERT(supp.supp_name using utf8) as name
            FROM ".TB_PREF."supp_trans trans INNER JOIN ".TB_PREF."suppliers supp USING (supplier_id)
            WHERE trans_no=".db_escape($trans_no)." AND type=".db_escape($trans_type)
            ."
            UNION
                SELECT IF (person_type_id=0, 0 ,NULL) as person_id, 
            IF (person_type_id=0, CONVERT(person_id using utf8),NULL) as name 
            FROM ".TB_PREF."bank_trans
            WHERE trans_no=".db_escape($trans_no)." AND type=".db_escape($trans_type)
            ;
            break;

Re: Bank Statement Report

null@example.com (Nibbik) — Mon, 01 Jul 2019 11:38:58 +0000

I think I solved it with this solution
(file: frontaccounting/admin/db/transactions_db.inc, from line 115 onward):

        case ST_BANKPAYMENT :
        case ST_BANKDEPOSIT :
            $sql = "SELECT trans.debtor_no as person_id, CONVERT(debtor.name using utf8) as name
            FROM ".TB_PREF."debtor_trans trans, ".TB_PREF."debtors_master debtor
            WHERE trans_no=".db_escape($trans_no)." AND type=".db_escape($trans_type)
            ." AND trans.debtor_no=debtor.debtor_no
            UNION
                SELECT trans.supplier_id as person_id, CONVERT(supp.supp_name using utf8) as name
            FROM ".TB_PREF."supp_trans trans, ".TB_PREF."suppliers supp
            WHERE trans_no=".db_escape($trans_no)." AND type=".db_escape($trans_type)
            ." AND trans.supplier_id=supp.supplier_id
            UNION
                SELECT IF (person_type_id=0, 0 ,NULL) as person_id, 
            IF (person_type_id=0, CONVERT(person_id using utf8),NULL) as name 
            FROM ".TB_PREF."bank_trans WHERE type=".db_escape($trans_type)." 
            AND trans_no=".db_escape($trans_no);
            break;

/*            $sql = "SELECT trans.debtor_no as person_id, debtor.name as name
            FROM ".TB_PREF."debtor_trans trans, ".TB_PREF."debtors_master debtor
            WHERE trans_no=".db_escape($trans_no)." AND type=".db_escape($trans_type)
            ." AND trans.debtor_no=debtor.debtor_no
            UNION
                SELECT trans.supplier_id as person_id, supp.supp_name as name
            FROM ".TB_PREF."supp_trans trans, ".TB_PREF."suppliers supp
            WHERE trans_no=".db_escape($trans_no)." AND type=".db_escape($trans_type)
            ." AND trans.supplier_id=supp.supplier_id";
            break;
*/

Re: Bank Statement Report

null@example.com (apmuthu) — Sat, 30 Jun 2018 18:21:02 +0000

@joe: any headway?

Re: Bank Statement Report

null@example.com (apmuthu) — Sat, 17 Mar 2018 14:38:59 +0000

@joe: What are the consequences of this pull request. @rafat seems satisfied.

Re: Bank Statement Report

null@example.com (rafat) — Sat, 17 Mar 2018 10:07:09 +0000

Thanks @BraathWaate your pull request works great. Beautiful..Thanks guys.

Re: Bank Statement Report

null@example.com (rafat) — Sat, 17 Mar 2018 09:28:07 +0000

Thanks Guys.. I will test and revert back to you.

Re: Bank Statement Report

null@example.com (apmuthu) — Fri, 16 Mar 2018 15:52:57 +0000

The function get_counterparty_name() defined in admin/db/transactions_db.inc is a quite extensive one whereas the alternative used in @BraathWaate's pull request, function payment_person_name() defined in includes/types.inc may not cover all types of transactions.

@joe: will this pull request make for covering all types of transactions required here?
@rafat: can you extensively test @BraathWaate's pull request with your data?

Re: Bank Statement Report

null@example.com (Braath Waate) — Fri, 16 Mar 2018 13:17:11 +0000

Or you could modify your FA installation with this pull request:
https://github.com/FrontAccountingERP/FA/pull/26

Re: Bank Statement Report

null@example.com (joe) — Fri, 16 Mar 2018 12:31:10 +0000

Yes this is right.

/Joe

Re: Bank Statement Report

null@example.com (apmuthu) — Fri, 16 Mar 2018 12:09:28 +0000

The Bank Statement only knows Customer and Suppliers as that is where the AR/AP allocations take place. Everything else is a mere Journal kind of Transaction without tracking allocations that can be seen in the GL Account Transactions.

@joe: is this right?

Bank Statement Report

null@example.com (rafat) — Fri, 16 Mar 2018 09:58:07 +0000

When asking for a Bank Statement report from Banking reports the final report does not show the person/item unless the field is either a customer or a supplier. While if asking for a GL Account Transaction Report and choosing the Bank Accounts.. the Person/Item field is shown correctly to all Person/item types. Is this how it should be?