Home » blog » Zuzana_Kelyman's blog

Modifying Public view "Search page" to include "category column" from Orders pages.

Posted March 11th, 2010 by Zuzana_Kelyman

We would like to modify our public views  "search page"...../iMISpublic/Core/Search.aspx to display the "product category column"

from the pages under "orders" directory, e.g.  ...../iMISpublic/Core/Orders/Default.aspx.

Anyone can help?

Thanks.

Zuzana_Kelyman's blog | login or register to post comments | printer friendly version

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Hi It is possible but bit

On March 11th, 2010 Balaji says:

Hi

 It is possible but bit tricky, you have to follow these steps to get it working.
 
Step 1: HTML change
 
Open Search.aspx file and find the following text
 
<tr class="GridHeader">
<td>Name</td><td>Description</td>
</tr>
 
Change to  
 
<tr class="GridHeader">
<td>Name</td><td>Category</td><td>Description</td>
</tr>
 
New category Column added for the grid result set “<td>Category</td>”
 
Step 2: Modify BAESearchEvents stored procedure
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[BAESearchEvents]
@SearchString VARCHAR(200),
@MatchType INT
 
AS
 
-- SeachString: String of 1 or more search terms, all separated by spaces
-- MatchType: 0=match any, 1 =match all, 2 = exact match of entire expression only
-- Results are returned in order of relevance
 
DECLARE @i1 INT;
DECLARE @i2 INT;
DECLARE @Word VARCHAR(100);
DECLARE @Words TABLE (Word VARCHAR(100) NOT NULL);
DECLARE @Events TABLE (
      Meeting     VARCHAR(10),
      Title VARCHAR(60),
      Description text
)
 
INSERT INTO @Events
SELECT MEETING, TITLE,'</td><td>' + (CAST(DESCRIPTION AS varchar(4096))) DESCRIPTION FROM Meet_Master
      WHERE WEB_ENABLED = '1' AND STATUS = 'A' AND (BEGIN_DATE > GETDATE() OR END_DATE >= GETDATE())
 
DECLARE @WordCount AS integer;
 
SET NOCOUNT ON
 
-- Parse the SearchString to extract all words:
 
IF (@MatchType != 2)
      BEGIN
            SET @SearchString = ' ' + @SearchString + ' ';
            SET @i1 = 1;
 
            WHILE (@i1 != 0)
                  BEGIN
                        SET @i2=CHARINDEX(' ', @SearchString, @i1+1)
                        IF (@i2 != 0)
                              BEGIN
                               SET @Word = RTRIM(LTRIM(SUBSTRING(@SearchString, @i1+1, @i2-@i1)))
                               IF @Word != '' INSERT INTO @Words SELECT @Word
                              END
                        SET @i1 = @i2
                  END
            END
ELSE
      INSERT INTO @Words SELECT LTRIM(RTRIM(@SearchString))
 
 -- Get the total # of words:
 
set @WordCount = (select count(*) from @Words)
 
-- Return Results in order of relevance:
 
SELECT a.MatchPct, T.*
FROM @Events AS T
INNER JOIN
(
      SELECT T.Meeting, COUNT(*) * 1.0 / @WordCount AS MatchPct
      FROM @Events T
      INNER JOIN
            @Words W on (' ' + LOWER(T.Title) + ' ' LIKE '%[^a-z]' + LOWER(Word) + '[^a-z]%')
            OR (' ' + Lower(Substring(T.Description,1,DATALENGTH(T.Description))) + ' ' LIKE '%[^a-z]' + LOWER(Word) + '[^a-z]%')
      GROUP BY T.Meeting
 ) a ON T.Meeting = a.Meeting
WHERE
      MatchPct = 1 or @MatchType <>1
ORDER BY
      MatchPct desc
 
 
I have added </td><td> dummy tags to keep table structure consistant because this results includes event too.
 
Step 3: Modify BAESearchProductsWithCategory stored procedure
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[BAESearchProductsWithCategory]
@SearchString VARCHAR(200),
@MatchType INT
 
AS
 
-- SeachString: String of 1 or more search terms, all separated by spaces
-- MatchType: 0=match any, 1 =match all, 2 = exact match of entire expression only
-- Results are returned in order of relevance
 
DECLARE @i1 INT;
DECLARE @i2 INT;
DECLARE @Word VARCHAR(100);
DECLARE @Words TABLE (Word VARCHAR(100) NOT NULL);
DECLARE @ProductsWithCategory TABLE (
      OrderProductID    INT,
      Title             VARCHAR(60),
      Description       VARCHAR(1024),
      IsSuperProduct    BIT,
      ProductCode       VARCHAR(31),
      SellOnWeb         INT,
      IsKit             BIT,
      OrderCategoryID   INT
)
 
INSERT INTO @ProductsWithCategory
SELECT DISTINCT OrderProduct.OrderProductID, p.TITLE COLLATE database_default AS Title,
 
 ISNULL(pcat.description,'') + '</td><td>' + ISNULL((CAST(p.WEB_DESC AS varchar(4096))),'') Description
 
, IsSuperProduct, ProductCode, p.WEB_OPTION AS SellOnWeb, p.IS_KIT AS IsKit,
            (SELECT     TOP 1 OrderCategoryID FROM OrderProductCategoryLookup cat WHERE cat.OrderProductID = OrderProduct.OrderProductID) AS OrderCategoryID
      FROM OrderProduct INNER JOIN Product p
            ON p.PRODUCT_CODE COLLATE database_default = OrderProduct.ProductCode COLLATE database_default
            INNER JOIN Product_Cat pcat ON pcat.Product_Category = p.category
      WHERE       ((IsSuperProduct = 0 AND p.WEB_OPTION > 0)) AND (SELECT TOP 1 OrderCategoryID FROM OrderProductCategoryLookup cat WHERE cat.OrderProductID = OrderProduct.OrderProductID) IS NOT NULL
      UNION
      SELECT DISTINCT op.OrderProductID, op.Title COLLATE database_default AS Title, op.Description COLLATE database_default, op.IsSuperProduct, op.ProductCode, op.SellOnWeb, CAST('0' AS bit) AS IsKit,
            (SELECT TOP 1 OrderCategoryID FROM OrderProductCategoryLookup cat WHERE cat.OrderProductID = op.OrderProductID) AS OrderCategoryID
      FROM OrderProduct op
     
      WHERE       IsSuperProduct = 1 AND op.SellOnWeb > 0 AND (SELECT TOP 1 OrderCategoryID FROM OrderProductCategoryLookup cat WHERE cat.OrderProductID = op.OrderProductID) IS NOT NULL
 
DECLARE @WordCount AS integer;
 
SET NOCOUNT ON
 
-- Parse the SearchString to extract all words:
 
IF (@MatchType != 2)
      BEGIN
            SET @SearchString = ' ' + @SearchString + ' ';
            SET @i1 = 1;
 
            WHILE (@i1 != 0)
                  BEGIN
                        SET @i2=CHARINDEX(' ', @SearchString, @i1+1)
                        IF (@i2 != 0)
                              BEGIN
                               SET @Word = RTRIM(LTRIM(SUBSTRING(@SearchString, @i1+1, @i2-@i1)))
                               IF @Word != '' INSERT INTO @Words SELECT @Word
                              END
                        SET @i1 = @i2
                  END
            END
ELSE
 
      INSERT INTO @Words SELECT LTRIM(RTRIM(@SearchString))
 
 -- Get the total # of words:
 
set @WordCount = (select count(*) from @Words)
 
-- Return Results in order of relevance:
 
SELECT a.MatchPct, T.*
FROM @ProductsWithCategory T
INNER JOIN
(
      SELECT T.OrderProductID, COUNT(*) * 1.0 / @WordCount AS MatchPct
      FROM @ProductsWithCategory T
      INNER JOIN
            @Words W on ' ' + LOWER(T.Title) + ' ' LIKE '%[^a-z]' + LOWER(Word) + '[^a-z]%'
            OR ' ' + LOWER(T.Description) + ' ' LIKE '%[^a-z]' + LOWER(Word) + '[^a-z]%'
            OR dbo.SearchForSuperProduct(T.OrderProductID, T.IsSuperProduct, @SearchString, @MatchType) = 1
      GROUP BY T.OrderProductID
 ) a ON T.OrderProductID = a.OrderProductID
WHERE
      MatchPct = 1 or @MatchType <>1
ORDER BY
      T.Title
 
 
 
Created an inner join with product category table using category code and added category description as an additional column.
 
Change 1:
 
ISNULL(pcat.description,'') + '</td><td>' + ISNULL((CAST(p.WEB_DESC AS varchar(4096))),'') Description
 
Change 2:
 
INNER JOIN Product_Cat pcat ON pcat.Product_Category = p.category
  
Once you completed these steps reload search.aspx page and hit search, you will see additional category column.
 
Good luck
 
 
 
 
 
 
 
login or register to post comments

Thanks.

On March 12th, 2010 Zuzana_Kelyman says:

 It is more complex than I thought.  I will give a try.  Thank you very much for sharing.

Actually I am just looking at this closely and maybe I wasn't clear describing what we want. 

We don't want to add Category column to the search result but we would like to add the "category selections panel" on the search page. It would be there even before  searching by key word. So people could either type the search word in and click search or just click on the category and be redirected to .../Core/Orders/category.aspx  and see the products in the category.

 

login or register to post comments