Photo albums not being populated in the correct order in the parent albums dropdown list when adding a photo.
DnnForge_SimpleGallery_AlbumListAll stored procured for version 2.03.04 is
Create PROCEDURE DnnForge_SimpleGallery_AlbumListAll
@ModuleID int,
@ParentAlbumID int,
@ShowPublicOnly bit,
@ShowChildren bit
as
IF( @ShowChildren = 0 )
BEGIN
SELECT
Album.[AlbumID],
Album.[ModuleID],
Album.[ParentAlbumID],
Album.[Caption],
Album.[Description],
Album.[IsPublic],
Album.[HomeDirectory],
Album.[Password],
Album.[AlbumOrder],
(select count(*) from DnnForge_SimpleGallery_Photo where AlbumID = Album.AlbumID and IsApproved = 1) as 'NumberOfPhotos',
(select count(*) from DnnForge_SimpleGallery_Album where ParentAlbumID = Album.AlbumID and IsPublic = 1) as 'NumberOfAlbums',
(select count(*) from DnnForge_SimpleGallery_Album a, DnnForge_SimpleGallery_Photo p where a.ParentAlbumID = Album.AlbumID and a.AlbumID = p.AlbumID and a.IsPublic = 1 and p.IsApproved = 1) as 'NumberOfAlbumPhotos'
FROM
DnnForge_SimpleGallery_Album Album
WHERE
Album.[ModuleID] = @ModuleID
AND
Album.[ParentAlbumID] = @ParentAlbumID
AND
([Album].[IsPublic] = @ShowPublicOnly OR [Album].[IsPublic] = 1)
ORDER BY
Album.[AlbumOrder], Album.[Caption]
END
ELSE
BEGIN
SET NOCOUNT ON
DECLARE @level int, @line int, @current int
CREATE TABLE #hierarchy(AlbumID int, level int)
CREATE TABLE #stack (item int, level int)
INSERT INTO #stack VALUES (@ParentAlbumID, 1)
SELECT @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
SELECT @current = item
FROM #stack
WHERE level = @level
IF( @ParentAlbumID = -1 OR @ParentAlbumID != @current )
BEGIN
insert into #hierarchy(AlbumID, level) values(@current, @level)
END
DELETE FROM #stack
WHERE level = @level
AND item = @current
INSERT #stack
SELECT AlbumID, @level + 1
FROM DnnForge_SimpleGallery_Album
WHERE parentAlbumID = @current and ModuleID = @ModuleID
ORDER BY AlbumOrder desc, Caption desc
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END -- WHILE
SELECT
Album.[AlbumID],
Album.[ModuleID],
Album.[ParentAlbumID],
Album.[Caption],
Album.[Description],
Album.[IsPublic],
Album.[HomeDirectory],
Album.[Password],
Album.[AlbumOrder],
(Select count(*) from DnnForge_SimpleGallery_Photo Photo where Photo.AlbumID = Album.AlbumID) as 'NumberOfPhotos',
(select count(*) from DnnForge_SimpleGallery_Album where ParentAlbumID = Album.AlbumID and IsPublic = 1) as 'NumberOfAlbums',
(select count(*) from DnnForge_SimpleGallery_Album a, DnnForge_SimpleGallery_Photo p where a.ParentAlbumID = Album.AlbumID and a.AlbumID = p.AlbumID and a.IsPublic = 1 and p.IsApproved = 1) as 'NumberOfAlbumPhotos',
REPLICATE('.',(level-2)*2) + Album.[Caption] as 'CaptionIndented'
FROM
DnnForge_SimpleGallery_Album Album, #hierarchy
WHERE
#hierarchy.AlbumID = Album.AlbumID
AND
Album.[ModuleID] = @ModuleID
AND
([Album].[IsPublic] = @ShowPublicOnly OR [Album].[IsPublic] = 1)
drop table #hierarchy
drop table #stack
END
If I change the table’s #stack and #hierarchy to be temporary tables when defined the list populates correctly. I don’t know how to explain this correctly but instead of
CREATE TABLE #hierarchy(AlbumID int, level int)
CREATE TABLE #stack (item int, level int)
change to:
DECLARE @hierarchy TABLE(AlbumID int, level int)
DECLARE @stack TABLE(item int, level int)
Here is the updated SP that fixes issue:
CREATE PROCEDURE [DnnForge_SimpleGallery_AlbumListAll]
@ModuleID int,
@ParentAlbumID int,
@ShowPublicOnly bit,
@ShowChildren bit
as
IF( @ShowChildren = 0 )
BEGIN
SELECT
Album.[AlbumID],
Album.[ModuleID],
Album.[ParentAlbumID],
Album.[Caption],
Album.[Description],
Album.[IsPublic],
Album.[HomeDirectory],
Album.[Password],
Album.[AlbumOrder],
(select count(*) from dbo.DnnForge_SimpleGallery_Photo where AlbumID = Album.AlbumID and IsApproved = 1) as 'NumberOfPhotos',
(select count(*) from dbo.DnnForge_SimpleGallery_Album where ParentAlbumID = Album.AlbumID and IsPublic = 1) as 'NumberOfAlbums',
(select count(*) from dbo.DnnForge_SimpleGallery_Album a, dbo.DnnForge_SimpleGallery_Photo p where a.ParentAlbumID = Album.AlbumID and a.AlbumID = p.AlbumID and a.IsPublic = 1 and p.IsApproved = 1) as 'NumberOfAlbumPhotos'
FROM
dbo.DnnForge_SimpleGallery_Album Album
WHERE
Album.[ModuleID] = @ModuleID
AND
Album.[ParentAlbumID] = @ParentAlbumID
AND
([Album].[IsPublic] = @ShowPublicOnly OR [Album].[IsPublic] = 1)
ORDER BY
Album.[AlbumOrder], Album.[Caption]
END
ELSE
BEGIN
SET NOCOUNT ON
DECLARE @level int, @line int, @current int
DECLARE @hierarchy TABLE(AlbumID int, level int)
DECLARE @stack TABLE(item int, level int)
INSERT INTO @stack VALUES (@ParentAlbumID, 1)
SELECT @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM @stack WHERE level = @level)
BEGIN
SELECT @current = item
FROM @stack
WHERE level = @level
IF( @ParentAlbumID = -1 OR @ParentAlbumID != @current )
BEGIN
insert into @hierarchy(AlbumID, level) values(@current, @level)
END
DELETE FROM @stack
WHERE level = @level
AND item = @current
INSERT @stack
SELECT AlbumID, @level + 1
FROM DnnForge_SimpleGallery_Album
WHERE parentAlbumID = @current and ModuleID = @ModuleID
ORDER BY AlbumOrder desc, Caption desc
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END -- WHILE
SELECT
Album.[AlbumID],
Album.[ModuleID],
Album.[ParentAlbumID],
Album.[Caption],
Album.[Description],
Album.[IsPublic],
Album.[HomeDirectory],
Album.[Password],
Album.[AlbumOrder],
(Select count(*) from dbo.DnnForge_SimpleGallery_Photo Photo where Photo.AlbumID = Album.AlbumID) as 'NumberOfPhotos',
(select count(*) from dbo.DnnForge_SimpleGallery_Album where ParentAlbumID = Album.AlbumID and IsPublic = 1) as 'NumberOfAlbums',
(select count(*) from dbo.DnnForge_SimpleGallery_Album a, dbo.DnnForge_SimpleGallery_Photo p where a.ParentAlbumID = Album.AlbumID and a.AlbumID = p.AlbumID and a.IsPublic = 1 and p.IsApproved = 1) as 'NumberOfAlbumPhotos',
REPLICATE('.',(level-2)*2) + Album.[Caption] as 'CaptionIndented'
FROM
dbo.DnnForge_SimpleGallery_Album Album, @hierarchy h
WHERE
h.AlbumID = Album.AlbumID
AND
Album.[ModuleID] = @ModuleID
AND
([Album].[IsPublic] = @ShowPublicOnly OR [Album].[IsPublic] = 1)
--drop table @hierarchy
--drop table @stack
END
Additional information I found that was weird, if I executed the sp in SQL studio if pulled the data incorrectly but if I took it out of the sp and just wrote in a select it pulled the data correctly. |