select concat(repeat("..",
   ((LENGTH(ounder.`hierarchy`) - LENGTH(REPLACE(ounder.`hierarchy`, '.', '')) - 1))), ounder.`name`) as "Office/Branch",
lo.display_name as "Loan Officer",
c.display_name as "Client", l.account_no as "Loan Account", c.mobile_no AS "MobileNo", pl.`name` as "Product", 
l.principal_amount as "Principal Amount",
(IFNULL(l.principal_outstanding_derived, 0) + IFNULL(l.interest_outstanding_derived, 0) + IFNULL(l.fee_charges_outstanding_derived, 0) + IFNULL(l.penalty_charges_outstanding_derived, 0)) AS "Outstanding",
l.approvedon_date  as  "Disbursed On",
l.expected_maturedon_date as "Maturity Date",
IFNULL(laa.total_overdue_derived, 0) AS "Arrears",
COALESCE(sum(xx.amount),0) as "Total Paid"
from m_office o
join m_office ounder on ounder.hierarchy like concat(o.hierarchy, '%')
and ounder.hierarchy like concat('${currentUserHierarchy}', '%')
join m_client c on c.office_id = ounder.id
join m_loan l on l.client_id = c.id
join m_product_loan pl on pl.id = l.product_id
left join m_staff lo on lo.id = l.loan_officer_id
left join m_currency cur on cur.code = l.currency_code
left join m_fund f on f.id = l.fund_id
left join m_loan_arrears_aging laa on laa.loan_id = l.id
left join m_loan_transaction xx on xx.loan_id=l.id
where o.id = ${officeId}
and (l.currency_code = "${currencyId}" or "-1" = "${currencyId}")
and (l.product_id = "${loanProductId}" or "-1" = "${loanProductId}")
and (ifnull(l.loan_officer_id, -10) = "${loanOfficerId}" or "-1" = "${loanOfficerId}")
and (ifnull(l.fund_id, -10) = ${fundId} or -1 = ${fundId})
and (ifnull(l.loanpurpose_cv_id, -10) = ${loanPurposeId} or -1 = ${loanPurposeId})
and xx.transaction_date  between  '${startDate}' and '${endDate}'
and xx.transaction_type_enum<>'1'
and xx.payment_detail_id is not null
and xx.is_reversed=0
group by l.id
order by ounder.hierarchy, l.currency_code, c.account_no, l.account_no