public ReferralCommissionDTO getReferralcommissionDetails(string ConnectionString, Int64 SavingAccountId)
{
ReferralCommissionDTO objReferralCommission = new ReferralCommissionDTO();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select ts.Savingconfigid,isreferralcommissionapplicable,referralcommissioncalfield,referralcommissiontype,commissionValue,istdsapplicable,tdsaccountid,tdssection,tdspercentage from tblmstSavingAccountConfigreferraldetails ts join tblmstSavingAccountConfig tt on ts.Savingconfigid=tt.Savingconfigid where ts.Savingconfigid=" + SavingAccountId + " and ts.statusid=1"))
{
if (dr.Read())
{
objReferralCommission = new ReferralCommissionDTO()
{
pSavingConfigid = Convert.ToInt64(dr["Savingconfigid"]),
pIsreferralcommissionapplicable = dr["isreferralcommissionapplicable"] == DBNull.Value ?false: Convert.ToBoolean(dr["isreferralcommissionapplicable"]),
pReferralcommissioncalfield = Convert.ToString(dr["referralcommissioncalfield"]),
pReferralcommissiontype = Convert.ToString(dr["referralcommissiontype"]),
pCommissionValue = Convert.ToDecimal(dr["commissionValue"]),
pIstdsapplicable = Convert.ToBoolean(dr["istdsapplicable"]),
pTdsaccountid = Convert.ToString(dr["tdsaccountid"]),
ptdssection = Convert.ToString(dr["tdssection"]),
pTdspercentage = Convert.ToDecimal(dr["tdspercentage"])
};
}
}
}
catch (Exception ex)
{
throw ex;
}
return(objReferralCommission);
}
public bool SaveReferralCommission(ReferralCommissionDTO objReferralCommission, string ConnectionString)
{
bool isSaved = false;
StringBuilder sbinsert = new StringBuilder();
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (string.IsNullOrEmpty(objReferralCommission.pCommissionValue.ToString()))
{
objReferralCommission.pCommissionValue = 0;
}
if (string.IsNullOrEmpty(objReferralCommission.pTdspercentage.ToString()))
{
objReferralCommission.pTdspercentage = 0;
}
int count = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "select count(*) from tblmstSavingAccountConfigreferraldetails where savingconfigid=" + objReferralCommission.pSavingConfigid + " and statusid=" + Convert.ToInt32(Status.Active) + ""));
if (count != 0)
{
sbinsert.Append("Update tblmstSavingAccountConfigreferraldetails set isreferralcommissionapplicable=" + objReferralCommission.pIsreferralcommissionapplicable + ", referralcommissioncalfield='" + ManageQuote(objReferralCommission.pReferralcommissioncalfield) + "',referralcommissiontype='" + objReferralCommission.pReferralcommissiontype + "' ,commissionValue=" + objReferralCommission.pCommissionValue + ",istdsapplicable=" + objReferralCommission.pIstdsapplicable + ",tdsaccountid='" + ManageQuote(objReferralCommission.pTdsaccountid) + "',tdssection='" + ManageQuote(objReferralCommission.ptdssection) + "',tdspercentage=" + objReferralCommission.pTdspercentage + ",modifiedby =" + objReferralCommission.pCreatedby + ",modifieddate=current_timestamp where savingconfigid =" + objReferralCommission.pSavingConfigid + ";");
}
if (count == 0)
{
sbinsert.Append("insert into tblmstSavingAccountConfigreferraldetails(savingconfigid ,savingaccname,isreferralcommissionapplicable,referralcommissioncalfield,referralcommissiontype ,commissionValue,istdsapplicable,tdsaccountid,tdssection,tdspercentage,statusid,createdby,createddate) values(" + objReferralCommission.pSavingConfigid + ",'" + objReferralCommission.pSavingAccname + "'," + objReferralCommission.pIsreferralcommissionapplicable + ",'" + ManageQuote(objReferralCommission.pReferralcommissioncalfield) + "','" + objReferralCommission.pReferralcommissiontype + "'," + objReferralCommission.pCommissionValue + "," + objReferralCommission.pIstdsapplicable + ",'" + ManageQuote(objReferralCommission.pTdsaccountid) + "','" + ManageQuote(objReferralCommission.ptdssection) + "'," + objReferralCommission.pTdspercentage + "," + Convert.ToInt32(Status.Active) + "," + objReferralCommission.pCreatedby + ",current_timestamp);");
}
if (!string.IsNullOrEmpty(sbinsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbinsert.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 IActionResult SaveReferralCommission(ReferralCommissionDTO ReferralCommissionlist)
{
bool isSaved = false;
try
{
isSaved = objSavingAccountConfig.SaveReferralCommission(ReferralCommissionlist, Con);
}
catch (Exception ex)
{
throw new FinstaAppException(ex.ToString());
}
return(Ok(isSaved));
}
