-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-20
-- ------------------------------
-- 自定义信息模块表
-- ==============================

--创建自定义信息表
if not exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_CustomModel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [PPFOR_TZB_CustomModel] (
	[modelID] int identity(1,1) not null primary key,
	[belongLanguage] int NOT NULL REFERENCES [PPFOR_TZB_Language]([ID]) ON DELETE CASCADE,	--所属语言
	[modelName] nvarchar (30) COLLATE Chinese_PRC_CS_AS not null,	--信息模块名称
	[orderID] int NOT NULL default(0),								--排序
	[modelContent] ntext NULL,										--内容
	[show] bit not null default(0)									--是否显示
)
PPFOR_GO;

--添加信息模块
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_CustomModel_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_CustomModel_Add]
PPFOR_GO;
create procedure PPFOR_TZB_CustomModel_Add(@belongLanguage int,@modelName nvarchar(30),@orderID int,
	@modelContent ntext,@show bit, @modelID int out)
as
	begin
		declare @tempID int
		select @tempID=count(1) from [PPFOR_TZB_CustomModel] where [belongLanguage]=@belongLanguage 
			and [modelName]=@modelName
		if @tempID=0
			begin
				insert into [PPFOR_TZB_CustomModel] ([belongLanguage],[modelName],[orderID],
					[modelContent],[show]) values (@belongLanguage,@modelName,@orderID,
					@modelContent,@show)
				set @modelID=@@identity
			end
		else
			set @modelID=0
	end
PPFOR_GO;

--更新信息模块
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_CustomModel_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_CustomModel_Update]
PPFOR_GO;
create procedure PPFOR_TZB_CustomModel_Update(@modelID int,@belongLanguage int,
	@modelName nvarchar(30), @orderID int,@modelContent ntext,@show bit)
as
	begin
		declare @tempID int
		select @tempID=count(1) from [PPFOR_TZB_CustomModel] where [belongLanguage]=@belongLanguage
			and [modelName]=@modelName and [modelID]<>@modelID
		if @tempID=0
			update [PPFOR_TZB_CustomModel] set [modelName]=@modelName,[orderID]=@orderID,
			[modelContent]=@modelContent,[show]=@show where modelID=@modelID
	end
PPFOR_GO;

--删除信息模块
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_CustomModel_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_CustomModel_Delete]
PPFOR_GO;
create procedure PPFOR_TZB_CustomModel_Delete(@modelID int,@belongLanguage int)
as
	begin
		delete from [PPFOR_TZB_CustomModel] where [modelID]=@modelID 
			and [belongLanguage]=@belongLanguage
	end
PPFOR_GO;

--获取信息模块
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_CustomModel_GetModel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_CustomModel_GetModel]
PPFOR_GO;
create procedure PPFOR_TZB_CustomModel_GetModel(@modelID int,@belongLanguage int)
as
	begin
		select * from [PPFOR_TZB_CustomModel] where [modelID]=@modelID 
			and [belongLanguage]=@belongLanguage
	end
PPFOR_GO;

if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_CustomModel_GetModelList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_CustomModel_GetModelList]
PPFOR_GO;
create procedure PPFOR_TZB_CustomModel_GetModelList(@belongLanguage int)
as
	begin
		select [modelID],[belongLanguage],[modelName],[orderID],[show] from [PPFOR_TZB_CustomModel]
		 	where [belongLanguage]=@belongLanguage order by [orderID] desc,[modelID] asc
	end
PPFOR_GO;

if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_CustomModel_GetShortModelList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_CustomModel_GetShortModelList]
PPFOR_GO;
create procedure PPFOR_TZB_CustomModel_GetShortModelList(@belongLanguage int)
as
	begin
		if @belongLanguage>0
			select [modelID],[modelName],[belongLanguage] from [PPFOR_TZB_CustomModel] where
				[belongLanguage]=@belongLanguage and [show]=1 order by [orderID] asc,[modelID] asc
		else 
			select [modelID],[modelName],[belongLanguage] from [PPFOR_TZB_CustomModel] where 
				[show]=1 order by [belongLanguage] asc, [orderID] desc,[modelID] asc
	end
PPFOR_GO;

if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_CustomModel_IsExists]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_CustomModel_IsExists]
PPFOR_GO;
create procedure PPFOR_TZB_CustomModel_IsExists(@belongLanguage int,@modelName nvarchar(30),@retValue bit out)
as
	begin
		declare @tempID int
		select @tempID=count(1) from [PPFOR_TZB_CustomModel] 
			where [belongLanguage]=@belongLanguage and [modelName]=@modelName
		if @tempID=0
			set @retValue=0
		else
			set @retValue=1
	end
PPFOR_GO;

--
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_CustomModel_IsEnableUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_CustomModel_IsEnableUpdate]
PPFOR_GO;
create procedure PPFOR_TZB_CustomModel_IsEnableUpdate(@modelID int,@belongLanguage int,
	@modelName nvarchar(30),@retValue bit out)
as
	begin
		declare @tempID int
		select @tempID=count(1) from [PPFOR_TZB_CustomModel] where [belongLanguage]=@belongLanguage
			and [modelName]=@modelName and [modelID]<>@modelID
		if @tempID=0
			set @retValue=1
		else
			set @retValue=0
	end
PPFOR_GO;

