Topic: REP709.php TAX REPORT BUG
I found a bug in rep709.php tax report. I got it fixed and would like to share. (see the ======> below in the sql query codes)
Problem:
When performing a Bank Payment and if the entries contain the same tax code which appear more than one time, and when the tax report rep709.php is run, it will generate multiple lines for the same tax codes and the number of times it appear will depend on the number of appearance of the same tax code.
For example, if my tax code is 8800 (tax name TX) and I have made 2 tax amounts of is say $7 and $14.
My bank payment entries would be:
- 8800 $7
- 8800 $14
In this example, 8800 appear twice. Now if I run this report, this tax will appear as follows (each entry appear twice):
Bank Payment BKPMT-00010 03-07-2020 -100.00 7.00 -7.00 TX
Bank Payment BKPMT-00010 03-07-2020 -100.00 7.00 -7.00 TX
Bank Payment BKPMT-00010 03-07-2020 -200.00 7.00 -14.00 TX
Bank Payment BKPMT-00010 03-07-2020 -200.00 7.00 -14.00 TX
If the same bank payment transaction has the 3rd tax entry say $21, total 3 rows now (each entry now appear 3 times):
Bank Payment BKPMT-00010 03-07-2020 -100.00 7.00 -7.00 TX
Bank Payment BKPMT-00010 03-07-2020 -100.00 7.00 -7.00 TX
Bank Payment BKPMT-00010 03-07-2020 -100.00 7.00 -7.00 TX
Bank Payment BKPMT-00010 03-07-2020 -200.00 7.00 -14.00 TX
Bank Payment BKPMT-00010 03-07-2020 -200.00 7.00 -14.00 TX
Bank Payment BKPMT-00010 03-07-2020 -200.00 7.00 -14.00 TX
Bank Payment BKPMT-00010 03-07-2020 -300.00 7.00 -21.00 TX
Bank Payment BKPMT-00010 03-07-2020 -300.00 7.00 -21.00 TX
Bank Payment BKPMT-00010 03-07-2020 -300.00 7.00 -21.00 TX
I looked into the sql query and I found a fix:
$sql = "SELECT tt.name as taxname, taxrec.*, taxrec.amount*ex_rate AS amount,
taxrec.net_amount*ex_rate AS net_amount,
IF(taxrec.trans_type=".ST_BANKPAYMENT." OR taxrec.trans_type=".ST_BANKDEPOSIT.",
IF(gl.person_type_id<>".PT_MISC.", gl.memo_, gl.person_id),
IF(ISNULL(supp.supp_name), debt.name, supp.supp_name)) as name,
branch.br_name
FROM ".TB_PREF."trans_tax_details taxrec
LEFT JOIN ".TB_PREF."tax_types tt
ON taxrec.tax_type_id=tt.id
LEFT JOIN ".TB_PREF."gl_trans gl
ON taxrec.trans_type=gl.type AND taxrec.trans_no=gl.type_no AND gl.amount<>0 AND
=======> gl.amount=taxrec.amount AND <====== Add this line to the query and it fix the problem.
(tt.purchasing_gl_code=gl.account OR tt.sales_gl_code=gl.account)
LEFT JOIN ".TB_PREF."supp_trans strans
ON taxrec.trans_no=strans.trans_no AND taxrec.trans_type=strans.type
LEFT JOIN ".TB_PREF."suppliers as supp ON strans.supplier_id=supp.supplier_id
LEFT JOIN ".TB_PREF."debtor_trans dtrans
ON taxrec.trans_no=dtrans.trans_no AND taxrec.trans_type=dtrans.type
LEFT JOIN ".TB_PREF."debtors_master as debt ON dtrans.debtor_no=debt.debtor_no
LEFT JOIN ".TB_PREF."cust_branch as branch ON dtrans.branch_code=branch.branch_code
WHERE (taxrec.amount <> 0 OR taxrec.net_amount <> 0)
AND !ISNULL(taxrec.reg_type)
AND taxrec.tran_date >= '$fromdate'
AND taxrec.tran_date <= '$todate'
ORDER BY taxrec.trans_type, taxrec.tran_date, taxrec.trans_no, taxrec.ex_rate";Any comments are welcome.
/Mark
