02.03.07.SqlDataProvider


/************************************************************/
/*****              SqlDataProvider                     *****/
/*****                                                  *****/
/*****                                                  *****/
/***** Note: To manually execute this script you must   *****/
/*****       perform a search and replace operation     *****/
/*****       for {databaseOwner} and {objectQualifier}  *****/
/*****                                                  *****/
/*****                                                  *****/
/*****                                                  *****/
/************************************************************/

-------------------------------------------------------------------------------
--------------                       TABLES                        ------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
--------------                       SPROCS                        ------------
-------------------------------------------------------------------------------

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Products_GetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Products_GetList]
GO

/*
Author: DCL
Last Modified for NB_Store version: 02.03.00
*/
CREATE     PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Products_GetList]
@PortalID int,
@CategoryID int,
@Lang char(5),
@Filter nvarchar(50),
@GetArchived bit,
@FeaturedOnly bit,
@OrderBy nvarchar(50),
@OrderDESC bit,
@ReturnLimit nvarchar(5),
@PageIndex int,
@PageSize int,
@SearchDescription bit,
@IsDealer bit ,
@CategoryList nvarchar(1000),
@ExcludeFeatured bit
AS
begin

	declare @SQL nvarchar(4000)

-- clean up filter
set @Filter = replace(@Filter,'''','''''')

set @SQL = ' SELECT '
if @ReturnLimit > 0 
begin
	set @SQL = @SQL + ' top ' + @ReturnLimit
end
set @SQL = @SQL + ' row,ProductID,PortalID,TaxCategoryID,Featured,Archived,CreatedByUser,CreatedDate,ModifiedDate,IsDeleted,ProductRef,Lang,Summary,[Description],Manufacturer,FromPrice,QtyRemaining,QtyStockSet,ImageID,ImageURL,ImageDesc,ProductName,[XMLData],SEOName,TagWords,IsHidden  '
set @SQL = @SQL + ' FROM ' 
set @SQL = @SQL + ' (select ROW_NUMBER() OVER ('

	if @OrderBy='man'
	begin
		if @OrderDESC=1
			set @SQL = @SQL + ' order by PL.Manufacturer DESC,P.ProductRef DESC'
		else
			set @SQL = @SQL + ' order by PL.Manufacturer,P.ProductRef'
	end
	if @OrderBy='ref'
	begin
		if @OrderDESC=1
			set @SQL = @SQL + ' order by P.ProductRef DESC,PL.Manufacturer DESC'
		else
			set @SQL = @SQL + ' order by P.ProductRef,PL.Manufacturer'
	end
	if @OrderBy='name'
	begin
		if @OrderDESC=1
			set @SQL = @SQL + ' order by PL.ProductName DESC,PL.Manufacturer DESC'
		else
			set @SQL = @SQL + ' order by PL.ProductName,PL.Manufacturer'
	end
	if @OrderBy='price'
	begin
		if @OrderDESC=1
			set @SQL = @SQL + ' order by {databaseOwner}{objectQualifier}NEvoWeb_NB_Store_FGetProductBasePrice(P.ProductID) DESC,PL.Manufacturer DESC,P.ProductRef DESC'
		else
			set @SQL = @SQL + ' order by {databaseOwner}{objectQualifier}NEvoWeb_NB_Store_FGetProductBasePrice(P.ProductID),PL.Manufacturer,P.ProductRef'
	end
	if @OrderBy='cdate'
	begin
		if @OrderDESC=1
			set @SQL = @SQL + ' order by P.CreatedDate DESC'
		else
			set @SQL = @SQL + ' order by P.CreatedDate'
	end
	if @OrderBy=''
	begin
		set @SQL = @SQL + ' order by PL.Manufacturer,P.ProductRef'
	end
	set @SQL = @SQL + ') AS row, '
	set @SQL = @SQL + ' P.ProductID,'
	set @SQL = @SQL + ' PortalID,'
	set @SQL = @SQL + ' P.TaxCategoryID,'
	set @SQL = @SQL + ' Featured,'
	set @SQL = @SQL + ' Archived,'
	set @SQL = @SQL + ' CreatedByUser,'
	set @SQL = @SQL + ' CreatedDate,'
	set @SQL = @SQL + ' ModifiedDate,'
	set @SQL = @SQL + ' IsDeleted,'
	set @SQL = @SQL + ' ProductRef,'
	set @SQL = @SQL + ' Lang,'
	set @SQL = @SQL + ' Summary,'
	set @SQL = @SQL + ' Description,'
	set @SQL = @SQL + ' Manufacturer,'
	set @SQL = @SQL + ' {databaseOwner}{objectQualifier}NEvoWeb_NB_Store_FGetProductBasePrice(P.ProductID) as FromPrice,'
	set @SQL = @SQL + ' isnull((select sum(QtyRemaining) from {databaseOwner}{objectQualifier}NB_Store_Model where productid = P.ProductID and qtyremaining != 0),0) as QtyRemaining,'
	set @SQL = @SQL + ' isnull((select sum(QtyStockSet) from {databaseOwner}{objectQualifier}NB_Store_Model where productid = P.ProductID),0) as QtyStockSet,'
	set @SQL = @SQL + ' (select top 1 ImageID from {databaseOwner}{objectQualifier}NB_Store_ProductImage AS I where productid = P.ProductID order by listorder) as ImageID,'
	set @SQL = @SQL + ' (select top 1 ImageURL from {databaseOwner}{objectQualifier}NB_Store_ProductImage AS I where productid = P.ProductID order by listorder) as ImageURL,'
	set @SQL = @SQL + ' (select top 1 ImageDesc from {databaseOwner}{objectQualifier}NB_Store_ProductImage AS I inner join {databaseOwner}{objectQualifier}NB_Store_ProductImageLang as IL on IL.ImageID = I.ImageID and IL.Lang = PL.Lang where productid = P.ProductID order by listorder) as ImageDesc,'
	set @SQL = @SQL + ' PL.ProductName,'
	set @SQL = @SQL + ' PL.[XMLData], SEOName, TagWords, IsHidden '
	set @SQL = @SQL + ' from {databaseOwner}[{objectQualifier}NB_Store_Products] as P'
	set @SQL = @SQL + ' inner join {databaseOwner}{objectQualifier}NB_Store_ProductLang as PL on P.ProductID = PL.ProductID and PL.Lang = ''' + @Lang + ''''
	set @SQL = @SQL + ' where P.PortalID = ' + convert(nvarchar(5),@PortalID)

	
	if ((@CategoryID > 0 or @CategoryList != ''))
	begin
		if (substring(@CategoryList,1,1) = 'A')
		begin
		    set @CategoryList = replace(@CategoryList,'A','')
			
			-- Oh!!..the shame!!!
			--  OK we need this to calculate the number of categories a product must be included in to meet the AND specifications.
			DECLARE @counter int,@position int,@oldposition int
			SET @counter = 1
			SET @position = -2

			WHILE (@counter < LEN(@CategoryList))
			BEGIN

				SET @oldposition = @position
				SET @position = CHARINDEX(',',@CategoryList , @position + 1)
				IF @position = 0 AND @counter < LEN(@CategoryList)
				BEGIN
					SET @oldposition = 0
					BREAK
				END

				SET @counter = @counter + 1
			END
			
			set @SQL = @SQL + ' and ''' + convert(nvarchar(10),@counter) + ''' + ''|'' + convert(nvarchar(10),P.ProductID) in ( '
			set @SQL = @SQL + ' select convert(nvarchar(10),sum(1)) + ''|'' + convert(nvarchar(10),P.ProductID) from {databaseOwner}{objectQualifier}NB_Store_Products as P '
			set @SQL = @SQL + ' inner join {databaseOwner}{objectQualifier}NB_Store_ProductCategory as PC on P.ProductID = PC.ProductID '
			set @SQL = @SQL + ' and PC.CategoryID in (' + @CategoryList + ') '
			set @SQL = @SQL + ' group by P.ProductID ) '
		end 
		else
		begin
			-- filter needs to search ALL categories if or used, for backward compatiblity!
			if (@Filter = '')
			begin
				set @SQL = @SQL + ' and EXISTS (select ProductID from {databaseOwner}{objectQualifier}NB_Store_ProductCategory as PC where PC.ProductID = P.ProductID and PC.CategoryID in (' 
				if (@CategoryID > 0)
				begin
					set @SQL = @SQL + convert(nvarchar(10),@CategoryID)
					if (@CategoryList != '') set @SQL = @SQL + ','
				end
				set @SQL = @SQL + @CategoryList
				set @SQL = @SQL + ')) '
			end
		end
	end	
	
	if @IsDealer=0
	begin
		set @SQL = @SQL + ' and P.ProductID in (select M.ProductID from {databaseOwner}{objectQualifier}NB_Store_Model as M where M.ProductID = P.ProductID and DealerOnly = 0) '
	end

	if @FeaturedOnly = 1
	begin
		set @SQL = @SQL + ' and P.Featured = 1 '
	end

	if @ExcludeFeatured = 1
	begin
		set @SQL = @SQL + ' and P.Featured != 1 '
	end

	if @GetArchived = 0
	begin
		set @SQL = @SQL + ' and not P.IsDeleted = 1 and not P.Archived = 1 and not P.IsHidden = 1'
	end

	if @Filter != ''
	begin
		
		declare @Filter1 nvarchar(50)

		-- Search for whole filter
		if CHARINDEX('+', @Filter) = 0
		begin
			set @Filter1 = @Filter
		end
		else
		begin
			-- Search for first filter
			set @Filter1 = SUBSTRING(@Filter, 1, CHARINDEX('+', @Filter) - 1)
		end


set @SQL = @SQL + ' and (PL.ProductName collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' or P.ProductRef collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' or PL.Summary collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' or PL.Manufacturer collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' or PL.TagWords collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' '
if @SearchDescription = 1
begin
set @SQL = @SQL + ' or PL.Description collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' '
end
set @SQL = @SQL + ')'

-- Search for second word
if CHARINDEX('+', @Filter) > 0
begin

set @Filter1 = SUBSTRING(@Filter, CHARINDEX('+', @Filter) + 1, CHARINDEX('+', @Filter))

set @SQL = @SQL + ' and (PL.ProductName collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' or P.ProductRef collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' or PL.Summary collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' or PL.Manufacturer collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' or PL.TagWords collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' '
if @SearchDescription = 1
begin
set @SQL = @SQL + ' or PL.Description collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' '
end
set @SQL = @SQL + ')'

end 

	end

set @SQL = @SQL + ') AS NB_Store_ProductsPage '

if @PageSize > 0 
begin
	set @SQL = @SQL + ' WHERE Row between ((' + convert(nvarchar(15),(@PageIndex - 1)) + ' * ' + convert(nvarchar(15),@PageSize) + ') + 1) and ' + convert(nvarchar(15),(@PageIndex*@PageSize))
end

	exec sp_executesql @SQL
 
end
GO


if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Products_GetListSize]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Products_GetListSize]
GO


/*
Author: DCL
Last Modified for NB_Store version: 02.02.00
*/
CREATE PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Products_GetListSize]
@PortalID int,
@CategoryID int,
@Lang char(5),
@Filter nvarchar(50),
@GetArchived bit,
@FeaturedOnly bit,
@SearchDescription bit,
@IsDealer bit,
@CategoryList nvarchar(1000),
@ExcludeFeatured bit
AS
begin

	declare @SQL nvarchar(2000)

-- clean up filter
set @Filter = replace(@Filter,'''','''''')


set @SQL = 'select count(P.productid) '
	set @SQL = @SQL + ' from {databaseOwner}[{objectQualifier}NB_Store_Products] as P'
	set @SQL = @SQL + ' inner join {databaseOwner}{objectQualifier}NB_Store_ProductLang as PL on P.ProductID = PL.ProductID and PL.Lang = ''' + @Lang + ''''
	set @SQL = @SQL + ' where P.PortalID = ' + convert(nvarchar(5),@PortalID)

	if ((@CategoryID > 0 or @CategoryList != ''))
	begin
		if (substring(@CategoryList,1,1) = 'A')
		begin
		    set @CategoryList = replace(@CategoryList,'A','')
			
			-- Oh!!..the shame!!!
			--  OK we need this to calculate the number of categories a product must be included in to meet the AND specifications.
			DECLARE @counter int,@position int,@oldposition int
			SET @counter = 1
			SET @position = -2

			WHILE (@counter < LEN(@CategoryList))
			BEGIN

				SET @oldposition = @position
				SET @position = CHARINDEX(',',@CategoryList , @position + 1)
				IF @position = 0 AND @counter < LEN(@CategoryList)
				BEGIN
					SET @oldposition = 0
					BREAK
				END

				SET @counter = @counter + 1
			END
			
			set @SQL = @SQL + ' and ''' + convert(nvarchar(10),@counter) + ''' + ''|'' + convert(nvarchar(10),P.ProductID) in ( '
			set @SQL = @SQL + ' select convert(nvarchar(10),sum(1)) + ''|'' + convert(nvarchar(10),P.ProductID) from {databaseOwner}{objectQualifier}NB_Store_Products as P '
			set @SQL = @SQL + ' inner join {databaseOwner}{objectQualifier}NB_Store_ProductCategory as PC on P.ProductID = PC.ProductID '
			set @SQL = @SQL + ' and PC.CategoryID in (' + @CategoryList + ') '
			set @SQL = @SQL + ' group by P.ProductID ) '
		end 
		else
		begin
			-- filter needs to search ALL categories if or used, for backward compatiblity!
			if (@Filter = '')
			begin
				set @SQL = @SQL + ' and EXISTS (select ProductID from {databaseOwner}{objectQualifier}NB_Store_ProductCategory as PC where PC.ProductID = P.ProductID and PC.CategoryID in (' 
				if (@CategoryID > 0)
				begin
					set @SQL = @SQL + convert(nvarchar(10),@CategoryID)
					if (@CategoryList != '') set @SQL = @SQL + ','
				end
				set @SQL = @SQL + @CategoryList
				set @SQL = @SQL + ')) '
			end
		end
	end	

	if @IsDealer=0
	begin
		set @SQL = @SQL + ' and P.ProductID in (select M.ProductID from {databaseOwner}{objectQualifier}NB_Store_Model as M where M.ProductID = P.ProductID and DealerOnly = 0) '
	end

	if @FeaturedOnly = 1
	begin
		set @SQL = @SQL + ' and P.Featured = 1 '
	end

	if @ExcludeFeatured = 1
	begin
		set @SQL = @SQL + ' and P.Featured != 1 '
	end

	if @GetArchived = 0
	begin
		set @SQL = @SQL + ' and not P.IsDeleted = 1 and not P.Archived = 1 and not P.IsHidden = 1'
	end

	if @Filter != ''
	begin
		
		declare @Filter1 nvarchar(50)

		-- Search for whole filter
		if CHARINDEX('+', @Filter) = 0
		begin
			set @Filter1 = @Filter
		end
		else
		begin
			-- Search for first filter
			set @Filter1 = SUBSTRING(@Filter, 1, CHARINDEX('+', @Filter) - 1)
		end


set @SQL = @SQL + ' and (PL.ProductName collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' or P.ProductRef collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' or PL.Summary collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' or PL.Manufacturer collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' or PL.TagWords collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' '
if @SearchDescription = 1
begin
set @SQL = @SQL + ' or PL.Description collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' '
end
set @SQL = @SQL + ')'

-- Search for second word
if CHARINDEX('+', @Filter) > 0
begin

set @Filter1 = SUBSTRING(@Filter, CHARINDEX('+', @Filter) + 1, CHARINDEX('+', @Filter))

set @SQL = @SQL + ' and (PL.ProductName collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' or P.ProductRef collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' or PL.Summary collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' or PL.Manufacturer collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' or PL.TagWords collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' '
if @SearchDescription = 1
begin
set @SQL = @SQL + ' or PL.Description collate Latin1_General_CI_AI like ''%' + @Filter1 + '%'' '
end
set @SQL = @SQL + ')'

end 

	end

	exec sp_executesql @SQL
 
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

-------------------------------------------------------------------------------
--------------                       TABLES                        ------------
-------------------------------------------------------------------------------