The default stored procedure displays pages in the order in which they were created. I needed to control the sort order myself, so I modified the stored procedure into a new stored procedure. I excluded the object qualifiers for this example. This stored proc name should be entered into the custom procedure setting of the control. Pages will then be sorted in the order you specify for your site.
CREATE Procedure dbo.GetMyNavigation
(
@ModuleID int,
@TabId int,
@UserId int
)
as
declare @displaytype varchar(2000)
declare @expansiontype varchar(2000)
declare @itemlefttextclass varchar(50)
declare @itemrighttextclass varchar(50)
declare @grouplefttextclass varchar(50)
declare @grouprighttextclass varchar(50)
declare @groupleftimageclass varchar(50)
declare @grouprightimageclass varchar(50)
declare @itemleftimageclass varchar(50)
declare @itemrightimageclass varchar(50)
declare @groupclass varchar(50)
declare @groupclasshover varchar(50)
declare @itemclass varchar(50)
declare @itemclasshover varchar(50)
select @displaytype = SettingValue from dbo.ModuleSettings where SettingName = 'displaytype' and ModuleID = @ModuleID
select @expansiontype = SettingValue from dbo.ModuleSettings where SettingName = 'expansiontype' and ModuleID = @ModuleID
declare @ParentTabID int
declare @ExpandAll bit
declare @ExpandCurrent bit
declare @ExpandAuto bit
declare @ExpandNone bit
create table #Expand(TabID int)
create table #Tabs(TabID int)
if lower(@expansiontype) = 'all'
select @ExpandAll = 1
else
if lower(@expansiontype) = 'current'
select @ExpandCurrent = 1
else
if lower(@expansiontype) = 'none'
select @ExpandNone = 1
else
if lower(@expansiontype) <> 'auto' and not len(lower(@expansiontype)) = 0
begin
Select @expansiontype = replace(replace(replace(@expansiontype,',','.'),';','.'),' ','')
Insert into #Expand(TabID) Select distinct cast(Value as int) from dbo.Split(0,@expansiontype,'.')
end
else
if lower(@expansiontype) = 'auto'
Select @ExpandAuto = 1
if lower(@displaytype) = 'child'
Select @ParentTabID = @TabId
else
if lower(@displaytype) = 'parent'
begin
Select @ParentTabID = ParentId from dbo.Tabs where TabID = @TabID
end
else
if lower(@displaytype) = 'root'
begin
Select @ParentTabID = null
end
else
begin
Select @ParentTabID = -1
Select @displaytype = replace(replace(replace(@displaytype,',','.'),';','.'),' ','')
Insert into #Tabs(TabID) Select distinct cast(Value as int) from dbo.Split(0,@displaytype,'.')
end
declare @PortalID int
Select @PortalID = PortalID from dbo.Tabs where tabid = @TabId
CREATE TABLE #Navigation(
ItemID int,
Expanded bit,
[Group] int,
LeftImage varchar(200) null,
LeftImageClass varchar(50) null,
LeftText varchar(200) null,
LeftTextClass varchar(50) null,
RightTextClass varchar(50) null,
RightText varchar(200) null,
RightImage varchar(200) null,
RightImageClass varchar(50) null,
ItemClass varchar(50) null,
ItemClassOver varchar(50) null,
Enabled bit,
ItemLink int null,
IsCurrent bit)
INSERT INTO #Navigation
(ItemID,
Expanded,
[Group],
LeftImage,
LeftImageClass,
LeftText,
LeftTextClass,
RightTextClass,
RightText,
RightImage,
RightImageClass,
ItemClass,
ItemClassOver,
Enabled,
ItemLink,
isCurrent)
select
ItemID,
Expanded,
[Group],
LeftImage,
LeftImageClass,
replace(LeftText,'|','') LeftText,
LeftTextClass,
RightTextClass,
RightText,
RightImage,
RightImageClass,
ItemClass,
ItemClassOver,
Enabled,
ItemLink,
isCurrent
from
(
select
TabId as ParentTabId,
-1000000 + isnull(TabOrder,0) as OrderId,
TabId as ItemID,
CASE
WHEN @ExpandAll=1 THEN 1
WHEN @ExpandNone=1 THEN 0
WHEN @ExpandAuto=1 and (TabID = @TabID or TabID IN (Select ParentID from dbo.Tabs where TabID = @TabID)) THEN 1
WHEN @ExpandCurrent=1 and TabID = @TabID THEN 1
WHEN TabID in (Select TabID from #Expand) THEN 1 ELSE 0 END as Expanded,
1 as [Group],
case
when IconFile is null or len(IconFile) = 0 then
''
else
case when PortalID is null or TabOrder > 10000 then
'![]()
'
else
'![]()
'
end
end LeftImage,
@groupleftimageclass as LeftImageClass,
TabName as LeftText,
Title as RightText,
@grouplefttextclass as LeftTextClass,
@grouprighttextclass as RightTextClass,
'' as RightImage,
@grouprightimageclass as RightImageClass,
@groupclass as ItemClass,
@groupclasshover as ItemClassOver,
1 as Enabled,
(CASE WHEN
(Select count(x.TabID) from dbo.Tabs x where x.ParentID=Tabs.TabId) = 0 THEN TabID ELSE
CASE WHEN NOT @ExpandAuto=1 THEN
null
WHEN @ExpandAuto=1 AND NOT (TabID = @TabID or TabID IN (Select ParentID from dbo.Tabs where TabID = @TabID)) THEN
TabID
ELSE
null
END
END) as ItemLink,
isCurrent
from
(
select TabID,
TabOrder,
Tabs.PortalID,
TabName,
IsVisible,
ParentId,
[Level],
'IconFile' = case when Files_1.FileName is null then Tabs.IconFile else Files_1.Folder + Files_1.FileName end,
DisableLink,
Title,
Description,
KeyWords,
IsDeleted,
SkinSrc,
ContainerSrc,
TabPath,
StartDate,
EndDate,
'URL' = case when Files.FileName is null then Tabs.URL else Files.Folder + Files.FileName end,
'HasChildren' = case when exists (select 1 from dbo.Tabs T2 where T2.ParentId = Tabs.TabId) then 'true' else 'false' end,
'isCurrent' = case when TabId = @TabId THEN 1 ELSE 0 End
from dbo.Tabs Tabs
left outer join dbo.Files Files on Tabs.URL = 'fileid=' + convert(varchar,Files.FileID)
left outer join dbo.Files Files_1 ON Tabs.IconFile = 'fileid=' + convert(varchar,Files_1.FileID)
)
Tabs
where ((ParentId= @ParentTabID or (ParentId is null and @ParentTabID is null)) and IsVisible = 1 and IsDeleted = 0 and (PortalID = @PortalID or PortalID is null) or TabID in (select TabID from #Tabs))
and (
TabID in (select distinct t.TabID from
dbo.Tabs t join
dbo.TabPermission p on p.TabID = t.TabID and p.AllowAccess=1 join
dbo.UserRoles r on (r.RoleID = p.RoleID and r.UserId = @UserId) OR p.RoleId in (-1,-3))
or @UserId in (Select UserId from dbo.Users where isSuperUser = 1)
)
UNION
select
ParentId as ParentTabId,
isnull(TabOrder,0) as OrderId,
TabId as ItemID,
0 as Expanded,
0 as [Group],
case
when IconFile is null or len(IconFile) = 0 then
''
else
case when PortalID is null or TabOrder > 10000 then
'![]()
'
else
'![]()
'
end
end LeftImage,
@itemleftimageclass as LeftImageClass,
TabName as LeftText,
Title as RightText,
@itemlefttextclass as LeftTextClass,
@itemrighttextclass as RightTextClass,
case
when IconFile is null or len(IconFile) = 0 then
''
else
case when PortalID is null or TabOrder > 10000 then
'![]()
'
else
'![]()
'
end
end RightImage,
@itemrightimageclass as RightImageClass,
@itemclass as ItemClass,
@itemclasshover as ItemClassOver,
(abs(DisableLink - 1)) as Enabled,
TabId as ItemLink,
isCurrent
from (
select TabID,
TabOrder,
Tabs.PortalID,
TabName,
IsVisible,
ParentId,
[Level],
'IconFile' = case when Files_1.FileName is null then Tabs.IconFile else Files_1.Folder + Files_1.FileName end,
DisableLink,
Title,
Description,
KeyWords,
IsDeleted,
SkinSrc,
ContainerSrc,
TabPath,
StartDate,
EndDate,
'URL' = case when Files.FileName is null then Tabs.URL else Files.Folder + Files.FileName end,
'HasChildren' = case when exists (select 1 from dbo.Tabs T2 where T2.ParentId = Tabs.TabId) then 'true' else 'false' end,
'isCurrent' = case when TabId = @TabId THEN 1 ELSE 0 End
from dbo.Tabs Tabs
left outer join dbo.Files Files on Tabs.URL = 'fileid=' + convert(varchar,Files.FileID)
left outer join dbo.Files Files_1 ON Tabs.IconFile = 'fileid=' + convert(varchar,Files_1.FileID)
) Tabs
where (ParentId in (Select TabId from dbo.Tabs Tabs where (ParentId= @ParentTabID or (ParentId is null and @ParentTabID is null)) and IsVisible = 1 and IsDeleted = 0) and IsVisible = 1 and IsDeleted = 0 and
(PortalID = @PortalID or PortalID is null) or ParentId in (select TabID from #Tabs))
and (
TabID in (select distinct t.TabID from
dbo.Tabs t join
dbo.TabPermission p on p.TabID = t.TabID and p.AllowAccess=1 join
dbo.UserRoles r on (r.RoleID = p.RoleID and r.UserId = @UserId) OR p.RoleId in (-1,-3))
or @UserId in (Select UserId from dbo.Users where isSuperUser = 1)
)
) X
Drop table #Expand
Drop table #Tabs
SELECT
ItemID,
Expanded,
[Group],
LeftImage,
LeftImageClass,
replace(LeftText,'|','') LeftText,
LeftTextClass,
RightTextClass,
RightText,
RightImage,
RightImageClass,
ItemClass,
ItemClassOver,
Enabled,
ItemLink,
isCurrent
FROM
#Navigation
INNER JOIN
dbo.Tabs ON #Navigation.ItemID = dbo.Tabs.TabID
WHERE
dbo.Tabs.IsDeleted = 0
AND
(dbo.Tabs.StartDate <= GETDATE()
OR
dbo.Tabs.StartDate IS NULL)
AND
(dbo.Tabs.EndDate >= GETDATE()
OR
dbo.Tabs.EndDate IS Null)
ORDER BY
TabOrder,
TabName
DROP TABLE #Navigation