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

--创建标签分类表
if not exists(select name from sysobjects where name='PPFOR_TZB_LabelType' and type='U')
	create table PPFOR_TZB_LabelType
	(
		typeId int identity(1,1) not null primary key,	--自动编号，从1开始递增
		typeName nvarchar(30) COLLATE Chinese_PRC_CS_AS not null unique,			--标签分类名称
		typeDescription nvarchar(255) null,				--标签分类描述
		typeLanguageId int default(0) not null,			--标签分类所属语言
		typeIsSystem bit default(0) not null,			--是否是系统内置标签类
		typeAddTime datetime not null					--标签分类创建时间
	)
PPFOR_GO;  

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

--添加标签分类，返回添加的标签分类id
create procedure PPFOR_TZB_LabelType_Add (@typeName nvarchar(30),@typeDescription nvarchar(255),
	@typeLanguageId int,@typeAddTime datetime,@typeId int output)
as
	begin
		if not exists(select typeId from PPFOR_TZB_LabelType where typeName=@typeName)
			begin
				insert into PPFOR_TZB_LabelType values(@typeName,@typeDescription,@typeLanguageId,
					0,@typeAddTime)
				set @typeId=@@identity
			end
		else
			set @typeId=0
	end
PPFOR_GO;

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

--更新标签分类名称和描述
create procedure PPFOR_TZB_LabelType_Update (@typeId int,@typeName nvarchar(30),@typeDescription nvarchar(255))
as
	update PPFOR_TZB_LabelType set typeName=@typeName,typeDescription=@typeDescription
		where typeId=@typeId
PPFOR_GO;

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

--根据id删除标签分类
create procedure PPFOR_TZB_LabelType_Delete(@typeId int)
as
	delete from PPFOR_TZB_LabelType where typeId=@typeId and typeIsSystem=0
PPFOR_GO;

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

--根据标签分类id返回标签分类名称
create procedure PPFOR_TZB_LabelType_GetName (@typeId int)
as
	select typeName from PPFOR_TZB_LabelType where typeId=@typeId
PPFOR_GO;

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

--返回所有的标签分类ID,名称，语言种类ID
create procedure PPFOR_TZB_LabelType_GetShortList
as
	select typeId,typeName,typeLanguageId,typeIsSystem from PPFOR_TZB_LabelType 
		order by typeId desc
PPFOR_GO;

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

--返回所有的标签分类ID,名称，语言种类ID
create procedure PPFOR_TZB_LabelType_GetLanguageId(@typeId int)
as
	select typeLanguageId from PPFOR_TZB_LabelType where typeIsSystem=0 and typeId=@typeId 
PPFOR_GO;

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

--根据标签分类id返回标签分类Id,名称，描述，语言种类ID, 添加时间
create procedure PPFOR_TZB_LabelType_GetModel (@typeId int)
as
	select typeId,typeName,typeDescription,typeLanguageId,typeIsSystem,typeAddTime 
		from PPFOR_TZB_LabelType where typeId=@typeId
PPFOR_GO;

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

--返回所有标签分类资料
create procedure PPFOR_TZB_LabelType_GetModelListByPage(@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(typeId) from PPFOR_TZB_LabelType
		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=' typeId,typeName,typeDescription,typeLanguageId,typeIsSystem,typeAddTime
			 from PPFOR_TZB_LabelType '
	
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by typeId desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by typeId asc) Temptable order by typeId desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by 
				typeId asc) Temptable order by typeId desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

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

--返回是否存在标签分类，是为1，否为0
create procedure PPFOR_TZB_LabelType_IsExists(@typeName nvarchar(30),@retValue bit output)
as
	begin
		if exists(select typeId from PPFOR_TZB_LabelType where typeName=@typeName)
			set @retValue=1
		else
			set @retValue=0
	end
PPFOR_GO;


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