public ReferralSave getReferralDetails(string ConnectionString, Int64 SavingAccountId)
{
ReferralSave _ReferralSave = new ReferralSave();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select recordid,savingaccountid,savingaccountno,referralid,referralname,salespersonname from tbltranssavingaccountreferraldetails where savingaccountid=" + SavingAccountId + " and statusid =" + Convert.ToInt32(Status.Active) + ""))
{
if (dr.Read())
{
_ReferralSave = new ReferralSave()
{
pRecordid = Convert.ToInt64(dr["recordid"]),
pSavingaccountid = Convert.ToInt64(dr["savingaccountid"]),
pSavingaccountno = Convert.ToString(dr["savingaccountno"]),
pReferralid = Convert.ToInt64(dr["referralid"]),
pReferralname = Convert.ToString(dr["referralname"]),
pSalespersonname = Convert.ToString(dr["salespersonname"]),
ptypeofoperation = Convert.ToString("UPDATE")
};
}
}
}
catch (Exception ex)
{
throw ex;
}
return(_ReferralSave);
}
public bool SaveReferral(ReferralSave _ReferralSave, string ConnectionString)
{
bool IsSaved = false;
StringBuilder sbSaveReferral = new StringBuilder();
StringBuilder sbupdate = new StringBuilder();
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
sbSaveReferral.Append("update tbltranssavingaccountcreation set isreferralapplicable=" + _ReferralSave.pIsreferralapplicable + " where savingaccountid=" + _ReferralSave.pSavingaccountid + ";");
if (!string.IsNullOrEmpty(_ReferralSave.ptypeofoperation))
{
int count = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "select count(*) from tbltranssavingaccountreferraldetails where savingaccountid=" + _ReferralSave.pSavingaccountid + " and statusid =" + Convert.ToInt32(Status.Active) + ""));
if (count == 0)
{
if (_ReferralSave.pIsreferralapplicable == true)
{
sbSaveReferral.Append("INSERT INTO tbltranssavingaccountreferraldetails(savingaccountid , savingaccountno, referralid , referralname ,salespersonname ,statusid, createdby, createddate) VALUES (" + _ReferralSave.pSavingaccountid + ",'" + ManageQuote(_ReferralSave.pSavingaccountno) + "'," + _ReferralSave.pReferralid + ",'" + ManageQuote(_ReferralSave.pReferralname) + "','" + ManageQuote(_ReferralSave.pSalespersonname) + "'," + Convert.ToInt32(Status.Active) + ", '" + _ReferralSave.pCreatedby + "', current_timestamp);");
}
}
else if (ManageQuote(_ReferralSave.ptypeofoperation).ToUpper() == "UPDATE")
{
if (_ReferralSave.pIsreferralapplicable == true)
{
sbSaveReferral.AppendLine("UPDATE tbltranssavingaccountreferraldetails SET referralid=" + _ReferralSave.pReferralid + ",referralname='" + ManageQuote(_ReferralSave.pReferralname) + "',salespersonname='" + ManageQuote(_ReferralSave.pSalespersonname) + "' WHERE savingaccountid=" + _ReferralSave.pSavingaccountid + ";");
}
else
{
sbSaveReferral.AppendLine("UPDATE tbltranssavingaccountreferraldetails SET statusid=" + Convert.ToInt32(Status.Inactive) + " WHERE savingaccountid=" + _ReferralSave.pSavingaccountid + ";");
}
}
}
string Recordid = string.Empty;
if (!string.IsNullOrEmpty(sbSaveReferral.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbSaveReferral.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);
}
