Task <PagedList <Banner> > IBannerStorage.GetPagedListAsync(int page, int pageSize, int type, string title, string orderBy)
{
var order = base.GetSortInfo(orderBy, new SortInfo("id"), m_PagedOrderColumns);
var condition = SqlClauses.Create().AndCaluse("type = @type", type > 0).AndCaluse("title like @title", !string.IsNullOrEmpty(title)).ToSql();
string sql = $@"SELECT * FROM (SELECT ROW_NUMBER() OVER({order}) AS row, * FROM banner {condition}
) t where t.row between (@page - 1) * @pageSize + 1 AND @page * @pageSize ;
SELECT COUNT(1) FROM banner {condition} ;";
return(base.GetPagedListAsync <Banner>(sql, new { page, pageSize, type, title = base.ToLikeParam(title) }));
}
Task <PagedList <Intention> > IIntentionStorage.GetPagedListAsync(int page, int pageSize, string keyword, int[] intentions, string orderBy)
{
var order = base.GetSortInfo(orderBy, new SortInfo("id"), m_PagedOrderColumns);
var intentionSqls = new List <string>();
if (intentions != null && intentions.Any())
{
foreach (var item in intentions.Distinct())
{
intentionSqls.Add($" intention like '%{item}%' ");
}
}
var condition = SqlClauses.Create()
.AndCaluse($"({string.Join(" or ", intentionSqls)})", intentionSqls.Any())
.AndCaluse("( name like @keyword or phone like @keyword)", !string.IsNullOrEmpty(keyword)).ToSql();
string sql = $@"SELECT * FROM (SELECT ROW_NUMBER() OVER({order}) AS row, * FROM intention {condition}
) t where t.row between (@page - 1) * @pageSize + 1 AND @page * @pageSize ;
SELECT COUNT(1) FROM intention {condition} ;";
return(base.GetPagedListAsync <Intention>(sql, new { page, pageSize, keyword = base.ToLikeParam(keyword) }));
}