public JsonResult RoleList()
{
var request = new DataTablesRequest <ManagementService.Model.ViewModel.RoleViewModel>(Request?.QueryString.Value);
IPagedList <ManagementService.Model.ViewModel.RoleViewModel> model = _userService.GetRoles(request);
return(JsonDataTable(model, request.Draw));
}
public JsonResult list()
{
var request = new DataTablesRequest <Model.ViewModel.UserViewModel>(Request?.QueryString.Value);
IPagedList <Model.ViewModel.UserViewModel> model = _userService.GetUsers(request);
return(JsonDataTable(model, request.Draw)); //new Model.ViewModel.DataTabaleAjaxViewModel<List<Model.ViewModel.UserViewModel>>() { Data = model };
}
// for display datatable
public ActionResult GetCategoryList(DataTablesRequest param)
{
//http://www.justinmichaels.net/using-jquery-datatables-with-asp-net-mvc-for-serverside-filtering-sorting-and-paging
var categoryList = _db.Categories.AsEnumerable();
var viewCategoryList = categoryList;
IEnumerable <Category> filteredCategoryList;
if (!string.IsNullOrEmpty(param.sSearch))
{
filteredCategoryList = viewCategoryList.Where(cat => (cat.Name ?? "").Contains(param.sSearch)).ToList();
}
else
{
filteredCategoryList = viewCategoryList;
}
var viewDataList = filteredCategoryList.Skip(param.iDisplayStart).Take(param.iDisplayLength).ToList();
string[] data = viewDataList.ConvertAll(x => x.ToString()).ToArray();
var result = DataTablesResponse.CreateResponse(param, categoryList.Count(), filteredCategoryList.Count(), data);
return(Json(result, JsonRequestBehavior.AllowGet));
}
/// <summary>
///
/// </summary>
/// <param name="dataRequest"></param>
/// <returns></returns>
public IActionResult GetOrderList([DataTablesRequest] DataTablesRequest dataRequest)
{
IEnumerable <OrderRequest> orders = _orderProxy.GetAll();
int recordsTotal = orders.Count();
int recordsFilterd = recordsTotal;
//if (!string.IsNullOrEmpty(dataRequest.Search?.Value))
//{
// orders = orders.Where(e => e.OrderNo.Contains(dataRequest.Search.Value));
// recordsFilterd = orders.Count();
//}
orders = orders.Skip(dataRequest.Start).Take(dataRequest.Length);
var json = Json(orders
.Select(e => new
{
Id = e.Id,
OrderNo = e.OrderNo,
OrderDate = e.OrderDate,
TotalAmount = e.TotalAmount,
TotalDiscount = e.TotalDiscount,
Status = e.Status.ToString()
})
.ToDataTablesResponse(dataRequest, recordsTotal, recordsFilterd));
return(json);
}
protected Query ProcessColumnSearch(DataTablesRequest request, Query baseQuery)
{
//Apply column search. Always check the column configuration
//to validate that the column is actually searchable
var searchColumns = request.Columns.Where(sc => sc.HasSearch && (Configuration.Column(sc.Name)?.Searchable ?? false));
if (searchColumns.Any())
{
foreach (var col in searchColumns)
{
var colConfig = Configuration.Column(col.Name);
var convertToType = Nullable.GetUnderlyingType(colConfig.Type) ?? colConfig.Type;
var searchValue = Convert.ChangeType(col.Search.Value, convertToType);
//TODO use operators to generate the WHERE clause
if (colConfig.Type == typeof(string))
{
baseQuery.WhereStarts(colConfig.Column, searchValue.ToString());
}
else
{
baseQuery.Where(col.Name, searchValue);
}
}
}
return(baseQuery);
}
public async Task <IActionResult> BenefitUploadListByClient([DataTablesRequest] DataTablesRequest dataRequest, string EnrollmentHdrID)
{
int.TryParse(EnrollmentHdrID.Trim(), out int _clientID);
var query = string.Format(@"SELECT bu.ID,bu.PlanId,bu.CorpCode,cc.ShortName CoverageCode,bu.BenefitCode,bc.Code BenefitShortName,
bc.Description BenefitDescription,bu.ActiveFlag,bu.ConditionDescription,bu.LOA_Description,bu.ClientBenefitcode,
lc.Description LimitCode,fc.Description FrequencyCode,bu.MaxValue,bu.MultipleCondition,
CONCAT(CONVERT(VARCHAR(30),bu.UploadDate,106),' ',CONVERT(VARCHAR(30),bu.UploadDate,108)) UploadDate,bu.UserUpload,
CONCAT(bu.errPlanId+'<br>',bu.errCorpCode+'<br>',bu.errCoverageCode+'<br>',bu.errBenefitCode+'<br>',bu.errConditionDescription+'<br>',
bu.errLOA_Description+'<br>',bu.errActiveFlag+'<br>',bu.errClientBenefitcode+'<br>',bu.errLimitCode+'<br>',bu.errFrequencyCode+'<br>',
bu.errMaxValue+'<br>') ErrorMessage
FROM stg.BenefitUpload bu
LEFT JOIN dbo.CoverageCodes cc ON cc.ID = bu.CoverageCode
LEFT JOIN dbo.BenefitCodes bc ON bc.ID = bu.BenefitCode
LEFT JOIN FrequencyCodes fc ON fc.ID = bu.FrequencyCode
LEFT JOIN LimitCodes lc ON lc.ID = bu.LimitCode
WHERE bu.EnrollmentHdrID=@EnrollmentHdrID ");
var ls = new List <BenefitUploadVM>();
try
{
var _params = new SqlParameter {
ParameterName = "@EnrollmentHdrID", SqlDbType = SqlDbType.Int, Value = _clientID
};
ls = await _context.Set <BenefitUploadVM>().FromSql(query, _params).ToListAsync();
}
catch (Exception ex)
{
flashMessage.Danger("Error Paging benefit : " + ex.Message);
throw new Exception();
}
return(Json(ls.ToDataTablesResponse(dataRequest, ls.Count, ls.Count)));
}
public IActionResult GetUserList([DataTablesRequest] DataTablesRequest dataRequest)
{
IEnumerable <ApplicationUserRequest> users = _proxy.GetAllUser();
int recordsTotal = users.Count();
int recordsFilterd = recordsTotal;
//if (!string.IsNullOrEmpty(dataRequest.Search?.Value))
//{
// users = users.Where(e => e.Email.Contains(dataRequest.Search.Value));
// recordsFilterd = users.Count();
//}
users = users.Skip(dataRequest.Start).Take(dataRequest.Length);
var json = Json(users
.Select(e => new
{
Id = e.Id,
UserName = e.UserName,
Email = e.Email,
Role = e.Role,
ContactNo = e.ContactNo,
IsLocked = e.IsLocked
})
.ToDataTablesResponse(dataRequest, recordsTotal, recordsFilterd));
return(json);
}
protected object Bind()
{
var request = new DataTablesRequest();
request.Challenge = GetRequired <int>("sEcho");
request.DisplayedColumns = GetRequired <int>("iColumns");
request.DisplayStart = GetRequired <int>("iDisplayStart");
request.DisplayLength = GetRequired <int>("iDisplayLength");
request.GlobalSearch = GetRequired <string>("sSearch");
request.IsGlobalSearchRegex = GetRequired <bool>("bRegex");
request.SortedColumns = GetRequired <int>("iSortingCols");
for (int i = 0; i < request.DisplayedColumns; i++)
{
var column = new DataTablesColumnSearchRequest();
column.ColumnIndex = i;
column.IsSearchable = GetRequired <bool>("bSearchable_" + i);
column.IsSortable = GetRequired <bool>("bSortable_" + i);
column.Search = GetRequired <string>("sSearch_" + i);
column.IsSearchRegex = GetRequired <bool>("bRegex_" + i);
request.SearchColumns.Add(column);
}
for (int i = 0; i < request.SortedColumns; i++)
{
var column = new DataTablesColumnSortRequest();
column.ColumnIndex = GetRequired <int>("iSortCol_" + i);;
column.Direction = GetRequired <string>("sSortDir_" + i) == "asc" ? SortingDirection.Ascending : SortingDirection.Descending;
request.SortColumns.Add(column);
}
return(request);
}
/// <summary>
/// Applies the given <see cref="DataTablesRequest{TEntity, TEntityViewModel}"/> to the given
/// <see cref="IQueryable{T}"/> by performing a few consecutive steps. The method will apply
/// the global filter predicate, global search, column search and column ordering.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <typeparam name="TEntityViewModel">The type of the entity view model.</typeparam>
/// <param name="queryable">The queryable.</param>
/// <param name="request">The request.</param>
public static IDataTablesQueryable <TEntity, TEntityViewModel> Apply <TEntity, TEntityViewModel>(
this IQueryable <TEntity> queryable, DataTablesRequest <TEntity, TEntityViewModel> request)
{
queryable = queryable.AsDataTablesQueryable(request)
.ApplyGlobalSearchFilter()
.ApplyColumnSearchFilter()
.ApplyOrder();
if (request.Log != null)
{
var sb = new StringBuilder("DataTables.Queryable -> Incoming request:\n");
foreach (string key in request.OriginalRequest.AllKeys)
{
string value = request.OriginalRequest[key];
sb.AppendLine($"{key} = {$"\"{value}\""}");
}
sb.AppendLine();
sb.AppendLine($"DataTables.Queryable -> Resulting queryable:\n{queryable}\n");
request.Log.Invoke(sb.ToString());
}
return((IDataTablesQueryable <TEntity, TEntityViewModel>)queryable);
}
public IEnumerable <VisitViewModel> GetVisitsByFilter(DataTablesRequest dataTablesRequest, VisitFilter visitFilter, out int totalRecordsNumber)
{
IQueryable <Visite> query = db.Visites.AsQueryable <Visite>();
if (!String.IsNullOrEmpty(visitFilter.VisitDate))
{
DateTime date = DateTime.Parse(visitFilter.VisitDate);
query = query.Where(x => x.VisiteDate.Year == date.Year).Where(x => x.VisiteDate.Month == date.Month).Where(x => x.VisiteDate.Day == date.Day);
}
if (visitFilter.DoctorName != "-1")
{
query = query.Where(x => (x.Doctor.FirstName + " " + x.Doctor.LastName == visitFilter.DoctorName));
}
totalRecordsNumber = query.Count();
query = query.OrderBy(x => x.DoctorId).Skip(dataTablesRequest.iDisplayStart).Take(dataTablesRequest.iDisplayLength);
var visistTableList = query.ToList <Visite>().Select(v => new VisitViewModel()
{
Description = v.Description,
City = v.Patient.City.CityName,
DoctorName = v.Doctor.FirstName + " " + v.Doctor.LastName,
DoctorSpecialization = v.Doctor.MedicalSpecialization.Specialization,
PatientName = v.Patient.FirstName + " " + v.Patient.LastName,
VisitDate = v.VisiteDate.ToString()
});
return(visistTableList);
}
public async Task <IActionResult> PlanUploadListByClient([DataTablesRequest] DataTablesRequest dataRequest, string EnrollmentHdrID)
{
int.TryParse(EnrollmentHdrID.Trim(), out int _clientID);
var query = string.Format(@"SELECT pu.ID,pu.PayorCode,pu.PlanId,pu.CorpCode,pu.EffectiveDate,pu.TerminationDate,pu.ActiveFlag,
pu.ShortName,pu.LongName,pu.Remarks,pu.PolicyNo,fc.Description FrequencyCode,lc.Description LimitCode,pu.MaxValue,
pu.FamilyMaxValue,pu.PrintText,CONCAT(CONVERT(VARCHAR(30),pu.UploadDate,106),' ',CONVERT(VARCHAR(30),pu.UploadDate,108)) UploadDate,
pu.UserUpload,CONCAT(pu.errPayorCode+'<br>',pu.errPlanId+'<br>',pu.errCorpCode+'<br>',pu.errEffectiveDate+'<br>',pu.errTerminationDate+'<br>',pu.errActiveFlag+'<br>',
pu.errFrequencyCode+'<br>',pu.errLimitCode+'<br>',pu.errMaxValue+'<br>',pu.errFamilyMaxValue) ErrorMessage
FROM stg.PlanUpload pu
LEFT JOIN FrequencyCodes fc ON fc.ID = pu.FrequencyCode
LEFT JOIN LimitCodes lc ON lc.ID = pu.LimitCode
WHERE pu.EnrollmentHdrID=@EnrollmentHdrID ");
var ls = new List <PlanUploadVM>();
try
{
var _params = new SqlParameter {
ParameterName = "@EnrollmentHdrID", SqlDbType = SqlDbType.Int, Value = _clientID
};
ls = await _context.Set <PlanUploadVM>().FromSql(query, _params).ToListAsync();
}
catch (Exception ex) { throw new Exception(ex.Message); }
return(Json(ls.ToDataTablesResponse(dataRequest, ls.Count, ls.Count)));
}
/// <summary>
///
/// </summary>
/// <param name="dataRequest"></param>
/// <returns></returns>
public IActionResult GetProductList([DataTablesRequest] DataTablesRequest dataRequest)
{
IEnumerable <ProductRequest> products = _productProxy.GetAll();
int recordsTotal = products.Count();
int recordsFilterd = recordsTotal;
//if (!string.IsNullOrEmpty(dataRequest.Search?.Value))
//{
// products = products.Where(e => e.ProductName.Contains(dataRequest.Search.Value));
// recordsFilterd = products.Count();
//}
products = products.Skip(dataRequest.Start).Take(dataRequest.Length);
var json = Json(products
.Select(e => new
{
Id = e.Id,
ProductName = e.ProductName,
UnitPrice = e.UnitPrice,
AvailableDiscount = e.AvailableDiscount,
CategoryName = e.CategoryName,
UnitName = e.UnitName,
Status = e.Status.ToString(),
IsActive = e.IsActive
})
.ToDataTablesResponse(dataRequest, recordsTotal, recordsFilterd));
return(json);
}
public IActionResult Get([DataTablesRequest] DataTablesRequest dataRequest)
{
IEnumerable <Product> products = Products.GetProducts();
int recordsTotal = products.Count();
int recordsFilterd = recordsTotal;
if (!string.IsNullOrEmpty(dataRequest.Search?.Value))
{
products = products.Where(e => e.Name.Contains(dataRequest.Search.Value));
recordsFilterd = products.Count();
}
products = products.Skip(dataRequest.Start).Take(dataRequest.Length);
return(Json(products
.Select(e => new
{
Id = e.Id,
Name = e.Name,
Created = e.Created,
Salary = e.Salary,
Position = e.Position,
Office = e.Office
})
.ToDataTablesResponse(dataRequest, recordsTotal, recordsFilterd)));
//return Json(new {
// draw = dataRequest.Draw,
// recordsTotal = products.Count(),
// recordsFiltered = products.Count(),
// data = products.Select(e => new { Id = e.Id, Name = e.Name, Created = e.Created, Price = 10 })
//});
}
public dynamic ToDataTableResponse<T>(IQueryable<T> data)
{
var drawString = Request.Query["draw"];
if (data != null)
{
var request = new DataTablesRequest<T>(Request.QueryString.ToString());
var filtered = data.ToPagedList(request);
int draw = Convert.ToInt32(drawString);
return new
{
draw,
recordsFiltered = filtered.TotalCount,
recordsTotal = filtered.TotalCount,
data = filtered
};
}
else
{
return new
{
draw = drawString,
recordsFiltered = 0,
recordsTotal = 0,
data = new List<T>()
};
}
}
private Result get_page(HttpContext context)
{
DataTablesRequest param = new DataTablesRequest(context.Request);
int region = Convert.ToInt32(context.Request["region"]);
int restype = Convert.ToInt32(context.Request["restype"]);
int projectsource = Convert.ToInt32(context.Request["projectsource"]);
string positionid = context.Request["position"];
string positionname = context.Request["positionname"];
string pagename = context.Request["pagename"];
string pagetype = context.Server.UrlDecode(context.Request["pagetype"]);
int downtype = Convert.ToInt32(context.Request["downtype"]);
int checkistag = Convert.ToInt32(context.Request["checkistag"]);
int pagesize = param.iDisplayLength;
int pageIndex = (param.iDisplayStart + 1) / param.iDisplayLength + 1;
int recordcount = 0;
B_BaseTool_DataAccess basetool = new B_BaseTool_DataAccess();
int softid2 = basetool.getSoftid2byProjectsource(projectsource, region);
var list = B_BaseDownPositionService.Instance.GetB_DownPositionListByCache((ProjectSourceTypeOptions)region, downtype, checkistag,
restype, projectsource, positionid,
positionname, pagename, pagetype, (pageIndex - 1) * pagesize,
pagesize, softid2, out recordcount);
JQueryDataTableData dt = new JQueryDataTableData();
dt.sEcho = param.sEcho;
dt.iTotalRecords = dt.iTotalDisplayRecords = recordcount;
dt.aaData = GetDataStrList(list);
return(Result.GetSuccessedResult(dt, false));
}
public async Task <IActionResult> CoverageUploadListByClient([DataTablesRequest] DataTablesRequest dataRequest, string EnrollmentHdrID)
{
int.TryParse(EnrollmentHdrID.Trim(), out int _clientID);
var query = string.Format(@"SELECT cu.ID,cu.PlanId,cu.CorpCode,cu.CoverageCode,cu.ActiveFlag,
cu.ClientCoverageCode,lc.Description LimitCode,fc.Description FrequencyCode,cu.MinValue,cu.MaxValue,
cu.FamilyValue,CONCAT(CONVERT(VARCHAR(30),cu.UploadDate,106),' ',CONVERT(VARCHAR(30),cu.UploadDate,108)) UploadDate,
cu.UserUpload,CONCAT(cu.errPlanId+'<br>',cu.errCorpCode+'<br>',cu.errCoverageCode+'<br>',cu.errActiveFlag+'<br>',cu.errClientCoverageCode+'<br>',
cu.errLimitCode+'<br>',cu.errFrequencyCode+'<br>',cu.errMaxValue+'<br>',cu.errFamilyValue+'<br>') ErrorMessage
FROM stg.CoverageUpload cu
LEFT JOIN FrequencyCodes fc ON fc.ID = cu.FrequencyCode
LEFT JOIN LimitCodes lc ON lc.ID = Cu.LimitCode
WHERE cu.EnrollmentHdrID=@EnrollmentHdrID ");
var ls = new List <CoverageUploadVM>();
try
{
var _params = new SqlParameter {
ParameterName = "@EnrollmentHdrID", SqlDbType = SqlDbType.Int, Value = _clientID
};
ls = await _context.Set <CoverageUploadVM>().FromSql(query, _params).ToListAsync();
}
catch (Exception ex)
{
flashMessage.Danger("Error Paging Coverage : " + ex.Message);
}
return(Json(ls.ToDataTablesResponse(dataRequest, ls.Count, ls.Count)));
}
public IActionResult Get([DataTablesRequest] DataTablesRequest dataRequest)
{
//var users = _context.User.ToList();
IEnumerable <Users> users = _context.User.ToList();
int recordsTotal = users.Count();
int recordsFilterd = recordsTotal;
if (!string.IsNullOrEmpty(dataRequest.Search?.Value))
{
users = users.Where(e => e.Name.Contains(dataRequest.Search.Value) || e.Email.Contains(dataRequest.Search.Value) || e.SelectedEvents.Contains(dataRequest.Search.Value));
recordsFilterd = users.Count();
}
users = users.Skip(dataRequest.Start).Take(dataRequest.Length).AsQueryable().OrderBy(dataRequest.Columns.ToList()[dataRequest.Orders.First().Column].Data + " " + dataRequest.Orders.First().Dir);
return(Json(users
.Select(e => new
{
Id = e.Id,
Name = e.Name,
Email = e.Email,
RegisteredDate = e.RegisteredDate.ToShortDateString(),
Gender = e.Gender,
SelectedEvents = e.SelectedEvents,
AdditionalRequest = e.AdditionalRequest
})
.ToDataTablesResponse(dataRequest, recordsTotal, recordsFilterd)));
}
public object BindModel(ControllerContext controllerContext, System.Web.Mvc.ModelBindingContext bindingContext)
{
var pageRequest = new DataTablesRequest
{
Echo = BindDataTablesRequestParam <Int32>(bindingContext, "sEcho"),
DisplayStart = BindDataTablesRequestParam <Int32>(bindingContext, "iDisplayStart"),
DisplayLength = BindDataTablesRequestParam <Int32>(bindingContext, "iDisplayLength"),
ColumnNames = BindDataTablesRequestParam <string>(bindingContext, "sColumns"),
Columns = BindDataTablesRequestParam <Int32>(bindingContext, "iColumns"),
Search = BindDataTablesRequestParam <string>(bindingContext, "sSearch"),
Regex = BindDataTablesRequestParam <bool>(bindingContext, "bRegex"),
SortingCols = BindDataTablesRequestParam <Int32>(bindingContext, "iSortingCols"),
DataProp = BindDataTablesRequestParam <string>(controllerContext.HttpContext.Request, "mDataProp_"),
RegexColumns = BindDataTablesRequestParam <bool>(controllerContext.HttpContext.Request, "bRegex_"),
Searchable = BindDataTablesRequestParam <bool>(controllerContext.HttpContext.Request, "bSearchable_"),
Sortable = BindDataTablesRequestParam <bool>(controllerContext.HttpContext.Request, "bSortable_"),
SortCol = BindDataTablesRequestParam <Int32>(controllerContext.HttpContext.Request, "iSortCol_"),
SearchColumns = BindDataTablesRequestParam <string>(controllerContext.HttpContext.Request, "sSearch_"),
SortDir = BindDataTablesRequestParam <string>(controllerContext.HttpContext.Request, "sSortDir_"),
CategoryId = BindDataTablesRequestParamstring(bindingContext, "CategoryId"),
VendorId = BindDataTablesRequestParamstring(bindingContext, "VendorId")
};
return(pageRequest);
}
protected override Result GetDetailTable <T>(HttpContext context)
{
var lists = GetData <T>(context, true);
var list = lists.Count != 0 ? lists[0] : new List <T>();
DataTablesRequest param = new DataTablesRequest(context.Request);
JQueryDataTableData dt = new JQueryDataTableData();
dt.sEcho = param.sEcho;
dt.iTotalRecords = dt.iTotalDisplayRecords = list.Count;
dt.aaData = ObjectParseListFillDetailTable(list);
DateTime restime = Convert.ToDateTime(context.Request["begintime"]);
int period = Convert.ToInt32(context.Request["period"]);
if (1 == period)
{
dt.begintime = restime.ToString("yyyy-MM-dd");
dt.endtime = restime.ToString("yyyy-MM-dd");
}
if (3 == period)
{
dt.begintime = B_BaseToolService.GetWeekUpOfDate(restime, DayOfWeek.Monday, -1).ToString("yyyy-MM-dd");
dt.endtime = B_BaseToolService.GetWeekUpOfDate(restime, DayOfWeek.Sunday, 0).ToString("yyyy-MM-dd");
}
if (12 == period)
{
dt.begintime = restime.AddDays(-1 * (restime.Day) + 1).AddMonths(-1).ToString("yyyy-MM-dd");
dt.endtime = restime.AddDays(-1 * (restime.Day)).ToString("yyyy-MM-dd");
}
return(Result.GetSuccessedResult(dt, false));
}
/// <summary>
/// Create a Lambda Expression that is chain of Or Expressions
/// for each column. Each column will be tested if it contains the
/// generic search string.
///</summary>
/// <remarks>
/// Query logic = (or … or …) And (or … or …)
/// </remarks>
/// <returns>IQueryable of T</returns>
private static IQueryable <T> Filter(IQueryable source, DataTablesRequest requestModel)
{
// If there is no search term wew don't need to bother with filtering
if (String.IsNullOrEmpty(requestModel.sSearch))
{
return((IQueryable <T>)source);
}
var predicate = PredicateBuilder.False <T>();
var obj = Expression.Parameter(typeof(T));
var columnNames = requestModel.sColumns.Split(',');
var propNames = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public)
.Where(e => e.PropertyType == typeof(string))
.Select(x => x.Name).ToList();
for (int i = 0; i < columnNames.Length; i++)
{
if (requestModel.bSearchable[i] && propNames.Contains(columnNames[i]))
{
var filterBy = FilterByString(obj, columnNames[i], requestModel.sSearch);
predicate = predicate.Or(filterBy);
}
}
var rewired = RewireLambdaExpression(predicate, obj);
var filter = source.OfType <T>().Where(rewired);
return(filter);
}
public JsonResult WayBillExceptionList(WayBillExceptionFilter filter)
{
dicSort.Add(2, "w.PostingTime");
DataTablesRequest parm = new DataTablesRequest(this.Request); //处理对象
int pageIndex = parm.iDisplayLength == 0 ? 0 : parm.iDisplayStart / parm.iDisplayLength;
filter.PageIndex = pageIndex; //页索引
filter.PageSize = parm.iDisplayLength; //页行数
string strSortField = dicSort.Where(x => x.Key == parm.SortColumns[0].Index).Select(x => x.Value).FirstOrDefault();
string strSortDire = parm.SortColumns[0].Direction == SortDirection.Asc ? "asc" : "desc";
filter.OrderBy = " " + strSortField + " " + strSortDire;
var DataSource = Core.Reconciliation.WayBillException.GetByFilter(filter) as WRPageOfList <WayBillException>;
int i = parm.iDisplayLength * pageIndex;
List <WayBillException> queryData = DataSource.ToList();
var data = queryData.Select(u => new
{
Index = ++i, //行号
ID = u.ID,
IsInputCost = u.IsInputCost,
CusName = u.CusName, //客户简称
PostingTime = u.PostingTime == null ? string.Empty : u.PostingTime.Value.ToStringDate(), //收寄日期
ExpressNo = u.ExpressNo, //运单号
BatchNO = u.LoadBillNum, //提单号
Weight = u.Weight == null ? 0m : u.Weight / 1000, //重量
WayBillFee = u.WayBillFee, //邮资
ProcessingFee = u.ProcessingFee, //邮政邮件处理费
InComeWayBillFee = u.ExpressFee, //客户运费
InComeOprateFee = u.OperateFee, //客户操作费
WayBillMargins = u.WayBillProfit, //运费毛利
TotalMargins = u.ExpressFee + u.OperateFee + u.InComeOtherFee - (u.WayBillFee + u.ProcessingFee + u.CostOtherFee), //总毛利
Margin = Math.Round((u.ExpressFee + u.OperateFee + u.InComeOtherFee == 0 ? 0m : (u.ExpressFee + u.OperateFee + u.InComeOtherFee - (u.WayBillFee + u.ProcessingFee + u.CostOtherFee)) / (u.ExpressFee + u.OperateFee + u.InComeOtherFee) * 100), 2) + "%", //毛利率 毛利率=(总收入-总的支出的成本)/总收入*100%
ReconcileDate = u.ReconcileDate.ToStringDate(), //对账日期
CostOtherFee = u.CostOtherFee, //成本 其他费用
CostTotalFee = u.WayBillFee + u.ProcessingFee + u.CostOtherFee, //成本 总费用
CostStatus = u.CostStatus.ToChinese(), //成本 状态
InComeOtherFee = u.InComeOtherFee, //收入 其他费用
InComeTotalFee = u.ExpressFee + u.OperateFee + u.InComeOtherFee, //收入 总费用
InComeStatus = u.InComeStatus.ToChinese(), //收入 状态
ExceptionMsg = u.ExceptionMsg, //运单异常原因
WayBillCostID = u.WayBillCostID //运单成本ID
// ExceptionType = u.ExceptionType //运单异常状态
});
//decimal totalProfit = 0m; //总毛利求和
//构造成Json的格式传递
var result = new
{
iTotalRecords = DataSource.Count,
iTotalDisplayRecords = DataSource.RecordTotal,
data = data
};
return(Json(result, JsonRequestBehavior.AllowGet));
}
public async Task <IActionResult> GetUsers(UserViewModel userViewModel, DataTablesRequest dtReq)
{
var userList = await usrService.FindUsers(userViewModel, dtReq);
var userVM = mapper.Map <IEnumerable <UserCreateViewModel> >(userList.Items);
return(Json(new DataTablesResponse(dtReq.draw, userVM, userList.TotalCount, userList.TotalCount).ToJson()));
}
public JsonResult Data(DataTablesRequest model)
{
var data = _menuServices.GetAll().Data.AsQueryable();
var dataTableParser = new DataTablesParser <Menu>(model, data);
var formattedList = dataTableParser.Process();
return(Json(formattedList, JsonRequestBehavior.AllowGet));
}
public static DataTablesJsonResult CreateResponse(
this DataTablesRequest request,
string errorMessage)
{
var response = DataTablesResponse.Create(request, errorMessage);
return(new DataTablesJsonResult(response));
}
public IPagedList <RoleViewModel> GetRoles(DataTablesRequest <RoleViewModel> request)
{
return(Roles.Queryable().Select(p => new RoleViewModel()
{
Name = p.Name,
RoleId = Convert.ToString(p.Id)
}).ToPagedList(request));
}
public ActionResult GetPatientListByFilter(DataTablesRequest dataTablesRequest, PatientFilter patient)
{
Service serv = new Service();
int totalRecordsNumber;
var model = serv.FindPatiets(dataTablesRequest, patient, out totalRecordsNumber);
return(new DataTablesResult(dataTablesRequest.sEcho, totalRecordsNumber, totalRecordsNumber, model));
}
public async Task <IActionResult> GetList([DataTablesRequest] DataTablesRequest dataRequest)
{
var customerId = Guid.Parse("13bce473-5e31-4f3f-87a0-8863b6a814f5");
var result = await driverQueries.GetLisOfDriversAsync(customerId, dataRequest.Search?.Value, dataRequest.Start,
dataRequest.Length).ConfigureAwait(false);
return(Json(result.Data.ToDataTablesResponse(dataRequest, result.RecordsTotal, result.RecordsFilterd)));
}
public ActionResult GetVisitsByFilter(DataTablesRequest dataTablesRequest, VisitFilter visitFilter)
{
Service serv = new Service();
int totalRecordsNumber;
var visitList = serv.GetVisitsByFilter(dataTablesRequest, visitFilter, out totalRecordsNumber);
return(new DataTablesResult(dataTablesRequest.sEcho, totalRecordsNumber, totalRecordsNumber, visitList));
}
public JsonResult Data(DataTablesRequest model)
{
var data = _roleManager.Roles.AsQueryable();
var dataTableParser = new DataTablesParser <ApplicationRole>(model, data);
var formattedList = dataTableParser.Process();
return(Json(formattedList, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// Datas the table.
/// </summary>
/// <param name="request">The request.</param>
/// <returns></returns>
public JsonResult DataTable(DataTablesRequest request)
{
IMediaRepository repository = new DbMediaRepository(new EntityFrameworkSexContext());
var videos = repository.GetVideos();
// return Json(
// request.Response(
// videos.AsQueryable(),
// new Field<Video, string>(video => video.Hash, (video, search) => video.Hash.Contains(search)),
// new Field<Video, string>(video => video.FileName, (video, search) => video.FileName.Contains(search), (videoFileName) => string.Format(@"
//<video id=""videoBox"" class=""video-js vjs-default-skin"" controls preload=""none"" width=""640"" height=""264"" data-setup=""{{}}"">
// <source src=""{0}"" type='video/mp4' />
//</video>", Url.Action("OceansClip", new { filename = videoFileName }))),
// new Field<Video, TimeSpan>(video => video.Duration, (IQueryable<Video> items, string search) =>
// {
// int maxDuration = int.Parse(search);
// return items.Where(video => DbFunctions.DiffMinutes(TimeSpan.Zero, video.Duration) <= maxDuration);
// }),
// new Field<Video, string>(video => video.NormalizedTags, (video, search) => video.NormalizedTags.Contains(search))),
// JsonRequestBehavior.AllowGet);
return(Json(
request.Response(
videos.AsQueryable(),
new Field <Video, string>(
video => video.FileName,
(video, search) => video.FileName.Contains(search)),
new Field <Video, TimeSpan>(
video => video.Duration,
(IQueryable <Video> items, string search) =>
{
try
{
var split = search.Split('-');
if (split.Count() == 1)
{
int maxDuration = int.Parse(search);
return items.Where(video => DbFunctions.DiffMinutes(TimeSpan.Zero, video.Duration) <= maxDuration);
}
if (split.Count() == 2)
{
int minDuration = int.Parse(split[0]);
int maxDuration = int.Parse(split[1]) + 1;
return items.Where(video => DbFunctions.DiffMinutes(TimeSpan.Zero, video.Duration) <= maxDuration && DbFunctions.DiffMinutes(TimeSpan.Zero, video.Duration) >= minDuration);
}
}
catch (Exception)
{
}
return items;
},
duration => duration.ToString(@"hh\:mm\:ss")),
new Field <Video, string>(
video => video.NormalizedTags,
(video, search) => video.NormalizedTags.Contains(search))),
JsonRequestBehavior.AllowGet));
}
public TestData(string columnName, string searchString)
{
Queryable = new List<SomeModel>()
{
new SomeModel()
{
Category = 1,
DisplayName = "Kapitein Archibald Haddock",
Id = 123,
Scale = 123.456d,
Discounted = true,
Cost = 123,
When = new DateTime(2015,1,1,10,01,0)
},
new SomeModel()
{
Category = 2,
DisplayName = "Trifonius Zonnebloem",
Id = 234,
Scale = 234.567d,
Discounted = true,
Cost = 234,
When = new DateTime(2016,1,1,10,01,0)
}
}.AsQueryable();
DataTablesRequest = new DataTablesRequest()
{
Length = 1,
Draw = 1,
Order = new List<DataTablesRequest.OrderT>() {
new DataTablesRequest.OrderT() { Column = 2, Dir = "Asc" }
},
Search = new DataTablesRequest.SearchT()
};
Columns = ModelProperties<SomeModel>.Properties.Select(p =>
Tuple.Create(p.Name, new ModelProperty(p.PropertyInfo))).ToArray();
DataTablesRequest.Columns = Columns.Select(c => new DataTablesRequest.ColumnT()
{
Searchable = true,
Orderable = true,
Data = c.Item1,
Search = new DataTablesRequest.SearchT() { Regex = false, Value = "" }
}).ToArray();
var search = new DataTablesRequest.SearchT()
{
Value = searchString,
Regex = false
};
if (columnName.Any())
DataTablesRequest.Columns.Where(c => c.Data == columnName).First().Search = search;
else
DataTablesRequest.Search = search;
}
