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