-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-21
-- ------------------------------
-- 招聘类型表
-- ==============================

--创建招聘类型表
if not exists(select name from sysobjects where name='PPFOR_TZB_JobType' and type='U')
CREATE TABLE [PPFOR_TZB_JobType] (
	[ID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[BelongMenu] int NOT NULL,					--所属栏目
	[Name] nvarchar(200) COLLATE Chinese_PRC_CS_AS NOT NULL ,	--招聘类型名称	
	[Content] text NULL ,						--招聘类型内容
	[Image] varchar (255) NULL ,				--图片
	[Order] int NOT NULL DEFAULT (0),			--排序
	[IsShow] bit NOT NULL DEFAULT (1),			--是否显示	
	[UpdateTime] datetime NOT NULL ,			--更新时间
	[KeyWord] nvarchar (255) COLLATE Chinese_PRC_CI_AS NULL ,		--META关键字
	[Description] nvarchar (255) COLLATE Chinese_PRC_CI_AS NULL ,	--META描述
	[ListTemplate] varchar (255) Not NULL ,		--招聘类型模板	
	[PostTemplate] varchar (255) NULL ,			--招聘职位模板	
	[ResumeTemplate] varchar (255) Not NULL , 	--简历默认模板
	[ResumeID] int NOT NULL default(0),			--绑定简历ID
	[ParamIDs] text NULL ,						--扩展参数ID
	[ContentIDs] text NULL,						--扩展内容ID
	[HtmlName] nvarchar (255) not null			--静态页名称
)
PPFOR_GO;


--添加招聘类型
if exists(select name from sysobjects where name='PPFOR_TZB_JobType_Add' and type='P')
	drop procedure PPFOR_TZB_JobType_Add
PPFOR_GO;
CREATE  PROCEDURE PPFOR_TZB_JobType_Add(@ID int output,@BelongMenu int,@Name nvarchar(200),
	@Content text,@Image varchar(255),@Order int,@IsShow bit,@UpdateTime datetime,@KeyWord nvarchar(255),
	@Description nvarchar(255),@ListTemplate varchar(255),@PostTemplate varchar(255),
	@ResumeTemplate varchar(255),@ResumeID int,@ParamIDs text,@ContentIDs text,@HtmlName nvarchar (255))
 AS 
	begin
		if EXISTS (SELECT [ID] FROM [PPFOR_TZB_JobType] WHERE [Name] = @Name and [BelongMenu]=@BelongMenu)
			SET @ID = 0 
		else
		begin
			INSERT INTO [PPFOR_TZB_JobType]([BelongMenu],[Name],[Content],[Image],[Order],[IsShow],
				[UpdateTime],[KeyWord],[Description],[ListTemplate],[PostTemplate],[ResumeTemplate],
				[ResumeID],[ParamIDs],[ContentIDs],[HtmlName]) VALUES (@BelongMenu,@Name,@Content,
				@Image,@Order,@IsShow,@UpdateTime,@KeyWord,@Description,@ListTemplate,@PostTemplate,
				@ResumeTemplate,@ResumeID,@ParamIDs,@ContentIDs,@HtmlName)
			if @@ROWCOUNT>0
				SET @ID = @@IDENTITY
			else
				SET @ID = 0 
		end
	end
PPFOR_GO;

--更新招聘类型
if exists(select name from sysobjects where name='PPFOR_TZB_JobType_Update' and type='P')
	drop procedure PPFOR_TZB_JobType_Update
PPFOR_GO;
CREATE   PROCEDURE PPFOR_TZB_JobType_Update(@ID int,@BelongMenu int,@Name nvarchar(200),
	@Content text,@Image varchar(255),@Order int,@IsShow bit,@UpdateTime datetime,@KeyWord nvarchar(255),
	@Description nvarchar(255),@ListTemplate varchar(255),@PostTemplate varchar(255),
	@ResumeTemplate varchar(255),@ResumeID int,@ParamIDs text,@ContentIDs text,@HtmlName nvarchar (255))
 AS 	
	if NOT EXISTS (SELECT [ID] FROM [PPFOR_TZB_JobType] WHERE [Name] = @Name 
		and [BelongMenu]=@BelongMenu and [ID]<>@ID)			
		UPDATE [PPFOR_TZB_JobType] SET [Name]=@Name,[Content]=@Content,	[Image]=@Image,
		[Order]=@Order,[IsShow]=@IsShow,[UpdateTime]=@UpdateTime,[KeyWord]=@KeyWord,
		[Description]=@Description,[ListTemplate]=@ListTemplate,[PostTemplate]=@PostTemplate,
		[ResumeTemplate]=@ResumeTemplate,[ResumeID]=@ResumeID,[ParamIDs]=@ParamIDs,
		[ContentIDs]=@ContentIDs,[HtmlName]=@HtmlName WHERE [ID]=@ID and [BelongMenu]=@BelongMenu	
PPFOR_GO;

--更新招聘类型列表信息
if exists(select name from sysobjects where name='PPFOR_TZB_JobType_UpdateList' and type='P')
	drop procedure PPFOR_TZB_JobType_UpdateList
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_JobType_UpdateList(@ID int,@Order int,@IsShow bit,@UpdateTime datetime)
 AS 
	UPDATE [PPFOR_TZB_JobType] SET [Order] = @Order,[IsShow] = @IsShow,[UpdateTime] = @UpdateTime
		WHERE [ID]=@ID 
PPFOR_GO;

--删除招聘类型
if exists(select name from sysobjects where name='PPFOR_TZB_JobType_Delete' and type='P')
	drop procedure PPFOR_TZB_JobType_Delete
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_JobType_Delete(@ID int)
 AS 
	DELETE [PPFOR_TZB_JobType] WHERE [ID]=@ID 
PPFOR_GO;

--是否存在招聘类型
if exists(select name from sysobjects where name='PPFOR_TZB_JobType_IsExists' and type='P')
	drop procedure PPFOR_TZB_JobType_IsExists
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_JobType_IsExists(@Name nvarchar (200),@BelongMenu int,@retValue bit out)
AS
	begin
		DECLARE @TempID int
		SELECT @TempID = count(1) FROM [PPFOR_TZB_JobType] WHERE [Name] = @Name 
			and [BelongMenu]=@BelongMenu
		IF @TempID = 0
			set @retValue= 0
		ELSE
			set @retValue= 1
	end
PPFOR_GO;

----查询出所有的招聘类型--------------
if exists (select * from sysobjects where name='PPFOR_TZB_JobType_GetModelList')
drop procedure PPFOR_TZB_JobType_GetModelList
PPFOR_GO;
create procedure PPFOR_TZB_JobType_GetModelList(@BelongMenu int)
as 
     select [ID],[Name],[UpdateTime],[Order],[IsShow],[BelongMenu],[ResumeID],[HtmlName]
     	from [PPFOR_TZB_JobType] where [BelongMenu]=@BelongMenu order by [Order] desc,[ID] asc
PPFOR_GO;

----查询出所有的招聘类型--------------
if exists (select * from sysobjects where name='PPFOR_TZB_JobType_GetAllModelList')
drop procedure PPFOR_TZB_JobType_GetAllModelList
PPFOR_GO;
create procedure PPFOR_TZB_JobType_GetAllModelList(@BelongMenu int)
as 
     select * from [PPFOR_TZB_JobType] where [BelongMenu]=@BelongMenu order by [Order] desc,[ID] asc
PPFOR_GO;

----查询出所有的招聘类型ID和名称----------------
if exists (select * from sysobjects where name='PPFOR_TZB_JobType_GetNameList')
drop procedure PPFOR_TZB_JobType_GetNameList
PPFOR_GO;
create procedure PPFOR_TZB_JobType_GetNameList(@BelongMenu int)
as	
     select [ID],[Name],[IsShow],[HtmlName] from [PPFOR_TZB_JobType] 
		where [BelongMenu]=@BelongMenu order by [Order] desc,[ID] asc
PPFOR_GO;

----获取招聘类型详细-------------
if exists (select * from sysobjects where name='PPFOR_TZB_JobType_GetModel')
drop procedure PPFOR_TZB_JobType_GetModel
PPFOR_GO;
create procedure PPFOR_TZB_JobType_GetModel(@ID int)
as
     select * from [PPFOR_TZB_JobType] where [ID]=@ID
PPFOR_GO;

-------------------------------
-----标签
-------------------------------
----获取招聘类型列表-------------
if exists(select name from sysobjects where name='PPFOR_TZB_JobType_GetShortListForLabel' and type='P')
	drop procedure PPFOR_TZB_JobType_GetShortListForLabel
PPFOR_GO;
create procedure PPFOR_TZB_JobType_GetShortListForLabel(@BelongLanguage int)
as
	begin
	if(@BelongLanguage=0)
		select [ID],[Name],[BelongMenu],[HtmlName] from [PPFOR_TZB_JobType] 
	else if @BelongLanguage>0
		select [ID],[Name],[BelongMenu],[HtmlName] from [PPFOR_TZB_JobType] where [BelongMenu] in 
			(select [ID] from [PPFOR_TZB_Menu] where [belongLanguage]=@BelongLanguage and [belongMenu]=0)
	end
PPFOR_GO;

--获取所有招聘分类列表
if exists(select name from sysobjects where name='PPFOR_TZB_JobType_GetModelListForLabel' and type='P')
	drop procedure PPFOR_TZB_JobType_GetModelListForLabel
PPFOR_GO;
create procedure PPFOR_TZB_JobType_GetModelListForLabel(@BelongMenuId int)
as	
	if(@BelongMenuId<=0)
		select * from [PPFOR_TZB_JobType] where [IsShow]=1 order by [Order] desc,[ID] asc
	else
		select * from [PPFOR_TZB_JobType] where [IsShow]=1 and [BelongMenu]=@BelongMenuId 
			order by [Order] desc,[ID] asc
PPFOR_GO;

--获取路径招聘类型--
if exists(select name from sysobjects where name='PPFOR_TZB_JobType_GetShortInfo' and type='P')
	drop procedure PPFOR_TZB_JobType_GetShortInfo
PPFOR_GO;
create procedure PPFOR_TZB_JobType_GetShortInfo(@ID int)
as
	select [ID],[Name],[BelongMenu],[HtmlName] from [PPFOR_TZB_JobType] 
		where [IsShow]=1 and [ID]=@ID
PPFOR_GO;

--更新招聘类型静态页文件名
if exists(select name from sysobjects where name='PPFOR_TZB_JobType_UpdateHtmlName' and type='P')
	drop procedure PPFOR_TZB_JobType_UpdateHtmlName
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_JobType_UpdateHtmlName(@ID int,@HtmlName nvarchar(255))
 AS 
	UPDATE [PPFOR_TZB_JobType] SET [HtmlName] = @HtmlName WHERE [ID]=@ID 
PPFOR_GO;

--获取招聘类型静态文件名
if exists(select name from sysobjects where name='PPFOR_TZB_JobType_GetHtmlName' and type='P')
	drop procedure PPFOR_TZB_JobType_GetHtmlName
PPFOR_GO;
create procedure PPFOR_TZB_JobType_GetHtmlName(@ID int)
as
	select [HtmlName] from [PPFOR_TZB_JobType] where [ID]=@ID 
PPFOR_GO;