public static void Execute() { string topPath = @"../../../../WritingFiles/"; string jsonPath = @"../../../../WritingFiles/JSON/"; Directory.Delete(topPath, true); Directory.CreateDirectory(topPath); Directory.CreateDirectory(jsonPath); SuperMarketEntities dbContext = new SuperMarketEntities(); using (dbContext) { dbContext.Database.ExecuteSqlCommand("DELETE FROM SalesReports"); dbContext.Database.ExecuteSqlCommand("DELETE FROM Locations"); dbContext.Database.ExecuteSqlCommand("DELETE FROM Products"); dbContext.Database.ExecuteSqlCommand("DELETE FROM VendorExpenses"); dbContext.Database.ExecuteSqlCommand("DELETE FROM Vendors"); dbContext.Database.ExecuteSqlCommand("DELETE FROM Measures"); dbContext.Database.ExecuteSqlCommand("DBCC CHECKIDENT (SalesReports, reseed, 0)"); dbContext.Database.ExecuteSqlCommand("DBCC CHECKIDENT (Locations, reseed, 0)"); dbContext.Database.ExecuteSqlCommand("DBCC CHECKIDENT (Products, reseed, 0)"); dbContext.Database.ExecuteSqlCommand("DBCC CHECKIDENT (VendorExpenses, reseed, 0)"); dbContext.Database.ExecuteSqlCommand("DBCC CHECKIDENT (Vendors, reseed, 0)"); dbContext.Database.ExecuteSqlCommand("DBCC CHECKIDENT (Measures, reseed, 0)"); dbContext.SaveChanges(); } }
public static void PopulateVendorExpensesInSQL() { SuperMarketEntities dbContext = new SuperMarketEntities(); List<VendorExpense> vendorExpenses = XMLDataTransefer.GetVendorExpenses(); using(dbContext) { VendorExpens currReport = null; foreach (var vendorExpnese in vendorExpenses) { for (int itemIdex = 0; itemIdex < vendorExpnese.MonthDates.Count; itemIdex++) { currReport = new VendorExpens(); currReport.VendorID = vendorExpnese.VendorID; currReport.MonthDate = vendorExpnese.MonthDates[itemIdex]; currReport.Expnenses = vendorExpnese.Expenses[itemIdex]; dbContext.VendorExpenses.Add(currReport); } } dbContext.SaveChanges(); } }
static DataSender() { dbContext = new SuperMarketEntities(); products = new List<ProductEntity>(); vendors = new List<VendorEntity>(); measures = new List<MeasureEntity>(); }
public static void Generate() { SuperMarketEntities dbContext = new SuperMarketEntities(); XElement xml = new XElement("sales"); using (dbContext) { foreach (var vendor in dbContext.Vendors) { XElement sales = new XElement("sale"); XAttribute currentVendor = new XAttribute("vendor", vendor.VendorName); sales.Add(currentVendor); var query = from s in dbContext.SalesReports join p in dbContext.Products on s.ProductID equals p.ProductID join v in dbContext.Vendors on p.VendorID equals v.VendorID where v.VendorName == vendor.VendorName group s by s.Date into d select new { date = d.Key, totalSum = d.Sum(x => x.Sum) }; foreach (var summary in query) { XElement currentSummary = new XElement("summary"); string formatDate = summary.date.ToString("dd-MMM-yyyy", new CultureInfo("en-US")); XAttribute currentDate = new XAttribute("date", formatDate); currentSummary.Add(currentDate); string formatTotalSum = summary.totalSum.ToString("F2", new CultureInfo("en-US")); XAttribute currentSum = new XAttribute("total-sum", formatTotalSum); currentSummary.Add(currentSum); sales.Add(currentSummary); } xml.Add(sales); } xml.Save("../../../../WritingFiles/Sales-by-Vendors-report.xml"); } }
public static void PopulateMeasures() { Measure currMeasure = null; using (DataSender.dbContext = new SuperMarketEntities()) { foreach (var measure in DataSender.measures) { currMeasure = new Measure(); currMeasure.MeasureID = measure.MeasureID; currMeasure.Name = measure.Name; dbContext.Measures.Add(currMeasure); dbContext.SaveChanges(); } } }
public static List<ProductReport> GetProductReports() { using (context = new SuperMarketEntities()) { var result = (from product in context.Products join vendor in context.Vendors on product.VendorID equals vendor.VendorID join saleReport in context.SalesReports on product.ProductID equals saleReport.ProductID select new { ProductId = product.ProductID, ProductName = product.ProductName, VendorName = vendor.VendorName, Income = saleReport.Sum, Quantity = saleReport.Quantity }).GroupBy(x => x.ProductId); foreach (var item in result) { ProductReport currReport = new ProductReport(); currReport.ProductId = item.First().ProductId; currReport.ProductName = item.First().ProductName; currReport.VendorName = item.First().VendorName; decimal totalIncome = 0; int totalQuantities = 0; foreach (var sp in item) { totalIncome = totalIncome + sp.Income; totalQuantities = totalQuantities + sp.Quantity; } currReport.TotalIncomes = totalIncome; currReport.TotalQuantitySold = totalQuantities; SQLReader.productReports.Add(currReport); } } return SQLReader.productReports; }
public static void PopulateProducts() { Product currProduct = null; using (DataSender.dbContext = new SuperMarketEntities()) { foreach (var product in DataSender.products) { currProduct = new Product(); currProduct.ProductID = product.ProductID; currProduct.ProductName = product.Name; currProduct.MeasureID = product.MeasureID; currProduct.BasePrice = product.BasePrice; currProduct.VendorID = product.VendorID; dbContext.Products.Add(currProduct); dbContext.SaveChanges(); } } }
public static void Generate() { StringBuilder html = new StringBuilder(); html.Append("<!DOCTYPE html><html><body><h1>Aggregated Sales Report</h1>"); SuperMarketEntities dbContext = new SuperMarketEntities(); using (dbContext) { var dateList = dbContext.SalesReports.Select(x => x.Date).Distinct(); foreach (var date in dateList) { html.Append(string.Format("<h4 style=\"color:#555\">Date: {0}</h4>", date.ToString("dd-MMM-yyyy", new CultureInfo("en-US")))); var products = dbContext.SalesReports. Join(dbContext.Products, (s => s.ProductID), (p => p.ProductID), (s, p) => new { Date = s.Date, Product = p.ProductName, Quantity = s.Quantity, MeasureID = p.MeasureID, Price = s.UnitPrice, LocationID = s.LocationID, Sum = s.Sum }); var measures = products.Join(dbContext.Measures, (sp => sp.MeasureID), (m => m.MeasureID), (sp, m) => new { Date = sp.Date, Product = sp.Product, Quantity = sp.Quantity, Measure = m.Name, Price = sp.Price, LocationID = sp.LocationID, Sum = sp.Sum }); var locations = measures.Join(dbContext.Locations, (spm => spm.LocationID), (l => l.LocationID), (spm, l) => new { Date = spm.Date, Product = spm.Product, Quantity = spm.Quantity, Measure = spm.Measure, Price = spm.Price, Location = l.Supermarket, Sum = spm.Sum }).Select(spml => spml). Where(spml => spml.Date == date); html.Append("<div><table border=\"1\"><tr bgcolor=\"#aaa\">" + "<th>Product</th><th>Quantity</th><th>Unit Price</th><th>Location</th><th>Sum</th></tr><tr>"); decimal totalSum = 0m; foreach (var item in locations) { html.Append(string.Format("<td>{0}</td><td>{1}</td><td>{2:F2}</td><td>{3}</td><td>{4:F2}</td></tr><tr>", item.Product, item.Quantity + " " + item.Measure, item.Price, item.Location.Replace('“', '"').Replace('–', '-').Replace('”', '"'), item.Sum)); totalSum += item.Sum; } html.Append(string.Format("<td colspan=\"4\">Total sum for {0}:</td><td>{1:F2}</td></tr><tr></table></div>", date.ToString("dd-MMM-yyyy", new CultureInfo("en-US")), totalSum)); } } html.Append("</body></html>"); Docs.Pdf.HtmlToPdf convertion = new Docs.Pdf.HtmlToPdf(); convertion.OpenHTML(html.ToString()); convertion.SavePDF(PDFGenerator.PDF_PATH); }
public static void PopulateVendors() { Vendor currVendor = null; using (DataSender.dbContext = new SuperMarketEntities()) { foreach (var vendor in DataSender.vendors) { currVendor = new Vendor(); currVendor.VendorID = vendor.VendorID; currVendor.VendorName = vendor.Name; dbContext.Vendors.Add(currVendor); dbContext.SaveChanges(); } } }
public static Dictionary<string, List<Tuple<string, decimal>>> GetVendorsAndProducts(DateTime checkDate) { Dictionary<string, List<Tuple<string, decimal>>> vendorAndProducts = new Dictionary<string, List<Tuple<string, decimal>>>(); using (var context = new SuperMarketEntities()) { var vendors = (from vendor in context.Vendors join product in context.Products on vendor.VendorID equals product.VendorID select new { VendorID = vendor.VendorID, ProductID = product.ProductID, VendorName = vendor.VendorName, ProductName = product.ProductName, }).GroupBy(x => x.VendorName); foreach (var vendor in vendors) { List<Tuple<string, decimal>> currVendorProducts = new List<Tuple<string, decimal>>(); foreach (var product in vendor) { var query = from s in context.SalesReports where s.ProductID == product.ProductID && s.Date.Month == checkDate.Month && s.Date.Year == checkDate.Year group s by new { s.Date.Month, s.Date.Year } into d select new { date = d.Key, totalSum = d.Sum(x => x.Sum) }; currVendorProducts.Add(new Tuple<string, decimal>(product.ProductName, query.First().totalSum)); } vendorAndProducts.Add(vendor.First().VendorName, currVendorProducts); } } return vendorAndProducts; }
private static Dictionary<string, int> GetVendorsNameIdPairs() { Dictionary<string, int> vendors = new Dictionary<string, int>(); SuperMarketEntities dbcontext = new SuperMarketEntities(); using (dbcontext) { foreach (var vendor in dbcontext.Vendors) { vendors.Add(vendor.VendorName, vendor.VendorID); } } return vendors; }
private static Dictionary<int, string> GetVendorsIdNamePairs() { Dictionary<int, string> vendors = new Dictionary<int, string>(); SuperMarketEntities dbcontext = new SuperMarketEntities(); using (dbcontext) { foreach (var vendor in dbcontext.Vendors) { vendors.Add(vendor.VendorID, vendor.VendorName); } } return vendors; }