-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-19
-- ------------------------------
-- 下载文件类型表
-- ==============================

if not exists (select * from sysobjects where name='PPFOR_TZB_DownloadType')
create table PPFOR_TZB_DownloadType
(
 [ID] int identity(1,1) not null primary key,
 [FileParentID] int not null,				--父类ID
 [TypeName] nvarchar(200) COLLATE Chinese_PRC_CS_AS not null,			--类型名
 [UploadTemplate]  nvarchar(255) null,		--文件下载详细页模板
 [ListTemplate] nvarchar(255) not null,		--文件类型模板
 [UpdateDate] datetime not null,			--最后修改时间
 [Sort] int not null default(0),			--排序
 [IsShow] bit not null,						--是否显示
 [PageKeyWord] nvarchar(255) COLLATE Chinese_PRC_CI_AS null, 			--Meta关键字
 [PageDescription] nvarchar(255) COLLATE Chinese_PRC_CI_AS null, 		--Meta描述
 [BelongMenu] int not null,  				--属于栏目
 [TypeDescription] text null,				--类型描述
 [SmallImage] nvarchar(255) null,			--小图
 [HtmlName]	nvarchar(255) not null			--静态页文件名
)
PPFOR_GO;

--获取所有子类型ID，并以临时表格存储
if exists (select name from sysobjects where name='PPFOR_TZB_DownloadType_GetChildIdsTable')
drop function PPFOR_TZB_DownloadType_GetChildIdsTable
PPFOR_GO;
create function PPFOR_TZB_DownloadType_GetChildIdsTable(@belongType int)   
	returns  @ChildIdsTable  table([ID] int,[IsShow] bit)   
as   
  	begin 
		insert into @ChildIdsTable select [ID],[IsShow] from [PPFOR_TZB_DownloadType] where [ID]=@belongType   
  		insert into @ChildIdsTable select [ID],[IsShow] from [PPFOR_TZB_DownloadType] where [FileParentID]=@belongType   
		while @@rowcount>0   
		  	insert into @ChildIdsTable select a.[ID],a.[IsShow] from [PPFOR_TZB_DownloadType] a,@ChildIdsTable b
			   where a.[FileParentID]=b.[ID] and a.[ID] not in(select [ID] from @ChildIdsTable)   
		return
	end   
PPFOR_GO; 

----检查同级中是否已存在该类型名----
IF EXISTS (SELECT name FROM  sysobjects WHERE  name = 'PPFOR_TZB_DownloadType_IsExists')
DROP PROCEDURE PPFOR_TZB_DownloadType_IsExists
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_DownloadType_IsExists(@typeName nvarchar(200),@fileParentID int,@belongMenuID int,@retValue bit out)
AS
	set @retValue=0
	SELECT @retValue = count(1) FROM [PPFOR_TZB_DownloadType] WHERE [TypeName] =@typeName 
		and [FileParentID]=@fileParentID and [BelongMenu]=@belongMenuID	
PPFOR_GO;

--获取顶级父类型ID
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadType_GetRootID' and type='P')
	drop procedure PPFOR_TZB_DownloadType_GetRootID
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_DownloadType_GetRootID(@ID int out,@belongMenuID int)
AS
	begin
		DECLARE @TempID int		
		set @TempID=@ID
		while @TempID>0
		begin
			SELECT @TempID = [FileParentID] FROM PPFOR_TZB_DownloadType where [ID]=@ID and [BelongMenu]=@belongMenuID			
			if @ID<>@TempID and @TempID>0
				set @ID=@TempID
			else
				set @TempID=0
		end
	end
PPFOR_GO;

----添加下载类型------------------------
IF EXISTS (SELECT name FROM  sysobjects WHERE  name = 'PPFOR_TZB_DownloadType_Add')
DROP PROCEDURE PPFOR_TZB_DownloadType_Add
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_DownloadType_Add(@ID int out,@FileParentID int,@TypeName nvarchar(200),
@UploadTemplate nvarchar(255),@ListTemplate nvarchar(255),@UpdateDate datetime,@Sort int,@IsShow bit,
@PageKeyWord nvarchar(255),@PageDescription nvarchar(255),@BelongMenu int,@TypeDescription text,
@SmallImage nvarchar(255),@HtmlName nvarchar(255))
AS
	begin  	     
		insert into PPFOR_TZB_DownloadType values (@FileParentID,@TypeName,@UploadTemplate,
			@ListTemplate,@UpdateDate,@Sort,@IsShow,@PageKeyWord,@PageDescription,
			@BelongMenu,@TypeDescription,@SmallImage,@HtmlName)
		set @ID=@@identity
	end
PPFOR_GO;

----修改下载类型-----------
if exists (select * from sysobjects where name='PPFOR_TZB_DownloadType_Update')
drop procedure PPFOR_TZB_DownloadType_Update
PPFOR_GO;
create procedure PPFOR_TZB_DownloadType_Update(@ID int,@FileParentID int,@TypeName nvarchar(200),@UploadTemplate nvarchar(255),
@ListTemplate nvarchar(255),@UpdateDate datetime,@Sort int,@IsShow bit,@PageKeyWord nvarchar(255),
@PageDescription nvarchar(255),@BelongMenu int,@TypeDescription text,@SmallImage nvarchar(255),@HtmlName nvarchar(255))
as 
    update PPFOR_TZB_DownloadType set 
	    FileParentID=@FileParentID,TypeName=@TypeName,UploadTemplate=@UploadTemplate,
	    ListTemplate=@ListTemplate,UpdateDate=@UpdateDate,Sort=@Sort,IsShow=@IsShow,
	    PageKeyWord=@PageKeyWord,PageDescription=@PageDescription,BelongMenu=@BelongMenu,
	    TypeDescription=@TypeDescription,SmallImage=@SmallImage,HtmlName=@HtmlName where [ID]=@ID
PPFOR_GO;

----批量修改下载类型----------
if exists (select * from sysobjects where name='PPFOR_TZB_DownloadType_UpdateList')
drop procedure PPFOR_TZB_DownloadType_UpdateList
PPFOR_GO;
create procedure PPFOR_TZB_DownloadType_UpdateList(@ID int,@UpdateDate datetime,@Sort int,@IsShow bit)
as 
    update PPFOR_TZB_DownloadType set UpdateDate=@UpdateDate,Sort=@Sort,IsShow=@IsShow where [ID]=@ID
PPFOR_GO;

----删除下载类型----------
if exists (select * from sysobjects where name='PPFOR_TZB_DownloadType_Delete')
drop procedure PPFOR_TZB_DownloadType_Delete
PPFOR_GO;
create procedure PPFOR_TZB_DownloadType_Delete(@ID int)
as
     delete PPFOR_TZB_DownloadType where [ID]=@ID
PPFOR_GO;

----查询出所有的下载类型--------------
if exists (select * from sysobjects where name='PPFOR_TZB_DownloadType_GetModelList')
drop procedure PPFOR_TZB_DownloadType_GetModelList
PPFOR_GO;
create procedure PPFOR_TZB_DownloadType_GetModelList(@fileParentID int,@belongMenu int)
as 
     select [ID],FileParentID,TypeName,UpdateDate,Sort,IsShow,BelongMenu,HtmlName 
     	from PPFOR_TZB_DownloadType where FileParentID=@fileParentID and BelongMenu=@belongMenu
		 order by Sort desc,[ID] asc
PPFOR_GO;

----查询出所有的下载类型ID和名称----------------
if exists (select * from sysobjects where name='PPFOR_TZB_DownloadType_GetNameList')
drop procedure PPFOR_TZB_DownloadType_GetNameList
PPFOR_GO;
create procedure PPFOR_TZB_DownloadType_GetNameList(@FileParentID int,@BelongMenu int)
as	
     select [ID],[TypeName],[IsShow],[HtmlName] from [PPFOR_TZB_DownloadType] 
		where [FileParentID]=@FileParentID and [BelongMenu]=@BelongMenu order by Sort desc,[ID] asc
PPFOR_GO;

----查询出栏目下所有的下载类型ID和名称----------------
if exists (select * from sysobjects where name='PPFOR_TZB_DownloadType_GetNameListByMenu')
drop procedure PPFOR_TZB_DownloadType_GetNameListByMenu
PPFOR_GO;
create procedure PPFOR_TZB_DownloadType_GetNameListByMenu(@BelongMenu int)
as	
     select [ID],[FileParentID],[TypeName],[HtmlName] from [PPFOR_TZB_DownloadType] where [BelongMenu]=@BelongMenu
PPFOR_GO;

----获取下载类型详细-------------
if exists (select * from sysobjects where name='PPFOR_TZB_DownloadType_GetModel')
drop procedure PPFOR_TZB_DownloadType_GetModel
PPFOR_GO;
create procedure PPFOR_TZB_DownloadType_GetModel(@ID int)
as
     select * from PPFOR_TZB_DownloadType where [ID]=@ID
PPFOR_GO;


-------------------------------
-----标签
-------------------------------

------获取所有下载分类列表-------
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadType_GetModelListForLabel' and type='P')
	drop procedure PPFOR_TZB_DownloadType_GetModelListForLabel
PPFOR_GO;
create procedure PPFOR_TZB_DownloadType_GetModelListForLabel(@belongMenuId int,@belongType int)
as
	if(@belongMenuId<=0)
		select * from PPFOR_TZB_DownloadType where IsShow=1 and FileParentID=@belongType order by [Sort] desc,[ID] asc
	else
		select * from PPFOR_TZB_DownloadType where IsShow=1 and FileParentID=@belongType and BelongMenu=@belongMenuId order by [Sort] desc,[ID] asc
PPFOR_GO;

----获取下载类型列表-------------
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadType_GetShortListForLabel' and type='P')
	drop procedure PPFOR_TZB_DownloadType_GetShortListForLabel
PPFOR_GO;
create procedure PPFOR_TZB_DownloadType_GetShortListForLabel(@belongLanguage int,@belongType int)
as
	begin
	if(@belongLanguage=0)
		select [ID],[TypeName],[BelongMenu],[HtmlName] from PPFOR_TZB_DownloadType where FileParentID=@belongType
	else if @belongLanguage>0
		select [ID],[TypeName],[BelongMenu],[HtmlName] from 
		 PPFOR_TZB_DownloadType where FileParentID=@belongType and 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_DownloadType_GetShortInfo' and type='P')
	drop procedure PPFOR_TZB_DownloadType_GetShortInfo
PPFOR_GO;
create procedure PPFOR_TZB_DownloadType_GetShortInfo(@TypeID int)
as
	select [ID],[FileParentID],[TypeName],[BelongMenu],[HtmlName] from [PPFOR_TZB_DownloadType] where [IsShow]=1 and [ID]=@TypeID
PPFOR_GO;

--返回下载类型动态信息--
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadType_GetVisitInfo' and type='P')
	drop procedure PPFOR_TZB_DownloadType_GetVisitInfo
PPFOR_GO;
create procedure PPFOR_TZB_DownloadType_GetVisitInfo(@ID int)
as
	select [ID],[FileParentID] from [PPFOR_TZB_DownloadType] where [IsShow]=1 and [ID]=@ID
PPFOR_GO;

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

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

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