public List<Ingredient> GetNonMedIngredientByLicenceNumber(int licenceNumber, string lang)
{
var items = new List<Ingredient>();
string commandText = "SELECT DISTINCT I.SUBMISSION_ID, I.MATRIX_ID, I.MATRIX_TYPE_CODE, ";
if (lang.Equals("fr"))
{
commandText += "I.INGREDIENT_NAME_OTHER as I.INGREDIENT_NAME ";
}
else {
commandText += "I.INGREDIENT_NAME ";
}
commandText += "FROM NHPPLQ_OWNER.INGREDIENT_ONLINE I, NHPPLQ_OWNER.PRODUCT_LICENCE_ONLINE L WHERE L.SUBMISSION_ID = I.SUBMISSION_ID AND I.MATRIX_TYPE_CODE = 3 AND L.LICENCE_NUMBER = " + licenceNumber;
commandText += " ORDER BY I.INGREDIENT_NAME ASC";
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 Ingredient();
item.submission_id = dr["SUBMISSION_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SUBMISSION_ID"]);
item.ingredient_name = dr["INGREDIENT_NAME"] == DBNull.Value ? string.Empty : dr["INGREDIENT_NAME"].ToString().Trim();
item.matrix_id = dr["MATRIX_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["MATRIX_ID"]);
item.matrix_type_code = dr["MATRIX_TYPE_CODE"] == DBNull.Value ? 0 : Convert.ToInt32(dr["MATRIX_TYPE_CODE"]);
items.Add(item);
}
}
}
}
catch (Exception ex)
{
string errorMessages = string.Format("DbConnection.cs - GetNonMedIngredientByLicenceNumber()");
ExceptionHelper.LogException(ex, errorMessages);
}
finally
{
if (con.State == ConnectionState.Open)
con.Close();
}
}
return items;
}
public Ingredient Get(int id, string lang)
{
_ingredient = dbConnection.GetIngredientById(id, lang);
return _ingredient;
}
public Ingredient GetIngredientById(int id, string lang)
{
var ingredient = new Ingredient();
string commandText = "SELECT SUBMISSION_ID, MATRIX_ID, MATRIX_TYPE_CODE, ";
if (lang.Equals("fr"))
{
commandText += "INGREDIENT_NAME_OTHER as INGREDIENT_NAME ";
}
else {
commandText += "INGREDIENT_NAME ";
}
commandText += "FROM NHPPLQ_OWNER.INGREDIENT_ONLINE WHERE SUBMISSION_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 Ingredient();
item.submission_id = dr["SUBMISSION_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SUBMISSION_ID"]);
item.ingredient_name = dr["INGREDIENT_NAME"] == DBNull.Value ? string.Empty : dr["INGREDIENT_NAME"].ToString().Trim();
item.matrix_id = dr["MATRIX_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["MATRIX_ID"]);
item.matrix_type_code = dr["MATRIX_TYPE_CODE"] == DBNull.Value ? 0 : Convert.ToInt32(dr["MATRIX_TYPE_CODE"]);
item.quantity_list = GetAllIngredientQuantityByMatrixId(item.matrix_id, lang);
ingredient = item;
}
}
}
}
catch (Exception ex)
{
string errorMessages = string.Format("DbConnection.cs - GetIngredientById()");
ExceptionHelper.LogException(ex, errorMessages);
}
finally
{
if (con.State == ConnectionState.Open)
con.Close();
}
}
return ingredient;
}