DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

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