private void GetSavedRecipes()
{
try
{
using (SqlConnection sqlConn = new SqlConnection(sqlConnection))
{
sqlConn.Open();
SqlCommand cmd = sqlConn.CreateCommand();
//cmd.CommandText = "select r.*, ri.TargetPercent, ri.IngredientWeight, ri.SortIndex, i.* from recipes r inner join RecipeIngredient ri on r.recipeId = ri.recipeId and r.ProcessLine = '" + ProcessLineName + "' inner join ingredients i on ri.ingredientid = i.ingredientid order by r.RecipeID, ri.SortIndex";
cmd.CommandText = @" SELECT r.*, ri.TargetPercent, ri.IngredientWeight, ri.SortIndex, i.*
FROM recipes r inner join RecipeIngredient ri on r.recipeId = ri.recipeId and r.ProcessLine = '" + ProcessLineName + "'"
+ @"INNER JOIN (SELECT BinID IngredientID, ProductName IngredientName, BinNumber, BinScaleNumber ScaleNumber, MinValue, MaxValue, ProductType
FROM( SELECT b.BinID, b.BinNumber, bp.BinProductID, p.ProductName +'(' + p.ProductAbbreviation+')' ProductName, b.BinScaleNumber, b.MinValue, b.MaxValue, p.ProductType,
RANK() OVER (PARTITION BY bp.BinID ORDER BY bp.ts_datetime DESC) groupNumber
FROM BinProductChangeLog bp
INNER JOIN Bins b on b.BinID = bp.binID AND b.binNumber in(402, 403,404,405,406,407, 5031, 5033, 5034, 5035, 5036) /*Only Mixing line Bins*/
INNER JOIN Products p on bp.BinProductID = p.ProductID )a WHERE groupNumber = 1) i /*Only get latest record per bin */
ON ri.ingredientid = i.ingredientid order by r.RecipeID, ri.SortIndex";
SqlDataReader resultReader = cmd.ExecuteReader();
int iRecipeID = 0;
Recipe r = new Recipe();
while (resultReader.Read())
{
if (iRecipeID != resultReader.GetInt32(0))
{
iRecipeID = resultReader.GetInt32(0);
r = new Recipe() { RecipeId = Convert.ToInt32(resultReader["RecipeID"]), RecipeName = resultReader["RecipeName"].ToString(), RecipeBatchWeight = Convert.ToDouble(resultReader["BatchWeight"]), ProcessLineName = resultReader["ProcessLine"].ToString() };
r.ocIngredients.Add(new Ingredient()
{
IngredientPercentageValue = Convert.ToDouble(resultReader["TargetPercent"]),
IngredientKilogramValue = Convert.ToDouble(resultReader["IngredientWeight"]),
IngredientID = Convert.ToInt32(resultReader["IngredientID"]),
IngredientName = resultReader["IngredientName"].ToString(),
IngredientBinNumber = Convert.ToInt32(resultReader["BinNumber"]),
IngredientScaleNumber = Convert.ToInt32(resultReader["ScaleNumber"]),
MinimumValue = Convert.ToDouble(resultReader["MinValue"]),
MaximumValue = Convert.ToDouble(resultReader["MaxValue"]),
IngredientType = Convert.ToInt32(resultReader["ProductType"])
});
ocAvailableRecipes.Add(r);
}
else
{
r.ocIngredients.Add(new Ingredient()
{
IngredientPercentageValue = Convert.ToDouble(resultReader["TargetPercent"]),
IngredientKilogramValue = Convert.ToDouble(resultReader["IngredientWeight"]),
IngredientID = Convert.ToInt32(resultReader["IngredientID"]),
IngredientName = resultReader["IngredientName"].ToString(),
IngredientBinNumber = Convert.ToInt32(resultReader["BinNumber"]),
IngredientScaleNumber = Convert.ToInt32(resultReader["ScaleNumber"]),
MinimumValue = Convert.ToDouble(resultReader["MinValue"]),
MaximumValue = Convert.ToDouble(resultReader["MaxValue"]),
IngredientType = Convert.ToInt32(resultReader["ProductType"])
});
}
}
resultReader.Close();
sqlConn.Close();
}
}
catch (Exception)
{
//ErrorMessgae = ex.Message;
}
}
public void cmdNewRecipeImplementation()
{
Recipe r = new Recipe() { RecipeId = -1, RecipeName = "New Recipe" };
r.ProcessLineName = ProcessLineName;
AvailableRecipes.Add(r);
SelectedRecipe = r;
}