/// <summary> /// 获取分类统计 /// </summary> /// <param name="starTime"></param> /// <param name="endTime"></param> /// <param name="userIds"></param> /// <param name="inOrOut"></param> /// <param name="channelId"></param> /// <returns></returns> public Dictionary <int, decimal> GetCostMonthStatistics(DateTime starTime, DateTime endTime, List <long> userIds, CostInOrOutEnum inOrOut, long channelId) { var costDic = _dataAccess.GetStatisticsCostMonth(starTime, endTime, userIds, inOrOut, channelId); return(costDic); }
/// <summary> /// 获取分类统计 /// </summary> /// <param name="starTime"></param> /// <param name="endTime"></param> /// <param name="userIds"></param> /// <param name="inOrOut"></param> /// <param name="channelId"></param> /// <returns></returns> public List <CanPayAcountModel> GetCostTypeStatistics(DateTime starTime, DateTime endTime, List <long> userIds, CostInOrOutEnum inOrOut, long channelId) { var costTypeList = _dataAccess.GetStatisticsCostTypePay(starTime, endTime, userIds, CostInOrOutEnum.Out, channelId); return(costTypeList); }
/// <summary> /// 获取消费分类记录 /// </summary> /// <param name="starTime"></param> /// <param name="endTime"></param> /// <param name="userIds"></param> /// <param name="inOrOut"></param> /// <param name="channelId"></param> /// <returns></returns> public Dictionary <int, decimal> GetStatisticsCostMonth(DateTime starTime, DateTime endTime, List <long> userIds, CostInOrOutEnum inOrOut, long channelId) { var select = @"SELECT CostYear,CostMonth, sum( cost ) CostCount FROM costcontent " ; var groupby = " GROUP BY CostYear,CostMonth ORDER BY CostYear desc,CostMonth desc"; var where = new StringBuilder("WHERE UserId in @UserIds and IsDel=@IsDel "); where.Append(" AND SpendType!=2 "); where.Append(" AND CostInOrOut = @CostInOrOut "); if (starTime > new DateTime(1900, 1, 1)) { where.Append(" and CostTime>@StartTime "); } if (endTime > new DateTime(1900, 1, 1)) { where.Append(" and CostTime<=@EndTime "); } if (channelId > 0) { where.Append(" and CostChannel=@CostChannel "); } var param = new { UserIds = userIds.ToArray(), CostInOrOut = inOrOut.GetHashCode(), StartTime = starTime, EndTime = endTime, CostChannel = channelId, IsDel = FlagEnum.HadZore.GetHashCode() }; var resultMap = new Dictionary <int, decimal>(); using (var conn = SqlConnectionHelper.GetOpenConnection()) { IEnumerable <dynamic> query = conn.Query(select + where + groupby, param); foreach (var rows in query) { if (!(rows is IDictionary <string, object> fields)) { continue; } var sum = fields["CostCount"]; var costYear = fields["CostYear"]; var costMonth = fields["CostMonth"]; resultMap.Add(DataTypeConvertHelper.ToInt(costYear) * 100 + DataTypeConvertHelper.ToInt(costMonth), DataTypeConvertHelper.ToDecimal(sum)); } } return(resultMap); }
/// <summary> /// 获取消费天记录 /// </summary> /// <param name="starTime"></param> /// <param name="endTime"></param> /// <param name="userId"></param> /// <param name="inOrOut"></param> /// <param name="channelId"></param> /// <returns></returns> public List <CanPayAcountModel> GetStatisticsCostDayPay(DateTime starTime, DateTime endTime, long userId, CostInOrOutEnum inOrOut, long channelId) { var select = @"select DATE(CostTime) as CostDay,sum(cost) as CostCount from costcontent "; var groupby = " group by CostDay ORDER BY CostDay"; var where = new StringBuilder("WHERE UserId = @UserId and IsDel=@IsDel "); where.Append(" AND SpendType!=2 "); where.Append(" AND CostInOrOut = @CostInOrOut "); if (starTime > new DateTime(1900, 1, 1)) { where.Append(" and CostTime>@StartTime "); } if (endTime > new DateTime(1900, 1, 1)) { where.Append(" and CostTime<=@EndTime "); } if (channelId > 0) { where.Append(" and CostChannel=@CostChannel "); } var param = new { UserId = userId, CostInOrOut = inOrOut.GetHashCode(), StartTime = starTime, EndTime = endTime, CostChannel = channelId, IsDel = FlagEnum.HadZore.GetHashCode() }; using (var conn = SqlConnectionHelper.GetOpenConnection()) { IEnumerable <CanPayAcountModel> query = conn.Query <CanPayAcountModel>(select + where + groupby, param); return(query.ToList()); } }
/// <summary> /// 获取消费分类记录 /// </summary> /// <param name="starTime"></param> /// <param name="endTime"></param> /// <param name="userIds"></param> /// <param name="inOrOut"></param> /// <param name="channelId"></param> /// <returns></returns> public List <CanPayAcountModel> GetStatisticsCostTypePay(DateTime starTime, DateTime endTime, List <long> userIds, CostInOrOutEnum inOrOut, long channelId) { var select = @"SELECT CostTypeName, sum( cost ) CostCount FROM costcontent " ; var groupby = " GROUP BY CostTypeName ORDER BY CostCount DESC"; var where = new StringBuilder("WHERE UserId in @UserIds and IsDel=@IsDel "); where.Append(" AND SpendType!=2 "); where.Append(" AND CostInOrOut = @CostInOrOut "); if (starTime > new DateTime(1900, 1, 1)) { where.Append(" and CostTime>@StartTime "); } if (endTime > new DateTime(1900, 1, 1)) { where.Append(" and CostTime<=@EndTime "); } if (channelId > 0) { where.Append(" and CostChannel=@CostChannel "); } var param = new { UserIds = userIds.ToArray(), CostInOrOut = inOrOut.GetHashCode(), StartTime = starTime, EndTime = endTime, CostChannel = channelId, IsDel = FlagEnum.HadZore.GetHashCode() }; using (var conn = SqlConnectionHelper.GetOpenConnection()) { IEnumerable <CanPayAcountModel> query = conn.Query <CanPayAcountModel>(select + where + groupby, param); return(query.ToList()); } }