USE [gAMSPro_VCCB_v2]
GO
/****** Object:  StoredProcedure [dbo].[CM_ATTACH_FILE_Upd_Xml_v2]    Script Date: 8/18/2020 9:20:31 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*
SELECT * FROM CM_ATTACH_FILE
[dbo].[CM_ATTACH_FILE_Ins] '','','','','','',NULL,'','29/10/2013',''
INSERT INTO [dbo].[SYS_PREFIX](ID,Prefix,Description) VALUES('CM_ATTACH_FILE','CMA','FILE ĐÍNH KÉM')
*/

ALTER PROCEDURE [dbo].[CM_ATTACH_FILE_Upd_Xml_v2]
@p_REF_ID	varchar(15)  = null,
@p_AttachDetail	xml  = NULL,
@p_TYPE VARCHAR(20)= NULL
AS
Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output, @p_AttachDetail
	DECLARE attDetail CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/AttachDetail',2)
	WITH 
	(
	ATTACH_ID	varchar(15),
	[TYPE]	varchar(50),
	REF_ID	varchar(15),
	FILE_NAME_OLD	nvarchar(200),
	PATH_OLD	nvarchar(1000),
	FILE_NAME_NEW	nvarchar(200),
	PATH_NEW	nvarchar(1000),
	FILE_SIZE	decimal(18),
	FILE_TYPE	varchar(50),
	ATTACH_DT	VARCHAR(20),
	EMP_ID	varchar(15),
	[INDEX]	varchar(50),
	NOTES NVARCHAR(1000),
	IS_VIEW BIT
	)
	OPEN attDetail

BEGIN TRANSACTION
		Declare @ATTACH_ID VARCHAR(15), @TYPE varchar(50), @REF_ID varchar(15), @FILE_NAME_OLD	nvarchar(200), @PATH_OLD nvarchar(1000), @FILE_NAME_NEW nvarchar(200), 
		@PATH_NEW nvarchar(1000), @FILE_SIZE decimal(18), @FILE_TYPE varchar(50), @ATTACH_DT VARCHAR(20), @EMP_ID varchar(15), @INDEX varchar(50),@NOTES NVARCHAR(1000),
		@IS_VIEW BIT
		--Xoa detail
		DELETE CM_ATTACH_FILE WHERE REF_ID = @p_REF_ID;
		DELETE dbo.TR_REQUEST_DOC_FILE WHERE REQ_ID=@p_REF_ID


		IF @@Error <> 0 GOTO ABORT

		FETCH NEXT FROM attDetail INTO @ATTACH_ID,@TYPE, @REF_ID, @FILE_NAME_OLD, @PATH_OLD, @FILE_NAME_NEW, @PATH_NEW, @FILE_SIZE, @FILE_TYPE, @ATTACH_DT, @EMP_ID, @INDEX,@NOTES,@IS_VIEW
		WHILE @@FETCH_STATUS = 0	
		BEGIN
			EXEC SYS_CodeMasters_Gen 'CM_ATTACH_FILE', @ATTACH_ID out
			IF @ATTACH_ID='' OR @ATTACH_ID IS NULL GOTO ABORT
			--IF(LEN(@ATTACH_ID) = 0)
			--	BEGIN
			--		EXEC SYS_CodeMasters_Gen 'CM_ATTACH_FILE', @ATTACH_ID out
			--		IF @ATTACH_ID='' OR @ATTACH_ID IS NULL GOTO ABORT
			--	END		
			BEGIN				
				INSERT INTO CM_ATTACH_FILE([ATTACH_ID],[TYPE],[REF_ID],[FILE_NAME_OLD],[PATH_OLD],[FILE_NAME_NEW],[PATH_NEW],[FILE_SIZE],[FILE_TYPE],[ATTACH_DT],[EMP_ID], [INDEX],[NOTES])
				VALUES(@ATTACH_ID ,@TYPE ,@REF_ID ,@FILE_NAME_OLD ,@PATH_OLD ,@FILE_NAME_NEW ,@PATH_NEW ,@FILE_SIZE ,@FILE_TYPE ,CONVERT(DATETIME, @ATTACH_DT, 103) ,@EMP_ID, @INDEX,@NOTES )
				IF @@Error <> 0 GOTO ABORT

				IF(@p_TYPE='PYC')
				BEGIN
					DECLARE @TR_REQ_DOC_FILE_ID VARCHAR(20)

					EXEC SYS_CodeMasters_Gen 'TR_REQUEST_DOC_FILE', @TR_REQ_DOC_FILE_ID out
					IF @TR_REQ_DOC_FILE_ID='' OR @TR_REQ_DOC_FILE_ID IS NULL GOTO ABORT
					INSERT INTO dbo.TR_REQUEST_DOC_FILE
					(
					    TR_REQUEST_DOC_FILE_ID,
					    ATTACH_ID,
					    IS_VIEW,
					    REQ_ID
					)
					VALUES
					(   @TR_REQ_DOC_FILE_ID,   -- TR_REQUEST_DOC_FILE_ID - varchar(20)
					    @ATTACH_ID,   -- ATTACH_ID - varchar(20)
					    @IS_VIEW, -- IS_VIEW - bit
					    @REF_ID    -- REQ_ID - varchar(20)
					    )
				END
				-- next Id
				FETCH NEXT FROM attDetail INTO @ATTACH_ID, @TYPE, @REF_ID, @FILE_NAME_OLD, @PATH_OLD, @FILE_NAME_NEW, @PATH_NEW, @FILE_SIZE, @FILE_TYPE, @ATTACH_DT, @EMP_ID, @INDEX,@NOTES,@IS_VIEW
			END
		END
		CLOSE attDetail
		DEALLOCATE attDetail
COMMIT TRANSACTION
SELECT '0' as Result, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		CLOSE attDetail
		DEALLOCATE attDetail
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ErrorDesc
		RETURN '-1'
End