//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);
}
