TAAS

Solutions

1) GUI Changes
- Column heading Descrption to Account
-List Grouping -> Sale(Need Each Group Total), Refund(Need Each Group Total), Net Sale, Income(Need Each Group Total), Expense(Need Each Group Total), Net Profit
2) SQL
SELECT MAX(coa.vhr_code) AS vhr_code,
    MAX(coa.vhr_name) AS vhr_name,
    MAX(COALESCE(coa.vhr_display_name, coa.vhr_name)) AS vhr_display_name,
        MAX(tr.vhr_sys_module_name) AS vhr_sys_module_name,
        MAX(coa.sin_chart_of_ac_category) AS sin_chart_of_ac_category,
    SUM(tr.dbl_base_cur_debit) AS dbl_debit, -- Currency base field change
    SUM(dbl_base_cur_credit) AS dbl_credit -- Currency base field change
FROM transaction.tbl_transaction tr
    LEFT JOIN document.tbl_documents doc
        ON tr.fk_documents_id = doc.pk_documents_id
    LEFT JOIN accounts.tbl_chart_of_ac coa
        ON tr.fk_main_ledger_id = coa.pk_chart_of_ac_id
    LEFT JOIN accounts.tbl_account ac
        ON coa.pk_chart_of_ac_id = ac.fk_default_main_ledger_id
WHERE ac.sin_if_sys_account = 0
    AND tr.vhr_sys_module_name IN ('SALE', 'REFUND')
    AND tr.sin_posting_status = 1
    AND tr.sin_transaction_status = 1
    AND tr.sin_document_status = 1
    AND tr.fk_branch_id = 1
    AND tr.fk_department_id = 1
    -- tr.dat_transaction or doc.dat_document between
GROUP BY tr.vhr_sys_module_name, tr.fk_main_ledger_id
ORDER BY vhr_name

*Sale -> if vhr_sys_module_name == 'SALE' and sin_chart_of_ac_category IN (1,2); Credit=Paid; debit = Received; Net = Dr-Cr
*Refund -> if vhr_sys_module_name == 'REFUND' and sin_chart_of_ac_category IN (1,2); Credit=Paid; debit = Received; Net = Dr-Cr
*Net Sale -> Sale Total - REfund Total for each column
***Income and Expense ACCOUNT WISE TOTAL in .cs because Sale and Refund seprated
*Income -> if sin_chart_of_ac_category = 3; Credit=Received, Debit=Paid; Net = Cr-Dr
*Expense -> if sin_chart_of_ac_category = 4; Credit=Received, Debit=Paid; Net = Cr-Dr
*Net Profit -> Income Total + Expense Total for each column