11月 12日

use nopCommerce


--select categoryid,count(categoryid) from dbo.Nop_Product_Category_Mapping group by categoryid

select * from 


SELECT pcm.ProductID, pcm.DisplayOrder 
            FROM [Nop_Product_Category_Mapping] pcm WHERE pcm.CategoryID = 30

SELECT p.ProductID, 1 
            FROM [Nop_Product] p
            ORDER BY p.[Name]


SELECT DISTINCT
        p.ProductID
    FROM Nop_Product p with (NOLOCK) 
    LEFT OUTER JOIN Nop_Product_Category_Mapping pcm with (NOLOCK) ON p.ProductID=pcm.ProductID
    LEFT OUTER JOIN Nop_Product_Manufacturer_Mapping pmm with (NOLOCK) ON p.ProductID=pmm.ProductID
    LEFT OUTER JOIN Nop_ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID     
    WHERE  
         
                30 IS NULL OR 30=0
                OR (pcm.CategoryID=30 
         
        
         

        AND    (
                patindex(@Keywords, isnull(p.name, '')) > 0
                or patindex(@Keywords, isnull(pv.name, '')) > 0
                or patindex(@Keywords, isnull(pv.sku , '')) > 0
                or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(p.ShortDescription, '')) > 0)
                or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(p.FullDescription, '')) > 0)
                or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(pv.Description, '')) > 0)
            )
        )
    ORDER BY do.DisplayOrder




select * from dbo.Nop_Product_Category_Mapping
select * from dbo.Nop_Product








USE [nopCommerce]
GO
/****** 对象:  StoredProcedure [dbo].[Nop_ProductLoadAllPaged]    脚本日期: 11/12/2009 22:36:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


alter PROCEDURE [dbo].[Nop_ProductLoadAllPagedTEST]
(
    @CategoryID            int,
    @ManufacturerID        int,
    @FeaturedProducts    bit = null,    --0 featured only , 1 not featured only, null - load all products
    @PriceMin            money = null,
    @PriceMax            money = null,
    @Keywords            nvarchar(MAX),    
    @SearchDescriptions bit = 0,
    @ShowHidden            bit,
    @PageIndex            int = 0, 
    @PageSize            int = 2147483644,
    @TotalRecords        int = null OUTPUT
)
AS
BEGIN
    
    SET @Keywords = isnull(@Keywords, '')
    SET @Keywords = '%' + rtrim(ltrim(@Keywords)) + '%'

    SET @PriceMin = isnull(@PriceMin, 0)
    SET @PriceMax = isnull(@PriceMax, 2147483644)

    --display order
    CREATE TABLE #DisplayOrder
    (
        ProductID int not null PRIMARY KEY,
        DisplayOrder int not null
    )    

    IF @CategoryID IS NOT NULL AND @CategoryID > 0
        BEGIN
            INSERT #DisplayOrder 
            SELECT distinct pcm.ProductID, pcm.DisplayOrder 
            FROM [Nop_Product_Category_Mapping] pcm WHERE pcm.CategoryID in (50,51,31,33,32)
        END
    ELSE IF @ManufacturerID IS NOT NULL AND @ManufacturerID > 0
        BEGIN
            INSERT #DisplayOrder 
            SELECT pmm.ProductID, pmm.Displayorder 
            FROM [Nop_Product_Manufacturer_Mapping] pmm WHERE pmm.ManufacturerID = @ManufacturerID
        END
    ELSE
        BEGIN
            INSERT #DisplayOrder 
            SELECT p.ProductID, 1 
            FROM [Nop_Product] p
            ORDER BY p.[Name]
        END


    --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 #PageIndex 
    (
        IndexID int IDENTITY (1, 1) NOT NULL,
        ProductID int NOT NULL,
        DisplayOrder int NOT NULL,
    )

    INSERT INTO #PageIndex (ProductID, DisplayOrder)
    SELECT DISTINCT
        p.ProductID, do.DisplayOrder
    FROM Nop_Product p with (NOLOCK) 
    LEFT OUTER JOIN Nop_Product_Category_Mapping pcm with (NOLOCK) ON p.ProductID=pcm.ProductID
    LEFT OUTER JOIN Nop_Product_Manufacturer_Mapping pmm with (NOLOCK) ON p.ProductID=pmm.ProductID
    LEFT OUTER JOIN Nop_ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID        
    JOIN #DisplayOrder do on p.ProductID = do.ProductID 
    WHERE 
        (
            (
                p.Published = 1 OR @ShowHidden = 1
            )
        AND 
            (
                pv.Published = 1 or @ShowHidden = 1
            )
        AND 
            (
                p.Deleted=0
            )
        AND (
                @CategoryID IS NULL OR @CategoryID=0
                OR (pcm.CategoryID  in (50,51,31,33,32) AND (@FeaturedProducts IS NULL OR pcm.IsFeaturedProduct=@FeaturedProducts))
            )
        AND (
                @ManufacturerID IS NULL OR @ManufacturerID=0
                OR (pmm.ManufacturerID=@ManufacturerID AND (@FeaturedProducts IS NULL OR pmm.IsFeaturedProduct=@FeaturedProducts))
            )
        AND (
                pv.Price BETWEEN @PriceMin AND @PriceMax
            )
        AND    (
                patindex(@Keywords, isnull(p.name, '')) > 0
                or patindex(@Keywords, isnull(pv.name, '')) > 0
                or patindex(@Keywords, isnull(pv.sku , '')) > 0
                or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(p.ShortDescription, '')) > 0)
                or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(p.FullDescription, '')) > 0)
                or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(pv.Description, '')) > 0)
            )
        )
    ORDER BY do.DisplayOrder

    SET @TotalRecords = @@rowcount    
    SET ROWCOUNT @RowsToReturn
    
    SELECT  
        p.*
    FROM
        #PageIndex [pi]
        INNER JOIN Nop_Product p on p.ProductID = [pi].ProductID
    WHERE
        [pi].IndexID > @PageLowerBound AND 
        [pi].IndexID < @PageUpperBound
    ORDER BY
        IndexID
    
    SET ROWCOUNT 0

    DROP TABLE #PageIndex
END







    @CategoryID            int,
    @ManufacturerID        int,
    @FeaturedProducts    bit = null,    --0 featured only , 1 not featured only, null - load all products
    @PriceMin            money = null,
    @PriceMax            money = null,
    @Keywords            nvarchar(MAX),    
    @SearchDescriptions bit = 0,
    @ShowHidden            bit,
    @PageIndex            int = 0, 
    @PageSize            int = 2147483644,
    @TotalRecords        int = null OUTPUT


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

    SET @PriceMin = isnull(@PriceMin, 0)
    SET @PriceMax = isnull(@PriceMax, 2147483644)

    --display order
    CREATE TABLE #DisplayOrder
    (
        ProductID int not null PRIMARY KEY,
        DisplayOrder int not null
    )    

    IF @CategoryID IS NOT NULL AND @CategoryID > 0
        BEGIN
            INSERT #DisplayOrder 
            SELECT pcm.ProductID, pcm.DisplayOrder 
            FROM [Nop_Product_Category_Mapping] pcm WHERE pcm.CategoryID = @CategoryID
        END
    ELSE IF @ManufacturerID IS NOT NULL AND @ManufacturerID > 0
        BEGIN
            INSERT #DisplayOrder 
            SELECT pmm.ProductID, pmm.Displayorder 
            FROM [Nop_Product_Manufacturer_Mapping] pmm WHERE pmm.ManufacturerID = @ManufacturerID
        END
    ELSE
        BEGIN
            INSERT #DisplayOrder 
            SELECT p.ProductID, 1 
            FROM [Nop_Product] p
            ORDER BY p.[Name]
        END


    --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 #PageIndex 
    (
        IndexID int IDENTITY (1, 1) NOT NULL,
        ProductID int NOT NULL,
        DisplayOrder int NOT NULL,
    )

    INSERT INTO #PageIndex (ProductID, DisplayOrder)
    SELECT DISTINCT
        p.ProductID, do.DisplayOrder
    FROM Nop_Product p with (NOLOCK) 
    LEFT OUTER JOIN Nop_Product_Category_Mapping pcm with (NOLOCK) ON p.ProductID=pcm.ProductID
    LEFT OUTER JOIN Nop_Product_Manufacturer_Mapping pmm with (NOLOCK) ON p.ProductID=pmm.ProductID
    LEFT OUTER JOIN Nop_ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID        
    JOIN #DisplayOrder do on p.ProductID = do.ProductID 
    WHERE 
        (
            (
                p.Published = 1 OR @ShowHidden = 1
            )
        AND 
            (
                pv.Published = 1 or @ShowHidden = 1
            )
        AND 
            (
                p.Deleted=0
            )
        AND (
                @CategoryID IS NULL OR @CategoryID=0
                OR (pcm.CategoryID=@CategoryID AND (@FeaturedProducts IS NULL OR pcm.IsFeaturedProduct=@FeaturedProducts))
            )
        AND (
                @ManufacturerID IS NULL OR @ManufacturerID=0
                OR (pmm.ManufacturerID=@ManufacturerID AND (@FeaturedProducts IS NULL OR pmm.IsFeaturedProduct=@FeaturedProducts))
            )
        AND (
                pv.Price BETWEEN @PriceMin AND @PriceMax
            )
        AND    (
                patindex(@Keywords, isnull(p.name, '')) > 0
                or patindex(@Keywords, isnull(pv.name, '')) > 0
                or patindex(@Keywords, isnull(pv.sku , '')) > 0
                or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(p.ShortDescription, '')) > 0)
                or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(p.FullDescription, '')) > 0)
                or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(pv.Description, '')) > 0)
            )
        )
    ORDER BY do.DisplayOrder

    SET @TotalRecords = @@rowcount    
    SET ROWCOUNT @RowsToReturn
    
    SELECT  
        p.*
    FROM
        #PageIndex [pi]
        INNER JOIN Nop_Product p on p.ProductID = [pi].ProductID
    WHERE
        [pi].IndexID > @PageLowerBound AND 
        [pi].IndexID < @PageUpperBound
    ORDER BY
        IndexID
    
    SET ROWCOUNT 0

    DROP TABLE #PageIndex
END