select
	recovery.name
  , recovery.installmentNo
  ,outstand.loan
  ,outstand.installmentNo
  ,disper.Loanee_count_Disbursement
 ,disper.Loan_Disbursement
 ,recovery.Repayment_Loanee_Count
 ,recovery.recovery
 ,outstand.Loanee_Count_Outstanding
 ,outstand.outstand
 ,overdue.overdue_count
 ,overdue.loan_overdue

from
(

	SELECT
            SUM(lad.val) AS outstand,
			COUNT(DISTINCT lao.client_id) AS Loanee_Count_Outstanding,
			lad.loan,
			ld.installmentNo

		FROM
			(
				SELECT
				t.loan_account_opening_id,
				SUM(dr - COALESCE(cr, 0)) AS val,
				t.loan
				FROM
				(
					SELECT
						SUM(loanAmount) dr,
						0 AS cr,
						ld.loan_account_opening_id,
						lp.name as loan
					FROM
					loan_disperse ld
					inner join loan_account_opening lao on ld.loan_account_opening_id=lao.id
					inner join loan_product lp on lp.id=lao.loan_product_id
					WHERE
					ld.disverse_on <=  ':todate'
					and (ld.organization =:orgname or :orgname=-1)
					 and (lp.id = :pro or :pro =-1)
					GROUP BY ld.loan_account_opening_id
					UNION
					SELECT
						0 AS dr,
						SUM(installment) AS cr,
						loan_account_opening_id,
						lp.name as loan
					FROM
					loan_recovery lr
					inner join loan_account_opening lao on lr.loan_account_opening_id=lao.id
					inner join loan_product lp on lp.id=lao.loan_product_id

					WHERE
					lr.value_date <=':todate'
			       and (lr.organization =:orgname or :orgname=-1)
			       and (lp.id = :pro or :pro =-1)
					GROUP BY loan_account_opening_id
				) AS t
				GROUP BY t.loan_account_opening_id
			) lad
		INNER JOIN loan_disperse ld ON ld.loan_account_opening_id = lad.loan_account_opening_id
		inner join loan_account_opening lao on lao.id=lad.loan_account_opening_id
		where val<>0
		GROUP BY lad.loan,ld.installmentNo
) outstand
left join
(
	select
		  COUNT(DISTINCT lao.client_id) AS overdue_count,
			sum(due.pridue) as loan_overdue
			,ld.installmentNo,
			due.name

	from
	(
				select
					receivable.loanid
					,receivable.name
					, sum(receivable.prireceivable - COALESCE(recovered.prirecovered, 0)) pridue
					, sum(receivable.intreceivable - COALESCE(recovered.intrecovered, 0)) intdue
				from
				(
					select sum(installment) prireceivable
							, sum(interest) intreceivable
							, max(value_date) as rec_date
							, lao.id as loanid
							, lp.name
						from loan_receivable_detail lrd
						inner join loan_account_opening lao ON lrd.loan_account_opening_id = lao.id
						inner join loan_product lp on lp.id=lao.loan_product_id
						where
						lrd.value_date <= ':todate'
						and (lrd.organization =:orgname or :orgname=-1)
				        and (lp.id = :pro or :pro =-1)
						group by lao.id
				) as receivable
				right join
				(
					select min(value_date) as lrddate
						, lrd.loan_account_opening_id as lao
					from loan_receivable_detail lrd
					left join loan_account_opening lao ON lrd.loan_account_opening_id = lao.id
					where lrd.value_date <= ':todate'
						 and (lrd.completed = 0 or lrd.completed_date > ':todate')
						 and (lrd.organization =:orgname or :orgname=-1)
					group by lrd.loan_account_opening_id
				) as receivable_date ON receivable.loanid = receivable_date.lao

				left join
				(
					select sum(installment) as prirecovered
						, sum(interest) as intrecovered
						, max(value_date) as lrdate
						, lao.id as loanid
					from loan_recovery lr
					inner join loan_account_opening lao ON lr.loan_account_opening_id = lao.id
					where lr.value_date <= ':todate'
						 and (lr.organization =:orgname or :orgname=-1)
						and (lp.id = :pro or :pro =-1)
					group by lao.id
				) as recovered on recovered.loanid=receivable.loanid
				group by receivable.loanid
				having pridue>0 or intdue>0
	) as due
	inner join loan_account_opening lao on lao.id=due.loanid
	inner join loan_disperse ld on ld.loan_account_opening_id=lao.id
	group by ld.installmentNo,	due.name
) as overdue  on overdue.name = outstand.loan and overdue.installmentNo = outstand.installmentNo
left join
(
	SELECT
			COUNT(DISTINCT lao.client_id) AS Loanee_count_Disbursement,
			SUM(ld.loanAmount) AS Loan_Disbursement,
			lp.name , ld.installmentNo
		FROM
			loan_disperse ld
			inner join loan_account_opening lao on ld.loan_account_opening_id=lao.id
			inner join loan_product lp on lp.id=lao.loan_product_id
		WHERE
			ld.disverse_on BETWEEN ':from' AND ':todate'
			  and (ld.organization =:orgname or :orgname=-1)
			  and (lp.id = :pro or :pro =-1)
		GROUP BY lp.name,ld.installmentNo
) as disper on disper.name = outstand.loan and disper.installmentNo=outstand.installmentNo
right join
(
	SELECT
			COUNT(DISTINCT lao.client_id) AS Repayment_Loanee_Count,
			SUM(installment) AS recovery,
			lp.name,
			ld.installmentNo
		FROM
			loan_recovery lr
			inner join loan_account_opening lao on lr.loan_account_opening_id=lao.id
			inner join loan_disperse ld on ld.loan_account_opening_id=lao.id
			inner join loan_product lp on lp.id=lao.loan_product_id
		WHERE
			lr.value_date BETWEEN ':from' AND ':todate'
			 and (lr.organization =:orgname or :orgname=-1)
			 and (lp.id = :pro or :pro =-1)
		GROUP BY lp.name ,ld.installmentNo
) as recovery on recovery.name = outstand.loan and recovery.installmentNo=outstand.installmentNo