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