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); } }