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