private ProductRisk executeOne(string query, string lang)
{
ProductRisk item = null;
using (OracleConnection con = new OracleConnection(LnhpdDBConnection))
{
try
{
con.Open();
OracleCommand cmd = new OracleCommand(query, con);
using (OracleDataReader dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
dr.Read();
item = ProductRiskFactory(dr, lang);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
return(item);
}
private ProductRisk ProductRiskFactory(OracleDataReader reader, string lang)
{
var item = new ProductRisk();
string risk_text_e = reader["RISK_TEXT_E"] == DBNull.Value ? string.Empty : reader["RISK_TEXT_E"].ToString().Trim();
string risk_text_f = reader["RISK_TEXT_F"] == DBNull.Value ? string.Empty : reader["RISK_TEXT_F"].ToString().Trim();
if (risk_text_e.Length > 3999)
{
risk_text_e = "N/A";
}
item.lnhpd_id = reader["SUBMISSION_ID"] == DBNull.Value ? 0 : idLnhpd(Convert.ToInt32(reader["SUBMISSION_ID"]));
item.risk_id = reader["RISK_ID"] == DBNull.Value ? 0 : Convert.ToInt32(reader["RISK_ID"]);
if (lang.Equals("fr"))
{
item.risk_type_desc = reader["RISK_TYPE_DESC_F"] == DBNull.Value ? string.Empty : reader["RISK_TYPE_DESC_F"].ToString().Trim();
item.sub_risk_type_desc = reader["SUB_RISK_TYPE_DESC_F"] == DBNull.Value ? string.Empty : reader["SUB_RISK_TYPE_DESC_F"].ToString().Trim();
item.risk_text = risk_text_f == "" ? risk_text_e : risk_text_f;
}
else
{
item.risk_type_desc = reader["RISK_TYPE_DESC"] == DBNull.Value ? string.Empty : reader["RISK_TYPE_DESC"].ToString().Trim();
item.sub_risk_type_desc = reader["SUB_RISK_TYPE_DESC"] == DBNull.Value ? string.Empty : reader["SUB_RISK_TYPE_DESC"].ToString().Trim();
item.risk_text = risk_text_e == "N/A" ? risk_text_f : risk_text_e;
}
//item.risk_type_desc = reader["RISK_TYPE_DESC"] == DBNull.Value ? string.Empty : reader["RISK_TYPE_DESC"].ToString().Trim();
//item.sub_risk_type_desc = reader["SUB_RISK_TYPE_DESC"] == DBNull.Value ? string.Empty : reader["SUB_RISK_TYPE_DESC"].ToString().Trim();
//var riskTextList = GetAllProductRiskTextByRiskId(item.risk_id,lang);
//if (riskTextList != null && riskTextList.Count > 0)
//{
// item.risk_text_list = riskTextList;
//}
//item.risk_text = reader["RISK_TEXT"] == DBNull.Value ? string.Empty : reader["RISK_TEXT"].ToString().Trim();
return(item);
}
public ProductRisk GetProductRiskById(int id, string lang)
{
var risk = new ProductRisk();
string commandText = "SELECT SUBMISSION_ID, RISK_ID, ";
if (lang.Equals("fr"))
{
commandText += "RISK_TYPE_DESC_F as RISK_TYPE_DESC, SUB_RISK_TYPE_DESC_F as SUB_RISK_TYPE_DESC ";
}
else {
commandText += "RISK_TYPE_DESC, SUB_RISK_TYPE_DESC ";
}
commandText += "FROM NHPPLQ_OWNER.PRODUCT_RISK_ONLINE WHERE RISK_ID = " + id;
using (
OracleConnection con = new OracleConnection(LnhpdDBConnection))
{
OracleCommand cmd = new OracleCommand(commandText, con);
try
{
con.Open();
using (OracleDataReader dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
while (dr.Read())
{
var item = new ProductRisk();
item.submission_id = dr["SUBMISSION_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SUBMISSION_ID"]);
item.risk_id = dr["RISK_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["RISK_ID"]);
item.risk_type_desc = dr["RISK_TYPE_DESC"] == DBNull.Value ? string.Empty : dr["RISK_TYPE_DESC"].ToString().Trim();
item.sub_risk_type_desc = dr["SUB_RISK_TYPE_DESC"] == DBNull.Value ? string.Empty : dr["SUB_RISK_TYPE_DESC"].ToString().Trim();
risk = item;
}
}
}
}
catch (Exception ex)
{
string errorMessages = string.Format("DbConnection.cs - GetProductRiskById()");
ExceptionHelper.LogException(ex, errorMessages);
}
finally
{
if (con.State == ConnectionState.Open)
con.Close();
}
}
return risk;
}
public List<ProductRisk> GetRiskByLicenceNumber(int licenceNumber, string lang)
{
var items = new List<ProductRisk>();
string commandText = "SELECT DISTINCT R.SUBMISSION_ID, R.RISK_ID, ";
if (lang.Equals("fr"))
{
commandText += "R.RISK_TYPE_DESC_F as R.RISK_TYPE_DESC, R.SUB_RISK_TYPE_DESC_F as R.SUB_RISK_TYPE_DESC ";
}
else {
commandText += "R.RISK_TYPE_DESC, R.SUB_RISK_TYPE_DESC ";
}
commandText += "FROM NHPPLQ_OWNER.PRODUCT_LICENCE_ONLINE P, NHPPLQ_OWNER.PRODUCT_RISK_ONLINE R WHERE P.SUBMISSION_ID = R.SUBMISSION_ID AND P.LICENCE_NUMBER = " + licenceNumber;
using (
OracleConnection con = new OracleConnection(LnhpdDBConnection))
{
OracleCommand cmd = new OracleCommand(commandText, con);
try
{
con.Open();
using (OracleDataReader dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
while (dr.Read())
{
var item = new ProductRisk();
item.submission_id = dr["SUBMISSION_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SUBMISSION_ID"]);
item.risk_id = dr["RISK_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["RISK_ID"]);
item.risk_type_desc = dr["RISK_TYPE_DESC"] == DBNull.Value ? string.Empty : dr["RISK_TYPE_DESC"].ToString().Trim();
item.sub_risk_type_desc = dr["SUB_RISK_TYPE_DESC"] == DBNull.Value ? string.Empty : dr["SUB_RISK_TYPE_DESC"].ToString().Trim();
item.risk_text_list = GetAllProductRiskTextByRiskId(item.risk_id, lang);
items.Add(item);
}
}
}
}
catch (Exception ex)
{
string errorMessages = string.Format("DbConnection.cs - GetRiskByLicenceNumber()");
ExceptionHelper.LogException(ex, errorMessages);
}
finally
{
if (con.State == ConnectionState.Open)
con.Close();
}
}
return items;
}
public ProductRisk Get(int id, string lang)
{
_risk = dbConnection.GetProductRiskById(id, lang);
return _risk;
}
