/// <summary>
/// 获取店铺运营概况明细
/// </summary>
/// <param name="userContext"></param>
/// <param name="type"></param>
/// <returns></returns>
public List <AccountOverviewDetail> GetAccountOverViewDetail(UserContext userContext, int type)
{
var sDate = DateTime.Now.ToShortDateString();
var eDate = DateTime.Now.ToShortDateString();
var dateAdd = 0;
var salesReportList = new List <AccountOverviewDetail>();
switch (type)
{
case (int)DateSpanEnum.Yesterday:
sDate = DateTime.Now.AddDays(-1).ToShortDateString();
eDate = sDate;
break;
case (int)DateSpanEnum.ThisWeek:
dateAdd = 7;
sDate = DateTime.Now.AddDays(-6).ToShortDateString();
break;
case (int)DateSpanEnum.ThisMonth:
dateAdd = DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month);
sDate = DateTime.Now.ToString("yyyy-MM") + "-01";
eDate = DateTime.Parse(sDate).AddMonths(1).AddDays(-1).ToShortDateString();
break;
}
sDate = sDate + " 00:00:00";
eDate = eDate + " 23:59:59";
var strSqlCoupon = new StringBuilder();
strSqlCoupon.Append("SELECT CONVERT(VARCHAR(10),saleTime,120) AS Date, ");
strSqlCoupon.Append("SUM(RealMoney) AS salesMoney, ");
strSqlCoupon.Append("SUM(AbleMoney-RealMoney) AS salesGrossProfit ");
strSqlCoupon.Append("FROM dbo.T_Sale_List AS A ");
strSqlCoupon.Append("WHERE accID=@AccountId ");
strSqlCoupon.Append("AND saleTime BETWEEN @sDate AND @eDate ");
strSqlCoupon.Append("GROUP BY CONVERT(VARCHAR(10),saleTime,120) ");
var sqlParams = new
{
AccountId = userContext.AccId,
sDate,
eDate
};
var dapperParam = new DynamicParameters(sqlParams);
var sqlQuery = new SqlQuery(strSqlCoupon.ToString(), dapperParam);
var overviewDetailList = _salesReportDapperRepository.FindAll(sqlQuery);
if (type != 0 && type != -1)
{
var dealDate = Convert.ToDateTime(sDate);
for (var i = 0; i < dateAdd; i++)
{
var tempDate = new DateTime(dealDate.Year, dealDate.Month, dealDate.Day, 0, 0, 0).AddDays(i);
if (overviewDetailList == null)
{
var tempAccountOverviewDetail = new AccountOverviewDetail
{
Date = tempDate,
SalesMoney = 0,
SalesGrossProfit = 0
};
salesReportList.Add(tempAccountOverviewDetail);
}
else
{
var accountOverviewDetails = overviewDetailList as AccountOverviewDetail[] ??
overviewDetailList.ToArray();
var overviewDetails = accountOverviewDetails.ToArray();
var tempAccountOverviewDetail = new AccountOverviewDetail
{
Date = tempDate,
SalesMoney =
overviewDetails.Where(x => x.Date == tempDate)
.Select(x => x.SalesMoney)
.FirstOrDefault(),
SalesGrossProfit =
overviewDetails.Where(x => x.Date == tempDate)
.Select(x => x.SalesGrossProfit)
.FirstOrDefault()
};
salesReportList.Add(tempAccountOverviewDetail);
}
}
}
else
{
salesReportList = overviewDetailList.ToList();
}
return(salesReportList);
}
/// <summary>
/// 获取店铺基本信息
/// </summary>
/// <param name="userContext"></param>
/// <returns></returns>
public AccountBaseInfo GetAccountBaseInfo(UserContext userContext)
{
var accountInfo = _accountDapperRepository.Find(x => x.Id == userContext.AccId);
var accountBusiness = _accountBusinessDapperRepository.Find(x => x.accountid == userContext.AccId);
var accountUser =
_accountUserDapperRepository.Find(x => x.Id == userContext.UserId && x.AccountId == userContext.AccId);
var accountExpand = _accountExpandDapperRepository.Find(x => x.AccountId == userContext.AccId);
var accountLogoModel = _accountLogoDapperRepository.Find(x => x.ShopperId == userContext.AccId);
//头像
var accountLogo = "/v/assets/images/logo.png";
var accountAvatar = "/SetUp/assets/img/pic-default.png";
if (accountLogoModel != null && !string.IsNullOrEmpty(accountLogoModel.ImgUrl))
{
accountLogo = "/upload/logo/" + accountLogoModel.ImgUrl;
}
if (!string.IsNullOrEmpty(accountExpand.t_Avatar))
{
accountAvatar = "http://img.i200.cn" + accountExpand.t_Avatar + "!small";
}
//礼金券
var strSqlCoupon = @"SELECT @ActiveCouponNum=COUNT(1) FROM T_Order_CouponList
LEFT JOIN T_Order_CouponInfo ON T_Order_CouponList.groupId=T_Order_CouponInfo.id
WHERE T_Order_CouponList.toAccId=@AccountId
AND (T_Order_CouponList.couponStatus=0 OR T_Order_CouponList.couponStatus=2)
AND (T_Order_CouponInfo.couponType=1 OR T_Order_CouponInfo.couponType=2)";
var sqlParams = new
{
AccountId = userContext.AccId
};
var dapperParam = new DynamicParameters(sqlParams);
dapperParam.Add("ActiveCouponNum", dbType: DbType.Int32, direction: ParameterDirection.Output);
var sqlQuery = new SqlQuery(strSqlCoupon, dapperParam);
_orderGouponDapperRepository.FindAll(sqlQuery);
var accountActiveCoupon = dapperParam.Get <int>("ActiveCouponNum");
//今日积分
var strSqlIntegral = @"SELECT @TodayIntegral=SUM(CAST(EditVal AS INT)) FROM T_LogInfo
WHERE accID=@AccountId
AND Keys='Integral'
AND CreatTime>convert(varchar(10),getdate(),120)
IF @TodayIntegral IS NULL SET @TodayIntegral=0;";
var sqlParams2 = new
{
AccountId = userContext.AccId
};
var dapperParam2 = new DynamicParameters(sqlParams2);
dapperParam2.Add("TodayIntegral", dbType: DbType.Int32, direction: ParameterDirection.Output);
var sqlQuery2 = new SqlQuery(strSqlIntegral, dapperParam2);
_accountUserLogInfoDapperRepository.FindAll(sqlQuery2);
var accountIntegralToday = dapperParam2.Get <int>("TodayIntegral");
//资金账户
decimal accountTotalMoney = 0;
var responesModelAccountbook = _accountbookService.GetWithdrawingBalance(userContext);
if (responesModelAccountbook.Code == 200)
{
var responseData = (AccountBalanceResponse)responesModelAccountbook.Data;
accountTotalMoney = responseData.TotalBalance;
}
var accountBaseInfo = new AccountBaseInfo();
accountBaseInfo.accountId = accountInfo.Id;
accountBaseInfo.accountName = accountInfo.CompanyName;
accountBaseInfo.accountContactPhone = accountUser.PhoneNumber;
if (accountBusiness.aotjb != 3 && accountBusiness.aotjb != 5)
{
accountBaseInfo.accountLicense = 1;
}
else
{
accountBaseInfo.accountLicense = accountBusiness.aotjb;
}
accountBaseInfo.accountLicenseName =
StatusDictionary.DicAccountLicenseNameStatus[accountBaseInfo.accountLicense];
accountBaseInfo.accountEnterprise = string.IsNullOrEmpty(accountInfo.EnterpriseId) ? 0 : 1;
accountBaseInfo.accountLogo = accountLogo;
accountBaseInfo.accountAvatar = accountAvatar;
accountBaseInfo.accountIntegral = accountBusiness.integral;
accountBaseInfo.accountIntegralToday = accountIntegralToday;
accountBaseInfo.accountActiveCoupon = accountActiveCoupon;
accountBaseInfo.accountTotalMoney = accountTotalMoney;
accountBaseInfo.accountLoginUser = new accountLoginUser();
accountBaseInfo.accountLoginUser.userId = accountUser.Id;
accountBaseInfo.accountLoginUser.userName = accountUser.Name;
accountBaseInfo.accountLoginUser.role = accountUser.Grade == "管理员" ? 1 : 0;
return(accountBaseInfo);
}
/// <summary>
/// 获取店铺今日运营概况
/// </summary>
/// <param name="userContext"></param>
/// <returns></returns>
public AccountOverview GetAccountOverViewToday(UserContext userContext)
{
var accountBusiness = _accountBusinessDapperRepository.Find(x => x.accountid == userContext.AccId);
//销售情况
var strSqlCoupon =
@"SELECT @SalesMoney=cast(sum(RealMoney) as decimal(18, 2)),@GoodsSalesCount=SUM(saleNum) FROM dbo.T_SaleInfo
WHERE accID=@AccountId AND saleTime BETWEEN @sDate AND @eDate;
IF @SalesMoney IS NULL SET @SalesMoney=0;
IF @GoodsSalesCount IS NULL SET @GoodsSalesCount=0;
";
var sDate = DateTime.Now.ToShortDateString();
var eDate = DateTime.Now.ToShortDateString();
sDate = sDate + " 00:00:00";
eDate = eDate + " 23:59:59";
var sqlParams = new
{
AccountId = userContext.AccId,
sDate,
eDate
};
var dapperParam = new DynamicParameters(sqlParams);
dapperParam.Add("SalesMoney", dbType: DbType.Decimal, direction: ParameterDirection.Output);
dapperParam.Add("GoodsSalesCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
var sqlQuery = new SqlQuery(strSqlCoupon, dapperParam);
_salesDapperRepository.FindAll(sqlQuery);
var salesMoney = dapperParam.Get <decimal>("SalesMoney");
var goodsSalesCount = dapperParam.Get <int>("GoodsSalesCount");
//库存警告
var strSqlGoodsWarning = new StringBuilder();
strSqlGoodsWarning.Append("SELECT @GoodsWarning=COUNT( DISTINCT a.gid) ");
strSqlGoodsWarning.Append("from T_GoodsInfo ");
strSqlGoodsWarning.Append("a left join T_Goods_Sku b ");
strSqlGoodsWarning.Append("on a.gid=b.gid ");
strSqlGoodsWarning.Append("where a.accID=@accId and ISNULL(a.isDown,0)=0 ");
strSqlGoodsWarning.Append(
"and ((a.IsExtend=1 and ( b.gsQuantity>=b.LimitUpper or b.gsQuantity<=b.LimitLower)) ");
strSqlGoodsWarning.Append(
"or (ISNULL(a.IsExtend,0)=0 and (a.gQuantity>=a.LimitUpper or a.gQuantity<=a.LimitLower))) ");
var sqlParamsGoodsWarning = new
{
accId = userContext.AccId
};
var dapperParamGoodsWarning = new DynamicParameters(sqlParamsGoodsWarning);
dapperParamGoodsWarning.Add("GoodsWarning", dbType: DbType.Int32, direction: ParameterDirection.Output);
var sqlQueryGoodsWarning = new SqlQuery(strSqlGoodsWarning.ToString(), dapperParamGoodsWarning);
_salesDapperRepository.FindAll(sqlQueryGoodsWarning);
var goodsWarning = dapperParamGoodsWarning.Get <int>("GoodsWarning");
//店铺会员生日
var userBirthdayCount = 0;
var responseUser = _userService.GetBirthdayUsers(userContext,
DateTime.Parse(DateTime.Now.ToShortDateString()));
if (responseUser.Code == (int)ErrorCodeEnum.Success)
{
userBirthdayCount = ((IEnumerable <BirthdayUsersResult>)responseUser.Data).Count();
}
var expenseService = new ExpensesService();
var accountOverview = new AccountOverview
{
SalesMoney = salesMoney,
GoodsSalesCount = goodsSalesCount,
UserBirthdayCount = userBirthdayCount,
GoodsStockCount = goodsWarning,
SmsCount = accountBusiness.dxunity
};
var searchParams = new ExpensesSearchParam
{
StartDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1, 0, 0, 0),
EndDate = DateTime.Now
};
//本月支出总额
var expenseResult = expenseService.GetExpenses(userContext, searchParams);
if (expenseResult.Code == (int)ErrorCodeEnum.Success)
{
accountOverview.ThisMonthExpense = ((ExpensesSearchResult)expenseResult.Data).TotalExpensesAmount;
}
//资金账户可提现余额
accountOverview.TotalMoeny =
((AccountBalanceResponse)_accountbookService.GetWithdrawingBalance(userContext).Data).TotalBalance;
//会员总数
accountOverview.UsersNum = _userInfoDapperRepository.FindAll(x => x.AccId == userContext.AccId).Count();
//今日手机橱窗订单总数
var ordersStrSql = new StringBuilder();
var searchDate = Convert.ToDateTime(DateTime.Now.ToShortDateString());
ordersStrSql.Append(
"SELECT COUNT(bid) as TotalOrdersNum FROM T_Goods_Booking WHERE accid=@accId AND payType=1 AND bState IN (4,6) AND bInsertTime BETWEEN @StartDate AND @EndDate;");
var sqlParamMobileOrdersNum = new
{
accId = userContext.AccId,
StartDate = searchDate,
EndDate = searchDate.AddDays(1).AddSeconds(-1)
};
var dapperParamMobileOrdersNum = new DynamicParameters(sqlParamMobileOrdersNum);
var sqlQueryMobileOrdersNum = new SqlQuery(ordersStrSql.ToString(), dapperParamMobileOrdersNum);
accountOverview.TodayMobileOrdersNum = Convert.ToInt32(_getRepository.FindAll(sqlQueryMobileOrdersNum).FirstOrDefault());
return(accountOverview);
}
public IEnumerable <TEntity> FindAll()
{
return(ExecuteWithTryCatch(() => _internalRepository.FindAll()));
}
/// <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
});
}
/// <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
});
}