-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-19
-- ------------------------------
-- 积分表
-- ==============================

--积分表
if not exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_MemberPoint]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [PPFOR_TZB_MemberPoint] (	
	[ID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[MemberID] int not null REFERENCES [PPFOR_TZB_Member]([_ID]) ON DELETE CASCADE,	
	[OrderID] int not null default(0),
	[Point] int not null,	
	[UpdateTime] datetime not null,
	[Description] nvarchar(255) null
)
PPFOR_GO;

if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_MemberPoint_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_MemberPoint_Add]
PPFOR_GO;
create procedure PPFOR_TZB_MemberPoint_Add(@ID int output, @MemberID int,@OrderID int,@Point int,
	@UpdateTime datetime,@Description nvarchar(255))
as
	begin	
		insert [PPFOR_TZB_MemberPoint] values (@MemberID,@OrderID,@Point,@UpdateTime,@Description)
		if @@ROWCOUNT>0
			set @ID=@@identity
		else 
			set @ID=0
	end
PPFOR_GO;

if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_MemberPoint_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_MemberPoint_Delete]
PPFOR_GO;
create procedure PPFOR_TZB_MemberPoint_Delete(@ID int)
as
	begin		
		delete [PPFOR_TZB_MemberPoint] where [ID]=@ID
	end
PPFOR_GO;

if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_MemberPoint_DeleteByOrder]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_MemberPoint_DeleteByOrder]
PPFOR_GO;
create procedure PPFOR_TZB_MemberPoint_DeleteByOrder(@MemberID int,@OrderID int)
as
	begin		
		delete [PPFOR_TZB_MemberPoint] where [MemberID]=@MemberID and [OrderID]=@OrderID and @OrderID>0
	end
PPFOR_GO;

if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_MemberPoint_GetModelList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_MemberPoint_GetModelList]
PPFOR_GO;
create procedure PPFOR_TZB_MemberPoint_GetModelList(@MemberID int,@pageSize int,@pageIndex int)
as
	begin
		declare @rc int
		declare @pc int
		declare @sqlStr nvarchar(1500)
		declare @sqlStr2 nvarchar(1000)

		if @pageSize < 1
			set @pageSize=20
		select @rc=count([ID]) from [PPFOR_TZB_MemberPoint] where [MemberID]=@MemberID
		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=' * from [PPFOR_TZB_MemberPoint] where [MemberID]='+str(@memberID)+' '

		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [UpdateTime] desc,[ID] desc'
		else if @pageIndex = @pc
			set @sqlStr='select * from (select top ' + str(@rc-@pageSize * (@pageIndex-1) )
				+ @sqlStr2 + ' order by [UpdateTime] asc,[ID] asc) Temtable order by [UpdateTime] desc,[ID] desc'
		else
			set @sqlStr='select top ' + str(@pageSize) + ' * from (select top ' 
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [UpdateTime] asc,[ID] asc)
				Temtable order by [UpdateTime] desc,[ID] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_MemberPoint_GetModelListByOrder]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_MemberPoint_GetModelListByOrder]
PPFOR_GO;
create procedure PPFOR_TZB_MemberPoint_GetModelListByOrder(@MemberID int, @OrderID int)
as
	begin
		select * from [PPFOR_TZB_MemberPoint] where [MemberID]=@MemberID and [OrderID]=@OrderID 
			and @OrderID>0 order by [UpdateTime] desc, [ID] desc
	end
PPFOR_GO;