It's much more fun, when you can discuss your problems with others...
You are not logged in. Please login or register.
FrontAccounting forum → Reporting → 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?
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?
Yes this is right.
/Joe
Or you could modify your FA installation with this pull request:
https://github.com/FrontAccountingERP/FA/pull/26
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?
Thanks Guys.. I will test and revert back to you.
Thanks @BraathWaate your pull request works great. Beautiful..Thanks guys.
@joe: What are the consequences of this pull request. @rafat seems satisfied.
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;
*/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;@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
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.
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?
FrontAccounting forum → Reporting → Bank Statement Report
Powered by PunBB, supported by Informer Technologies, Inc.
Currently installed 4 official extensions. Copyright © 2003–2009 PunBB.