public IngredientQuantity Get(int id, string lang)
{
_quantity = dbConnection.GetIngredientQuantityById(id, lang);
return _quantity;
}
public IngredientQuantity GetIngredientQuantityById(int id, string lang)
{
var quantity = new IngredientQuantity();
string commandText = "SELECT INGREDIENT_AMOUNT_ID, MATRIX_ID, QUANTITY, QUANTITY_MINIMUM, QUANTITY_MAXIMUM, RATIO_NUMERATOR, RATIO_DENOMINATOR, DRIED_HERB_EQUIVALENT, POTENCY_AMOUNT, POTENCY_CONSTITUENT, ";
if (lang.Equals("fr"))
{
commandText += "UOM_TYPE_DESC_AMT_QUANTITY_F as UOM_TYPE_DESC_AMT_QUANTITY, UOM_TYPE_DESC_DHE_F as UOM_TYPE_DESC_DHE, EXTRACT_TYPE_DESC_F as EXTRACT_TYPE_DESC, UOM_TYPE_DESC_POTENCY_F as UOM_TYPE_DESC_POTENCY ";
}
else {
commandText += "UOM_TYPE_DESC_AMT_QUANTITY, UOM_TYPE_DESC_DHE, EXTRACT_TYPE_DESC, UOM_TYPE_DESC_POTENCY ";
}
commandText += "FROM NHPPLQ_OWNER.INGREDIENT_QUANTITY_ONLINE WHERE INGREDIENT_AMOUNT_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 IngredientQuantity();
item.ingredient_amount_id = dr["INGREDIENT_AMOUNT_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["INGREDIENT_AMOUNT_ID"]);
item.matrix_id = dr["MATRIX_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["MATRIX_ID"]);
item.quantity = dr["QUANTITY"] == DBNull.Value ? 0 : Convert.ToDouble(dr["QUANTITY"]);
item.quantity_minimum = dr["QUANTITY_MINIMUM"] == DBNull.Value ? 0 : Convert.ToDouble(dr["QUANTITY_MINIMUM"]);
item.quantity_maximum = dr["QUANTITY_MAXIMUM"] == DBNull.Value ? 0 : Convert.ToDouble(dr["QUANTITY_MAXIMUM"]);
item.quantity_uom_type_desc = dr["UOM_TYPE_DESC_AMT_QUANTITY"] == DBNull.Value ? string.Empty : dr["UOM_TYPE_DESC_AMT_QUANTITY"].ToString().Trim();
item.ratio_numerator = dr["RATIO_NUMERATOR"] == DBNull.Value ? string.Empty : dr["RATIO_NUMERATOR"].ToString().Trim();
item.ratio_denominator = dr["RATIO_DENOMINATOR"] == DBNull.Value ? string.Empty : dr["RATIO_DENOMINATOR"].ToString().Trim();
item.dried_herb_equivalent = dr["DRIED_HERB_EQUIVALENT"] == DBNull.Value ? string.Empty : dr["DRIED_HERB_EQUIVALENT"].ToString().Trim();
item.dried_herb_equivalent_uom_type_desc = dr["UOM_TYPE_DESC_DHE"] == DBNull.Value ? string.Empty : dr["UOM_TYPE_DESC_DHE"].ToString().Trim();
item.extract_type_desc = dr["EXTRACT_TYPE_DESC"] == DBNull.Value ? string.Empty : dr["EXTRACT_TYPE_DESC"].ToString().Trim();
item.potency = dr["POTENCY_AMOUNT"] == DBNull.Value ? 0 : Convert.ToDouble(dr["POTENCY_AMOUNT"]);
item.potency_uom_type_desc = dr["UOM_TYPE_DESC_POTENCY"] == DBNull.Value ? string.Empty : dr["UOM_TYPE_DESC_POTENCY"].ToString().Trim();
item.potency_constituent = dr["POTENCY_CONSTITUENT"] == DBNull.Value ? string.Empty : dr["POTENCY_CONSTITUENT"].ToString().Trim();
quantity = item;
}
}
}
}
catch (Exception ex)
{
string errorMessages = string.Format("DbConnection.cs - GetIngredientQuantityById()");
ExceptionHelper.LogException(ex, errorMessages);
}
finally
{
if (con.State == ConnectionState.Open)
con.Close();
}
}
return quantity;
}
public List<IngredientQuantity> GetAllIngredientQuantityByMatrixId(int matrix_id, string lang)
{
var items = new List<IngredientQuantity>();
var newItems = new List<IngredientQuantity>(); // MassagedItems contains merged Potencies with the same ingredient names
string commandText = "SELECT I.INGREDIENT_NAME, Q.INGREDIENT_AMOUNT_ID, Q.MATRIX_ID, Q.QUANTITY, Q.QUANTITY_MINIMUM, Q.QUANTITY_MAXIMUM, Q.RATIO_NUMERATOR, Q.RATIO_DENOMINATOR, Q.DRIED_HERB_EQUIVALENT, Q.POTENCY_AMOUNT, Q.POTENCY_CONSTITUENT, ";
if (lang.Equals("fr"))
{
commandText += "Q.UOM_TYPE_DESC_AMT_QUANTITY_F as Q.UOM_TYPE_DESC_AMT_QUANTITY, Q.UOM_TYPE_DESC_DHE_F as Q.UOM_TYPE_DESC_DHE, Q.EXTRACT_TYPE_DESC_F as Q.EXTRACT_TYPE_DESC, Q.UOM_TYPE_DESC_POTENCY_F as Q.UOM_TYPE_DESC_POTENCY ";
}
else {
commandText += "Q.UOM_TYPE_DESC_AMT_QUANTITY, Q.UOM_TYPE_DESC_DHE, Q.EXTRACT_TYPE_DESC, Q.UOM_TYPE_DESC_POTENCY ";
}
commandText += "FROM NHPPLQ_OWNER.INGREDIENT_QUANTITY_ONLINE Q, NHPPLQ_OWNER.INGREDIENT_ONLINE I WHERE I.MATRIX_ID = Q.MATRIX_ID AND Q.MATRIX_ID = " + matrix_id;
commandText += " ORDER BY UPPER(I.INGREDIENT_NAME), Q.QUANTITY, Q.RATIO_NUMERATOR 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 IngredientQuantity();
var quantityString = "";
var extractString = "";
var potencyString = "";
item.ingredient_amount_id = dr["INGREDIENT_AMOUNT_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["INGREDIENT_AMOUNT_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.quantity = dr["QUANTITY"] == DBNull.Value ? 0 : Convert.ToDouble(dr["QUANTITY"]);
item.quantity_minimum = dr["QUANTITY_MINIMUM"] == DBNull.Value ? 0 : Convert.ToDouble(dr["QUANTITY_MINIMUM"]);
item.quantity_maximum = dr["QUANTITY_MAXIMUM"] == DBNull.Value ? 0 : Convert.ToDouble(dr["QUANTITY_MAXIMUM"]);
item.quantity_uom_type_desc = dr["UOM_TYPE_DESC_AMT_QUANTITY"] == DBNull.Value ? string.Empty : dr["UOM_TYPE_DESC_AMT_QUANTITY"].ToString().Trim();
item.ratio_numerator = dr["RATIO_NUMERATOR"] == DBNull.Value ? string.Empty : dr["RATIO_NUMERATOR"].ToString().Trim();
item.ratio_denominator = dr["RATIO_DENOMINATOR"] == DBNull.Value ? string.Empty : dr["RATIO_DENOMINATOR"].ToString().Trim();
item.dried_herb_equivalent = dr["DRIED_HERB_EQUIVALENT"] == DBNull.Value ? string.Empty : dr["DRIED_HERB_EQUIVALENT"].ToString().Trim();
item.dried_herb_equivalent_uom_type_desc = dr["UOM_TYPE_DESC_DHE"] == DBNull.Value ? string.Empty : dr["UOM_TYPE_DESC_DHE"].ToString().Trim();
item.extract_type_desc = dr["EXTRACT_TYPE_DESC"] == DBNull.Value ? string.Empty : dr["EXTRACT_TYPE_DESC"].ToString().Trim();
item.potency = dr["POTENCY_AMOUNT"] == DBNull.Value ? 0 : Convert.ToDouble(dr["POTENCY_AMOUNT"]);
item.potency_uom_type_desc = dr["UOM_TYPE_DESC_POTENCY"] == DBNull.Value ? string.Empty : dr["UOM_TYPE_DESC_POTENCY"].ToString().Trim();
item.potency_constituent = dr["POTENCY_CONSTITUENT"] == DBNull.Value ? string.Empty : dr["POTENCY_CONSTITUENT"].ToString().Trim();
// Construct full Quantity String
if (item.quantity != 0)
{
if ((item.quantity % 1) == 0)
{
quantityString += item.quantity + ".0 " + item.quantity_uom_type_desc + " ";
}
else {
quantityString += item.quantity + " " + item.quantity_uom_type_desc + " ";
}
}
if (item.quantity_minimum != 0 && item.quantity_maximum != 0)
{
quantityString += "Min: " + item.quantity_minimum + " " + item.quantity_uom_type_desc;
quantityString += "<br />Max: " + item.quantity_maximum + " " + item.quantity_uom_type_desc + " ";
}
else {
if (item.quantity_minimum != 0)
{
quantityString += "Min: " + item.quantity_minimum + " " + item.quantity_uom_type_desc;
}
if (item.quantity_maximum != 0)
{
quantityString += "<br />Max: " + item.quantity_maximum + " " + item.quantity_uom_type_desc;
}
}
item.quantity_string = quantityString;
// Construct full Extract String
if (item.ratio_numerator != null)
{
extractString += item.ratio_numerator + " : ";
if (item.ratio_denominator != null)
{
extractString += item.ratio_denominator + "<br />";
}
}
if (item.dried_herb_equivalent != null)
{
extractString += "DHE: " + item.dried_herb_equivalent;
}
if (item.dried_herb_equivalent_uom_type_desc != null)
{
extractString += " " + item.dried_herb_equivalent_uom_type_desc + " ";
}
if (item.extract_type_desc != null)
{
extractString += "<br />" + item.extract_type_desc;
}
item.extract_string = extractString;
// Construct full Potency String
if (item.potency != 0)
{
if ((item.potency % 1) == 0)
{
potencyString += item.potency + ".0";
}
else {
potencyString += item.potency;
}
if (item.potency_uom_type_desc != null)
{
potencyString += " " + item.potency_uom_type_desc;
}
if (item.potency_constituent != null)
{
potencyString += "<br />" + item.potency_constituent;
}
}
item.potency_string = potencyString;
items.Add(item);
}
}
}
}
catch (Exception ex)
{
string errorMessages = string.Format("DbConnection.cs - GetAllIngredientQuantity()");
ExceptionHelper.LogException(ex, errorMessages);
}
finally
{
if (con.State == ConnectionState.Open)
con.Close();
}
}
return this.mergeMedIngredientRow(items, newItems);
}
public List<IngredientQuantity> mergeMedIngredientRow(List<IngredientQuantity> medInglist, List<IngredientQuantity> newMedInglist)
{
if (medInglist == null || medInglist.Count == 0)
{
return newMedInglist;
}
if (newMedInglist == null || newMedInglist.Count == 0)
{
newMedInglist.Add(medInglist[0]);
medInglist.Remove(medInglist[0]);
return mergeMedIngredientRow(medInglist, newMedInglist);
}
IngredientQuantity iq = new IngredientQuantity();
if (medInglist[0].ingredient_name == newMedInglist[newMedInglist.Count - 1].ingredient_name)
{
if (medInglist[0].quantity > 0)
{
newMedInglist[newMedInglist.Count - 1].quantity_string += "<br />" + medInglist[0].quantity_string;
}
if (medInglist[0].ratio_numerator != null)
{
newMedInglist[newMedInglist.Count - 1].extract_string += "<br />" + medInglist[0].extract_string;
}
if (medInglist[0].potency_string != "")
{
newMedInglist[newMedInglist.Count - 1].potency_string += "<br />" + medInglist[0].potency_string;
}
medInglist.Remove(medInglist[0]);
}
else
{
newMedInglist.Add(medInglist[0]);
medInglist.Remove(medInglist[0]);
}
if (medInglist.Count > 0)
{
return mergeMedIngredientRow(medInglist, newMedInglist);
}
else
{
return newMedInglist;
}
}
public IngredientQuantity Get(int id, string lang)
{
_quantity = dbConnection.GetIngredientQuantityById(id, lang);
return(_quantity);
}