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); }