public ResponseModel GetExpenses([FromUri] ExpensesSearchParam searchParam)
{
if (searchParam == null)
{
searchParam = new ExpensesSearchParam();
}
//校验时间参数
var checkResult = CheckDateParams(searchParam);
if (checkResult.Code > 0)
{
return(checkResult);
}
//校验单页最大数据量不超过100条,防止通过接口单页请求大批量数据
if (searchParam.PageSize != null && searchParam.PageSize > 100)
{
return(FunctionReturn(new ResponseModel
{
Code = (int)ErrorCodeEnum.MoreThanMaxSize
}));
}
if (searchParam.StartDate != null && searchParam.EndDate != null)
{
var newStartDate = (DateTime)searchParam.StartDate;
searchParam.StartDate = new DateTime(newStartDate.Year, newStartDate.Month, newStartDate.Day);
var newEndDate = (DateTime)searchParam.EndDate;
searchParam.EndDate =
new DateTime(newEndDate.Year, newEndDate.Month, newEndDate.Day).AddDays(1).AddSeconds(-1);
}
var result = _expensesService.GetExpenses(GetUserContext(), searchParam);
return(FunctionReturn(result));
}
/// <summary>
/// 校验时间参数
/// </summary>
/// <param name="searchParam"></param>
/// <returns></returns>
private ResponseModel CheckDateParams(ExpensesSearchParam searchParam)
{
var responseResult = new ResponseModel();
//开始时间和结束时间不能为空
var checkOneOfDateIsNull = (searchParam.StartDate != null && searchParam.EndDate == null) ||
(searchParam.StartDate == null && searchParam.EndDate != null);
if (checkOneOfDateIsNull)
{
responseResult = Fail(ErrorCodeEnum.CheckStartDateOrEndDateIsNull);
}
//开始时间不能小于结束时间
if (searchParam.StartDate != null && searchParam.EndDate != null &&
searchParam.StartDate > searchParam.EndDate)
{
responseResult = Fail(ErrorCodeEnum.SearchDateParamInvalid);
}
return(responseResult);
}
/// <summary>
/// 获取支出记录列表
/// </summary>
/// <returns></returns>
public ResponseModel GetExpenses(UserContext userContext, ExpensesSearchParam param)
{
//1.获取筛选的条件
if (param.CurrentPage == null || param.CurrentPage < 1)
{
param.CurrentPage = 1;
}
if (param.PageSize == null || param.PageSize < 1)
{
param.PageSize = 25;
}
var result = new ExpensesSearchResult();
var mainCategoryName = string.Empty;
var subCategoryName = string.Empty;
var strSql = new StringBuilder();
var sqlWhereBuilder = new StringBuilder();
var tRowBuilder = new StringBuilder();
sqlWhereBuilder.Append(" WHERE t_PayRecord.ShopperId= @ShopperId");
//日期过滤
if (param.StartDate != null && param.EndDate != null && param.StartDate <= param.EndDate)
{
sqlWhereBuilder.Append(" and t_PayRecord.PayDate >=@StartDate and t_PayRecord.PayDate <=@EndDate ");
}
//支出人员过滤
if (param.Staff != null && param.Staff > 0)
{
sqlWhereBuilder.Append(" and t_PayRecord.InsertUserId =@InsertUserId ");
}
//支出大分类过滤
if (param.MainCategoryId != null)
{
var mainCategory = _expensesCategoryRepository.Find(x => x.Id == param.MainCategoryId);
if (mainCategory != null)
{
mainCategoryName = mainCategory.Name;
sqlWhereBuilder.Append(" and t_PayRecord.PayMaxType =@PayMaxType ");
}
}
//支出小分类过滤
if (param.SubCategoryId != null)
{
var subCategory = _expensesCategoryRepository.Find(x => x.Id == param.SubCategoryId);
if (subCategory != null)
{
subCategoryName = subCategory.Name;
sqlWhereBuilder.Append(" and t_PayRecord.PayMinType =@PayMinType ");
}
}
//筛选项
tRowBuilder.Append(" SELECT ROW_NUMBER() OVER ( ");
tRowBuilder.Append(" ORDER BY t_PayRecord.ID DESC ");
tRowBuilder.Append(" ) AS rownumber ");
tRowBuilder.Append(
" ,*,PaySum as Amount,PayMaxType as MainCategoryName,PayName as Notes,PayMinType as SubCategoryName,ShopperId as MerchanId,insertUserName as UserName");
tRowBuilder.Append(" FROM t_PayRecord ");
tRowBuilder.Append(sqlWhereBuilder);
//分页查询
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 ;");
//统计支出总金额
strSql.Append(" SELECT @TotalMoney= ISNULL(SUM(T.PaySum),0),@TotalNum=COUNT(1) from ( ");
strSql.Append(tRowBuilder);
strSql.Append(" ) AS T");
var sqlParams = new
{
ShopperId = userContext.AccId,
param.StartDate,
param.EndDate,
InsertUserId = param.Staff,
PayMaxType = mainCategoryName,
PayMinType = subCategoryName,
param.PageSize,
PageIndex = param.CurrentPage
};
var dapperParam = new DynamicParameters(sqlParams);
dapperParam.Add("TotalMoney", dbType: DbType.Decimal, direction: ParameterDirection.Output, precision: 10, scale: 2);
dapperParam.Add("TotalNum", dbType: DbType.Int32, direction: ParameterDirection.Output);
var sqlQuery = new SqlQuery(strSql.ToString(), dapperParam);
result.Items = _expensesRepository.FindAll(sqlQuery);
result.CurrentPage = param.CurrentPage ?? 1;
result.PageSize = param.PageSize ?? 25;
result.TotalExpensesAmount = dapperParam.Get <decimal>("TotalMoney");
result.TotalSize = dapperParam.Get <int>("TotalNum");
result.TotalPage =
Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(result.TotalSize) / Convert.ToDecimal(result.PageSize)));
//2.返回查询结果
return(new ResponseModel
{
Code = result.Items.Any() ? (int)ErrorCodeEnum.Success : (int)ErrorCodeEnum.NotFound,
Data = result
});
}
/// <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);
}
