03.00.00.noupgrade.sqldataprovider

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

/*****
ARTICLES 3.0 SCRIPT
*****/

if exists (select * from sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Articles_AddArticle') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Articles_AddArticle
GO

if exists (select * from sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Articles_DeleteArticle') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Articles_DeleteArticle
GO

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

if exists (select * from sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Articles_GetPortalSettings') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Articles_GetPortalSettings
GO

if exists (select * from sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Articles_GetArticle') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Articles_GetArticle
GO

if exists (select * from sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Articles_UpdateArticle') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Articles_UpdateArticle
GO

if exists (select * from sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Articles_UpdateArticleViews') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Articles_UpdateArticleViews
GO

/***************************************************************************/
CREATE TABLE {databaseOwner}{objectQualifier}Article (
	[ItemID] [int] IDENTITY (0, 1) NOT NULL ,
	[PortalId] [int] NOT NULL ,
	[ModuleId] [int] NULL,
	[CategoryId] [int] NOT NULL ,
	[UserId] [int] NOT NULL ,
	[CreatedDate] [datetime] NULL ,
	[Title] [nvarchar] (150) NULL ,
	[Description] [nvarchar] (2000) NULL ,
	[Authed] [bit] NOT NULL CONSTRAINT [DF_Articles_Authed] DEFAULT (0),
	[Featured] [bit] NOT NULL CONSTRAINT [DF_Articles_Featured] DEFAULT (0),
	[Article] [nText] COLLATE Latin1_General_CI_AS NULL ,
	[ImageFile] [nvarchar] (200) NULL ,
	[PublishDate] [datetime] NULL,
	[ExpireDate] [datetime] NULL
	[NumberOfViews] [int] NULL CONSTRAINT [DF_Articles_NumberOfViews] DEFAULT (0),
	CONSTRAINT [PK{objectQualifier}_Article] PRIMARY KEY  CLUSTERED
	(
		[ItemID]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_{objectQualifier}Articles_Users] FOREIGN KEY
	(
		[UserId]
	) REFERENCES {databaseOwner}{objectQualifier}Users (
		[UserID]
	)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/***************************************************************************/
CREATE PROCEDURE {databaseOwner}{objectQualifier}Articles_DeleteArticle
	@ItemId int
AS
DELETE FROM	{databaseOwner}{objectQualifier}Article
WHERE
	ItemId = @ItemId
GO

/***************************************************************************/
CREATE PROCEDURE {databaseOwner}Articles_GetArticles
   @PortalID	int,
   @PublishDate	datetime,
   @CategoryId		int,
   @MaxNumber		int,
   @Age      		int,
   @ShowAuthOnly	bit,
   @Featured   	bit,
   @SearchTerm		varchar(20)
AS

SET ROWCOUNT @MaxNumber

SELECT  {objectQualifier}Article.*, CategoryName
FROM {databaseOwner}{objectQualifier}Article
INNER JOIN {databaseOwner}{objectQualifier}Categories ON {objectQualifier}Article.CategoryID={objectQualifier}Categories.CategoryID
WHERE {objectQualifier}Article.PortalID = @PortalID
AND (@CategoryId = -1 OR {objectQualifier}Article.CategoryId = @CategoryId)
AND (@Age = -1 OR ({objectQualifier}Article.CreatedDate BETWEEN DateAdd(day, @Age, GetDate()) AND GetDate()))
AND (@ShowAuthOnly = 0 OR {objectQualifier}Article.Authed = 1)
AND (@Featured = 0 OR {objectQualifier}Article.Authed = 0)
AND (@SearchTerm = '' OR {objectQualifier}Article.Title LIKE '%' + @SearchTerm + '%')
ORDER BY {objectQualifier}Article.CreatedDate DESC
GO

/***************************************************************************/
CREATE  PROCEDURE {databaseOwner}{objectQualifier}Articles_GetArticle
	@ItemID int
AS
SELECT
    {objectQualifier}Article.*,
    {objectQualifier}Users.Username

FROM
    {databaseOwner}{objectQualifier}Article
INNER JOIN {databaseOwner}{objectQualifier}Users ON {objectQualifier}Article.UserId = {objectQualifier}Users.UserID

WHERE
    {objectQualifier}Article.ItemID = @ItemID
GO

/***************************************************************************/
CREATE PROCEDURE {databaseOwner}{objectQualifier}Articles_AddArticle

	@PortalId	int,
	@CategoryId	int,
	@UserId		int,
	@CreatedDate	datetime,
	@Title		nvarchar(150),
	@Description	nvarchar(2000),
	@Authed		bit,
	@Featured		bit,
	@Article	ntext,
	@ImageFIle	nvarchar(200)

AS

INSERT INTO
	{databaseOwner}{objectQualifier}Article(
		PortalId,
		CategoryId,
		UserId,
		CreatedDate,
		Title,
		Description,
		Authed,
		Featured,
		Article,
      ImageFile
	)
VALUES
	(
	@PortalId,
	@CategoryId,
	@UserId,
	@CreatedDate,
	@Title,
	@Description,
	@Authed,
	@Featured,
	@Article,
	@ImageFile
	)

GO

/***************************************************************************/
CREATE  PROCEDURE {databaseOwner}{objectQualifier}Articles_UpdateArticle

	@ItemId		int,
	@CategoryId	int,
	@UserId		int,
	@CreatedDate	datetime,
	@Title		nvarchar(150),
	@Description	nvarchar(2000),
	@Authed		bit,
	@Featured bit,
	@Article	ntext,
	@ImageFIle	nvarchar(200)
AS
UPDATE
	{databaseOwner}{objectQualifier}Article
SET
	CategoryId = @CategoryId,
	UserId = @UserId,
	CreatedDate = @CreatedDate,
	Title = @Title,
	Description = @Description,
	Authed = @Authed,
	Featured = @Featured,
	Article = @Article,
   ImageFile = @ImageFile

WHERE
	ItemId = @ItemId
GO

/***************************************************************************/
CREATE  PROCEDURE {databaseOwner}{objectQualifier}Articles_UpdateArticleViews
	@ItemId		int
AS
UPDATE
	{databaseOwner}{objectQualifier}Article
SET
	NumberOfViews = NumberOfViews + 1
WHERE
	ItemID = @ItemID
GO