|
|
|
|
 I decided it was about time I created some sort of consolidated What's New for the members. I haven't done this for all users, because I'm using the reports module, and the query is getting long enough as it is. Besides most of the new information is in the members area anyway, but it could be used for all users depending on yoru security requirements. To the left is what it looks like in the end.
I started off by doing this via the Search function, but this caused various problems. I then went down the road of using views, but to be honest this left messy queries to be used within the Reports module, or too many layers of views. So in the end I have a stored procedure, which can be broken down into a setup section, a section for each module I'm getting data from, and an output section:- | Setup | CREATE Procedure [dbo].[tdc_Whats_New]
(
@Rows INT,
@AnnouncementsRows INT,
@EventsRows INT,
@ForumRows1 INT,
@ForumRows2 INT,
@GalleryRows INT,
@NewsArticlesRows INT,
@NewsletterRows INT,
@WikiRows INT,
@DescriptionLength INT,
@StripHTML as BIT
)
as CREATE TABLE #WhatsNew
(
Title varchar(500),
Description ntext,
PubDate DateTime,
Link varchar(500),
Userid Int,
ModuleID Int,
LinkType varchar(500),
ImageURL varchar(500),
ImageLinkType varchar(500),
ImageHeight varchar(20),
ImageWidth varchar(20)
) | | Announcements | SELECT TOP (@AnnouncementsRows) dbo.Announcements.Title, dbo.udf_HTMLDecode(dbo.Announcements.Description) AS Description,
dbo.Announcements.PublishDate AS PubDate, CASE ISNUMERIC(URL) WHEN 0 THEN CASE isnull(LEFT(url, 6), 'null')
WHEN '' THEN '' WHEN 'FileID' THEN 'Portals/0/' + Folder + FileName ELSE URL END ELSE 'default.aspx?tabid=' + CONVERT(varchar, URL)
END AS Link, dbo.Announcements.CreatedByUser AS UserID, dbo.Announcements.ModuleID, CASE isnull(LEFT(url, 7), 'null')
WHEN 'http://' THEN 'http' ELSE 'Full' END AS LinkType, NULL AS ImageURL, 'None' AS ImageLinkType, '' AS ImageHeight, '' AS ImageWidth
FROM dbo.Announcements LEFT OUTER JOIN
dbo.Files ON RIGHT(dbo.Announcements.URL, CASE ISNUMERIC(dbo.Announcements.URL) WHEN 0 THEN CASE LEN(dbo.Announcements.URL)
WHEN 0 THEN 7 ELSE LEN(dbo.Announcements.URL) END ELSE 7 END - 7) = CONVERT(varchar, dbo.Files.FileId)
WHERE (dbo.Announcements.PublishDate < GETDATE()) AND (dbo.Announcements.ExpireDate > GETDATE() OR
dbo.Announcements.ExpireDate IS NULL)
ORDER BY PubDate DESC | | Events | Insert into #WhatsNew (Title, Description, PubDate, Link, Userid, ModuleID, LinkType, ImageURL, ImageLinkType, ImageHeight, ImageWidth)
SELECT TOP (@EventsRows) EventName + ' - ' + CONVERT(varchar, EventDateBegin, 103) AS Title, dbo.udf_HTMLDecode(EventDesc) AS Description,
LastUpdatedAt AS PubDate, 'Mid=' + CONVERT(varchar, ModuleID) + '&ItemID=' + CONVERT(varchar, EventID)
+ '&ctl=Details&selecteddate=' + CONVERT(varchar, EventDateBegin, 103) AS Link, LastUpdatedID AS UserID, ModuleID, 'Part' AS LinkType, NULL
AS ImageURL, 'None' AS ImageLinkType, '' AS ImageHeight, '' AS ImageWidth
FROM dbo.Events
ORDER BY PubDate DESC | | Forum | Insert into #WhatsNew (Title, Description, PubDate, Link, Userid, ModuleID, LinkType, ImageURL, ImageLinkType, ImageHeight, ImageWidth)
SELECT TOP (@ForumRows1) dbo.Forum_Posts.Subject AS Title,
'<b>' + dbo.Forum_Forums.Name + '</b>: ' + dbo.udf_HTMLDecode(dbo.Forum_Posts.Body) AS Description,
dbo.Forum_Posts.CreatedDate AS PubDate, 'forumid=' + CONVERT(varchar, dbo.Forum_Threads.ForumID) + '&postid=' + CONVERT(varchar,
dbo.Forum_Threads.LastPostedPostID) + '&scope=posts' AS Link, dbo.Forum_Posts.UserID, dbo.Forum_Groups.ModuleID, 'Part' AS LinkType,
null as ImageURL, 'None' as ImageLinkType, '' as ImageHeight, '' as ImageWidth
FROM dbo.Forum_Threads INNER JOIN
dbo.Forum_Posts ON dbo.Forum_Threads.LastPostedPostID = dbo.Forum_Posts.PostID AND
dbo.Forum_Threads.ThreadID = dbo.Forum_Posts.ThreadID INNER JOIN
dbo.Forum_Forums ON dbo.Forum_Threads.ForumID = dbo.Forum_Forums.ForumID INNER JOIN
dbo.Forum_Groups ON dbo.Forum_Forums.GroupID = dbo.Forum_Groups.GroupID
WHERE (dbo.Forum_Forums.PublicView <> 0)
ORDER BY PubDate DESC Insert into #WhatsNew (Title, Description, PubDate, Link, Userid, ModuleID, LinkType, ImageURL, ImageLinkType, ImageHeight, ImageWidth)
SELECT TOP (@ForumRows2) dbo.Forum_Posts.Subject AS Title,
'<b>' + dbo.Forum_Forums.Name + '</b>:- <br />' + dbo.udf_HTMLDecode(dbo.Forum_Posts.Body) AS Description,
dbo.Forum_Posts.CreatedDate AS PubDate, 'forumid=' + CONVERT(varchar, dbo.Forum_Threads.ForumID) + '&postid=' + CONVERT(varchar,
dbo.Forum_Posts.PostID) + '&scope=posts' AS Link, dbo.Forum_Posts.UserID, dbo.Forum_Groups.ModuleID, 'Part' AS LinkType,
null as ImageURL, 'None' as ImageLinkType, '' as ImageHeight, '' as ImageWidth
FROM dbo.Forum_Threads RIGHT OUTER JOIN
dbo.Forum_Posts ON dbo.Forum_Threads.ThreadID = dbo.Forum_Posts.ThreadID LEFT OUTER JOIN
dbo.Forum_Groups INNER JOIN
dbo.Forum_Forums ON dbo.Forum_Groups.GroupID = dbo.Forum_Forums.GroupID ON
dbo.Forum_Threads.ForumID = dbo.Forum_Forums.ForumID
WHERE (dbo.Forum_Forums.PublicView <> 0)
ORDER BY PubDate DESC | | Gallery | SELECT TOP (@GalleryRows) dbo.DnnForge_SimpleGallery_Album.Caption + ': ' + dbo.DnnForge_SimpleGallery_Photo.Name AS Title,
CASE isnull(CONVERT(varchar, dbo.DnnForge_SimpleGallery_Photo.Description), 'null') WHEN 'null' THEN 'Read more' ELSE CONVERT(varchar(1000),
dbo.DnnForge_SimpleGallery_Photo.Description) END AS Description, dbo.DnnForge_SimpleGallery_Photo.DateCreated AS PubDate,
'ctl=SlideShow&mid=' + CONVERT(varchar, dbo.DnnForge_SimpleGallery_Photo.ModuleID) + '&ItemID=' + CONVERT(varchar,
dbo.DnnForge_SimpleGallery_Photo.PhotoID) AS Link, dbo.DnnForge_SimpleGallery_Photo.AuthorID AS UserID,
dbo.DnnForge_SimpleGallery_Photo.ModuleID, 'Part' AS LinkType, 'Portals/0/Gallery/Album/' + CONVERT(varchar,
dbo.DnnForge_SimpleGallery_Photo.AlbumID) + '/' + dbo.DnnForge_SimpleGallery_Photo.FileName AS ImageURL, 'Full' AS ImageLinkType,
CONVERT(varchar, dbo.DnnForge_SimpleGallery_Photo.Height / 2) + 'px' AS ImageHeight, CONVERT(varchar,
dbo.DnnForge_SimpleGallery_Photo.Width / 2) + 'px' AS ImageWidth
FROM dbo.DnnForge_SimpleGallery_Photo INNER JOIN
dbo.DnnForge_SimpleGallery_Album ON dbo.DnnForge_SimpleGallery_Photo.AlbumID = dbo.DnnForge_SimpleGallery_Album.AlbumID
ORDER BY PubDate DESC | | NewsArticles | Insert into #WhatsNew (Title, Description, PubDate, Link, Userid, ModuleID, LinkType, ImageURL, ImageLinkType, ImageHeight, ImageWidth)
SELECT DISTINCT
TOP (@NewsArticlesRows) dbo.DnnForge_NewsArticles_Article.Title, CASE isnull(CONVERT(varchar, Summary), 'null')
WHEN 'null' THEN dbo.udf_HTMLDecode(CONVERT(varchar(8000), PageText))
ELSE dbo.udf_HTMLDecode(CONVERT(varchar(8000), Summary)) END AS Description,
dbo.DnnForge_NewsArticles_Article.LastUpdate AS PubDate, CASE isnull(LEFT(url, 6), 'null')
WHEN 'FileID' THEN 'Portals/0/' + Folder + FileName WHEN 'null' THEN 'Default.aspx?tabid=' + CONVERT(varchar, tabid)
+ '&articleType=ArticleView&articleId=' + CONVERT(varchar, dbo.DnnForge_NewsArticles_Article.articleID)
ELSE 'LinkClick.aspx?link=' + url + '&tabid=' + CONVERT(varchar, tabid) + '&mid=' + CONVERT(varchar, dbo.tabmodules.moduleid) END AS Link,
dbo.DnnForge_NewsArticles_Article.AuthorID AS Userid, dbo.DnnForge_NewsArticles_Article.ModuleID, 'Full' AS LinkType, NULL AS ImageURL,
'None' AS ImageLinkType, '' AS ImageHeight, '' AS ImageWidth
FROM dbo.DnnForge_NewsArticles_Article LEFT OUTER JOIN
dbo.DnnForge_NewsArticles_ArticleCategories ON
dbo.DnnForge_NewsArticles_Article.ArticleID = dbo.DnnForge_NewsArticles_ArticleCategories.ArticleID LEFT OUTER JOIN
dbo.DnnForge_NewsArticles_Page ON dbo.DnnForge_NewsArticles_Article.ArticleID = dbo.DnnForge_NewsArticles_Page.ArticleID LEFT OUTER JOIN
dbo.Files ON RIGHT(dbo.DnnForge_NewsArticles_Article.URL, LEN(dbo.DnnForge_NewsArticles_Article.URL) - 7) = CONVERT(varchar, dbo.Files.FileId)
LEFT OUTER JOIN
dbo.TabModules ON dbo.DnnForge_NewsArticles_Article.ModuleID = dbo.TabModules.ModuleID
WHERE (dbo.DnnForge_NewsArticles_Article.IsApproved <> 0) AND (dbo.DnnForge_NewsArticles_Article.EndDate IS NULL OR
dbo.DnnForge_NewsArticles_Article.EndDate > GETDATE()) AND (dbo.DnnForge_NewsArticles_Article.StartDate < GETDATE()) AND
(dbo.DnnForge_NewsArticles_ArticleCategories.CategoryID <> 4 AND dbo.DnnForge_NewsArticles_ArticleCategories.CategoryID <> 6 OR
dbo.DnnForge_NewsArticles_ArticleCategories.CategoryID IS NULL) AND (dbo.DnnForge_NewsArticles_Page.SortOrder = 0)
ORDER BY PubDate DESC | | Wiki | Insert into #WhatsNew (Title, Description, PubDate, Link, Userid, ModuleID, LinkType, ImageURL, ImageLinkType, ImageHeight, ImageWidth)
SELECT TOP (@WikiRows) Name AS Title, 'Read more' AS Description, UpdateDate AS PubDate, 'topic=' + Name AS Link,
UpdatedByUserID as Userid, ModuleID, 'Part' AS LinkType, null as ImageURL, 'None' as ImageLinkType, '' as ImageHeight, '' as ImageWidth
FROM dbo.Wiki_Topic
ORDER BY PubDate DESC | | Output | SELECT DISTINCT
TOP (@Rows) #WhatsNew.Title, CASE @StripHTML WHEN 0 THEN LEFT(CONVERT(varchar(8000), #WhatsNew.Description), @DescriptionLength) ELSE LEFT(dbo.udf_StripHTML(CONVERT(varchar(8000), #WhatsNew.Description)), @DescriptionLength) END AS Description, CONVERT(varchar, #WhatsNew.PubDate,
103) AS OutDate,
CASE LinkType WHEN 'http' THEN Link WHEN 'Full' THEN 'http://www.thedivingclub.co.uk/' + Link WHEN 'Part' THEN 'http://www.thedivingclub.co.uk/Default.aspx?tabid='
+ CONVERT(varchar, dbo.TabModules.TabID) + '&' + #WhatsNew.Link END AS Link, dbo.Users.DisplayName AS Creator,
CASE ModuleTitle WHEN 'Dive' THEN 'Dive Calendar' WHEN 'Social' THEN 'Social Calendar' ELSE dbo.Modules.ModuleTitle END AS Category,
#WhatsNew.PubDate, Case ImageLinkType when 'None' then '' WHEN 'http' THEN ImageURL WHEN 'Full' THEN 'http://www.thedivingclub.co.uk/' + ImageURL WHEN 'Part' THEN 'http://www.thedivingclub.co.uk/Default.aspx?tabid='
+ CONVERT(varchar, dbo.TabModules.TabID) + '&' + #WhatsNew.ImageURL END AS ImageURL, ImageHeight, ImageWidth
FROM #WhatsNew INNER JOIN
dbo.Users ON #WhatsNew.Userid = dbo.Users.UserID INNER JOIN
dbo.Modules ON #WhatsNew.ModuleID = dbo.Modules.ModuleID INNER JOIN
dbo.TabModules ON dbo.Modules.ModuleID = dbo.TabModules.ModuleID
ORDER BY #WhatsNew.PubDate DESC | The query in the report is then very simple:- | EXEC tdc_Whats_New @Rows = 8,
@AnnouncementsRows = 3,
@EventsRows = 3,
@ForumRows1 = 5,
@ForumRows2 = 0,
@GalleryRows = 3,
@NewsArticlesRows = 3,
@NewsletterRows = 3,
@WikiRows = 2,
@DescriptionLength = 77,
@StripHTML = True | There are probably better ways of doing this, but it works.... I'm using the HTML Template Visualiser in the reports module, which looks like this:- | <a href="[Link]"><strong>[Title]</strong></a></br>
Posted: [OutDate]</br>
In: [ModuleTitle]</br>
By: [FullName]</br>
<span style="color: #606060">[Description]...</span></br>
<hr style="width: 200px; text-align: left"> | You'll notice a couple of functions shown to decode the HTML and the strip out the HTML tags: | HTMLDecode | CREATE FUNCTION [dbo].[udf_HTMLDecode] (@vcWhat varchar(8000))
RETURNS varchar(8000) AS
BEGIN
DECLARE @vcResult varchar(8000)
DECLARE @vcCrLf varchar(2)
DECLARE @siPos smallint,@vcEncoded varchar(7),@siChar smallint set @vcCrLF=char(13) + char(10) select @vcResult=@vcWhat
select @siPos=PatIndex('%&#___;%',@vcResult)
WHILE @siPos>0
BEGIN
select @vcEncoded=substring(@vcResult,@siPos,6)
select @siChar=cast(substring(@vcEncoded,3,3) as smallint)
select @vcResult=replace(@vcResult,@vcEncoded,nchar(@siChar))
select @siPos=PatIndex('%&#___;%',@vcResult)
END select @siPos=PatIndex('%&#____;%',@vcResult)
WHILE @siPos>0
BEGIN
select @vcEncoded=substring(@vcResult,@siPos,7)
select @siChar=cast(substring(@vcEncoded,3,4) as smallint)
select @vcResult=replace(@vcResult,@vcEncoded,nchar(@siChar))
select @siPos=PatIndex('%&#____;%',@vcResult)
END select @vcResult=replace(@vcResult,'"','"')
select @vcResult=replace(@vcResult,'&','&')
select @vcResult=replace(@vcResult,'©','©')
select @vcResult=replace(@vcResult,'«','«')
select @vcResult=replace(@vcResult,'»','»')
select @vcResult=replace(@vcResult,'¼','¼')
select @vcResult=replace(@vcResult,'½','½')
select @vcResult=replace(@vcResult,'¿','¿')
select @vcResult=replace(@vcResult,'<','<')
select @vcResult=replace(@vcResult,'>','>')
select @vcResult=replace(@vcResult,' ',' ') select @vcResult=replace(@vcResult,'<P>',@vcCrLf) return @vcResult
END | | StripHTML | CREATE FUNCTION [dbo].[udf_StripHTML]
(@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1
WHILE @Start > 0
AND @End > 0
AND @Length > 0
BEGIN
SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1
END
RETURN LTRIM(RTRIM(@HTMLText))
END |
Previous Page | Next Page
|
|
|
|
 |
|