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