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