02.01.07.SqlDataProvider

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

/************************************************************/
/*****    TABLE Start                                   *****/
/************************************************************/

if not exists (select * from dbo.syscolumns where id=object_id(N'{databaseOwner}[{objectQualifier}NB_Store_Model]') and name='Allow')
	alter table {databaseOwner}{objectQualifier}NB_Store_Model add [Allow] int not null default -1
go


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

CREATE TABLE {databaseOwner}[{objectQualifier}NB_Store_SearchWords] (
	[SearchWordID] [int] IDENTITY (1, 1) NOT NULL ,
	[PortalID] [int] NULL ,
	[SearchWord] [nvarchar] (50)  NULL ,
	[ExistsOnProduct] [bit] NULL ,
	[ExistsCount] [int] NULL ,
	[LastHitDate] [datetime] NULL ,
	CONSTRAINT [PK_NB_Store_SearchWords] PRIMARY KEY  CLUSTERED 
	(
		[SearchWordID]
	)   
) 
GO

 CREATE  INDEX [IX_NB_Store_SearchWords] ON {databaseOwner}[{objectQualifier}NB_Store_SearchWords]([SearchWord]) 
GO


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

CREATE TABLE {databaseOwner}[{objectQualifier}NB_Store_SearchWordHits] (
	[SearchWordHitID] [int] IDENTITY (1, 1) NOT NULL ,
	[SearchWordID] [int] NULL ,
	[HitDate] [datetime] NULL ,
	[HitCount] [int] NULL ,
	[WordPosition] [int] NULL ,
	[WordRank] AS ([WordPosition]/[Hitcount]) ,
	CONSTRAINT [PK_NB_Store_SearchWordHits] PRIMARY KEY  CLUSTERED 
	(
		[SearchWordHitID]
	)   ,
	CONSTRAINT [FK_NB_Store_SearchWordHits_NB_Store_SearchWords] FOREIGN KEY 
	(
		[SearchWordID]
	) REFERENCES {databaseOwner}[{objectQualifier}NB_Store_SearchWords] (
		[SearchWordID]
	) ON DELETE CASCADE 
) 
GO

 CREATE  INDEX [IX_NB_Store_SearchWordHits] ON {databaseOwner}[{objectQualifier}NB_Store_SearchWordHits]([HitDate] DESC ) 
GO






/************************************************************/
/*****    SPROC Start                                   *****/
/************************************************************/

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_Model_Get]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Model_Get]
GO




/*
Author: DCL
Last Modified for NB_Store version: 02.01.07
*/
CREATE     PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Model_Get]
@ModelID int,
@Lang nchar(5)
AS
begin
	select 
		M.ModelID,
		M.ProductID,
		M.ListOrder,
		M.UnitCost,
		M.Barcode,
		M.ModelRef,
		M.Deleted,
		ML.Lang,
		ML.ModelName,
		QtyRemaining,
		QtyTrans,
		QtyTransDate,
		PL.ProductName,
		P.PortalID,
		isnull(SR.ProductWeight,0) as Weight,
		isnull(SR.ProductHeight,0) as Height,
		isnull(SR.ProductLength,0) as Length,
		isnull(SR.ProductWidth,0) as Width,
		M.QtyStockSet,
		M.DealerCost,
		M.DealerOnly,
		M.PurchaseCost,
		ML.[XMLData],
		ML.Extra,
		M.Allow
	from {databaseOwner}[{objectQualifier}NB_Store_Model] as M
	inner join {databaseOwner}[{objectQualifier}NB_Store_Products] as P on P.ProductID = M.ProductID
	left outer join {databaseOwner}[{objectQualifier}NB_Store_ModelLang] as ML on ML.ModelID = M.ModelID and ML.Lang = @Lang
	left outer join {databaseOwner}[{objectQualifier}NB_Store_ProductLang] as PL on PL.ProductID = M.ProductID and PL.Lang = @Lang
	left outer join {databaseOwner}[{objectQualifier}NB_Store_ShippingRates] as SR on SR.ObjectID = M.ModelID and ShipType='PRD' and SR.[Disable]=0	
	where M.ModelID = @ModelID
 end

 









GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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_Model_GetByRef]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Model_GetByRef]
GO





/*
Author: DCL
Last Modified for NB_Store version: 02.01.07
*/
CREATE    PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Model_GetByRef]
@ProductID int,
@ModelRef nvarchar(20),
@Lang nchar(5)
AS
begin
	select top 1 
		M.ModelID,
		M.ProductID,
		M.ListOrder,
		M.UnitCost,
		M.Barcode,
		M.ModelRef,
		M.Deleted,
		ML.Lang,
		ML.ModelName,
		QtyRemaining,
		QtyTrans,
		QtyTransDate,
		PL.ProductName,
		P.PortalID,
		isnull(SR.ProductWeight,0) as Weight,
		isnull(SR.ProductHeight,0) as Height,
		isnull(SR.ProductLength,0) as Length,
		isnull(SR.ProductWidth,0) as Width,
		M.QtyStockSet,
		M.DealerCost,
		M.DealerOnly,
		M.PurchaseCost,
		ML.[XMLData],
		ML.Extra,
		M.Allow
	from {databaseOwner}[{objectQualifier}NB_Store_Model] as M
	inner join {databaseOwner}[{objectQualifier}NB_Store_Products] as P on P.ProductID = M.ProductID
	left outer join {databaseOwner}[{objectQualifier}NB_Store_ModelLang] as ML on ML.ModelID = M.ModelID and ML.Lang = @Lang
	left outer join {databaseOwner}[{objectQualifier}NB_Store_ProductLang] as PL on PL.ProductID = M.ProductID and PL.Lang = @Lang
	left outer join {databaseOwner}[{objectQualifier}NB_Store_ShippingRates] as SR on SR.ObjectID = M.ModelID and ShipType='PRD' and SR.[Disable]=0	
	where M.ModelRef = @ModelRef
	and M.ProductID = @ProductID
 end







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


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_Model_GetInStockList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Model_GetInStockList]
GO






/*
Author: DCL
Last Modified for NB_Store version: 02.01.07
*/
CREATE    PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Model_GetInStockList]
@ProductID int,
@Lang nchar(5),
@IsDealer as bit
AS
begin

	select 
		M.ModelID,
		M.ProductID,
		M.ListOrder,
		M.UnitCost,
		M.Barcode,
		M.ModelRef,
		M.Deleted,
		ML.Lang,
		ML.ModelName,
		QtyRemaining,
		QtyTrans,
		QtyTransDate,
		PL.ProductName,
		P.PortalID,
		isnull(SR.ProductWeight,0) as Weight,
		isnull(SR.ProductHeight,0) as Height,
		isnull(SR.ProductLength,0) as Length,
		isnull(SR.ProductWidth,0) as Width,
		M.QtyStockSet,
		M.DealerCost,
		M.DealerOnly,
		M.PurchaseCost,
		ML.[XMLData],
		ML.Extra,
		M.Allow			
	from {databaseOwner}[{objectQualifier}NB_Store_Model] as M
	inner join {databaseOwner}[{objectQualifier}NB_Store_Products] as P on P.ProductID = M.ProductID
	left outer join {databaseOwner}[{objectQualifier}NB_Store_ModelLang] as ML on ML.ModelID = M.ModelID and ML.Lang = @Lang
	left outer join {databaseOwner}[{objectQualifier}NB_Store_ProductLang] as PL on PL.ProductID = M.ProductID and PL.Lang = @Lang
	left outer join {databaseOwner}[{objectQualifier}NB_Store_ShippingRates] as SR on SR.ObjectID = M.ModelID and ShipType='PRD' and SR.[Disable]=0	
	where M.ProductID = @ProductID	
	and (QtyRemaining > 0 or QtyRemaining < 0)
	and (@IsDealer = 1 or DealerOnly = 0)
	order by M.ListOrder
 
end








GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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_Model_GetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Model_GetList]
GO





/*
Author: DCL
Last Modified for NB_Store version: 02.01.07
*/
CREATE     PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Model_GetList]
@PortalID int,
@ProductID int,
@Lang nchar(5),
@IsDealer as bit
AS
begin

	select 
		M.ModelID,
		M.ProductID,
		M.ListOrder,
		M.UnitCost,
		M.Barcode,
		M.ModelRef,
		M.Deleted,
		ML.Lang,
		ML.ModelName,
		QtyRemaining,
		QtyTrans,
		QtyTransDate,
		PL.ProductName,
		P.PortalID,
		isnull(SR.ProductWeight,0) as Weight,
		isnull(SR.ProductHeight,0) as Height,
		isnull(SR.ProductLength,0) as Length,
		isnull(SR.ProductWidth,0) as Width,
		M.QtyStockSet,
		M.DealerCost,
		M.DealerOnly,
		M.PurchaseCost,
		ML.[XMLData],
		ML.Extra,
		M.Allow
	from {databaseOwner}[{objectQualifier}NB_Store_Model] as M
	inner join {databaseOwner}[{objectQualifier}NB_Store_Products] as P on P.ProductID = M.ProductID
	left outer join {databaseOwner}[{objectQualifier}NB_Store_ModelLang] as ML on ML.ModelID = M.ModelID and ML.Lang = @Lang
	left outer join {databaseOwner}[{objectQualifier}NB_Store_ProductLang] as PL on PL.ProductID = M.ProductID and PL.Lang = @Lang
	left outer join {databaseOwner}[{objectQualifier}NB_Store_ShippingRates] as SR on SR.ObjectID = M.ModelID and ShipType='PRD' and SR.[Disable]=0	
	where M.ProductID = @ProductID	
	and (P.PortalID = @PortalID or @PortalID = -1)
	and (@IsDealer = 1 or DealerOnly = 0)
	order by M.ListOrder
 
end





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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_Model_GetStockList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Model_GetStockList]
GO






/*
Author: DCL
Last Modified for NB_Store version: 02.01.07
*/
CREATE     PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Model_GetStockList]
@PortalID int,
@Filter nvarchar(50),
@Lang nchar(5),
@CategoryID int,
@PageIndex int,
@PageSize int,
@IsDealer as bit
AS
begin

	declare @SQL nvarchar(4000)

set @SQL = ' SELECT '
set @SQL = @SQL + ' top ' + convert(nvarchar(5),@PageSize)

set @SQL = @SQL + ' row,ModelID,ProductID,TaxCategoryID,ListOrder,UnitCost,Barcode,ModelRef,Deleted,Lang,ModelName,QtyRemaining,QtyTrans,QtyTransDate,ProductName,PortalID,QtyStockSet,DealerCost,DealerOnly,PurchaseCost,Extra,Allow'
set @SQL = @SQL + ' FROM ' 
set @SQL = @SQL + ' (select '

set @SQL = @SQL + ' ROW_NUMBER() OVER (order by PL.ProductName,M.ListOrder) AS row, '


set @SQL = @SQL + ' 		M.ModelID,'
set @SQL = @SQL + ' 		M.ProductID,'
set @SQL = @SQL + ' 		P.TaxCategoryID,'
set @SQL = @SQL + ' 		M.ListOrder,'
set @SQL = @SQL + ' 		M.UnitCost,'
set @SQL = @SQL + ' 		M.Barcode,'
set @SQL = @SQL + ' 		M.ModelRef,'
set @SQL = @SQL + ' 		M.Deleted,'
set @SQL = @SQL + ' 		ML.Lang,'
set @SQL = @SQL + ' 		ML.ModelName,'
set @SQL = @SQL + ' 		QtyRemaining,'
set @SQL = @SQL + ' 		QtyTrans,'
set @SQL = @SQL + ' 		QtyTransDate,'
set @SQL = @SQL + ' 		PL.ProductName,'
set @SQL = @SQL + ' 		P.PortalID,'
set @SQL = @SQL + ' 		M.QtyStockSet,'
set @SQL = @SQL + ' 		M.DealerCost,'
set @SQL = @SQL + ' 		M.DealerOnly,'
set @SQL = @SQL + ' 		M.PurchaseCost,'
set @SQL = @SQL + ' 		ML.Extra,'
set @SQL = @SQL + ' 		M.Allow'
set @SQL = @SQL + ' 	from {databaseOwner}[{objectQualifier}NB_Store_Model] as M'
set @SQL = @SQL + ' 	left outer join {databaseOwner}[{objectQualifier}NB_Store_ModelLang] as ML on ML.ModelID = M.ModelID and ML.Lang = ''' + @Lang + ''''
set @SQL = @SQL + ' 	inner join {databaseOwner}{objectQualifier}NB_Store_Products as P on P.ProductID = M.ProductID'
set @SQL = @SQL + ' 	left outer join {databaseOwner}[{objectQualifier}NB_Store_ProductLang] as PL on PL.ProductID = M.ProductID and PL.Lang = ''' + @Lang + ''''

if @CategoryID > -1
begin
	set @SQL = @SQL + ' 	left join {databaseOwner}{objectQualifier}NB_Store_ProductCategory as PC on PC.ProductID = P.ProductID'
end

-- START SELCECT SECTION -------------------------------------------------------------
set @SQL = @SQL + ' where P.PortalID = ' + convert(nvarchar(5),@PortalID)
set @SQL = @SQL + ' and isnull(M.Deleted,0) = 0'
set @SQL = @SQL + ' and isnull(P.Archived,0) = 0'

if @IsDealer = 0
begin
	set @SQL = @SQL + ' 	and DealerOnly = 0 '
end

if @CategoryID > -1
begin
	set @SQL = @SQL + ' and PC.CategoryID = ' + convert(nvarchar(6),@CategoryID)
end

if @Filter != ''
begin
	set @SQL = @SQL + ' and (ML.Modelname like ''%' + @Filter + '%'' or M.Modelref like ''%' + @Filter + '%'' or PL.ProductName like ''%' + @Filter + '%'' or M.Barcode like ''%' + @Filter + '%'') '
end
-- END SELCECT SECTION -------------------------------------------------------------


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

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

print @sql

	exec sp_executesql @SQL

end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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_Model_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Model_Update]
GO




/*
Author: DCL
Last Modified for NB_Store version: 02.01.07
*/
CREATE   PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Model_Update]
(
@ModelID int,
@ProductID int,
@ListOrder int,
@UnitCost money,
@Barcode nvarchar(20),
@ModelRef nvarchar(20),
@Lang nchar(5),
@ModelName nvarchar(50),
@QtyRemaining int,
@QtyTrans int,
@QtyTransDate datetime,
@Deleted bit,
@QtyStockSet int,
@DealerCost money,
@PurchaseCost money,
@XMLData xml,
@Extra nvarchar(50),
@DealerOnly bit,
@Allow int
)
AS
BEGIN

	if not exists (select ModelID from {databaseOwner}[{objectQualifier}NB_Store_Model] where ModelID = @ModelID
 )
	begin
		insert into {databaseOwner}[{objectQualifier}NB_Store_Model]
		(
		ProductID,
		ListOrder,
		UnitCost,
		Barcode,
		ModelRef,
		QtyRemaining,
		QtyTrans,
		QtyTransDate,
		Deleted,
		QtyStockSet,
		DealerCost,
		PurchaseCost,
		DealerOnly,
		Allow
		)
		values
		(
		@ProductID,
		@ListOrder,
		@UnitCost,
		@Barcode,
		@ModelRef,
		@QtyRemaining,
		@QtyTrans,
		@QtyTransDate,
		@Deleted,
		@QtyStockSet,
		@DealerCost,
		@PurchaseCost,
		@DealerOnly,
		@Allow
		)
		
		set @ModelID = @@Identity
	
		exec {databaseOwner}{objectQualifier}NEvoweb_NB_Store_ModelLang_Update @ModelID, @Lang, @ModelName, @XMLData, @Extra

	end
	else
	begin
		Update {databaseOwner}[{objectQualifier}NB_Store_Model]
		set 
		ProductID = @ProductID,
		ListOrder = @ListOrder,
		UnitCost = @UnitCost,
		Barcode = @Barcode,
		ModelRef = @ModelRef,
		QtyRemaining = @QtyRemaining,
		QtyTrans = @QtyTrans,
		QtyTransDate = @QtyTransDate,
		Deleted = @Deleted,
		QtyStockSet = @QtyStockSet,
		DealerCost = @DealerCost,
		PurchaseCost = @PurchaseCost,
		DealerOnly = @DealerOnly,
		Allow = @Allow
		where ModelID = @ModelID

		exec {databaseOwner}{objectQualifier}NEvoweb_NB_Store_ModelLang_Update @ModelID, @Lang, @ModelName, @XMLData, @Extra
 
	end

	select @ModelID

END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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_SearchWords_Process]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_SearchWords_Process]
GO




/*
Author: DCL
Last Modified for NB_Store version: 02.01.07
*/

CREATE   PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_SearchWords_Process]
(
@PortalID int
)
AS
begin

-- Update exist count ---
update {databaseOwner}{objectQualifier}NB_Store_SearchWords
set ExistsCount = 
(select count(P.ProductID) from {databaseOwner}{objectQualifier}NB_Store_Products as P
inner join {databaseOwner}{objectQualifier}NB_Store_ProductLang as PL on PL.ProductID = P.ProductID
where PortalID = @PortalID
and (PL.ProductName like '%' + SW.SearchWord + '%' 
or P.ProductRef like '%' + SW.SearchWord + '%' 
or PL.Summary like '%' + SW.SearchWord + '%'
or PL.Manufacturer like '%' + SW.SearchWord + '%' 
or PL.TagWords like '%' + SW.SearchWord + '%'
or PL.Description like '%' + SW.SearchWord + '%')

)
from {databaseOwner}{objectQualifier}NB_Store_SearchWords as SW
where PortalID = @PortalID

-- Update exist flag ---
update {databaseOwner}{objectQualifier}NB_Store_SearchWords
set ExistsOnProduct = 1
from {databaseOwner}{objectQualifier}NB_Store_SearchWords as SW
where SW.ExistsCount > 0
and PortalID = @PortalID
 
end





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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_SearchWords_Purge]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_SearchWords_Purge]
GO



/*
Author: DCL
Last Modified for NB_Store version: 02.01.07
*/

CREATE   PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_SearchWords_Purge]
@PortalID int,
@PurgeBeforeDate datetime
AS
begin
	delete from {databaseOwner}[{objectQualifier}NB_Store_SearchWords] 
	where LastHitDate < @PurgeBeforeDate
	and PortalID = @PortalID 
end




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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_SearchWords_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_SearchWords_Update]
GO




/*
Author: DCL
Last Modified for NB_Store version: 02.01.07
*/
CREATE   PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_SearchWords_Update]
(
@PortalID int,
@SearchWord nvarchar(50),
@WordPosition int
)
AS
BEGIN

declare @ExistsOnProduct bit
declare @ExistsCount int

set @ExistsOnProduct = 0
set @ExistsCount = 0

declare @today as datetime
set @today = CONVERT(datetime, CONVERT(varchar(10), getdate(), 111) + ' 00:00:00', 111)

	if not exists (select SearchWordID from {databaseOwner}[{objectQualifier}NB_Store_SearchWords] where SearchWord = @SearchWord and PortalID = @PortalID)
	begin
		insert into {databaseOwner}[{objectQualifier}NB_Store_SearchWords]
		(
		PortalID,
		SearchWord,
		ExistsOnProduct,
		ExistsCount,
		LastHitDate
		)
		values
		(
		@PortalID,
		@SearchWord,
		@ExistsOnProduct,
		@ExistsCount,
		@today
		)


		insert into {databaseOwner}[{objectQualifier}NB_Store_SearchWordHits]
		(	
		SearchWordID,
		HitDate,
		HitCount,
		WordPosition
		)
		values
		(
		@@IDENTITY,
		@today,
		1,
		@WordPosition
		)


	end
	else
	begin
		Update {databaseOwner}[{objectQualifier}NB_Store_SearchWords]
		set 
		LastHitDate = @today
		where SearchWord = @SearchWord


		declare @SearchWordID int

		select top 1 @SearchWordID=SearchWordID from {databaseOwner}[{objectQualifier}NB_Store_SearchWords] where SearchWord = @SearchWord and PortalID = @PortalID

	if not exists (select SearchWordHitID from {databaseOwner}[{objectQualifier}NB_Store_SearchWordHits] where SearchWordID = @SearchWordID and HitDate = @today)
	begin
		insert into {databaseOwner}[{objectQualifier}NB_Store_SearchWordHits]
		(
		SearchWordID,
		HitDate,
		HitCount,
		WordPosition
		)
		values
		(
		@SearchWordID,
		@today,
		1,
		@WordPosition
		)
	end
	else
	begin
		Update {databaseOwner}[{objectQualifier}NB_Store_SearchWordHits]
		set 
		HitCount = (HitCount + 1),
		WordPosition = (WordPosition + @WordPosition)
		where SearchWordID = @SearchWordID and HitDate = @today 
	end


	end

END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


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.01.07
*/
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)

set @SQL = ' SELECT '
if @ReturnLimit > 0 
begin
	set @SQL = @SQL + ' top ' + @ReturnLimit
end
set @SQL = @SQL + ' row,ProductID,PortalID,TaxCategoryID,Featured,Archived,CreatedByUser,CreatedDate,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 + ''''

	if (@CategoryID > 0 and @Filter = '')
	begin
		set @SQL = @SQL + ' inner join {databaseOwner}{objectQualifier}NB_Store_ProductCategory as PC on PC.ProductID = P.ProductID '
	end	
	set @SQL = @SQL + ' where P.PortalID = ' + convert(nvarchar(5),@PortalID)

	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 (@CategoryID >= 0 and @CategoryList = '' and @Filter = '')
	begin
		set @SQL = @SQL + ' and PC.CategoryID = ' + convert(nvarchar(5),@CategoryID)
	end

	if (@CategoryID >= 0 and @CategoryList != '' and @Filter = '')
	begin
		set @SQL = @SQL + ' and PC.CategoryID in (' + convert(nvarchar(1000),@CategoryList) + ')'
	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 like ''%' + @Filter1 + '%'' or P.ProductRef like ''%' + @Filter1 + '%'' or PL.Summary like ''%' + @Filter1 + '%'' or PL.Manufacturer like ''%' + @Filter1 + '%'' or PL.TagWords like ''%' + @Filter1 + '%''  '
		if @SearchDescription = 1
		begin
			set @SQL = @SQL + ' or PL.Description 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 like ''%' + @Filter1 + '%'' or P.ProductRef like ''%' + @Filter1 + '%'' or PL.Summary like ''%' + @Filter1 + '%'' or PL.Manufacturer like ''%' + @Filter1 + '%'' or PL.TagWords like ''%' + @Filter1 + '%''  '
			if @SearchDescription = 1
			begin
				set @SQL = @SQL + ' or PL.Description 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(5),(@PageIndex - 1)) + ' * ' + convert(nvarchar(5),@PageSize) + ') + 1) and ' + convert(nvarchar(5),(@PageIndex*@PageSize))
end

print @SQL
	exec sp_executesql @SQL
 
end






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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_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.01.07
*/
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)

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 + ''''

	if (@CategoryID > 0 and @Filter = '')
	begin
		set @SQL = @SQL + ' inner join {databaseOwner}{objectQualifier}NB_Store_ProductCategory as PC on PC.ProductID = P.ProductID '
	end	
	set @SQL = @SQL + ' where P.PortalID = ' + convert(nvarchar(5),@PortalID)

	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 (@CategoryID >= 0 and @CategoryList = '' and @Filter = '')
	begin
		set @SQL = @SQL + ' and PC.CategoryID = ' + convert(nvarchar(5),@CategoryID)
	end

	if (@CategoryID >= 0 and @CategoryList != '' and @Filter = '')
	begin
		set @SQL = @SQL + ' and PC.CategoryID in (' + convert(nvarchar(1000),@CategoryList) + ')'
	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 like ''%' + @Filter1 + '%'' or P.ProductRef like ''%' + @Filter1 + '%'' or PL.Summary like ''%' + @Filter1 + '%'' or PL.Manufacturer like ''%' + @Filter1 + '%'' or PL.TagWords like ''%' + @Filter1 + '%''  '
		if @SearchDescription = 1
		begin
			set @SQL = @SQL + ' or PL.Description 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 like ''%' + @Filter1 + '%'' or P.ProductRef like ''%' + @Filter1 + '%'' or PL.Summary like ''%' + @Filter1 + '%'' or PL.Manufacturer like ''%' + @Filter1 + '%'' or PL.TagWords like ''%' + @Filter1 + '%''  '
			if @SearchDescription = 1
			begin
				set @SQL = @SQL + ' or PL.Description like ''%' + @Filter1 + '%'' '
			end
			set @SQL = @SQL + ')'

		end

	end

	exec sp_executesql @SQL
 
end







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO





/************************************************************/