/// <summary> /// 每一天的量(中国范围) /// </summary> /// <param name="softId"></param> /// <param name="platform"></param> /// <param name="period"></param> /// <param name="statDate"></param> /// <param name="selectchanneltype"></param> /// <param name="channelids"></param> /// <returns></returns> public List <Sjqd_StatUsersByArea> GetSoftAreaDaylyWithChinaByChannels(string areaname, int softId, MobileOption platform, int begindate, int enddate, ChannelTypeOptions selectchanneltype, string channelids) { string sql = string.Empty; string key = BuildCacheKey("GetSoftAreaDaylyWithChinaByChannels", areaname, begindate, enddate, softId, platform, selectchanneltype, channelids); List <Sjqd_StatUsersByArea> list = CacheHelper.Get <List <Sjqd_StatUsersByArea> >(key); if (list == null) { if (channelids != "") { int[] channels = channelids.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(a => Convert.ToInt32(a)).ToArray(); List <int> channelIds = new URLoginService().GetAvailableChannelIds(softId, selectchanneltype, channels); if (channelIds.Count == 0) { return(new List <Sjqd_StatUsersByArea>()); } string channelIdsString = string.Join(",", channelIds.Select(a => a.ToString()).ToArray()); sql = string.Format(@" SELECT B.Province AreaName,A.StatDate,sum(userscount) usercount FROM ( select AreaID , NewUserCount+ActiveUserCount userscount,StatDate from Sjqd_StatUsersByArea with(nolock) where Period=@period and StatDate between @begintime and @endtime and SoftID=@SoftID AND [Platform]=@Platform and ChannelID in ({0}) )AS A inner join Sjqd_Areas as B WITH(NOLOCK) ON A.AreaID=B.ID and B.Province='{1}' and B.E_Country='中国' group by B.Province,A.StatDate order by A.StatDate asc", channelIdsString, areaname); } else { sql = string.Format(@" SELECT B.Province AreaName,sum(userscount) usercount,StatDate FROM ( select AreaID , NewUserCount+ActiveUserCount userscount,StatDate from Sjqd_StatUsersByArea with(nolock) where Period=@period and StatDate between @begintime and @endtime and SoftID=@SoftID AND [Platform]=@Platform and ChannelID=0 )AS A inner join Sjqd_Areas as B WITH(NOLOCK) ON A.AreaID=B.ID and B.Province='{0}' and B.E_Country='中国' group by B.Province,A.StatDate order by A.StatDate asc", areaname); } SqlParameter[] param = new SqlParameter[] { new SqlParameter() { ParameterName = "@period", SqlDbType = System.Data.SqlDbType.TinyInt, Size = 1, Value = 1 }, new SqlParameter() { ParameterName = "@begintime", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = begindate }, new SqlParameter() { ParameterName = "@endtime", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = enddate }, new SqlParameter() { ParameterName = "@SoftID", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = softId }, new SqlParameter() { ParameterName = "@Platform", SqlDbType = System.Data.SqlDbType.TinyInt, Size = 1, Value = (int)platform } }; list = new List <Sjqd_StatUsersByArea>(); using (IDataReader read = SqlHelper.ExecuteReader(statdbConn, CommandType.Text, sql, param)) { while (read.Read()) { Sjqd_StatUsersByArea area = new Sjqd_StatUsersByArea(); if (read["usercount"] != null && read["usercount"] != DBNull.Value) { area.UseCount = Convert.ToInt32(read["usercount"]); } if (read["AreaName"] != null && read["AreaName"] != DBNull.Value) { area.AreaName = read["AreaName"].ToString(); } if (read["StatDate"] != null && read["StatDate"] != DBNull.Value) { int date = Convert.ToInt32(read["StatDate"]); area.StatDate = new DateTime(date / 10000, date % 10000 / 100, date % 100); } list.Add(area); } } if (list.Count > 0) { CacheHelper.Set <List <Sjqd_StatUsersByArea> >(key, list, CacheTimeOption.TenMinutes); } } return(list); }
/// <summary> /// 根据渠道商集合查询渠道商用户量(世界的) /// </summary> /// <param name="softId"></param> /// <param name="platform"></param> /// <param name="period"></param> /// <param name="statDate"></param> /// <param name="selectchanneltype"></param> /// <param name="channelids"></param> /// <returns></returns> public List <Sjqd_StatUsersByArea> GetSoftAreaTransverseWithWorldByChannels(int softId, MobileOption platform, PeriodOptions period, int statDate, ChannelTypeOptions selectchanneltype, string channelids) { string sql = string.Empty; string key = BuildCacheKey("GetSoftAreaTransverseWithWorldByChannels", period, statDate, softId, platform, selectchanneltype, channelids); List <Sjqd_StatUsersByArea> list = CacheHelper.Get <List <Sjqd_StatUsersByArea> >(key); if (list == null) { int[] channels = channelids.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(a => Convert.ToInt32(a)).ToArray(); List <int> channelIds = new URLoginService().GetAvailableChannelIds(softId, selectchanneltype, channels); if (channelIds.Count == 0) { return(new List <Sjqd_StatUsersByArea>()); } string channelIdsString = string.Join(",", channelIds.Select(a => a.ToString()).ToArray()); sql = string.Format(@" SELECT B.E_Country AreaName,sum(userscount) usercount FROM ( select AreaID , NewUserCount+ActiveUserCount userscount from Sjqd_StatUsersByArea with(nolock) where Period=@period and StatDate=@StatDate and SoftID=@SoftID AND [Platform]=@Platform and ChannelID in ({0}) )AS A inner join Sjqd_Areas as B WITH(NOLOCK) ON A.AreaID=B.ID group by B.E_Country order by sum(userscount) desc", channelIdsString); SqlParameter[] param = new SqlParameter[] { new SqlParameter() { ParameterName = "@period", SqlDbType = System.Data.SqlDbType.TinyInt, Size = 1, Value = (int)period }, new SqlParameter() { ParameterName = "@StatDate", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = statDate }, new SqlParameter() { ParameterName = "@SoftID", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = softId }, new SqlParameter() { ParameterName = "@Platform", SqlDbType = System.Data.SqlDbType.TinyInt, Size = 1, Value = (int)platform } }; list = new List <Sjqd_StatUsersByArea>(); using (IDataReader read = SqlHelper.ExecuteReader(statdbConn, CommandType.Text, sql, param)) { while (read.Read()) { list.Add(new Sjqd_StatUsersByArea() { UseCount = Convert.ToInt32(read["usercount"]), AreaName = read["AreaName"].ToString(), }); } } if (list.Count > 0) { CacheHelper.Set <List <Sjqd_StatUsersByArea> >(key, list, CacheTimeOption.TenMinutes); } } return(list); }