USE [shop2]
GO
/****** 对象:  StoredProcedure [dbo].[Nop_SalesBestSellersReport]    脚本日期: 07/01/2012 13:12:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[Nop_SalesBestSellersReport]
(
    @LastDays int = 360,
    @RecordsToReturn int = 10,
    @OrderBy int = 1,
    @CategoryWhere varchar(100)
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @cmd varchar(500)
    
    CREATE TABLE #tmp (
        ID int not null identity,
        ProductID int,
        SalesTotalCount int,
        SalesTotalAmount MONEY,
        CollectCount int,
        [Count] int)
    INSERT #tmp (
        ProductID,
        SalesTotalCount,
        SalesTotalAmount,
        CollectCount,
        [Count] )
        
SELECT
    pv.ProductID,
    SUM(ISNULL(SalesTotalCount,0)) AS  SalesTotalCount,
    SUM(ISNULL(SalesTotalAmount,0)) AS  SalesTotalAmount,
    SUM(ISNULL(CollectCount,0)) AS   CollectCount,
    pp.[Count]
FROM [Nop_ProductVariant] pv WITH (NOLOCK) 
LEFT JOIN (
    SELECT opv.ProductVariantId, 
        SUM(opv.Quantity) AS SalesTotalCount, 
        SUM(opv.PriceExclTax) AS SalesTotalAmount
    FROM [Nop_OrderProductVariant] opv
    WHERE  EXISTS (SELECT  1 FROM [Nop_Order] o 
        WHERE  opv.OrderID = o.OrderID
        AND o.CreatedOn >= DATEADD(dy, -@LastDays, GETDATE())
        AND o.Deleted=0)
    GROUP BY  opv.ProductVariantID
) s ON  pv.ProductVariantID=s.ProductVariantID
LEFT JOIN (
    SELECT ProductVariantID,
        COUNT(ShoppingCartItemID) AS  CollectCount
    FROM dbo.Nop_ShoppingCartItem 
    WHERE  ShoppingCartTypeID=2
    GROUP  BY ProductVariantID
) AS  sc ON  pv.ProductVariantID = sc.ProductVariantID
LEFT JOIN dbo.Nop_Product AS pp ON pv.ProductID=pp.ProductId
WHERE  pv.Published = 1 AND pv.Deleted = 0
GROUP BY  pv.ProductID,pp.[Count]
ORDER BY CASE @OrderBy WHEN 1 THEN SalesTotalAmount ELSE SalesTotalAmount END DESC

    SET @cmd = 'SELECT TOP ' + CONVERT(varchar(10), @RecordsToReturn ) + ' * FROM #tmp WHERE 1=1 '

    EXEC (@cmd)

    DROP TABLE #tmp
END

SELECT
    pv.ProductID,
    SUM(ISNULL(SalesTotalCount,0)) AS  SalesTotalCount,
    SUM(ISNULL(SalesTotalAmount,0)) AS  SalesTotalAmount,
    SUM(ISNULL(CollectCount,0)) AS   CollectCount,
    pp.[Count]
FROM [Nop_ProductVariant] pv WITH (NOLOCK) 
LEFT JOIN (
    SELECT opv.ProductVariantId, 
        SUM(opv.Quantity) AS SalesTotalCount, 
        SUM(opv.PriceExclTax) AS SalesTotalAmount
    FROM [Nop_OrderProductVariant] opv
    WHERE  EXISTS (SELECT  1 FROM [Nop_Order] o 
        WHERE  opv.OrderID = o.OrderID
        AND o.CreatedOn >= DATEADD(dy, -@LastDays, GETDATE())
        AND o.Deleted=0)
    GROUP BY  opv.ProductVariantID
) s ON  pv.ProductVariantID=s.ProductVariantID
LEFT JOIN (
    SELECT ProductVariantID,
        COUNT(ShoppingCartItemID) AS  CollectCount
    FROM dbo.Nop_ShoppingCartItem 
    WHERE  ShoppingCartTypeID=2
    GROUP  BY ProductVariantID
) AS  sc ON  pv.ProductVariantID = sc.ProductVariantID
LEFT JOIN dbo.Nop_Product AS pp ON pv.ProductID=pp.ProductId
WHERE  pv.Published = 1 AND pv.Deleted = 0
GROUP BY  pv.ProductID,pp.[Count]

USE [shop2]
GO
/****** 对象:  StoredProcedure [dbo].[Nop_SalesBestSellersReport]    脚本日期: 07/01/2012 13:12:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO