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