private void AddSubrecipe(int ParentRecipeRno, int SubrecipeRno, decimal Scaler, int JobRno, string Customer, int JobServings, string MenuItem, int RecipeRno, string Recipe, decimal Qty, int UnitRno, string UnitSingle, string UnitPlural, string Subrecipe, List <String> Notes)
{
Debug.WriteLine("> AddSubrecipe");
string Sql = string.Format(
"Select\n" +
"r.RecipeRno, r.Name As Subrecipe,\n" +
"r.NumServings, r.MenServingRatio, r.WomenServingRatio, r.ChildServingRatio,\n" +
"r.YieldQty, r.YieldUnitRno, (Select UnitPlural From Units Where UnitRno = r.YieldUnitRno) As YieldUnit,\n" +
"r.PortionQty, r.PortionUnitRno, (Select UnitPlural From Units Where UnitRno = r.PortionUnitRno) As PortionUnit,\n" +
"x.IngredRno, x.SubrecipeRno, x.UnitQty, x.UnitRno,\n" +
"(Select UnitSingle From Units Where UnitRno = x.UnitRno) As UnitSingle,\n" +
"(Select UnitPlural From Units Where UnitRno = x.UnitRno) As UnitPlural,\n" +
"i.Name, i.PrefVendors, i.StockedFlg,\n" +
"(Select Name From Recipes Where RecipeRno = {1}) As ParentRecipe,\n" +
"(Select UnitPlural From Units Where UnitRno = {2}) As ParentUnit\n" +
"From Recipes r\n" +
"Inner join RecipeIngredXref x On r.RecipeRno = x.RecipeRno\n" +
"Left Join Ingredients i On x.IngredRno = i.IngredRno\n" +
"Where r.RecipeRno = {0}\n" +
"And IsNull(i.NonPurchaseFlg, 0) = 0",
SubrecipeRno,
ParentRecipeRno,
UnitRno);
try
{
DataTable dt = db.DataTable(Sql);
foreach (DataRow dr in dt.Rows)
{
int IngredRno = DB.Int32(dr["IngredRno"]);
int xSubrecipeRno = DB.Int32(dr["SubrecipeRno"]);
decimal YieldQty = DB.Dec(dr["YieldQty"]);
int YieldUnitRno = DB.Int32(dr["YieldUnitRno"]);
Ingred Ingred = new Ingred(0);
decimal ConversionScaler = Ingred.ConversionScaler(UnitRno, YieldUnitRno);
decimal NextScaler = Scaler * Qty * ConversionScaler;
if (ConversionScaler == 0)
{
string Note = string.Format("<a href='Recipes.aspx?Rno={0}' target='Fix' tabindex='-1'><i class=\"icon-warning-sign\"></i> <b>{2}'s</b> unit needs to use subrecipe's yield unit ({3}) or a known unit.</a>", ParentRecipeRno, DB.Str(dr["ParentUnit"]), DB.Str(dr["Subrecipe"]), DB.Str(dr["YieldUnit"]));
Notes.Add(Note);
}
// if an ingredient
if (IngredRno != 0)
{
AddIngredient(dr);
AddServings(IngredRno, NextScaler, JobRno, Customer, JobServings, MenuItem, RecipeRno, Recipe, YieldQty, DB.Dec(dr["UnitQty"]), DB.Int32(dr["UnitRno"]), DB.Str(dr["UnitSingle"]), DB.Str(dr["UnitPlural"]), Subrecipe + " < " + DB.Str(dr["Subrecipe"]), Notes);
}
// if a subrecipe
if (xSubrecipeRno != 0)
{
if (YieldQty == 0)
{
string Note = string.Format("<a href='Recipes.aspx?Rno={0}' target='_blank' tabindex='-1'><i class=\"icon-warning-sign\"></i> Recipe for <b>{1}</b> is missing the yield quantity.</a>", SubrecipeRno, DB.Str(dr["Subrecipe"]));
Notes.Add(Note);
YieldQty = 1;
}
AddSubrecipe(SubrecipeRno, xSubrecipeRno, NextScaler / YieldQty, JobRno, Customer, JobServings, MenuItem, RecipeRno, Recipe, DB.Dec(dr["UnitQty"]), DB.Int32(dr["UnitRno"]), DB.Str(dr["UnitSingle"]), DB.Str(dr["UnitPlural"]), Subrecipe + " < " + DB.Str(dr["Subrecipe"]), Notes);
}
}
}
catch (Exception Ex)
{
Err Err = new Err(Ex, Sql);
Response.Write(Err.Html());
}
Debug.WriteLine("< AddSubrecipe");
}
private void LoadConversions(int IngredRno)
{
if (IngredRno > 0)
{
String Sql = "";
try
{
Sql = string.Format(
"Select IngredConvRno, c.PurchaseQty, c.RecipeQty, c.MissingFlg, " +
"pu.UnitSingle As PurchaseUnitSingle, pu.UnitPlural As PurchaseUnitPlural, " +
"ru.UnitSingle As RecipeUnitSingle, ru.UnitPlural As RecipeUnitPlural " +
"From IngredConv c " +
"Left Join Units pu On c.PurchaseUnitRno = pu.UnitRno " +
"Left Join Units ru On c.RecipeUnitRno = ru.UnitRno " +
"Where c.IngredRno = {0} " +
"Order By pu.UnitSingle",
IngredRno);
DataTable dt = db.DataTable(Sql);
cConversions = dt.Rows.Count;
// prep for finding missing conversions
Ingred IngredData = new Ingred(IngredRno);
Ingred.LoadIngredPurchases();
Ingred.LoadIngredConversions();
// find any recipe units that doesn't have an easy conversion to the last purchase unit
string[] RecipeUnitRnos = { };
Ingred.IngredPurchase Purchase = Ingred.LastPurchase(IngredRno);
if (Purchase != null)
{
Sql = string.Format("Select Distinct UnitRno From RecipeIngredXref Where IngredRno = {0}", IngredRno);
RecipeUnitRnos = db.StrArray(Sql);
for (int i = 0; i < RecipeUnitRnos.Length; i++)
{
int RecipeUnitRno = Str.Num(RecipeUnitRnos[i]);
if (IngredData.ConversionScaler(RecipeUnitRno, Purchase.UnitRno) == 0)
{
// needs conversion
cConversions++;
}
else
{
// doesn't need conversion
RecipeUnitRnos[i] = string.Empty;
}
}
}
AddLines();
// existing conversions
int iRow = 0;
foreach (DataRow dr in dt.Rows)
{
iRow++;
TableRow tr = tblConv.Rows[iRow + 1];
HtmlInputHidden hfIngredConvRno = (HtmlInputHidden)tr.FindControl("hfIngredConvRno" + iRow);
TextBox txtQty = (TextBox)tr.FindControl("txtQty" + iRow);
int iCell = 0;
tr.Cells[iCell++].Text = string.Empty;
iCell++;
hfIngredConvRno.Value = DB.Int32(dr["IngredConvRno"]).ToString();
decimal Qty = DB.Dec(dr["RecipeQty"]);
txtQty.Text = Str.ShowFract(Qty);
tr.Cells[iCell++].Text = DB.Str(dr["Recipe" + (Qty <= 1 ? "UnitSingle" : "UnitPlural")]);
Qty = DB.Dec(dr["PurchaseQty"]);
tr.Cells[iCell++].Text = Str.ShowFract(Qty);
tr.Cells[iCell++].Text = DB.Str(dr["Purchase" + (Qty <= 1 ? "UnitSingle" : "UnitPlural")]);
}
// needed new conversions
for (int i = 0; i < RecipeUnitRnos.Length; i++)
{
int RecipeUnitRno = Str.Num(RecipeUnitRnos[i]);
if (RecipeUnitRno != 0)
{
iRow++;
TableRow tr = tblConv.Rows[iRow + 1];
HtmlInputHidden hfIngredConvRno = (HtmlInputHidden)tr.FindControl("hfIngredConvRno" + iRow);
TextBox txtQty = (TextBox)tr.FindControl("txtQty" + iRow);
HiddenField hfRecipeUnitRno = (HiddenField)tr.FindControl("hfRecipeUnitRno" + iRow);
HiddenField hfPurchaseQty = (HiddenField)tr.FindControl("hfPurchaseQty" + iRow);
HiddenField hfPurchaseUnitRno = (HiddenField)tr.FindControl("hfPurchaseUnitRno" + iRow);
int iCell = 0;
hfIngredConvRno.Value = "0";
txtQty.Text = "";
hfRecipeUnitRno.Value = RecipeUnitRno.ToString();
hfPurchaseQty.Value = (Purchase.Qty * Purchase.UnitQty).ToString();
hfPurchaseUnitRno.Value = Purchase.UnitRno.ToString();
tr.Cells[iCell++].Text = "<span class=\"NeedConv\"><i class=\"icon-warning-sign\" /> Enter missing quantity</span>";
iCell++;
tr.Cells[iCell++].Text = Ingred.Unit(RecipeUnitRno);
tr.Cells[iCell++].Text = Str.ShowFract(Purchase.Qty * Purchase.UnitQty);
tr.Cells[iCell++].Text = Ingred.Unit(Purchase.UnitRno);
}
}
}
catch (Exception Ex)
{
Err Err = new Err(Ex, Sql);
Response.Write(Err.Html());
}
}
}
private void Page_Load(object sender, System.EventArgs e)
{
db = new DB();
Pg = new WebPage("Images");
Pg.CheckLogin(Path.GetFileNameWithoutExtension(Page.AppRelativeVirtualPath));
// Put user code to initialize the page here
if (!Page.IsPostBack)
{
Session["Menu"] = WebPage.Recipes.Title;
//Setup();
string Sql = string.Empty;
try
{
// get list of recipes for menu items
Sql = "Select Name, RecipeRno From Recipes Where IsNull(HideFlg, 0) = 0 Order By Name";
DataTable dtRecipes = db.DataTable(Sql);
// menu item with a recipe or marked As Is
Sql =
"Select MenuItemRno, Category, MenuItem " +
"From mcJobMenuItems " +
"Where RecipeRno Is Null " +
"And IsNull(HideFlg, 0) = 0 " +
"And IsNull(AsIsFlg, 0) = 0 " +
"And MenuItem <> '' " +
"And Category <> '' " +
"And Category In (Select Category From mcJobMenuCategories Where IsNull(HideFlg, 0) = 0) " +
"Order By MenuItem, Category";
DataTable dt = db.DataTable(Sql);
foreach (DataRow dr in dt.Rows)
{
int MenuItemRno = DB.Int32(dr["MenuItemRno"]);
// category
HtmlTableRow tr = new HtmlTableRow();
HtmlTableCell td = new HtmlTableCell();
td.InnerText = DB.Str(dr["Category"]);
td.Attributes.Add("class", "category");
tr.Controls.Add(td);
// menu item
td = new HtmlTableCell();
td.Attributes.Add("class", "menu-item");
td.Attributes.Add("data-name", DB.Str(dr["MenuItem"]));
td.InnerText = DB.Str(dr["MenuItem"]);
tr.Controls.Add(td);
// Hide
td = new HtmlTableCell();
CheckBox chk = new CheckBox();
chk.CssClass = "set-as-hidden";
chk.ToolTip = "Hide this menu item";
chk.Attributes.Add("data-item-menu-rno", MenuItemRno.ToString());
td.Controls.Add(chk);
tr.Controls.Add(td);
// as is
td = new HtmlTableCell();
chk = new CheckBox();
chk.CssClass = "mark-as-is";
chk.ToolTip = "Set menu item As Is";
chk.Attributes.Add("data-item-menu-rno", MenuItemRno.ToString());
td.Controls.Add(chk);
// edit menu item icon
HtmlAnchor a = new HtmlAnchor();
a.HRef = string.Format("SetupMenuItems.aspx?Rno={0}", MenuItemRno);
a.Target = "Fix";
HtmlGenericControl i = new HtmlGenericControl("i");
i.Attributes.Add("class", "icon-edit");
i.Attributes.Add("title", "Edit the menu item");
a.Controls.Add(i);
td.Controls.Add(a);
tr.Controls.Add(td);
// recipe
td = new HtmlTableCell();
HtmlGenericControl s = new HtmlGenericControl("select");
s.Attributes.Add("class", "select-recipe");
s.Attributes.Add("data-item-menu-rno", MenuItemRno.ToString());
HtmlGenericControl o = new HtmlGenericControl("option");
o.InnerText = "Select Recipe";
o.Attributes.Add("value", "0");
o.Attributes.Add("style", "color: #888;");
s.Controls.Add(o);
foreach (DataRow drRecipe in dtRecipes.Rows)
{
o = new HtmlGenericControl("option");
o.InnerText = DB.Str(drRecipe["Name"]);
o.Attributes.Add("value", DB.Int32(drRecipe["RecipeRno"]).ToString());
s.Controls.Add(o);
}
td.Controls.Add(s);
a = new HtmlAnchor();
a.HRef = "Recipes.aspx";
a.Target = "Fix";
i = new HtmlGenericControl("i");
i.Attributes.Add("class", "icon-edit");
i.Attributes.Add("title", "Create a recipe for this menu item.");
a.Controls.Add(i);
td.Controls.Add(a);
td.Attributes.Add("class", "select");
tr.Controls.Add(td);
tblMenuItems.Rows.Add(tr);
}
divMenuItems.Visible = (dt.Rows.Count > 0);
// subrecipe with mismatched units
Sql =
"Select r.RecipeRno, r.Name As RecipeName, sr.Name As SubrecipeName, x.SubrecipeRno, x.UnitRno, sr.YieldUnitRno, " +
"(Select UnitSingle From Units Where UnitRno = x.UnitRno) As Unit, " +
"(Select UnitSingle From Units Where UnitRno = sr.YieldUnitRno) As YieldUnit " +
"From RecipeIngredXref x " +
"Inner Join Recipes sr On sr.RecipeRno = x.SubrecipeRno " +
"Inner Join Recipes r On r.RecipeRno = x.RecipeRno " +
"Where SubrecipeRno Is Not Null " +
"And x.UnitRno <> sr.YieldUnitRno " +
"And IsNull(r.HideFlg, 0) = 0 " +
"Order By r.Name, sr.Name";
dt = db.DataTable(Sql);
Ingred Ingred = new Ingred(0);
int cProblems = 0;
foreach (DataRow dr in dt.Rows)
{
decimal ConversionScaler = Ingred.ConversionScaler(DB.Int32(dr["UnitRno"]), DB.Int32(dr["YieldUnitRno"]));
if (ConversionScaler == 0)
{
cProblems++;
HtmlTableRow tr = new HtmlTableRow();
HtmlTableCell td = new HtmlTableCell();
HtmlAnchor a = new HtmlAnchor();
a.HRef = string.Format("Recipes.aspx?Rno={0}", DB.Int32(dr["RecipeRno"]));
a.InnerText = DB.Str(dr["RecipeName"]);
a.Target = "Fix";
td.Controls.Add(a);
tr.Controls.Add(td);
td = new HtmlTableCell();
td.InnerText = DB.Str(dr["Unit"]);;
tr.Controls.Add(td);
td = new HtmlTableCell();
td.InnerText = DB.Str(dr["YieldUnit"]);;
tr.Controls.Add(td);
td = new HtmlTableCell();
td.InnerText = DB.Str(dr["SubrecipeName"]);;
tr.Controls.Add(td);
tblSubrecipe.Rows.Add(tr);
}
}
divSubrecipe.Visible = (cProblems > 0);
// zero servings recipes
Sql =
"Select RecipeRno, Name From Recipes Where IsNull(NumServings, 0) = 0 " +
"And IsNull(SubrecipeFlg, 0) = 0 " +
"And IsNull(HideFlg, 0) = 0 " +
"Order By Name";
dt = db.DataTable(Sql);
foreach (DataRow dr in dt.Rows)
{
HtmlTableRow tr = new HtmlTableRow();
HtmlTableCell td = new HtmlTableCell();
HtmlAnchor a = new HtmlAnchor();
a.HRef = string.Format("Recipes.aspx?Rno={0}", DB.Int32(dr["RecipeRno"]));
a.InnerText = DB.Str(dr["Name"]);
a.Target = "Fix";
td.Controls.Add(a);
tr.Controls.Add(td);
tblRecipes.Rows.Add(tr);
}
divRecipes.Visible = (dt.Rows.Count > 0);
}
catch (Exception Ex)
{
Err Err = new Err(Ex, Sql);
Response.Write(Err.Html());
}
}
}
private void Page_Load(object sender, System.EventArgs e)
{
db = new DB();
Pg = new WebPage("Images");
Pg.CheckLogin(Path.GetFileNameWithoutExtension(Page.AppRelativeVirtualPath));
// Put user code to initialize the page here
if (!Page.IsPostBack)
{
Session["Menu"] = WebPage.Shopping.Title;
//Setup();
string Sql = string.Empty;
try
{
// ingredient without a receipt price
Sql =
"Select IngredRno, Name From Ingredients " +
"Where IsNull(HideFlg, 0) = 0 " +
"And IsNull(NonPurchaseFlg, 0) = 0 " +
"And IngredRno Not In (Select Distinct IngredRno From PurchaseDetails Where IngredRno Is Not Null) " +
"And IngredRno In (Select IngredRno From RecipeIngredXref) " +
"Order By Name";
DataTable dt = db.DataTable(Sql);
foreach (DataRow dr in dt.Rows)
{
int IngredRno = DB.Int32(dr["IngredRno"]);
HtmlTableRow tr = new HtmlTableRow();
HtmlTableCell td = new HtmlTableCell();
td.InnerText = DB.Str(dr["Name"]);
tr.Controls.Add(td);
td = new HtmlTableCell();
HtmlAnchor a = new HtmlAnchor();
a.HRef = string.Format("Purchases.aspx?IngredRno={0}", DB.Int32(dr["IngredRno"]));
a.InnerText = "Enter Receipt";
a.Attributes.Add("title", "Go to the Receipt page to enter a price.");
a.Target = "Fix";
td.Controls.Add(a);
td.Attributes.Add("class", "select");
tr.Controls.Add(td);
// non-purchase
td = new HtmlTableCell();
CheckBox chk = new CheckBox();
chk.CssClass = "set-non-purchase";
chk.ToolTip = "Set Ingredient as Non-Purchased";
chk.Attributes.Add("data-ingredient-rno", IngredRno.ToString());
td.Controls.Add(chk);
// edit ingredient icon
a = new HtmlAnchor();
a.HRef = string.Format("Ingredients.aspx?Rno={0}", IngredRno);
a.Target = "Fix";
HtmlGenericControl i = new HtmlGenericControl("i");
i.Attributes.Add("class", "icon-edit");
i.Attributes.Add("title", "Edit this ingredient");
a.Controls.Add(i);
td.Controls.Add(a);
tr.Controls.Add(td);
tblIngreds.Rows.Add(tr);
}
divIngreds.Visible = (dt.Rows.Count > 0);
// ingredient purchase units
Sql =
"Select i.IngredRno, i.Name, x.UnitRno, d.PurchaseUnitRno, " +
"(Select UnitSingle From Units Where UnitRno = x.UnitRno) As Unit, " +
"(Select UnitSingle From Units Where UnitRno = d.PurchaseUnitRno) As PurchaseUnit " +
"From RecipeIngredXref x " +
"Inner Join PurchaseDetails d On d.IngredRno = x.IngredRno And d.PurchaseRno = (Select Max(PurchaseRno) From PurchaseDetails Where IngredRno = x.IngredRno) " +
"Inner Join Recipes r On r.RecipeRno = x.RecipeRno " +
"Inner Join Ingredients i On i.IngredRno = x.IngredRno " +
"Where x.UnitRno <> d.PurchaseUnitRno " +
"And IsNull(r.HideFlg, 0) = 0 " +
"And IsNull(i.MenuItemAsIsFlg, 0) = 0 " +
"Group By i.Name, x.UnitRno, i.IngredRno, d.PurchaseUnitRno " +
"Order By i.Name";
dt = db.DataTable(Sql);
Ingred Ingred = new Ingred(0);
int cProblems = 0;
foreach (DataRow dr in dt.Rows)
{
int IngredRno = DB.Int32(dr["IngredRno"]);
Ingred = new Ingred(IngredRno);
decimal ConversionScaler = Ingred.ConversionScaler(DB.Int32(dr["UnitRno"]), DB.Int32(dr["PurchaseUnitRno"]));
if (ConversionScaler == 0)
{
cProblems++;
HtmlTableRow tr = new HtmlTableRow();
HtmlTableCell td = new HtmlTableCell();
HtmlAnchor a = new HtmlAnchor();
a.HRef = string.Format("Ingredients.aspx?Rno={0}", IngredRno);
a.InnerText = DB.Str(dr["Name"]);
a.Target = "Fix";
td.Controls.Add(a);
tr.Controls.Add(td);
td = new HtmlTableCell();
td.InnerText = DB.Str(dr["Unit"]);;
tr.Controls.Add(td);
td = new HtmlTableCell();
td.InnerText = DB.Str(dr["PurchaseUnit"]);;
tr.Controls.Add(td);
tblPurchaseUnits.Rows.Add(tr);
}
}
divPurchaseUnits.Visible = (cProblems > 0);
}
catch (Exception Ex)
{
Err Err = new Err(Ex, Sql);
Response.Write(Err.Html());
}
}
}