internal static void ListeEmployeCommandeGroupeJoin(int yearSearch, string country)
{
using (ComptoirAnglais_V1Context dbcontext = new ComptoirAnglais_V1Context())
{
/*var employees = dbcontext.Employees.Where(x => x.Country.Contains(country.ToUpper()))
* .GroupJoin(dbcontext.Orders, employe => employe.EmployeeId, order => order.EmployeeId, (employe, order) => new { orders = order
* .Where(y => y.OrderDate.Value.Year == yearSearch), empl = employe });
*
* foreach(var item in employees)
* {
* Console.WriteLine(item.empl.Country+" "+item.empl.City+" "+item.empl.FirstName );
* foreach(var ord in item.orders)
* {
* Console.WriteLine(ord.EmployeeId + " " + ord.OrderId + " " + ord.OrderDate);
* }
* }*/
var employees = dbcontext.Employees.Where(y => y.Country.Contains(country.ToUpper())).Include(x => x.Orders.Where(y => y.OrderDate.Value.Year == yearSearch));
foreach (var item in employees)
{
Console.WriteLine(item.Country + " " + item.City + " " + item.FirstName + " " + item.LastName);
foreach (var ord in item.Orders)
{
Console.WriteLine(ord.EmployeeId + " " + ord.OrderId + " " + ord.OrderDate);
}
}
}
}
internal static void ListeClientNoCommand()
{
using (ComptoirAnglais_V1Context dbcontext = new ComptoirAnglais_V1Context())
{
var query = from customer in dbcontext.Customers
join order in
dbcontext.Orders on customer.CustomerId equals order.CustomerId into orderss
from m in orderss.DefaultIfEmpty()
select new
{
client = customer,
idorder = (int?)m.OrderId
};
//query.Where(x=>x.idorder == null).ToList();
foreach (var item in query.Where(x => x.idorder == null).ToList())
{
Console.WriteLine(item.client.CompanyName + " " + item.client.Country);
}
var query2 = dbcontext.Customers.Include(t => t.Orders).Where(x => x.Orders.Count == 0);
foreach (var item in query2)
{
Console.WriteLine(item.CompanyName + " " + item.Country);
}
}
}
internal static void ClientMoreRemise()
{
using (ComptoirAnglais_V1Context dbcontext = new ComptoirAnglais_V1Context())
{
var query2 = dbcontext.Customers
.Join(dbcontext.Orders, c => c.CustomerId, o => o.CustomerId, (c, o) => new { cus = c, ord = o })
.Join(dbcontext.OrderDetails, p => p.ord.OrderId, t => t.OrderId, (p, t) => new { cusOr = p, orde = t })
.GroupBy(or => new { or.cusOr.cus.CustomerId, or.cusOr.cus.CompanyName })
.Select(y => new {
chiffre = y
.Sum(t => t.orde.Quantity * (t.orde.UnitPrice * (t.orde.Discount == 0 ? (decimal)1 : (decimal)(1 - t.orde.Discount))))
}).Max(t => t.chiffre);
var query = dbcontext.Customers
.Join(dbcontext.Orders, c => c.CustomerId, o => o.CustomerId, (c, o) => new { cus = c, ord = o })
.Join(dbcontext.OrderDetails, p => p.ord.OrderId, t => t.OrderId, (p, t) => new { cusOr = p, orde = t })
.GroupBy(or => new { or.cusOr.cus.CustomerId, or.cusOr.cus.CompanyName })
.Select(y => new {
name = y.Key.CompanyName,
id = y.Key.CustomerId,
chiffre = y
.Sum(t => t.orde.Quantity * (t.orde.UnitPrice * (t.orde.Discount == 0 ? (decimal)1 : (decimal)(1 - t.orde.Discount))))
}).Where(p => p.chiffre == query2);
foreach (var item in query)
{
Console.WriteLine(item.name + " " + item.id + " " + item.chiffre);
}
}
}
internal static void ListeAllClientAllCommande()
{
using (ComptoirAnglais_V1Context dbcontext = new ComptoirAnglais_V1Context())
{
var query = dbcontext.Customers.ToList()
.GroupJoin(dbcontext.Orders, c => c.CustomerId, o => o.CustomerId, (c, o) => new { customer = c, nb = o.Count() });
foreach (var item in query)
{
Console.WriteLine(item.customer.CompanyName + " : " + item.nb);
}
var query2 = dbcontext.Customers.Include(x => x.Orders).Select(s => new { custom = s, nb = s.Orders.Count() });
foreach (var item in query2)
{
Console.WriteLine(item.custom.CompanyName + " nombre de commande : " + item.nb);
}
// pas bonne
var query3 = dbcontext.Customers.Join(dbcontext.Orders, c => c.CustomerId, o => o.CustomerId, (o, c) => new { c.Customer.CompanyName })
.GroupBy(x => x.CompanyName, (name, nombre) => new { key = name, count = nombre.Count() }).OrderBy(t => t.key);
foreach (var item in query3)
{
Console.WriteLine(item.key + " : " + item.count);
}
////
}
}
internal static void AfficheFirstClientCommenceOrder(string name)
{
using (ComptoirAnglais_V1Context dbcontext = new ComptoirAnglais_V1Context())
{
var client = dbcontext.Customers.Where(x => x.CompanyName.StartsWith(name.ToUpper())).OrderBy(y => y.CompanyName).First();
Console.WriteLine(client.CompanyName + " " + client.City);
}
}
internal static void ListeMaxPriceCategorie()
{
using (ComptoirAnglais_V1Context dbcontext = new ComptoirAnglais_V1Context())
{
var query = dbcontext.Categories.Include(x => x.Products);
// .Where(t => t.Products.Select(h => h.UnitPrice)==t.Products.Max(u=>u.UnitPrice));
}
}
internal static void AfficheClientCommence(string name)
{
using (ComptoirAnglais_V1Context dbcontext = new ComptoirAnglais_V1Context())
{
var client = dbcontext.Customers.Where(x => x.CompanyName.StartsWith(name.ToUpper()));
foreach (Customer item in client)
{
Console.WriteLine(item.CompanyName + " " + item.City);
}
}
}
internal static void ListeProduitCategorieNoRupture()
{
using (ComptoirAnglais_V1Context dbcontext = new ComptoirAnglais_V1Context())
{
var query = dbcontext.Categories.Where(x => x.Products.All(x => x.Discontinued == false));
foreach (var item in query)
{
Console.WriteLine(item.CategoryName);
}
}
}
internal static void ListeCategorieProduitRupture()
{
using (ComptoirAnglais_V1Context dbcontext = new ComptoirAnglais_V1Context())
{
var query = dbcontext.Categories.Join(dbcontext.Products.Where(x => x.Discontinued == true), cat => cat.CategoryId, prod => prod.CategoryId, (cat, prod) => new { catId = cat.CategoryId, catName = cat.CategoryName }).Distinct();
foreach (var item in query)
{
Console.WriteLine(item.catId + " " + item.catName);
}
}
}
internal static void ListeClientNoCommandDate(int year)
{
using (ComptoirAnglais_V1Context dbcontext = new ComptoirAnglais_V1Context())
{
var query = dbcontext.Customers.Where(t => t.Orders.Max(u => u.OrderDate).Value.Year <= year);
foreach (var item in query)
{
Console.WriteLine(item.CompanyName + " " + item.Country);
}
}
}
internal static void ChiffreAffaireClient(string country)
{
using (ComptoirAnglais_V1Context dbcontext = new ComptoirAnglais_V1Context())
{
var query = dbcontext.Customers.Where(x => x.Country.Contains(country))//.Include(x => x.Orders).ThenInclude(x => x.OrderDetails)
.Select(x => new
{
nom = x.CompanyName,
chiffre = x.Orders.Select(y => y.OrderDetails.Sum(y => y.Quantity * (y.UnitPrice * (y.Discount == 0 ? (decimal)1 : (decimal)(1 - y.Discount)))))
});
foreach (var item in query)
{
Console.WriteLine(item.nom);
decimal chiffre = 0;
foreach (decimal x in item.chiffre)
{
chiffre += x;
}
Console.WriteLine(chiffre);
}
var query2 = dbcontext.Customers.Where(x => x.Country.Contains(country))
.Join(dbcontext.Orders, c => c.CustomerId, o => o.CustomerId, (c, o) => new { cus = c, ord = o })
.Join(dbcontext.OrderDetails, p => p.ord.OrderId, t => t.OrderId, (p, t) => new { cusOr = p, orde = t })
.GroupBy(or => new { or.cusOr.cus.CustomerId, or.cusOr.cus.CompanyName })
.Select(y => new { name = y.Key.CompanyName, chiffre = y
.Sum(t => t.orde.Quantity * (t.orde.UnitPrice * (t.orde.Discount == 0?(decimal)1:(decimal)(1 - t.orde.Discount)))) });
var query3 = from customer in dbcontext.Customers
join order in
dbcontext.Orders on customer.CustomerId equals order.CustomerId into orderss
from o in orderss.DefaultIfEmpty()
join orderdetail in
dbcontext.OrderDetails on o.OrderId equals orderdetail.OrderId into orderdetails
from or in orderdetails.DefaultIfEmpty()
where customer.Country.StartsWith(country)
select new { name = customer.CompanyName, id = customer.CustomerId, };
//group customer by new { name = customer.CompanyName, id = customer.CustomerId } into c
//select new
//{
// name = c.Key.name,
// id = c.Key.id,
// chiffre = c.Sum(x => x.Orders.Count())
//};
foreach (var item in query2)
{
Console.WriteLine(item.name + " " + item.chiffre);
}
}
}
internal static void ListeEmployeCommande(int year, string country)
{
using (ComptoirAnglais_V1Context dbcontext = new ComptoirAnglais_V1Context())
{
var employeesOrders = dbcontext.Employees.Where(x => x.Country.Contains(country.ToUpper()))
.Join(dbcontext.Orders, employe => employe.EmployeeId, order => order.EmployeeId, (employe, order) => new { name = employe.FirstName, idOrder = order.OrderId, date = order.OrderDate })
.Where(y => y.date.Value.Year == year);
foreach (var item in employeesOrders)
{
Console.WriteLine(item.name + " " + item.idOrder + " " + item.date);
}
}
}
