جست و جوی تولیدکنندگان

+  
+  --show hidden
+  IF @ShowHidden = 0
+  BEGIN
+    SET @sql = @sql + '
+    AND p.Deleted = 0
+    AND (getutcdate() BETWEEN ISNULL(p.AvailableStartDateTimeUtc, ''1/1/1900'') and ISNULL(p.AvailableEndDateTimeUtc, ''1/1/2999''))'
+  END
+  
+  --min price
+  IF @PriceMin is not null
+  BEGIN
+    SET @sql = @sql + '
+    AND (
+        (
+          --special price (specified price and valid date range)
+          (p.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
+          AND
+          (p.SpecialPrice >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
+        )
+        OR 
+        (
+          --regular price (price isnt specified or date range isnt valid)
+          (p.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
+          AND
+          (p.Price >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
+        )
+      )'
+  END
+  
+  --max price
+  IF @PriceMax is not null
+  BEGIN
+    SET @sql = @sql + '
+    AND (
+        (
+          --special price (specified price and valid date range)
+          (p.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
+          AND
+          (p.SpecialPrice <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
+        )
+        OR 
+        (
+          --regular price (price isnt specified or date range isnt valid)
+          (p.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
+          AND
+          (p.Price <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
+        )
+      )'
+  END
+  
+  --show hidden and ACL
+  IF @ShowHidden = 0
+  BEGIN
+    SET @sql = @sql + '
+    AND (p.SubjectToAcl = 0 OR EXISTS (
+      SELECT 1 FROM #FilteredCustomerRoleIds [fcr]
+      WHERE
+        [fcr].CustomerRoleId IN (
+          SELECT [acl].CustomerRoleId
+          FROM [AclRecord] acl with (NOLOCK)
+          WHERE [acl].EntityId = p.Id AND [acl].EntityName = ''Product''
+        )
+      ))'
+  END
+  
+  --show hidden and filter by store
+  IF @StoreId > 0
+  BEGIN
+    SET @sql = @sql + '
+    AND (p.LimitedToStores = 0 OR EXISTS (
+      SELECT 1 FROM [StoreMapping] sm with (NOLOCK)
+      WHERE [sm].EntityId = p.Id AND [sm].EntityName = ''Product'' and [sm].StoreId=' + CAST(@StoreId AS nvarchar(max)) + '
+      ))'
+  END
+  
+  --filter by specification attribution options
+  SET @FilteredSpecs = isnull(@FilteredSpecs, '')  
+  CREATE TABLE #FilteredSpecs
+  (
+    SpecificationAttributeOptionId int not null
+  )
+  INSERT INTO #FilteredSpecs (SpecificationAttributeOptionId)
+  SELECT CAST(data as int) FROM [nop_splitstring_to_table](@FilteredSpecs, ',')
+  DECLARE @SpecAttributesCount int  
+  SET @SpecAttributesCount = (SELECT COUNT(1) FROM #FilteredSpecs)
+  IF @SpecAttributesCount > 0
+  BEGIN
+    --do it for each specified specification option
+    DECLARE @SpecificationAttributeOptionId int
+    DECLARE cur_SpecificationAttributeOption CURSOR FOR
+    SELECT [SpecificationAttributeOptionId]
+    FROM [#FilteredSpecs]
+    OPEN cur_SpecificationAttributeOption
+    FETCH NEXT FROM cur_SpecificationAttributeOption INTO @SpecificationAttributeOptionId
+    WHILE @@FETCH_STATUS = 0
+    BEGIN
+      SET @sql = @sql + '
+      AND p.Id in (select psam.ProductId from [Product_SpecificationAttribute_Mapping] psam with (NOLOCK) where psam.AllowFiltering = 1 and psam.SpecificationAttributeOptionId = ' + CAST(@SpecificationAttributeOptionId AS nvarchar(max)) + ')'
+      --fetch next identifier
+      FETCH NEXT FROM cur_SpecificationAttributeOption INTO @SpecificationAttributeOptionId
+    END
+    CLOSE cur_SpecificationAttributeOption
+    DEALLOCATE cur_SpecificationAttributeOption
+  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 = ' p.[Price] ASC'
+  ELSE IF @OrderBy = 11 /* Price: High to Low */
+    SET @sql_orderby = ' p.[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
+  DROP TABLE #FilteredCustomerRoleIds
+  DROP TABLE #KeywordProducts
+
+  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] with (NOLOCK)
+    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(4000))
+    FROM #FilterableSpecs
+
+    DROP TABLE #FilterableSpecs
+   END
+
+  --return products
+  SELECT TOP (@RowsToReturn)
+    p.*
+  FROM
+    #PageIndex [pi]
+    INNER JOIN Product p with (NOLOCK) on p.Id = [pi].[ProductId]
+  WHERE
+    [pi].IndexId > @PageLowerBound AND 
+    [pi].IndexId < @PageUpperBound
+  ORDER BY
+    [pi].IndexId
+  
+  DROP TABLE #PageIndex
+END