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