SHARE
TWEET

Untitled

Manish_chand Feb 28th, 2020 87 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select
  2.     recovery.name
  3.   , recovery.installmentNo
  4.   ,outstand.loan
  5.   ,outstand.installmentNo
  6.   ,disper.Loanee_count_Disbursement
  7.  ,disper.Loan_Disbursement
  8.  ,recovery.Repayment_Loanee_Count
  9.  ,recovery.recovery
  10.  ,outstand.Loanee_Count_Outstanding
  11.  ,outstand.outstand
  12.  ,overdue.overdue_count
  13.  ,overdue.loan_overdue
  14.  
  15. from
  16. (
  17.  
  18.     SELECT
  19.             SUM(lad.val) AS outstand,
  20.             COUNT(DISTINCT lao.client_id) AS Loanee_Count_Outstanding,
  21.             lad.loan,
  22.             ld.installmentNo
  23.            
  24.         FROM
  25.             (
  26.                 SELECT
  27.                 t.loan_account_opening_id,
  28.                 SUM(dr - COALESCE(cr, 0)) AS val,
  29.                 t.loan
  30.                 FROM
  31.                 (
  32.                     SELECT
  33.                         SUM(loanAmount) dr,
  34.                         0 AS cr,
  35.                         ld.loan_account_opening_id,
  36.                         lp.name as loan
  37.                     FROM
  38.                     loan_disperse ld
  39.                     inner join loan_account_opening lao on ld.loan_account_opening_id=lao.id
  40.                     inner join loan_product lp on lp.id=lao.loan_product_id
  41.                     WHERE
  42.                     ld.disverse_on <=  ':todate'
  43.                     and (ld.organization =:orgname or :orgname=-1)
  44.                      and (lp.id = :pro or :pro =-1)
  45.                     GROUP BY ld.loan_account_opening_id
  46.                     UNION
  47.                     SELECT
  48.                         0 AS dr,
  49.                         SUM(installment) AS cr,
  50.                         loan_account_opening_id,
  51.                         lp.name as loan
  52.                     FROM
  53.                     loan_recovery lr
  54.                     inner join loan_account_opening lao on lr.loan_account_opening_id=lao.id
  55.                     inner join loan_product lp on lp.id=lao.loan_product_id
  56.                    
  57.                     WHERE
  58.                     lr.value_date <=':todate'
  59.                    and (lr.organization =:orgname or :orgname=-1)
  60.                    and (lp.id = :pro or :pro =-1)
  61.                     GROUP BY loan_account_opening_id
  62.                 ) AS t
  63.                 GROUP BY t.loan_account_opening_id
  64.             ) lad  
  65.         INNER JOIN loan_disperse ld ON ld.loan_account_opening_id = lad.loan_account_opening_id
  66.         inner join loan_account_opening lao on lao.id=lad.loan_account_opening_id
  67.         where val<>0
  68.         GROUP BY lad.loan,ld.installmentNo
  69. ) outstand
  70. left join
  71. (
  72.     select
  73.           COUNT(DISTINCT lao.client_id) AS overdue_count,
  74.             sum(due.pridue) as loan_overdue
  75.             ,ld.installmentNo, 
  76.             due.name
  77.            
  78.     from
  79.     (
  80.                 select
  81.                     receivable.loanid
  82.                     ,receivable.name
  83.                     , sum(receivable.prireceivable - COALESCE(recovered.prirecovered, 0)) pridue
  84.                     , sum(receivable.intreceivable - COALESCE(recovered.intrecovered, 0)) intdue
  85.                 from
  86.                 (
  87.                     select sum(installment) prireceivable
  88.                             , sum(interest) intreceivable
  89.                             , max(value_date) as rec_date
  90.                             , lao.id as loanid
  91.                             , lp.name
  92.                         from loan_receivable_detail lrd
  93.                         inner join loan_account_opening lao ON lrd.loan_account_opening_id = lao.id
  94.                         inner join loan_product lp on lp.id=lao.loan_product_id
  95.                         where
  96.                         lrd.value_date <= ':todate'
  97.                         and (lrd.organization =:orgname or :orgname=-1)
  98.                         and (lp.id = :pro or :pro =-1)
  99.                         group by lao.id
  100.                 ) as receivable
  101.                 right join
  102.                 (
  103.                     select min(value_date) as lrddate
  104.                         , lrd.loan_account_opening_id as lao
  105.                     from loan_receivable_detail lrd
  106.                     left join loan_account_opening lao ON lrd.loan_account_opening_id = lao.id
  107.                     where lrd.value_date <= ':todate'
  108.                          and (lrd.completed = 0 or lrd.completed_date > ':todate')
  109.                          and (lrd.organization =:orgname or :orgname=-1)
  110.                     group by lrd.loan_account_opening_id
  111.                 ) as receivable_date ON receivable.loanid = receivable_date.lao
  112.  
  113.                 left join
  114.                 (
  115.                     select sum(installment) as prirecovered
  116.                         , sum(interest) as intrecovered
  117.                         , max(value_date) as lrdate
  118.                         , lao.id as loanid
  119.                     from loan_recovery lr
  120.                     inner join loan_account_opening lao ON lr.loan_account_opening_id = lao.id
  121.                     where lr.value_date <= ':todate'
  122.                          and (lr.organization =:orgname or :orgname=-1)
  123.                         and (lp.id = :pro or :pro =-1)
  124.                     group by lao.id
  125.                 ) as recovered on recovered.loanid=receivable.loanid
  126.                 group by receivable.loanid
  127.                 having pridue>0 or intdue>0
  128.     ) as due
  129.     inner join loan_account_opening lao on lao.id=due.loanid
  130.     inner join loan_disperse ld on ld.loan_account_opening_id=lao.id
  131.     group by ld.installmentNo,  due.name       
  132. ) as overdue  on overdue.name = outstand.loan and overdue.installmentNo = outstand.installmentNo
  133. left join
  134. (
  135.     SELECT
  136.             COUNT(DISTINCT lao.client_id) AS Loanee_count_Disbursement,
  137.             SUM(ld.loanAmount) AS Loan_Disbursement,
  138.             lp.name , ld.installmentNo
  139.         FROM
  140.             loan_disperse ld
  141.             inner join loan_account_opening lao on ld.loan_account_opening_id=lao.id
  142.             inner join loan_product lp on lp.id=lao.loan_product_id
  143.         WHERE
  144.             ld.disverse_on BETWEEN ':from' AND ':todate'
  145.               and (ld.organization =:orgname or :orgname=-1)
  146.               and (lp.id = :pro or :pro =-1)
  147.         GROUP BY lp.name,ld.installmentNo
  148. ) as disper on disper.name = outstand.loan and disper.installmentNo=outstand.installmentNo
  149. right join
  150. (
  151.     SELECT
  152.             COUNT(DISTINCT lao.client_id) AS Repayment_Loanee_Count,
  153.             SUM(installment) AS recovery,
  154.             lp.name,
  155.             ld.installmentNo
  156.         FROM
  157.             loan_recovery lr
  158.             inner join loan_account_opening lao on lr.loan_account_opening_id=lao.id
  159.             inner join loan_disperse ld on ld.loan_account_opening_id=lao.id
  160.             inner join loan_product lp on lp.id=lao.loan_product_id
  161.         WHERE
  162.             lr.value_date BETWEEN ':from' AND ':todate'
  163.              and (lr.organization =:orgname or :orgname=-1)
  164.              and (lp.id = :pro or :pro =-1)
  165.         GROUP BY lp.name ,ld.installmentNo
  166. ) as recovery on recovery.name = outstand.loan and recovery.installmentNo=outstand.installmentNo
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top