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