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

DROP INDEX idx_CM_ATTACH_FILE01 ON CM_ATTACH_FILE;

GO	

ALTER TABLE CM_ATTACH_FILE

ALTER COLUMN FILE_TYPE VARCHAR(MAX)

GO	

CREATE NONCLUSTERED INDEX [idx_CM_ATTACH_FILE01] ON [dbo].[CM_ATTACH_FILE]

(

	[REF_MASTER] ASC

)

INCLUDE([ATTACH_DT],[EMP_ID],[FILE_NAME_NEW],[FILE_NAME_OLD],[FILE_SIZE],[FILE_TYPE],[INDEX],[NOTES],[PATH_NEW],[PATH_OLD],[REF_ID],[STATUS],[TRAN_ID],[TYPE]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

ALTER TABLE CM_ATTACH_FILE_HIST

ALTER COLUMN FILE_TYPE VARCHAR(MAX)

GO

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

		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

GO

ALTER PROCEDURE dbo.CM_ATTACH_FILE_Upd

	@p_ATTACH_ID	varchar(15) = null ,

	@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(17) = 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

SET @p_PATH_OLD = NULL

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(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)

	)

	OPEN attDetail

BEGIN TRANSACTION

		--IF @p_PATH_NEW IS NOT NULL AND @p_PATH_NEW <> '' ---THIEUVQ BO SUNG DK PATH_NEW KIEM TRA CO DINH KEM FILE MOI INSERT

		--BEGIN

			DECLARE @l_FILE_VERSION INT

			IF(@p_ATTACH_ID IS NULL)

			BEGIN

				DECLARE @l_ATTACH_ID VARCHAR(15)

				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])

				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)

				IF @@Error <> 0 GOTO ABORT

			END

			ELSE

			BEGIN

				SELECT @l_FILE_VERSION = MAX(CONVERT(INT,VERSION))+1 FROM CM_ATTACH_FILE_HIST WHERE ATTACH_ID = @p_ATTACH_ID

				IF(@l_FILE_VERSION IS NULL)

					SET @l_FILE_VERSION = 0

				UPDATE CM_ATTACH_FILE SET [TYPE] = @p_TYPE,[FILE_NAME_OLD] = @p_FILE_NAME_OLD,[PATH_OLD] = @p_PATH_OLD,[FILE_NAME_NEW] = @p_FILE_NAME_NEW,[PATH_NEW] = @p_PATH_NEW,[FILE_SIZE] = @p_FILE_SIZE,[FILE_TYPE] = @p_FILE_TYPE,[ATTACH_DT] = CONVERT(DATETIME, @p_ATTACH_DT, 103),[EMP_ID] = @p_EMP_ID, [INDEX]=@p_INDEX,

				[NOTES] = @P_NOTES

				WHERE  ATTACH_ID = @p_ATTACH_ID

				IF @@Error <> 0 GOTO ABORT

			END

			print @l_FILE_VERSION

			IF(NOT EXISTS(SELECT * FROM CM_ATTACH_FILE_HIST WHERE 

			[VERSION] = (@l_FILE_VERSION - 1) and

			((PATH_NEW is not null and @p_PATH_NEW is not null and PATH_NEW = @p_PATH_NEW) or ((PATH_NEW is null or PATH_NEW = '') and (@p_PATH_NEW is null or @p_PATH_NEW = ''))) AND 

			((FILE_NAME_NEW is not null and @p_FILE_NAME_NEW is not null and FILE_NAME_NEW = @p_FILE_NAME_NEW) or ((FILE_NAME_NEW is null or FILE_NAME_NEW = '') and (@p_FILE_NAME_NEW is null or @p_FILE_NAME_NEW = ''))) AND 

			((ATTACH_ID is not null and @p_ATTACH_ID is not null and ATTACH_ID = @p_ATTACH_ID) or ((ATTACH_ID is null or ATTACH_ID = '') and (@p_ATTACH_ID is null or @p_ATTACH_ID = '')))))

			BEGIN

				-- INSERT HIST

				EXEC CM_ATTACH_FILE_HIS_Ins @p_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, @l_FILE_VERSION, 'UpdateFile'

				IF @@Error <> 0 GOTO ABORT

			END

		--END

		Declare @ATTACH_ID VARCHAR(15), @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(50), @ATTACH_DT VARCHAR(20), @EMP_ID varchar(15), @INDEX varchar(50),@NOTES NVARCHAR(1000)

		--Xoa detail

		DELETE CM_ATTACH_FILE WHERE REF_MASTER = @p_REF_ID AND REF_ID <> REF_MASTER;

		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	

		WHILE @@FETCH_STATUS = 0	

		BEGIN

			IF(@ATTACH_ID IS NULL OR 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

			ELSE

			BEGIN

				SELECT @l_FILE_VERSION = MAX(CONVERT(INT,VERSION))+1 FROM CM_ATTACH_FILE_HIST WHERE ATTACH_ID = @ATTACH_ID

				IF(@l_FILE_VERSION IS NULL)

					SET @l_FILE_VERSION = 0

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

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

				IF @@Error <> 0 GOTO ABORT

				PRINT 'D1'

				PRINT @PATH_OLD + @FILE_NAME_OLD

				PRINT 'D2'

				PRINT @PATH_NEW + @FILE_NAME_NEW

				IF(NOT EXISTS(SELECT * FROM CM_ATTACH_FILE_HIST WHERE [VERSION] = (@l_FILE_VERSION - 1) AND PATH_NEW = @PATH_NEW AND FILE_NAME_NEW = @FILE_NAME_NEW AND ATTACH_ID = @ATTACH_ID))

				IF(NOT EXISTS(SELECT * FROM CM_ATTACH_FILE_HIST WHERE 

				[VERSION] = (@l_FILE_VERSION - 1) and

				((PATH_NEW is not null and @PATH_NEW is not null and PATH_NEW = @PATH_NEW) or ((PATH_NEW is null or PATH_NEW = '') and (@PATH_NEW is null or @PATH_NEW = ''))) AND 

				((FILE_NAME_NEW is not null and @FILE_NAME_NEW is not null and FILE_NAME_NEW = @FILE_NAME_NEW) or ((FILE_NAME_NEW is null or FILE_NAME_NEW = '') and (@FILE_NAME_NEW is null or @FILE_NAME_NEW = ''))) AND 

				((ATTACH_ID is not null and @ATTACH_ID is not null and ATTACH_ID = @ATTACH_ID) or ((ATTACH_ID is null or ATTACH_ID = '') and (@ATTACH_ID is null or @ATTACH_ID = '')))))

				BEGIN

					-- INSERT HIST

					EXEC CM_ATTACH_FILE_HIS_Ins @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, @l_FILE_VERSION, 'UpdateFile'

					IF @@Error <> 0 GOTO ABORT

				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

			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

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_HIS_Ins]

@p_ATTACH_ID	varchar(15)  = NULL,

@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_VERSION VARCHAR(15)=NULL,

@p_ACTION VARCHAR(15)=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

	INSERT INTO CM_ATTACH_FILE_HIST([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],VERSION,ACTION)

	VALUES(@p_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_VERSION,@p_ACTION)

END