INSERT INTO SYS_PREFIX (ID, Prefix, Description) VALUES
('ASS_IMPORT_UPDATE_DT', 'IMP_DT', N'Import tài sản update'),
('ASS_IMPORT_UPDATE_MASTER', 'IMP', N'Import tài sản update')
GO
ALTER PROCEDURE dbo.ASS_PRIVATE_TRANSFER_MASTER_App
@p_TRANS_MULTI_MASTER_ID varchar(15),
@p_AUTH_STATUS varchar(1)  = NULL,
@p_CHECKER_ID	varchar(100)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL		
AS


BEGIN TRANSACTION
declare @l_OLD_LOCHIST_ID varchar(15)
declare @l_LOCHIST_ID varchar(15)
declare @l_ASSHIST_ID varchar(15)
declare @l_ASSET_ID varchar(15)
declare @l_BRANCH_ID varchar(15)
declare @l_DEPT_ID	varchar(15)
declare @l_EMP_ID	varchar(15)
declare @l_DESC	nvarchar(1000)
declare @l_LOCATION nvarchar(500)
DECLARE @l_MAKER_ID			varchar(15)
--DECLARE @sToday varchar(10) = convert(varchar(10), @p_APPROVE_DT, 103)	

--DECLARE @l_CUR_BRANCH_ID VARCHAR(15), @l_ASSET_TYPE VARCHAR(15), @l_AMORT_ACCTNO VARCHAR(50), 
--		@l_ASSET_GROUP VARCHAR(15), @l_ET_ID VARCHAR(15), @l_TRN_REF_NO VARCHAR(15),
--		@l_DO_BRANCH_ID VARCHAR(15), @l_CUR_AMORT_AMT DECIMAL(18), @l_AMORT_STATUS VARCHAR(15)	
--
--DECLARE @NOTE NVARCHAR(500), @CRET DATETIME
--SELECT @NOTE = NOTES, @CRET = CREATE_DT
--FROM [ASS_TRANSFER_MULTI_MASTER] 
--WHERE [TRANS_MULTI_MASTER_ID] = @p_TRANS_MULTI_MASTER_ID
--LUCTV: 26-12-2018 KIEM TRA NEU TINH TRANG DANG LA 'R' THI KHONG CHO PHEP DUYET
IF(EXISTS(SELECT * FROM ASS_PRIVATE_TRANSFER_MASTER WHERE AUTH_STATUS ='R' AND TRANS_MULTI_MASTER_ID =@p_TRANS_MULTI_MASTER_ID))
BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' COL_MULTI_MASTER_ID, N'Thông tin điều chuyển tài sản đang bị trả về. Vui lòng cập nhật lại thông tin trước khi duyệt' ErrorDesc
			RETURN '-1'
END
IF(NOT EXISTS (SELECT * FROM dbo.TL_USER_GET_ROLES(@p_CHECKER_ID) WHERE (ROLE_ID = 'GDDV' OR ROLE_ID = 'GDDV_QLTS')))
BEGIN
      ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' COL_MULTI_MASTER_ID, N'Bạn không có quyền duyệt phiếu này' ErrorDesc
			RETURN '-1'
END
--IF (SELECT CHARINDEX('ATGETD',@NOTE)) > 0
--BEGIN
--	SET @sToday = convert(varchar(10), @p_APPROVE_DT, 103)
--	SET @p_APPROVE_DT = @sToday
--END
		--APPROVE MASTER
--        UPDATE [dbo].[ASS_PRIVATE_TRANSFER_MASTER]
--		SET AUTH_STATUS = 'A',AUTH_STATUS_KT='U',
--			CHECKER_ID = @p_CHECKER_ID,
--			APPROVE_DT = CONVERT(datetime, @p_APPROVE_DT, 103),MAKER_ID_KT = NULL,APPROVE_DT_KT = NULL, CHECKER_ID_KT = NULL
--		WHERE [TRANS_MULTI_MASTER_ID] = @p_TRANS_MULTI_MASTER_ID
--		
		DECLARE @TRANSFER_MULTI_ID varchar(15), @IS_PRIVATE int = 0, @count int =0
		DECLARE @ASSET_ID varchar(15), @BRANCH_ID_OLD VARCHAR(15), @DEP_ID_OLD VARCHAR(15), @EMP_ID_OLD VARCHAR(15)
		DECLARE DataCusor SCROLL CURSOR 
		FOR  
		SELECT A.TRANSFER_MULTI_ID,A.ASSET_ID
		FROM [dbo].[ASS_PRIVATE_TRANSFER_DT] A
	WHERE [TRANS_MULTI_MASTER_ID] = @p_TRANS_MULTI_MASTER_ID

		OPEN DataCusor  

FETCH NEXT FROM DataCusor INTO  @TRANSFER_MULTI_ID,@ASSET_ID
WHILE @@FETCH_STATUS = 0
BEGIN	
--Lay thong tin
					SELECT	@l_ASSET_ID = ASSET_ID,
							@l_BRANCH_ID = BRANCH_ID,
							@l_DEPT_ID	= DEPT_ID,
							@l_EMP_ID	= EMP_ID,
							@l_MAKER_ID = MAKER_ID,
							@BRANCH_ID_OLD = BRANCH_ID_OLD,
							@DEP_ID_OLD = DEPT_ID_OLD, @EMP_ID_OLD = EMP_ID_OLD
					FROM [ASS_PRIVATE_TRANSFER_DT]
					WHERE TRANSFER_MULTI_ID = @TRANSFER_MULTI_ID
					
					EXEC SYS_CodeMasters_Gen 'ASS_MASTER_HIST', @l_ASSHIST_ID out
					IF @l_ASSHIST_ID='' OR @l_ASSHIST_ID IS NULL GOTO ABORT		
		
					INSERT INTO ASS_MASTER_HIST
						SELECT @l_ASSHIST_ID, a.*
						FROM ASS_MASTER a
						where a.ASSET_ID = @l_ASSET_ID
					IF @@Error <> 0 GOTO ABORT

					UPDATE ASS_MASTER
					SET EMP_ID	=	@l_EMP_ID
					WHERE ASSET_ID = @l_ASSET_ID

					IF @@Error <> 0 GOTO ABORT

					SELECT @l_OLD_LOCHIST_ID = LOCHIST_ID FROM ASS_LOCATION_HIST WHERE  ASSET_ID = @l_ASSET_ID AND ISLEAF='Y'
	
					--Uptdae isleaf='N' and END_DATE = ngay hien tai cho record hien tai
					UPDATE ASS_LOCATION_HIST
					SET USE_END_DT = CONVERT(DATETIME,@p_APPROVE_DT,103),
						ISLEAF='N'
					WHERE  ASSET_ID = @l_ASSET_ID AND ISLEAF='Y'
	
					EXEC SYS_CodeMasters_Gen 'ASS_LOCATION_HIST', @l_LOCHIST_ID out
					IF @l_LOCHIST_ID='' OR @l_LOCHIST_ID IS NULL GOTO ABORT
	
					--insert location moi
					INSERT INTO ASS_LOCATION_HIST
					(
						LOCHIST_ID, ASSET_ID, USE_START_DT, USE_END_DT, BRANCH_ID, DEPT_ID, 
						EMP_ID, LOCATION, ISLEAF, PARENT_ID
					)
					VALUES
					(
						@l_LOCHIST_ID, @l_ASSET_ID, CONVERT(DATETIME,@p_APPROVE_DT,103), NULL, @l_BRANCH_ID, @l_DEPT_ID,
						@l_EMP_ID, '', 'Y', @l_OLD_LOCHIST_ID
					)
					IF @@Error <> 0 GOTO ABORT	

					--INSERT VAO BANG ASS_TRANSACTIONS
					INSERT INTO ASS_TRANSACTIONS(ASSET_ID, TRN_ID, TRN_TYPE, TRN_DATE, RECORD_STATUS, AUTH_STATUS, 	
							[MAKER_ID], [CREATE_DT], [CHECKER_ID], [APPROVE_DT],ASSHIST_ID,LOCHIST_ID
					)VALUES
					(
						@l_ASSET_ID, @TRANSFER_MULTI_ID, 'PRIVATE_TRANSFER', CONVERT(DATETIME, @p_APPROVE_DT, 103), '1', 'A', 
						@l_MAKER_ID, CONVERT(DATETIME, @p_APPROVE_DT, 103), @p_CHECKER_ID, 	CONVERT(DATETIME, @p_APPROVE_DT, 103) ,@l_ASSHIST_ID,@l_LOCHIST_ID
					)
					IF @@Error <> 0 GOTO ABORT
				
	FETCH NEXT FROM DataCusor INTO  @TRANSFER_MULTI_ID,@ASSET_ID
END

		UPDATE [dbo].[ASS_PRIVATE_TRANSFER_MASTER]
		SET AUTH_STATUS_KT = 'A',AUTH_STATUS = 'A',
      CHECKER_ID = @p_CHECKER_ID,
      APPROVE_DT = CONVERT(datetime, @p_APPROVE_DT, 103),
			CHECKER_ID_KT = 'ADMIN',
			APPROVE_DT_KT = CONVERT(datetime, @p_APPROVE_DT, 103),
			CREATE_DT_KT = CONVERT(datetime, @p_APPROVE_DT, 103),
			MAKER_ID_KT = 'ADMIN'
		WHERE [TRANS_MULTI_MASTER_ID] = @p_TRANS_MULTI_MASTER_ID
		
		UPDATE ASS_PRIVATE_TRANSFER_DT 
		SET AUTH_STATUS_KT = 'A',AUTH_STATUS = 'A',
      CHECKER_ID = @p_CHECKER_ID,
      APPROVE_DT = CONVERT(datetime, @p_APPROVE_DT, 103),
			CHECKER_ID_KT = 'ADMIN',
			APPROVE_DT_KT = CONVERT(datetime, @p_APPROVE_DT, 103),
			CREATE_DT_KT = CONVERT(datetime, @p_APPROVE_DT, 103),
			MAKER_ID_KT = 'ADMIN'
		WHERE [TRANS_MULTI_MASTER_ID] = @p_TRANS_MULTI_MASTER_ID

UPDATE B
SET B.EMP_ID = A.EMP_ID
FROM ASS_PRIVATE_TRANSFER_DT A
LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID
WHERE A.TRANS_MULTI_MASTER_ID = @p_TRANS_MULTI_MASTER_ID

	-- GIANT 21/09/2021
	INSERT INTO dbo.PL_PROCESS
					(
						REQ_ID,
						PROCESS_ID,
						CHECKER_ID,
						APPROVE_DT,
						PROCESS_DESC,NOTES
					)
					VALUES
					(   @p_TRANS_MULTI_MASTER_ID,        -- REQ_ID - varchar(15)
						'APPROVE',        -- PROCESS_ID - varchar(10)
						@p_CHECKER_ID,        -- CHECKER_ID - varchar(15)
						GETDATE(), -- APPROVE_DT - datetime
					   N'Trưởng đơn vị phê duyệt ' ,
					   N'Trưởng đơn vị phê duyệt thành công'      -- PROCESS_DESC - nvarchar(1000)
					)

	-- HUYHT 01/11/2021
--	DECLARE @BRANCH_ID_BG VARCHAR(15), @DEP_ID_BG VARCHAR(15), @BRANCH_ID_BN VARCHAR(15), @DEP_ID_BN VARCHAR(15)
--	SELECT TOP 1 @BRANCH_ID_BG = DT.BRANCH_ID_OLD, @DEP_ID_BG = DT.DEPT_ID_OLD, @BRANCH_ID_BN = DT.BRANCH_ID, @DEP_ID_BN = DT.DEPT_ID
--	FROM dbo.ASS_TRANSFER_MULTI_MASTER AM
--	INNER JOIN dbo.ASS_TRANSFER_MULTI_DT DT ON AM.TRANS_MULTI_MASTER_ID = DT.TRANS_MULTI_MASTER_ID
--	WHERE AM.TRANS_MULTI_MASTER_ID = @p_TRANS_MULTI_MASTER_ID
--	ORDER BY DT.TRANSFER_MULTI_ID ASC

--	DECLARE @l_TRANS_MULTI_MASTER_CONFIRM_ID VARCHAR(15)
--	EXEC SYS_CodeMasters_Gen 'ASS_TRANSFER_CONFIRM_MASTER', @l_TRANS_MULTI_MASTER_CONFIRM_ID out
--	IF @l_TRANS_MULTI_MASTER_CONFIRM_ID='' OR @l_TRANS_MULTI_MASTER_CONFIRM_ID IS NULL GOTO ABORT
--
--	INSERT INTO [ASS_TRANSFER_CONFIRM_MASTER]
--	(
--		[TRANS_MULTI_MASTER_CONFIRM_ID],
--		[TRANS_MULTI_MASTER_ID],
--		[BRANCH_ID_BG],
--		[DEP_ID_BG],
--		[BRANCH_ID_BN],
--		[DEP_ID_BN]
--	)
--	VALUES
--	(
--		@l_TRANS_MULTI_MASTER_CONFIRM_ID,
--		@p_TRANS_MULTI_MASTER_ID ,
--		@BRANCH_ID_BG,
--		@DEP_ID_BG,
--		@BRANCH_ID_BN,
--		@DEP_ID_BN
--	)

  --PHUCVH 14/11/22 UPDATE TÀI SẢN ĐÃ XONG GIAO DỊCH
  UPDATE ASS_MASTER SET CURRENT_TRANS = NULL, CURRENT_TRANS_TYPE = NULL 
  WHERE ASSET_ID IN (SELECT A.ASSET_ID FROM ASS_PRIVATE_TRANSFER_DT A WHERE A.TRANS_MULTI_MASTER_ID = @p_TRANS_MULTI_MASTER_ID)

COMMIT TRANSACTION
SELECT '0' as Result, @p_TRANS_MULTI_MASTER_ID  COL_MULTI_MASTER_ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
	ROLLBACK TRANSACTION	
		SELECT '-1' as Result, '' COL_MULTI_MASTER_ID, ERROR_MESSAGE() ErrorDesc
		RETURN '-1'
End
GO

ALTER TRIGGER dbo.TRIG_CM_EMPLOYEE_SYNC_Ins
	ON dbo.CM_EMPLOYEE_SYNC
	AFTER INSERT
AS 
BEGIN
  --STORE ĐỒNG BỘ NHÂN VIÊN VÀ ĐIỀU CHUYỂN TÀI SẢN NHÂN SỰ
  --29/09/22
	SET NOCOUNT ON;
          IF (EXISTS(SELECT TOP 1 MaNS FROM INSERTED)) 
          BEGIN  
          
              DECLARE @MaDV_Cu_SYNC VARCHAR(500) = (SELECT TOP 1 MaDVCu FROM INSERTED)
              DECLARE @MaDV_Moi_SYNC VARCHAR(500) = (SELECT TOP 1 MaDVMoi FROM INSERTED) 

              --UPDATE MÃ MỚI DO KT UPDATE
              IF(@MaDV_Cu_SYNC = '0100')
              BEGIN
                  SET @MaDV_Cu_SYNC = '0600'
              END

              IF(@MaDV_Cu_SYNC = '0101')
              BEGIN
                  SET @MaDV_Cu_SYNC = '0601'
              END

              IF(@MaDV_Moi_SYNC = '0100')
              BEGIN
                  SET @MaDV_Moi_SYNC = '0600'
              END

              IF(@MaDV_Moi_SYNC = '0101')
              BEGIN
                  SET @MaDV_Moi_SYNC = '0601'
              END
                
              --MAP DATA HR VỚI KT
              IF(EXISTS(SELECT 1 FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Cu_SYNC))
              BEGIN
                  SET @MaDV_Cu_SYNC = (SELECT TOP 1 KT_ID FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Cu_SYNC)
              END
              IF(EXISTS(SELECT 1 FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Moi_SYNC))
              BEGIN
                  SET @MaDV_Moi_SYNC = (SELECT TOP 1 KT_ID FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Moi_SYNC)
              END
              DECLARE @MACD_CU_SYNC VARCHAR(500) = (SELECT TOP 1 MaCDCu FROM INSERTED)
              DECLARE @MACD_MOI_SYNC VARCHAR(500) = (SELECT TOP 1 MaCDMoi FROM INSERTED)
    
            --START: PARAM INSERT NHÂN VIÊN MỚI
              DECLARE @l_EMP_ID VARCHAR(500)
              DECLARE @p_EMP_CODE	varchar(500)  = (SELECT TOP 1 MaNS FROM INSERTED)
              DECLARE @p_EMP_NAME	nvarchar(500)  = (SELECT TOP 1 HoTen FROM INSERTED)
      
              DECLARE @p_BRANCH_ID	varchar(15) = NULL 
              DECLARE @p_DEP_ID	varchar(15) = NULL             
              
              DECLARE @p_POS_CODE VARCHAR(50) = NULL
              DECLARE @p_POS_NAME NVARCHAR(MAX) = NULL             
      
              DECLARE @p_NOTES	nvarchar(1000)  = N'ĐỒNG BỘ'
              DECLARE @p_RECORD_STATUS	varchar(1)  = '1'
              DECLARE @p_AUTH_STATUS	varchar(1)  = 'A'
              DECLARE @p_MAKER_ID	varchar(15)  = 'ADMIN'
              DECLARE @p_CREATE_DT	VARCHAR(50) = GETDATE()
              DECLARE @p_CHECKER_ID	varchar(15)  = 'ADMIN'
              DECLARE @p_APPROVE_DT	VARCHAR(50) = GETDATE()
            --END: PARAM INSERT NHÂN VIÊN MỚI
    
            --START: PARAM INSERT CM_EMPLOYEE_TRANSFER_LOG
              DECLARE @I_EMP_ID VARCHAR(20) = (SELECT TOP 1 ce.EMP_ID FROM CM_EMPLOYEE ce WHERE ce.EMP_CODE = (SELECT MaNS FROM INSERTED))
              DECLARE @I_STATUS VARCHAR(5) 
    
              DECLARE @C_Ma_DV_Cu VARCHAR(500) = NULL  --MÃ ĐV CŨ ĐỂ CHECK IF
              DECLARE @C_Ma_DV_Moi VARCHAR(500) = NULL --MÃ DV MỚI ĐỂ CHECK IF
              DECLARE @C_Ma_PB_Cu VARCHAR(500) = NULL  --MÃ PB CŨ ĐỂ CHECK IF
              DECLARE @C_Ma_PB_Moi VARCHAR(500) = NULL --MÃ PB MỚI ĐỂ CHECK IF
            --END: PARAM INSERT CM_EMPLOYEE_TRANSFER_LOG
    
            --START: PARAM INSERT 
    
            --CHECK XEM ĐƠN VỊ/PHÒNG BAN CỦA NHÂN VIÊN MỚI CỬA KH LƯU TRONG CỘT MaDVCu HAY CỘT MaDVMoi
            IF (EXISTS(SELECT TOP 1 MaDVCu FROM INSERTED WHERE MaDVCu IS NOT NULL AND MaDVCu <> ''))
            BEGIN 
                --CHECK XEM MÃ LÀ ĐƠN VỊ HAY PHÒNG BAN
                IF(EXISTS(SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Cu_SYNC))
                BEGIN
                    SET @p_BRANCH_ID = 'DV0001'
                    SET @C_Ma_DV_Cu = 'DV0001'
                    SET @p_DEP_ID = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Cu_SYNC)
                    SET @C_Ma_PB_Cu = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Cu_SYNC)
                END
                ELSE
                BEGIN
                    SET @p_BRANCH_ID = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Cu_SYNC)
                    SET @C_Ma_DV_Cu = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Cu_SYNC)
                END         
            END
            --ELSE
    
            IF(EXISTS(SELECT TOP 1 MaDVMoi FROM INSERTED WHERE MaDVMoi IS NOT NULL AND MaDVMoi <> ''))
            BEGIN
                --CHECK XEM MÃ LÀ ĐƠN VỊ HAY PHÒNG BAN
                IF(EXISTS(SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = (SELECT MaDVMoi FROM INSERTED)))
                BEGIN
                    SET @p_BRANCH_ID = 'DV0001'
                    SET @C_Ma_DV_Moi = 'DV0001'
                    SET @p_DEP_ID = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Moi_SYNC)
                    SET @C_Ma_PB_Moi = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Moi_SYNC)
                END
                ELSE
                BEGIN
                    SET @p_BRANCH_ID = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Moi_SYNC)
                    SET @C_Ma_DV_Moi = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Moi_SYNC)
                END
            END              
                               
            PRINT @C_Ma_DV_Cu
            PRINT @C_Ma_DV_Moi
        		IF((@C_Ma_DV_Cu IS NULL OR @C_Ma_DV_Cu = '') AND (@C_Ma_DV_Moi IS NULL OR @C_Ma_DV_Moi = ''))
        		BEGIN
          			--N'Mã DV cũ và Mã DV mới không có giá trị'
                PRINT 1
        		END
            ELSE
            BEGIN
--CHECK DATA CHỨC DANH LƯU CỘT CDMOI HAY CDCU
            IF(EXISTS(SELECT 1 FROM INSERTED WHERE MaCDMoi IS NOT NULL AND MaCDMoi <> ''))
            BEGIN
                  --NẾU CHỨC DANH CHƯA CÓ TRONG HỆ THỐNG THÌ THÊM MỚI CHỨC DANH
                  IF(NOT EXISTS(SELECT 1 FROM CM_MAPPING_CHUCDANH_ROLE WHERE MA_CHUC_DANH = @MACD_MOI_SYNC))
                  BEGIN
                       INSERT INTO CM_MAPPING_CHUCDANH_ROLE (MA_CHUC_DANH, TEN_CHUC_DANH, NHOM_CHUC_DANH, ROLE_ID)
                       VALUES (@MACD_MOI_SYNC, (SELECT TOP 1 CDMoi FROM INSERTED), NULL, NULL);
                  END
    
                  SET @p_POS_CODE = @MACD_MOI_SYNC
                  SET @p_POS_NAME = (SELECT TOP 1 CDMoi FROM INSERTED)
            END
            ELSE IF(EXISTS(SELECT 1 FROM INSERTED WHERE MaCDCu IS NOT NULL AND MaCDCu <> ''))
            BEGIN
                  --NẾU CHỨC DANH CHƯA CÓ TRONG HỆ THỐNG THÌ THÊM MỚI CHỨC DANH
                  IF(NOT EXISTS(SELECT 1 FROM CM_MAPPING_CHUCDANH_ROLE WHERE MA_CHUC_DANH = @MACD_CU_SYNC))
                  BEGIN
                       INSERT INTO CM_MAPPING_CHUCDANH_ROLE (MA_CHUC_DANH, TEN_CHUC_DANH, NHOM_CHUC_DANH, ROLE_ID)
                       VALUES (@MACD_CU_SYNC, (SELECT TOP 1 CDCu FROM INSERTED), NULL, NULL);
                  END
    
                  SET @p_POS_CODE = @MACD_CU_SYNC
                  SET @p_POS_NAME = (SELECT TOP 1 CDCu FROM INSERTED)
            END
    
    
             --CHECK NHÂN VIÊN XEM CÓ XEM TRONG HỆ THỐNG KHÔNG
             IF(EXISTS(SELECT TOP 1 ce.EMP_ID FROM CM_EMPLOYEE ce WHERE ce.EMP_CODE = (SELECT MaNS FROM INSERTED)))
             BEGIN
                
                IF(EXISTS(
                        SELECT atmd.ASSET_ID FROM ASS_TRANSFER_MULTI_DT atmd
                        WHERE (atmd.EMP_ID = @I_EMP_ID AND atmd.EMP_ID_OLD = @I_EMP_ID AND (atmd.BRANCH_ID = @C_Ma_DV_Moi AND atmd.BRANCH_ID_OLD = @C_Ma_DV_Cu) 
      																				 AND atmd.AUTH_STATUS_KT <> 'A') --ĐIỀU CHUYỂN TS TỪ ĐV NÀY SANG ĐV KHÁC CHO CHÙNG NV
                              OR (atmd.EMP_ID_OLD = @I_EMP_ID AND atmd.BRANCH_ID_OLD = @C_Ma_DV_Cu AND atmd.EMP_ID <> @I_EMP_ID AND atmd.AUTH_STATUS_KT <> 'A') --ĐIỀU CHUYỂN TS TỪ ĐV NÀY SANG ĐV KHÁC NHƯNG KHÁC NV
                        UNION ALL
                        SELECT am.ASSET_ID FROM ASS_MASTER am WHERE am.BRANCH_ID = @C_Ma_DV_Cu AND am.EMP_ID = @I_EMP_ID))
                  SET @I_STATUS = '0'
                ELSE
                  SET @I_STATUS = '1'
      
                INSERT INTO CM_EMPLOYEE_TRANSFER_LOG (EMP_ID, TRANSFER_DT, BRANCH_ID_OLD, BRANCH_ID_NEW, STATUS)
                VALUES (@I_EMP_ID, CONVERT(DATETIME,(SELECT TOP 1 TGTao FROM INSERTED),103), @C_Ma_DV_Cu, @C_Ma_DV_Moi, @I_STATUS);  
      
                --PHUCVH 22/12/22 CHECK NẾU MADV,PB MỚI THAY ĐỔI THÌ UPDATE LẠI MADV,PB CHO NHANVIEN
                IF(EXISTS(SELECT 1 FROM CM_EMPLOYEE A WHERE A.EMP_CODE = @p_EMP_CODE 
                              AND (ISNULL(A.BRANCH_ID,'') <> ISNULL(@p_BRANCH_ID,'')
                                  OR ISNULL(A.DEP_ID,'') <> ISNULL(@p_DEP_ID,''))))
                BEGIN
                      UPDATE CM_EMPLOYEE SET BRANCH_ID = @p_BRANCH_ID, DEP_ID = @p_DEP_ID 
                      WHERE EMP_CODE = @p_EMP_CODE
    
                      --CHECK UPDATE TL_USER VÀ CM_EMPLOYEE_LOG
                      IF(EXISTS(SELECT 1 FROM CM_EMPLOYEE_LOG WHERE EMP_CODE = @p_EMP_CODE))
                      BEGIN
                          UPDATE CM_EMPLOYEE_LOG SET BRANCH_CODE = (SELECT TOP 1 BRANCH_CODE FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_ID),
                                                     DEP_CODE = (SELECT TOP 1 DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID = @p_DEP_ID)
                          WHERE EMP_CODE = @p_EMP_CODE
    
                          UPDATE TL_USER SET TLSUBBRID = @p_BRANCH_ID, SECUR_CODE = @p_DEP_ID, DEP_ID = @p_DEP_ID
                          WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE EMP_CODE = @p_EMP_CODE)
                      END
    
                END  
                
                --PHUCVH 22/12/22 CHECK NẾU MA CHUC DANH THAY ĐỔI THÌ UPDATE
                IF(EXISTS(SELECT 1 FROM CM_EMPLOYEE A WHERE A.EMP_CODE = @p_EMP_CODE
                                AND ISNULL(A.POS_CODE,'') <> ISNULL(@p_POS_CODE,'')))
                BEGIN
                     UPDATE CM_EMPLOYEE SET POS_CODE = @p_POS_CODE, POS_NAME = @p_POS_NAME
                     WHERE EMP_CODE = @p_EMP_CODE
    
                     UPDATE CM_EMPLOYEE_LOG SET POS_CODE = @p_POS_CODE, POS_NAME = @p_POS_NAME
                     WHERE EMP_CODE = @p_EMP_CODE
                END                     
    
                --IF @@Error <> 0 GOTO ABORT
          		  --SET @Message = N'Đồng bộ điều chuyển nhân sự thành công'
             END
             ELSE
             BEGIN
                --KHÔNG CÓ TRONG HỆ THỐNG
                --THÊM THÔNG TIN NHÂN VIÊN
                		EXEC SYS_CodeMasters_Gen 'CM_EMPLOYEE', @l_EMP_ID out		       
                		INSERT INTO CM_EMPLOYEE([EMP_ID],[EMP_CODE],[EMP_NAME],[BRANCH_ID],[DEP_ID],[NOTES],[RECORD_STATUS],[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT],[POS_CODE],[POS_NAME])
                		VALUES(@l_EMP_ID ,@p_EMP_CODE ,@p_EMP_NAME ,@p_BRANCH_ID ,@p_DEP_ID ,@p_NOTES ,@p_RECORD_STATUS ,@p_AUTH_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103), @p_POS_CODE, @p_POS_NAME )
                    --IF @@Error <> 0 GOTO ABORT
            				--SET @Message = N'Đồng bộ nhân viên mới thành công'
             END
            END
    
            
          END
END

GO


BEGIN TRANSACTION
DECLARE @FULLNAME NVARCHAR(MAX) =N'Nguyễn Hồng Hải ,Nguyễn Thị Lợi ,Phan Thị Cẩm Tú ,Nông Bùi Tấn Huy ,Nguyễn Thị Hường ,Nguyễn Thị Bích Ngọc ,Phạm Mạnh Hiệp ,Vũ Đức Dũng ,Trần Kim Thành ,Lâm Thu Thư ,Lục Mỹ Phụng ,Châu Văn Hiệp ,NGUYỄN HOÀNG THÔNG ,NGUYỄN THỊ THÙY DUNG ,LÊ THỊ CHÂU GIANG ,ĐỖ THỊ HIỀN ,NGUYỄN THỊ HIỀN ,PHAN THỊ LAN HƯƠNG ,Nguyễn Thị Vân ,Hoàng Thị Thanh Huyền ,Nguyễn Thanh Tùng ,Nguyễn Duy Hiến ,Trần Thái Hà ,Lương Thị Y Duyên ,Nguyễn Thị Phương Mai ,Tiêu Ngoc Lâm Thảo ,Dương Mộc Triều ,Châu Văn Hiệp ,Nguyễn Thị Phương Nhung ,Trịnh Thị Mai Soan ,Hà Quang Kiên ,NGUYỄN HOÀNG THÔNG ,NGUYỄN THỊ THÙY DUNG ,LÊ THỊ CHÂU GIANG ,BÙI THỊ HỒNG ÁNH ,TRẦN LÊ TƯỜNG VY ,NGUYỄN THỊ THÙY NGÂN ,Bùi Ngô Anh ,Phạm Thị Bích Phượng ,Đỗ Thị Nga ,Lê Văn Trung ,Lê Hoàng Thanh'
DECLARE @EMP_CODE VARCHAR(MAX) = N'2018-07001 ,0000-10139 ,0000-10129 ,2019-08061 ,2020-07054 ,2020-06056 ,2020-07076 ,0000-10348 ,0201-12003 ,STR-08-028 ,STR-08-003 ,STR-08-005 ,HNI-08-034 ,0000-09213 ,2020-03021 ,2022-08040 ,040-09-012 ,0412-14001 ,HNI-08-031 ,0401-10029 ,017-08-003 ,0405-14002 ,0401-10031 ,040-09-054 ,020-09-001 ,STR-08-032 ,STR-08-017 ,STR-08-005 ,0401- 09023 ,2018-04041 ,0501-10003 ,HNI-08-034 ,0000-09213 ,2020-03021 ,HSO-08-091 ,2023-02019 ,0135-17001 ,0401-11009 ,2017-04051 ,0417-15002 ,21.0-17063 ,2022-10030'
DECLARE @USER_NAME VARCHAR(MAX) =N'hainh1200,loint,tuptc,huynbt,huongnt2800,ngocntb2800,hieppm2800,dungvd,thanhtk,thult,phunglm,hiepcv,thongnh,dungntt,giangltc0714,HIENDT0811,HIENNT0811,HUONGPTL,VANNT0812,HUYENHTT,TUNGNT0812,hiennd0802,hatt0802,duyenlty,maintp,thaotnl,trieudm,hiepcv,nhungntp,soanttm,kienhq,thongnh,dungntt,giangltc0714,ANHBTH1901,VYTLT1901,NGANNTT,anhbn,phuongptb,ngadt,trunglv,thanhlh0713'
DECLARE @EMAIL NVARCHAR(MAX) = N'nguyenhonghai@vietbank.com.vn,nguyenthiloi@vietbank.com.vn,phanthicamtu@vietbank.com.vn,nongbuitanhuy@vietbank.com.vn,nguyenthihuong1@vietbank.com.vn,nguyenthibichngoc1@vietbank.com.vn,phammanhhiep@vietbank.com.vn,vuducdung@vietbank.com.vn,trankimthanh@vietbank.com.vn,lamthuthu@vietbank.com.vn,lucmyphung@vietbank.com.vn,chauvanhiep@vietbank.com.vn,nguyenhoangthong@vietbank.com.vn,nguyenthithuydung@vietbank.com.vn,lethichaugiang@vietbank.com.vn,dothihien@vietbank.com.vn,nguyenthihien2@vietbank.com.vn,phanthilanhuong@vietbank.com.vn,nguyenthivan@vietbank.com.vn,hoangthithanhhuyen@vietbank.com.vn,nguyenthanhtung@vietbank.com.vn,nguyenduyhien@vietbank.com.vn,tranthaiha@vietbank.com.vn,luongthiyduyen@vietbank.com.vn,nguyenthiphuongmai@vietbank.com.vn,tieungoclamthao@vietbank.com.vn,duongmoctrieu@vietbank.com.vn,chauvanhiep@vietbank.com.vn,nguyenthiphuongnhung@vietbank.com.vn,trinhthimaisoan@vietbank.com.vn,haquangkien@vietbank.com.vn,nguyenhoangthong@vietbank.com.vn,nguyenthithuydung@vietbank.com.vn,lethichaugiang@vietbank.com.vn,buithihonganh@vietbank.com.vn,tranletuongvy@vietbank.com.vn,nguyenthithuyngan@vietbank.com.vn,buingoanh@vietbank.com.vn,phamthibichphuong@vietbank.com.vn,dothinga@vietbank.com.vn,levantrung@vietbank.com.vn,lehoangthanh@vietbank.com.vn'
DECLARE @BRANCH_CODE VARCHAR(MAX) = N'1200,1200,1200,1200,2800,2800,2800,2800,0600,0600,0600,0600,0714,0714,0714,0811,0811,0811,0812,0812,0812,0802,0802,0802,0601,0601,0601,0600,1003,1003,1003,0714,0714,0714,1901,1901,1901,0808,0808,0808,0718,0718'
DECLARE @DEP_ID_IMP VARCHAR(MAX) = N',,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,'
DECLARE @ROLE VARCHAR(MAX) = 'GDDV,NVTT,NVTT,DVCM,NVTT,NVTT,DVCM,GDDV,NVTT,NVTT,GDDV,DVCM,GDDV,NVTT,NVTT,NVTT,NVTT,GDDV,NVTT,NVTT,GDDV,GDDV,NVTT,NVTT,NVTT,NVTT,GDDV,DVCM,NVTT,NVTT,GDDV,GDDV,NVTT,NVTT,NVTT,NVTT,GDDV,GDDV,NVTT,NVTT,NVTT,GDDV'
--DELETE IMPORT_USER_QLTS
--INSERT INTO IMPORT_USER_QLTS (TLNAME, FULLNAME, EMAIL, EMP_CODE, BRANCH_CODE, DEP_CODE, ROLE_NAME)
SELECT C.TLNAME,A.FULLNAME,D.EMAIL,B.EMP_CODE,E.BRANCH_CODE,F.DEP_CODE,G.ROLE_NAME
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS FULLNAME
FROM STRING_SPLIT(@FULLNAME,','))A
LEFT JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS EMP_CODE
    FROM STRING_SPLIT(@EMP_CODE,','))B ON A.ID = B.ID
LEFT JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS TLNAME
    FROM STRING_SPLIT(@USER_NAME,',')) C ON A.ID = C.ID
LEFT JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS EMAIL
    FROM STRING_SPLIT(@EMAIL,','))D ON A.ID = D.ID
LEFT JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS BRANCH_CODE
    FROM STRING_SPLIT(@BRANCH_CODE,','))E ON A.ID = E.ID
LEFT JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS DEP_CODE
    FROM STRING_SPLIT(@DEP_ID_IMP,','))F ON A.ID = F.ID
LEFT JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS ROLE_NAME
    FROM STRING_SPLIT(@ROLE,','))G ON A.ID = G.ID
DECLARE @INS_MESSAGE NVARCHAR(1000)         
DECLARE @INS_FULLNAME NVARCHAR(1000)
DECLARE @INS_EMP_CODE VARCHAR(1000)        
DECLARE @INS_USER_NAME VARCHAR(1000) 
DECLARE @INS_EMAIL NVARCHAR(1000)
DECLARE @INS_BRANCH_CODE VARCHAR(1000) 
DECLARE @INS_DEP_CODE VARCHAR(1000)   
DECLARE @INS_ROLE VARCHAR(1000)
DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
--SELECT TLNAME
--      ,FULLNAME
--      ,EMAIL
--      ,EMP_CODE
--      ,BRANCH_CODE
--      ,DEP_CODE
--      ,ROLE_NAME FROM IMPORT_USER_QLTS 
--	  where TLNAME = 'khoidt'
SELECT C.TLNAME,A.FULLNAME,D.EMAIL,B.EMP_CODE,E.BRANCH_CODE,F.DEP_CODE,G.ROLE_NAME
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS FULLNAME
FROM STRING_SPLIT(@FULLNAME,','))A
LEFT JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS EMP_CODE
    FROM STRING_SPLIT(@EMP_CODE,','))B ON A.ID = B.ID
LEFT JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS TLNAME
    FROM STRING_SPLIT(@USER_NAME,',')) C ON A.ID = C.ID
LEFT JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS EMAIL
    FROM STRING_SPLIT(@EMAIL,','))D ON A.ID = D.ID
LEFT JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS BRANCH_CODE
    FROM STRING_SPLIT(@BRANCH_CODE,','))E ON A.ID = E.ID
LEFT JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS DEP_CODE
    FROM STRING_SPLIT(@DEP_ID_IMP,','))F ON A.ID = F.ID
LEFT JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS ROLE_NAME
    FROM STRING_SPLIT(@ROLE,','))G ON A.ID = G.ID
OPEN cur
FETCH NEXT FROM cur INTO @INS_USER_NAME,@INS_FULLNAME,@INS_EMAIL,@INS_EMP_CODE,@INS_BRANCH_CODE,@INS_DEP_CODE,@INS_ROLE
WHILE @@FETCH_STATUS = 0 BEGIN
   
	IF(NOT EXISTS(SELECT 1 FROM TL_USER WHERE TLNANME = @INS_USER_NAME))
    BEGIN
			DECLARE @BRANCH_ID VARCHAR(15), @DEP_ID VARCHAR(15), @DEP_CODE VARCHAR(100)
    
			SELECT @BRANCH_ID = cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @INS_BRANCH_CODE
			SELECT @DEP_ID = cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @INS_DEP_CODE
			SET @DEP_CODE = @INS_DEP_CODE

      if(@INS_ROLE = 'DVCM')
      BEGIN
          SET @BRANCH_ID = 'DV0001'
          SET @DEP_ID = 'DEP000000000027'
          SET @DEP_CODE = '05J03'
      END
          
			INSERT INTO TL_USER ( TLID, TLNANME, Password, TLFullName, TLSUBBRID,  BRANCH_TYPE,  EMAIL, ADDRESS, PHONE, AUTH_STATUS, MARKER_ID, AUTH_ID, APPROVE_DT, ISAPPROVE, Birthday, ISFIRSTTIME, SECUR_CODE, AccessFailedCount, AuthenticationSource, ConcurrencyStamp, CreatorUserId, DeleterUserId, EmailAddress, EmailConfirmationCode, IsActive, IsDeleted, IsEmailConfirmed, IsLockoutEnabled, IsPhoneNumberConfirmed, IsTwoFactorEnabled, LastModifierUserId, LockoutEndDateUtc, Name, NormalizedEmailAddress, NormalizedUserName, PasswordResetCode, PhoneNumber, ProfilePictureId, SecurityStamp, ShouldChangePasswordOnNextLogin, Surname, TenantId, SignInToken, SignInTokenExpireTimeUtc, GoogleAuthenticatorKey, SendActivationEmail, DEP_ID, CreationTime, UamFullName, UamEmployeeId, UamCompanyCode, UamWfDataId, UamCompanyName, UamJobTitle, UserCurrentLanguage, EMAILTEMP) VALUES
			(NULL, @INS_USER_NAME, N'AQAAAAEAACcQAAAAEMF7sJx/2L/X7bkO6YmSRfr8d7Na/RURfT4tDZYFIDMaik/cy+y7PSfq48Btaka28A==', @INS_FULLNAME, @BRANCH_ID, '',  @INS_EMAIL, N'', '', 'A', 'bichnn', NULL, GETDATE(), '1', GETDATE(), '1', @DEP_ID, 0, NULL, N'fd2b0a93-3081-460d-b969-b5a75f96c0de', NULL, NULL, @INS_EMAIL, NULL, CONVERT(bit, 'True'), CONVERT(bit, 'False'), CONVERT(bit, 'True'), CONVERT(bit, 'False'), CONVERT(bit, 'True'), CONVERT(bit, 'False'), 259, NULL, NULL, @INS_EMAIL, @INS_USER_NAME, NULL, NULL, NULL, N'QSTE4B7VTYWRQON4JZTJST6QN4PEVVLB', CONVERT(bit, 'False'), NULL, 1, NULL, NULL, NULL, CONVERT(bit, 'False'), @DEP_ID, GETDATE(), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
        
			INSERT INTO CM_EMPLOYEE_LOG (EMP_CODE, EMP_NAME, BRANCH_CODE, DEP_CODE, USER_DOMAIN, POS_CODE, POS_NAME, CREATE_DT)
			VALUES (@INS_EMP_CODE, @INS_FULLNAME, @INS_BRANCH_CODE, @INS_DEP_CODE, @INS_USER_NAME, '', N'', GETDATE());
    
			INSERT INTO AbpUserRoles (CreationTime, CreatorUserId, RoleId, TenantId, UserId)
			VALUES (SYSDATETIME(), 0, (SELECT TOP 1 ar.Id FROM AbpRoles ar WHERE ar.DisplayName = @INS_ROLE), 1, (SELECT MAX(ID) FROM TL_USER tu WHERE tu.TLNANME = @INS_USER_NAME));

  	END

    IF(NOT EXISTS(SELECT 1 FROM AbpUserRoles AUR WHERE AUR.RoleId = (SELECT TOP 1 ar.Id FROM AbpRoles ar WHERE ar.DisplayName = @INS_ROLE) AND AUR.UserId = (SELECT MAX(ID) FROM TL_USER tu WHERE tu.TLNANME = @INS_USER_NAME)))
    BEGIN
      	INSERT INTO AbpUserRoles (CreationTime, CreatorUserId, RoleId, TenantId, UserId)
			  VALUES (SYSDATETIME(), 0, (SELECT TOP 1 ar.Id FROM AbpRoles ar WHERE ar.DisplayName = @INS_ROLE), 1, (SELECT MAX(ID) FROM TL_USER tu WHERE tu.TLNANME = @INS_USER_NAME));
    END
  
  SET @BRANCH_ID = NULL
  SET @DEP_ID = NULL
  SET @DEP_CODE = NULL

	FETCH NEXT FROM cur INTO @INS_USER_NAME,@INS_FULLNAME,@INS_EMAIL,@INS_EMP_CODE,@INS_BRANCH_CODE,@INS_DEP_CODE,@INS_ROLE
END
CLOSE cur
DEALLOCATE cur
COMMIT TRANSACTION