SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_nntp_GetArticlesToPost]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_nntp_GetArticlesToPost]
GO

create procedure [dbo].cs_nntp_GetArticlesToPost
(
	@SectionID int
)
AS
DECLARE @MaxPostID int
DECLARE @ThreadID int
DECLARE @ParentID int
DECLARE @PostAuthor nvarchar(128)
DECLARE @Subject nvarchar(512)
DECLARE @PostDate datetime
DECLARE @SiteUrl nvarchar(512)

SET @MaxPostID = (SELECT max(PostID) FROM cs_nntp_Posts WHERE SectionID = @SectionID)

SET @SiteUrl = (	SELECT 
				SiteUrl 
			FROM 
				cs_Sites S, 
				cs_Sections SN,
				cs_SiteMappings F 
			WHERE 
				F.SiteID = S.SiteID AND 
				SN.SettingsID = F.SettingsID AND
				SN.SectionID = @SectionID
		)

CREATE TABLE #PostIndex 
(
	PostID int,
	NntpUniqueID nvarchar(1024)
)

-- Temporary table for values we need to operate on
INSERT INTO #PostIndex (PostID, NntpUniqueID)
SELECT 
	PostID = P.PostID,
	NntpUniqueID = ('<' + CAST(P.PostID as nvarchar(64)) + '-' + CAST(P.ThreadID as nvarchar(64)) + '-' + CAST(P.ParentID as nvarchar(64)) + '@' + (@SiteUrl) + '>')
FROM 
	cs_Posts P
WHERE 
	P.PostID > @MaxPostID AND
	P.SectionID = @SectionID

-- Return values
SELECT 
	NntpReferenceId = (SELECT NntpUniqueID FROM cs_nntp_Posts WHERE PostID = P.ParentID),
	NntpGroup = (SELECT NntpGroup FROM cs_nntp_NewsGroups WHERE SectionID = @SectionID),
	P1.NntpUniqueID,
	P.*
FROM 
	cs_Posts P,
	#PostIndex P1
WHERE 
	P.PostID = P1.PostID

-- Mark items as NNTP Posts
INSERT INTO cs_nntp_Posts
SELECT 
	P.PostID,
	P.SectionID,
	0,
	P1.NntpUniqueID,
	P.SettingsID
FROM 
	cs_Posts P,
	#PostIndex P1
WHERE 
	P.PostID = P1.PostID
	




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




grant execute on [dbo].[cs_nntp_GetArticlesToPost] to public
go