-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-20
-- ------------------------------
-- 标签表
-- ==============================

--创建标签表
if not exists(select name from sysobjects where name='PPFOR_TZB_Label' and type='U')
	create table PPFOR_TZB_Label
	(
		labelId int identity(1,1) not null primary key,		--自动编号，从1开始递增
		labelName nvarchar(32) COLLATE Chinese_PRC_CS_AS not null unique,				--标签名称
		labelTypeId int not null default(0) REFERENCES [PPFOR_TZB_LabelType]([typeId]) ON DELETE CASCADE,				--标签分类ID
		labelIsSystem bit not null default(0),				--是否系统内置标签
		labelContent ntext null,							--标签内容
		labelDescription nvarchar(255) null,				--标签描述
		labelLanguageId int default(0) not null,			--标签所属语言
		labelAddTime datetime not null						--标签创建时间
	)
PPFOR_GO;  

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

--添加标签，返回添加的标签id
create procedure PPFOR_TZB_Label_Add (@labelName nvarchar(32),@labelTypeId int,@labelContent ntext,
	@labelDescription nvarchar(255),@labelLanguageId int,@labelAddTime datetime,@labelId int output)
as
	begin
		if not exists(select labelId from PPFOR_TZB_Label where labelName=@labelName)
			begin
				insert into PPFOR_TZB_Label values(@labelName,@labelTypeId,0,@labelContent,
					@labelDescription,@labelLanguageId,@labelAddTime)
				set @labelId=@@identity
			end
		else
			set @labelId=0
	end
PPFOR_GO;

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

--更新标签名称和描述
create procedure PPFOR_TZB_Label_Update (@labelId int,@labelName nvarchar(32),@labelTypeId int,
	@labelContent ntext,@labelDescription nvarchar(255),@labelLanguageId int)
as
	update PPFOR_TZB_Label set labelName=@labelName,labelTypeId=@labelTypeId,labelContent=@labelContent,
		labelDescription=@labelDescription,labelLanguageId=@labelLanguageId	where labelId=@labelId
PPFOR_GO;

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

--根据id删除标签
create procedure PPFOR_TZB_Label_Delete(@labelId int)
as
	delete from PPFOR_TZB_Label where labelId=@labelId and labelIsSystem=0
PPFOR_GO;

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

--根据标签id返回标签名称
create procedure PPFOR_TZB_Label_GetName (@labelId int)
as
	select labelName from PPFOR_TZB_Label where labelId=@labelId
PPFOR_GO;

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

--返回所有的标签ID,名称，分类ID，语言种类ID
create procedure PPFOR_TZB_Label_GetShortList(@labelTypeId int)
as
	select labelId,labelName,labelLanguageId from PPFOR_TZB_Label where 
		labelTypeId=@labelTypeId order by labelId desc
PPFOR_GO;

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

--返回所有的标签ID,名称
create procedure PPFOR_TZB_Label_GetShortListByLanguage(@labelLanguageId int)
as
	if @labelLanguageId=0
		select labelId,labelName from PPFOR_TZB_Label order by labelTypeId desc
	else
		select labelId,labelName from PPFOR_TZB_Label where labelLanguageId=@labelLanguageId 
			or labelLanguageId=0 order by labelTypeId desc
PPFOR_GO;

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

--根据标签id返回标签信息
create procedure PPFOR_TZB_Label_GetModel (@labelId int)
as
	select labelId,labelName,labelTypeId,labelIsSystem,labelContent,labelDescription,
		labelLanguageId,labelAddTime from PPFOR_TZB_Label where labelId=@labelId
PPFOR_GO;

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

--根据标签名返回标签信息
create procedure PPFOR_TZB_Label_GetModelByName (@labelName nvarchar(32))
as
	select labelId,labelName,labelTypeId,labelIsSystem,labelContent,labelDescription,
		labelLanguageId,labelAddTime from PPFOR_TZB_Label where labelName=@labelName
PPFOR_GO;

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

--返回所有标签资料，不包括权限
create procedure PPFOR_TZB_Label_GetModelListByPage(@labelTypeId 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=20

		select @rc=count(labelId) from PPFOR_TZB_Label where labelTypeId=@labelTypeId
		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=' labelId,labelName,labelTypeId,labelIsSystem,labelContent,labelDescription,
			labelLanguageId,labelAddTime from PPFOR_TZB_Label where labelTypeId=' + str(@labelTypeId) + ' '
	
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by labelId desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by labelId asc) Temptable order by labelId desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by 
				labelId asc) Temptable order by labelId desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

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

--返回是否存在标签，是为1，否为0
create procedure PPFOR_TZB_Label_IsExists(@labelName nvarchar(32),@retValue bit output)
as
	begin
		if exists(select labelId from PPFOR_TZB_Label where labelName=@labelName)
			set @retValue=1
		else
			set @retValue=0
	end
PPFOR_GO;


if exists(select name from sysobjects where name='PPFOR_TZB_Label_IsEnableUpdate' and type='P')
	drop procedure PPFOR_TZB_Label_IsEnableUpdate
PPFOR_GO;
--返回是否可以更新，是为1，否为0
create procedure PPFOR_TZB_Label_IsEnableUpdate(@labelId int,@labelName nvarchar(30),@retValue bit output)
as
	begin
		if @labelId<=0
			set @retValue=0
		else if exists(select labelId from PPFOR_TZB_Label where labelName=@labelName and labelId<>@labelId)
			set @retValue=0
		else
			set @retValue=1
	end
PPFOR_GO;