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);
}
public static MsCrmResultObject GetSurveyInfo(Guid surveyId, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string sqlQuery = @"SELECT
E.new_surveyId Id
,E.new_name Name
,E.new_startdate AS StartDate
,E.new_enddate AS EndDate
,E.new_portalId AS PortalId
,E.new_portalIdName AS PortalIdName
,CAST({2}.dbo.fn_UTCToTzSpecificLocalTime(E.CreatedOn, us.TimeZoneBias, us.TimeZoneDaylightBias,us.TimeZoneDaylightYear, us.TimeZoneDaylightMonth, us.TimeZoneDaylightDay, us.TimeZoneDaylightHour,us.TimeZoneDaylightMinute, us.TimeZoneDaylightSecond, 0, us.TimeZoneDaylightDayOfWeek,us.TimeZoneStandardBias, us.TimeZoneStandardYear, us.TimeZoneStandardMonth, us.TimeZoneStandardDay,us.TimeZoneStandardHour, us.TimeZoneStandardMinute, us.TimeZoneStandardSecond, 0,us.TimeZoneStandardDayOfWeek) as DATETIME) CreatedOn
FROM
new_survey AS E (NOLOCK)
JOIN
dbo.UserSettingsBase US (NOLOCK)
ON
US.SystemUserId ='{1}'
WHERE
E.new_surveyId='{0}'" ;
#endregion
DataTable dt = sda.getDataTable(string.Format(sqlQuery, surveyId, Globals.AdminId, Globals.DatabaseName));
if (dt.Rows.Count > 0)
{
Survey sur = new Survey();
sur.Id = (Guid)dt.Rows[0]["Id"];
sur.Name = dt.Rows[0]["Name"] != DBNull.Value ? dt.Rows[0]["Name"].ToString() : string.Empty;
if (dt.Rows[0]["PortalId"] != DBNull.Value)
{
EntityReference er = new EntityReference()
{
Id = (Guid)dt.Rows[0]["PortalId"],
Name = dt.Rows[0]["PortalIdName"].ToString()
};
sur.Portal = er;
}
if (dt.Rows[0]["StartDate"] != DBNull.Value)
{
sur.StartDate = (DateTime)dt.Rows[0]["StartDate"];
}
if (dt.Rows[0]["EndDate"] != DBNull.Value)
{
sur.EndDate = (DateTime)dt.Rows[0]["EndDate"];
}
MsCrmResultObject choiceRes = SurveyHelper.GetSurveyChoices(sur.Id, sda);
if (choiceRes.Success)
{
sur.SurveyChoices = (List <SurveyChoices>)choiceRes.ReturnObject;
}
returnValue.ReturnObject = sur;
returnValue.Success = true;
returnValue.Result = "Anket bilgisi çekildi.";
}
else
{
returnValue.Result = "Anket bilgisi bulunamadı.";
}
}
catch (Exception ex)
{
returnValue.Result = ex.Message;
}
return(returnValue);
}