public static Annotation GetAnnotationDetail(Guid annotationId, SqlDataAccess sda)
{
Annotation returnValue = new Annotation();
try
{
#region | SQL QUERY |
string query = @"SELECT
A.AnnotationId
,A.MimeType
,A.FileName
,A.DocumentBody
FROM
Annotation A
WHERE
A.AnnotationId = '{0}'";
#endregion
DataTable dt = sda.getDataTable(string.Format(query, annotationId));
if (dt != null && dt.Rows.Count > 0)
{
returnValue.AnnotationId = (Guid)dt.Rows[0]["AnnotationId"];
returnValue.MimeType = dt.Rows[0]["MimeType"] != DBNull.Value ? dt.Rows[0]["MimeType"].ToString() : string.Empty;
returnValue.FileName = dt.Rows[0]["FileName"] != DBNull.Value ? dt.Rows[0]["FileName"].ToString() : string.Empty;
returnValue.File = dt.Rows[0]["DocumentBody"] != DBNull.Value ? dt.Rows[0]["DocumentBody"].ToString() : string.Empty;
}
}
catch (Exception)
{
}
return returnValue;
}
public static MsCrmResultObject GetPointCodeInfo(Guid pointCodeId, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string sqlQuery = @"SELECT
pc.new_pointcodeId AS Id
,pc.new_name AS Name
,pc.new_portalid AS PortalId
,pc.new_portalidName AS PortalIdName
,'new_portal' AS PortalIdTypeName
,pc.new_group AS GroupCode
,pc.new_code AS Code
,pc.new_point AS Point
,pc.statuscode AS Status
FROM
new_pointcode AS pc (NOLOCK)
WHERE
pc.new_pointcodeId='{0}'";
#endregion
DataTable dt = sda.getDataTable(string.Format(sqlQuery, pointCodeId));
if (dt.Rows.Count > 0)
{
List<PointCode> pCode = dt.ToList<PointCode>();
returnValue.Success = true;
returnValue.ReturnObject = pCode[0];
}
else
{
returnValue.Result = "Kayıt bulunamadı.";
}
}
catch (Exception ex)
{
returnValue.Result = ex.Message;
}
return returnValue;
}
public static MsCrmResultObj<List<Town>> GetAllTowns(SqlDataAccess sda)
{
MsCrmResultObj<List<Town>> returnValue = new MsCrmResultObj<List<Town>>();
try
{
#region | SQL QUERY |
string sqlQuery = @"SELECT
t.new_townId AS Id
,t.new_name AS Name
,t.new_towncode AS Code
,t.new_cityId AS CityId
,t.new_cityIdName AS CityIdName
,'new_city' AS CityIdTypeName
FROM
new_town AS t (NOLOCK)
JOIN
new_city AS c (NOLOCK)
ON
t.new_cityId=c.new_cityId";
#endregion
DataTable dt = sda.getDataTable(string.Format(sqlQuery));
if (dt.Rows.Count > 0)
{
List<Town> townList = dt.ToList<Town>();
returnValue.Success = true;
returnValue.ReturnObject = townList;
}
else
{
returnValue.Result = "Kayıt bulunamadı.";
}
}
catch (Exception ex)
{
returnValue.Result = ex.Message;
returnValue.HasException = true;
}
return returnValue;
}
public static MsCrmResultObject GetArticleCategoryInfo(Guid categoryId, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string query = @"SELECT DISTINCT
E.new_articlecategoryId AS Id
,E.new_name Name
,E.new_portalId PortalId
,E.new_portalIdName PortalIdName
,E.new_imageurl AS ImageUrl
FROM
new_articlecategory AS E (NOLOCK)
WHERE
E.new_articlecategoryId = '{0}'
AND
E.statuscode=1 --Active";
#endregion
DataTable dt = sda.getDataTable(string.Format(query, categoryId));
if (dt != null && dt.Rows.Count > 0)
{
EntityReference er = new EntityReference()
{
Id = (Guid)dt.Rows[0]["Id"],
Name = dt.Rows[0]["Name"].ToString(),
LogicalName = dt.Rows[0]["ImageUrl"] != DBNull.Value ? dt.Rows[0]["ImageUrl"].ToString() : "no_image_available.png"
};
returnValue.Success = true;
returnValue.ReturnObject = er;
}
else
{
returnValue.Success = false;
returnValue.Result = "M024";
}
}
catch (Exception ex)
{
returnValue.Success = false;
returnValue.Result = ex.Message;
}
return returnValue;
}
public static MsCrmResult GetPortalId(string url, SqlDataAccess sda)
{
MsCrmResult returnValue = new MsCrmResult();
try
{
#region | SQL QUERY |
string query = @"SELECT
u.new_portalId AS BrandId
FROM
new_portalurl AS u (NOLOCK)
JOIN
new_portal AS b (NOLOCK)
ON
b.new_portalId=u.new_portalId
AND
b.statecode=0
AND
b.statuscode=1 --Active
WHERE
u.new_name='{0}'
AND
u.statecode=0";
#endregion
DataTable dt = sda.getDataTable(string.Format(query, url));
if (dt != null && dt.Rows.Count > 0)
{
returnValue.CrmId = (Guid)dt.Rows[0]["BrandId"];
returnValue.Success = true;
}
else
{
returnValue.Result = "M004"; //"Girmiş olduğunuz adres herhangi bir portala ait değil.";
}
}
catch (Exception ex)
{
returnValue.Success = false;
returnValue.Result = ex.Message;
}
return returnValue;
}
public static MsCrmResult CheckIsUserYourFriend(Guid portalId, Guid portalUserId, Guid selectedUserId, SqlDataAccess sda)
{
MsCrmResult returnValue = new MsCrmResult();
try
{
#region | SQL QUERY |
string query = @"SELECT
f.new_friendshipId AS Id
FROM
new_friendship AS f (NOLOCK)
WHERE
f.new_portalId='{0}'
AND
f.statecode=0
AND
(
(f.new_partyoneId='{1}' AND f.new_partytwoId='{2}')
OR
(f.new_partyoneId='{2}' AND f.new_partytwoId='{1}')
)";
#endregion
DataTable dt = sda.getDataTable(string.Format(query, portalId, portalUserId, selectedUserId));
if (dt != null && dt.Rows.Count > 0)
{
returnValue.Success = true;
returnValue.CrmId = (Guid)dt.Rows[0]["Id"];
}
else
{
returnValue.Success = false;
returnValue.Result = "M039"; //"Kullanıcı arkadaşınız değildir!";
}
}
catch (Exception ex)
{
returnValue.Success = false;
returnValue.Result = ex.Message;
}
return returnValue;
}
public static MsCrmResult LoginControl(Guid portalId, string userName, string password, SqlDataAccess sda)
{
MsCrmResult returnValue = new MsCrmResult();
try
{
#region | SQL QUERY |
string query = @"SELECT
U.new_userId UserId
FROM
new_user U (NoLock)
WHERE
U.new_name = '{0}'
AND
U.new_password = '******'
AND
U.statecode = 0
AND
U.statuscode = {2}";
#endregion
DataTable dt = sda.getDataTable(string.Format(query, userName, password, (int)PortalUserStatus.Active));
if (dt != null && dt.Rows.Count > 0)
{
returnValue.CrmId = (Guid)dt.Rows[0]["UserId"];
MsCrmResultObject roleResult = PortalUserHelper.GetPortalUserRoles(portalId, returnValue.CrmId, sda);
returnValue.Success = roleResult.Success;
returnValue.Result = roleResult.Result;
}
else
{
returnValue.Success = false;
returnValue.Result = "M035"; //"Hatalı kullanıcı adı veya şifre!";
}
}
catch (Exception ex)
{
throw ex;
}
return returnValue;
}
public static MsCrmResult GetPageContent(Guid portalId, PageNames pageName, SqlDataAccess sda)
{
MsCrmResult returnValue = new MsCrmResult();
try
{
#region | SQL QUERY |
string sqlQuery = @"SELECT
pc.new_content AS Content
FROM
new_pagecontent AS pc (NOLOCK)
WHERE
pc.new_portalId='{0}'
AND
pc.new_page={1}
AND
pc.statecode=0";
#endregion
DataTable dt = sda.getDataTable(string.Format(sqlQuery, portalId, ((int)pageName).ToString()));
if (dt.Rows.Count > 0)
{
if (dt.Rows[0]["Content"] != DBNull.Value)
{
returnValue.Success = true;
returnValue.Result = dt.Rows[0]["Content"].ToString();
}
}
else
{
returnValue.Result = "M051"; //"Sayfa içeriği hazırlanmamıştır.";
}
}
catch (Exception ex)
{
returnValue.Result = ex.Message;
}
return returnValue;
}
public static MsCrmResultObj<List<City>> GetCities(SqlDataAccess sda)
{
MsCrmResultObj<List<City>> returnValue = new MsCrmResultObj<List<City>>();
try
{
#region | SQL QUERY |
string sqlQuery = @"SELECT
c.new_cityId AS Id
,c.new_name AS Name
,c.new_citycode AS Code
FROM
new_city AS c (NOLOCK)
WHERE
c.new_countryId='F55A5B6D-B32E-E511-80C4-000D3A216510'";
#endregion
DataTable dt = sda.getDataTable(sqlQuery);
if (dt.Rows.Count > 0)
{
List<City> cityList = dt.ToList<City>();
returnValue.Success = true;
returnValue.ReturnObject = cityList;
}
else
{
returnValue.Result = "Kayıt bulunamadı.";
}
}
catch (Exception ex)
{
returnValue.Result = ex.Message;
}
return returnValue;
}
public static MsCrmResultObject GetQuestionLevels(Guid portalId, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string query = @"SELECT
ql.new_questionlevelId AS Id
,ql.new_name AS Name
,ql.new_portalId AS PortalId
,ql.new_portalIdName AS PortalIdName
,ql.new_imageurl AS ImageUrl
FROM
new_questionlevel AS ql (NOLOCK)
WHERE
ql.new_portalId='{0}'
AND
ql.statecode=0
AND
ql.statuscode=1 --Active";
#endregion
DataTable dt = sda.getDataTable(string.Format(query, portalId));
if (dt != null && dt.Rows.Count > 0)
{
List<QuestionLevel> returnList = new List<QuestionLevel>();
for (int i = 0; i < dt.Rows.Count; i++)
{
QuestionLevel _questionLevel = new QuestionLevel();
_questionLevel.Id = (Guid)dt.Rows[i]["Id"];
_questionLevel.Name = dt.Rows[i]["Name"] != DBNull.Value ? dt.Rows[i]["Name"].ToString() : string.Empty;
_questionLevel.ImagePath = dt.Rows[i]["ImageUrl"] != DBNull.Value ? dt.Rows[i]["ImageUrl"].ToString() : "no_image_available.png";
if (dt.Rows[i]["PortalId"] != DBNull.Value)
{
EntityReference er = new EntityReference()
{
Id = (Guid)dt.Rows[i]["PortalId"],
Name = dt.Rows[i]["PortalIdName"].ToString()
};
_questionLevel.Portal = er;
}
returnList.Add(_questionLevel);
}
returnValue.Success = true;
returnValue.ReturnObject = returnList;
}
else
{
returnValue.Success = false;
returnValue.Result = "M027"; //"Herhangi bir soru seviyesi tanımı bulunamadı.";
}
}
catch (Exception ex)
{
returnValue.Success = false;
returnValue.Result = ex.Message;
}
return returnValue;
}
public static MsCrmResultObject GetQuestionInfo(Guid questionId, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string query = @"SELECT
q.new_questionId AS Id
,q.new_name AS Name
,q.new_portalId AS PortalId
,q.new_portalIdName AS PortalIdName
,q.new_questionlevelId AS LevelId
,q.new_questionlevelIdName AS LevelIdName
--,q.new_portal_rubic_cube_definitionid AS DefinationId
--,q.new_portal_rubic_cube_definitionidName AS DefinationIdName
,q.new_category AS QuestionCategoryValue
,NULL AS QuestionCategoryName
,q.new_timecount AS [Time]
,q.new_point AS [Point]
FROM
new_question AS q (NOLOCK)
WHERE
q.new_questionId='{0}'
AND
q.statecode=0
AND
q.statuscode=1 --Active";
#endregion
DataTable dt = sda.getDataTable(string.Format(query, questionId));
if (dt != null && dt.Rows.Count > 0)
{
Question _question = new Question();
_question.Id = (Guid)dt.Rows[0]["Id"];
_question.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()
};
_question.Portal = er;
}
if (dt.Rows[0]["LevelId"] != DBNull.Value)
{
EntityReference er = new EntityReference()
{
Id = (Guid)dt.Rows[0]["LevelId"],
Name = dt.Rows[0]["LevelIdName"].ToString()
};
_question.QuestionLevel = er;
}
//if (dt.Rows[0]["DefinationId"] != DBNull.Value)
//{
// EntityReference er = new EntityReference()
// {
// Id = (Guid)dt.Rows[0]["DefinationId"],
// Name = dt.Rows[0]["DefinationIdName"].ToString()
// };
// _question.QuestionDefination = er;
//}
if (dt.Rows[0]["QuestionCategoryValue"] != DBNull.Value)
{
StringMap sm = new StringMap()
{
Value = (int)dt.Rows[0]["QuestionCategoryValue"],
Name = dt.Rows[0]["QuestionCategoryName"] != DBNull.Value ? dt.Rows[0]["QuestionCategoryName"].ToString() : string.Empty
};
_question.QuestionCategory = sm;
}
_question.Time = dt.Rows[0]["Time"] != DBNull.Value ? (int)dt.Rows[0]["Time"] : 0;
_question.Point = dt.Rows[0]["Point"] != DBNull.Value ? (int)dt.Rows[0]["Point"] : 0;
_question.QuestionChoices = (List<QuestionChoices>)GetQuestionChoices(_question.Id, sda).ReturnObject;
returnValue.Success = true;
returnValue.ReturnObject = _question;
}
else
{
returnValue.Success = false;
returnValue.Result = "M028"; //"Soruya ait bilgi 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;
}
public static MsCrmResultObject GetQuestionChoices(Guid questionId, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string query = @"SELECT
c.new_questionchoiceId AS Id
,c.new_name AS Name
,c.new_iscorrect AS IsCorrect
,c.new_questionId AS QuestionId
,c.new_questionIdName AS QUestionIdName
FROM
new_questionchoice AS c (NOLOCK)
WHERE
c.new_questionId='{0}'
AND
c.statecode=0 ORDER BY NEWID()";
#endregion
DataTable dt = sda.getDataTable(string.Format(query, questionId));
if (dt != null && dt.Rows.Count > 0)
{
List<QuestionChoices> returnList = new List<QuestionChoices>();
for (int i = 0; i < dt.Rows.Count; i++)
{
QuestionChoices _questionChoices = new QuestionChoices();
_questionChoices.Id = (Guid)dt.Rows[i]["Id"];
_questionChoices.Name = dt.Rows[i]["Name"] != DBNull.Value ? dt.Rows[i]["Name"].ToString() : string.Empty;
if (dt.Rows[i]["QuestionId"] != DBNull.Value)
{
EntityReference er = new EntityReference()
{
Id = (Guid)dt.Rows[i]["QuestionId"],
Name = dt.Rows[i]["QuestionIdName"].ToString()
};
_questionChoices.Question = er;
}
_questionChoices.IsCorrect = dt.Rows[i]["IsCorrect"] != DBNull.Value ? (bool)dt.Rows[i]["IsCorrect"] : false;
returnList.Add(_questionChoices);
}
returnValue.Success = true;
returnValue.ReturnObject = returnList;
}
else
{
returnValue.Success = false;
returnValue.Result = "M030"; //"Soruya ait şıklar bulunamadı.";
}
}
catch (Exception ex)
{
returnValue.Success = false;
returnValue.Result = ex.Message;
}
return returnValue;
}
public static MsCrmResultObject GetSurveyChoices(Guid surveyId, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string query = @"SELECT
c.new_surveychoiceId AS Id
,c.new_name AS Name
,c.new_surveyId AS SurveyId
,c.new_surveyIdName AS SurveyIdName
FROM
new_surveychoice AS c (NOLOCK)
WHERE
c.new_surveyId='{0}'
AND
c.statecode=0";
#endregion
DataTable dt = sda.getDataTable(string.Format(query, surveyId));
if (dt != null && dt.Rows.Count > 0)
{
List<SurveyChoices> returnList = new List<SurveyChoices>();
for (int i = 0; i < dt.Rows.Count; i++)
{
SurveyChoices surveyChoices = new SurveyChoices();
surveyChoices.Id = (Guid)dt.Rows[i]["Id"];
surveyChoices.Name = dt.Rows[i]["Name"] != DBNull.Value ? dt.Rows[i]["Name"].ToString() : string.Empty;
if (dt.Rows[i]["SurveyId"] != DBNull.Value)
{
EntityReference er = new EntityReference()
{
Id = (Guid)dt.Rows[i]["SurveyId"],
Name = dt.Rows[i]["SurveyIdName"].ToString()
};
surveyChoices.Survey = er;
}
returnList.Add(surveyChoices);
}
returnValue.Success = true;
returnValue.ReturnObject = returnList;
}
else
{
returnValue.Success = false;
returnValue.Result = "Ankete ait şıklar bulunamadı.";
}
}
catch (Exception ex)
{
returnValue.Success = false;
returnValue.Result = ex.Message;
}
return returnValue;
}
public static MsCrmResultObject SearchContact(Guid portalId, Guid userId, string key, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string query = @"SELECT DISTINCT
u.new_userId AS UserId
,u.new_name AS UserName
,u.new_imageurl AS ImageUrl
,c.FullName
,c.JobTitle
,CASE WHEN fr.new_friendshipId IS NOT NULL THEN 1 ELSE 0 END AS IsFriend
FROM
new_user AS u (NOLOCK)
JOIN
new_new_user_new_role AS ur (NOLOCK)
ON
ur.new_userid=u.new_userId
JOIN
new_role AS r (NOLOCK)
ON
ur.new_roleid=r.new_roleId
AND
r.statecode=0
AND
r.statuscode=1 --Active
AND
r.new_portalId='{0}'
JOIN
Contact AS c (NOLOCK)
ON
u.new_contactId=c.ContactId
LEFT JOIN
new_friendship AS fr (NOLOCK)
ON
'{2}' IN (fr.new_partyOneId,fr.new_partyTwoId)
AND
u.new_userId IN (fr.new_partyOneId,fr.new_partyTwoId)
AND
fr.statecode=0
WHERE
u.statecode=0
AND
u.new_userId!='{2}'
AND
u.statuscode=1 --Active
AND
c.FullName LIKE '%{1}%'";
#endregion
DataTable dt = sda.getDataTable(string.Format(query, portalId, key, userId));
if (dt != null && dt.Rows.Count > 0)
{
List<UserFriends> lstUser = new List<UserFriends>();
for (int i = 0; i < dt.Rows.Count; i++)
{
UserFriends uf = new UserFriends()
{
UserId = (Guid)dt.Rows[i]["UserId"],
UserName = dt.Rows[i]["UserName"].ToString(),
FullName = dt.Rows[i]["FullName"].ToString(),
ImageUrl = dt.Rows[i]["ImageUrl"] != DBNull.Value ? dt.Rows[i]["ImageUrl"].ToString() : "nouserprofile.jpg",
JobTitle = dt.Rows[i]["JobTitle"] != DBNull.Value ? dt.Rows[i]["JobTitle"].ToString() : "---",
UserType = (int)dt.Rows[i]["IsFriend"]
};
lstUser.Add(uf);
}
returnValue.Success = true;
returnValue.ReturnObject = lstUser;
}
else
{
returnValue.Success = false;
returnValue.Result = "M057"; //"Herhangi bir kayıt bulunamadı!";
}
}
catch (Exception ex)
{
returnValue.Success = false;
returnValue.Result = ex.Message;
}
return returnValue;
}
public static string GetCityCode(Guid cityId, SqlDataAccess sda)
{
string returnValue = string.Empty;
#region | SQL QUERY |
string sqlQuery = @"SELECT c.new_citycode AS Code FROM new_city AS c (NOLOCK) WHERE c.new_cityId='{0}'";
#endregion
DataTable dt = sda.getDataTable(string.Format(sqlQuery, cityId));
if (dt.Rows.Count > 0)
{
if (dt.Rows[0]["Code"] != DBNull.Value)
{
returnValue = dt.Rows[0]["Code"].ToString();
}
}
return returnValue;
}
public static MsCrmResultObject GetAnnouncementInfo(Guid announcementId, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string query = @"SELECT
A.new_announcementId Id
,A.new_name Name
,A.new_content Content
,A.new_imageurl ImageUrl
,CAST({2}.dbo.fn_UTCToTzSpecificLocalTime(A.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_announcement A (NoLock)
INNER JOIN
SystemUser SU (NoLock)
ON
SU.SystemUserId = '{1}'
INNER JOIN
dbo.UserSettingsBase US (NoLock)
ON
US.SystemUserId =SU.SystemUserId
WHERE
A.new_announcementId = '{0}'
ORDER BY
A.CreatedOn DESC";
#endregion
DataTable dt = sda.getDataTable(string.Format(query, announcementId, Globals.AdminId, Globals.DatabaseName));
if (dt != null && dt.Rows.Count > 0)
{
Announcement _announcement = new Announcement();
_announcement.AnnouncementId = (Guid)dt.Rows[0]["Id"];
_announcement.Caption = dt.Rows[0]["Name"] != DBNull.Value ? dt.Rows[0]["Name"].ToString() : string.Empty;
_announcement.Description = dt.Rows[0]["Content"] != DBNull.Value ? dt.Rows[0]["Content"].ToString() : string.Empty;
_announcement.ImagePath = dt.Rows[0]["ImageUrl"] != DBNull.Value ? dt.Rows[0]["ImageUrl"].ToString() : "no_image_available.png";
if (dt.Rows[0]["CreatedOn"] != DBNull.Value)
{
_announcement.CreatedOnString = dt.Rows[0]["CreatedOn"] != DBNull.Value ? ((DateTime)dt.Rows[0]["CreatedOn"]).ToString("dd MMMM yyyy ddddd HH:mm", new CultureInfo("tr-TR", false)) : string.Empty;
_announcement.CreatedOn = (DateTime)dt.Rows[0]["CreatedOn"];
}
#region | GET ATTACHMENTS |
MsCrmResultObject attachmentResult = AttachmentFileHelper.GetAnnouncementAttachmentFiles(announcementId, sda);
if (attachmentResult.Success)
{
_announcement.AttachmentFileList = (List<AttachmentFile>)attachmentResult.ReturnObject;
}
#endregion
returnValue.Success = true;
returnValue.ReturnObject = _announcement;
}
else
{
returnValue.Result = "M026"; //"İlgili duyuruya ait bilgiye ulaşılamadı.";
}
}
catch (Exception ex)
{
returnValue.Success = false;
returnValue.Result = ex.Message;
}
return returnValue;
}
public static MsCrmResultObject GetEntityLikeInfo(Guid entityId, string entityName, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string query = @" DECLARE @LikeCount INT
DECLARE @DislikeCount INT
SELECT
@LikeCount= COUNT(0)
FROM
new_likerecord AS l (NOLOCK)
WHERE
l.{1}Id='{0}'
AND
l.new_liketype=1
AND
l.statecode=0
SELECT
@DislikeCount= COUNT(0)
FROM
new_likerecord AS l (NOLOCK)
WHERE
l.{1}Id='{0}'
AND
l.new_liketype=0
AND
l.statecode=0
SELECT
@LikeCount AS LikeCount
,@DislikeCount AS DislikeCount";
#endregion
DataTable dt = sda.getDataTable(string.Format(query, entityId, entityName));
if (dt != null && dt.Rows.Count > 0)
{
List<Education> returnList = new List<Education>();
LikeInfo _like = new LikeInfo();
_like.LikeCount = (int)dt.Rows[0]["LikeCount"];
_like.DislikeCount = (int)dt.Rows[0]["DislikeCount"];
_like.Entity = new EntityReference(entityName, entityId);
returnValue.Success = true;
returnValue.ReturnObject = _like;
}
else
{
returnValue.Success = false;
returnValue.Result = "M056"; //"Beğeni kaydı bulunamadı!";
}
}
catch (Exception ex)
{
returnValue.Success = false;
returnValue.Result = ex.Message;
}
return returnValue;
}
public static MsCrmResultObject GetDiscoveryFormInfo(Guid discoveryFormId, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string sqlQuery = @"SELECT
pc.new_discoveryformId AS Id
,pc.new_name AS Name
,pc.new_firstname AS FirstName
,pc.new_lastname AS LastName
,pc.new_phonenumber AS PhoneNumber
,pc.new_email AS Email
,pc.new_cityid AS CityId
,pc.new_cityidName AS CityIdName
,'new_city' AS CityIdTypeName
,pc.new_townid AS TownId
,pc.new_townidName AS TownIdName
,'new_town' AS TownIdTypeName
,pc.new_userid AS UserId
,pc.new_useridName AS UserIdName
,'new_user' AS UserIdTypeName
,pc.new_portalid AS PortalId
,pc.new_portalidName AS PortalIdName
,'new_portal' AS PortalIdTypeName
,pc.new_hometype AS HomeType
,pc.new_informedby AS InformedBy
,pc.new_visithour AS VisitHour
,pc.new_visitdate AS VisitDate
,pc.new_formcode AS FormCode
,pc.statuscode AS Status
,pc.CreatedOn AS CreatedOn
FROM
new_discoveryform AS pc (NOLOCK)
WHERE
pc.new_discoveryformid='{0}'";
#endregion
DataTable dt = sda.getDataTable(string.Format(sqlQuery, discoveryFormId));
if (dt.Rows.Count > 0)
{
List<DiscoveryForm> discoveryForm = dt.ToList<DiscoveryForm>();
returnValue.Success = true;
returnValue.ReturnObject = discoveryForm[0];
}
else
{
returnValue.Result = "Kayıt bulunamadı.";
}
}
catch (Exception ex)
{
returnValue.Result = ex.Message;
}
return returnValue;
}
public static MsCrmResultObject GetGiftReuqestListByStatus(DiscoveryFormStatus discoveryFormStatus, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string sqlQuery = @"DECLARE @objectTypeCode INT
SELECT
TOP 1
@objectTypeCode=e.ObjectTypeCode
FROM
Entity AS e (NOLOCK)
WHERE
e.Name='new_discoveryform'
SELECT
pc.new_discoveryformId AS Id
,pc.new_name AS Name
,pc.new_firstname AS FirstName
,pc.new_lastname AS LastName
,pc.new_phonenumber AS PhoneNumber
,pc.new_email AS Email
,pc.new_cityid AS CityId
,pc.new_cityidName AS CityIdName
,'new_city' AS CityIdTypeName
,pc.new_townid AS TownId
,pc.new_townidName AS TownIdName
,'new_town' AS TownIdTypeName
,pc.new_userid AS UserId
,pc.new_useridName AS UserIdName
,'new_user' AS UserIdTypeName
--,pc.new_hometype AS HomeType
--,smHomeType.Value AS HomeTypeValue
--,pc.new_informedby AS InformedBy
--,smInformedBy.Value AS InformedByValue
--,pc.new_visithour AS VisitHour
--,smVisitHour.Value AS VisitHourValue
,pc.new_visitdate AS VisitDate
,pc.new_formcode AS FormCode
,pc.statuscode AS Status
,pc.CreatedOn AS CreatedOn
FROM
new_discoveryform AS pc (NOLOCK)
-- JOIN
-- StringMap AS smHomeType (NOLOCK)
-- ON
-- smHomeType.ObjectTypeCode=@objectTypeCode
-- AND
-- smHomeType.AttributeName='new_hometype'
-- AND
-- smHomeType.AttributeValue=pc.new_hometype
-- JOIN
-- StringMap AS smInformedBy (NOLOCK)
-- ON
-- smInformedBy.ObjectTypeCode=@objectTypeCode
-- AND
-- smInformedBy.AttributeName='new_informedby'
-- AND
-- smInformedBy.AttributeValue=pc.new_informedby
-- JOIN
-- StringMap AS smVisitHour (NOLOCK)
-- ON
-- smVisitHour.ObjectTypeCode=@objectTypeCode
-- AND
-- smVisitHour.AttributeName='new_visithour'
-- AND
-- smVisitHour.AttributeValue=pc.new_visithour
WHERE
pc.statuscode={0}";
#endregion
DataTable dt = sda.getDataTable(string.Format(sqlQuery, ((int)discoveryFormStatus)).ToString());
if (dt.Rows.Count > 0)
{
List<DiscoveryForm> giftList = dt.ToList<DiscoveryForm>();
returnValue.Success = true;
returnValue.ReturnObject = giftList;
}
else
{
returnValue.Success = false;
returnValue.Result = "Keşif form kaydı bulunamadı!";
}
}
catch (Exception ex)
{
returnValue.Success = false;
returnValue.Result = ex.Message;
}
return returnValue;
}
public static MsCrmResultObject GetScoreLimitsByType(ScoreType scoreType, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string query = @"SELECT
sl.new_scorelimitId AS Id
,sl.new_name AS Name
,sl.new_scoretype AS ScoreType
,sl.new_frequency AS Frequency
,sl.new_point AS Point
,sl.new_scoreperiod AS Period
FROM
new_scorelimit AS sl (NOLOCK)
WHERE
sl.new_scoretype=@ScoreType --Sisteme giriş
AND
sl.statecode=0
AND
sl.statuscode=1 --Aktif
ORDER BY
sl.new_scoreperiod ASC";
#endregion
SqlParameter[] parameters = {
new SqlParameter("@ScoreType",(int)scoreType)
};
DataTable dt = sda.getDataTable(query, parameters);
if (dt != null && dt.Rows.Count > 0)
{
List<ScoreLimit> returnList = new List<ScoreLimit>();
for (int i = 0; i < dt.Rows.Count; i++)
{
ScoreLimit _scoreLimit = new ScoreLimit();
_scoreLimit.Id = (Guid)dt.Rows[i]["Id"];
_scoreLimit.Name = dt.Rows[i]["Name"] != DBNull.Value ? dt.Rows[i]["Name"].ToString() : string.Empty;
_scoreLimit.ScoreType = (ScoreType)(int)dt.Rows[i]["ScoreType"];
_scoreLimit.Period = (ScorePeriod)(int)dt.Rows[i]["Period"];
_scoreLimit.Point = (int)dt.Rows[i]["Point"];
_scoreLimit.Frequency = (int)dt.Rows[i]["Frequency"];
returnList.Add(_scoreLimit);
}
returnValue.Success = true;
returnValue.ReturnObject = returnList;
}
else
{
returnValue.Success = false;
returnValue.Result = "Skor Limit kaydı 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 SelectQuestion(Guid portalUserId, Guid portalId, Guid questionLevelId, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string query = @"SELECT TOP 1
q.new_questionId AS QUestionId
FROM
new_question AS q (NOLOCK)
WHERE
q.new_portalId='{0}'
AND
q.new_questionlevelId='{1}'
AND
q.statecode=0
AND
q.statuscode=1 --Active
AND
q.new_questionId NOT IN
(
SELECT
q.new_questionId
FROM
new_questionanswers AS a (NOLOCK)
WHERE
a.new_questionId=q.new_questionId
AND
a.new_userId='{2}'
AND
a.new_point IS NOT NULL AND a.new_point != 0
AND
a.statecode=0
)
ORDER BY NEWID()";
#endregion
DataTable dt = sda.getDataTable(string.Format(query, portalId, questionLevelId, portalUserId));
if (dt != null && dt.Rows.Count > 0)
{
returnValue = GetQuestionInfo((Guid)dt.Rows[0]["QuestionId"], sda);
}
else
{
returnValue.Success = false;
returnValue.Result = "M029"; //"Soru seviyesine ait uygun soru bulunamadı.";
}
}
catch (Exception ex)
{
returnValue.Success = false;
returnValue.Result = ex.Message;
}
return returnValue;
}
public static MsCrmResultObj<List<AssemblyRequestInfo>> GetAssemblyRequestList(Guid userId, SqlDataAccess sda)
{
MsCrmResultObj<List<AssemblyRequestInfo>> returnValue = new MsCrmResultObj<List<AssemblyRequestInfo>>();
try
{
#region | SQL QUERY |
string sqlQuery = @"SELECT
req.new_assemblyrequestId AS Id
,req.new_name AS Name
,req.new_firstname AS FirstName
,req.new_lastname AS LastName
,req.new_mobilephone AS MobilePhoneNumber
,req.new_workphone AS WorkPhoneNumber
,req.new_emailaddress AS EmailAddress
,req.new_cityid AS CityId
,req.new_cityidName AS CityIdName
,'new_city' AS CityIdTypeName
,req.new_townid AS TownId
,req.new_townidName AS TownIdName
,'new_town' AS TownIdTypeName
,req.new_addressdetail AS AddressDetail
,req.new_userid AS AssemblerId
,req.new_useridName AS AssemblerIdName
,'new_user' AS AssemblerIdTypeName
,req.statuscode AS StatusCode
,req.CreatedOn
FROM
new_assemblyrequest AS req (NOLOCK)
WHERE
req.new_userid='{0}'";
#endregion
DataTable dt = sda.getDataTable(string.Format(sqlQuery, userId));
if (dt.Rows.Count > 0)
{
List<AssemblyRequestInfo> assemblyRequestList = dt.ToList<AssemblyRequestInfo>();
returnValue.Success = true;
returnValue.ReturnObject = assemblyRequestList;
}
else
{
returnValue.Result = "Kayıt bulunamadı.";
}
}
catch (Exception ex)
{
returnValue.HasException = true;
returnValue.Result = ex.Message;
}
return returnValue;
}
public static MsCrmResultObject GetContactInfo(Guid contactId, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string query = @"SELECT
C.ContactId
,C.FirstName
,C.LastName
,C.JobTitle
,C.new_functionname FunctionName
,C.AccountId
,C.AccountIdName
,C.EMailAddress1 EmailAddress
,C.MobilePhone
,C.Telephone1
,C.new_identitynumber AS IdentityNo
,C.GenderCode
,DATEADD(HH,3,C.BirthDate) AS BirthDate
,C.Description
,C.new_cityId AS CityId
,C.new_cityIdName AS CityIdName
,C.new_townId AS TownId
,C.new_townIdName AS TownIdName
,C.new_addressdetail AS AddressDetail
,C.new_markcontact AS MarkContact
FROM
Contact C (NoLock)
WHERE
C.ContactId = '{0}'
AND
C.StateCode = 0";
#endregion
DataTable dt = sda.getDataTable(string.Format(query, contactId));
if (dt != null && dt.Rows.Count > 0)
{
Contact _contact = new Contact();
_contact.ContactId = (Guid)dt.Rows[0]["ContactId"];
_contact.FirstName = dt.Rows[0]["FirstName"] != DBNull.Value ? dt.Rows[0]["FirstName"].ToString() : string.Empty;
_contact.LastName = dt.Rows[0]["LastName"] != DBNull.Value ? dt.Rows[0]["LastName"].ToString() : string.Empty;
_contact.Title = dt.Rows[0]["JobTitle"] != DBNull.Value ? dt.Rows[0]["JobTitle"].ToString() : string.Empty;
_contact.FunctionName = dt.Rows[0]["FunctionName"] != DBNull.Value ? dt.Rows[0]["FunctionName"].ToString() : string.Empty;
_contact.EmailAddress = dt.Rows[0]["EmailAddress"] != DBNull.Value ? dt.Rows[0]["EmailAddress"].ToString() : string.Empty;
_contact.MobilePhone = dt.Rows[0]["MobilePhone"] != DBNull.Value ? dt.Rows[0]["MobilePhone"].ToString() : string.Empty;
_contact.WorkPhone = dt.Rows[0]["Telephone1"] != DBNull.Value ? dt.Rows[0]["Telephone1"].ToString() : string.Empty;
_contact.IdentityNumber = dt.Rows[0]["IdentityNo"] != DBNull.Value ? dt.Rows[0]["IdentityNo"].ToString() : string.Empty;
if (dt.Rows[0]["GenderCode"] != DBNull.Value) { _contact.Gender = (int)dt.Rows[0]["GenderCode"]; }
if (dt.Rows[0]["BirthDate"] != DBNull.Value) { _contact.BirthDate = (DateTime)dt.Rows[0]["BirthDate"]; }
_contact.Description = dt.Rows[0]["Description"] != DBNull.Value ? dt.Rows[0]["Description"].ToString() : string.Empty;
_contact.MarkContact = dt.Rows[0]["MarkContact"] != DBNull.Value ? (bool)dt.Rows[0]["MarkContact"] : false;
if (dt.Rows[0]["AccountId"] != DBNull.Value)
{
EntityReference er = new EntityReference();
er.LogicalName = "account";
er.Id = (Guid)dt.Rows[0]["AccountId"];
if (dt.Rows[0]["AccountIdName"] != DBNull.Value) { er.Name = dt.Rows[0]["AccountIdName"].ToString(); }
_contact.ParentAccount = er;
}
if (dt.Rows[0]["CityId"] != DBNull.Value)
{
EntityReference er = new EntityReference();
er.LogicalName = "new_city";
er.Id = (Guid)dt.Rows[0]["CityId"];
if (dt.Rows[0]["CityIdName"] != DBNull.Value) { er.Name = dt.Rows[0]["CityIdName"].ToString(); }
_contact.CityId = er;
}
if (dt.Rows[0]["TownId"] != DBNull.Value)
{
EntityReference er = new EntityReference();
er.LogicalName = "new_town";
er.Id = (Guid)dt.Rows[0]["TownId"];
if (dt.Rows[0]["TownIdName"] != DBNull.Value) { er.Name = dt.Rows[0]["TownIdName"].ToString(); }
_contact.TownId = er;
}
if (dt.Rows[0]["AccountId"] != DBNull.Value)
{
EntityReference er = new EntityReference();
er.LogicalName = "account";
er.Id = (Guid)dt.Rows[0]["AccountId"];
if (dt.Rows[0]["AccountIdName"] != DBNull.Value) { er.Name = dt.Rows[0]["AccountIdName"].ToString(); }
_contact.ParentAccount = er;
}
_contact.AddressDetail = dt.Rows[0]["AddressDetail"] != DBNull.Value ? dt.Rows[0]["AddressDetail"].ToString() : string.Empty;
returnValue.ReturnObject = _contact;
returnValue.Success = true;
}
else
{
returnValue.Success = false;
returnValue.Result = "M007"; //"No contact info found!";
}
}
catch (Exception ex)
{
returnValue.Success = false;
returnValue.Result = ex.Message;
}
return returnValue;
}
public static MsCrmResultObj<List<AssemblerInfo>> GetAllAssemblerList(Guid portalId, SqlDataAccess sda)
{
MsCrmResultObj<List<AssemblerInfo>> returnValue = new MsCrmResultObj<List<AssemblerInfo>>();
try
{
#region | SQL QUERY |
string sqlQuery = @"SELECT
DISTINCT
u.new_userId AS Id
,u.new_name AS IdName
,'new_user' AS IdTypeName
,c.FirstName
,c.LastName
,c.JobTitle AS CompanyName
,c.MobilePhone AS MobilePhoneNumber
,c.Telephone1 AS WorkPhoneNumber
,c.new_cityId AS CityId
,c.new_cityIdName AS CityIdName
,'new_city' AS CityIdTypeName
,c.new_townId AS TownId
,c.new_townIdName AS TownIdName
,'new_town' AS TownIdTypeName
,c.new_addressdetail AS AddressDetail
FROM
new_user AS u (NOLOCK)
JOIN
new_new_user_new_role AS ur (NOLOCK)
ON
ur.new_userid=u.new_userId
JOIN
new_role AS r (NOLOCK)
ON
ur.new_roleid=r.new_roleId
AND
r.statecode=0
AND
r.statuscode=1 --Active
AND
r.new_portalId='{0}'
JOIN
Contact AS c (NOLOCK)
ON
u.new_contactId=c.ContactId
WHERE
u.statecode=0
AND
u.statuscode=1 --Active
AND
c.new_markcontact=1
AND
c.new_cityId IS NOT NULL
AND
c.new_townId IS NOT NULL";
#endregion
DataTable dt = sda.getDataTable(string.Format(sqlQuery, portalId));
if (dt.Rows.Count > 0)
{
List<AssemblerInfo> assemblerList = dt.ToList<AssemblerInfo>();
returnValue.Success = true;
returnValue.ReturnObject = assemblerList;
}
else
{
returnValue.Result = "Kayıt bulunamadı.";
}
}
catch (Exception ex)
{
returnValue.HasException = true;
returnValue.Result = ex.Message;
}
return returnValue;
}
public static MsCrmResultObject GetGraffities(Guid portalId, int commentCount, int startRow, int endRow, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string query = @"SELECT
A.*
FROM
(
SELECT
PG.new_graffitiId GraffitiId
,PG.new_content [Description]
,PG.new_userId PortalUserId
,PG.new_userIdName PortalUserIdName
,PG.new_portalId BrandId
,PG.new_portalIdName BrandIdName
,PG.new_imageurl [Image]
,U.new_imageurl UserImage
,CAST({4}.dbo.fn_UTCToTzSpecificLocalTime(PG.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
,ROW_NUMBER() OVER(ORDER BY PG.CreatedOn DESC) AS RowNumber
,(
SELECT
COUNT(0)
FROM
new_comment AS c (NOLOCK)
WHERE
c.new_graffitiId=PG.new_graffitiId
) AS CommentCount
,(
SELECT
COUNT(0)
FROM
new_likerecord AS lr (NOLOCK)
WHERE
lr.new_graffitiId=PG.new_graffitiId
) AS LikeCount
FROM
new_graffiti PG (NoLock)
INNER JOIN
new_user U (NoLock)
ON
PG.new_portalId = '{0}'
AND
PG.statecode = 0
AND
U.new_userId = PG.new_userId
INNER JOIN
dbo.UserSettingsBase US (NoLock)
ON
US.SystemUserId ='{1}'
WHERE
PG.statecode=0
AND
PG.statuscode=1 --Active
)A
WHERE
A.RowNumber BETWEEN {2} AND {3}
ORDER BY
A.RowNumber ASC";
#endregion
DataTable dt = sda.getDataTable(string.Format(query, portalId, Globals.AdminId, startRow, endRow, Globals.DatabaseName));
if (dt != null && dt.Rows.Count > 0)
{
List<Graffiti> returnList = new List<Graffiti>();
for (int i = 0; i < dt.Rows.Count; i++)
{
Graffiti _graffiti = new Graffiti();
_graffiti.GraffitiId = (Guid)dt.Rows[i]["GraffitiId"];
_graffiti.Description = dt.Rows[i]["Description"] != DBNull.Value ? dt.Rows[i]["Description"].ToString() : string.Empty;
_graffiti.ImagePath = dt.Rows[i]["Image"] != DBNull.Value ? dt.Rows[i]["Image"].ToString() : string.Empty;
_graffiti.PortalUserImage = dt.Rows[i]["UserImage"] != DBNull.Value ? dt.Rows[i]["UserImage"].ToString() : "nouserprofile.jpg";
_graffiti.CreatedOnString = dt.Rows[i]["CreatedOn"] != DBNull.Value ? ((DateTime)dt.Rows[i]["CreatedOn"]).ToString("dd MMMM yyyy, HH:mm", new CultureInfo("tr-TR", false)) : string.Empty;
_graffiti.HasMedia = dt.Rows[i]["Image"] != DBNull.Value ? true : false;
_graffiti.LikeCount = (int)dt.Rows[i]["LikeCount"];
_graffiti.CommentCount = (int)dt.Rows[i]["CommentCount"];
if (dt.Rows[i]["CreatedOn"] != DBNull.Value)
{
_graffiti.CreatedOn = (DateTime)dt.Rows[i]["CreatedOn"];
}
if (dt.Rows[i]["PortalUserId"] != DBNull.Value)
{
EntityReference er = new EntityReference();
er.LogicalName = "new_user";
er.Id = (Guid)dt.Rows[i]["PortalUserId"];
if (dt.Rows[i]["PortalUserIdName"] != DBNull.Value) { er.Name = dt.Rows[i]["PortalUserIdName"].ToString(); }
_graffiti.PortalUser = er;
}
if (dt.Rows[i]["BrandId"] != DBNull.Value)
{
EntityReference er = new EntityReference();
er.LogicalName = "new_brand";
er.Id = (Guid)dt.Rows[i]["BrandId"];
if (dt.Rows[i]["BrandIdName"] != DBNull.Value) { er.Name = dt.Rows[i]["BrandIdName"].ToString(); }
_graffiti.Portal = er;
}
#region | GET COMMENTS |
MsCrmResultObject commentResult = CommentHelper.GetGraffitiComments(_graffiti.GraffitiId, 0, commentCount, sda);
//MsCrmResultObject commentResult = CommentHelper.GetGraffitiComments(_graffiti.GraffitiId, sda);
if (commentResult.Success)
{
_graffiti.CommentList = (List<Comment>)commentResult.ReturnObject;
}
#endregion
returnList.Add(_graffiti);
}
returnValue.Success = true;
returnValue.ReturnObject = returnList;
}
else
{
returnValue.Success = true;
returnValue.Result = "M013"; //"Duvar yazısı 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;
}
public static MsCrmResultObject GetAnnouncementList(Guid portalId, Guid portalUserId, int start, int end, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string query = @"DECLARE @Date DATETIME = GETUTCDATE()
SELECT
*
FROM
(
SELECT
D.*
,COUNT(0) OVER() AS TotalRow
FROM
(
SELECT
B.*
,ROW_NUMBER() OVER(ORDER BY B.CreatedOn DESC) AS RowNumber
FROM
(
SELECT DISTINCT
A.new_announcementId Id
,A.new_name Name
,A.new_content Content
,A.new_imageurl ImageUrl
,CAST({1}.dbo.fn_UTCToTzSpecificLocalTime(A.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_announcement A (NoLock)
JOIN
new_new_user_new_role AS UR (NOLOCK)
ON
UR.new_userid='{2}'
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_announcement_new_role AS ERDF (NOLOCK)
ON
ERDF.new_announcementid=A.new_announcementId
AND
ERDF.new_roleid=RD.new_roleId
INNER JOIN
dbo.UserSettingsBase US (NoLock)
ON
US.SystemUserId ='{0}'
WHERE
@Date BETWEEN A.new_startdate AND A.new_enddate
AND
A.new_portalId='{3}'
AND
A.StateCode = 0
AND
A.StatusCode=1 --Active
) AS B
) AS D
) AS C
WHERE
C.RowNumber BETWEEN {4} AND {5}
ORDER BY
C.RowNumber ASC";
#endregion
DataTable dt = sda.getDataTable(string.Format(query, Globals.AdminId, Globals.DatabaseName, portalUserId, portalId.ToString(), start.ToString(), end.ToString()));
if (dt != null && dt.Rows.Count > 0)
{
List<Announcement> returnList = new List<Announcement>();
for (int i = 0; i < dt.Rows.Count; i++)
{
Announcement _announcement = new Announcement();
_announcement.AnnouncementId = (Guid)dt.Rows[i]["Id"];
_announcement.Caption = dt.Rows[i]["Name"] != DBNull.Value ? dt.Rows[i]["Name"].ToString() : string.Empty;
_announcement.Description = dt.Rows[i]["Content"] != DBNull.Value ? dt.Rows[i]["Content"].ToString() : string.Empty;
_announcement.ImagePath = dt.Rows[i]["ImageUrl"] != DBNull.Value ? dt.Rows[i]["ImageUrl"].ToString() : string.Empty;
if (dt.Rows[i]["TotalRow"] != DBNull.Value)
{
returnValue.RecordCount = (int)dt.Rows[i]["TotalRow"];
}
if (dt.Rows[i]["CreatedOn"] != DBNull.Value)
{
_announcement.CreatedOn = (DateTime)dt.Rows[i]["CreatedOn"];
_announcement.CreatedOnString = dt.Rows[i]["CreatedOn"] != DBNull.Value ? ((DateTime)dt.Rows[i]["CreatedOn"]).ToString("dd MMMM yyyy ddddd HH:mm", new CultureInfo("tr-TR", false)) : string.Empty;
}
returnList.Add(_announcement);
}
returnValue.Success = true;
returnValue.ReturnObject = returnList;
}
else
{
returnValue.Success = true;
returnValue.Result = "M025"; //"Duyuru kaydı bulunamadı!";
}
}
catch (Exception ex)
{
returnValue.Success = false;
returnValue.Result = ex.Message;
}
return returnValue;
}
public static MsCrmResultObj<AssemblerInfo> GetAssemblerInfo(Guid userId, SqlDataAccess sda)
{
MsCrmResultObj<AssemblerInfo> returnValue = new MsCrmResultObj<AssemblerInfo>();
try
{
#region | SQL QUERY |
string sqlQuery = @"SELECT
DISTINCT
u.new_userId AS Id
,u.new_name AS IdName
,'new_user' AS IdTypeName
,c.FirstName
,c.LastName
,c.JobTitle AS CompanyName
,c.MobilePhone AS MobilePhoneNumber
,c.Telephone1 AS WorkPhoneNumber
,c.new_cityId AS CityId
,c.new_cityIdName AS CityIdName
,'new_city' AS CityIdTypeName
,c.new_townId AS TownId
,c.new_townIdName AS TownIdName
,'new_town' AS TownIdTypeName
,c.new_addressdetail AS AddressDetail
FROM
new_user AS u (NOLOCK)
JOIN
Contact AS c (NOLOCK)
ON
u.new_contactId=c.ContactId
WHERE
u.new_userId='{0}'";
#endregion
DataTable dt = sda.getDataTable(string.Format(sqlQuery, userId));
if (dt.Rows.Count > 0)
{
List<AssemblerInfo> assemblerList = dt.ToList<AssemblerInfo>();
returnValue.Success = true;
returnValue.ReturnObject = assemblerList[0];
}
else
{
returnValue.Result = "Kayıt bulunamadı.";
}
}
catch (Exception ex)
{
returnValue.HasException = true;
returnValue.Result = ex.Message;
}
return returnValue;
}
