public ResponseModel GetStoreMoneyHistoryList([FromUri] UserStoreMoneySearchParam searchParam)
{
//1.校验查询参数是否为空，空则新建对象
if (searchParam == null)
{
searchParam = new UserStoreMoneySearchParam();
}
//2.校验单页最大数据量不超过100条,防止通过接口单页请求大批量数据
if (searchParam.PageSize != null && searchParam.PageSize > 100)
{
return(FunctionReturn(new ResponseModel
{
Code = (int)ErrorCodeEnum.MoreThanMaxSize
}));
}
//3.校验查询的日期参数是否合法（开始日期小于等于结束日期）
if (searchParam.StartDate != null && searchParam.EndDate != null &&
searchParam.StartDate > searchParam.EndDate)
{
return(FunctionReturn(new ResponseModel
{
Code = (int)ErrorCodeEnum.SearchDateInvalid
}));
}
return(_userStoreMoneyCardService.GetStoreMoneyHistoryList(GetUserContext(), searchParam));
}
/// <summary>
/// 获取会员储值总计数据（总共会员数、总储值、总消费、总余额）
/// </summary>
/// <param name="userContext"></param>
/// <param name="searchParam"></param>
/// <returns></returns>
public UserStoreTransactionRecordResult GetUserStoreMoneyTotalData(UserContext userContext,
UserStoreMoneySearchParam searchParam)
{
var totalStoreMoneyStrSql = new StringBuilder();
var totalShoppingStrSql = new StringBuilder();
var totalBalanceStrSql = new StringBuilder();
var totalUsersStrSql = new StringBuilder();
var strSql = new StringBuilder();
//统计总金额
totalStoreMoneyStrSql.Append(
" SELECT @TotalStoreMoney= ISNULL(SUM(T_User_LogInfo.EditVal),0) from T_User_LogInfo where accId=@AccId ");
totalShoppingStrSql.Append(
" SELECT @TotalShopping= ISNULL(SUM(T_User_LogInfo.EditVal),0) from T_User_LogInfo where accId=@AccId ");
totalBalanceStrSql.Append(
" SELECT @TotalBalance= ISNULL(SUM(T_User_LogInfo.FinalVal),0) from T_User_LogInfo where accId=@AccId ");
totalUsersStrSql.Append(
" SELECT @TotalUserCount= count(DISTINCT uid) from T_User_LogInfo where accId=@AccId ");
var userIdStr = " and uid=@UserId ";
if (searchParam.UserId != 0)
{
totalStoreMoneyStrSql.Append(userIdStr);
totalShoppingStrSql.Append(userIdStr);
totalBalanceStrSql.Append(userIdStr);
totalUsersStrSql.Append(userIdStr);
}
var searchDateStr = " and T_User_LogInfo.LogTime >=@StartDate and T_User_LogInfo.LogTime <=@EndDate ";
if (searchParam.StartDate != null && searchParam.EndDate != null &&
searchParam.StartDate <= searchParam.EndDate)
{
var newEndDate = (DateTime)searchParam.EndDate;
searchParam.EndDate = newEndDate.AddDays(1).AddSeconds(-1);
totalStoreMoneyStrSql.Append(searchDateStr);
totalShoppingStrSql.Append(searchDateStr);
totalBalanceStrSql.Append(searchDateStr);
totalUsersStrSql.Append(searchDateStr);
}
//记录类型过滤（1: 储值消费 2：充值）
var operateTypeStr = " and T_User_LogInfo.logType =@LogType and T_User_LogInfo.itemType =@ItemType ";
if (searchParam.OperateType != null)
{
totalStoreMoneyStrSql.Append(operateTypeStr);
totalShoppingStrSql.Append(operateTypeStr);
totalBalanceStrSql.Append(operateTypeStr);
totalUsersStrSql.Append(operateTypeStr);
}
else
{
var operateRechargeType =
" and (T_User_LogInfo.logType =1 and T_User_LogInfo.itemType =1 ) ; ";
var operateShoppingType = " and (T_User_LogInfo.logType =1 and T_User_LogInfo.itemType =2) ; ";
var operateTypeStrElse =
" and ((T_User_LogInfo.logType =1 and T_User_LogInfo.itemType =1 ) or (T_User_LogInfo.logType =1 and T_User_LogInfo.itemType =2)) ;";
totalStoreMoneyStrSql.Append(operateRechargeType);
totalShoppingStrSql.Append(operateShoppingType);
totalBalanceStrSql.Append(operateTypeStrElse);
totalUsersStrSql.Append(operateTypeStrElse);
}
var sqlParams = new
{
userContext.AccId,
searchParam.UserId,
LogType = (int)UserLogTypeEnum.StoreChange,
ItemType =
searchParam.OperateType == 1
? (int)UserLogItemTypeEnum.Shopping
: (int)UserLogItemTypeEnum.ExchangeIntegral,
searchParam.StartDate,
searchParam.EndDate,
searchParam.PageSize,
PageIndex = searchParam.CurrentPage
};
var dapperParam = new DynamicParameters(sqlParams);
dapperParam.Add("TotalStoreMoney", dbType: DbType.Decimal, direction: ParameterDirection.Output);
dapperParam.Add("TotalShopping", dbType: DbType.Decimal, direction: ParameterDirection.Output);
dapperParam.Add("TotalBalance", dbType: DbType.Decimal, direction: ParameterDirection.Output);
dapperParam.Add("TotalUserCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
strSql.Append(totalStoreMoneyStrSql)
.Append(totalBalanceStrSql)
.Append(totalShoppingStrSql)
.Append(totalUsersStrSql);
var sqlQuery = new SqlQuery(strSql.ToString(), dapperParam);
var result = _userStoreTransactionRecordSumRepository.FindAll(sqlQuery);
//汇总结果集
decimal totalStoreMoney = 0;
decimal totalShopping = 0;
var totalUserCount = dapperParam.Get <int?>("TotalUserCount") ?? 0;
var totalBalance = dapperParam.Get <decimal?>("TotalBalance") ?? 0;
switch (searchParam.OperateType)
{
case 1:
totalStoreMoney = dapperParam.Get <decimal?>("TotalStoreMoney") ?? 0;
totalShopping = 0;
break;
case 2:
totalStoreMoney = 0;
totalShopping = dapperParam.Get <decimal?>("TotalShopping") ?? 0;
break;
default:
totalStoreMoney = dapperParam.Get <decimal?>("TotalStoreMoney") ?? 0;
totalShopping = dapperParam.Get <decimal?>("TotalShopping") ?? 0;
break;
}
return(new UserStoreTransactionRecordResult
{
TotalUserCount = totalUserCount,
TotalBalance = totalBalance,
TotalShopping = totalShopping,
TotalStoreMoney = totalStoreMoney
});
}
/// <summary>
/// 获取储值历史记录
/// </summary>
/// <param name="userContext"></param>
/// <param name="searchParam"></param>
/// <returns></returns>
public ResponseModel GetStoreMoneyHistoryList(UserContext userContext, UserStoreMoneySearchParam searchParam)
{
if (searchParam.CurrentPage == null || searchParam.CurrentPage < 1)
{
searchParam.CurrentPage = 1;
}
if (searchParam.PageSize == null || searchParam.PageSize < 1)
{
searchParam.PageSize = 25;
}
//1.初始化查询条件
var userStoreTransactionRecordResult = new UserStoreTransactionRecordResult();
var strSql = new StringBuilder();
var sqlWhereBuilder = new StringBuilder();
var tRowBuilder = new StringBuilder();
sqlWhereBuilder.Append(" WHERE T_User_LogInfo.AccId= @AccId ");
//日期过滤
if (searchParam.StartDate != null && searchParam.EndDate != null &&
searchParam.StartDate <= searchParam.EndDate)
{
var newEndDate = (DateTime)searchParam.EndDate;
searchParam.EndDate = newEndDate.AddDays(1).AddSeconds(-1);
sqlWhereBuilder.Append(
" and T_User_LogInfo.LogTime >=@StartDate and T_User_LogInfo.LogTime <=@EndDate ");
}
if (searchParam.UserId != 0)
{
sqlWhereBuilder.Append(" and T_User_LogInfo.uid =@UserId ");
}
//记录类型过滤（1: 储值消费 2：充值）
sqlWhereBuilder.Append(
searchParam.OperateType != null
? " and T_User_LogInfo.logType =@LogType and T_User_LogInfo.itemType =@ItemType "
: " and (( T_User_LogInfo.logType =1 and T_User_LogInfo.itemType =1) or (T_User_LogInfo.logType =1 and T_User_LogInfo.itemType =2 )) ");
//2.筛选项
tRowBuilder.Append(" SELECT ROW_NUMBER() OVER ( ");
tRowBuilder.Append(" ORDER BY T_User_LogInfo.userLogID DESC ");
tRowBuilder.Append(" ) AS rownumber ");
tRowBuilder.Append(
",LogTime as createdAt,T_UserInfo.uid AS userId,T_UserInfo.uName AS userName, logType,itemType,operatorID, dbo.T_Account_User.name AS Salesman,remark,editVal as editMoney,FinalVal as StoreMoney ");
tRowBuilder.Append(" FROM T_User_LogInfo ");
tRowBuilder.Append(" LEFT JOIN T_UserInfo ON T_User_LogInfo.uid = T_UserInfo.uid ");
tRowBuilder.Append(" LEFT JOIN dbo.T_Account_User ON operatorID= dbo.T_Account_User.id ");
tRowBuilder.Append(sqlWhereBuilder);
//3.分页查询
strSql.Append(
" SELECT *,(case when logType=1 and itemType=1 then 1 when logType=1 and itemType=2 then 2 else null end ) as OperateType ");
strSql.Append(" FROM (");
strSql.Append(tRowBuilder);
strSql.Append(" ) AS T");
strSql.Append(" WHERE RowNumber BETWEEN (@PageIndex-1)*@PageSize+1 ");
strSql.Append(" AND @PageSize*@PageIndex ;");
strSql.Append(" SELECT @TotalNum=COUNT(1) from ( ");
strSql.Append(tRowBuilder);
strSql.Append(" ) AS T");
var sqlParams = new
{
userContext.AccId,
searchParam.UserId,
LogType = (int)UserLogTypeEnum.StoreChange,
ItemType =
searchParam.OperateType == 1
? (int)UserLogItemTypeEnum.Shopping
: (int)UserLogItemTypeEnum.ExchangeIntegral,
searchParam.StartDate,
searchParam.EndDate,
PageSize = searchParam.PageSize ?? 25,
PageIndex = searchParam.CurrentPage ?? 1
};
var dapperParam = new DynamicParameters(sqlParams);
dapperParam.Add("TotalNum", dbType: DbType.Int32, direction: ParameterDirection.Output);
var sqlQuery = new SqlQuery(strSql.ToString(), dapperParam);
//5.赋值查询结果
var itemResult = _userStoreMoneyTransactionDapperRepository.FindAll(sqlQuery).ToList();
var summaryResult = GetUserStoreMoneyTotalData(userContext, searchParam);
foreach (var item in itemResult)
{
item.OperateTypeText =
(item.OperateType == 1 ? 1 : 2).ToEnumDescriptionString(typeof(StoreMoneyOperateEnum));
}
userStoreTransactionRecordResult.Items = itemResult;
userStoreTransactionRecordResult.CurrentPage = searchParam.CurrentPage ?? 1;
userStoreTransactionRecordResult.PageSize = searchParam.PageSize ?? 25;
userStoreTransactionRecordResult.TotalSize = dapperParam.Get <int?>("TotalNum") ?? 0;
userStoreTransactionRecordResult.TotalPage =
Convert.ToInt32(
Math.Ceiling(Convert.ToDecimal(userStoreTransactionRecordResult.TotalSize) /
Convert.ToDecimal(userStoreTransactionRecordResult.PageSize)));
if (summaryResult != null)
{
userStoreTransactionRecordResult.TotalBalance = summaryResult.TotalBalance;
userStoreTransactionRecordResult.TotalShopping = summaryResult.TotalShopping;
userStoreTransactionRecordResult.TotalStoreMoney = summaryResult.TotalStoreMoney;
userStoreTransactionRecordResult.TotalUserCount = summaryResult.TotalUserCount;
}
//6.返回查询数据
return(new ResponseModel
{
Code = (int)ErrorCodeEnum.Success,
Data = userStoreTransactionRecordResult
});
}
