cs_procedures.sql

本系统是在asp版《在线文件管理器》的基础上设计制作

GO




CREATE PROCEDURE dbo.cs_LinkCategories_Get
	@SectionID int,
	@SettingsID int
AS
SET Transaction Isolation Level Read UNCOMMITTED

	SELECT *
	FROM cs_LinkCategories
	WHERE SectionID = @SectionID and SettingsID = @SettingsID
	Order By SortOrder


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


--exec dbo.cs_LinkCategory_CreateUpdateDelete @LinkCategoryID = 3, @DeleteLinkCategory = 1

CREATE PROCEDURE dbo.cs_LinkCategory_CreateUpdateDelete
	@DeleteLinkCategory bit=0,
	@SectionID int,
	@Name nvarchar(256)='',
	@Description nvarchar(2000)=null,
	@IsEnabled bit=1,
	@SortOrder int=0,
	@SettingsID int,
	@LinkCategoryID int=0 out
AS
SET Transaction Isolation Level Read UNCOMMITTED

-- Are we deleting?
if @DeleteLinkCategory = 1
begin
	DELETE FROM cs_Links WHERE LinkCategoryID = @LinkCategoryID and SettingsID = @SettingsID
	DELETE FROM cs_LinkCategories WHERE LinkCategoryID = @LinkCategoryID and SectionID = @SectionID and SettingsID = @SettingsID
	RETURN
end

-- Are we updating?
if @LinkCategoryID > 0
begin
	UPDATE cs_LinkCategories SET
		[Name] = @Name,
		[Description] = @Description,
		IsEnabled = @IsEnabled,
		SortOrder = @SortOrder
	WHERE LinkCategoryID = @LinkCategoryID and SectionID = @SectionID and SettingsID = @SettingsID
end
else
begin
	If( @SortOrder = 0 )
	Begin
		Select @SortOrder = max(SortOrder) + 1 from cs_LinkCategories where SectionID = @SectionID and SettingsID = @SettingsID

		if(@SortOrder is null)
			Select	@SortOrder = 0
	End
	INSERT INTO cs_LinkCategories (SectionID, [Name], [Description], IsEnabled, SortOrder, SettingsID)
		VALUES (@SectionID, @Name, @Description, @IsEnabled, @SortOrder, @SettingsID)
	set @LinkCategoryID = @@IDENTITY
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE procedure [dbo].cs_LinkCategory_UpdateSortOrder
( 
            @LinkCategoryID int, 
	    @SettingsID int,
            @MoveUp bit 
) 
AS 
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN 
	set nocount on
	
	DECLARE @currentSortValue int 
	DECLARE @replaceSortValue int 
	DECLARE @replaceLinkCategoryID int
	DECLARE @SectionID int
	
	-- Get the current sort order 
	SELECT @currentSortValue = SortOrder, @SectionID = SectionID FROM cs_LinkCategories WHERE LinkCategoryID = @LinkCategoryID  and SettingsID = @SettingsID
	
	-- Move the item up or down? 
	IF (@MoveUp = 1) 
	BEGIN 
		SELECT @replaceSortValue = coalesce(f.SortOrder, -1), @replaceLinkCategoryID = coalesce(f.LinkCategoryID, -1)
			FROM cs_LinkCategories f
				inner join (
					select top 1 * 
					from cs_LinkCategories 
					WHERE SectionID = @SectionID and SortOrder < @currentSortValue  and SettingsID = @SettingsID order by SortOrder DESC
				) as pf on 
					pf.LinkCategoryID = f.LinkCategoryID  and f.SettingsID = @SettingsID

		if( @replaceSortValue != -1 And @replaceSortValue is Not Null  )
		begin	
			UPDATE cs_LinkCategories SET SortOrder = @currentSortValue WHERE LinkCategoryID = @replaceLinkCategoryID  and SettingsID = @SettingsID
			UPDATE cs_LinkCategories SET SortOrder = @replaceSortValue WHERE LinkCategoryID = @LinkCategoryID  and SettingsID = @SettingsID
		END 
	END 
	ELSE 
	BEGIN 
		SELECT @replaceSortValue = coalesce(f.SortOrder, -1), @replaceLinkCategoryID = coalesce(f.LinkCategoryID, -1)
			FROM cs_LinkCategories f
				inner join (
					select top 1 * 
					FROM cs_LinkCategories 
					WHERE SectionID = @SectionID and SortOrder > @currentSortValue  and SettingsID = @SettingsID order by SortOrder ASC				
				) as pf on 
					pf.LinkCategoryID = f.LinkCategoryID  and f.SettingsID = @SettingsID


		if( @replaceSortValue != -1 And @replaceSortValue is Not Null  )
		begin		
			UPDATE cs_LinkCategories SET SortOrder = @currentSortValue WHERE LinkCategoryID = @replaceLinkCategoryID  and SettingsID = @SettingsID
			UPDATE cs_LinkCategories SET SortOrder = @replaceSortValue WHERE LinkCategoryID = @LinkCategoryID  and SettingsID = @SettingsID
		end
	END 
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE dbo.cs_Link_CreateUpdateDelete
	@DeleteLink bit=0,
	@LinkCategoryID int=0,
	@Title nvarchar(100)='',
	@Url nvarchar(255)='',
	@IsEnabled bit=1,
	@SortOrder int=0,
	@SettingsID int,
	@LinkID int=0 out
AS
SET Transaction Isolation Level Read UNCOMMITTED
-- Are we deleting?
if @DeleteLink = 1
begin
	DELETE FROM cs_Links WHERE LinkID = @LinkID
end

-- Are we updating?
if @LinkID > 0
begin
	UPDATE cs_Links SET
		Title = @Title,
		Url = @Url,
		IsEnabled = @IsEnabled,
		SortOrder = @SortOrder
	WHERE LinkID = @LinkID and LinkCategoryID = @LinkCategoryID and SettingsID = @SettingsID
end
else
begin
	If( @SortOrder = 0 )
	Begin
		Select @SortOrder = max(SortOrder) + 1 from cs_Links where LinkCategoryID = @LinkCategoryID and SettingsID = @SettingsID

		if(@SortOrder is null)
			Select @SortOrder = 0
	End
	INSERT INTO cs_Links (LinkCategoryID, Title, Url, IsEnabled, SortOrder, SettingsID)
		VALUES (@LinkCategoryID, @Title, @Url, @IsEnabled, @SortOrder, @SettingsID)
	set @LinkID = @@IDENTITY
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE procedure [dbo].cs_Link_UpdateSortOrder
( 
            @LinkID int,
	    @SettingsID int, 
            @MoveUp bit 
) 
AS 
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN 
	set nocount on
	
	DECLARE @currentSortValue int 
	DECLARE @replaceSortValue int 
	DECLARE @replaceLinkID int
	DECLARE @LinkCategoryID int
	
	-- Get the current sort order 
	SELECT @currentSortValue = SortOrder, @LinkCategoryID = LinkCategoryID FROM cs_Links WHERE LinkID = @LinkID  and SettingsID = @SettingsID

	-- Move the item up or down? 
	IF (@MoveUp = 1) 
	BEGIN 
		SELECT @replaceSortValue = coalesce(f.SortOrder, -1), @replaceLinkID = coalesce(f.LinkID, -1)
			FROM cs_Links f
				inner join (
					select top 1 * 
					from cs_Links 
					WHERE LinkCategoryID = @LinkCategoryID and SortOrder < @currentSortValue and SettingsID = @SettingsID order by SortOrder DESC
				) as pf on 
					pf.LinkID = f.LinkID and f.SettingsID = @SettingsID
		if( @replaceSortValue != -1 And @replaceSortValue is Not Null )
		begin	
			UPDATE cs_Links SET SortOrder = @currentSortValue WHERE LinkID = @replaceLinkID and SettingsID = @SettingsID
			UPDATE cs_Links SET SortOrder = @replaceSortValue WHERE LinkID = @LinkID and SettingsID = @SettingsID
		END 
	END 
	ELSE 
	BEGIN 
		SELECT @replaceSortValue = coalesce(f.SortOrder, -1), @replaceLinkID = coalesce(f.LinkID, -1)
			FROM cs_Links f
				inner join (
					select top 1 * 
					FROM cs_Links 
					WHERE LinkCategoryID = @LinkCategoryID and SortOrder > @currentSortValue and SettingsID = @SettingsID order by SortOrder ASC				
				) as pf on 
					pf.LinkID = f.LinkID and f.SettingsID = @SettingsID


		if( @replaceSortValue != -1 And @replaceSortValue is Not Null )
		begin		
			UPDATE cs_Links SET SortOrder = @currentSortValue WHERE LinkID = @replaceLinkID and SettingsID = @SettingsID
			UPDATE cs_Links SET SortOrder = @replaceSortValue WHERE LinkID = @LinkID and SettingsID = @SettingsID
		end
	END 
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE dbo.cs_Links_Get
	@LinkCategoryID int,
	@SettingsID int
AS
SET Transaction Isolation Level Read UNCOMMITTED

	SELECT *
	FROM cs_Links
	WHERE LinkCategoryID = @LinkCategoryID and SettingsID = @SettingsID
	Order By SortOrder


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE         PROCEDURE [dbo].cs_MarkPostAsRead
(
	@PostID	int,
	@UserName nvarchar (50),
	@SettingsID int
)
 AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	-- If @UserName is null it is an anonymous user
	IF @UserName IS NOT NULL
	BEGIN
		DECLARE @SectionID int
		DECLARE @PostDate datetime

		-- Mark the post as read
		-- *********************

		-- Only for PostLevel = 1
		IF EXISTS (SELECT PostID FROM cs_Posts WHERE PostID = @PostID AND PostLevel = 1 and SettingsID = @SettingsID)
			IF NOT EXISTS (SELECT HasRead FROM PostsReadx WHERE UserName = @UserName and PostID = @PostID and SettingsID = @SettingsID)
				INSERT INTO PostsRead (UserName, PostID, SettingsID) VALUES (@UserName, @PostID, @SettingsID)

	END

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


create procedure [dbo].cs_Message_CreateUpdateDelete
(
	@MessageID int,
	@Title NVarChar(1024),
	@Body NVarChar(4000),
	@Action int,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
-- CREATE
IF @Action = 0
BEGIN
	SELECT "Not Implemented"
END
-- UPDATE
ELSE IF @Action  = 1
BEGIN
	UPDATE
		cs_Messages
	SET
		Title = @Title,
		Body = @Body
	WHERE
		MessageID = @MessageID and SettingsID = @SettingsID
END

-- DELETE
ELSE IF @Action = 2
BEGIN
	SELECT "Not Implemented"
END	



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE         procedure [dbo].cs_Moderate_ApprovePost
(
	@PostID		int,
	@ApprovedBy	int,
	@SettingsID 	int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
DECLARE @SectionID 	int
DECLARE @ThreadID 	int
DECLARE @PostLevel 	int
DECLARE @UserID		int
DECLARE @IsLocked	bit

-- first make sure that the post is ALREADY non-approved
IF (SELECT IsApproved FROM cs_Posts (nolock) WHERE PostID = @PostID and SettingsID = @SettingsID) = 1
BEGIN
	print 'Post is already approved'
	SELECT 0
	RETURN
END
ELSE
BEGIN

	print 'Post is not approved'

	-- Get details about the thread and forum this post belongs in
	SELECT
		@SectionID = SectionID,
		@ThreadID = ThreadID,
		@PostLevel = PostLevel,
		@UserID	= UserID,
		@IsLocked = IsLocked
	FROM
		cs_Posts
	WHERE
		PostID = @PostID and SettingsID = @SettingsID

	-- Approve the post
	UPDATE 
		cs_Posts
	SET 
		IsApproved = 1
	WHERE 
		PostID = @PostID and SettingsID = @SettingsID

	-- Approved the thread if necessary
	IF @PostLevel = 1
		UPDATE
			cs_Threads
		SET
			IsApproved = 1
		WHERE
			ThreadID = @ThreadID and SettingsID = @SettingsID

	-- Update the user's post count
	exec cs_system_UpdateUserP