public SearchResult <OrderSearchItem> OrderSearch(OrderSearchArgs args)
{
if (UseSession())
{
return(GetOrderResultFromSession(args));
}
else
{
return(GetOrderResultFromDatabase(args));
}
}
private IEnumerable <OrderSearchItem> GetItemsFromDatabase(OrderSearchArgs args)
{
var query = DataSession.Query <Ordering.PurchaseOrderSearch>();
var statusIds = args.GetStatusIds();
if (statusIds.Length > 0)
{
query = query.Where(x => statusIds.Contains(x.StatusID));
}
return(PurchaseOrderSearchUtility.CreateOrderItems(query, args.DisplayOption));
}
public static string GetSearchText(OrderSearchItem item, OrderSearchArgs args)
{
string result = string.Empty;
foreach (var col in args.Columns)
{
if (col.Searchable)
{
result += SearchProperties[col.Name](item);
}
}
return(result.ToLower());
}
private SearchResult <OrderSearchItem> GetOrderResultFromDatabase(OrderSearchArgs args)
{
var builder = DataSession.QueryBuilder <Ordering.PurchaseOrderSearch>();
var statusIds = args.GetStatusIds();
if (statusIds.Length > 0)
{
builder.Where(builder.Restriction(x => x.StatusID).In(statusIds));
}
int recordsTotal = builder.Count();
PurchaseOrderSearchUtility.SetSearch(builder, args);
if (args.StartDate.HasValue)
{
builder.Where(x => x.CreatedDate >= args.StartDate.Value);
}
if (args.EndDate.HasValue)
{
builder.Where(x => x.CreatedDate < args.EndDate.Value);
}
//IncludeMyself comes from a checkbox
//ClientID comes from the logged in user
int clientId = (args.IncludeSelf) ? args.ClientID : -999;
//OtherClientID comes from a dropdownlist where "View All" = -1
int otherClientId = (args.OtherClientID > 0) ? args.OtherClientID : -999;
if (otherClientId > 0)
{
builder.Where(builder.Restriction(x => x.ClientID).In(new[] { clientId, otherClientId }));
}
if (!string.IsNullOrEmpty(args.VendorName))
{
builder.Where(builder.Restriction(x => x.CleanVendorName).Contains(PurchaseOrderSearchUtility.CleanString(args.VendorName)));
}
if (args.VendorID > 0)
{
builder.Where(x => x.VendorID == args.VendorID);
}
if (!string.IsNullOrEmpty(args.Keywords))
{
builder.Where(builder.Restriction(x => x.Description).Contains(args.Keywords));
}
if (!string.IsNullOrEmpty(args.PartNumber))
{
builder.Where(builder.Restriction(x => x.PartNum).Contains(args.PartNumber));
}
if (args.POID > 0)
{
builder.Where(x => x.POID == args.POID);
}
if (!string.IsNullOrEmpty(args.ShortCode))
{
builder.Where(builder.Restriction(x => x.ShortCode).Contains(args.ShortCode));
}
int recordsFiltered = builder.Count();
PurchaseOrderSearchUtility.SetOrder(builder, args);
IList <Ordering.PurchaseOrderSearch> data;
if (args.Length > 0)
{
data = builder.Skip(args.Start).Take(args.Length).List();
}
else
{
data = builder.Skip(args.Start).List();
}
return(new SearchResult <OrderSearchItem>()
{
Draw = args.Draw,
RecordsTotal = recordsTotal,
RecordsFiltered = recordsFiltered,
Data = PurchaseOrderSearchUtility.CreateOrderItems(data, args.DisplayOption).ToArray()
});
}
public static void SetOrder(IQueryBuilder <Ordering.PurchaseOrderSearch> builder, OrderSearchArgs args)
{
if (args.Order == null)
{
return;
}
if (args.Columns == null)
{
return;
}
foreach (var order in args.Order)
{
if (args.Columns.Length > order.Column)
{
var col = args.Columns[order.Column];
if (col.Orderable)
{
var item = ColumnExpressions[col.Name];
if (item != null)
{
if (order.Dir == "desc")
{
builder.OrderByDescending(item.Order);
}
else
{
builder.OrderBy(item.Order);
}
}
}
}
}
}
public static void SetSearch(IQueryBuilder <Ordering.PurchaseOrderSearch> builder, OrderSearchArgs args)
{
if (args.Search == null)
{
return;
}
if (string.IsNullOrEmpty(args.Search.Value))
{
return;
}
if (args.Columns == null)
{
return;
}
var disj = builder.Disjunction();
foreach (var col in args.Columns)
{
if (col.Searchable)
{
var item = ColumnExpressions[col.Name];
if (item != null)
{
disj.Add(builder.Restriction(item.Search).StartsWith(args.Search.Value));
}
}
}
builder.And(disj);
}
private static IEnumerable <OrderSearchItem> Order(IEnumerable <OrderSearchItem> items, OrderSearchArgs args)
{
if (args.Order == null)
{
return(items);
}
if (args.Columns == null)
{
return(items);
}
IOrderedEnumerable <OrderSearchItem> ordered = null;
foreach (var order in args.Order)
{
if (args.Columns.Length > order.Column)
{
var col = args.Columns[order.Column];
if (col.Orderable)
{
if (PurchaseOrderSearchUtility.OrderProperties.TryGetValue(col.Name, out Func <OrderSearchItem, object> fn))
{
if (order.Dir == "desc")
{
ordered = (ordered == null) ? items.OrderByDescending(fn) : ordered.ThenByDescending(fn);
}
else
{
ordered = (ordered == null) ? items.OrderBy(fn) : ordered.ThenBy(fn);
}
}
}
}
}
if (ordered == null)
{
return(items);
}
else
{
return(ordered);
}
}
private static IEnumerable <OrderSearchItem> Search(IEnumerable <OrderSearchItem> items, OrderSearchArgs args)
{
if (args.Search == null)
{
return(items);
}
if (string.IsNullOrEmpty(args.Search.Value))
{
return(items);
}
if (args.Columns == null)
{
return(items);
}
var result = items.Where(x => PurchaseOrderSearchUtility.GetSearchText(x, args).Contains(args.Search.Value.ToLower()));
return(result);
}
private SearchResult <OrderSearchItem> GetOrderResultFromSession(OrderSearchArgs args)
{
IEnumerable <OrderSearchItem> items;
items = GetItemsFromSession();
if (items == null)
{
items = GetItemsFromDatabase(args);
SetSession(items);
}
int recordsTotal = items.Count();
items = Search(items, args);
if (args.StartDate.HasValue)
{
items = items.Where(x => x.CreatedDate >= args.StartDate.Value);
}
if (args.EndDate.HasValue)
{
items = items.Where(x => x.CreatedDate < args.EndDate.Value);
}
//IncludeMyself comes from a checkbox
//ClientID comes from the logged in user
int clientId = (args.IncludeSelf) ? args.ClientID : -999;
//OtherClientID comes from a dropdownlist where "View All" = -1
int otherClientId = (args.OtherClientID > 0) ? args.OtherClientID : -999;
if (otherClientId > 0)
{
items = items.Where(x => new[] { clientId, otherClientId }.Contains(x.ClientID));
}
if (!string.IsNullOrEmpty(args.VendorName))
{
items = items.Where(x => PurchaseOrderSearchUtility.CleanString(x.VendorName).Contains(PurchaseOrderSearchUtility.CleanString(args.VendorName)));
}
if (args.VendorID > 0)
{
items = items.Where(x => x.VendorID == args.VendorID);
}
if (!string.IsNullOrEmpty(args.Keywords))
{
items = items.Where(x => x.Description.Contains(args.Keywords));
}
if (!string.IsNullOrEmpty(args.PartNumber))
{
items = items.Where(x => x.PartNum.Contains(args.PartNumber));
}
if (args.POID > 0)
{
items = items.Where(x => x.POID == args.POID);
}
if (!string.IsNullOrEmpty(args.ShortCode))
{
items = items.Where(x => x.ShortCode.Contains(args.ShortCode));
}
int recordsFiltered = items.Count();
items = Order(items, args);
var take = args.Length > 0 ? args.Length : 10; // default is 10 rows
OrderSearchItem[] data;
if (args.Length > 0)
{
data = items.Skip(args.Start).Take(args.Length).ToArray();
}
else
{
data = items.Skip(args.Start).ToArray();
}
return(new SearchResult <OrderSearchItem>()
{
Draw = args.Draw,
RecordsTotal = recordsTotal,
RecordsFiltered = recordsFiltered,
Data = data
});
}
public JsonResult OrderSearch(OrderSearchArgs orderSearchArgs)
{
OrderService orderService = new OrderService();
List <Orders> orderList = orderService.getAllData();
/**/
IEnumerable <Orders> orderResult = orderList;
// 訂單編號
if (orderSearchArgs.OrderID.HasValue)
{
orderResult = orderResult.Where(m => m.OrderID == orderSearchArgs.OrderID.Value);
}
// 負責員工
if (!string.IsNullOrWhiteSpace(orderSearchArgs.EmployeeName))
{
EmployeeService employeeService = new EmployeeService();
orderResult =
orderResult.Where(
m => employeeService.GetEmployeeName(m.EmployeeID).Contains(orderSearchArgs.EmployeeName)
);
}
// 訂購日期
if (orderSearchArgs.OrderDate.HasValue)
{
orderResult = orderResult.Where(m => m.OrderDate == orderSearchArgs.OrderDate.Value);
}
// 需要日期
if (orderSearchArgs.RequiredDate.HasValue)
{
orderResult = orderResult.Where(m => m.RequiredDate == orderSearchArgs.RequiredDate.Value);
}
// 出貨日期
if (orderSearchArgs.ShippedDate.HasValue)
{
orderResult = orderResult.Where(m => m.ShippedDate == orderSearchArgs.ShippedDate.Value);
}
// 運輸公司
if (!string.IsNullOrWhiteSpace(orderSearchArgs.CompanyName))
{
ShipperService shipperService = new ShipperService();
orderResult =
orderResult.Where(
m => shipperService.GetCompanyName(m.ShipperID).Contains(orderSearchArgs.CompanyName)
);
}
EmployeeService employeesService = new EmployeeService();
List <Employees> employeeList = employeesService.getAllData();
ShipperService shippersService = new ShipperService();
List <Shippers> ShipperList = shippersService.getAllData();
List <OrderSearchArgs> orderSearchArgsList = orderResult.Select(
m => new OrderSearchArgs {
OrderID = m.OrderID,
EmployeeName = employeeList.Single(empM => empM.EmployeeID == m.EmployeeID).FirstName + employeeList.Single(empM => empM.EmployeeID == m.EmployeeID).LastName,
OrderDate = m.OrderDate,
RequiredDate = m.RequiredDate,
ShippedDate = m.ShippedDate,
CompanyName = ShipperList.Single(shipperM => shipperM.ShipperID == m.ShipperID).CompanyName
}).ToList();
return(Json(orderSearchArgsList, JsonRequestBehavior.AllowGet));
}