-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-19
-- ------------------------------
-- 收款历史表
-- ==============================

--收款历史表
if not exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_PaidHistory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [PPFOR_TZB_PaidHistory] (	
	[ID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[MemberName] nvarchar (20) COLLATE Chinese_PRC_CS_AS NOT NULL,	
	[OrderNo] varchar(18) not null,
	[PaidMoney] money not null,		
	[PaidType] tinyint not null,
	[PaidIP] varchar(15) not null,
	[PaidTime] datetime not null,
	[PayCurrency] char(3) not null,
	[Payment] varchar (50) not null,
	[IsConfirm] bit not null, 
	[Payer] nvarchar(20) null,	
	[RAccount] varchar(64) null,
	[RBank] varchar(128) null,
	[PAccount] varchar(64) null,	
	[PID] varchar(64) null,
	[Description] nvarchar(255) null
)
PPFOR_GO;

if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_PaidHistory_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_PaidHistory_Add]
PPFOR_GO;
create procedure PPFOR_TZB_PaidHistory_Add(@ID int output, @MemberName nvarchar (20),
	@OrderNo varchar(18),@PaidMoney money,@PaidType tinyint,@PaidIP varchar(15),@PaidTime datetime,
	@PayCurrency char(3),@Payment varchar (50),@Payer nvarchar(20),@RAccount varchar(64),
	@RBank varchar(128),@PAccount varchar(64),@PID varchar(64),@Description nvarchar(255))
as
	begin	
		insert [PPFOR_TZB_PaidHistory] ([MemberName],[OrderNo],[PaidMoney],[PaidType],[PaidIP],[PaidTime],
			[PayCurrency],[Payment],[IsConfirm],[Payer],[RAccount],[RBank],[PAccount],[PID],[Description]) 
				values (@MemberName,@OrderNo,@PaidMoney,@PaidType,@PaidIP,@PaidTime,@PayCurrency,
				@Payment,0,@Payer,@RAccount,@RBank,@PAccount,@PID,@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_PaidHistory_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_PaidHistory_Delete]
PPFOR_GO;
create procedure PPFOR_TZB_PaidHistory_Delete(@ID int)
as
	begin		
		delete [PPFOR_TZB_PaidHistory] where [ID]=@ID
	end
PPFOR_GO;

if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_PaidHistory_Confirm]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_PaidHistory_Confirm]
PPFOR_GO;
create procedure PPFOR_TZB_PaidHistory_Confirm(@ID int,@Payer nvarchar(20))
as
	begin		
		update [PPFOR_TZB_PaidHistory] set [IsConfirm]=1,[Payer]=@Payer where [ID]=@ID
	end
PPFOR_GO;

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

if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_PaidHistory_GetModelList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_PaidHistory_GetModelList]
PPFOR_GO;
create procedure PPFOR_TZB_PaidHistory_GetModelList(@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_PaidHistory]
		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,OrderNo,PaidMoney,PaidType,PaidIP,PaidTime,PayCurrency,Payment,IsConfirm,Payer,RAccount,RBank,PAccount,[PID] from [PPFOR_TZB_PaidHistory] '

		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [PaidTime] desc,[ID] desc'
		else if @pageIndex = @pc
			set @sqlStr='select * from (select top ' + str(@rc-@pageSize * (@pageIndex-1) )
				+ @sqlStr2 + ' order by [PaidTime] asc,[ID] asc) Temtable order by [PaidTime] desc,[ID] desc'
		else
			set @sqlStr='select top ' + str(@pageSize) + ' * from (select top ' 
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [PaidTime] asc,[ID] asc)
				Temtable order by [PaidTime] 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_PaidHistory_GetModelListByOrder]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_PaidHistory_GetModelListByOrder]
PPFOR_GO;
create procedure PPFOR_TZB_PaidHistory_GetModelListByOrder(@OrderNo varchar(18))
as
	begin
		select [ID],[MemberName],[OrderNo],[PaidMoney],[PaidType],[PaidIP],[PaidTime],[PayCurrency],
			[Payment],[IsConfirm],[Payer],[RAccount],[RBank],[PAccount],[PID] 
			from [PPFOR_TZB_PaidHistory] where [OrderNo]=@OrderNo		
	end
PPFOR_GO;

if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_PaidHistory_GetModelListByRAccount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_PaidHistory_GetModelListByRAccount]
PPFOR_GO;
create procedure PPFOR_TZB_PaidHistory_GetModelListByRAccount(@RAccount varchar(64))
as
	begin
		select [ID],[MemberName],[OrderNo],[PaidMoney],[PaidType],[PaidIP],[PaidTime],[PayCurrency],
			[Payment],[IsConfirm],[Payer],[RAccount],[RBank],[PAccount],[PID] 
			from [PPFOR_TZB_PaidHistory] where @RAccount is not null and @RAccount<>''
			and [RAccount]=@RAccount		
	end
PPFOR_GO;