public List <LoanchargetypesConfigDTO> GetLoanWiseLoanPayin(string ConnectionString, long loanid, string applicanttype)
{
lstLoanchargesConfig = new List <LoanchargetypesConfigDTO>();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select distinct loanpayin from tblmstloanconfiguration where Loanid=" + loanid + " and applicanttype='" + applicanttype + "'"))
{
while (dr.Read())
{
LoanchargetypesConfigDTO objloanchargesConfig = new LoanchargetypesConfigDTO();
objloanchargesConfig.pLoanpayin = dr["loanpayin"].ToString();
lstLoanchargesConfig.Add(objloanchargesConfig);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(lstLoanchargesConfig);
}
public List <LoanchargetypesConfigDTO> GetLoanWiseChargeConfig(string ConnectionString, Int64 loanChargeid)
{
lstLoanchargesConfig = new List <LoanchargetypesConfigDTO>();
try
{
dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "SELECT TBL.RECORDID,TBL.LOANTYPEID,TLP.LOANTYPE,TBL.LOANID,TML.LOANNAME,TBL.LOANCHARGEID,TBL.CHARGENAME,TBL.LOANPAYIN, case when TBL.ISCHARGEAPPLICABLEONLOANRANGE ='Y' then 'YES' else 'NO' end as ISCHARGEAPPLICABLEONLOANRANGE,TBL.CHARGEVALUEFIXEDORPERCENTAGE,TBL.APPLICANTTYPE,TBL.ISCHARGERANGEAPPLICABLEONVALUEORTENURE,TBL.MINLOANAMOUNTORTENURE,TBL.MAXLOANAMOUNTORTENURE,TBL.CHARGEPERCENTAGE,TBL.MINCHARGESVALUE,TBL.MAXCHARGESVALUE,TBL.CHARGECALONFIELD,TBL.GSTTYPE,TBL.GSTVALUE,to_char(TBL.EFFECTFROMDATE,'dd/MM/yyyy') EFFECTFROMDATE, to_char(TBL.EFFECTTODATE,'dd/MM/yyyy') EFFECTTODATE,TBL.ISCHARGEWAIVEDOFF,TBL.LOCKINGPERIOD FROM TBLMSTLOANWISECHARGESCONFIG TBL JOIN TBLMSTLOANTYPES TLP ON TBL.LOANTYPEID = TLP.LOANTYPEID JOIN TBLMSTLOANS TML ON TBL.LOANID = TML.LOANID join TBLMSTSTATUS ts on ts.statusid = tbl.statusid where TBL.LOANCHARGEID=" + loanChargeid + " and statusname='Active' order by CHARGENAME");
while (dr.Read())
{
LoanchargetypesConfigDTO objloanchargesConfig = new LoanchargetypesConfigDTO();
objloanchargesConfig.precordid = Convert.ToInt64(dr["RECORDID"]);
objloanchargesConfig.pLoantypeid = Convert.ToInt64(dr["loantypeid"]);
objloanchargesConfig.pLoantype = dr["LOANTYPE"].ToString();
objloanchargesConfig.pLoanid = Convert.ToInt64(dr["Loanid"]);
objloanchargesConfig.pLoanNmae = dr["LOANNAME"].ToString();
objloanchargesConfig.pLoanChargeid = Convert.ToInt64(dr["loanchargeid"]);
objloanchargesConfig.pChargename = Convert.ToString(dr["CHARGENAME"]);
objloanchargesConfig.pLoanpayin = dr["loanpayin"].ToString();
objloanchargesConfig.pIsChargedependentOnLoanRange = dr["ischargeapplicableonloanrange"].ToString();
objloanchargesConfig.pChargevaluefixedorpercentage = dr["chargevaluefixedorpercentage"].ToString();
objloanchargesConfig.pApplicanttype = dr["applicanttype"].ToString();
objloanchargesConfig.pIschargerangeapplicableonvalueortenure = dr["ischargerangeapplicableonvalueortenure"].ToString();
objloanchargesConfig.pMinLoanAmountorTenure = Convert.ToInt64(dr["minloanamountortenure"]);
objloanchargesConfig.pMaxLoanAmountorTenure = Convert.ToInt64(dr["maxloanamountortenure"]);
objloanchargesConfig.pChargePercentage = Convert.ToDecimal(dr["chargepercentage"]);
objloanchargesConfig.pMinchargesvalue = Convert.ToInt64(dr["minchargesvalue"]);
objloanchargesConfig.pMaxchargesvalue = Convert.ToInt64(dr["maxchargesvalue"]);
objloanchargesConfig.pChargecalonfield = dr["chargecalonfield"].ToString();
objloanchargesConfig.pGsttype = dr["gsttype"].ToString();
objloanchargesConfig.pGstvalue = Convert.ToInt64(dr["gstvalue"]);
objloanchargesConfig.pChargeEffectFrom = dr["effectfromdate"].ToString();
objloanchargesConfig.pChargeEffectTo = Convert.ToString(dr["effecttodate"]);
if (string.IsNullOrEmpty(objloanchargesConfig.pChargeEffectTo))
{
objloanchargesConfig.peditstatus = "YES";
}
else
{
objloanchargesConfig.peditstatus = "NO";
}
objloanchargesConfig.pIschargewaivedoff = dr["ischargewaivedoff"].ToString();
objloanchargesConfig.pLockingperiod = Convert.ToInt64(dr["lockingperiod"]);
objloanchargesConfig.ptypeofoperation = "OLD";
lstLoanchargesConfig.Add(objloanchargesConfig);
}
}
catch (Exception)
{
throw;
}
return(lstLoanchargesConfig);
}
public IActionResult DeleteLoanWiseChargeConfig(LoanchargetypesConfigDTO _Charges)
{
bool isSaved = false;
try
{
isSaved = objChargemaster.DeleteLoanWiseChargeConfig(_Charges, Con);
}
catch (Exception ex)
{
throw new FinstaAppException(ex.ToString());
}
return(Ok(isSaved));
}
public List <LoanchargetypesConfigDTO> GetLoanWiseApplicantTypes(string ConnectionString, Int64 loanid)
{
lstLoanchargesConfig = new List <LoanchargetypesConfigDTO>();
try
{
dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select distinct applicanttype from tblmstloanconfiguration where Loanid=" + loanid + "");
while (dr.Read())
{
LoanchargetypesConfigDTO objloanchargesConfig = new LoanchargetypesConfigDTO();
objloanchargesConfig.pApplicanttype = dr["applicanttype"].ToString();
lstLoanchargesConfig.Add(objloanchargesConfig);
}
}
catch (Exception ex)
{
throw ex;
}
return(lstLoanchargesConfig);
}
public List <LoanchargetypesConfigDTO> ViewLoanWiseChargeConfig(string ConnectionString)
{
lstLoanchargesConfig = new List <LoanchargetypesConfigDTO>();
try
{
dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "SELECT TBL.LOANTYPEID,TLP.LOANTYPE,TBL.LOANID,TML.LOANNAME,TBL.LOANCHARGEID,TBL.CHARGENAME,TBL.LOANPAYIN,TBL.ISCHARGEAPPLICABLEONLOANRANGE,TBL.CHARGEVALUEFIXEDORPERCENTAGE,TBL.APPLICANTTYPE,TBL.ISCHARGERANGEAPPLICABLEONVALUEORTENURE,TBL.MINLOANAMOUNTORTENURE,TBL.MAXLOANAMOUNTORTENURE,TBL.CHARGEPERCENTAGE,TBL.MINCHARGESVALUE,TBL.MAXCHARGESVALUE,TBL.CHARGECALONFIELD,TBL.GSTTYPE,TBL.GSTVALUE,TBL.EFFECTFROMDATE,TBL.EFFECTTODATE,TBL.ISCHARGEWAIVEDOFF,TBL.LOCKINGPERIOD FROM TBLMSTLOANWISECHARGESCONFIG TBL JOIN TBLMSTLOANTYPES TLP ON TBL.LOANTYPEID = TLP.LOANTYPEID JOIN TBLMSTLOANS TML ON TBL.LOANID = TML.LOANID order by CHARGENAME");
while (dr.Read())
{
LoanchargetypesConfigDTO objloanchargesConfig = new LoanchargetypesConfigDTO();
objloanchargesConfig.pLoantypeid = Convert.ToInt16(dr["loantypeid"]);
objloanchargesConfig.pLoantype = dr["LOANTYPE"].ToString();
objloanchargesConfig.pLoanid = Convert.ToInt16(dr["Loanid"]);
objloanchargesConfig.pLoanNmae = dr["LOANNAME"].ToString();
objloanchargesConfig.pLoanChargeid = Convert.ToInt16(dr["loanchargeid"]);
objloanchargesConfig.pLoanpayin = dr["loanpayin"].ToString();
objloanchargesConfig.pIsChargedependentOnLoanRange = dr["ischargeapplicableonloanrange"].ToString();
objloanchargesConfig.pChargevaluefixedorpercentage = dr["chargevaluefixedorpercentage"].ToString();
objloanchargesConfig.pApplicanttype = dr["applicanttype"].ToString();
objloanchargesConfig.pIschargerangeapplicableonvalueortenure = dr["ischargerangeapplicableonvalueortenure"].ToString();
objloanchargesConfig.pMinLoanAmountorTenure = Convert.ToInt16(dr["minloanamountortenure"]);
objloanchargesConfig.pMaxLoanAmountorTenure = Convert.ToInt16(dr["maxloanamountortenure"]);
objloanchargesConfig.pChargePercentage = Convert.ToInt16(dr["chargepercentage"]);
objloanchargesConfig.pMinchargesvalue = Convert.ToInt16(dr["minchargesvalue"]);
objloanchargesConfig.pMaxchargesvalue = Convert.ToInt16(dr["maxchargesvalue"]);
objloanchargesConfig.pChargecalonfield = dr["chargecalonfield"].ToString();
objloanchargesConfig.pGsttype = dr["gsttype"].ToString();
objloanchargesConfig.pGstvalue = Convert.ToInt16(dr["gstvalue"]);
objloanchargesConfig.pChargeEffectFrom = dr["effectfromdate"].ToString();
objloanchargesConfig.pChargeEffectTo = dr["effecttodate"].ToString();
objloanchargesConfig.pIschargewaivedoff = dr["ischargewaivedoff"].ToString();
objloanchargesConfig.pLockingperiod = Convert.ToInt16(dr["lockingperiod"]);
lstLoanchargesConfig.Add(objloanchargesConfig);
}
}
catch (Exception)
{
throw;
}
return(lstLoanchargesConfig);
}
public bool DeleteLoanWiseChargeConfig(LoanchargetypesConfigDTO Charges, string ConnectionString)
{
bool isSaved = false;
try
{
string query = "";
if (Charges.precordid == 0)
{
if (Charges.pStatusname.ToUpper() == "IN-ACTIVE")
{
query = "Update tblmstloanwisechargestypes Set statusid=" + getStatusid(Charges.pStatusname, ConnectionString) + ",modifiedby=" + Charges.pCreatedby + ",modifieddate=current_timestamp where loanid=" + Charges.pLoanid + " and loanchargeid=" + Charges.pLoanChargeid + "; Update TBLMSTLOANWISECHARGESCONFIG Set statusid=" + getStatusid(Charges.pStatusname, ConnectionString) + ",modifiedby=" + Charges.pCreatedby + ",modifieddate=current_timestamp where loanid=" + Charges.pLoanid + " and loanchargeid=" + Charges.pLoanChargeid + ";";
}
else
{
query = "Update tblmstloanwisechargestypes Set statusid=" + getStatusid(Charges.pStatusname, ConnectionString) + ",modifiedby=" + Charges.pCreatedby + ",modifieddate=current_timestamp where loanid=" + Charges.pLoanid + " and loanchargeid=" + Charges.pLoanChargeid + "; ";
}
}
else
{
query = "Update TBLMSTLOANWISECHARGESCONFIG Set effecttodate =current_date, statusid = " + getStatusid(Charges.pStatusname, ConnectionString) + ",modifiedby = " + Charges.pCreatedby + ",modifieddate = current_timestamp where loanid = " + Charges.pLoanid + " and loanchargeid = " + Charges.pLoanChargeid + " and recordid = " + Charges.precordid + "; ";
}
if (query != "")
{
NPGSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, query);
isSaved = true;
}
}
catch (Exception ex)
{
throw ex;
}
return(isSaved);
}