-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-05-08
-- ------------------------------
-- 品牌表
-- ==============================

--创建品牌表
if not exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_Brand]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [PPFOR_TZB_Brand] (
	[ID] int identity(1,1) not null primary key,						--ID
	[Name] nvarchar (30) COLLATE Chinese_PRC_CS_AS not null unique,		--名称
	[Order]	int not null default(0),									--排序
	[BelongLanguage] int not null REFERENCES [PPFOR_TZB_Language]([ID]) ON DELETE CASCADE, --所属语言
	[IsShow] bit not null,												--是否显示
	[IsRec] bit not null,												--是否推荐
	[Url] nvarchar(255) null,											--网址
	[Logo] nvarchar(255) not null,										--LOGO
	[Description] ntext null,											--描述
	[Template] nvarchar(255) not null,									--模板
	[KeyWord] nvarchar(255) null,										--META关键字
	[KeyDescription] nvarchar(255) null,	 							--META描述
	[HtmlName] nvarchar(255) not null									--品牌静态页文件名
)
PPFOR_GO;

--是否存在品牌名
if exists(select name from sysobjects where name='PPFOR_TZB_Brand_IsExists' and type='P')
	drop procedure [PPFOR_TZB_Brand_IsExists]
PPFOR_GO;
create procedure PPFOR_TZB_Brand_IsExists(@Name nvarchar (30),@BelongLanguage int,
	 @retValue bit out)
as
	begin			
		if exists ( select [ID] from [PPFOR_TZB_Brand] where [Name]=@Name 
			and [BelongLanguage]=@BelongLanguage) 
			set @retValue=1
		else
			set @retValue=0
	end
PPFOR_GO;

--添加品牌
if exists(select name from sysobjects where name='PPFOR_TZB_Brand_Add' and type='P')
	drop procedure [PPFOR_TZB_Brand_Add]
PPFOR_GO;
create procedure PPFOR_TZB_Brand_Add(@ID int out, @Name nvarchar (30),@Order int,
	@BelongLanguage int,@IsShow bit,@IsRec bit,@Url nvarchar (255),@Logo nvarchar(255),
	@Description ntext,@Template nvarchar(255),@KeyWord nvarchar(255), 
	@KeyDescription nvarchar(255),@HtmlName nvarchar(255))
as
	begin				
		if not exists ( select [ID] from [PPFOR_TZB_Brand] where [Name]=@Name 
			and [BelongLanguage]=@BelongLanguage)
			begin
				insert into [PPFOR_TZB_Brand] ([Name],[Order],[BelongLanguage],[IsShow],[IsRec],[Url],
					[Logo],[Description],[Template],[KeyWord],[KeyDescription],[HtmlName]) values (@Name,
					@Order,@BelongLanguage,@IsShow,@IsRec,@Url,@Logo,@Description,@Template,@KeyWord,
					@KeyDescription,@HtmlName)
				SET @ID = @@IDENTITY
			end
		else
			SET @ID = 0
	end
PPFOR_GO;

--更新品牌
if exists(select name from sysobjects where name='PPFOR_TZB_Brand_Update' and type='P')
	drop procedure [PPFOR_TZB_Brand_Update]
PPFOR_GO;
create procedure PPFOR_TZB_Brand_Update(@ID int out, @Name nvarchar (30),@Order int,
	@BelongLanguage int,@IsShow bit,@IsRec bit,@Url nvarchar (255),@Logo nvarchar(255),
	@Description ntext,@Template nvarchar(255),@KeyWord nvarchar(255), 
	@KeyDescription nvarchar(255),@HtmlName nvarchar(255))
as
	begin		
		if not exists ( select [ID] from [PPFOR_TZB_Brand] where [ID]<>@ID and [Name]=@Name 
			and [BelongLanguage]=@BelongLanguage) 
				update [PPFOR_TZB_Brand] set [Name]=@Name,[Order]=@Order,[IsShow]=@IsShow,[IsRec]=@IsRec,
					[Url]=@Url,[Logo]=@Logo,[Description]=@Description,[Template]=@Template, 
					[KeyWord]=@KeyWord,[KeyDescription]=@KeyDescription,[HtmlName]=@HtmlName
					where [ID]=@ID and [BelongLanguage]=@BelongLanguage
	end
PPFOR_GO;

--更新品牌排序
if exists(select name from sysobjects where name='PPFOR_TZB_Brand_UpdateList' and type='P')
	drop procedure [PPFOR_TZB_Brand_UpdateList]
PPFOR_GO;
create procedure PPFOR_TZB_Brand_UpdateList(@ID int,@IsShow bit,@IsRec bit,@Order int)
as
	begin		
		update [PPFOR_TZB_Brand] set [IsShow]=@IsShow,[IsRec]=@IsRec, [Order]=@Order where [ID]=@ID
	end
PPFOR_GO;

--删除品牌
if exists(select name from sysobjects where name='PPFOR_TZB_Brand_Delete' and type='P')
	drop procedure [PPFOR_TZB_Brand_Delete]
PPFOR_GO;
create procedure PPFOR_TZB_Brand_Delete(@ID int)
as
	begin		
		delete [PPFOR_TZB_Brand] where [ID]=@ID
	end
PPFOR_GO;

--获取品牌
if exists(select name from sysobjects where name='PPFOR_TZB_Brand_GetModel' and type='P')
	drop procedure [PPFOR_TZB_Brand_GetModel]
PPFOR_GO;
create procedure PPFOR_TZB_Brand_GetModel(@ID int)
as
	begin		
		select [ID],[Name],[Order],[BelongLanguage],[IsShow],[IsRec],[Url],[Logo],[Description],
			[Template],[KeyWord],[KeyDescription],[HtmlName] from [PPFOR_TZB_Brand] where [ID]=@ID
	end
PPFOR_GO;

--获取所有品牌
if exists(select name from sysobjects where name='PPFOR_TZB_Brand_GetModelList' and type='P')
	drop procedure [PPFOR_TZB_Brand_GetModelList]
PPFOR_GO;
create procedure PPFOR_TZB_Brand_GetModelList(@BelongLanguage int)
as
	begin		
		select [ID],[Name],[Order],[BelongLanguage],[IsShow],[IsRec],[Url],[Logo],[Description],
			[Template],[KeyWord],[KeyDescription],[HtmlName] from [PPFOR_TZB_Brand] 
			where [BelongLanguage]=@BelongLanguage order by [Order] asc,[ID] asc
	end
PPFOR_GO;

--获取所有品牌
if exists(select name from sysobjects where name='PPFOR_TZB_Brand_GetShortList' and type='P')
	drop procedure [PPFOR_TZB_Brand_GetShortList]
PPFOR_GO;
create procedure PPFOR_TZB_Brand_GetShortList(@BelongLanguage int)
as
	begin		
		select [ID],[Name],[Order],[BelongLanguage],[IsShow],[IsRec],[Url],[HtmlName]
			from [PPFOR_TZB_Brand] where [BelongLanguage]=@BelongLanguage
			order by [Order] asc,[ID] asc
	end
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Brand_GetModelListByPage' and type='P')
	drop procedure PPFOR_TZB_Brand_GetModelListByPage
PPFOR_GO;
create procedure PPFOR_TZB_Brand_GetModelListByPage(@belongLanguage int,@pageSize int,@pageIndex int) 
as
	begin
		declare @rc int
		declare @pc int
		declare @sqlStr nvarchar(2000)
		declare @sqlStr2 nvarchar(1000)

		if @pageSize<1
			set @pageSize=20		

		set @rc=(select count([ID]) from [PPFOR_TZB_Brand] where [IsShow]=1 and [BelongLanguage]=@belongLanguage)
		
		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=' [ID],[Name],[Order],[Url],[Logo],[HtmlName] from [PPFOR_TZB_Brand] where [IsShow]=1 and [BelongLanguage]=' + str(@belongLanguage) + ' '

		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [Order] asc, [ID] asc'
		else if @pageIndex = @pc
			set @sqlStr = 'select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [Order] desc,[ID] desc) Temptable order by [Order] asc, [ID] asc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [Order] desc,
				[ID] desc) Temptable order  by [Order] asc,[ID] asc'
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Brand_GetModelListForLabel' and type='P')
	drop procedure PPFOR_TZB_Brand_GetModelListForLabel
PPFOR_GO;
create procedure PPFOR_TZB_Brand_GetModelListForLabel(@belongLanguage int,@recommended bit,@number int)
as
	begin
		declare @sql varchar(1000)
		begin
			set @sql='select top ' + str(@number) + ' [ID],[Name],[Order],[Url],[Logo],[HtmlName] from [PPFOR_TZB_Brand] where [IsShow]=1 '
			
			if @recommended=1
				set	@sql= @sql + ' and [IsRec]=1'

			if @belongLanguage>0
				set	@sql= @sql + ' and [BelongLanguage]=' + str(@belongLanguage)
						
			set @sql=@sql + ' order by [Order] asc,[ID] asc'
			exec (@sql)			
		end
	end
PPFOR_GO;
--更新品牌静态页文件名
if exists(select name from sysobjects where name='PPFOR_TZB_Brand_UpdateHtmlName' and type='P')
	drop procedure PPFOR_TZB_Brand_UpdateHtmlName
PPFOR_GO;

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

--获取品牌静态文件名
if exists(select name from sysobjects where name='PPFOR_TZB_Brand_GetHtmlName' and type='P')
	drop procedure PPFOR_TZB_Brand_GetHtmlName
PPFOR_GO;
create procedure PPFOR_TZB_Brand_GetHtmlName(@ID int)
as
	select [HtmlName] from [PPFOR_TZB_Brand] where [ID]=@ID 
PPFOR_GO;
