public void LoadData() { string username = Request.Cookies["usernameLoginSystem"].Value; var acc = AccountController.GetByUsername(username); if (acc != null) { string TextSearch = String.Empty; string RefundFee = String.Empty; int Status = 0; string CreatedBy = String.Empty; string CreatedDate = String.Empty; int Page = 1; if (Request.QueryString["textsearch"] != null) { TextSearch = Request.QueryString["textsearch"].Trim(); } if (Request.QueryString["status"] != null) { Status = Request.QueryString["status"].ToInt(); } if (Request.QueryString["refundfee"] != null) { RefundFee = Request.QueryString["refundfee"]; } if (Request.QueryString["CreatedBy"] != null) { CreatedBy = Request.QueryString["createdby"]; } if (Request.QueryString["createddate"] != null) { CreatedDate = Request.QueryString["createddate"]; } if (Request.QueryString["Page"] != null) { Page = Request.QueryString["Page"].ToInt(); } txtSearchOrder.Text = TextSearch; ddlStatus.SelectedValue = Status.ToString(); ddlRefundFee.SelectedValue = RefundFee.ToString(); ddlCreatedBy.Text = CreatedBy.ToString(); ddlCreatedDate.SelectedValue = CreatedDate.ToString(); ddlCreatedBy.Enabled = acc.RoleID == 0; var filter = new RefundFilterModel() { search = TextSearch, status = Status, feeStatus = RefundFee, dateTimePicker = CreatedDate, staff = acc.RoleID != 0 ? acc.Username : CreatedBy }; // Create pagination var pagination = new PaginationMetadataModel() { currentPage = Page }; var rs = RefundGoodController.Filter(filter, ref pagination); pagingall(rs, pagination); ltrNumberOfOrder.Text = pagination.totalCount.ToString(); PageCount = pagination.totalPages; } }
public static List <RefundOrder> Filter(RefundFilterModel filter, ref PaginationMetadataModel pagination) { using (var con = new inventorymanagementEntities()) { #region Loại bớt data chỉ lấy những dữ liệu trong 2019-02-15 // ẩn sản phẩm theo thời gian DateTime year = new DateTime(2019, 12, 15); var config = ConfigController.GetByTop1(); if (config.ViewAllOrders == 1) { year = new DateTime(2018, 6, 22); } if (config.ViewAllReports == 0) { year = DateTime.Now.AddMonths(-2); } var refunds = con.tbl_RefundGoods .Where(x => x.CreatedDate.HasValue) .Where(x => x.CreatedDate >= year); #endregion #region Các filter trực tiếp trên RefundGoods table // Filter Status if (filter.status > 0) { refunds = refunds.Where(x => x.Status == filter.status); } // Filter Created By if (!String.IsNullOrEmpty(filter.staff)) { refunds = refunds.Where(x => x.CreatedBy == filter.staff); } // Filter Created Date if (!String.IsNullOrEmpty(filter.dateTimePicker)) { DateTime fromdate = DateTime.Today; DateTime todate = DateTime.Now; switch (filter.dateTimePicker) { case "today": fromdate = DateTime.Today; todate = DateTime.Now; break; case "yesterday": fromdate = fromdate.AddDays(-1); todate = DateTime.Today; break; case "beforeyesterday": fromdate = DateTime.Today.AddDays(-2); todate = DateTime.Today.AddDays(-1); break; case "week": int days = DateTime.Today.DayOfWeek == DayOfWeek.Sunday ? 7 : (int)DateTime.Today.DayOfWeek; fromdate = fromdate.AddDays(-days + 1); todate = DateTime.Now; break; case "thismonth": fromdate = new DateTime(fromdate.Year, fromdate.Month, 1); todate = DateTime.Now; break; case "lastmonth": var thismonth = new DateTime(fromdate.Year, fromdate.Month, 1); fromdate = thismonth.AddMonths(-1); todate = thismonth; break; case "7days": fromdate = DateTime.Today.AddDays(-6); todate = DateTime.Now; break; case "30days": fromdate = DateTime.Today.AddDays(-29); todate = DateTime.Now; break; } refunds = refunds .Where(x => x.CreatedDate.HasValue) .Where(x => x.CreatedDate >= fromdate) .Where(x => x.CreatedDate <= todate); } // Filter Search if (!String.IsNullOrEmpty(filter.search)) { var customerFilter = con.tbl_Customer .Where(x => x.CustomerName.Contains(filter.search) || x.Nick.Contains(filter.search) || x.CustomerPhone == filter.search ) .Join( refunds.Where(x => x.CustomerID.HasValue), c => c.ID, r => r.CustomerID, (c, r) => new { refundID = r.ID } ) .Distinct(); var refundDetailFilter = con.tbl_RefundGoodsDetails .Where(x => x.SKU.StartsWith(filter.search)) .Where(x => x.RefundGoodsID.HasValue) .Join( refunds, d => d.RefundGoodsID.Value, r => r.ID, (d, r) => d ) .Select(x => new { refundID = x.RefundGoodsID.Value }) .Distinct(); refunds = refunds.Where(x => x.ID.ToString() == filter.search || x.OrderSaleID.ToString() == filter.search || customerFilter.Where(c => c.refundID == x.ID).Any() || refundDetailFilter.Where(d => d.refundID == x.ID).Any() ); } #endregion #region Lấy thông tin khách hàng var customer = con.tbl_Customer .Join( refunds .Where(x => x.CustomerID.HasValue) .Select(x => new { CustomerID = x.CustomerID.Value }) .Distinct(), c => c.ID, r => r.CustomerID, (c, r) => c ) .Select(x => new { customerID = x.ID, customerName = x.CustomerName, customerPhone = x.CustomerPhone, nick = x.Nick }) .ToList(); #endregion #region Trường hợp filter đặc biệt var data = refunds .Select(x => new { refundID = x.ID, status = x.Status, dateDone = x.CreatedDate, customerID = x.CustomerID, totalQuantity = x.TotalQuantity, totalPrice = String.IsNullOrEmpty(x.TotalPrice) ? "0" : x.TotalPrice, totalRefundFee = String.IsNullOrEmpty(x.TotalRefundFee) ? "0" : x.TotalRefundFee, orderID = x.OrderSaleID, note = x.RefundNote, createdBy = x.CreatedBy }) .ToList(); #region Filter TotalRefundFee (do column kiểu nvarchar) // Filter Fee Status if (!String.IsNullOrEmpty(filter.feeStatus) && filter.feeStatus == "yes") { data = data.Where(x => Convert.ToDecimal(x.totalRefundFee) > 0).ToList(); } else if (!String.IsNullOrEmpty(filter.feeStatus) && filter.feeStatus == "no") { data = data.Where(x => Convert.ToDecimal(x.totalRefundFee) == 0).ToList(); } #endregion #endregion #region Tính toán phân trang // Calculate pagination pagination.totalCount = data.Count(); pagination.totalPages = (int)Math.Ceiling(pagination.totalCount / (double)pagination.pageSize); data = data .OrderByDescending(x => x.refundID) .Skip((pagination.currentPage - 1) * pagination.pageSize) .Take(pagination.pageSize) .ToList(); #endregion #region Xuất dữ liệu var result = data .Join( customer, d => d.customerID, c => c.customerID, (d, c) => new RefundOrder() { ID = d.refundID, CreatedDate = d.dateDone.Value, Status = d.status.HasValue ? d.status.Value : 0, CustomerID = c.customerID, CustomerName = c.customerName, CustomerPhone = c.customerPhone, Nick = c.nick, Quantity = d.totalQuantity, TotalPrice = Convert.ToDouble(d.totalPrice), TotalRefundFee = Convert.ToDouble(d.totalRefundFee), OrderSaleID = d.orderID, RefundNote = d.note, CreatedBy = d.createdBy } ) .ToList(); #endregion return(result); } }