USE [Adayroi_TMS]
GO
/****** Object:  StoredProcedure [dbo].[APIClient_Sodetail_GetProductTms_V01]    Script Date: 12/15/2017 9:42:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--====================================================================================
-- Entity Name:	APIClient_Sodetail_GetProductTms_V01
-- Author:	Trần an Bình
-- Create date:	15/12/2014 8:32:27 PM
-- Description:	lấy danh sách serail
-- ================================================================================
ALTER PROCEDURE [dbo].[APIClient_Sodetail_GetProductTms_V01]
(
	@Soid bigint,
	@Deliveryfrom datetime,
	@SourceClientId int = 0
)
AS
BEGIN
	If (@SourceClientId is null OR @SourceClientId = 0)
		BEGIN
			Set @SourceClientId = 1
		END
	If(@SourceClientId = 1)
		BEGIN
		SELECT DISTINCT
				[Soid],
				[PoNumber],
				[ProductItemId],
				[ProductName],
				[ProductType],
				[Quantity]
				FROM
		(SELECT DISTINCT
				sd.ClientSoId as [Soid],
				sd.ClientPoId as [PoNumber],
				cp.ProductItemId as [ProductItemId],
				cp.ProductName as [ProductName],
				sd.ProductTypeID as [ProductType],
				sd.Amount as [Quantity]
				FROM
				dbo.SO_Detail sd WITH (NOLOCK)
				INNER JOIN dbo.ClientProduct cp WITH (NOLOCK) ON cp.ClientProductId = sd.ClientProductId
				INNER JOIN dbo.PushCsDateTime pcdt WITH(NOLOCK) ON (pcdt.ProductItemId = cp.ProductItemId AND pcdt.Soid = sd.ClientSoId)
				WHERE
				sd.ClientSoId = @Soid AND pcdt.Push = 0
				AND convert(date,pcdt.TmsShippingFrom) = convert(DATE,@Deliveryfrom)
				AND	DATEPART(hour,pcdt.TmsShippingFrom) = DATEPART(hour,@Deliveryfrom)
				--ORDER BY
				--cp.ClientProductId DESC
				UNION	ALL

				SELECT DISTINCT
				@Soid as [Soid],
				pofm.ClientPoId as [PoNumber],
				pofm.ProductItemId as [ProductItemId],
				pof.PoFeeName as [ProductName],
				1 AS  [ProductType], --hàng binh thường
				1 as [Quantity]
				FROM
				dbo.PurchaseOrder po WITH (NOLOCK)
				INNER JOIN	dbo.PurchaseOrder_Fee_Map pofm WITH(NOLOCK)	ON pofm.ClientPoId= po.ClientPoId
				INNER JOIN	dbo.PurchaseOrder_Fee pof WITH(NOLOCK)	ON pof.PoFeeId =pofm.PoFeeId
				INNER JOIN	dbo.SO s WITH(NOLOCK) ON s.SOID = po.SoId
				INNER JOIN dbo.PushCsDateTime pcdt WITH(NOLOCK) ON (pcdt.Soid = s.ClientSoId AND po.PoId= pcdt.PoNumber)
				WHERE
				s.ClientSoId = @Soid
				AND convert(date,pcdt.TmsShippingFrom) = convert(DATE,@Deliveryfrom)
				AND	DATEPART(hour,pcdt.TmsShippingFrom) = DATEPART(hour,@Deliveryfrom)
				AND pofm.Status	 =1
			) AS t
		UPDATE	PushCsDateTime
		SET NumCount = NumCount + 1
		FROM PushCsDateTime push
		INNER JOIN	dbo.SO s	WITH(nolock) ON push.Soid	=s.ClientSoId
		WHERE push.Soid	=@Soid AND push.Push = 0
		AND s.SourceClientId =@SourceClientId
		END
		ELSE
			BEGIN
				SELECT DISTINCT
				[Soid],
				[PoNumber],
				[ProductItemId],
				[ProductName],
				[ProductType],
				[Quantity]
				FROM
		(SELECT DISTINCT
				sd.ClientSoId as [Soid],
				sd.ClientPoId as [PoNumber],
				cp.ProductItemId as [ProductItemId],
				cp.ProductName as [ProductName],
				sd.ProductTypeID as [ProductType],
				sd.Amount as [Quantity]
				FROM
				dbo.SO_Detail sd WITH (NOLOCK)
				INNER JOIN dbo.ClientProduct cp WITH (NOLOCK) ON cp.ClientProductId = sd.ClientProductId
				INNER JOIN dbo.PushCsDateTime pcdt WITH(NOLOCK) ON (pcdt.Soid = sd.ClientSoId AND pcdt.SODetailID	= sd.SODetailID AND sd.Visible	=1)
				WHERE
				sd.ClientSoId = @Soid AND pcdt.Push = 0
				AND convert(date,pcdt.TmsShippingFrom) = convert(DATE,@Deliveryfrom)
				AND	DATEPART(hour,pcdt.TmsShippingFrom) = DATEPART(hour,@Deliveryfrom)
				--ORDER BY
				--cp.ClientProductId DESC
				UNION	ALL

				SELECT DISTINCT
				@Soid as [Soid],
				pofm.ClientPoId as [PoNumber],
				pofm.ProductItemId as [ProductItemId],
				pof.PoFeeName as [ProductName],
				1 AS  [ProductType], --hàng binh thường
				1 as [Quantity]
				FROM
				dbo.PurchaseOrder po WITH (NOLOCK)
				INNER JOIN	dbo.PurchaseOrder_Fee_Map pofm WITH(NOLOCK)	ON pofm.ClientPoId= po.ClientPoId
				INNER JOIN	dbo.PurchaseOrder_Fee pof WITH(NOLOCK)	ON pof.PoFeeId =pofm.PoFeeId
				INNER JOIN	dbo.SO s WITH(NOLOCK) ON s.SOID = po.SoId
				INNER JOIN dbo.PushCsDateTime pcdt WITH(NOLOCK) ON (pcdt.Soid = s.ClientSoId AND po.PoId= pcdt.PoNumber)
				WHERE
				s.ClientSoId = @Soid
				AND convert(date,pcdt.TmsShippingFrom) = convert(DATE,@Deliveryfrom)
				AND	DATEPART(hour,pcdt.TmsShippingFrom) = DATEPART(hour,@Deliveryfrom)
				AND pofm.Status	 =1
			) AS t
			END


END



--SELECT	* FROM dbo.SO_Detail sd	WHERE sd.ClientSoId	=160146296