/// <summary> /// 日志列表按照日期 /// </summary> /// <param name="dataLogName"></param> /// <param name="ServerIp"></param> /// <param name="begintime"></param> /// <param name="endtime"></param> /// <param name="cachetime"></param> /// <returns></returns> public List <Monitor_DataLogs> GetLogListByDateCache(string dataLogName, string ServerIp, DateTime begintime, DateTime endtime, net91com.Stat.Core.PeriodOptions period, CacheTimeOption cachetime) { if (useCache == true) { string cacheKey = BuildCacheKey("GetLogListByDateCache", dataLogName, ServerIp, begintime, endtime, period); if (CacheHelper.Contains(cacheKey) && cachetime != CacheTimeOption.None) { return(CacheHelper.Get <List <Monitor_DataLogs> >(cacheKey).ToList()); } List <Monitor_DataLogs> list = GetLogListByDate(dataLogName, ServerIp, begintime, endtime, period); if (list != null && cachetime != CacheTimeOption.None) { CacheHelper.Set <List <Monitor_DataLogs> >(cacheKey, list, cachetime, CacheExpirationOption.AbsoluteExpiration); } return(list); } else { return(GetLogListByDate(dataLogName, ServerIp, begintime, endtime, period)); } }
public void SetTime(net91com.Stat.Core.PeriodOptions period, ref DateTime begintime, ref DateTime endtime) { DateTime dt = endtime; while (true) { if (period == net91com.Stat.Core.PeriodOptions.Weekly && dt.DayOfWeek == DayOfWeek.Sunday) { break; } else if (period == net91com.Stat.Core.PeriodOptions.Monthly && dt.Day == 20) { break; } else if (period == net91com.Stat.Core.PeriodOptions.Daily) { break; } else { dt = dt.AddDays(-1); } } endtime = dt; if (period == net91com.Stat.Core.PeriodOptions.Monthly) { begintime = dt.AddMonths(-1); } if (period == net91com.Stat.Core.PeriodOptions.Weekly) { begintime = dt.AddDays(-6); } }
/// <summary> /// 获取 时间范围内的所有软件平台,statuser对象 /// </summary> /// <param name="begin"></param> /// <param name="end"></param> /// <param name="softsid"></param> /// <param name="period"></param> /// <returns></returns> public List <Sjqd_StatUsers> GetSimpleSoftUserList(DateTime begin, net91com.Stat.Core.PeriodOptions period) { List <Sjqd_StatUsers> lists = new List <Sjqd_StatUsers>(); string sqlstr = @"select SoftID,Platform,?periodid period,?begindate statdate,SUM(TotalUserCount) TotalUserCount, SUM(NewUserCount+NewUserCountFromCache-ifnull(NewUserCount_Shualiang,0)) NewUserCount,SUM(ActiveUserCount+ActiveUserCountFromCache) ActiveUserCount from U_StatUsers where StatDate=?begindate and Period=?periodid group by SoftID,Platform"; var parameters = new [] { new MySqlParameter("?periodid", (int)period), new MySqlParameter("?begindate", int.Parse(begin.ToString("yyyyMMdd"))), }; using (IDataReader dataReader = MySqlHelper.ExecuteReader(connString, sqlstr, parameters)) { while (dataReader.Read()) { lists.Add(SimpleSoftUserBind(dataReader)); } } return(lists); }
/// <summary> /// 获取参数 /// </summary> protected void GetParams() { //获取周期值 net91com.Stat.Core.PeriodOptions period = PeriodSelector1.SelectedPeriod; //获取开始时间和结束时间 DateTime beginTime = string.IsNullOrEmpty(Request["inputtimestart"]) ? (HeadControl1.IsFirstLoad ? DateTime.MinValue : HeadControl1.BeginTime) : Convert.ToDateTime(Request["inputtimestart"]); DateTime endTime = string.IsNullOrEmpty(Request["inputtimeend"]) ? (HeadControl1.IsFirstLoad ? DateTime.MinValue : HeadControl1.EndTime) : Convert.ToDateTime(Request["inputtimeend"]); //获取选择的软件及平台列表 List <int> selectedSoftIds = string.IsNullOrEmpty(Request["inputsoftselect"]) ? (HeadControl1.IsFirstLoad ? new List <int>() : HeadControl1.SoftID.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Select(p => int.Parse(p)).ToList()) : Request["inputsoftselect"].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Select(p => int.Parse(p)).ToList(); List <MobileOption> selectedPlatforms = string.IsNullOrEmpty(Request["inputplatformselect"]) ? (HeadControl1.IsFirstLoad ? new List <MobileOption>() : HeadControl1.PlatID.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Select(p => (MobileOption)int.Parse(p)).ToList()) : Request["inputplatformselect"].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Select(p => (MobileOption)int.Parse(p)).ToList(); //验证参数 CheckParams(selectedSoftIds, selectedPlatforms, period, beginTime, endTime, ReportType.UserUseNewActivity); //根据周期显示不同的列 SuperPeriod = (Period == net91com.Stat.Core.PeriodOptions.TimeOfDay || Period == net91com.Stat.Core.PeriodOptions.Hours); }
/// <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)); } }
/// <summary> /// 添加里程碑 /// </summary> /// <param name="date"></param> /// <param name="period"></param> /// <param name="remark"></param> public void AddMarker(DateTime date, net91com.Stat.Core.PeriodOptions period, string remark) { DateTime d = date; switch (period) { case net91com.Stat.Core.PeriodOptions.Weekly: for (; ; d = d.AddDays(1)) { if (d.DayOfWeek == DayOfWeek.Sunday) { break; } } break; case net91com.Stat.Core.PeriodOptions.Of2Weeks: for (; ; d = d.AddDays(1)) { if (d.Subtract(new DateTime(2009, 5, 10)).Days % 14 == 0) { break; } } break; case net91com.Stat.Core.PeriodOptions.Monthly: for (; ; d = d.AddDays(1)) { if (d.Day == 20) { break; } } break; case net91com.Stat.Core.PeriodOptions.NaturalMonth: for (; ; d = d.AddDays(1)) { if (d.AddDays(1).Day == 1) { break; } } break; default: break; } if (!markers.ContainsKey(d)) { markers.Add(d, remark); } else if (period != net91com.Stat.Core.PeriodOptions.Daily && period != net91com.Stat.Core.PeriodOptions.LatestOneMonth) { markers[d] = markers[d] + remark; } }
public void GetDownZjs() { string tablename = HttpContext.Current.Request["tablename"]; string[] paras = tablename.Split('_'); int softid = Convert.ToInt32(paras[0]); MobileOption platid = (MobileOption)Convert.ToInt32(paras[1]); int statDate = Convert.ToInt32(paras[2]); DateTime dt = new DateTime(statDate / 10000, statDate / 100 % 100, statDate % 100); net91com.Stat.Core.PeriodOptions period = (net91com.Stat.Core.PeriodOptions)Convert.ToInt32(paras[3]); CheckHasRight(softid, "Reports/NewUserByMac.aspx"); List <Sjqd_StatUsersByMAC> lists = new StatUsersByMACService(true).GetTop100MacUsersByCache( softid, platid, period, dt, CacheTimeOption.TenMinutes ); HttpResponse resp; resp = HttpContext.Current.Response; resp.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8"); string filename = string.Empty; filename = "装机量.xls"; AddHead(filename); ///文件标题+内容 string colHeaders = "", ls_item = ""; //列数 int cl = 2; colHeaders += "Mac" + "\t" + "新增量" + "\t\n"; resp.Write(colHeaders); //向HTTP输出流中写入取得的数据信息 //逐行处理数据 for (int j = 0; j < lists.Count; j++) { //当前行数据写入HTTP输出流,并且置空ls_item以便下行数据 for (int i = 0; i < cl; i++) { if (i == 0) { ls_item += "'" + lists[j].Mac + "\t"; } else if (i == 1) { ls_item += lists[j].NewUserCount + "\t\n"; } } resp.Write(ls_item); ls_item = ""; } resp.End(); }
public void GetCustomStatExcel() { CheckUrl("Reports/SoftVersionSjqd.aspx"); Dictionary <string, int> tableColumn = new Dictionary <string, int>(); int channelid = Convert.ToInt32(GetPara("Channelid")); int channeltype = Convert.ToInt32(GetPara("Channeltype")); string name = GetPara("channelname"); string plat = GetPara("plat"); net91com.Stat.Core.PeriodOptions period = (net91com.Stat.Core.PeriodOptions)Convert.ToInt32(GetPara("period")); DateTime begintime = Convert.ToDateTime(GetPara("begintime")); DateTime endtime = Convert.ToDateTime(GetPara("endtime")); var listAll = Sjqd_StatUsersByChannelsService.GetInstance().GetAllSonChannelCustomUserCache((ChannelTypeOptions)channeltype, plat, channelid, begintime, endtime, period, CacheTimeOption.TenMinutes); List <DateTime> times = listAll.Select(p => p.StatDate).Distinct().OrderByDescending(p => p).ToList(); var channels = listAll.Select(p => new ChannelCustomers { Name = p.ChannelName, ID = p.ChannelID }).Distinct(new channelComparer()).ToList(); for (int i = 0; i < channels.Count; i++) { tableColumn.Add(channels[i].Name, channels[i].ID); } DataTable dt = MakeChannelIncDataTable(tableColumn); for (int i = 0; i < times.Count; i++) { DataRow dr = dt.NewRow(); dr["日期"] = times[i].ToString("yyyy-MM-dd"); foreach (var item in tableColumn.Keys) { var temp = listAll.Find(p => p.ChannelID == tableColumn[item] && p.StatDate == times[i]); if (temp != null) { dr[item] = temp.NewNum; } } dt.Rows.Add(dr); } HttpResponse resp; resp = HttpContext.Current.Response; resp.ContentEncoding = System.Text.Encoding.UTF8; string filename = string.Empty; filename = name + ".xls"; AddHead(filename); string tablestr = TableHelper.GetTableStrForAllCustomers(dt, true); resp.Write(tablestr); resp.End(); }
///严格规范时间范围 protected void SetStandardTime(net91com.Stat.Core.PeriodOptions Period, DateTime endtime, DateTime begintime) { TimeSpan dtSpan = endtime - begintime; int days = dtSpan.Days; if (Period == net91com.Stat.Core.PeriodOptions.Hours) { if (days > 5) { begintime = endtime.AddDays(-5); } } }
public List <Sjqd_StatUsersByMAC> GetTop100MacUsers(int softId, MobileOption platform, net91com.Stat.Core.PeriodOptions period, DateTime statdate) { string sql = @"select top 100 softid,PLATFORM,statdate,period,mac ,newusercount from Sjqd_StatUsersByMAC where StatDate=@statdate and Period=@period and SoftID=@softid and Platform=@platform order by NewUserCount desc"; SqlParameter[] param = new SqlParameter[] { new SqlParameter() { ParameterName = "@period", SqlDbType = System.Data.SqlDbType.TinyInt, Size = 1, Value = (int)period }, new SqlParameter() { ParameterName = "@softid", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = softId }, new SqlParameter() { ParameterName = "@statdate", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = Convert.ToInt32(statdate.ToString("yyyyMMdd")) }, new SqlParameter() { ParameterName = "@platform", SqlDbType = System.Data.SqlDbType.TinyInt, Size = 1, Value = (int)platform } }; List <Sjqd_StatUsersByMAC> results = new List <Sjqd_StatUsersByMAC>(); using (IDataReader dr = SqlHelper.ExecuteReader(statdbConn, CommandType.Text, sql, param)) { while (dr.Read()) { results.Add(BindStatUsersByMac(dr)); } } return(results); }
/// <summary> /// 验证当前选择的软件及其它参数是否在权限范围内 /// </summary> /// <param name="selSoftIds">当前选择的软件列表,如果为空选择Cookie里指定的软件,否则选择有权限列表中第一个软件</param> /// <param name="selPlatforms">当前选择的平台列表,如果为空选择Cookie里指定的平台,否则选择有权限列表中第一个软件的第一个支持的平台</param> /// <param name="period"></param> /// <param name="beginTime"></param> /// <param name="endTime"></param> /// <param name="reportType"></param> protected void CheckParams(List <int> selSoftIds, List <MobileOption> selPlatforms, net91com.Stat.Core.PeriodOptions period, DateTime beginTime, DateTime endTime, ReportType reportType) { //如果用户一个产品权限都没有,则抛出无权限异常 if (AvailableSofts.Count == 0) { throw new NotRightException(); } List <int> selectedSoftIds = selSoftIds == null ? new List <int>() : selSoftIds; List <MobileOption> selectedPlatforms = selPlatforms == null ? new List <MobileOption>() : selPlatforms; //如果没有选择任何产品,使用Cookie指定的 if (selectedSoftIds.Count == 0) { selectedSoftIds = UtilityHelp.GetDefaultSoftsFromCookie(out selectedPlatforms); } List <Soft> selectedSofts = (from a in AvailableSofts join b in selectedSoftIds on a.ID equals b select a).ToList(); //如果客户端第一次请求或选择的软件都没有权限,使用第一个有权限的软件和第一个可以支持的平台 if (selectedSofts.Count == 0) { selectedSofts.Add(AvailableSofts[0]); } if (selectedPlatforms.Count == 0) { selectedPlatforms.Add(selectedSofts[0].Platforms[0]); } //设置默认的软件和平台到Cookie UtilityHelp.SetDefaultSoftsToCookie(selectedSofts, selectedPlatforms); //设置相关属性 SelectedSofts = selectedSofts; SelectedPlatforms = selectedPlatforms; Period = period; DateTime maxTime = UtilityService.GetInstance().GetMaxTimeCache(net91com.Stat.Core.PeriodOptions.Daily, reportType, CacheTimeOption.TenMinutes); EndTime = endTime == DateTime.MinValue || endTime > maxTime ? maxTime : endTime; BeginTime = beginTime == DateTime.MinValue ? EndTime.AddDays(-30) : (beginTime > EndTime ? EndTime : beginTime); if (Period == net91com.Stat.Core.PeriodOptions.Hours && EndTime.Subtract(BeginTime).Days > 10) { BeginTime = EndTime.AddDays(-10); } }
public List <LinkTagCount> GetTagCountCache( DateTime begin, DateTime end, int softid, int platform, net91com.Stat.Core.PeriodOptions period, int version, int tagid, string tagText, bool isCategory, CacheTimeOption cachetime) { string cacheKey = BuildCacheKey("GetTagCountCache", begin, end, softid, platform, period, version, tagid, isCategory); if (CacheHelper.Contains(cacheKey) && cachetime != CacheTimeOption.None) { return(CacheHelper.Get <List <LinkTagCount> >(cacheKey).ToList()); } List <LinkTagCount> list = LinkTagLog_DataAccess.Instance.GetTagCountList( begin, end, (int)period, softid, platform, version, tagid, tagText, isCategory); if (list != null && cachetime != CacheTimeOption.None) { CacheHelper.Set <List <LinkTagCount> >(cacheKey, list, cachetime, CacheExpirationOption.AbsoluteExpiration); } return(list); }
/// <summary> /// 专门为特定渠道商查询能够获取的最大时间 /// </summary> /// <param name="softid"></param> /// <param name="period"></param> /// <returns></returns> public DateTime GetMaxChannelUserTime(int softid, MobileOption plat, net91com.Stat.Core.PeriodOptions period) { ///默认是当前 DateTime maxTime = DateTime.Now; string cmdText = string.Format(@"select max(StatDate) from U_StatChannelUsers where softid={0} and period={1}", softid, (int)plat); using (IDataReader dataReader = MySqlHelper.ExecuteReader(connString, cmdText)) { while (dataReader.Read()) { if (dataReader[0] != null && dataReader[0] != DBNull.Value) { int time = Convert.ToInt32(dataReader[0]); maxTime = new DateTime(time / 10000, time / 100 % 100, time % 100, 0, 0, 0); } } } return(maxTime); }
/// <summary> /// 下载固件版本分布数据 /// </summary> private void DownloadGJBB() { net91com.Stat.Core.PeriodOptions Period = GetQueryString("zhouqi").ToEnum <net91com.Stat.Core.PeriodOptions>(net91com.Stat.Core.PeriodOptions.LatestOneWeek); int excelsoft = Convert.ToInt32(GetQueryString("soft")); int excelplatform = Convert.ToInt32(GetQueryString("platform")); CheckHasRight(excelsoft, "Reports/TransverseReports/SoftGJBBTransverse.aspx"); List <Sjqd_StatUsersByGjbb> list = StatUsersByGjbbService.GetInstance().GetSoftGJBBTransverse(Period, Convert.ToInt32(UtilityService.GetInstance().GetMaxTimeCache(Period, ReportType.StatTerminationDistribution, CacheTimeOption.TenMinutes).ToString("yyyyMMdd")), excelsoft, (MobileOption)excelplatform); int all = list.Sum(p => p.UseCount); ThisResponse.ContentEncoding = System.Text.Encoding.GetEncoding("GBK"); AddHead("固件版本分布.xls"); ThisResponse.Write("固件版本\t用户数\t百分比\t\n"); string temp = string.Empty; list.ForEach(gjbb => { ThisResponse.Write((string.IsNullOrEmpty(gjbb.Gjbb) ? "未知" : gjbb.Gjbb) + "\t" + gjbb.UseCount + "\t" + (Convert.ToDecimal(gjbb.UseCount) / all * 100).ToString("0.00") + "\t\n"); }); ThisResponse.End(); }
public DateTime GetMaxChannelUserTimeCache(int softid, MobileOption plat, net91com.Stat.Core.PeriodOptions period, CacheTimeOption cachetime) { if (useCache == true) { string cacheKey = BuildCacheKey("GetMaxChannelUserTimeCache", softid, plat, period); if (CacheHelper.Contains(cacheKey) && cachetime != CacheTimeOption.None) { return(new DateTime(CacheHelper.Get <DateTime>(cacheKey).Ticks)); } DateTime list = GetMaxChannelUserTime(softid, plat, period); if (list != null && cachetime != CacheTimeOption.None) { CacheHelper.Set <DateTime>(cacheKey, list, cachetime, CacheExpirationOption.AbsoluteExpiration); } return(new DateTime(list.Ticks)); } else { return(GetMaxChannelUserTime(softid, plat, period)); } }
/// <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))); }
public DateTime GetMaxTimeCache(net91com.Stat.Core.PeriodOptions period, ReportType Type, CacheTimeOption cachetime, string otherKeyString = "") { if (useCache == true) { string cacheKey = BuildCacheKey("GetMaxTimeCache", period, Type, otherKeyString); if (CacheHelper.Contains(cacheKey) && cachetime != CacheTimeOption.None) { return(new DateTime(CacheHelper.Get <DateTime>(cacheKey).Ticks)); } DateTime list = GetMaxTime(period, Type, otherKeyString); if (list != null && cachetime != CacheTimeOption.None) { CacheHelper.Set <DateTime>(cacheKey, list, cachetime, CacheExpirationOption.AbsoluteExpiration); } return(new DateTime(list.Ticks)); } else { return(GetMaxTime(period, Type, otherKeyString)); } }
/// <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)); } }
/// 获取能查询数据的最大时间 public DateTime GetMaxTimeForDownSpeed(net91com.Stat.Core.PeriodOptions period) { string sqlstr = "select value from etlstates where [key]=@key "; string key = ""; if (period == net91com.Stat.Core.PeriodOptions.TimeOfDay) { key = "CurStatDate_DownHourSpeed"; } else { key = "CurStatDate_DownDaySpeed"; } SqlParameter[] para = new SqlParameter[1] { SqlParamHelper.MakeInParam("@key", SqlDbType.VarChar, 100, key) }; DateTime dt = DateTime.MinValue; using (IDataReader dataReader = SqlHelper.ExecuteReader(connString, CommandType.Text, sqlstr, para)) { if (dataReader.Read()) { if (dataReader[0] != null && dataReader[0] != DBNull.Value) { dt = Convert.ToDateTime(dataReader[0]); } } } if (period == net91com.Stat.Core.PeriodOptions.TimeOfDay) { return(dt.AddHours(-1)); } else { return(dt.AddDays(-1)); } }
private void DownResolution() { net91com.Stat.Core.PeriodOptions Period = GetQueryString("zhouqi").ToEnum <net91com.Stat.Core.PeriodOptions>(net91com.Stat.Core.PeriodOptions.LatestOneWeek); int excelsoft = Convert.ToInt32(GetQueryString("soft")); int excelplatform = Convert.ToInt32(GetQueryString("platform")); CheckHasRight(excelsoft, "Reports/TransverseReports/ResolutionReport.aspx"); List <Resolution> list = TerminalService.GetInstance().GetResolutions(excelsoft, excelplatform, (int)Period, Convert.ToInt32(UtilityService.GetInstance().GetMaxTimeCache(Period, ReportType.StatTerminationDistribution, CacheTimeOption.TenMinutes).ToString("yyyyMMdd")) ); ThisResponse.ContentEncoding = System.Text.Encoding.GetEncoding("GBK"); AddHead("分辨率分布.xls"); ThisResponse.Write("分辨率\t用户数\t百分比\t\n"); string temp = string.Empty; int allcount = list.Sum(p => p.UseCount); list.ForEach(resol => { ThisResponse.Write((string.IsNullOrEmpty(resol.ResolutionStr) ? "未知分辨率" : resol.ResolutionStr) + "\t" + string.Format("{0:N0}", (resol.UseCount)) + "\t" + (Convert.ToDecimal(resol.UseCount) / allcount * 100).ToString("0.00") + "\t\n"); }); ThisResponse.End(); }
public List <Sjqd_StatUsers> GetSimpleSoftUserListCache(DateTime begin, net91com.Stat.Core.PeriodOptions period, CacheTimeOption cachetime) { if (useCache == true) { string cacheKey = BuildCacheKey("GetSimpleSoftUserListCache", begin, period); if (CacheHelper.Contains(cacheKey) && cachetime != CacheTimeOption.None) { return(CacheHelper.Get <List <Sjqd_StatUsers> >(cacheKey).ToList()); } List <Sjqd_StatUsers> list = GetSimpleSoftUserList(begin, period); if (list != null && cachetime != CacheTimeOption.None) { CacheHelper.Set <List <Sjqd_StatUsers> >(cacheKey, list, cachetime, CacheExpirationOption.AbsoluteExpiration); } return(list); } else { return(GetSimpleSoftUserList(begin, period)); } }
/// <summary> /// 获取日期范围列表 /// </summary> /// <param name="period"></param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <returns></returns> private List <DateTime> GetDateList(net91com.Stat.Core.PeriodOptions period, DateTime startDate, DateTime endDate) { List <DateTime> dates = new List <DateTime>(); for (DateTime dt = endDate; dt >= startDate;) { switch (period) { case net91com.Stat.Core.PeriodOptions.Daily: dates.Add(dt); break; case net91com.Stat.Core.PeriodOptions.Weekly: if (dt.DayOfWeek == DayOfWeek.Sunday) { dates.Add(dt); } break; case net91com.Stat.Core.PeriodOptions.NaturalMonth: if (dt.AddDays(1).Day == 1) { dates.Add(dt); } break; case net91com.Stat.Core.PeriodOptions.Monthly: if (dt.Day == 20) { dates.Add(dt); } break; } dt = dt.AddDays(-1); } return(dates); }
public List <Sjqd_StatUsersByMAC> GetTop100MacUsersByCache(int softId, MobileOption platform, net91com.Stat.Core.PeriodOptions period, DateTime statdate, CacheTimeOption cachetime) { if (useCache == true) { string cacheKey = BuildCacheKey("GetTop100MacUsersByCache", softId, platform, period, statdate); if (CacheHelper.Contains(cacheKey) && cachetime != CacheTimeOption.None) { return(CacheHelper.Get <List <Sjqd_StatUsersByMAC> >(cacheKey)); } List <Sjqd_StatUsersByMAC> list = GetTop100MacUsers(softId, platform, period, statdate); if (list != null && cachetime != CacheTimeOption.None) { CacheHelper.Set <List <Sjqd_StatUsersByMAC> >(cacheKey, list, cachetime, CacheExpirationOption.AbsoluteExpiration); } return(list); } else { return(GetTop100MacUsers(softId, platform, period, statdate)); } }
public static string BuildUserLostUsers(List <SoftUser> users, string tableName, int tableIndex, net91com.Stat.Core.PeriodOptions period, bool forDown) { StringBuilder sb = new StringBuilder(); if (forDown) { sb.Append("<table border=\"1\">"); } else { sb.AppendFormat("<table id=\"tab{0}\" class=\"tablesorter\" name=\"{1}\" {2} cellspacing=\"1\">", tableIndex, tableName, tableIndex != 0 ? "style=\"display:none\"" : ""); } //表头 sb.Append("<thead><tr style=\"text-align:center\">"); sb.AppendFormat("<th>{0}</th><th>活跃用户</th><th>流失用户(下一周期)</th><th>流失率</th></tr></thead><tbody>", period == net91com.Stat.Core.PeriodOptions.Monthly ? "活跃月份" : "活跃日期"); //表体 foreach (SoftUser item in users) { sb.AppendFormat("<tr style=\"text-align:right;\"><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td></tr>", period == net91com.Stat.Core.PeriodOptions.Monthly ? item.StatDate.ToString("yyyy-MM") : item.StatDate.ToString("yyyy-MM-dd"), item.UseNum, item.LostNum, item.LostPercent); } //表尾 sb.AppendFormat("<tr style=\"text-align:right;\"><td>平均</td><td>{0}</td><td>{1}</td><td>{2:0.00%}</td></tr>" , SetNum((int)users.Average(p => p.UseNum), true), SetNum((int)users.Average(p => p.LostNum), true), users.Average(p => (double)p.LostNum / p.UseNum)); sb.Append("</tbody></table>"); return(sb.ToString()); }
/// <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="period"></param> /// <param name="orignalDate"></param> /// <param name="statDate"></param> private static string GetRetainedUserCountString(List <net91com.Reports.Entities.DataBaseEntity.Sjqd_StatRetainedUsers> retainedUsers, TableTemplate.ColumnNode col, net91com.Stat.Core.PeriodOptions period) { int index = int.Parse(col.Name.Replace("RetainedUserCount", string.Empty).Replace("RetainedRate", string.Empty)); for (int t = 0; t < retainedUsers.Count; t++) { switch (period) { case net91com.Stat.Core.PeriodOptions.Daily: if (retainedUsers[t].StatDate.Subtract(retainedUsers[t].OriginalDate).Days != index) { continue; } break; case net91com.Stat.Core.PeriodOptions.Weekly: if (retainedUsers[t].StatDate.Subtract(retainedUsers[t].OriginalDate).Days / 7 != index) { continue; } break; default: //月 if (retainedUsers[t].StatDate.Subtract(retainedUsers[t].OriginalDate).Days / 28 != index) { continue; } break; } if (col.Name.StartsWith("RetainedUserCount")) { return(String.Format("{0:N0}", retainedUsers[t].RetainedUserCount)); } //否则返回留存率 return(retainedUsers[t].OriginalNewUserCount == 0 ? "100.00%" : String.Format("{0:0.00}%", retainedUsers[t].RetainedUserCount / (double)retainedUsers[t].OriginalNewUserCount * 100)); } return(string.Empty); }
/// <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="platform"></param> /// <param name="isInternalSoft">是否是内部软件</param> /// <param name="byChannel">按渠道查询</param> /// <param name="period">统计周期</param> /// <param name="reportType"></param> /// <param name="onlyOldUser"></param> /// <param name="users"></param> /// <param name="forDown">是否用于下载(EXCEL)</param> /// <param name="tableIndex">多个表格时,用于指明第几张表格</param> /// <param name="tableName">表格名称</param> /// <returns></returns> public static string BuildStatUsersTable(int softId, MobileOption platform, bool isInternalSoft, bool byChannel, net91com.Stat.Core.PeriodOptions period, string reportType, bool onlyOldUser, List <SoftUser> users, bool forDown, int tableIndex, string tableName) { #region 表头开始 StringBuilder htmlBuilder = new StringBuilder(); if (forDown) { htmlBuilder.Append("<table border=\"1\">"); } else { htmlBuilder.AppendFormat("<table id=\"tab{0}\" class=\"tablesorter\" name=\"{1}\" {2} cellspacing=\"1\">", tableIndex, tableName, tableIndex != 0 ? "style=\"display:none\"" : ""); } if (statUsersXmlDoc == null) { statUsersXmlDoc = new XmlDocument(); statUsersXmlDoc.Load(HttpContext.Current.Server.MapPath("~/DataTables/StatUsers.xml")); } TableTemplate.IsTrueHandler IsTrue = new TableTemplate.IsTrueHandler(IsTrue_StatUsers); NodeCondition_StatUsers nCodition = new NodeCondition_StatUsers { ByChannel = byChannel, SoftID = softId, Platform = platform, IsInternalSoft = isInternalSoft, Period = period, OnlyOldUser = onlyOldUser, ReportType = reportType }; TableTemplate.ColumnNode rootNode = new TableTemplate.ColumnNode { CorrespondXmlNode = statUsersXmlDoc.DocumentElement }; //生成表头,并返回数据绑定列 List <TableTemplate.ColumnNode> dataColumns; htmlBuilder.Append(TableTemplate.BuildTableHead(rootNode, IsTrue, nCodition, out dataColumns)); #endregion #region 表体开始 htmlBuilder.Append("<tbody>"); StringBuilder averageBuilder = new StringBuilder(); StringBuilder sumBuilder = new StringBuilder(); for (int i = 0; i < users.Count; i++) { bool red = period == net91com.Stat.Core.PeriodOptions.Daily && (users[i].StatDate.DayOfWeek == DayOfWeek.Sunday || users[i].StatDate.DayOfWeek == DayOfWeek.Saturday); htmlBuilder.Append("<tr style=\"text-align:right;\">"); foreach (TableTemplate.ColumnNode col in dataColumns) { htmlBuilder.Append(red ? "<td style=\"color:red;\">" : "<td>"); switch (col.Name) { case "StatDate": htmlBuilder.AppendFormat("{0:yyyy-MM-dd}", users[i].StatDate); if (i == 0) { averageBuilder.Append("<td>平均</td>"); sumBuilder.Append("<td>总计</td>"); } break; case "Hour": htmlBuilder.Append(users[i].Hour); if (i == 0) { averageBuilder.AppendFormat("<td>{0}</td>", averageBuilder.Length == 0 ? "平均" : "--"); sumBuilder.AppendFormat("<td>{0}</td>", sumBuilder.Length == 0 ? "总计" : "--"); } break; case "NewUserCount": htmlBuilder.AppendFormat("{0:N0}", users[i].NewNum); if (i == 0) { averageBuilder.AppendFormat("<td>{0:N0}</td>", (int)users.Average(a => a.NewNum)); sumBuilder.AppendFormat("<td>{0:N0}</td>", users.Sum(a => a.NewNum)); } break; case "NewUserCount_NotFromCache": htmlBuilder.AppendFormat("{0:N0}", users[i].FirstNewUserCount); if (i == 0) { averageBuilder.AppendFormat("<td>{0:N0}</td>", (int)users.Average(a => a.FirstNewUserCount)); sumBuilder.AppendFormat("<td>{0:N0}</td>", users.Sum(a => a.FirstNewUserCount)); } break; case "NewUserCount_Shanzhai": htmlBuilder.AppendFormat("{0:N0}", users[i].NewUserCount_Shanzhai); if (i == 0) { averageBuilder.AppendFormat("<td>{0:N0}</td>", (int)users.Average(a => a.NewUserCount_Shanzhai)); sumBuilder.AppendFormat("<td>{0:N0}</td>", users.Sum(a => a.NewUserCount_Shanzhai)); } break; case "NewUserCount_SecAct": htmlBuilder.AppendFormat("{0:N0}", users[i].NewUserCount_SecAct); if (i == 0) { averageBuilder.AppendFormat("<td>{0:N0}</td>", (int)users.Average(a => a.NewUserCount_SecAct)); sumBuilder.AppendFormat("<td>{0:N0}</td>", users.Sum(a => a.NewUserCount_SecAct)); } break; case "NewUserCount_SecAct2": htmlBuilder.AppendFormat("{0:N0}", users[i].NewUserCount_SecAct2); if (i == 0) { averageBuilder.AppendFormat("<td>{0:N0}</td>", (int)users.Average(a => a.NewUserCount_SecAct2)); sumBuilder.AppendFormat("<td>{0:N0}</td>", users.Sum(a => a.NewUserCount_SecAct2)); } break; case "NewUserCount_Broken": htmlBuilder.AppendFormat("{0:N0}", users[i].NewNum_Broken); if (i == 0) { averageBuilder.AppendFormat("<td>{0:N0}</td>", (int)users.Average(a => a.NewNum_Broken)); sumBuilder.AppendFormat("<td>{0:N0}</td>", users.Sum(a => a.NewNum_Broken)); } break; case "NewUserCount_NotBroken": htmlBuilder.AppendFormat("{0:N0}", users[i].NewNum_NotBroken); if (i == 0) { averageBuilder.AppendFormat("<td>{0:N0}</td>", (int)users.Average(a => a.NewNum_NotBroken)); sumBuilder.AppendFormat("<td>{0:N0}</td>", users.Sum(a => a.NewNum_NotBroken)); } break; case "NewUserCount_ZJS": htmlBuilder.AppendFormat("{0:N0}", users[i].NewNum_ZJS); if (i == 0) { averageBuilder.AppendFormat("<td>{0:N0}</td>", (int)users.Average(a => a.NewNum_ZJS)); sumBuilder.AppendFormat("<td>{0:N0}</td>", users.Sum(a => a.NewNum_ZJS)); } break; case "NewUserCount_ValuedUsers": htmlBuilder.AppendFormat("{0:N0}", Math.Max(users[i].FuncValueUsersForNew, users[i].DownValueUsersForNew)); if (i == 0) { averageBuilder.AppendFormat("<td>{0:N0}</td>", (int)users.Average(a => Math.Max(a.FuncValueUsersForNew, a.DownValueUsersForNew))); sumBuilder.AppendFormat("<td>{0:N0}</td>", users.Sum(a => Math.Max(a.FuncValueUsersForNew, a.DownValueUsersForNew))); } break; case "ActiveUserCount": htmlBuilder.AppendFormat("{0:N0}", onlyOldUser ? users[i].ActiveNum : users[i].ActiveNum + users[i].NewNum); if (i == 0) { averageBuilder.AppendFormat("<td>{0:N0}</td>", onlyOldUser ? (int)users.Average(a => a.ActiveNum) : (int)users.Average(a => a.ActiveNum + a.NewNum)); sumBuilder.Append("<td>--</td>"); } break; case "OldUserCount": htmlBuilder.AppendFormat("{0:N0}", users[i].ActiveNum); if (i == 0) { averageBuilder.AppendFormat("<td>{0:N0}</td>", (int)users.Average(a => a.ActiveNum)); sumBuilder.Append("<td>--</td>"); } break; case "OldPercent": htmlBuilder.Append((((double)users[i].ActiveNum) * 100 / (users[i].ActiveNum + users[i].NewNum)).ToString("0.00") + "%"); if (i == 0) { averageBuilder.Append("<td>--</td>"); sumBuilder.Append("<td>--</td>"); } break; case "ActiveUserCount_NotFromCache": htmlBuilder.AppendFormat("{0:N0}", onlyOldUser ? users[i].FirstActiveUserCount : users[i].FirstActiveUserCount + users[i].FirstNewUserCount); if (i == 0) { averageBuilder.AppendFormat("<td>{0:N0}</td>", onlyOldUser ? (int)users.Average(a => a.FirstActiveUserCount) : (int)users.Average(a => a.FirstActiveUserCount + a.FirstNewUserCount)); sumBuilder.Append("<td>--</td>"); } break; case "ActiveUserCount_Shanzhai": htmlBuilder.AppendFormat("{0:N0}", onlyOldUser ? users[i].ActiveUserCount_Shanzhai : users[i].ActiveUserCount_Shanzhai + users[i].NewUserCount_Shanzhai); if (i == 0) { averageBuilder.AppendFormat("<td>{0:N0}</td>", onlyOldUser ? (int)users.Average(a => a.ActiveUserCount_Shanzhai) : (int)users.Average(a => a.ActiveUserCount_Shanzhai + a.NewUserCount_Shanzhai)); sumBuilder.Append("<td>--</td>"); } break; case "ActiveUserCount_Broken": htmlBuilder.AppendFormat("{0:N0}", onlyOldUser ? users[i].ActiveNum_Broken : users[i].ActiveNum_Broken + users[i].NewNum_Broken); if (i == 0) { averageBuilder.AppendFormat("<td>{0:N0}</td>", onlyOldUser ? (int)users.Average(a => a.ActiveNum_Broken) : (int)users.Average(a => a.ActiveNum_Broken + a.NewNum_Broken)); sumBuilder.Append("<td>--</td>"); } break; case "ActiveUserCount_NotBroken": htmlBuilder.AppendFormat("{0:N0}", onlyOldUser ? users[i].ActiveNum_NotBroken : users[i].ActiveNum_NotBroken + users[i].NewNum_NotBroken); if (i == 0) { averageBuilder.AppendFormat("<td>{0:N0}</td>", onlyOldUser ? (int)users.Average(a => a.ActiveNum_NotBroken) : (int)users.Average(a => a.ActiveNum_NotBroken + a.NewNum_NotBroken)); sumBuilder.Append("<td>--</td>"); } break; case "ActiveUserCount_ZJS": htmlBuilder.AppendFormat("{0:N0}", onlyOldUser ? users[i].ActiveNum_ZJS : users[i].ActiveNum_ZJS + users[i].NewNum_ZJS); if (i == 0) { averageBuilder.AppendFormat("<td>{0:N0}</td>", onlyOldUser ? (int)users.Average(a => a.ActiveNum_ZJS) : (int)users.Average(a => a.ActiveNum_ZJS + a.NewNum_ZJS)); sumBuilder.Append("<td>--</td>"); } break; case "ActiveUserCount_ValuedUsers": htmlBuilder.AppendFormat("{0:N0}", onlyOldUser ? Math.Max(users[i].FuncValueUsersForAct, users[i].DownValueUsersForAct) : Math.Max(users[i].FuncValueUsersForAct + users[i].FuncValueUsersForNew, users[i].DownValueUsersForAct + users[i].DownValueUsersForNew)); if (i == 0) { averageBuilder.AppendFormat("<td>{0:N0}</td>", onlyOldUser ? (int)users.Average(a => Math.Max(a.FuncValueUsersForAct, a.DownValueUsersForAct)) : (int)users.Average(a => Math.Max(a.FuncValueUsersForAct + a.FuncValueUsersForNew, a.DownValueUsersForAct + a.DownValueUsersForNew))); sumBuilder.Append("<td>--</td>"); } break; case "TotalUserCount": htmlBuilder.AppendFormat("{0:N0}", users[i].TotalNum); if (i == 0) { averageBuilder.Append("<td>--</td>"); sumBuilder.AppendFormat("<td>{0:N0}</td>", users.Max(a => a.TotalNum)); } break; case "TotalUserCount_Shanzhai": htmlBuilder.AppendFormat("{0:N0}", users[i].TotalUserCount_Shanzhai); if (i == 0) { averageBuilder.Append("<td>--</td>"); sumBuilder.AppendFormat("<td>{0:N0}</td>", users.Max(a => a.TotalUserCount_Shanzhai)); } break; case "Growth": htmlBuilder.Append(users[i].Growth); if (i == 0) { averageBuilder.Append("<td>--</td>"); sumBuilder.Append("<td>--</td>"); } break; case "ActivePercent": htmlBuilder.Append(onlyOldUser ? users[i].ActivityPercent : users[i].UsePercent); if (i == 0) { averageBuilder.Append("<td>--</td>"); sumBuilder.Append("<td>--</td>"); } break; } htmlBuilder.Append("</td>"); } htmlBuilder.Append("</tr>"); } #endregion #region 表尾开始 htmlBuilder.Append("</tbody><tr style=\"text-align:right;\">"); htmlBuilder.Append(averageBuilder.ToString()); htmlBuilder.Append("</tr>"); if (period != net91com.Stat.Core.PeriodOptions.LatestOneMonth) { htmlBuilder.Append("<tr style=\"text-align:right;\">"); htmlBuilder.Append(sumBuilder.ToString()); htmlBuilder.Append("</tr>"); } htmlBuilder.Append("</tr></table>"); #endregion return(htmlBuilder.ToString()); }
/// <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="period"></param> /// <param name="statDate"></param> /// <param name="softId"></param> /// <param name="platform"></param> /// <returns></returns> public List <Sjqd_StatUsersByLan> GetSoftLanTransverse(net91com.Stat.Core.PeriodOptions period, int statDate, int softId, MobileOption platform) { string key = BuildCacheKey("GetSoftLanTransverse", period, statDate, softId, platform); List <Sjqd_StatUsersByLan> list = CacheHelper.Get <List <Sjqd_StatUsersByLan> >(key); if (list == null) { // string cmdText = @" // select E_Lan,sum(userscount) userCount from // ( // SELECT case when b.E_Lan='' or b.E_Lan is null then 0 else LanID end LanID,userscount,isnull(b.E_Lan,'') E_Lan // FROM // ( // SELECT LanID,SUM(NewUserCount+ActiveUserCount) userscount // FROM Sjqd_StatUsersByLan WITH(NOLOCK) // WHERE Period=@period AND StatDate = @StatDate AND SoftID=@SoftID AND [Platform]=@Platform // GROUP BY LanID // ) A // LEFT JOIN // Sjqd_Lan b WITH(NOLOCK) ON A.LanID=b.ID // ) A // group by E_Lan // ORDER BY sum(userscount) DESC // "; // 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} // }; string sql = string.Format(@" select coalesce(LangId,0) langid,SUM(NewUserCount+ActiveUserCount) userscount from U_StatUsersByLan where Period={0} and statdate={1} and softid={2} and platform={3} group by LangId Order by userscount desc", (int)period, statDate, softId, (int)platform); list = new List <Sjqd_StatUsersByLan>(); using (IDataReader read = MySqlHelper.ExecuteReader(MySql_StatDbConn, sql)) { while (read.Read()) { list.Add(new Sjqd_StatUsersByLan() { UseCount = Convert.ToInt32(read["userscount"]), LanID = Convert.ToInt32(read["langid"]) }); } } int count = 0; var listsjqd = Sjqd_LanService.GetSjqd_LanList("", "", 0, int.MaxValue, out count); var reslst = (from itemStatUsersByLan in list join itemlan in listsjqd on itemStatUsersByLan.LanID equals itemlan.ID into temp from tt in temp.DefaultIfEmpty() select new Sjqd_StatUsersByLan() { UseCount = itemStatUsersByLan.UseCount, Lan = tt == null ? "" : tt.E_Lan }).ToList(); var realreslst = (from item in reslst group item by item.Lan into g select new Sjqd_StatUsersByLan() { Lan = g.Key, UseCount = g.Sum(p => p.UseCount) }).ToList(); if (realreslst.Count > 0) { CacheHelper.Set <List <Sjqd_StatUsersByLan> >(key, realreslst, CacheTimeOption.TenMinutes); list = realreslst; } } return(list); }