public List <EmployeeDepartment> GetDepartments(DateTime?startDate, DateTime?endDate, string departmentName)
{
using (var context = new Laboratorio2Entities())
{
IQueryable <EmployeeDepartment> query = context.EmployeeDepartments;
if (startDate.HasValue)
{
query = query.Where(e => e.HireDate >= startDate);
}
if (endDate.HasValue)
{
query = query.Where(e => e.HireDate <= endDate);
}
if (!string.IsNullOrEmpty(departmentName))
{
query = query.Where(e => e.Name.Contains(departmentName));
}
var result = query
.GroupBy(x => new { x.DepartmentID, x.Name, x.GroupName })
.Select(x => new
{
DepartmentID = x.Key.DepartmentID,
Name = x.Key.Name,
GroupName = x.Key.GroupName
})
.ToList()
.Select(x => new EmployeeDepartment
{
DepartmentID = x.DepartmentID,
Name = x.Name,
GroupName = x.GroupName
});
return(result.ToList());
}
}
public List <EmployeeDepartment> GetEmployeesByDepartmentID(short departmentID)
{
using (var context = new Laboratorio2Entities())
{
return(context.EmployeeDepartments.Where(e => e.DepartmentID == departmentID).ToList());
}
}
public List <PersonAddress> GetAddressesByPersonID(int personID)
{
using (var context = new Laboratorio2Entities())
{
return(context.PersonAddresses.Where(e => e.BusinessEntityID == personID).ToList());
}
}
public List <SalesOrder> GetSalesOrderDetailBySalesOrderID(int salesOrderID)
{
using (var context = new Laboratorio2Entities())
{
return(context.SalesOrders.Where(e => e.SalesOrderID == salesOrderID).ToList());
}
}
public List <SalesOrder> GetSales(DateTime?startDate, DateTime?endDate, string productName)
{
using (var context = new Laboratorio2Entities())
{
IQueryable <SalesOrder> query = context.SalesOrders.Take(1000);
if (startDate.HasValue)
{
query = query.Where(e => e.OrderDate >= startDate);
}
if (endDate.HasValue)
{
query = query.Where(e => e.OrderDate <= endDate);
}
if (!string.IsNullOrEmpty(productName))
{
query = query.Where(e => e.Name.Contains(productName));
}
var result = query
.GroupBy(x => new
{
x.SalesOrderID,
x.SalesOrderNumber,
x.PurchaseOrderNumber,
x.OrderDate,
x.Status,
x.TotalDue,
x.Name
})
.Select(x => new
{
SalesOrderID = x.Key.SalesOrderID,
SalesOrderNumber = x.Key.SalesOrderNumber,
PurchaseOrderNumber = x.Key.PurchaseOrderNumber,
OrderDate = x.Key.OrderDate,
Status = x.Key.Status,
TotalDue = x.Key.TotalDue,
Name = x.Key.Name
})
.ToList()
.Select(x => new SalesOrder
{
SalesOrderID = x.SalesOrderID,
SalesOrderNumber = x.SalesOrderNumber,
PurchaseOrderNumber = x.PurchaseOrderNumber,
OrderDate = x.OrderDate,
Status = x.Status,
TotalDue = x.TotalDue,
Name = x.Name
});
return(result.ToList());
}
}
public List <PersonAddress> GetPersons(short type, string city, string province, string country, string name)
{
using (var context = new Laboratorio2Entities())
{
IQueryable <PersonAddress> query = context.PersonAddresses.Where(x => x.Type == type);
if (!string.IsNullOrEmpty(city))
{
query = query.Where(e => e.City.Contains(city));
}
if (!string.IsNullOrEmpty(province))
{
query = query.Where(e => e.StateProvinceName.Contains(province));
}
if (!string.IsNullOrEmpty(country))
{
query = query.Where(e => e.CountryRegionName.Contains(country));
}
if (!string.IsNullOrEmpty(name))
{
query = query.Where(e => e.FirstName.Contains(name));
}
var result = query
.GroupBy(x => new { x.BusinessEntityID, x.FirstName, x.AddressID })
.Select(x => new
{
BusinessEntityID = x.Key.BusinessEntityID,
FirstName = x.Key.FirstName,
AddressID = x.Key.AddressID
})
.ToList()
.Select(x => new PersonAddress
{
BusinessEntityID = x.BusinessEntityID,
FirstName = x.FirstName,
AddressID = x.AddressID
});
return(result.ToList());
}
}
