/// <summary> /// 获取每一天 /// </summary> /// <returns></returns> public List <Sjqd_StatUsersByGjbb> GetGjbbByDates(DateTime begintime, DateTime endtime, int softid, int platform, string gjbb) { if (gjbb == "未适配固件") { gjbb = ""; } string key = BuildCacheKey("GetGjbbByDates", begintime, endtime, softid, platform, gjbb); List <Sjqd_StatUsersByGjbb> list = CacheHelper.Get <List <Sjqd_StatUsersByGjbb> >(key); if (list == null) { var gjbblst = Sjqd_GJBBService.GetSjqd_GJBBList(platform, gjbb).Select(p => p.ID.ToString()); if (gjbblst.Count() <= 0) { return(list); } String sql = string.Format(@" SELECT StatDate,SUM(NewUserCount+ActiveUserCount) userscount,'{6}' E_GJBB FROM U_StatUsersByGjbb WHERE Period={0} AND StatDate between {1} and {2} AND SoftID={3} AND Platform={4} and gjbbid in({5}) GROUP BY StatDate" , 1, begintime.ToString("yyyyMMdd"), endtime.ToString("yyyyMMdd"), softid, platform, String.Join(",", gjbblst.ToArray()), gjbb); list = new List <Sjqd_StatUsersByGjbb>(); using (var read = MySqlHelper.ExecuteReader(MySql_StatDbConn, sql)) { while (read.Read()) { int timedate = Convert.ToInt32(read["StatDate"]); list.Add(new Sjqd_StatUsersByGjbb() { UseCount = Convert.ToInt32(read["userscount"]), Gjbb = read["E_GJBB"].ToString(), StatDate = new DateTime(timedate / 10000, timedate % 10000 / 100, timedate % 100) }); } } if (list.Count > 0) { CacheHelper.Set <List <Sjqd_StatUsersByGjbb> >(key, list, CacheTimeOption.TenMinutes, CacheExpirationOption.AbsoluteExpiration); } } return(list); }
public List <Sjqd_StatUsersByGjbb> GetSoftGJBBTransverse(net91com.Stat.Core.PeriodOptions period, int statDate, int softId, MobileOption platform) { string key = BuildCacheKey("GetSoftGJBBTransverse", period, statDate, softId, platform); List <Sjqd_StatUsersByGjbb> list = CacheHelper.Get <List <Sjqd_StatUsersByGjbb> >(key); if (list == null) { // string cmdText = @"select E_GJBB,sum(userscount) userCount from // ( // SELECT case when b.E_GJBB='' or b.E_GJBB is null then 0 else GjbbID end GjbbID,userscount,isnull(b.E_GJBB,'') E_GJBB // FROM // ( // SELECT GjbbID,SUM(NewUserCount+ActiveUserCount) userscount // FROM Sjqd_StatUsersByGjbb WITH(NOLOCK) // WHERE Period=@period AND StatDate = @StatDate AND SoftID=@SoftID AND [Platform]=@Platform // GROUP BY GjbbID // ) A // LEFT JOIN // Sjqd_GJBB b WITH(NOLOCK) ON A.GjbbID=b.ID // ) A // group by E_GJBB // 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 GjbbID,SUM(NewUserCount+ActiveUserCount) userscount FROM U_StatUsersByGjbb WHERE Period={0} AND StatDate ={1} AND SoftID={2} AND Platform={3} GROUP BY GjbbID Order by userscount desc", (int)period, statDate, softId, (int)platform); list = new List <Sjqd_StatUsersByGjbb>(); using (IDataReader read = MySqlHelper.ExecuteReader((MySql_StatDbConn), sql)) { while (read.Read()) { list.Add(new Sjqd_StatUsersByGjbb() { UseCount = Convert.ToInt32(read["userscount"]), GjbbID = Convert.ToInt32(read["GjbbID"]) }); } } int count = 0; var gjbblst = Sjqd_GJBBService.GetSjqd_GJBBList((int)platform, "", "", 0, int.MaxValue, out count); var reslst = (from sjqdStatUsersByGjbb in list join sjqdGjbb in gjbblst on sjqdStatUsersByGjbb.GjbbID equals sjqdGjbb.ID into os from tt in os.DefaultIfEmpty() select new Sjqd_StatUsersByGjbb() { UseCount = sjqdStatUsersByGjbb.UseCount, Gjbb = tt == null ? "" : tt.E_GJBB }).ToList(); var realreslst = (from item in reslst group item by item.Gjbb into g select new Sjqd_StatUsersByGjbb() { Gjbb = g.Key, UseCount = g.Sum(p => p.UseCount) }).OrderByDescending(p => p.UseCount).ToList(); if (realreslst.Count > 0) { CacheHelper.Set <List <Sjqd_StatUsersByGjbb> >(key, realreslst, CacheTimeOption.TenMinutes); list = realreslst; } } return(list); }