-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-20
-- ------------------------------
-- 会员等级表
-- ==============================

--创建会员等级表
if not exists(select name from sysobjects where name='PPFOR_TZB_MemberDegree' and type='U')
CREATE TABLE [PPFOR_TZB_MemberDegree] (
	[ID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[MemberDegreeName] nvarchar(32) COLLATE Chinese_PRC_CS_AS NOT NULL unique,
	[MemberDegreePercentage] tinyint NOT NULL DEFAULT (100),
	[IfDefault] bit NOT NULL DEFAULT (0),
	[DegreeType] int NOT NULL REFERENCES [PPFOR_TZB_DegreeType](DegreeTypeID) ON DELETE CASCADE,
	[NeedPoints] int NOT NULL default(0),
	[MemberFields] text NULL
)
PPFOR_GO;

--添加会员等级
if exists(select name from sysobjects where name='PPFOR_TZB_MemberDegree_Add' and type='P')
	drop procedure PPFOR_TZB_MemberDegree_Add
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_MemberDegree_Add(@ID int output,@MemberDegreeName nvarchar(32),
	@MemberDegreePercentage tinyint,@IfDefault bit,@DegreeType int,@NeedPoints int,
	@MemberFields text)
 AS 
	begin
		INSERT INTO [PPFOR_TZB_MemberDegree]([MemberDegreeName],[MemberDegreePercentage],
			[IfDefault],[DegreeType],[NeedPoints],[MemberFields])VALUES(@MemberDegreeName,
			@MemberDegreePercentage,@IfDefault,@DegreeType,@NeedPoints,@MemberFields)
		SET @ID = @@IDENTITY
	end
PPFOR_GO;

--删除会员等级
if exists(select name from sysobjects where name='PPFOR_TZB_MemberDegree_Delete' and type='P')
	drop procedure PPFOR_TZB_MemberDegree_Delete
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_MemberDegree_Delete(@ID int)
 AS 
	DELETE [PPFOR_TZB_MemberDegree] WHERE [ID]=@ID 
PPFOR_GO;

--是否存在会员等级
if exists(select name from sysobjects where name='PPFOR_TZB_MemberDegree_IsExists' and type='P')
	drop procedure PPFOR_TZB_MemberDegree_IsExists
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_MemberDegree_IsExists(@DegreeName nvarchar(32),@retValue bit out)
AS
	begin
		DECLARE @TempID int
		SELECT @TempID = count(1) FROM [PPFOR_TZB_MemberDegree] 
			WHERE [MemberDegreeName]=@DegreeName
		IF @TempID = 0
			set @retValue= 0
		ELSE
			set @retValue= 1
	end
PPFOR_GO;

--是否存在默认会员等级
if exists(select name from sysobjects where name='PPFOR_TZB_MemberDegree_IsExistsDefault' and type='P')
	drop procedure PPFOR_TZB_MemberDegree_IsExistsDefault
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_MemberDegree_IsExistsDefault(@retValue bit out)
AS
	begin
		DECLARE @TempID int
		SELECT @TempID = count(1) FROM [PPFOR_TZB_MemberDegree] WHERE IfDefault=1 
		IF @TempID = 0
			set @retValue= 0
		ELSE
			set @retValue= 1
	end
PPFOR_GO;

--获取所有会员等级
if exists(select name from sysobjects where name='PPFOR_TZB_MemberDegree_GetModelList' and type='P')
	drop procedure PPFOR_TZB_MemberDegree_GetModelList
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_MemberDegree_GetModelList
 AS 
	SELECT [ID],MemberDegreeName,MemberDegreePercentage,IfDefault,DegreeType,DegreeTypeName,
		NeedPoints,MemberFields FROM [PPFOR_TZB_MemberDegree],[PPFOR_TZB_DegreeType] 
		where PPFOR_TZB_MemberDegree.DegreeType=PPFOR_TZB_DegreeType.DegreeTypeID
PPFOR_GO;

--获取会员等级
if exists(select name from sysobjects where name='PPFOR_TZB_MemberDegree_GetModel' and type='P')
	drop procedure PPFOR_TZB_MemberDegree_GetModel
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_MemberDegree_GetModel(@ID int)
 AS 
	SELECT [ID],MemberDegreeName,MemberDegreePercentage,IfDefault,DegreeType,DegreeTypeName,
		NeedPoints,MemberFields FROM [PPFOR_TZB_MemberDegree],[PPFOR_TZB_DegreeType]
		WHERE PPFOR_TZB_MemberDegree.[ID]=@ID 
PPFOR_GO;

--获取默认会员等级
if exists(select name from sysobjects where name='PPFOR_TZB_MemberDegree_GetModelDefault' and type='P')
	drop procedure PPFOR_TZB_MemberDegree_GetModelDefault
PPFOR_GO;

create  PROCEDURE PPFOR_TZB_MemberDegree_GetModelDefault
 AS 
	select top 1 [ID],MemberDegreeName,MemberDegreePercentage,IfDefault,DegreeType,
		DegreeTypeName,NeedPoints,MemberFields FROM [PPFOR_TZB_MemberDegree],[PPFOR_TZB_DegreeType] 
		where PPFOR_TZB_MemberDegree.IfDefault=1 and PPFOR_TZB_MemberDegree.DegreeType=PPFOR_TZB_DegreeType.DegreeTypeID
		order by PPFOR_TZB_MemberDegree.[ID] desc
PPFOR_GO;

--更新会员等级
if exists(select name from sysobjects where name='PPFOR_TZB_MemberDegree_Update' and type='P')
	drop procedure PPFOR_TZB_MemberDegree_Update
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_MemberDegree_Update(@ID int,@MemberDegreeName nvarchar(32),
	@MemberDegreePercentage tinyint,@IfDefault bit,@DegreeType int,@NeedPoints int,
	@MemberFields text)
 AS 
	UPDATE [PPFOR_TZB_MemberDegree] SET	[MemberDegreeName] = @MemberDegreeName,
	[MemberDegreePercentage] = @MemberDegreePercentage,[IfDefault] = @IfDefault,
	[DegreeType] = @DegreeType,[NeedPoints] = @NeedPoints,[MemberFields]=@MemberFields
	WHERE [ID]=@ID 
PPFOR_GO;


