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));
}
}
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));
}
}
/// <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());
}
public SetOptionVolumeMsg(MobileOption mobileOption)
{
this.mobileOption = mobileOption;
}
/// <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);
}
public List <Sjqd_StatUsersByGjbb> GetSoftGJBBTransverse(net91com.Stat.Core.PeriodOptions period, int statDate,
int softId, MobileOption platform)
{
string key = BuildCacheKey("GetSoftGJBBTransverse", period, statDate, softId, platform);
List <Sjqd_StatUsersByGjbb> list = CacheHelper.Get <List <Sjqd_StatUsersByGjbb> >(key);
if (list == null)
{
// string cmdText = @"select E_GJBB,sum(userscount) userCount from
// (
// SELECT case when b.E_GJBB='' or b.E_GJBB is null then 0 else GjbbID end GjbbID,userscount,isnull(b.E_GJBB,'') E_GJBB
// FROM
// (
// SELECT GjbbID,SUM(NewUserCount+ActiveUserCount) userscount
// FROM Sjqd_StatUsersByGjbb WITH(NOLOCK)
// WHERE Period=@period AND StatDate = @StatDate AND SoftID=@SoftID AND [Platform]=@Platform
// GROUP BY GjbbID
// ) A
// LEFT JOIN
// Sjqd_GJBB b WITH(NOLOCK) ON A.GjbbID=b.ID
// ) A
// group by E_GJBB
// 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 GjbbID,SUM(NewUserCount+ActiveUserCount) userscount
FROM U_StatUsersByGjbb
WHERE Period={0} AND StatDate ={1} AND SoftID={2} AND Platform={3}
GROUP BY GjbbID
Order by userscount desc", (int)period, statDate, softId, (int)platform);
list = new List <Sjqd_StatUsersByGjbb>();
using (IDataReader read = MySqlHelper.ExecuteReader((MySql_StatDbConn), sql))
{
while (read.Read())
{
list.Add(new Sjqd_StatUsersByGjbb()
{
UseCount = Convert.ToInt32(read["userscount"]),
GjbbID = Convert.ToInt32(read["GjbbID"])
});
}
}
int count = 0;
var gjbblst = Sjqd_GJBBService.GetSjqd_GJBBList((int)platform, "", "", 0, int.MaxValue, out count);
var reslst = (from sjqdStatUsersByGjbb in list
join sjqdGjbb in gjbblst on sjqdStatUsersByGjbb.GjbbID equals sjqdGjbb.ID
into os
from tt in os.DefaultIfEmpty()
select new Sjqd_StatUsersByGjbb()
{
UseCount = sjqdStatUsersByGjbb.UseCount,
Gjbb = tt == null ? "" : tt.E_GJBB
}).ToList();
var realreslst = (from item in reslst
group item by item.Gjbb
into g
select new Sjqd_StatUsersByGjbb()
{
Gjbb = g.Key,
UseCount = g.Sum(p => p.UseCount)
}).OrderByDescending(p => p.UseCount).ToList();
if (realreslst.Count > 0)
{
CacheHelper.Set <List <Sjqd_StatUsersByGjbb> >(key, realreslst, CacheTimeOption.TenMinutes);
list = realreslst;
}
}
return(list);
}
/// <summary>
/// 渠道分城市每天
/// </summary>
/// <param name="softid"></param>
/// <param name="mobileOption"></param>
/// <param name="periodOptions"></param>
/// <param name="beginstatdate"></param>
/// <param name="endstatdate"></param>
/// <param name="channelTypeOptions"></param>
/// <param name="channelIds"></param>
/// <param name="provinceName"></param>
/// <param name="type"></param>
/// <returns></returns>
public List <Sjqd_StatUsersByArea> GetSoftAreaTransverseWithChinaByCitysDayly(int softid, MobileOption mobileOption,
PeriodOptions periodOptions, DateTime beginstatdate, DateTime endstatdate,
ChannelTypeOptions channelTypeOptions,
List <int> channelIds, string city, string province)
{
string channelids = string.Join(",", channelIds.Select(p => p.ToString()).ToArray());
string sql = string.Empty;
string key = BuildCacheKey("GetSoftAreaTransverseWithChinaByCitysDayly", softid, mobileOption,
periodOptions, beginstatdate,
endstatdate, channelTypeOptions, channelids, city, province);
List <Sjqd_StatUsersByArea> list = CacheHelper.Get <List <Sjqd_StatUsersByArea> >(key);
if (list == null)
{
int[] channels = channelIds.Select(a => a).ToArray();
List <int> channelavailableIds = channels.Count() == 0? new List <int>(): new URLoginService().GetAvailableChannelIds(softid, channelTypeOptions, channels);
if (channelIds.Count != 0 && channelavailableIds.Count == 0)
{
return(new List <Sjqd_StatUsersByArea>());
}
string channelIdsString = string.Join(",", channelavailableIds.Select(a => a.ToString()).ToArray());
//不区分渠道的
if (channelIds.Count == 0)
{
sql = @"select A.StatDate,B.City AreaName,SUM(A.NewUserCount+A.ActiveUserCount) usercount from dbo.Sjqd_StatUsersByArea A
inner join dbo.Sjqd_Areas B
on A.AreaID=B.ID and B.E_Country='中国'
and B.City=@city and B.Province=@province and Period=@period and SoftID=@softid and Platform=@platform and ChannelID=0
and StatDate between @begintime and @endtime
group by B.City,A.StatDate";
}
else //区分渠道
{
sql =
string.Format(@"select A.StatDate, B.City AreaName,SUM(A.NewUserCount+A.ActiveUserCount) usercount from dbo.Sjqd_StatUsersByArea A
inner join dbo.Sjqd_Areas B
on A.AreaID=B.ID and B.E_Country='中国'
and B.City=@city and B.Province=@province and Period=@period and SoftID=@softid and Platform=@platform
and ChannelID in ({0})
and StatDate between @begintime and @endtime
group by B.City,A.StatDate", channelIdsString);
}
SqlParameter[] param = new SqlParameter[] {
new SqlParameter()
{
ParameterName = "@begintime", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = beginstatdate.ToString("yyyyMMdd")
},
new SqlParameter()
{
ParameterName = "@endtime", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = endstatdate.ToString("yyyyMMdd")
},
new SqlParameter()
{
ParameterName = "@softid", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = softid
},
new SqlParameter()
{
ParameterName = "@platform", SqlDbType = System.Data.SqlDbType.TinyInt, Size = 1, Value = (int)mobileOption
},
new SqlParameter()
{
ParameterName = "@city", SqlDbType = System.Data.SqlDbType.VarChar, Size = 100, Value = city.Trim()
},
new SqlParameter()
{
ParameterName = "@province", SqlDbType = System.Data.SqlDbType.VarChar, Size = 100, Value = province.Trim()
},
new SqlParameter()
{
ParameterName = "@period", SqlDbType = System.Data.SqlDbType.TinyInt, Size = 1, Value = (int)periodOptions
}
};
list = new List <Sjqd_StatUsersByArea>();
using (IDataReader read = SqlHelper.ExecuteReader(statdbConn, CommandType.Text, sql, param))
{
while (read.Read())
{
Sjqd_StatUsersByArea area = new Sjqd_StatUsersByArea();
if (read["usercount"] != null && read["usercount"] != DBNull.Value)
{
area.UseCount = Convert.ToInt32(read["usercount"]);
}
if (read["AreaName"] != null && read["AreaName"] != DBNull.Value)
{
area.AreaName = read["AreaName"].ToString();
}
if (read["StatDate"] != null && read["StatDate"] != DBNull.Value)
{
int date = Convert.ToInt32(read["StatDate"]);
area.StatDate = new DateTime(date / 10000, date % 10000 / 100, date % 100);
}
list.Add(area);
}
}
if (list.Count > 0)
{
CacheHelper.Set <List <Sjqd_StatUsersByArea> >(key, list, CacheTimeOption.TenMinutes);
}
}
return(list);
}
/// <summary>
/// 中国地区查找
/// </summary>
/// <param name="period">周期</param>
/// <param name="statDate">统计日期</param>
/// <param name="softId">软件ID</param>
/// <param name="platform">平台</param>
/// <returns></returns>
public List <Sjqd_StatUsersByArea> GetSoftAreaTransverseWithChina(PeriodOptions period, int statDate, int softId, MobileOption platform)
{
string key = BuildCacheKey("GetSoftAreaTransverseWithChina", period, statDate, softId, platform);
List <Sjqd_StatUsersByArea> list = CacheHelper.Get <List <Sjqd_StatUsersByArea> >(key);
if (list == null)
{
string cmdText = @" SELECT B.Province AreaName,sum(userscount) usercount FROM
(
select AreaID , NewUserCount+ActiveUserCount userscount
from Sjqd_StatUsersByArea with(nolock)
where Period=@period and StatDate=@StatDate and SoftID=@SoftID AND [Platform]=@Platform and ChannelID=0
)AS A inner join Sjqd_Areas as B WITH(NOLOCK)
ON A.AreaID=B.ID and B.E_Country='中国'
group by B.Province 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
}
};
list = new List <Sjqd_StatUsersByArea>();
int allcount = 0;
using (IDataReader read = SqlHelper.ExecuteReader(statdbConn, CommandType.Text, cmdText, param))
{
while (read.Read())
{
allcount += Convert.ToInt32(read["usercount"]);
list.Add(new Sjqd_StatUsersByArea()
{
UseCount = Convert.ToInt32(read["usercount"]),
AreaName = read["AreaName"].ToString(),
});
}
}
if (list.Count > 0)
{
CacheHelper.Set <List <Sjqd_StatUsersByArea> >(key, list, CacheTimeOption.TenMinutes);
}
}
return(list);
}
/// <summary>
/// 每一天的量(中国范围)
/// </summary>
/// <param name="softId"></param>
/// <param name="platform"></param>
/// <param name="period"></param>
/// <param name="statDate"></param>
/// <param name="selectchanneltype"></param>
/// <param name="channelids"></param>
/// <returns></returns>
public List <Sjqd_StatUsersByArea> GetSoftAreaDaylyWithChinaByChannels(string areaname, int softId, MobileOption platform, int begindate, int enddate, ChannelTypeOptions selectchanneltype, string channelids)
{
string sql = string.Empty;
string key = BuildCacheKey("GetSoftAreaDaylyWithChinaByChannels", areaname, begindate, enddate, softId, platform, selectchanneltype, channelids);
List <Sjqd_StatUsersByArea> list = CacheHelper.Get <List <Sjqd_StatUsersByArea> >(key);
if (list == null)
{
if (channelids != "")
{
int[] channels = channelids.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(a => Convert.ToInt32(a)).ToArray();
List <int> channelIds = new URLoginService().GetAvailableChannelIds(softId, selectchanneltype, channels);
if (channelIds.Count == 0)
{
return(new List <Sjqd_StatUsersByArea>());
}
string channelIdsString = string.Join(",", channelIds.Select(a => a.ToString()).ToArray());
sql = string.Format(@" SELECT B.Province AreaName,A.StatDate,sum(userscount) usercount FROM
(
select AreaID , NewUserCount+ActiveUserCount userscount,StatDate
from Sjqd_StatUsersByArea with(nolock)
where Period=@period and StatDate between @begintime and @endtime and SoftID=@SoftID AND [Platform]=@Platform and ChannelID in ({0})
)AS A inner join Sjqd_Areas as B WITH(NOLOCK)
ON A.AreaID=B.ID and B.Province='{1}' and B.E_Country='中国'
group by B.Province,A.StatDate order by A.StatDate asc", channelIdsString, areaname);
}
else
{
sql = string.Format(@" SELECT B.Province AreaName,sum(userscount) usercount,StatDate FROM
(
select AreaID , NewUserCount+ActiveUserCount userscount,StatDate
from Sjqd_StatUsersByArea with(nolock)
where Period=@period and StatDate between @begintime and @endtime and SoftID=@SoftID AND [Platform]=@Platform and ChannelID=0
)AS A inner join Sjqd_Areas as B WITH(NOLOCK)
ON A.AreaID=B.ID and B.Province='{0}' and B.E_Country='中国'
group by B.Province,A.StatDate order by A.StatDate asc", areaname);
}
SqlParameter[] param = new SqlParameter[] {
new SqlParameter()
{
ParameterName = "@period", SqlDbType = System.Data.SqlDbType.TinyInt, Size = 1, Value = 1
},
new SqlParameter()
{
ParameterName = "@begintime", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = begindate
},
new SqlParameter()
{
ParameterName = "@endtime", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = enddate
},
new SqlParameter()
{
ParameterName = "@SoftID", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = softId
},
new SqlParameter()
{
ParameterName = "@Platform", SqlDbType = System.Data.SqlDbType.TinyInt, Size = 1, Value = (int)platform
}
};
list = new List <Sjqd_StatUsersByArea>();
using (IDataReader read = SqlHelper.ExecuteReader(statdbConn, CommandType.Text, sql, param))
{
while (read.Read())
{
Sjqd_StatUsersByArea area = new Sjqd_StatUsersByArea();
if (read["usercount"] != null && read["usercount"] != DBNull.Value)
{
area.UseCount = Convert.ToInt32(read["usercount"]);
}
if (read["AreaName"] != null && read["AreaName"] != DBNull.Value)
{
area.AreaName = read["AreaName"].ToString();
}
if (read["StatDate"] != null && read["StatDate"] != DBNull.Value)
{
int date = Convert.ToInt32(read["StatDate"]);
area.StatDate = new DateTime(date / 10000, date % 10000 / 100, date % 100);
}
list.Add(area);
}
}
if (list.Count > 0)
{
CacheHelper.Set <List <Sjqd_StatUsersByArea> >(key, list, CacheTimeOption.TenMinutes);
}
}
return(list);
}
/// <summary>
/// 根据渠道商集合查询渠道商用户量（世界的）
/// </summary>
/// <param name="softId"></param>
/// <param name="platform"></param>
/// <param name="period"></param>
/// <param name="statDate"></param>
/// <param name="selectchanneltype"></param>
/// <param name="channelids"></param>
/// <returns></returns>
public List <Sjqd_StatUsersByArea> GetSoftAreaTransverseWithWorldByChannels(int softId, MobileOption platform, PeriodOptions period, int statDate, ChannelTypeOptions selectchanneltype, string channelids)
{
string sql = string.Empty;
string key = BuildCacheKey("GetSoftAreaTransverseWithWorldByChannels", period, statDate, softId, platform, selectchanneltype, channelids);
List <Sjqd_StatUsersByArea> list = CacheHelper.Get <List <Sjqd_StatUsersByArea> >(key);
if (list == null)
{
int[] channels = channelids.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(a => Convert.ToInt32(a)).ToArray();
List <int> channelIds = new URLoginService().GetAvailableChannelIds(softId, selectchanneltype, channels);
if (channelIds.Count == 0)
{
return(new List <Sjqd_StatUsersByArea>());
}
string channelIdsString = string.Join(",", channelIds.Select(a => a.ToString()).ToArray());
sql = string.Format(@" SELECT B.E_Country AreaName,sum(userscount) usercount FROM
(
select AreaID , NewUserCount+ActiveUserCount userscount
from Sjqd_StatUsersByArea with(nolock)
where Period=@period and StatDate=@StatDate and SoftID=@SoftID AND [Platform]=@Platform and ChannelID in ({0})
)AS A inner join Sjqd_Areas as B WITH(NOLOCK)
ON A.AreaID=B.ID
group by B.E_Country order by sum(userscount) desc", channelIdsString);
SqlParameter[] param = new SqlParameter[] {
new SqlParameter()
{
ParameterName = "@period", SqlDbType = System.Data.SqlDbType.TinyInt, Size = 1, Value = (int)period
},
new SqlParameter()
{
ParameterName = "@StatDate", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = statDate
},
new SqlParameter()
{
ParameterName = "@SoftID", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = softId
},
new SqlParameter()
{
ParameterName = "@Platform", SqlDbType = System.Data.SqlDbType.TinyInt, Size = 1, Value = (int)platform
}
};
list = new List <Sjqd_StatUsersByArea>();
using (IDataReader read = SqlHelper.ExecuteReader(statdbConn, CommandType.Text, sql, param))
{
while (read.Read())
{
list.Add(new Sjqd_StatUsersByArea()
{
UseCount = Convert.ToInt32(read["usercount"]),
AreaName = read["AreaName"].ToString(),
});
}
}
if (list.Count > 0)
{
CacheHelper.Set <List <Sjqd_StatUsersByArea> >(key, list, CacheTimeOption.TenMinutes);
}
}
return(list);
}
/// <summary>
/// 获取对外的留存率数据
/// </summary>
/// <param name="softId"></param>
/// <param name="platform"></param>
/// <param name="channelType"></param>
/// <param name="channelId"></param>
/// <param name="period"></param>
/// <param name="beginDate"></param>
/// <param name="endDate"></param>
/// <param name="minTime"></param>
/// <returns></returns>
public List <Sjqd_StatRetainedUsers> GetChannelRetainedUsersForOutByCache(int softId, MobileOption platform,
ChannelTypeOptions channelType,
int channelId, int period,
DateTime beginDate, DateTime endDate,
DateTime minTime)
{
string key = BuildCacheKey("GetChannelRetainedUsersForOutByCache", softId, platform, channelType, channelId,
period, beginDate, endDate, minTime);
//传入日期需要特殊处理
//周
if (period == (int)PeriodOptions.Weekly)
{
while (!(beginDate.DayOfWeek == DayOfWeek.Sunday))
{
beginDate = beginDate.AddDays(1);
}
}
//月
if (period == (int)PeriodOptions.Monthly)
{
while (!(beginDate.Day == 20))
{
beginDate = beginDate.AddDays(1);
}
}
if (CacheHelper.Contains(key))
{
return(CacheHelper.Get <List <Sjqd_StatRetainedUsers> >(key));
}
List <int> channelIds = channelId > 0
? new URLoginService().GetAvailableChannelIds(softId, channelType,
new[] { channelId })
: new List <int>();
if (channelIds.Count == 0)
{
return(new List <Sjqd_StatRetainedUsers>());
}
return(CacheHelper.Get <List <Sjqd_StatRetainedUsers> >
(key, CacheTimeOption.TenMinutes,
() =>
Sjqd_StatChannelRetainedUsers_DataAccess.Instance.GetChannelRetainUsersForOut(softId, platform,
channelIds, period,
beginDate, endDate,
minTime)));
}
/// <summary>
/// 开放对外的留存用户数据
/// </summary>
/// <param name="softId"></param>
/// <param name="platform"></param>
/// <param name="channelIds"></param>
/// <param name="period"></param>
/// <param name="beginDate"></param>
/// <param name="endDate"></param>
/// <param name="minTime">最小可查看时间</param>
/// <returns></returns>
public List <Sjqd_StatRetainedUsers> GetChannelRetainUsersForOut(int softId, MobileOption platform,
List <int> channelIds, int period,
DateTime beginDate, DateTime endDate,
DateTime minTime)
{
if (platform == 0)
{
return(GetChannelRetainUsersForOut(softId, channelIds, period, beginDate, endDate, minTime));
}
if (channelIds.Count == 0)
{
return(new List <Sjqd_StatRetainedUsers>());
}
string ids = string.Join(",", channelIds.Select(p => p.ToString()).ToArray());
string sql = string.Format(@"declare @statendtime datetime =@begintime;
declare @statbegindate datetime;
declare @begindateint int;
declare @enddateint int;
create table #retaintable(originaldate int,OriginalNewUserCount int)
while(@statendtime<=@endtime)
begin
set @enddateint=CAST( convert(varchar(8),@statendtime,112) as int);
if(@period=3)
begin
set @statbegindate=dateadd(day,-6,@statendtime);
set @begindateint=CAST( convert(varchar(8),@statbegindate,112) as int);
insert into #retaintable
select @enddateint originaldate, SUM( (NewUserCount-isnull(NewUserCount_Shualiang,0))*(case when Modulus=0 then 1 else Modulus end))
from dbo.Sjqd_StatChannelUsers
where softid=@softid and platform=@platform and Period=1
and StatDate between @begindateint and @enddateint
and ChannelID in({0}) and StatDate>=@mindateint
set @statendtime=dateadd(day,7,@statendtime);
end
else if(@period=5)
begin
set @statbegindate=dateadd(day,1,dateadd(MONTH,-1,@statendtime));
set @begindateint=CAST( convert(varchar(8),@statbegindate,112) as int);
insert into #retaintable
select @enddateint originaldate, SUM((NewUserCount-isnull(NewUserCount_Shualiang,0))*(case when Modulus=0 then 1 else Modulus end))
from dbo.Sjqd_StatChannelUsers
where softid=@softid and platform=@platform and Period=1
and StatDate between @begindateint and @enddateint
and ChannelID in({0}) and StatDate>=@mindateint
set @statendtime=dateadd(MONTH,1,@statendtime);
end
else
begin--period=1
insert into #retaintable
select @enddateint originaldate, SUM((NewUserCount-isnull(NewUserCount_Shualiang,0))*(case when Modulus=0 then 1 else Modulus end))
from dbo.Sjqd_StatChannelUsers
where softid=@softid and platform=@platform and Period=1
and StatDate= @enddateint
and ChannelID in({0}) and StatDate>=@mindateint
set @statendtime=dateadd(day,1,@statendtime);
end
print(@statendtime);
print(@begindateint);
print(@enddateint);
end
set @begindateint=CAST( convert(varchar(8),@begintime,112) as int);
set @enddateint=CAST( convert(varchar(8),@endtime,112) as int);
select B.StatDate,B.OriginalDate,B.Period,B.SoftID,B.Platform,A.OriginalNewUserCount,
A.OriginalNewUserCount*1.0/B.OriginalNewUserCount*B.RetainedUserCount RetainedUserCount
from #retaintable A
inner join
(
select
StatDate,OriginalDate,@period Period,@softid SoftID,@platform Platform,
SUM(OriginalNewUserCount) originalnewusercount,
SUM(RetainedUserCount) RetainedUserCount from dbo.U_StatRetainedUsers with(nolock)
where OriginalDate between @begindateint and @enddateint
and softid=@softid and platform=@platform and Period=@period and ChannelID in({0})
group by OriginalDate,StatDate
) B
on A.originaldate=B.OriginalDate
where A.OriginalNewUserCount!=0
order by B.OriginalDate desc,B.StatDate desc", ids);
SqlParameter[] paras =
{
SqlParamHelper.MakeInParam("@period", SqlDbType.TinyInt, 1, period),
SqlParamHelper.MakeInParam("@softid", SqlDbType.Int, 4, softId),
SqlParamHelper.MakeInParam("@platform", SqlDbType.TinyInt, 1, platform),
SqlParamHelper.MakeInParam("@begintime", SqlDbType.DateTime, 8, beginDate),
SqlParamHelper.MakeInParam("@endtime", SqlDbType.DateTime, 8, endDate),
SqlParamHelper.MakeInParam("@mindateint", SqlDbType.Int, 4, minTime.ToString("yyyyMMdd")),
};
List <Sjqd_StatRetainedUsers> list = new List <Sjqd_StatRetainedUsers>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(StatConn, CommandType.Text, sql, paras))
{
while (reader.Read())
{
list.Add(new Sjqd_StatRetainedUsers(reader));
}
}
return(list);
}
/// <summary>
/// 获取品牌分布
/// </summary>
/// <param name="period"></param>
/// <param name="statDate"></param>
/// <param name="softId"></param>
/// <param name="platform"></param>
/// <returns></returns>
public List <Sjqd_StatUsersBySbxh> GetSoftBrandTransverse(net91com.Stat.Core.PeriodOptions period, int statDate,
int softId, MobileOption platform)
{
string key = BuildCacheKey("GetSoftBrandTransverse", period, statDate, softId, platform);
List <Sjqd_StatUsersBySbxh> list = CacheHelper.Get <List <Sjqd_StatUsersBySbxh> >(key);
if (list == null)
{
// string cmdText = @" select mobile_name,sum(userscount) userCount from (
// SELECT case when b.mobile_name='' or b.mobile_name is null then '未适配品牌' else b.mobile_name end mobile_name,userscount
// FROM (
// SELECT SbxhID,SUM(NewUserCount+ActiveUserCount) userscount
// FROM Sjqd_StatUsersBySbxh WITH(NOLOCK)
// WHERE Period=@period AND StatDate=@StatDate AND SoftID=@SoftID AND [Platform]=@Platform
// GROUP BY SbxhID) A LEFT JOIN Sjqd_SBXH b WITH(NOLOCK) ON A.SbxhID=b.ID) A
// group by mobile_name
// ORDER BY userCount 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 SbxhID,SUM(NewUserCount+ActiveUserCount) userscount
FROM U_StatUsersBySbxh
WHERE Period={0} AND StatDate={1} AND SoftID={2} AND Platform={3}
GROUP BY SbxhID", (int)period, statDate, softId, (int)platform);
list = new List <Sjqd_StatUsersBySbxh>();
using (IDataReader read = MySqlHelper.ExecuteReader(MySql_StatDbConn, sql))
{
while (read.Read())
{
list.Add(new Sjqd_StatUsersBySbxh()
{
UseCount = Convert.ToInt32(read["userscount"]),
SbxhID = Convert.ToInt32(read["SbxhID"].ToString())
});
}
}
string strrr = "";
foreach (string s2 in list.Select(p => p.SbxhID.ToString()).ToList())
{
strrr += s2 + ",";
LogHelper.WriteInfo("s2:" + strrr);
}
var sbxhlst = Sjqd_SBXHService.GetSjqd_SBXHList((int)platform,
list.Select(p => p.SbxhID.ToString()).ToList());
var reslst = from sjqdStatUsersBySbxh in list
join sjqdSbxh in sbxhlst on sjqdStatUsersBySbxh.SbxhID equals sjqdSbxh.ID into os
from tt in os.DefaultIfEmpty()
select new Sjqd_StatUsersBySbxh()
{
Brand = tt == null ? "未适配品牌" : tt.mobile_name,
UseCount = sjqdStatUsersBySbxh.UseCount
};
var s = reslst.GroupBy(p => p.Brand).Select(p => p.Key).ToList();
foreach (string sjqdStatUsersBySbxhs in s)
{
LogHelper.WriteInfo("brand:" + sjqdStatUsersBySbxhs + "configlst:" + sbxhlst.Count + "reslst:" +
reslst.Count());
}
var realreslst = (from item in reslst
group item by item.Brand
into g
select new Sjqd_StatUsersBySbxh()
{
Brand = g.Key,
UseCount = g.Sum(p => p.UseCount)
}).ToList();
if (realreslst.Count > 0)
{
CacheHelper.Set <List <Sjqd_StatUsersBySbxh> >(key, realreslst, CacheTimeOption.TenMinutes);
list = realreslst.OrderByDescending(p => p.UseCount).ToList();
}
}
return(list);
}
