-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-19
-- ------------------------------
-- 语言表
-- ==============================

--创建语言表
if not exists (select * from sysobjects where name='PPFOR_TZB_Language')
CREATE TABLE [PPFOR_TZB_Language] (
	[ID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[languageName] nvarchar (20) COLLATE Chinese_PRC_CS_AS NOT NULL ,		--语言名称
	[fileName] varchar (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,			--保存文件夹名称
	[languageSaveFile] nvarchar (150) NOT NULL ,							--保存文件夹路径
	[languageTemple] nvarchar (150) NOT NULL ,								--语言首页模板
	[dateTime] datetime NOT NULL ,											--更新时间
	[isDefault] bit NOT NULL ,												--是否默认语言
	[webTitle] nvarchar (255) NULL ,										--网站标题
	[pageKeyWord] nvarchar (255) COLLATE Chinese_PRC_CI_AS NULL ,			--Meta关键字
	[pageDescription] nvarchar (255) COLLATE Chinese_PRC_CI_AS NULL ,		--Meta描述
	[newsSearchTemplate] nvarchar (150) NULL ,								--新闻搜索页模板
	[proSearchTemplate] nvarchar (150) NULL ,								--产品搜索页模板
	[picSearchTemplate] nvarchar (150) NULL ,								--图片搜索页模板
	[downloadSearchTemplate] nvarchar (150) NULL,							--下载搜索页模板
	[brandTemplate] nvarchar(150) NULL,										--品牌页模板
	[brandListTemplate] nvarchar (150) NULL,								--品牌列表页模板
	[isShowUserCheckCode] bit not null default(1),							--是否显示会员登录验证码
	[userLoginPage] nvarchar (150) NULL,									--会员登录页面
	[userRegeditPage] nvarchar (150) NULL,									--会员注册页面
	[userGetPwdPage] nvarchar (150) NULL,									--会员找回密码页面
	[userCenterPage] nvarchar (150) NULL,									--会员中心页面
	[userLogoutPage] nvarchar (150) NULL,									--会员注销页面
	[cartViewPage] nvarchar (150) NULL,										--购物车页面		
	[createType] tinyint not null 											--创建HTML命名方式
) 
PPFOR_GO;

--添加语言
if exists(select name from sysobjects where name='PPFOR_TZB_Language_Add' and type='P')
	drop procedure [PPFOR_TZB_Language_Add]
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Language_Add(@ID int output,@languageName nvarchar(20),
	@fileName varchar(20),@languageSaveFile nvarchar(150),@languageTemple nvarchar(150),
	@dateTime datetime,@isDefault bit,@webTitle nvarchar (255),@pageKeyWord nvarchar (255),
	@pageDescription nvarchar (255),@newsSearchTemplate nvarchar (150),
	@proSearchTemplate nvarchar (150),@picSearchTemplate nvarchar (150),
	@downloadSearchTemplate nvarchar (150),@brandTemplate nvarchar(150),
	@brandListTemplate nvarchar(150),@isShowUserCheckCode bit,@userLoginPage nvarchar (150),
	@userRegeditPage nvarchar (150),@userGetPwdPage nvarchar (150),@userCenterPage nvarchar (150),
	@userLogoutPage nvarchar (150),@cartViewPage nvarchar (150),@createType tinyint)
 AS 
	begin
		INSERT INTO [PPFOR_TZB_Language]([languageName],[fileName],[languageSaveFile],[languageTemple],
		[dateTime],[isDefault],[webTitle],[pageKeyWord],[pageDescription],[newsSearchTemplate],
		[proSearchTemplate],[picSearchTemplate],[downloadSearchTemplate],[brandTemplate],[brandListTemplate],
		[isShowUserCheckCode],[userLoginPage],[userRegeditPage],[userGetPwdPage],[userCenterPage],
		[userLogoutPage],[cartViewPage],[createType]) VALUES (@languageName,@fileName,@languageSaveFile,@languageTemple,
		@dateTime,@isDefault,@webTitle,@pageKeyWord,@pageDescription,@newsSearchTemplate,@proSearchTemplate,
		@picSearchTemplate,@downloadSearchTemplate,@brandTemplate,@brandListTemplate,@isShowUserCheckCode,
		@userLoginPage,@userRegeditPage,@userGetPwdPage,@userCenterPage,@userLogoutPage,@cartViewPage,@createType)
	
		SET @ID = @@IDENTITY
		if @ID>0 and @isDefault=1
			update [PPFOR_TZB_Language] set [isDefault]=0 where [ID]<>@ID
	end
PPFOR_GO;

---删除
if exists(select name from sysobjects where name='PPFOR_TZB_Language_Delete' and type='P')
	drop procedure [PPFOR_TZB_Language_Delete]
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_Language_Delete(@ID int)
 AS 
	DELETE [PPFOR_TZB_Language] WHERE [ID]=@ID 
PPFOR_GO;

---检查是否存在该语言名称
if exists(select name from sysobjects where name='PPFOR_TZB_Language_IsExists' and type='P')
	drop procedure [PPFOR_TZB_Language_IsExists]
PPFOR_GO;

CREATE   PROCEDURE PPFOR_TZB_Language_IsExists(@languageName nvarchar(20),@retValue bit out)
AS
	begin
		DECLARE @TempID int
		SELECT @TempID = count(1) FROM [PPFOR_TZB_Language] WHERE languageName=@languageName
		IF @TempID = 0
			set @retValue= 0
		ELSE
			set @retValue= 1	
	end
PPFOR_GO;

---检查是否存在该语言名称
if exists(select name from sysobjects where name='PPFOR_TZB_Language_IsExistsFileName' and type='P')
	drop procedure [PPFOR_TZB_Language_IsExistsFileName]
PPFOR_GO;

CREATE  PROCEDURE PPFOR_TZB_Language_IsExistsFileName(@fileName varchar(20),@retValue bit out)
AS
	begin
		DECLARE @TempID int
		SELECT @TempID = count(1) FROM [PPFOR_TZB_Language] WHERE [fileName]=@fileName
		IF @TempID = 0
			set @retValue= 0
		ELSE
			set @retValue= 1	
	end
PPFOR_GO;

--获取语言
if exists(select name from sysobjects where name='PPFOR_TZB_Language_GetModel' and type='P')
	drop procedure [PPFOR_TZB_Language_GetModel]
PPFOR_GO;

CREATE  PROCEDURE PPFOR_TZB_Language_GetModel(@ID int)
 AS 
	SELECT * FROM [PPFOR_TZB_Language] WHERE [ID]=@ID 
PPFOR_GO;

--获取语言部分信息
if exists(select name from sysobjects where name='PPFOR_TZB_Language_GetShortModel' and type='P')
	drop procedure [PPFOR_TZB_Language_GetShortModel]
PPFOR_GO;

CREATE  PROCEDURE PPFOR_TZB_Language_GetShortModel(@ID int)
 AS 
	SELECT [ID],[languageName],[fileName],[languageSaveFile],[dateTime],[isDefault],[webTitle],
		[isShowUserCheckCode],[createType] FROM [PPFOR_TZB_Language] WHERE [ID]=@ID 
PPFOR_GO;

---获得所有语言
if exists(select name from sysobjects where name='PPFOR_TZB_Language_GetModelList' and type='P')
	drop procedure [PPFOR_TZB_Language_GetModelList]
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_Language_GetModelList
AS	
	SELECT * FROM [PPFOR_TZB_Language] 
PPFOR_GO;

--更新语言
if exists(select name from sysobjects where name='PPFOR_TZB_Language_Update' and type='P')
	drop procedure [PPFOR_TZB_Language_Update]
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_Language_Update(@ID int,@languageName nvarchar(20),@fileName varchar(20),
	@languageSaveFile nvarchar(150),@languageTemple nvarchar(150),@dateTime datetime,@isDefault bit,
	@webTitle nvarchar (255),@pageKeyWord nvarchar (255),@pageDescription nvarchar (255),
	@newsSearchTemplate nvarchar (150),@proSearchTemplate nvarchar (150),
	@picSearchTemplate nvarchar(150),@downloadSearchTemplate nvarchar(150),
	@brandTemplate nvarchar(150),@brandListTemplate nvarchar(150),@isShowUserCheckCode bit,
	@userLoginPage nvarchar (150),@userRegeditPage nvarchar (150),@userGetPwdPage nvarchar (150),
	@userCenterPage nvarchar (150),@userLogoutPage nvarchar (150),@cartViewPage nvarchar (150),@createType tinyint)
 AS 
	begin
		declare @TempID int

		UPDATE [PPFOR_TZB_Language] SET @TempID=1,[languageName] = @languageName,[fileName] = @fileName,
		[languageSaveFile] = @languageSaveFile,[languageTemple] = @languageTemple,[dateTime] = @dateTime,
		[isDefault]=@isDefault,[webTitle]=@webTitle,[pageKeyWord]=@pageKeyWord,
		[pageDescription]=@pageDescription,[newsSearchTemplate]=@newsSearchTemplate,
		[proSearchTemplate]=@proSearchTemplate,[picSearchTemplate]=@picSearchTemplate,
		[downloadSearchTemplate]=@downloadSearchTemplate,[brandTemplate]=@brandTemplate,
		[brandListTemplate]=@brandListTemplate,[isShowUserCheckCode]=@isShowUserCheckCode,
		[userLoginPage]=@userLoginPage,[userRegeditPage]=@userRegeditPage,[userGetPwdPage]=@userGetPwdPage,
		[userCenterPage]=@userCenterPage,[userLogoutPage]=@userLogoutPage,
		[cartViewPage]=@cartViewPage,[createType]=@createType WHERE [ID]=@ID 
	
		if @TempID=1 and @isDefault=1
			UPDATE [PPFOR_TZB_Language] SET [isDefault]=0 where [ID]<> @ID	
	end
PPFOR_GO;

--获取语言列表
if exists(select name from sysobjects where name='PPFOR_TZB_Language_GetShortListForLabel' and type='P')
	drop procedure PPFOR_TZB_Language_GetShortListForLabel
PPFOR_GO;
create procedure PPFOR_TZB_Language_GetShortListForLabel	
as
	select [ID],[languageName] from PPFOR_TZB_Language
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Language_GetName' and type='P')
	drop procedure PPFOR_TZB_Language_GetName
PPFOR_GO;
--获取语言名称
create procedure PPFOR_TZB_Language_GetName(@id int)	
as
	select [languageName] from PPFOR_TZB_Language where [ID]=@id
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Language_GetFileName' and type='P')
	drop procedure PPFOR_TZB_Language_GetFileName
PPFOR_GO;
--获取语言文件夹名称
create procedure PPFOR_TZB_Language_GetFileName(@id int)	
as
	select [fileName] from PPFOR_TZB_Language where [ID]=@id
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Language_GetCreateType' and type='P')
	drop procedure PPFOR_TZB_Language_GetCreateType
PPFOR_GO;
--获取语言静态页命名方式
create procedure PPFOR_TZB_Language_GetCreateType(@ID int)	
as
	select [createType] from PPFOR_TZB_Language where [ID]=@ID
PPFOR_GO;