02.00.06.SqlDataProvider

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


if exists (select * from dbo.syscolumns where id=object_id(N'{databaseOwner}[{objectQualifier}NB_Store_ProductImage]') and name='ImagePath')
	alter table {databaseOwner}{objectQualifier}NB_Store_ProductImage alter column ImagePath nvarchar(256) null
go


if not exists (select * from dbo.syscolumns where id=object_id(N'{databaseOwner}[{objectQualifier}NB_Store_Products]') and name='ModifiedDate')
	alter table {databaseOwner}{objectQualifier}NB_Store_Products add ModifiedDate datetime null
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_Orders_GetExportList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Orders_GetExportList]
GO

/*
Author: DCL
Last Modified for NB_Store version: 02.00.06
*/
CREATE    PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Orders_GetExportList]
@PortalID int,
@StatusID int
AS
begin

if @StatusID = -1 
begin
	select * 
	from {databaseOwner}[{objectQualifier}NB_Store_Orders] 
	where PortalID = @PortalID
	order by OrderDate desc
end
else
begin
	select * 
	from {databaseOwner}[{objectQualifier}NB_Store_Orders] 
	where OrderStatusID = @StatusID
	and PortalID = @PortalID
	order by OrderDate desc
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_ProductImage_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_ProductImage_Update]
GO

/*
Author: DCL
Last Modified for NB_Store version: 02.00.06
*/
CREATE   PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_ProductImage_Update]
(
@ImageID int,
@ProductID int,
@ImagePath nvarchar(256),
@ListOrder int,
@Hidden bit,
@Lang nchar(5),
@ImageDesc nvarchar(200),
@ImageURL nvarchar(256)
)
AS
BEGIN

	if not exists (select ImageID from {databaseOwner}[{objectQualifier}NB_Store_ProductImage] where ImageID = @ImageID)
	begin
		insert into {databaseOwner}[{objectQualifier}NB_Store_ProductImage]
		(
		ProductID,
		ImagePath,
		ListOrder,
		Hidden,
		ImageURL
		)
		values
		(
		@ProductID,
		@ImagePath,
		@ListOrder,
		@Hidden,
		@ImageURL
		)
		
		set @ImageID = @@Identity
	
	end
	else
	begin
		Update {databaseOwner}[{objectQualifier}NB_Store_ProductImage]
		set 
		ProductID = @ProductID,
		ImagePath = @ImagePath,
		ListOrder = @ListOrder,
		Hidden = @Hidden,
		ImageURL = @ImageURL
		where ImageID = @ImageID
 
	end

	exec {databaseOwner}{objectQualifier}NEvoweb_NB_Store_ProductImageLang_Update @ImageID,@Lang,@ImageDesc

	select @ImageID

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

/*
Author: DCL
Last Modified for NB_Store version: 02.00.06
*/
CREATE   PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_ProductImage_UpdateImageOnly]
(
@ImageID int,
@ProductID int,
@ImagePath nvarchar(256),
@ListOrder int,
@Hidden bit,
@ImageURL nvarchar(256)
)
AS
BEGIN

	if exists (select ImageID from {databaseOwner}[{objectQualifier}NB_Store_ProductImage] where ImageID = @ImageID)
	begin
		Update {databaseOwner}[{objectQualifier}NB_Store_ProductImage]
		set 
		ProductID = @ProductID,
		ImagePath = @ImagePath,
		ListOrder = @ListOrder,
		Hidden = @Hidden,
		ImageURL = @ImageURL
		where ImageID = @ImageID 
	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_Get]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Products_Get]
GO


/*
Author: DCL
Last Modified for NB_Store version: 02.00.06
*/
CREATE    PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Products_Get]
@ProductID int,
@Lang char(5)
AS
begin
	select 
		P.ProductID,
		PortalID,
		P.TaxCategoryID,
		Featured,
		Archived,
		CreatedByUser,
		CreatedDate,
		ModifiedDate,
		IsDeleted,
		ProductRef,
		PL.Lang,
		Summary,
		Description,
		Manufacturer,
		{databaseOwner}{objectQualifier}NEvoWeb_NB_Store_FGetProductBasePrice(P.ProductID) as FromPrice,
		isnull((select sum(QtyRemaining) from {databaseOwner}{objectQualifier}NB_Store_Model where productid = P.ProductID and qtyremaining != 0),0) as QtyRemaining,
	    isnull((select sum(QtyStockSet) from {databaseOwner}{objectQualifier}NB_Store_Model where productid = P.ProductID),0) as QtyStockSet,
		(select top 1 ImageID from {databaseOwner}{objectQualifier}NB_Store_ProductImage AS I where productid = P.ProductID order by listorder) as ImageID,
		(select top 1 ImageURL from {databaseOwner}{objectQualifier}NB_Store_ProductImage AS I where productid = P.ProductID order by listorder) as ImageURL,
		(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,
		PL.ProductName,
		PL.[XMLData]
	from {databaseOwner}[{objectQualifier}NB_Store_Products] as P
	inner join {databaseOwner}{objectQualifier}NB_Store_ProductLang as PL on P.ProductID = PL.ProductID and PL.Lang = @Lang
	where P.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_Products_GetByRef]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Products_GetByRef]
GO


/*
Author: DCL
Last Modified for NB_Store version: 02.00.06
*/
CREATE     PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Products_GetByRef]
@PortalID int,
@ProductRef nvarchar(20),
@Lang char(5)
AS
begin
	select top 1 
		P.ProductID,
		PortalID,
		P.TaxCategoryID,
		Featured,
		Archived,
		CreatedByUser,
		CreatedDate,
		ModifiedDate,
		IsDeleted,
		ProductRef,
		Lang,
		Summary,
		Description,
		Manufacturer,
		{databaseOwner}{objectQualifier}NEvoWeb_NB_Store_FGetProductBasePrice(P.ProductID) as FromPrice,
		isnull((select sum(QtyRemaining) from {databaseOwner}{objectQualifier}NB_Store_Model where productid = P.ProductID and qtyremaining != 0),0) as QtyRemaining,
	    isnull((select sum(QtyStockSet) from {databaseOwner}{objectQualifier}NB_Store_Model where productid = P.ProductID),0) as QtyStockSet,
		(select top 1 ImageID from {databaseOwner}{objectQualifier}NB_Store_ProductImage AS I where productid = P.ProductID) as ImageID,
		(select top 1 ImageURL from {databaseOwner}{objectQualifier}NB_Store_ProductImage AS I where productid = P.ProductID) as ImageURL,
		(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) as ImageDesc,
		PL.ProductName,
		PL.[XMLData]
	from {databaseOwner}[{objectQualifier}NB_Store_Products] as P
	inner join {databaseOwner}{objectQualifier}NB_Store_ProductLang as PL on P.ProductID = PL.ProductID and PL.Lang = @Lang
	where P.ProductRef = @ProductRef
	and P.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_Products_GetExportList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Products_GetExportList]
GO


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

CREATE     PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Products_GetExportList]
@PortalID int,
@Lang nvarchar(5),
@GetDeletedOnly bit
AS
begin

	declare @SQL nvarchar(2000)

	set @SQL = 'select' 
	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 + ' PL.ProductName,'		
	set @SQL = @SQL + ' PL.[XMLData]'
	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 @GetDeletedOnly = 1
	begin
		set @SQL = @SQL + ' and P.IsDeleted = 1 '
	end

	set @SQL = @SQL + ' order by PL.Manufacturer,P.ProductRef,PL.ProductName'

	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_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.00.06
*/
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)
AS
begin

	declare @SQL nvarchar(2000)

	set @SQL = 'select' 

if @ReturnLimit > 0 
begin
	set @SQL = @SQL + ' top ' + @ReturnLimit
end

	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]'
	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 
	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 @FeaturedOnly = 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'
	end

	if @CategoryID > 0 
	begin
		set @SQL = @SQL + ' and PC.CategoryID = ' + convert(nvarchar(5),@CategoryID)
	end

	if @Filter != ''
	begin
		set @SQL = @SQL + ' and (PL.ProductName like ''%' + @Filter + '%'' or P.ProductRef like ''%' + @Filter + '%'' )'
	end


	if @OrderBy='man'
	begin
		if @OrderDESC=1
			set @SQL = @SQL + ' order by PL.Manufacturer DESC,P.ProductRef DESC,PL.ProductName DESC'
		else
			set @SQL = @SQL + ' order by PL.Manufacturer,P.ProductRef,PL.ProductName'
	end
	if @OrderBy='ref'
	begin
		if @OrderDESC=1
			set @SQL = @SQL + ' order by P.ProductRef DESC,PL.Manufacturer DESC,PL.ProductName DESC'
		else
			set @SQL = @SQL + ' order by P.ProductRef,PL.Manufacturer,PL.ProductName'
	end
	if @OrderBy='name'
	begin
		if @OrderDESC=1
			set @SQL = @SQL + ' order by PL.ProductName DESC,PL.Manufacturer DESC,P.ProductRef DESC'
		else
			set @SQL = @SQL + ' order by PL.ProductName,PL.Manufacturer,P.ProductRef'
	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,PL.ProductName DESC'
		else
			set @SQL = @SQL + ' order by {databaseOwner}{objectQualifier}NEvoWeb_NB_Store_FGetProductBasePrice(P.ProductID),PL.Manufacturer,P.ProductRef,PL.ProductName'
	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,PL.ProductName'
	end

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




/*
Author: DCL
Last Modified for NB_Store version: 02.00.06
*/
CREATE    PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Products_GetSearchList]
@PortalID int,
@Lang char(5),
@Word nvarchar(100),
@OrderBy nvarchar(50),
@OrderDESC bit,
@ReturnLimit nvarchar(5),
@DNNSearch bit
AS
begin

if @DNNSearch = 1
begin
	select distinct si.searchkey into #SearchTemp
	from {databaseOwner}{objectQualifier}SearchWord sw 
	INNER JOIN {databaseOwner}{objectQualifier}SearchItemWord siw ON sw.SearchWordsID = siw.SearchWordsID 
	INNER JOIN {databaseOwner}{objectQualifier}SearchItem si ON siw.SearchItemID = si.SearchItemID 
	inner join {databaseOwner}[{objectQualifier}NB_Store_Products] as P on si.SearchKey = (convert(nvarchar(10),P.ProductID) + @Lang) 
	inner join {databaseOwner}{objectQualifier}NB_Store_ProductLang as PL on P.ProductID = PL.ProductID and PL.Lang = @Lang  
	WHERE sw.Word collate Latin1_General_CI_AI = @Word 
    and P.PortalID = @PortalID
end

	declare @SQL nvarchar(4000)

	set @SQL = 'select ' 

if @ReturnLimit > 0 
begin
	set @SQL = @SQL + ' top ' + @ReturnLimit
end

 set @SQL = @SQL + ' P.ProductID,'
 set @SQL = @SQL + ' P.PortalID,'
 set @SQL = @SQL + ' P.TaxCategoryID,'
 set @SQL = @SQL + ' P.Featured,'
 set @SQL = @SQL + ' P.Archived,'
 set @SQL = @SQL + ' P.CreatedByUser,'
 set @SQL = @SQL + ' P.CreatedDate,'
 set @SQL = @SQL + ' P.ModifiedDate,'
 set @SQL = @SQL + ' P.IsDeleted,'
 set @SQL = @SQL + ' P.ProductRef,'
 set @SQL = @SQL + ' PL.Lang,'
 set @SQL = @SQL + ' PL.Summary,'
 set @SQL = @SQL + ' PL.Description,'
 set @SQL = @SQL + ' PL.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]'

if @DNNSearch = 1
begin

set @SQL = @SQL + ' from {databaseOwner}[{objectQualifier}NB_Store_Products] 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 ((convert(nvarchar(10),P.ProductID) + ''' + @Lang + ''') in (select SearchKey from #searchTemp)) '
	set @SQL = @SQL + ' and P.PortalID = ' + convert(nvarchar(5),@PortalID)
	set @SQL = @SQL + ' and P.Isdeleted = 0 '
	set @SQL = @SQL + ' and P.Archived = 0 '

end 
else
begin

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 (PL.Summary collate Latin1_General_CI_AI like ''%' + @Word + '%'' '
	set @SQL = @SQL + ' or PL.Manufacturer collate Latin1_General_CI_AI like ''%' + @Word + '%'' '
	set @SQL = @SQL + ' or PL.ProductName collate Latin1_General_CI_AI like ''%' + @Word + '%'' '
	set @SQL = @SQL + ' or PL.Description collate Latin1_General_CI_AI like ''%' + @Word + '%'' '
	set @SQL = @SQL + ' or P.ProductRef collate Latin1_General_CI_AI like ''%' + @Word + '%'' )'
	set @SQL = @SQL + ' and P.PortalID = ' + convert(nvarchar(5),@PortalID)
end

	if @OrderBy='man'
	begin
		if @OrderDESC=1
			set @SQL = @SQL + ' order by PL.Manufacturer DESC,P.ProductRef DESC,PL.ProductName DESC'
		else
			set @SQL = @SQL + ' order by PL.Manufacturer,P.ProductRef,PL.ProductName'
	end
	if @OrderBy='ref'
	begin
		if @OrderDESC=1
			set @SQL = @SQL + ' order by P.ProductRef DESC,PL.Manufacturer DESC,PL.ProductName DESC'
		else
			set @SQL = @SQL + ' order by P.ProductRef,PL.Manufacturer,PL.ProductName'
	end
	if @OrderBy='name'
	begin
		if @OrderDESC=1
			set @SQL = @SQL + ' order by PL.ProductName DESC,PL.Manufacturer DESC,P.ProductRef DESC'
		else
			set @SQL = @SQL + ' order by PL.ProductName,PL.Manufacturer,P.ProductRef'
	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,PL.ProductName DESC'
		else
			set @SQL = @SQL + ' order by {databaseOwner}{objectQualifier}NEvoWeb_NB_Store_FGetProductBasePrice(P.ProductID),PL.Manufacturer,P.ProductRef,PL.ProductName'
	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.ProductName,PL.Manufacturer,P.ProductRef'
	end

	exec sp_executesql @SQL

if @DNNSearch = 1
begin
	drop table #SearchTemp
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_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Products_Update]
GO


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

CREATE   PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Products_Update]
(
@ProductID int,
@PortalID int,
@TaxCategoryID int,
@Featured bit,
@Archived bit,
@CreatedByUser nvarchar(100),
@CreatedDate datetime,
@IsDeleted bit,
@ProductRef nvarchar(20),
@Lang char(5),
@Summary nvarchar(1000),
@Description ntext,
@Manufacturer nvarchar(50),
@ProductName nvarchar(50),
@XMLdata xml
)
AS
BEGIN

	if not exists (select ProductID from {databaseOwner}[{objectQualifier}NB_Store_Products] where ProductID = @ProductID
 )
	begin
		insert into {databaseOwner}[{objectQualifier}NB_Store_Products]
		(
		PortalID,
		TaxCategoryID,
		Featured,
		Archived,
		CreatedByUser,
		CreatedDate,
		IsDeleted,
		ProductRef,
		ModifiedDate
		)
		values
		(
		@PortalID,
		@TaxCategoryID,
		@Featured,
		@Archived,
		@CreatedByUser,
		@CreatedDate,
		@IsDeleted,
		@ProductRef,
		getdate()
		)

		set @ProductID = @@Identity

		exec {databaseOwner}{objectQualifier}NEvoweb_NB_Store_ProductLang_Update @ProductID, @Lang, @Summary, @Description, @Manufacturer, @ProductName, @XMLdata

	end
	else
	begin
		Update {databaseOwner}[{objectQualifier}NB_Store_Products]
		set 
		PortalID = @PortalID,
		TaxCategoryID = @TaxCategoryID,
		Featured = @Featured,
		Archived = @Archived,
		CreatedByUser = @CreatedByUser,
		CreatedDate = @CreatedDate,
		IsDeleted = @IsDeleted,
		ProductRef = @ProductRef,
		ModifiedDate = getdate()
		where ProductID = @ProductID
 
		exec {databaseOwner}{objectQualifier}NEvoweb_NB_Store_ProductLang_Update @ProductID, @Lang, @Summary, @Description, @Manufacturer, @ProductName, @XMLdata

	end

exec {databaseOwner}{objectQualifier}NEvoweb_NB_Store_Products_Get @ProductID, @Lang
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO