public void Update(string onDate, string columnName, Decimal newValue) { System.Diagnostics.Debug.WriteLine(onDate + "" + columnName + "val:" + newValue); var sql = new Tools.OurSql(); sql.UpdateSales(columnName, onDate, newValue); }
public ActionResult StoreSalesReport() { var sql = new Tools.OurSql(); List <SalesReportModel> data = new List <SalesReportModel>(); var rdr = sql.Query("SELECT * " + "FROM Sales "); while (rdr.Read()) { data.Add(new SalesReportModel { OnDate = rdr.GetDateTime(0), FountainNet = rdr.GetDecimal(1), FountainGross = rdr.GetDecimal(2), BeerWineNet = rdr.GetDecimal(3), BeerWineGross = rdr.GetDecimal(4), SuppliesNet = rdr.GetDecimal(5), SuppliesGross = rdr.GetDecimal(6), CigPackNet = rdr.GetDecimal(7), CigPackGross = rdr.GetDecimal(8), CigCartonNet = rdr.GetDecimal(9), CigCartonGross = rdr.GetDecimal(10), LottoNet = rdr.GetDecimal(11), LotoGross = rdr.GetDecimal(12), PropaneNet = rdr.GetDecimal(13), PropaneGross = rdr.GetDecimal(14), PackBeverageNet = rdr.GetDecimal(15), PackBeverageGross = rdr.GetDecimal(16), CofeeNet = rdr.GetDecimal(17), CofeeGross = rdr.GetDecimal(18), PhoneCardNet = rdr.GetDecimal(19), PhoneCardGross = rdr.GetDecimal(20) }); } return(View(data)); }
public void ExportFuelSalesToExcel() { var sql = new Tools.OurSql(); List <FuelSalesViewModel> data = new List <FuelSalesViewModel>(); var rdr = sql.Query("SELECT OnDate, Dollars " + "FROM FuelSales "); while (rdr.Read()) { data.Add(new FuelSalesViewModel { OnDate = rdr.GetDateTime(0), Dollars = rdr.GetDecimal(1) }); } ExcelPackage pck = new ExcelPackage(); ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Report"); ws.Cells["A1"].Value = "Date"; ws.Cells["B1"].Value = "Dollars"; int rowstart = 2; foreach (var item in data) { ws.Cells[String.Format("A{0}", rowstart)].Value = item.OnDate.ToString(); ws.Cells[String.Format("B{0}", rowstart)].Value = item.Dollars; rowstart++; } ws.Cells["A:AZ"].AutoFitColumns(); Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment: filename=" + "ExcelReport.xlsx"); Response.BinaryWrite(pck.GetAsByteArray()); Response.End(); }
public ActionResult GetMonthlySalesData(string categoryName, string year) { List <SalesMonthModel> monthlySalesList = new List <SalesMonthModel>(); Decimal yearGross = 0;; Decimal yearNet = 0;; using (var sql = new Tools.OurSql()) { var rdr = sql.Query("SELECT date_format(OnDate, '%M %Y') AS Month, sum(" + categoryName + "Net) AS netMonthly, sum(" + categoryName + "Gross) AS grossMonthly " + "FROM Sales " + "WHERE YEAR(OnDate) >= '" + year + "' AND YEAR(OnDate) <= '" + year + "' " + "GROUP BY YEAR(OnDate), MONTH(OnDate)"); while (rdr.Read()) { //split date into month and year var monthYear = rdr.GetString(0).Split(' '); var monthSales = new SalesMonthModel() { month = monthYear[0], monthlyNet = rdr.GetDecimal(1), monthlyGross = rdr.GetDecimal(2) }; yearNet += rdr.GetDecimal(1); yearGross += rdr.GetDecimal(2); monthlySalesList.Add(monthSales); } } var yearSales = new SalesYearModel() { yearlyGross = yearGross, yearlyNet = yearNet }; return(Json(new { monthlySalesList, yearSales }, JsonRequestBehavior.AllowGet)); }
public ActionResult GetBusinessExp(string beginDate, string endDate) { using (var sql = new Tools.OurSql()) { List <BusinessExpViewModel> expDataList = new List <BusinessExpViewModel>(); var rdr = sql.Query("SELECT InvoiceNum, BusinessExpenses.VendorId, OnDate, DueDate, AccountNum, Amount , IFNULL(Name,''), IFNULL(Contact,''), IFNULL(Street,''), " + "IFNULL(City,''), IFNULL(State,''), IFNULL(Zip,0), IFNULL(Phone,0), IFNULL(Email,''), IFNULL(TaxId,0) " + "FROM BusinessExpenses " + "LEFT JOIN VendorInfo on BusinessExpenses.VendorId = VendorInfo.VendorId " + "WHERE OnDate >= '" + beginDate + "' AND OnDate <= '" + endDate + "'" + "ORDER BY InvoiceNum desc;"); while (rdr.Read()) { var expData = new BusinessExpViewModel() { InvoiceNum = rdr.GetInt32(0), VendorId = rdr.GetInt32(1), OnDate = rdr.GetString(2), DueDate = rdr.GetString(3), AccountNum = rdr.GetInt32(4), Amount = rdr.GetDecimal(5), VendorInfo = "Tax ID: " + rdr.GetInt32(14).ToString() + "\n" + rdr.GetString(6) + "\n" + rdr.GetString(7) + "\n" + rdr.GetString(8) + " " + rdr.GetString(9) + ", " + rdr.GetString(10) + " " + rdr.GetInt32(11).ToString() + "\n" + rdr.GetInt32(12).ToString() + "\n" + rdr.GetString(13) }; if (rdr.GetString(6) == "" || rdr.GetString(7) == "" || rdr.GetInt32(12) == 0) { expData.VendorInfo = "Vendor info not available"; } expDataList.Add(expData); } return(Json(new { expDataList }, JsonRequestBehavior.AllowGet)); } }
public ActionResult CStoreInventoryReport() { var sql = new Tools.OurSql(); Dictionary <int, string> catagory = new Dictionary <int, string>(); List <CStoreInventoryReportModel> data = new List <CStoreInventoryReportModel>(); var rdr = sql.Query("SELECT * " + "FROM CStoreInventoryCategory "); while (rdr.Read()) { catagory.Add(rdr.GetInt32(0), rdr.GetString(1)); } rdr.Close(); rdr = sql.Query("SELECT * " + "FROM CStoreInventory "); while (rdr.Read()) { string temp = ""; catagory.TryGetValue(rdr.GetInt32(1), out temp); data.Add(new CStoreInventoryReportModel { Id = rdr.GetInt32(0), Category = temp, Description = rdr.GetString(2), Stock = rdr.GetInt32(3), SalePrice = rdr.GetDecimal(5), ListPrice = rdr.GetDecimal(6) }); } return(View(data)); }
public void ExportCstoreSalesToExcel() { var sql = new Tools.OurSql(); Dictionary <int, string> catagory = new Dictionary <int, string>(); List <CStoreInventoryReportModel> data = new List <CStoreInventoryReportModel>(); var rdr = sql.Query("SELECT * " + "FROM CStoreInventoryCategory "); while (rdr.Read()) { catagory.Add(rdr.GetInt32(0), rdr.GetString(1)); } rdr.Close(); rdr = sql.Query("SELECT * " + "FROM CStoreInventory "); while (rdr.Read()) { string temp = ""; catagory.TryGetValue(rdr.GetInt32(1), out temp); data.Add(new CStoreInventoryReportModel { Id = rdr.GetInt32(0), Category = temp, Description = rdr.GetString(2), Stock = rdr.GetInt32(3), SalePrice = rdr.GetDecimal(5), ListPrice = rdr.GetDecimal(6) }); } ExcelPackage pck = new ExcelPackage(); ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Report"); ws.Cells["A1"].Value = "ID"; ws.Cells["B1"].Value = "Category"; ws.Cells["C1"].Value = "Description"; ws.Cells["D1"].Value = "Stock"; ws.Cells["E1"].Value = "Sale Price"; ws.Cells["F1"].Value = "List Price"; int rowstart = 2; foreach (var item in data) { ws.Cells[String.Format("A{0}", rowstart)].Value = item.Id; ws.Cells[String.Format("B{0}", rowstart)].Value = item.Category; ws.Cells[String.Format("C{0}", rowstart)].Value = item.Description.ToString(); ws.Cells[String.Format("D{0}", rowstart)].Value = item.Stock; ws.Cells[String.Format("E{0}", rowstart)].Value = item.SalePrice; ws.Cells[String.Format("F{0}", rowstart)].Value = item.ListPrice; rowstart++; } ws.Cells["A:AZ"].AutoFitColumns(); Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment: filename=" + "ExcelReport.xlsx"); Response.BinaryWrite(pck.GetAsByteArray()); Response.End(); }
public void Update(string InvoiceNum, string newValue, string colName) { // update values in to the databse using (var sql = new Tools.OurSql()) { var values = new Dictionary <string, string>(); values.Add(colName, newValue); sql.Update("BusinessExpenses", "InvoiceNum", InvoiceNum + "", values); } }
public ActionResult RestaurantInventoryReport() { var sql = new Tools.OurSql(); List <RestaurantInventoryViewModel> data = new List <RestaurantInventoryViewModel>(); var rdr = sql.Query("SELECT *" + "FROM RestaurantInventory "); while (rdr.Read()) { data.Add(new RestaurantInventoryViewModel { Id = rdr.GetInt32(0), Item = rdr.GetString(1), Cost = rdr.GetDecimal(2), SoldFor = rdr.GetDecimal(3), AmountSold = rdr.GetInt32(4) }); } return(View(data)); }
public ActionResult FuelSalesReport() { var sql = new Tools.OurSql(); List <FuelSalesViewModel> data = new List <FuelSalesViewModel>(); var rdr = sql.Query("SELECT OnDate, Dollars " + "FROM FuelSales "); while (rdr.Read()) { data.Add(new FuelSalesViewModel { OnDate = rdr.GetDateTime(0), Dollars = rdr.GetDecimal(1) }); } return(View(data)); }
public ActionResult BusinessExpensesReport() { var sql = new Tools.OurSql(); List <BusinessExpViewModel> data = new List <BusinessExpViewModel>(); var rdr = sql.Query("SELECT *" + "FROM BusinessExpenses"); while (rdr.Read()) { data.Add(new BusinessExpViewModel { InvoiceNum = rdr.GetInt32(0), VendorId = rdr.GetInt32(1), OnDate = rdr.GetString(2), DueDate = rdr.GetString(3), AccountNum = rdr.GetInt32(4), Amount = rdr.GetInt32(5) }); } return(View(data)); }
private void Insert(RestaurantInventoryViewModel model) { // insert a new value into the database using (var sql = new Tools.OurSql()) { var values = new Dictionary <string, string> { { "Item", model.Item + "" }, { "Cost", model.Cost + "" }, { "SoldFor", model.SoldFor + "" }, { "AmountSold", model.AmountSold + "" } }; sql.Insert("RestaurantInventory", values); } }
public void Insert(BusinessExpViewModel model) { // insert a new value into the database using (var sql = new Tools.OurSql()) { var values = new Dictionary <string, string> { { "VendorId", model.VendorId + "" }, { "OnDate", model.OnDate + "" }, { "DueDate", model.DueDate + "" }, { "AccountNum", model.AccountNum + "" }, { "Amount", model.Amount + "" } }; sql.Insert("BusinessExpenses", values); } }
private void Insert(CStoreInventoryChangeViewModel model) { // insert a new value into the database using (var sql = new Tools.OurSql()) { var values = new Dictionary <string, string> { { "Id", model.Id + "" }, { "Category", model.Category + "" }, { "Description", model.Description }, { "Stock", model.Stock + "" }, { "SalePrice", model.SalePrice + "" }, { "ListPrice", model.ListPrice + "" } }; sql.Insert("CStoreInventory", values); } }
private void Update(CStoreInventoryChangeViewModel model) { // update values in to the databse using (var sql = new Tools.OurSql()) { var values = new Dictionary <string, string> { { "Id", model.Id + "" }, { "Category", model.Category + "" }, { "Description", model.Description }, { "Stock", model.Stock + "" }, { "SalePrice", model.SalePrice + "" }, { "ListPrice", model.ListPrice + "" } }; sql.Update("CStoreInventory", "Id", model.OriginalId + "", values); } }
public void ExportBusinessExpensesToExcel() { var sql = new Tools.OurSql(); List <BusinessExpViewModel> data = new List <BusinessExpViewModel>(); var rdr = sql.Query("SELECT *" + "FROM BusinessExpenses"); while (rdr.Read()) { data.Add(new BusinessExpViewModel { InvoiceNum = rdr.GetInt32(0), VendorId = rdr.GetInt32(1), OnDate = rdr.GetString(2), DueDate = rdr.GetString(3), AccountNum = rdr.GetInt32(4), Amount = rdr.GetInt32(5) }); } ExcelPackage pck = new ExcelPackage(); ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Report"); ws.Cells["A1"].Value = "InvoiceNum"; ws.Cells["B1"].Value = "VendorID"; ws.Cells["C1"].Value = "OnDate"; ws.Cells["D1"].Value = "DueDate"; ws.Cells["E1"].Value = "AccountNum"; ws.Cells["F1"].Value = "Amount"; int rowstart = 2; foreach (var item in data) { ws.Cells[String.Format("A{0}", rowstart)].Value = item.InvoiceNum; ws.Cells[String.Format("B{0}", rowstart)].Value = item.VendorId; ws.Cells[String.Format("C{0}", rowstart)].Value = item.OnDate; ws.Cells[String.Format("D{0}", rowstart)].Value = item.DueDate; ws.Cells[String.Format("E{0}", rowstart)].Value = item.AccountNum; ws.Cells[String.Format("F{0}", rowstart)].Value = item.Amount; rowstart++; } ws.Cells["A:AZ"].AutoFitColumns(); Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment: filename=" + "ExcelReport.xlsx"); Response.BinaryWrite(pck.GetAsByteArray()); Response.End(); }
public ActionResult GetSalesData(string categoryName, string beginDate, string endDate) { using (var sql = new Tools.OurSql()) { List <SalesModel> salesDataList = new List <SalesModel>(); var rdr = sql.Query("SELECT date_format(OnDate, '%b %d, %Y') as OnDate, " + categoryName + "Gross, " + categoryName + "Net " + "FROM Sales " + "WHERE OnDate >= '" + beginDate + "' AND OnDate <= '" + endDate + "';"); while (rdr.Read()) { var salesData = new SalesModel() { dateString = rdr.GetString(0), gross = rdr.GetDecimal(1), net = rdr.GetDecimal(2) }; salesDataList.Add(salesData); } return(Json(new { salesDataList }, JsonRequestBehavior.AllowGet)); } }
public ActionResult GetPrices() { using (var sql = new Tools.OurSql()) { List <RestaruantPricesModels> priceList = new List <RestaruantPricesModels>(); var rdr = sql.Query(""); while (rdr.Read()) { var priceData = new RestaruantPricesModels() { FoodName = rdr.GetString(0), FoodCategory = rdr.GetString(1), FoodId = rdr.GetDecimal(2), CostPrice = rdr.GetDecimal(3), ListPrice = rdr.GetDecimal(4) }; priceList.Add(priceData); } return(Json(new { priceList }, JsonRequestBehavior.AllowGet)); } }
public ActionResult GetYearlySalesData(string categoryName) { List <SalesYearModel> yearlySalesList = new List <SalesYearModel>(); using (var sql = new Tools.OurSql()) { var rdr = sql.Query("SELECT date_format(OnDate, '%Y') AS YEAR, sum(" + categoryName + "Net) AS netYearly, sum(" + categoryName + "Gross) AS grossYearly " + "FROM Sales " + "GROUP BY YEAR(OnDate);"); while (rdr.Read()) { var yearlySales = new SalesYearModel() { year = rdr.GetString(0), yearlyNet = rdr.GetDecimal(1), yearlyGross = rdr.GetDecimal(2) }; yearlySalesList.Add(yearlySales); } } return(Json(new { yearlySalesList }, JsonRequestBehavior.AllowGet)); }
public ActionResult FuelInventoryReport() { var sql = new Tools.OurSql(); List <FuelInventoryViewModel> data = new List <FuelInventoryViewModel>(); var rdr = sql.Query("SELECT OnDate, FuelAmount " + "FROM FuelInventoryA "); while (rdr.Read()) { data.Add(new FuelInventoryViewModel { OnDate = rdr.GetDateTime(0), FuelAmount = rdr.GetDecimal(1) }); } rdr.Close(); rdr = sql.Query("SELECT OnDate, FuelAmount " + "FROM FuelInventoryB "); while (rdr.Read()) { data.Add(new FuelInventoryViewModel { OnDate = rdr.GetDateTime(0), FuelAmount = rdr.GetDecimal(1) }); } return(View(data)); }
public void ExportRestaurantInventoryToExcel() { var sql = new Tools.OurSql(); List <RestaurantInventoryViewModel> data = new List <RestaurantInventoryViewModel>(); var rdr = sql.Query("SELECT *" + "FROM RestaurantInventory "); while (rdr.Read()) { data.Add(new RestaurantInventoryViewModel { Id = rdr.GetInt32(0), Item = rdr.GetString(1), Cost = rdr.GetDecimal(2), SoldFor = rdr.GetDecimal(3), AmountSold = rdr.GetInt32(4) }); } ExcelPackage pck = new ExcelPackage(); ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Report"); ws.Cells["A1"].Value = "Id"; ws.Cells["B1"].Value = "Item"; ws.Cells["C1"].Value = "Cost"; ws.Cells["D1"].Value = "SoldFor"; ws.Cells["E1"].Value = "AmountSold"; int rowstart = 2; foreach (var item in data) { ws.Cells[String.Format("A{0}", rowstart)].Value = item.Id; ws.Cells[String.Format("B{0}", rowstart)].Value = item.Item; ws.Cells[String.Format("C{0}", rowstart)].Value = item.Cost; ws.Cells[String.Format("D{0}", rowstart)].Value = item.SoldFor; ws.Cells[String.Format("E{0}", rowstart)].Value = item.AmountSold; rowstart++; } ws.Cells["A:AZ"].AutoFitColumns(); Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment: filename=" + "ExcelReport.xlsx"); Response.BinaryWrite(pck.GetAsByteArray()); Response.End(); }
public void ExportStoreSalesToExcel() { var sql = new Tools.OurSql(); List <SalesReportModel> data = new List <SalesReportModel>(); var rdr = sql.Query("SELECT * " + "FROM Sales "); while (rdr.Read()) { data.Add(new SalesReportModel { OnDate = rdr.GetDateTime(0), FountainNet = rdr.GetDecimal(1), FountainGross = rdr.GetDecimal(2), BeerWineNet = rdr.GetDecimal(3), BeerWineGross = rdr.GetDecimal(4), SuppliesNet = rdr.GetDecimal(5), SuppliesGross = rdr.GetDecimal(6), CigPackNet = rdr.GetDecimal(7), CigPackGross = rdr.GetDecimal(8), CigCartonNet = rdr.GetDecimal(9), CigCartonGross = rdr.GetDecimal(10), LottoNet = rdr.GetDecimal(11), LotoGross = rdr.GetDecimal(12), PropaneNet = rdr.GetDecimal(13), PropaneGross = rdr.GetDecimal(14), PackBeverageNet = rdr.GetDecimal(15), PackBeverageGross = rdr.GetDecimal(16), CofeeNet = rdr.GetDecimal(17), CofeeGross = rdr.GetDecimal(18), PhoneCardNet = rdr.GetDecimal(19), PhoneCardGross = rdr.GetDecimal(20) }); } ExcelPackage pck = new ExcelPackage(); ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Report"); ws.Cells["A1"].Value = "Date"; ws.Cells["B1"].Value = "FountainNet"; ws.Cells["C1"].Value = "FountainGross"; ws.Cells["D1"].Value = "BeerWineNet"; ws.Cells["E1"].Value = "BeerWineGross"; ws.Cells["F1"].Value = "SuppliesNet"; ws.Cells["G1"].Value = "SuppliesGross"; ws.Cells["H1"].Value = "CigPcakNet"; ws.Cells["I1"].Value = "CigPackGross"; ws.Cells["J1"].Value = "CigCartonNet"; ws.Cells["K1"].Value = "CigCartonGross"; ws.Cells["L1"].Value = "LottoNet"; ws.Cells["M1"].Value = "LotoGross"; ws.Cells["N1"].Value = "PropaneNet"; ws.Cells["O1"].Value = "PropaneGross"; ws.Cells["P1"].Value = "PackBeverageNet"; ws.Cells["Q1"].Value = "PackBeverageGross"; ws.Cells["R1"].Value = "CofeeNet"; ws.Cells["S1"].Value = "CofeeGross"; ws.Cells["T1"].Value = "PhoneCardNet"; ws.Cells["U1"].Value = "PhoneCardGross"; int rowstart = 2; foreach (var item in data) { ws.Cells[String.Format("A{0}", rowstart)].Value = item.OnDate.ToString(); ws.Cells[String.Format("B{0}", rowstart)].Value = item.FountainNet; ws.Cells[String.Format("C{0}", rowstart)].Value = item.FountainGross; ws.Cells[String.Format("D{0}", rowstart)].Value = item.BeerWineNet; ws.Cells[String.Format("E{0}", rowstart)].Value = item.BeerWineGross; ws.Cells[String.Format("F{0}", rowstart)].Value = item.SuppliesNet; ws.Cells[String.Format("G{0}", rowstart)].Value = item.SuppliesGross; ws.Cells[String.Format("H{0}", rowstart)].Value = item.CigPackNet; ws.Cells[String.Format("I{0}", rowstart)].Value = item.CigPackGross; ws.Cells[String.Format("J{0}", rowstart)].Value = item.CigCartonNet; ws.Cells[String.Format("K{0}", rowstart)].Value = item.CigCartonGross; ws.Cells[String.Format("L{0}", rowstart)].Value = item.LottoNet; ws.Cells[String.Format("M{0}", rowstart)].Value = item.LotoGross; ws.Cells[String.Format("N{0}", rowstart)].Value = item.PropaneNet; ws.Cells[String.Format("O{0}", rowstart)].Value = item.PropaneGross; ws.Cells[String.Format("P{0}", rowstart)].Value = item.PackBeverageNet; ws.Cells[String.Format("Q{0}", rowstart)].Value = item.PackBeverageGross; ws.Cells[String.Format("R{0}", rowstart)].Value = item.CofeeNet; ws.Cells[String.Format("S{0}", rowstart)].Value = item.CofeeGross; ws.Cells[String.Format("T{0}", rowstart)].Value = item.PhoneCardNet; ws.Cells[String.Format("U{0}", rowstart)].Value = item.PhoneCardGross; rowstart++; } ws.Cells["A:AZ"].AutoFitColumns(); Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment: filename=" + "SalesReport.xlsx"); Response.BinaryWrite(pck.GetAsByteArray()); Response.End(); }