//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); }
//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); }
//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); }
// ******* 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); }
//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); }
public static string GetSoldProducts(ProductShopContext context) { const string xmlRoot = "Users"; var users = context .Users .Where(u => u.ProductsSold.Any(p => p.Buyer != null)) .Select(u => new ExportUserSoldProductDto { FirstName = u.FirstName, LastName = u.LastName, SoldProducts = u .ProductsSold .Select(p => new ProductDto { Name = p.Name, Price = p.Price }) .ToArray() }) .ToArray(); var xml = XMLConverter.Serialize(users, xmlRoot); return(xml); }
//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); }
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); }
//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); }
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); }
//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); }
// 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); }
//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); }
//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); }
//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); }
//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); }
//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); }
public void AuthorizeUser(string name, string password) { Console.WriteLine("Engine: AuthorizeUser"); AuthorizeUserRequest request = new AuthorizeUserRequest(); request.UserName = name; request.Password = password; MemoryStream xmlMessage = XMLConverter.Serialize(request); ProjectClient.SendRequest(xmlMessage); }
//Query 8. Users and Products public static string GetUsersWithProducts(ProductShopContext context) { //var productsInRange = GetUsersWithProducts(context); //if (!Directory.Exists(ResultDirectoryPath)) //{ // Directory.CreateDirectory(ResultDirectoryPath); //} //File.WriteAllText("../../../Datasets/Results/users-and-products.xml", productsInRange); const string rootElement = "Users"; var uasersAndProducts = context.Users .ToArray() .Where(u => u.ProductsSold.Any()) .Select(u => new ExportUserDto { FirstName = u.FirstName, LastName = u.LastName, Age = u.Age, SoldProduct = new ExportProductCountDto { Count = u.ProductsSold.Count(), Products = u.ProductsSold.Select(cp => new ExportCountedProductDto { Name = cp.Name, Price = cp.Price }) .OrderByDescending(p => p.Price) .ToArray() } }) .OrderByDescending(p => p.SoldProduct.Count) .Take(10) .ToArray(); var resultCount = new ExporUsersCount { Count = context.Users.Count(u => u.ProductsSold.Any()), Users = uasersAndProducts }; var result = XMLConverter.Serialize(resultCount, rootElement); return(result); }
//16. Local Suppliers public static string GetLocalSuppliers(CarDealerContext context) { var suppliers = context.Suppliers .Where(x => !x.IsImporter) .Select(x => new ExportLocalSuppliersDto { Id = x.Id, Name = x.Name, PartsCount = x.Parts.Count(p => p.Quantity > 0) }).ToList(); var root = "suppliers"; var xml = XMLConverter.Serialize(suppliers, root); return(xml); }
public static string GetLocalSuppliers(CarDealerContext context) { var suppliers = context .Suppliers .Where(s => s.IsImporter == false) .Select(s => new ExportLocalSuppliersDTO { Id = s.Id, Name = s.Name, PartsCount = s.Parts.Count }) .ToList(); var root = "suppliers"; var result = XMLConverter.Serialize(suppliers, root); return(result); }
//07. Export Categories By Products Count public static string GetCategoriesByProductsCount(ProductShopContext context) { var categoriesDTO = context.Categories .Select(x => new ExportCatecoryDTO { Name = x.Name, Count = x.CategoryProducts.Count(), AveragePrice = x.CategoryProducts.Average(y => y.Product.Price), TotalRevenue = x.CategoryProducts.Sum(y => y.Product.Price) }) .OrderByDescending(x => x.Count) .ThenBy(x => x.TotalRevenue) .ToArray(); var rootAttributeName = "Categories"; return(XMLConverter.Serialize(categoriesDTO, rootAttributeName)); }
//05. Export Products In Range public static string GetProductsInRange(ProductShopContext context) { var productsDTO = context.Products .Where(x => 500 <= x.Price && x.Price <= 1000) .OrderBy(x => x.Price) .Take(10) .Select(x => new ExportProductsDTO { Name = x.Name, Price = x.Price, Buyer = $"{x.Buyer.FirstName} {x.Buyer.LastName}" }) .ToArray(); var rootAttributeName = "Products"; return(XMLConverter.Serialize(productsDTO, rootAttributeName)); }
//18. Total Sales by Customer public static string GetTotalSalesByCustomer(CarDealerContext context) { var customers = context.Customers .Where(c => c.Sales.Count() > 0) .Select(x => new ExportTotalSalesByCustomerDto { FullName = x.Name, BoughtCars = x.Sales.Count(), SpentMoney = x.Sales.Sum(s => s.Car.PartCars.Sum(pc => pc.Part.Price)) }) .OrderByDescending(c => c.SpentMoney) .ThenByDescending(c => c.BoughtCars) .ToArray(); var root = "customers"; var xml = XMLConverter.Serialize(customers, root); return(xml); }
//15. Export Cars From Make BMW public static string GetCarsFromMakeBmw(CarDealerContext context) { var result = context .Cars .Where(x => x.Make == "BMW") .Select(x => new GetCarsFromMakeBmw { Id = x.Id, Model = x.Model, Distance = x.TravelledDistance }) .OrderBy(x => x.Model) .ThenByDescending(x => x.Distance) .ToList(); var xmlResult = XMLConverter.Serialize(result, "cars"); return(xmlResult); }
//Problem16 public static string GetLocalSuppliers(CarDealerContext context) { string rootName = "suppliers"; List <ExportLocalSupplierDto> suppliers = context .Suppliers .Where(s => !s.IsImporter) .Select(s => new ExportLocalSupplierDto { Id = s.Id, Name = s.Name, PartsCount = s.Parts.Count }) .ToList(); string xml = XMLConverter.Serialize(suppliers, rootName); return(xml); }
//15. Cars from make BMW public static string GetCarsFromMakeBmw(CarDealerContext context) { var cars = context.Cars .Where(c => c.Make == "BMW") .Select(c => new ExportCarsFromMakeBMWDto { Id = c.Id, Model = c.Model, TravelledDistance = c.TravelledDistance }) .OrderBy(c => c.Model) .ThenByDescending(c => c.TravelledDistance) .ToArray(); var root = "cars"; var xml = XMLConverter.Serialize(cars, root); return(xml); }
public static string ExportTopCustomers(CinemaContext context, int age) { var targetCustomers = context.Customers .Where(x => x.Age >= age) .Select(x => new ExportCustomerDto { FirstName = x.FirstName, LastName = x.LastName, SpentMoney = x.Tickets.Sum(t => t.Price).ToString("0.00"), SpentTime = TimeSpan.FromSeconds(x.Tickets.Sum(t => t.Projection.Movie.Duration.TotalSeconds)).ToString(@"hh\:mm\:ss") }) .OrderByDescending(x => decimal.Parse(x.SpentMoney)) .Take(10) .ToList(); var resultXml = XMLConverter.Serialize(targetCustomers, "Customers"); return(resultXml); }
public static string GetProductsInRange(ProductShopContext context) { const string xmlRoot = "Products"; var products = context .Products //.Where(p => p.Price >= 500 && p.Price <= 100) .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 xml = XMLConverter.Serialize(products, xmlRoot); return(xml); }