public static MsCrmResultObject GetUserScoreDetails(Guid portalId, Guid portalUserId, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string query = @"SELECT
sc.new_scoreId AS Id
FROM
new_score AS sc (NOLOCK)
WHERE
sc.new_portalId=@PortalId
AND
sc.new_userId=@UserId
AND
sc.StateCode=0
ORDER BY
sc.CreatedOn DESC" ;
#endregion
SqlParameter[] parameters =
{
new SqlParameter("@PortalId", portalId)
, new SqlParameter("@UserId", portalUserId)
};
DataTable dt = sda.getDataTable(query, parameters);
if (dt != null && dt.Rows.Count > 0)
{
List <Score> returnList = new List <Score>();
for (int i = 0; i < dt.Rows.Count; i++)
{
Score _score = ScoreHelper.GetScoreDetail((Guid)dt.Rows[i]["Id"], sda);
if (_score != null && _score.Id != Guid.Empty)
{
returnList.Add(_score);
}
}
returnValue.Success = true;
returnValue.ReturnObject = returnList;
}
else
{
returnValue.Success = false;
returnValue.Result = "Puan kaydı bulunamadı.";
}
}
catch (Exception ex)
{
returnValue.Success = false;
returnValue.Result = ex.Message;
}
return(returnValue);
}
public static MsCrmResultObject SelectSurvey(Guid portalId, Guid portalUserId, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
bool isLimitOver = false;
try
{
#region | CHECK LIMIT |
List <ScoreLimit> lstLimits = new List <ScoreLimit>();
MsCrmResultObject limitRes = ScoreHelper.GetScoreLimitsByType(ScoreType.Survey, sda);
if (limitRes.Success)
{
lstLimits = (List <ScoreLimit>)limitRes.ReturnObject;
for (int i = 0; i < lstLimits.Count; i++)
{
int recCount = 0;
DateTime start = GeneralHelper.GetStartDateByScorePeriod(lstLimits[i].Period);
DateTime end = GeneralHelper.GetEndDateByScorePeriod(lstLimits[i].Period);
recCount = SurveyHelper.GetUserSurveyAnswerCount(portalId, portalUserId, start, end, sda);
if (lstLimits[i].Frequency <= recCount)
{
returnValue.Result = "Anket cevaplama limitiniz dolmuştur.<br /> Limitler hakkında bilgiye Puanlarım bölümünden ulaşabilirsiniz.";
isLimitOver = true;
break;
}
}
}
if (isLimitOver)
{
return(returnValue);
}
#endregion
#region | SQL QUERY |
string sqlQuery = @"DECLARE @Date DATETIME = GETUTCDATE()
SELECT DISTINCT
E.new_surveyId Id
FROM
new_survey AS E (NOLOCK)
JOIN
new_new_user_new_role AS UR (NOLOCK)
ON
UR.new_userid='{1}'
JOIN
new_role AS RD (NOLOCK)
ON
RD.new_roleId=UR.new_roleid
AND
Rd.statecode=0
AND
RD.statuscode=1 --Active
JOIN
new_new_survey_new_role AS ERDF (NOLOCK)
ON
ERDF.new_surveyid =E.new_surveyId
AND
ERDF.new_roleid =RD.new_roleId
JOIN
dbo.UserSettingsBase US (NOLOCK)
ON
US.SystemUserId ='{2}'
WHERE
@Date BETWEEN E.new_startdate AND E.new_enddate
AND
E.new_portalId = '{0}'
AND
E.statuscode=1 --Active
AND
E.new_surveyId NOT IN
(
SELECT
a.new_surveyId
FROM
new_surveyanswer AS a (NOLOCK)
WHERE
a.new_surveyId =E.new_surveyId
AND
a.new_userId='{1}'
AND
a.statecode=0
)" ;
#endregion
DataTable dt = sda.getDataTable(string.Format(sqlQuery, portalId, portalUserId, Globals.AdminId));
if (dt.Rows.Count > 0)
{
returnValue = SurveyHelper.GetSurveyInfo((Guid)dt.Rows[0]["Id"], sda);
}
else
{
returnValue.Result = "M052"; //"Herhangi bir anket kaydı bulunamadı.";
}
}
catch (Exception ex)
{
returnValue.Result = ex.Message;
}
return(returnValue);
}