1 Reply by zybersup

(20 replies, posted in Report Bugs here)

I found this bug 2 years ago but I cannot find the cause. Just today that I tried to fix it again and found that the SQL in report709.php seems to be unreasonable as the following.

    $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 
            (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 taxrec.trans_type <> ".ST_CUSTDELIVERY."
            AND taxrec.tran_date >= '$fromdate'
            AND taxrec.tran_date <= '$todate'
        ORDER BY taxrec.trans_type, taxrec.tran_date, taxrec.trans_no, taxrec.ex_rate";

When there are 2 records in tax_trans (The amount value in one record is not 0 and the other one is 0) which linked to the same record in debtor_trans, this SQL will generate 2 records after joining.

In tax_trans, the record of amount value 0 is filtered out by WHERE parameters, and only 1 tax_tran will be activated to join. But in joining gl_trans, the record of amount 0 is not filtered out so they all will be joined with this 1 tax_tran and get 2 transactions in the report.

Solution:
Just add " AND (gl.amount <> 0)" into the LEFT JOIN of gl_trans. This is what I have done so far. However, I do not really know the side effect as I have never thoroughly get into the codes of FrontAccounting.

Go to forum: Report Bugs here