public List <ViewapplicationsDTO> ViewApplicationsbyid(string applicationid, string ConnectionString)
{
lstViewapplications = new List <ViewapplicationsDTO>();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select ta.contacttype,ta.contactreferenceid,ta.loantypeid,loantype,ta.loanid,loanname,Applicanttype, applicationid,vchapplicationid, applicantname, dateofapplication,purposeofloan, amountrequested, tenureofloan, rateofinterest, interesttype, loanpayin,loaninstalmentpaymentmode,coalesce(instalmentamount,0)as instalmentamount,coalesce(partprinciplepaidinterval,0) as partprinciplepaidinterval ,tc.businessentitycontactno,penaltygraceperiod,(select schemeid from tblmstschemenamescodes where upper(schemename)=upper(ta.schemename) and statusid=" + Convert.ToInt32(Status.Active) + ") as schemeid from tabapplication ta join tblmstcontact tc on ta.contactreferenceid = tc.contactreferenceid left join tblmstpenaltyconfiguration tp on ta.loanid=tp.loanid where upper(loanstatus) in('FI SAVED','FI PARTIAL SAVED','TELE VERIFICATION','FIELD VERIFICATION','DOCUMENT VERIFICATION','LOAN ACCEPTED') and ta.statusid = " + Convert.ToInt32(Status.Active) + " and vchapplicationid='" + applicationid + "';"))
while (dr.Read())
{
ViewapplicationsDTO objViewapplications = new ViewapplicationsDTO();
objViewapplications.pContacttype = dr["contacttype"].ToString();
objViewapplications.pContactreferenceid = dr["contactreferenceid"].ToString();
objViewapplications.pLoantypeid = Convert.ToInt64(dr["loantypeid"]);
objViewapplications.pLoantype = dr["loantype"].ToString();
objViewapplications.pLoanid = Convert.ToInt64(dr["loanid"]);
objViewapplications.pLoanname = dr["loanname"].ToString();
objViewapplications.pApplicanttype = dr["Applicanttype"].ToString();
objViewapplications.pApplicationid = Convert.ToInt64(dr["applicationid"]);
objViewapplications.pVchapplicationid = dr["vchapplicationid"].ToString();
objViewapplications.pApplicantname = dr["applicantname"].ToString();
objViewapplications.pDateofapplication = Convert.ToDateTime(dr["Dateofapplication"]).ToString("dd/MM/yyyy");
objViewapplications.pPurposeofloan = dr["purposeofloan"].ToString();
objViewapplications.pAmountrequested = Convert.ToDecimal(dr["amountrequested"]);
objViewapplications.pTenureofloan = Convert.ToDecimal(dr["tenureofloan"]);
objViewapplications.pRateofinterest = Convert.ToDecimal(dr["rateofinterest"]);
objViewapplications.pInteresttype = dr["interesttype"].ToString();
objViewapplications.pLoanpayin = dr["loanpayin"].ToString();
objViewapplications.pLoaninstalmentpaymentmode = dr["Loaninstalmentpaymentmode"].ToString();
objViewapplications.pMobileno = dr["businessentitycontactno"].ToString();
objViewapplications.pInstalmentamount = Convert.ToDecimal(dr["Instalmentamount"]);
objViewapplications.pInterevels = Convert.ToInt16(dr["partprinciplepaidinterval"]);
objViewapplications.pGraceperiod = Convert.ToDecimal(dr["penaltygraceperiod"]);
objViewapplications.pschemeid = dr["schemeid"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["schemeid"]);
lstViewapplications.Add(objViewapplications);
}
}
catch (Exception ex)
{
throw ex;
}
return(lstViewapplications);
}
public List <ViewapplicationsDTO> ViewApplications(string Viewtype, string ConnectionString)
{
lstViewapplications = new List <ViewapplicationsDTO>();
try
{
if (Viewtype == "New")
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select ta.contacttype,ta.contactreferenceid,loantypeid,loantype,loanid,loanname,Applicanttype,applicationid,vchapplicationid, applicantname, dateofapplication,purposeofloan, amountrequested, tenureofloan, rateofinterest, interesttype, loanpayin,loaninstalmentpaymentmode,coalesce(instalmentamount,0)as instalmentamount,coalesce(partprinciplepaidinterval,0) as partprinciplepaidinterval , tc.businessentitycontactno from tabapplication ta join tblmstcontact tc on ta.contactreferenceid = tc.contactreferenceid where upper(loanstatus) in('FI SAVED','FI PARTIAL SAVED','TELE VERIFICATION','FIELD VERIFICATION','DOCUMENT VERIFICATION','LOAN ACCEPTED') and ta.statusid = 1 order by applicationid desc"))
{
while (dr.Read())
{
ViewapplicationsDTO objViewapplications = new ViewapplicationsDTO();
objViewapplications.pContacttype = dr["contacttype"].ToString();
objViewapplications.pContactreferenceid = dr["contactreferenceid"].ToString();
objViewapplications.pLoantypeid = Convert.ToInt64(dr["loantypeid"]);
objViewapplications.pLoantype = dr["loantype"].ToString();
objViewapplications.pLoanid = Convert.ToInt64(dr["loanid"]);
objViewapplications.pLoanname = dr["loanname"].ToString();
objViewapplications.pApplicanttype = dr["Applicanttype"].ToString();
objViewapplications.pApplicationid = Convert.ToInt64(dr["applicationid"]);
objViewapplications.pVchapplicationid = dr["vchapplicationid"].ToString();
objViewapplications.pApplicantname = dr["applicantname"].ToString();
objViewapplications.pDateofapplication = Convert.ToDateTime(dr["Dateofapplication"]).ToString("dd/MM/yyyy");
objViewapplications.pPurposeofloan = dr["purposeofloan"].ToString();
objViewapplications.pAmountrequested = Convert.ToDecimal(dr["amountrequested"]);
objViewapplications.pTenureofloan = Convert.ToDecimal(dr["tenureofloan"]);
objViewapplications.pRateofinterest = Convert.ToDecimal(dr["rateofinterest"]);
objViewapplications.pInteresttype = dr["interesttype"].ToString();
objViewapplications.pLoanpayin = dr["loanpayin"].ToString();
objViewapplications.pLoaninstalmentpaymentmode = dr["Loaninstalmentpaymentmode"].ToString();
objViewapplications.pMobileno = dr["businessentitycontactno"].ToString();
objViewapplications.pInstalmentamount = Convert.ToDecimal(dr["Instalmentamount"]);
objViewapplications.pInterevels = Convert.ToInt16(dr["partprinciplepaidinterval"]);
lstViewapplications.Add(objViewapplications);
}
}
}
if (Viewtype == "Approved")
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select ta.contacttype,ta.contactreferenceid,ta.loantypeid,ta.loantype,ta.loanid,ta.loanname,ta.Applicanttype,ta.applicationid,ta.vchapplicationid,ta.dateofapplication,ta.contactreferenceid,ta.purposeofloan,ta.applicantname,ta.amountrequested,tap.approveddate,tap.approvedby,tap.approvedloanamount,tap.loanpayin,tap.interesttype,tap.tenureofloan,tap.rateofinterest,tc.businessentitycontactno from tabapplication ta join tbltransapprovedapplications tap on ta.vchapplicationid=tap.vchapplicationid join tblmstcontact tc on ta.contactreferenceid=tc.contactreferenceid where loanstatus = 'Loan Approved' order by recordid desc"))
{
while (dr.Read())
{
ViewapplicationsDTO objViewapplications = new ViewapplicationsDTO();
objViewapplications.pContacttype = dr["contacttype"].ToString();
objViewapplications.pContactreferenceid = dr["contactreferenceid"].ToString();
objViewapplications.pLoantypeid = Convert.ToInt64(dr["loantypeid"]);
objViewapplications.pLoantype = dr["loantype"].ToString();
objViewapplications.pLoanid = Convert.ToInt64(dr["loanid"]);
objViewapplications.pLoanname = dr["loanname"].ToString();
objViewapplications.pApplicanttype = dr["Applicanttype"].ToString();
objViewapplications.pVchapplicationid = dr["vchapplicationid"].ToString();
objViewapplications.pApplicationid = Convert.ToInt64(dr["applicationid"]);
objViewapplications.pDateofapplication = Convert.ToDateTime(dr["Dateofapplication"]).ToString("dd/MM/yyyy");
objViewapplications.pContactreferenceid = dr["contactreferenceid"].ToString();
objViewapplications.pApplicantname = dr["applicantname"].ToString();
objViewapplications.pPurposeofloan = dr["purposeofloan"].ToString();
objViewapplications.pAmountrequested = Convert.ToDecimal(dr["amountrequested"]);
objViewapplications.pAproveddate = Convert.ToDateTime(dr["approveddate"]).ToString("dd/MM/yyyy");
objViewapplications.pApprovedloanamount = Convert.ToDecimal(dr["approvedloanamount"]);
objViewapplications.pLoanpayin = dr["loanpayin"].ToString();
objViewapplications.pInteresttype = dr["interesttype"].ToString();
objViewapplications.pTenureofloan = Convert.ToDecimal(dr["Tenureofloan"]);
objViewapplications.pRateofinterest = Convert.ToDecimal(dr["Rateofinterest"]);
objViewapplications.pMobileno = dr["businessentitycontactno"].ToString();
lstViewapplications.Add(objViewapplications);
}
}
}
if (Viewtype == "Rejected")
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select ta.contacttype,ta.contactreferenceid,loantypeid,loantype,loanid,loanname,Applicanttype,applicationid,vchapplicationid, applicantname, dateofapplication,purposeofloan, amountrequested, tenureofloan, rateofinterest, interesttype, loanpayin, tc.businessentitycontactno,ta.approvaldate from tabapplication ta join tblmstcontact tc on ta.contactreferenceid = tc.contactreferenceid where loanstatus = 'Loan Rejected' and ta.statusid = 1 order by applicationid desc"))
{
while (dr.Read())
{
ViewapplicationsDTO objViewapplications = new ViewapplicationsDTO();
objViewapplications.pContacttype = dr["contacttype"].ToString();
objViewapplications.pContactreferenceid = dr["contactreferenceid"].ToString();
objViewapplications.pLoantypeid = Convert.ToInt64(dr["loantypeid"]);
objViewapplications.pLoantype = dr["loantype"].ToString();
objViewapplications.pLoanid = Convert.ToInt64(dr["loanid"]);
objViewapplications.pLoanname = dr["loanname"].ToString();
objViewapplications.pApplicanttype = dr["Applicanttype"].ToString();
objViewapplications.pApplicationid = Convert.ToInt64(dr["applicationid"]);
objViewapplications.pVchapplicationid = dr["vchapplicationid"].ToString();
objViewapplications.pApplicantname = dr["applicantname"].ToString();
objViewapplications.pDateofapplication = Convert.ToDateTime(dr["Dateofapplication"]).ToString("dd/MM/yyyy");
objViewapplications.pAproveddate = Convert.ToDateTime(dr["approvaldate"]).ToString("dd/MM/yyyy");
objViewapplications.pPurposeofloan = dr["purposeofloan"].ToString();
objViewapplications.pAmountrequested = Convert.ToDecimal(dr["amountrequested"]);
objViewapplications.pTenureofloan = Convert.ToDecimal(dr["tenureofloan"]);
objViewapplications.pRateofinterest = Convert.ToDecimal(dr["rateofinterest"]);
objViewapplications.pInteresttype = dr["interesttype"].ToString();
objViewapplications.pLoanpayin = dr["loanpayin"].ToString();
objViewapplications.pMobileno = dr["businessentitycontactno"].ToString();
lstViewapplications.Add(objViewapplications);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lstViewapplications);
}
