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

سلام دوستان گرامی
من قصد دارم توی سایتم امکانی وجود داشته باشه که بشه تولید کنندگان رو هم جست و جو کرد. اما متاسفانه ناپ کامرس همچین قابلیتی نداره و من نمیخوام اسم تولیدکننده ها رو توی قسمت تگ ها یا همون برچسب ها ذکر کنم.
کسی میتونه کمک کنه؟

البته باید بگم که خودم یه قطعه کد نوشتم و روند زیر رو انجام دادم:
- dbo.nopCommerce > Stored Procedures > dbo.ProductLoadAllPaged رو اصلاح کردم
- بعد از اتمام اصلاح، از کوئری خروجی گرفتم تا روندهای ذخیره شده رو آپدیت کنم
- کش سایت رو پاک کردم و اپلیکیشن رو مجددا راه اندازی کردم

تغییرات dbo.ProductLoadAllPaged.sql:

- افزودن متغیر '@SearchManufacturer' (bit - default = 0)
- ست کردن '@SearchManufacturer = 1'
-افزودن کد زیر :

IF @SearchManufacturer = 1 
BEGIN 
    SET @sql = @sql + ' 
    UNION 
    SELECT pmm.ProductId 
    FROM Manufacturer m with (NOLOCK) 
    JOIN Product_Manufacturer_Mapping pmm with (NOLOCK) 
        ON pmm.ManufacturerId = m.Id 
    WHERE ' 
    IF @UseFullTextSearch = 1 
        --Toggle the below lines if you index these tables (Full-Text) 
        --SET @sql = @sql + 'CONTAINS(m.[Name], @Keywords) ' 
        SET @sql = @sql + 'PATINDEX(@Keywords, m.[Name]) > 0 ' 
    ELSE 
        SET @sql = @sql + 'PATINDEX(@Keywords, m.[Name]) > 0 ' 
END 

این کوئری وقتی جدا اجرا میشه ، ProductId درست رو برمیگردونه اما توی سایت که قرار میدمش، درست کار نمیکنه. نمیدونم چه مشکلی داره.
اگر کسی بتونه بگه ایراد کارم کجاست، واقعا ممنون میشم

راستش دوست ندارم به پلاگین کسی تکیه کنم. ترجیح میدم با تغییر و اصلاح کد به نتیجه ی مورد نظرم برسم

با سلام
میتوانید از کد زیر استفاده نمایید:

1) روند یا همان Procedure را اصلاح کنید:

@SearchManufactures = 1
    BEGIN
      --product short description
      SET @sql = @sql + '
      UNION
      SELECT pmm.ProductId 
      FROM Manufacturer manufacture with (NOLOCK) 
      Inner JOIN Product_Manufacturer_Mapping pmm with (NOLOCK) 
      ON pmm.ManufacturerId = manufacture.Id 
      WHERE ' 
      IF @UseFullTextSearch = 1
        SET @sql = @sql + 'CONTAINS(manufacture.[Name], @Keywords) '
      ELSE
        SET @sql = @sql + 'PATINDEX(@Keywords, manufacture.[Name]) > 0 '
END

2) متد _productSearvice SearchProducts را اصلاح نمایید. باید همه ی پارامترهای SearchManufactures  را درست همانند کاری که در بخش اول کردید، منتقل کنید. مثلا @SearchDescriptions .

dbo.ProductLoadAllPaged.sql

+USE [nopCommerce]
+GO
+SET ANSI_NULLS ON
+GO
+SET QUOTED_IDENTIFIER ON
+GO
+
+
+ALTER PROCEDURE [dbo].[ProductLoadAllPaged]
+(
+  @CategoryIds    nvarchar(MAX) = null,  --a list of category IDs (comma-separated list). e.g. 1,2,3
+  @ManufacturerId    int = 0,
+  @StoreId      int = 0,
+  @VendorId      int = 0,
+  @WarehouseId    int = 0,
+  @ProductTypeId    int = null, --product type identifier, null - load all products
+  @VisibleIndividuallyOnly bit = 0,   --0 - load all products , 1 - "visible indivially" only
+  @MarkedAsNewOnly  bit = 0,   --0 - load all products , 1 - "marked as new" only
+  @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(4000) = null,
+  @SearchDescriptions bit = 0, --a value indicating whether to search by a specified "keyword" in product descriptions
+  @SearchSku      bit = 0, --a value indicating whether to search by a specified "keyword" in product SKU
+  @SearchProductTags  bit = 0, --a value indicating whether to search by a specified "keyword" in product tags
+  @UseFullTextSearch  bit = 0,
+  @FullTextMode    int = 0, --0 - using CONTAINS with <prefix_term>, 5 - using CONTAINS and OR with <prefix_term>, 10 - using CONTAINS and AND with <prefix_term>
+  @FilteredSpecs    nvarchar(MAX) = null,  --filter by specification attribute options (comma-separated list of IDs). e.g. 14,15,16
+  @LanguageId      int = 0,
+  @OrderBy      int = 0, --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
+  @AllowedCustomerRoleIds  nvarchar(MAX) = null,  --a list of customer role IDs (comma-separated list) for which a product should be shown (if a subjet to ACL)
+  @PageIndex      int = 0, 
+  @PageSize      int = 2147483644,
+  @ShowHidden      bit = 0,
+  @OverridePublished  bit = null, --null - process "Published" property according to "showHidden" parameter, true - load only "Published" products, false - load only "Unpublished" products
+  @LoadFilterableSpecificationAttributeOptionIds bit = 0, --a value indicating whether we should load the specification attribute option identifiers applied to loaded products (all pages)
+  @FilterableSpecificationAttributeOptionIds nvarchar(MAX) = 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,
+  @SearchManufacturer  bit = 0 --a value indicating whether to search by a specified "keyword" in product SKU
+)
+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
+  SET @Keywords = isnull(@Keywords, '')
+  SET @Keywords = rtrim(ltrim(@Keywords))
+  IF ISNULL(@Keywords, '') != ''
+  BEGIN
+    SET @SearchKeywords = 1
+    SET @SearchSku = 1
+    SET @SearchManufacturer = 1
+    
+    IF @UseFullTextSearch = 1
+    BEGIN
+      --remove wrong chars (' ")
+      SET @Keywords = REPLACE(@Keywords, '''', '')
+      SET @Keywords = REPLACE(@Keywords, '"', '')
+      
+      --full-text search
+      IF @FullTextMode = 0 
+      BEGIN
+        --0 - using CONTAINS with <prefix_term>
+        SET @Keywords = ' "' + @Keywords + '*" '
+      END
+      ELSE
+      BEGIN
+        --5 - using CONTAINS and OR with <prefix_term>
+        --10 - using CONTAINS and AND with <prefix_term>
+
+        --clean multiple spaces
+        WHILE CHARINDEX('  ', @Keywords) > 0 
+          SET @Keywords = REPLACE(@Keywords, '  ', ' ')
+
+        DECLARE @concat_term nvarchar(100)        
+        IF @FullTextMode = 5 --5 - using CONTAINS and OR with <prefix_term>
+        BEGIN
+          SET @concat_term = 'OR'
+        END 
+        IF @FullTextMode = 10 --10 - using CONTAINS and AND with <prefix_term>
+        BEGIN
+          SET @concat_term = 'AND'
+        END
+
+        --now let's build search string
+        declare @fulltext_keywords nvarchar(4000)
+        set @fulltext_keywords = N''
+        declare @index int    
+    
+        set @index = CHARINDEX(' ', @Keywords, 0)
+
+        -- if index = 0, then only one field was passed
+        IF(@index = 0)
+          set @fulltext_keywords = ' "' + @Keywords + '*" '
+        ELSE
+        BEGIN    
+          DECLARE @first BIT
+          SET  @first = 1      
+          WHILE @index > 0
+          BEGIN
+            IF (@first = 0)
+              SET @fulltext_keywords = @fulltext_keywords + ' ' + @concat_term + ' '
+            ELSE
+              SET @first = 0
+
+            SET @fulltext_keywords = @fulltext_keywords + '"' + SUBSTRING(@Keywords, 1, @index - 1) + '*"'          
+            SET @Keywords = SUBSTRING(@Keywords, @index + 1, LEN(@Keywords) - @index)            
+            SET @index = CHARINDEX(' ', @Keywords, 0)
+          end
+          
+          -- add the last field
+          IF LEN(@fulltext_keywords) > 0
+            SET @fulltext_keywords = @fulltext_keywords + ' ' + @concat_term + ' ' + '"' + SUBSTRING(@Keywords, 1, LEN(@Keywords)) + '*"'  
+        END
+        SET @Keywords = @fulltext_keywords
+      END
+    END

+    ELSE
+    BEGIN
+      --usual search by PATINDEX
+      SET @Keywords = '%' + @Keywords + '%'
+    END
+    --PRINT @Keywords
+
+    --product name
+    SET @sql = '
+    INSERT INTO #KeywordProducts ([ProductId])
+    SELECT p.Id
+    FROM Product p with (NOLOCK)
+    WHERE '
+    IF @UseFullTextSearch = 1
+      SET @sql = @sql + 'CONTAINS(p.[Name], @Keywords) '
+    ELSE
+      SET @sql = @sql + 'PATINDEX(@Keywords, p.[Name]) > 0 '
+
+
+    --localized product name
+    SET @sql = @sql + '
+    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'''
+    IF @UseFullTextSearch = 1
+      SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
+    ELSE
+      SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
+  
+
+    IF @SearchDescriptions = 1
+    BEGIN
+      --product short description
+      SET @sql = @sql + '
+      UNION
+      SELECT p.Id
+      FROM Product p with (NOLOCK)
+      WHERE '
+      IF @UseFullTextSearch = 1
+        SET @sql = @sql + 'CONTAINS(p.[ShortDescription], @Keywords) '
+      ELSE
+        SET @sql = @sql + 'PATINDEX(@Keywords, p.[ShortDescription]) > 0 '
+
+
+      --product full description
+      SET @sql = @sql + '
+      UNION
+      SELECT p.Id
+      FROM Product p with (NOLOCK)
+      WHERE '
+      IF @UseFullTextSearch = 1
+        SET @sql = @sql + 'CONTAINS(p.[FullDescription], @Keywords) '
+      ELSE
+        SET @sql = @sql + 'PATINDEX(@Keywords, p.[FullDescription]) > 0 '
+
+
+
+      --localized product short description
+      SET @sql = @sql + '
+      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'''
+      IF @UseFullTextSearch = 1
+        SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
+      ELSE
+        SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
+        
+
+      --localized product full description
+      SET @sql = @sql + '
+      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'''
+      IF @UseFullTextSearch = 1
+        SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
+      ELSE
+        SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
+    END

+
+    --SKU
+    IF @SearchSku = 1
+    BEGIN
+      SET @sql = @sql + '
+      UNION
+      SELECT p.Id
+      FROM Product p with (NOLOCK)
+      WHERE '
+      IF @UseFullTextSearch = 1
+        SET @sql = @sql + 'CONTAINS(p.[Sku], @Keywords) '
+      ELSE
+        SET @sql = @sql + 'PATINDEX(@Keywords, p.[Sku]) > 0 '
+    END
+
+    IF @SearchProductTags = 1
+    BEGIN
+      --product tag
+      SET @sql = @sql + '
+      UNION
+      SELECT pptm.Product_Id
+      FROM Product_ProductTag_Mapping pptm with(NOLOCK) INNER JOIN ProductTag pt with(NOLOCK) ON pt.Id = pptm.ProductTag_Id
+      WHERE '
+      IF @UseFullTextSearch = 1
+        SET @sql = @sql + 'CONTAINS(pt.[Name], @Keywords) '
+      ELSE
+        SET @sql = @sql + 'PATINDEX(@Keywords, pt.[Name]) > 0 '
+
+      --localized product tag
+      SET @sql = @sql + '
+      UNION
+      SELECT pptm.Product_Id
+      FROM LocalizedProperty lp with (NOLOCK) INNER JOIN Product_ProductTag_Mapping pptm with(NOLOCK) ON lp.EntityId = pptm.ProductTag_Id
+      WHERE
+        lp.LocaleKeyGroup = N''ProductTag''
+        AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
+        AND lp.LocaleKey = N''Name'''
+      IF @UseFullTextSearch = 1
+        SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
+      ELSE
+        SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
+    END

+
+    --Manufacturer
+    IF @SearchManufacturer = 1
+    BEGIN
+      SET @sql = @sql + '
+      UNION
+      SELECT pmm.ProductId
+      FROM Manufacturer m with (NOLOCK)
+      JOIN Product_Manufacturer_Mapping pmm with (NOLOCK)
+        ON pmm.ManufacturerId = m.Id
+      WHERE '
+      IF @UseFullTextSearch = 1
+        --Toggle the below lines if you index these tables (Full-Text)
+        --SET @sql = @sql + 'CONTAINS(m.[Name], @Keywords) '
+        SET @sql = @sql + 'PATINDEX(@Keywords, m.[Name]) > 0 '
+      ELSE
+        SET @sql = @sql + 'PATINDEX(@Keywords, m.[Name]) > 0 '
+    END
+
+    --PRINT (@sql)
+    EXEC sp_executesql @sql, N'@Keywords nvarchar(4000)', @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 [nop_splitstring_to_table](@CategoryIds, ',')  
+  DECLARE @CategoryIdsCount int  
+  SET @CategoryIdsCount = (SELECT COUNT(1) FROM #FilteredCategoryIds)
+
+  --filter by customer role IDs (access control list)
+  SET @AllowedCustomerRoleIds = isnull(@AllowedCustomerRoleIds, '')  
+  CREATE TABLE #FilteredCustomerRoleIds
+  (
+    CustomerRoleId int not null
+  )
+  INSERT INTO #FilteredCustomerRoleIds (CustomerRoleId)
+  SELECT CAST(data as int) FROM [nop_splitstring_to_table](@AllowedCustomerRoleIds, ',')
+  
+  --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
+  
+  --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 vendor
+  IF @VendorId > 0
+  BEGIN
+    SET @sql = @sql + '
+    AND p.VendorId = ' + CAST(@VendorId AS nvarchar(max))
+  END
+  
+  --filter by warehouse
+  IF @WarehouseId > 0
+  BEGIN
+    --we should also ensure that 'ManageInventoryMethodId' is set to 'ManageStock' (1)
+    --but we skip it in order to prevent hard-coded values (e.g. 1) and for better performance
+    SET @sql = @sql + '
+    AND  
+      (
+        (p.UseMultipleWarehouses = 0 AND
+          p.WarehouseId = ' + CAST(@WarehouseId AS nvarchar(max)) + ')
+        OR
+        (p.UseMultipleWarehouses > 0 AND
+          EXISTS (SELECT 1 FROM ProductWarehouseInventory [pwi]
+          WHERE [pwi].WarehouseId = ' + CAST(@WarehouseId AS nvarchar(max)) + ' AND [pwi].ProductId = p.Id))
+      )'
+  END
+  
+  --filter by product type
+  IF @ProductTypeId is not null
+  BEGIN
+    SET @sql = @sql + '
+    AND p.ProductTypeId = ' + CAST(@ProductTypeId AS nvarchar(max))
+  END
+  
+  --filter by "visible individually"
+  IF @VisibleIndividuallyOnly = 1
+  BEGIN
+    SET @sql = @sql + '
+    AND p.VisibleIndividually = 1'
+  END
+  
+  --filter by "marked as new"
+  IF @MarkedAsNewOnly = 1
+  BEGIN
+    SET @sql = @sql + '
+    AND p.MarkAsNew = 1
+    AND (getutcdate() BETWEEN ISNULL(p.MarkAsNewStartDateTimeUtc, ''1/1/1900'') and ISNULL(p.MarkAsNewEndDateTimeUtc, ''1/1/2999''))'
+  END
+  
+  --filter by product tag
+  IF ISNULL(@ProductTagId, 0) != 0
+  BEGIN
+    SET @sql = @sql + '
+    AND pptm.ProductTag_Id = ' + CAST(@ProductTagId AS nvarchar(max))
+  END
+  
+  --"Published" property
+  IF (@OverridePublished is null)
+  BEGIN
+    --process according to "showHidden"
+    IF @ShowHidden = 0
+    BEGIN
+      SET @sql = @sql + '
+      AND p.Published = 1'
+    END
+  END
+  ELSE IF (@OverridePublished = 1)
+  BEGIN
+    --published only
+    SET @sql = @sql + '
+    AND p.Published = 1'
+  END
+  ELSE IF (@OverridePublished = 0)
+  BEGIN
+    --unpublished only
+    SET @sql = @sql + '
+    AND p.Published = 0'
+  END