public static List <RegisterProductList> Filter(RegisterProductFilterModel filter, ref PaginationMetadataModel page)
{
using (var con = new inventorymanagementEntities())
{
var data = con.RegisterProducts.Where(x => 1 == 1);
var staff = con.tbl_Account.Where(x => 1 == 1);
#region Thực hiện trích xuất dữ liệu theo bộ lọc
// Filter theo ký tự search
if (!String.IsNullOrEmpty(filter.search))
{
data = data.Where(x =>
x.Title.Contains(filter.search) ||
x.Customer.Contains(filter.search) ||
x.SKU.Contains(filter.search)
);
}
// Filter theo category
if (filter.category > 0)
{
var parentCatogory = con.tbl_Category.Where(x => x.ID == filter.category).FirstOrDefault();
var catogoryFilter = CategoryController.getCategoryChild(parentCatogory).Select(x => x.ID).ToList();
var product = con.tbl_Product
.Where(x =>
catogoryFilter.Contains(
x.CategoryID.HasValue ? x.CategoryID.Value : 0
)
);
data = data
.Join(
product,
d => d.ProductID,
p => p.ID,
(d, p) => d
);
}
// Filter theo trang thái
if (filter.status != 0)
{
data = data.Where(x => x.Status == filter.status);
}
// Filter theo người khở tạo
if (!String.IsNullOrEmpty(filter.createdBy))
{
staff = staff.Where(x => x.Username == filter.createdBy);
data = data
.Join(
staff,
d => d.CreatedBy,
s => s.ID,
(d, s) => d
);
}
// Filter Order Created Date
if (filter.fromDate.HasValue && filter.toDate.HasValue)
{
data = data.Where(x =>
x.CreatedDate >= filter.fromDate.Value &&
x.CreatedDate <= filter.toDate.Value
);
}
// Filter color
if (!String.IsNullOrEmpty(filter.color))
{
data = data.Where(x => x.Color.Contains(filter.color));
}
// Filter color
if (!String.IsNullOrEmpty(filter.size))
{
data = data.Where(x => x.Size.Contains(filter.size));
}
// Filter theo đã chọn
if (filter.selected)
{
var session = SessionController.getRegisterProductSession(filter.account)
.Select(x => x.id)
.ToList();
data = data.Where(x => session.Contains(x.ID));
}
#endregion
#region Tính toán phân trang
// Calculate pagination
page.totalCount = data.Count();
page.totalPages = (int)Math.Ceiling(page.totalCount / (double)page.pageSize);
data = data
.OrderByDescending(x => x.ID)
.Skip((page.currentPage - 1) * page.pageSize)
.Take(page.pageSize);
#endregion
var result = data
.Join(
staff,
d => d.CreatedBy,
s => s.ID,
(d, s) => new {
register = d,
staff = s
}
)
.OrderByDescending(o => o.register.ID)
.ToList();
return(result
.Select(x => {
var statusName = "";
switch (x.register.Status)
{
case (int)RegisterProductStatus.Approve:
statusName = "Đã duyệt";
break;
case (int)RegisterProductStatus.Ordering:
statusName = "Đã đặt hàng";
break;
case (int)RegisterProductStatus.Done:
statusName = "Hàng về";
break;
default:
statusName = "Chưa duyệt";
break;
}
return new RegisterProductList()
{
check = false,
id = x.register.ID,
customer = x.register.Customer,
productID = x.register.ProductID,
variableID = x.register.VariableID,
sku = x.register.SKU,
productStyle = x.register.ProductStyle,
status = x.register.Status,
statusName = statusName,
title = x.register.Title,
image = x.register.Image,
color = x.register.Color,
size = x.register.Size,
numberchild = x.register.NumberChild,
quantity = x.register.Quantity,
expectedDate = x.register.ExpectedDate,
note1 = x.register.Note1,
note2 = x.register.Note2,
staffID = x.register.CreatedBy,
staffName = x.staff.Username,
createdDate = x.register.CreatedDate
};
})
.ToList());
}
}
public static List <Models.Pages.cron_job_product_status.ProductModel> getScheduleProductStatus(FilterModel filter, ref PaginationMetadataModel page)
{
using (var con = new inventorymanagementEntities())
{
#region Lọc ra sẩn phẩn cần show
var products = con.tbl_Product.Where(x => 1 == 1);
var schedules = con.CronJobProductStatus
.Where(x => x.CreatedDate >= filter.fromDate)
.Where(x => x.CreatedDate <= filter.toDate);
// Lọc theo thông tin sản phẩm
if (!String.IsNullOrEmpty(filter.search))
{
products = products.Where(x =>
x.ID.ToString() == filter.search ||
x.ProductSKU.Contains(filter.search) ||
x.ProductTitle.Contains(filter.search)
);
}
// Lọc theo thông tin lịch chạy cron cập nhật trạng thái sản phẩm
if (!String.IsNullOrEmpty(filter.web))
{
schedules = schedules.Where(x => x.Web.EndsWith(filter.web));
}
// Lọc trạng thái cron job
if (filter.status > 0)
{
schedules = schedules.Where(x => x.Status == filter.status);
}
// Lọc theo danh mục
if (filter.category > 0)
{
var category = CategoryController.GetByID(filter.category);
var categoryID = CategoryController.getCategoryChild(category)
.Select(x => x.ID)
.OrderBy(o => o)
.ToList();
schedules = schedules.Where(x => categoryID.Contains(x.CategoryID));
}
// Lọc trạng thái ẩn hiện của sẩn phẩm
if (filter.isHidden.HasValue)
{
schedules = schedules.Where(x => x.IsHidden == filter.isHidden.Value);
}
#endregion
// Lọc theo trạng thái ẩn hiện trang quảng cáo
if (!String.IsNullOrEmpty(filter.showHomePage))
{
products = products.Where(x =>
x.ShowHomePage.ToString() == filter.showHomePage
);
}
#region Tính toán phân trang
var data = schedules
.Join(
products,
s => s.ProductID,
p => p.ID,
(s, p) => new { product = p, schedule = s }
)
.Join(
con.tbl_Category,
tem => tem.schedule.CategoryID,
c => c.ID,
(tem, c) => new { product = tem.product, schedule = tem.schedule, category = c }
)
.Select(x => new
{
categoryID = x.schedule.ID,
categoryName = x.category.CategoryName,
id = x.schedule.ProductID,
sku = x.product.ProductSKU,
title = x.product.ProductTitle,
image = x.product.ProductImage,
costOfGood = x.product.CostOfGood.HasValue ? x.product.CostOfGood.Value : 0,
regularPrice = x.product.Regular_Price.HasValue ? x.product.Regular_Price.Value : 0,
retailPrice = x.product.Retail_Price.HasValue ? x.product.Retail_Price.Value : 0,
quantity = x.schedule.Quantity,
web = x.schedule.Web,
showHomePage = x.product.ShowHomePage.HasValue ? x.product.ShowHomePage.Value : 0,
isHidden = x.schedule.IsHidden,
cronJobStatus = x.schedule.Status,
startDate = x.schedule.ModifiedDate,
note = x.schedule.Note,
productCreatedDate = x.product.CreatedDate.Value
});
// Sort theo filter
if (filter.sort == "ProductCreationDate")
{
data = data.OrderByDescending(x => x.productCreatedDate);
}
else if (filter.sort == "QuantityAsc")
{
data = data.OrderBy(x => x.quantity);
}
else if (filter.sort == "QuantityDesc")
{
data = data.OrderByDescending(x => x.quantity);
}
else
{
data = data.OrderByDescending(x => x.startDate);
}
// Calculate pagination
page.totalCount = data.Count();
page.totalPages = (int)Math.Ceiling(page.totalCount / (double)page.pageSize);
data = data
.Skip((page.currentPage - 1) * page.pageSize)
.Take(page.pageSize);
#endregion
var result = data.Select(x => new Models.Pages.cron_job_product_status.ProductModel()
{
categoryID = x.categoryID,
categoryName = x.categoryName,
id = x.id,
sku = x.sku,
title = x.title,
image = x.image,
costOfGood = x.costOfGood,
regularPrice = x.regularPrice,
retailPrice = x.retailPrice,
quantity = x.quantity,
web = x.web,
showHomePage = x.showHomePage,
isHidden = x.isHidden,
cronJobStatus = x.cronJobStatus,
startDate = x.startDate,
note = x.note,
productCreatedDate = x.productCreatedDate
})
.ToList();
return(result);
}
}