//07. Categories By Products Count public static string GetCategoriesByProductsCount(ProductShopContext context) { //Get all categories. //For each category select its name, //the number of products, //the average price of those products //and the total revenue(total price sum) of those products(regardless if they have a buyer or not). //Order them by the number of products(descending) then by total revenue. var categories = context.Categories .Select(x => new ExportCategoryDto { Name = x.Name, Count = x.CategoryProducts.Count, AveragePrice = x.CategoryProducts.Average(p => p.Product.Price), TotalRevenue = x.CategoryProducts.Where(b => b.Product.BuyerId != 0).Sum(p => p.Product.Price) }) .OrderByDescending(x => x.Count) .ThenBy(x => x.TotalRevenue) .ToArray(); var root = "Categories"; var xmlcategories = XMLConverter.Serialize(categories, root); return(xmlcategories); }
//Query 5. Products In Range public static string GetProductsInRange(ProductShopContext context) { //var productsInRange = GetProductsInRange(context); //if (!Directory.Exists(ResultDirectoryPath)) //{ // Directory.CreateDirectory(ResultDirectoryPath); //} //File.WriteAllText("../../../Datasets/Results/products-in-range.xml", productsInRange); const string rootElement = "Products"; var products = context.Products .Where(p => p.Price >= 500 && p.Price <= 1000) .Select(p => new ExportProductDto { Name = p.Name, Price = p.Price, Buyer = p.Buyer.FirstName + " " + p.Buyer.LastName }) .OrderBy(p => p.Price) .Take(10) .ToArray(); var result = XMLConverter.Serialize(products, rootElement); return(result); }
// ******* Task 4 - Import Categories and Products ******* public static string ImportCategoryProducts(ProductShopContext context, string inputXml) { var catProFromXML = XMLConverter.Deserializer <ImportCategoriesProductsDto>(inputXml, "CategoryProducts"); var productsId = context.Products.Select(i => i.Id).ToList(); var categoiesId = context.Categories.Select(c => c.Id).ToList(); var categoryProducts = new List <CategoryProduct>(); foreach (var item in catProFromXML) { if (productsId.Contains(item.ProductId) && categoiesId.Contains(item.CategoryId)) { var newCatPro = new CategoryProduct { CategoryId = item.CategoryId, ProductId = item.ProductId }; categoryProducts.Add(newCatPro); } } context.CategoryProducts.AddRange(categoryProducts); context.SaveChanges(); return($"Successfully imported {categoryProducts.Count}"); }
//11. Import Cars public static string ImportCars(CarDealerContext context, string inputXml3) { var rootAttributeName = "Cars"; var carsDTO = XMLConverter.Deserializer <ImportCarDTO>(inputXml3, rootAttributeName).ToArray(); var partIds = context.Parts.Select(x => x.Id).ToArray(); var cars = carsDTO.Select(x => new Car { Make = x.Make, Model = x.Model, TravelledDistance = x.TravelledDistance, PartCars = x.Parts .Where(y => partIds.Contains(y.Id)) .Select(y => new PartCar { PartId = y.Id }) .Distinct() .ToArray() }) .ToArray(); context.Cars.AddRange(cars); context.SaveChanges(); return($"Successfully imported {cars.Length}"); }
//Problem03 public static string ImportCategories(ProductShopContext context, string inputXml) { string rootElement = "Categories"; var categoriesDto = XMLConverter.Deserializer <ImportCategoryDto>(inputXml, rootElement); List <Category> categories = new List <Category>(); foreach (var c in categoriesDto) { if (c.Name != null) { Category category = new Category { Name = c.Name }; categories.Add(category); } } context.Categories.AddRange(categories); context.SaveChanges(); return($"Successfully imported {categories.Count}"); }
//Query 18. Total Sales by Customer public static string GetTotalSalesByCustomer(CarDealerContext context) { //var totalSales = GetTotalSalesByCustomer(context); //if (!Directory.Exists(ResultDirectoryPath)) //{ //Directory.CreateDirectory(ResultDirectoryPath); //} //File.WriteAllText("../../../Datasets/Results/customers-total-sales.xml", totalSales); const string rootElement = "customers"; var customerWithCars = context.Sales .Where(s => s.Car.Sales.Any()) .Select(s => new ExportTotalSalesCustomersDto { FullName = s.Customer.Name, BoughtCars = s.Customer.Sales.Count, SpentMoney = s.Car.PartCars.Sum(p => p.Part.Price) }) .OrderByDescending(c => c.SpentMoney) .ToArray(); var result = XMLConverter.Serialize(customerWithCars, rootElement); return(result); }
//Query 10. Import Parts public static string ImportParts(CarDealerContext context, string inputXml) { //var result = ImportParts(context, partsXml); //Console.WriteLine(result); const string rootElement = "Parts"; var partsResult = XMLConverter.Deserializer <ImportPartsDto>(inputXml, rootElement); var parts = partsResult .Where(p => context.Suppliers.Any(s => s.Id == p.SupplierId)) .Select(p => new Part { Name = p.Name, Price = p.Price, Quantity = p.Quantity, SupplierId = p.SupplierId }) .ToArray(); context.Parts.AddRange(parts); int partsCount = context.SaveChanges(); return($"Successfully imported {partsCount}"); }
//4. Import Categories and Products public static string ImportCategoryProducts(ProductShopContext context, string inputXml) { const string rootElement = "CategoryProducts"; var cpResult = XMLConverter.Deserializer <ImportCategoryProductDto>(inputXml, rootElement); //var catProds = cpResult // .Where(cp=>context.Categories.Any(c=>c.Id == cp.CategoryId) && context.Products.Any(p=>p.Id == cp.ProductId)) // .Select(cp => new CategoryProduct // { // CategoryId = cp.CategoryId, // ProductId = cp.ProductId // }) // .ToArray(); List <CategoryProduct> catProds = new List <CategoryProduct>(); foreach (var dto in cpResult) { if (context.Categories.Any(c => c.Id == dto.CategoryId) && context.Products.Any(p => p.Id == dto.ProductId)) { CategoryProduct catProd = new CategoryProduct { CategoryId = dto.CategoryId, ProductId = dto.ProductId }; catProds.Add(catProd); } } context.CategoryProducts.AddRange(catProds); context.SaveChanges(); return($"Successfully imported {catProds.Count}"); }
//6. Export Sold Products public static string GetSoldProducts(ProductShopContext context) { const string rootElement = "Users"; var users = context .Users .Where(u => u.ProductsSold.Count > 0) .Select(u => new ExportUserWithSoldProductDto { FirstName = u.FirstName, LastName = u.LastName, SoldProducts = u.ProductsSold .Select(p => new ExportSoldProductDto { Name = p.Name, Price = p.Price }) .ToArray() }) .OrderBy(u => u.LastName) .ThenBy(u => u.FirstName) .Take(5) .ToArray(); var result = XMLConverter.Serialize(users, rootElement); return(result); }
public void ProductDictionarySerializationCountXMLTest() { Dictionary <int, Product> products = new Dictionary <int, Product> { { 1, new Product { Name = "Yamaha 2", Price = 400.99 } }, { 2, new Product { Name = "Ibanez 2111", Price = 402130.99 } }, { 3, new Product { Name = "Cort 242", Price = 4111100.99 } }, { 4, new Product { Name = "Yamaha -4", Price = 4500.99 } }, }; XMLConverter xmlc = new XMLConverter(); xmlc.writeProductsDictionary(products, "products.txt"); Dictionary <int, Product> deProducts = xmlc.readProductsDictionary("products.txt"); Assert.AreEqual(products.Count, deProducts.Count); }
public void ProductDictionarySerializationXMLTest() { Dictionary <int, Product> products = new Dictionary <int, Product> { { 0, new Product { Name = "Yamaha 2.0", Price = 3200.99 } }, { 1, new Product { Name = "Yamaha 2", Price = 400.99 } }, { 2, new Product { Name = "Ibanez 2111", Price = 402130.99 } }, { 3, new Product { Name = "Cort 242", Price = 4111100.99 } }, { 4, new Product { Name = "Yamaha -4", Price = 4500.99 } }, }; XMLConverter xmlc = new XMLConverter(); xmlc.writeProductsDictionary(products, "products.txt"); Dictionary <int, Product> deProducts = xmlc.readProductsDictionary("products.txt"); for (int i = 0; i < products.Count; i++) { Assert.AreEqual(products[i].ToString(), deProducts[i].ToString()); } }
//17. Cars with Their List of Parts public static string GetCarsWithTheirListOfParts(CarDealerContext context) { var cars = context.Cars .Select(x => new ExportCarsWithTheirListOfPartsDto { CarMake = x.Make, Model = x.Model, TravelledDistance = x.TravelledDistance, Parts = x.PartCars.Select(p => new PartsDto { Name = p.Part.Name, Price = p.Part.Price }).OrderByDescending(p => p.Price) .ToArray() }) .OrderByDescending(x => x.TravelledDistance) .ThenBy(x => x.Model) .Take(5) .ToArray(); var root = "cars"; var xml = XMLConverter.Serialize(cars, root); return(xml); }
private void ANewXMLConverter(Encoding encoding) { var fileSystem = new MockFileSystem(); _outputEncoding = encoding; _XMLConverter = new XMLConverter(encoding, fileSystem); }
/// <summary> /// Загрузить все справочники из файла загрузки. /// </summary> public void LoadAll() { this.Logger.WriteLog("Checking download file"); List <byte> bytes = new List <byte>(); byte[] bytesAr = null; try { bytesAr = File.ReadAllBytes(Path.Combine(this.ExchangeFolder, this.DownloadFileName)); } catch (FileNotFoundException ex) { this.Logger.WriteLog("Download file not found"); throw ex; } this.Logger.WriteLog("Download file found"); foreach (var item in bytesAr) { bytes.Add(item); } XMLConverter converter = new XMLConverter(bytes, this.Logger); this.LoadClients(converter); this.LoadSuppliers(converter); this.LoadTradeObjects(converter); File.Move(Path.Combine(this.ExchangeFolder, this.DownloadFileName), Path.Combine(this.ExchangeFolder, this.DownloadFileName + DateTime.Now.ToString("ddMMyyyy_hhmmss"))); }
//Query 15. Cars from make BMW public static string GetCarsFromMakeBmw(CarDealerContext context) { //var carsMakeBmw = GetCarsFromMakeBmw(context); //if (!Directory.Exists(ResultDirectoryPath)) //{ //Directory.CreateDirectory(ResultDirectoryPath); //} //File.WriteAllText("../../../Datasets/Results/bmw-cars.xml", carsMakeBmw); const string rootElement = "cars"; var carsBmw = context.Cars .Where(c => c.Make.ToLower() == "bmw") .Select(c => new ExportCarBmwDto { Id = c.Id, Model = c.Model, TravelledDistance = c.TravelledDistance }) .OrderBy(c => c.Model) .ThenByDescending(c => c.TravelledDistance) .ToArray(); var result = XMLConverter.Serialize(carsBmw, rootElement); return(result); }
//1. Import Users public static string ImportUsers(ProductShopContext context, string inputXml) { const string rootElement = "Users"; var userResult = XMLConverter.Deserializer <ImportUserDto>(inputXml, rootElement); List <User> users = new List <User>(); foreach (var dto in userResult) { User user = new User { FirstName = dto.FirstName, LastName = dto.LastName, Age = dto.Age }; users.Add(user); } //or: //var users = userResult // .Select(u => new User // { // FirstName = u.FirstName, // LastName = u.LastName, // Age = u.Age // }) // .ToArray(); context.Users.AddRange(users); context.SaveChanges(); return($"Successfully imported {users.Count}"); }
//Query 16. Local Suppliers public static string GetLocalSuppliers(CarDealerContext context) { //var localSuppliers = GetLocalSuppliers(context); //if (!Directory.Exists(ResultDirectoryPath)) //{ //Directory.CreateDirectory(ResultDirectoryPath); //} //File.WriteAllText("../../../Datasets/Results/local-suppliers.xml", localSuppliers); const string rootElement = "suppliers"; var localSupppliersResult = context.Suppliers .Where(s => s.IsImporter == false) .Select(s => new ExportLocalSuppliersDto { Id = s.Id, Name = s.Name, PartsCount = s.Parts.Count }) .ToArray(); var result = XMLConverter.Serialize(localSupppliersResult, rootElement); return(result); }
//2. Import Products public static string ImportProducts(ProductShopContext context, string inputXml) { const string rootElement = "Products"; var productResult = XMLConverter.Deserializer <ImportProductDto>(inputXml, rootElement); List <Product> products = new List <Product>(); foreach (var dto in productResult) { Product product = new Product { Name = dto.Name, Price = dto.Price, SellerId = dto.SellerId, BuyerId = dto.BuyerId }; products.Add(product); } context.Products.AddRange(products); context.SaveChanges(); return($"Successfully imported {products.Count}"); }
//Query 19. Sales with Applied Discount public static string GetSalesWithAppliedDiscount(CarDealerContext context) { //var discountSales = GetSalesWithAppliedDiscount(context); //if (!Directory.Exists(ResultDirectoryPath)) //{ //Directory.CreateDirectory(ResultDirectoryPath); //} //File.WriteAllText("../../../Datasets/Results/sales-discounts.xml", discountSales); const string rootElement = "sales"; var discountSales = context.Sales .Select(s => new ExportDicountSalesDto { Car = new CarDto { Make = s.Car.Make, Model = s.Car.Model, TravelledDistance = s.Car.TravelledDistance, }, Discount = s.Discount, CustomerName = s.Customer.Name, Price = s.Car.PartCars.Sum(p => p.Part.Price), PriceWithDiscount = s.Car.PartCars.Sum(p => p.Part.Price) - s.Car.PartCars.Sum(p => p.Part.Price) * s.Discount / 100 }) .ToArray(); var result = XMLConverter.Serialize(discountSales, rootElement); return(result); }
public static string GetSoldProducts(ProductShopContext context) { var usersProducts = context .Users .Where(u => u.ProductsSold.Any()) .Select(u => new ExportUsersProductsDTO { FirstName = u.FirstName, LastName = u.LastName, soldProducts = u.ProductsSold .Select(p => new ExportProductDTO { Name = p.Name, Price = p.Price }) .ToList() }) .OrderBy(x => x.LastName) .ThenBy(x => x.FirstName) .Take(5) .ToList(); var root = "Users"; var result = XMLConverter.Serialize(usersProducts, root); return(result); }
// Problem 06 public static string GetSoldProducts(ProductShopContext context) { const string rootElement = "Users"; var soldProducts = context .Users .Where(u => u.ProductsSold.Count >= 1) .Select(u => new ExportUserProductInfoDto { FirstName = u.FirstName, LastName = u.LastName, SoldProducts = u.ProductsSold.Select(p => new UserProductDto { Name = p.Name, Price = p.Price }) .ToArray() }) .OrderBy(u => u.LastName) .ThenBy(u => u.FirstName) .Take(5) .ToList(); var xmlOutput = XMLConverter.Serialize(soldProducts, rootElement); return(xmlOutput); }
public static string GetSalesWithAppliedDiscount(CarDealerContext context) { //Get all sales with information about the car, customer and price of the sale with and without discount. var sales = context .Sales .Select(s => new ExportSalesDTO { Car = new ExportCarDTO { Make = s.Car.Make, Model = s.Car.Model, TravelledDistance = s.Car.TravelledDistance }, Discount = s.Discount, CusomerName = s.Customer.Name, Price = s.Car.PartCars.Sum(pc => pc.Part.Price), PriceWithDiscount = s.Car.PartCars.Sum(pc => pc.Part.Price) - s.Car.PartCars.Sum(pc => pc.Part.Price) * s.Discount / 100M }) .ToList(); var root = "sales"; var result = XMLConverter.Serialize(sales, root); return(result); }
//11. Import Cars public static string ImportCars(CarDealerContext context, string inputXml) { //2:39:36, 2:49:12 var carsDtos = XMLConverter.Deserializer <ImportCarDto>(inputXml, "Cars"); var cars = new List <Car>(); foreach (var carDto in carsDtos) { //2:49:12 var uniqueParts = carDto.Parts.Select(x => x.Id).Distinct().ToArray(); var realPartds = uniqueParts.Where(id => context.Parts.Any(i => i.Id == id)); var car = new Car { Make = carDto.Make, Model = carDto.Model, TravelledDistance = carDto.TraveledDistance, PartCars = realPartds.Select(id => new PartCar { PartId = id }) .ToArray() }; cars.Add(car); } context.Cars.AddRange(cars); context.SaveChanges(); return($"Successfully imported {cars.Count}"); }
public static string GetCarsWithTheirListOfParts(CarDealerContext context) { var cars = context .Cars .Select(c => new ExportCarsWithPartsDTO { Make = c.Make, Model = c.Model, TravelledDistance = c.TravelledDistance, Parts = c.PartCars.Select(pc => new ExportPartsDTO { Name = pc.Part.Name, Price = pc.Part.Price }) .OrderByDescending(x => x.Price) .ToList() }) .OrderByDescending(c => c.TravelledDistance) .ThenBy(c => c.Model) .Take(5) .ToList(); var root = "cars"; var result = XMLConverter.Serialize(cars, root); return(result); }
//Problem06 public static string GetSoldProducts(ProductShopContext context) { string rootElement = "Users"; List <ExportUserSoldProductDto> usersWithProducts = context .Users .Where(up => up.ProductsSold.Any()) .Select(up => new ExportUserSoldProductDto { FirstName = up.FirstName, LastName = up.LastName, SoldProducts = up.ProductsSold .Select(p => new UserProductDto { Name = p.Name, Price = p.Price }) .ToArray() }) .OrderBy(u => u.LastName) .ThenBy(u => u.FirstName) .Take(5) .ToList(); string xml = XMLConverter.Serialize(usersWithProducts, rootElement); return(xml); }
//Query 13. Import Sales public static string ImportSales(CarDealerContext context, string inputXml) { //var result = ImportSales(context, salesXml); //Console.WriteLine(result); const string rootElement = "Sales"; var salesResult = XMLConverter.Deserializer <ImportSalesDto>(inputXml, rootElement); var sales = salesResult .Where(s => context.Cars.Any(c => c.Id == s.CarId)) .Select(s => new Sale { CarId = s.CarId, CustomerId = s.CustomerId, Discount = s.Discount }) .ToArray(); context.Sales.AddRange(sales); int salesCount = context.SaveChanges(); return($"Successfully imported {salesCount}"); }
//Query 7. Categories By Products Count public static string GetCategoriesByProductsCount(ProductShopContext context) { //var productsInRange = GetCategoriesByProductsCount(context); //if (!Directory.Exists(ResultDirectoryPath)) //{ // Directory.CreateDirectory(ResultDirectoryPath); //} //File.WriteAllText("../../../Datasets/Results/categories-by-products.xml", productsInRange); const string rootElement = "Categories"; var exportUserSoldProductDtos = context.Categories .Select(p => new ExportCategoriesByProductCountDto { Name = p.Name, Count = p.CategoryProducts.Count(), AveragePrice = p.CategoryProducts.Average(pr => pr.Product.Price), TotalRevenue = p.CategoryProducts.Sum(pr => pr.Product.Price) }) .OrderByDescending(p => p.Count) .ThenBy(p => p.TotalRevenue) .ToArray(); var result = XMLConverter.Serialize(exportUserSoldProductDtos, rootElement); return(result); }
//Query 14. Cars With Distance public static string GetCarsWithDistance(CarDealerContext context) { //var carsWithDistance = GetCarsWithDistance(context); //if (!Directory.Exists(ResultDirectoryPath)) //{ //Directory.CreateDirectory(ResultDirectoryPath); //} //File.WriteAllText("../../../Datasets/Results/cars.xml", carsWithDistance); const string rootElement = "cars"; var carsWithDistance = context.Cars .Where(c => c.TravelledDistance > 2000000) .Select(c => new ExportCarsWithDistance { Make = c.Make, Model = c.Model, TravelledDistance = c.TravelledDistance }) .OrderBy(c => c.Make) .ThenBy(c => c.Model) .Take(10) .ToArray(); var result = XMLConverter.Serialize(carsWithDistance, rootElement); return(result); }
// ******* Task 6 - Export Sold Products ******* public static string GetSoldProducts(ProductShopContext context) { const string rootElement = "Users"; var users = context.Users .Where(u => u.ProductsSold.Any()) .Select(x => new ExportUserSoldProductsDto { FirstName = x.FirstName, LastName = x.LastName, SoldProducts = x.ProductsSold .Select(p => new SoldProduct { Name = p.Name, Price = p.Price }).ToArray() }) .OrderBy(l => l.LastName) .ThenBy(f => f.FirstName) .Take(5) .ToArray(); string result = XMLConverter.Serialize(users, rootElement); return(result); }
//06. Sold Products public static string GetSoldProducts(ProductShopContext context) { var user = context.Users .Where(x => x.ProductsSold.Any()) .Select(x => new ExportUsersDto { FirstName = x.FirstName, LastName = x.LastName, SoldProducts = x.ProductsSold.Select(s => new ProductDto { Name = s.Name, Price = s.Price }).ToArray() }) .OrderBy(x => x.LastName) .ThenBy(x => x.FirstName) .Take(5) .ToArray(); var root = "Users"; var xmlUserProducts = XMLConverter.Serialize(user, root); return(xmlUserProducts); }