USE [Adayroi_TMS]
GO
/****** Object:  StoredProcedure [dbo].[APIClient_ClientProduct_HybrisAddClientProduct_V05]    Script Date: 11/20/2017 4:37:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--====================================================================================
-- Entity Name:	APIClient_ClientProduct_AddClientProduct_V04
-- Author:	Hoàng Anh Thủy
-- Create date:	15/12/2014 8:32:27 PM
-- Description:	Thêm mới ClientProduct
-- ================================================================================
ALTER PROCEDURE [dbo].[APIClient_ClientProduct_HybrisAddClientProduct_V05]
(
	@SourceClientId	int,
	@HybrisProductItemId	varchar(30),
	@ProductImage	nvarchar(250),
	@ProductDescription	nvarchar(250),
	@ClientSKU	varchar(50),
	@ProductName	nvarchar(500),
	@MerchantSku	nvarchar(50),
	@ManufactureBarcode	varchar(50),
	@ProductUnit	nvarchar(50),
	@Weight	decimal(18,4),
	@Height	decimal(18, 4),
	@Length	decimal(18, 4),
	@Width	decimal(18, 4),
	@CBM	decimal(18, 4),
	@UpdateByLogistic	bit,
	@RequireSerial	bit,
	@MerchantProductName nvarchar(500) = NULL,
	@RelatedBarcode nvarchar(200) = null,
	@Brand  nvarchar(100) = null,
	@QtyPerUnit decimal(18,4),
	@ProductId int
)
AS
BEGIN
		DECLARE @ClientProductId INT = 0;
		DECLARE @tempWeight decimal(18,4);
		Declare @tempHeight decimal(18,4);
		Declare @tempLength decimal(18,4);
		Declare @tempWidth decimal(18,4);
		Declare @tempCBM decimal(18,4);
		SELECT top 1 @ClientProductId = [ClientProductId] ,
					@tempWeight = [Weight],
					@tempHeight = [Height],
					@tempLength = [Length],
					@tempWidth = Width,
					@tempCBM = CBM
		FROM [dbo].[ClientProduct] WITH(NOLOCK)
		WHERE [SourceClientId] = @SourceClientId AND
		[HybrisProductItemId] = @HybrisProductItemId AND
		--[ProductImage] = @ProductImage AND
		--[ProductDescription] = @ProductDescription AND
		[ClientSKU] = @ClientSKU AND
		[ProductName] = @ProductName AND
		[MerchantSku] = @MerchantSku AND
		[ManufactureBarcode] = @ManufactureBarcode AND
		[ProductId]=@ProductId
		--[ProductUnit] = @ProductUnit
		--[Weight] = @Weight AND
		--[Height] = @Height AND
		--[Length] = @Length AND
		--Width = @Width AND
		--CBM = @CBM AND
		--UpdateByLogistic = @UpdateByLogistic AND
		--RequireSerial = @RequireSerial

		IF (@ClientProductId IS NULL OR @ClientProductId<=0)
			BEGIN
				if(@tempWeight > 0)
					begin
						Set @Weight = @tempWeight;
					end
				IF(@tempHeight > 0)
					begin
						Set 	@Height = @tempHeight;
					end
				if(@tempLength > 0)
					begin
						Set  @Length = @tempLength;
					end
				if(@tempWidth > 0)
					begin
						Set @Width = @tempWidth;
					end
				if(@tempCBM > 0)
					begin
						Set @CBM = @tempCBM;
					end
				INSERT INTO [dbo].[ClientProduct]
								(
										 [SourceClientId]
										,[ProductItemId]
										,[ProductImage]
										,[ProductDescription]
										,[ClientSKU]
										,[ProductName]
										,[MerchantSku]
										,[ManufactureBarcode]
										,[ProductUnit]
										,[Weight]
										,[Height]
										,[Length]
										,[Width]
										,[CBM]
										,[UpdateByLogistic]
										,[RequireSerial]
										,[MerchantProductName]
										,RelatedBarcode
										,Brand
										,QtyPerUnit
										,ProductId
										,HybrisProductItemId
								)
				VALUES
								(
										 @SourceClientId
										,NULL
										,@ProductImage
										,@ProductDescription
										,@ClientSKU
										,@ProductName
										,@MerchantSku
										,@ManufactureBarcode
										,@ProductUnit
										,@Weight
										,@Height
										,@Length
										,@Width
										,@CBM
										,@UpdateByLogistic
										,@RequireSerial
										,@MerchantProductName
										,@RelatedBarcode
										,@Brand
										,@QtyPerUnit
										,@ProductId
										,@HybrisProductItemId
								)

				SET @ClientProductId = @@IDENTITY

				--DECLARE @xmlProduct NVARCHAR(MAX);
				--SET @xmlProduct=(SELECT		[ClientProductId]
				--							,[SourceClientId]
				--							,[ProductItemId]
				--							,[ProductImage]
				--							,[ProductDescription]
				--							,[ClientSKU]
				--							,[ProductName]
				--							,[MerchantSku]
				--							,[ManufactureBarcode]
				--							,[ProductUnit]
				--							,[Weight]
				--							,[Height]
				--							,[Length]
				--							,[Width]
				--							,[CBM]
				--							,[UpdateByLogistic]
				--							,[RequireSerial]
				--							,[MerchantProductName]
				--							,[RelatedBarcode]
				--							,[Brand]
				--							,[QtyPerUnit]
				--					FROM [ClientProduct]
				--					WHERE								ClientProductId = @ClientProductId
				--					FOR XML PATH('ReceiveUID'), ROOT('ArrayOfReceiveUID'))
			END
		ELSE
			IF NOT EXISTS(		SELECT top 1	[ClientProductId]
								FROM [dbo].[ClientProduct]
								WHERE		[SourceClientId]		=	@SourceClientId			AND
											[HybrisProductItemId]	=	@HybrisProductItemId	AND
											[ProductImage]			=	@ProductImage			AND
											[ProductDescription]	=	@ProductDescription		AND
											[ClientSKU]				=	@ClientSKU				AND
											--[ProductName]			=	@ProductName			AND
											[MerchantProductName]	=	@MerchantProductName	AND
											[MerchantSku]			=	@MerchantSku			AND
											[ManufactureBarcode]	=	@ManufactureBarcode		AND
											[ProductUnit]			=	@ProductUnit			AND
											[Weight]				=	@Weight					AND
											[Height]				=	@Height					AND
											[Length]				=	@Length					AND
											[Width]					=	@Width					AND
											[CBM]					=	@CBM					AND
											[UpdateByLogistic]		=	@UpdateByLogistic		AND
											[RequireSerial]			=	@RequireSerial			AND
											[MerchantProductName]	=	@MerchantProductName	AND
											[RelatedBarcode]		=	@RelatedBarcode			AND
											[Brand]					=	@Brand					AND
											[QtyPerUnit]			=	@QtyPerUnit
										--	[ProductId]				=@ProductId
						)
			BEGIN
				if(@tempWeight > 0)
					begin
						Set @Weight = @tempWeight;
					end
				IF(@tempHeight > 0)
					begin
						Set 	@Height = @tempHeight;
					end
				if(@tempLength > 0)
					begin
						Set  @Length = @tempLength;
					end
				if(@tempWidth > 0)
					begin
						Set @Width = @tempWidth;
					end
				if(@tempCBM > 0)
					begin
						Set @CBM = @tempCBM;
					end
				UPDATE	[ClientProduct]
					SET					[SourceClientId]		=	@SourceClientId,
										[HybrisProductItemId]	=	@HybrisProductItemId,
										[ProductImage]			=	@ProductImage ,
										[ProductDescription]	=	@ProductDescription,
										[ClientSKU]				=	@ClientSKU,
										[ProductName]			=	@ProductName,
										[MerchantSku]			=	@MerchantSku,
										[ManufactureBarcode]	=	@ManufactureBarcode,
										[ProductUnit]			=	@ProductUnit,
										[Weight]				=	@Weight,
										[Height]				=	@Height,
										[Length]				=	@Length,
										[Width]					=	@Width,
										[CBM]					=	@CBM,
										[UpdateByLogistic]		=	@UpdateByLogistic,
										[RequireSerial]			=	@RequireSerial,
										[MerchantProductName]	=	@MerchantProductName,
										[RelatedBarcode]		=	@RelatedBarcode,
										[Brand]					=	@Brand,
										[QtyPerUnit]			=	@QtyPerUnit  ,
										[ProductId]				=	@ProductId
			  FROM	[ClientProduct]
			  WHERE						ClientProductId			=	 @ClientProductId
			END

			SELECT				 [ClientProductId]
								,[SourceClientId]
								,[HybrisProductItemId]
								,[ProductImage]
								,[ProductDescription]
								,[ClientSKU]
								,[ProductName]
								,[MerchantSku]
								,[ManufactureBarcode]
								,[ProductUnit]
								,[Weight]
								,[Height]
								,[Length]
								,[Width]
								,[CBM]
								,[UpdateByLogistic]
								,[RequireSerial]
								,[MerchantProductName]
								,[RelatedBarcode]
								,[Brand]
								,[QtyPerUnit]
								,[ProductId]
			FROM [ClientProduct] WITH(NOLOCK)
			WHERE	ClientProductId = @ClientProductId
END