03.02.04.sqldataprovider

Module articles for Dot Net Nuke 3.x.x , 4.x.x

/***************************************************************************/
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}Articles_GetArticles') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}Articles_GetArticles
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}Articles_GetArticles
   @PortalID	int,
   @ModuleID	int,
   @Categories 	varchar(100),
   @MaxNumber	int,
   @Age		int,
   @ShowAuthOnly	bit,
   @Featured	bit,
   @IgnorePublishDate	bit,
   @IgnoreExpired	bit,
   @SortField	varchar(100)
AS

--SET ROWCOUNT @MaxNumber

SELECT ItemID,
       PortalID,
       ModuleId,
       a.UserID,
       u.UserName,
       Title,
       Description,
       Article,
       Authed,
       Featured,
       ImageFile,
       NumberOfViews,
       CreatedDate,
       PublishDate,
       ExpireDate,
       LastModifiedDate
FROM {databaseOwner}{objectQualifier}Article a
INNER JOIN {databaseOwner}{objectQualifier}Users u ON a.UserID = u.UserID
WHERE PortalID = @PortalID
AND (@ModuleID = -1 OR (ModuleID = @ModuleID) OR (ModuleID is null))
AND (@Categories = '' OR ItemID in (
	SELECT DISTINCT ArticleID FROM {databaseOwner}{objectQualifier}ArticleCategories ac WHERE 		(ac.CategoryID IN (SELECT intValue FROM 	{databaseOwner}{objectQualifier}csvToInt(@Categories)))
))
AND (@Age = -1 OR (CreatedDate BETWEEN DateAdd(day, @Age, GetDate()) AND GetDate()))
AND (@IgnorePublishDate = 1 OR IsNull(PublishDate, 1) <= CONVERT(CHAR(8), GETDATE(), 112))
AND (@IgnoreExpired = 1 OR IsNull(ExpireDate, DateAdd(d, 1, GetDate())) > CONVERT(CHAR(8), GETDATE(), 112))
AND (@ShowAuthOnly = 0 OR Authed = 1)
AND (@Featured = 0 OR Featured = 1)
ORDER BY 
	CASE @SortField 
		WHEN 'PublishDate' THEN PublishDate 
		WHEN 'LastModifiedDate' THEN LastModifiedDate
		ELSE CreatedDate 
	END DESC, 
LastModifiedDate DESC

GO