public PagedResult <IQueryable <CatalogPreviewDTO> > CatalogPreview(
int?currentPage,
int?pageSize,
string catalogName,
string catalogDesc,
int?productId,
int?storeId,
bool?active)
{
// 0. Load data / data source
EfContext context = new EfContext();
// 1. Project
var qry = (from c in context.Catalogs select c);
// 2. Filter
if (!string.IsNullOrEmpty(catalogName))
{
qry = qry.Where(e => e.CatalogName.Contains(catalogName) || e.InternalName.Contains(catalogName));
}
if (!string.IsNullOrEmpty(catalogDesc))
{
qry = qry.Where(e => e.CatalogDesc.Contains(catalogDesc));
}
if (productId != null)
{
qry = qry.Where(e => e.CatalogProducts.Any(f => f.Product.ProductId == productId));
}
if (storeId != null)
{
qry = qry.Where(e => e.Store.StoreId == storeId);
}
if (active != null)
{
qry = qry.Where(e => e.Active == active);
}
// 3. Select to DTO
var items = qry.Select(q => new CatalogPreviewDTO
{
CatalogId = q.CatalogId,
CatalogName = q.CatalogName,
InternalName = q.InternalName,
StoreId = q.Store.StoreId,
StoreName = q.Store.StoreName,
Active = q.Active,
ProductCount = q.CatalogProducts.Select(e => e.Product).Distinct().Count()
});
// 4. Sort
items = items.OrderBy(o => o.CatalogName);
// 5. Page
return(PagedResult <PreviewDetailDTO> .AutoPage(items, currentPage, pageSize));
}
public PagedResult<IQueryable<ProductPreviewDto>> ProductPreview(
int? currentPage,
int? pageSize,
string productName,
string productDesc,
int? catalogId,
bool? active)
{
// 0. Load data / data source
EfContext context = new EfContext();
// 1. Project
var qry = (from p in context.Products
from catprod in context.CatalogProducts.Where(e => e.Product == p).DefaultIfEmpty()
group catprod.Catalog by p
into grp
select new
{
Product = grp.Key,
Catalogs = grp
});
// 2. Filter
if (!string.IsNullOrEmpty(productName))
qry = qry.Where(e => e.Product.ProductName.Contains(productName));
if (!string.IsNullOrEmpty(productDesc))
qry = qry.Where(e =>
e.Product.ProductDesc.Contains(productDesc) || e.Product.ProductRichDesc.Contains(productDesc));
if (catalogId != null)
qry = qry.Where(e => e.Catalogs.Where(w => w != null).Any(f => f.CatalogId == catalogId));
if (active != null)
qry = qry.Where(e => e.Product.Active == active);
// 3. Select to DTO
var items = qry.Select(q => new ProductPreviewDto
{
ProductId = q.Product.ProductId,
ProductName = q.Product.ProductName,
Active = q.Product.Active,
Catalogs = q.Catalogs
.Where(w => w != null)
.Select(c => c.CatalogName)
.ToList()
});
// 4. Sort
items = items.OrderBy(o => o.ProductName);
// 5. Page
return PagedResult<PreviewDetailDTO>.AutoPage(items, currentPage, pageSize);
}
public PagedResult <IQueryable <StorePreviewDTO> > StorePreview(
int?currentPage,
int?pageSize,
string storeName,
int?storeStatusId,
int?importantConfigId,
string owner,
int?catalogId,
bool?active)
{
// 0. Load data / data source
EfContext context = new EfContext();
// 1. Project
var qry = (from s in context.Stores
from storecat in context.Catalogs.Where(e => e.Store == s).DefaultIfEmpty()
group storecat by s into grp
select new
{
Store = grp.Key,
Catalogs = grp
});
// 2. Filter
if (!string.IsNullOrEmpty(storeName))
{
qry = qry.Where(e => e.Store.StoreName.Contains(storeName));
}
if (storeStatusId != null)
{
qry = qry.Where(e => e.Store.Status.StoreStatusId == storeStatusId);
}
if (importantConfigId != null)
{
qry = qry.Where(e => e.Store.ImportantConfigId == importantConfigId);
}
if (!string.IsNullOrEmpty(owner))
{
qry = qry.Where(e => e.Store.Owner.Contains(owner));
}
if (catalogId != null)
{
qry = qry.Where(e => e.Catalogs.Where(w => w != null).Any(f => f.CatalogId == catalogId));
}
if (active != null)
{
qry = qry.Where(e => e.Store.Active == active);
}
// 3. Select to DTO
var items = qry.Select(q => new StorePreviewDTO
{
StoreId = q.Store.StoreId,
StoreName = q.Store.StoreName,
StoreStatusName = q.Store.Status.StoreStatusName,
ImportantConfigId = q.Store.ImportantConfigId,
Owner = q.Store.Owner,
CatalogCount = q.Catalogs.Count(w => w != null),
Active = q.Store.Active
});
// 4. Sort
items = items.OrderBy(o => o.StoreName);
// 5. Page
return(PagedResult <PreviewDetailDTO> .AutoPage(items, currentPage, pageSize));
}
public PagedResult <IQueryable <PreviewDetailDTO> > PreviewDetailPreview(
int?currentPage,
int?pageSize,
string name,
int?categoryId,
bool?active,
DateTime?startDate,
DateTime?endDate)
{
// 0. Load data / data source
List <PreviewDetailEntity> previewDetail = _loader.LoadFromFile <List <PreviewDetailEntity> >("preview-detail");
List <CategoryEntity> categories = _loader.LoadFromFile <List <CategoryEntity> >("category");
// 1. Project
var qry = (from pd in previewDetail
from cat in categories.Where(e => pd.CategoryIds != null && pd.CategoryIds.Contains(e.CategoryId)).DefaultIfEmpty()
group cat by pd
into grp
select new { previewDetail = grp.Key, categories = grp })
.AsQueryable(); //Not needed if using entity framework - want to ensure pattern works with IQueryable
// 2. Filter
if (!string.IsNullOrEmpty(name))
{
qry = qry.Where(e => e.previewDetail.Name != null && e.previewDetail.Name.IndexOf(name, StringComparison.OrdinalIgnoreCase) >= 0);
}
if (categoryId != null)
{
qry = qry.Where(e => e.categories.Where(w => w != null).Any(f => f.CategoryId == categoryId));
}
if (active != null)
{
qry = qry.Where(e => e.previewDetail.Active == active);
}
if (startDate != null)
{
qry = qry.Where(e => e.previewDetail.Date >= startDate);
}
if (endDate != null)
{
qry = qry.Where(e => e.previewDetail.Date <= endDate);
}
// 3. Select to DTO
IQueryable <PreviewDetailDTO> items = qry.Select(q => new PreviewDetailDTO
{
PreviewDetailId = q.previewDetail.PreviewDetailId,
Name = q.previewDetail.Name,
Active = q.previewDetail.Active,
Date = q.previewDetail.Date,
Categories = q.categories
.Where(w => w != null) //TODO: Figure out why grouping results in 1 null category instead of 0 categories
.Select(c => c.CategoryName)
.ToList(),
Codes = new List <string> {
"TODO 1", "todo 2"
}
});
// 4. Sort
items = items.OrderBy(o => o.Name);
// 5. Page
return(PagedResult <PreviewDetailDTO> .AutoPage(items, currentPage, pageSize));
}
