public static IEnumerable <CustomerViewModel> GetAll(IUnitOfWork db,
CustomerFilterViewModel filter)
{
var orderByCustomerQuery = from o in db.Orders.GetAll()
where o.OrderStatus != OrderStatusEnumEx.Pending
group o by o.CustomerId into byCustomer
select new
{
CustomerId = byCustomer.Key,
OrdersCount = byCustomer.Count(),
};
var sourceOrderQuery = (from c in db.Customers.GetAll()
join o in orderByCustomerQuery on c.Id equals o.CustomerId into withCustomer
from o in withCustomer.DefaultIfEmpty()
select new CustomerViewModel
{
Id = c.Id,
CreateDate = c.CreateDate,
BuyerName = c.Name,
ShippingAddress1 = c.Address1,
ShippingAddress2 = c.Address2,
ShippingCity = c.City,
ShippingZip = c.Zip,
ShippingZipAddon = c.ZipAddon,
ShippingState = c.State,
ShippingCountry = c.Country,
ShippingPhone = c.Phone,
BuyerEmail = c.Email,
OrdersCount = ((int?)o.OrdersCount) ?? 0,
});
if (filter.DateFrom.HasValue)
{
sourceOrderQuery = sourceOrderQuery.Where(o => o.CreateDate >= filter.DateFrom.Value);
}
if (filter.DateTo.HasValue)
{
sourceOrderQuery = sourceOrderQuery.Where(o => o.CreateDate <= filter.DateTo.Value);
}
if (!String.IsNullOrEmpty(filter.BuyerName))
{
sourceOrderQuery = sourceOrderQuery.Where(o => o.BuyerName.Contains(filter.BuyerName));
}
if (!String.IsNullOrEmpty(filter.OrderNumber))
{
sourceOrderQuery = sourceOrderQuery.Where(o => o.OrderNumber == filter.OrderNumber);
}
var results = sourceOrderQuery.ToList();
return(results.OrderByDescending(o => o.CreateDate));
}
public static MemoryStream ExportToExcel(ILogService log,
ITime time,
IUnitOfWork db,
CustomerFilterViewModel filter)
{
var templateName = AppSettings.CustomerReportTemplate;
var gridItems = GetAll(db, filter).ToList();
return(ExcelHelper.ExportIntoFile(HttpContext.Current.Server.MapPath(templateName),
"Template",
gridItems,
null,
1));
}
