DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

ALTER PROCEDURE dbo.CM_ATTACH_FILE_Ins

@p_TYPE	varchar(50)  = NULL,

@p_REF_ID	varchar(15)  = NULL,

@p_FILE_NAME_OLD	nvarchar(MAX)  = NULL,

@p_PATH_OLD	nvarchar(MAX)  = NULL,

@p_FILE_NAME_NEW	nvarchar(MAX)  = NULL,

@p_PATH_NEW	nvarchar(MAX)  = NULL,

@p_FILE_SIZE	decimal(18)  = NULL,

@p_FILE_TYPE	varchar(MAX)  = NULL,

@p_ATTACH_DT	VARCHAR(20) = NULL,

@p_EMP_ID	varchar(15)  = NULL,

@p_INDEX	varchar(50)  = NULL,

@P_NOTES NVARCHAR(1000) = NULL,

@p_AttachDetail	xml  = NULL

AS

--Validation is here

--DECLARE @ERRORSYS NVARCHAR(15) = '' 

--	IF(@p_REF_ID <> '')

--		IF ( NOT EXISTS ( SELECT * FROM CM_ATTACH_FILE WHERE ATTACH_ID = @p_REF_ID ))

--			SET @ERRORSYS = 'CMA-99999'

--IF @ERRORSYS <> '' 

--BEGIN

--	SELECT ErrorCode Result, ''  ATTACH_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS

--	RETURN '0'

--END 

BEGIN TRANSACTION

DECLARE @l_ATTACH_ID VARCHAR(15)

		IF @p_PATH_NEW IS NOT NULL AND @p_PATH_NEW <> ''

		BEGIN

			EXEC SYS_CodeMasters_Gen 'CM_ATTACH_FILE', @l_ATTACH_ID out

			IF @l_ATTACH_ID='' OR @l_ATTACH_ID IS NULL GOTO ABORT

			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], [REF_MASTER])

			VALUES(@l_ATTACH_ID ,@p_TYPE ,@p_REF_ID ,@p_FILE_NAME_OLD ,@p_PATH_OLD ,@p_FILE_NAME_NEW ,@p_PATH_NEW ,@p_FILE_SIZE ,@p_FILE_TYPE ,CONVERT(DATETIME, @p_ATTACH_DT, 103) ,@p_EMP_ID, @p_INDEX,@P_NOTES, @p_REF_ID)

			IF @@Error <> 0 GOTO ABORT

			-- INSERT HIST

			EXEC CM_ATTACH_FILE_HIS_Ins @l_ATTACH_ID, @p_TYPE, @p_REF_ID, @p_FILE_NAME_OLD, @p_PATH_OLD, @p_FILE_NAME_NEW, @p_PATH_NEW, @p_FILE_SIZE, @p_FILE_TYPE, @p_ATTACH_DT, @p_EMP_ID, @p_INDEX, @P_NOTES, 0, 'CreateFile'

			IF @@Error <> 0 GOTO ABORT

		END

		-- INSERT LIST IF EXISTS

		IF(@p_AttachDetail IS NOT NULL)

		BEGIN

		Declare @hdoc INT

		Exec sp_xml_preparedocument @hdoc Output, @p_AttachDetail

		DECLARE attDetail CURSOR FOR

		SELECT *

		FROM OPENXML(@hDoc,'/Root/AttachDetail',2)

		WITH 

		(

			[TYPE]	varchar(50),

			REF_ID	varchar(15),

			FILE_NAME_OLD	nvarchar(MAX),

			PATH_OLD	nvarchar(MAX),

			FILE_NAME_NEW	nvarchar(MAX),

			PATH_NEW	nvarchar(MAX),

			FILE_SIZE	decimal(18),

			FILE_TYPE	varchar(MAX),

			ATTACH_DT	VARCHAR(20),

			EMP_ID	varchar(15),

			[INDEX]	varchar(50),

			NOTES NVARCHAR(1000),

			REF_MASTER	varchar(15)

		)

		OPEN attDetail

		Declare @TYPE varchar(50), @REF_ID varchar(15), @REF_MASTER varchar(15), @FILE_NAME_OLD	nvarchar(MAX), @PATH_OLD nvarchar(MAX), @FILE_NAME_NEW nvarchar(MAX), 

		@PATH_NEW nvarchar(MAX), @FILE_SIZE decimal(18), @FILE_TYPE varchar(50), @ATTACH_DT VARCHAR(20), @EMP_ID varchar(15), @INDEX varchar(50),@NOTES NVARCHAR(1000)

		FETCH NEXT FROM attDetail INTO @TYPE, @REF_ID, @FILE_NAME_OLD, @PATH_OLD, @FILE_NAME_NEW, @PATH_NEW, @FILE_SIZE, @FILE_TYPE, @ATTACH_DT, @EMP_ID, @INDEX,@NOTES,@REF_MASTER

		WHILE @@FETCH_STATUS = 0	

		BEGIN

			EXEC SYS_CodeMasters_Gen 'CM_ATTACH_FILE', @l_ATTACH_ID out

			IF(@l_ATTACH_ID='' OR @l_ATTACH_ID IS NULL)

				GOTO ABORT

			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], [REF_MASTER])

			VALUES(@l_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, @p_REF_ID)

			IF @@Error <> 0 GOTO ABORT

			-- INSERT HIST

			EXEC CM_ATTACH_FILE_HIS_Ins @l_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, 0, 'CreateFile'

			IF @@Error <> 0 GOTO ABORT

			-- next Id

			FETCH NEXT FROM attDetail INTO @TYPE, @REF_ID, @FILE_NAME_OLD, @PATH_OLD, @FILE_NAME_NEW, @PATH_NEW, @FILE_SIZE, @FILE_TYPE, @ATTACH_DT, @EMP_ID, @INDEX,@NOTES,@REF_MASTER

			END

			CLOSE attDetail

			DEALLOCATE attDetail

		END

		-------------------------BAODNQ 16/10/2022 : HOT FIX ĐÍNH KÈM FILE PYCMS----------------------

		-----------------------UPDATE ATTACH_ID VÀO BẢNG TR_REQUEST_DOC_FILE---------------------

		IF(EXISTS(

			SELECT * FROM TR_REQUEST_DOC_FILE TRF 

			INNER JOIN CM_ATTACH_FILE CAF ON TRF.TR_REQUEST_DOC_FILE_ID = CAF.REF_ID)

		)

		BEGIN

--			UPDATE TR_REQUEST_DOC_FILE SET

--				ATTACH_ID = CAF.ATTACH_ID

--			FROM TR_REQUEST_DOC_FILE TRF 

--			INNER JOIN CM_ATTACH_FILE CAF ON TRF.TR_REQUEST_DOC_FILE_ID = CAF.REF_ID

      --hieuhm 06/04/2023 hotfix chỉnh sửa đính kèm file ở DMMS

      UPDATE TR_REQUEST_DOC_FILE SET ATTACH_ID = (SELECT TOP(1) ATTACH_ID FROM CM_ATTACH_FILE WHERE TR_REQUEST_DOC_FILE.TR_REQUEST_DOC_FILE_ID = CM_ATTACH_FILE.REF_ID ORDER BY ATTACH_ID DESC)

		  --hieuhm 06/04/2023 hotfix chỉnh sửa đính kèm file ở DMMS

    END

		-------------------------END BADNQ-------------------------------

COMMIT TRANSACTION

SELECT '0' as Result, @l_ATTACH_ID  ID, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ID, '' ErrorDesc

		RETURN '-1'

End