-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-19
-- ------------------------------
-- 订单表
-- ==============================
if not exists(select name from sysobjects where name='PPFOR_TZB_Order' and type='U')
create table PPFOR_TZB_Order
(
 [ID] int identity(1,1) not null primary key ([ID]),--主键ID
 BelongLanguage int not null REFERENCES [PPFOR_TZB_Language]([ID]) ON DELETE CASCADE,	--所属语言
 MemberID int not null,								--会员ID
 OrderNo varchar(18) not null unique,				--订单号
 OrderStatus tinyint not null,						--订单状态(等待发货、确认发货，等)
 OrderDate datetime not null,						--下单日期
 UpdateTime DateTime not null,						--最后修改时间
 SumPrice money not null,							--商品总金额
 SumPoint int not null,								--商品总积分
 SumWeight decimal(19,3) not null,					--商品重量
 Delivery int not null,								--配送方式
 DeliveryPrice money not null,						--配送费用
 Payment int not null,								--支付方式
 PaulPrice money not null,							--保价价格
 IsProtect bit not null,							--是否保价
 CommissionFee money not null,						--支付手续费
 Invoice nvarchar(255) null,						--发票抬头
 Tax money not null,								--税金
 IsInvoice bit not null,							--是否开发票
 PayCurrency int not null,							--支付币种
 OrderDiscount money not null,						--订单折扣或涨价
 OrderMoney money not null,							--订单总金额
 RecipientName nvarchar(100) not null,				--收货人姓名
 ContactPhone varchar(15) null, 					--联系手机
 ContactTel varchar(15) null,						--联系电话
 ContactEmail varchar(100) null,					--Email地址
 DeliveryTime varchar(100) null,					--发货时间
 ContactCode varchar(15) not null,					--邮政编码 
 DeliveryAddress nvarchar(255) not null,			--发货地址 
 DeliveryArea int not null,							--发货地区
 OrderPS nvarchar(255) null,						--订单附言
 PayCurRate decimal (10,4) not null default (1),	--支付币种汇率
 Payed money not null default (0),					--已支付金额	
 PaymentArg varchar(50) null						--支付参数
)
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Order_IsExists' and type='P')
drop procedure PPFOR_TZB_Order_IsExists
PPFOR_GO;

--判断是否存在订单号,是返回1,否返回0
create procedure PPFOR_TZB_Order_IsExists(@OrderNo varchar(16),@retValue bit output)
as
	begin
		if exists(select [ID] from [PPFOR_TZB_Order] where [OrderNo]=@OrderNo)
 			set @retValue=1
		else
			set @retValue=0
	end
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Order_Add' and type='P')
drop procedure PPFOR_TZB_Order_Add
PPFOR_GO;

--添加
create procedure PPFOR_TZB_Order_Add(@ID int output,@BelongLanguage int,@MemberID int,
	@OrderNo varchar(18),@OrderStatus tinyint,@OrderDate datetime,@UpdateTime DateTime,@SumPrice money,
	@SumPoint int,@SumWeight decimal(19,3),@Delivery int,@DeliveryPrice money,@Payment int,
	@PaulPrice money,@IsProtect bit,@CommissionFee money,@Invoice nvarchar(255),@Tax money,
	@IsInvoice bit,@PayCurrency int,@OrderDiscount money,@OrderMoney money,@RecipientName nvarchar(100),
	@ContactPhone varchar(15),@ContactTel varchar(15),@ContactEmail varchar(100),
	@DeliveryTime varchar(100),@ContactCode varchar(15),@DeliveryAddress nvarchar(255),@DeliveryArea int,
	@OrderPS nvarchar(255),@PayCurRate decimal (10,4),@Payed money,@PaymentArg varchar(50))
as
	begin
		insert into [PPFOR_TZB_Order]([BelongLanguage],[MemberID],[OrderNo],[OrderStatus],[OrderDate],[UpdateTime],[SumPrice],
		[SumPoint],[SumWeight],[Delivery],[DeliveryPrice],[Payment],[PaulPrice],[IsProtect],[CommissionFee],
		[Invoice],[Tax],[IsInvoice],[PayCurrency],[OrderDiscount],[OrderMoney],[RecipientName],[ContactPhone],
		[ContactTel],[ContactEmail],[DeliveryTime],[ContactCode],[DeliveryAddress],[DeliveryArea],[OrderPS],[PayCurRate],[Payed],[PaymentArg])
       	values(@BelongLanguage,@MemberID,@OrderNo,@OrderStatus,@OrderDate,@UpdateTime,@SumPrice,@SumPoint,@SumWeight,
		@Delivery,@DeliveryPrice,@Payment,@PaulPrice,@IsProtect,@CommissionFee,@Invoice,@Tax,@IsInvoice,
		@PayCurrency,@OrderDiscount,@OrderMoney,@RecipientName,@ContactPhone,@ContactTel,@ContactEmail,
		@DeliveryTime,@ContactCode,@DeliveryAddress,@DeliveryArea,@OrderPS,@PayCurRate,@Payed,@PaymentArg)
		if @@rowcount>0
			set @ID=@@identity
		else
			set @ID=0
	end
PPFOR_GO;

if exists(select * from sysobjects where name='PPFOR_TZB_Order_Update')
drop procedure PPFOR_TZB_Order_Update
PPFOR_GO;

--修改
create procedure PPFOR_TZB_Order_Update(@ID int,@BelongLanguage int,@MemberID int,@OrderNo varchar(18),@OrderStatus tinyint,
	@OrderDate datetime,@UpdateTime DateTime,@SumPrice money,@SumPoint int,@SumWeight decimal(19,3),
	@Delivery int,@DeliveryPrice money,@Payment int,@PaulPrice money,@IsProtect bit,@CommissionFee money,
 	@Invoice nvarchar(255),@Tax money,@IsInvoice bit,@PayCurrency int,@OrderDiscount money,@OrderMoney money,
	@RecipientName nvarchar(100),@ContactPhone varchar(15),@ContactTel varchar(15),@ContactEmail varchar(100),
	@DeliveryTime varchar(100),@ContactCode varchar(15),@DeliveryAddress nvarchar(255),@DeliveryArea int,
	@OrderPS nvarchar(255),@PayCurRate decimal (10,4),@Payed money,@PaymentArg varchar(50))
as
	update [PPFOR_TZB_Order] set [OrderStatus]=@OrderStatus,[UpdateTime]=@UpdateTime,[SumPrice]=@SumPrice,
	[SumPoint]=@SumPoint,[Delivery]=@Delivery,[DeliveryPrice]=@DeliveryPrice,[Payment]=@Payment,
	[PaulPrice]=@PaulPrice,[IsProtect]=@IsProtect,[SumWeight]=@SumWeight,[CommissionFee]=@CommissionFee,
	[Invoice]=@Invoice,[Tax]=@Tax,[IsInvoice]=@IsInvoice,[OrderDiscount]=@OrderDiscount,
	[OrderMoney]=@OrderMoney,[RecipientName]=@RecipientName,[ContactPhone]=@ContactPhone,
	[ContactTel]=ContactTel,[DeliveryTime]=@DeliveryTime,[ContactCode]=@ContactCode,
	[DeliveryAddress]=@DeliveryAddress,[DeliveryArea]=@DeliveryArea,[OrderPS]=@OrderPS,
	[PayCurRate]=@PayCurRate,[Payed]=@Payed,[PaymentArg]=@PaymentArg
	where [BelongLanguage]=@BelongLanguage and [ID]=@ID
PPFOR_GO;

if exists(select * from sysobjects where name='PPFOR_TZB_Order_Delete')
drop procedure PPFOR_TZB_Order_Delete
PPFOR_GO;

--删除
create procedure PPFOR_TZB_Order_Delete(@ID int)
as
	delete from [PPFOR_TZB_Order] where [ID]=@ID
PPFOR_GO;

if exists(select * from sysobjects where name='PPFOR_TZB_Order_GetModel')
drop procedure PPFOR_TZB_Order_GetModel
PPFOR_GO;

--获取详细信息
create procedure PPFOR_TZB_Order_GetModel(@ID int)
as
	select * from [PPFOR_TZB_Order] where [ID]=@ID
PPFOR_GO;

if exists(select * from sysobjects where name='PPFOR_TZB_Order_GetModelByNo')
drop procedure PPFOR_TZB_Order_GetModelByNo
PPFOR_GO;
--获取详细信息
create procedure PPFOR_TZB_Order_GetModelByNo(@OrderNo varchar(18))
as
	select * from [PPFOR_TZB_Order] where [OrderNo]=@OrderNo
PPFOR_GO;

if exists(select * from sysobjects where name='PPFOR_TZB_Order_GetModelCount')
drop procedure PPFOR_TZB_Order_GetModelCount
PPFOR_GO;
--获取会员订单数
create procedure PPFOR_TZB_Order_GetModelCount(@MemberID int)
as
	select count([ID]) from [PPFOR_TZB_Order] where [MemberID]=@MemberID
PPFOR_GO;

if exists(select * from sysobjects where name='PPFOR_TZB_Order_GetModelListByNo')
drop procedure PPFOR_TZB_Order_GetModelListByNo
PPFOR_GO;

--获取列表
create procedure PPFOR_TZB_Order_GetModelListByNo(@OrderNo varchar(18))
as
	begin 		
		select [ID],BelongLanguage,MemberID,OrderNo,OrderStatus,OrderDate,
		UpdateTime,SumPoint,SumWeight,PayCurrency,OrderMoney,
		RecipientName,ContactPhone,ContactTel,ContactEmail,ContactCode,DeliveryAddress,
		DeliveryArea from [PPFOR_TZB_Order] where OrderNo like '%'+@OrderNo+'%'
	end
PPFOR_GO;

if exists(select * from sysobjects where name='PPFOR_TZB_Order_GetModelListByName')
drop procedure PPFOR_TZB_Order_GetModelListByName
PPFOR_GO;

--获取列表
create procedure PPFOR_TZB_Order_GetModelListByName(@RecipientName varchar(100))
as
	begin 		
		select [ID],BelongLanguage,MemberID,OrderNo,OrderStatus,OrderDate,
		UpdateTime,SumPoint,SumWeight,PayCurrency,OrderMoney,
		RecipientName,ContactPhone,ContactTel,ContactEmail,ContactCode,DeliveryAddress,
		DeliveryArea from [PPFOR_TZB_Order] where RecipientName like '%'+@RecipientName+'%'
	end
PPFOR_GO;

if exists(select * from sysobjects where name='PPFOR_TZB_Order_GetAllModelList')
drop procedure PPFOR_TZB_Order_GetAllModelList
PPFOR_GO;

--获取列表
create procedure PPFOR_TZB_Order_GetAllModelList(@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=2
		select @rc=count([ID]) from [PPFOR_TZB_Order]
		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],BelongLanguage,MemberID,OrderNo,OrderStatus,OrderDate,UpdateTime,SumPoint,SumWeight,PayCurrency,OrderMoney,
		RecipientName,ContactPhone,ContactTel,ContactEmail,ContactCode,DeliveryAddress,
		DeliveryArea from [PPFOR_TZB_Order] '
	
		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) Temtable order by [ID] desc'
		else
			set @sqlStr='select top ' + str(@pageSize) + ' * from (select top ' 
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [ID] asc)
				Temtable order by [ID] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

if exists(select * from sysobjects where name='PPFOR_TZB_Order_GetModelList')
drop procedure PPFOR_TZB_Order_GetModelList
PPFOR_GO;

--获取列表
create procedure PPFOR_TZB_Order_GetModelList(@BelongLanguage int,@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=2
		select @rc=count([ID]) from [PPFOR_TZB_Order] where [BelongLanguage]=@BelongLanguage
		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],BelongLanguage,MemberID,OrderNo,OrderStatus,OrderDate,UpdateTime,SumPoint,SumWeight,PayCurrency,OrderMoney,
		RecipientName,ContactPhone,ContactTel,ContactEmail,ContactCode,DeliveryAddress,
		DeliveryArea from [PPFOR_TZB_Order] where [BelongLanguage]='+str(@BelongLanguage)
	
		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) Temtable order by [ID] desc'
		else
			set @sqlStr='select top ' + str(@pageSize) + ' * from (select top ' 
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [ID] asc)
				Temtable order by [ID] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;


if exists(select * from sysobjects where name='PPFOR_TZB_Order_GetModelListByMember')
drop procedure PPFOR_TZB_Order_GetModelListByMember
PPFOR_GO;
--获取列表
create procedure PPFOR_TZB_Order_GetModelListByMember(@MemberID int,@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=2
		select @rc=count([ID]) from [PPFOR_TZB_Order] 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=' [ID],BelongLanguage,MemberID,OrderNo,OrderStatus,OrderDate,UpdateTime,SumPoint,SumWeight,PayCurrency,OrderMoney,
		RecipientName,ContactPhone,ContactTel,ContactEmail,ContactCode,DeliveryAddress,
		DeliveryArea from [PPFOR_TZB_Order] where [MemberID]='+str(@MemberID)
	
		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) Temtable order by [ID] desc'
		else
			set @sqlStr='select top ' + str(@pageSize) + ' * from (select top ' 
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [ID] asc)
				Temtable order by [ID] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

