bin.sql

-- D051-Cash Sheet Statement Loan
select *
from
(
	select member_code
		, member_name
		, concat(loan_product, ' - ','Principal') as loan_product
		, subOrder
		, amount
	from
	(
		select c.code as member_code
			, c.name as member_name
			, lao.loan_product_id
			, lp.name as loan_product
			, concat("a",lp.orderNo) as subOrder
			, sum(installment) as amount
		from loan_recovery lr
		inner join loan_account_opening lao on lr.loan_account_opening_id=lao.id
		inner join loan_product lp on lao.loan_product_id=lp.id
		inner join client c on lao.client_id=c.id
		where lr.organization=4
			and (lr.collection_sheet_id is null)
			and (lr.value_date between '2015-01-01' and '2015-01-02')
		 	and (lr.created_by=-1 or -1=-1)
		group by member_code, lao.loan_product_id
		having amount>0
	) as t1
	UNION ALL
	select member_code
		, member_name
		, concat(loan_product, ' - ','Interest') as loan_product
		, subOrder
		, amount
	from
	(
		select c.code as member_code
			, c.name as member_name
			, lao.loan_product_id
			, lp.name as loan_product
			, concat("b",lp.orderNo) as subOrder
			, sum(interest) as amount
		from loan_recovery lr
		inner join loan_account_opening lao on lr.loan_account_opening_id=lao.id
		inner join loan_product lp on lao.loan_product_id=lp.id
		inner join client c on lao.client_id=c.id
		where lr.organization=4
			and (lr.collection_sheet_id is null)
			and (lr.value_date between '2015-01-01' and '2015-01-02')
		 	and (lr.created_by=-1 or -1=-1)
		group by member_code, lao.loan_product_id
		having amount>0
	) as t2
	UNION ALL
	select member_code
		, member_name
		, concat(loan_product, ' - ','Penalty') as loan_product
		, subOrder
		, amount
	from
	(
		select c.code as member_code
			, c.name as member_name
			, lao.loan_product_id
			, lp.name as loan_product
			, concat("c",lp.orderNo) as subOrder
			, sum(penalty) as amount
		from loan_recovery lr
		inner join loan_account_opening lao on lr.loan_account_opening_id=lao.id
		inner join loan_product lp on lao.loan_product_id=lp.id
		inner join client c on lao.client_id=c.id
		where lr.organization=4
			and (lr.collection_sheet_id is null)
			and (lr.value_date between '2015-01-01' and '2015-01-02')
		 	and (lr.created_by=-1 or -1=-1)
		group by member_code, lao.loan_product_id
		having amount>0
	) as t3
) as master
order by subOrder, member_code