/// <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="userIds"></param>
/// <param name="spendType"></param>
/// <param name="address"></param>
/// <param name="costThing"></param>
/// <param name="costType"></param>
/// <param name="costchannel"></param>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public Dictionary <int, decimal> GetStatisticsCost(List <long> userIds, int spendType, string address, string costThing, int costType, long costchannel, DateTime startTime, DateTime endTime)
{
var select = "select CostInOrOut,sum(cost) Sum from costcontent ";
var where = new StringBuilder(" where UserId in @UserIds and IsDel=@IsDel ");
if (spendType != -1)
{
where.Append(" AND SpendType = @SpendType ");
}
if (costType != -1)
{
where.Append(" AND CostType = @CostType ");
}
if (costchannel != -1)
{
where.Append(" and CostChannel=@CostChannel ");
}
if (startTime > new DateTime(1900, 1, 1))
{
where.Append(" AND CostTime >= @StartTime ");
}
if (endTime > new DateTime(1900, 1, 1))
{
where.Append(" AND CostTime <= @EndTime ");
}
if (!string.IsNullOrEmpty(address))
{
where.Append(" AND CostAddress LIKE @CostAddress ");
}
if (!string.IsNullOrEmpty(costThing))
{
where.Append(" AND CostThing LIKE @CostThing ");
}
var groupby = " GROUP BY CostInOrOut ";
var param = new
{
UserIds = userIds.ToArray(),
SpendType = spendType,
CostType = costType,
CostChannel = costchannel,
StartTime = startTime,
EndTime = endTime,
CostAddress = "%" + address + "%",
CostThing = "%" + costThing + "%",
IsDel = FlagEnum.HadZore.GetHashCode()
};
var result = 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["Sum"];
var inOrOut = fields["CostInOrOut"];
result.Add(DataTypeConvertHelper.ToInt(inOrOut), DataTypeConvertHelper.ToDecimal(sum));
}
}
return(result);
}