/// <summary>
/// 获取对应所选渠道下的，所有用户信息
/// </summary>
/// <param name="begin"></param>
/// <param name="end"></param>
/// <param name="softid"></param>
/// <param name="platformid"></param>
/// <param name="period"></param>
/// <param name="selectchanneltype"></param>
/// <param name="selectchannelvalue"></param>
/// <returns></returns>
public List <SoftUser> GetSoftUserChanelList(DateTime begin, DateTime end, int softid, int platformid,
net91com.Stat.Core.PeriodOptions period,
ChannelTypeOptions selectchanneltype, int selectchannelvalue,
string channeltext, bool useModules, URLoginService loginService)
{
List <int> rangeChannelIds = loginService == null
? new URChannelsService().GetChannelIds(softid, selectchanneltype,
new int[] { selectchannelvalue })
: loginService.GetAvailableChannelIds(softid, selectchanneltype,
new int[] { selectchannelvalue });
if (rangeChannelIds.Count == 0)
{
return(new List <SoftUser>());
}
string channelIdsString = string.Join(",", rangeChannelIds.Select(a => a.ToString()).ToArray());
string sql;
if (!useModules)
{
sql =
string.Format(@"select * from ( select period,StatDate {5},{2} softid,sum(NewUserCount-ifnull(NewUserCount_Shualiang,0)) as newnum,sum(ActiveUserCount) as activenum
,0 lostnum
,sum(TotalUserCount) totalnum
,0 DownValueUsersForNew
,0 DownValueUsersForAct
,0 FuncValueUsersForNew
,0 FuncValueUsersForAct
,'{0}' ChannelID
,'{1}' ChannelName
,0 NewUserCount_SecAct
,sum(ifnull(NewUserCount_SecAct2,0)) NewUserCount_SecAct2
from U_StatChannelUsers as A
where A.softid=?softid and {3} A.period=?period and A.StatDate between ?begindate and ?enddate and ChannelID in ({6})
Group By A.period,A.StatDate {4})A order by StatDate desc",
selectchannelvalue,
channeltext,
softid,
platformid == 0 ? " A.platform<252 and " : " A.platform=?platform and ",
platformid == 0 ? "" : ",A.platform",
platformid == 0 ? ",0 platform" : ",platform",
channelIdsString);
}
else
{
sql =
string.Format(@"select * from( select period,StatDate {5},{2} softid,sum(case when Modulus=0 then NewUserCount-ifnull(NewUserCount_Shualiang,0) else (NewUserCount-ifnull(NewUserCount_Shualiang,0))*Modulus end) as newnum,
sum(case when Modulus=0 then ActiveUserCount else ActiveUserCount*Modulus end) as activenum
,0 as lostnum ,sum(TotalUserCount) as totalnum,
0 DownValueUsersForNew,
0 DownValueUsersForAct,
0 FuncValueUsersForNew,
0 FuncValueUsersForAct,
'{0}' as ChannelID,'{1}' as ChannelName
,0 as NewUserCount_SecAct
,sum(case when Modulus2=0 then ifnull(NewUserCount_SecAct2,0) else ifnull(NewUserCount_SecAct2,0)*Modulus2 end) as NewUserCount_SecAct2
from U_StatChannelUsers AS A
where A.softid=?softid and {3} A.period=?period and A.StatDate between ?begindate and ?enddate and ChannelID in ({6})
Group By A.period,A.StatDate {4})A order by StatDate desc", selectchannelvalue,
channeltext, softid,
platformid == 0 ? " A.platform<252 and " : " A.platform=?platform and ",
platformid == 0 ? "" : ",A.platform", platformid == 0 ? ",0 platform" : ",platform",
channelIdsString);
}
MySqlParameter[] parameters = new MySqlParameter[]
{
new MySqlParameter("?softid", softid),
new MySqlParameter("?platform", platformid),
new MySqlParameter("?period", (int)period),
new MySqlParameter("?begindate", begin.ToString("yyyyMMdd")),
new MySqlParameter("?enddate", end.ToString("yyyyMMdd")),
};
List <SoftUser> lists = new List <SoftUser>();
using (MySqlCommand cmd = new MySqlCommand(sql, new MySqlConnection(connString)))
{
cmd.Connection.Open();
cmd.CommandTimeout = 180;
cmd.Parameters.AddRange(parameters);
using (IDataReader dataReader = cmd.ExecuteReader())
{
while (dataReader.Read())
{
lists.Add(UserBindSjQd(dataReader, platformid, true));
}
}
}
return(lists);
}
public List <SoftUser> GetSoftUserList(DateTime begin, DateTime end, int softid, int platformid, PeriodOptions period, URLoginService loginService)
{
List <SoftUser> lists = new List <SoftUser>();
string sqlstr = "";
///无渠道商权限限制
if (loginService.LoginUser.AccountType != UserTypeOptions.Channel &&
loginService.LoginUser.AccountType != UserTypeOptions.ChannelPartner)
{
if (platformid != (int)MobileOption.None && platformid > 0)
{
sqlstr =
@" select period,StatDate,platform,softid ,
(NewUserCount+NewUserCountFromCache+NewUserCount_Shanzhai-ifnull(NewUserCount_Shualiang,0)) as newnum,
NewUserCountFromCache,NewUserCount_Broken,NewUserCount_NotBroken,
ActiveUserCountFromCache+ActiveUserCount+ActiveUserCount_Shanzhai activenum,ActiveUserCountFromCache,
ActiveUserCount_Broken,ActiveUserCount_NotBroken,LostUserCount lostnum ,
TotalUserCount+TotalUserCount_Shanzhai as totalnum
,DownValueUsersForNew,
DownValueUsersForAct,
FuncValueUsersForNew,
FuncValueUsersForAct,
NewUserCount-ifnull(NewUserCount_Shualiang,0) FirstNewUserCount,
ActiveUserCount FirstActiveUserCount,
NewUserCount_ZJS,
ActiveUserCount_ZJS,
NewUserCount_Shanzhai,
TotalUserCount_Shanzhai,
ActiveUserCount_Shanzhai,
IFNULL(NewUserCount_SecAct,0) NewUserCount_SecAct,
IFNULL(NewUserCount_SecAct2,0) NewUserCount_SecAct2
from U_StatUsers
where softid=?softid and platform=?platform and period=?periodid and StatDate between ?begindate and ?enddate
order by StatDate desc" ;
}
else//不区分平台
{
sqlstr =
@" select period,StatDate,0 as platform,softid ,
sum(NewUserCount+NewUserCountFromCache+NewUserCount_Shanzhai-ifnull(NewUserCount_Shualiang,0)) as newnum,
sum(NewUserCountFromCache) NewUserCountFromCache,sum(NewUserCount_Broken) NewUserCount_Broken,
sum(NewUserCount_NotBroken) NewUserCount_NotBroken,
sum(ActiveUserCountFromCache+ActiveUserCount+ActiveUserCount_Shanzhai) as activenum,
sum(ActiveUserCountFromCache) ActiveUserCountFromCache,
sum(ActiveUserCount_Broken) as ActiveUserCount_Broken,
sum(ActiveUserCount_NotBroken) as ActiveUserCount_NotBroken,
sum(LostUserCount) as lostnum ,
sum(TotalUserCount+TotalUserCount_Shanzhai) as totalnum
,sum(DownValueUsersForNew) DownValueUsersForNew,
sum(DownValueUsersForAct) DownValueUsersForAct,
sum(FuncValueUsersForNew) FuncValueUsersForNew,
sum(FuncValueUsersForAct) FuncValueUsersForAct,
sum(NewUserCount-ifnull(NewUserCount_Shualiang,0)) FirstNewUserCount,
sum(ActiveUserCount) FirstActiveUserCount,
sum(NewUserCount_ZJS) NewUserCount_ZJS,
sum(ActiveUserCount_ZJS) ActiveUserCount_ZJS,
sum(TotalUserCount_Shanzhai) TotalUserCount_Shanzhai,
sum(NewUserCount_Shanzhai) NewUserCount_Shanzhai,
sum(ActiveUserCount_Shanzhai) ActiveUserCount_Shanzhai,
sum(IFNULL(NewUserCount_SecAct,0)) NewUserCount_SecAct,
sum(IFNULL(NewUserCount_SecAct2,0)) NewUserCount_SecAct2
from U_StatUsers
where softid=?softid and period=?periodid and platform<252 and StatDate between ?begindate and ?enddate
Group By period,StatDate,softid order by StatDate desc" ;
}
}
else ///有渠道商权限限制
{
List <int> rangeChannelIds = loginService.GetAvailableChannelIds(softid);
if (rangeChannelIds.Count == 0)
{
return(new List <SoftUser>());
}
string channelIdsString = string.Join(",", rangeChannelIds.Select(a => a.ToString()).ToArray());
sqlstr =
string.Format(@" select period, StatDate,{1} softid ,sum(NewUserCount+NewUserCount_Shanzhai-ifnull(NewUserCount_Shualiang,0)) as newnum,
0 NewUserCountFromCache,0 NewUserCount_Broken,
0 NewUserCount_NotBroken,
sum(ActiveUserCount+ActiveUserCount_Shanzhai) as activenum,
0 ActiveUserCountFromCache,
0 as ActiveUserCount_Broken,
0 as ActiveUserCount_NotBroken,
sum(LostUserCount) as lostnum ,
sum(TotalUserCount+TotalUserCount_Shanzhai) as totalnum,
sum(DownValueUsersForNew) DownValueUsersForNew,
sum(DownValueUsersForAct) DownValueUsersForAct,
sum(FuncValueUsersForNew) FuncValueUsersForNew,
sum(FuncValueUsersForAct) FuncValueUsersForAct,
sum(NewUserCount-ifnull(NewUserCount_Shualiang,0)) FirstNewUserCount,
sum(ActiveUserCount) FirstActiveUserCount,
0 NewUserCount_ZJS,0 ActiveUserCount_ZJS,
sum(TotalUserCount_Shanzhai) TotalUserCount_Shanzhai,
sum(NewUserCount_Shanzhai) NewUserCount_Shanzhai,
sum(ActiveUserCount_Shanzhai) ActiveUserCount_Shanzhai,
sum(ifnull(NewUserCount_SecAct,0)) NewUserCount_SecAct,
sum(ifnull(NewUserCount_SecAct2,0)) NewUserCount_SecAct2
from Sjqd_StatChannelUsers with(nolock)
where SoftID=?softid and ChannelID in ({3})
and Period=?periodid and Platform<252 {0} and StatDate between ?begindate and ?enddate
group by Period,StatDate,SoftID {2}
order by StatDate desc",
(platformid != (int)MobileOption.None && platformid > 0)
? " and platform=?platform "
: "",
(platformid != (int)MobileOption.None && platformid > 0)
? " platform,"
: "0 as platform,",
(platformid != (int)MobileOption.None && platformid > 0) ? " ,platform" : "",
channelIdsString);
}
var parameters = new []
{
new MySqlParameter("?softid", softid),
new MySqlParameter("?platform", platformid),
new MySqlParameter("?periodid", (int)period),
new MySqlParameter("?begindate", int.Parse(begin.ToString("yyyyMMdd"))),
new MySqlParameter("?enddate", int.Parse(end.ToString("yyyyMMdd")))
};
using (IDataReader dataReader = MySqlHelper.ExecuteReader(connString, sqlstr, parameters))
{
while (dataReader.Read())
{
lists.Add(UserBind(dataReader, platformid, true, true, true));
}
}
return(lists);
}
/// <summary>
/// 共用的用户留存量
/// </summary>
/// <param name="softId"></param>
/// <param name="platform"></param>
/// <param name="channelId"></param>
/// <param name="period"></param>
/// <param name="fromDate"></param>
/// <param name="toDate"></param>
/// <returns></returns>
public List <Sjqd_StatChannelRetainedUsers> GetStatRetainedUsers(int softId, int platform, int channelId,
net91com.Stat.Core.PeriodOptions period,
DateTime fromDate, DateTime toDate,
ChannelTypeOptions channelType,
URLoginService loginService)
{
string cmdText;
if (channelId > 0 || loginService.LoginUser.AccountType == UserTypeOptions.Channel ||
loginService.LoginUser.AccountType == UserTypeOptions.ChannelPartner)
{
List <int> rangeChannelIds = null;
if (loginService == null)
{
rangeChannelIds = new URChannelsService().GetChannelIds(softId, channelType, new int[] { channelId });
}
else
{
rangeChannelIds = channelId > 0
? loginService.GetAvailableChannelIds(softId, channelType,
new int[] { channelId })
: loginService.GetAvailableChannelIds(softId);
}
if (rangeChannelIds.Count == 0)
{
return(new List <Sjqd_StatChannelRetainedUsers>());
}
string channelIdsString = string.Join(",", rangeChannelIds.Select(a => a.ToString()).ToArray());
//因为有些质量低的渠道不存在留存用户，造成之前的方法在聚合时原始新增量会少
cmdText = string.Format(
@"select 0 ChannelID,B.StatDate,B.OriginalDate,{0} Period,{1} SoftID,{2} Platform,A.NewUserCount OriginalNewUserCount,B.RetainedUserCount
from (select StatDate,SUM(NewUserCount) NewUserCount from U_StatChannelUsers where SoftID={1} and Platform={2} and Period={0} and StatDate between {3} and {4} and ChannelID IN ({5}) group by StatDate) A inner join
(select StatDate,OriginalDate,SUM(RetainedUserCount) RetainedUserCount from U_StatRetainedUsers where SoftID={1} and Platform={2} and Period={0} and OriginalDate between {3} and {4} and ChannelID IN ({5}) GROUP BY StatDate,OriginalDate) B
on A.StatDate=B.OriginalDate
order by B.OriginalDate desc,B.StatDate desc", (int)period, softId, platform, fromDate.ToString("yyyyMMdd"), toDate.ToString("yyyyMMdd"), channelIdsString);
}
else
{
if (platform != 0)
{
cmdText = string.Format(
"select *,0 ChannelID from U_StatRetainedUsers where SoftID={1} and Platform={2} and Period={0} and OriginalDate between {3} and {4} and ChannelID=-1 order by OriginalDate desc ,StatDate desc"
, (int)period, softId, platform, fromDate.ToString("yyyyMMdd"), toDate.ToString("yyyyMMdd"));
}
else //不区分平台
{
cmdText = string.Format(
@"select * ,0 ChannelID, 0 Platform from (select StatDate,OriginalDate,Period,SoftID,sum(OriginalNewUserCount) OriginalNewUserCount,sum(RetainedUserCount) RetainedUserCount
from U_StatRetainedUsers
where SoftID={1} and Period={0} and OriginalDate between {2} and {3} and ChannelID=-1
group by StatDate,OriginalDate,Period,SoftID) as temp order by OriginalDate desc ,StatDate desc"
, (int)period, softId, fromDate.ToString("yyyyMMdd"), toDate.ToString("yyyyMMdd"));
}
}
if (period == net91com.Stat.Core.PeriodOptions.Daily)
{
if ((toDate - fromDate).Days > 31)
{
fromDate = toDate.AddDays(-31);
}
}
List <Sjqd_StatChannelRetainedUsers> retainedUsers = new List <Sjqd_StatChannelRetainedUsers>();
using (IDataReader reader = MySqlHelper.ExecuteReader(statdbConn, cmdText))
{
while (reader.Read())
{
retainedUsers.Add(RetainedUserBind(reader));
}
}
return(retainedUsers);
}
/// <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);
}