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