public async Task <PagedListData <Artist> > GetArtistsAsync(int pageIndex, int pageSize = 10, string search = "", string orderBy = "")
{
var query = Enumerable.Empty <Artist>().AsQueryable();
if (!search.IsNullOrEmpty())
{
query = _context.Artist.Where(a => a.ArtistName.Contains(search));
}
else
{
query = _context.Artist;
}
if (orderBy.IsNullOrEmpty())
{
query.OrderByDescending(a => a.ArtistName);
}
else
{
query.OrderByDescending(a => a.ArtistId);
}
// var a = _context.Artist.ToList();
var pagedList = await PagedListData <Artist> .CreateAsync(query, pageIndex, pageSize);
return(pagedList);
}
public ResponseResult <PagedListData <List <SysUser> > > GetUserListByQuery(QueryUserModel query)
{
PagedListData <List <SysUser> > model = new PagedListData <List <SysUser> >();
var userService = GetService <SysUserService>();
var result = userService.GetUserListByQuery(query);
model.PagingData = result.PagingData?.ToList();
model.PagingInfo = result.PagingInfo;
return(ResponseResult <PagedListData <List <SysUser> > > .GenSuccessResponse(model));
}
/// <summary>
/// 对服务端分页数据进行序列化
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="pagedList"></param>
/// <returns></returns>
public static object JqGridJsonData <T>(this PagedListData <List <T> > pagedList)
{
var json = new
{
page = pagedList?.PagingInfo?.PageIndex ?? 1,
records = pagedList?.PagingInfo?.TotalCount ?? 0,
total = pagedList?.PagingInfo?.PageCount,
rows = pagedList?.PagingData?.ToArray() ?? new List <T>().ToArray()
};
return(json);
}
/// <summary>
/// Renders a list with page number button elements, and a label before it
/// </summary>
/// <typeparam name="TModel"></typeparam>
/// <param name="html"></param>
/// <param name="label"></param>
/// <returns></returns>
public static MvcHtmlString Pager <TModel>(this HtmlHelper <TModel> html, string label = "Page: ")
where TModel : ITableModel
{
if (html.ViewData.Model?.PageItems?.PageCount == 0)
{
return(null);
}
var pageItems = html.ViewData.Model.PageItems;
var pageData = new PagedListData(pageItems.PageNumber, pageItems.PageSize, pageItems.PageCount, pageItems.TotalItemCount);
return(Pager(html, pageData, label));
}
public async virtual Task <IActionResult> GetDataAsync(
[FromRoute] string name,
[FromQuery(Name = "$top")] string top,
[FromQuery(Name = "$skip")] string skip,
[FromQuery(Name = "$orderby")] string orderby,
[FromQuery(Name = "$expand")] string expand,
[FromQuery(Name = "$filter")] string filter)
{
try
{
ODataQueryOptions oDataQueryOptions = new ODataQueryOptions
{
Top = top,
Skip = skip,
//Select = select,
OrderBy = orderby,
Expand = expand,
Filters = string.IsNullOrEmpty(filter) ? null : new List <string> {
filter
}
};
var entity = entitiesRepository.GetById(name);
if (entity == null)
{
return(NotFound($"No data found for entity {name}"));
}
var items = await(Task <IEnumerable <object> >) GetType()
.GetMethod("ApplyQueryOptionsAsync")
.MakeGenericMethod(entity.ToType())
.Invoke(this, new object[] { oDataQueryOptions });
return(Ok(new DataListResponse
{
Data = await PagedListData <object> .CreateAsync(
items,
top,
skip,
async() => await(Task <long?>) GetType()
.GetMethod("CountAsync")
.MakeGenericMethod(entity.ToType())
.Invoke(this, null))
}));
}
catch (Exception e)
{
return(this.InternalServerError(e.FlattenMessages()));
}
}
public void PagedListData_Right()
{
int pageNumber = 15, pageSize = 10, pages = 20, totalCount = pageSize * pages;
var data = Enumerable.Range(1, totalCount);
var pagedList = data.ToPagedList(pageNumber, pageSize);
var pagedListData = new PagedListData(pagedList);
Assert.Equal(pageNumber, pagedListData.PageNumber);
Assert.Equal(pages, pagedListData.PageCount);
Assert.False(pagedListData.CanSeeFirstPage());
Assert.True(pagedListData.CanSeeLastPage());
Assert.Collection(pagedListData.PageRange, Enumerable.Range(11, TableModel.DefaultPageRange).Select(PageMatcher).ToArray());
}
public async Task <PagedListData <Ssuser> > GetUsersAsync(int pageIndex, int pageSize = 10, string search = "", string orderBy = "")
{
if (!search.IsNullOrEmpty())
{
_userManager.Users.Where(s => s.UserName.Contains(search)); //todo more searching fields
}
if (orderBy.IsNullOrEmpty())
{
_userManager.Users.OrderByDescending(u => u.LastName); //todo
}
else
{
_userManager.Users.OrderByDescending(u => u.Id); //todo
}
var pagedList = await PagedListData <Ssuser> .CreateAsync(_userManager.Users, pageIndex, pageSize);
return(pagedList);
}
// Admin
public PagedListDto <UserWithRolesDto> MapToUserWithRolesDto(PagedListData <Ssuser> pl)
{
var items = pl.Select(
u => new UserWithRolesDto()
{
Id = u.Id,
UserName = u.UserName,
Roles = u.SsuserRole.Select(r => r.Role.ToString()).ToList(), // is this right?? 062820/070620/070920
}).ToList();
var pldto = new PagedListDto <UserWithRolesDto>(items)
{
TotalItems = pl.TotalItems,
ItemsPerPage = pl.ItemsPerPage,
CurrentPage = pl.CurrentPage,
TotalPages = pl.TotalPages
};
return(pldto);
}
private TagBuilder RenderPage(PagedListData pageData, string inputName, int page)
{
var liTag = new TagBuilder("li");
if (page == pageData.PageNumber)
{
liTag.AddCssClass("page-current");
liTag.InnerHtml.Append(page.ToString());
}
else
{
var buttonTag = new TagBuilder("button");
buttonTag.MergeAttribute("type", "submit");
buttonTag.MergeAttribute("name", inputName);
buttonTag.MergeAttribute("value", page.ToString());
buttonTag.InnerHtml.Append(page.ToString());
liTag.InnerHtml.AppendHtml(buttonTag);
}
return(liTag);
}
public async Task GetArtists(ArtistPageParams p)
{
using (var mock = AutoMock.GetLoose())
{
var plArtists = new PagedListData <Artist>(new List <Artist>(), 1, 1, 1)
{
};
var returnDto = new PagedListDto <ArtistForListDto>(new List <ArtistForListDto>());
mock.Mock <IArtistData>().Setup(x => x.GetArtistsAsync(p.PN, p.IPP, p.Search, p.OrderBy)).Returns(Task.FromResult(plArtists));
mock.Mock <IMap>().Setup(x => x.MapToArtistForListDto(plArtists)).Returns(returnDto);
var cls = mock.Create <ArtistRepo>();
var expected = new PagedListDto <ArtistForListDto>(new List <ArtistForListDto>());
var actual = await cls.GetArtistsAsync(p);
Assert.True(actual != null);
Assert.Equal(expected, actual);
// More Tests needed
}
}
public override void Process(TagHelperContext context, TagHelperOutput output)
{
var pageData = new PagedListData(Model.PageItems);
var inputName = ViewContext.ViewData.TemplateInfo.GetFullHtmlFieldName("Page");
if (!pageData.CanSeeFirstPage())
{
output.Content.AppendHtml(RenderPage(pageData, inputName, 1));
}
foreach (var page in pageData.PageRange)
{
output.Content.AppendHtml(RenderPage(pageData, inputName, page));
}
if (!pageData.CanSeeLastPage())
{
output.Content.AppendHtml(RenderPage(pageData, inputName, pageData.PageCount));
}
output.TagMode = TagMode.StartTagAndEndTag;
output.TagName = "ul";
}
public virtual async Task <IActionResult> GetEntitiesAsync(
[FromQuery(Name = "$top")] string top,
[FromQuery(Name = "$skip")] string skip,
[FromQuery(Name = "$orderby")] string orderby,
[FromQuery(Name = "$filter")] string filter)
{
try
{
ODataQueryOptions oDataQueryOptions = new ODataQueryOptions
{
Top = top,
Skip = skip,
OrderBy = orderby,
Filters = string.IsNullOrEmpty(filter) ? null : new List <string> {
filter
}
};
var entities = await Task.FromResult(entitiesRepository
.AsQueryable()
.OData()
.ApplyQueryOptionsWithoutSelectExpand(oDataQueryOptions)
.ToList());
return(Ok(new EntityListResponse
{
SchemaVersion = Assembly.GetAssembly(typeof(DataHub.Entities.Asset)).GetName().Version.ToString(),
Data = await PagedListData <Entity> .CreateAsync(
entities,
top,
skip,
async() => await Task.FromResult(entitiesRepository.AsQueryable().LongCount()))
}));
}
catch (Exception e)
{
return(this.InternalServerError(e.FlattenMessages()));
}
}
/// <summary>
/// Renders a list with page number button elements, and a label before it
/// </summary>
/// <typeparam name="TModel"></typeparam>
/// <param name="html"></param>
/// <param name="pageData"></param>
/// <param name="label"></param>
/// <returns></returns>
public static MvcHtmlString Pager <TModel>(this HtmlHelper <TModel> html, PagedListData pageData, string label = "Page: ")
where TModel : ITableModel
{
var labelTag = new TagBuilder("label");
labelTag.SetInnerText(label);
var ulTag = new TagBuilder("ul");
if (!pageData.CanSeeFirstPage())
{
ulTag.InnerHtml += PagerLink(1);
}
foreach (var page in pageData.PageRange)
{
if (page == pageData.PageNumber)
{
ulTag.InnerHtml += $"<li class=\"page-current\">{page}</li>";
}
else
{
ulTag.InnerHtml += PagerLink(page);
}
}
if (!pageData.CanSeeLastPage())
{
ulTag.InnerHtml += PagerLink(pageData.PageCount);
}
var divTag = new TagBuilder("div");
divTag.AddCssClass("table-pager");
divTag.InnerHtml += labelTag.ToString();
divTag.InnerHtml += ulTag;
return(MvcHtmlString.Create(divTag.ToString()));
}
public async virtual Task <IActionResult> GetMetadataAsync(
[FromQuery(Name = "$top")] string top,
[FromQuery(Name = "$skip")] string skip,
[FromQuery(Name = "$orderby")] string orderby,
[FromQuery(Name = "$filter")] string filter)
{
try
{
ODataQueryOptions oDataQueryOptions = new ODataQueryOptions
{
Top = top,
Skip = skip,
OrderBy = orderby,
Filters = string.IsNullOrEmpty(filter) ? null : new List <string> {
filter
}
};
var entities = await Task.FromResult(timeseriesMetadataRepository
.AsQueryable()
.OData()
.ApplyQueryOptionsWithoutSelectExpand(oDataQueryOptions)
.ToList());
return(Ok(new TimeseriesMetadataListResponse
{
Data = await PagedListData <TimeseriesMetadata> .CreateAsync(
entities,
top,
skip,
async() => await timeseriesMetadataRepository.AsQueryable().LongCountAsync())
}));
}
catch (Exception e)
{
return(this.InternalServerError(e.FlattenMessages()));
}
}
public async virtual Task <IActionResult> GetFilesAsync(
[FromQuery(Name = "$top")] string top,
[FromQuery(Name = "$skip")] string skip,
[FromQuery(Name = "$orderby")] string orderby,
[FromQuery(Name = "$filter")] string filter)
{
try
{
ODataQueryOptions oDataQueryOptions = new ODataQueryOptions
{
Top = top,
Skip = skip,
OrderBy = orderby,
Filters = string.IsNullOrEmpty(filter) ? null : new List <string> {
filter
}
};
var files = await Task.FromResult(filesRepository
.AsQueryable()
.OData()
.ApplyQueryOptionsWithoutSelectExpand(oDataQueryOptions)
.ToList());
foreach (var file in files)
{
file.DownloadUri = this.BuildLink($"files/{file.Id}");
}
return(Ok(new FileListResponse
{
Data = await PagedListData <FileInfo> .CreateAsync(files)
}));
}
catch (Exception e)
{
return(this.InternalServerError(e.FlattenMessages()));
}
}
/// <summary>
/// 分页，条件由SqlBuilder传递过来
/// 因为使用了查询模板，因此需要 使用Select，where，OrderBy三个方法生成builder
/// </summary>
/// <param name="builder"></param>
/// <param name="page"></param>
/// <returns></returns>
public PagedListData <IEnumerable <T> > Query(SqlBuilder builder, PagingInfo page)
{
if (IsConnection())
{
int noffset = page.PageIndex * page.PageSize;
string tableName = GetTableName();
var countTemplate = builder.AddTemplate($@"select count(*) from {tableName} /**where**/");
var selectTemplate = builder.AddTemplate($@"select /**select**/ FROM {tableName} /**where**/ /**orderby**/ OFFSET @Offset ROWS FETCH NEXT @Limit ROWS ONLY ",
new
{
Limit = page.PageSize,
Offset = noffset
});
return(TraceExecFunc <PagedListData <IEnumerable <T> > >(
() =>
{
//DynamicParameters d = new DynamicParameters();
//d.AddDynamicParams(new {tablename = "fdfds"});
var count = Conn.Query <int>(countTemplate.RawSql, selectTemplate.Parameters).Single();
var rows = Conn.Query <T>(selectTemplate.RawSql, selectTemplate.Parameters);
PagedListData <IEnumerable <T> > pData = new PagedListData <IEnumerable <T> >();
page.TotalCount = count;
pData.PagingInfo = page;
pData.PagingData = rows;
return pData;
}, $"{countTemplate.RawSql};{selectTemplate.RawSql}; "));
}
else
{
page.TotalCount = 0;
return(new PagedListData <IEnumerable <T> >()
{
PagingInfo = page
});
}
}
// Artist
public PagedListDto <ArtistForListDto> MapToArtistForListDto(PagedListData <Artist> pl)
{
var artists = pl.Select(a => new ArtistForListDto
{
Id = a.ArtistId,
Name = a.ArtistName,
ArtistStatusId = a.ArtistStatusId,
YearsActive = ArtistCalculations.CalculateArtistYearsActive(a.CareerBeginDate, a.CareerEndDate),
ArtistGroup = a.ArtistGroup,
UserId = a.UserId,
Verified = a.Verified,
HomeCity = GetHomeCity(a)
}).ToList();
var pldto = new PagedListDto <ArtistForListDto>(artists)
{
TotalItems = pl.TotalItems,
ItemsPerPage = pl.ItemsPerPage,
CurrentPage = pl.CurrentPage,
TotalPages = pl.TotalPages
};;
return(pldto);
}
/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="page"></param>
/// <param name="conditions">eg:where a=a </param>
/// <param name="orderby">eg:id,name desc</param>
/// <returns></returns>
public PagedListData <IEnumerable <T> > Query(PagingInfo page, string conditions, string orderby)
{
if (IsConnection())
{
return(TraceExecFunc <PagedListData <IEnumerable <T> > >(() =>
{
var count = Conn.RecordCount <T>(conditions);
var rows = Conn.GetListPaged <T>(page.PageIndex, page.PageSize, conditions, orderby);
PagedListData <IEnumerable <T> > pData = new PagedListData <IEnumerable <T> >();
page.TotalCount = count;
pData.PagingInfo = page;
pData.PagingData = rows;
return pData;
}, GetEntityDesc(new { start = page.PageIndex, size = page.PageSize, conditions = conditions, orderby = orderby })));
}
else
{
page.TotalCount = 0;
return(new PagedListData <IEnumerable <T> >()
{
PagingInfo = page
});
}
}
/// <summary>
/// 根据条件获取用户列表
/// </summary>
/// <param name="query"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public PagedListData <List <SysUser> > GetUserListByQuery(QueryUserModel query, IDbTransaction transaction = null)
{
var sql = @" DECLARE @Sql NVARCHAR(MAX);
DECLARE @Order NVARCHAR(200);
IF ( @OrderBy IS NOT NULL AND @OrderBy<>'')
BEGIN
SET @Order = 'ORDER BY ' + @OrderBy;
END;
ELSE
BEGIN
SET @Order = ' ORDER BY InDate DESC ';
END;
IF ( @Where IS NULL)
BEGIN
SET @Where = '';
END;
SET @Sql = N'
WITH SysUserList AS
(
SELECT ROW_NUMBER() OVER ( ' + @Order + ' ) RowNumber,
[Id]
,[UserName]
,[LoginName]
,[UserExtent]
,[Enabled]
,[UserIsFreeze]
,[UserIsManage]
,[UserRemark]
,[BeginDate]
,[ExpireDade]
,[IsExpireDate]
,[PartitionFalg]
,[FlowDepId]
,[FlowTypeCode]
,[UserType]
,[UserPosition]
,[UserCustomerId]
,[PlaneNumber]
,[WhetherDog]
,[Securitylevel]
,[DistrictId]
,[DataGroupId]
,[InUser]
,[InDate]
,[EditUser]
,[EditDate]
,[DelUser]
,[DelState]
,[DelDate]
FROM dbo.SysUser WHERE DelState = 0 AND Enabled=1' + @Where + '
)
SELECT
A.[Id]
,A.[UserName]
,A.[LoginName]
,A.[UserExtent]
,A.[Enabled]
,A.[UserIsFreeze]
,A.[UserIsManage]
,A.[UserRemark]
,A.[BeginDate]
,A.[ExpireDade]
,A.[IsExpireDate]
,A.[PartitionFalg]
,A.[FlowDepId]
,A.[FlowTypeCode]
,A.[UserType]
,A.[UserPosition]
,A.[UserCustomerId]
,A.[PlaneNumber]
,A.[WhetherDog]
,A.[Securitylevel]
,A.[DistrictId]
,A.[DataGroupId]
,A.[InUser]
,CONVERT(varchar(20), A.[InDate], 20) InDate
,A.[EditUser]
,CONVERT(varchar(20), A.[EditDate], 20) EditDate
,A.[DelUser]
,A.[DelState]
,A.[DelDate]
,D.UserName InUserName
,E.UserName EditUserName
FROM SysUserList A
LEFT JOIN SysUser D
ON A.InUser=D.Id
LEFT JOIN SysUser E
ON A.EditUser=E.Id
WHERE (RowNumber BETWEEN ( @PageSize * ( @CurrentPageIndex - 1 ) + 1 )
AND @PageSize * @CurrentPageIndex )
SELECT @Count = COUNT(1)
FROM dbo.SysUser WHERE DelState = 0 AND Enabled=1 AND UserIsManage=0 ' + @Where;
EXECUTE sp_executesql @Sql,N'
@CurrentPageIndex INT,
@PageSize INT,
@Where NVARCHAR(1000),
@OrderBy NVARCHAR(200),
@Count int OUT',
@CurrentPageIndex,
@PageSize,
@Where,
@OrderBy,
@Count OUTPUT;";
PagedListData <List <SysUser> > model = new PagedListData <List <SysUser> >();
var dp = new DynamicParameters();
dp.Add("@CurrentPageIndex", query.PageIndex);
dp.Add("@PageSize", query.PageSize);
dp.Add("@Where", query.Where);
dp.Add("@OrderBy", query.OrderBy);
dp.Add("@Count", 0, DbType.Int32, ParameterDirection.Output);
var data = this.Query(sql, dp, transaction);
var pageInfo = new PagingInfo(query.PageSize, query.PageIndex);
model.PagingData = data?.ToList();
pageInfo.TotalCount = dp.Get <int>("@Count");
model.PagingInfo = pageInfo;
return(model);
}
/// <summary>
/// 分页查询，传入参数
/// </summary>
/// <param name="obj">分页条件</param>
/// <param name="info">查询条件</param>
/// <returns></returns>
public PagedListData <List <T> > PagedListQuery(PageListModel obj, PageSearch info)
{
if (IsConnection())
{
var sql = $@"DECLARE @Sql NVARCHAR(MAX);
DECLARE @Order NVARCHAR(200);
IF ( @OrderBy IS NOT NULL AND @OrderBy<>'')
BEGIN
SET @Order = 'ORDER BY ' + @OrderBy;
END;
ELSE
BEGIN
SET @Order = ' ORDER BY InDate DESC ';
END;
IF ( @Where IS NULL)
BEGIN
SET @Where = '';
END;
SET @Sql = N'
WITH Select_List AS
(
SELECT ROW_NUMBER() OVER ( ' + @Order + ' ) RowNumber,
{obj.WithColumn}
FROM {GetTableName()}
WHERE {obj.WithWhere} ' + @Where + '
)
SELECT
A.* " ;
sql += !string.IsNullOrWhiteSpace(obj.LeftColumn) ? $@",{obj.LeftColumn}" : "";
sql += $@" FROM Select_List A
{obj.LeftJoin}
WHERE (RowNumber BETWEEN ( @PageSize * ( @CurrentPageIndex - 1 ) + 1 ) AND @PageSize * @CurrentPageIndex )
SELECT @Count = COUNT(1) FROM {GetTableName()} WHERE {obj.WithWhere} ' + @Where;
EXECUTE sp_executesql @Sql,N'
@CurrentPageIndex INT,
@PageSize INT,
@Where NVARCHAR(1000),
@OrderBy NVARCHAR(200),
@Count int OUT',
@CurrentPageIndex,
@PageSize,
@Where,
@OrderBy,
@Count OUTPUT; ";
var model = new PagedListData <List <T> >();
var dp = new DynamicParameters();
dp.Add("@CurrentPageIndex", info.PageIndex);
dp.Add("@PageSize", info.PageSize);
dp.Add("@Where", info.Where);
dp.Add("@OrderBy", info.OrderBy);
dp.Add("@Count", 0, DbType.Int32, ParameterDirection.Output);
var data = this.Query(sql, dp);
var pageInfo = new PagingInfo(info.PageSize, info.PageIndex);
if (data != null)
{
model.PagingData = data.ToList();
pageInfo.TotalCount = dp.Get <int>("@Count");
model.PagingInfo = pageInfo;
}
else
{
model.PagingData = new List <T>();
pageInfo.TotalCount = 0;
model.PagingInfo = pageInfo;
}
return(model);
}
else
{
return(null);
}
}
