-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-20
-- ------------------------------
-- 地区配送方式表
-- ==============================

--创建地区配送方式表
if not exists (select * from sysobjects where name='PPFOR_TZB_AreaDelivery')
CREATE TABLE [PPFOR_TZB_AreaDelivery] (
	[ID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[DeliveryID] int NOT NULL REFERENCES [PPFOR_TZB_Delivery]([DeliveryID]) ON DELETE CASCADE,	--地区配送方式ID	
	[IsCashOnDeliver] bit not null,											--是否支持货到付款 
	[InitialPrice] money not null default(0),								--首重费用
	[AdditionalPrice] money not null default(0),							--续重费用
	[AreaList] text not null												--配送地区列表
)
PPFOR_GO;

--添加地区配送方式
if exists(select name from sysobjects where name='PPFOR_TZB_AreaDelivery_Add' and type='P')
	drop procedure [PPFOR_TZB_AreaDelivery_Add]
PPFOR_GO;
create procedure PPFOR_TZB_AreaDelivery_Add(@ID int out,@DeliveryID int,
	@InitialPrice money,@AdditionalPrice money,@AreaList text)
as
	begin
		insert into [PPFOR_TZB_AreaDelivery] ([DeliveryID],[InitialPrice],
			[AdditionalPrice],[AreaList]) values (@DeliveryID,@InitialPrice,
			@AdditionalPrice,@AreaList)
		if @@ROWCOUNT>0
			set @ID=@@IDENTITY
		else
			set @ID=0
	end	
PPFOR_GO;

--更新地区配送方式
if exists(select name from sysobjects where name='PPFOR_TZB_AreaDelivery_Update' and type='P')
	drop procedure [PPFOR_TZB_AreaDelivery_Update]
PPFOR_GO;
create procedure PPFOR_TZB_AreaDelivery_Update(@ID int,@DeliveryID int,
	@InitialPrice money,@AdditionalPrice money,@AreaList text)
as	
	update [PPFOR_TZB_AreaDelivery] set [InitialPrice]=@InitialPrice,[AdditionalPrice]=@AdditionalPrice,
		[AreaList]=@AreaList where [DeliveryID]=@DeliveryID and [ID]=@ID
PPFOR_GO;

--删除地区配送方式
if exists(select name from sysobjects where name='PPFOR_TZB_AreaDelivery_Delete' and type='P')
	drop procedure [PPFOR_TZB_AreaDelivery_Delete]
PPFOR_GO;
create procedure PPFOR_TZB_AreaDelivery_Delete(@ID int)
as
	delete [PPFOR_TZB_AreaDelivery] where [ID]=@ID
PPFOR_GO;

--获取地区配送方式
if exists(select name from sysobjects where name='PPFOR_TZB_AreaDelivery_GetModel' and type='P')
	drop procedure [PPFOR_TZB_AreaDelivery_GetModel]
PPFOR_GO;
create procedure PPFOR_TZB_AreaDelivery_GetModel(@ID int)
as
	select * from [PPFOR_TZB_AreaDelivery] where [ID]=@ID
PPFOR_GO;

--获取地区配送方式列表
if exists(select name from sysobjects where name='PPFOR_TZB_AreaDelivery_GetModelList' and type='P')
	drop procedure [PPFOR_TZB_AreaDelivery_GetModelList]
PPFOR_GO;
create procedure PPFOR_TZB_AreaDelivery_GetModelList(@DeliveryID int)
as
	select * from [PPFOR_TZB_AreaDelivery] where [DeliveryID]=@DeliveryID order by [ID] asc
PPFOR_GO;

--获取地区配送方式ID列表
if exists(select name from sysobjects where name='PPFOR_TZB_AreaDelivery_GetIDList' and type='P')
	drop procedure [PPFOR_TZB_AreaDelivery_GetIDList]
PPFOR_GO;
create procedure PPFOR_TZB_AreaDelivery_GetIDList(@DeliveryID int)
as
	select [ID] from [PPFOR_TZB_AreaDelivery] where [DeliveryID]=@DeliveryID order by [ID] asc
PPFOR_GO;

--获取地区配送方式列表
if exists(select name from sysobjects where name='PPFOR_TZB_AreaDelivery_GetModelByArea' and type='P')
	drop procedure [PPFOR_TZB_AreaDelivery_GetModelByArea]
PPFOR_GO;
create procedure PPFOR_TZB_AreaDelivery_GetModelByArea(@DeliveryID int,@AreaID int,@BelongArea int,@IsChildAll bit)
as
	declare @caseStr varchar(20)
	if @IsChildAll=0	
		set @caseStr=replace('%|'+str(@AreaID)+':'+str(@BelongArea)+'[:|]%',' ','')
	else
		set @caseStr=replace('%|'+str(@AreaID)+':'+str(@BelongArea)+':1|%',' ','')	
	select top 1 [ID],[DeliveryID],[InitialPrice],[AdditionalPrice]
	 	from [PPFOR_TZB_AreaDelivery] where [DeliveryID]=@DeliveryID
 	 	and [AreaList] like @caseStr order by [ID] asc
PPFOR_GO;

