private static void WriteSalesToWriter(XmlWriter writer, DateTime startDate, DateTime endDate) { using (var db = new SupermarketsChainEntities()) { foreach (var vendor in db.Vendors.ToList()) { var totalSalesSumByDate = db.Sales .Where(sale => sale.Product.VendorId == vendor.Id && sale.DateOfSale >= startDate && sale.DateOfSale <= endDate) .OrderBy(sale => sale.DateOfSale) .GroupBy(sale => sale.DateOfSale) .Select(group => new { Date = group.Key, TotalSum = group.Sum(g => g.PricePerUnit * g.Quantity) }) .ToList(); if (totalSalesSumByDate.Any()) { writer.WriteStartElement("sale"); writer.WriteAttributeString("vendor", vendor.Name); foreach (var sale in totalSalesSumByDate) { AddSaleToVendor(writer, sale.Date, sale.TotalSum); } writer.WriteEndElement(); } } } }
private static void ExportSales(OracleConnection connection, SupermarketsChainEntities db) { const string query = "SELECT PRODUCT_NAME, LOCATION_NAME, QUANTITY, DATE_SALE, PRICE_PER_UNIT FROM SALES S " + "JOIN PRODUCTS P ON P.PRODUCT_ID = S.PRODUCT_ID JOIN LOCATIONS L ON L.LOCATION_ID = S.LOCATION_ID"; using (var command = new OracleCommand(query, connection)) { using (var reader = command.ExecuteReader()) { while (reader.Read()) { var productName = (string)reader["PRODUCT_NAME"]; var locationName = (string)reader["LOCATION_NAME"]; db.Sales.Add(new Sale { Product = db.Products.FirstOrDefault(p => p.Name == productName), Location = db.Locations.FirstOrDefault(l => l.Name == locationName), Quantity = (decimal)reader["QUANTITY"], DateOfSale = (DateTime)reader["DATE_SALE"], PricePerUnit = (decimal)(double)reader["PRICE_PER_UNIT"] }); } } } }
private static void ImportProducts(MySqlConnection mySqlConnection, SupermarketsChainEntities db) { var products = db.Products.Select(p => new { p.Name, VendorName = p.Vendor.Name, TotalIncome = db.Sales .Where(s => s.ProductId == p.Id) .Select(s => s.Quantity * s.PricePerUnit) .DefaultIfEmpty(0) .Sum() }); foreach (var product in products) { var query = string.Format( "USE supermarketschain; INSERT INTO products (name,vendor_id,total_income) VALUES('{0}',{1},{2});", product.Name, GetVendorId(product.VendorName, mySqlConnection), product.TotalIncome); using (var command = new MySqlCommand(query, mySqlConnection)) { command.ExecuteNonQuery(); } } }
private static void ImportVendors(MySqlConnection mySqlConnection, SupermarketsChainEntities db) { var vendorNames = db.Vendors.Select(v => "('" + v.Name + "')"); var query = string.Format( "USE supermarketschain; INSERT IGNORE INTO vendors (name) VALUES {0};", string.Join(",", vendorNames)); using (var command = new MySqlCommand(query, mySqlConnection)) { command.ExecuteNonQuery(); } }
public static void ImportDataFromSqlServer() { var mySqlConnection = new MySqlConnection(Settings.Default.MySqlConnectionString); mySqlConnection.Open(); using (mySqlConnection) { using (var sqlServerDb = new SupermarketsChainEntities()) { ImportVendors(mySqlConnection, sqlServerDb); ImportExpenses(mySqlConnection, sqlServerDb); ImportProducts(mySqlConnection, sqlServerDb); } } }
public static void Main() { Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture; const string XmlResultFileName = @"..\..\..\result.xml"; var context = new SupermarketsChainEntities(); var startDate = new DateTime(2015, 1, 1); var endDate = new DateTime(2015, 3, 30); Console.WriteLine("Generating report from sales to xml..."); var sales = SalesByVendors(context, startDate, endDate); GenerateXmlFromSales(sales, XmlResultFileName); Console.WriteLine("The report is done!"); }
private static Location GetOrCreateLocation(string locationName, SupermarketsChainEntities db) { var location = db.Locations.FirstOrDefault(l => l.Name == locationName); if (location == null) { location = new Location { Name = locationName }; db.Locations.Add(location); db.SaveChanges(); } return(location); }
private static void ImportExpenses(MySqlConnection mySqlConnection, SupermarketsChainEntities db) { foreach (var expense in db.Expenses.Select(e => new { VendorName = e.Vendor.Name, e.Value })) { var query = string.Format( "USE supermarketschain; INSERT INTO expenses (vendor_id,value) VALUES({0},{1});", GetVendorId(expense.VendorName, mySqlConnection), expense.Value); using (var command = new MySqlCommand(query, mySqlConnection)) { command.ExecuteNonQuery(); } } }
private static void ExportLocations(OracleConnection connection, SupermarketsChainEntities db) { using (var command = new OracleCommand("SELECT LOCATION_NAME FROM LOCATIONS", connection)) { using (var reader = command.ExecuteReader()) { while (reader.Read()) { db.Locations.Add(new Location { Name = (string)reader["LOCATION_NAME"] }); } } } }
private static void ExportMeasures(OracleConnection connection, SupermarketsChainEntities db) { using (var command = new OracleCommand("SELECT MEASURE_NAME FROM MEASURES", connection)) { using (var reader = command.ExecuteReader()) { while (reader.Read()) { db.Measures.Add(new Measure { Name = (string)reader["MEASURE_NAME"] }); } } } }
private static void ExportVendors(OracleConnection connection, SupermarketsChainEntities db) { using (var command = new OracleCommand("SELECT VENDOR_NAME FROM VENDORS", connection)) { using (var reader = command.ExecuteReader()) { while (reader.Read()) { db.Vendors.Add(new Vendor { Name = (string)reader["VENDOR_NAME"] }); } } } }
private static Product GetOrCreateProduct(string productName, SupermarketsChainEntities db) { var product = db.Products.FirstOrDefault(p => p.Name == productName); if (product == null) { product = new Product { Name = productName, Vendor = new Vendor { Name = productName.Split(' ').Last() + " Corp." } }; db.Products.Add(product); db.SaveChanges(); } return(product); }
public static void ExportDbToSqlServer() { var connection = new OracleConnection(Settings.Default.OracleConnectionString); connection.Open(); using (connection) { using (var sqlServerDb = new SupermarketsChainEntities()) { ExportMeasures(connection, sqlServerDb); ExportVendors(connection, sqlServerDb); ExportLocations(connection, sqlServerDb); sqlServerDb.SaveChanges(); ExportProducts(connection, sqlServerDb); sqlServerDb.SaveChanges(); ExportSales(connection, sqlServerDb); sqlServerDb.SaveChanges(); } } }
private static Dictionary <string, SortedSet <Summary> > SalesByVendors(SupermarketsChainEntities context, DateTime startDate, DateTime endDate) { using (context) { var salesResult = new Dictionary <string, SortedSet <Summary> >(); var sales = context.Sales.Select(s => new { s.Product, s.Product.Vendor, s.SoldOn }); foreach (var sale in sales) { if (startDate <= sale.SoldOn && sale.SoldOn <= endDate) { var vendorName = sale.Product.Vendor.Name; if (!salesResult.ContainsKey(vendorName)) { salesResult[vendorName] = new SortedSet <Summary>(); } var summary = new Summary(sale.SoldOn, sale.Product.Price); var summariesWithEqualDates = salesResult[vendorName].Where(x => x.Date == summary.Date); if (summariesWithEqualDates.Count() == 0) { salesResult[vendorName].Add(summary); } else { summariesWithEqualDates.FirstOrDefault().TotalSum += summary.TotalSum; } } } return(salesResult); } }
private static void ExportProducts(OracleConnection connection, SupermarketsChainEntities db) { const string query = "SELECT PRODUCT_NAME, VENDOR_NAME, MEASURE_NAME FROM PRODUCTS P " + "JOIN VENDORS V ON V.VENDOR_ID = P.VENDOR_ID JOIN MEASURES M ON M.MEASURE_ID = P.MEASURE_ID"; using (var command = new OracleCommand(query, connection)) { using (var reader = command.ExecuteReader()) { while (reader.Read()) { var vendorName = (string)reader["VENDOR_NAME"]; var measureName = (string)reader["MEASURE_NAME"]; db.Products.Add(new Product { Name = (string)reader["PRODUCT_NAME"], Vendor = db.Vendors.FirstOrDefault(v => v.Name == vendorName), Measure = db.Measures.FirstOrDefault(m => m.Name == measureName) }); } } } }
private static IEnumerable <ProductTotalSale> GetSalesByProduct(DateTime startDate, DateTime endDate) { using (var sqlServerDb = new SupermarketsChainEntities()) { return(sqlServerDb.Sales .Where(sale => sale.DateOfSale >= startDate && sale.DateOfSale <= endDate) .GroupBy(sale => new { sale.ProductId, ProductName = sale.Product.Name, VendorName = sale.Product.Vendor.Name }) .Select(group => new ProductTotalSale { ProductId = group.Key.ProductId, ProductName = group.Key.ProductName, VendorName = group.Key.VendorName, QuantitySold = (double)group.Sum(g => g.Quantity), TotalIncomes = (double)group.Sum(g => g.Quantity * g.PricePerUnit) }) .ToList()); } }
public static void ImportExpenses() { var expenses = ReadExpensesFromXml(); using (var db = new SupermarketsChainEntities()) { foreach (var expense in expenses) { var existingVendor = db.Vendors.FirstOrDefault(v => v.Name == expense.Vendor.Name); if (existingVendor != null) { expense.Vendor = existingVendor; } else { db.Vendors.Add(expense.Vendor); db.SaveChanges(); } } db.Expenses.AddRange(expenses); db.SaveChanges(); } }
private static void AddSalesToDb(IExcelDataReader excelReader, DateTime currentDate) { using (var db = new SupermarketsChainEntities()) { var salesTable = excelReader.AsDataSet().Tables["Sales"]; var locationName = (string)salesTable.Rows[1].ItemArray[1]; var currentLocation = GetOrCreateLocation(locationName, db); for (var i = 3; i < salesTable.Rows.Count; i++) { if (((string)salesTable.Rows[i].ItemArray[1]).Contains("Total sum")) { break; } var productName = (string)salesTable.Rows[i].ItemArray[1]; productName = Regex.Replace(productName, @"[^\w'\. ]", string.Empty); var currentProduct = GetOrCreateProduct(productName, db); var quantity = (double)salesTable.Rows[i].ItemArray[2]; var pricePerUnit = (double)salesTable.Rows[i].ItemArray[3]; db.Sales.Add(new Sale { Location = currentLocation, DateOfSale = currentDate, Product = currentProduct, Quantity = (decimal)quantity, PricePerUnit = (decimal)pricePerUnit }); } db.SaveChanges(); } }
public static void ExportSales(DateTime startDate, DateTime endDate) { using (var pdfDocument = new Document()) { var file = File.Create(Settings.Default.PdfSalesReportLocation); PdfWriter.GetInstance(pdfDocument, file); pdfDocument.Open(); var table = new PdfPTable(5) { TotalWidth = 550f, LockedWidth = true }; table.SetWidths(new[] { 150f, 70f, 70f, 230f, 70f }); table.AddCell(new PdfPCell( new Phrase("Aggregated Sales Report", new Font(BaseFont, 14, Font.BOLD))) { Colspan = 5, HorizontalAlignment = 1, BackgroundColor = new BaseColor(255, 255, 255), PaddingTop = 10f, PaddingBottom = 10f }); var grandTotal = 0m; using (var db = new SupermarketsChainEntities()) { var dates = db.Sales .Select(sale => sale.DateOfSale) .Where(date => date >= startDate && date <= endDate) .Distinct() .ToList(); foreach (var date in dates) { AddHeaderToTable(table, date); var sales = db.Sales .Where(sale => sale.DateOfSale == date) .Select(sale => new { sale.Product.Name, sale.Quantity, sale.PricePerUnit, sale.DateOfSale, Location = sale.Location.Name, TotalValue = sale.Quantity * sale.PricePerUnit }) .ToList(); foreach (var sale in sales) { AddNormalCellToTable(table, sale.Name); AddNormalCellToTable(table, sale.Quantity.ToString("F2")); AddNormalCellToTable(table, sale.PricePerUnit.ToString("F2")); AddNormalCellToTable(table, sale.Location); AddNormalCellToTable(table, sale.TotalValue.ToString("F2")); } var totalSum = sales.Sum(sale => sale.TotalValue); AddSaleFooterToTable(table, date, totalSum); grandTotal += totalSum; } } AddGrandTotalToTable(table, grandTotal); pdfDocument.Add(table); pdfDocument.Close(); } }