private void GetSales(string directory, BottleGreenEntities supermarketContext, IList<SalesReport> allSales) { foreach (var sale in GetSalesFromExcelFiles(directory, supermarketContext)) { allSales.Add(sale); } string[] subDirectories = Directory.GetDirectories(directory); if (subDirectories.Length > 0) { for (int i = 0; i < subDirectories.Length; i++) { GetSales(subDirectories[i], supermarketContext, allSales); } } }
public Dictionary<string, List<Summary>> ReadData(DateTime startDate, DateTime endDate) { using (var contextBg = new BottleGreenEntities()) { var salesResult = new Dictionary<string, List<Summary>>(); var sales = contextBg.SalesReports .Select(s => new { s.Product, s.Product.Vendor, s.ReportDate }); foreach (var sale in sales) { if (startDate <= sale.ReportDate && sale.ReportDate <= endDate) { var vendorName = sale.Product.Vendor.Vendor_Name; if (!salesResult.ContainsKey(vendorName)) { salesResult[vendorName] = new List<Summary>(); } var summary = new Summary(sale.ReportDate, sale.Product.Price); var summariesWithEqualDates = salesResult[vendorName] .Where(x => x.DateOfSale == summary.DateOfSale); if (summariesWithEqualDates.Count() == 0) { salesResult[vendorName].Add(summary); } else { summariesWithEqualDates .FirstOrDefault() .TotalSum += summary.TotalSum; } } } return salesResult; } }
public void MigrateDataFromExcelFiles() { ExtractZipFile(ReportsFilePath); using (var supermarketContext = new BottleGreenEntities()) { IList<SalesReport> allSales = new List<SalesReport>(); GetSales(TempFolderForExtract, supermarketContext, allSales); foreach (var sale in allSales) { supermarketContext.SalesReports.Add(sale); } supermarketContext.SaveChanges(); } Directory.Delete(TempFolderForExtract, true); }
public void GenerateJsonReports(DateTime start, DateTime end) { if (!Directory.Exists(@"../../../Json-Reports/")) { Directory.CreateDirectory(@"../../../Json-Reports/"); } using (var context = new BottleGreenEntities()) { var products = GetProducts(context, start, end); var serializer = new JavaScriptSerializer(); foreach (var product in products) { if (product.TotalQuantitySold == null) { product.TotalQuantitySold = 0; } if (product.TotalIncomes == null) { product.TotalIncomes = 0; } string path = "../../../Json-Reports/"; string dirName = String.Format("{0}.{1}.{2}-{3}.{4}.{5}/", start.Year, start.Month, start.Day, end.Year, end.Month, end.Day); path = path + dirName; if (!Directory.Exists(@path)) { Directory.CreateDirectory(@path); } var json = JsonConvert.SerializeObject(product, Formatting.Indented); path = path + string.Format("{0}.json", product.ProductId); System.IO.File.WriteAllText(path, json); // opens files in notepad var xmlReaderStr = "notepad.exe"; Process.Start(xmlReaderStr, path); } } }
public void UploadMongoReports(DateTime start, DateTime end) { var client = new MongoClient(); var db = client.GetDatabase("BottleGreen"); var collection = db.GetCollection<Report>("SalesByProductReports"); using (var context = new BottleGreenEntities()) { var reports = GetProducts(context, start, end); foreach (var report in reports) { if (report.TotalQuantitySold == null) { report.TotalQuantitySold = 0; } if (report.TotalIncomes == null) { report.TotalIncomes = 0; } collection.InsertOneAsync(report); } } }
public static void PolulateSqlTables(string filePath) { XmlDocument xmlDoc = new XmlDocument(); xmlDoc.Load(filePath); XmlElement root = xmlDoc.DocumentElement; XmlNodeList xmlVendors = root.SelectNodes("/expenses-by-month/vendor"); using (var context = new BottleGreenEntities()) { foreach (XmlNode vendor in xmlVendors) { var vendorName = vendor.Attributes["name"].Value; XmlNodeList expenses = vendor.SelectNodes("expenses"); for (int i = 0; i < expenses.Count; i++) { var expense = expenses[i]; var expenceMonth = expense.Attributes["month"].Value; var expenceDate = DateTime.Parse(expenceMonth); var expenceSum = decimal.Parse(expenses[i].InnerText); var vendorId = context.Vendors.FirstOrDefault(v => v.Vendor_Name == vendorName).ID; var expenceEntity = new ExpensesByMonth() { ExpenseMonth = expenceDate, Expenses = expenceSum, VendorId = vendorId }; context.ExpensesByMonths.Add(expenceEntity); } } context.SaveChanges(); } }
private static IQueryable<Report> GetProducts(BottleGreenEntities context, DateTime start, DateTime end) { var products = from p in context.Products where p.SalesReports.Any(r=>r.ReportDate <= end) && p.SalesReports.Any(r=>r.ReportDate >= start) select new Report() { ProductId = p.ID, ProductName = p.Product_Name, VendorName = p.Vendor.Vendor_Name, TotalQuantitySold = p.SalesReports.Select(s => s.Quantity).Sum(), TotalIncomes = p.SalesReports.Select(s => s.Quantity).Sum()*p.Price, Start = start, End = end }; return products; }
private ICollection<SalesReport> GetSalesFromExcelFiles(string directory, BottleGreenEntities supermarketContext) { IList<SalesReport> sales = new List<SalesReport>(); string[] excelFilesPaths = Directory.GetFiles(directory, "*.xls"); foreach (var excelFilePath in excelFilesPaths) { string excelConnectionString = string.Format(Settings.Default.ExcelReadConnectionString, excelFilePath); OleDbConnection excelConnection = new OleDbConnection(excelConnectionString); excelConnection.Open(); DataSet dataSet = new DataSet(); using (excelConnection) { string selectAllRowsCommandString = "SELECT * FROM [Sales$]"; OleDbCommand selectAllRowsCommand = new OleDbCommand(selectAllRowsCommandString, excelConnection); OleDbDataAdapter excelAdapter = new OleDbDataAdapter(selectAllRowsCommand); excelAdapter.Fill(dataSet, "SalesReports"); } DataRowCollection excelRows = dataSet.Tables["SalesReports"].Rows; string supermarketName = excelRows[0][0].ToString(); if (!supermarketContext.Supermarkets.Any(s => s.Name == supermarketName)) { supermarketContext.Supermarkets.Add(new Supermarket() { Name = supermarketName }); supermarketContext.SaveChanges(); } for (int i = 2; i < excelRows.Count - 1; i++) { string productName = excelRows[i][0].ToString(); var product = supermarketContext.Products.FirstOrDefault(p => p.Product_Name == productName); if (product != null) { int productID = product.ID; int quantity = 0; int.TryParse(excelRows[i][1].ToString(), out quantity); decimal unitPrice = 0; decimal.TryParse(excelRows[i][2].ToString(), out unitPrice); int supermarketID = supermarketContext.Supermarkets.First(s => s.Name == supermarketName).Id; string saleDateString = Path.GetFileName(Path.GetDirectoryName(excelFilePath)); sales.Add(new SalesReport() { ReportDate = DateTime.Parse(saleDateString), Product = supermarketContext.Products.Find(productID), Quantity = quantity, Supermarket = supermarketContext.Supermarkets.Find(supermarketID), ActualPrice = unitPrice }); } else { // todo add new product //supermarketContext.Products.Add(new Product() //{ // Product_Name = productName, // VendorID = 1, // Measure = new Measure(), // Price = 1 //}); } } } return sales; }