//2.Export Successfully Sold Products JSON
static void GetUserWithOneOrMoreSoldItemsJson()
{
using (var db = new ProductShopDbContext())
{
var users = db.Users
.Where(x => x.SoldProducts.Any(y => y.BuyerId != null))
.OrderBy(x => x.LastName)
.ThenBy(x => x.FirstName)
.Select(x => new
{
firstName = x.FirstName,
lastName = x.LastName,
soldProducts = x.SoldProducts.Select(y => new
{
name = y.Name,
price = y.Price,
buyerFirstName = y.Buyer.FirstName,
buyerLastName = y.Buyer.LastName
}).ToList()
}).ToList();
string jsonString = JsonConvert.SerializeObject(users, Formatting.Indented, new JsonSerializerSettings()
{
DefaultValueHandling = DefaultValueHandling.Ignore
});
File.WriteAllText("FilesExported/Json/2.Users-sold-products.json", jsonString);
}
}
private static void ReadCategoryProducts()
{
var config = new MapperConfiguration(cfg =>
{
cfg.AddProfile <ProductShopProfile>();
});
var mapper = config.CreateMapper();
var categoryProducts = new List <CategoryProduct>();
for (int productId = 1; productId < 200; productId++)
{
var categoryId = new Random().Next(1, 11);
var categoryProduct = new CategoryProduct()
{
ProductId = productId,
CategoryId = categoryId
};
categoryProducts.Add(categoryProduct);
}
var context = new ProductShopDbContext();
context.CategoryProducts.AddRange(categoryProducts);
context.SaveChanges();
}
private static void ImportProducts(ProductShopDbContext context)
{
if (!context.Users.Any())
{
ImportUsers(context);
}
HashSet <User> users = context.Users.ToHashSet();
using (StreamReader productsJSON = File.OpenText(@"..\..\..\Resources\products.json"))
{
Product[] products = JsonConvert.DeserializeObject <Product[]>(productsJSON.ReadToEnd());
List <int> sellerIds = users.Select(u => u.Id).Take(users.Count / 2).ToList();
List <int> buyerIds = users.Select(u => u.Id).Skip(sellerIds.Count).ToList();
Random rng = new Random();
HashSet <Product> existingProducts = context.Products.ToHashSet();
for (int i = 0; i < products.Length; i++)
{
Product product = products[i];
if (IsObjectValid(product) && !existingProducts.Any(p => p.Name == product.Name))
{
product.SellerId = sellerIds[rng.Next(0, sellerIds.Count)];
if (i % 5 != 0)
{
product.BuyerId = buyerIds[rng.Next(0, buyerIds.Count)];
}
context.Products.Add(product);
existingProducts.Add(product);
}
}
context.SaveChanges();
}
}
private static void GetUsersAndProducts(ProductShopDbContext context)
{
var users = new UsersDto()
{
UsersCount = context.Users.Where(u => u.ProductsSold.Count >= 1).Count(),
UsersProducts = context.Users.Include(u => u.ProductsSold)
.Where(u => u.ProductsSold.Count >= 1)
.OrderByDescending(u => u.ProductsSold.Count)
.ThenBy(u => u.LastName)
.Select(u => new UserSoldProductsDto()
{
FirstName = u.FirstName,
LastName = u.LastName,
Age = u.Age.ToString(),
SoldProducts = new SoldProductsDto()
{
ProductsCounts = u.ProductsSold.Count,
Products = u.ProductsSold.Select(p => new ProductPriceDto()
{
Name = p.Name,
Price = p.Price
}).ToArray(),
}
})
.ToArray()
};
var serializerNamespaces = new XmlSerializerNamespaces(new[] { XmlQualifiedName.Empty });
var serializer = new XmlSerializer(typeof(UsersDto), new XmlRootAttribute("users"));
using (StreamWriter writer = new StreamWriter(@"..\..\..\Output\users-and-products.xml", false))
{
serializer.Serialize(writer, users, serializerNamespaces);
}
}
private static void GetUsersAndProducts(ProductShopDbContext context)
{
var usersProducts = new
{
usersCount = context.Users.Where(u => u.ProductsSold.Count >= 1).Count(),
users = context.Users
.Where(u => u.ProductsSold.Count >= 1)
.OrderByDescending(u => u.ProductsSold.Count)
.ThenBy(u => u.LastName)
.Select(u => new
{
firstName = u.FirstName,
lastName = u.LastName,
age = u.Age,
soldProducts = new
{
count = u.ProductsSold.Count,
products = u.ProductsSold.Select(p => new
{
name = p.Name,
price = p.Price
}).ToArray(),
}
})
.ToArray()
};
string output = JsonConvert.SerializeObject(usersProducts, new JsonSerializerSettings()
{
Formatting = Formatting.Indented,
NullValueHandling = NullValueHandling.Ignore
});
File.WriteAllText(@"..\..\..\Output\users-and-products.json", output, Encoding.UTF8);
}
private static void SuccessfullySoldProducts(ProductShopDbContext db)
{
var successfullySold = db.Users
.Where(p => p.ProductsForSale.Any(x => x.Buyer != null))
.OrderBy(x => x.LastName)
.ThenBy(x => x.FirstName)
.Select(p => new
{
firstName = p.FirstName,
lastName = p.LastName,
soldProducts = p.ProductsForSale
.Where(pr => pr.Buyer != null)
.Select(pr => new
{
name = pr.Name,
price = pr.Price,
buyerFisrtName = pr.Buyer.FirstName,
buyerLastName = pr.Buyer.LastName,
})
})
.ToList();
string outputFileName = "SuccessfullySold.json";
SerializeToJson(outputFileName, successfullySold);
}
static void Main(string[] args)
{
//01-SetupDatabase
//using(var ctx = new ProductShopDbContext())
//{
// ctx.Database.Migrate();
//}
//02-ImportData
var config = new MapperConfiguration(cfg =>
{
cfg.AddProfile <ProductShopProfile>();
});
var mapper = config.CreateMapper();
//ImportUsers(mapper);
//ImportProducts(mapper);
//ImportCategories(mapper);
//ImportCategoryProducts();
var ctx = new ProductShopDbContext();
//Query 1. Products In Range
//GetProductsInRange(ctx);
//Query 2. Sold Products
//GetSoldProducts(ctx);
//Query 3. Categories By Products Count
//GetCategoriesByProductsCount(ctx);
//Query 4. Users and Products
//GetUsersAndProducts(ctx);
}
static void GetProductsInRangeXml()
{
using (var db = new ProductShopDbContext())
{
var products = db.Products
.Where(x => x.BuyerId != null && x.Price >= 1000 & x.Price <= 2000)
.Select(x => new
{
name = x.Name,
price = x.Price,
buyer = string.Join(" ", x.Buyer.FirstName, x.Buyer.LastName)
})
.ToList()
.OrderBy(x => x.price);
var xDoc = new XDocument();
xDoc.Add(new XElement("products"));
foreach (var p in products)
{
xDoc.Root.Add(new XElement("product", new XAttribute("name", p.name), new XAttribute("price", p.price), new XAttribute("buyer", p.buyer)));
}
xDoc.Save("FilesExported/Xml/1.Products-in-range.xml");
}
}
static void GetCategoriesXml()
{
using (var db = new ProductShopDbContext())
{
var categories = db.Categories
.Include(x => x.CategoriesProducts)
.ThenInclude(x => x.Product)
.Select(x => new
{
name = x.Name,
productCount = x.CategoriesProducts.Count(),
averagePrice = x.CategoriesProducts.Average(y => y.Product.Price),
totalRevenue = x.CategoriesProducts.Sum(z => z.Product.Price)
}).ToList()
.OrderByDescending(x => x.productCount);
var xmlDoc = new XDocument(new XElement("categories"));
foreach (var cat in categories)
{
xmlDoc.Root.Add(new XElement("category",
new XAttribute("name", cat.name),
new XElement("products-count", cat.productCount),
new XElement("average-price", cat.averagePrice),
new XElement("total-revenue", cat.totalRevenue)));
}
xmlDoc.Save("FilesExported/Xml/3.Categories-by-products.xml");
}
}
private static void GetUsersAndProducts(ProductShopDbContext ctx)
{
var users = new Q4_UsersDto()
{
UsersCount = ctx.Users.Where(u => u.ProductsSold.Count >= 1).Count(),
Users = ctx.Users.Where(u => u.ProductsSold.Count >= 1).Select(u => new Q4_UserDto()
{
FirstName = u.FirstName,
LastName = u.LastName,
Age = u.Age.ToString(),
SoldProducts = new Q4_SoldProductDto()
{
ProductsCount = u.ProductsSold.Count,
Products = u.ProductsSold.Select(p => new Q4_ProductDto()
{
Name = p.Name,
Price = p.Price
}).ToArray()
}
})
.OrderByDescending(u => u.SoldProducts.ProductsCount)
.ThenBy(u => u.LastName).ToArray()
};
var serializer = new XmlSerializer(typeof(Q4_UsersDto), new XmlRootAttribute("users"));
using (var writer = new StreamWriter("../../../XML/Outer/users-and-products.xml"))
{
serializer.Serialize(writer, users, new XmlSerializerNamespaces(new[] { XmlQualifiedName.Empty }));
}
}
private static void SoldProducts()
{
var config = new MapperConfiguration(cfg =>
{
cfg.AddProfile <ProductShopProfile>();
});
var mapper = config.CreateMapper();
var xmlString = File.ReadAllText("Xml/users.xml");
var serializer = new XmlSerializer(typeof(UserDto[]), new XmlRootAttribute("users"));
var deserializeUser = (UserDto[])serializer.Deserialize(new StringReader(xmlString));
var users = new List <Users>();
foreach (var userDto in deserializeUser)
{
if (!IsValid(userDto))
{
continue;
}
var user = mapper.Map <Users>(userDto);
users.Add(user);
}
var context = new ProductShopDbContext();
context.AddRange(users);
context.SaveChanges();
}
private static void ImportUsers(IMapper mapper)
{
var xmlString = File.ReadAllText("../../../XML/Inner/users.xml");
var serializer = new XmlSerializer(typeof(UserDto_02[]), new XmlRootAttribute("users"));
var deserializedUsers = (UserDto_02[])serializer.Deserialize(new StringReader(xmlString));
List <User> users = new List <User>();
foreach (var userDto in deserializedUsers)
{
if (!IsValid(userDto))
{
continue;
}
var user = mapper.Map <User>(userDto);
users.Add(user);
}
var ctx = new ProductShopDbContext();
ctx.Users.AddRange(users);
ctx.SaveChanges();
}
private static void SeedCategoriesProducts(ProductShopDbContext context)
{
if (!context.Categories.Any())
{
ImportCategories(context);
}
List <int> categoryIds = context.Categories.Select(c => c.Id).ToList();
if (!context.Products.Any())
{
ImportProducts(context);
}
List <int> productIds = context.Products.Select(p => p.Id).OrderBy(id => id).ToList();
Random rng = new Random();
HashSet <CategoryProduct> categoryProducts = context.CategoryProducts.ToHashSet();
foreach (int productId in productIds)
{
CategoryProduct categoryProduct = new CategoryProduct()
{
ProductId = productId
};
if (!categoryProducts.Any(cp => cp.ProductId == productId))
{
categoryProduct.CategoryId = categoryIds[rng.Next(0, categoryIds.Count - 1)];
context.CategoryProducts.Add(categoryProduct);
categoryProducts.Add(categoryProduct);
}
}
context.SaveChanges();
}
private static void GetSoldProducts(ProductShopDbContext ctx)
{
var users = ctx.Users
.Where(u => u.ProductsSold.Count >= 1)
.Select(u => new Q2_UserDto
{
FirstName = u.FirstName,
LastName = u.LastName,
SoldProducts = u.ProductsSold.Select(p => new Q2_SoldProductDto()
{
Name = p.Name,
Price = p.Price
}).ToArray()
})
.OrderBy(u => u.LastName)
.ThenBy(u => u.FirstName)
.ToArray();
var serializer = new XmlSerializer(typeof(Q2_UserDto[]), new XmlRootAttribute("users"));
using (var writer = new StreamWriter("../../../XML/Outer/users-sold-products.xml"))
{
serializer.Serialize(writer, users, new XmlSerializerNamespaces(new[] { XmlQualifiedName.Empty }));
}
}
private static void QueryUsersAndProducts(ProductShopDbContext db)
{
//Get all users who have at least 1 sold product. Order them by the number of sold products (from highest to lowest), then by last name (ascending). Select only their first and last name, age and for each product - name and price.
var users = db.Users
.Where(u => u.SoldProducts.Count > 0)
.Include(u => u.SoldProducts)
.OrderByDescending(u => u.SoldProducts.Count)
.ThenBy(u => u.LastName)
.Select(u => new
{
firstName = u.FirstName,
lastName = u.LastName,
age = u.Age,
soldProducts = new
{
count = u.SoldProducts.Count,
products = u.SoldProducts.Select(p => new
{
name = p.Name,
price = p.Price
})
}
});
var usersToSerialize = new
{
usersCount = users.Count(),
users
};
ExportJsonToFolder(usersToSerialize, "ExportJson/usersAndProducts.json");
}
static string ImportCategoriesFromXML()
{
string path = "../../../Files/categories.xml";
string xmlString = File.ReadAllText(path);
var xmlDoc = XDocument.Parse(xmlString);
var elements = xmlDoc.Root.Elements();
var categories = new List <Category>();
foreach (var e in elements)
{
var category = new Category
{
Name = e.Element("name").Value
};
categories.Add(category);
}
;
using (var db = new ProductShopDbContext())
{
db.Categories.AddRange(categories);
db.SaveChanges();
}
return($"{categories.Count()} categories were added!");
}
private static void ImportCategories(IMapper mapper)
{
var xmlString = File.ReadAllText("../../../XML/Inner/categories.xml");
var serializer = new XmlSerializer(typeof(CategoryDto_02[]), new XmlRootAttribute("categories"));
var deserializedCategories = (CategoryDto_02[])serializer.Deserialize(new StringReader(xmlString));
List <Category> categories = new List <Category>();
foreach (var categoryDto in deserializedCategories)
{
if (!IsValid(categoryDto))
{
continue;
}
var category = mapper.Map <Category>(categoryDto);
categories.Add(category);
}
var ctx = new ProductShopDbContext();
ctx.Categories.AddRange(categories);
ctx.SaveChanges();
}
private static void CategoriesByProductCount()
{
var context = new ProductShopDbContext();
var categories = context.Categories
.OrderByDescending(b => b.CategoryProducts.Count)
.Select(x => new ExportCategoryDto
{
Name = x.Name,
ProductsCount = x.CategoryProducts.Count,
AveragePrice = x.CategoryProducts.Select(c => c.Products.Price)
.DefaultIfEmpty(0).Average(),
TotalRevenue = x.CategoryProducts.Select(c => c.Products.Price)
.DefaultIfEmpty(0).Sum()
}).ToArray()
.ToArray();
var sb = new StringBuilder();
var serializer = new XmlSerializer(typeof(ExportCategoryDto[]), new XmlRootAttribute("categories"));
var xmlNamespace = new XmlSerializerNamespaces(new[] { XmlQualifiedName.Empty });
serializer.Serialize(new StringWriter(sb), categories, xmlNamespace);
File.WriteAllText("ExportedXmls/categories-by-products.xml", sb.ToString());
}
private static void QueryProductsInRangeXml(ProductShopDbContext db)
{
// Get all products in a specified price range between 1000 and 2000(inclusive) which have a buyer. Order them by price(from lowest to highest). Select only the product name, price and the full name of the buyer. Export the result to XML.
var productsInRange = db.Products
.Include(p => p.Buyer)
.Where(p => p.Price >= 1000 && p.Price <= 2000 && p.BuyerId != null)
.OrderBy(p => p.Price)
.Select(p => new
{
name = p.Name,
price = p.Price,
buyer = p.Buyer.FirstName + " " + p.Buyer.LastName
});
XDocument documentXml = new XDocument();
XElement productListXml = new XElement("products");
foreach (var product in productsInRange)
{
XElement productXml = new XElement("product");
productXml.SetAttributeValue("name", product.name);
productXml.SetAttributeValue("price", product.price);
productXml.SetAttributeValue("buyer", product.buyer);
productListXml.Add(productXml);
}
documentXml.Add(productListXml);
documentXml.Save("ExportXml/productsInRange.xml");
}
static string ImportCategoriesFromXml()
{
string path = "Files/categories.xml";
var xmlStr = File.ReadAllText(path);
var xmlDoc = XDocument.Parse(xmlStr);
var elements = xmlDoc.Root.Elements();
var categories = new List <Category>();
foreach (var e in elements)
{
string catName = e.Element("name").Value;
Category cat = new Category()
{
Name = catName
};
categories.Add(cat);
}
using (var db = new ProductShopDbContext())
{
db.Categories.AddRange(categories);
db.SaveChanges();
}
return($"{categories.Count()} categories were imported successfully from {path}!");
}
private static void ResetDatabase()
{
using (var db = new ProductShopDbContext())
{
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
}
}
private static void InitializeDatabase(ProductShopDbContext context, IMapper mapper)
{
context.Database.Migrate();
ImportCategories(context, mapper);
ImportUsers(context, mapper);
ImportProducts(context, mapper);
SeedCategoriesProducts(context, mapper);
}
private static void QuerySuccessfullySoldProductsXml(ProductShopDbContext db)
{
//Get all users who have at least 1 sold item. Order them by last name, then by first name. Select the person's first and last name. For each of the sold products, select the product's name and price.
var users = db.Users
.Include(u => u.SoldProducts)
.Where(u => u.SoldProducts.Count >= 1)
.OrderBy(u => u.LastName)
.ThenBy(u => u.FirstName)
.Select(u => new
{
firstName = u.FirstName,
lastName = u.LastName,
soldProducts = u.SoldProducts.Select(sp => new
{
name = sp.Name,
price = sp.Price,
buyerFirstName = sp.Buyer.FirstName,
buyerLastName = sp.Buyer.LastName
})
});
XDocument documentXml = new XDocument();
XElement userListXml = new XElement("users");
foreach (var user in users)
{
XElement userXml = new XElement("user");
if (user.firstName != null)
{
userXml.SetAttributeValue("first-name", user.firstName);
}
userXml.SetAttributeValue("last-user", user.lastName);
XElement productListXml = new XElement("sold-products");
var products = user.soldProducts;
foreach (var product in products)
{
XElement productXml = new XElement("product");
productXml.SetElementValue("name", product.name);
productXml.SetElementValue("price", product.price);
productListXml.Add(productXml);
}
userXml.Add(productListXml);
userListXml.Add(userXml);
}
documentXml.Add(userListXml);
documentXml.Save("ExportXml/soldProducts.xml");
}
static void Main(string[] args)
{
using (var db = new ProductShopDbContext())
{
db.Database.EnsureCreated();
JsonConvert
}
Console.WriteLine("Hello World!");
}
static string ImportCategoriesFromJson()
{
string path = "../../../Files/categories.json";
Category[] categories = ImportJson <Category>(path);
using (var db = new ProductShopDbContext())
{
db.Categories.AddRange(categories);
db.SaveChanges();
}
return($"{categories.Length} categories were imported from file: {path}");
}
static string ImportUsersFromJson()
{
string path = "../../../Files/users.json";
User[] users = ImportJson <User>(path);
using (var db = new ProductShopDbContext())
{
db.Users.AddRange(users);
db.SaveChanges();
}
return($"{users.Length} users were imported from file: {path}");
}
static void Main()
{
using (var context = new ProductShopDbContext())
{
#region /* Uncomment and run to initialize database and recreate missing output files*/
//InitializeDatabase(context);
//GetProductsInRange(context, 500M, 1000M);
//GetSoldProducts(context);
//GetCategoriesByProductCount(context);
//GetUsersAndProducts(context);
#endregion
}
}
static string ImportProductsFromXml()
{
Random rnd = new Random();
string path = "Files/products.xml";
string xmlStr = File.ReadAllText(path);
var xml = XDocument.Parse(xmlStr);
var elements = xml.Root.Elements();
var products = new List <Product>();
using (var db = new ProductShopDbContext())
{
var userIds = db.Users.Select(x => x.UserId).ToList();
foreach (var e in elements)
{
int index = rnd.Next(0, userIds.Count());
int sellerId = userIds[index];
int?buyerId = sellerId;
while (buyerId == sellerId)
{
int buyerIndex = rnd.Next(0, userIds.Count());
buyerId = userIds[buyerIndex];
}
if (buyerId - sellerId < 5)
{
buyerId = null;
}
string name = e.Element("name").Value;
decimal price = decimal.Parse(e.Element("price").Value);
Product product = new Product()
{
Name = name,
Price = price,
SellerId = sellerId,
BuyerId = buyerId
};
products.Add(product);
}
db.Products.AddRange(products);
db.SaveChanges();
}
return($"{products.Count()} products were imported successfully from file: {path}");
}
static void Main()
{
using (var db = new ProductShopDbContext())
{
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
}
Console.WriteLine("Database initialized");
AutoMapperConfiguration.InitializeMapper();
ImportData();
ExportData();
}
private static void GetCategoriesByProductCount(ProductShopDbContext context)
{
var categoriesProductStats = context.Categories
.OrderBy(c => c.Name)
.Select(c => new
{
category = c.Name,
productsCount = c.CategoryProducts.Count,
averagePrice = c.CategoryProducts.Any() ? c.CategoryProducts.Sum(cp => cp.Product.Price) / c.CategoryProducts.Count : 0,
totalRevenue = c.CategoryProducts.Sum(cp => cp.Product.Price)
}).ToArray();
string output = JsonConvert.SerializeObject(categoriesProductStats, Formatting.Indented);
File.WriteAllText(@"..\..\..\Output\categories-by-products.json", output, Encoding.UTF8);
}
