-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-05-05
-- ------------------------------
-- 地区表
-- ==============================

--创建地区表
if not exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_Area]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [PPFOR_TZB_Area] (
	[AreaID] int identity(1,1) not null primary key,						--地区ID
	[AreaName] nvarchar (255) COLLATE Chinese_PRC_CS_AS not null,			--地区名称
	[BelongAreaID] int not null,				--所属上级地区
	[AreaOrder]	int not null default(0),		--排序
	[AreaDegree] int not null					--地区等级
)
PPFOR_GO;

--是否存在地区
if exists(select name from sysobjects where name='PPFOR_TZB_Area_IsExists' and type='P')
	drop procedure [PPFOR_TZB_Area_IsExists]
PPFOR_GO;
create procedure PPFOR_TZB_Area_IsExists(@AreaName nvarchar (255),
	@BelongAreaID int,@retValue bit out)
as
	begin			
		if exists ( select [AreaID] from [PPFOR_TZB_Area] 
				where [BelongAreaID]=@BelongAreaID and [AreaName]=@AreaName) 
			set @retValue=1
		else
			set @retValue=0
	end
PPFOR_GO;

--获取所有子地区ID，并以临时表格存储
if exists (select name from sysobjects where name='PPFOR_TZB_Area_GetChildIdsTable' and type='TF')
drop function PPFOR_TZB_Area_GetChildIdsTable
PPFOR_GO;
create function PPFOR_TZB_Area_GetChildIdsTable(@BelongAreaID int)   
	returns  @ChildIdsTable  table([AreaID] int)   
as   
  	begin 		
  		insert into @ChildIdsTable select [AreaID] from [PPFOR_TZB_Area] where [BelongAreaID]=@BelongAreaID   
		while @@rowcount>0   
		  	insert into @ChildIdsTable select a.[AreaID] from [PPFOR_TZB_Area] a,@ChildIdsTable b
			   where a.[BelongAreaID]=b.[AreaID] and a.[AreaID] not in(select [AreaID] from @ChildIdsTable)   
		return
	end   
PPFOR_GO; 

--是否可以更新的地区名称
if exists(select name from sysobjects where name='PPFOR_TZB_Area_IsUpdateable' and type='P')
	drop procedure [PPFOR_TZB_Area_IsUpdateable]
PPFOR_GO;
create procedure PPFOR_TZB_Area_IsUpdateable(@AreaID int,@AreaName nvarchar (255),
	@BelongAreaID int,@retValue bit out)
as
	begin			
		if exists ( select [AreaID] from [PPFOR_TZB_Area] where [AreaID]<>@AreaID 
			and [BelongAreaID]=@BelongAreaID and [AreaName]=@AreaName) 
			set @retValue=0
		else
			set @retValue=1
	end
PPFOR_GO;

--添加地区
if exists(select name from sysobjects where name='PPFOR_TZB_Area_Add' and type='P')
	drop procedure [PPFOR_TZB_Area_Add]
PPFOR_GO;
create procedure PPFOR_TZB_Area_Add(@AreaID int out, @AreaName nvarchar (255),
	@BelongAreaID int,@AreaOrder int,@AreaDegree int)
as
	begin				
		if not exists ( select [AreaID] from [PPFOR_TZB_Area] 
				where [BelongAreaID]=@BelongAreaID and [AreaName]=@AreaName)  
			begin
				insert into [PPFOR_TZB_Area] ([AreaName],[BelongAreaID],[AreaOrder],
					[AreaDegree])	values (@AreaName,@BelongAreaID,@AreaOrder,@AreaDegree)
				SET @AreaID = @@IDENTITY
			end
		else
			SET @AreaID = 0
	end
PPFOR_GO;

--更新地区
if exists(select name from sysobjects where name='PPFOR_TZB_Area_Update' and type='P')
	drop procedure [PPFOR_TZB_Area_Update]
PPFOR_GO;
create procedure PPFOR_TZB_Area_Update(@AreaID int, @AreaName nvarchar (255),
	@BelongAreaID int,@AreaOrder int,@AreaDegree int)
as
	begin		
		if not exists ( select [AreaID] from [PPFOR_TZB_Area] where [AreaID]<>@AreaID
				and [BelongAreaID]=@BelongAreaID and [AreaName]=@AreaName) 
			begin
				declare @OldAreaDegree int
				select @OldAreaDegree=[AreaDegree] from [PPFOR_TZB_Area] where [AreaID]=@AreaID
				if @OldAreaDegree is null or @OldAreaDegree<=0
					set @OldAreaDegree=1
				update [PPFOR_TZB_Area] set [AreaName]=@AreaName,[BelongAreaID]=@BelongAreaID,
					[AreaOrder]=@AreaOrder,[AreaDegree]=@AreaDegree where [AreaID]=@AreaID	
				if @@ROWCOUNT>0 and @OldAreaDegree!=@AreaDegree
					update [PPFOR_TZB_Area] set [AreaDegree]=[AreaDegree]+@AreaDegree-@OldAreaDegree 
						where [AreaID] in (select [AreaID] from PPFOR_TZB_Area_GetChildIdsTable(@AreaID))
			end
	end
PPFOR_GO;

--更新地区排序
if exists(select name from sysobjects where name='PPFOR_TZB_Area_UpdateOrder' and type='P')
	drop procedure [PPFOR_TZB_Area_UpdateOrder]
PPFOR_GO;
create procedure PPFOR_TZB_Area_UpdateOrder(@AreaID int,@AreaOrder int)
as
	begin		
		update [PPFOR_TZB_Area] set [AreaOrder]=@AreaOrder where [AreaID]=@AreaID
	end
PPFOR_GO;

--删除地区
if exists(select name from sysobjects where name='PPFOR_TZB_Area_Delete' and type='P')
	drop procedure [PPFOR_TZB_Area_Delete]
PPFOR_GO;
create procedure PPFOR_TZB_Area_Delete(@AreaID int)
as
	begin		
		delete [PPFOR_TZB_Area] where [AreaID]=@AreaID
	end
PPFOR_GO;

--删除地区
if exists(select name from sysobjects where name='PPFOR_TZB_Area_DeleteAll' and type='P')
	drop procedure [PPFOR_TZB_Area_DeleteAll]
PPFOR_GO;
create procedure PPFOR_TZB_Area_DeleteAll
as
	begin		
		truncate table [PPFOR_TZB_Area]
	end
PPFOR_GO;

--获取地区最大等级
if exists(select name from sysobjects where name='PPFOR_TZB_Area_GetMaxDegree' and type='P')
	drop procedure [PPFOR_TZB_Area_GetMaxDegree]
PPFOR_GO;
create procedure PPFOR_TZB_Area_GetMaxDegree
as
	begin		
		select max([AreaDegree]) from [PPFOR_TZB_Area]
	end
PPFOR_GO;

--获取地区
if exists(select name from sysobjects where name='PPFOR_TZB_Area_GetModel' and type='P')
	drop procedure [PPFOR_TZB_Area_GetModel]
PPFOR_GO;
create procedure PPFOR_TZB_Area_GetModel(@AreaID int)
as
	begin		
		select [AreaID],[AreaName],[BelongAreaID],[AreaOrder],[AreaDegree] 
			from [PPFOR_TZB_Area] where [AreaID]=@AreaID
	end
PPFOR_GO;

--获取所有子级地区
if exists(select name from sysobjects where name='PPFOR_TZB_Area_GetChildList' and type='P')
	drop procedure [PPFOR_TZB_Area_GetChildList]
PPFOR_GO;
create procedure PPFOR_TZB_Area_GetChildList(@BelongAreaID int)
as
	begin		
		select [AreaID],[AreaName],[BelongAreaID],[AreaOrder],[AreaDegree],
			[HasChild]= (select count([AreaID]) from [PPFOR_TZB_Area] a 
				where a.[BelongAreaID]=b.[AreaID])
			from [PPFOR_TZB_Area] b where [BelongAreaID]=@BelongAreaID 
			order by [AreaOrder] asc,[AreaID] asc
	end
PPFOR_GO;

--获取所有子级地区ID
if exists(select name from sysobjects where name='PPFOR_TZB_Area_GetChildIDList' and type='P')
	drop procedure [PPFOR_TZB_Area_GetChildIDList]
PPFOR_GO;
create procedure PPFOR_TZB_Area_GetChildIDList(@BelongAreaID int)
as
	begin		
		select [AreaID] from [PPFOR_TZB_Area] where [BelongAreaID]=@BelongAreaID
	end
PPFOR_GO;