cs_procedures.sql

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

GO
SET ANSI_NULLS ON 
GO



Create Proc [dbo].cs_Forums_ReAssignAndDeleteUser

(
	@CurrentUserName nvarchar(256),
	@NewUserName nvarchar(256),
	@SettingsID int
)

as

Declare @CUserID int
Declare @NUserID int
Declare @CGUserID uniqueidentifier
Declare @NGUserID uniqueidentifier

Declare @ApplicationID uniqueidentifier


--Find the Application
Select @ApplicationID = ApplicationID FROM aspnet_applications where ApplicationName in (Select ApplicationName FROM cs_SiteSettings where SettingsID = @SettingsID)

if(@ApplicationID is null)
Return -1

--Find the Guid UserID's
Select @CGUserID = UserID FROM aspnet_Users where  ApplicationID = @ApplicationID and UserName = @CurrentUserName
Select @NGUserID = UserID FROM aspnet_Users where  ApplicationID = @ApplicationID and UserName = @NewUserName

if(@CGUserID is null)
Return -2

if(@NGUserID is null)
Return -3

--Find the int UserID's
Select @CUserID = UserID FROM cs_UserProfile where MembershipID = @CGUserID and SettingsID = @SettingsID
Select @NUserID = UserID FROM cs_UserProfile where MembershipID = @NGUserID and SettingsID = @SettingsID

if(@CUserID is null)
Return -4

if(@NUserID is null)
Return -5

-- ReAssign Content

update cs_ModerationAudit
Set UserID = @NUserID 
where UserID = @CUserID

update cs_PostAttachments
Set UserID = @NUserID
where UserID = @CUserID


update cs_Posts
Set UserID = @NUserID, PostAuthor = convert(nvarchar(64), @NewUserName)
where UserID = @CUserID

update cs_PrivateMessages
Set UserID = @NUserID
where UserID = @CUserID

update cs_Threads
Set UserID = @NUserID, PostAuthor = convert(nvarchar(64), @NewUserName)
where UserID = @CUserID

update cs_Threads
Set MostRecentPostAuthorID = @NUserID, MostRecentPostAuthor = convert(nvarchar(64), @NewUserName)
where MostRecentPostAuthorID = @CUserID

update cs_Sections
Set MostRecentPostAuthorID = @NUserID, MostRecentPostAuthor = convert(nvarchar(64), @NewUserName)
where MostRecentPostAuthorID = @CUserID

-- Delete Content

Delete FROM cs_PostRating where UserID = @CUserID
Delete FROM cs_SectionsRead where UserID = @CUserID
Delete FROM cs_statistics_User where UserID = @CUserID
Delete FROM cs_TrackedSections where UserID = @CUserID
Delete FROM cs_ThreadsRead where UserID = @CUserID
Delete FROM cs_UserAvatar where UserID = @CUserID
Delete FROM cs_Votes where UserID = @CUserID
Delete FROM cs_UserProfile where UserID = @CUserID

Delete FROM aspnet_UsersInRoles where UserID = @CGUserID
Delete FROM aspnet_Profile where UserID = @CGUserID
Delete FROM aspnet_Membership where UserID = @CGUserID
Delete FROM aspnet_Users where UserID = @CGUserID


grant execute on [dbo].[cs_Forums_ReAssignAndDeleteUser] to public

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



Create Proc [dbo].cs_GetAnonymousUserID
(
	@SettingsID int,
	@UserID int output
)
as
SET Transaction Isolation Level Read UNCOMMITTED
Select @UserID = cs_UserID FROM cs_vw_Users_FullUser where SettingsID = @SettingsID and IsAnonymous = 1


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_GetSectionSubscriptionType
(
	@UserID int,
	@SectionID int,
	@SettingsID int,
	@SubType int OUTPUT
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
SELECT SubscriptionType FROM cs_TrackedSections WHERE SectionID=@SectionID AND UserID=@UserID and SettingsID = @SettingsID









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_GetTotalPostCount
(
	@SettingsID int
)

 AS
SET Transaction Isolation Level Read UNCOMMITTED
	SELECT TOP 1 
		TotalPosts 
	FROM 
		forums_Statistics where SettingsID = @SettingsID







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_GetUserIDByAppToken
(
	@AppUserToken varchar(128),
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
SELECT
	U.cs_UserID
FROM 
	cs_vw_Users_FullUser U (nolock)
WHERE 
	U.cs_AppUserToken = @AppUserToken and SettingsID = @SettingsID































GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



CREATE       PROCEDURE [dbo].cs_Group_CreateUpdateDelete
(
	@GroupID	int out,
	@Name		nvarchar(256),
	@ApplicationType smallint = 0,
	@Action 	int,
	@SettingsID 	int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
-- CREATE
IF @Action = 0
BEGIN
	DECLARE @SortOrder int

	SELECT @SortOrder = coalesce(MAX(SortOrder) + 1, 1) FROM cs_Groups where SettingsID = @SettingsID

	-- Create a new forum group
	INSERT INTO 
		cs_Groups 
		(
			Name,
			SortOrder,
			ApplicationType,
			SettingsID
		)
	VALUES 
		(
			@Name,
			@SortOrder,
			@ApplicationType,
			@SettingsID
		)
	
	SET @GroupID = @@IDENTITY
END


-- UPDATE
ELSE IF @Action = 1
BEGIN

	IF EXISTS(SELECT GroupID FROM cs_Groups WHERE GroupID = @GroupID)
	BEGIN
		UPDATE
			cs_Groups
		SET
			Name = @Name
		WHERE
			GroupID = @GroupID and SettingsID = @SettingsID
	END

END

-- DELETE
ELSE IF @Action = 2
BEGIN
	DELETE cs_Groups WHERE GroupID = @GroupID and SettingsID = @SettingsID
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_Groups_Get
(
	@SettingsID int,
	@ApplicationType smallint = 0,
	@RequireModeration bit = 0

)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	IF (@RequireModeration = 0)
		SELECT
			*
		FROM
			cs_Groups
		WHERE
			(SettingsID = @SettingsID) and ApplicationType = @ApplicationType
	ELSE
		SELECT DISTINCT
			FG.*
		FROM
			cs_Sections S,
			cs_Groups FG
		WHERE
			S.GroupID = FG.GroupID AND
			S.IsActive = 1 AND
			(SELECT Count(PostID) FROM cs_Posts P WHERE SectionID = S.SectionID AND P.IsApproved = 0) > 0 AND
			(FG.SettingsID = @SettingsID) AND
			FG.ApplicationType = @ApplicationType

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_Image_CreateUpdateDelete
(
    @UserID  int,
    @Content image,
    @ContentType nvarchar(64),
    @ContentSize int,
    @Action  int,
    @Filename nvarchar(256) = '',
    @SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN
    DECLARE @ImageID int

    -- Create
    IF @Action = 0 OR @Action = 1
    BEGIN
        -- Remove if already exists from tables: cs_Images, cs_UserAvatar
        SET @ImageID = (SELECT ImageID FROM cs_UserAvatar WHERE UserID = @UserID and SettingsID = @SettingsID)
        DELETE cs_Images WHERE ImageID = @ImageID and SettingsID = @SettingsID
        DELETE cs_UserAvatar WHERE UserID = @UserID and SettingsID = @SettingsID

        -- Add new entry
        -- 保存文件名 
        INSERT INTO cs_Images VALUES (@ContentSize, @ContentType, @Content, GetDate(), @SettingsID)
        SET @ImageID = @@Identity
 
        --文件名
        INSERT INTO cs_UserAvatar VALUES (@UserID, @ImageID, @FileName, @SettingsID)
    END
    ELSE IF @Action = 2
    BEGIN
        -- Remove if already exists from tables: forums_Images, forums_UserAvatar
        IF  EXISTS(SELECT UserID FROM cs_UserAvatar WHERE UserID = @UserID and SettingsID = @SettingsID)
        BEGIN
            SET @ImageID = (SELECT ImageID FROM cs_UserAvatar WHERE UserID = @UserID and SettingsID = @SettingsID)
            DELETE cs_Images WHERE ImageID = @ImageID and SettingsID = @SettingsID
        END

        IF  EXISTS(SELECT UserID FROM cs_UserAvatar WHERE UserID = @UserID and SettingsID = @SettingsID)
            UPDATE cs_UserAvatar SET ImageID = 0, [FileName] = @FileName WHERE UserID = @UserID
        ELSE
            INSERT INTO cs_UserAvatar VALUES (@UserID, 0, @FileName, @SettingsID)

    END
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF