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