internal static string GetCategoriesByProductsCount(ProductsShopContext context)
{
var categoreisByProductsCount = context.Categories
.Include(p => p.ThisCategoryProducts)
.ThenInclude(cp => cp.Product)
.OrderBy(cp => cp.Name)
.Select(cp => new
{
category = cp.Name,
productsCount = cp.ThisCategoryProducts.Count == 0 ? 0 : cp.ThisCategoryProducts.Select(t => t.Product).Count(),
averagePrice = cp.ThisCategoryProducts.Count == 0 ? 0 : cp.ThisCategoryProducts.Select(t => t.Product.Price).Average(),
totalRevenue = cp.ThisCategoryProducts.Count == 0 ? 0 : cp.ThisCategoryProducts.Select(t => t.Product.Price).Sum(),
});
XDocument xCategories = new XDocument();
xCategories.Add(new XElement("categories"));
foreach (var c in categoreisByProductsCount)
{
xCategories.Root.Add(new XElement("category", new XAttribute("name", c.category),
new XElement("products-count", c.productsCount),
new XElement("average-price", c.averagePrice),
new XElement("total-revenue", c.totalRevenue)));
}
string result = XMLSerializer.SerializeXML(xCategories, true);
// System.Console.WriteLine(result);
return(result);
}
internal static string GetProductsInRange(ProductsShopContext context)
{
var products = context.Products
.Where(p => p.Price >= 1000 && p.Price <= 2000 && p.Buyer != null)
.Select(o => new
{
name = o.Name,
price = o.Price,
seller = o.Seller.FirstName == null ? o.Seller.LastName : o.Seller.FirstName + " " + o.Seller.LastName,
})
.OrderBy(a => a.price)
.ToArray();
XDocument xProducts = new XDocument(new XElement("products"));
foreach (var item in products)
{
xProducts.Root.Add(new XElement("product", new XAttribute("name", item.name), new XAttribute("price", item.price), new XAttribute("seller", item.seller)));
}
string result = XMLSerializer.SerializeXML(xProducts, true);
// System.Console.WriteLine(result);
return(result);
}
internal static string GetSuccessfullySoldProducts(ProductsShopContext context)
{
var usersAndSoldProducts = context.Users
.Include(u => u.ProductsSold)
.Where(u => u.ProductsSold.Any(p => p.BuyerId != null))
.OrderBy(u => u.LastName)
.ThenBy(u => u.FirstName)
.Select(u => new
{
firstName = u.FirstName,
lastName = u.LastName,
soldProducts = u.ProductsSold.Where(p => p.BuyerId != null)
.Select(p => new
{
name = p.Name,
price = p.Price,
})
})
.ToArray();
XDocument xProducts = new XDocument(new XElement("users"));
foreach (var item in usersAndSoldProducts)
{
XElement user = new XElement("user");
if (item.firstName != null)
{
user.Add(new XAttribute("first-name", item.firstName));
}
user.Add(new XAttribute("last-name", item.lastName));
ICollection <XElement> currentUserProducts = new List <XElement>();
foreach (var p in item.soldProducts)
{
XElement currentProduct = new XElement("product",
new XElement("name", p.name),
new XElement("price", p.price));
currentUserProducts.Add(currentProduct);
}
XElement products = new XElement("sold-products", currentUserProducts);
user.Add(products);
xProducts.Root.Add(user);
}
string result = XMLSerializer.SerializeXML(xProducts, true);
// System.Console.WriteLine(result);
return(result);
}
internal static string GetUsersAndProducts(ProductsShopContext context)
{
var usersBySoldProducts = context.Users
.Include(u => u.ProductsSold)
.Where(u => u.ProductsSold.Any(p => p.BuyerId != null))
.OrderByDescending(u => u.ProductsSold.Where(p => p.BuyerId != 0).Count())
.ThenBy(u => u.LastName)
.Select(u => new
{
firstName = u.FirstName,
lastName = u.LastName,
age = u.Age,
soldProducts = (new
{
count = u.ProductsSold.Where(pr => pr.BuyerId != 0).Count(),
products = u.ProductsSold.Select(pr => new
{
name = pr.Name,
price = pr.Price
})
})
});
var usersToSerialize = new
{
usersCount = usersBySoldProducts.Count(),
users = usersBySoldProducts
};
XDocument xUsersAndProducts = new XDocument();
xUsersAndProducts.Add(new XElement("users", new XAttribute("count", usersBySoldProducts.Count())));
foreach (var item in usersBySoldProducts)
{
XElement user = new XElement("user");
if (item.firstName != null)
{
user.Add(new XAttribute("first-name", item.firstName));
}
user.Add(new XAttribute("last-name", item.lastName));
if (item.age != null)
{
user.Add(new XAttribute("age", item.age));
}
XElement soldProducts = new XElement("sold-products", new XAttribute("count", item.soldProducts.count));
foreach (var soldProduct in item.soldProducts.products)
{
XElement product = new XElement("product", new XAttribute("name", soldProduct.name), new XAttribute("price", soldProduct.price));
soldProducts.Add(product);
}
user.Add(soldProducts);
xUsersAndProducts.Root.Add(user);
}
string result = XMLSerializer.SerializeXML(xUsersAndProducts, true);
// System.Console.WriteLine(result);
return(result);
}
