public List<SalesOrder> GetSalesOrderDetailBySalesOrderID(int salesOrderID)
{
using (var context = new Laboratorio2Entities())
{
return context.SalesOrders.Where(e => e.SalesOrderID == salesOrderID).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();
}
}
public List<PersonAddress> GetAddressesByPersonID(int personID)
{
using (var context = new Laboratorio2Entities())
{
return context.PersonAddresses.Where(e => e.BusinessEntityID == personID).ToList();
}
}
public List<EmployeeDepartment> GetEmployeesByDepartmentID(short departmentID)
{
using (var context = new Laboratorio2Entities())
{
return context.EmployeeDepartments.Where(e => e.DepartmentID == departmentID).ToList();
}
}
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<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();
}
}
