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

--创建配送方式表
if not exists (select * from sysobjects where name='PPFOR_TZB_Delivery')
CREATE TABLE [PPFOR_TZB_Delivery] (
	[DeliveryID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,					--配送方式ID
	[DeliveryName] nvarchar (50) COLLATE Chinese_PRC_CS_AS NOT NULL unique,	--配送方式名称
	[LogisticsCompany] int not null	,										--物流公司
	[Status] bit not null,													--是否启用
	[Order] int not null,													--排序
	[InitialWeight] int not null,											--首重
	[AdditionalWeight] int not null,										--续重
	[IsProtectable] bit not null,											--是否支持物流保价
 	[ProtectRates] decimal(6,3) not null default (0),					    --保价费率
	[ProtectMinPrice] money not null default(0),							--最低保费
	[IsSameSetting] bit not null,											--是否统一设置费用
	[IsDefaultable] bit not null,											--是否启用默认费用
	[IsCashOnDeliver] bit not null,											--是否支持货到付款 
	[InitialPrice] money not null default(0),								--首重费用
	[AdditionalPrice] money not null default(0),							--续重费用
	[Description] text null													--介绍内容
)
PPFOR_GO;

--是否存在配送方式
if exists(select name from sysobjects where name='PPFOR_TZB_Delivery_IsExists' and type='P')
	drop procedure [PPFOR_TZB_Delivery_IsExists]
PPFOR_GO;
create procedure PPFOR_TZB_Delivery_IsExists(@DeliveryName nvarchar(50),@retValue bit out)
as
	if exists (select [DeliveryID] from [PPFOR_TZB_Delivery] 
		where [DeliveryName]=@DeliveryName)
		set @retValue=1
	else
		set @retValue=0
PPFOR_GO;

--添加配送方式
if exists(select name from sysobjects where name='PPFOR_TZB_Delivery_Add' and type='P')
	drop procedure [PPFOR_TZB_Delivery_Add]
PPFOR_GO;
create procedure PPFOR_TZB_Delivery_Add(@DeliveryID int out,@DeliveryName nvarchar(50),
	@LogisticsCompany int,@Status bit,@Order int,@InitialWeight int,
	@AdditionalWeight int,@IsProtectable bit,@ProtectRates decimal(6,3),
	@ProtectMinPrice money,@IsSameSetting bit,@IsDefaultable bit,
	@IsCashOnDeliver bit,@InitialPrice money,@AdditionalPrice money,
	@Description text)
as
	begin
		insert into [PPFOR_TZB_Delivery] ([DeliveryName],[LogisticsCompany],[Status],
		[Order],[InitialWeight],[AdditionalWeight],[IsProtectable],[ProtectRates],
		[ProtectMinPrice],[IsSameSetting],[IsDefaultable],[IsCashOnDeliver],
		[InitialPrice],[AdditionalPrice],[Description]) values (@DeliveryName,
		@LogisticsCompany,@Status,@Order,@InitialWeight,@AdditionalWeight,
		@IsProtectable,@ProtectRates,@ProtectMinPrice,@IsSameSetting,@IsDefaultable,
		@IsCashOnDeliver,@InitialPrice,@AdditionalPrice,@Description)
		if @@ROWCOUNT>0
			set @DeliveryID=@@IDENTITY
		else
			set @DeliveryID=0
	end	
PPFOR_GO;

--更新配送方式
if exists(select name from sysobjects where name='PPFOR_TZB_Delivery_Update' and type='P')
	drop procedure [PPFOR_TZB_Delivery_Update]
PPFOR_GO;
create procedure PPFOR_TZB_Delivery_Update(@DeliveryID int out,@DeliveryName nvarchar(50),
	@LogisticsCompany int,@Status bit,@Order int,@InitialWeight int,
	@AdditionalWeight int,@IsProtectable bit,@ProtectRates decimal(6,3),
	@ProtectMinPrice money,@IsSameSetting bit,@IsDefaultable bit,
	@IsCashOnDeliver bit,@InitialPrice money,@AdditionalPrice money,
	@Description text)
as
	if not exists (select [DeliveryID] from [PPFOR_TZB_Delivery] 
		where [DeliveryID]<>@DeliveryID and [DeliveryName]=@DeliveryName)
			update [PPFOR_TZB_Delivery] set [DeliveryName]=@DeliveryName,
				[LogisticsCompany]=@LogisticsCompany,[Status]=@Status,[Order]=@Order,
				[InitialWeight]=@InitialWeight,[AdditionalWeight]=@AdditionalWeight,
				[IsProtectable]=@IsProtectable,[ProtectRates]=@ProtectRates,
				[ProtectMinPrice]=@ProtectMinPrice,[IsSameSetting]=@IsSameSetting,
				[IsDefaultable]=@IsDefaultable,[IsCashOnDeliver]=@IsCashOnDeliver,
				[InitialPrice]=@InitialPrice,[AdditionalPrice]=@AdditionalPrice,
				[Description]=@Description where [DeliveryID]=@DeliveryID
PPFOR_GO;

--更新配送方式排序
if exists(select name from sysobjects where name='PPFOR_TZB_Delivery_UpdateList' and type='P')
	drop procedure [PPFOR_TZB_Delivery_UpdateList]
PPFOR_GO;
create procedure PPFOR_TZB_Delivery_UpdateList(@DeliveryID int,@Order int,@Status bit)
as	
	update [PPFOR_TZB_Delivery] set [Order]=@Order,[Status]=@Status where [DeliveryID]=@DeliveryID
PPFOR_GO;

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

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

--获取配送方式列表
if exists(select name from sysobjects where name='PPFOR_TZB_Delivery_GetShortList' and type='P')
	drop procedure [PPFOR_TZB_Delivery_GetShortList]
PPFOR_GO;
create procedure PPFOR_TZB_Delivery_GetShortList
as
	begin
		declare @CashOnDeliver bit
		declare @NotCashOnDeliver bit
		set @CashOnDeliver=1
		set @NotCashOnDeliver=0
		select [DeliveryID],[DeliveryName],[LogisticsCompany],[Status],
			[Order],[IsProtectable],[IsCashOnDeliver] from [PPFOR_TZB_Delivery]
			order by [Order] asc, [DeliveryID] asc
	end
PPFOR_GO;

--获取配送方式列表
if exists(select name from sysobjects where name='PPFOR_TZB_Delivery_GetModelList' and type='P')
	drop procedure [PPFOR_TZB_Delivery_GetModelList]
PPFOR_GO;
create procedure PPFOR_TZB_Delivery_GetModelList
as
	select * from [PPFOR_TZB_Delivery]  order by [Order] asc, [DeliveryID] asc
PPFOR_GO;


