-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-21
-- ------------------------------
-- 产品表
-- ==============================

--创建产品表
if not exists(select name from sysobjects where name='PPFOR_TZB_Product' and type='U')
CREATE TABLE [PPFOR_TZB_Product] (
	[ID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[belongType] int NOT NULL ,					--所属类型
	[productName] nvarchar (200) COLLATE Chinese_PRC_CI_AS NOT NULL ,	--产品名称	
	[proDescription] text NULL ,				--产品内容
	[bigImage] nvarchar(255) NULL ,				--大图
	[smallImage] nvarchar(255) NULL ,			--缩略图
	[ifThumbnail] bit NOT NULL ,				--是否自动生成缩略图		
	[recommended] bit NOT NULL ,				--是否推荐
	[new] bit NOT NULL ,						--是否最新
	[hot] bit NOT NULL ,						--是否热点
	[slide] bit NOT NULL ,						--是否幻灯片	
	[updateDate] datetime NOT NULL ,			--更新时间
	[sort] int NOT NULL ,						--排序
	[show] bit NOT NULL ,						--显示
	[moreSmallImage] text NULL ,				--更多图片
	[productTemplate] nvarchar(255) NOT NULL ,	--详细页模板
	[pageKeyWord] nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL ,  		--META关键字
	[pageDescription] nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL ,	--META描述
	[click] int not NULL default(0),			--点击数	
	[slideImage] nvarchar(255) NULL,			--幻灯图片	
	[isPublic] bit not null,					--是否允许所有人浏览
	[allowDegreeIds] text null,					--限制允许浏览的会员等级
	[belongBrand] int not null default(0),		--所属品牌
	[no] nvarchar(18) COLLATE Chinese_PRC_CS_AS null,	--商品编号
	[score] int not null default(0),			--积分	
	[mktPrice] money not null default(0),		--市场价
	[spec] text null,							--规格	
	[code] nvarchar(18) COLLATE Chinese_PRC_CS_AS null,	--货品货号
	[cost] money not null default(0),			--成本价	
	[price] money not null default(0),			--销售价	
	[store] int not null default(0),			--库存	
	[weight] decimal(19,3) not null default(0),	--重量	
	[degreePrice] text null,					--会员价
	[proIntroduction] nvarchar (1000) null,		--产品简介
	[publishDate] datetime NOT NULL ,			--发布时间
	[htmlName] nvarchar(255)					--静态页文件名
)
PPFOR_GO;

--添加产品
if exists(select name from sysobjects where name='PPFOR_TZB_Product_Add' and type='P')
	drop procedure PPFOR_TZB_Product_Add
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Product_Add(@ID int output,@belongType int,@productName nvarchar(200),
	@proDescription text,@bigImage nvarchar(255),@smallImage nvarchar(255),@ifThumbnail bit,
	@recommended bit,@new bit,@hot bit,@slide bit,@updateDate datetime,@sort int,@show bit,
	@moreSmallImage text,@productTemplate nvarchar(255),@pageKeyWord nvarchar(255),
	@pageDescription nvarchar(255),@click int,@slideImage nvarchar(255),
	@isPublic bit,@allowDegreeIds text,@belongBrand int,@no nvarchar(18),@score int,@mktPrice money,
	@spec text,@code nvarchar(18),@cost money,@price money,@store int,@weight decimal(19,3),
	@degreePrice text,@proIntroduction nvarchar (1000),@publishDate datetime,@htmlName nvarchar(255))
 AS 
	begin
		INSERT INTO [PPFOR_TZB_Product]([belongType],[productName],[proDescription],[bigImage],
			[smallImage],[ifThumbnail],[recommended],[new],[hot],[slide],[updateDate],[sort],
			[show],[moreSmallImage],[productTemplate],[pageKeyWord],[pageDescription],[click],
			[slideImage],[isPublic],[allowDegreeIds],[belongBrand],[no],[score],[mktPrice],[spec],
			[code],[cost],[price],[store],[weight],[degreePrice],[proIntroduction],[publishDate],[htmlName])
			VALUES(@belongType,@productName,@proDescription,@bigImage,@smallImage,@ifThumbnail,
			@recommended,@new,@hot,@slide,@updateDate,@sort,@show,@moreSmallImage,@productTemplate,
			@pageKeyWord,@pageDescription,@click,@slideImage,@isPublic,@allowDegreeIds,
			@belongBrand,@no,@score,@mktPrice,@spec,@code,@cost,@price,@store,@weight,@degreePrice,
			@proIntroduction,@publishDate,@htmlName)
		SET @ID = @@IDENTITY
	end
PPFOR_GO;

--删除产品
if exists(select name from sysobjects where name='PPFOR_TZB_Product_Delete' and type='P')
	drop procedure PPFOR_TZB_Product_Delete
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Product_Delete(@ID int)
 AS 
	DELETE [PPFOR_TZB_Product] WHERE [ID]=@ID
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Product_IsExistsNo' and type='P')
	drop procedure PPFOR_TZB_Product_IsExistsNo
PPFOR_GO;
CREATE  procedure PPFOR_TZB_Product_IsExistsNo(@no nvarchar (18),@retValue bit out)
as
	begin			
		if @no is not null and exists ( select [ID] from [PPFOR_TZB_Product] where [no]=@no) 
			set @retValue=1
		else
			set @retValue=0
	end
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Product_IsExistsCode' and type='P')
	drop procedure PPFOR_TZB_Product_IsExistsCode
PPFOR_GO;
CREATE  procedure PPFOR_TZB_Product_IsExistsCode(@code nvarchar (18),@retValue bit out)
as
	begin			
		if @code is not null and (exists ( select [ID] from [PPFOR_TZB_Product] where [code]=@code)
			or exists(select [ID] from [PPFOR_TZB_Goods] where [code]=@code))
			set @retValue=1
		else
			set @retValue=0
	end
PPFOR_GO;

--获取所有产品
if exists(select name from sysobjects where name='PPFOR_TZB_Product_GetShortList' and type='P')
	drop procedure PPFOR_TZB_Product_GetShortList
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Product_GetShortList(@belongType int)
 AS 
	SELECT [ID],[belongType],[htmlName] FROM [PPFOR_TZB_Product] WHERE belongType=@belongType
		 order by sort,[ID]
PPFOR_GO;

--获取产品库存
if exists(select name from sysobjects where name='PPFOR_TZB_Product_GetStore' and type='P')
	drop procedure PPFOR_TZB_Product_GetStore
PPFOR_GO;
CREATE   PROCEDURE PPFOR_TZB_Product_GetStore(@ID int)
 AS 
	SELECT [store] FROM [PPFOR_TZB_Product] WHERE [ID]=@ID 
PPFOR_GO;

--获取产品
if exists(select name from sysobjects where name='PPFOR_TZB_Product_GetModel' and type='P')
	drop procedure PPFOR_TZB_Product_GetModel
PPFOR_GO;
CREATE   PROCEDURE PPFOR_TZB_Product_GetModel(@ID int)
 AS 
	SELECT * FROM [PPFOR_TZB_Product] WHERE [ID]=@ID 
PPFOR_GO;

--获取产品购物信息
if exists(select name from sysobjects where name='PPFOR_TZB_Product_GetCartModel' and type='P')
	drop procedure PPFOR_TZB_Product_GetCartModel
PPFOR_GO;
CREATE   PROCEDURE PPFOR_TZB_Product_GetCartModel(@ID int)
 AS 
	SELECT [ID],[belongType],[productName],[smallImage],[show],[isPublic],[allowDegreeIds],[belongBrand],
		[no],[score],[mktPrice],[spec],[code],[cost],[price],[store],[weight],[degreePrice],[htmlName]
	 	FROM [PPFOR_TZB_Product] WHERE [ID]=@ID and [show]=1
PPFOR_GO;

--更新产品
if exists(select name from sysobjects where name='PPFOR_TZB_Product_Update' and type='P')
	drop procedure PPFOR_TZB_Product_Update
PPFOR_GO;
CREATE  PROCEDURE PPFOR_TZB_Product_Update(@ID int,@belongType int,@productName nvarchar(200),
	@proDescription text,@bigImage nvarchar(255),@smallImage nvarchar(255),@ifThumbnail bit,
	@recommended bit,@new bit,@hot bit,@slide bit,@updateDate datetime,@sort int,@show bit,
	@moreSmallImage text,@productTemplate nvarchar(255),@pageKeyWord nvarchar(255),
	@pageDescription nvarchar(255),@click int,@slideImage nvarchar(255),
	@isPublic bit,@allowDegreeIds text,@belongBrand int,@no nvarchar(18),@score int,@mktPrice money,
	@spec text,@code nvarchar(18),@cost money,@price money,@store int,@weight decimal(19,3),
	@degreePrice text,@proIntroduction nvarchar (1000),@publishDate datetime,@htmlName nvarchar(255))
 AS 
	UPDATE [PPFOR_TZB_Product] SET [belongType] = @belongType,[productName] = @productName,
		[proDescription]=@proDescription,[bigImage] = @bigImage,[smallImage] = @smallImage,
		[ifThumbnail] = @ifThumbnail,[recommended] = @recommended,[new] = @new,[hot] = @hot,
		[slide] = @slide,[updateDate] = @updateDate,[sort] = @sort,[show] = @show,
		[moreSmallImage] = @moreSmallImage,[productTemplate] = @productTemplate,
		[pageKeyWord] = @pageKeyWord,[pageDescription] = @pageDescription,[click] = @click,
		[slideImage]=@slideImage, [isPublic]=@isPublic,[allowDegreeIds]=@allowDegreeIds,
		[belongBrand]=@belongBrand,[no]=@no,[score]=@score,[mktPrice]=@mktPrice,[spec]=@spec,
		[code]=@code,[cost]=@cost,[price]=@price,[store]=@store,[weight]=@weight,
		[degreePrice]=@degreePrice,[proIntroduction]=@proIntroduction,
		[publishDate]=@publishDate,[htmlName]=@htmlName WHERE [ID]=@ID 
PPFOR_GO;

--更新产品编号
if exists(select name from sysobjects where name='PPFOR_TZB_Product_UpdateNoAndCode' and type='P')
	drop procedure PPFOR_TZB_Product_UpdateNoAndCode
PPFOR_GO;
CREATE  PROCEDURE PPFOR_TZB_Product_UpdateNoAndCode(@ID int,@no nvarchar(18),@code nvarchar(18),@store int)
 AS 
	UPDATE [PPFOR_TZB_Product] SET [no]=@no,[code]=@code,[store]=@store WHERE [ID]=@ID 
PPFOR_GO;

--更新产品列表信息
if exists(select name from sysobjects where name='PPFOR_TZB_Product_UpdateList' and type='P')
	drop procedure PPFOR_TZB_Product_UpdateList
PPFOR_GO;
CREATE  PROCEDURE PPFOR_TZB_Product_UpdateList(@ID int,@updateDate datetime,@sort int,@show bit)
 AS 
	UPDATE [PPFOR_TZB_Product] SET [updateDate] = @updateDate,[sort] = @sort,[show] = @show
		WHERE [ID]=@ID 
PPFOR_GO;

--分页返回产品资料
if exists(select name from sysobjects where name='PPFOR_TZB_Product_GetModelListByTypeID' and type='P')
	drop procedure PPFOR_TZB_Product_GetModelListByTypeID
PPFOR_GO;
create procedure PPFOR_TZB_Product_GetModelListByTypeID(@belongType 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

		select @rc=count([ID]) from [PPFOR_TZB_Product] where [belongType]=@belongType
		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],productName,smallImage,show,sort,recommended,new,hot,slide,
			updateDate,publishDate,htmlName from [PPFOR_TZB_Product] where [belongType]=' + str(@belongType) + ' '
	
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [sort] desc,[publishDate] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [sort] asc,[publishDate] asc) Temptable order by [sort] desc,[publishDate] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by 
				[sort] asc,[publishDate] asc) Temptable order by [sort] desc,[publishDate] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--分页返回搜索结果
if exists(select name from sysobjects where name='PPFOR_TZB_Product_GetModelListByWhere' and type='P')
	drop procedure PPFOR_TZB_Product_GetModelListByWhere
PPFOR_GO;
create procedure PPFOR_TZB_Product_GetModelListByWhere(@belongMenu int,@whereStr nvarchar(255),@pageSize int,@pageIndex int)
as
	begin
		declare @rc int
		declare @pc int
		declare @TmpSelect nvarchar(1000)
		declare @sqlStr nvarchar(2000)
		declare @sqlStr2 nvarchar(1000)
		
		if @pageSize<1
			set @pageSize=20

		set @TmpSelect = 'select @rc = count([ID]) from [PPFOR_TZB_Product] where belongType = any (select [ID] from PPFOR_TZB_ProductType where belongMenu=' + str(@belongMenu) + ') and ' + @whereStr		
		execute sp_executesql @TmpSelect, N'@rc int output', @rc output

		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],belongType,productName,smallImage,show,sort,updateDate,publishDate,htmlName 
			from [PPFOR_TZB_Product] where belongType = any (select [ID] from PPFOR_TZB_ProductType where belongMenu=' + str(@belongMenu) + ') and ' + @whereStr + ' '
	
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [sort] desc,[publishDate] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [sort] asc,[publishDate] asc) Temptable order by [sort] desc,[publishDate] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by 
				[sort] asc,[publishDate] asc) Temptable order by [sort] desc,[publishDate] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--分页返回搜索结果
if exists(select name from sysobjects where name='PPFOR_TZB_Product_GetModelListBySearch' and type='P')
	drop procedure PPFOR_TZB_Product_GetModelListBySearch
PPFOR_GO;

create procedure PPFOR_TZB_Product_GetModelListBySearch(@belongLanguage int,@belongMenu int,@memberDegree int,
	@whereStr nvarchar(255),@pageSize int,@pageIndex int)
as
	begin
		declare @rc int
		declare @pc int
		declare @TmpSelect nvarchar(1000)
		declare @sqlStr nvarchar(2000)
		declare @sqlStr2 nvarchar(1000)
		
		if @pageSize<1
			set @pageSize=20

		set @TmpSelect = 'select @rc = count([ID]) from PPFOR_TZB_Product where show=1 and [belongType]=any (select [ID] from PPFOR_TZB_ProductType where show=1 '
		if @belongMenu>0
			set @TmpSelect=@TmpSelect + ' and belongMenu='+str(@belongMenu)+') '
		else
			set @TmpSelect=@TmpSelect + ' and belongMenu= any(select [ID] from PPFOR_TZB_Menu where show=1 and belongLanguage=' + str(@belongLanguage) + ')) '
		set @TmpSelect=@TmpSelect + ' and ' + @whereStr + ' '
	
		if @memberDegree=0 
			set	@TmpSelect=@TmpSelect + ' and isPublic=1'
		else if @memberDegree>0
			set	@TmpSelect=@TmpSelect + ' and (isPublic=1 or allowDegreeIds like ''%,'+ rtrim(ltrim(str(@memberDegree)))+',%'')'
				
		execute sp_executesql @TmpSelect, N'@rc int output', @rc output

		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],belongType,productName,proIntroduction,bigImage,smallImage,updateDate,sort,click,
			slideImage,belongBrand,no,spec,mktPrice,price,degreePrice,publishDate,htmlName from [PPFOR_TZB_Product] where show=1 
			and [belongType]=any (select [ID] from [PPFOR_TZB_ProductType] where show=1 '
		if @belongMenu>0
			set @sqlStr2=@sqlStr2 + ' and belongMenu='+ str(@belongMenu)+') '
		else
			set @sqlStr2=@sqlStr2 + ' and belongMenu= any(select [ID] from PPFOR_TZB_Menu where show=1 and belongLanguage=' + str(@belongLanguage) + ')) '
		set @sqlStr2=@sqlStr2 + ' and ' + @whereStr + ' '	
				
		if @memberDegree=0 
			set	@sqlStr2=@sqlStr2 + ' and isPublic=1'
		else if @memberDegree>0
			set	@sqlStr2=@sqlStr2 + ' and (isPublic=1 or allowDegreeIds like ''%,'+ rtrim(ltrim(str(@memberDegree)))+',%'')'
			
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [sort] desc,[publishDate] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [sort] asc,[publishDate] asc) Temptable order by [sort] desc,[publishDate] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by 
				[sort] asc,[publishDate] asc) Temptable order by [sort] desc,[publishDate] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--获取相关产品列表
if exists(select name from sysobjects where name='PPFOR_TZB_Product_GetRelated' and type='P')
	drop procedure PPFOR_TZB_Product_GetRelated
PPFOR_GO;
create procedure PPFOR_TZB_Product_GetRelated(@id int,@belongType int,@keyword nvarchar(255),@number int)
as
	begin
		if @number>0
			set ROWCOUNT @number
	 	if @keyword is not null and len(@keyword)>0
			select [ID],[productName],[smallImage],[htmlName] from [PPFOR_TZB_Product] where [ID]<>@id 
				and belongType=@belongType and pageKeyWord like @keyword order by [sort] desc,[publishDate] desc
		else 
			select [ID],[productName],[smallImage],[htmlName] from [PPFOR_TZB_Product] where [ID]<>@id 
				and belongType=@belongType order by [sort] desc, [publishDate] desc
	end
PPFOR_GO;

--获取产品列表
if exists(select name from sysobjects where name='PPFOR_TZB_Product_GetModelListForLabel' and type='P')
	drop procedure PPFOR_TZB_Product_GetModelListForLabel
PPFOR_GO;
create procedure PPFOR_TZB_Product_GetModelListForLabel(@belongLanguage int,@belongMenu int,@belongType int,
	@belongBrand int,@memberDegree int,@new bit,@recommended bit,@hot bit,@slide bit,@number int)
as
	begin
		declare @sql varchar(1000)
		declare @sqlStr2 varchar(255)
		if @number>=0			
		begin
			set @sqlStr2=' [ID],belongType,productName,proIntroduction,bigImage,smallImage,updateDate,sort,click,
			slideImage,belongBrand,no,spec,mktPrice,price,degreePrice,publishDate,htmlName '
			
			if @belongBrand>0
				set	@sql= 'select top ' + str(@number) + @sqlStr2 + ' from [PPFOR_TZB_Product] where show=1 and [belongBrand]=' + str(@belongBrand)
			else if @belongType>0
				set	@sql= 'select top ' + str(@number) + @sqlStr2 + ' from [PPFOR_TZB_Product] where show=1 and [belongType]=' + str(@belongType)
			else if @belongMenu>0
				set	@sql= 'select top ' + str(@number) + @sqlStr2 + ' from [PPFOR_TZB_Product] where show=1 and [belongType]=any (select [ID] from [PPFOR_TZB_ProductType] where show=1 and belongMenu=' + str(@belongMenu) + ')'
			else if @belongLanguage>0
				set	@sql= 'select top ' + str(@number) + @sqlStr2 + ' from [PPFOR_TZB_Product] where show=1 and [belongType]=any (select [ID] from [PPFOR_TZB_ProductType] where show=1 and belongMenu= any(select [ID] from [PPFOR_TZB_Menu] where show=1 and belongLanguage=' + str(@belongLanguage) + '))'
			if @sql is not null
			begin
			 	if @memberDegree=0 
					set	@sql=@sql + ' and isPublic=1'
				else if @memberDegree>0
					set	@sql=@sql + ' and (isPublic=1 or allowDegreeIds like ''%,'+ rtrim(ltrim(str(@memberDegree)))+',%'')'
				
			 	if @recommended=1 
					set @sql=@sql + ' and [recommended]=1'					
				else if @new=1
					set @sql=@sql + ' and [new]=1'					
				else if @hot=1
					set @sql=@sql + ' and [hot]=1'
				else if @slide=1
					set @sql=@sql + ' and [slide]=1'					
				set @sql=@sql + ' order by [sort] desc, [publishDate] desc'
				exec (@sql)
			end
		end
	end
PPFOR_GO;

--返回单页产品列表
if exists(select name from sysobjects where name='PPFOR_TZB_Product_GetModelListByPage' and type='P')
	drop procedure PPFOR_TZB_Product_GetModelListByPage
PPFOR_GO;
create procedure PPFOR_TZB_Product_GetModelListByPage(@belongLanguage int,@belongMenu int,@belongType 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
		
		if @belongType>0
			set @rc=(select count([ID]) from [PPFOR_TZB_Product] where [show]=1 and [belongType]=@belongType)
		else if @belongMenu>0
			set @rc=(select count([ID]) from [PPFOR_TZB_Product] where [show]=1 and [belongType]=any (select [ID] from [PPFOR_TZB_ProductType] where [show]=1 and [belongMenu]=@belongMenu))
		else if @belongLanguage>0
			set	@rc=(select count([ID]) from [PPFOR_TZB_Product] where [show]=1 and [belongType]=any (select [ID] from [PPFOR_TZB_ProductType] where [show]=1 and [belongMenu]= any(select [ID] from [PPFOR_TZB_Menu] where [show]=1 and [belongLanguage]=@belongLanguage)))
		else 
			set @rc=(select count([ID]) from [PPFOR_TZB_Product] where [show]=1)
		
		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

		if @belongType>0
			set	@sqlStr2=' * from [PPFOR_TZB_Product] where show=1 and [belongType]=' + str(@belongType) + ' '
		else if @belongMenu>0
			set	@sqlStr2=' * from [PPFOR_TZB_Product] where show=1 and [belongType]=any (select [ID] from PPFOR_TZB_ProductType where show=1 and belongMenu=' + str(@belongMenu) + ') '
		else if @belongLanguage>0
			set	@sqlStr2=' * from [PPFOR_TZB_Product] where show=1 and [belongType]=any (select [ID] from PPFOR_TZB_ProductType where show=1 and belongMenu= any(select [ID] from PPFOR_TZB_Menu where show=1 and belongLanguage=' + str(@belongLanguage) + ')) '
		else
			set @sqlStr2=' * from [PPFOR_TZB_Product] where show=1 '

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

--返回单页产品列表
if exists(select name from sysobjects where name='PPFOR_TZB_Product_GetShortListByPage' and type='P')
	drop procedure PPFOR_TZB_Product_GetShortListByPage
PPFOR_GO;
create procedure PPFOR_TZB_Product_GetShortListByPage(@belongLanguage int,@belongMenu int,@belongType int,
	@belongBrand int,@memberDegree 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 @sqlStr2='select @rc = count([ID]) from [PPFOR_TZB_Product] where [show]=1'
		if @belongBrand>0
			set @sqlStr2=@sqlStr2+' and [belongBrand]='+str(@belongBrand)
		else if @belongType>0
			set @sqlStr2=@sqlStr2+' and [belongType]='+str(@belongType)
		else if @belongMenu>0
			set @sqlStr2=@sqlStr2+' and [belongType]=any (select [ID] from [PPFOR_TZB_ProductType] where [show]=1 and [belongMenu]='+str(@belongMenu)+')'
		else if @belongLanguage>0
			set @sqlStr2=@sqlStr2+' and [belongType]=any (select [ID] from [PPFOR_TZB_ProductType] where [show]=1 and [belongMenu]= any(select [ID] from [PPFOR_TZB_Menu] where [show]=1 and [belongLanguage]='+str(@belongLanguage)+'))'
		
		if @memberDegree=0 
			set	@sqlStr2=@sqlStr2 + ' and isPublic=1'
		else if @memberDegree>0
			set	@sqlStr2=@sqlStr2 + ' and (isPublic=1 or allowDegreeIds like ''%,'+ rtrim(ltrim(str(@memberDegree)))+',%'')'
			
		execute sp_executesql @sqlStr2, N'@rc int output', @rc output			
		
		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],belongType,productName,proIntroduction,bigImage,smallImage,updateDate,sort,click,
			slideImage,belongBrand,no,spec,mktPrice,price,degreePrice,publishDate,htmlName from [PPFOR_TZB_Product] where show=1 '
		if @belongBrand>0
			set	@sqlStr2=@sqlStr2 + ' and [belongBrand]=' + str(@belongBrand) + ' '
		else if @belongType>0
			set	@sqlStr2=@sqlStr2 + ' and [belongType]=' + str(@belongType) + ' '
		else if @belongMenu>0
			set	@sqlStr2=@sqlStr2 + ' and [belongType]=any (select [ID] from PPFOR_TZB_ProductType where show=1 and belongMenu=' + str(@belongMenu) + ') '
		else if @belongLanguage>0
			set	@sqlStr2=@sqlStr2 + ' and [belongType]=any (select [ID] from PPFOR_TZB_ProductType where show=1 and belongMenu= any(select [ID] from PPFOR_TZB_Menu where show=1 and belongLanguage=' + str(@belongLanguage) + ')) '
		
		if @memberDegree=0 
			set	@sqlStr2=@sqlStr2 + ' and isPublic=1'
		else if @memberDegree>0
			set	@sqlStr2=@sqlStr2 + ' and (isPublic=1 or allowDegreeIds like ''%,'+ rtrim(ltrim(str(@memberDegree)))+',%'')'
			
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [sort] desc, [publishDate] desc'
		else if @pageIndex = @pc
			set @sqlStr = 'select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [sort] asc,[publishDate] asc) Temptable order by [sort] desc, [publishDate] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [sort] asc,
				[publishDate] asc) Temptable order  by [sort] desc,[publishDate] desc'
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--返回单页产品列表
if exists(select name from sysobjects where name='PPFOR_TZB_Product_GetModelListInTypeByPage' and type='P')
	drop procedure PPFOR_TZB_Product_GetModelListInTypeByPage
PPFOR_GO;
create procedure PPFOR_TZB_Product_GetModelListInTypeByPage(@belongType int,@memberDegree 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 @sqlStr2='select @rc = count([ID]) from [PPFOR_TZB_Product] where [show]=1 and [belongType] 
			in (select [ID] from PPFOR_TZB_ProductType_GetChildIdsTable('+str(@belongType)+') where [show]=1)'

		if @memberDegree=0 
			set	@sqlStr2=@sqlStr2 + ' and isPublic=1'
		else if @memberDegree>0
			set	@sqlStr2=@sqlStr2 + ' and (isPublic=1 or allowDegreeIds like ''%,'+ rtrim(ltrim(str(@memberDegree)))+',%'')'
			
		execute sp_executesql @sqlStr2, N'@rc int output', @rc output		

		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],belongType,productName,proIntroduction,bigImage,smallImage,updateDate,sort,
			click,slideImage,belongBrand,no,spec,mktPrice,price,degreePrice,publishDate,htmlName 
			from [PPFOR_TZB_Product] where [show]=1 and [belongType] in 
			(select [ID] from PPFOR_TZB_ProductType_GetChildIdsTable('+str(@belongType)+') where [show]=1) '	

		if @memberDegree=0 
			set	@sqlStr2=@sqlStr2 + ' and isPublic=1'
		else if @memberDegree>0
			set	@sqlStr2=@sqlStr2 + ' and (isPublic=1 or allowDegreeIds like ''%,'+ rtrim(ltrim(str(@memberDegree)))+',%'')'
			
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [sort] desc, [publishDate] desc'
		else if @pageIndex = @pc
			set @sqlStr = 'select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [sort] asc,[publishDate] asc) Temptable order by [sort] desc, [publishDate] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [sort] asc,
				[publishDate] asc) Temptable order  by [sort] desc,[publishDate] desc'
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--获取产品点击数
if exists(select name from sysobjects where name='PPFOR_TZB_Product_getClickCount' and type='P')
	drop procedure PPFOR_TZB_Product_getClickCount
PPFOR_GO;
create procedure PPFOR_TZB_Product_GetClickCount(@ID int)
as
	select [click] from [PPFOR_TZB_Product] where [ID]=@ID
PPFOR_GO;
--获取产品名称
if exists(select name from sysobjects where name='PPFOR_TZB_Product_GetModelName' and type='P')
	drop procedure PPFOR_TZB_Product_GetModelName
PPFOR_GO;
create procedure PPFOR_TZB_Product_GetModelName(@ID int)
as
	select [productName] from [PPFOR_TZB_Product] where [ID]=@ID
PPFOR_GO;
--点击+1，并返回产品动态信息
if exists(select name from sysobjects where name='PPFOR_TZB_Product_GetVisitInfo' and type='P')
	drop procedure PPFOR_TZB_Product_GetVisitInfo
PPFOR_GO;
create procedure PPFOR_TZB_Product_GetVisitInfo(@ID int)
as
	update [PPFOR_TZB_Product] set [click]=[click]+1 where [show]=1 and [ID]=@ID and isPublic=1
	select [PPFOR_TZB_Product].[ID],[PPFOR_TZB_ProductType].[ID],
		[PPFOR_TZB_ProductType].[productTypeName],[PPFOR_TZB_Product].[click]		
		from [PPFOR_TZB_Product],[PPFOR_TZB_ProductType] where [PPFOR_TZB_Product].[show]=1
	 	and [PPFOR_TZB_Product].[ID]=@ID and isPublic=1
		and [PPFOR_TZB_Product].[belongType]=[PPFOR_TZB_ProductType].[ID]
PPFOR_GO;	

--点击+1，并返回产品动态信息,权限
if exists(select name from sysobjects where name='PPFOR_TZB_Product_GetVisitContent' and type='P')
	drop procedure PPFOR_TZB_Product_GetVisitContent
PPFOR_GO;
create procedure PPFOR_TZB_Product_GetVisitContent(@ID int)
as
	update [PPFOR_TZB_Product] set [click]=[click]+1 where [show]=1 and [ID]=@ID
	select [PPFOR_TZB_Product].[ID],[PPFOR_TZB_ProductType].[ID],
		[PPFOR_TZB_ProductType].[productTypeName],[PPFOR_TZB_Product].[click],
		[PPFOR_TZB_Product].[proDescription],[PPFOR_TZB_Product].[isPublic],
		[PPFOR_TZB_Product].[allowDegreeIds] from [PPFOR_TZB_Product],[PPFOR_TZB_ProductType]
		where [PPFOR_TZB_Product].[show]=1 and [PPFOR_TZB_Product].[ID]=@ID
		and [PPFOR_TZB_Product].[belongType]=[PPFOR_TZB_ProductType].[ID]
PPFOR_GO;	

--获取品牌信息
if exists(select name from sysobjects where name='PPFOR_TZB_Product_GetBrand' and type='P')
	drop procedure PPFOR_TZB_Product_GetBrand
PPFOR_GO;
create procedure PPFOR_TZB_Product_GetBrand(@ID int)
as	
	select [PPFOR_TZB_Brand].[ID],[PPFOR_TZB_Brand].[Name],[PPFOR_TZB_Brand].[HtmlName] 
		from [PPFOR_TZB_Product],[PPFOR_TZB_Brand]
		where [PPFOR_TZB_Product].[show]=1 and [PPFOR_TZB_Product].[ID]=@ID
		and [PPFOR_TZB_Product].[belongBrand]=[PPFOR_TZB_Brand].[ID]
PPFOR_GO;	

--返回上一条产品
if exists(select name from sysobjects where name='PPFOR_TZB_Product_GetPrevProduct' and type='P')
	drop procedure PPFOR_TZB_Product_GetPrevProduct
PPFOR_GO;
create procedure PPFOR_TZB_Product_GetPrevProduct(@ID int) 
as
	select top 1 [ID],[productName],[htmlName] from [PPFOR_TZB_Product] where [show]=1 and [ID]>@ID and [belongType]=
		(select [belongType] from [PPFOR_TZB_Product] where [show]=1 and [ID]=@ID) order by [ID] asc
PPFOR_GO;	

--返回下一条产品
if exists(select name from sysobjects where name='PPFOR_TZB_Product_GetNextProduct' and type='P')
	drop procedure PPFOR_TZB_Product_GetNextProduct
PPFOR_GO;
create procedure PPFOR_TZB_Product_GetNextProduct(@ID int) 
as
	select top 1 [ID],[productName],[htmlName] from [PPFOR_TZB_Product] where [show]=1 and [ID]<@ID and [belongType]=
		(select [belongType] from [PPFOR_TZB_Product] where [show]=1 and [ID]=@ID) order by [ID] desc
PPFOR_GO;	

--返回第一条产品
if exists(select name from sysobjects where name='PPFOR_TZB_Product_GetFirstProduct' and type='P')
	drop procedure PPFOR_TZB_Product_GetFirstProduct
PPFOR_GO;
create procedure PPFOR_TZB_Product_GetFirstProduct(@belongType int) 
as
	select top 1 [ID],[productName],[htmlName] from [PPFOR_TZB_Product] where [show]=1 and [belongType]=
		@belongType order by [sort] desc, [publishDate] desc
PPFOR_GO;	

--获取相关产品列表
if exists(select name from sysobjects where name='PPFOR_TZB_Product_GetRelatedList' and type='P')
	drop procedure PPFOR_TZB_Product_GetRelatedList
PPFOR_GO;
create procedure PPFOR_TZB_Product_GetRelatedList(@ID int,@number int)
as
	begin
		declare @keyword nvarchar (255)
		declare @belongType int
		select @keyword=pageKeyWord,@belongType=belongType from [PPFOR_TZB_Product] 
			where [show]=1 and [ID]=@ID 
		if @number>0
			set ROWCOUNT @number
	 	if @keyword is not null and len(@keyword)>0
			begin
				exec PPFOR_TZB_Meta_FormatKeyWord @keyword,@keyword out
				select [ID],[productName],[smallImage],[htmlName] from [PPFOR_TZB_Product] where [ID]<>@ID 
					and [show]=1 and belongType=@belongType and pageKeyWord like @keyword 
					order by [sort] desc, [publishDate] desc
			end
		else if @belongType>0
			select [ID],[productName],[smallImage],[htmlName] from [PPFOR_TZB_Product] where [ID]<>@ID 
				and [show]=1 and belongType=@belongType 
				order by [sort] desc, [publishDate] desc
	end
PPFOR_GO;
--更新产品静态页文件名
if exists(select name from sysobjects where name='PPFOR_TZB_Product_UpdateHtmlName' and type='P')
	drop procedure PPFOR_TZB_Product_UpdateHtmlName
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_Product_UpdateHtmlName(@ID int,@htmlName nvarchar(255))
 AS 
	UPDATE [PPFOR_TZB_Product] SET [htmlName] = @htmlName WHERE [ID]=@ID 
PPFOR_GO;

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