Improving Interprise 5.6.22’s Web Search Page

by jasonrshaver 10. September 2011 00:09

So, lets take a look at the Interprise search page.  You know, the one that looks something like this:

image

Don’t get me wrong, It functions, and I find that I use this screen all the time over the course of my work day, but I just wish it could answer some questions for me, such as these:

  • How much does this product cost again?
  • What do I have in stock right now?
  • What was the name of that discontinued product again?

Template Changes

So, first, we need to make one change to our template to support this.  Don’t fret, it is really easy:

  1. <script type="text/javascript" src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.6.3.min.js" />

Put the above line to your skin’s template.ascx’s head section.  If you don’t know jQuery yet, it is the #1 tool needed to make your site pop.  It does not do it for free, but between XmlPackages, SQL, and jQuery, there is almost nothing you can’t do. 

Including Discontinued Products In Search Results

Ok, this one is really easy!  Create the following SQL Stored Procedure which is just slightly modified from the default aspdnsf_WebSearchInventory procedure:

  1.  
  2.               CREATE PROCEDURE [dbo].[ttx_WebSearchInventoryExt]
  3. @SearchTerm            NVARCHAR(3000),              
  4. @WebSiteCode            NVARCHAR(30),              
  5. @LanguageCode            NVARCHAR(50),            
  6. @ItemCode                NVARCHAR(1000) = NULL,             
  7. @ItemType                NVARCHAR(50) = NULL,          
  8. @UnitMeasure            NVARCHAR(30) = NULL,          
  9. @CategoryID            NVARCHAR (100) = NULL,
  10. @SectionID                NVARCHAR (100) = NULL,
  11. @ManufacturerID        NVARCHAR (100) = NULL,
  12. @SearchDescriptions    VARCHAR(5),    
  13. @CurrentDate            DATETIME,
  14. @ShowDiscontinued        BIT = 0
  15.     
  16.               AS
  17.               BEGIN
  18.               SET NOCOUNT ON
  19.           
  20. SET @SearchTerm = Replace(@SearchTerm,'''','''''')          
  21. SET @SearchTerm = '''%' + @SearchTerm + '%'''              
  22. SET @SearchTerm = Replace(@SearchTerm,'+',' ')          
  23. SET @ItemCode = NULLIF(@ItemCode, NULL)           
  24.           
  25. IF (@ItemType = 'ANY')           
  26.   SET @ItemType = '''%'''             
  27. ELSE          
  28.   SET @ItemType = '''%' + @ItemType + '%'''            
  29.           
  30. IF (@UnitMeasure = 'ANY')           
  31.   SET @UnitMeasure = '''%'''              
  32. ELSE          
  33.   SET @UnitMeasure = '%' + @UnitMeasure + '%'''             
  34.               
  35. DECLARE @SearchDescriptionString VARCHAR(1000)          
  36.   IF (@SearchDescriptions = '0')           
  37.   SET @SearchDescriptionString = ' '           
  38.   ELSE          
  39.   SET @SearchDescriptionString = ' OR IIWOD.WebDescription LIKE ' + @SearchTerm           
  40.       
  41. DECLARE @JoinUnitMeasureString VARCHAR(1000)          
  42.   IF (@UnitMeasure = '''%''')           
  43.   SET @JoinUnitMeasureString = ' '           
  44.   ELSE          
  45.   SET @JoinUnitMeasureString = ' INNER JOIN InventoryUnitMeasure IUM WITH (NOLOCK) ON IUM.ItemCode = A.ItemCode AND ISNULL(UnitMeasureQty,0)>0 AND IUM.UnitMeasureCode LIKE ''' + @UnitMeasure + ''             
  47. DECLARE @EntityBuilderString VARCHAR(1000)
  48. SET @EntityBuilderString = ''
  49.   IF (@CategoryID <> '0')
  50.     BEGIN
  51.         SET @EntityBuilderString = 'INNER JOIN (SELECT ItemCode FROM InventoryCategory A WITH (NOLOCK) INNER JOIN SystemCategory B WITH (NOLOCK) ON A.CategoryCode = B.CategoryCode WHERE B.Counter = ''' + @CategoryID +
  52.                                    ''') IC ON IC.ItemCode = A.ItemCode'
  53.     END
  54.   IF (@SectionID <> '0')
  55.     BEGIN
  56.         SET @EntityBuilderString = @EntityBuilderString + ' INNER JOIN (SELECT ItemCode FROM InventoryItemDepartment A WITH (NOLOCK) INNER JOIN InventorySellingDepartment B WITH (NOLOCK) ON A.DepartmentCode = B.DepartmentCode WHERE B.Counter = ''' + @SectionID +
  57.                                    ''') IIDep ON IIDep.ItemCode = A.ItemCode'
  58.     END
  59.   IF (@ManufacturerID <> '0')
  60.     BEGIN
  61.         SET @EntityBuilderString = @EntityBuilderString + ' INNER JOIN (SELECT A.ManufacturerCode, A.ItemCode FROM InventoryItem A WITH (NOLOCK) INNER JOIN SystemManufacturer B WITH (NOLOCK) ON A.ManufacturerCode = B.ManufacturerCode WHERE B.Counter = ''' + @ManufacturerID +
  62.                                    ''') SM ON SM.ItemCode = A.ItemCode'
  63.     END
  65. DECLARE @SQL VARCHAR(8000)       
  66. SET @SQL='SELECT          
  67.     A.*, IID.ItemDescription, IIWOD.WebDescription, H.Status, H.RetailPrice, H.UnitsInStock FROM InventoryItem A WITH (NOLOCK)          
  68.     LEFT JOIN (SELECT ItemCode FROM InventoryItemPricingDetail B WITH (NOLOCK)      
  69. INNER JOIN SystemCurrency C WITH (NOLOCK) ON B.CurrencyCode = C.CurrencyCode WHERE IsHomeCurrency = 1) D ON A.ItemCode = D.ItemCode   
  70. LEFT JOIN (SELECT TOP 1 ItemKitCode FROM InventoryKitPricingDetail E WITH (NOLOCK)             
  71. INNER JOIN SystemCurrency F WITH (NOLOCK) ON E.CurrencyCode = F.CurrencyCode WHERE IsHomeCurrency = 1) G ON A.ItemCode = G.ItemKitCode    
  72.   LEFT JOIN (
  73.  
  74.                         SELECT iiapv.ItemCode
  75.                             , iiapv.Status
  76.                             , iiapv.RetailPrice
  77.                             , SUM(istv.UnitsInStock) as UnitsInStock
  78.                           FROM [InventoryItemAndPricingView] iiapv WITH (NOLOCK)
  79.                           JOIN InventoryMatrixItem imi WITH (NOLOCK) ON iiapv.ItemCode = imi.ItemCode
  80.                           JOIN InventoryStockTotalView istv WITH (NOLOCK) ON imi.MatrixItemCode = istv.ItemCode
  81.                         WHERE iiapv.LanguageCode = ''' + @LanguageCode + '''
  82.                            AND istv.LanguageCode = ''' + @LanguageCode + '''
  83.                            AND iiapv.ItemType = ''Matrix Group''
  84.                       GROUP BY iiapv.ItemCode
  85.                             , iiapv.Status
  86.                             , iiapv.RetailPrice
  87.                 UNION
  88.                         SELECT iiapv.ItemCode
  89.                             , iiapv.Status
  90.                             , iiapv.RetailPrice
  91.                             , istv.UnitsInStock as UnitsInStock
  92.                           FROM [InventoryItemAndPricingView] iiapv WITH (NOLOCK)
  93.                           JOIN InventoryStockTotalView istv WITH (NOLOCK) ON IIAPV.ItemCode = istv.ItemCode
  94.                         WHERE iiapv.LanguageCode = ''' + @LanguageCode + '''
  95.                            AND istv.LanguageCode = ''' + @LanguageCode + '''
  96.                            AND iiapv.ItemType IN (''Non-Stock'', ''Service'', ''Stock'',''Service'',''Electronic Download'')
  97.                ) H ON H.ItemCode = a.ItemCode           
  98. INNER JOIN InventoryItemDescription IID WITH (NOLOCK) ON IID.ItemCode = A.ItemCode AND IID.LanguageCode = ''' + @LanguageCode + '''           
  99. INNER JOIN InventoryItemWebOption IIWO WITH (NOLOCK) ON IIWO.ItemCode = A.ItemCode AND IIWO.WebsiteCode = ''' + @WebsiteCode + '''          
  100. INNER JOIN InventoryItemWebOptionDescription IIWOD WITH (NOLOCK) ON IIWOD.ItemCode = IIWO.ItemCode AND IIWOD.WebsiteCode = IIWO.WebsiteCode  AND IIWOD.LanguageCode = ''' + @LanguageCode + ''' ' + @JoinUnitMeasureString + ' ' + @EntityBuilderString + '
  101. WHERE (IID.ItemDescription LIKE ' + @SearchTerm + ' OR A.ItemName LIKE ' + @SearchTerm + ' ' + @SearchDescriptionString + ') AND     
  102.     ItemType IN (''Non-Stock'', ''Service'', ''Stock'',''Matrix Group'',''Kit'',''Service'',''Electronic Download'', ''Assembly'') AND ''' +
  103.    cast(@CurrentDate as nvarchar(50)) + ''' between isnull(IIWO.StartDate, ''1/1/1900'') AND isnull(IIWO.EndDate, ''12/31/9999'') '
  104.  
  105. IF (@ShowDiscontinued = 0) BEGIN
  106.     SET @SQL = @SQL + ' AND A.Status = ''A'' '
  107. END
  108.  
  109. SET @SQL = @SQL + ' AND IIWO.Published = 1 AND IIWO.CheckOutOption = 0 AND         
  110.        ItemType LIKE ' + @ItemType + 'ORDER BY IIWO.IsExclusive DESC, A.ItemName ASC'
  111.   
  112. EXEC(@SQL)  
  113. --PRINT @SQL
  114.  
  115. END

Now open your XmlPackages\page.search.xml.config and change the following block:

  1. <query name="Products" rowElementName="Product" runif="SearchTerm">
  2.   <sql>
  3.     <![CDATA[
  4.           exec aspdnsf_WebSearchInventory @SearchTerm, @WebSiteCode, @LanguageCode, NULL, 'ANY', 'ANY', '0', '0', '0',@SearchDescriptions, @CurrentDate
  5.         ]]>
  6.   </sql>
  7.   <queryparam paramname="@SearchTerm" paramtype="request" requestparamname="SearchTerm" sqlDataType="varchar" defvalue="" validationpattern=""/>
  8.   <queryparam paramname="@WebSiteCode" paramtype="runtime" requestparamname="WebSiteCode" sqlDataType="varchar" defvalue="" validationpattern=""/>
  9.   <queryparam paramname="@LanguageCode" paramtype="runtime" requestparamname="LanguageCode" sqlDataType="varchar" defvalue="" validationpattern=""/>
  10.   <queryparam paramname="@SearchDescriptions" paramtype="runtime" requestparamname="SearchDescriptions" sqlDataType="varchar" defvalue="0" validationpattern=""/>
  11.   <queryparam paramname="@CurrentDate" paramtype="runtime" requestparamname="Date" sqlDataType="datetime" defvalue="0" validationpattern=""/>
  12. </query>

to this

  1. <query name="Products" rowElementName="Product" runif="SearchTerm">
  2.   <sql>
  3.     <![CDATA[
  4.           exec ttx_WebSearchInventoryExt @SearchTerm, @WebSiteCode, @LanguageCode, NULL, 'ANY', 'ANY', '0', '0', '0',@SearchDescriptions, @CurrentDate, @ShowDiscontinued
  5.         ]]>
  6.   </sql>
  7.   <queryparam paramname="@SearchTerm" paramtype="request" requestparamname="SearchTerm" sqlDataType="varchar" defvalue="" validationpattern=""/>
  8.   <queryparam paramname="@WebSiteCode" paramtype="runtime" requestparamname="WebSiteCode" sqlDataType="varchar" defvalue="" validationpattern=""/>
  9.   <queryparam paramname="@LanguageCode" paramtype="runtime" requestparamname="LanguageCode" sqlDataType="varchar" defvalue="" validationpattern=""/>
  10.   <queryparam paramname="@SearchDescriptions" paramtype="runtime" requestparamname="SearchDescriptions" sqlDataType="varchar" defvalue="0" validationpattern=""/>
  11.   <queryparam paramname="@CurrentDate" paramtype="runtime" requestparamname="Date" sqlDataType="datetime" defvalue="0" validationpattern=""/>
  12.   <queryparam paramname="@ShowDiscontinued" paramtype="request" requestparamname="ShowDiscontinued" sqlDataType="varchar" defvalue="1" validationpattern=""/>
  13. </query>

and you should be ready to go.  Note, you can add a ‘ShowDiscontinued=0’ or ‘ShowDiscontinued=1’ to show or hide discontinued products. 

Labeling Out of Stock or Discontinued Products

So now that we added discontinued products, we can’t tell quickly what is discontinued or not.  And while we are at it, why not fix the same issue for stock problems.  Let’s throw in some price displays while we are at it.

These are all simple changes to the XmlPackages\page.search.xml.config.  Around line 158, we need to add two columns to our Product Results header row:

  1. <tr>
  2.     <td>
  3.         <b>Notes</b>                                                   
  4.     </td>
  5.     <td align="left">
  6.     <b>
  7.         <xsl:value-of select="ise:StringResource('search.aspx.6', $LocaleSetting)" disable-output-escaping="yes"/>
  8.     </b>
  9.     </td>
  10.     <td>
  11.         <b>Price</b>
  12.     </td>
  13.     <td align="center">
  14.     <b>
  15.         <xsl:value-of select="ise:StringResource('AppConfig.CategoryPromptSingular', $LocaleSetting)" disable-output-escaping="yes"/>
  16.     </b>
  17.     </td>
  18.     <xsl:if test="ise:AppConfigBool('Search_ShowManufacturersInResults')='true'">
  19.     <td align="center">
  20.         <b>
  21.         <xsl:value-of select="ise:StringResource('search.aspx.8', $LocaleSetting)" disable-output-escaping="yes"/>
  22.         </b>
  23.     </td>
  24.     </xsl:if>
  25. </tr>
  26. <xsl:apply-templates select="/root/Products/Product"/>

And now, in your product template (starting at line ~202), right after the inner-most <tr> element of the product table, add the following:

  1. <tr>
  2.     <xsl:choose>
  3.         <xsl:when test="Status != 'A' and UnitsInStock = 0">
  4.             <xsl:attribute name="class">searchResult discontinued outOfStock</xsl:attribute>
  5.         </xsl:when>
  6.         <xsl:when test="Status != 'A'">
  7.             <xsl:attribute name="class">searchResult discontinued</xsl:attribute>
  8.         </xsl:when>
  9.         <xsl:when test="UnitsInStock = 0">
  10.             <xsl:attribute name="class">searchResult outOfStock</xsl:attribute>
  11.         </xsl:when>
  12.         <xsl:otherwise>
  13.             <xsl:attribute name="class">searchResult</xsl:attribute>
  14.         </xsl:otherwise>
  15.     </xsl:choose>

add then following columns; a Notes column before the product name column:

  1. <td width="110px">
  2.     <span style="font-weight:normal;">
  3.         <xsl:choose>
  4.             <xsl:when test="Status != 'A' and UnitsInStock = 0">
  5.                 <span style="background: #F2F2F2;">Discontinued</span>
  6.                 <br/>
  7.                 <span style="background: #F2DCDB;">Out of Stock</span>
  8.             </xsl:when>
  9.             <xsl:when test="Status != 'A'">
  10.                 <span style="background: #F2F2F2;">Discontinued</span>
  11.             </xsl:when>
  12.             <xsl:when test="UnitsInStock = 0">
  13.                 <span style="background: #F2DCDB;">Out of Stock</span>
  14.             </xsl:when>
  15.         </xsl:choose>
  16.     </span>
  17. </td>

And the price column after the product name column:

  1. <td align="right" width="65px" >
  2.     <xsl:choose>
  3.         <xsl:when test="Status != 'A' and UnitsInStock = 0">
  4.             <span style="background: #F2F2F2;">
  5.                 $ <xsl:value-of select="format-number(RetailPrice, '#,##0.00')" />
  6.             </span>
  7.         </xsl:when>
  8.         <xsl:when test="Status != 'A'">
  9.             <span style="background: #F2F2F2;">
  10.                 $ <xsl:value-of select="format-number(RetailPrice, '#,##0.00')" />
  11.             </span>
  12.         </xsl:when>
  13.         <xsl:when test="UnitsInStock = 0">
  14.             <span style="background: #F2DCDB;">
  15.                 $ <xsl:value-of select="format-number(RetailPrice, '#,##0.00')" />
  16.             </span>
  17.         </xsl:when>
  18.         <xsl:otherwise>
  19.             <span >
  20.                 $ <xsl:value-of select="format-number(RetailPrice, '#,##0.00')" />
  21.             </span>
  22.         </xsl:otherwise>
  23.     </xsl:choose>
  24. </td>

Now you have the price and pretty notes for the stock status:

image

One Word: Checkboxes!!!

Now that we have all that done, we can use some simple jQuery magic to add some checkboxes that hide discontinued and/or out of stock items!

As part of this, we can refactor the search text to a format that is easier to extend (table system that breaks everything out) and makes it easily to match visuals with all our users’ browsers.  Replace the the Form element located around Line 76 of XmlPackages\page.search.xml.config with the following:

  1. <form method="GET" action="search.aspx" id="SearchForm2" name="SearchForm2">
  2.         <table border="0" cellpadding="0" cellspacing="0" width="500px">
  3.             <tr>
  4.                 <td colspan="2">
  5.                     <span style="color:red;font-weight:bold">
  6.                         <xsl:value-of select="/root/QueryString/errormsg" />
  7.                     </span>
  8.                 </td>
  9.             </tr>
  10.             <tr>
  11.                 <td colspan="2">
  12.                     <xsl:value-of select="ise:StringResource('search.aspx.3', $LocaleSetting)" disable-output-escaping="yes" />&#0160;
  13.                 </td>
  14.             </tr>
  15.             <tr align="left">
  16.                 <td width="300px">
  17.                     <input type="text" id="SearchTerm" name="SearchTerm" size="50" maxlength="70" value="{$pSearchTerm}" style="width:300px;"></input>
  18.                     <br/>
  19.                     <xsl:value-of select="ise:GetSearchFormValidatorScript('SearchForm', 'SearchTerm')" disable-output-escaping="yes" />
  20.                 </td>
  21.                 <td width="200px">
  22.                     <input type="submit" value="Search" name="B1" />
  23.                 </td>
  24.             </tr>
  25.             <tr align="">
  26.                 <td></td>
  27.                 <td>
  28.                     <input id="ShowDiscontinued" type="checkbox">Show Discontinued</input>
  29.                 </td>
  30.             </tr>
  31.             <tr align="">
  32.                 <td></td>
  33.                 <td>
  34.                     <input id="ShowOutOfStock" type="checkbox" checked="checked">Show Out of Stock</input>
  35.                 </td>
  36.             </tr>
  37.         </table>
  38. </form>

which gives us some checkboxes, but now we have to wire them up.  Put this script with your <xsl:template match=”/”>

  1.       <xsl:template match="/">
  2.         <xsl:value-of select="ise:Topic('SearchPageHeader')" disable-output-escaping="yes"/>
  3.           <script type="text/javascript">
  4. function UpdateVisibility()
  5. {
  6. $(".searchResult").css("display", "table-row");
  7.  
  8. if ($("#ShowDiscontinued").is(':checked') == false)
  9.     $(".discontinued").css("display", "none");
  10.  
  11. if ($("#ShowOutOfStock").is(':checked') == false)
  12.     $(".outOfStock").css("display", "none");
  13. };
  14.              
  15. $(document).ready(function()
  16. {
  17. $("#ShowDiscontinued").click(UpdateVisibility);
  18. $("#ShowOutOfStock").click(UpdateVisibility);
  19. UpdateVisibility();
  20. });
  21.           </script>

Now, save everything and if you did it right (I know my instructions are not the easiest out there), you have some awesome stuff:

image

And if you uncheck the “Show Out of Stock” checkbox, all my products go away!

image

Pretty cool for an hour’s effort huh?  Maybe my next post will be table sorting…

Tags:

Blog

Submit to DotNetKicks...
Permalink | Comments (0)

About the author

I am a software developer working for Microsoft in Redmond, WA.  In addition, my wife and I own TTXOnline, what is likely the 3rd largest table tennis store in the US.

Month List

Page List