private void Report()
{
Debug.WriteLine("> Report");
lstIngred = new List <Ingred>();
hfBegDate.Value = dtBeg.ToString();
hfEndDate.Value = dtEnd.ToString();
ltlBegDate.Text = Fmt.Dt(dtBeg);
ltlEndDate.Text = Fmt.Dt(dtEnd);
string Sql = string.Format(
"Select\n" +
"j.JobRno, Coalesce(cu.Name, c.Name) as Customer, j.NumMenServing, NumWomenServing, NumChildServing, f.Qty, f.MenuItem,\n" +
"r.RecipeRno, r.Name As Recipe,\n" +
"r.NumServings, r.MenServingRatio, r.WomenServingRatio, r.ChildServingRatio,\n" +
"r.YieldQty, r.YieldUnitRno, (Select UnitSingle From Units Where UnitRno = r.YieldUnitRno) As YieldUnit,\n" +
"r.PortionQty, r.PortionUnitRno, (Select UnitSingle 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" +
"From mcJobs j\n" +
"Inner Join Contacts c On j.ContactRno = c.ContactRno\n" +
"Left Join Customers cu on c.CustomerRno = cu.CustomerRno\n" +
"Inner Join mcJobFood f On j.JobRno = f.JobRno\n" +
"Inner Join mcJobMenuItems m On f.MenuItemRno = m.MenuItemRno\n" +
"Inner Join Recipes r On m.RecipeRno = r.RecipeRno\n" +
"Inner join RecipeIngredXref x On r.RecipeRno = x.RecipeRno\n" +
"Where JobDate Between {0} And {1}\n" +
"And j.ProposalFlg = 0\n" +
"And j.CancelledDtTm Is Null\n" +
"Order By j.JobRno, f.MenuItem\n",
DB.PutDtTm(dtBeg),
DB.PutDtTm(dtEnd));
try
{
string Html = string.Empty;
int ID = 0;
//Response.Write(Sql + "<br/>");
DataTable dtRecipeIngredXref = db.DataTable(Sql);
if (dtRecipeIngredXref.Rows.Count > 0)
{
Ingred.LoadVendors();
Ingred.LoadIngredConversions();
Ingred.LoadIngredPurchases();
// find the ingredients for the recipes
Sql = string.Format(
"Select IngredRno, Name, StockedFlg, PrefVendors\n" +
"From Ingredients i\n" +
"Where IsNull(NonPurchaseFlg, 0) = 0\n" +
"and IsNull(HideFlg, 0) = 0");
//IngredRno In ({ 0})\n" +
//"And
//Str.Join(DB.StrArray(dtRecipeIngredXref, "IngredRno"), ","));
DataTable dt = db.DataTable(Sql);
//Response.Write(Sql + "<br/>");
// build a collection of ingredients
foreach (DataRow dr in dt.Rows)
{
AddIngredient(dr);
}
// add the number of servings and recipe quantities
foreach (DataRow dr in dtRecipeIngredXref.Rows)
{
int JobServings = DB.Int32(dr["Qty"]);
if (JobServings == 0)
{
JobServings = DB.Int32(dr["NumMenServing"]) + DB.Int32(dr["NumWomenServing"]) + DB.Int32(dr["NumChildServing"]);
}
int IngredRno = DB.Int32(dr["IngredRno"]);
int SubrecipeRno = DB.Int32(dr["SubrecipeRno"]);
decimal NumServings = DB.Dec(dr["NumServings"]);
// if an ingredient
if (IngredRno != 0)
{
AddServings(IngredRno, JobServings, DB.Int32(dr["JobRno"]), DB.Str(dr["Customer"]), JobServings, DB.Str(dr["MenuItem"]), DB.Int32(dr["RecipeRno"]), DB.Str(dr["Recipe"]), NumServings, DB.Dec(dr["UnitQty"]), DB.Int32(dr["UnitRno"]), DB.Str(dr["UnitSingle"]), DB.Str(dr["UnitPlural"]), string.Empty, null);
}
// if a subrecipe
if (SubrecipeRno != 0)
{
if (NumServings != 0)
{
int RecipeRno = DB.Int32(dr["RecipeRno"]);
AddSubrecipe(RecipeRno, SubrecipeRno, JobServings / NumServings, DB.Int32(dr["JobRno"]), DB.Str(dr["Customer"]), JobServings, DB.Str(dr["MenuItem"]), RecipeRno, DB.Str(dr["Recipe"]), DB.Dec(dr["UnitQty"]), DB.Int32(dr["UnitRno"]), DB.Str(dr["UnitSingle"]), DB.Str(dr["UnitPlural"]), string.Empty, new List <string>());
}
}
}
// sort by vendor, ingredient
lstIngred.Sort(CompareIngredByName);
string PrevVendor = string.Empty;
//Html += ReportHeader();
foreach (Ingred CurrIngred in lstIngred)
{
ID++;
string sID = ID.ToString();
CurrIngred.FindPurchaseInfo();
CurrIngred.Note = string.Empty; // remove any "No prior receipt found" note
// replace stocked items quantity
if (CurrIngred.StockedFlg)
{
CurrIngred.AdjustStockedItemsQuantity(db);
}
/*
* if (CurrIngred.fFoundConversion)
* {
* Html += string.Format(
* "\t\t<tr>\n" +
* "\t\t\t<td><input id='hfIngredRno{0}' type='hidden' value='{1}'/><img src='Images/Box.gif'/></td>\n" +
* "\t\t\t<td class='Qty'>{2}</td>\n" +
* "\t\t\t<td>{3}{4}</td>\n" +
* "\t\t\t<td title='{11}' class='qtp Ingred'><a href='Ingredients.aspx?Rno={1}' target='_blank' tabindex='-1'>{5}</a></td>\n" +
* "\t\t\t<td><input id='txtQty{0}' type=text class='Qty' value='{6}'/></td>\n" +
* "\t\t\t<td><input id='txtUnitQty{0}' type=text class='Qty UnitQty' value='{7}'/></td>\n" +
* "\t\t\t<td><input id='hfUnitRno{0}' type=hidden value='{8}' class='UnitRno'/><input id='txtUnit{0}' type=text class='Unit' value='{9}'/></td>\n" +
* "\t\t\t<td>{10}</td>\n" +
* "\t\t</tr>\n",
* ID,
* CurrIngred.IngredRno,
* Str.ShowFract(CurrIngred.PurchaseQty * CurrIngred.PurchaseUnitQty, 2),
* string.Empty,
* (CurrIngred.PurchaseQty <= 1 ? CurrIngred.PurchaseUnitSingle : CurrIngred.PurchaseUnitPlural),
* 5 CurrIngred.Name.Replace("'", "'"),
* Math.Ceiling(CurrIngred.PurchaseQty),
* (CurrIngred.PurchaseUnitQty != 1 ? CurrIngred.PurchaseUnitQty.ToString() : string.Empty),
* CurrIngred.PurchaseUnitRno,
* (CurrIngred.PurchaseQty <= 1 ? CurrIngred.PurchaseUnitSingle : CurrIngred.PurchaseUnitPlural),
* 10 CurrIngred.Vendor,
* string.Empty,
* CurrIngred.AddlInfo);
* }
* else
* {
* Html += string.Format(
* "\t\t<tr>\n" +
* "\t\t\t<td><input id='hfIngredRno{0}' type='hidden' value='{1}'/><img src='Images/Box.gif'/></td>\n" +
* "\t\t\t<td class='Qty'>{2}</td>\n" +
* "\t\t\t<td>{3}{4}</td>\n" +
* "\t\t\t<td title='{11}' class='qtp'>{5}</td>\n" +
* "\t\t\t<td><input id='txtQty{0}' type=text class='Qty' value='{6}'/></td>\n" +
* "\t\t\t<td><input id='txtUnitQty{0}' type=text class='Qty UnitQty' value='{7}'/></td>\n" +
* "\t\t\t<td><input id='hfUnitRno{0}' type=hidden value='{8}' class='UnitRno'/><input id='txtUnit{0}' type=text class='Unit' value='{9}'/></td>\n" +
* "\t\t\t<td>{10}</td>\n" +
* "\t\t</tr>\n",
* ID,
* CurrIngred.IngredRno,
* Str.ShowFract(CurrIngred.Qty, 2),
* string.Empty,
* (CurrIngred.Qty <= 1 ? CurrIngred.UnitSingle : CurrIngred.UnitPlural),
* CurrIngred.Name.Replace("'", "'"),
* "?",
* (CurrIngred.PurchaseUnitQty != 1 ? CurrIngred.PurchaseUnitQty + " " : string.Empty),
* CurrIngred.PurchaseUnitRno,
* (CurrIngred.PurchaseQty <= 1 ? CurrIngred.PurchaseUnitSingle : CurrIngred.PurchaseUnitPlural),
* CurrIngred.Vendor,
* string.Empty,
* CurrIngred.AddlInfo);
*
* if (CurrIngred.PurchaseUnitRno != 0)
* {
* CurrIngred.AddNote(
* string.Format(
* "<a href='Ingredients.aspx?Rno={2}' target='_blank' tabindex='-1'><i class=\"icon-warning-sign\"></i> Need conversion from {0} to {1}.</a>",
* CurrIngred.UnitPlural,
* CurrIngred.PurchaseUnitPlural,
* CurrIngred.IngredRno));
* }
* }
*
* if (CurrIngred.Note.Length > 0)
* {
* Html += string.Format(
* "<tr><td colspan='9' class='Notes'><ul>{0}</ul></td></tr>\n",
* CurrIngred.Note);
* }
*/
TableRow tr = new TableRow();
tblReport.Rows.Add(tr);
TableCell tc = new TableCell();
tr.Cells.Add(tc);
tc.Controls.Add(new HiddenField()
{
ID = "hfIngredRno" + sID, Value = CurrIngred.IngredRno.ToString()
});
tc.Controls.Add(new Image()
{
ImageUrl = "Images/Box.gif"
});
tr.Cells.Add(new TableCell()
{
CssClass = "Qty", Text = Str.ShowFract(CurrIngred.PurchaseQty * CurrIngred.PurchaseUnitQty, 2)
});
tr.Cells.Add(new TableCell()
{
Text = (CurrIngred.PurchaseQty <= 1 ? CurrIngred.PurchaseUnitSingle : CurrIngred.PurchaseUnitPlural)
});
string Name = CurrIngred.Name.Replace("'", "'");
if (CurrIngred.fFoundConversion)
{
tc = new TableCell()
{
ToolTip = CurrIngred.AddlInfo, CssClass = "qtp Ingred"
};
tr.Cells.Add(tc);
tc.Controls.Add(new HyperLink()
{
NavigateUrl = "Ingredients.aspx?Rno=" + CurrIngred.IngredRno, Target = "_blank", TabIndex = -1, Text = Name
});
}
else
{
tr.Cells.Add(new TableCell()
{
ToolTip = CurrIngred.AddlInfo, CssClass = "qtp", Text = Name
});
}
tr.Cells.Add(new TableCell()
{
CssClass = "Qty", Text = Math.Ceiling(CurrIngred.PurchaseQty).ToString()
});
tr.Cells.Add(new TableCell()
{
CssClass = "UnitQty",
Text = string.Format("{0} {1}",
(CurrIngred.PurchaseUnitQty != 1 ? Math.Ceiling(CurrIngred.PurchaseUnitQty).ToString() : string.Empty),
(CurrIngred.PurchaseQty <= 1 ? CurrIngred.PurchaseUnitSingle : CurrIngred.PurchaseUnitPlural))
});
tc = new TableCell();
tr.Cells.Add(tc);
tc.Controls.Add(new TextBox()
{
ID = "txtQty" + sID, CssClass = "Qty", Text = (CurrIngred.StockedPurchaseQty != 0 ? Math.Ceiling(CurrIngred.StockedPurchaseQty).ToString() : string.Empty)
});
if (!CurrIngred.fFoundConversion && CurrIngred.PurchaseUnitRno != 0 && CurrIngred.UnitPlural.Length > 0 && CurrIngred.PurchaseUnitPlural.Length > 0)
{
CurrIngred.AddNote(
string.Format(
"<a href='Ingredients.aspx?Rno={2}' target='_blank' tabindex='-1'><i class=\"icon-warning-sign\"></i> Need conversion from {0} to {1}.</a>",
CurrIngred.UnitPlural,
CurrIngred.PurchaseUnitPlural,
CurrIngred.IngredRno));
}
if (CurrIngred.Note.Length > 0)
{
//Html += string.Format(
// "<tr><td colspan='9' class='Notes'><ul>{0}</ul></td></tr>\n",
// CurrIngred.Note);
tr = new TableRow();
tblReport.Rows.Add(tr);
tr.Cells.Add(new TableCell()
{
ColumnSpan = 8, CssClass = "Notes", Text = string.Format("<ul>{0}</ul>", CurrIngred.Note)
});
}
}
}
//Html += "</table>";
hfParmCount.Value = ID.ToString();
//ltlReport.Text = Html;
}
catch (Exception Ex)
{
Err Err = new Err(Ex, Sql);
Response.Write(Err.Html());
}
Debug.WriteLine("< Report");
}
private void Report()
{
lstIngred = new List <Ingred>();
hfBegDate.Value = dtBeg.ToString();
hfEndDate.Value = dtEnd.ToString();
ltlBegDate.Text = Fmt.Dt(dtBeg);
ltlEndDate.Text = Fmt.Dt(dtEnd);
string Sql = string.Format(
"Select\n" +
"j.JobRno, Coalesce(cu.Name, c.Name) as Customer, j.NumMenServing, NumWomenServing, NumChildServing, " +
"f.Qty, f.MenuItem, r.RecipeRno, r.Name As Recipe,\n" +
"r.NumServings, r.MenServingRatio, r.WomenServingRatio, r.ChildServingRatio,\n" +
"r.YieldQty, r.YieldUnitRno, (Select UnitSingle From Units Where UnitRno = r.YieldUnitRno) As YieldUnit,\n" +
"r.PortionQty, r.PortionUnitRno, (Select UnitSingle 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" +
"From mcJobs j\n" +
"Inner Join Contacts c On j.ContactRno = c.ContactRno\n" +
"Left Join Customers cu on c.CustomerRno = cu.CustomerRno\n" +
"Inner Join mcJobFood f On j.JobRno = f.JobRno\n" +
"Inner Join mcJobMenuItems m On f.MenuItemRno = m.MenuItemRno\n" +
"Inner Join Recipes r On m.RecipeRno = r.RecipeRno\n" +
"Inner join RecipeIngredXref x On r.RecipeRno = x.RecipeRno\n" +
"Where JobDate Between {0} And {1}\n" +
"And j.ProposalFlg = 0\n" +
"And j.CancelledDtTm Is Null\n" +
"Order By j.JobRno, f.MenuItem\n",
DB.PutDtTm(dtBeg),
DB.PutDtTm(dtEnd));
try
{
//Response.Write(Sql + "<br/>");
DataTable dtRecipeIngredXref = db.DataTable(Sql);
//Response.Write(dtRecipeIngredXref.Rows.Count + "<br/>");
if (dtRecipeIngredXref.Rows.Count > 0)
{
Ingred.LoadVendors();
Ingred.LoadIngredConversions();
Ingred.LoadIngredPurchases();
// find the ingredients for the recipes
Sql = string.Format(
"With IngredData As (\n" +
"\tSelect IngredRno, Name, IsNull(StockedFlg, 0) As StockedFlg, PrefVendors\n" +
//"\t(Select Top 1 v.VendorRno From VendorIngredXref x Inner Join Vendors v On x.VendorRno = v.VendorRno Where x.IngredRno = i.IngredRno) As VendorRno, " +
//"\t(Select Top 1 v.Name From VendorIngredXref x Inner Join Vendors v On x.VendorRno = v.VendorRno Where x.IngredRno = i.IngredRno) As Vendor " +
"\tFrom Ingredients i\n" +
"\tWhere (IngredRno In ({0}) Or IsNull(StockedFlg, 0) = 1 And IsNull(StockedPurchaseQty, 0) <> 0 And IsNull(HideFlg, 0) = 0)\n" +
"\tAnd IsNull(NonPurchaseFlg, 0) = 0\n" +
")\n" +
"Select * From IngredData {1}\n",
Str.Join(DB.StrArray(dtRecipeIngredXref, "IngredRno"), ","),
//(chkIncludeStocked.Checked ? string.Empty : "Where StockedFlg = 0\n"));
string.Empty);
DataTable dt = db.DataTable(Sql);
//Response.Write(Sql + "<br/>");
// build a collection of ingredients
foreach (DataRow dr in dt.Rows)
{
AddIngredient(dr);
}
// add the number of servings and recipe quantities
foreach (DataRow dr in dtRecipeIngredXref.Rows)
{
int JobServings = DB.Int32(dr["Qty"]);
if (JobServings == 0)
{
JobServings = DB.Int32(dr["NumMenServing"]) + DB.Int32(dr["NumWomenServing"]) + DB.Int32(dr["NumChildServing"]);
}
int IngredRno = DB.Int32(dr["IngredRno"]);
int SubrecipeRno = DB.Int32(dr["SubrecipeRno"]);
decimal NumServings = DB.Dec(dr["NumServings"]);
// if an ingredient
if (IngredRno != 0)
{
AddServings(IngredRno, JobServings, DB.Int32(dr["JobRno"]), DB.Str(dr["Customer"]), JobServings, DB.Str(dr["MenuItem"]), DB.Int32(dr["RecipeRno"]), DB.Str(dr["Recipe"]), NumServings, DB.Dec(dr["UnitQty"]), DB.Int32(dr["UnitRno"]), DB.Str(dr["UnitSingle"]), DB.Str(dr["UnitPlural"]), string.Empty, null);
}
// if a subrecipe
if (SubrecipeRno != 0)
{
if (NumServings != 0)
{
int RecipeRno = DB.Int32(dr["RecipeRno"]);
AddSubrecipe(RecipeRno, SubrecipeRno, JobServings / NumServings, DB.Int32(dr["JobRno"]), DB.Str(dr["Customer"]), JobServings, DB.Str(dr["MenuItem"]), RecipeRno, DB.Str(dr["Recipe"]), DB.Dec(dr["UnitQty"]), DB.Int32(dr["UnitRno"]), DB.Str(dr["UnitSingle"]), DB.Str(dr["UnitPlural"]), string.Empty, new List <string>());
}
}
}
// add any menu items that do not have a recipe
Sql = string.Format(
"Select j.JobRno, Coalesce(cu.Name, c.Name) as Customer, j.NumMenServing, NumWomenServing, NumChildServing, f.Qty, f.Category, f.MenuItem, f.MenuItemRno,\n" +
"u.UnitRno, u.UnitSingle, u.UnitPlural, m.AsIsFlg, m.RecipeRno\n" +
"From mcJobs j\n" +
"Inner Join Contacts c On j.ContactRno = c.ContactRno\n" +
"Left Join Customers cu on c.CustomerRno = cu.CustomerRno\n" +
"Inner Join mcJobFood f On j.JobRno = f.JobRno\n" +
"Inner Join mcJobMenuItems m On f.MenuItemRno = m.MenuItemRno\n" +
"Inner Join Units u on u.UnitSingle = 'ea'\n" +
"Where JobDate Between {0} And {1}\n" +
"And j.ProposalFlg = 0\n" +
"And j.CancelledDtTm Is Null\n" +
"And m.RecipeRno Is Null\n" +
"And m.MenuItem <> ''\n" +
"Order By j.JobRno, f.MenuItem",
DB.PutDtTm(dtBeg),
DB.PutDtTm(dtEnd));
dt = db.DataTable(Sql);
foreach (DataRow dr in dt.Rows)
{
int JobServings = DB.Int32(dr["Qty"]);
if (JobServings == 0)
{
JobServings = DB.Int32(dr["NumMenServing"]) + DB.Int32(dr["NumWomenServing"]) + DB.Int32(dr["NumChildServing"]);
}
string MenuItem = string.Format("{0} - {1}", DB.Str(dr["Category"]), DB.Str(dr["MenuItem"]));
Ingred NotIngred = null;
foreach (Ingred CurrIngred in lstIngred)
{
if (CurrIngred.IngredRno == 0 && CurrIngred.Name == MenuItem)
{
NotIngred = CurrIngred;
break;
}
}
if (NotIngred == null)
{
NotIngred = new Ingred(0);
NotIngred.Name = MenuItem;
if (!DB.Bool(dr["AsIsFlg"]) && DB.Int32(dr["RecipeRno"]) == 0)
{
NotIngred.AddNote(string.Format("Menu item needs a <a href=\"Recipes.aspx\" target=\"_blank\">recipe</a> or <a href=\"SetupMenuItems.aspx?Rno={0}\" target=\"_blank\">checked As Is.</a>", DB.Int32(dr["MenuItemRno"])));
}
lstIngred.Add(NotIngred);
}
NotIngred.AddQty(JobServings, DB.Int32(dr["JobRno"]), DB.Str(dr["Customer"]), JobServings, MenuItem, 0, MenuItem, 1, 1, DB.Int32(dr["UnitRno"]), DB.Str(dr["UnitSingle"]), DB.Str(dr["UnitPlural"]), string.Empty);
}
// sort by vendor, ingredient
lstIngred.Sort();
string PrevVendor = string.Empty;
int iCount = 0;
//Html += ReportHeader();
foreach (Ingred CurrIngred in lstIngred)
{
//if (CurrIngred.Vendor != PrevVendor)
//{
// Html += NewVendor(CurrIngred.Vendor, fFirst);
// fFirst = false;
// fPrevStockedFlg = false;
//}
//if (CurrIngred.StockedFlg != fPrevStockedFlg)
//{
// Html += "<tr><td colspan='8' class='Stocked'>Stock Items</td></tr>\n";
//}
CurrIngred.FindPurchaseInfo();
// replace stocked items quantity
if (CurrIngred.StockedFlg)
{
CurrIngred.AdjustStockedItemsQuantity(db);
}
decimal Qty = (CurrIngred.StockedFlg ? CurrIngred.StockedPurchaseQty : CurrIngred.PurchaseQty); // && CurrIngred.StockedPurchaseQty != 0
if (Qty == 0)
{
continue;
}
/*
* if (CurrIngred.fFoundConversion)
* {
* Html += string.Format(
* "\t\t<tr>\n" +
* "\t\t\t<td><img src='Images/Box.gif'/></td>\n" +
* "\t\t\t<td class='Qty'>{0}</td>\n" +
* "\t\t\t<td>{1}</td>\n" +
* "\t\t\t<td title='{7}' class='qtp Ingred'><a href='Ingredients.aspx?Rno={8}' target='_blank'>{2}</a></td>\n" +
* "\t\t\t<td class='Qty'>{3}</td>\n" +
* "\t\t\t<td>{4}{5}</td>\n" +
* "\t\t\t<td>{6}</td>\n" +
* "\t\t</tr>\n",
* Str.ShowFract(CurrIngred.PurchaseQty * CurrIngred.PurchaseUnitQty, 2),
* (CurrIngred.PurchaseQty <= 1 ? CurrIngred.PurchaseUnitSingle : CurrIngred.PurchaseUnitPlural),
* CurrIngred.Name.Replace("'", "'"),
* Math.Ceiling(CurrIngred.PurchaseQty),
* (CurrIngred.PurchaseUnitQty != 1 ? Math.Ceiling(CurrIngred.PurchaseUnitQty) + " " : string.Empty),
* (CurrIngred.PurchaseQty <= 1 ? CurrIngred.PurchaseUnitSingle : CurrIngred.PurchaseUnitPlural),
* CurrIngred.Vendor,
* CurrIngred.AddlInfo,
* CurrIngred.IngredRno);
* }
* else
* {
* Html += string.Format(
* "\t\t<tr>\n" +
* "\t\t\t<td><img src='Images/Box.gif'/></td>\n" +
* "\t\t\t<td class='Qty'>{0}</td>\n" +
* "\t\t\t<td>{1}</td>\n" +
* "\t\t\t<td title='{7}' class='qtp'>{3}</td>\n" +
* "\t\t\t<td class='Qty'>{3}</td>\n" +
* "\t\t\t<td>{4}{5}</td>\n" +
* "\t\t\t<td>{6}</td>\n" +
* "\t\t</tr>\n",
* Str.ShowFract(CurrIngred.Qty, 2),
* (CurrIngred.Qty <= 1 ? CurrIngred.UnitSingle : CurrIngred.UnitPlural),
* CurrIngred.Name.Replace("'", "'"),
* "?",
* (CurrIngred.PurchaseUnitQty != 1 ? Math.Ceiling(CurrIngred.PurchaseUnitQty) + " " : string.Empty),
* (CurrIngred.PurchaseQty <= 1 ? CurrIngred.PurchaseUnitSingle : CurrIngred.PurchaseUnitPlural),
* CurrIngred.Vendor,
* CurrIngred.AddlInfo);
*
* if (CurrIngred.PurchaseUnitRno != 0)
* {
* CurrIngred.AddNote(string.Format("<a href='Ingredients.aspx?Rno={2}' target='_blank'><i class=\"icon-warning-sign\"></i> Need conversion from {0} to {1}.</a>", CurrIngred.UnitPlural, CurrIngred.PurchaseUnitPlural, CurrIngred.IngredRno));
* }
* }
*
* if (CurrIngred.Note.Length > 0)
* {
* Html += string.Format(
* "<tr><td colspan='8' class='Notes'><ul>{0}</ul></td></tr>\n",
* CurrIngred.Note);
* }
*/
TableRow tr = new TableRow();
tblReport.Rows.Add(tr);
TableCell tc = new TableCell();
tr.Cells.Add(tc);
tc.Controls.Add(new Image()
{
ImageUrl = "Images/Box.gif"
});
tc.Controls.Add(new HiddenField()
{
ID = "hfIngredRno" + iCount, Value = CurrIngred.IngredRno.ToString()
});
tc.Controls.Add(new HiddenField()
{
ID = "hfStocked" + iCount, Value = CurrIngred.StockedFlg.ToString()
});
tc.Controls.Add(new HiddenField()
{
ID = "hfStockedQty" + iCount, Value = CurrIngred.StockedPurchaseQty.ToString()
});
tc.Controls.Add(new HiddenField()
{
ID = "hfQty" + iCount, Value = CurrIngred.PurchaseQty.ToString()
});
tc.Controls.Add(new HiddenField()
{
ID = "hfUnitQty" + iCount, Value = CurrIngred.PurchaseUnitQty.ToString()
});
tc.Controls.Add(new HiddenField()
{
ID = "hfUnitRno" + iCount, Value = CurrIngred.PurchaseUnitRno.ToString()
});
tc.Controls.Add(new HiddenField()
{
ID = "hfVendorRno" + iCount, Value = CurrIngred.VendorRno.ToString()
});
tr.Cells.Add(new TableCell()
{
CssClass = "Qty", Text = Str.ShowFract(CurrIngred.PurchaseQty * CurrIngred.PurchaseUnitQty, 2)
});
tr.Cells.Add(new TableCell()
{
Text = (CurrIngred.PurchaseQty <= 1 ? CurrIngred.PurchaseUnitSingle : CurrIngred.PurchaseUnitPlural)
});
string Name = CurrIngred.Name.Replace("'", "'");
if (CurrIngred.fFoundConversion)
{
tc = new TableCell()
{
ToolTip = CurrIngred.AddlInfo, CssClass = "qtp Ingred"
};
tr.Cells.Add(tc);
tc.Controls.Add(new HyperLink()
{
NavigateUrl = "Ingredients.aspx?Rno=" + CurrIngred.IngredRno, Target = "_blank", Text = Name
});
}
else
{
tr.Cells.Add(new TableCell()
{
ToolTip = CurrIngred.AddlInfo, CssClass = "qtp Ingred", Text = Name
});
}
tr.Cells.Add(new TableCell()
{
CssClass = "Qty", Text = Math.Ceiling(Qty).ToString()
});
tr.Cells.Add(new TableCell()
{
Text = string.Format("{0}{1}", (CurrIngred.PurchaseUnitQty != 1 ? Math.Ceiling(CurrIngred.PurchaseUnitQty).ToString() : string.Empty), (CurrIngred.PurchaseQty <= 1 ? CurrIngred.PurchaseUnitSingle : CurrIngred.PurchaseUnitPlural))
});
tr.Cells.Add(new TableCell()
{
CssClass = "Vendor", Text = CurrIngred.Vendor
});
if (!CurrIngred.fFoundConversion && CurrIngred.PurchaseUnitRno != 0)
{
CurrIngred.AddNote(
string.Format(
"<a href='Ingredients.aspx?Rno={2}' target='_blank' tabindex='-1'><i class=\"icon-warning-sign\"></i> Need conversion from {0} to {1}.</a>",
CurrIngred.UnitPlural,
CurrIngred.PurchaseUnitPlural,
CurrIngred.IngredRno));
}
if (CurrIngred.Note.Length > 0)
{
tr = new TableRow();
tblReport.Rows.Add(tr);
tr.Cells.Add(new TableCell()
{
ColumnSpan = 7, CssClass = "Notes", Text = string.Format("<ul>{0}</ul>", CurrIngred.Note)
});
}
iCount++;
//PrevVendor = CurrIngred.Vendor;
//fPrevStockedFlg = CurrIngred.StockedFlg;
}
hfParmCount.Value = iCount.ToString();
}
//Html += FinishVendor() + "<br /><br />";
//Html += "</table><br /><br />";
//ltlReport.Text = Html;
FindVendorInfo();
}
catch (Exception Ex)
{
Err Err = new Err(Ex, Sql);
Response.Write(Err.Html());
}
}
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 SaveDetails(int Rno)
{
// force a refresh of last purchase prices
Ingred.LoadIngredPurchases();
for (int iDetail = 1; iDetail <= cDetails; iDetail++)
{
bool fRemove = Parm.Bool("chkRemove" + iDetail);
int OrigIngredRno = Parm.Int("hfOrigIngredRno" + iDetail);
int IngredRno = Parm.Int("hfIngredRno" + iDetail);
if (!fRemove)
{
int PurchaseDetailRno = Parm.Int("hfPurchaseDetailRno" + iDetail);
bool fNewRec = (PurchaseDetailRno == 0);
bool fStockedFlg = Parm.Bool("hfStocked" + iDetail);
string Ingredient = Parm.Str("txtIngredient" + iDetail);
decimal PurchaseQty = Str.Fract(Parm.Str("txtPurchaseQty" + iDetail));
decimal UnitQty = Str.Fract(Parm.Str("txtUnitQty" + iDetail));
int UnitRno = Parm.Int("hfUnitRno" + iDetail);
decimal Price = (Parm.Dec("txtPrice" + iDetail));
DateTime Tm = DateTime.Now;
String Sql = "";
try
{
// if a new ingredient, create it
if (Ingredient.Length > 0)
{
if (IngredRno == 0)
{
// first off, lets see if there is an ingredient with this name, just in case there already is one, probably hidden
Sql = string.Format("Select IngredRno, HideFlg From Ingredients Where Name = {0}", DB.PutStr(Ingredient));
DataRow dr = db.DataRow(Sql);
if (dr != null)
{
// the ingredient does indeed exist
IngredRno = DB.Int32(dr["IngredRno"]);
bool fHide = DB.Bool(dr["HideFlg"]);
if (fHide)
{
// it is hidden, so now unhide it
Sql = string.Format("Update Ingredients Set HideFlg = 0 Where IngredRno = {0}", IngredRno);
db.Exec(Sql);
}
}
else
{
// the indgredient does indeed not exist, so create it
Sql = string.Format(
"Insert Into Ingredients (Name, StockedFlg, CreatedDtTm, CreatedUser) " +
"Values ({0}, {1}, GetDate(), {2});" +
"Select Scope_Identity()",
DB.PutStr(Ingredient),
DB.PutBool(fStockedFlg),
DB.PutStr(g.User));
IngredRno = db.SqlNum(Sql);
}
}
if (PurchaseDetailRno == 0)
{
Sql = string.Format(
"Insert Into PurchaseDetails (PurchaseRno, IngredRno, CreatedDtTm, CreatedUser) " +
"Values ({0}, {1}, GetDate(), {2});" +
"Select Scope_Identity()",
Rno,
IngredRno,
DB.PutStr(g.User));
PurchaseDetailRno = db.SqlNum(Sql);
}
Sql = string.Format(
"Update PurchaseDetails Set " +
"IngredRno = {1}, " +
"PurchaseQty = {2}, " +
"PurchaseUnitQty = {3}, " +
"PurchaseUnitRno = {4}, " +
"Price = {5}, " +
"UpdatedDtTm = GetDate(), " +
"UpdatedUser = {6} " +
"Where PurchaseDetailRno = {0}",
PurchaseDetailRno,
IngredRno,
PurchaseQty,
UnitQty,
UnitRno,
Price,
DB.PutStr(g.User));
db.Exec(Sql);
// update the ingredient prices
Ingred.UpdateWithLastPrice(IngredRno);
}
}
catch (Exception Ex)
{
Err Err = new Err(Ex, Sql);
Response.Write(Err.Html());
}
}
// if the deleted or changed, update the original ingredient prices
if (fRemove || IngredRno != OrigIngredRno)
{
Ingred.UpdateWithLastPrice(OrigIngredRno);
}
}
}