/// <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());
}
}
