public List <ChargesMasterDTO> GetChargesName(string ConnectionString, string type)
{
lstChargesType = new List <ChargesMasterDTO>();
string strQuery = string.Empty;
try
{
if (type == "All")
{
strQuery = "SELECT * FROM TBLMSTCHARGESTYPES";
}
else
{
strQuery = "SELECT * FROM TBLMSTCHARGESTYPES WHERE STATUSID = 1";
}
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, strQuery))
{
while (dr.Read())
{
ChargesMasterDTO objcharges = new ChargesMasterDTO();
objcharges.pChargename = dr["CHARGENAME"].ToString();
objcharges.pChargeid = Convert.ToInt64(dr["chargeid"]);
lstChargesType.Add(objcharges);
}
}
}
catch (Exception)
{
throw;
}
return(lstChargesType);
}
public void SaveLoanWiseChargeConfig(ChargesMasterDTO _Charges)
{
try
{
objChargemaster.SaveLoanWiseChargeConfig(_Charges, Con);
}
catch (Exception ex)
{
throw new FinstaAppException(ex.ToString());
}
}
public IActionResult UpdateChargesName(ChargesMasterDTO _Charges)
{
bool isSaved = false;
try
{
isSaved = objChargemaster.UpdateChargesName(_Charges, Con);
}
catch (Exception ex)
{
throw new FinstaAppException(ex.ToString());
}
return(Ok(isSaved));
}
public IActionResult SaveLoanWiseChargeConfig(ChargesMasterDTO _Charges)
{
bool isSaved = false;
try
{
isSaved = objChargemaster.SaveLoanWiseChargeConfig(_Charges, Con);
}
catch (Exception ex)
{
throw new FinstaAppException(ex.ToString());
}
return(Ok(isSaved));
}
public bool SaveChargesName(ChargesMasterDTO charges, string ConnectionString)
{
bool isSaved = false;
try
{
NPGSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, "insert into TBLMSTCHARGESTYPES(CHARGENAME,statusid,createdby,createddate)values('" + ManageQuote(charges.pChargename) + "'," + getStatusid(charges.pStatusname, ConnectionString) + "," + charges.pCreatedby + ",current_timestamp);");
isSaved = true;
}
catch (Exception)
{
throw;
}
return(isSaved);
}
public bool DeleteChargesName(ChargesMasterDTO charges, string ConnectionString)
{
bool isSaved = false;
try
{
NPGSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, "update TBLMSTCHARGESTYPES set statusid=" + getStatusid(charges.pStatusname, ConnectionString) + ",modifiedby=" + charges.pCreatedby + ",modifieddate=current_timestamp where chargeid=" + charges.pChargeid + ";");
isSaved = true;
}
catch (Exception ex)
{
throw ex;
}
return(isSaved);
}
public bool SaveLoanWiseChargeConfig(ChargesMasterDTO charges, string ConnectionString)
{
bool isSaved = false;
try
{
NPGSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, "insert into TBLMSTLOANWISECHARGESCONFIG(LOANTYPEID,LOANID,LOANCHARGEID,CHARGENAME,LOANPAYIN,TENUREFROM,TENURETO,ISCHARGEAPPLICABLE,ISTAXAPPLICABLE,ISCHARGERANGEAPPLICABLEON,CHARGECALTYPE,CHARGECALONFIELD,CHARGESVALUE,TAXTYPE,TAXVALUE,MINLOANAMOUNT,MAXLOANAMOUNT,MINCHARGEAMOUNTOFPERCENTAGE,MAXCHARGEAMOUNTOFPERCENTAGE,EFFECTFROMDATE,EFFECTTODATE,ISCHARGEWAIVEDOFF,LOCKINGPERIOD,STATUSID,CREATEDBY,CREATEDDATE)values(" + charges.pLOANTYPEID + "," + charges.pLOANID + "," + charges.pLOANCHARGEID + "," +
"'" + ManageQuote(charges.pCHARGENAME) + "'," + charges.pLOANPAYIN + "," + charges.pTENUREFROM + "," + charges.pTENURETO + "," + charges.pISCHARGEAPPLICABLE + "," + charges.pISTAXAPPLICABLE + "," + charges.pISCHARGERANGEAPPLICABLEON + "," + charges.pCHARGECALTYPE + "," + charges.pCHARGECALONFIELD + "," + charges.pCHARGESVALUE + "," + charges.pTAXTYPE + "," + charges.pMINLOANAMOUNT + "," + charges.pMAXLOANAMOUNT + "," + charges.pMINCHARGEAMOUNTOFPERCENTAGE + "," + charges.pMAXCHARGEAMOUNTOFPERCENTAGE + "," + charges.pEFFECTFROMDATE + "," + charges.pEFFECTTODATE + "," + charges.pISCHARGEWAIVEDOFF + "," + charges.pLOCKINGPERIOD + ",1,1,current_timestamp);");
isSaved = true;
}
catch (Exception)
{
throw;
}
return(isSaved);
}
public bool UpdateChargesName(ChargesMasterDTO Charges, string ConnectionString)
{
bool isSaved = false;
try
{
string loanchargename = Convert.ToString(NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "select chargename from tblmstloanwisechargestypes where chargeid=" + Charges.pChargeid + ";"));
NPGSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, "update tblmstloanwisechargestypes set chargename='" + ManageQuote(Charges.pChargename) + "' where chargeid=" + Charges.pChargeid + ";Update TBLMSTCHARGESTYPES Set CHARGENAME='" + ManageQuote(Charges.pChargename) + "' where CHARGEID=" + Charges.pChargeid + ";update tblmstloanwisechargesconfig set chargename='" + ManageQuote(Charges.pChargename) + "' where chargename='" + loanchargename + "';");
isSaved = true;
}
catch (Exception)
{
throw;
}
return(isSaved);
}
//Loan Wise Charges Assgining
#region SaveLoanWiseChargesName
public bool SaveLoanWiseChargesName(ChargesMasterDTO LoanWiseCharges, string ConnectionString)
{
bool isSaved = false;
StringBuilder sb = new StringBuilder();
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
try
{
for (var i = 0; i < LoanWiseCharges.pLoanchargetypes.Count; i++)
{
sb.Append("insert into TBLMSTLOANWISECHARGESTYPES(LOANTYPEID,LOANID,CHARGEID,CHARGENAME,LEDGERNAME,PARENTGROUPLEDGERNAME,PARENTGROUPLEDGERID,STATUSID,CREATEDBY,CREATEDDATE) values(" + LoanWiseCharges.pLoanchargetypes[i].pLoantypeid + "," + LoanWiseCharges.pLoanchargetypes[i].pLoanid + "," + LoanWiseCharges.pLoanchargetypes[i].pChargeid + ",'" + ManageQuote(LoanWiseCharges.pLoanchargetypes[i].pChargename) + "','" + ManageQuote(LoanWiseCharges.pLoanchargetypes[i].pLedgername) + "','" + ManageQuote(LoanWiseCharges.pLoanchargetypes[i].pParentgroupledgername) + "','" + ManageQuote(LoanWiseCharges.pLoanchargetypes[i].pParentgroupledgerid) + "'," + getStatusid(LoanWiseCharges.pStatusname, ConnectionString) + ", " + LoanWiseCharges.pCreatedby + ", current_timestamp)");
}
if (Convert.ToString(sb) != string.Empty)
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sb.ToString());
}
trans.Commit();
isSaved = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(isSaved);
}
public bool SaveLoanWiseChargeTypes(ChargesMasterDTO charges, string ConnectionString)
{
bool isSaved = false;
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
for (var i = 0; i < charges.pLoanchargetypes.Count; i++)
{
NPGSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, "insert into TBLMSTLOANWISECHARGESTYPES(LOANTYPEID,LOANID,CHARGEID,CHARGENAME,LEDGERNAME,PARENTGROUPLEDGERNAME,PARENTGROUPLEDGERID,STATUSID,CREATEDBY,CREATEDDATE)values(" + charges.pLoanchargetypes[i].pLOANTYPEID + "," + charges.pLoanchargetypes[i].pLOANTYPEID + "," + charges.pLoanchargetypes[i].pCHARGEID + ",'" + ManageQuote(charges.pLoanchargetypes[i].pCHARGENAME) + "','" + ManageQuote(charges.pLoanchargetypes[i].pLEDGERNAME) + "','" + ManageQuote(charges.pLoanchargetypes[i].pPARENTGROUPLEDGERNAME) + "','" + ManageQuote(charges.pLoanchargetypes[i].pPARENTGROUPLEDGERID) + "',1,1,current_timestamp);");
}
isSaved = true;
}
catch (Exception)
{
throw;
}
return(isSaved);
}
public bool UpdateLoanWiseChargeConfig(ChargesMasterDTO Charges, string ConnectionString)
{
bool isSaved = false;
StringBuilder sb = new StringBuilder();
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
try
{
if (Charges.pLoanchargetypesConfig != null)
{
for (var i = 0; i < Charges.pLoanchargetypesConfig.Count; i++)
{
if (string.IsNullOrEmpty(Charges.pLoanchargetypesConfig[i].pChargeEffectFrom))
{
Charges.pLoanchargetypesConfig[i].pChargeEffectFrom = "null";
}
else
{
Charges.pLoanchargetypesConfig[i].pChargeEffectFrom = "'" + FormatDate(Charges.pLoanchargetypesConfig[i].pChargeEffectFrom) + "'";
}
if (string.IsNullOrEmpty(Charges.pLoanchargetypesConfig[i].pChargeEffectTo))
{
Charges.pLoanchargetypesConfig[i].pChargeEffectTo = "null";
}
else
{
Charges.pLoanchargetypesConfig[i].pChargeEffectTo = "'" + FormatDate(Charges.pLoanchargetypesConfig[i].pChargeEffectTo) + "'";
}
if (Charges.pLoanchargetypesConfig[i].ptypeofoperation.ToUpper() == "UPDATE")
{
sb.Append("Update TBLMSTLOANWISECHARGESCONFIG Set loanpayin='" + ManageQuote(Charges.pLoanchargetypesConfig[i].pLoanpayin) + "',ischargeapplicableonloanrange='" + ManageQuote(Charges.pLoanchargetypesConfig[i].pIsChargedependentOnLoanRange) + "',chargevaluefixedorpercentage='" + ManageQuote(Charges.pLoanchargetypesConfig[i].pChargevaluefixedorpercentage) + "',applicanttype='" + ManageQuote(Charges.pLoanchargetypesConfig[i].pApplicanttype) + "',ischargerangeapplicableonvalueortenure='" + ManageQuote(Charges.pLoanchargetypesConfig[i].pIschargerangeapplicableonvalueortenure) + "',minloanamountortenure=" + Charges.pLoanchargetypesConfig[i].pMinLoanAmountorTenure + ",maxloanamountortenure= " + Charges.pLoanchargetypesConfig[i].pMaxLoanAmountorTenure + ",chargepercentage= " + Charges.pLoanchargetypesConfig[i].pChargePercentage + ",minchargesvalue=" + Charges.pLoanchargetypesConfig[i].pMinchargesvalue + ",maxchargesvalue=" + Charges.pLoanchargetypesConfig[i].pMaxchargesvalue + ",chargecalonfield='" + ManageQuote(Charges.pLoanchargetypesConfig[i].pChargecalonfield) + "',gsttype='" + ManageQuote(Charges.pLoanchargetypesConfig[i].pGsttype) + "',gstvalue=" + Charges.pLoanchargetypesConfig[i].pGstvalue + ",effectfromdate= " + (Charges.pLoanchargetypesConfig[i].pChargeEffectFrom) + ",effecttodate=" + (Charges.pLoanchargetypesConfig[i].pChargeEffectTo) + ",ischargewaivedoff='" + ManageQuote(Charges.pLoanchargetypesConfig[i].pIschargewaivedoff) + "',lockingperiod=" + Charges.pLoanchargetypesConfig[i].pLockingperiod + " where loanid=" + Charges.pLoanchargetypesConfig[i].pLoanid + " and RECORDID=" + Charges.pLoanchargetypesConfig[i].precordid + ";");
}
else if (Charges.pLoanchargetypesConfig[i].ptypeofoperation == "CREATE")
{
sb.Append("insert into tblmstloanwisechargesconfig(loantypeid,loanid,loanchargeid,chargename,loanpayin,ischargeapplicableonloanrange,chargevaluefixedorpercentage,applicanttype,ischargerangeapplicableonvalueortenure,minloanamountortenure,maxloanamountortenure,chargepercentage,minchargesvalue,maxchargesvalue,chargecalonfield,gsttype,gstvalue,effectfromdate,effecttodate,ischargewaivedoff,lockingperiod,statusid,createdby,createddate)values(" + Charges.pLoanchargetypesConfig[i].pLoantypeid + "," + Charges.pLoanchargetypesConfig[i].pLoanid + "," + Charges.pLoanchargetypesConfig[i].pLoanChargeid + ",'" + ManageQuote(Charges.pLoanchargetypesConfig[i].pChargename) + "','" + ManageQuote(Charges.pLoanchargetypesConfig[i].pLoanpayin) + "','" + ManageQuote(Charges.pLoanchargetypesConfig[i].pIsChargedependentOnLoanRange) + "','" + ManageQuote(Charges.pLoanchargetypesConfig[i].pChargevaluefixedorpercentage) + "','" + ManageQuote(Charges.pLoanchargetypesConfig[i].pApplicanttype) + "','" + ManageQuote(Charges.pLoanchargetypesConfig[i].pIschargerangeapplicableonvalueortenure) + "'," + Charges.pLoanchargetypesConfig[i].pMinLoanAmountorTenure + "," + Charges.pLoanchargetypesConfig[i].pMaxLoanAmountorTenure + "," + Charges.pLoanchargetypesConfig[i].pChargePercentage + "," + Charges.pLoanchargetypesConfig[i].pMinchargesvalue + "," + Charges.pLoanchargetypesConfig[i].pMaxchargesvalue + ",'" + ManageQuote(Charges.pLoanchargetypesConfig[i].pChargecalonfield) + "','" + ManageQuote(Charges.pLoanchargetypesConfig[i].pGsttype) + "'," + Charges.pLoanchargetypesConfig[i].pGstvalue + "," + Charges.pLoanchargetypesConfig[i].pChargeEffectFrom + "," + Charges.pLoanchargetypesConfig[i].pChargeEffectTo + ",'" + ManageQuote(Charges.pLoanchargetypesConfig[i].pIschargewaivedoff) + "'," + Charges.pLoanchargetypesConfig[i].pLockingperiod + ",1," + Charges.pCreatedby + ",current_timestamp);");
}
}
}
if (!string.IsNullOrEmpty(sb.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sb.ToString());
}
trans.Commit();
isSaved = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(isSaved);
}
//Charges Configaration
#region SaveLoanWiseChargesConfig
public bool SaveLoanWiseChargeConfig(ChargesMasterDTO charges, string ConnectionString)
{
bool isSaved = false;
StringBuilder sb = new StringBuilder();
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
try
{
if (charges.pLoanchargetypesConfig != null)
{
for (var i = 0; i < charges.pLoanchargetypesConfig.Count; i++)
{
// FormatDate(charges.pLoanchargetypesConfig[i].pChargeEffectFrom) + "','" + FormatDate(charges.pLoanchargetypesConfig[i].pChargeEffectTo)
if (string.IsNullOrEmpty(charges.pLoanchargetypesConfig[i].pChargeEffectFrom))
{
charges.pLoanchargetypesConfig[i].pChargeEffectFrom = "null";
}
else
{
charges.pLoanchargetypesConfig[i].pChargeEffectFrom = "'" + FormatDate(charges.pLoanchargetypesConfig[i].pChargeEffectFrom) + "'";
}
if (string.IsNullOrEmpty(charges.pLoanchargetypesConfig[i].pChargeEffectTo))
{
charges.pLoanchargetypesConfig[i].pChargeEffectTo = "null";
}
else
{
charges.pLoanchargetypesConfig[i].pChargeEffectTo = "'" + FormatDate(charges.pLoanchargetypesConfig[i].pChargeEffectTo) + "'";
}
sb.Append("insert into tblmstloanwisechargesconfig(loantypeid,loanid,loanchargeid,chargename,loanpayin,ischargeapplicableonloanrange,chargevaluefixedorpercentage,applicanttype,ischargerangeapplicableonvalueortenure,minloanamountortenure,maxloanamountortenure,chargepercentage,minchargesvalue,maxchargesvalue,chargecalonfield,gsttype,gstvalue,effectfromdate,effecttodate,ischargewaivedoff,lockingperiod,statusid,createdby,createddate)values(" + charges.pLoanchargetypesConfig[i].pLoantypeid + "," + charges.pLoanchargetypesConfig[i].pLoanid + "," + charges.pLoanchargetypesConfig[i].pLoanChargeid + ",'" + ManageQuote(charges.pLoanchargetypesConfig[i].pChargename) + "','" + ManageQuote(charges.pLoanchargetypesConfig[i].pLoanpayin) + "','" + ManageQuote(charges.pLoanchargetypesConfig[i].pIsChargedependentOnLoanRange) + "','" + ManageQuote(charges.pLoanchargetypesConfig[i].pChargevaluefixedorpercentage) + "','" + ManageQuote(charges.pLoanchargetypesConfig[i].pApplicanttype) + "','" + ManageQuote(charges.pLoanchargetypesConfig[i].pIschargerangeapplicableonvalueortenure) + "'," + charges.pLoanchargetypesConfig[i].pMinLoanAmountorTenure + "," + charges.pLoanchargetypesConfig[i].pMaxLoanAmountorTenure + "," + charges.pLoanchargetypesConfig[i].pChargePercentage + "," + charges.pLoanchargetypesConfig[i].pMinchargesvalue + "," + charges.pLoanchargetypesConfig[i].pMaxchargesvalue + ",'" + ManageQuote(charges.pLoanchargetypesConfig[i].pChargecalonfield) + "','" + ManageQuote(charges.pLoanchargetypesConfig[i].pGsttype) + "'," + charges.pLoanchargetypesConfig[i].pGstvalue + "," + charges.pLoanchargetypesConfig[i].pChargeEffectFrom + "," + charges.pLoanchargetypesConfig[i].pChargeEffectTo + ",'" + ManageQuote(charges.pLoanchargetypesConfig[i].pIschargewaivedoff) + "'," + charges.pLoanchargetypesConfig[i].pLockingperiod + ",1," + charges.pCreatedby + ",current_timestamp);");
}
}
//if (Convert.ToString(sb) != string.Empty)
//{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sb.ToString());
// }
trans.Commit();
isSaved = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(isSaved);
}
