///获取历史最值
public List <SoftUser> GetMaxNumCache(DateTime begin, DateTime end, int softid, int platformid,
net91com.Stat.Core.PeriodOptions period, CacheTimeOption cachetime)
{
string cacheKey = BuildCacheKey("GetMaxNumCache", begin, end, softid, platformid, period);
return(CacheHelper.Get <List <SoftUser> >(cacheKey, CacheTimeOption.TenMinutes, () => GetMaxNum(begin, end, softid, platformid, period)));
}
/// <summary>
/// 获取用户新增/活跃等数据
/// </summary>
/// <param name="softId"></param>
/// <param name="platform"></param>
/// <param name="period"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public List <SoftUser> GetStatUsers(int softId, int platform, net91com.Stat.Core.PeriodOptions period,
DateTime startDate, DateTime endDate)
{
string cmdText = string.Format(
@"select StatDate
,SUM(NewUserCount+NewUserCountFromCache+NewUserCount_Shanzhai-ifnull(NewUserCount_Shualiang,0)) NewUserCount
,SUM(ActiveUserCount+ActiveUserCountFromCache+ActiveUserCount_Shanzhai) ActiveUserCount
,Sum(TotalUserCount+TotalUserCount_Shanzhai) TotalNum
,Sum(ifnull(DownValueUsersForNew,0)) DownValueUsersForNew
,Sum(ifnull(DownValueUsersForAct,0)) DownValueUsersForAct
,SUM(NewUserCount+NewUserCount_Shanzhai-ifnull(NewUserCount_Shualiang,0)) FirstNewUserCount
,SUM(ActiveUserCount+ActiveUserCount_Shanzhai) FirstActiveUserCount
from Sjqd_StatUsers
where SoftID=?SoftID and Period=?Period and StatDate between ?StartDate and ?EndDate{0}
group by StatDate
order by StatDate",
platform != (int)MobileOption.None && platform > 0 ? " and Platform=?Platform" : " and Platform<252 ");
MySqlParameter[] parameters = new MySqlParameter[]
{
new MySqlParameter("?SoftID", softId),
new MySqlParameter("?Platform", platform),
new MySqlParameter("?Period", (int)period),
new MySqlParameter("?StartDate", startDate.ToString("yyyyMMdd")),
new MySqlParameter("?EndDate", endDate.ToString("yyyyMMdd"))
};
List <SoftUser> result = new List <SoftUser>();
using (MySqlCommand cmd = new MySqlCommand(cmdText, new MySqlConnection(connString)))
{
cmd.Connection.Open();
cmd.CommandTimeout = 180;
cmd.Parameters.AddRange(parameters);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int statDate = Convert.ToInt32(reader["StatDate"]);
result.Add(
new SoftUser
{
SoftId = softId,
Platform = platform,
Period = (int)period,
StatDate = new DateTime(statDate / 10000, statDate / 100 % 100, statDate % 100),
NewNum = Convert.ToInt32(reader["NewUserCount"]),
ActiveNum = Convert.ToInt32(reader["ActiveUserCount"]),
TotalNum = Convert.ToInt32(reader["TotalNum"]),
DownValueUsersForAct = Convert.ToInt32(reader["DownValueUsersForAct"]),
DownValueUsersForNew = Convert.ToInt32(reader["DownValueUsersForNew"]),
FirstActiveUserCount = Convert.ToInt32(reader["FirstActiveUserCount"]),
FirstNewUserCount = Convert.ToInt32(reader["FirstNewUserCount"])
});
}
}
}
return(result);
}
/// <summary>
/// 获取用户新增/活跃等数据
/// </summary>
/// <param name="softId"></param>
/// <param name="platform"></param>
/// <param name="period"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="cacheTime"></param>
/// <returns></returns>
public List <SoftUser> GetStatUsers(int softId, int platform, net91com.Stat.Core.PeriodOptions period,
DateTime startDate, DateTime endDate, CacheTimeOption cacheTime)
{
string cacheKey = string.Format("Sjqd_StatUsersService.GetStatUsers-{0}-{1}-{2}-{3}-{4}", softId, platform,
period, startDate, endDate);
return(CacheHelper.Get <List <SoftUser> >(cacheKey, CacheTimeOption.TenMinutes, () => GetStatUsers(softId, platform, period, startDate, endDate)));
}
/// <summary>
/// 获取预测数据
/// </summary>
/// <param name="softid"></param>
/// <param name="platformid"></param>
/// <param name="period"></param>
/// <returns></returns>
public List <ForecastSoftUser> GetForecastSoftUser(int softid, int platformid,
net91com.Stat.Core.PeriodOptions period,
URLoginService loginService)
{
string sqlstr = string.Empty;
if (loginService.LoginUser.AccountType != UserTypeOptions.ChannelPartner &&
loginService.LoginUser.AccountType != UserTypeOptions.Channel)
{
if (platformid != (int)MobileOption.None && platformid > 0)
{
sqlstr =
@" select period,StatDate,platform,softid ,(NewUserCount+NewUserCountFromCache+NewUserCount_Shanzhai) as newnum
,TotalUserCount+TotalUserCount_Shanzhai as totalnum
from U_StatUsers
where softid=?softid and platform=?platform and period=?periodid
Order by StatDate desc
limit 4
";
}
else
{
sqlstr =
@" select period,StatDate,0 as platform,softid
,sum(NewUserCount+NewUserCountFromCache+NewUserCount_Shanzhai) as newnum
,sum(TotalUserCount+TotalUserCount_Shanzhai) as totalnum
from U_StatUsers
where softid=?softid and period=?periodid and platform<252
Group By period,StatDate,softid Order by StatDate desc
limit 4
";
}
}
else
{
List <int> rangeChannelIds = loginService.GetAvailableChannelIds(softid);
if (rangeChannelIds.Count == 0)
{
return(new List <ForecastSoftUser>());
}
string channelIdsString = string.Join(",", rangeChannelIds.Select(a => a.ToString()).ToArray());
sqlstr = string.Format(@" select
period, StatDate,0 as platform, softid ,sum(NewUserCount+NewUserCount_Shanzhai) as newnum,
sum(TotalUserCount+TotalUserCount_Shanzhai) as totalnum
from U_StatChannelUsers
where SoftID=?softid AND ChannelID in ({1})
and Period=?periodid and Platform<252 {0}
group by Period,StatDate,SoftID
order by StatDate desc
limit 4",
(platformid != (int)MobileOption.None && platformid > 0)
? " and platform=?platform "
: "", channelIdsString);
}
var parameters = new []
{
new MySqlParameter("?softid", softid),
new MySqlParameter("?platform", platformid),
new MySqlParameter("?periodid", (int)period)
};
List <ForecastSoftUser> users = new List <ForecastSoftUser>();
using (IDataReader dataReader = MySqlHelper.ExecuteReader(connString, sqlstr, parameters))
{
while (dataReader.Read())
{
ForecastSoftUser softUser = new ForecastSoftUser();
softUser.Period = Convert.ToInt16(dataReader["period"]);
softUser.Platform = platformid;
softUser.SoftId = Convert.ToInt32(dataReader["softid"]);
int sDate = Convert.ToInt32(dataReader["StatDate"]);
softUser.StatDate = new DateTime(sDate / 10000, sDate / 100 % 100, sDate % 100, 0, 0, 0);
switch (softUser.Period)
{
case (int)PeriodOptions.Daily:
softUser.ForecaseDate = softUser.StatDate.AddDays(1);
break;
case (int)PeriodOptions.Weekly:
softUser.ForecaseDate = softUser.StatDate.AddDays(7);
break;
case (int)PeriodOptions.Monthly:
softUser.ForecaseDate = softUser.StatDate.AddMonths(1);
break;
case (int)PeriodOptions.Of2Weeks:
softUser.ForecaseDate = softUser.StatDate.AddMonths(14);
break;
case (int)PeriodOptions.Of3Days:
softUser.ForecaseDate = softUser.StatDate.AddMonths(3);
break;
}
softUser.NewUserCount = Convert.ToInt32(dataReader["newnum"]);
softUser.TotalUserCount = Convert.ToInt32(dataReader["totalnum"]);
users.Add(softUser);
}
}
return(users);
}
