SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  View dbo.View_CommunityJoinForum    Script Date: 2009-7-10 19:03:37 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[View_CommunityJoinForum]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[View_CommunityJoinForum]
GO

--===============================================================================
--== Forum е CommunityID    ID γһ IDصļ¼Ϊ Community  Forum ѡʱݹµ
--== Community ѡһ¼ JavaScript Ӹݹµѡȡ Community Ӧ Forum 䵽 Forum ѡ
--===============================================================================
--==Ȩ 2001-2009 CMS CMS- http://www.zhuzhicms.com
--==޸ 2008-10-20
--===============================================================================

CREATE VIEW dbo.View_CommunityJoinForum
AS

--ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ

--====================================================
--==
--====================================================
SELECT  TOP 100 PERCENT 

	fr.[ID] AS [ID],
	 (CONVERT(NVARCHAR(20),fr.CommunityID) +N':'+CONVERT(NVARCHAR(20),fr.[ID]))   COLLATE database_default AS CommunityIDJoinForumID,
	fr.[Name] AS [Name]

FROM dbo.Forum fr

--ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ





























GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  SELECT  ON [dbo].[View_CommunityJoinForum]  TO [public]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  View dbo.View_KnowledgeTree    Script Date: 2009-7-10 19:03:37 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[View_KnowledgeTree]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[View_KnowledgeTree]
GO

--===============================================================================
--== MainDirectory, SubDirectory, PageDirectory, Knowledge ϳһ ID, Parent....ṹļ¼
--===============================================================================
--==Ȩ 2001-2009 CMS CMS- http://www.zhuzhicms.com
--==޸ 2008-10-20
--===============================================================================

CREATE VIEW dbo.View_KnowledgeTree
AS

--ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ

--====================================================
--==
--====================================================
SELECT TOP 100 PERCENT

	md.[ID] AS [ID],
	0 AS KnowledgeID,
	md.[Name] AS [Name],
	0 AS Parent,
	md.Serial AS Serial,
	N'false' AS Home,
	N'false' AS Overview,
	N'' AS ParentDirectory,
	N'MainDirectory' AS Directory,
	N'SubDirectory' AS ChildDirectory,
	mdc.Title AS Title,
	mdc.Culture AS Culture,
	ISNULL(mdc.Hidden,N'false') AS Hidden

FROM dbo.MainDirectory md
LEFT JOIN dbo.MainDirectoryContent mdc ON mdc.MainDirectoryID=md.[ID]


UNION


SELECT TOP 100 PERCENT

	sd.[ID] AS [ID],
	0 AS KnowledgeID,
	sd.[Name] AS [Name],
	sd.MainDirectoryID AS Parent,
	sd.Serial AS Serial,
	N'false' AS Home,
	ISNULL(sd.Overview,N'false') AS Overview,
	N'MainDirectory' AS ParentDirectory,
	N'SubDirectory' AS Directory,
	N'PageDirectory' AS ChildDirectory,
	sdc.Title AS Title,
	sdc.Culture AS Culture,
	ISNULL(sdc.Hidden,N'false') AS Hidden

FROM dbo.SubDirectory sd
LEFT JOIN dbo.SubDirectoryContent sdc ON sdc.SubDirectoryID=sd.[ID]


UNION

	
SELECT TOP 100 PERCENT

	pd.[ID] AS [ID],
	0 AS KnowledgeID,
	pd.[Name] AS [Name],
	pd.SubDirectoryID AS Parent,
	pd.Serial AS Serial,
	ISNULL(pd.Home,N'false') AS Home,
	ISNULL(pd.Overview,N'false') AS Overview,
	N'SubDirectory' AS ParentDirectory,
	N'PageDirectory' AS Directory,
	N'Knowledge' AS ChildDirectory,
	pdc.Title AS Title,
	pdc.Culture AS Culture,
	ISNULL(pdc.Hidden,N'false') AS Hidden

FROM dbo.PageDirectory pd
LEFT JOIN dbo.PageDirectoryContent pdc ON pdc.PageDirectoryID=pd.[ID]


UNION 


SELECT TOP 100 PERCENT

	now.[ID] AS [ID],
	now.[ID] AS KnowledgeID,
	pd.[Name] AS [Name],
	now.PageDirectoryID AS Parent,
	now.Serial AS Serial,
	N'false' AS Home,
	N'false' AS Overview,
	N'PageDirectory' AS ParentDirectory,
	N'Knowledge' AS Directory,
	N'' AS ChildDirectory,
	nowc.Title AS Title,
	nowc.Culture AS Culture,
	ISNULL(nowc.Hidden,N'false') AS Hidden

FROM dbo.Knowledge now
LEFT JOIN dbo.KnowledgeContent nowc ON nowc.KnowledgeID=now.[ID]
LEFT JOIN dbo.PageDirectory pd ON pd.[ID]=now.PageDirectoryID

--====================================================
--==ʹ UNION ʱֻһ UNION ſʹ ORDER BY
--====================================================
ORDER BY Serial


--ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ















































GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  SELECT  ON [dbo].[View_KnowledgeTree]  TO [public]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  View dbo.View_CommunityTree    Script Date: 2009-7-10 19:03:37 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[View_CommunityTree]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[View_CommunityTree]
GO

--===============================================================================
--== Community, Forum ϳһ ID, Parent....ṹļ¼
--===============================================================================
--==Ȩ 2001-2009 CMS CMS- http://www.zhuzhicms.com
--==޸ 2008-10-20
--===============================================================================

CREATE VIEW dbo.View_CommunityTree
AS

--ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ

--====================================================
--==
--====================================================
SELECT TOP 100 PERCENT

	co.[ID] AS [ID],
	0 AS Parent,
	co.Serial AS Serial,
	N'' AS ParentDirectory,
	N'Community' AS Directory,
	N'Forum' AS ChildDirectory,
	co.[Name] AS [Name],
	coc.Culture AS Culture,
	coc.Title AS Title,
	coc.[Description] AS [Description],
	ISNULL(coc.Hidden,N'false') AS Hidden

FROM dbo.Community co
LEFT JOIN dbo.CommunityContent coc ON coc.CommunityID=co.[ID]


UNION


SELECT TOP 100 PERCENT

	fo.[ID] AS [ID],
	fo.CommunityID AS Parent,
	fo.Serial AS Serial,
	N'Community' AS ParentDirectory,
	N'Forum' AS Directory,
	N'' AS ChildDirectory,
	fo.[Name] AS [Name],
	foc.Culture AS Culture,
	foc.Title AS Title,
	foc.[Description] AS [Description],
	ISNULL(foc.Hidden,N'false') AS Hidden

FROM dbo.Forum fo
LEFT JOIN dbo.ForumContent foc ON foc.ForumID=fo.[ID]


--====================================================
--==ʹ UNION ʱֻһ UNION ſʹ ORDER BY
--====================================================
ORDER BY Serial

--ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ









































GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  SELECT  ON [dbo].[View_CommunityTree]  TO [public]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  View dbo.View_OptionCat    Script Date: 2009-7-10 19:03:37 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[View_OptionCat]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[View_OptionCat]
GO

--===============================================================================
--== Option / OptionContent  Option 𣨲ͬµģ
--===============================================================================
--==Ȩ 2001-2009 CMS CMS- http://www.zhuzhicms.com
--==޸ 2009-03-01
--===============================================================================

CREATE VIEW dbo.View_OptionCat
AS

--ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ

--====================================================
--==
--====================================================
SELECT TOP 100 PERCENT 

	opt.Cat AS Cat,
	opt.RightIndent AS RightIndent,
	opc.CatTitle AS CatTitleWithSerial,
	RIGHT(opc.CatTitle,LEN(opc.CatTitle)-2) AS CatTitle,
	opc.Culture AS Culture

FROM dbo.[Option] opt
LEFT JOIN dbo.[OptionContent] opc ON opc.OptionID=opt.[ID]

GROUP BY opt.Cat,  opt.RightIndent,  opc.CatTitle,  RIGHT(opc.CatTitle,LEN(opc.CatTitle)-2),  opc.Culture
ORDER BY opc.CatTitle















GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  SELECT  ON [dbo].[View_OptionCat]  TO [public]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  View dbo.View_UserGroups    Script Date: 2009-7-10 19:03:37 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[View_UserGroups]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[View_UserGroups]
GO

--===============================================================================
--== UserGroup  UserGroups һ
--===============================================================================
--==Ȩ 2001-2009 CMS CMS- http://www.zhuzhicms.com
--==޸ 2009-04-12
--===============================================================================

CREATE VIEW dbo.View_UserGroups
AS

--ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ

--====================================================
--==
--====================================================
SELECT

	ugs.[ID] AS [ID],
	ug.[ID] AS UserGroupID,
	ug.[GroupName] AS [GroupName],
	ugs.[User] AS [User]

FROM dbo.UserGroups ugs
INNER JOIN dbo.UserGroup ug ON ug.[ID]=ugs.UserGroupID



GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  SELECT  ON [dbo].[View_UserGroups]  TO [public]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  View dbo.View_KnowledgePermission    Script Date: 2009-7-10 19:03:37 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[View_KnowledgePermission]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[View_KnowledgePermission]
GO

--===============================================================================
--== MainDirectory, SubDirectory, PageDirectory  DedicatedTo  CommentsPermission ǿӵ Knowledge   DedicatedTo  CommentsPermission 
--== MenuMainDirectorySubDirectoryPageDirectory еԱ DedicatedTo õķȨޣϵͳ֪ЩĿ¼ṹ
--===============================================================================
--==Ȩ 2001-2009 CMS CMS- http://www.zhuzhicms.com
--==޸ 2009-04-12
--===============================================================================

CREATE VIEW dbo.View_KnowledgePermission

AS

--ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ

--====================================================
--==
--==ע⣺ʹ RIGHT JOIN ΪչЩûݣKnowledge md, sd,pd ʹ LEFT JOIN ûݣKnowledge md, sd, pd Ͷʧ
--====================================================
SELECT
 
	md.[ID] AS MainDirectoryID,
	sd.[ID] AS SubDirectoryID,
	pd.[ID] AS PageDirectoryID,
	now.[ID] AS KnowledgeID,

	COALESCE(CONVERT(NVARCHAR(4000),md.DedicatedTo) + N',',N'') + 
	COALESCE(CONVERT(NVARCHAR(4000),sd.DedicatedTo) + N',',N'') + 
	COALESCE(CONVERT(NVARCHAR(4000),pd.DedicatedTo) + N',',N'') + 
	CONVERT(NVARCHAR(4000),ISNULL(now.DedicatedTo,N'')) AS DedicatedTo, 

	COALESCE(CONVERT(NVARCHAR(4000),md.CommentsPermission) + N',',N'') + 
	COALESCE(CONVERT(NVARCHAR(4000),sd.CommentsPermission) + N',',N'') + 
	COALESCE(CONVERT(NVARCHAR(4000),pd.CommentsPermission) + N',',N'') + 
	CONVERT(NVARCHAR(4000),ISNULL(now.CommentsPermission,N'')) AS CommentsPermission

FROM dbo.Knowledge now

RIGHT JOIN dbo.PageDirectory pd on pd.[ID]=now.PageDirectoryID
RIGHT JOIN dbo.SubDirectory sd on sd.[ID]=pd.SubDirectoryID
RIGHT JOIN dbo.MainDirectory md on md.[ID]=sd.MainDirectoryID

--ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ





GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  SELECT  ON [dbo].[View_KnowledgePermission]  TO [public]
GO

