public bool Savesanctionletter(SanctionLetter _SanctionLetter, string Connectionstring)
{
bool Issaved = false;
try
{
con = new NpgsqlConnection(Connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
string Strsavesanctionletter = string.Empty;
Strsavesanctionletter = "INSERT INTO tabapplicationsanctionletter(applicationid, vchapplicationid, transdate, sanctionlettersent,sentthrough, applicantname, loanname, loanamount, interesttype,rateofinterest, tenureofloan, tenuretype, downpayment, termsandconditions,statusid, createdby, createddate) VALUES(" + _SanctionLetter.pApplicationId + ", '" + ManageQuote(_SanctionLetter.pVchapplicationID) + "', current_date, 'Y','','" + ManageQuote(_SanctionLetter.pApplicantname) + "', '" + ManageQuote(_SanctionLetter.pLoanname) + "', " + _SanctionLetter.pApprovedloanamount + ", '" + ManageQuote(_SanctionLetter.pInteresttype) + "', " + _SanctionLetter.pInterestRate + ", " + _SanctionLetter.pTenureofloan + ", '" + _SanctionLetter.pLoanpayin + "', " + _SanctionLetter.pDownpayment + ", '', " + Convert.ToInt32(Status.Active) + ", " + _SanctionLetter.pCreatedby + ", current_timestamp); ";
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, Strsavesanctionletter);
trans.Commit();
Issaved = true;
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
public async Task <SanctionLetter> GetSanctionLetterData(string ConnectionString, string VchapplicationID)
{
await Task.Run(() =>
{
try
{
VchapplicationID = ManageQuote(VchapplicationID).Trim().ToUpper();
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select applicationid,applicationdate,vchapplicationid,applicantname,loantype,loanname,loantypeandcode,approveddate,approvedloanamount,tenureofloan,loanpayin,interesttype,installmentamount,rateofinterest,downpayment,titlename from vwsanctionletterloandetails where upper(vchapplicationid)='" + VchapplicationID + "';"))
{
while (dr.Read())
{
_SanctionLetter = new SanctionLetter
{
pApplicationId = Convert.ToInt64(dr["applicationid"]),
pVchapplicationID = Convert.ToString(dr["vchapplicationid"]).Trim().ToUpper(),
pApplicationdate = dr["applicationdate"] == DBNull.Value ? Convert.ToDateTime(null).ToString("dd/MM/yyyy") : Convert.ToDateTime(dr["applicationdate"]).ToString("dd/MM/yyyy"),
pApplicantname = Convert.ToString(dr["applicantname"]),
pLoantype = Convert.ToString(dr["loantype"]).Trim(),
pLoanname = Convert.ToString(dr["loanname"]).Trim(),
pLoantypeandCode = Convert.ToString(dr["loantypeandcode"]),
pApprovedDate = dr["approveddate"] == DBNull.Value ? "" : Convert.ToDateTime(dr["approveddate"]).ToString(),
pApprovedloanamount = dr["approvedloanamount"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["approvedloanamount"]),
pTenureofloan = dr["tenureofloan"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["tenureofloan"]),
pLoanpayin = Convert.ToString(dr["loanpayin"]).Trim().ToUpper(),
pInteresttype = Convert.ToString(dr["interesttype"]),
pInstallmentamount = dr["installmentamount"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["installmentamount"]),
pInterestRate = Convert.ToDecimal(dr["rateofinterest"]),
pDownpayment = Convert.ToDecimal(dr["downpayment"]),
pChargesList = GetSanctionletterCharges(VchapplicationID, ConnectionString),
pCoapplicantslist = GetSanctionLetterCoapplicants(ConnectionString, VchapplicationID),
pGuarantorslist = GetSanctionLetterGuarantors(ConnectionString, VchapplicationID),
pGuardianOrParentlist = GetSanctionLetterGuardianorParents(ConnectionString, VchapplicationID),
pPromoterslist = GetSanctionLetterPromoters(ConnectionString, VchapplicationID),
pPartnerslist = GetSanctionLetterPartners(ConnectionString, VchapplicationID),
pJointOwnersList = GetSanctionLetterJointOwners(ConnectionString, VchapplicationID),
pTitlename = Convert.ToString(dr["titlename"])
};
}
}
}
catch (Exception)
{
throw;
}
});
return(_SanctionLetter);
}
public IActionResult Savesanctionletter(SanctionLetter _SanctionLetter)
{
try
{
if (_SanctionLetterAccess.Savesanctionletter(_SanctionLetter, Con))
{
return(Ok(true));
}
else
{
return(StatusCode(StatusCodes.Status304NotModified));
}
}
catch (Exception ex)
{
return(StatusCode(StatusCodes.Status500InternalServerError));
throw ex;
}
}
public async Task <List <SanctionLetter> > GetSanctionLetterMainData(string ConnectionString, string Letterstatus)
{
_SanctionLetterListDTO = new List <SanctionLetter>();
await Task.Run(() =>
{
try
{
string Strsanctionlettercondition = string.Empty;
if (Letterstatus.Trim().ToUpper() == "N")
{
Strsanctionlettercondition = "not in";
}
else if (Letterstatus.Trim().ToUpper() == "Y")
{
Strsanctionlettercondition = "in";
}
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select tap.vchapplicationid,applicantname,approvedloanamount,businessentitycontactno as contactnumber,businessentityemailid as emailid,ta.applicantid from tabapplication ta join tbltransapprovedapplications tap on ta.applicationid=tap.applicationid join tblmstcontact tc on tc.contactid=ta.applicantid where ta.vchapplicationid in (select vchapplicationid from tbltransapprovedapplications) and tap.vchapplicationid " + Strsanctionlettercondition + " (select vchapplicationid from tabapplicationsanctionletter where statusid = " + Convert.ToInt32(Status.Active) + ") order by ta.applicantid desc; "))
{
while (dr.Read())
{
_SanctionLetter = new SanctionLetter
{
pVchapplicationID = Convert.ToString(dr["vchapplicationid"]).Trim().ToUpper(),
pApplicantname = Convert.ToString(dr["applicantname"]).Trim(),
pApprovedloanamount = dr["approvedloanamount"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["approvedloanamount"]),
pApplicantEmail = Convert.ToString(dr["emailid"]),
pApplicantMobileNo = Convert.ToString(dr["contactnumber"])
};
_SanctionLetterListDTO.Add(_SanctionLetter);
}
}
}
catch (Exception)
{
throw;
}
});
return(_SanctionLetterListDTO);
}
public async Task <IActionResult> GetSanctionLetterData(string VchapplicationID)
{
_SanctionLetterDTO = new SanctionLetter();
try
{
if (!string.IsNullOrEmpty(VchapplicationID))
{
_SanctionLetterDTO = await _SanctionLetterAccess.GetSanctionLetterData(Con, VchapplicationID);
return(_SanctionLetterDTO != null?Ok(_SanctionLetterDTO) : (IActionResult)StatusCode(StatusCodes.Status204NoContent));
}
else
{
return(StatusCode(StatusCodes.Status406NotAcceptable));
}
}
catch (Exception)
{
return(StatusCode(StatusCodes.Status500InternalServerError));
throw;
}
}
