/// <summary>
/// 生日会员
/// </summary>
/// <param name="statisticsType"></param>
/// <param name="ShopId"></param>
/// <returns></returns>
private int StatisticsBirthdayMember(MemberStatisticsType statisticsType, long ShopId)
{
using (var conn = MySqlHelper.OpenConnection())
{
DateTime startDate = DateTime.Now;
DateTime endDate = DateTime.Now;
string query = "SELECT count(id) as total FROM Himall_Members where Disabled=false ";
if (ShopId > 0)
{
query += " and ShopId=" + ShopId;
}
switch (statisticsType)
{
case MemberStatisticsType.BirthdayToday:
query += string.Format(" and MONTH(BirthDay)='{0}' and DAY(BirthDay)='{1}'", DateTime.Now.Month, DateTime.Now.Day);
break;
case MemberStatisticsType.BirthdayToMonth:
query += string.Format(" and MONTH(BirthDay)='{0}' and DAY(BirthDay)<>'{1}'", DateTime.Now.Month, DateTime.Now.Day);
break;
case MemberStatisticsType.BirthdayNextMonth:
startDate = DateTime.Now.AddMonths(1);
query += string.Format(" and MONTH(BirthDay)='{0}'", startDate.Month);
break;
}
return(conn.Query <int>(query, new { }).FirstOrDefault());
}
}
/// <summary>
/// 沉睡会员统计
/// </summary>
/// <param name="statisticsType"></param>
/// <param name="ShopId"></param>
/// <returns></returns>
private int StatisticsSleepingMember(MemberStatisticsType statisticsType, long ShopId)
{
using (var conn = MySqlHelper.OpenConnection())
{
DateTime startDate = DateTime.Now;
DateTime endDate = DateTime.Now;
string query = "SELECT count(id) as total FROM Himall_Members where Disabled=false ";
if (ShopId > 0)
{
query += " and ShopId=" + ShopId;
}
switch (statisticsType)
{
case MemberStatisticsType.SleepingThree:
startDate = DateTime.Now.AddMonths(-6);
endDate = DateTime.Now.AddMonths(-3);
query += string.Format(" and LastConsumptionTime>'{0}' and LastConsumptionTime<'{1}'", startDate, endDate);
break;
case MemberStatisticsType.SleepingSix:
startDate = DateTime.Now.AddMonths(-9);
endDate = DateTime.Now.AddMonths(-6);
query += string.Format(" and LastConsumptionTime>'{0}' and LastConsumptionTime<'{1}'", startDate, endDate);
break;
case MemberStatisticsType.SleepingNine:
startDate = DateTime.Now.AddMonths(-12);
endDate = DateTime.Now.AddMonths(-9);
query += string.Format(" and LastConsumptionTime>'{0}' and LastConsumptionTime<'{1}'", startDate, endDate);
break;
case MemberStatisticsType.SleepingTwelve:
startDate = DateTime.Now.AddMonths(-24);
endDate = DateTime.Now.AddMonths(-12);
query += string.Format(" and LastConsumptionTime>'{0}' and LastConsumptionTime<'{1}'", startDate, endDate);
break;
case MemberStatisticsType.SleepingTwentyFour:
endDate = DateTime.Now.AddMonths(-24);
query += string.Format(" and (LastConsumptionTime<'{0}')", endDate);
break;
}
return(conn.Query <int>(query, new { }).FirstOrDefault());
}
}