NopCommerce ProductLoadAllPaged

/****** Object:  StoredProcedure [dbo].[ProductLoadAllPaged]    Script Date: 16/11/2012 10:34:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ProductLoadAllPaged]
(
    @CategoryIds        nvarchar(300) = null,   --a list of category IDs (comma-separated list). e.g. 1,2,3
    @ManufacturerId     int = 0,
    @ProductTagId       int = 0,
    @FeaturedProducts   bit = null, --0 featured only , 1 not featured only, null - load all products
    @PriceMin           decimal(18, 4) = null,
    @PriceMax           decimal(18, 4) = null,
    @Keywords           nvarchar(MAX) = null,
    @SearchDescriptions bit = 0,
    @FilteredSpecs      nvarchar(300) = null,   --filter by attributes (comma-separated list). e.g. 14,15,16
    @LanguageId         int = 0,
    @OrderBy            int = 5, --0 position, 5 - Name: A to Z, 6 - Name: Z to A, 10 - Price: Low to High, 11 - Price: High to Low, 15 - creation date
    @PageIndex          int = 0,
    @PageSize           int = 2147483644,
    @ShowHidden         bit = 0,
    @LoadFilterableSpecificationAttributeOptionIds bit = 0, --a value indicating whether we should load the specification attribute option identifiers applied to loaded products (all pages)
    @FilterableSpecificationAttributeOptionIds nvarchar(100) = null OUTPUT, --the specification attribute option identifiers applied to loaded products (all pages). returned as a comma separated list of identifiers
    @TotalRecords       int = null OUTPUT
)
AS
BEGIN

    /* Products that filtered by keywords */
    CREATE TABLE #KeywordProducts
    (
        [ProductId] int NOT NULL
    )

    DECLARE
        @SearchKeywords bit,
        @sql nvarchar(max),
        @sql_orderby nvarchar(max)

    SET NOCOUNT ON

    --filter by keywords
    IF ISNULL(@Keywords, '') != ''
    BEGIN
        SET @SearchKeywords = 1

        SET @Keywords = isnull(@Keywords, '')
        SET @Keywords = '%' + rtrim(ltrim(@Keywords)) + '%'


        DECLARE @FTS_Keywords nvarchar(400)
        SET @FTS_Keywords = @Keywords
        SET @FTS_Keywords = '"' +  LTRIM(RTRIM(@FTS_Keywords)) + '*"'
        -- This method cleans multiple spaces between words
        SET @FTS_Keywords = REPLACE(@FTS_Keywords, '  ', ' þ')
        SET @FTS_Keywords = REPLACE(@FTS_Keywords, 'þ ', '')
        SET @FTS_Keywords = REPLACE(@FTS_Keywords, 'þ', '')
        --SET SEARCH WILDCARDS
        SET @FTS_Keywords = REPLACE(@FTS_Keywords, ' ', '*" AND "')

        SET @sql = '
        INSERT INTO #KeywordProducts ([ProductId])
        SELECT p.Id
        FROM Product p with (NOLOCK)
        WHERE CONTAINS(p.name, @FTS_Keywords)
        UNION
        SELECT pv.ProductId
        FROM ProductVariant pv with (NOLOCK)
        WHERE PATINDEX(@Keywords, pv.name) > 0
        UNION
        SELECT pv.ProductId
        FROM ProductVariant pv with (NOLOCK)
        WHERE PATINDEX(@Keywords, pv.sku) > 0
        UNION
        SELECT lp.EntityId
        FROM LocalizedProperty lp with (NOLOCK)
        WHERE
            lp.LocaleKeyGroup = N''Product''
            AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
            AND lp.LocaleKey = N''Name''
            AND PATINDEX(@Keywords, lp.LocaleValue) > 0'

        IF @SearchDescriptions = 1 SET @sql = @sql + '
        UNION
        SELECT p.Id
        FROM Product p with (NOLOCK)
        WHERE PATINDEX(@Keywords, p.ShortDescription) > 0
        UNION
        SELECT p.Id
        FROM Product p with (NOLOCK)
        WHERE PATINDEX(@Keywords, p.FullDescription) > 0
        UNION
        SELECT pv.ProductId
        FROM ProductVariant pv with (NOLOCK)
        WHERE PATINDEX(@Keywords, pv.Description) > 0
        UNION
        SELECT lp.EntityId
        FROM LocalizedProperty lp with (NOLOCK)
        WHERE
            lp.LocaleKeyGroup = N''Product''
            AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
            AND lp.LocaleKey = N''ShortDescription''
            AND PATINDEX(@Keywords, lp.LocaleValue) > 0
        UNION
        SELECT lp.EntityId
        FROM LocalizedProperty lp with (NOLOCK)
        WHERE
            lp.LocaleKeyGroup = N''Product''
            AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
            AND lp.LocaleKey = N''FullDescription''
            AND PATINDEX(@Keywords, lp.LocaleValue) > 0'

        --PRINT (@sql)
        EXEC sp_executesql @sql, N'@Keywords nvarchar(MAX), @FTS_Keywords nvarchar(400)',
             @Keywords = @Keywords,
             @FTS_Keywords = @FTS_Keywords

    END
    ELSE
    BEGIN
        SET @SearchKeywords = 0
    END

    --filter by category IDs
    SET @CategoryIds = isnull(@CategoryIds, '')
    CREATE TABLE #FilteredCategoryIds
    (
        CategoryId int not null
    )
    INSERT INTO #FilteredCategoryIds (CategoryId)
    SELECT CAST(data as int) FROM dbo.[nop_splitstring_to_table](@CategoryIds, ',')
    DECLARE @CategoryIdsCount int
    SET @CategoryIdsCount = (SELECT COUNT(1) FROM #FilteredCategoryIds)

    --filter by attributes
    SET @FilteredSpecs = isnull(@FilteredSpecs, '')
    CREATE TABLE #FilteredSpecs
    (
        SpecificationAttributeOptionId int not null
    )
    INSERT INTO #FilteredSpecs (SpecificationAttributeOptionId)
    SELECT CAST(data as int) FROM dbo.[nop_splitstring_to_table](@FilteredSpecs, ',')
    DECLARE @SpecAttributesCount int
    SET @SpecAttributesCount = (SELECT COUNT(1) FROM #FilteredSpecs)

    --paging
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
    DECLARE @RowsToReturn int
    SET @RowsToReturn = @PageSize * (@PageIndex + 1)
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageLowerBound + @PageSize + 1

    CREATE TABLE #DisplayOrderTmp
    (
        [Id] int IDENTITY (1, 1) NOT NULL,
        [ProductId] int NOT NULL
    )

    SET @sql = '
    INSERT INTO #DisplayOrderTmp ([ProductId])
    SELECT p.Id
    FROM
        Product p with (NOLOCK)'

    IF @CategoryIdsCount > 0
    BEGIN
        SET @sql = @sql + '
        LEFT JOIN Product_Category_Mapping pcm with (NOLOCK)
            ON p.Id = pcm.ProductId'
    END

    IF @ManufacturerId > 0
    BEGIN
        SET @sql = @sql + '
        LEFT JOIN Product_Manufacturer_Mapping pmm with (NOLOCK)
            ON p.Id = pmm.ProductId'
    END

    IF ISNULL(@ProductTagId, 0) != 0
    BEGIN
        SET @sql = @sql + '
        LEFT JOIN Product_ProductTag_Mapping pptm with (NOLOCK)
            ON p.Id = pptm.Product_Id'
    END

    IF @ShowHidden = 0
    OR @PriceMin > 0
    OR @PriceMax > 0
    OR @OrderBy = 10 /* Price: Low to High */
    OR @OrderBy = 11 /* Price: High to Low */
    BEGIN
        SET @sql = @sql + '
        LEFT JOIN ProductVariant pv with (NOLOCK)
            ON p.Id = pv.ProductId'
    END

    --searching by keywords
    IF @SearchKeywords = 1
    BEGIN
        SET @sql = @sql + '
        JOIN #KeywordProducts kp
            ON  p.Id = kp.ProductId'
    END

    SET @sql = @sql + '
    WHERE
        p.Deleted = 0'

    --filter by category
    IF @CategoryIdsCount > 0
    BEGIN
        SET @sql = @sql + '
        AND pcm.CategoryId IN (SELECT CategoryId FROM #FilteredCategoryIds)'

        IF @FeaturedProducts IS NOT NULL
        BEGIN
            SET @sql = @sql + '
        AND pcm.IsFeaturedProduct = ' + CAST(@FeaturedProducts AS nvarchar(max))
        END
    END

    --filter by manufacturer
    IF @ManufacturerId > 0
    BEGIN
        SET @sql = @sql + '
        AND pmm.ManufacturerId = ' + CAST(@ManufacturerId AS nvarchar(max))

        IF @FeaturedProducts IS NOT NULL
        BEGIN
            SET @sql = @sql + '
        AND pmm.IsFeaturedProduct = ' + CAST(@FeaturedProducts AS nvarchar(max))
        END
    END

    --filter by product tag
    IF ISNULL(@ProductTagId, 0) != 0
    BEGIN
        SET @sql = @sql + '
        AND pptm.ProductTag_Id = ' + CAST(@ProductTagId AS nvarchar(max))
    END

    IF @ShowHidden = 0
    BEGIN
        SET @sql = @sql + '
        AND p.Published = 1
        AND pv.Published = 1
        AND pv.Deleted = 0
        AND (getutcdate() BETWEEN ISNULL(pv.AvailableStartDateTimeUtc, ''1/1/1900'') and ISNULL(pv.AvailableEndDateTimeUtc, ''1/1/2999''))'
    END

    --min price
    IF @PriceMin > 0
    BEGIN
        SET @sql = @sql + '
        AND (
                (
                    --special price (specified price and valid date range)
                    (pv.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(pv.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(pv.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
                    AND
                    (pv.SpecialPrice >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
                )
                OR
                (
                    --regular price (price isnt specified or date range isnt valid)
                    (pv.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(pv.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(pv.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
                    AND
                    (pv.Price >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
                )
            )'
    END

    --max price
    IF @PriceMax > 0
    BEGIN
        SET @sql = @sql + '
        AND (
                (
                    --special price (specified price and valid date range)
                    (pv.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(pv.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(pv.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
                    AND
                    (pv.SpecialPrice <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
                )
                OR
                (
                    --regular price (price isnt specified or date range isnt valid)
                    (pv.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(pv.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(pv.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
                    AND
                    (pv.Price <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
                )
            )'
    END

    --filter by specs
    IF @SpecAttributesCount > 0
    BEGIN
        SET @sql = @sql + '
        AND NOT EXISTS (
            SELECT 1
            FROM
                #FilteredSpecs [fs]
            WHERE
                [fs].SpecificationAttributeOptionId NOT IN (
                    SELECT psam.SpecificationAttributeOptionId
                    FROM dbo.Product_SpecificationAttribute_Mapping psam
                    WHERE psam.AllowFiltering = 1 AND psam.ProductId = p.Id
                )
            )'
    END

    --sorting
    SET @sql_orderby = ''
    IF @OrderBy = 5 /* Name: A to Z */
        SET @sql_orderby = ' p.[Name] ASC'
    ELSE IF @OrderBy = 6 /* Name: Z to A */
        SET @sql_orderby = ' p.[Name] DESC'
    ELSE IF @OrderBy = 10 /* Price: Low to High */
        SET @sql_orderby = ' pv.[Price] ASC'
    ELSE IF @OrderBy = 11 /* Price: High to Low */
        SET @sql_orderby = ' pv.[Price] DESC'
    ELSE IF @OrderBy = 15 /* creation date */
        SET @sql_orderby = ' p.[CreatedOnUtc] DESC'
    ELSE /* default sorting, 0 (position) */
    BEGIN
        --category position (display order)
        IF @CategoryIdsCount > 0 SET @sql_orderby = ' pcm.DisplayOrder ASC'

        --manufacturer position (display order)
        IF @ManufacturerId > 0
        BEGIN
            IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', '
            SET @sql_orderby = @sql_orderby + ' pmm.DisplayOrder ASC'
        END

        --name
        IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', '
        SET @sql_orderby = @sql_orderby + ' p.[Name] ASC'
    END

    SET @sql = @sql + '
    ORDER BY' + @sql_orderby

    --PRINT (@sql)
    EXEC sp_executesql @sql

    DROP TABLE #FilteredCategoryIds
    DROP TABLE #FilteredSpecs

    CREATE TABLE #PageIndex
    (
        [IndexId] int IDENTITY (1, 1) NOT NULL,
        [ProductId] int NOT NULL
    )
    INSERT INTO #PageIndex ([ProductId])
    SELECT ProductId
    FROM #DisplayOrderTmp
    GROUP BY ProductId
    ORDER BY min([Id])

    --total records
    SET @TotalRecords = @@rowcount

    DROP TABLE #DisplayOrderTmp

    --prepare filterable specification attribute option identifier (if requested)
    IF @LoadFilterableSpecificationAttributeOptionIds = 1
    BEGIN
        CREATE TABLE #FilterableSpecs
        (
            [SpecificationAttributeOptionId] int NOT NULL
        )
        INSERT INTO #FilterableSpecs ([SpecificationAttributeOptionId])
        SELECT DISTINCT [psam].SpecificationAttributeOptionId
        FROM [Product_SpecificationAttribute_Mapping] [psam]
        WHERE [psam].[AllowFiltering] = 1
        AND [psam].[ProductId] IN (SELECT [pi].ProductId FROM #PageIndex [pi])

        --build comma separated list of filterable identifiers
        SELECT @FilterableSpecificationAttributeOptionIds = COALESCE(@FilterableSpecificationAttributeOptionIds + ',' , '') + CAST(SpecificationAttributeOptionId as nvarchar(1000))
        FROM #FilterableSpecs

        DROP TABLE #FilterableSpecs
    END

    --return products
    SELECT TOP (@RowsToReturn)
        p.*
    FROM
        #PageIndex [pi]
        INNER JOIN Product p on p.Id = [pi].[ProductId]
    WHERE
        [pi].IndexId > @PageLowerBound AND
        [pi].IndexId < @PageUpperBound
    ORDER BY
        [pi].IndexId

    DROP TABLE #PageIndex
END

go