public async Task <OperationResult <PagedModel <ShopEmployeeVoteBaseModel> > > SelectShopEmployeeRankingAsync(SexAnnualVoteQueryRequest query)
{
return(await SexAnnualVoteManager.SelectShopEmployeeRankingAsync(query));
}
public static async Task <OperationResult <PagedModel <ShopEmployeeVoteBaseModel> > > SelectShopEmployeeRankingAsync(SexAnnualVoteQueryRequest query)
{
return(await OperationResult.FromResultAsync(DalSexAnnualVote.SelectShopEmployeeRankingAsync(query)));
}
public static async Task <PagedModel <ShopVoteBaseModel> > SelectShopRankingAsync(SexAnnualVoteQueryRequest query)
{
string wheresql = " WHERE 1=1 ";
List <SqlParameter> sqlParameters = new List <SqlParameter>();
List <SqlParameter> countSqlParameters = new List <SqlParameter>();
if (query.ShopId > 0)
{
wheresql += " AND PKID=@shopId";//搜索用的是PKID
sqlParameters.Add(new SqlParameter("@shopId", query.ShopId));
countSqlParameters.Add(new SqlParameter("@shopId", query.ShopId));
}
if (query.ProvinceId > 0)
{
wheresql += " AND ProvinceId=@provinceId ";
sqlParameters.Add(new SqlParameter("@provinceId", query.ProvinceId));
countSqlParameters.Add(new SqlParameter("@provinceId", query.ProvinceId));
}
if (query.CityId > 0)
{
wheresql += " AND CityId=@cityId ";
sqlParameters.Add(new SqlParameter("@cityId", query.CityId));
countSqlParameters.Add(new SqlParameter("@cityId", query.CityId));
}
if (!string.IsNullOrWhiteSpace(query.Keywords))
{
wheresql += " AND ShopName LIKE @keywords ";
sqlParameters.Add(new SqlParameter("@keywords", $"%{query.Keywords}%"));
countSqlParameters.Add(new SqlParameter("@keywords", $"%{query.Keywords}%"));
}
var pager = new PagedModel <ShopVoteBaseModel>();
using (var helper = DbHelper.CreateDbHelper(true))
{
using (var cmd = helper.CreateCommand($@"SELECT * FROM (SELECT
ROW_NUMBER() OVER(ORDER BY VoteNumber DESC) AS Ranking,
ROW_NUMBER() OVER(PARTITION BY ProvinceId ORDER BY VoteNumber DESC) AS ProvinceRanking,
ROW_NUMBER() OVER(PARTITION BY ProvinceId, CityId ORDER BY VoteNumber DESC) AS CityRanking,
VoteNumber,
ShopName,
Image,
PKID,
ShopId,
ProvinceId,
CityId,
ImageUrls
FROM Activity..tbl_ShopVoteDetail WITH(NOLOCK)) AS T {wheresql}
ORDER BY T.Ranking ASC
OFFSET {query.PageSize*(query.PageIndex-1)} ROWS
FETCH NEXT {query.PageSize} ROWS ONLY;"))
{
cmd.Parameters.AddRange(sqlParameters.ToArray());
pager.Source = await helper.ExecuteSelectAsync <ShopVoteBaseModel>(cmd);
}
using (var cmd = helper.CreateCommand($@"SELECT COUNT(1) FROM Activity..tbl_ShopVoteDetail WITH(NOLOCK) {wheresql}"))
{
cmd.Parameters.AddRange(countSqlParameters.ToArray());
pager.Pager = new PagerModel()
{
Total = (int)await helper.ExecuteScalarAsync(cmd),
PageSize = query.PageSize,
CurrentPage = query.PageIndex
};
}
}
return(pager);
}