protected void SaveData() { int Rno = Str.Num(hfRno.Value); string Sql = string.Empty; try { Sql = string.Format( "Update Settings Set " + "BaseCostPct = {1}, " + "AsIsBaseCostPct = {2} " + "Where SettingRno = {0}", Rno, Str.Dec(txtBaseCostPct.Text), Str.Dec(txtAsIsBaseCostPct.Text)); db.Exec(Sql); // recalculate the menu item price after changing the default cost percentages Sql = "Update mcJobMenuItems Set ServingPrice = r.PortionPrice / (IsNull(r.BaseCostPct, (Select Top 1 Case When IsNull(r.MenuItemAsIsFlg, 0) = 1 Then AsIsBaseCostPct Else BaseCostPct End From Settings)) / 100) " + "From mcJobMenuItems m Inner Join Recipes r On m.RecipeRno = r.RecipeRno " + "Where r.BaseCostPct Is Null"; db.Exec(Sql); } catch (Exception Ex) { Err Err = new Err(Ex, Sql); Response.Write(Err.Html()); } }
private void ProcessReport() { decimal DiffPct = Str.Dec(txtDiffPct.Text); string Sql = "Update Settings Set QuoteDiffPct = " + DiffPct.ToString(); try { db.Exec(Sql); Sql = "Select m.MenuItemRno, m.Category, m.MenuItem, m.ServingQuote, m.ServingPrice, m.InaccuratePriceFlg " + "From mcJobMenuItems m " + "Inner Join mcJobMenuCategories c On m.Category = c.Category " + "Where IsNull(m.HideFlg, 0) = 0 " + "And IsNull(c.HideFlg, 0) = 0 " + "And (IsNull(m.ServingQuote, 0) = 0 Or IsNull(m.ServingPrice, 0) <> 0) " + "And MenuItem <> '' " + "Order By m.Category, m.MenuItem "; DataTable dt = db.DataTable(Sql); foreach (DataRow dr in dt.Rows) { decimal Quote = Math.Round(DB.Dec(dr["ServingQuote"]), 2); decimal Price = Math.Round(DB.Dec(dr["ServingPrice"]), 2); decimal Diff = (Price != 0 ? (Quote - Price) / Price * 100M : 0); if (Quote == 0 || Diff > DiffPct || -Diff > DiffPct) { HtmlTableRow tr = new HtmlTableRow(); HtmlTableCell td = new HtmlTableCell(); td.InnerText = DB.Str(dr["Category"]); tr.Controls.Add(td); td = new HtmlTableCell(); HtmlAnchor a = new HtmlAnchor(); a.InnerText = DB.Str(dr["MenuItem"]); a.HRef = string.Format("SetupMenuItems.aspx?Rno={0}", DB.Int32(dr["MenuItemRno"])); a.Target = "Fix"; td.Controls.Add(a); tr.Controls.Add(td); td = new HtmlTableCell(); td.InnerText = Fmt.Dollar(DB.Dec(dr["ServingQuote"])); td.Attributes.Add("class", "amt quote"); tr.Controls.Add(td); td = new HtmlTableCell(); td.InnerText = Fmt.Dollar(DB.Dec(dr["ServingPrice"])); td.Attributes.Add("class", "amt price"); tr.Controls.Add(td); td = new HtmlTableCell(); if (DB.Bool(dr["InaccuratePriceFlg"])) { HtmlGenericControl i = new HtmlGenericControl("i"); i.Attributes.Add("class", "icon-dollar"); i.Attributes.Add("title", "Cost and Price are inaccurate because there are ingredients with no price from receipts."); td.Controls.Add(i); } tr.Controls.Add(td); td = new HtmlTableCell(); td.InnerText = string.Format("{0:0}% {1}", Math.Abs(Diff), (Diff < 0 ? "under" : "over")); tr.Controls.Add(td); tblRpt.Rows.Add(tr); } } } catch (Exception Ex) { Err Err = new Err(Ex, Sql); Response.Write(Err.Html()); } }
public string FindPayment() { string Html = string.Empty; string Sql = string.Empty; try { if (txtPaymentAmt.Text.Length > 0) { decimal RptTotal = 0; Sql = string.Format( "Select Coalesce(cu.Name, c.Name) as Customer, *, 1 as Section " + "From mcJobs j " + "Inner Join Contacts c on j.ContactRno = c.ContactRno " + "Left Join Customers cu on c.CustomerRno = cu.CustomerRno " + "Where {0} = j.InvBalAmt And j.InvCreatedDtTm Is Not Null And j.JobDate >= DateAdd(day, -90, GetDate())\n" + "union\n" + "Select Coalesce(cu.Name, c.Name) as Customer, *, 2 as Section " + "From mcJobs j " + "Inner Join Contacts c on j.ContactRno = c.ContactRno " + "Left Join Customers cu on c.CustomerRno = cu.CustomerRno " + "Where {0} Between j.InvBalAmt And j.InvBalAmt + 0.25 * j.SubTotAmt And j.InvBalAmt > 0 And j.InvCreatedDtTm Is Not Null And j.JobDate >= DateAdd(day, -90, GetDate())\n" + "Order by Section, JobDate", Str.Dec(txtPaymentAmt.Text)); DataTable dt = db.DataTable(Sql); foreach (DataRow dr in dt.Rows) { InvoiceAmounts(dr); string Customer = DB.Str(dr["Customer"]); Html += string.Format( "<tr>\n" + "<td class=\"Right\"><a href=\"Invoice.aspx?JobRno={0}\" target=\"invoice\">{0}</a></td>\n" + "<td>{1}</td>\n" + "<td class=\"Center\">{2}</td>\n" + "<td>{3}</td>\n" + "<td class=\"Right\">{4}</td>\n" + "<td class=\"Right\">{5}</td>\n" + "<td class=\"Right\">{6}</td>\n" + "<td class=\"Right\">{7}</td>\n" + "<td class=\"Right\">{8}</td>\n" + "</tr>\n", DB.Int32(dr["JobRno"]), DB.Str(dr["Customer"]), Fmt.Dt(DB.DtTm(dr["JobDate"])), DB.Str(dr["Location"]), Fmt.Dollar(Subtotal), Fmt.Dollar(OtherTotal), Fmt.Dollar(SalesTaxTotal), Fmt.Dollar(Total), Fmt.Dollar(Balance)); RptTotal += Math.Round(Total, 2); Html += CCPayments(DB.Int32(dr["JobRno"])); } } } catch (Exception Ex) { Err Err = new Err(Ex, Sql); Response.Write(Err.Html()); } return(Html); }
protected void SaveData() { int Rno = Str.Num(txtRno.Value); string Category = txtCurrCategory.Value; string MenuItem = txtCurrMenuItem.Value; string Sql = string.Empty; try { // setup to check if menu items need to be merged Sql = string.Format("Select Count(*) From mcJobMenuItems Where Category = {0} And MenuItem = {1}", DB.PutStr(ddlCategory.Text), DB.PutStr(txtMenuItem.Text)); // if user has said yes to merging if (hfMerge.Value == "true") { Sql = string.Format( "Begin Transaction\n" + "Update mcJobFood Set Category = {2}, MenuItem = {3} Where Category = {0} And MenuItem = {1}\n" + "Delete From mcJobMenuItems Where Category = {0} And MenuItem = {1}\n" + "Commit Transaction", DB.PutStr(Category), DB.PutStr(MenuItem), DB.PutStr(ddlCategory.Text), DB.PutStr(txtMenuItem.Text)); //Response.Write("Sql " + Sql + "<br />"); db.Exec(Sql); fStartHere = true; StartHere = ddlCategory.Text + " - " + txtMenuItem.Text; hfMerge.Value = "false"; } else // if not a new record and the category or menu item name has changed and the new value already exists then ask if they intend to merge if ((Category.Length > 0 && Category != ddlCategory.Text || MenuItem.Length > 0 && MenuItem != txtMenuItem.Text) && db.SqlNum(Sql) > 0) { fMerging = true; MergeMenuItem = MenuItem; MergeIntoMenuItem = txtMenuItem.Text; MergeCategory = Category; MergeIntoCategory = ddlCategory.Text; Sql = string.Format( "Select Count(*) From mcJobFood Where Category = {0} And MenuItem = {1}", DB.PutStr(Category), DB.PutStr(MenuItem)); AffectedJobs = Fmt.Num(db.SqlNum(Sql)); } else // not merging, simple update { DateTime Tm = DateTime.Now; // if a new menu item if (Rno == 0) { // if the new menu item already exists if (db.SqlNum(Sql) > 0) { fError = true; ErrorMsg = string.Format("There is already a menu item in this category named <b>{0}</b>. Please choose another name.", txtMenuItem.Text); } else { Sql = string.Format( "Insert Into mcJobMenuItems (MenuItem, CategorySortOrder, CreatedDtTm, CreatedUser) Values " + "('temp', (Select Count(*) + 1 From mcJobMenuItems Where Category = {2} And (HideFlg = 0 Or HideFlg Is Null)), {0}, {1}); Select @@Identity", DB.PutDtTm(Tm), DB.PutStr(g.User), DB.PutStr(ddlCategory.Text)); Rno = db.SqlNum(Sql); Category = ddlCategory.Text; MenuItem = txtMenuItem.Text; } } // if there wasn't an error if (!fError) { Misc.SetAsIsData(Rno, chkAsIs.Checked); // update the values Sql = string.Format( "Begin Transaction\n" + "Update mcJobMenuItems Set " + "Category = {1}, " + "MenuItem = {2}, " + "ProposalMenuItem = {3}, " + "ServingQuote = {4}, " + "KitchenLocRno = {5}, " + "AsIsFlg = {6}, " + "MultSelFlg = {7}, " + "MultItems = {8}, " + "IngredSelFlg = {9}, " + "HideFlg = {10}, " + "UpdatedDtTm = {11}, " + "UpdatedUser = {12} " + "Where MenuItemRno = {0}", Rno, DB.PutStr(ddlCategory.Text), DB.PutStr(txtMenuItem.Text), DB.PutStr(txtProposal.Text), Str.Dec(txtServingQuote.Text), ddlLocation.SelectedValue, DB.PutBool(chkAsIs.Checked), DB.PutBool(chkMultiSelect.Checked), DB.PutStr(txtMultItems.Value), DB.PutBool(chkIngredSelect.Checked), DB.PutBool(chkHide.Checked), DB.PutDtTm(Tm), DB.PutStr(g.User)); // if changed, update other areas if (Category != ddlCategory.Text || MenuItem != txtMenuItem.Text) { Sql += string.Format( "Update mcJobFood Set Category = {2}, MenuItem = {3} Where Category = {0} And MenuItem = {1}\n", DB.PutStr(Category), DB.PutStr(MenuItem), DB.PutStr(ddlCategory.Text), DB.PutStr(txtMenuItem.Text)); } Sql += "Commit Transaction"; //Response.Write("Sql " + Sql + "<br />"); db.Exec(Sql); fStartHere = true; StartHere = ddlCategory.Text + " - " + txtMenuItem.Text; } } } catch (Exception Ex) { Err Err = new Err(Ex, Sql); Response.Write(Err.Html()); } }
protected void SaveData() { int Rno = Str.Num(hfRno.Value); string Sql = string.Empty; try { Sql = string.Format( "Update Settings Set " + "SubTotTaxPct = {1}, " + "ServiceSubTotPct = {2}, " + "ServiceTaxPct = {3}, " + "DeliverySubTotPct = {4}, " + "DeliveryTaxPct = {5}, " + "ChinaSubTotPct = {6}, " + "ChinaTaxPct = {7}, " + "AddServiceSubTotPct = {8}, " + "AddServiceTaxPct = {9}, " + "FuelTravelSubTotPct = {10}, " + "FuelTravelTaxPct = {11}, " + "FacilitySubTotPct = {12}, " + "FacilityTaxPct = {13}, " + "GratuitySubTotPct = {14}, " + "GratuityTaxPct = {15}, " + "RentalsSubTotPct = {16}, " + "RentalsTaxPct = {17}, " + "Adj1TaxPct = {18}, " + "Adj2TaxPct = {19}, " + "CCFeePct = {20}, " + "CCFeeTaxPct = {21} " + "Where SettingRno = {0}", Rno, Str.Dec(txtSubTotTaxPct.Text), Str.Dec(txtServiceSubTotPct.Text), Str.Dec(txtServiceTaxPct.Text), Str.Dec(txtDeliverySubTotPct.Text), Str.Dec(txtDeliveryTaxPct.Text), Str.Dec(txtChinaSubTotPct.Text), Str.Dec(txtChinaTaxPct.Text), Str.Dec(txtAddServiceSubTotPct.Text), Str.Dec(txtAddServiceTaxPct.Text), Str.Dec(txtFuelTravelSubTotPct.Text), Str.Dec(txtFuelTravelTaxPct.Text), Str.Dec(txtFacilitySubTotPct.Text), Str.Dec(txtFacilityTaxPct.Text), Str.Dec(txtGratuitySubTotPct.Text), Str.Dec(txtGratuityTaxPct.Text), Str.Dec(txtRentalsSubTotPct.Text), Str.Dec(txtRentalsTaxPct.Text), Str.Dec(txtAdj1TaxPct.Text), Str.Dec(txtAdj2TaxPct.Text), Str.Dec(txtCCFeePct.Text), Str.Dec(txtCCFeeTaxPct.Text)); db.Exec(Sql); } catch (Exception Ex) { Err Err = new Err(Ex, Sql); Response.Write(Err.Html()); } }
private void Scale() { string Sql = string.Empty; try { Sql = string.Format( "Select r.*, " + "(Select UnitSingle From Units Where UnitRno = r.YieldUnitRno) As YieldUnitSingle, " + "(Select UnitPlural From Units Where UnitRno = r.YieldUnitRno) As YieldUnitPlural, " + "(Select UnitSingle From Units Where UnitRno = r.PortionUnitRno) As PortionUnitSingle, " + "(Select UnitPlural From Units Where UnitRno = r.PortionUnitRno) As PortionUnitPlural, " + "(Select Top 1 BaseCostPct From Settings) As DefaultBaseCostPct " + "From Recipes r Where RecipeRno = {0}", RecipeRno); DataRow drRecipe = db.DataRow(Sql); if (drRecipe != null) { decimal RecipeServings = DB.Dec(drRecipe["NumServings"]); decimal ScaleServings = Str.Dec(txtServings.Text); decimal Multiplier = Str.Dec(txtMultiplier.Text); if (ScaleServings == 0) { ScaleServings = RecipeServings; } if (Multiplier == 0) { Multiplier = 1; } decimal Scaler = (RecipeServings != 0 ? ScaleServings / RecipeServings : Multiplier); decimal Yield = DB.Dec(drRecipe["YieldQty"]); decimal ServingSize = DB.Dec(drRecipe["PortionQty"]); decimal BaseCostPrice = DB.Dec(drRecipe["BaseCostPrice"]); decimal BaseCostPct = DB.Dec(drRecipe["BaseCostPct"]); bool fUseDefaultBaseCostPct = (drRecipe["BaseCostPct"] == DBNull.Value); decimal DefaultBaseCostPct = DB.Dec(drRecipe["DefaultBaseCostPct"]); if (fUseDefaultBaseCostPct) { BaseCostPct = DefaultBaseCostPct; } lblPrmRecipeName.Text = lblRecipeName.Text = DB.Str(drRecipe["Name"]); txtServings.Text = Fmt.Num(Scaler * RecipeServings, 3); txtMultiplier.Text = Fmt.Num(Scaler, 3); hfOrigServings.Value = RecipeServings.ToString(); tdServings.Text = Str.ShowFract(Scaler * RecipeServings); tdPrmYield.Text = tdYield.Text = Str.ShowFract(Scaler * Yield); tdPrmYieldUnit.Text = tdYieldUnit.Text = (Scaler * Yield <= 1 ? DB.Str(drRecipe["YieldUnitSingle"]) : DB.Str(drRecipe["YieldUnitPlural"])); tdPrmSize.Text = tdSize.Text = Str.ShowFract(ServingSize); tdPrmSizeUnit.Text = tdSizeUnit.Text = (ServingSize <= 1 ? DB.Str(drRecipe["PortionUnitSingle"]) : DB.Str(drRecipe["PortionUnitPlural"])); tdPrmServingCost.Text = tdServingCost.Text = Fmt.Dollar(RecipeServings != 0 ? BaseCostPrice / RecipeServings : 0); tdPrmRecipeCost.Text = tdRecipeCost.Text = Fmt.Dollar(Scaler * BaseCostPrice); tdPrmServingPrice.Text = tdServingPrice.Text = Fmt.Dollar(RecipeServings != 0 ? BaseCostPrice / RecipeServings : 0); tdPrmServingPrice.Text = tdServingPrice.Text = Fmt.Dollar(RecipeServings != 0 ? BaseCostPrice / RecipeServings / (BaseCostPct / 100) : 0); tdPrmRecipePrice.Text = tdRecipePrice.Text = Fmt.Dollar(Scaler * BaseCostPrice / (BaseCostPct / 100)); ltlDirections.Text = DB.Str(drRecipe["Instructions"]); liDirections.Visible = (ltlDirections.Text.Length > 0); ltlNotes.Text = DB.Str(drRecipe["IntNote"]); liNotes.Visible = (ltlNotes.Text.Length > 0); ltlSource.Text = DB.Str(drRecipe["Source"]); liSource.Visible = (ltlSource.Text.Length > 0); Sql = string.Format( "Select x.*, IsNull(i.Name, sr.Name) as Name, " + "(Select UnitSingle From Units Where UnitRno = x.UnitRno) As UnitSingle, " + "(Select UnitPlural From Units Where UnitRno = x.UnitRno) As UnitPlural " + "From RecipeIngredXref x Left Join Ingredients i On i.IngredRno = x.IngredRno " + "Left Join Recipes sr on sr.RecipeRno = x.SubrecipeRno " + "Where x.RecipeRno = {0} " + "Order By RecipeSeq", RecipeRno); DataTable dt = db.DataTable(Sql); foreach (DataRow dr in dt.Rows) { string Title = DB.Str(dr["Title"]); decimal Qty = Scaler * DB.Dec(dr["UnitQty"]); TableRow tr = new TableRow(); if (Title.Length > 0) { tr.Cells.Add(new TableCell() { Text = string.Format("<span class='Title'>{0}</span>", Title), ColumnSpan = 4 }); } else { tr.Cells.Add(new TableCell() { Text = Str.ShowFract(Qty) }); tr.Cells.Add(new TableCell() { Text = DB.Str(dr["Unit" + (Qty <= 1 ? "Single" : "Plural")]) }); string note = DB.Str(dr["Note"]); tr.Cells.Add(new TableCell() { Text = DB.Str(dr["Name"]) + (note.Length > 0 ? " - " + note : "") }); } tblIngredients.Rows.Add(tr); } } } catch (Exception Ex) { Err Err = new Err(Ex, Sql); Response.Write(Err.Html()); } }