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

--创建会员表
if not exists(select name from sysobjects where name='PPFOR_TZB_Member' and type='U')
CREATE TABLE [PPFOR_TZB_Member] (
	[_ID] int IDENTITY (1000, 1) NOT NULL PRIMARY KEY,
	[_MemberName] nvarchar (20) COLLATE Chinese_PRC_CS_AS NOT NULL unique,	--会员账号
	[_MemberEmail] varchar (100) COLLATE Chinese_PRC_CS_AS NOT NULL ,		--会员邮箱地址
	[_MemberDegree] int NOT NULL REFERENCES [PPFOR_TZB_MemberDegree]([ID]) ON DELETE CASCADE,	--会员等级
	[_MemberPassword] char (32) COLLATE Chinese_PRC_CS_AS NOT NULL ,		--会员密码
	[_MemberPoints] int DEFAULT (0),							--会员可用积分
	[_MemberTotalPoints] int NOT NULL DEFAULT (0),				--会员总积分
	[_MemberMoney] money NOT NULL,								--会员预存款
	[_RegeditTime] datetime NOT NULL ,							--注册时间
	[_RegeditIP] varchar(15) NOT NULL ,							--注册IP
	[_IsLocked] bit NOT NULL DEFAULT (0),						--是否被锁定
	[_LoginTime] datetime NULL ,								--登录时间
	[_LoginIP] varchar (15) NULL ,								--登录IP
	[_LoginNumber] int NOT NULL DEFAULT (0),					--登录次数
	[_MemberDetail] ntext NULL ,								--备注信息
	[_MemberCode] char(32) COLLATE Chinese_PRC_CS_AS NULL,		--附加串
	[_LoginTimes] int not null default(0)
)
PPFOR_GO;

--添加会员，返回添加的会员id
if exists(select name from sysobjects where name='PPFOR_TZB_Member_Add' and type='P')
	drop procedure PPFOR_TZB_Member_Add
PPFOR_GO;
create procedure PPFOR_TZB_Member_Add (@MemberName nvarchar (20),@MemberEmail varchar(100),
	@MemberDegree int,@MemberPassword char(32),@RegeditTime datetime, @RegeditIP varchar(15),
	@IsLocked bit,@MemberDetail ntext,@ID int output)
as
	begin
		if not exists(select [_ID] from PPFOR_TZB_Member where [_MemberName]=@MemberName)
			begin
				insert into PPFOR_TZB_Member values(@MemberName,@MemberEmail,@MemberDegree,@MemberPassword,
					0,0,0,@RegeditTime,@RegeditIP,@IsLocked,null,null,0,@MemberDetail,null,0)				
				set @ID=@@identity	
				insert into PPFOR_TZB_CustomMember([_MemberID]) values (@ID)			
			end
		else
			set @ID=0
	end
PPFOR_GO;

--管理员更新会员设置,不包括更新账号和密码
if exists(select name from sysobjects where name='PPFOR_TZB_Member_Update' and type='P')
	drop procedure PPFOR_TZB_Member_Update
PPFOR_GO;
create procedure PPFOR_TZB_Member_Update(@MemberName nvarchar (20),@MemberEmail varchar (100),
	@MemberDegree int,@IsLocked bit,@MemberDetail ntext)
as
	update PPFOR_TZB_Member set [_MemberDegree]=@MemberDegree,[_MemberEmail]=@MemberEmail,
	[_IsLocked]=@IsLocked, [_MemberDetail]=@MemberDetail where [_MemberName]=@MemberName
PPFOR_GO;

--管理员更新会员积分
if exists(select name from sysobjects where name='PPFOR_TZB_Member_UpdatePoints' and type='P')
	drop procedure PPFOR_TZB_Member_UpdatePoints
PPFOR_GO;
create procedure PPFOR_TZB_Member_UpdatePoints(@ID int,@MemberPoints int,@MemberTotalPoints int)
as
	update [PPFOR_TZB_Member] set [_MemberPoints]=@MemberPoints,
		[_MemberTotalPoints]=@MemberTotalPoints where [_ID]=@ID
PPFOR_GO;

--管理员更新会员预存款
if exists(select name from sysobjects where name='PPFOR_TZB_Member_UpdateMoney' and type='P')
	drop procedure PPFOR_TZB_Member_UpdateMoney
PPFOR_GO;
create procedure PPFOR_TZB_Member_UpdateMoney(@ID int,@Money money)
as
	if @Money>0		
		update [PPFOR_TZB_Member] set [_MemberMoney]=[_MemberMoney]+@Money where [_ID]=@ID
	else if @Money<0
		update [PPFOR_TZB_Member] set [_MemberMoney]=[_MemberMoney]+@Money where [_ID]=@ID
			and [_MemberMoney]+@Money>=0
PPFOR_GO;

--更新会员密码
if exists(select name from sysobjects where name='PPFOR_TZB_Member_UpdatePassword' and type='P')
	drop procedure PPFOR_TZB_Member_UpdatePassword
PPFOR_GO;
create procedure PPFOR_TZB_Member_UpdatePassword(@MemberName nvarchar(20),@MemberPassword char(32))
as
	update PPFOR_TZB_Member set [_MemberPassword]=@MemberPassword where [_MemberName]=@MemberName
PPFOR_GO;

--会员更新会员密码
if exists(select name from sysobjects where name='PPFOR_TZB_Member_ChangePassword' and type='P')
	drop procedure PPFOR_TZB_Member_ChangePassword
PPFOR_GO;
create procedure PPFOR_TZB_Member_ChangePassword(@MemberName nvarchar(20),
	@oldMemberPassword char(32),@newMemberPassword char(32))
as
	update PPFOR_TZB_Member set [_MemberPassword]=@newMemberPassword 
		where [_MemberName]=@MemberName and [_MemberPassword]=@oldMemberPassword
PPFOR_GO;

--更新会员Email
if exists(select name from sysobjects where name='PPFOR_TZB_Member_UpdateEmail' and type='P')
	drop procedure PPFOR_TZB_Member_UpdateEmail
PPFOR_GO;
create procedure PPFOR_TZB_Member_UpdateEmail(@MemberName nvarchar(20),@MemberEmail varchar(100))
as
	update PPFOR_TZB_Member set [_MemberEmail]=@MemberEmail where [_MemberName]=@MemberName
PPFOR_GO;

--根据id删除会员
if exists(select name from sysobjects where name='PPFOR_TZB_Member_Delete' and type='P')
	drop procedure PPFOR_TZB_Member_Delete
PPFOR_GO;
create procedure PPFOR_TZB_Member_Delete(@ID int)
as
	delete from PPFOR_TZB_Member where [_ID]=@ID
	if @@rowcount>0
		begin
			exec PPFOR_TZB_MemberMessage_DeleteByMember @ID,0   --删除消息
		end
PPFOR_GO;

--根据会员账号删除会员
if exists(select name from sysobjects where name='PPFOR_TZB_Member_DeleteByName' and type='P')
	drop procedure PPFOR_TZB_Member_DeleteByName
PPFOR_GO;
create procedure PPFOR_TZB_Member_DeleteByName(@MemberName nvarchar(20))
as
	declare @memberID int
	select @memberID=[_ID] from PPFOR_TZB_Member where [_MemberName]=@MemberName
	delete PPFOR_TZB_Member where [_MemberName]=@MemberName
	if @@rowcount>0 and @memberID is not null
		begin
			exec PPFOR_TZB_MemberMessage_DeleteByMember @memberID,0   --删除消息
		end
PPFOR_GO;

--分页返回所有会员资料，不包括密码
if exists(select name from sysobjects where name='PPFOR_TZB_Member_GetModelListByPage' and type='P')
	drop procedure PPFOR_TZB_Member_GetModelListByPage
PPFOR_GO;
create procedure PPFOR_TZB_Member_GetModelListByPage(@pageSize int,@pageIndex int)
as
	begin
		declare @rc int
		declare @pc int
		declare @sqlStr nvarchar(1000)
		declare @sqlStr2 nvarchar(1000)
		
		if @pageSize<1
			set @pageSize=20

		select @rc=count([_ID]) from PPFOR_TZB_Member
		if @rc=0
			set @pc=0
		else
			set @pc = ceiling(@rc * 1.0 / @pageSize)

		if @pageIndex>@pc
			set @pageIndex=@pc
		else if @pageIndex<1
			set @pageIndex=1
		
		set @sqlStr2=' [_ID],[_MemberName],[_MemberEmail],[_MemberDegree],[_IsLocked],[_LoginTime],
			[_LoginIP],[_LoginNumber] from PPFOR_TZB_Member '
	
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [_ID] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [_ID] asc) Temptable order by [_ID] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by 
				[_ID] asc) Temptable order by [_ID] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--返回会员资料
if exists(select name from sysobjects where name='PPFOR_TZB_Member_GetModel' and type='P')
	drop procedure PPFOR_TZB_Member_GetModel
PPFOR_GO;
create procedure PPFOR_TZB_Member_GetModel(@ID int)
as
	select [_ID],[_MemberName],[_MemberEmail],[_MemberDegree],[_MemberPoints],[_MemberTotalPoints],
		[_MemberMoney],[_IsLocked],[_RegeditTime],[_RegeditIP],[_LoginTime],[_LoginIP],[_LoginNumber],
		[_MemberDetail],[_MemberCode] from PPFOR_TZB_Member where [_ID]=@ID
PPFOR_GO;

--返回会员资料
if exists(select name from sysobjects where name='PPFOR_TZB_Member_GetModelByName' and type='P')
	drop procedure PPFOR_TZB_Member_GetModelByName
PPFOR_GO;
create procedure PPFOR_TZB_Member_GetModelByName(@MemberName  nvarchar(20))
as
	select [_ID],[_MemberName],[_MemberEmail],[_MemberDegree],[_MemberPoints],[_MemberTotalPoints],[_MemberMoney],[_IsLocked],
	[_RegeditTime],[_RegeditIP],[_LoginTime],[_LoginIP],[_LoginNumber],[_MemberDetail],[_MemberCode]
	 from PPFOR_TZB_Member where [_MemberName]=@MemberName
PPFOR_GO;

--返回会员名是否已经存在
if exists(select name from sysobjects where name='PPFOR_TZB_Member_IsExists' and type='P')
	drop procedure PPFOR_TZB_Member_IsExists
PPFOR_GO;
create procedure PPFOR_TZB_Member_IsExists(@MemberName nvarchar(20),@retValue bit output)
as
	begin
		if exists(select [_ID] from PPFOR_TZB_Member where [_MemberName]=@MemberName)
			set @retValue=1
		else
			set @retValue=0
	end
PPFOR_GO;

--验证会员是否可以登录，成功@retValue为1，失败@retValue为0
if exists(select name from sysobjects where name='PPFOR_TZB_Member_CheckLogin' and type='P')
	drop procedure PPFOR_TZB_Member_CheckLogin
PPFOR_GO;
create procedure PPFOR_TZB_Member_CheckLogin(@MemberName nvarchar(20),@MemberPassword char(32), 
	@retValue bit output)
as
	begin
		if exists (select [_ID] from PPFOR_TZB_Member where [_MemberName]=@MemberName and 
			[_MemberPassword]=@MemberPassword and [_IsLocked]=0)	
			set @retValue=1
		else
			set @retValue=0
	end
PPFOR_GO;

--会员登录，成功@retValue为1，失败@retValue为0
if exists(select name from sysobjects where name='PPFOR_TZB_Member_Login' and type='P')
	drop procedure PPFOR_TZB_Member_Login
PPFOR_GO;
create procedure PPFOR_TZB_Member_Login(@MemberName nvarchar(20),@MemberPassword char(32),
	@MemberCode char(32),@lastIP varchar(15),@lastTime datetime,@retValue bit output)
as
	begin
		set @retValue=0
		update PPFOR_TZB_Member set [_MemberCode]=@MemberCode, [_LoginIP]=@lastIP,[_LoginTime]=@lastTime,
			[_LoginNumber]=[_LoginNumber]+1,@retValue=1 where [_MemberName]=@MemberName 
			and [_MemberPassword]=@MemberPassword and [_IsLocked]=0 			
	end
PPFOR_GO;

--根据ID锁定会员
if exists(select name from sysobjects where name='PPFOR_TZB_Member_Lock' and type='P')
	drop procedure PPFOR_TZB_Member_Lock
PPFOR_GO;
create procedure PPFOR_TZB_Member_Lock(@ID int)
as
	update PPFOR_TZB_Member set [_IsLocked]=1 where [_ID]=@ID
PPFOR_GO;

--根据ID解锁会员
if exists(select name from sysobjects where name='PPFOR_TZB_Member_UnLock' and type='P')
	drop procedure PPFOR_TZB_Member_UnLock
PPFOR_GO;
create procedure PPFOR_TZB_Member_UnLock(@ID int)
as
	update PPFOR_TZB_Member set [_IsLocked]=0 where [_ID]=@ID
PPFOR_GO;

--设置会员附加串码
if exists(select name from sysobjects where name='PPFOR_TZB_Member_SetCode' and type='P')
	drop procedure PPFOR_TZB_Member_SetCode
PPFOR_GO;
create procedure PPFOR_TZB_Member_SetCode(@MemberName nvarchar(20),@MemberCode char(32))
as
	update PPFOR_TZB_Member set [_MemberCode]=@MemberCode where [_MemberName]=@MemberName
PPFOR_GO;

--获取会员附加串码
if exists(select name from sysobjects where name='PPFOR_TZB_Member_GetCode' and type='P')
	drop procedure PPFOR_TZB_Member_GetCode
PPFOR_GO;
create procedure PPFOR_TZB_Member_GetCode(@MemberName nvarchar(20))
as
	select [_MemberCode] from PPFOR_TZB_Member where [_MemberName]=@MemberName
PPFOR_GO;

--会员找回密码
if exists(select name from sysobjects where name='PPFOR_TZB_Member_ResetPassword' and type='P')
	drop procedure PPFOR_TZB_Member_ResetPassword
PPFOR_GO;
create procedure PPFOR_TZB_Member_ResetPassword(@MemberName nvarchar(20),@MemberCode char(32),
	@newMemberPassword char(32), @result bit out)
as
	set @result=0
	if LEN(RTRIM(LTRIM(@MemberCode)))=32 and LEN(RTRIM(LTRIM(@newMemberPassword)))=32
		update PPFOR_TZB_Member set [_MemberPassword]=@newMemberPassword, @result=1 
		where [_MemberName]=@MemberName and [_MemberCode]=@MemberCode
	if @result=1
		update PPFOR_TZB_Member set _MemberCode=null where [_MemberName]=@MemberName
PPFOR_GO;