/// <summary>
/// 获取每一天
/// </summary>
/// <returns></returns>
public List <Sjqd_StatUsersByLan> GetLanByDates(DateTime begintime, DateTime endtime, int softid, int platform,
string lanname)
{
if (lanname == "未知语言")
{
lanname = "";
}
string key = BuildCacheKey("GetLanByDates", begintime, endtime, softid, platform, lanname);
List <Sjqd_StatUsersByLan> list = CacheHelper.Get <List <Sjqd_StatUsersByLan> >(key);
if (list == null)
{
// string cmdText = @"
// select @lanname E_Lan,sum(userscount) userCount,StatDate from
// (
// SELECT case when b.E_Lan='' or b.E_Lan is null then 0 else LanID end LanID,userscount,StatDate,
// isnull(b.E_Lan,'') E_Lan
// FROM
// (
// SELECT LanID,SUM(NewUserCount+ActiveUserCount) userscount ,StatDate
// FROM Sjqd_StatUsersByLan WITH(NOLOCK)
// WHERE Period=@period AND StatDate between @begintime and @endtime AND SoftID=@SoftID AND [Platform]=@Platform
// GROUP BY LanID,StatDate
// ) A
// LEFT JOIN
// Sjqd_Lan b WITH(NOLOCK) ON A.LanID=b.ID
// ) A
// where E_Lan=@lanname
// group by StatDate
// ORDER BY StatDate asc
// ";
// 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 = begintime.ToString("yyyyMMdd")},
// new SqlParameter(){ ParameterName = "@endtime", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = endtime.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)platform},
// new SqlParameter(){ ParameterName = "@lanname", SqlDbType = System.Data.SqlDbType.NVarChar, Size = 50, Value = lanname}
// };
var langlst = Sjqd_LanService.GetSjqd_LanList(lanname).Select(p => p.ID.ToString());
if (langlst.Count() <= 0)
{
return(list);
}
string sql = string.Format(@"
select StatDate,SUM(NewUserCount+ActiveUserCount) userscount,'{6}' E_Lan
from U_StatUsersByLan
WHERE Period={0} AND StatDate between {1} and {2} AND SoftID={3} AND Platform={4} and LangId in({5})
group by StatDate", 1, begintime.ToString("yyyyMMdd"),
endtime.ToString("yyyyMMdd"),
softid,
platform,
String.Join(",", langlst.ToArray()), lanname
);
list = new List <Sjqd_StatUsersByLan>();
using (var read = MySqlHelper.ExecuteReader((MySql_StatDbConn), sql))
{
while (read.Read())
{
int timedate = Convert.ToInt32(read["StatDate"]);
list.Add(new Sjqd_StatUsersByLan()
{
UseCount = Convert.ToInt32(read["userscount"]),
Lan = read["E_Lan"].ToString(),
StatDate = new DateTime(timedate / 10000, timedate % 10000 / 100, timedate % 100)
});
}
}
if (list.Count > 0)
{
CacheHelper.Set <List <Sjqd_StatUsersByLan> >(key, list, CacheTimeOption.TenMinutes);
}
}
return(list);
}
/// <summary>
/// 获取所有语言分布
/// </summary>
/// <param name="period"></param>
/// <param name="statDate"></param>
/// <param name="softId"></param>
/// <param name="platform"></param>
/// <returns></returns>
public List <Sjqd_StatUsersByLan> GetSoftLanTransverse(net91com.Stat.Core.PeriodOptions period, int statDate,
int softId, MobileOption platform)
{
string key = BuildCacheKey("GetSoftLanTransverse", period, statDate, softId, platform);
List <Sjqd_StatUsersByLan> list = CacheHelper.Get <List <Sjqd_StatUsersByLan> >(key);
if (list == null)
{
// string cmdText = @"
// select E_Lan,sum(userscount) userCount from
// (
// SELECT case when b.E_Lan='' or b.E_Lan is null then 0 else LanID end LanID,userscount,isnull(b.E_Lan,'') E_Lan
// FROM
// (
// SELECT LanID,SUM(NewUserCount+ActiveUserCount) userscount
// FROM Sjqd_StatUsersByLan WITH(NOLOCK)
// WHERE Period=@period AND StatDate = @StatDate AND SoftID=@SoftID AND [Platform]=@Platform
// GROUP BY LanID
// ) A
// LEFT JOIN
// Sjqd_Lan b WITH(NOLOCK) ON A.LanID=b.ID
// ) A
// group by E_Lan
// ORDER BY sum(userscount) DESC
// ";
// SqlParameter[] param = new SqlParameter[] {
// new SqlParameter(){ ParameterName = "@period", SqlDbType = System.Data.SqlDbType.TinyInt, Size = 1, Value = (int)period},
// new SqlParameter(){ ParameterName = "@StatDate", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = statDate},
// new SqlParameter(){ ParameterName = "@SoftID", SqlDbType = System.Data.SqlDbType.Int, Size = 4, Value = softId},
// new SqlParameter(){ ParameterName = "@Platform", SqlDbType = System.Data.SqlDbType.TinyInt, Size = 1, Value = (int)platform}
// };
string sql = string.Format(@"
select coalesce(LangId,0) langid,SUM(NewUserCount+ActiveUserCount) userscount
from U_StatUsersByLan
where Period={0} and statdate={1} and softid={2} and platform={3}
group by LangId
Order by userscount desc", (int)period, statDate, softId, (int)platform);
list = new List <Sjqd_StatUsersByLan>();
using (IDataReader read = MySqlHelper.ExecuteReader(MySql_StatDbConn, sql))
{
while (read.Read())
{
list.Add(new Sjqd_StatUsersByLan()
{
UseCount = Convert.ToInt32(read["userscount"]),
LanID = Convert.ToInt32(read["langid"])
});
}
}
int count = 0;
var listsjqd = Sjqd_LanService.GetSjqd_LanList("", "", 0, int.MaxValue, out count);
var reslst = (from itemStatUsersByLan in list
join itemlan in listsjqd on itemStatUsersByLan.LanID equals itemlan.ID into temp
from tt in temp.DefaultIfEmpty()
select new Sjqd_StatUsersByLan()
{
UseCount = itemStatUsersByLan.UseCount,
Lan = tt == null ? "" : tt.E_Lan
}).ToList();
var realreslst = (from item in reslst
group item by item.Lan
into g
select new Sjqd_StatUsersByLan()
{
Lan = g.Key,
UseCount = g.Sum(p => p.UseCount)
}).ToList();
if (realreslst.Count > 0)
{
CacheHelper.Set <List <Sjqd_StatUsersByLan> >(key, realreslst, CacheTimeOption.TenMinutes);
list = realreslst;
}
}
return(list);
}
