public List <SoftUser> GetAllSoftUsers(DateTime begin, DateTime end, int softid, int platformid,
PeriodOptions period)
{
List <SoftUser> lists = new List <SoftUser>();
string sqlstr = "";
if (period != (int)MobileOption.None)
{
sqlstr =
@" select period,StatDate,platform,softid ,
NewUserCountFromCache+NewUserCount+NewUserCount_Shanzhai-ifnull(NewUserCount_Shualiang,0) as newnum,
ActiveUserCountFromCache+ActiveUserCount+ActiveUserCount_Shanzhai as activenum,LostUserCount as lostnum ,
TotalUserCount+TotalUserCount_Shanzhai as totalnum
from U_StatUsers
where softid=?softid and platform=?platform and period=?periodid and StatDate between ?begindate and ?enddate ";
}
var parameters = new []
{
new MySqlParameter("?softid", softid),
new MySqlParameter("?platform", platformid),
new MySqlParameter("?periodid", (int)period),
new MySqlParameter("?begindate", begin.ToString("yyyyMMdd")),
new MySqlParameter("?enddate", end.ToString("yyyyMMdd"))
};
using (IDataReader dataReader = MySqlHelper.ExecuteReader(connString, sqlstr, parameters))
{
while (dataReader.Read())
{
lists.Add(UserBind(dataReader));
}
}
return(lists);
}
/// <summary>
/// 获取标准的统计时间
/// </summary>
/// <param name="time"></param>
/// <param name="period"></param>
public static void SpecificateSingleTime(ref DateTime time, PeriodOptions period)
{
//周
if (period == PeriodOptions.Weekly)
{
while (!(time.DayOfWeek == DayOfWeek.Sunday))
{
time = time.AddDays(-1);
}
}
//月
else if (period == PeriodOptions.Monthly)
{
while (!(time.Day == 20))
{
time = time.AddDays(-1);
}
}
else if (period == PeriodOptions.NaturalMonth)
{
while (!(time.AddDays(1).Day == 1))
{
time = time.AddDays(-1);
}
}
}
/// <summary>
/// 中国地区查找
/// </summary>
/// <param name="period">周期</param>
/// <param name="statDate">统计日期</param>
/// <param name="softId">软件ID</param>
/// <param name="platform">平台</param>
/// <returns></returns>
public List <Sjqd_StatUsersByArea> GetSoftAreaTransverseWithChina(PeriodOptions period, int statDate, int softId, MobileOption platform)
{
string key = BuildCacheKey("GetSoftAreaTransverseWithChina", period, statDate, softId, platform);
List <Sjqd_StatUsersByArea> list = CacheHelper.Get <List <Sjqd_StatUsersByArea> >(key);
if (list == null)
{
string cmdText = @" SELECT B.Province AreaName,sum(userscount) usercount FROM
(
select AreaID , NewUserCount+ActiveUserCount userscount
from Sjqd_StatUsersByArea with(nolock)
where Period=@period and StatDate=@StatDate and SoftID=@SoftID AND [Platform]=@Platform and ChannelID=0
)AS A inner join Sjqd_Areas as B WITH(NOLOCK)
ON A.AreaID=B.ID and B.E_Country='中国'
group by B.Province order by sum(userscount) desc";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter()
{
ParameterName = "@period", SqlDbType = System.Data.SqlDbType.TinyInt, Size = 1, Value = (int)period
},
new SqlParameter()
{
ParameterName = "@StatDate", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = statDate
},
new SqlParameter()
{
ParameterName = "@SoftID", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = softId
},
new SqlParameter()
{
ParameterName = "@Platform", SqlDbType = System.Data.SqlDbType.TinyInt, Size = 1, Value = (int)platform
}
};
list = new List <Sjqd_StatUsersByArea>();
int allcount = 0;
using (IDataReader read = SqlHelper.ExecuteReader(statdbConn, CommandType.Text, cmdText, param))
{
while (read.Read())
{
allcount += Convert.ToInt32(read["usercount"]);
list.Add(new Sjqd_StatUsersByArea()
{
UseCount = Convert.ToInt32(read["usercount"]),
AreaName = read["AreaName"].ToString(),
});
}
}
if (list.Count > 0)
{
CacheHelper.Set <List <Sjqd_StatUsersByArea> >(key, list, CacheTimeOption.TenMinutes);
}
}
return(list);
}
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)));
}
/// <summary>
/// Make OpenWeather API Call to get weather information
/// </summary>
public async void GetForecast()
{
try
{
var excludePeriod = new PeriodOptions[] { PeriodOptions.Minutely, PeriodOptions.Hourly };
OneCallResponse = await OpenWeather.OneCall(Location.Lat, Location.Lon, excludePeriod);
SetForecast();
}
catch (Exception)
{
var E = new ErrorMessage("An error occurred whilst getting weather forecast");
DisplayErrors.Add(E);
}
}
/// <summary>
/// 渠道分城市每天
/// </summary>
/// <param name="softid"></param>
/// <param name="mobileOption"></param>
/// <param name="periodOptions"></param>
/// <param name="beginstatdate"></param>
/// <param name="endstatdate"></param>
/// <param name="channelTypeOptions"></param>
/// <param name="channelIds"></param>
/// <param name="provinceName"></param>
/// <param name="type"></param>
/// <returns></returns>
public List <Sjqd_StatUsersByArea> GetSoftAreaTransverseWithChinaByCitysDayly(int softid, MobileOption mobileOption,
PeriodOptions periodOptions, DateTime beginstatdate, DateTime endstatdate,
ChannelTypeOptions channelTypeOptions,
List <int> channelIds, string city, string province)
{
string channelids = string.Join(",", channelIds.Select(p => p.ToString()).ToArray());
string sql = string.Empty;
string key = BuildCacheKey("GetSoftAreaTransverseWithChinaByCitysDayly", softid, mobileOption,
periodOptions, beginstatdate,
endstatdate, channelTypeOptions, channelids, city, province);
List <Sjqd_StatUsersByArea> list = CacheHelper.Get <List <Sjqd_StatUsersByArea> >(key);
if (list == null)
{
int[] channels = channelIds.Select(a => a).ToArray();
List <int> channelavailableIds = channels.Count() == 0? new List <int>(): new URLoginService().GetAvailableChannelIds(softid, channelTypeOptions, channels);
if (channelIds.Count != 0 && channelavailableIds.Count == 0)
{
return(new List <Sjqd_StatUsersByArea>());
}
string channelIdsString = string.Join(",", channelavailableIds.Select(a => a.ToString()).ToArray());
//不区分渠道的
if (channelIds.Count == 0)
{
sql = @"select A.StatDate,B.City AreaName,SUM(A.NewUserCount+A.ActiveUserCount) usercount from dbo.Sjqd_StatUsersByArea A
inner join dbo.Sjqd_Areas B
on A.AreaID=B.ID and B.E_Country='中国'
and B.City=@city and B.Province=@province and Period=@period and SoftID=@softid and Platform=@platform and ChannelID=0
and StatDate between @begintime and @endtime
group by B.City,A.StatDate";
}
else //区分渠道
{
sql =
string.Format(@"select A.StatDate, B.City AreaName,SUM(A.NewUserCount+A.ActiveUserCount) usercount from dbo.Sjqd_StatUsersByArea A
inner join dbo.Sjqd_Areas B
on A.AreaID=B.ID and B.E_Country='中国'
and B.City=@city and B.Province=@province and Period=@period and SoftID=@softid and Platform=@platform
and ChannelID in ({0})
and StatDate between @begintime and @endtime
group by B.City,A.StatDate", channelIdsString);
}
SqlParameter[] param = new SqlParameter[] {
new SqlParameter()
{
ParameterName = "@begintime", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = beginstatdate.ToString("yyyyMMdd")
},
new SqlParameter()
{
ParameterName = "@endtime", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = endstatdate.ToString("yyyyMMdd")
},
new SqlParameter()
{
ParameterName = "@softid", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = softid
},
new SqlParameter()
{
ParameterName = "@platform", SqlDbType = System.Data.SqlDbType.TinyInt, Size = 1, Value = (int)mobileOption
},
new SqlParameter()
{
ParameterName = "@city", SqlDbType = System.Data.SqlDbType.VarChar, Size = 100, Value = city.Trim()
},
new SqlParameter()
{
ParameterName = "@province", SqlDbType = System.Data.SqlDbType.VarChar, Size = 100, Value = province.Trim()
},
new SqlParameter()
{
ParameterName = "@period", SqlDbType = System.Data.SqlDbType.TinyInt, Size = 1, Value = (int)periodOptions
}
};
list = new List <Sjqd_StatUsersByArea>();
using (IDataReader read = SqlHelper.ExecuteReader(statdbConn, CommandType.Text, sql, param))
{
while (read.Read())
{
Sjqd_StatUsersByArea area = new Sjqd_StatUsersByArea();
if (read["usercount"] != null && read["usercount"] != DBNull.Value)
{
area.UseCount = Convert.ToInt32(read["usercount"]);
}
if (read["AreaName"] != null && read["AreaName"] != DBNull.Value)
{
area.AreaName = read["AreaName"].ToString();
}
if (read["StatDate"] != null && read["StatDate"] != DBNull.Value)
{
int date = Convert.ToInt32(read["StatDate"]);
area.StatDate = new DateTime(date / 10000, date % 10000 / 100, date % 100);
}
list.Add(area);
}
}
if (list.Count > 0)
{
CacheHelper.Set <List <Sjqd_StatUsersByArea> >(key, list, CacheTimeOption.TenMinutes);
}
}
return(list);
}
/// <summary>
/// 根据渠道商集合查询渠道商用户量（世界的）
/// </summary>
/// <param name="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);
}
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);
}
///获取历史最值
private List <SoftUser> GetMaxNum(DateTime begin, DateTime end, int softid, int platformid,
PeriodOptions period)
{
string sqlstr = "";
if (platformid != (int)MobileOption.None)
{
sqlstr =
@"select StatDate,NewUserCount,ActiveUserCount from (
select * from(
select StatDate,NewUserCount+NewUserCountFromCache+NewUserCount_Shanzhai-ifnull(NewUserCount_Shualiang,0) NewUserCount
,ActiveUserCount+ActiveUserCountFromCache+ActiveUserCount_Shanzhai ActiveUserCount
from U_StatUsers
where SoftID=?softid and Platform=?platform and Period=?periodid
order by NewUserCount desc
limit 1 ) A
union
select * from (
select StatDate,NewUserCount+NewUserCountFromCache+NewUserCount_Shanzhai-ifnull(NewUserCount_Shualiang,0) NewUserCount
,ActiveUserCount+ActiveUserCountFromCache+ActiveUserCount_Shanzhai ActiveUserCount
from U_StatUsers
where SoftID=?softid and Platform=?platform and Period=?periodid
order by ActiveUserCount desc
limit 1
) A ) B";
}
var parameters = new MySqlParameter[]
{
new MySqlParameter("?softid", softid),
new MySqlParameter("?platform", platformid),
new MySqlParameter("?periodid", (int)period)
};
List <SoftUser> softs = new List <SoftUser>();
using (MySqlCommand cmd = new MySqlCommand(sqlstr, new MySqlConnection(connString)))
{
cmd.Connection.Open();
cmd.CommandTimeout = 180;
cmd.Parameters.AddRange(parameters);
using (IDataReader dataReader = cmd.ExecuteReader())
{
while (dataReader.Read())
{
SoftUser softuser = new SoftUser();
if (dataReader["statdate"] != DBNull.Value && dataReader["statdate"] != null)
{
int temp = Convert.ToInt32(dataReader["statdate"]);
softuser.StatDate = new DateTime(temp / 10000, temp / 100 % 100, temp % 100, 0, 0, 0);
}
if (dataReader["newusercount"] != DBNull.Value && dataReader["newusercount"] != null)
{
softuser.NewNum = Convert.ToInt32(dataReader["newusercount"]);
}
if (dataReader["activeusercount"] != DBNull.Value && dataReader["activeusercount"] != null)
{
softuser.ActiveNum = Convert.ToInt32(dataReader["activeusercount"]);
}
softs.Add(softuser);
}
}
}
return(softs);
}
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)));
}