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; }