/// <summary> /// 获取指定渠道的子渠道列表(KEY则为渠道类型加ID值) /// </summary> /// <param name="softId"></param> /// <param name="channelType"></param> /// <param name="channelId"></param> /// <param name="includeChannelIds"></param> /// <returns></returns> public Dictionary <string, Channel> GetSubChannelDict(int softId, ChannelTypeOptions channelType, int channelId, bool includeChannelIds) { Dictionary <string, Channel> channelDict = GetChannelDict(softId, includeChannelIds); Dictionary <string, Channel> subChannelDict = new Dictionary <string, Channel>(); foreach (Channel channel in channelDict.Values) { if (channel.ChannelType == channelType && channel.ID == channelId) { continue; } string parentKey = channel.ParentChannelType.ToString() + channel.ParentID.ToString(); Channel parentChannel; while (channelDict.ContainsKey(parentKey)) { parentChannel = channelDict[parentKey]; if (parentChannel.ChannelType == channelType && parentChannel.ID == channelId) { subChannelDict[channel.ChannelType.ToString() + channel.ID.ToString()] = channel; break; } parentKey = parentChannel.ParentChannelType.ToString() + parentChannel.ParentID.ToString(); } } return(subChannelDict); }
/// <summary> /// 获取所有子渠道商对应不分平台的数据 /// </summary> /// <param name="fathercustomid"></param> /// <param name="softid"></param> /// <param name="platform"></param> /// <param name="begin"></param> /// <param name="end"></param> /// <param name="cachetime"></param> /// <returns></returns> public List <SoftUser> GetAllSonChannelCustomUserCache(ChannelTypeOptions type, string plat, int cateOrCustomid, DateTime begintime, DateTime endtime, net91com.Stat.Core.PeriodOptions period, CacheTimeOption cachetime) { if (useCache == true) { string cacheKey = BuildCacheKey("GetAllSonChannelCustomUserCache", type, plat, cateOrCustomid, begintime, endtime, period, cachetime); if (CacheHelper.Contains(cacheKey) && cachetime != CacheTimeOption.None) { return(CacheHelper.Get <List <SoftUser> >(cacheKey).ToList()); } List <SoftUser> list = GetAllSonChannelCustomUser(type, plat, cateOrCustomid, begintime, endtime, period); if (list != null && cachetime != CacheTimeOption.None) { CacheHelper.Set <List <SoftUser> >(cacheKey, list, cachetime, CacheExpirationOption.AbsoluteExpiration); } return(list); } else { return(GetAllSonChannelCustomUser(type, plat, cateOrCustomid, begintime, endtime, period)); } }
public SelectChannelValue(int channelValue, ChannelTypeOptions type, string channelText, int platform) { this.ChannelValue = channelValue; this.ChannelType = type; this.ChannelText = channelText; this.Platform = platform; }
/// <summary> /// 获取指定软件指定渠道绑定的渠道ID列表 /// </summary> /// <param name="softId"></param> /// <param name="channelType"></param> /// <param name="channels"></param> /// <returns></returns> public static List <int> GetChannelIds(int softId, ChannelTypeOptions channelType, int[] channels) { string cmdText = string.Empty; bool filterChannels = channels != null && channels.Length > 0; string channelsString = filterChannels ? string.Join(",", channels.Select(a => a.ToString()).ToArray()) : string.Empty; if (channelType == 0 || (channelType == ChannelTypeOptions.ChannelID && filterChannels)) { cmdText = string.Format("select distinct ChannelID from Cfg_Channels where SoftID={0} and ChannelID is not null", softId); if (channelType == ChannelTypeOptions.ChannelID) { cmdText += " and ChannelID in (" + channelsString + ")"; } } else if ((channelType == ChannelTypeOptions.Category || channelType == ChannelTypeOptions.Customer) && filterChannels) { if (channelType == ChannelTypeOptions.Category) { cmdText = string.Format("select B.ID,B.PID from Cfg_ChannelCategories A inner join Cfg_ChannelCustomers B on B.CID=A.ID and A.SoftID={0} and B.PID=0 and A.ID in ({1})", softId, channelsString); } else if (channelType == ChannelTypeOptions.Customer) { cmdText = string.Format("select ID,PID from Cfg_ChannelCustomers where SoftID={0} and ID in ({1})", softId, channelsString); } string customerIds = string.Empty; using (IDataReader reader = MySqlHelper.ExecuteReader(DACommonHelper.ConnectionString, cmdText)) { while (reader.Read()) { customerIds += reader["ID"].ToString() + ","; } } if (customerIds == string.Empty) { return(new List <int>()); } customerIds = customerIds.TrimEnd(','); customerIds = GetSubCustomerIds(customerIds, true); cmdText = string.Format("select distinct ChannelID from Cfg_Channels where CCID in ({0}) and ChannelID is not null", customerIds); } else { return(new List <int>()); } List <int> rights = new List <int>(); using (IDataReader reader = MySqlHelper.ExecuteReader(DACommonHelper.ConnectionString, cmdText)) { while (reader.Read()) { rights.Add(Convert.ToInt32(reader["ChannelID"])); } } return(rights); }
public List <D_StatDownCountsBySoft_SUM> GetD_StatDownBySoft_SUMByAreaCache(int restype, int softid, int platform, DateTime begintime, DateTime endtime, int period, ChannelTypeOptions selectchanneltype, string channelname, int selectchannelvalue, int sourceid, List <string> e_versionid, int countryid, int province, URLoginService loginService) { string key = BuildCacheKey("GetD_StatDownBySoft_SUMByAreaCache", restype, softid, platform, begintime, endtime, period, selectchanneltype, channelname, selectchannelvalue, sourceid, string.Join(",", e_versionid.Select(p => p.ToString()).ToArray()), countryid, province, loginService == null ? "" : ((loginService.LoginUser.AccountType == Reports.UserRights.UserTypeOptions.Channel || loginService.LoginUser.AccountType == Reports.UserRights.UserTypeOptions.ChannelPartner) ? loginService.LoginUser.ID.ToString() : "") ); return(net91com.Core.Web.CacheHelper.Get <List <D_StatDownCountsBySoft_SUM> >(key , Core.CacheTimeOption.TenMinutes , () => new D_StatDownCount_DataAccess().GetD_StatDownBySoft_SUMByArea(restype, softid, platform, begintime, endtime, selectchanneltype, channelname, selectchannelvalue, sourceid, e_versionid, period, countryid, province, loginService))); }
/// <summary> /// 获取市排行数据 /// </summary> /// <param name="softId"></param> /// <param name="platform"></param> /// <param name="channelType"></param> /// <param name="channelId"></param> /// <param name="period"></param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <returns></returns> public List <Sjqd_StatUsers> GetRankOfCities(int softId, int platform, ChannelTypeOptions channelType, int channelId, int period, ref DateTime startDate, ref DateTime endDate) { GetDateOfRank(softId, period, ref startDate, ref endDate, "GetRankOfCities"); DateTime startDate2 = startDate, endDate2 = endDate; return(CacheHelper.Get <List <Sjqd_StatUsers> >( BuildCacheKey("GetRankOfCities", softId, platform, channelType, channelId, period, startDate2, endDate2) , CacheTimeOption.TenMinutes , () => suDA.GetRankOfCities(softId, platform, channelType, channelId, period, startDate2, endDate2))); }
/// <summary> /// 获取分小时用户数据 /// </summary> /// <param name="softId"></param> /// <param name="platform"></param> /// <param name="channelType"></param> /// <param name="channelId"></param> /// <param name="period"></param> /// <param name="beginDate"></param> /// <param name="endDate"></param> /// <returns></returns> public List <Sjqd_StatUsers> GetStatUsersByHour(int softId, int platform, ChannelTypeOptions channelType, int channelId, int period, DateTime beginDate, DateTime endDate) { List <int> channelIds = channelId > 0 ? new URLoginService().GetAvailableChannelIds(softId, channelType, new[] { channelId }) : null; return(CacheHelper.Get <List <Sjqd_StatUsers> >( BuildCacheKey("GetStatUsersByHour", softId, platform, channelType, channelId, period, beginDate, endDate) , CacheTimeOption.TenMinutes , () => suDA.GetStatUsersByHour(softId, platform, channelIds, period, beginDate, endDate))); }
/// <summary> /// 获取活跃用户留存率数据 /// </summary> /// <param name="softId"></param> /// <param name="platform"></param> /// <param name="channelId"></param> /// <param name="channelType"></param> /// <param name="fromDate"></param> /// <param name="toDate"></param> /// <returns></returns> public List <Sjqd_StatRetainedUsers> GetStatRetainedActiveUsers(int softId, int platform, int channelId, ChannelTypeOptions channelType, DateTime fromDate, DateTime toDate) { List <int> channelIds = channelId > 0 ? new URLoginService().GetAvailableChannelIds(softId, channelType, new[] { channelId }) : null; return(CacheHelper.Get <List <Sjqd_StatRetainedUsers> >( BuildCacheKey("GetStatRetainedActiveUsers", softId, platform, channelType, channelId, fromDate, toDate) , CacheTimeOption.TenMinutes , () => suDA.GetStatRetainedActiveUsers(softId, platform, channelIds, fromDate, toDate))); }
/// <summary> /// 获取用户资源权限 /// </summary> /// <param name="userId"></param> /// <param name="softId"></param> /// <param name="channelType"></param> /// <returns></returns> private static string GetUserChannelRightIds(int userId, int softId, ChannelTypeOptions channelType) { string cmdText = string.Format( "SELECT distinct ChannelID FROM R_UserChannelRights WHERE UserID={0} {1} and ChannelType={2}", userId, softId == 0 ? "" : "and SoftID=" + softId.ToString(), (int)channelType); StringBuilder result = new StringBuilder(); using (MySqlDataReader reader = MySqlHelper.ExecuteReader(DACommonHelper.ConnectionString, cmdText)) { while (reader.Read()) { result.Append(reader["ChannelID"].ToString() + ","); } } return(result.Length == 0 ? "-1" : result.ToString().TrimEnd(',')); }
/// <summary> /// 获取分渠道用户数据 /// </summary> /// <param name="softId"></param> /// <param name="platform"></param> /// <param name="channelType"></param> /// <param name="channelId"></param> /// <param name="period"></param> /// <param name="beginDate"></param> /// <param name="endDate"></param> /// <param name="mustLogin"></param> /// <param name="forPartner">是否提供给合作方</param> /// <returns></returns> public List <Sjqd_StatUsers> GetStatUsersByChannel(int softId, int platform, ChannelTypeOptions channelType, int channelId, int period, DateTime beginDate, DateTime endDate, bool mustLogin = true, bool forPartner = false) { List <int> channelIds = channelId > 0 ? new URLoginService().GetAvailableChannelIds(softId, channelType, new[] { channelId }, mustLogin) : null; return(CacheHelper.Get <List <Sjqd_StatUsers> >( BuildCacheKey("GetStatUsersByChannel", softId, platform, channelType, channelId, period, beginDate, endDate, mustLogin, forPartner) , CacheTimeOption.TenMinutes , () => suDA.GetStatUsersByChannel(softId, platform, channelIds, period, beginDate, endDate, forPartner))); }
/// <summary> /// 接口主入口 /// </summary> /// <param name="context"></param> public override void ProcessRequest(HttpContext context) { #region 获取公共参数 int[] softIds = string.IsNullOrEmpty(ThisRequest["SoftIds"]) ? new int[] { -1 } : ThisRequest["SoftIds"].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(a => Convert.ToInt32(a)).ToArray(); int[] platforms = string.IsNullOrEmpty(ThisRequest["Platforms"]) ? new int[] { -1 } : ThisRequest["Platforms"].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(a => Convert.ToInt32(a)).ToArray(); int period = string.IsNullOrEmpty(ThisRequest["Period"]) ? -1 : Convert.ToInt32(ThisRequest["Period"]); DateTime startDate = string.IsNullOrEmpty(ThisRequest["StartDate"]) ? DateTime.MinValue : Convert.ToDateTime(ThisRequest["StartDate"]); DateTime endDate = string.IsNullOrEmpty(ThisRequest["EndDate"]) ? DateTime.MinValue : Convert.ToDateTime(ThisRequest["EndDate"]); int[] chlIds = string.IsNullOrEmpty(ThisRequest["ChannelIds"]) ? new int[] { -1 } : ThisRequest["ChannelIds"].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(a => Convert.ToInt32(a)).ToArray(); ChannelTypeOptions[] chlTypes = string.IsNullOrEmpty(ThisRequest["ChannelTypes"]) ? new ChannelTypeOptions[] { ChannelTypeOptions.Category } : ThisRequest["ChannelTypes"].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(a => (ChannelTypeOptions)Convert.ToInt32(a)).ToArray(); if (chlIds.Length != chlTypes.Length) { chlIds = new int[] { -1 }; chlTypes = new ChannelTypeOptions[] { ChannelTypeOptions.Category }; } #endregion //使用GB2312输出 ThisResponse.ContentEncoding = Encoding.GetEncoding("GB2312"); switch (ThisRequest["Action"]) { case "GetRankOfSubChannels": GetRankOfSubChannels(softIds[0], platforms[0], period, startDate, endDate, chlIds[0]); break; case "GetRankOfVersions": GetRankOfVersions(softIds[0], platforms[0], period, startDate, endDate, chlIds[0], chlTypes[0]); break; case "GetRankOfAreas": GetRankOfAreas(softIds[0], platforms[0], period, startDate, endDate, chlIds[0], chlTypes[0]); break; case "GetStatUsersByVersionLine": GetStatUsersByVersionLine(softIds[0], platforms[0], period, startDate, endDate, chlIds[0], chlTypes[0]); break; case "GetStatUsersByAreaLine": GetStatUsersByAreaLine(softIds[0], platforms[0], period, startDate, endDate, chlIds[0], chlTypes[0]); break; } ThisResponse.Flush(); }
/// <summary> /// 获取对外的留存率数据 /// </summary> /// <param name="softId"></param> /// <param name="platform"></param> /// <param name="channelType"></param> /// <param name="channelId"></param> /// <param name="period"></param> /// <param name="beginDate"></param> /// <param name="endDate"></param> /// <param name="minTime"></param> /// <returns></returns> public List <Sjqd_StatRetainedUsers> GetChannelRetainedUsersForOutByCache(int softId, MobileOption platform, ChannelTypeOptions channelType, int channelId, int period, DateTime beginDate, DateTime endDate, DateTime minTime) { string key = BuildCacheKey("GetChannelRetainedUsersForOutByCache", softId, platform, channelType, channelId, period, beginDate, endDate, minTime); //传入日期需要特殊处理 //周 if (period == (int)PeriodOptions.Weekly) { while (!(beginDate.DayOfWeek == DayOfWeek.Sunday)) { beginDate = beginDate.AddDays(1); } } //月 if (period == (int)PeriodOptions.Monthly) { while (!(beginDate.Day == 20)) { beginDate = beginDate.AddDays(1); } } if (CacheHelper.Contains(key)) { return(CacheHelper.Get <List <Sjqd_StatRetainedUsers> >(key)); } List <int> channelIds = channelId > 0 ? new URLoginService().GetAvailableChannelIds(softId, channelType, new[] { channelId }) : new List <int>(); if (channelIds.Count == 0) { return(new List <Sjqd_StatRetainedUsers>()); } return(CacheHelper.Get <List <Sjqd_StatRetainedUsers> > (key, CacheTimeOption.TenMinutes, () => Sjqd_StatChannelRetainedUsers_DataAccess.Instance.GetChannelRetainUsersForOut(softId, platform, channelIds, period, beginDate, endDate, minTime))); }
/// <summary> /// 获取用户渠道ID权限 /// </summary> /// <param name="softId"></param> /// <param name="channelType"></param> /// <param name="channels"></param> /// <param name="mustLogin">有些地方不需要登录也可以请求</param> /// <returns></returns> public List <int> GetAvailableChannelIds(int softId, ChannelTypeOptions channelType, int[] channels, bool mustLogin = true) { if (mustLogin) { if (!AvailableSofts.Exists(a => a.ID == softId)) { return(new List <int>()); } if (LoginUser.AccountType == UserTypeOptions.Channel || LoginUser.AccountType == UserTypeOptions.ChannelPartner) { return(DAChannelsHelper.GetChannelIds(LoginUser.ID, softId, channelType, channels)); } } return(DAChannelsHelper.GetChannelIds(softId, channelType, channels)); }
/// <summary> /// 已选择的用户ID列表 /// </summary> /// <returns></returns> protected void GetChannelUserIds() { int softId = Convert.ToInt32(Request["softId"]); int channelId = Convert.ToInt32(Request["channelId"]); ChannelTypeOptions channelType = (ChannelTypeOptions)Convert.ToInt32(Request["channelType"]); List <int> userIds = new URChannelsService().GetUserIds(softId, new ChannelRight { ChannelID = channelId, ChannelType = channelType }); StringBuilder jsonBuilder = new StringBuilder("["); userIds.ForEach((a) => { jsonBuilder.AppendFormat("{0},", a); }); string result = jsonBuilder.ToString().TrimEnd(',') + "]"; Response.Clear(); Response.ContentType = "text/json"; Response.Write(result); Response.End(); }
/// <summary> /// 获取数据加上绑定数据 /// </summary> protected void BindData() { int channelId = 0; ChannelTypeOptions channelType = ChannelTypeOptions.Category; if (HeadControl1.Channel1.ChannelValues.Count > 0) { channelId = HeadControl1.Channel1.ChannelValues[0].ChannelValue; channelType = HeadControl1.Channel1.ChannelValues[0].ChannelType; } StatUsersService suService = new StatUsersService(); List <net91com.Reports.Entities.DataBaseEntity.Sjqd_StatRetainedUsers> users = suService.GetStatRetainedUsersByVersion(SoftID, PlatformID, HeadControl1.VersionID, channelId, channelType, (int)Period, BeginTime, EndTime); ExcelDownUrl = string.Format("/Services/ExcelDownloader.ashx?Action=GetStatRetainedUsersByVersion&SoftID={0}&Platform={1}&Period={2}&StartDate={3:yyyy-MM-dd}&EndDate={4:yyyy-MM-dd}&VersionIds={5}&VersionNames={6}&ChannelIds={7}&ChannelTypes={8}&ChannelTexts={9}&v={10}", SoftID, PlatformID, (int)Period, BeginTime, EndTime, HeadControl1.VersionID, HeadControl1.VersionID, HeadControl1.Channel1.SelectedValue, HeadControl1.Channel1.SelectedCate, HttpUtility.UrlEncode(HeadControl1.Channel1.SelectedText), DateTime.Now.Ticks); SeriesJsonStr = LineChartHelper.BuildStatRetainedUsersLine(users, Period, BeginTime, EndTime, out AxisJsonStr); TablesHtml = TableTemplateHelper.BuildStatRetainedUsersTable((int)Period, users, false, string.Empty); }
/// <summary> /// 获取数据加上绑定数据 /// </summary> protected void BindData() { int channelId = 0; ChannelTypeOptions channelType = ChannelTypeOptions.Category; if (HeadControl1.Channel1.ChannelValues.Count > 0) { channelId = HeadControl1.Channel1.ChannelValues[0].ChannelValue; channelType = HeadControl1.Channel1.ChannelValues[0].ChannelType; } StatUsersService suService = new StatUsersService(); List <net91com.Reports.Entities.DataBaseEntity.Sjqd_StatUsers> users = AreaType == 1 ? suService.GetRankOfCountries(softsid, platformsid, channelType, channelId, (int)Period, ref BeginTime, ref EndTime) : (AreaType == 2 ? suService.GetRankOfProvinces(softsid, platformsid, channelType, channelId, (int)Period, ref BeginTime, ref EndTime) : suService.GetRankOfCities(softsid, platformsid, channelType, channelId, (int)Period, ref BeginTime, ref EndTime)); if (reporttype.Value == "0") { users = users.OrderByDescending(a => a.NewUserCount).ToList(); } else { users = users.OrderByDescending(a => a.ActiveUserCount).ToList(); } HeadControl1.BeginTime = BeginTime; HeadControl1.EndTime = EndTime; reportTitle = string.Format("地区分布({0:yyyy-MM-dd}至{1:yyyy-MM-dd})", BeginTime, EndTime); ExcelDownUrl = string.Format("/Services/ExcelDownloader.ashx?Action=GetStatUsersByAreaTransverse&SoftID={0}&Platform={1}&Period={2}&StartDate={3:yyyy-MM-dd}&EndDate={4:yyyy-MM-dd}&ChannelIds={5}&ChannelTypes={6}&ChannelTexts={7}&v={8}&AreaType={9}", softsid, platformsid, (int)Period, BeginTime, EndTime, HeadControl1.Channel1.SelectedValue, HeadControl1.Channel1.SelectedCate, HttpUtility.UrlEncode(HeadControl1.Channel1.SelectedText), DateTime.Now.Ticks, AreaType); GetStatUsersByAreaLineUrl = string.Format("/Services/GetMore.ashx?Action=GetStatUsersByAreaLine&SoftIds={0}&Platforms={1}&Period={2}&StartDate={3:yyyy-MM-dd}&EndDate={4:yyyy-MM-dd}&ChannelIds={5}&ChannelTypes={6}&ChannelTexts={7}&v={8}", softsid, platformsid, (int)net91com.Stat.Core.PeriodOptions.Daily, BeginTime, EndTime, HeadControl1.Channel1.SelectedValue, HeadControl1.Channel1.SelectedCate, HttpUtility.UrlEncode(HeadControl1.Channel1.SelectedText), DateTime.Now.Ticks); if (users.Count == 0) { SeriesJsonStr = "[]"; reportTitle = "无数据"; } else { SeriesJsonStr = GetYlineJson(users); } tableStr = GetTableString(users); }
/// <summary> /// 添加用户渠道权限 /// </summary> protected void DeleteUserChannelRight() { Response.Clear(); Response.ContentType = "text/json"; try { int userId = Convert.ToInt32(Request["userId"]); int softId = Convert.ToInt32(Request["softId"]); int channelId = Convert.ToInt32(Request["channelId"]); ChannelTypeOptions channelType = (ChannelTypeOptions)Convert.ToInt32(Request["channelType"]); new URChannelsService().DeleteUserChannelRight(userId, softId, new ChannelRight { ChannelID = channelId, ChannelType = channelType }); Response.Write("{\"state\":1}"); } catch (Exception ex) { net91com.Core.Util.LogHelper.WriteException("", ex); Response.Write("{\"state\":0}"); } Response.End(); }
/// <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> /// <param name="cachetime"></param> /// <returns></returns> public List <SoftUser> GetSoftUserChanelListCache(DateTime begin, DateTime end, int softid, int platformid, net91com.Stat.Core.PeriodOptions period, ChannelTypeOptions selectchanneltype, int selectchannelvalue, string channleName, bool useModules, net91com.Reports.UserRights.URLoginService loginService, CacheTimeOption cachetime) { string cacheKey = BuildCacheKey("GetSoftUserChanelListCache", begin, end, softid, platformid, period, selectchanneltype, selectchannelvalue, useModules, loginService == null ? "" : ((loginService.LoginUser.AccountType == UserTypeOptions.Channel || loginService.LoginUser.AccountType == UserTypeOptions.ChannelPartner) ? loginService.LoginUser.ID.ToString() : "")); return(CacheHelper.Get <List <SoftUser> >(cacheKey, CacheTimeOption.TenMinutes, () => GetSoftUserChanelList(begin, end, softid, platformid, period, selectchanneltype, selectchannelvalue, channleName, useModules, loginService))); }
/// <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> /// <param name="cachetime"></param> /// <returns></returns> public List <Sjqd_StatChannelRetainedUsers> GetStatRetainedUsersCache(int softId, int platform, int channelId, net91com.Stat.Core.PeriodOptions period, DateTime fromDate, DateTime toDate, CacheTimeOption cachetime, ChannelTypeOptions channelType, URLoginService loginService) { if (useCache == true) { string cacheKey = BuildCacheKey("GetStatRetainedUsersCache", softId, platform, channelId, period, fromDate, toDate, channelType, loginService == null ? "" : ((loginService.LoginUser.AccountType == Reports.UserRights.UserTypeOptions.Channel || loginService.LoginUser.AccountType == Reports.UserRights.UserTypeOptions.ChannelPartner) ? loginService.LoginUser.ID.ToString() : "")); if (CacheHelper.Contains(cacheKey) && cachetime != CacheTimeOption.None) { return(CacheHelper.Get <List <Sjqd_StatChannelRetainedUsers> >(cacheKey).ToList()); } List <Sjqd_StatChannelRetainedUsers> list = GetStatRetainedUsers(softId, platform, channelId, period, fromDate, toDate, channelType, loginService); if (list != null && cachetime != CacheTimeOption.None) { CacheHelper.Set <List <Sjqd_StatChannelRetainedUsers> >(cacheKey, list, cachetime, CacheExpirationOption.AbsoluteExpiration); } return(list.ToList()); } else { return(GetStatRetainedUsers(softId, platform, channelId, period, fromDate, toDate, channelType, loginService)); } }
/// <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); }
/// <summary> /// 获取分地区曲线数据 /// </summary> /// <param name="softId"></param> /// <param name="platform"></param> /// <param name="period"></param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <param name="channelId"></param> /// <param name="channelType"></param> public void GetStatUsersByAreaLine(int softId, int platform, int period, DateTime startDate, DateTime endDate, int channelId, ChannelTypeOptions channelType) { //权限验证 CheckHasRight(softId, "Reports/StatUsersByAreaTransverse.aspx"); string[] areaIds = string.IsNullOrEmpty(ThisRequest["AreaIds"]) ? new string[0] : ThisRequest["AreaIds"].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToArray(); if (areaIds.Length > 0) { StatUsersService suService = new StatUsersService(); List <Sjqd_StatUsers> statUsersList = suService.GetStatUsersByArea(softId, platform, channelType, channelId, areaIds[0], period, startDate, endDate); LineChart chart = new LineChart(startDate, endDate); chart.Period = (net91com.Stat.Core.PeriodOptions)period; LineChartLine newUserLine = new LineChartLine { Name = "新增", Show = true, XIntervalDays = 0, Points = statUsersList.Select(a => new LineChartPoint { XValue = a.StatDate, YValue = a.NewUserCount, DataContext = a }).ToList() }; chart.Y.Add(newUserLine); LineChartLine activeUserLine = new LineChartLine { Name = "活跃", Show = true, XIntervalDays = 0, Points = statUsersList.Select(a => new LineChartPoint { XValue = a.StatDate, YValue = a.ActiveUserCount, DataContext = a }).ToList() }; chart.Y.Add(activeUserLine); string axisJsonStr = string.Format("{{{0},labels:{{align:'left',tickLength:80,tickPixelInterval:140,rotation:-45,x:-30,y:45,step:{1}}}}}" , chart.GetXJson() , chart.Step); string seriesJsonStr = chart.GetYJson( delegate(LineChartPoint point) { return(string.Format(",\"growth\":\"{0}\",\"Denominator\":{1},\"Other\":null", "", "0")); }); string result = "{ x:" + axisJsonStr + "," + "y:" + seriesJsonStr + "}"; HttpContext.Current.Response.Write(result); } }
/// <summary> /// 获取所有子渠道商对应不分平台的数据 /// </summary> /// <param name="type"></param> /// <param name="cateOrCustomid"></param> /// <param name="begintime"></param> /// <param name="endtime"></param> /// <param name="period"></param> /// <returns></returns> public List <SoftUser> GetAllSonChannelCustomUser(ChannelTypeOptions type, string plat, int cateOrCustomid, DateTime begintime, DateTime endtime, net91com.Stat.Core.PeriodOptions period) { using (MySqlConnection connection = new MySqlConnection(connString)) { connection.Open(); string cmdText = @"drop table if exists tmpList; drop table if exists tmpList2; CREATE TEMPORARY TABLE tmpList (ID INT, RootID INT, RootName VARCHAR(100),`Level` int); CREATE TEMPORARY TABLE tmpList2 (ID INT, RootID INT, RootName VARCHAR(100),`Level` int);"; if (type == ChannelTypeOptions.Category) { cmdText += string.Format(@"insert into tmpList(ID,RootID,RootName,`Level`) select ID,ID,`Name`,1 from Cfg_ChannelCustomers where CID={0} and PID=0;", cateOrCustomid); } else if (type == ChannelTypeOptions.Customer) { cmdText += string.Format(@"insert into tmpList(ID,RootID,RootName,`Level`) select ID,ID,`Name`,1 from Cfg_ChannelCustomers where PID={0};", cateOrCustomid); } else { return(new List <SoftUser>()); } cmdText += "insert into tmpList2 select * from tmpList;"; MySqlHelper.ExecuteNonQuery(connection, cmdText); int level = 1; while (true) { cmdText = string.Format(@"insert into tmpList(ID,RootID,RootName,`Level`) select A.ID,B.RootID,B.RootName,{0}+1 from Cfg_ChannelCustomers A inner join tmpList2 B on A.PID=B.ID and B.Level={0}; truncate table tmpList2; insert into tmpList2 select * from tmpList where `Level`={0}+1;", level); level++; if (MySqlHelper.ExecuteNonQuery(connection, cmdText) == 0) { break; } } cmdText = string.Format(@" select Y.StatDate,Y.Period,0 SoftID,0 platform,X.RootID ChannelID,X.RootName ChannelName ,SUM(NewUserCount) newnum,SUM(ActiveUserCount) activenum ,0 NewUserCount_SecAct,SUM(ifnull(NewUserCount_SecAct2,0)) NewUserCount_SecAct2 ,SUM(LostUserCount) lostnum,sum(TotalUserCount) totalnum from ( select A.ChannelID,B.RootID,B.RootName from Cfg_Channels A inner join tmpList B on A.CCID=B.ID and A.ChannelID is not null) X inner join U_StatChannelUsers Y on X.ChannelID=Y.ChannelID and Y.Period={0} and Y.StatDate between {1:yyyyMMdd} and {2:yyyyMMdd} {3} group by Y.StatDate,Y.Period,X.RootName,X.RootID order by X.RootName; drop table tmpList; drop table tmpList2;" , (int)period, begintime, endtime , plat == "0" ? "and Y.platform<252" : ("and Y.platform in (" + plat + ")")); List <SoftUser> users = new List <SoftUser>(); using (IDataReader reader = MySqlHelper.ExecuteReader(connection, cmdText)) { while (reader.Read()) { users.Add(UserBindSjQd(reader, 0, false)); } } return(users); } }
/// <summary> /// 获取国家平均排行数据 /// </summary> /// <param name="softId"></param> /// <param name="platform"></param> /// <param name="channelType"></param> /// <param name="channelId"></param> /// <param name="period"></param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <returns></returns> public List <Sjqd_StatUsers> GetSumRankOfCountries(int softId, int platform, ChannelTypeOptions channelType, int channelId, int period, DateTime startDate, DateTime endDate) { return(suDA.GetRankOfCountries(softId, platform, channelType, channelId, period, startDate, endDate)); }
/// <summary> /// 接口主入口 /// </summary> /// <param name="context"></param> public override void ProcessRequest(HttpContext context) { #region 获取公共参数 int softId = string.IsNullOrEmpty(ThisRequest["SoftID"]) ? -1 : Convert.ToInt32(ThisRequest["SoftID"]); int platform = string.IsNullOrEmpty(ThisRequest["Platform"]) ? -1 : Convert.ToInt32(ThisRequest["Platform"]); int period = string.IsNullOrEmpty(ThisRequest["Period"]) ? -1 : Convert.ToInt32(ThisRequest["Period"]); DateTime startDate = string.IsNullOrEmpty(ThisRequest["StartDate"]) ? DateTime.MinValue : Convert.ToDateTime(ThisRequest["StartDate"]); DateTime endDate = string.IsNullOrEmpty(ThisRequest["EndDate"]) ? DateTime.MinValue : Convert.ToDateTime(ThisRequest["EndDate"]); int[] chlIds = string.IsNullOrEmpty(ThisRequest["ChannelIds"]) ? new int[] { -1 } : ThisRequest["ChannelIds"].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(a => Convert.ToInt32(a)).ToArray(); ChannelTypeOptions[] chlTypes = string.IsNullOrEmpty(ThisRequest["ChannelTypes"]) ? new ChannelTypeOptions[] { ChannelTypeOptions.Category } : ThisRequest["ChannelTypes"].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(a => (ChannelTypeOptions)Convert.ToInt32(a)).ToArray(); string[] chlTexts = string.IsNullOrEmpty(ThisRequest["ChannelTexts"]) ? new string[] { string.Empty } : ThisRequest["ChannelTexts"].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToArray(); if (chlIds.Length != chlTypes.Length || chlIds.Length != chlTexts.Length) { chlIds = new int[] { -1 }; chlTypes = new ChannelTypeOptions[] { ChannelTypeOptions.Category }; chlTexts = new string[] { string.Empty }; } #endregion //使用GB2312输出 ThisResponse.ContentEncoding = Encoding.GetEncoding("GB2312"); switch (ThisRequest["Action"]) { case "GetStatRetainedUsers": GetStatRetainedUsers(softId, platform, period, startDate, endDate, chlIds, chlTypes, chlTexts); break; case "GetStatRetainedActiveUsers": GetStatRetainedActiveUsers(softId, platform, startDate, endDate, chlIds, chlTypes, chlTexts); break; case "GetStatUsersByArea": GetStatUsersByArea(softId, platform, period, startDate, endDate, chlIds, chlTypes, chlTexts); break; case "GetStatRetainedUsersByArea": GetStatRetainedUsersByArea(softId, platform, period, startDate, endDate, chlIds, chlTypes, chlTexts); break; case "GetStatRetainedActiveUsersByArea": GetStatRetainedActiveUsersByArea(softId, platform, startDate, endDate, chlIds, chlTypes, chlTexts); break; case "GetStatUsersByVersion": GetStatUsersByVersion(softId, platform, period, startDate, endDate, chlIds, chlTypes, chlTexts); break; case "GetStatUsersByCountryByVersionEn": GetStatUsersByCountryByVersionEn(softId, platform, startDate, endDate); break; case "GetStatRetainedUsersByVersion": GetStatRetainedUsersByVersion(softId, platform, period, startDate, endDate, chlIds, chlTypes, chlTexts); break; case "GetStatUsersByVersionTransverse": GetStatUsersByVersionTransverse(softId, platform, period, startDate, endDate, chlIds, chlTypes); break; case "GetStatUsersByAreaTransverse": GetStatUsersByAreaTransverse(softId, platform, period, startDate, endDate, chlIds, chlTypes); break; } ThisResponse.Flush(); }
/// <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); }
public List <D_StatDownCountsBySoft_SUM> GetD_StatDownByAreaDistribution_SUMByCache(int restype, int softid, int platform, DateTime begintime, DateTime endtime, ChannelTypeOptions selectchanneltype, string channelname, int selectchannelvalue, int period, int areatype, URLoginService loginService) { string key = BuildCacheKey("GetD_StatDownByAreaDistribution_SUMByCache", restype, softid, platform, begintime, endtime, period, selectchanneltype, channelname, selectchannelvalue, areatype, loginService == null ? "" : ((loginService.LoginUser.AccountType == Reports.UserRights.UserTypeOptions.Channel || loginService.LoginUser.AccountType == Reports.UserRights.UserTypeOptions.ChannelPartner) ? loginService.LoginUser.ID.ToString() : "")); return(net91com.Core.Web.CacheHelper.Get <List <D_StatDownCountsBySoft_SUM> >(key , Core.CacheTimeOption.TenMinutes , () => new D_StatDownCount_DataAccess().GetD_StatDownByAreaDistribution_SUM(restype, softid, platform, begintime, endtime, selectchanneltype, channelname, selectchannelvalue, period, areatype, loginService))); }
/// <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); }
/// <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="mobileOption"></param> /// <param name="periodOptions"></param> /// <param name="beginstatdate"></param> /// <param name="endstatdate"></param> /// <param name="channelTypeOptions"></param> /// <param name="channelIds"></param> /// <param name="provinceName"></param> /// <param name="type"></param> /// <returns></returns> public List <Sjqd_StatUsersByArea> GetSoftAreaTransverseWithChinaByCitysDayly(int softid, MobileOption mobileOption, PeriodOptions periodOptions, DateTime beginstatdate, DateTime endstatdate, ChannelTypeOptions channelTypeOptions, List <int> channelIds, string city, string province) { string channelids = string.Join(",", channelIds.Select(p => p.ToString()).ToArray()); string sql = string.Empty; string key = BuildCacheKey("GetSoftAreaTransverseWithChinaByCitysDayly", softid, mobileOption, periodOptions, beginstatdate, endstatdate, channelTypeOptions, channelids, city, province); List <Sjqd_StatUsersByArea> list = CacheHelper.Get <List <Sjqd_StatUsersByArea> >(key); if (list == null) { int[] channels = channelIds.Select(a => a).ToArray(); List <int> channelavailableIds = channels.Count() == 0? new List <int>(): new URLoginService().GetAvailableChannelIds(softid, channelTypeOptions, channels); if (channelIds.Count != 0 && channelavailableIds.Count == 0) { return(new List <Sjqd_StatUsersByArea>()); } string channelIdsString = string.Join(",", channelavailableIds.Select(a => a.ToString()).ToArray()); //不区分渠道的 if (channelIds.Count == 0) { sql = @"select A.StatDate,B.City AreaName,SUM(A.NewUserCount+A.ActiveUserCount) usercount from dbo.Sjqd_StatUsersByArea A inner join dbo.Sjqd_Areas B on A.AreaID=B.ID and B.E_Country='中国' and B.City=@city and B.Province=@province and Period=@period and SoftID=@softid and Platform=@platform and ChannelID=0 and StatDate between @begintime and @endtime group by B.City,A.StatDate"; } else //区分渠道 { sql = string.Format(@"select A.StatDate, B.City AreaName,SUM(A.NewUserCount+A.ActiveUserCount) usercount from dbo.Sjqd_StatUsersByArea A inner join dbo.Sjqd_Areas B on A.AreaID=B.ID and B.E_Country='中国' and B.City=@city and B.Province=@province and Period=@period and SoftID=@softid and Platform=@platform and ChannelID in ({0}) and StatDate between @begintime and @endtime group by B.City,A.StatDate", channelIdsString); } SqlParameter[] param = new SqlParameter[] { new SqlParameter() { ParameterName = "@begintime", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = beginstatdate.ToString("yyyyMMdd") }, new SqlParameter() { ParameterName = "@endtime", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = endstatdate.ToString("yyyyMMdd") }, 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)mobileOption }, new SqlParameter() { ParameterName = "@city", SqlDbType = System.Data.SqlDbType.VarChar, Size = 100, Value = city.Trim() }, new SqlParameter() { ParameterName = "@province", SqlDbType = System.Data.SqlDbType.VarChar, Size = 100, Value = province.Trim() }, new SqlParameter() { ParameterName = "@period", SqlDbType = System.Data.SqlDbType.TinyInt, Size = 1, Value = (int)periodOptions } }; 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> /// 获取指定软件指定渠道绑定的渠道ID列表(未登录) /// </summary> /// <param name="softId"></param> /// <param name="channelType"></param> /// <param name="channels"></param> /// <returns></returns> public List <int> GetChannelIds(int softId, ChannelTypeOptions channelType, int[] channels) { return(DAChannelsHelper.GetChannelIds(softId, channelType, channels)); }