Let's say product 1 through 100 belongs to portal A, and product 101 through 200 belongs to portal B, and portal A and B are both hosted under the same DNN installation.
Products 1 through 100 of portal A all has sort order "0" assigned. Products of 101 through 200 of portal B all has sort order "10" assigned.
Let's say that "manage products" page list fist 25 products by default.
Smith_productByFilter stored procedure grabs all the products from the database (both portal A and portal B) sorted by sort order value assigned then grab the first 25 products to be displayed.
Portal A has no issue since their products has lower sort order number...so their products are shown at the beginning of the product data queries.
Portal B, on the other hand, will have the problem. because Smith_productByFilter will query products of both portal A and portal B then grab the first 25 products which consists of products from portal A. THEN it tries to find if there is any products from Portal B...which is none....so the "manage products" list NONE of the product in the page.
Our solution to this issue was to modify the stored procedure so that it
query the list of the products belongs to portal B by filtering the portalID first THEN grab the first 25 products from the queried data.
I'm inserting our modified procedure below. Please compare with yours. I bolded the area we modified:
ALTER PROCEDURE [dbo].[Smith_productByFilter]
@Page int,
@RecsPerPage int,
@sortfield varchar(50),
@sortorder varchar(10),
@filter varchar(50),
@searchtext varchar(50) ,
@tabModuleId int,
@PortalId int
as
CREATE TABLE #TempItems
(
ProductAutoID int IDENTITY,
ProductID int
)
DECLARE @SearchSQL varchar(5000)
if(@Filter='All')
Begin
SELECT @SearchSQL ='INSERT INTO #TempItems (ProductID) select P.ProductID from Smith_Products P left outer join ' +
'Smith_Category C on C.CategoryID=P.CategoryID'+
' where ( p.TabModuleId = ' + CAST ( @tabModuleId AS VARCHAR(10)) + ' OR p.Share = 1 ) and p.PortalID = ' + cast(@PortalId as varchar(100)) + ' and P.LogicallyDeleted !=''1'''
if( @sortfield='CategoryName')
SELECT @SearchSQL=@SearchSQL+' '+' order by C.' +@sortfield+' '+ @sortorder
else
SELECT @SearchSQL=@SearchSQL+' '+' order by P.'+@sortfield+' '+ @sortorder
End
else if(@Filter='CategoryName')
Begin
SELECT @SearchSQL ='INSERT INTO #TempItems (ProductID) select P.ProductID from Smith_Products P inner join ' +
' Smith_CategoryProduct CP on CP.ProductID=P.ProductID inner join Smith_Category C on C.CategoryID=CP.CategoryID'+
' where ( p.TabModuleId = ' + CAST ( @tabModuleId AS VARCHAR(10)) + ' OR p.Share = 1 ) and P.LogicallyDeleted !=''1'' and p.PortalID = ' + cast(@PortalId as varchar(100)) + ' and ( C.'+@filter+' Like ''%' + @searchtext + '%'')'
if( @sortfield='CategoryName')
SELECT @SearchSQL=@SearchSQL+' '+' order by C.' +@sortfield+' '+ @sortorder
else
SELECT @SearchSQL=@SearchSQL+' '+' order by P.'+@sortfield+' '+ @sortorder
End
else
Begin
SELECT @SearchSQL ='INSERT INTO #TempItems (ProductID) select p.ProductID from Smith_Products P ' +
' where ( p.TabModuleId = ' + CAST ( @tabModuleId AS VARCHAR(10)) + ' OR p.Share = 1 ) and p.PortalID = ' + cast(@PortalId as varchar(100)) + ' and P.LogicallyDeleted !=''1'' and ( P.'+@filter+' Like ''%' + @searchtext + '%'')'
if( @sortfield='CategoryName')
SELECT @SearchSQL=@SearchSQL+' '+' order by C.' +@sortfield+' '+ @sortorder
else
SELECT @SearchSQL=@SearchSQL+' '+' order by P.'+@sortfield+' '+ @sortorder
End
EXECUTE(@SearchSQL)
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
SELECT p.*,c.categoryname, TotalRecords=(
SELECT COUNT(*)
FROM #TempItems TI),
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.ProductAutoID >= @LastRec
)
FROM Smith_Products P left outer join
Smith_Category C on C.CategoryID=P.CategoryID RIGHT OUTER JOIN
#TempItems T ON P.ProductID = T.ProductID
WHERE ProductAutoID > @FirstRec AND ProductAutoID < @LastRec and p.PortalID = @PortalId
--Order By P.SortOrder , P.ModelName