C#/VB.NET Code:
ALTER proc [dbo].[aspdnsf_GetProducts]
@categoryID int = null,
@sectionID int = null,
@manufacturerID int = null,
@distributorID int = null,
@genreID int = null,
@vectorID int = null,
@localeID int = null,
@CustomerLevelID int = null,
@affiliateID int = null,
@ProductTypeID int = null,
@ViewType bit = 1, -- 0 = all variants, 1 = one variant
@sortEntity int = 0, -- 1 = category, 2 = section, 3 = manufacturer, 4 = distributor, 5= genre, 6 = vector
@pagenum int = 1,
@pagesize int = null,
@StatsFirst tinyint = 1,
@searchstr nvarchar(4000) = null,
@searchstr2 nvarchar(4000) = null,
@extSearch tinyint = 0,
@publishedonly tinyint = 0,
@ExcludePacks tinyint = 0,
@ExcludeKits tinyint = 0,
@ExcludeSysProds tinyint = 0,
@InventoryFilter int = 0, -- will only show products with an inventory level GREATER OR EQUAL TO than the number specified in this parameter, set to -1 to disable inventory filtering
@sortEntityName varchar(20) = '', -- usely only when the entity id is provided, allowed values: category, section, manufacturer, distributor, genre, vector
@localeName varchar(20) = '',
@OnSaleOnly tinyint = 0,
@IncludeAll bit = 0, -- Don't filter products that have a start date in the future or a stop date in the past
@storeID int = 1,
@filterProduct bit = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @rcount int
DECLARE @productfilter table (rownum int not null identity primary key, productid int not null,displayorder int null, VariantID int not null, VariantDisplayOrder int not null, ProductName nvarchar(400) null, VariantName nvarchar(400) null, [rank] int null)
DECLARE @FilterProductsByAffiliate tinyint, @FilterProductsByCustomerLevel tinyint, @HideProductsWithLessThanThisInventoryLevel int
CREATE TABLE #displayorder ([name] nvarchar (800), productid int not null primary key, displayorder int not null)
CREATE TABLE #inventoryfilter (productid int not null, variantid int not null, InvQty int not null)
CREATE CLUSTERED INDEX tmp_inventoryfilter ON #inventoryfilter (productid, variantid)
DECLARE @custlevelcount int, @sectioncount int, @localecount int, @affiliatecount int, @categorycount int, @CustomerLevelFilteringIsAscending bit, @distributorcount int, @genrecount int, @vectorcount int, @manufacturercount int
SELECT @FilterProductsByAffiliate = case ConfigValue when 'true' then 1 else 0 end FROM dbo.AppConfig with (nolock) WHERE [Name] = 'FilterProductsByAffiliate'
SELECT @FilterProductsByCustomerLevel = case ConfigValue when 'true' then 1 else 0 end FROM dbo.AppConfig with (nolock) WHERE [Name] = 'FilterProductsByCustomerLevel'
SELECT @HideProductsWithLessThanThisInventoryLevel = convert(int, ConfigValue) FROM dbo.AppConfig with (nolock) WHERE [Name] = 'HideProductsWithLessThanThisInventoryLevel' and isnumeric(ConfigValue) = 1
IF @InventoryFilter <> -1 and (@HideProductsWithLessThanThisInventoryLevel > @InventoryFilter or @HideProductsWithLessThanThisInventoryLevel = -1)
SET @InventoryFilter = @HideProductsWithLessThanThisInventoryLevel
SET @categoryID = nullif(@categoryID, 0)
SET @sectionID = nullif(@sectionID, 0)
SET @manufacturerID = nullif(@manufacturerID, 0)
SET @distributorID = nullif(@distributorID, 0)
SET @genreID = nullif(@genreID, 0)
SET @vectorID = nullif(@vectorID, 0)
SET @affiliateID = nullif(@affiliateID, 0)
SET @ProductTypeID = nullif(@ProductTypeID, 0)
SET @CustomerLevelFilteringIsAscending = 0
SELECT @CustomerLevelFilteringIsAscending = case configvalue when 'true' then 1 else 0 end
FROM dbo.AppConfig with (nolock)
WHERE name = 'FilterByCustomerLevelIsAscending'
IF @localeID is null and ltrim(rtrim(@localeName)) <> ''
SELECT @localeID = LocaleSettingID FROM dbo.LocaleSetting with (nolock) WHERE Name = ltrim(rtrim(@localeName))
select @categorycount = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('productcategory') and si.indid < 2 and type = 'u'
select @sectioncount = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('productsection') and si.indid < 2 and type = 'u'
select @localecount = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductLocaleSetting') and si.indid < 2 and type = 'u'
select @custlevelcount = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductCustomerLevel') and si.indid < 2 and type = 'u'
select @affiliatecount = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductAffiliate') and si.indid < 2 and type = 'u'
select @distributorcount = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductDistributor') and si.indid < 2 and type = 'u'
select @genrecount = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductGenre') and si.indid < 2 and type = 'u'
select @vectorcount = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductVector') and si.indid < 2 and type = 'u'
select @manufacturercount = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductManufacturer') and si.indid < 2 and type = 'u'
-- get page size
IF @pagesize is null or @pagesize = 0 BEGIN
IF @categoryID is not null
SELECT @pagesize = PageSize FROM dbo.Category with (nolock) WHERE categoryID = @categoryID
ELSE IF @sectionID is not null
SELECT @pagesize = PageSize FROM dbo.Section with (nolock) WHERE sectionID = @sectionID
ELSE IF @manufacturerID is not null
SELECT @pagesize = PageSize FROM dbo.Manufacturer with (nolock) WHERE manufacturerID = @manufacturerID
ELSE IF @distributorID is not null
SELECT @pagesize = PageSize FROM dbo.Distributor with (nolock) WHERE distributorID = @distributorID
ELSE IF @genreID is not null
SELECT @pagesize = PageSize FROM dbo.Genre with (nolock) WHERE genreID = @genreID
ELSE IF @vectorID is not null
SELECT @pagesize = PageSize FROM dbo.Vector with (nolock) WHERE vectorID = @vectorID
ELSE
SELECT @pagesize = convert(int, ConfigValue) FROM dbo.AppConfig with (nolock) WHERE [Name] = 'Default_CategoryPageSize'
END
IF @pagesize is null or @pagesize = 0
SET @pagesize = 20
-- get sort order
IF @sortEntity = 1 or @sortEntityName = 'category' BEGIN
INSERT #displayorder select distinct null as [name], a.productid, displayorder from dbo.ProductCategory a with (nolock) inner join (select distinct a.ProductID from ProductCategory a with (nolock)
left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b on a.ProductID = b.ProductID where categoryID = @categoryID
END
ELSE IF @sortEntity = 2 or @sortEntityName = 'section' BEGIN
INSERT #displayorder select distinct null as [name], a.productid, displayorder from dbo.ProductSection a with (nolock) inner join (select distinct a.ProductID from ProductSection a with (nolock)
left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b on a.ProductID = B.ProductID where sectionId = @sectionID
END
ELSE IF @sortEntity = 3 or @sortEntityName = 'manufacturer' BEGIN
INSERT #displayorder select distinct null as [name], a.productid, displayorder from dbo.ProductManufacturer a with (nolock) inner join (select distinct a.ProductID from ProductManufacturer a with (nolock)
left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b on a.ProductID = B.ProductID where ManufacturerID = @manufacturerID
END
ELSE IF @sortEntity = 4 or @sortEntityName = 'distributor' BEGIN
INSERT #displayorder select distinct null as [name], a.productid, displayorder from dbo.ProductDistributor a with (nolock) inner join (select distinct a.ProductID from ProductDistributor a with (nolock)
left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b on a.ProductID = B.ProductID where DistributorID = @distributorID
END
ELSE IF @sortEntity = 5 or @sortEntityName = 'genre' BEGIN
INSERT #displayorder select distinct null as [name], a.productid, displayorder from dbo.ProductGenre a with (nolock) inner join (select distinct a.ProductID from ProductGenre a with (nolock)
left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b on a.ProductID = B.ProductID where GenreID = @genreID
END
ELSE IF @sortEntity = 6 or @sortEntityName = 'vector' BEGIN
INSERT #displayorder select distinct null as [name], a.productid, displayorder from dbo.ProductVector a with (nolock) inner join (select distinct a.ProductID from ProductVector a with (nolock)
left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b on a.ProductID = B.ProductID where VectorID = @vectorID
END
ELSE BEGIN
INSERT #displayorder select distinct [name], a.productid, 1 from dbo.Product a with (nolock) inner join (select distinct a.ProductID from Product a with (nolock)
left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b on a.ProductID = B.ProductID ORDER BY Name
END
--SET @searchstr = '%' + rtrim(ltrim(@searchstr)) + '%'
declare @ftscount int
CREATE TABLE #fts ([key] int not null, [rank] int not null)
IF @InventoryFilter <> -1 BEGIN
IF @ViewType = 1 BEGIN
INSERT #inventoryfilter
SELECT p.productid, pv.VariantID, sum(case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end ) invqty
FROM product p with (NOLOCK) join #displayorder d on p.ProductID = d.ProductID
join ProductVariant pv with (NOLOCK) on p.ProductID = pv.ProductID and pv.IsDefault = 1
left join Inventory i with (NOLOCK) on pv.VariantID = i.VariantID
GROUP BY p.productid, pv.VariantID
HAVING sum(case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end ) >= @InventoryFilter
END
ELSE
INSERT #inventoryfilter
SELECT p.productid, pv.VariantID, sum(case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end ) invqty
FROM product p with (NOLOCK) join #displayorder d on p.ProductID = d.ProductID
join ProductVariant pv with (NOLOCK) on p.ProductID = pv.ProductID
left join Inventory i with (NOLOCK) on pv.VariantID = i.VariantID
GROUP BY p.productid, pv.VariantID
HAVING sum(case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end ) >= @InventoryFilter
IF @searchstr is not null BEGIN
INSERT #fts SELECT [key],[rank] from CONTAINSTABLE (product,*,@searchstr)
SELECT @ftscount = (SELECT count(*) from #fts)
IF @ftscount <> 0 BEGIN
INSERT @productfilter (productid, VariantID, VariantDisplayOrder, ProductName, VariantName, [rank])
SELECT distinct p.productid, pv.VariantID, pv.DisplayOrder, p.Name, pv.Name, ft.rank
FROM
product p with (nolock)
inner join #fts as ft on p.ProductID=ft.[key]
left join ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType
left join productcategory pc with (nolock) on p.ProductID = pc.ProductID
left join ProductCustomerLevel pcl with (nolock) on p.ProductID = pcl.ProductID
join #inventoryfilter i on pv.VariantID = i.VariantID
WHERE
(pc.categoryid = @categoryID or @categoryID is null or @categorycount = 0)
and p.ProductTypeID = coalesce(@ProductTypeID, p.ProductTypeID)
and (case
when @FilterProductsByCustomerLevel = 0 or @custlevelcount = 0 or pcl.CustomerLevelID is null or @CustomerLevelID is null then 1
when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID then 1
when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1
else 0
end = 1
)
and case when isnull(pv.saleprice,0) = 0 then 0 else 1 end >= @OnSaleOnly
and p.published >= @publishedonly
and pv.published >= @publishedonly
and isnull(p.IsAPack, 0) <= 1-@ExcludePacks
and isnull(p.IsAKit, 0) <= 1-@ExcludeKits
and p.IsSystem <= 1-@ExcludeSysProds
and p.Deleted = 0
and pv.Deleted = 0
or (
@searchstr2 is null
or patindex(@searchstr2, isnull(convert(nvarchar(20),p.productid), '')) > 0
)
order by ft.rank desc
END
ELSE BEGIN
INSERT @productfilter (productid, VariantID, VariantDisplayOrder, ProductName, VariantName)
SELECT distinct p.productid, pv.VariantID, pv.DisplayOrder, p.Name, pv.Name
FROM
product p with (nolock)
left join ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType
left join productcategory pc with (nolock) on p.ProductID = pc.ProductID
left join ProductCustomerLevel pcl with (nolock) on p.ProductID = pcl.ProductID
join #inventoryfilter i on pv.VariantID = i.VariantID
WHERE
(pc.categoryid = @categoryID or @categoryID is null or @categorycount = 0)
and p.ProductTypeID = coalesce(@ProductTypeID, p.ProductTypeID)
and (case
when @FilterProductsByCustomerLevel = 0 or @custlevelcount = 0 or pcl.CustomerLevelID is null or @CustomerLevelID is null then 1
when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID then 1
when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1
else 0
end = 1
)
and (
@searchstr2 is null
or patindex('%'+@searchstr2+'%', isnull(p.name, '')) > 0
or patindex(@searchstr2, isnull(convert(nvarchar(20),p.productid), '')) > 0
or patindex('%'+@searchstr2+'%', isnull(pv.name, '')) > 0
or patindex('%'+@searchstr2+'%', isnull(p.sku , '')+isnull(pv.skusuffix, '')) > 0
or patindex('%'+@searchstr2+'%', isnull(p.manufacturerpartnumber, '')) > 0
or patindex('%'+@searchstr2+'%', isnull(pv.manufacturerpartnumber, '')) > 0
)
and case when isnull(pv.saleprice,0) = 0 then 0 else 1 end >= @OnSaleOnly
and p.published >= @publishedonly
and pv.published >= @publishedonly
and isnull(p.IsAPack, 0) <= 1-@ExcludePacks
and isnull(p.IsAKit, 0) <= 1-@ExcludeKits
and p.IsSystem <= 1-@ExcludeSysProds
and p.Deleted = 0
and pv.Deleted = 0
END
END
ELSE BEGIN
INSERT @productfilter (productid, displayorder, VariantID, VariantDisplayOrder, ProductName, VariantName)
SELECT distinct p.productid, do.displayorder, pv.VariantID, pv.DisplayOrder, p.Name, pv.Name
FROM
product p with (nolock)
join #displayorder do on p.ProductID = do.ProductID
left join ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType
left join productcategory pc with (nolock) on p.ProductID = pc.ProductID
left join ProductCustomerLevel pcl with (nolock) on p.ProductID = pcl.ProductID
join #inventoryfilter i on pv.VariantID = i.VariantID
WHERE
(pc.categoryid = @categoryID or @categoryID is null or @categorycount = 0)
and p.ProductTypeID = coalesce(@ProductTypeID, p.ProductTypeID)
and (case
when @FilterProductsByCustomerLevel = 0 or @custlevelcount = 0 or pcl.CustomerLevelID is null or @CustomerLevelID is null then 1
when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID then 1
when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1
else 0
end = 1
)
and (
@searchstr2 is null
or patindex('%'+@searchstr2+'%', isnull(p.name, '')) > 0
or patindex(@searchstr2, isnull(convert(nvarchar(20),p.productid), '')) > 0
or patindex('%'+@searchstr2+'%', isnull(pv.name, '')) > 0
or patindex('%'+@searchstr2+'%', isnull(p.sku , '')+isnull(pv.skusuffix, '')) > 0
or patindex('%'+@searchstr2+'%', isnull(p.manufacturerpartnumber, '')) > 0
or patindex('%'+@searchstr2+'%', isnull(pv.manufacturerpartnumber, '')) > 0
or (patindex('%'+@searchstr2+'%', isnull(p.Description, '')) > 0 and @extSearch = 1)
or (patindex('%'+@searchstr2+'%', isnull(p.Summary, '')) > 0 and @extSearch = 1)
)
and case when isnull(pv.saleprice,0) = 0 then 0 else 1 end >= @OnSaleOnly
and p.published >= @publishedonly
and pv.published >= @publishedonly
and isnull(p.IsAPack, 0) <= 1-@ExcludePacks
and isnull(p.IsAKit, 0) <= 1-@ExcludeKits
and p.IsSystem <= 1-@ExcludeSysProds
and p.Deleted = 0
and pv.Deleted = 0
order by do.displayorder, p.Name, pv.DisplayOrder, pv.Name
END
END
ELSE BEGIN
IF @searchstr is not null BEGIN
INSERT #fts SELECT [key],[rank] from CONTAINSTABLE (product,*,@searchstr)
SELECT @ftscount = (SELECT count(*) from #fts)
IF @ftscount <> 0 BEGIN
INSERT @productfilter (productid, VariantID, VariantDisplayOrder, ProductName, VariantName, [rank])
SELECT distinct p.productid, pv.VariantID, pv.DisplayOrder, p.Name, pv.Name, ft.rank
FROM
product p with (nolock)
inner join #fts as ft on p.ProductID=ft.[key]
join ProductVariant pv with (NOLOCK) on p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType
left join productcategory pc with (nolock) on p.ProductID = pc.ProductID
left join ProductCustomerLevel pcl with (nolock) on p.ProductID = pcl.ProductID
WHERE
(pc.categoryid = @categoryID or @categoryID is null or @categorycount = 0)
and p.ProductTypeID = coalesce(@ProductTypeID, p.ProductTypeID)
and (case
when @FilterProductsByCustomerLevel = 0 or @custlevelcount = 0 or pcl.CustomerLevelID is null or @CustomerLevelID is null then 1
when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID then 1
when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1
else 0
end = 1
)
and case when isnull(pv.saleprice,0) = 0 then 0 else 1 end >= @OnSaleOnly
and p.published >= @publishedonly
and pv.published >= @publishedonly
and isnull(p.IsAPack, 0) <= 1-@ExcludePacks
and isnull(p.IsAKit, 0) <= 1-@ExcludeKits
and p.IsSystem <= 1-@ExcludeSysProds
and p.Deleted = 0
and pv.Deleted = 0
or patindex(@searchstr2, isnull(convert(nvarchar(20),p.productid), '')) > 0
order by ft.rank desc
END
ELSE BEGIN
INSERT @productfilter (productid, VariantID, VariantDisplayOrder, ProductName, VariantName)
SELECT distinct p.productid, pv.VariantID, pv.DisplayOrder, p.Name, pv.Name
FROM
product p with (nolock)
join ProductVariant pv with (NOLOCK) on p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType
left join productcategory pc with (nolock) on p.ProductID = pc.ProductID
left join ProductCustomerLevel pcl with (nolock) on p.ProductID = pcl.ProductID
WHERE
(pc.categoryid = @categoryID or @categoryID is null or @categorycount = 0)
and p.ProductTypeID = coalesce(@ProductTypeID, p.ProductTypeID)
and (case
when @FilterProductsByCustomerLevel = 0 or @custlevelcount = 0 or pcl.CustomerLevelID is null or @CustomerLevelID is null then 1
when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID then 1
when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1
else 0
end = 1
)
and (
@searchstr2 is null
or patindex('%'+@searchstr2+'%', isnull(p.name, '')) > 0
or patindex(@searchstr2, isnull(convert(nvarchar(20),p.productid), '')) > 0
or patindex('%'+@searchstr2+'%', isnull(pv.name, '')) > 0
or patindex('%'+@searchstr2+'%', isnull(p.sku , '')+isnull(pv.skusuffix , '')) > 0
or patindex('%'+@searchstr2+'%', isnull(p.manufacturerpartnumber, '')) > 0
or patindex('%'+@searchstr2+'%', isnull(pv.manufacturerpartnumber, '')) > 0
or (patindex('%'+@searchstr2+'%', isnull(p.Description, '')) > 0 and @extSearch = 1)
or (patindex('%'+@searchstr2+'%', isnull(p.Summary, '')) > 0 and @extSearch = 1)
)
and case when isnull(pv.saleprice,0) = 0 then 0 else 1 end >= @OnSaleOnly
and p.published >= @publishedonly
and pv.published >= @publishedonly
and isnull(p.IsAPack, 0) <= 1-@ExcludePacks
and isnull(p.IsAKit, 0) <= 1-@ExcludeKits
and p.IsSystem <= 1-@ExcludeSysProds
and p.Deleted = 0
and pv.Deleted = 0
END
END
ELSE BEGIN
INSERT @productfilter (productid, displayorder, VariantID, VariantDisplayOrder, ProductName, VariantName)
SELECT distinct p.productid, do.displayorder, pv.VariantID, pv.DisplayOrder, p.Name, pv.Name
FROM
product p with (nolock)
join #displayorder do on p.ProductID = do.ProductID
join ProductVariant pv with (NOLOCK) on p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType
left join productcategory pc with (nolock) on p.ProductID = pc.ProductID
left join ProductCustomerLevel pcl with (nolock) on p.ProductID = pcl.ProductID
WHERE
(pc.categoryid = @categoryID or @categoryID is null or @categorycount = 0)
and p.ProductTypeID = coalesce(@ProductTypeID, p.ProductTypeID)
and (case
when @FilterProductsByCustomerLevel = 0 or @custlevelcount = 0 or pcl.CustomerLevelID is null or @CustomerLevelID is null then 1
when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID then 1
when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1
else 0
end = 1
)
and (
@searchstr2 is null
or patindex('%'+@searchstr2+'%', isnull(p.name, '')) > 0
or patindex(@searchstr2, isnull(convert(nvarchar(20),p.productid), '')) > 0
or patindex('%'+@searchstr2+'%', isnull(pv.name, '')) > 0
or patindex('%'+@searchstr2+'%', isnull(p.sku , '')+isnull(pv.skusuffix , '')) > 0
or patindex('%'+@searchstr2+'%', isnull(p.manufacturerpartnumber, '')) > 0
or patindex('%'+@searchstr2+'%', isnull(pv.manufacturerpartnumber, '')) > 0
or (patindex('%'+@searchstr2+'%', isnull(p.Description, '')) > 0 and @extSearch = 1)
or (patindex('%'+@searchstr2+'%', isnull(p.Summary, '')) > 0 and @extSearch = 1)
)
and case when isnull(pv.saleprice,0) = 0 then 0 else 1 end >= @OnSaleOnly
and p.published >= @publishedonly
and pv.published >= @publishedonly
and isnull(p.IsAPack, 0) <= 1-@ExcludePacks
and isnull(p.IsAKit, 0) <= 1-@ExcludeKits
and p.IsSystem <= 1-@ExcludeSysProds
and p.Deleted = 0
and pv.Deleted = 0
order by do.displayorder, p.Name, pv.DisplayOrder, pv.Name
END
END
SET @rcount = @@rowcount
IF @StatsFirst = 1
SELECT cast(ceiling(@rcount*1.0/@pagesize) as int) pages, @rcount ProductCount
IF @searchstr is not null BEGIN
SELECT pf.rank,
p.ProductID,
p.Name,
pv.VariantID,
pv.Name AS VariantName,
p.ProductGUID,
p.Summary,
p.Description,
p.SEKeywords,
p.SEDescription,
p.SpecTitle,
p.MiscText,
p.SwatchImageMap,
p.IsFeaturedTeaser,
p.FroogleDescription,
p.SETitle,
p.SENoScript,
p.SEAltText,
p.SizeOptionPrompt,
p.ColorOptionPrompt,
p.TextOptionPrompt,
p.ProductTypeID,
p.TaxClassID,
p.SKU,
p.ManufacturerPartNumber,
p.SalesPromptID,
p.SpecCall,
p.SpecsInline,
p.IsFeatured,
p.XmlPackage,
p.ColWidth,
p.Published,
p.RequiresRegistration,
p.Looks,
p.Notes,
p.QuantityDiscountID,
p.RelatedProducts,
p.UpsellProducts,
p.UpsellProductDiscountPercentage,
p.RelatedDocuments,
p.TrackInventoryBySizeAndColor,
p.TrackInventoryBySize,
p.TrackInventoryByColor,
p.IsAKit,
p.ShowInProductBrowser,
p.IsAPack,
p.PackSize,
p.ShowBuyButton,
p.RequiresProducts,
p.HidePriceUntilCart,
p.IsCalltoOrder,
p.ExcludeFromPriceFeeds,
p.RequiresTextOption,
p.TextOptionMaxLength,
p.SEName,
p.Deleted,
p.CreatedOn,
p.ImageFileNameOverride,
pv.VariantGUID,
pv.Description AS VariantDescription,
pv.SEKeywords AS VariantSEKeywords,
pv.SEDescription AS VariantSEDescription,
pv.Colors,
pv.ColorSKUModifiers,
pv.Sizes,
pv.SizeSKUModifiers,
pv.FroogleDescription AS VariantFroogleDescription,
pv.SKUSuffix,
pv.ManufacturerPartNumber AS VariantManufacturerPartNumber,
pv.Price,
pv.CustomerEntersPrice,
pv.CustomerEntersPricePrompt,
isnull(pv.SalePrice, 0) SalePrice,
cast(isnull(pv.Weight,0) as decimal(10,1)) Weight,
pv.MSRP,
pv.Cost,
isnull(pv.Points,0) Points,
pv.Dimensions,
case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end Inventory,
pv.DisplayOrder as VariantDisplayOrder,
pv.Notes AS VariantNotes,
pv.IsTaxable,
pv.IsShipSeparately,
pv.IsDownload,
pv.DownloadLocation,
pv.Published AS VariantPublished,
pv.IsSecureAttachment,
pv.IsRecurring,
pv.RecurringInterval,
pv.RecurringIntervalType,
pv.SubscriptionInterval,
pv.SEName AS VariantSEName,
pv.RestrictedQuantities,
pv.MinimumQuantity,
pv.Deleted AS VariantDeleted,
pv.CreatedOn AS VariantCreatedOn,
d.Name AS DistributorName,
d.DistributorID,
d.SEName AS DistributorSEName,
m.ManufacturerID,
m.Name AS ManufacturerName,
m.SEName AS ManufacturerSEName,
s.Name AS SalesPromptName,
case when pcl.productid is null then 0 else isnull(ep.Price, 0) end ExtendedPrice,
p.ExtensionData,
p.ExtensionData2,
p.ExtensionData3,
p.ExtensionData4,
p.ExtensionData5
FROM dbo.Product p with (NOLOCK)
left join dbo.ProductVariant pv with (NOLOCK) on p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType
join @productfilter pf on pv.ProductID = pf.ProductID and pv.VariantID = pf.VariantID
left join dbo.SalesPrompt s with (NOLOCK) on p.SalesPromptID = s.SalesPromptID
left join dbo.ProductManufacturer pm with (NOLOCK) on p.ProductID = pm.ProductID
left join dbo.Manufacturer m with (NOLOCK) on pm.ManufacturerID = m.ManufacturerID
left join dbo.ProductDistributor pd with (NOLOCK) on p.ProductID = pd.ProductID
left join dbo.Distributor d with (NOLOCK) on pd.DistributorID = d.DistributorID
left join dbo.ExtendedPrice ep with (NOLOCK) on ep.VariantID = pv.VariantID and ep.CustomerLevelID = @CustomerLevelID
left join dbo.ProductCustomerLevel pcl with (NOLOCK) on p.ProductID = pcl.ProductID and pcl.CustomerLevelID = @CustomerLevelID
left join (select VariantID, sum(quan) quan from dbo.Inventory with (nolock) group by VariantID) i on pv.VariantID = i.VariantID
WHERE pf.rownum >= @pagesize*(@pagenum-1)+1 and pf.rownum <= @pagesize*(@pagenum)
ORDER BY pf.rank desc
END
ELSE BEGIN
SELECT
p.ProductID,
p.Name,
pv.VariantID,
pv.Name AS VariantName,
p.ProductGUID,
p.Summary,
p.Description,
p.SEKeywords,
p.SEDescription,
p.SpecTitle,
p.MiscText,
p.SwatchImageMap,
p.IsFeaturedTeaser,
p.FroogleDescription,
p.SETitle,
p.SENoScript,
p.SEAltText,
p.SizeOptionPrompt,
p.ColorOptionPrompt,
p.TextOptionPrompt,
p.ProductTypeID,
p.TaxClassID,
p.SKU,
p.ManufacturerPartNumber,
p.SalesPromptID,
p.SpecCall,
p.SpecsInline,
p.IsFeatured,
p.XmlPackage,
p.ColWidth,
p.Published,
p.RequiresRegistration,
p.Looks,
p.Notes,
p.QuantityDiscountID,
p.RelatedProducts,
p.UpsellProducts,
p.UpsellProductDiscountPercentage,
p.RelatedDocuments,
p.TrackInventoryBySizeAndColor,
p.TrackInventoryBySize,
p.TrackInventoryByColor,
p.IsAKit,
p.ShowInProductBrowser,
p.IsAPack,
p.PackSize,
p.ShowBuyButton,
p.RequiresProducts,
p.HidePriceUntilCart,
p.IsCalltoOrder,
p.ExcludeFromPriceFeeds,
p.RequiresTextOption,
p.TextOptionMaxLength,
p.SEName,
p.Deleted,
p.CreatedOn,
p.ImageFileNameOverride,
pv.VariantGUID,
pv.Description AS VariantDescription,
pv.SEKeywords AS VariantSEKeywords,
pv.SEDescription AS VariantSEDescription,
pv.Colors,
pv.ColorSKUModifiers,
pv.Sizes,
pv.SizeSKUModifiers,
pv.FroogleDescription AS VariantFroogleDescription,
pv.SKUSuffix,
pv.ManufacturerPartNumber AS VariantManufacturerPartNumber,
pv.Price,
pv.CustomerEntersPrice,
pv.CustomerEntersPricePrompt,
isnull(pv.SalePrice, 0) SalePrice,
cast(isnull(pv.Weight,0) as decimal(10,1)) Weight,
pv.MSRP,
pv.Cost,
isnull(pv.Points,0) Points,
pv.Dimensions,
case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end Inventory,
pv.DisplayOrder as VariantDisplayOrder,
pv.Notes AS VariantNotes,
pv.IsTaxable,
pv.IsShipSeparately,
pv.IsDownload,
pv.DownloadLocation,
pv.Published AS VariantPublished,
pv.IsSecureAttachment,
pv.IsRecurring,
pv.RecurringInterval,
pv.RecurringIntervalType,
pv.SubscriptionInterval,
pv.SEName AS VariantSEName,
pv.RestrictedQuantities,
pv.MinimumQuantity,
pv.Deleted AS VariantDeleted,
pv.CreatedOn AS VariantCreatedOn,
d.Name AS DistributorName,
d.DistributorID,
d.SEName AS DistributorSEName,
m.ManufacturerID,
m.Name AS ManufacturerName,
m.SEName AS ManufacturerSEName,
s.Name AS SalesPromptName,
case when pcl.productid is null then 0 else isnull(ep.Price, 0) end ExtendedPrice,
p.ExtensionData,
p.ExtensionData2,
p.ExtensionData3,
p.ExtensionData4,
p.ExtensionData5
FROM Product p with (NOLOCK)
left join ProductVariant pv with (NOLOCK) on p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType
join @productfilter pf on pv.ProductID = pf.ProductID and pv.VariantID = pf.VariantID
left join SalesPrompt s with (NOLOCK) on p.SalesPromptID = s.SalesPromptID
left join ProductManufacturer pm with (NOLOCK) on p.ProductID = pm.ProductID
left join Manufacturer m with (NOLOCK) on pm.ManufacturerID = m.ManufacturerID
left join ProductDistributor pd with (NOLOCK) on p.ProductID = pd.ProductID
left join Distributor d with (NOLOCK) on pd.DistributorID = d.DistributorID
left join ExtendedPrice ep with (NOLOCK) on ep.VariantID = pv.VariantID and ep.CustomerLevelID = @CustomerLevelID
left join ProductCustomerLevel pcl with (NOLOCK) on p.ProductID = pcl.ProductID and pcl.CustomerLevelID = @CustomerLevelID
left join (select VariantID, sum(quan) quan from Inventory with (nolock) group by VariantID) i on pv.VariantID = i.VariantID
WHERE pf.rownum >= @pagesize*(@pagenum-1)+1 and pf.rownum <= @pagesize*(@pagenum)
ORDER BY pf.rownum
END
IF @StatsFirst <> 1
SELECT cast(ceiling(@rcount*1.0/@pagesize) as int) pages, @rcount ProductCount
END
As you may notice there is a there is SQL variable called searchstr2. It is used so I can use both full text search and the regular search. Some times the regular search is better to use when it comes to part numbers for me. This is because many of our part numbers have a "-" in them. Since "-" is considered white text it doesn't return the result I want for that search. For this reason the code behind and search xml package are also edited. The xmlpackage is simply adding SearchTerm2 to the sql statment and runtime. The code behind is a little be more complex. Due also note that my search page uses a aspdnsf:XmlPackage control but is should be easy to port over the code to the litSearch if that is used for output instead. Here is that code in C#:
Full Text Search Question
Could you please elaborate
Originally Posted by mmcgeachy
Copyright © 2012, AspDotNetStorefront. All rights reserved.