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