/* Update sprocs to fix error which datetime value in culture other than EN-us */

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

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

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

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

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

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

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

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

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

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

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_ModeratePostReject
(
@PostID		int,
@RejectedBy	int,
@Notes		nvarchar(1024)
)
AS

DECLARE @UserID int
DECLARE @ThreadID int
DECLARE @ForumID int

-- first make sure that the post is ALREADY non-approved
IF (SELECT IsApproved FROM {objectQualifier}Forum_Posts (nolock) WHERE PostID = @PostID) = 0
BEGIN
	print 'Post is already locked'
	SELECT 0
	RETURN
END
ELSE
BEGIN
	print 'Post is not locked'
	-- Reject the post
	UPDATE 
		{objectQualifier}Forum_Posts
	SET 
		[IsApproved] = 0,
		[IsLocked] = 1
	WHERE 
		[PostID] = @PostID

	-- Get threadid & forumid of this post, and update forum moderate post count of the forum
	SELECT 
		@ThreadID = [ThreadID],
		@UserID = [UserID]
	FROM 
		{objectQualifier}Forum_Posts (nolock) 
	WHERE 
		PostID = @PostID
	SELECT @ForumID = [ForumID] FROM {objectQualifier}Forum_Threads (nolock) WHERE ThreadID = @ThreadID
	EXEC {objectQualifier}Forum_AA_ForumPostCountUpdate @ForumID, @ThreadID, @PostID, @UserID, 'reject'

-------------------------------------------------------------------------------
-- Since DNN2.0 field IsApproved has been removed from Forum_Threads
-------------------------------------------------------------------------------
--DECLARE @ForumID int
--DECLARE @ThreadID int
--DECLARE @PostLevel int
--DECLARE @UserID	int
--DECLARE @IsPinned	bit
--DECLARE @CreatedDate DateTime
--DECLARE @Image	nvarchar(255)
-- Get details about the thread and forum this post belongs in
--	SELECT		
--		@ThreadID	 = ThreadID,
--		@PostLevel	 = PostLevel,
--		@CreatedDate  	= CreatedDate,
--		@UserID	= UserID		
--	FROM
--		{objectQualifier}Forum_Posts (nolock) 
--	WHERE
--		PostID = @PostID
-------------------------------------------------------------------------------
-- Since DNN2.0 this is to be called from logic layer
-------------------------------------------------------------------------------		
--	SELECT  
--		@ForumID =  ForumID		
--	FROM 
--		{objectQualifier}Forum_Threads (nolock) 
--	WHERE
--		ThreadID = @ThreadID	
--	EXEC {objectQualifier}Forum_Moderate_ForumPostCount @ForumID, @PostID, @UserID, 'reject'	
--	EXEC {objectQualifier}Forum_Moderate_ModeratorPostCount @RejectedBy, @ForumID	
-------------------------------------------------------------------------------
	-- Increase moderator approved posts count
	INSERT INTO
		{objectQualifier}Forum_ModerationAudit
		(
		[ModeratedOn],
		[PostID],
		[ModeratedBy],
		[ModerationAction],
		[Notes]
		)
	VALUES
		(
		GetDate(), 
		@PostID, 
		@RejectedBy, 
		'Reject', 
		@Notes
		)
END
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_SearchGetResults
(
@Filter		nvarchar(500),
@PageIndex	int,
@PageSize	int,
@UserID		int,
@ModuleID	int,
@FromDate	datetime,
@ToDate	datetime
)
AS
-- Create a temp table to store the select results
CREATE TABLE #PageIndex 
(
	IndexID	int IDENTITY (1, 1) NOT NULL,
	ThreadID	int
)
-- Create dynamic SQL to populate temporary table
DECLARE @sql nvarchar(1000)
SELECT  @sql =	'INSERT INTO #PageIndex(ThreadID) SELECT DISTINCT({objectQualifier}Forum_Posts.ThreadID) ' + 	
			'FROM {objectQualifier}Forum_Posts (nolock) , {objectQualifier}Forum_Threads (nolock) , {objectQualifier}Forum_Forums (nolock) , {objectQualifier}Forum_Groups (nolock) ' +		
			'WHERE {objectQualifier}Forum_Posts.ThreadID = {objectQualifier}Forum_Threads.ThreadID AND ' +
			'{objectQualifier}Forum_Threads.ForumID = {objectQualifier}Forum_Forums.ForumID AND ' +		
			'{objectQualifier}Forum_Forums.GroupID = {objectQualifier}Forum_Groups.GroupID ' +	
			'AND {objectQualifier}Forum_Posts.IsApproved = 1 ' +
			'AND {objectQualifier}Forum_Posts.IsLocked = 0 ' +
			'AND {objectQualifier}Forum_Groups.ModuleID = ' + CONVERT(varchar, @ModuleID) + ' ' +
			@Filter + ' ORDER BY {objectQualifier}Forum_Posts.ThreadID DESC'
Print @sql
EXEC(@sql)
-- All of the rows are inserted into the table - now select the correct subset
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RecordCount	int
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
SELECT @RecordCount = COUNT(*) FROM #PageIndex

Print @RecordCount
-- Select the data out of the temporary table
-- Select the data out of the temporary table
SELECT
	PageIndex.ThreadID,	
	{objectQualifier}Forum_Posts.[Subject],
	{objectQualifier}Forum_Posts.[CreatedDate],
	{objectQualifier}Forum_Users.[UserID],
	{objectQualifier}Forum_Users.[Alias],
	{objectQualifier}Forum_Threads.[ForumID],
	{objectQualifier}Forum_Forums.[Name] As ForumName,
	RecordCount = @RecordCount,
	(SELECT Count({objectQualifier}Forum_Posts.PostID) FROM {objectQualifier}Forum_Posts (nolock) WHERE ThreadID = PageIndex.ThreadID) As PostCount,	
	(SELECT Count([ThreadID]) FROM {objectQualifier}Forum_ThreadRating  (nolock) WHERE ThreadID = PageIndex.ThreadID) As RatingCount,
	(SELECT AVG([Rate]) FROM {objectQualifier}Forum_ThreadRating  (nolock) WHERE ThreadID = PageIndex.ThreadID) As Rating,
	(SELECT Distinct  Count(UserID) FROM {objectQualifier}Forum_ThreadsRead (nolock) WHERE ForumID = {objectQualifier}Forum_Threads.[ForumID] AND UserID = {objectQualifier}Forum_Users.[UserID] AND {objectQualifier}Forum_Posts.[CreatedDate] > LastPurseDate AND ReadThreads NOT LIKE '%' + CONVERT(varchar, PageIndex.ThreadID) + ';%')  As IsUnRead
FROM
	{objectQualifier}Forum_Users (nolock) , {objectQualifier}Forum_Forums (nolock) , {objectQualifier}Forum_Threads (nolock) , {objectQualifier}Forum_Posts (nolock) , #PageIndex PageIndex (nolock) 
WHERE
	PageIndex.IndexID > @PageLowerBound AND
	PageIndex.IndexID < @PageUpperBound AND
	PageIndex.ThreadID = {objectQualifier}Forum_Posts.PostID AND	
	{objectQualifier}Forum_Users.[UserID] = {objectQualifier}Forum_Posts.[UserID] AND
	{objectQualifier}Forum_Posts.[ThreadID] = {objectQualifier}Forum_Threads.[ThreadID] AND 
	{objectQualifier}Forum_Posts.[CreatedDate] > @FromDate AND
	{objectQualifier}Forum_Posts.[CreatedDate] < @ToDate AND
	{objectQualifier}Forum_Threads.[ForumID] = {objectQualifier}Forum_Forums.[ForumID] AND
	({objectQualifier}Forum_Forums.ForumID NOT IN (SELECT ForumID FROM {objectQualifier}Forum_PrivateForums) OR 
	{objectQualifier}Forum_Forums.ForumID IN (SELECT ForumID FROM {objectQualifier}Forum_PrivateForums (nolock) WHERE RoleID IN (SELECT RoleID FROM {objectQualifier}UserRoles (nolock) WHERE UserID = @UserID))) 	
ORDER BY
	PageIndex.IndexID
GO


CREATE procedure {databaseOwner}{objectQualifier}Forum_WhatsNewGetAll
(
	@ModuleID	int,
	@NumberOfThread	int,	
	@UserID		int,	
	@FromDate		datetime,
	@ToDate		datetime,
	@Filter		nvarchar(500)
)
AS
-- Create a temp table to store the select results
CREATE TABLE #PageIndex 
(
	IndexID		int IDENTITY (1, 1) NOT NULL,
	PostID	int)
DECLARE @sql nvarchar(1000)
SELECT @sql = 	'INSERT INTO #PageIndex (PostID)' +
		' SELECT Max(PostID) As PostID' +
		' FROM {objectQualifier}Forum_Posts, {objectQualifier}Forum_Threads, {objectQualifier}Forum_Forums, {objectQualifier}Forum_Groups (nolock) ' +	
		' WHERE ModuleID = ' +  CONVERT(varchar, @ModuleID) +
		' AND {objectQualifier}Forum_Groups.GroupID = {objectQualifier}Forum_Forums.GroupID' +
		' AND {objectQualifier}Forum_Forums.ForumID = {objectQualifier}Forum_Threads.ForumID' +
		' AND {objectQualifier}Forum_Threads.ThreadID = {objectQualifier}Forum_Posts.ThreadID' +		
		' AND {objectQualifier}Forum_Posts.IsApproved = 1' +
		' AND {objectQualifier}Forum_Posts.IsLocked = 0' +
		' AND ({objectQualifier}Forum_Threads.ForumID NOT IN (SELECT ForumID FROM {objectQualifier}Forum_PrivateForums) OR' + 
		' {objectQualifier}Forum_Threads.ForumID IN (SELECT ForumID FROM {objectQualifier}Forum_PrivateForums (nolock) ' +
		' WHERE RoleID IN (SELECT RoleID FROM {objectQualifier}UserRoles (nolock) WHERE UserID =' + CONVERT(varchar, @UserID) + 		
		')))' +
		@Filter + 
		' GROUP BY {objectQualifier}Forum_Posts.ThreadID'
Print @sql
EXEC(@sql)
SELECT 	
	G.ModuleID,       	
        	T.ForumID,
	F.Name AS ForumName,
        	P1.ThreadID,
	P1.PostID As LastPostID,
        	P2.UserID As StartedByUserID,
        	U2.Alias As StartedByAlias,
	P2.Subject As StartedSubject,
        	P1.Subject As LastSubject, 
	P1.UserID As LastPostUserID,
        	U1.Alias As LastPostAlias,
       	P1.Body As LastPostBody,
        	P1.CreatedDate As LastCreatedDate
FROM 
	#PageIndex PageIndex (nolock), {objectQualifier}Forum_Posts P1 (nolock), {objectQualifier}Forum_Posts P2 (nolock), {objectQualifier}Forum_Threads T (nolock), {objectQualifier}Forum_Forums F (nolock), {objectQualifier}Forum_Groups G (nolock), {objectQualifier}Modules M (nolock), {objectQualifier}Forum_Users U1 (nolock), {objectQualifier}Forum_Users U2 (nolock)
WHERE	
	P1.PostID = PageIndex.PostID AND
	P1.CreatedDate > @FromDate AND
	P1.CreatedDate < @ToDate AND
	P1.ThreadID = T.ThreadID AND
	P1.ThreadID = P2.PostID AND 	
	P1.UserID = U1.UserID AND	
	P2.UserID = U2.UserID AND
	T.ForumID = F.ForumID AND
	F.GroupID = G.GroupID AND
	G.ModuleID = M.ModuleID	 AND
	PageIndex.IndexID <= @NumberOfThread
ORDER BY
	PageIndex.IndexID DESC
GO