// GET: Plato
public ActionResult Index()
{
Ingred objIngred = new Ingred();
List <Ingred> lista = objIngredNeg.findAll();
return(View(lista));
}
public void update(Ingred ing)
{
string update = "update ingred set nombrei='" + ing.nombreI + "', unidades='" + ing.unidades + "', almacen= '" + ing.almacen + "' where idIngred = '" + ing.idIngred + "'";
try
{
comando = new SqlCommand(update, objConexionDB.getCon());
objConexionDB.getCon().Open();
comando.ExecuteNonQuery();
}
catch (Exception error)
{
throw new Exception(error.Message);
}
finally
{
objConexionDB.getCon().Close();
objConexionDB.closeDB();
}
}
public void create(Ingred objIngred)
{
string create = "insert into ingred values('" + objIngred.nombreI + "','" + objIngred.unidades + "','" + objIngred.almacen + "')";
try
{
comando = new SqlCommand(create, objConexionDB.getCon());
objConexionDB.getCon().Open();
comando.ExecuteNonQuery();
}
catch (Exception error)
{
throw new Exception(error.Message);
}
finally
{
objConexionDB.getCon().Close();
objConexionDB.closeDB();
}
}
private void AddIngredient(DataRow dr)
{
Debug.WriteLine("> AddIngredient");
int VendorRno = 0;
int IngredRno = DB.Int32(dr["IngredRno"]);
bool fFound = false;
// check for stocked flag
bool fStockedFlag = DB.Bool(dr["StockedFlg"]);
if (fStockedFlag)
{
// see if the ingredient is already in the list
foreach (Ingred Ingred in lstIngred)
{
if (Ingred.IngredRno == IngredRno)
{
fFound = true;
}
}
if (!fFound)
{
// not in the ingredit list, so add it
string PrefVendors = DB.Str(dr["PrefVendors"]);
string[] aPrefVendors = PrefVendors.Split(new char[] { ',' });
if (aPrefVendors.Length > 1)
{
VendorRno = Str.Num(aPrefVendors[0]);
}
else
{
VendorRno = Ingred.LastPurchaseVendor(IngredRno);
}
lstIngred.Add(new Ingred(IngredRno, DB.Str(dr["Name"]), fStockedFlag, VendorRno, Ingred.VendorName(VendorRno)));
}
}
Debug.WriteLine("< AddIngredient");
}
private void AddServings(int IngredRno, decimal Scaler, int JobRno, string Customer, int JobServings, string MenuItem, int RecipeRno, string Recipe, decimal RecipeServings, decimal Qty, int UnitRno, string UnitSingle, string UnitPlural, string Subrecipe, List <string> Notes)
{
foreach (Ingred Ingred in lstIngred)
{
if (Ingred.IngredRno == IngredRno && (Ingred.UnitRno == 0 || Ingred.UnitRno == UnitRno))
{
Ingred.AddQty(Scaler, JobRno, Customer, JobServings, MenuItem, RecipeRno, Recipe, RecipeServings, Qty, UnitRno, UnitSingle, UnitPlural, Subrecipe);
if (Notes != null)
{
foreach (string Note in Notes)
{
if (Note.Length > 0)
{
Ingred.AddNote(Note);
}
}
}
}
}
}
public List <Ingred> findAll()
{
List <Ingred> listaIngred = new List <Ingred>();
string find = "select * from ingred";
comando = new SqlCommand(find, objConexionDB.getCon());
objConexionDB.getCon().Open();
reader = comando.ExecuteReader();
while (reader.Read())
{
Ingred objIngred = new Ingred();
objIngred.idIngred = Int32.Parse(reader[0].ToString());
objIngred.nombreI = reader[1].ToString();
objIngred.unidades = Convert.ToDecimal(reader[2].ToString());
objIngred.almacen = Convert.ToDecimal(reader[3].ToString());
listaIngred.Add(objIngred);
}
objConexionDB.getCon().Close();
objConexionDB.closeDB();
return(listaIngred);
}
public Ingred buscar(int id)
{
List <Categoria> listaCategoria = new List <Categoria>();
string find = "select * from ingred where idIngred = '" + id + "'";
comando = new SqlCommand(find, objConexionDB.getCon());
objConexionDB.getCon().Open();
reader = comando.ExecuteReader();
Ingred objIngred = new Ingred();
while (reader.Read())
{
objIngred.idIngred = Int32.Parse(reader[0].ToString());
objIngred.nombreI = reader[1].ToString();
objIngred.unidades = Convert.ToDecimal(reader[2].ToString());
objIngred.almacen = Convert.ToDecimal(reader[3].ToString());
}
objConexionDB.getCon().Close();
objConexionDB.closeDB();
return(objIngred);
}
protected void btnDelete_Click(object sender, EventArgs e)
{
int Rno = Str.Num(hfRno.Value);
string Sql = String.Format(
"Select IngredRno From PurchaseDetails Where PurchaseRno = {0}",
Rno);
try
{
// prep for recalculating the latest price for all the ingredients on this receipt that is being deleted
DataTable dt = db.DataTable(Sql);
// delete the receipt
Sql = String.Format(
"Delete From PurchaseDetails Where PurchaseRno = {0}; " +
"Delete From Purchases Where PurchaseRno = {0}; ",
Rno);
db.Exec(Sql);
// now recalc the latest prices
foreach (DataRow dr in dt.Rows)
{
int IngredRno = DB.Int32(dr["IngredRno"]);
Ingred.UpdateWithLastPrice(IngredRno);
}
LoadList();
ClearData();
LoadDetails(0);
}
catch (Exception Ex)
{
Err Err = new Err(Ex, Sql);
Response.Write(Err.Html());
}
}
protected void btnInitialize_Click(object sender, EventArgs e)
{
string Sql = "Select IngredRno From Ingredients Order By IngredRno";
try
{
DataTable dt = db.DataTable(Sql);
foreach (DataRow dr in dt.Rows)
{
int IngredRno = DB.Int32(dr["IngredRno"]);
Ingred.UpdateWithLastPrice(IngredRno);
Response.Write(IngredRno.ToString() + "<br/>");
}
Sql = "Update mcJobMenuItems Set ServingQuote = ServingPrice";
db.Exec(Sql);
}
catch (Exception Ex)
{
Err Err = new Err(Ex, Sql);
Response.Write(Err.Html());
}
}
public ActionResult Edit(int id, Ingred ing)
{
List <Ingred> lista = objIngredNeg.findAll();
if (string.IsNullOrEmpty(id.ToString()))
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
else
{
mensajeInicioRegistrar();
objIngredNeg.find(ing);
var ingred = objIngredNeg.buscar(id);
Ingred ingred2 = new Ingred
{
idIngred = ingred.idIngred,
nombreI = ingred.nombreI,
unidades = ingred.unidades,
almacen = ingred.almacen
};
objIngredNeg.update(ing);
return(View("Edit", ingred2));
}
}
public bool find(Ingred objIngred)
{
bool hayRegistros;
string find = "select * from ingred where idIngred='" + objIngred.idIngred + "'";
comando = new SqlCommand(find, objConexionDB.getCon());
objConexionDB.getCon().Open();
reader = comando.ExecuteReader();
hayRegistros = reader.Read();
if (hayRegistros)
{
objIngred.nombreI = reader[1].ToString();
objIngred.unidades = Convert.ToDecimal(reader[2].ToString());
objIngred.almacen = Convert.ToDecimal(reader[3].ToString());
}
else
{
//objCategoria.estado = 1;
}
objConexionDB.getCon().Close();
objConexionDB.closeDB();
return(hayRegistros);
}
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 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 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());
}
}
}
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);
}
}
}
public static void SetAsIsData(int MenuItemRno, bool fAsIs)
{
string Sql = string.Format("Select AsIsFlg From mcJobMenuItems Where MenuItemRno = {0}", MenuItemRno);
try
{
DB db = new DB();
bool fPrevAsIs = db.SqlBool(Sql);
// if not currently flagged as an As Is menu item, but will be now
if (!fPrevAsIs && fAsIs)
{
bool fUpdateWithLastPrice = false;
Sql = string.Format("Select MenuItem From mcJobMenuItems Where MenuItemRno = {0}", MenuItemRno);
string MenuItem = db.SqlStr(Sql);
// we need a recipe, ingredient and recipe ingredient xref for this as is menu item
// find or create a recipe for this as item
Sql = string.Format(
"Select RecipeRno From Recipes Where Name = {0} And MenuItemAsIsFlg = 1",
DB.PutStr(MenuItem));
int RecipeRno = db.SqlNum(Sql);
bool fNewRecipe = false;
if (RecipeRno == 0)
{
Sql = string.Format(
"Insert Into Recipes (Name, SubRecipeFlg, NumServings, YieldQty, YieldUnitRno, PortionQty, PortionUnitRno, MenuItemAsIsFlg, CreatedDtTm, CreatedUser) " +
"Values ({0}, 0, 1, 1, (Select UnitRno From Units Where UnitSingle = 'ea'), 1, (Select UnitRno From Units Where UnitSingle = 'ea'), 1, GetDate(), {1}); " +
"Select Scope_Identity()",
DB.PutStr(MenuItem),
DB.PutStr(g.User));
RecipeRno = db.SqlNum(Sql);
fNewRecipe = true;
}
// find similar menu items in other categories, and set their As Is flag and set their recipe pointer
Sql = string.Format(
"Update mcJobMenuItems Set AsIsFlg = 1, RecipeRno = {1} Where MenuItem In (Select MenuItem From mcJobMenuItems Where MenuItemRno = {0}) And RecipeRno Is Null",
MenuItemRno,
RecipeRno);
db.Exec(Sql);
// find or create an ingredient
Sql = string.Format(
"Select Top 1 IngredRno From Ingredients Where Name In (Select MenuItem From mcJobMenuItems Where MenuItemRno = {0})",
MenuItemRno);
int IngredRno = db.SqlNum(Sql);
if (IngredRno == 0) // create ingredient
{
Sql = string.Format(
"Insert Into Ingredients (Name, MenuItemAsIsFlg, HideFlg, CreatedDtTm, CreatedUser) " +
"Values ((Select MenuItem From mcJobMenuItems Where MenuItemRno = {0}), 1, 1, GetDate(), {1}); " +
"Select Scope_Identity()",
MenuItemRno,
DB.PutStr(g.User));
IngredRno = db.SqlNum(Sql);
}
else // found ingredient
{
Sql = string.Format(
"Update Ingredients Set MenuItemAsIsFlg = 1 Where IngredRno = {0}",
IngredRno);
db.Exec(Sql);
fUpdateWithLastPrice = true;
}
// if a new recipe, add the xref
if (fNewRecipe)
{
Sql = string.Format(
"Insert Into RecipeIngredXref (RecipeRno, RecipeSeq, IngredRno, UnitQty, UnitRno, CreatedDtTm, CreatedUser) " +
"Values ({0}, 1, {1}, 1, (Select UnitRno From Units Where UnitSingle = 'ea'), GetDate(), {2}); " +
"Select Scope_Identity()",
RecipeRno,
IngredRno,
DB.PutStr(g.User));
int RecipeIngredRno = db.SqlNum(Sql);
}
if (fUpdateWithLastPrice)
{
Ingred.UpdateWithLastPrice(IngredRno);
}
}
// if currently flagged as an As Is menu item, but will not be now
else if (fPrevAsIs && !fAsIs)
{
Sql = string.Format(
"Select RecipeRno From mcJobMenuItems Where MenuItemRno = {0}", MenuItemRno);
int RecipeRno = db.SqlNum(Sql);
if (RecipeRno > 0)
{
Sql = string.Format(
"Delete From Ingredients Where IngredRno in (Select IngredRno From RecipeIngredXref Where RecipeRno = {0}) And MenuItemAsIsFlg = 1 And (Select Count(*) From PurchaseDetails Where IngredRno = Ingredients.IngredRno) = 0; " +
"Update Ingredients Set MenuItemAsIsFlg = Null Where IngredRno in (Select IngredRno From RecipeIngredXref Where RecipeRno = {0}); " +
"Delete From RecipeIngredXref Where RecipeRno In (Select RecipeRno From Recipes Where RecipeRno = {0} And MenuItemAsIsFlg = 1); " +
"Delete From Recipes Where RecipeRno = {0} And MenuItemAsIsFlg = 1; ",
RecipeRno);
db.Exec(Sql);
Sql = string.Format(
"Update mcJobMenuItems Set AsIsFlg = 0, RecipeRno = Null, ServingPrice = Null Where RecipeRno = {0} And AsIsFlg = 1",
RecipeRno);
db.Exec(Sql);
}
}
db.Close();
}
catch (Exception Ex)
{
Err Err = new Err(Ex, Sql);
HttpContext.Current.Response.Write(Err.Html());
}
}
public void update(Ingred objIngred)
{
objIngredDao.update(objIngred);
return;
}
public ActionResult Create(Ingred objIngred)
{
mensajeInicioRegistrar();
objIngredNeg.create(objIngred);
return(View("Create"));
}
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 static int CompareIngredByName(Ingred A, Ingred B)
{
return(A.Name.CompareTo(B.Name));
}
public void create(Ingred objIngred)
{
objIngredDao.create(objIngred);
return;
}
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");
}
public bool find(Ingred objIngred)
{
return(objIngredDao.find(objIngred));
}
