/// <summary>
/// 设置渠道商系数参数
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
private Result SetCustomerSwitch(HttpContext context)
{
var loginService = new URLoginService();
int soft = Convert.ToInt32(context.Request["softs"]);
int id = Convert.ToInt32(context.Request["id"]);
int switchtype = Convert.ToInt32(context.Request["switchtype"]);
int switchvalue = Convert.ToInt32(context.Request["switchvalue"]);
//这里不要使用GetChannelCustomeById 方法 因为获取到的cid 就是标准的cid
Sjqd_ChannelCustomers obj = SjqdChannelCustomers_DataAccess.Instance.GetChannelCustomer(id);
//对外链接地址开关设置
if (switchtype == 1)
{
obj.ReportType = switchvalue;
}
else if (switchtype == 2)
{
obj.ShowType = switchvalue;
}
if (SjqdChannelCustomers_DataAccess.Instance.UpdateChannelCustomer(obj) >= 0)
{
loginService.AddLog(string.Format("设置渠道商开关,softid:{3},customerid:{0},switchtype:{1},switchvalue:{2}", id,
switchtype, switchvalue, soft));
return(Result.GetSuccessedResult("", "设置成功!", true));
}
else
{
return(Result.GetFailedResult("设置失败!"));
}
}
/// <summary>
/// 修改渠道商位置
/// </summary>
public Result ChangeCustomerPositon(HttpContext context)
{
var loginService = new URLoginService();
int softid = Convert.ToInt32(context.Request["softs"]);
int targetID = Convert.ToInt32(context.Request["targetid"]);
int sourceID = Convert.ToInt32(context.Request["sourceid"]);
int type = Convert.ToInt32(context.Request["targettype"]);
//获取来源目标的类型
int result = SjqdChannelCustomers_DataAccess.Instance.ChangeChannelCustomerPosition(targetID, type, sourceID);
if (result > 0)
{
loginService.AddLog(string.Format("修改渠道商位置,softid:{0},customerid:{1},targetid:{2},targettype:{3}",
softid, sourceID, targetID, type));
//合并了渠道商
if (result == 2)
{
return(Result.GetSuccessedResult("", "合并成功", true));
}
else
{
return(Result.GetSuccessedResult((ChannelTypeOptions)type + "_" + targetID, "迁移成功", true));
}
}
else
{
return(Result.GetFailedResult("迁移失败!"));
}
}
/// <summary>
/// 删除渠道id
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
private Result DeleteChannelId(HttpContext context)
{
var loginService = new URLoginService();
List <int> channels =
context.Request["id"].TrimEnd(',').Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
.Select(p => Convert.ToInt32(p)).ToList();
int softid = Convert.ToInt32(context.Request["softs"]);
for (int i = 0; i < channels.Count; i++)
{
if (SjqdChannels_DataAccess.Instance.DeleteChannelBind(channels[i]) >= 0)
{
loginService.AddLog(string.Format("取消渠道绑定,softid:{0},channelid:{1}", softid, channels[i]));
if (channels.Count == 1)
{
return(Result.GetSuccessedResult("", "取消渠道绑定成功!", true));
}
}
else
{
if (channels.Count == 1)
{
return(Result.GetFailedResult("取消渠道绑定失败!"));
}
}
}
return(Result.GetSuccessedResult("", "取消渠道绑定成功!", true));
}
/// <summary>
/// 添加自定义channelid
/// </summary>
/// <returns></returns>
private Result AddDefinedChannelid(HttpContext context)
{
var loginService = new URLoginService();
int customerid = Convert.ToInt32(context.Request["customerid"]);
decimal m1 = Convert.ToDecimal(context.Request["m1"]);
string[] plats = context.Request["platform"].Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
HashSet <int> selectPlats = GetPlats(plats);
int softid = Convert.ToInt32(context.Request["softs"]);
string name = HttpUtility.HtmlEncode(context.Request["name"]);
foreach (int plat in selectPlats)
{
if (SjqdChannels_DataAccess.Instance.DefineIsExsit(plat, softid, name) == 0)
{
if (SjqdChannels_DataAccess.Instance.AddDefineChannel(customerid, m1, 0m, plat, softid, name) >= 0)
{
loginService.AddLog(string.Format("添加自定义渠道商,softid:{0},platform:{2},name:{1}", softid, name,
plat));
}
}
}
return(Result.GetSuccessedResult("", "添加成功!", true));
}
private Result GetComplexPlatUserData(HttpContext context)
{
var loginService = new URLoginService();
SetDownHead(context.Response, "复合平台用户下载量.xls", false, "gb2312");
if (loginService.CheckUrlRight("tools/InternalCommonUse.aspx", "?act=getcomplexplatuserdata"))
{
List <SoftUser> lstsoftuser = GetComplexPlatUserData_GetData(context);
Func <List <SoftUser>, List <List <string> > > func = p =>
{
var tempList = new List <List <string> >();
foreach (SoftUser item in p)
{
var values = new List <string>();
values.Add(item.StatDate.ToString("yyyy-MM-dd"));
values.Add(UtilityHelp.FormatNum(item.NewNum));
values.Add(UtilityHelp.FormatNum(item.ActiveNum));
tempList.Add(values);
}
return(tempList);
};
string html = GetTableHtml(new[] { "日期", "新增用户", "活跃用户" }, func(lstsoftuser));
return(Result.GetSuccessedResult(html, false, true));
}
else
{
return(Result.GetSuccessedResult("", false, true));
}
}
private Result AddCateCustom(HttpContext context)
{
var loginService = new URLoginService();
string name = HttpUtility.HtmlEncode(context.Request["name"]);
int cateid = Convert.ToInt32(context.Request["cateid"]);
int softid = Convert.ToInt32(context.Request["softs"]);
var custom = new Sjqd_ChannelCustomers();
//默认值
custom.IsRealtime = 0;
custom.Name = name;
custom.ReportType = 0;
custom.PID = 0;
custom.CID = cateid;
custom.SoftID = softid;
custom.AddTime = DateTime.Now;
int result = SjqdChannelCustomers_DataAccess.Instance.AddChannelCustomer(custom);
if (result > 0)
{
loginService.AddLog("AddChannelCustomer",
string.Format("添加渠道商(ID={0},SoftID={1},PID={2},CID={3},Name={4})", result, softid,
custom.PID, custom.CID, custom.Name));
return(Result.GetSuccessedResult(result, "保存成功！", true));
}
else
{
return(Result.GetFailedResult("该名称渠道商已经存在,保存失败！"));
}
}
void Application_Error(object sender, EventArgs e)
{
Exception ex = Server.GetLastError().InnerException ?? Server.GetLastError();
try
{
if (ex is net91com.Reports.UserRights.NotRightException)
{
Response.Redirect("/Reports/NoRight.aspx");
return;
}
if (ex is System.Threading.ThreadAbortException)
{
System.Threading.Thread.ResetAbort();
return;
}
string ip1 = net91com.Core.Web.WebHelper.GetRealIP();
string ip2 = Request.Headers.Get("HTTP_NDUSER_FORWARDED_FOR_HAPROXY");
string serverIp = Request.ServerVariables["LOCAL_ADDR"];
string msg = string.Format("\r\nGlobal异常: 客户IP:{0};{1}; 服务器ip:{2}\r\nPost数据:{3}",
ip1, ip2, serverIp, Request.Form);
URLoginService loginService = new URLoginService();
string account = string.Empty;
try
{
account = loginService.LoginUser.Account;
}
catch { }
LogHelper.WriteException(msg + ", Account=" + account, ex);
}
finally
{
Server.ClearError();
}
}
/// <summary>
/// 删除渠道商
/// </summary>
/// <returns></returns>
private Result DeleteChannelCustomer(HttpContext context)
{
var loginService = new URLoginService();
//int id = Convert.ToInt32(context.Request["id"]);
List <int> ids =
context.Request["id"].Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
.Select(p => Convert.ToInt32(p))
.ToList();
int softid = Convert.ToInt32(context.Request["softs"]);
for (int i = 0; i < ids.Count; i++)
{
if (SjqdChannelCustomers_DataAccess.Instance.DeleteChannelCustomer(ids[i]) > 0)
{
loginService.AddLog("DeleteChannelCustomer",
string.Format("删除渠道商(ID={0},SoftID={1})", ids[i], softid));
if (ids.Count == 1)
{
return(Result.GetSuccessedResult("", "删除成功!", true));
}
}
else if (ids.Count == 1)
{
return(Result.GetFailedResult("该渠道商下含有渠道ID或渠道商,删除失败!"));
}
}
return(Result.GetSuccessedResult("", "删除成功(若仍出现请确保该渠道商上没有挂渠道)!", true));
}
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)));
}
public List <SoftUser> GetSoftUserListCache(DateTime begin, DateTime end, int softid, int platformid,
PeriodOptions period, URLoginService loginService, CacheTimeOption cachetime)
{
string cacheKey = BuildCacheKey("GetSoftUserListCache", begin, end, softid, platformid, period,
((loginService.LoginUser.AccountType ==
UserTypeOptions.Channel || loginService.LoginUser.AccountType ==
UserTypeOptions.ChannelPartner)? loginService.LoginUser.ID.ToString(): ""));
return(CacheHelper.Get <List <SoftUser> >(cacheKey, CacheTimeOption.TenMinutes,
() =>
GetSoftUserList(begin, end, softid, platformid, period, loginService)));
}
public List <ForecastSoftUser> GetForecastSoftUserCache(int softid, int platformid, PeriodOptions period,
URLoginService loginService, CacheTimeOption cachetime)
{
string cacheKey = BuildCacheKey("GetForecastSoftUserCache", softid, platformid, period,
loginService == null
? ""
: ((loginService.LoginUser.AccountType == UserTypeOptions.Channel ||
loginService.LoginUser.AccountType == UserTypeOptions.ChannelPartner)
? loginService.LoginUser.ID.ToString(): ""));
return(CacheHelper.Get <List <ForecastSoftUser> >(cacheKey, CacheTimeOption.TenMinutes,
() =>
GetForecastSoftUser(softid, platformid, period,
loginService)));
}
/// <summary>
/// 获取未绑定的channelid
/// </summary>
/// <returns></returns>
private Result GetUnBindList(HttpContext context)
{
var loginService = new URLoginService();
int id = Convert.ToInt32(context.Request["customerid"]);
int soft = Convert.ToInt32(context.Request["softs"]);
int plat = Convert.ToInt32(context.Request["plat"]);
List <Sjqd_ChannelFromClient> channelsList =
SjqdChannelsFromClient_DataAccess.Instance.GetNotBoundChannelFromClientList(soft);
var list = new List <object>();
foreach (Sjqd_ChannelFromClient item in channelsList)
{
list.Add(new { ID = item.AuID, Name = item.Name.Trim(), platform = item.Platform });
}
return(Result.GetSuccessedResult(list, true));
}
/// <summary>
/// 删除分类
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
private Result DeleteCate(HttpContext context)
{
var loginService = new URLoginService();
int cateid = Convert.ToInt32(context.Request["cateid"]);
int softid = Convert.ToInt32(context.Request["softs"]);
int result = SjqdChannelCategories_DataAccess.Instance.DeleteChannelCategory(cateid);
if (result > 0)
{
loginService.AddLog("DeleteChannelCategory", string.Format("删除渠道分类(ID={0},SoftID={1})", cateid, softid));
return(Result.GetSuccessedResult("", "删除成功！", true));
}
else
{
return(Result.GetFailedResult("删除失败!"));
}
}
/// <summary>
/// 添加渠道商
/// </summary>
/// <returns></returns>
private Result AddBatchCustomer(HttpContext context)
{
var loginService = new URLoginService();
string nameprefix = HttpUtility.HtmlEncode(context.Request["nameprefix"]);
int begin = Convert.ToInt32(context.Request["beginnum"]);
int end = Convert.ToInt32(context.Request["endnum"]);
if ((end - begin) >= 20)
{
end = begin + 19;
}
int id = Convert.ToInt32(context.Request["parentcustomerid"]);
int cid = Convert.ToInt32(context.Request["cid"]);
int soft = Convert.ToInt32(context.Request["softs"]);
int resultid = 0;
Sjqd_ChannelCustomers obj = SjqdChannelCustomers_DataAccess.Instance.GetChannelCustomer(id);
for (int i = begin; i <= end; i++)
{
string name = nameprefix + i;
var custom = new Sjqd_ChannelCustomers();
custom.IsRealtime = 0;
custom.Name = name;
//默认值
custom.ReportType = 0;
custom.PID = id;
custom.CID = cid;
custom.SoftID = soft;
if (obj != null)
{
custom.CID = obj.CID;
custom.SoftID = obj.SoftID;
}
resultid = SjqdChannelCustomers_DataAccess.Instance.AddChannelCustomer(custom);
if (resultid > 0)
{
loginService.AddLog("AddChannelCustomer",
string.Format("添加渠道商(ID={0},SoftID={1},PID={2},CID={3},Name={4})", resultid,
soft, custom.PID, custom.CID, custom.Name));
}
}
return(Result.GetSuccessedResult(resultid, "添加成功!", true));
}
/// <summary>
/// 更新渠道ID
/// </summary>
/// <returns></returns>
private Result UpdateChannelId(HttpContext context)
{
var loginService = new URLoginService();
int id = Convert.ToInt32(context.Request["id"]);
decimal m1 = Convert.ToDecimal(context.Request["m1"]);
string name = HttpUtility.HtmlEncode(context.Request["name"]);
int softid = Convert.ToInt32(context.Request["softs"]);
if (SjqdChannels_DataAccess.Instance.UpdateChannelModulus(id, name, m1) >= 0)
{
loginService.AddLog(string.Format("更新渠道ID,softid:{0},name:{1},channelid:{2},m1:{3}",
softid, name, id, m1));
return(Result.GetSuccessedResult("", "更新成功!", true));
}
else
{
return(Result.GetFailedResult("更新失败!"));
}
}
//添加同级渠道商
private Result AddSameLevelCustomer(HttpContext context)
{
var loginService = new URLoginService();
string name = HttpUtility.HtmlEncode(context.Request["name"]);
int softid = Convert.ToInt32(context.Request["softs"]);
int samelevelcustomer = Convert.ToInt32(context.Request["samelevelcustomerid"]);
//获取来源目标的类型
Sjqd_ChannelCustomers parentcustomer =
SjqdChannelCustomers_DataAccess.Instance.GetChannelCustomer(samelevelcustomer);
if (parentcustomer.ID == 0)
{
return(Result.GetFailedResult("添加失败!"));
}
var custom = new Sjqd_ChannelCustomers();
custom.IsRealtime = 0;
custom.Name = name;
//默认值
custom.ReportType = 0;
custom.PID = parentcustomer.PID;
custom.CID = parentcustomer.CID;
custom.SoftID = parentcustomer.SoftID;
custom.AddTime = DateTime.Now;
//若不是顶级渠道商,需要将父级渠道商属性赋予
if (custom.PID != 0)
{
Sjqd_ChannelCustomers obj = SjqdChannelCustomers_DataAccess.Instance.GetChannelCustomer(custom.PID);
}
int resultid = SjqdChannelCustomers_DataAccess.Instance.AddChannelCustomer(custom);
if (resultid > 0)
{
loginService.AddLog("AddChannelCustomer",
string.Format("添加渠道商(ID={0},SoftID={1},PID={2},CID={3},Name={4})", resultid, softid,
custom.PID, custom.CID, custom.Name));
return(Result.GetSuccessedResult(resultid, "添加成功!", true));
}
else
{
return(Result.GetFailedResult("添加失败!"));
}
}
/// <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>
/// <returns></returns>
private Result AddCustomer(HttpContext context)
{
var loginService = new URLoginService();
string name = HttpUtility.HtmlEncode(context.Request["name"]);
int id = Convert.ToInt32(context.Request["parentcustomerid"]);
int cid = Convert.ToInt32(context.Request["cid"]);
int soft = Convert.ToInt32(context.Request["softs"]);
var custom = new Sjqd_ChannelCustomers();
custom.IsRealtime = 0;
custom.Name = name;
//默认值
custom.ReportType = 0;
custom.PID = id;
custom.CID = cid;
custom.SoftID = soft;
custom.AddTime = DateTime.Now;
Sjqd_ChannelCustomers obj = SjqdChannelCustomers_DataAccess.Instance.GetChannelCustomer(id);
if (obj != null)
{
custom.CID = obj.CID;
custom.SoftID = obj.SoftID;
}
int resultid = SjqdChannelCustomers_DataAccess.Instance.AddChannelCustomer(custom);
if (resultid > 0)
{
loginService.AddLog("AddChannelCustomer",
string.Format("添加渠道商(ID={0},SoftID={1},PID={2},CID={3},Name={4})", resultid, soft,
custom.PID, custom.CID, custom.Name));
return(Result.GetSuccessedResult(resultid, "添加成功!", true));
}
else
{
return(Result.GetFailedResult("添加失败!"));
}
}
/// <summary>
/// 绑定到渠道表中去
/// </summary>
/// <returns></returns>
private Result BindNewChannelFromClient(HttpContext context)
{
var loginService = new URLoginService();
List <int> channelids =
context.Request["autoids"].Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
.Select(p => Convert.ToInt32(p)).ToList();
int customerid = Convert.ToInt32(context.Request["customerid"]);
int softid = Convert.ToInt32(context.Request["softs"]);
string channelidstr = string.Join(",", channelids.Select(p => p.ToString()).ToArray());
;
if (SjqdChannelsFromClient_DataAccess.Instance.AddNewChannelFromClient(channelids, customerid) >= 0)
{
loginService.AddLog(string.Format("设置channelid到渠道商下,softid:{0},channelid:{1},customerid:{2}",
softid, context.Request["autoids"], customerid));
return(Result.GetSuccessedResult("", "添加成功!", true));
}
else
{
return(Result.GetFailedResult("添加失败!"));
}
}
public void ProcessRequest(HttpContext context)
{
HttpRequest request = HttpContext.Current.Request;
URLoginService loginService = new URLoginService();
try
{
string logoutString = request.QueryString["logout"];
bool logout = false;
if (!string.IsNullOrEmpty(logoutString) && bool.TryParse(logoutString, out logout) && logout)
{
loginService.Logout();
if (!string.IsNullOrEmpty(request.QueryString["ReturnUrl"]))
{
HttpContext.Current.Response.Redirect(request.QueryString["ReturnUrl"]);
}
else
{
HttpContext.Current.Response.Redirect(FormsAuthentication.DefaultUrl);
}
}
else
{
loginService.Login();
}
}
catch (ThreadAbortException) { }
catch (ToUserException ex)
{
HttpContext.Current.Response.Write("alert('" + ex.Message.Replace("'", "\\'") + "');location='" + request.Url + "&ReturnUrl=" + HttpContext.Current.Server.UrlEncode(request.QueryString["ReturnUrl"]) + "';");
}
catch (Exception ex)
{
LogHelper.WriteException("net91com.Stat.Web", ex);
HttpContext.Current.Response.Write("alert('系统错误,请稍候再试...');location='" + request.Url + "&ReturnUrl=" + HttpContext.Current.Server.UrlEncode(request.QueryString["ReturnUrl"]) + "';");
}
}
/// <summary>
/// 编辑添加分类
/// </summary>
/// <returns></returns>
private Result EditAddCate(HttpContext context)
{
var loginService = new URLoginService();
int softid = Convert.ToInt32(context.Request["softs"]);
string catename = context.Request["catename"];
int cateid = Convert.ToInt32(context.Request["cateid"]);
var cate = new Sjqd_ChannelCategories();
cate.SoftID = softid;
string oldName = cate.Name;
cate.Name = catename;
cate.ID = cateid;
cate.InDate = DateTime.Now;
int result = cate.ID == 0
? SjqdChannelCategories_DataAccess.Instance.AddChannelCategory(cate)
: SjqdChannelCategories_DataAccess.Instance.UpdateChannelCategory(cate);
if (result > 0)
{
if (cate.ID == 0)
{
loginService.AddLog("AddChannelCategory",
string.Format("添加渠道分类(SoftID={0},Name={1})", softid, cate.Name));
}
else
{
loginService.AddLog("UpdateChannelCategory",
string.Format("修改渠道分类(ID={0},SoftID={1},Name={2})", cate.ID, softid, cate.Name));
}
return(Result.GetSuccessedResult("", "保存成功！", true));
}
else
{
return(Result.GetFailedResult("保存失败！"));
}
}
/// <summary>
/// 设置所选channelid的m1系数
/// </summary>
/// <returns></returns>
private Result SetSelectChannelM1(HttpContext context)
{
var loginService = new URLoginService();
string[] ids = context.Request["channelids"].TrimEnd(',').Split(new[]
{
','
}
, StringSplitOptions.RemoveEmptyEntries);
int softid = Convert.ToInt32(context.Request["softs"]);
decimal m1 = Convert.ToDecimal(context.Request["m"]);
if (SjqdChannels_DataAccess.Instance.UpdateM1ByChannels(ids.Select(p => Convert.ToInt32(p)).ToList(), m1) >=
0)
{
loginService.AddLog(string.Format("修改所选渠道id系数值,softid:{0},channelids:{1},m:{2}", softid,
context.Request["channelids"], m1));
return(Result.GetSuccessedResult("", "设置成功!", true));
}
else
{
return(Result.GetFailedResult("设置失败!"));
}
}
/// <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>
/// <returns></returns>
private Result UpdateCustomer(HttpContext context)
{
//pc 产品列表
var softList = new[] { 9, 105550, 72, 57, 68, 69 };
var loginService = new URLoginService();
int soft = Convert.ToInt32(context.Request["softs"]);
int id = Convert.ToInt32(context.Request["id"]);
decimal m1 = Convert.ToDecimal(context.Request["m1"]);
string name = HttpUtility.HtmlEncode(context.Request["name"]);
int showtype = Convert.ToInt32(context.Request["showtype"]);
DateTime minViewTime = Convert.ToDateTime(context.Request["minviewtime"]);
bool needEditSonCustomer = Convert.ToBoolean(context.Request["needEditSonCustomer"]);
//开放给外部
int reporttype = Convert.ToInt32(context.Request["reporttype"]);
//这里不要使用GetChannelCustomeById 方法 因为获取到的cid 就是标准的cid
Sjqd_ChannelCustomers customer = SjqdChannelCustomers_DataAccess.Instance.GetChannelCustomer(id);
//minviewtime 若没传 用其老的minviewtime
if (context.Request["minviewtime"] == null)
{
minViewTime = customer.MinViewTime;
}
string oldName = customer.Name;
int oldreporttype = customer.ReportType;
int oldmodulus_Shanzhai = customer.Modulus_Shanzhai;
decimal oldm1 = customer.Modulus1;
int oldShowType = customer.ShowType;
DateTime oldmintime = customer.MinViewTime;
customer.MinViewTime = minViewTime;
customer.Modulus1 = m1;
customer.Name = name;
customer.ReportType = reporttype;
bool canUpdateName = true;
//有传入就修改
if (context.Request["showtype"] != null)
{
customer.ShowType = Convert.ToInt32(context.Request["showtype"]);
}
if (name != oldName &&
SjqdChannelCustomers_DataAccess.Instance.GetCustomerCount(customer.CID, customer.PID, customer.Name) > 1)
{
return(Result.GetFailedResult("修改失败,已经存在对应名称渠道商!"));
}
if (SjqdChannelCustomers_DataAccess.Instance.UpdateChannelCustomer(customer, canUpdateName) >= 0)
{
loginService.AddLog(
"UpdateChannelCustomer"
,
string.Format(
"修改渠道商(ID={0},SoftID={1},Name={2}(Old={3}),Modulus1={4}(Old={5}),MinViewTime={6}(Old={7}),ReportType={8}(Old={9}),ShowType={10}(Old={11})",
id, soft, name, oldName, m1, oldm1, oldmintime, minViewTime, oldmintime, customer.ReportType,
oldreporttype, customer.ShowType, oldShowType));
return(Result.GetSuccessedResult("", "修改成功!", true));
}
else
{
return(Result.GetFailedResult("修改失败!"));
}
}
public List <SoftUser> GetSoftUserList(DateTime begin, DateTime end, int softid, int platformid, PeriodOptions period, URLoginService loginService)
{
List <SoftUser> lists = new List <SoftUser>();
string sqlstr = "";
///无渠道商权限限制
if (loginService.LoginUser.AccountType != UserTypeOptions.Channel &&
loginService.LoginUser.AccountType != UserTypeOptions.ChannelPartner)
{
if (platformid != (int)MobileOption.None && platformid > 0)
{
sqlstr =
@" select period,StatDate,platform,softid ,
(NewUserCount+NewUserCountFromCache+NewUserCount_Shanzhai-ifnull(NewUserCount_Shualiang,0)) as newnum,
NewUserCountFromCache,NewUserCount_Broken,NewUserCount_NotBroken,
ActiveUserCountFromCache+ActiveUserCount+ActiveUserCount_Shanzhai activenum,ActiveUserCountFromCache,
ActiveUserCount_Broken,ActiveUserCount_NotBroken,LostUserCount lostnum ,
TotalUserCount+TotalUserCount_Shanzhai as totalnum
,DownValueUsersForNew,
DownValueUsersForAct,
FuncValueUsersForNew,
FuncValueUsersForAct,
NewUserCount-ifnull(NewUserCount_Shualiang,0) FirstNewUserCount,
ActiveUserCount FirstActiveUserCount,
NewUserCount_ZJS,
ActiveUserCount_ZJS,
NewUserCount_Shanzhai,
TotalUserCount_Shanzhai,
ActiveUserCount_Shanzhai,
IFNULL(NewUserCount_SecAct,0) NewUserCount_SecAct,
IFNULL(NewUserCount_SecAct2,0) NewUserCount_SecAct2
from U_StatUsers
where softid=?softid and platform=?platform and period=?periodid and StatDate between ?begindate and ?enddate
order by StatDate desc" ;
}
else//不区分平台
{
sqlstr =
@" select period,StatDate,0 as platform,softid ,
sum(NewUserCount+NewUserCountFromCache+NewUserCount_Shanzhai-ifnull(NewUserCount_Shualiang,0)) as newnum,
sum(NewUserCountFromCache) NewUserCountFromCache,sum(NewUserCount_Broken) NewUserCount_Broken,
sum(NewUserCount_NotBroken) NewUserCount_NotBroken,
sum(ActiveUserCountFromCache+ActiveUserCount+ActiveUserCount_Shanzhai) as activenum,
sum(ActiveUserCountFromCache) ActiveUserCountFromCache,
sum(ActiveUserCount_Broken) as ActiveUserCount_Broken,
sum(ActiveUserCount_NotBroken) as ActiveUserCount_NotBroken,
sum(LostUserCount) as lostnum ,
sum(TotalUserCount+TotalUserCount_Shanzhai) as totalnum
,sum(DownValueUsersForNew) DownValueUsersForNew,
sum(DownValueUsersForAct) DownValueUsersForAct,
sum(FuncValueUsersForNew) FuncValueUsersForNew,
sum(FuncValueUsersForAct) FuncValueUsersForAct,
sum(NewUserCount-ifnull(NewUserCount_Shualiang,0)) FirstNewUserCount,
sum(ActiveUserCount) FirstActiveUserCount,
sum(NewUserCount_ZJS) NewUserCount_ZJS,
sum(ActiveUserCount_ZJS) ActiveUserCount_ZJS,
sum(TotalUserCount_Shanzhai) TotalUserCount_Shanzhai,
sum(NewUserCount_Shanzhai) NewUserCount_Shanzhai,
sum(ActiveUserCount_Shanzhai) ActiveUserCount_Shanzhai,
sum(IFNULL(NewUserCount_SecAct,0)) NewUserCount_SecAct,
sum(IFNULL(NewUserCount_SecAct2,0)) NewUserCount_SecAct2
from U_StatUsers
where softid=?softid and period=?periodid and platform<252 and StatDate between ?begindate and ?enddate
Group By period,StatDate,softid order by StatDate desc" ;
}
}
else ///有渠道商权限限制
{
List <int> rangeChannelIds = loginService.GetAvailableChannelIds(softid);
if (rangeChannelIds.Count == 0)
{
return(new List <SoftUser>());
}
string channelIdsString = string.Join(",", rangeChannelIds.Select(a => a.ToString()).ToArray());
sqlstr =
string.Format(@" select period, StatDate,{1} softid ,sum(NewUserCount+NewUserCount_Shanzhai-ifnull(NewUserCount_Shualiang,0)) as newnum,
0 NewUserCountFromCache,0 NewUserCount_Broken,
0 NewUserCount_NotBroken,
sum(ActiveUserCount+ActiveUserCount_Shanzhai) as activenum,
0 ActiveUserCountFromCache,
0 as ActiveUserCount_Broken,
0 as ActiveUserCount_NotBroken,
sum(LostUserCount) as lostnum ,
sum(TotalUserCount+TotalUserCount_Shanzhai) as totalnum,
sum(DownValueUsersForNew) DownValueUsersForNew,
sum(DownValueUsersForAct) DownValueUsersForAct,
sum(FuncValueUsersForNew) FuncValueUsersForNew,
sum(FuncValueUsersForAct) FuncValueUsersForAct,
sum(NewUserCount-ifnull(NewUserCount_Shualiang,0)) FirstNewUserCount,
sum(ActiveUserCount) FirstActiveUserCount,
0 NewUserCount_ZJS,0 ActiveUserCount_ZJS,
sum(TotalUserCount_Shanzhai) TotalUserCount_Shanzhai,
sum(NewUserCount_Shanzhai) NewUserCount_Shanzhai,
sum(ActiveUserCount_Shanzhai) ActiveUserCount_Shanzhai,
sum(ifnull(NewUserCount_SecAct,0)) NewUserCount_SecAct,
sum(ifnull(NewUserCount_SecAct2,0)) NewUserCount_SecAct2
from Sjqd_StatChannelUsers with(nolock)
where SoftID=?softid and ChannelID in ({3})
and Period=?periodid and Platform<252 {0} and StatDate between ?begindate and ?enddate
group by Period,StatDate,SoftID {2}
order by StatDate desc",
(platformid != (int)MobileOption.None && platformid > 0)
? " and platform=?platform "
: "",
(platformid != (int)MobileOption.None && platformid > 0)
? " platform,"
: "0 as platform,",
(platformid != (int)MobileOption.None && platformid > 0) ? " ,platform" : "",
channelIdsString);
}
var parameters = new []
{
new MySqlParameter("?softid", softid),
new MySqlParameter("?platform", platformid),
new MySqlParameter("?periodid", (int)period),
new MySqlParameter("?begindate", int.Parse(begin.ToString("yyyyMMdd"))),
new MySqlParameter("?enddate", int.Parse(end.ToString("yyyyMMdd")))
};
using (IDataReader dataReader = MySqlHelper.ExecuteReader(connString, sqlstr, parameters))
{
while (dataReader.Read())
{
lists.Add(UserBind(dataReader, platformid, true, true, true));
}
}
return(lists);
}
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="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="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="platformid"></param>
/// <param name="period"></param>
/// <returns></returns>
public List <ForecastSoftUser> GetForecastSoftUser(int softid, int platformid,
net91com.Stat.Core.PeriodOptions period,
URLoginService loginService)
{
string sqlstr = string.Empty;
if (loginService.LoginUser.AccountType != UserTypeOptions.ChannelPartner &&
loginService.LoginUser.AccountType != UserTypeOptions.Channel)
{
if (platformid != (int)MobileOption.None && platformid > 0)
{
sqlstr =
@" select period,StatDate,platform,softid ,(NewUserCount+NewUserCountFromCache+NewUserCount_Shanzhai) as newnum
,TotalUserCount+TotalUserCount_Shanzhai as totalnum
from U_StatUsers
where softid=?softid and platform=?platform and period=?periodid
Order by StatDate desc
limit 4
";
}
else
{
sqlstr =
@" select period,StatDate,0 as platform,softid
,sum(NewUserCount+NewUserCountFromCache+NewUserCount_Shanzhai) as newnum
,sum(TotalUserCount+TotalUserCount_Shanzhai) as totalnum
from U_StatUsers
where softid=?softid and period=?periodid and platform<252
Group By period,StatDate,softid Order by StatDate desc
limit 4
";
}
}
else
{
List <int> rangeChannelIds = loginService.GetAvailableChannelIds(softid);
if (rangeChannelIds.Count == 0)
{
return(new List <ForecastSoftUser>());
}
string channelIdsString = string.Join(",", rangeChannelIds.Select(a => a.ToString()).ToArray());
sqlstr = string.Format(@" select
period, StatDate,0 as platform, softid ,sum(NewUserCount+NewUserCount_Shanzhai) as newnum,
sum(TotalUserCount+TotalUserCount_Shanzhai) as totalnum
from U_StatChannelUsers
where SoftID=?softid AND ChannelID in ({1})
and Period=?periodid and Platform<252 {0}
group by Period,StatDate,SoftID
order by StatDate desc
limit 4",
(platformid != (int)MobileOption.None && platformid > 0)
? " and platform=?platform "
: "", channelIdsString);
}
var parameters = new []
{
new MySqlParameter("?softid", softid),
new MySqlParameter("?platform", platformid),
new MySqlParameter("?periodid", (int)period)
};
List <ForecastSoftUser> users = new List <ForecastSoftUser>();
using (IDataReader dataReader = MySqlHelper.ExecuteReader(connString, sqlstr, parameters))
{
while (dataReader.Read())
{
ForecastSoftUser softUser = new ForecastSoftUser();
softUser.Period = Convert.ToInt16(dataReader["period"]);
softUser.Platform = platformid;
softUser.SoftId = Convert.ToInt32(dataReader["softid"]);
int sDate = Convert.ToInt32(dataReader["StatDate"]);
softUser.StatDate = new DateTime(sDate / 10000, sDate / 100 % 100, sDate % 100, 0, 0, 0);
switch (softUser.Period)
{
case (int)PeriodOptions.Daily:
softUser.ForecaseDate = softUser.StatDate.AddDays(1);
break;
case (int)PeriodOptions.Weekly:
softUser.ForecaseDate = softUser.StatDate.AddDays(7);
break;
case (int)PeriodOptions.Monthly:
softUser.ForecaseDate = softUser.StatDate.AddMonths(1);
break;
case (int)PeriodOptions.Of2Weeks:
softUser.ForecaseDate = softUser.StatDate.AddMonths(14);
break;
case (int)PeriodOptions.Of3Days:
softUser.ForecaseDate = softUser.StatDate.AddMonths(3);
break;
}
softUser.NewUserCount = Convert.ToInt32(dataReader["newnum"]);
softUser.TotalUserCount = Convert.ToInt32(dataReader["totalnum"]);
users.Add(softUser);
}
}
return(users);
}
