Topic: Bank Statement Report

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?

Re: Bank Statement Report

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?

Re: Bank Statement Report

Yes this is right.

/Joe

Re: Bank Statement Report

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

Re: Bank Statement Report

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

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

Re: Bank Statement Report

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

Re: Bank Statement Report

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

Re: Bank Statement Report

@joe: any headway?

10 (edited by Nibbik 07/01/2019 11:39:56 am)

Re: Bank Statement Report

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

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;

12 (edited by Nibbik 07/01/2019 04:36:05 pm)

Re: Bank Statement Report

@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

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.