public InsurenceConfigDTO GetInsurenceConfigurationDetails(string InsurenceName, string InsurenceNameCode, string ConnectionString)
{
InsurenceConfigDTO InsurenceConfigDetails = new InsurenceConfigDTO();
InsurenceConfigDetails.lstInsurenceConfigartionDetails = new List <InsurenceConfigartionDetailsDTO>();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select insuranceschemeconfigid,insuranceschemename,insuranceschemecode,companycode,branchcode,series,serieslength,insuranceschemenamecode,statusid from tblmstinsuranceconfig where upper(insuranceschemename) = '" + ManageQuote(InsurenceName.ToUpper()) + "' and upper(insuranceschemenamecode) ='" + ManageQuote(InsurenceNameCode.ToUpper()) + "' and statusid = " + Convert.ToInt32(Status.Active) + ";"))
{
while (dr.Read())
{
InsurenceConfigDetails.pInsurenceconfigid = Convert.ToInt64(dr["insuranceschemeconfigid"]);
InsurenceConfigDetails.pInsurencename = dr["insuranceschemename"].ToString();
InsurenceConfigDetails.pInsurencenamecode = dr["insuranceschemenamecode"].ToString();
}
}
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select recordid,insuranceschemeconfigid,insuranceschemename,membertypeid,membertype,applicanttype,agefrom,ageto,insuranceduration,premiumamountpayable,premiumpayin,insuranceclaimpayoutevent,insuranceclaimamount,premiumrefund from tblmstinsuranceconfigdetails where insuranceschemeconfigid=" + InsurenceConfigDetails.pInsurenceconfigid + " and upper(insuranceschemename)='" + ManageQuote(InsurenceName.ToUpper()) + "' and statusid = " + Convert.ToInt32(Status.Active) + ";"))
{
while (dr.Read())
{
InsurenceConfigDetails.lstInsurenceConfigartionDetails.Add(new InsurenceConfigartionDetailsDTO
{
precordid = Convert.ToInt64(dr["recordid"]),
pMembertypeid = Convert.ToInt64(dr["membertypeid"]),
pMembertype = dr["membertype"].ToString(),
pApplicanttype = dr["applicanttype"].ToString(),
pAgefrom = Convert.ToInt64(dr["agefrom"]),
pAgeto = Convert.ToInt64(dr["ageto"]),
pInsuranceduration = dr["insuranceduration"].ToString(),
pPremiumamountpayable = Convert.ToDecimal(dr["premiumamountpayable"]),
pPremiumpayin = dr["premiumpayin"].ToString(),
pInsuranceclaimpayoutevent = dr["insuranceclaimpayoutevent"].ToString(),
pInsuranceclaimamount = Convert.ToDecimal(dr["insuranceclaimamount"]),
pPremiumrefund = Convert.ToBoolean(dr["premiumrefund"]),
pTypeofOperation = "OLD"
});
}
}
}
catch (Exception ex)
{
throw ex;
}
return(InsurenceConfigDetails);
}
public IActionResult GetInsurenceConfigurationDetails(string InsurenceName, string InsurenceNameCode)
{
InsurenceConfigDTO objInsurenceConfigdetails = new InsurenceConfigDTO();
try
{
objInsurenceConfigdetails = objInsurenceconfig.GetInsurenceConfigurationDetails(InsurenceName, InsurenceNameCode, Con);
return(objInsurenceConfigdetails != null?Ok(objInsurenceConfigdetails) : (IActionResult)StatusCode(StatusCodes.Status204NoContent));
}
catch (Exception)
{
return(StatusCode(StatusCodes.Status500InternalServerError));
throw;
}
}
public IActionResult SaveInsurenceConfigDetails([FromBody] InsurenceConfigDTO InsurenceConfigDetails)
{
try
{
if (objInsurenceconfig.SaveInsurenceConfigDetails(InsurenceConfigDetails, Con))
{
return(Ok(true));
}
else
{
return(StatusCode(StatusCodes.Status304NotModified));
}
}
catch (Exception)
{
return(StatusCode(StatusCodes.Status500InternalServerError));
throw;
}
}
public bool SaveInsurenceConfigDetails(InsurenceConfigDTO InsurenceConfigDetails, string ConnectionString)
{
StringBuilder Sbinsert = new StringBuilder();
StringBuilder QueryUpdate = new StringBuilder();
bool IsSaved = false;
string Recordid = string.Empty;
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (InsurenceConfigDetails.lstInsurenceConfigartionDetails != null)
{
for (int i = 0; i < InsurenceConfigDetails.lstInsurenceConfigartionDetails.Count; i++)
{
if (InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pTypeofOperation.Trim().ToUpper() != "CREATE")
{
if (string.IsNullOrEmpty(Recordid))
{
Recordid = InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].precordid.ToString();
}
else
{
Recordid = Recordid + "," + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].precordid.ToString();
}
}
if (InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pTypeofOperation.Trim().ToUpper() == "CREATE")
{
Sbinsert.Append("insert into tblmstinsuranceconfigdetails(insuranceschemeconfigid,insuranceschemename,membertypeid,membertype,applicanttype,agefrom,ageto,insuranceduration,premiumamountpayable,premiumpayin,insuranceclaimpayoutevent,insuranceclaimamount,premiumrefund,statusid,createdby,createddate)VALUES(" + InsurenceConfigDetails.pInsurenceconfigid + ",'" + ManageQuote(InsurenceConfigDetails.pInsurencename) + "'," + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pMembertypeid + ",'" + ManageQuote(InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pMembertype) + "','" + ManageQuote(InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pApplicanttype) + "'," + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pAgefrom + "," + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pAgeto + ",'" + ManageQuote(InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pInsuranceduration) + "'," + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pPremiumamountpayable + ",'" + ManageQuote(InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pPremiumpayin) + "','" + ManageQuote(InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pInsuranceclaimpayoutevent) + "'," + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pInsuranceclaimamount + "," + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pPremiumrefund + "," + Convert.ToInt32(Status.Active) + "," + InsurenceConfigDetails.pCreatedby + ",current_timestamp);");
}
else if (InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pTypeofOperation.Trim().ToUpper() == "UPDATE")
{
Sbinsert.Append("update tblmstinsuranceconfigdetails set membertypeid=" + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pMembertypeid + ",membertype='" + ManageQuote(InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pMembertype) + "',applicanttype='" + ManageQuote(InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pApplicanttype) + "',agefrom=" + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pAgefrom + ",ageto=" + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pAgeto + ",insuranceduration='" + ManageQuote(InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pInsuranceduration) + "',premiumamountpayable=" + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pPremiumamountpayable + ",premiumpayin='" + ManageQuote(InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pPremiumpayin) + "',insuranceclaimpayoutevent='" + ManageQuote(InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pInsuranceclaimpayoutevent) + "',insuranceclaimamount=" + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pInsuranceclaimamount + ",premiumrefund=" + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pPremiumrefund + ",modifiedby=" + InsurenceConfigDetails.pCreatedby + ",modifieddate=current_timestamp,statusid=" + Convert.ToInt32(Status.Active) + " where recordid=" + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].precordid + ";");
}
}
}
if (!string.IsNullOrEmpty(Recordid))
{
QueryUpdate.Append("update tblmstinsuranceconfigdetails set statusid=" + Convert.ToInt32(Status.Inactive) + ",modifiedby=" + InsurenceConfigDetails.pCreatedby + ",modifieddate=current_timestamp where insuranceschemeconfigid=" + InsurenceConfigDetails.pInsurenceconfigid + " and recordid not in(" + Recordid + ");");
}
else
{
if (InsurenceConfigDetails.lstInsurenceConfigartionDetails.Count == 0)
{
QueryUpdate.Append("update tblmstinsuranceconfigdetails set statusid=" + Convert.ToInt32(Status.Inactive) + ",modifiedby=" + InsurenceConfigDetails.pCreatedby + ",modifieddate=current_timestamp where insuranceschemeconfigid=" + InsurenceConfigDetails.pInsurenceconfigid + ";");
}
}
if (!string.IsNullOrEmpty(Sbinsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, QueryUpdate.ToString() + "" + 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);
}