public ResponseModel GetUserStoreCardsList([FromUri] UserStoreCardSearchParam searchParam)
{
//1.判断查询参数是否为空，为空则初始化当前页面大小为PageSize=25，CurrentPage=1
if (searchParam == null)
{
searchParam = new UserStoreCardSearchParam();
}
//2.校验单页最大数据量不超过100条,防止通过接口单页请求大批量数据
if (searchParam.PageSize != null && searchParam.PageSize > 100)
{
return(FunctionReturn(new ResponseModel
{
Code = (int)ErrorCodeEnum.MoreThanMaxSize
}));
}
return(_userStoreMoneyCardService.GetUserStoreCardsList(GetUserContext(), searchParam));
}
/// <summary>
/// 获取会员储值卡列表
/// </summary>
/// <param name="userContext"></param>
/// <param name="searchParam"></param>
/// <returns></returns>
public ResponseModel GetUserStoreCardsList(UserContext userContext, UserStoreCardSearchParam searchParam)
{
if (searchParam.CurrentPage == null || searchParam.CurrentPage < 1)
{
searchParam.CurrentPage = 1;
}
if (searchParam.PageSize == null || searchParam.PageSize < 1)
{
searchParam.PageSize = 25;
}
//1.初始化查询条件
var userStoreMoneySearchResult = new UserStoreMoneySearchResult();
var strSql = new StringBuilder();
var sqlWhereBuilder = new StringBuilder();
var logStrSql = new StringBuilder();
var tRowBuilder = new StringBuilder();
sqlWhereBuilder.Append(" WHERE T_UserInfo.AccId= @AccId ");
logStrSql.Append("where T_User_LogInfo.accId=@accId ");
if (searchParam.UserId > 0)
{
sqlWhereBuilder.Append(" and T_UserInfo.uid =@UserId ");
logStrSql.Append("and T_User_LogInfo.uid=@UserId ");
}
//排序
var rankName = searchParam.Rank;
var sort = searchParam.Sort;
if (rankName != "userName")
{
rankName = "uid";
}
if (string.IsNullOrWhiteSpace(sort) || (sort.ToLower() != "desc" && sort.ToLower() != "asc"))
{
sort = "Desc";
}
//2.筛选项
tRowBuilder.Append(" SELECT ROW_NUMBER() OVER ( ");
tRowBuilder.Append(" ORDER BY " + rankName + " " + sort + " ");
tRowBuilder.Append(" ) AS rownumber ");
tRowBuilder.Append(
",uid as UserId ,uName as UserName,uPhone as Phone,accId,uStoreMoney as Balance ");
tRowBuilder.Append(" FROM T_UserInfo ");
tRowBuilder.Append(sqlWhereBuilder);
//3.分页查询
strSql.Append(" SELECT *");
strSql.Append(" FROM (");
strSql.Append(tRowBuilder);
strSql.Append(" ) AS T");
strSql.Append(" WHERE RowNumber BETWEEN (@PageIndex-1)*@PageSize+1 ");
strSql.Append(" AND @PageSize*@PageIndex ;");
//4.统计卡内余额、总共会员
strSql.Append(" SELECT @TotalBalance= ISNULL(SUM(uStoreMoney),0)" +
",@TotalNum=Count(1)" +
",@TotalUsers=COUNT(DISTINCT uid) from T_UserInfo ");
strSql.Append(sqlWhereBuilder);
//5.统计总充值金额
strSql.Append(" select @TotalStoreMoney=sum(EditVal) from T_User_LogInfo ");
strSql.Append(logStrSql);
var sqlParams = new
{
userContext.AccId,
searchParam.UserId,
PageSize = searchParam.PageSize ?? 25,
PageIndex = searchParam.CurrentPage ?? 1
};
var dapperParam = new DynamicParameters(sqlParams);
dapperParam.Add("TotalUsers", dbType: DbType.Int32, direction: ParameterDirection.Output);
dapperParam.Add("TotalStoreMoney", dbType: DbType.Decimal, direction: ParameterDirection.Output);
dapperParam.Add("TotalBalance", dbType: DbType.Decimal, direction: ParameterDirection.Output);
dapperParam.Add("TotalNum", dbType: DbType.Int32, direction: ParameterDirection.Output);
var sqlQuery = new SqlQuery(strSql.ToString(), dapperParam);
var itemResult = _userStoreMoneyCardDapperRepository.FindAll(sqlQuery);
var storeMoneySearchResultItems = itemResult as UserStoreMoneySearchResultItem[] ?? itemResult.ToArray();
if (itemResult != null && storeMoneySearchResultItems.Any())
{
foreach (var oItem in storeMoneySearchResultItems)
{
var item = oItem;
var sum =
_userLogRepository.FindAll(x => x.AccId == userContext.AccId && x.UId == item.UserId)
.Sum(x => x.EditVal);
if (sum != null)
{
oItem.TotalStoreMoney = (decimal)sum;
}
}
}
//6.赋值查询结果
userStoreMoneySearchResult.Items = storeMoneySearchResultItems;
userStoreMoneySearchResult.TotalUsers = dapperParam.Get <int?>("TotalUsers") ?? 0;
userStoreMoneySearchResult.TotalBalance = dapperParam.Get <decimal?>("TotalBalance") ?? 0;
userStoreMoneySearchResult.TotalStoreMoney = dapperParam.Get <decimal?>("TotalStoreMoney") ?? 0;
userStoreMoneySearchResult.CurrentPage = searchParam.CurrentPage ?? 1;
userStoreMoneySearchResult.PageSize = searchParam.PageSize ?? 25;
userStoreMoneySearchResult.TotalSize = dapperParam.Get <int?>("TotalNum") ?? 0;
userStoreMoneySearchResult.TotalPage =
Convert.ToInt32(
Math.Ceiling(Convert.ToDecimal(userStoreMoneySearchResult.TotalSize) /
Convert.ToDecimal(userStoreMoneySearchResult.PageSize)));
//7.返回查询数据
return(new ResponseModel
{
Code = (int)ErrorCodeEnum.Success,
Data = userStoreMoneySearchResult
});
}
