public void SingleStock_should_return_same_number_news_table_and_summary_info(DataTableDateRangeOptions dataTableDateRangeOptions)
{
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
var repository = new Repository();
var summaryDataProvider = new SummaryDataProvider(repository);
DataTableOptions stockTableOptions = new DataTableOptions
{
Filter = dataTableDateRangeOptions.Filter,
Symbol = dataTableDateRangeOptions.Symbol,
Take = dataTableDateRangeOptions.Take,
Skip = dataTableDateRangeOptions.Skip,
SortDescription = dataTableDateRangeOptions.SortDescription,
StartTonDate = dataTableDateRangeOptions.StartTonDate,
Providers = dataTableDateRangeOptions.Providers
};
var range = DateRange.Today;
var trr = MarketCalendar.CalculateTradingTimeRange(range, DateTime.Now, DateTime.Now);
if (dataTableDateRangeOptions.StartDate != null && dataTableDateRangeOptions.EndDate != null)
{
trr = MarketCalendar.CalculateTradingTimeRange(range, dataTableDateRangeOptions.StartDate.Value, dataTableDateRangeOptions.EndDate.Value);
}
var stockPageSymbolFunc = new StockPageSymbolFunc(repository);
var tableResult = stockPageSymbolFunc.GetNewsStreamingDataSource(stockTableOptions);
var symbolSummaryData = summaryDataProvider.BuildCompanySection(stockTableOptions.Symbol, range, trr, dataTableDateRangeOptions.StartTonDate, dataTableDateRangeOptions.Providers);
Assert.AreEqual(tableResult.Total, 10003);
}
public void GetColumns_ShouldReturnAllColumns()
{
var options = new DataTableOptions <Country>
{
ColumnsConfig = new ColumnsConfig()
};
var propertyInfos = options.GetColumns();
Assert.Equal(7, propertyInfos.Count);
Assert.Equal("TwoLetterCountryCode", propertyInfos.First().Name);
}
public void DataTable_GivenSourceUrlAndNullAjaxOptions_CreatesAjaxOptions()
{
// Arrange
using var context = CreateContext();
var datatable = context.RenderComponent <DataTable>(parameters => parameters.Add(x => x.SourceUrl, "some url"));
// Act
var options = DataTableOptions.FromComponent(datatable.Instance);
// Assert
Assert.NotNull(options.Ajax);
Assert.Equal("some url", options.Ajax.Url);
}
public async Task <IActionResult> GetDTResponseAsync(DataTableOptions options)
{
try
{
Specification.AddInclude(e => e.MainCategory);
Specification.AddInclude(e => e.SubCategory);
return(Ok(await ProductRepo.GetOptionResponseWithSpec(options, Specification)));
}
catch (Exception e)
{
this.ILogger.LogError("Error Occurred While Running GetOptions @ HomeController : \n" + e.Message);
return(BadRequest());
}
}
private void SetOptions(DataTableOptions options)
{
options.HeaderHeight = 33;
options.DataHeight = 31;
//设置表头样式
options.HeaderCellAction = (dataColumn, excelColumn, cell) =>
{
cell.Style.Fill.PatternType = ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(164, 50, 83));
cell.Style.Font.Color.SetColor(Color.White);
cell.Style.Font.Size = 11;
cell.Style.Font.Name = "微软雅黑";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
cell.Style.WrapText = true;
switch (dataColumn.ColumnName)
{
case "Id":
excelColumn.Width = 8;
break;
case "Name":
excelColumn.Width = 12;
break;
case "Birthday":
excelColumn.Width = 15;
break;
}
};
//设置数据单元格样式
options.DataCellAction = (dataColumn, cell) =>
{
cell.Style.Font.Size = 11;
cell.Style.Font.Name = "微软雅黑";
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
cell.Style.WrapText = true;
};
//dtStyle.ExcelWorksheetAction = sheet => { };
}
public void GetColumns_ShouldOrderColumns_WhenVisibleColumnsIsSet_AndOrderByThem()
{
var options = new DataTableOptions <Country>
{
ColumnsConfig = new ColumnsConfig(),
VisibleColumns = new List <string>
{
"ThreeLetterCountryCode",
"Id",
"TwoLetterCountryCode"
}
};
var propertyInfos = options.GetColumns();
Assert.Equal(7, propertyInfos.Count);
Assert.Equal("ThreeLetterCountryCode", propertyInfos.First().Name);
Assert.Equal("Id", propertyInfos[1].Name);
Assert.Equal("TwoLetterCountryCode", propertyInfos[2].Name);
}
public void GetColumns_ShouldThrowError_ForInvalidColumn()
{
var options = new DataTableOptions <Country>
{
ColumnsConfig = new ColumnsConfig(),
VisibleColumns = new List <string>
{
"ThreeLetterCountryCode",
"InvalidColumn",
"TwoLetterCountryCode"
}
};
try
{
var propertyInfos = options.GetColumns();
}
catch (Exception ex)
{
Assert.True(ex.GetType() == typeof(InvalidColumnNameException));
}
}
public DataTable <FlightDTO> GetFlightsTuple(DataTableOptions pagination)
{
var origin = pagination.Columns[1].Search.Value;
var destination = pagination.Columns[2].Search.Value;
var departureDate = pagination.Columns[4].Search.Value;
if (String.IsNullOrEmpty(origin) || String.IsNullOrEmpty(destination) || String.IsNullOrEmpty(departureDate))
{
return(new DataTable <FlightDTO>
{
Draw = pagination.Draw,
RecordsTotal = 0,
RecordsFiltered = 0,
Data = new List <FlightDTO>()
});
}
origin = origin.ToUpper();
destination = destination.ToUpper();
var returnDate = pagination.Columns[5].Search.Value;
var currency = (pagination.Columns[3].Search.Value ?? Default.CURRENCY).ToUpper();
var adults = Convert.ToInt32(pagination.Columns[6].Search.Value ?? Default.ADULTS.ToString());
var flightTuple = flightService.GetFlightsTuple(pagination.Start,
pagination.Length,
pagination.Order[0].Dir,
pagination.Columns[pagination.Order[0].Column].Name,
origin, destination, departureDate, returnDate, currency, adults);
return(new DataTable <FlightDTO>
{
Draw = pagination.Draw,
Data = flightTuple.Item2,
RecordsFiltered = flightTuple.Item1,
RecordsTotal = flightTuple.Item1,
});
}
/// <summary>
/// Returns Iquerable after applying dynamically all search and orders provided by the datatable options.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dtOptions"></param>
/// <param name="data"></param>
/// <returns></returns>
public static IQueryable <T> SearchEntity <T>(DataTableOptions dtOptions, IQueryable <T> data) where T : class
{
if (dtOptions.Search != null)
{
//Perform search filter on all Searchable Coloumns
if (!string.IsNullOrEmpty(dtOptions.Search.Value))
{
List <Expression <Func <T, bool> > > predicatesSearh = new List <Expression <Func <T, bool> > >();
foreach (var col in dtOptions.Columns.Where(t => t.Searchable))
{
string filterTarget = col.Data;
//If Property Not Found, upper the First char, other people might not need this if you explicitly use camel case serializing.
if (!typeof(T).GetProperties().Any(t => t.Name == filterTarget))
{
filterTarget = filterTarget[0].ToString().ToUpper() + filterTarget.Substring(1);
}
var colPredicate = ExpressionBuilder.BuildPredicate <T>(dtOptions.Search.Value, OperatorComparer.Contains, filterTarget);
if (colPredicate != null)
{
predicatesSearh.Add(colPredicate);
}
}
if (predicatesSearh.Any())
{
//Init Expression of type T return bool (search filter)
Expression <Func <T, bool> > predicateCompiled = null;
//Combine all search conditions
foreach (var pre in predicatesSearh)
{
predicateCompiled = predicateCompiled == null ? pre : predicateCompiled.Or(pre);
}
data = data.Where <T>(predicateCompiled);
}
}
}
//Perform column sorting
IOrderedQueryable <T> dataOrder = null;
if (dtOptions.Order != null)
{
var sortQuery = new List <string>();
foreach (var order in dtOptions.Order)
{
var col = dtOptions.Columns[order.Column];
if (col.Orderable)
{
var filterData = col.Data;
sortQuery.Add(filterData + " " + (order.Dir.ToLower().Equals("asc") ? "ASC" : "DESC"));
}
}
if (sortQuery.Any())
{
data = data.OrderBy(string.Join(",", sortQuery));
}
}
if (dataOrder != null)
{
return(dataOrder);
}
else
{
return(data);
}
}
public TableController(IPlacementContext context)
{
this.config = context.Config;
this.options = context.Options;
}
public async Task <DataTableResponse> GetOptionResponseWithSpec(DataTableOptions options, ISpecification <T> spec)
{
var data = await context.Set <T>().ProcessSpecification(spec).GetOptionResponseAsync <T>(options);
return(data);
}
public async Task <DataTableResponse> GetOptionResponse(DataTableOptions options)
{
return(await context.Set <T>().GetOptionResponseAsync <T>(options));
}
public Task BindModelAsync(ModelBindingContext bindingContext)
{
if (bindingContext == null)
{
throw new ArgumentNullException(nameof(bindingContext));
}
var allValues = bindingContext.HttpContext.Request.Query;
DataTableOptions DTOs = new DataTableOptions {
Draw = allValues.FirstOrDefault(a => a.Key == "draw").Value,
Start = Convert.ToInt32(allValues.FirstOrDefault(a => a.Key == "start").Value),
Length = Convert.ToInt32(allValues.FirstOrDefault(a => a.Key == "length").Value)
};
if (allValues.Any(a => a.Key.Length > 9 && a.Key.Substring(0, 9).Contains("columns")))
{
var myListIndex = 0;
var myListIndexComparer = 0;
var allColumns = allValues.Where(a => a.Key.Length > 9 && a.Key.Substring(0, 9).Contains("columns")).ToList();
DataTableColumn DTC = new DataTableColumn();
DataTableColumnSearch DTCS = new DataTableColumnSearch();
DTOs.columns = new List <DataTableColumn>();
foreach (var column in allColumns)
{
var perColumnArray = column.Key.Split(new char[] { '[', ']' }, StringSplitOptions.RemoveEmptyEntries);
var rawIndex = perColumnArray[1];
if (!int.TryParse(rawIndex, out myListIndex))
{
return(Task.CompletedTask);
}
if (myListIndexComparer != myListIndex)
{
DTC.search = DTCS;
DTOs.columns.Add(DTC);
DTC = new DataTableColumn();
DTCS = new DataTableColumnSearch();
}
myListIndexComparer = myListIndex;
switch (perColumnArray[2])
{
case "data":
DTC.data = column.Value;
break;
case "name":
DTC.name = column.Value;
break;
case "searchable":
DTC.searchable = String.IsNullOrWhiteSpace(column.Value) ? false : Convert.ToBoolean(column.Value);
break;
case "orderable":
DTC.orderable = String.IsNullOrWhiteSpace(column.Value) ? false : Convert.ToBoolean(column.Value);
break;
case "search":
if (perColumnArray[3] == "regex")
{
DTCS.regex = String.IsNullOrWhiteSpace(column.Value) ? false : Convert.ToBoolean(column.Value);
}
if (perColumnArray[3] == "value")
{
DTCS.value = column.Value;
}
break;
}
if (allColumns.IndexOf(column) == allColumns.IndexOf(allColumns.Last()))
{
DTC.search = DTCS;
DTOs.columns.Add(DTC);
}
}
}
if (allValues.Any(a => a.Key.Length > 7 && a.Key.Substring(0, 7).Contains("order")))
{
var myListIndex = 0;
var myListIndexComparer = 0;
var allOrders = allValues.Where(a => a.Key.Length > 7 && a.Key.Substring(0, 7).Contains("order")).ToList();
DataTableColumnOrder DTCO = new DataTableColumnOrder();
DTOs.order = new List <DataTableColumnOrder>();
foreach (var order in allOrders)
{
var perColumnArray = order.Key.Split(new char[] { '[', ']' }, StringSplitOptions.RemoveEmptyEntries);
var rawIndex = perColumnArray[1];
if (!int.TryParse(rawIndex, out myListIndex))
{
return(Task.CompletedTask);
}
if (myListIndexComparer != myListIndex)
{
DTOs.order.Add(DTCO);
DTCO = new DataTableColumnOrder();
}
myListIndexComparer = myListIndex;
switch (perColumnArray[2])
{
case "column":
DTCO.Column = Convert.ToInt32(order.Value);
break;
case "dir":
DTCO.Dir = order.Value;
break;
}
if (allOrders.IndexOf(order) == allOrders.IndexOf(allOrders.Last()))
{
DTOs.order.Add(DTCO);
}
}
}
if (allValues.Any(a => a.Key.Length > 7 && a.Key.Substring(0, 8).Contains("search")))
{
var allSearches = allValues.Where(a => a.Key.Length > 8 && a.Key.Substring(0, 8).Contains("search")).ToList();
DataTableColumnSearch DTCS = new DataTableColumnSearch();
DTOs.search = new DataTableColumnSearch();
foreach (var search in allSearches)
{
var perColumnArray = search.Key.Split(new char[] { '[', ']' }, StringSplitOptions.RemoveEmptyEntries);
switch (perColumnArray[1])
{
case "value":
DTCS.value = search.Value;
break;
case "regex":
DTCS.regex = String.IsNullOrWhiteSpace(search.Value) ? false : Convert.ToBoolean(search.Value);
break;
}
if (allSearches.IndexOf(search) == allSearches.IndexOf(allSearches.Last()))
{
DTOs.search = DTCS;
}
}
}
bindingContext.Result = ModelBindingResult.Success(DTOs);
return(Task.CompletedTask);
}
/// <inheritdoc/>
public ValueTask InitialiseAsync(ElementReference tableReference, DataTableOptions options)
=> _runtime.InvokeVoidAsync("datatablesInterop.initialiseDataTable", tableReference, JsonSerializer.Serialize(options, _serializerOptions));
/// <summary>
/// Use this function to invoke selector in your options,
/// this might be good for performance when dealing with large tables.
/// </summary>
/// <typeparam name="T">Object Type</typeparam>
/// <param name="data">IQuerable of the data</param>
/// <param name="option">Datatable Options</param>
/// <param name="selector">Selector of type X => new object {...} </param>
/// <returns>Datatable Response for provided DBSet</returns>
public static async Task <DataTableResponse> GetOptionResponseAsync <T>(this IQueryable <T> data, DataTableOptions option, Expression <Func <T, object> > selector) where T : class
{
var countTotal = await data.CountAsync();
var searchedEntities = SearchEntity <T>(option, data);
var countFiltered = await searchedEntities.CountAsync();
var theData = await searchedEntities
.Skip(option.Start)
.Take(option.Length)
.Select(selector)
.ToListAsync();
return(new DataTableResponse
{
Draw = Int32.Parse(option.Draw),
Data = theData.ToList(),
RecordsTotal = countTotal,
RecordsFiltered = countFiltered,
});
}
public DataTableViewModel()
{
DataTableOptions = new DataTableOptions();
}
/// <summary>
/// Returns DataTable Response
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data"></param>
/// <param name="option"></param>
/// <returns>Datatable Response for provided DBSet</returns>
public static DataTableResponse GetOptionResponse <T>(this IQueryable <T> data, DataTableOptions option) where T : class
{
var countTotal = data.Count();
var searchedEntities = SearchEntity <T>(option, data);
var countFiltered = searchedEntities.Count();
var theData = searchedEntities
.Skip(option.Start)
.Take(option.Length)
.ToList();
return(new DataTableResponse
{
Draw = Int32.Parse(option.Draw),
Data = theData.ToList <object>(),
RecordsTotal = countTotal,
RecordsFiltered = countFiltered,
});
}
public static MvcHtmlString DataTable(this HtmlHelper helper, DataTableOptions options)
{
string[] arrcols = options.Cols.Split(',');
int sortOffset = arrcols.Where(x => x == "Delete" || x == "View" || x == "Edit").Count();
StringBuilder sb = new StringBuilder();
sb.AppendLine("<script type=\"text/javascript\" charset=\"utf-8\">");
sb.AppendLine("$(function () {");
sb.AppendLine("$('#" + options.ID + "').dataTable({");
sb.AppendLine("\"bSort\" : " + options.Sort.ToString().ToLower() + ",");
sb.AppendLine("\"oLanguage\": { \"sUrl\": \"" + oLanguage + "\" },");
sb.AppendLine("\"bServerSide\": true,");
sb.AppendLine("\"sAjaxSource\": \"" + options.Url + "\",");
sb.AppendLine("\"bProcessing\": true,");
sb.AppendLine("\"sPaginationType\": \"full_numbers\",");
sb.AppendLine("\"iDisplayLength\": 25,");
sb.AppendLine("\"aLengthMenu\": " + aLengthMenu + ",");
sb.AppendLine("\"aaSorting\": [[" + sortOffset.ToString() + ",'asc']],");
sb.AppendLine("\"aoColumns\": [");
for (int i = 0; i < arrcols.Length; i++)
{
if (i > 0)
sb.Append(",");
switch (arrcols[i])
{
case "Delete":
sb.AppendLine("{");
sb.AppendLine("\"sName\": \"" + arrcols[i] + "\",");
sb.AppendLine("\"bSearchable\": false,");
sb.AppendLine("\"bSortable\": false,");
sb.AppendLine("\"fnRender\": function (oObj) {");
sb.Append("return '");
sb.Append("<a class=\"deletelink\" href=\"' + oObj.aData["+ i.ToString() + "] + '\">" + options.DeleteLinkname + "</a> ");
sb.Append("';");
sb.AppendLine("}");
sb.AppendLine("}");
break;
case "Edit":
sb.AppendLine("{");
sb.AppendLine("\"sName\": \"" + arrcols[i] + "\",");
sb.AppendLine("\"bSearchable\": false,");
sb.AppendLine("\"bSortable\": false,");
sb.AppendLine("\"fnRender\": function (oObj) {");
sb.Append("return '");
sb.Append("<a class=\"editlink\" href=\"' + oObj.aData["+ i.ToString() + "] +'\">" + options.EditUrlLinkname + "</a> ");
sb.Append("';");
sb.AppendLine("}");
sb.AppendLine("}");
break;
case "View":
sb.AppendLine("{");
sb.AppendLine("\"sName\": \"" + arrcols[i] + "\",");
sb.AppendLine("\"bSearchable\": false,");
sb.AppendLine("\"bSortable\": false,");
sb.AppendLine("\"fnRender\": function (oObj) {");
sb.Append("return '");
sb.Append("<a class=\"viewlink\" href=\"' + oObj.aData["+ i.ToString() + "] + '\">" + options.ViewUrlLinkname + "</a> ");
sb.Append("';");
sb.AppendLine("}");
sb.AppendLine("}");
break;
default:
sb.AppendLine("{ \"sName\": \"" + arrcols[i] + "\" }");
break;
}
}
sb.AppendLine("]");
sb.AppendLine("});");
sb.AppendLine("});");
sb.AppendLine("</script>");
if (options.DeleteLinkname != null)
{
sb.Append(ConfirmHelpers.RenderConfirm(options.DeleteTitle, options.DeleteYes, options.DeleteNo, options.DeleteMessage));
}
return MvcHtmlString.Create(sb.ToString());
}
