/****** オブジェクト: View [dbo].[vMenu] スクリプト日付: 10/17/2008 20:12:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
// ObjectName : vMenu
// Description : メニュー
// Author Info : 2008-08-08 by inno
// Modified Info :
// Usage, EX) : vMenu
Select * From dbo.Menu
Select * From dbo.vMenu
order by MenuOrder
SELECT * FROM dbo.CodeTypeInfo
Return : GroupID, MenuID, ParentID, MenuName, OrderView, MenuType, Depth
*/
ALTER VIEW [dbo].[vMenu]
AS
WITH TempMenu (Navi, aa2, bb2, MenuNavi, a2, b2, MenuOrder, GroupID, MenuID, ParentID, MenuName, MaxOrderView, OrderView, MenuType, Depth, Status)
AS
(
SELECT '00#00#00#00', A.MenuName, A.MenuName, convert(nvarchar(250), A.MenuName), 0,0,'00#00#00', 0, A.MenuID, A.ParentID, A.MenuName, A.OrderView, A.OrderView, A.MenuType, -1, Status from dbo.Menu A WHERE A.ParentID = -1 --AND A.Status = 'O'
UNION ALL
SELECT
CASE WHEN B.Depth >= 2 THEN convert(varchar(2),B.GroupID)+'#' ELSE '' END
+CASE WHEN B.Depth >= 1 THEN convert(varchar(2),B.ParentID)+'#' ELSE '' END
+CASE WHEN B.Depth >= 0 THEN convert(varchar(2),B.MenuID)+'#' ELSE '' END
+convert(varchar(2),A.MenuID)
,CASE WHEN B.Depth >= -1 THEN A.MenuName ELSE convert(nvarchar(50),'') END aa2
,CASE WHEN B.Depth >= -1 THEN B.MenuName ELSE convert(nvarchar(50),'') END bb2
--,CASE WHEN B.Depth >= -1 THEN B.MenuName ELSE 0 END bb2
,convert(nvarchar(250),
N'ホーム > '
+CASE WHEN B.Depth = -1 THEN convert(nvarchar(50),A.MenuName)
WHEN B.Depth = 0 THEN convert(nvarchar(50),B.aa2)+' > '
WHEN B.Depth = 1 THEN convert(nvarchar(50),B.bb2)+' > '
ELSE '' END
+CASE WHEN B.Depth = 0 THEN convert(nvarchar(50),A.MenuName)
WHEN B.Depth = 1 THEN convert(nvarchar(50),B.aa2)+' > '
ELSE '' END
+CASE WHEN B.Depth = 1 THEN convert(nvarchar(50),A.MenuName)
ELSE '' END
)
,
CASE WHEN B.Depth >= -1 THEN A.MenuOrderView ELSE 0 END a2
,CASE WHEN B.Depth >= -1 THEN B.MenuOrderView ELSE 0 END b2
,
CASE WHEN B.Depth = -1 THEN convert(varchar(2),A.MenuOrderView)
WHEN B.Depth = 0 THEN convert(varchar(2),B.a2)+'#'
WHEN B.Depth = 1 THEN convert(varchar(2),B.b2)+'#'
ELSE '' END
+CASE WHEN B.Depth = 0 THEN convert(varchar(2),A.MenuOrderView)
WHEN B.Depth = 1 THEN convert(varchar(2),B.a2)+'#'
ELSE '' END
+CASE WHEN B.Depth = 1 THEN convert(varchar(2),A.MenuOrderView)
ELSE '' END
, CASE WHEN B.Depth+1 < 2 THEN B.MenuID ELSE B.GroupID END GroupID, A.MenuID, A.ParentID, A.MenuName, CASE WHEN A.OrderView < B.MaxOrderView THEN B.MaxOrderView ELSE A.OrderView END MaxOrderView, A.OrderView ,A.MenuType, B.Depth+1 Depth, CASE WHEN B.Status = 'X' THEN B.Status ELSE A.Status END Status FROM
(
SELECT MenuID, MenuName, ParentID, OrderView, Replicate('0',case when len(OrderView) > 1 then 0 else 1 end ) + convert(varchar(2),OrderView) MenuOrderView, MenuType, Status FROM dbo.Menu
) A
JOIN
(
SELECT Navi, aa2, bb2, MenuNavi, Replicate('0',case when len(a2) > 1 then 0 else 1 end ) + convert(varchar(2),a2) a2, Replicate('0',case when len(b2) > 1 then 0 else 1 end ) + convert(varchar(2),b2) b2, MenuOrder, GroupID, MenuID, ParentID, MenuName, MaxOrderView, OrderView, Replicate('0',case when len(OrderView) > 1 then 0 else 1 end ) + convert(varchar(2),OrderView) MenuOrderView, MenuType, Depth, Status FROM TempMenu
) B ON A.ParentID = B.MenuID WHERE A.Status = 'O'
)
SELECT
A.*
, CASE WHEN A.MenuType in (2001,2002) THEN '/View/View.aspx?MenuID='+convert(varchar(10),A.MenuID1)
WHEN A.MenuType = 2003 THEN C.Url
WHEN A.MenuType = 2004 THEN '/Board/List.aspx?MenuID='+convert(varchar(10),A.MenuID1)
ELSE '/View/View.aspx?MenuID='+convert(varchar(10),A.MenuID1)
END MenuUrl
FROM
(
SELECT nullif(parsename(Temp,4)*1,0) Depth1
,nullif(parsename(Temp,3)*1,0) Depth2
,nullif(parsename(Temp,2)*1,0) Depth3
,nullif(parsename(Temp,1)*1,0) Depth4
, Navi, MenuOrder
, A.MenuNavi
, B.GroupID MenuGroup
, A.GroupID, A.RealGroupID
, A.MenuID
, CASE WHEN D.MenuID is null THEN A.MenuID ELSE D.MenuID END MenuID1, A.ParentID
, A.MenuName , MaxOrderView, A.OrderView
, CASE WHEN D.MenuType is null THEN A.MenuType ELSE D.MenuType END MenuType, Depth, A.Status
FROM
(
SELECT replace(Navi+replicate('#',3-(len(Navi)-len(replace(Navi,'#','')))),'#','.0') Temp, Navi, MenuNavi, MenuOrder, case when GroupID = 0 then MenuID ELSE GroupID END GroupID, GroupID RealGroupID, MenuID, ParentID, MenuName, MaxOrderView, OrderView, MenuType, Depth, Status FROM TempMenu
) A LEFT OUTER JOIN dbo.MenuGroup B
ON A.GroupID = B.MenuID
LEFT OUTER JOIN
(
SELECT A.ParentID, A.MenuID, B.MenuName, B.MenuType FROM
(
SELECT ParentID, min(MenuID) MenuID FROM dbo.Menu A GROUP BY ParentID, A.Status having A.Status = 'O'
) A JOIN dbo.Menu B
ON A.MenuID = B.MenuID and A.ParentID > 0
) D
ON A.MenuID = D.ParentID
) A
LEFT OUTER JOIN dbo.SiteLinkUrl C
ON A.MenuID1 = C.MenuID
