public static bool ExportSalesToJsonAndMongoDb(DateTime fromDate, DateTime toDate) { var context = new MsSqlEntities(); var productIds = context.Sales .Where(s => s.Date >= fromDate && s.Date <= toDate) .Select(s => s.ProductId) .Distinct(); if (!productIds.Any()) { return false; } List<Report> reports = new List<Report>(); foreach (var productId in productIds) { var productName = context.Sales .Where(s => s.ProductId == productId && s.Date >= fromDate && s.Date <= toDate) .Select(s => s.Product.Name) .FirstOrDefault(); var vendorName = context.Sales .Where(s => s.ProductId == productId && s.Date >= fromDate && s.Date <= toDate) .Select(s => s.Product.Vendor.Name) .FirstOrDefault(); var quantity = context.Sales .Where(s => s.ProductId == productId && s.Date >= fromDate && s.Date <= toDate) .Sum(s => s.Quantity); var totalPrice = context.Sales .Where(s => s.ProductId == productId && s.Date >= fromDate && s.Date <= toDate) .Sum(s => s.Quantity * s.UnitPrice); Report newReport = new Report { ProductId = productId, ProductName = productName, VendorName = vendorName, TotalSoldQuantity = quantity, TotalIncomes = totalPrice }; reports.Add(newReport); } Json.GenerateJsonReports(reports); MongoDb.InsertReportsInDatabase(reports); return true; }
public IList<Sale> GetSales(MsSqlEntities context) { var sales = new List<Sale>(); this.ExtractZip(this.ZipPath, this.ZipName, DefaultZipPath); var reportsDirectories = Directory.GetDirectories(DefaultZipPath); foreach (var directory in reportsDirectories) { var files = Directory.GetFiles(directory); var salesDate = Path.GetFileName(directory); var date = DateTime.ParseExact(salesDate, DefaultDateFormat, CultureInfo.InvariantCulture); foreach (var file in files) { var sale = this.GetSaleFromXls(context, file, date); sales.AddRange(sale); } } if (Directory.Exists(DefaultZipPath)) { Directory.Delete(DefaultZipPath, true); } // context.Sales.AddRange(sales); // context.SaveChanges(); return sales; }
public static void DisplayMsSqlMenu() { bool sqlExit = false; while (true) { Console.WriteLine("\nMS SQL Options:"); Console.WriteLine("1) Display Aggregated Data:"); Console.WriteLine("2) Load Data from Oracle Database."); Console.WriteLine("3) Load Excel Reports from ZIP File"); Console.WriteLine("0) Exit."); Console.Write("\nPlease, select option: "); string sqlChoice = Console.ReadLine(); switch (sqlChoice) { case "0": sqlExit = true; break; case "1": Console.WriteLine("Selected option: " + sqlChoice); break; case "2": Console.WriteLine("Selected option: " + sqlChoice); CloneOracleDbToSql.Run(); break; case "3": Console.WriteLine("Selected option: " + sqlChoice); var context = new MsSqlEntities(); var selectedFile = OpenFile(); var fileName = ExtractFileName(selectedFile); var path = Path.GetDirectoryName(selectedFile); var data = new ExcelImport(path, fileName).GetSales(context); context.Sales.AddRange(data); context.SaveChanges(); break; default: Console.WriteLine("Invalid selection!"); break; } if (sqlExit) { break; } } }
public static bool GenerateXmlReports(DateTime fromDate, DateTime toDate) { var context = new MsSqlEntities(); var vendorsName = context.Vendors .Select(v => v.Name) .Distinct(); XElement rootElement = new XElement("sales"); foreach (var vendorName in vendorsName) { var salesByVendor = context.Sales .Where(s => s.Date >= fromDate && s.Date <= toDate && s.Vendor.Name == vendorName) .OrderBy(s => s.Date) .GroupBy(s => s.Date) .Select(group => new { Date = group.Key, TotalSum = group.Sum(s => s.Quantity * s.UnitPrice) }); XElement vendor = new XElement("sale"); vendor.SetAttributeValue("vendor", vendorName); foreach (var sale in salesByVendor) { XElement summary = new XElement("summary"); summary.SetAttributeValue("date", sale.Date.ToString("dd-MMM-yyyy", CultureInfo.InvariantCulture)); summary.SetAttributeValue("total-sum", sale.TotalSum); vendor.Add(summary); } rootElement.Add(vendor); } rootElement.Save("../../../Sales-by-Vendors-Report.xml"); return true; }
public static void ImportExpensesInDatabase(string fileName) { var context = new MsSqlEntities(); XDocument doc = XDocument.Load(fileName); var vendorsXml = doc.XPathSelectElements("expenses-by-month/vendor"); foreach (var vendorXml in vendorsXml) { string vendorName = vendorXml.Attribute("name").Value; var expensesXml = vendorXml.XPathSelectElements("expenses"); foreach (var expsenseXml in expensesXml) { DateTime month = DateTime.Parse(expsenseXml.Attribute("month").Value); decimal sum = decimal.Parse(expsenseXml.Value); Vendor vendor = context.Vendors .FirstOrDefault(v => v.Name == vendorName); if (vendor == null) { Vendor newVendor = new Vendor(); newVendor.Name = vendorName; context.Vendors.Add(newVendor); context.SaveChanges(); vendor = newVendor; } int vendorId = vendor.Id; Expense newExpense = new Expense(); newExpense.Date = month; newExpense.Sum = sum; newExpense.VendorId = vendorId; context.Expenses.Add(newExpense); } } context.SaveChanges(); }
public static bool GeneratePdfReports(DateTime fromDate, DateTime toDate) { var context = new MsSqlEntities(); var sales = context.Sales .Where(s => s.Date >= fromDate && s.Date <= toDate) .GroupBy(s => s.Date) .Select( s => new { s.Key, product = s.Select(p => new { p.Product.Name, p.Quantity, p.UnitPrice, location = p.Supermarket.Name, sum = p.Quantity * p.UnitPrice }) }); if (sales.Any()) { using (FileStream fs = new FileStream("../../../sales-reports.pdf", FileMode.Create)) { Rectangle rec = new Rectangle(PageSize.A4); Document doc = new Document(rec); PdfWriter writer = PdfWriter.GetInstance(doc, fs); doc.Open(); PdfPTable table = new PdfPTable(5); table.TotalWidth = 510f; table.LockedWidth = true; float[] widths = { 2f, 1f, 1f, 3f, 1f }; table.SetWidths(widths); table.HorizontalAlignment = 1; table.SpacingBefore = 20f; table.SpacingAfter = 30f; PdfPCell cell = new PdfPCell(new Phrase("Aggregated Sales Report")); cell.Colspan = 5; cell.HorizontalAlignment = 1; table.AddCell(cell); foreach (var product in sales) { PdfPCell date = new PdfPCell(new Phrase("Date: " + product.Key.ToString("dd-MMM-yyyy", CultureInfo.InvariantCulture))); date.Colspan = 5; date.HorizontalAlignment = 0; date.BackgroundColor = new BaseColor(235, 235, 235); table.AddCell(date); PdfPCell headerProduct = new PdfPCell(new Phrase("Product")); headerProduct.HorizontalAlignment = 1; headerProduct.BackgroundColor = new BaseColor(210, 210, 210); table.AddCell(headerProduct); PdfPCell headerQuantity = new PdfPCell(new Phrase("Quantity")); headerQuantity.HorizontalAlignment = 1; headerQuantity.BackgroundColor = new BaseColor(210, 210, 210); table.AddCell(headerQuantity); PdfPCell headerPrice = new PdfPCell(new Phrase("Unit Price")); headerPrice.HorizontalAlignment = 1; headerPrice.BackgroundColor = new BaseColor(210, 210, 210); table.AddCell(headerPrice); PdfPCell headerLocation = new PdfPCell(new Phrase("Location")); headerLocation.HorizontalAlignment = 1; headerLocation.BackgroundColor = new BaseColor(210, 210, 210); table.AddCell(headerLocation); PdfPCell headerSum = new PdfPCell(new Phrase("Sum")); headerSum.HorizontalAlignment = 1; headerSum.BackgroundColor = new BaseColor(210, 210, 210); table.AddCell(headerSum); foreach (var sale in product.product) { PdfPCell columnProduct = new PdfPCell(new Phrase(sale.Name)); date.HorizontalAlignment = 1; table.AddCell(columnProduct); PdfPCell columnQuantity = new PdfPCell(new Phrase(sale.Quantity.ToString())); date.HorizontalAlignment = 1; table.AddCell(columnQuantity); PdfPCell columnPrice = new PdfPCell(new Phrase(sale.UnitPrice.ToString())); date.HorizontalAlignment = 1; table.AddCell(columnPrice); PdfPCell columnLocation = new PdfPCell(new Phrase(sale.location)); date.HorizontalAlignment = 1; table.AddCell(columnLocation); PdfPCell columnSum = new PdfPCell(new Phrase(sale.sum.ToString())); date.HorizontalAlignment = 1; table.AddCell(columnSum); } PdfPCell footerTitle = new PdfPCell( new Phrase("Total sum for " + product.Key.ToString("dd-MMM-yyyy", CultureInfo.InvariantCulture) + ": ")); footerTitle.Colspan = 4; footerTitle.HorizontalAlignment = 2; table.AddCell(footerTitle); PdfPCell footerTotal = new PdfPCell( new Phrase(product.product.Sum(p => p.Quantity * p.UnitPrice).ToString(CultureInfo.InvariantCulture))); footerTotal.HorizontalAlignment = 1; table.AddCell(footerTotal); } doc.Add(table); doc.Close(); } return true; } return false; }
private Supermarket CheckSupermarketExist(string supermarketName, MsSqlEntities context) { var supermarket = context.Supermarkets.FirstOrDefault(s => s.Name == supermarketName); if (supermarket == null) { // TODO: Add new supermarket from report. supermarket = new Supermarket { Name = supermarketName }; context.Supermarkets.Add(supermarket); context.SaveChanges(); } return supermarket; }
private IList<Sale> GetSaleFromXls(MsSqlEntities context, string file, DateTime date) { IList<Sale> sales = new List<Sale>(); var report = new Spreadsheet(); using (report) { report.LoadFromFile(file); Worksheet worksheet = report.Workbook.Worksheets.ByName("Sales"); var supermarketName = worksheet.Cell(WorksheetSettings.StartRow, WorksheetSettings.StartCell).Value.ToString(); string productName; Supermarket supermarket; Product product; int quantity; decimal sum; int currentRow = WorksheetSettings.FirstContentRow; supermarket = this.CheckSupermarketExist(supermarketName, context); string checkContent = worksheet.Cell(currentRow, WorksheetSettings.ProductCell).ValueAsString; while (checkContent != WorksheetSettings.EndRowContent) { productName = worksheet.Cell(currentRow, WorksheetSettings.ProductCell).ValueAsString; quantity = worksheet.Cell(currentRow, WorksheetSettings.QuantityCell).ValueAsInteger; sum = decimal.Parse(worksheet.Cell(currentRow, WorksheetSettings.ProductSumCell).ValueAsString); product = this.CheckValidProduct(productName, context); var sale = new Sale { Product = product, Supermarket = supermarket, Quantity = quantity, UnitPrice = product.Price, Sum = sum, Date = date, Vendor = product.Vendor }; sales.Add(sale); currentRow++; checkContent = worksheet.Cell(currentRow, WorksheetSettings.ProductCell).ValueAsString; } } return sales; }
private Product CheckValidProduct(string productName, MsSqlEntities context) { var product = context.Products.FirstOrDefault(p => p.Name == productName); if (product == null) { // TODO: Add new products from report fuctionality. product = new Product { Name = productName, CategoryId = 2, MeasureId = 2, Vendor = context.Vendors.Find(9), Price = 31.2m }; context.Products.Add(product); context.SaveChanges(); } return product; }