USE [Adayroi_TMS]
GO
/****** Object:  StoredProcedure [dbo].[APIClient_3PL_Upd_LadingWaitingListClosed_V01]    Script Date: 12/12/2017 9:29:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Hoàng Đình Khiêm
-- Create date:	05/10/2015
-- Description:	Lưu danh sách vận đơn chờ đóng
-- Author:		KhiemHD
-- Create date:	28/02/2017
--Note: Update theo User Story 454245:[TMS] TMS ghi nhận thời gian Min - Max giao hàng từ OMS đổ xuống & thay đổi cách tính thời gian còn lại theo thời gian Min
-- =============================================
ALTER PROCEDURE [dbo].[APIClient_3PL_Upd_LadingWaitingListClosed_V01]
	@strXmlDetail NVARCHAR(MAX),
	@CurrentUser INT,
	@SesstionKey VARCHAR(250),
	@Serials NVARCHAR(MAX)
AS
BEGIN
	DECLARE	@PROC_NAME VARCHAR(250) = OBJECT_NAME(@@PROCID)
	--Tạo Table temp
	CREATE TABLE #TableOpenXml
	(
		ReferenceId	BIGINT,
		LogId		BIGINT,
		IsAgain		BIT,
		IsCanceled	BIT,
		IsCheckSave	BIT,
		ReasonNameNote	NVARCHAR(250),
		ReasonId		INT,
		MethodId		INT,
		StatusId		INT,
		DateAgain		DATETIME,
		DateAgainTo	DATETIME
	)
	CREATE TABLE #TableSerial
	(
		Id			INT,
		SoDetailId	BIGINT,
		SerialNumber	VARCHAR(250),
		StatusId		INT
	)

	BEGIN TRY
		EXEC	[dbo].[CHECK_SESSION_AND_PERMISSION] @CurrentUser, @SesstionKey, @PROC_NAME

		BEGIN TRAN T
			DECLARE @idoc INT
			DECLARE @idoc1 INT
			EXEC sp_xml_preparedocument @idoc OUTPUT, @strXmlDetail
			EXEC sp_xml_preparedocument @idoc1 OUTPUT, @Serials

			INSERT INTO #TableOpenXml
			(
				ReferenceId,
				LogId,
				IsAgain,
				IsCanceled,
				IsCheckSave,
				ReasonNameNote,
				ReasonId,
				MethodId,
				StatusId,
				DateAgain,
				DateAgainTo
			)
			SELECT	ReferenceId, LogId, IsAgain, IsCanceled, IsCheckSave, ReasonNameNote, ReasonId, MethodId, StatusId, DateAgain, DateAgainTo
			FROM	OPENXML (@idoc, '/ArrayOfLadingWaitingListClosed/LadingWaitingListClosed', 8)
			WITH
				(
					ReferenceId	BIGINT	'ReferenceId',
					LogId		BIGINT	'LogId',
					IsAgain		BIT		'IsAgain',
					IsCanceled	BIT		'IsCanceled',
					IsCheckSave	BIT		'IsCheckSave',
					ReasonNameNote	NVARCHAR(250) 'ReasonNameNote',
					ReasonId		BIGINT	'ReasonId',
					MethodId		BIGINT	'MethodId',
					StatusId		BIGINT	'StatusId',
					DateAgain		DATETIME 'DateAgain',
					DateAgainTo	DATETIME 'DateAgainTo'
				)
			INSERT INTO #TableSerial
			(
				Id, SoDetailId, SerialNumber, StatusId
			)
			SELECT	Id, SoDetailId, SerialNumber, StatusId
			FROM	OPENXML (@idoc1, '/ArrayOfSerialInfo/SerialInfo', 8)
					WITH
						(
							Id			INT		'Id',
							SoDetailId	BIGINT	'SoDetailId',
							SerialNumber	VARCHAR(250) 'SerialNumber',
							StatusId		INT		'StatusId'
						)

			 Declare @tempSoDetail as Table
			 (
				ClientSoId		    BIGINT,
				ClientPoId		    BIGINT,
				SoId			    BIGINT,
				ProductTypeId	    INT,
				SoDetailId		    BIGINT,
				ClientProductId	    INT,
				PackingID		    BIGINT,
				PackingDetailId	    BIGINT,
				Amount		    INT,
				SellPrice		    DECIMAL(18,4),
				SAPReservationNumber	   NVARCHAR(50),
				SAPPONumberInterim	   NVARCHAR(50),
				SAPReservationLineItem  NVARCHAR(700)
			 )
			------------------------------------------------------------------------------------------------------------------------------------
			--Update UnitTransport_Log_CreateShippingOrder
			UPDATE	UnitTransport_Log_CreateShippingOrder
			SET
					IsAgain		= tox.IsAgain,
					IsCanceled	= tox.IsCanceled,
					IsCheckSave	= 1,
					StatusId		= CASE
									   WHEN tox.IsAgain	   = 1 THEN 9
									   WHEN tox.IsCanceled = 1 THEN 8 ELSE utl.StatusId
								  END,
					ReasonName	= tox.ReasonNameNote
			FROM	#TableOpenXml tox	INNER JOIN	dbo.UnitTransport_Log_CreateShippingOrder utl ON utl.LogId	= tox.LogId
			WHERE    utl.StatusId NOT IN (10, 11, 12)	-- Update: 03/11/2016

			--Giao hàng
			IF(EXISTS(SELECT 1 FROM  #TableOpenXml 	WHERE MethodId = 1))
			BEGIN
				--Update DE_DeliverySlipsDetails
				UPDATE	DE_DeliverySlipsDetails
				SET
						IsAgain					=	tox.IsAgain,
						IsCompleted				=	0,
						IsCanceled				=	tox.IsCanceled,
						QuantityCompleted			=	0,
						QuantityAgain				=	CASE WHEN tox.IsAgain	  = 1	THEN ddsd.Quantity		ELSE 0	END,
						QuantityCanceled			=	CASE WHEN tox.IsCanceled	  = 1	THEN ddsd.Quantity		ELSE 0	END,
						DateCompleted				=	NULL,
						DateAgain					=	CASE WHEN tox.IsAgain	  = 1	THEN tox.DateAgain		ELSE NULL	END,
						DateAgainTo				=	CASE WHEN tox.IsAgain	  = 1	THEN tox.DateAgainTo	ELSE NULL	END,
						DateCanceled				=	CASE WHEN tox.IsCanceled	  = 1	THEN GETDATE()			ELSE NULL	END,
						ReasonAgain				=	CASE WHEN tox.IsAgain	  = 1	THEN tox.ReasonId		ELSE 0	END,
						ReasonCanceled				=	CASE WHEN tox.IsCanceled	  = 1	THEN tox.ReasonId		ELSE 0	END,
						ModifiedBy				=	@CurrentUser,
						DateModified				=	GETDATE(),
						[Status]					=	29,
						StatusProcessFor3PL			=	73,	--Xử lý đơn hủy và hẹn lại từ 3PL
						ReasonAgainOrther			=	CASE WHEN tox.IsAgain	  = 1		THEN tox.ReasonNameNote	ELSE NULL	END,
						ReasonCanceledOrther		=	CASE WHEN tox.IsCanceled	  = 1		THEN tox.ReasonNameNote	ELSE NULL	END,
						DateSaveTemp				=	CASE WHEN ddsd.DateSaveTemp IS NULL THEN GETDATE() ELSE ddsd.DateSaveTemp END,
						Note						=	tox.ReasonNameNote
				FROM
						#TableOpenXml tox	INNER JOIN dbo.Packing_Detail		    pd	 WITH (NOLOCK) ON pd.PackingID		= tox.ReferenceId
										INNER JOIN dbo.DE_DeliverySlipsDetails ddsd	 WITH (NOLOCK) ON ddsd.PackingDetailId	= pd.PackingDetailId
				WHERE	tox.MethodId  = 1
				    AND	ddsd.Visible  = 1

				--Update SO_Detail

				UPDATE  SO_Detail
				SET		DeliveryDateFrom	  = CASE WHEN tox.IsAgain = 1 THEN tox.DateAgainTo ELSE sd.DeliveryDateFrom END,
						DeliveryDateTo		  = CASE WHEN tox.IsAgain = 1 THEN tox.DateAgainTo ELSE sd.DeliveryDateTo END,
						TempDeliveryDateFrom  = CASE WHEN tox.IsAgain = 1 THEN tox.DateAgain	ELSE sd.DeliveryDateFrom END,
						IsPushCS			  = 0,
						IsDeliveryAgain	  = CASE WHEN tox.IsAgain = 1 THEN 1 ELSE 0 END,
						SODetailStatusID	  =	CASE
												WHEN tox.IsAgain = 1 THEN 7
												WHEN tox.IsCanceled = 1 THEN 16 ELSE sd.SODetailStatusID
											 END,
						UserModify		  = @CurrentUser,
						DeliveryFromTimeUpdateTime = GETDATE(),
						DeliveryPeriodTime	  =	 CASE WHEN tox.IsAgain = 1 THEN (SELECT TOP 1 dpt.Name FROM DeliveryPeriodTime dpt WITH (NOLOCK) WHERE CONVERT(CHAR(8), tox.DateAgain, 108) BETWEEN dpt.FromTime AND dpt.ToTime)
											 ELSE sd.DeliveryPeriodTime
											 END


				OUTPUT  INSERTED.ClientSoId, INSERTED.ClientPoId, INSERTED.SOId, INSERTED.ProductTypeID, INSERTED.SODetailID, INSERTED.ClientProductId, pd.PackingID, pd.PackingDetailId,
					   INSERTED.Amount, INSERTED.SellPrice, INSERTED.SAPReservationNumber, INSERTED.SAPPONumberInterim, INSERTED.SAPReservationLineItem
				INTO	   @tempSoDetail (ClientSoId, ClientPoId, SoId, ProductTypeId, SoDetailId, ClientProductId, PackingID, PackingDetailId, Amount, SellPrice, SAPReservationNumber, SAPPONumberInterim, SAPReservationLineItem)

				FROM	#TableOpenXml tox	INNER JOIN dbo.Packing_Detail	 pd	WITH (NOLOCK) ON pd.PackingID	  = tox.ReferenceId
									INNER JOIN dbo.SO_Detail		 sd	WITH (NOLOCK) ON pd.SoDetailId  = sd.SODetailID
				WHERE   --sd.SODetailStatusID < 13
						sd.Visible = 1
					AND	tox.MethodId = 1

				--Update SO_Detail_Serial
				UPDATE	dbo.SO_Detail_Serial
				SET		StatusId = ts.StatusId,
						RollBackSerial	= 0
				FROM	dbo.SO_Detail_Serial sds INNER JOIN #TableSerial ts ON (ts.SerialNumber	= sds.SerialNumber AND ts.SoDetailId = sds.SODetailID)

				--Update (15/11/2016) đẩy thời gian hẹn lại sang PushCsDateTime
				DECLARE @NoteByUser nvarchar(250)
				SELECT @NoteByUser =	uu.FullName + ' - [' + CAST(uu.UserId AS varchar(50)) + ']' FROM dbo.UM_Users uu WHERE uu.UserId = @CurrentUser

				INSERT INTO dbo.PushCsDateTime
				(
				    Soid,
				    Push,
				    CreateDate,
				    TmsShippingFrom,
				    TmsShippingTo,
				    Note,
				    CityId,
				    PoNumber,
				    ProductItemId,
				    ProductType,
				    Quantity,
				    PhoneNumber,
				    FailDeliveryReson,
					cp.ClientProductId
				)
				SELECT s.ClientSoId,	   -- Số SO
				    0,				   -- đã push hay chưa default = 0
				    GETDATE(),
				    tox.DateAgain,		   -- Thời gian hẹn lại từ
				    tox.DateAgainTo,	   -- Thời gian hẹn lại đến
				    @NoteByUser,		   -- ghi chú
				    s.DeliveryCityId,	   -- Thành phố (get theo trạm)
				    sd.ClientPoId,		   -- Số PO
				    cp.ProductItemId,
				    sd.ProductTypeID,	   -- Loại hàng
				    ddsd.Quantity,		   -- Số lượng hẹn lại
				    s.CustomerPhone,	   -- Số dt khách hàng
				    0		,		   -- lý do false - default :0
					cp.ClientProductId
				FROM	#TableOpenXml tox	INNER JOIN @tempSoDetail			    sd			      ON sd.PackingID	   = tox.ReferenceId
									INNER JOIN dbo.SO				    s	  WITH (NOLOCK) ON s.SOID		   = sd.SoId
									INNER JOIN dbo.DE_DeliverySlipsDetails ddsd	  WITH (NOLOCK) ON ddsd.PackingDetailId	= sd.PackingDetailId
									INNER JOIN dbo.ClientProduct		    cp	  WITH (NOLOCK) ON cp.ClientProductId = sd.ClientProductId
				WHERE tox.IsAgain = 1

				--Update thời gian hẹn lại lại cho PurcharseOrder
				UPDATE  po
				SET	   DeliveryDateFrom	   =	  tox.DateAgainTo,
					   DeliveryDateTo	   =	  tox.DateAgainTo,
					   DeliveryPolicyType  =	  2,
					   TempDeliveryDateFrom  = tox.DateAgain,
					   DeliveryPeriodTime  =	  CASE WHEN tox.IsAgain = 1 THEN (  SELECT TOP 1 dpt.Name
																    FROM DeliveryPeriodTime dpt WITH (NOLOCK)
																    WHERE CONVERT( CHAR(8), tox.DateAgain, 108) BETWEEN dpt.FromTime AND dpt.ToTime
																 )
										  ELSE po.DeliveryPeriodTime
										  END
				FROM	 #TableOpenXml tox INNER JOIN @tempSoDetail		 tsd			    ON tsd.PackingID = tox.ReferenceId
								    INNER JOIN dbo.PurchaseOrder	 po	WITH (NOLOCK) ON po.ClientPoId = tsd.ClientPoId
				WHERE   tox.IsAgain	    = 1
				    AND po.POStatusId   < 5

				--Update SO
				UPDATE  dbo.SO
				SET	   dbo.SO.SOStatusId = (	  SELECT st.Id
										  FROM dbo.SOStatus st  WITH (NOLOCK) INNER JOIN
											 (SELECT  MIN(st1.Priority) AS MinPriority
											 FROM dbo.SO_Detail sd WITH (NOLOCK) INNER JOIN dbo.SOStatus st1  WITH (NOLOCK) ON sd.SODetailStatusID = st1.Id
											 WHERE sd.ClientSoId = s.ClientSoId AND sd.Visible = 1) ts ON st.Priority = ts.MinPriority)
				FROM	   dbo.SO s WITH (NOLOCK) INNER JOIN @tempSoDetail AS Sml ON Sml.SoId = s.SOID

			END

			--GHI LOG
			DECLARE @StationId int = 0
			SELECT	@StationId = uu.SID FROM dbo.UM_Users uu WHERE uu.UserId = @CurrentUser
			INSERT INTO Log_Information
			(
				ObjectTypeId, ObjectId, ActionId, LogDetails, CreatedBy, CreatedDate, UID_AssignTo, OldStatusId, NewStatusId, StationId
			)
			SELECT	3,
					tsd.SoDetailId,
					105,
					N'Xử lý vận đơn hủy từ 3PL: Lý do: ' + tox.ReasonNameNote,
					@CurrentUser,
					GETDATE(),
					@CurrentUser,
					7,
					CASE
						WHEN tox.IsAgain = 1 THEN 7
						WHEN tox.IsCanceled = 1 THEN 16 ELSE NULL
					END,
					@StationId
			FROM	#TableOpenXml tox	INNER JOIN @tempSoDetail tsd ON tsd.PackingID = tox.ReferenceId

			--Ghi log
			INSERT INTO dbo.UnitTransport_Tracking3PLStatus
			(
				CreatedDate,
				QueueId,
				MethodId,
				ClientSoId,
				ReferenceId,
				OrderReference,
				TPLOrderReference,
				OperationStatusId,
				OldOrderStatusId,
				NewOrderStatusId,
				OldMerchantStatusId,
				NewMerchantStatusId,
				Message
			)
			SELECT
				  GETDATE()					-- CreatedDate
				, utlcso.LogId					-- QueueId
				, utlcso.MethodId				-- MethodId
				, isnull(utlcso.AdrClientSoId,0)-- ClientSoId
				, utlcso.ReferenceId			-- ReferenceId
				, utlcso.OrderReference			-- OrderReference
				, utlcso.UnitTransportOrderCode	-- TPLOrderReference
				, 0								-- OperationStatusId - int
				, CASE
						WHEN tox.StatusId	= 8 THEN 8
						WHEN tox.StatusId	= 9 THEN 9 ELSE NULL
					END
				, CASE
						WHEN tox.StatusId	= 8 THEN 8
						WHEN tox.StatusId	= 9 THEN 9 ELSE NULL
					END							-- NewOrderStatusId - int
				, 2								-- OldMerchantStatusId - int
				, 2								-- NewMerchantStatusId - int
				, N'Xử lý vận đơn hủy từ 3PL -> Cập nhật:' +	   CASE
														  WHEN tox.IsAgain = 1	THEN N'Hẹn lại'
														  WHEN tox.IsCanceled = 1 THEN N'Hủy giao' ELSE NULL
													   END + N'( Lý do: ' + tox.ReasonNameNote + ' )'
			FROM #TableOpenXml tox INNER JOIN dbo.UnitTransport_Log_CreateShippingOrder utlcso ON utlcso.LogId = tox.LogId
			WHERE utlcso.StatusId NOT IN (10, 11, 12)	-- Update: 03/11/2016

			 --Thêm log cập nhật trạng thái OMS, lưu ý phải tự thêm vào các item phí đối với PO có phí (CPN, GCD)
			 IF (EXISTS(SELECT 1 FROM @tempSoDetail tsd	INNER JOIN dbo.SO s WITH (NOLOCK) ON s.SOID = tsd.SoId
							 WHERE s.SourceClientId = 1 ))
			 BEGIN
				DECLARE @TblOms AS Oms_UpdateSoDetailStatus
				INSERT INTO @TblOms
				(
				    SOID,
				    PO,
				    ProductItemId,
				    Amount,
				    CurrentStatus,
				    DeliveryReasonId,
				    DeliveryNote,
				    OldStatus,
				    PriceCustomer,
				    SAPReservationNumber,
				    SAPReservationLineItem,
				    SAPPONumberInterim,
				    Serials
				)
				SELECT		  -->HỦY
				    tsd.ClientSoId,
				    tsd.ClientPoId,
				    cp.ProductItemId,
				    tsd.Amount,
				    33554432,			    --Hủy
				    tox.ReasonId,
				    tox.ReasonNameNote,
				    CASE WHEN po.DeliveryFlowId <> 2 THEN 256 ELSE 128 END AS OldStatus,
				    tsd.SellPrice,
				    tsd.SAPReservationNumber,
				    tsd.SAPReservationLineItem,
				    tsd.SAPPONumberInterim,
				    (
					   SELECT  ts.SerialNumber AS string
					   FROM	  #TableSerial ts
					   WHERE	  (ts.SodetailId = tsd.SoDetailId OR ts.SoDetailId = sd.SoDetailIdRoot) AND ts.StatusId = 69 AND ISNULL(sd.IsVinIdCard, 0) = 0
					   FOR XML PATH ('')
				    ) AS Serials
				FROM	#TableOpenXml tox  INNER JOIN @tempSoDetail			tsd				ON tsd.PackingID	    = tox.ReferenceId
									   INNER JOIN dbo.SO			s   WITH (NOLOCK)	ON s.SOID			    = tsd.SoId
									   INNER JOIN dbo.SO_Detail		sd  WITH (NOLOCK)	ON tsd.SoDetailId	    = sd.SODetailID
									   LEFT JOIN dbo.ClientProduct	cp  WITH (NOLOCK)	ON cp.ClientProductId   = sd.ClientProductId
									   LEFT JOIN dbo.PurchaseOrder	po  WITH (NOLOCK)	ON po.ClientPoId	    = tsd.ClientPoId
				WHERE   tox.IsCanceled = 1
				    AND s.SourceClientId = 1
				UNION ALL
				SELECT		  -->HẸN LẠI
				    tsd.ClientSoId,
				    tsd.ClientPoId,
				    cp.ProductItemId,
				    tsd.Amount,
				    CASE WHEN po.DeliveryFlowId = 2 THEN 128 ELSE 256 END AS CurrentStatus,
				    tox.ReasonId,
				    tox.ReasonNameNote,
				    CASE WHEN po.DeliveryFlowId <> 2 THEN 256 ELSE 128 END AS OldStatus,
				    tsd.SellPrice,
				    tsd.SAPReservationNumber,
				    tsd.SAPReservationLineItem,
				    tsd.SAPPONumberInterim,
				    (
					   SELECT	 ts.SerialNumber AS string
					   FROM	 #TableSerial ts
					   WHERE	 (ts.SodetailId = tsd.SoDetailId OR ts.SoDetailId = sd.SoDetailIdRoot) AND ts.StatusId = 68 AND ISNULL(sd.IsVinIdCard, 0) = 0
					   FOR XML PATH ('')
				    ) AS Serials
				FROM	#TableOpenXml tox  INNER JOIN @tempSoDetail			tsd				ON tsd.PackingID	    = tox.ReferenceId
									   INNER JOIN dbo.SO			s   WITH (NOLOCK)	ON s.SOID			    = tsd.SoId
									   INNER JOIN dbo.SO_Detail		sd  WITH (NOLOCK)	ON tsd.SoDetailId	    = sd.SODetailID
									   LEFT JOIN dbo.ClientProduct	cp  WITH (NOLOCK)	ON cp.ClientProductId   = sd.ClientProductId
									   LEFT JOIN dbo.PurchaseOrder	po  WITH (NOLOCK)	ON po.ClientPoId	    = tsd.ClientPoId
				WHERE   tox.IsAgain = 1
				    AND s.SourceClientId = 1

				EXEC dbo.APIClient_OMS_CreateLogUpdateSoDetails_V01 @CurrentUser, @SesstionKey, @TblOms
			 END

			 --Nếu đơn hàng có hẹn lại thì insert đơn hàng vào bảng SoNotAllowAutoCall để chặn không cho gọi xác nhận giao hàng.
			 IF (EXISTS(SELECT 1 FROM #TableOpenXml tox WHERE tox.IsAgain =	1))
			 BEGIN
				INSERT INTO dbo.SoNotAllowAutoCall
				(
				    ClientSoId, CreatedBy, ReasonBlockId
				)
				SELECT DISTINCT
				    tsd.ClientSoId, -- ClientSoId - bigint
				    @CurrentUser, -- CreatedBy - int
				    2   --Thay đổi leadtime
				FROM @tempSoDetail tsd INNER JOIN #TableOpenXml tox ON tox.ReferenceId = tsd.PackingID
				WHERE   tox.IsAgain = 1
				    AND  NOT EXISTS (
						  SELECT	TOP 1 1
						  FROM	SoNotAllowAutoCall sna WITH (NOLOCK)
						  WHERE	sna.ClientSoId = tsd.ClientSoId AND sna.Visible = 1
				    )
			 END

			EXEC sp_xml_removedocument @idoc
			EXEC sp_xml_removedocument @idoc1
		COMMIT TRAN T
		SELECT 1 AS IsSuccess, 'Success' AS ResponseMessage
	END TRY
	BEGIN CATCH
		SELECT 0 AS IsSuccess, N'Lỗi rồi.' AS ResponseMessage;
		THROW;
	END CATCH
	DROP TABLE #TableSerial
	DROP TABLE #TableOpenXml
END