public static MsCrmResultObject GetCubeStatusList(Guid portalId, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); DateTime start = GeneralHelper.GetStartDateByScorePeriod(ScorePeriod.Monthly); DateTime end = GeneralHelper.GetEndDateByScorePeriod(ScorePeriod.Monthly); try { #region | SQL QUERY | string query = @"SELECT * INTO #temp FROM ( SELECT * ,DENSE_RANK() OVER (ORDER BY B.AllPoints DESC) AS Ranking FROM ( SELECT A.UserId ,ISNULL(SUM(A.Point),0) AS TotalPoint ,ISNULL(SUM(CASE WHEN A.ScoreType=100000012 THEN 0 ELSE A.Point END),0) AS AllPoints FROM ( SELECT DISTINCT u.new_userId AS UserId ,a.new_point AS Point ,a.new_scoreId AS AnswerId ,a.new_scoretype AS ScoreType FROM new_user AS u (NOLOCK) LEFT JOIN new_score AS a (NOLOCK) ON u.new_userId=a.new_userId --AND --a.CreatedOn BETWEEN @start AND @end AND a.statecode=0 JOIN new_new_user_new_role AS urd (NOLOCK) ON u.new_userId=urd.new_userid JOIN new_role AS rd (NOLOCK) ON urd.new_roleid =rd.new_roleId AND rd.statecode=0 AND rd.statuscode=1 --Active AND rd.new_portalId='{0}' WHERE u.statecode=0 AND u.statuscode=1 --Active ) AS A GROUP BY A.UserId ) AS B ) AS C SELECT t.* ,c.FullName AS UserIdName FROM #temp AS t (NOLOCK) JOIN new_user AS u (NOLOCK) ON t.UserId=u.new_userId JOIN Contact AS c (NOLOCK) ON u.new_contactId =c.ContactId ORDER BY t.Ranking ASC DROP TABLE #temp"; #endregion SqlParameter[] parameters = { new SqlParameter("@start", start) , new SqlParameter("@end", end) }; DataTable dt = sda.getDataTable(string.Format(query, portalId), parameters); if (dt != null && dt.Rows.Count > 0) { List <UserCubeStatus> returnList = new List <UserCubeStatus>(); for (int i = 0; i < dt.Rows.Count; i++) { UserCubeStatus _cubeStatus = new UserCubeStatus(); if (dt.Rows[i]["UserId"] != DBNull.Value) { EntityReference er = new EntityReference() { Id = (Guid)dt.Rows[i]["UserId"], Name = dt.Rows[i]["UserIdName"].ToString() }; _cubeStatus.UserId = er; } _cubeStatus.Point = dt.Rows[i]["TotalPoint"] != DBNull.Value ? Convert.ToInt32(dt.Rows[i]["TotalPoint"]) : 0; _cubeStatus.AllPoints = dt.Rows[i]["AllPoints"] != DBNull.Value ? Convert.ToInt32(dt.Rows[i]["AllPoints"]) : 0; _cubeStatus.Rank = dt.Rows[i]["Ranking"] != DBNull.Value ? Convert.ToInt32(dt.Rows[i]["Ranking"]) : 0; returnList.Add(_cubeStatus); } returnValue.Success = true; returnValue.ReturnObject = returnList; } else { returnValue.Success = false; returnValue.Result = "M033"; //"Soru durum listesi bulunamadı."; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return(returnValue); }
public static MsCrmResultObject GetUserCubeStatus(Guid portalUserId, Guid portalId, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); try { #region | SQL QUERY | string query = @"SELECT * FROM ( SELECT * ,DENSE_RANK() OVER (ORDER BY B.TotalPoint DESC) AS Ranking FROM ( SELECT A.UserId ,ISNULL(SUM(A.Point),0) AS TotalPoint FROM ( SELECT DISTINCT u.new_userId AS UserId ,a.new_point AS Point ,a.new_questionanswersId AS AnswerId FROM new_user AS u (NOLOCK) LEFT JOIN new_questionanswers AS a (NOLOCK) ON u.new_userId=a.new_userId AND a.statecode=0 JOIN new_new_user_new_role AS urd (NOLOCK) ON u.new_userId=urd.new_userid JOIN new_role AS rd (NOLOCK) ON urd.new_roleid=rd.new_roleId AND rd.statecode=0 AND rd.statuscode=1 --Active AND rd.new_portalId='{1}' WHERE u.statecode=0 AND u.statuscode=1 --Active ) AS A GROUP BY A.UserId ) AS B ) AS C WHERE C.UserId='{0}'"; #endregion DataTable dt = sda.getDataTable(string.Format(query, portalUserId, portalId)); if (dt != null && dt.Rows.Count > 0) { UserCubeStatus _cubeStatus = new UserCubeStatus(); if (dt.Rows[0]["UserId"] != DBNull.Value) { EntityReference er = new EntityReference() { Id = (Guid)dt.Rows[0]["UserId"] }; _cubeStatus.UserId = er; } _cubeStatus.Point = dt.Rows[0]["TotalPoint"] != DBNull.Value ? Convert.ToInt32(dt.Rows[0]["TotalPoint"]) : 0; _cubeStatus.Rank = dt.Rows[0]["Ranking"] != DBNull.Value ? Convert.ToInt32(dt.Rows[0]["Ranking"]) : 0; returnValue.Success = true; returnValue.ReturnObject = _cubeStatus; } else { returnValue.Success = false; returnValue.Result = "M032"; //"Kullanıcı soru cevaplarına ait bilgiler bulunamadı."; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObject GetUserCubeStatus(Guid portalUserId, Guid portalId, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); try { #region | SQL QUERY | string query = @"SELECT * FROM ( SELECT * ,DENSE_RANK() OVER (ORDER BY B.TotalPoint DESC) AS Ranking FROM ( SELECT A.UserId ,ISNULL(SUM(A.Point),0) AS TotalPoint FROM ( SELECT DISTINCT u.new_userId AS UserId ,a.new_point AS Point ,a.new_questionanswersId AS AnswerId FROM new_user AS u (NOLOCK) LEFT JOIN new_questionanswers AS a (NOLOCK) ON u.new_userId=a.new_userId AND a.statecode=0 JOIN new_new_user_new_role AS urd (NOLOCK) ON u.new_userId=urd.new_userid JOIN new_role AS rd (NOLOCK) ON urd.new_roleid=rd.new_roleId AND rd.statecode=0 AND rd.statuscode=1 --Active AND rd.new_portalId='{1}' WHERE u.statecode=0 AND u.statuscode=1 --Active ) AS A GROUP BY A.UserId ) AS B ) AS C WHERE C.UserId='{0}'" ; #endregion DataTable dt = sda.getDataTable(string.Format(query, portalUserId, portalId)); if (dt != null && dt.Rows.Count > 0) { UserCubeStatus _cubeStatus = new UserCubeStatus(); if (dt.Rows[0]["UserId"] != DBNull.Value) { EntityReference er = new EntityReference() { Id = (Guid)dt.Rows[0]["UserId"] }; _cubeStatus.UserId = er; } _cubeStatus.Point = dt.Rows[0]["TotalPoint"] != DBNull.Value ? Convert.ToInt32(dt.Rows[0]["TotalPoint"]) : 0; _cubeStatus.Rank = dt.Rows[0]["Ranking"] != DBNull.Value ? Convert.ToInt32(dt.Rows[0]["Ranking"]) : 0; returnValue.Success = true; returnValue.ReturnObject = _cubeStatus; } else { returnValue.Success = false; returnValue.Result = "M032"; //"Kullanıcı soru cevaplarına ait bilgiler bulunamadı."; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return(returnValue); }
public static MsCrmResultObject GetCubeStatusList(Guid portalId, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); DateTime start = GeneralHelper.GetStartDateByScorePeriod(ScorePeriod.Monthly); DateTime end = GeneralHelper.GetEndDateByScorePeriod(ScorePeriod.Monthly); try { #region | SQL QUERY | string query = @"SELECT * INTO #temp FROM ( SELECT * ,DENSE_RANK() OVER (ORDER BY B.TotalPoint DESC) AS Ranking FROM ( SELECT A.UserId ,ISNULL(SUM(A.Point),0) AS TotalPoint FROM ( SELECT DISTINCT u.new_userId AS UserId ,a.new_point AS Point ,a.new_scoreId AS AnswerId FROM new_user AS u (NOLOCK) LEFT JOIN new_score AS a (NOLOCK) ON u.new_userId=a.new_userId --AND --a.CreatedOn BETWEEN @start AND @end AND a.statecode=0 JOIN new_new_user_new_role AS urd (NOLOCK) ON u.new_userId=urd.new_userid JOIN new_role AS rd (NOLOCK) ON urd.new_roleid =rd.new_roleId AND rd.statecode=0 AND rd.statuscode=1 --Active AND rd.new_portalId='{0}' WHERE u.statecode=0 AND u.statuscode=1 --Active ) AS A GROUP BY A.UserId ) AS B ) AS C SELECT t.* ,c.FullName AS UserIdName FROM #temp AS t (NOLOCK) JOIN new_user AS u (NOLOCK) ON t.UserId=u.new_userId JOIN Contact AS c (NOLOCK) ON u.new_contactId =c.ContactId ORDER BY t.Ranking ASC DROP TABLE #temp"; #endregion SqlParameter[] parameters = { new SqlParameter("@start",start) ,new SqlParameter("@end",end) }; DataTable dt = sda.getDataTable(string.Format(query, portalId), parameters); if (dt != null && dt.Rows.Count > 0) { List<UserCubeStatus> returnList = new List<UserCubeStatus>(); for (int i = 0; i < dt.Rows.Count; i++) { UserCubeStatus _cubeStatus = new UserCubeStatus(); if (dt.Rows[i]["UserId"] != DBNull.Value) { EntityReference er = new EntityReference() { Id = (Guid)dt.Rows[i]["UserId"], Name = dt.Rows[i]["UserIdName"].ToString() }; _cubeStatus.UserId = er; } _cubeStatus.Point = dt.Rows[i]["TotalPoint"] != DBNull.Value ? Convert.ToInt32(dt.Rows[i]["TotalPoint"]) : 0; _cubeStatus.Rank = dt.Rows[i]["Ranking"] != DBNull.Value ? Convert.ToInt32(dt.Rows[i]["Ranking"]) : 0; returnList.Add(_cubeStatus); } returnValue.Success = true; returnValue.ReturnObject = returnList; } else { returnValue.Success = false; returnValue.Result = "M033"; //"Soru durum listesi bulunamadı."; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return returnValue; }