protected void load_Click(object sender, EventArgs e)
{
from = Convert.ToDateTime(from_date.Text).ToString("dd-MMM-yyyy");
to = Convert.ToDateTime(to_date.Text).ToString("dd-MMM-yyyy");
String SQLQuery = "";
SQLQuery = @"select abc.BRANCH_CODE,abc.LOAN_CODE, abc.LOAN_PRODUCT_CODE, to_number(listagg(abc.PRINCIPLE,',') within group (order by PRINCIPLE)) PRINCIPLE1,to_number(listagg(abc.markup,',') within group (order by markup)) markup1 ,abc.DATE_CLOSED,abc.DATE_LAST_REP,abc.DATE_LAST_DISBURSED,abc.DATE_EXPIRY,abc.status,decode(bi.GENDER,1,'M',2,'F')GENDER from(
SELECT l1.BORROWER_CODE, L1.BRANCH_CODE, L1.LOAN_CODE, L1.LOAN_PRODUCT_CODE,to_char(sum(G1.TRAN_AMNT)) PRINCIPLE, '' markup,
L1.DATE_CLOSED,DATE_LAST_REP,l1.DATE_LAST_DISBURSED,L1.DATE_EXPIRY, decode(l1.base_status,5,'open',6,'close') status FROM PLS.MG G1
inner join lmf.LMFLOANS L1
ON L1.BRANCH_CODE=G1.BRAN_CODE AND G1.ACCT_BASC = L1.ACCT_CUST_BASC AND G1.ACCT_SFIX = L1.ACCT_CUST_SFIX
WHERE g1.TRAN_DATE between '01 Dec 2014' and '01 Jan 2015' and G1.NARR_LIN2='LMF Repayment'
and l1.base_status in(5,6) and l1.DATE_LAST_DISBURSED >'01 may 2013'
--and g1.tran_code=641 and L1.LOAN_PRODUCT_CODE in(158)
group by l1.BORROWER_CODE,L1.BRANCH_CODE, L1.LOAN_CODE, L1.LOAN_PRODUCT_CODE,L1.DATE_CLOSED,l1.DATE_LAST_DISBURSED,
decode(l1.base_status,5,'open',6,'close'),L1.DATE_EXPIRY,DATE_LAST_REP
union
SELECT l1.BORROWER_CODE,L1.BRANCH_CODE, L1.LOAN_CODE, L1.LOAN_PRODUCT_CODE,'' PRINCIPLE,to_char( sum(G2.TRAN_AMNT)) markup,
L1.DATE_CLOSED,DATE_LAST_REP,l1.DATE_LAST_DISBURSED,DATE_EXPIRY , decode(l1.base_status,5,'open',6,'close') status FROM PLS.MG G2
inner join lmf.LMFLOANS L1
ON L1.BRANCH_CODE=G2.BRAN_CODE AND G2.ACCT_BASC = L1.ACCT_CUST_BASC AND G2.ACCT_SFIX = L1.ACCT_INT_NOM_SFIX
WHERE G2.TRAN_DATE
between '01 Dec 2014' and '01 Jan 2015' and G2.NARR_LIN2='LMF Repayment' and l1.base_status in(5,6)
and l1.DATE_LAST_DISBURSED >'01 may 2013'
--and g1.tran_code=641 and L1.LOAN_PRODUCT_CODE in(158)
group by l1.BORROWER_CODE,L1.BRANCH_CODE, L1.LOAN_CODE, L1.LOAN_PRODUCT_CODE,L1.DATE_CLOSED,
l1.DATE_LAST_DISBURSED,DATE_LAST_REP,DATE_EXPIRY, decode(l1.base_status,5,'open',6,'close')
) abc
inner join pls.BRMBUSINESSENTITIES bi
on abc.BORROWER_CODE=bi.BRM_CODE
group by abc.BRANCH_CODE,abc.LOAN_CODE, abc.LOAN_PRODUCT_CODE,abc.DATE_CLOSED,
abc.DATE_LAST_REP,abc.DATE_LAST_DISBURSED,abc.DATE_EXPIRY,abc.status,decode(bi.GENDER,1,'M',2,'F')
";
DataTable dt = ConnectionsPIBAS.GetFromDBPIBAS(SQLQuery, Convert.ToInt64(DropDownList1.SelectedValue));
GridView1.DataSource = dt;
GridView1.DataBind();
}
protected void load_Click(object sender, EventArgs e)
{
from = Convert.ToDateTime(from_date.Text).ToString("dd-MMM-yyyy");
to = Convert.ToDateTime(to_date.Text).ToString("dd-MMM-yyyy");
string SQLQuery;
SQLQuery = @"select '' File1, branch_code,'' NIC, NIC_NEW CNIC,FIRST_NAME,MIDDLE_NAME,LAST_NAME,DATE_OF_BIRTH, GENDER,
'' Spouse_First_Name ,
'' Spouse_Middle_Name,
'' Spouse_Last_Name,
'' Spouses_Birth_Name,
REGEXP_SUBSTR(FATHER_NAME, '[^ ]+', 1, 1) Father_First_Name ,
case
when REGEXP_SUBSTR(FATHER_NAME, '[^ ]+', 1, 3) is null
then
REGEXP_SUBSTR(FATHER_NAME, '[^ ]+', 1, 3) else
REGEXP_SUBSTR(FATHER_NAME, '[^ ]+', 1, 2)
end Father_Middle_Name,
case
when REGEXP_SUBSTR(FATHER_NAME, '[^ ]+', 1, 3) is null
then
REGEXP_SUBSTR(FATHER_NAME, '[^ ]+', 1, 2) else
REGEXP_SUBSTR(FATHER_NAME, '[^ ]+', 1, 3) end Father_Last_Name,
'' Father_Birth_Name,
'' Mothers_First_Name ,
'' Mothers_Middle_Name,
'' Mothers1_Last_Name,
'' Mothers_Birth_Name,
address,
'' Village,
'' Chak,
'' U_C,
'' Tehsil_Town,
city ,
'' Electronic_Consumer,
'' Gas_Consumer,
'' Phone,
'' Cell,
SHRT_NAME Company_Name, 'Proprietor ship' Ownership_Status , case
when LOAN_PRODUCT_CODE='158'
then 'Trade'
when LOAN_PRODUCT_CODE >'158' and LOAN_PRODUCT_CODE<'163'
then 'Others'
when LOAN_PRODUCT_CODE ='163'
then 'Agriculture'
when LOAN_PRODUCT_CODE >'163' and LOAN_PRODUCT_CODE<'168'
then 'Live Stock/Poultry'
when LOAN_PRODUCT_CODE ='168'
then 'Others'
end as Business_Category,
address,
'' Village,
'' Chak,
'' U_C,
'' Tehsil_Town,
city,
'' Electronic_Consumer,
'' Gas_Consumer,
'' Phone,
'' Cell,
BRANCH_CODE||'0'||LOAN_CODE Account,'IN' Account_Type,DATE_LAST_DISBURSED,
maturity,DISB_TOTAL_AMOUNT,'INT' Term, 'N' Electronic_Alert,'PRN' Association_Type,'' Group_Id,
gcnic NIC,
gcnic CNIC,
GFname First_Name ,
gMIDNAMe Middle_Name,
GLastName Last_Name,
'' Birth_Name,
'' Association
from(
SELECT distinct lmfs.DATE_LAST_DISBURSED,bi.BRM_CODE ,bi.BRM_CODE BRAN_CODE , '' AccountNo,lmfs.DESN SHRT_NAME,bi.FIRST_NAME,bi.MIDDLE_NAME,bi.LAST_NAME--,grn.BRANCH_CODE||'-'|| grn.GUARANTOR_ACCT_BASC GUARANTOR_ACCT
,lmfs.DATE_ADDED, lmfs.BRANCH_CODE,lmfs.LOAN_CODE,lmfs.LOAN_PRODUCT_CODE,lmfp.NME,lmfs.INT_RATE,lmfs.CREDIT_OFFICER,lmfs.DISB_TOTAL_AMOUNT
,bi.NIC_NEW,decode(bi.GENDER,1,'M',2,'F')GENDER,
lmfs.OUTSTANDING_PRINCIPAL,lmfs.DATE_EXPIRY maturity,lmfs.INT_RATE,
lmfs.OUTSTANDING_INT_NORMAL,lmfs.gp,lmfsc.DATE_FIRST_INST,lmfs.DATE_LAST_REP,lmfs.DATE_DUE,lmfs.SUPERVISION1_USER,lmfs.CREDIT_OFFICER,
lmfs.PERIOD_MONTH,lmfs.PERIOD_YEAR,bi.FATHER_NAME,bi.DATE_OF_BIRTH
,brmad.LINE1||' ' || brmad.LINE2||''||brmad.line3||' ' ||brmad.line4 address,brmad.line4
city,
case
when lmfs.PERIOD_YEAR=0 then lmfs.PERIOD_MONTH*30
when lmfs.PERIOD_MONTH=0 then 365
else lmfs.PERIOD_DAY
end days,
case
when lmfs.PERIOD_YEAR=0 then lmfs.PERIOD_MONTH
when lmfs.PERIOD_MONTH=0 then lmfs.PERIOD_YEAR *12
else lmfs.PERIOD_DAY
end NoOFInstalment
,lmfs.INT_TYPE,lmfs.DAYS_PAST_DUE,lmfs.DUE_PRINCIPAL
,b2.FIRST_NAME GFname,b2.MIDDLE_NAME gMIDNAMe,b2.LAST_NAME GLastName,b2.nic_new gcnic
FROM pls.BRMBUSINESSENTITIES Bi
--inner join pls.MD md
--on md.BRM_CODE=bi.BRM_CODE and md.BRAN_CODE=bi.BRANCH_CODE
inner join pls.BRMADDRESSES brmad
on
brmad.BRM_CODE=bi.BRM_CODE and brmad.ADDRESS_SEQ=1
--and (brmad.ADDRESS_TYPE_CODE='1' or brmad.ADDRESS_TYPE_CODE='2' or brmad.ADDRESS_TYPE_CODE='3')
inner join lmf.LMFLOANS lmfs
on lmfs.BORROWER_CODE =bi.BRM_CODE and lmfs.BRANCH_CODE=bi.BRANCH_CODE
inner join lmf.LMFLOANPRODUCTS lmfp
on lmfp.LOAN_PRODUCT_CODE=lmfs.LOAN_PRODUCT_CODE
inner join lmf.LMFLOANSCHEDULES lmfSc
on lmfsc.LOAN_CODE=lmfs.LOAN_CODE and lmfsc.BRANCH_CODE=lmfs.BRANCH_CODE
left join lmf.LMFLOANGUARANTORS grn
on grn.LOAN_CODE=lmfs.LOAN_CODE and grn.BRANCH_CODE=lmfs.BRANCH_CODE
left join pls.BRMBUSINESSENTITIES b2
on b2.BRANCH_CODE=grn.BRANCH_CODE and b2.BRM_CODE=grn.GUARANTOR_ACCT_BASC
where lmfs.BASE_STATUS='5' and lmfs.DATE_LAST_DISBURSED between '" + from + "' and '" + to + "'order by lmfs.DATE_ADDED asc)";
DataTable dt = ConnectionsPIBAS.GetFromDBPIBAS(SQLQuery, Convert.ToInt64(DropDownList1.SelectedItem.Value));
GridView1.DataSource = dt;
GridView1.DataBind();
}
protected void load_Click(object sender, EventArgs e)
{
last = Convert.ToDateTime(from_date.Text).ToString("dd-MMM-yyyy");
string SQLQuery = "";
SQLQuery = @"select BRANCH_CODE, file1, NIC , CNIC,
Account, NEW_ACount, Account_Type,
case when Account_Status=5
then 'open'
else 'close' end Account_Status1 , Status_Date,DISB_TOTAL_AMOUNT,
maturity, Term,
--to_Number(nvl(TRAN_AMNT,'0'))+to_number(nvl(Tran_MarkUp,'0'))
sum(Last_Payment) Last_Payment1 ,
Outstanding_Balance,
Over_Due_Balance,
Payment_Status, Association_Type, Group_Id,
NIC1,
CNIC1,
First_Name1 ,
Middle_Name1, Last_Name1,
Birth_Name1,
Association from(
select distinct BRANCH_CODE,'' file1,'' NIC ,NIC_NEW CNIC,
BRANCH_CODE||'0'||LOAN_CODE Account,'' NEW_ACount, 'IN' Account_Type,
BASE_STATUS Account_Status ,
'' Status_Date,DISB_TOTAL_AMOUNT,
maturity,'INT' Term,
--to_Number(nvl(TRAN_AMNT,'0'))+to_number(nvl(Tran_MarkUp,'0'))
AmntNorm Last_Payment,
OUTSTANDING_PRINCIPAL Outstanding_Balance,
DUE_PRINCIPAL+dUE_INT_NORMAL+DUE_INT_gp Over_Due_Balance,
case
when DAYS_PAST_DUE='0'
then 'ok'
when DAYS_PAST_DUE >0 and DAYS_PAST_DUE <30
then 'X'
when DAYS_PAST_DUE >30 and DAYS_PAST_DUE<60
then '30+'
when DAYS_PAST_DUE >60 and DAYS_PAST_DUE <89
then '60+'
when DAYS_PAST_DUE >90 and DAYS_PAST_DUE<119
then '90+'
when DAYS_PAST_DUE >120 and DAYS_PAST_DUE <149
then '120+'
when DAYS_PAST_DUE >150 and DAYS_PAST_DUE<179
then '150+'
when DAYS_PAST_DUE >180
then '180+'
end
Payment_Status,
'PRN' Association_Type,'' Group_Id,
gcnic NIC1,
gcnic CNIC1,
GFname First_Name1 ,
gMIDNAMe Middle_Name1,
GLastName Last_Name1,
'' Birth_Name1,
'' Association--,untildue, date_due--,TRAN_AMNT,abas,asfic,tdate
from(
SELECT distinct lmfs.DATE_LAST_DISBURSED, '' AmntNorm,lmfs.BASE_STATUS,--,md.TRAN_AMNT,md1.TRAN_AMNT Tran_MarkUp,ACCT_INT_NOM_SFIX,md.BRAN_CODE,md.ACCT_BASC abas,md.ACCT_SFIX asfic,md.TRAN_DATE tdate,
DECODE(lmfs.DAYS_PAST_DUE,'0',0,lmfs.DUE_PRINCIPAL +lmfs.DUE_INT_BEFORE_DUE+lmfs.dUE_INT_NORMAL+lmfs.DUE_INT_AFTER_DUE+lmfs.DUE_INT_gp+lmfs.OUTSTANDING_OTHERS ) OVERDUEAMOUNT,
--DECODE(DAYS_PAST_DUE,'0', date_due - date_current ,0) untildue,
bi.BRM_CODE ,dUE_INT_NORMAL,
DUE_INT_gp,
bi.BRM_CODE BRAN_CODE , '' AccountNo,'' SHRT_NAME,bi.FIRST_NAME,bi.MIDDLE_NAME,bi.LAST_NAME--,grn.BRANCH_CODE||'-'|| grn.GUARANTOR_ACCT_BASC GUARANTOR_ACCT
,lmfs.DATE_ADDED, lmfs.BRANCH_CODE,lmfs.LOAN_CODE,lmfs.LOAN_PRODUCT_CODE,lmfp.NME,lmfs.INT_RATE,lmfs.CREDIT_OFFICER,lmfs.DISB_TOTAL_AMOUNT
,bi.NIC_NEW,decode(bi.GENDER,1,'Male',2,'Female')GENDER,
lmfs.OUTSTANDING_PRINCIPAL,lmfs.DATE_EXPIRY maturity,lmfs.INT_RATE,
lmfs.OUTSTANDING_INT_NORMAL,lmfs.gp,lmfsc.DATE_FIRST_INST,lmfs.DATE_LAST_REP,lmfs.DATE_DUE,lmfs.SUPERVISION1_USER,lmfs.CREDIT_OFFICER,
lmfs.PERIOD_MONTH,lmfs.PERIOD_YEAR,bi.FATHER_NAME,bi.DATE_OF_BIRTH
,brmad.LINE1||' ' || brmad.LINE2||''||brmad.line3||' ' ||brmad.line4 address,brmad.line4
city,
case
when lmfs.PERIOD_YEAR=0 then lmfs.PERIOD_MONTH*30
when lmfs.PERIOD_MONTH=0 then 365
else lmfs.PERIOD_DAY
end days,
case
when lmfs.PERIOD_YEAR=0 then lmfs.PERIOD_MONTH
when lmfs.PERIOD_MONTH=0 then lmfs.PERIOD_YEAR *12
else lmfs.PERIOD_DAY
end NoOFInstalment
,lmfs.INT_TYPE,lmfs.DAYS_PAST_DUE,lmfs.DUE_PRINCIPAL
,b2.FIRST_NAME GFname,b2.MIDDLE_NAME gMIDNAMe,b2.LAST_NAME GLastName,b2.nic_new gcnic
FROM pls.BRMBUSINESSENTITIES Bi
inner join pls.BRMADDRESSES brmad
on
brmad.BRM_CODE=bi.BRM_CODE and brmad.ADDRESS_SEQ=1
--and (brmad.ADDRESS_TYPE_CODE='1' or brmad.ADDRESS_TYPE_CODE='2' or brmad.ADDRESS_TYPE_CODE='3')
inner join lmf.LMFLOANS lmfs
on lmfs.BORROWER_CODE =bi.BRM_CODE and lmfs.BRANCH_CODE=bi.BRANCH_CODE
--inner join lmf.LMFLOANREPAYMENTS lmfr
--on lmfr.BRANCH_CODE= lmfs.BRANCH_CODE and lmfr.LOAN_CODE=lmfs.LOAN_CODE and lmfr.DATE_VALUE =(select max(lmfr1.DATE_VALUE) from lmf.LMFLOANREPAYMENTS lmfr1 where lmfr1.BRANCH_CODE= lmfr.BRANCH_CODE and lmfr1.LOAN_CODE=lmfr.LOAN_CODE ) -->'1-may-2014'
--left join pls.mg md
--on md.bran_code=lmfs.BRANCH_CODE and lmfs.ACCT_CHG_NOM_BASC=md.ACCT_BASC and md.ACCT_SFIX= lmfs.ACCT_CUST_SFIX and to_char(md.tran_date,'mm/yyyy')=to_char(sysdate-40,'mm/yyyy') and md.NARR_LIN2='LMF Repayment'
--left join pls.mg md1
--on md1.bran_code=lmfs.BRANCH_CODE and lmfs.ACCT_CHG_NOM_BASC=md1.ACCT_BASC and md1.ACCT_SFIX= lmfs.ACCT_INT_NOM_SFIX and to_char(md1.tran_date,'mm/yyyy')=to_char(sysdate-40,'mm/yyyy') and md.NARR_LIN2='LMF Repayment'
inner join lmf.LMFLOANPRODUCTS lmfp
on lmfp.LOAN_PRODUCT_CODE=lmfs.LOAN_PRODUCT_CODE
inner join lmf.LMFLOANSCHEDULES lmfSc
on lmfsc.LOAN_CODE=lmfs.LOAN_CODE and lmfsc.BRANCH_CODE=lmfs.BRANCH_CODE
left join lmf.LMFLOANGUARANTORS grn
on grn.LOAN_CODE=lmfs.LOAN_CODE and grn.BRANCH_CODE=lmfs.BRANCH_CODE
--1jan 2014
left join pls.BRMBUSINESSENTITIES b2
on b2.BRANCH_CODE=grn.BRANCH_CODE and b2.BRM_CODE=grn.GUARANTOR_ACCT_BASC
where lmfs.BASE_STATUS in('5','6') and lmfs.DATE_LAST_DISBURSED<'" + last + @"' and ( lmfs.DATE_CLOSED > '01-SEP-2013' OR lmfs.DATE_CLOSED IS NULL)-- and lmfs.DATE_ADDED between '01-May-2014' and '01-Jun-2014'
order by lmfs.DATE_ADDED asc)
union
select distinct BRANCH_CODE,'' file1,'' NIC ,NIC_NEW CNIC,
BRANCH_CODE||'0'||LOAN_CODE Account,'' NEW_ACount, 'IN' Account_Type,
BASE_STATUS Account_Status ,
'' Status_Date,DISB_TOTAL_AMOUNT,
maturity,'INT' Term,
--to_Number(nvl(TRAN_AMNT,'0'))+to_number(nvl(Tran_MarkUp,'0'))
AmntNorm Last_Payment,
OUTSTANDING_PRINCIPAL Outstanding_Balance,
DUE_PRINCIPAL+dUE_INT_NORMAL+DUE_INT_gp Over_Due_Balance,
case
when DAYS_PAST_DUE='0'
then 'ok'
when DAYS_PAST_DUE >0 and DAYS_PAST_DUE <30
then 'X'
when DAYS_PAST_DUE >30 and DAYS_PAST_DUE<60
then '30+'
when DAYS_PAST_DUE >60 and DAYS_PAST_DUE <89
then '60+'
when DAYS_PAST_DUE >90 and DAYS_PAST_DUE<119
then '90+'
when DAYS_PAST_DUE >120 and DAYS_PAST_DUE <149
then '120+'
when DAYS_PAST_DUE >150 and DAYS_PAST_DUE<179
then '150+'
when DAYS_PAST_DUE >180
then '180+'
end
Payment_Status,
'PRN' Association_Type,'' Group_Id,
gcnic NIC,
gcnic CNIC,
GFname First_Name ,
gMIDNAMe Middle_Name,
GLastName Last_Name,
'' Birth_Name,
'' Association--,untildue, date_due--,TRAN_AMNT,abas,asfic,tdate
from(
SELECT distinct lmfs.DATE_LAST_DISBURSED, to_char(lmfr.AMOUNT+lmfr.INT_NORMAL) AmntNorm,lmfs.BASE_STATUS,--,md.TRAN_AMNT,md1.TRAN_AMNT Tran_MarkUp,ACCT_INT_NOM_SFIX,md.BRAN_CODE,md.ACCT_BASC abas,md.ACCT_SFIX asfic,md.TRAN_DATE tdate,
DECODE(lmfs.DAYS_PAST_DUE,'0',0,lmfs.DUE_PRINCIPAL +lmfs.DUE_INT_BEFORE_DUE+lmfs.dUE_INT_NORMAL+lmfs.DUE_INT_AFTER_DUE+lmfs.DUE_INT_gp+lmfs.OUTSTANDING_OTHERS ) OVERDUEAMOUNT,
--DECODE(DAYS_PAST_DUE,'0', date_due - date_current ,0) untildue,
bi.BRM_CODE ,dUE_INT_NORMAL,
DUE_INT_gp,
bi.BRM_CODE BRAN_CODE , '' AccountNo,'' SHRT_NAME,bi.FIRST_NAME,bi.MIDDLE_NAME,bi.LAST_NAME--,grn.BRANCH_CODE||'-'|| grn.GUARANTOR_ACCT_BASC GUARANTOR_ACCT
,lmfs.DATE_ADDED, lmfs.BRANCH_CODE,lmfs.LOAN_CODE,lmfs.LOAN_PRODUCT_CODE,lmfp.NME,lmfs.INT_RATE,lmfs.CREDIT_OFFICER,lmfs.DISB_TOTAL_AMOUNT
,bi.NIC_NEW,decode(bi.GENDER,1,'Male',2,'Female')GENDER,
lmfs.OUTSTANDING_PRINCIPAL,lmfs.DATE_EXPIRY maturity,lmfs.INT_RATE,
lmfs.OUTSTANDING_INT_NORMAL,lmfs.gp,lmfsc.DATE_FIRST_INST,lmfs.DATE_LAST_REP,lmfs.DATE_DUE,lmfs.SUPERVISION1_USER,lmfs.CREDIT_OFFICER,
lmfs.PERIOD_MONTH,lmfs.PERIOD_YEAR,bi.FATHER_NAME,bi.DATE_OF_BIRTH
,brmad.LINE1||' ' || brmad.LINE2||''||brmad.line3||' ' ||brmad.line4 address,brmad.line4
city,
case
when lmfs.PERIOD_YEAR=0 then lmfs.PERIOD_MONTH*30
when lmfs.PERIOD_MONTH=0 then 365
else lmfs.PERIOD_DAY
end days,
case
when lmfs.PERIOD_YEAR=0 then lmfs.PERIOD_MONTH
when lmfs.PERIOD_MONTH=0 then lmfs.PERIOD_YEAR *12
else lmfs.PERIOD_DAY
end NoOFInstalment
,lmfs.INT_TYPE,lmfs.DAYS_PAST_DUE,lmfs.DUE_PRINCIPAL
,b2.FIRST_NAME GFname,b2.MIDDLE_NAME gMIDNAMe,b2.LAST_NAME GLastName,b2.nic_new gcnic
FROM pls.BRMBUSINESSENTITIES Bi
inner join pls.BRMADDRESSES brmad
on
brmad.BRM_CODE=bi.BRM_CODE and brmad.ADDRESS_SEQ=1
--and (brmad.ADDRESS_TYPE_CODE='1' or brmad.ADDRESS_TYPE_CODE='2' or brmad.ADDRESS_TYPE_CODE='3')
inner join lmf.LMFLOANS lmfs
on lmfs.BORROWER_CODE =bi.BRM_CODE and lmfs.BRANCH_CODE=bi.BRANCH_CODE
inner join lmf.LMFLOANREPAYMENTS lmfr
on lmfr.BRANCH_CODE= lmfs.BRANCH_CODE and lmfr.LOAN_CODE=lmfs.LOAN_CODE and lmfr.DATE_VALUE =(select max(lmfr1.DATE_VALUE) from lmf.LMFLOANREPAYMENTS lmfr1 where lmfr1.BRANCH_CODE= lmfr.BRANCH_CODE and lmfr1.LOAN_CODE=lmfr.LOAN_CODE ) -->'1-may-2014'
--left join pls.mg md
--on md.bran_code=lmfs.BRANCH_CODE and lmfs.ACCT_CHG_NOM_BASC=md.ACCT_BASC and md.ACCT_SFIX= lmfs.ACCT_CUST_SFIX and to_char(md.tran_date,'mm/yyyy')=to_char(sysdate-40,'mm/yyyy') and md.NARR_LIN2='LMF Repayment'
--left join pls.mg md1
--on md1.bran_code=lmfs.BRANCH_CODE and lmfs.ACCT_CHG_NOM_BASC=md1.ACCT_BASC and md1.ACCT_SFIX= lmfs.ACCT_INT_NOM_SFIX and to_char(md1.tran_date,'mm/yyyy')=to_char(sysdate-40,'mm/yyyy') and md.NARR_LIN2='LMF Repayment'
inner join lmf.LMFLOANPRODUCTS lmfp
on lmfp.LOAN_PRODUCT_CODE=lmfs.LOAN_PRODUCT_CODE
inner join lmf.LMFLOANSCHEDULES lmfSc
on lmfsc.LOAN_CODE=lmfs.LOAN_CODE and lmfsc.BRANCH_CODE=lmfs.BRANCH_CODE
left join lmf.LMFLOANGUARANTORS grn
on grn.LOAN_CODE=lmfs.LOAN_CODE and grn.BRANCH_CODE=lmfs.BRANCH_CODE
--1jan 2014
left join pls.BRMBUSINESSENTITIES b2
on b2.BRANCH_CODE=grn.BRANCH_CODE and b2.BRM_CODE=grn.GUARANTOR_ACCT_BASC
where lmfs.BASE_STATUS in('5','6') and lmfs.DATE_LAST_DISBURSED<'" + last + @"' and ( lmfs.DATE_CLOSED > '01-SEP-2013' OR lmfs.DATE_CLOSED IS NULL)
order by lmfs.DATE_ADDED asc))xyz
group by
BRANCH_CODE, file1, NIC , CNIC,
Account, NEW_ACount, Account_Type,
Account_Status , Status_Date,DISB_TOTAL_AMOUNT,
maturity, Term,
--to_Number(nvl(TRAN_AMNT,'0'))+to_number(nvl(Tran_MarkUp,'0'))
Outstanding_Balance,
Over_Due_Balance,
Payment_Status, Association_Type, Group_Id,
NIC1,
CNIC1,
First_Name1 ,
Middle_Name1, Last_Name1,
Birth_Name1,
Association
";
DataTable dt = ConnectionsPIBAS.GetFromDBPIBAS(SQLQuery, Convert.ToInt64(DropDownList1.SelectedItem.Value));
GridView1.DataSource = dt;
GridView1.DataBind();
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
string SQLQuery;
SQLQuery = @"select '' File1, branch_code, NIC_NEW CNIC,
BRANCH_CODE||'0'||LOAN_CODE Account,'' New_Account, 'IN' Account_Type,'' NEw_defual_status, '' Default_date,
DUE_PRINCIPAL+dUE_INT_NORMAL+DUE_INT_gp Default_Amount,
'Payment Default' Reson_To_Report
from (
SELECT distinct lmfs.DATE_LAST_DISBURSED,bi.BRM_CODE ,bi.BRM_CODE BRAN_CODE , '' AccountNo, lmfs.DESN SHRT_NAME,bi.FIRST_NAME,bi.MIDDLE_NAME,bi.LAST_NAME--,grn.BRANCH_CODE||'-'|| grn.GUARANTOR_ACCT_BASC GUARANTOR_ACCT
,lmfs.DATE_ADDED, lmfs.BRANCH_CODE,lmfs.LOAN_CODE,lmfs.LOAN_PRODUCT_CODE,lmfp.NME,lmfs.INT_RATE,lmfs.CREDIT_OFFICER,lmfs.DISB_TOTAL_AMOUNT
,bi.NIC_NEW,decode(bi.GENDER,1,'M',2,'F')GENDER,
dUE_INT_NORMAL,DUE_INT_gp,
lmfs.OUTSTANDING_PRINCIPAL,lmfs.DATE_EXPIRY maturity,lmfs.INT_RATE,
lmfs.OUTSTANDING_INT_NORMAL,lmfs.gp,lmfsc.DATE_FIRST_INST,lmfs.DATE_LAST_REP,lmfs.DATE_DUE,lmfs.SUPERVISION1_USER,lmfs.CREDIT_OFFICER,
lmfs.PERIOD_MONTH,lmfs.PERIOD_YEAR,bi.FATHER_NAME,bi.DATE_OF_BIRTH
,brmad.LINE1||' ' || brmad.LINE2||''||brmad.line3||' ' ||brmad.line4 address,brmad.line4
city,
case
when lmfs.PERIOD_YEAR=0 then lmfs.PERIOD_MONTH*30
when lmfs.PERIOD_MONTH=0 then 365
else lmfs.PERIOD_DAY
end days,
case
when lmfs.PERIOD_YEAR=0 then lmfs.PERIOD_MONTH
when lmfs.PERIOD_MONTH=0 then lmfs.PERIOD_YEAR *12
else lmfs.PERIOD_DAY
end NoOFInstalment
,lmfs.INT_TYPE,lmfs.DAYS_PAST_DUE,lmfs.DUE_PRINCIPAL
,b2.FIRST_NAME GFname,b2.MIDDLE_NAME gMIDNAMe,b2.LAST_NAME GLastName,b2.nic_new gcnic
FROM pls.BRMBUSINESSENTITIES Bi
--inner join pls.MD md
--on md.BRM_CODE=bi.BRM_CODE and md.BRAN_CODE=bi.BRANCH_CODE
inner join pls.BRMADDRESSES brmad
on
brmad.BRM_CODE=bi.BRM_CODE and brmad.ADDRESS_SEQ=1
--and (brmad.ADDRESS_TYPE_CODE='1' or brmad.ADDRESS_TYPE_CODE='2' or brmad.ADDRESS_TYPE_CODE='3')
inner join lmf.LMFLOANS lmfs
on lmfs.BORROWER_CODE =bi.BRM_CODE and lmfs.BRANCH_CODE=bi.BRANCH_CODE
inner join lmf.LMFLOANPRODUCTS lmfp
on lmfp.LOAN_PRODUCT_CODE=lmfs.LOAN_PRODUCT_CODE
inner join lmf.LMFLOANSCHEDULES lmfSc
on lmfsc.LOAN_CODE=lmfs.LOAN_CODE and lmfsc.BRANCH_CODE=lmfs.BRANCH_CODE
left join lmf.LMFLOANGUARANTORS grn
on grn.LOAN_CODE=lmfs.LOAN_CODE and grn.BRANCH_CODE=lmfs.BRANCH_CODE
left join pls.BRMBUSINESSENTITIES b2
on b2.BRANCH_CODE=grn.BRANCH_CODE and b2.BRM_CODE=grn.GUARANTOR_ACCT_BASC
where lmfs.BASE_STATUS='5' and DAYS_PAST_DUE > 0
order by lmfs.DATE_ADDED asc)
--where BRANCH_CODE||'0'||LOAN_CODE in ('105809')";
DataTable dt = ConnectionsPIBAS.GetFromDBPIBAS(SQLQuery, Convert.ToInt64(DropDownList1.SelectedItem.Value));
GridView1.DataSource = dt;
GridView1.DataBind();
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
string SQLQuery;
SQLQuery = @"select BRANCH_CODE
, listagg(month1 ,'') within group(order by month1) Month1
, listagg(month2 ,'') within group(order by month2) Month2
, listagg(month3 ,'') within group(order by month3) Month3
, listagg(month4 ,'') within group(order by month4) Month4
, listagg(month5 ,'') within group(order by month5) Month5
, listagg(month6 ,'') within group(order by month6) Month6
, listagg(month7 ,'') within group(order by month7) Month7
, listagg(month8 ,'') within group(order by month8) Month8
, listagg(month9 ,'') within group(order by month9) Month9
, listagg(month10,'') within group(order by month10)Month10
from (
SELECT BRANCH_CODE,date_due,
DECODE ( date_due , 1, principal ||','||count ) month1,
DECODE ( date_due , 2, principal ||','||count ) month2,
DECODE ( date_due , 3, principal ||','||count ) month3,
DECODE ( date_due , 4, principal ||','||count ) month4,
DECODE ( date_due , 5, principal ||','||count ) month5,
DECODE ( date_due , 6, principal ||','||count ) month6,
DECODE ( date_due , 7, principal ||','||count ) month7,
DECODE ( date_due , 8, principal ||','||count ) month8,
DECODE ( date_due , 9, principal ||','||count ) month9,
DECODE ( date_due , 10,principal ||','||count ) month10
from (
select principal ,BRANCH_CODE,date_due,count,row_number() OVER ( partition by BRANCH_CODE order by rownum) rn from(
SELECT sum(INST_AMOUNT) principal ,BRANCH_CODE, '1' date_due ,count(*) count
FROM lmf.LMFLOANSCHEDULEINSTALLMENTS WHERE to_char(DATE_DUE ,'mm-yyyy')=to_char(sysdate,'mm-yyyy')
group by BRANCH_CODE, to_char(DATE_DUE,'mm-yyyy')
union
SELECT sum(INST_AMOUNT) principal ,BRANCH_CODE, '2' date_due,count(*) count FROM lmf.LMFLOANSCHEDULEINSTALLMENTS
WHERE to_char(DATE_DUE ,'mm-yyyy')=to_char(sysdate+30,'mm-yyyy')
group by BRANCH_CODE,to_char(DATE_DUE,'mm-yyyy')
union
SELECT sum(INST_AMOUNT) principal ,BRANCH_CODE, '3' date_due,count(*) count FROM lmf.LMFLOANSCHEDULEINSTALLMENTS
WHERE to_char(DATE_DUE ,'mm-yyyy')=to_char(sysdate+60,'mm-yyyy')
group by BRANCH_CODE,to_char(DATE_DUE,'mm-yyyy')
union
SELECT sum(INST_AMOUNT) principal ,BRANCH_CODE, '4' date_due,count(*) count FROM lmf.LMFLOANSCHEDULEINSTALLMENTS
WHERE to_char(DATE_DUE ,'mm-yyyy')=to_char(sysdate+90,'mm-yyyy')
group by BRANCH_CODE,to_char(DATE_DUE,'mm-yyyy')
union
SELECT sum(INST_AMOUNT) principal ,BRANCH_CODE, '5' date_due,count(*) count FROM lmf.LMFLOANSCHEDULEINSTALLMENTS
WHERE to_char(DATE_DUE ,'mm-yyyy')=to_char(sysdate+120,'mm-yyyy')
group by BRANCH_CODE,to_char(DATE_DUE,'mm-yyyy')
union
SELECT sum(INST_AMOUNT) principal ,BRANCH_CODE, '6' date_due,count(*) count FROM lmf.LMFLOANSCHEDULEINSTALLMENTS
WHERE to_char(DATE_DUE ,'mm-yyyy')=to_char(sysdate+150,'mm-yyyy')
group by BRANCH_CODE,to_char(DATE_DUE,'mm-yyyy')
union
SELECT sum(INST_AMOUNT) principal ,BRANCH_CODE,'7' date_due,count(*) count FROM lmf.LMFLOANSCHEDULEINSTALLMENTS
WHERE to_char(DATE_DUE ,'mm-yyyy')=to_char(sysdate+180,'mm-yyyy')
group by BRANCH_CODE,to_char(DATE_DUE,'mm-yyyy')
union
SELECT sum(INST_AMOUNT) principal ,BRANCH_CODE, '8' date_due,count(*) count FROM lmf.LMFLOANSCHEDULEINSTALLMENTS
WHERE to_char(DATE_DUE ,'mm-yyyy')=to_char(sysdate+210,'mm-yyyy')
group by BRANCH_CODE,to_char(DATE_DUE,'mm-yyyy')
union
SELECT sum(INST_AMOUNT) principal ,BRANCH_CODE, '9' date_due,count(*) count FROM lmf.LMFLOANSCHEDULEINSTALLMENTS
WHERE to_char(DATE_DUE ,'mm-yyyy')=to_char(sysdate+240,'mm-yyyy')
group by BRANCH_CODE,to_char(DATE_DUE,'mm-yyyy')
union
SELECT sum(INST_AMOUNT) principal ,BRANCH_CODE,'10' date_due,count(*) count FROM lmf.LMFLOANSCHEDULEINSTALLMENTS
WHERE to_char(DATE_DUE ,'mm-yyyy')=to_char(sysdate+270,'mm-yyyy')
group by BRANCH_CODE,to_char(DATE_DUE,'mm-yyyy')
)level1))level2 GROUP BY BRANCH_CODE";
DataTable dt = ConnectionsPIBAS.GetFromDBPIBAS(SQLQuery, Convert.ToInt64(DropDownList1.SelectedItem.Value));
GridView1.DataSource = dt;
GridView1.DataBind();
}