-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-05-08
-- ------------------------------
-- 物流公司表
-- ==============================

--创建物流公司表
if not exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_Logistics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [PPFOR_TZB_Logistics] (
	[ID] int identity(1,1) not null primary key,						--ID
	[Name] nvarchar (50) COLLATE Chinese_PRC_CS_AS not null unique,		--名称
	[Url] nvarchar(255) not null,	--网址
	[Order]	int not null default(0)	--排序
)
PPFOR_GO;

--是否存在物流公司名
if exists(select name from sysobjects where name='PPFOR_TZB_Logistics_IsExists' and type='P')
	drop procedure [PPFOR_TZB_Logistics_IsExists]
PPFOR_GO;
create procedure PPFOR_TZB_Logistics_IsExists(@Name nvarchar (50),@retValue bit out)
as
	begin			
		if exists ( select [ID] from [PPFOR_TZB_Logistics] where [Name]=@Name) 
			set @retValue=1
		else
			set @retValue=0
	end
PPFOR_GO;

--添加物流公司
if exists(select name from sysobjects where name='PPFOR_TZB_Logistics_Add' and type='P')
	drop procedure [PPFOR_TZB_Logistics_Add]
PPFOR_GO;
create procedure PPFOR_TZB_Logistics_Add(@ID int out, @Name nvarchar (50),
	@Url nvarchar (255),@Order int)
as
	begin				
		if not exists ( select [ID] from [PPFOR_TZB_Logistics] 
				where [Name]=@Name)  
			begin
				insert into [PPFOR_TZB_Logistics] ([Name],[Url],[Order])
					values (@Name,@Url,@Order)
				SET @ID = @@IDENTITY
			end
		else
			SET @ID = 0
	end
PPFOR_GO;

--更新物流公司
if exists(select name from sysobjects where name='PPFOR_TZB_Logistics_Update' and type='P')
	drop procedure [PPFOR_TZB_Logistics_Update]
PPFOR_GO;
create procedure PPFOR_TZB_Logistics_Update(@ID int, @Name nvarchar (50),
	@Url nvarchar (255),@Order int)
as
	begin		
		if not exists ( select [ID] from [PPFOR_TZB_Logistics] where [ID]<>@ID
				and [Name]=@Name) 
			update [PPFOR_TZB_Logistics] set [Name]=@Name,[Url]=@Url,
				[Order]=@Order where [ID]=@ID	
	end
PPFOR_GO;

--更新物流公司排序
if exists(select name from sysobjects where name='PPFOR_TZB_Logistics_UpdateOrder' and type='P')
	drop procedure [PPFOR_TZB_Logistics_UpdateOrder]
PPFOR_GO;
create procedure PPFOR_TZB_Logistics_UpdateOrder(@ID int,@Order int)
as
	begin		
		update [PPFOR_TZB_Logistics] set [Order]=@Order where [ID]=@ID
	end
PPFOR_GO;

--删除物流公司
if exists(select name from sysobjects where name='PPFOR_TZB_Logistics_Delete' and type='P')
	drop procedure [PPFOR_TZB_Logistics_Delete]
PPFOR_GO;
create procedure PPFOR_TZB_Logistics_Delete(@ID int)
as
	begin		
		delete [PPFOR_TZB_Logistics] where [ID]=@ID
	end
PPFOR_GO;

--删除物流公司
if exists(select name from sysobjects where name='PPFOR_TZB_Logistics_DeleteAll' and type='P')
	drop procedure [PPFOR_TZB_Logistics_DeleteAll]
PPFOR_GO;
create procedure PPFOR_TZB_Logistics_DeleteAll
as
	begin		
		truncate table [PPFOR_TZB_Logistics]
	end
PPFOR_GO;

--获取物流公司
if exists(select name from sysobjects where name='PPFOR_TZB_Logistics_GetModel' and type='P')
	drop procedure [PPFOR_TZB_Logistics_GetModel]
PPFOR_GO;
create procedure PPFOR_TZB_Logistics_GetModel(@ID int)
as
	begin		
		select [ID],[Name],[Url],[Order] 
			from [PPFOR_TZB_Logistics] where [ID]=@ID
	end
PPFOR_GO;

--获取所有物流公司
if exists(select name from sysobjects where name='PPFOR_TZB_Logistics_GetModelList' and type='P')
	drop procedure [PPFOR_TZB_Logistics_GetModelList]
PPFOR_GO;
create procedure PPFOR_TZB_Logistics_GetModelList
as
	begin		
		select [ID],[Name],[Url],[Order] from [PPFOR_TZB_Logistics]
			order by [Order] asc,[ID] asc
	end
PPFOR_GO;
