public static MsCrmResultObject GetArticleInfo(Guid articleId, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string query = @"SELECT
A.new_articleId ArticleId
,A.new_name Name
,A.new_summary Summary
,A.new_content [Description]
,A.new_imageurl [Image]
,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_article A (NoLock)
INNER JOIN
dbo.UserSettingsBase US (NoLock)
ON
US.SystemUserId ='{1}'
WHERE
A.new_articleId = '{0}'" ;
#endregion
DataTable dt = sda.getDataTable(string.Format(query, articleId, Globals.AdminId, Globals.DatabaseName));
if (dt != null && dt.Rows.Count > 0)
{
Article _article = new Article();
_article.ArticleId = (Guid)dt.Rows[0]["ArticleId"];
_article.Name = dt.Rows[0]["Name"].ToString();
_article.Summary = dt.Rows[0]["Summary"].ToString();
_article.Description = dt.Rows[0]["Description"].ToString();
_article.ImagePath = dt.Rows[0]["Image"] != DBNull.Value ? dt.Rows[0]["Image"].ToString() : "no_image_available.png";
_article.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;
#region | GET COMMENTS |
MsCrmResultObject commentResult = CommentHelper.GetArticleComments(articleId, sda);
if (commentResult.Success)
{
_article.CommentList = (List <Comment>)commentResult.ReturnObject;
}
#endregion
#region | GET ATTACHMENTS |
MsCrmResultObject attachmentResult = AttachmentFileHelper.GetArticleAttachmentFiles(articleId, sda);
if (attachmentResult.Success)
{
_article.AttachmentFileList = (List <AttachmentFile>)attachmentResult.ReturnObject;
}
#endregion
returnValue.Success = true;
returnValue.ReturnObject = _article;
}
else
{
returnValue.Success = true;
returnValue.Result = "M020"; //"İstenilen makaleye ait detay bulunamadı!";
}
}
catch (Exception ex)
{
returnValue.Success = false;
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 GetVideoInfo(Guid videoId, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string query = @"SELECT
E.new_videoId AS Id
,E.new_name Name
,E.new_summary Summary
,E.new_imageurl [Image]
,E.new_videourl AS [Video]
,E.new_youtubeurl AS [YoutubeUrl]
,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_video E (NoLock)
INNER JOIN
dbo.UserSettingsBase US (NoLock)
ON
US.SystemUserId ='{1}'
WHERE
E.new_videoId = '{0}'" ;
#endregion
DataTable dt = sda.getDataTable(string.Format(query, videoId, Globals.AdminId, Globals.DatabaseName));
if (dt != null && dt.Rows.Count > 0)
{
Video _video = new Video();
_video.Id = (Guid)dt.Rows[0]["Id"];
_video.Name = dt.Rows[0]["Name"].ToString();
_video.Summary = dt.Rows[0]["Summary"].ToString();
_video.ImagePath = dt.Rows[0]["Image"] != DBNull.Value ? dt.Rows[0]["Image"].ToString() : "no-video-bg.png";
_video.VideoPath = dt.Rows[0]["Video"] != DBNull.Value ? dt.Rows[0]["Video"].ToString() : string.Empty;
_video.YouTubeUrl = dt.Rows[0]["YoutubeUrl"] != DBNull.Value ? dt.Rows[0]["YoutubeUrl"].ToString() : string.Empty;
_video.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;
#region | GET COMMENTS |
MsCrmResultObject commentResult = CommentHelper.GetEntityComments(videoId, "new_video", 0, 100, sda);
if (commentResult.Success)
{
_video.CommentList = (List <Comment>)commentResult.ReturnObject;
}
#endregion
MsCrmResultObject likeResult = LikeHelper.GetEntityLikeInfo(_video.Id, "new_video", sda);
if (likeResult.Success)
{
_video.LikeDetail = (LikeInfo)likeResult.ReturnObject;
}
returnValue.Success = true;
returnValue.ReturnObject = _video;
}
else
{
returnValue.Success = true;
returnValue.Result = "M023"; //"Video detayı bulunamadı!";
}
}
catch (Exception ex)
{
returnValue.Success = false;
returnValue.Result = ex.Message;
}
return(returnValue);
}
public static MsCrmResultObject GetEducationInfo(Guid educationId, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string query = @"SELECT
E.new_educationId EducationId
,E.new_name Name
,E.new_summary Summary
,E.new_content [Description]
,E.new_imageurl [Image]
,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_education E (NoLock)
INNER JOIN
dbo.UserSettingsBase US (NoLock)
ON
US.SystemUserId ='{1}'
WHERE
E.new_educationId = '{0}'" ;
#endregion
DataTable dt = sda.getDataTable(string.Format(query, educationId, Globals.AdminId, Globals.DatabaseName));
if (dt != null && dt.Rows.Count > 0)
{
Education _education = new Education();
_education.EducationId = (Guid)dt.Rows[0]["EducationId"];
_education.Name = dt.Rows[0]["Name"].ToString();
_education.Summary = dt.Rows[0]["Summary"].ToString();
_education.Description = dt.Rows[0]["Description"].ToString();
_education.ImagePath = dt.Rows[0]["Image"] != DBNull.Value ? dt.Rows[0]["Image"].ToString() : "no_image_available.png";
_education.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;
MsCrmResultObject resultLike = LikeHelper.GetEntityLikeInfo(_education.EducationId, "new_graffiti", sda);
if (resultLike.Success)
{
_education.LikeDetail = (LikeInfo)resultLike.ReturnObject;
}
#region | GET COMMENTS |
MsCrmResultObject commentResult = CommentHelper.GetEducationComments(educationId, sda);
if (commentResult.Success)
{
_education.CommentList = (List <Comment>)commentResult.ReturnObject;
}
#endregion
#region | GET ATTACHMENTS |
MsCrmResultObject attachmentResult = AttachmentFileHelper.GetEducationAttachmentFiles(educationId, sda);
if (attachmentResult.Success)
{
_education.AttachmentFileList = (List <AttachmentFile>)attachmentResult.ReturnObject;
}
#endregion
returnValue.Success = true;
returnValue.ReturnObject = _education;
}
else
{
returnValue.Success = true;
returnValue.Result = "M017"; //"Eğitim kaydına ulaşılamadı.";
}
}
catch (Exception ex)
{
returnValue.Success = false;
returnValue.Result = ex.Message;
}
return(returnValue);
}
public static MsCrmResultObject GetForumSubjectInfo(Guid forumSubjectId, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string query = @"SELECT
fs.new_forumsubjectId AS Id
,fs.new_name AS Name
,fs.new_userId AS UserId
,fs.new_userIdName AS UserIdName
,fs.new_content AS Content
,fs.CreatedOn
,u.new_imageurl ImageUrl
FROM
new_forumsubject AS fs (NOLOCK)
JOIN
new_user AS u (NOLOCK)
ON
u.new_userId=fs.new_userId
WHERE
fs.new_forumsubjectId='{0}'
AND
fs.statecode=0
AND
fs.statuscode=1 --Active" ;
#endregion
DataTable dt = sda.getDataTable(string.Format(query, forumSubjectId));
if (dt != null && dt.Rows.Count > 0)
{
ForumSubject fs = new ForumSubject();
fs.Id = (Guid)dt.Rows[0]["Id"];
fs.Name = dt.Rows[0]["Name"] != DBNull.Value ? dt.Rows[0]["Name"].ToString() : string.Empty;
fs.Content = dt.Rows[0]["Content"] != DBNull.Value ? dt.Rows[0]["Content"].ToString() : string.Empty;
fs.PortalUserImage = dt.Rows[0]["ImageUrl"] != DBNull.Value ? dt.Rows[0]["ImageUrl"].ToString() : "nouserprofile.jpg";
fs.CreatedOn = (DateTime)dt.Rows[0]["CreatedOn"];
fs.CreatedOnString = ((DateTime)dt.Rows[0]["CreatedOn"]).ToString("dd MMMM yyyy ddddd HH:mm", new CultureInfo("tr-TR", false));
if (dt.Rows[0]["UserId"] != DBNull.Value)
{
EntityReference er = new EntityReference();
er.Id = (Guid)dt.Rows[0]["UserId"];
er.Name = dt.Rows[0]["UserIdName"] != DBNull.Value ? dt.Rows[0]["UserIdName"].ToString() : string.Empty;
er.LogicalName = "new_user";
fs.User = er;
}
MsCrmResultObject resComment = CommentHelper.GetEntityComments(fs.Id, "new_forumsubject", 0, 10, sda);
if (resComment.Success)
{
fs.CommentList = (List <Comment>)resComment.ReturnObject;
}
MsCrmResultObject resultLike = LikeHelper.GetEntityLikeInfo(fs.Id, "new_forumsubject", sda);
if (resultLike.Success)
{
fs.LikeDetail = (LikeInfo)resultLike.ReturnObject;
}
returnValue.Success = true;
returnValue.ReturnObject = fs;
}
else
{
returnValue.Success = false;
returnValue.Result = "M049"; //"Forum konu başlığına ait bilgi bulunamadı!";
}
}
catch (Exception ex)
{
returnValue.Success = false;
returnValue.Result = ex.Message;
}
return(returnValue);
}
public static MsCrmResultObject GetForumSubjects(Guid forumId, SqlDataAccess sda)
{
MsCrmResultObject returnValue = new MsCrmResultObject();
try
{
#region | SQL QUERY |
string query = @"SELECT
fs.new_forumsubjectId AS Id
,fs.new_name AS Name
,fs.new_userId AS UserId
,fs.new_userIdName AS UserIdName
,fs.new_content AS Content
,fs.CreatedOn
FROM
new_forumsubject AS fs (NOLOCK)
WHERE
fs.new_forumId='{0}'
AND
fs.statecode=0
AND
fs.statuscode=1 --Active
ORDER BY
fs.CreatedOn DESC" ;
#endregion
DataTable dt = sda.getDataTable(string.Format(query, forumId));
if (dt != null && dt.Rows.Count > 0)
{
List <ForumSubject> lstSub = new List <ForumSubject>();
for (int i = 0; i < dt.Rows.Count; i++)
{
ForumSubject fs = new ForumSubject();
fs.Id = (Guid)dt.Rows[i]["Id"];
fs.Name = dt.Rows[i]["Name"] != DBNull.Value ? dt.Rows[i]["Name"].ToString() : string.Empty;
fs.Content = dt.Rows[i]["Content"] != DBNull.Value ? dt.Rows[i]["Content"].ToString() : string.Empty;
fs.CreatedOn = (DateTime)dt.Rows[i]["CreatedOn"];
fs.CreatedOnString = ((DateTime)dt.Rows[i]["CreatedOn"]).ToString("dd.MM.yyyy HH:mm");
if (dt.Rows[i]["UserId"] != DBNull.Value)
{
EntityReference er = new EntityReference();
er.Id = (Guid)dt.Rows[i]["UserId"];
er.Name = dt.Rows[i]["UserIdName"] != DBNull.Value ? dt.Rows[i]["UserIdName"].ToString() : string.Empty;
er.LogicalName = "new_user";
fs.User = er;
}
MsCrmResultObject resComment = CommentHelper.GetEntityComments(fs.Id, "new_forumsubject", 0, 10, sda);
if (resComment.Success)
{
fs.CommentList = (List <Comment>)resComment.ReturnObject;
}
lstSub.Add(fs);
}
returnValue.Success = true;
returnValue.ReturnObject = lstSub;
}
else
{
returnValue.Success = false;
returnValue.Result = "M048"; //"Foruma ait konu başlığı bulunmamaktadır!";
}
}
catch (Exception ex)
{
returnValue.Success = false;
returnValue.Result = ex.Message;
}
return(returnValue);
}
