DOTNETNUKE Expert - Prasad Maduranga's Blog: dotnetnuke store product search module

There are very basic stored procedures for the search module.
Database Stored Procedures
CREATE PROCEDURE [dbo].[zee_Store_Products_GetMYSEARCHProducts]
  @CategoryID int,
  @Archived bit = 0,

  @SearchText varchar(500),
  @SearchField varchar(200)
 
 AS
 SET NOCOUNT ON
 
IF @SearchField = 'Manufacturer'
 IF @Archived = 1
  BEGIN
   SELECT
    ProductID,
    PortalID,
    CategoryID,
    Manufacturer,
    ModelNumber,
    ModelName,
    ProductImage,
    UnitCost,
    Keywords,
    Summary,
    [Description],
    Featured,
    Archived,
    CreatedByUser,
    CreatedDate,
    ProductWeight,
    ProductHeight,
    ProductLength,
    ProductWidth,
    SaleStartDate,
    SaleEndDate,
    SalePrice,
    StockQuantity,
    karatage
--   ,CASE
--    WHEN  @SearchField='Manufacturer' THEN Manufacturer -- PM @SearchField
--    WHEN  @SearchField='ModelNumber'  THEN ModelNumber
--       WHEN  @SearchField='ModelName'  THEN ModelName
--   END

   FROM  dbo.zee_Store_Products 
   WHERE  dbo.zee_Store_Products.CategoryID  = @CategoryID
   AND IsDeleted = 0
   AND Archived = 1
   AND Manufacturer  like '%' + @SearchText + '%'
   
   ORDER BY NEWID()
  END
 ELSE
  BEGIN
   
   SELECT
    ProductID,
    PortalID,
    CategoryID,
    Manufacturer,
    ModelNumber,
    ModelName,
    ProductImage,
    UnitCost,
    Keywords,
    Summary,
    [Description],
    Featured,
    Archived,
    CreatedByUser,
    CreatedDate,
    ProductWeight,
    ProductHeight,
    ProductLength,
    ProductWidth,
    SaleStartDate,
    SaleEndDate,
    SalePrice,
    StockQuantity,
    karatage

--   ,CASE
--    WHEN  @SearchField='Manufacturer' THEN Manufacturer -- PM @SearchField
--    WHEN  @SearchField='ModelNumber'  THEN ModelNumber
--       WHEN  @SearchField='ModelName'  THEN ModelName
--   END
   FROM  dbo.zee_Store_Products 
   WHERE  dbo.zee_Store_Products.CategoryID  = @CategoryID
   
   AND  Archived = 0
   AND IsDeleted = 0
   AND Manufacturer  like '%' + @SearchText + '%' --@SearchField PM 

   ORDER BY NEWID()
  END



IF @SearchField = 'ModelNumber'
 IF @Archived = 1
  BEGIN
   SELECT
    ProductID,
    PortalID,
    CategoryID,
    Manufacturer,
    ModelNumber,
    ModelName,
    ProductImage,
    UnitCost,
    Keywords,
    Summary,
    [Description],
    Featured,
    Archived,
    CreatedByUser,
    CreatedDate,
    ProductWeight,
    ProductHeight,
    ProductLength,
    ProductWidth,
    SaleStartDate,
    SaleEndDate,
    SalePrice,
    StockQuantity,
    karatage
   FROM  dbo.zee_Store_Products 
   WHERE  dbo.zee_Store_Products.CategoryID  = @CategoryID
   AND IsDeleted = 0
   AND Archived = 1
   AND ModelNumber  like '%' + @SearchText + '%'
   
   ORDER BY NEWID()
  END
 ELSE
  BEGIN
   
   SELECT
    ProductID,
    PortalID,
    CategoryID,
    Manufacturer,
    ModelNumber,
    ModelName,
    ProductImage,
    UnitCost,
    Keywords,
    Summary,
    [Description],
    Featured,
    Archived,
    CreatedByUser,
    CreatedDate,
    ProductWeight,
    ProductHeight,
    ProductLength,
    ProductWidth,
    SaleStartDate,
    SaleEndDate,
    SalePrice,
    StockQuantity,
    karatage

   FROM  dbo.zee_Store_Products 
   WHERE  dbo.zee_Store_Products.CategoryID  = @CategoryID
   
   AND  Archived = 0
   AND IsDeleted = 0
   AND ModelNumber  like '%' + @SearchText + '%' --@SearchField PM 

   ORDER BY NEWID()
  END

IF @SearchField = 'ModelName'
 IF @Archived = 1
  BEGIN
   SELECT
    ProductID,
    PortalID,
    CategoryID,
    Manufacturer,
    ModelNumber,
    ModelName,
    ProductImage,
    UnitCost,
    Keywords,
    Summary,
    [Description],
    Featured,
    Archived,
    CreatedByUser,
    CreatedDate,
    ProductWeight,
    ProductHeight,
    ProductLength,
    ProductWidth,
    SaleStartDate,
    SaleEndDate,
    SalePrice,
    StockQuantity,
    karatage
   FROM  dbo.zee_Store_Products 
   WHERE  dbo.zee_Store_Products.CategoryID  = @CategoryID
   AND IsDeleted = 0
   AND Archived = 1
   AND ModelName  like '%' + @SearchText + '%'
   
   ORDER BY NEWID()
  END
 ELSE
  BEGIN
   
   SELECT
    ProductID,
    PortalID,
    CategoryID,
    Manufacturer,
    ModelNumber,
    ModelName,
    ProductImage,
    UnitCost,
    Keywords,
    Summary,
    [Description],
    Featured,
    Archived,
    CreatedByUser,
    CreatedDate,
    ProductWeight,
    ProductHeight,
    ProductLength,
    ProductWidth,
    SaleStartDate,
    SaleEndDate,
    SalePrice,
    StockQuantity,
    karatage

   FROM  dbo.zee_Store_Products 
   WHERE  dbo.zee_Store_Products.CategoryID  = @CategoryID
   
   AND  Archived = 0
   AND IsDeleted = 0
   AND ModelName  like '%' + @SearchText + '%' --@SearchField PM 

   ORDER BY NEWID()
  END 


CREATE PROCEDURE [dbo].[zee_Store_Products_GetPortalMYSEARCHProducts]
  @PortalID int,
  @Archived bit = 0,

  @SearchText varchar(500),
  @SearchField varchar(200)

 AS
 SET NOCOUNT ON
 
IF @SearchField = 'Manufacturer'
 IF @Archived = 1
  SELECT
   ProductID,
   PortalID,
   CategoryID,
   Manufacturer,
   ModelNumber,
   ModelName,
   ProductImage,
   UnitCost,
   Keywords,
   Summary,
   [Description],
   Featured,
   Archived,
   CreatedByUser,
   CreatedDate,
   ProductWeight,
   ProductHeight,
   ProductLength,
   ProductWidth,
   SaleStartDate,
   SaleEndDate,
   SalePrice,
   StockQuantity,
    karatage

--   ,CASE
--    WHEN  @SearchField='Manufacturer' THEN Manufacturer -- PM @SearchField
--    WHEN  @SearchField='ModelNumber'  THEN ModelNumber
--       WHEN  @SearchField='ModelName'  THEN ModelName
--    --WHEN  @SearchField='SalePrice'  THEN SalePrice
--   END

  FROM  dbo.zee_Store_Products 
  WHERE  PortalID = @PortalID
  --AND Featured = 1
  AND IsDeleted = 0
  --AND  Manufacturer like 'mm%' -- PM Manufacturer
  AND Manufacturer like '%' + @SearchText + '%'

  ORDER BY NEWID()
 ELSE
  SELECT
   ProductID,
   PortalID,
   CategoryID,
   Manufacturer,
   ModelNumber,
   ModelName,
   ProductImage,
   UnitCost,
   Keywords,
   Summary,
   [Description],
   Featured,
   Archived,
   CreatedByUser,
   CreatedDate,
   ProductWeight,
   ProductHeight,
   ProductLength,
   ProductWidth,
   SaleStartDate,
   SaleEndDate,
   SalePrice,
   StockQuantity,
    karatage

--   ,CASE
--    WHEN  @SearchField='Manufacturer' THEN Manufacturer -- PM @SearchField
--    WHEN  @SearchField='ModelNumber'  THEN ModelNumber
--       WHEN  @SearchField='ModelName'  THEN ModelName
--    --WHEN  @SearchField='SalePrice'  THEN SalePrice
--   END

  FROM  dbo.zee_Store_Products 
  WHERE  PortalID = @PortalID
  --AND Featured = 1
  AND  Archived = 0
  AND IsDeleted = 0
  AND Manufacturer like '%' + @SearchText + '%' -- PM Manufacturer
  --AND Manufacturer  like 'mm%' -- PM @SearchField
  ORDER BY NEWID()


IF @SearchField = 'ModelNumber'
 IF @Archived = 1
  SELECT
   ProductID,
   PortalID,
   CategoryID,
   Manufacturer,
   ModelNumber,
   ModelName,
   ProductImage,
   UnitCost,
   Keywords,
   Summary,
   [Description],
   Featured,
   Archived,
   CreatedByUser,
   CreatedDate,
   ProductWeight,
   ProductHeight,
   ProductLength,
   ProductWidth,
   SaleStartDate,
   SaleEndDate,
   SalePrice,
   StockQuantity,
    karatage

  FROM  dbo.zee_Store_Products 
  WHERE  PortalID = @PortalID
  AND IsDeleted = 0
  --AND  Manufacturer like 'mm%' -- PM Manufacturer
  AND ModelNumber like '%' + @SearchText + '%'

  ORDER BY NEWID()
 ELSE
  SELECT
   ProductID,
   PortalID,
   CategoryID,
   Manufacturer,
   ModelNumber,
   ModelName,
   ProductImage,
   UnitCost,
   Keywords,
   Summary,
   [Description],
   Featured,
   Archived,
   CreatedByUser,
   CreatedDate,
   ProductWeight,
   ProductHeight,
   ProductLength,
   ProductWidth,
   SaleStartDate,
   SaleEndDate,
   SalePrice,
   StockQuantity,
    karatage

  FROM  dbo.zee_Store_Products 
  WHERE  PortalID = @PortalID
  AND  Archived = 0
  AND IsDeleted = 0
  AND ModelNumber like '%' + @SearchText + '%' -- PM Manufacturer
  --AND Manufacturer  like 'mm%' -- PM @SearchField
  ORDER BY NEWID()




IF @SearchField = 'ModelName'
 IF @Archived = 1
  SELECT
   ProductID,
   PortalID,
   CategoryID,
   Manufacturer,
   ModelNumber,
   ModelName,
   ProductImage,
   UnitCost,
   Keywords,
   Summary,
   [Description],
   Featured,
   Archived,
   CreatedByUser,
   CreatedDate,
   ProductWeight,
   ProductHeight,
   ProductLength,
   ProductWidth,
   SaleStartDate,
   SaleEndDate,
   SalePrice,
   StockQuantity,
    karatage

  FROM  dbo.zee_Store_Products 
  WHERE  PortalID = @PortalID
  AND IsDeleted = 0
  --AND  Manufacturer like 'mm%' -- PM Manufacturer
  AND ModelName like '%' + @SearchText + '%'

  ORDER BY NEWID()
 ELSE
  SELECT
   ProductID,
   PortalID,
   CategoryID,
   Manufacturer,
   ModelNumber,
   ModelName,
   ProductImage,
   UnitCost,
   Keywords,
   Summary,
   [Description],
   Featured,
   Archived,
   CreatedByUser,
   CreatedDate,
   ProductWeight,
   ProductHeight,
   ProductLength,
   ProductWidth,
   SaleStartDate,
   SaleEndDate,
   SalePrice,
   StockQuantity,
    karatage

  FROM  dbo.zee_Store_Products 
  WHERE  PortalID = @PortalID
  AND  Archived = 0
  AND IsDeleted = 0
  AND ModelName like '%' + @SearchText + '%' -- PM Manufacturer
  --AND Manufacturer  like 'mm%' -- PM @SearchField
  ORDER BY NEWID()
Now in the html templates you can use it [MYSEARCH] for search input and [MYSEARCHRESULT] to display searched result.

Return to - Part 1
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Microsoft Technical Issues

Powered By

dotnetnuke store product search module - part 5

0 Response to "dotnetnuke store product search module - part 5"

Post a Comment

Visitors

Translate this page

AddThis

Sponsored

Main Topics

Blog Archive

Random Post

Tags

Current Status

Chat with me

Search
