public static void GenerateProductReports(string folderName) { DirectoryInfo directory = Directory.CreateDirectory(folderName); List<MongoProductReport> mongoProductsReports = new List<MongoProductReport>(); MongoDBManager<MongoProductReport> mongo = new MongoDBManager<MongoProductReport>(); using (var msSQLServerContext = new SupermarketEntities()) { var sales = from product in msSQLServerContext.Products join vendor in msSQLServerContext.Vendors on product.VendorId equals vendor.VendorId join sale in msSQLServerContext.Sales on product.ProductId equals sale.ProductId group sale by sale.ProductId into productsById select new { ProductId = productsById.Key, SalesTotalQuantity = productsById.Sum(s => s.ProductQuantity), SalesTotalSum = productsById.Sum(s => s.ProductTotalSum) }; var productReports = from sale in sales join product in msSQLServerContext.Products on sale.ProductId equals product.ProductId join vendor in msSQLServerContext.Vendors on product.VendorId equals vendor.VendorId select new { ProductId = sale.ProductId, ProductName = product.ProductName, VendorName = vendor.VendorName, SalesTotalQuantity = sale.SalesTotalQuantity, SalesTotalSum = sale.SalesTotalSum }; foreach (var productReport in productReports) { JObject jsonObject = new JObject( new JProperty("product-id", productReport.ProductId), new JProperty("product-name", productReport.ProductName), new JProperty("vendor-name", productReport.VendorName), new JProperty("total-quantity-sold", productReport.SalesTotalQuantity), new JProperty("total-incomes", productReport.SalesTotalSum)); string filePath = Path.Combine(folderName, string.Format("{0}.json", productReport.ProductId)); using (FileStream file = File.Create(filePath)) { using (StreamWriter writer = new StreamWriter(file)) { writer.Write(jsonObject.ToString()); } } MongoProductReport mongoProductReport = new MongoProductReport { ProductId = productReport.ProductId, ProductName = productReport.ProductName, VendorName = productReport.VendorName, TotalQuantitySold = productReport.SalesTotalQuantity, TotalIncomes = productReport.SalesTotalSum }; mongoProductsReports.Add(mongoProductReport); } } mongo.InsertInMongoDB(mongoProductsReports, "ProductsReports"); }
/// <summary> /// Method for extracting expenses reports in XML format /// </summary> /// <param name="filePath">Filepath for the .xml file</param> public static void ReadVendorMonthlyExpenses(string filePath) { XmlReader reader = XmlReader.Create(filePath); List<MongoVendorExpense> mongoVendorExpensesList = new List<MongoVendorExpense>(); using (var msSQLServerContext = new SupermarketEntities()) { using (reader) { int vendorId = 0; while (reader.Read()) { //Insert a new vendor if (reader.NodeType == XmlNodeType.Element && reader.Name == "sale") { string vendorName = reader.GetAttribute("vendor"); var vendor = msSQLServerContext.Vendors.FirstOrDefault(v => v.VendorName == vendorName); if (vendor == null) { // the vendor doesn't exist vendor = new Vendor { VendorName = vendorName }; msSQLServerContext.Vendors.Add(vendor); msSQLServerContext.SaveChanges(); } vendorId = vendor.VendorId; } //Insert Expenses if (reader.NodeType == XmlNodeType.Element && reader.Name == "expenses") { string month = reader.GetAttribute("month"); DateTime monthAsDate = DateTime.ParseExact(month, "MMM-yyyy", CultureInfo.InvariantCulture); decimal amount = reader.ReadElementContentAsDecimal(); var vendorMonthlyExpense = new VendorExpense { VendorId = vendorId, Month = monthAsDate.Month, Year = monthAsDate.Year, Amount = amount }; var mongoVendorExpense = new MongoVendorExpense { VendorId = vendorId, Month = monthAsDate.Month, Year = monthAsDate.Year, Amount = amount }; mongoVendorExpensesList.Add(mongoVendorExpense); msSQLServerContext.VendorExpenses.Add(vendorMonthlyExpense); } } } MongoDBManager<MongoVendorExpense> mongoDBInserter = new MongoDBManager<MongoVendorExpense>(); mongoDBInserter.InsertInMongoDB(mongoVendorExpensesList, "VendorExpenses"); msSQLServerContext.SaveChanges(); } }