public static MsCrmResultObject GetArticleList(Guid portalId, Guid portalUserId, int startRow, int endRow, Guid categoryId, 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 A.* ,ROW_NUMBER() OVER(ORDER BY A.CreatedOn DESC) AS RowNumber FROM ( SELECT DISTINCT E.new_articleId ArticleId ,E.new_name Name ,E.new_summary Summary ,E.new_content [Description] ,E.new_imageurl [Image] ,CAST({3}.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 ,( SELECT COUNT(0) FROM new_comment AS PC (NOLOCK) WHERE PC.new_articleId =E.new_articleId AND PC.statecode=0 AND PC.statuscode=1 --Active ) AS CommentCount FROM new_article 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_article_new_role AS ERDF (NOLOCK) ON ERDF.new_articleid=E.new_articleId AND ERDF.new_roleid =RD.new_roleId JOIN dbo.UserSettingsBase US (NOLOCK) ON US.SystemUserId ='{2}' WHERE E.new_categoryId='{6}' AND @Date BETWEEN E.new_startdate AND E.new_enddate AND E.new_portalId = '{0}' AND E.statuscode=1 --Active ) AS A ) AS D ) AS B WHERE B.RowNumber BETWEEN {4} AND {5} ORDER BY B.RowNumber ASC" ; #endregion DataTable dt = sda.getDataTable(string.Format(query, portalId, portalUserId, Globals.AdminId, Globals.DatabaseName, startRow, endRow, categoryId)); if (dt != null && dt.Rows.Count > 0) { List <Article> returnList = new List <Article>(); for (int i = 0; i < dt.Rows.Count; i++) { Article _article = new Article(); _article.ArticleId = (Guid)dt.Rows[i]["ArticleId"]; _article.Name = dt.Rows[i]["Name"] != DBNull.Value ? dt.Rows[i]["Name"].ToString() : string.Empty; _article.Summary = dt.Rows[i]["Summary"] != DBNull.Value ? dt.Rows[i]["Summary"].ToString() : string.Empty; _article.Description = dt.Rows[i]["Description"] != DBNull.Value ? dt.Rows[i]["Description"].ToString() : string.Empty; _article.ImagePath = dt.Rows[i]["Image"] != DBNull.Value ? dt.Rows[i]["Image"].ToString() : "no_image_available.png"; _article.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; _article.CommentCount = dt.Rows[i]["CommentCount"] != DBNull.Value ? (int)dt.Rows[i]["CommentCount"] : 0; if (dt.Rows[i]["TotalRow"] != DBNull.Value) { returnValue.RecordCount = (int)dt.Rows[i]["TotalRow"]; } if (dt.Rows[i]["CreatedOn"] != DBNull.Value) { _article.CreatedOn = (DateTime)dt.Rows[i]["CreatedOn"]; } MsCrmResultObject likeResult = LikeHelper.GetEntityLikeInfo(_article.ArticleId, "new_article", sda); if (likeResult.Success) { _article.LikeDetail = (LikeInfo)likeResult.ReturnObject; } returnList.Add(_article); } returnValue.Success = true; returnValue.ReturnObject = returnList; } else { returnValue.Success = false; returnValue.Result = "M019"; //"Makale kaydı 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 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 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); }