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

              DECLARE @TLNAME VARCHAR(200) = NULL

              DECLARE @BLOCK_ID_OLD VARCHAR(15) = NULL --Mã khối cũ check điều chuyển nhân sự
              DECLARE @CENTER_ID_OLD VARCHAR(15) = NULL  --Mã trung tâm cũ check điều chuyển nhân sự
              DECLARE @DEP_ID_OLD VARCHAR(15) = NULL  --Mã phòng ban cũ check điều chuyển nhân sự

              DECLARE @BLOCK_ID_NEW VARCHAR(15) = NULL  --Mã khối mới check điều chuyển nhân sự
              DECLARE @CENTER_ID_NEW VARCHAR(15) = NULL  --Mã trung tâm mới check điều chuyển nhân sự
              DECLARE @DEP_ID_NEW VARCHAR(15) = NULL  --Mã phòng ban mới check điều chuyển nhân sự
    
            --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        
            
            --GET KHỐI, TRUNG TÂM, PHÒNG BAN
            EXEC CM_DEPARTMENT_GET_BLOCK_CENTER @C_Ma_PB_Cu ,@BLOCK_ID_OLD OUT ,@CENTER_ID_OLD OUT , @DEP_ID_OLD OUT   
                                   
            --GET KHỐI, TRUNG TÂM, PHÒNG BAN
            EXEC CM_DEPARTMENT_GET_BLOCK_CENTER @C_Ma_PB_Moi, @BLOCK_ID_NEW OUT ,@CENTER_ID_NEW OUT ,@DEP_ID_NEW OUT     
                               
            IF(@p_BRANCH_ID <> 'DV0001')
            BEGIN
                SET @p_DEP_ID = NULL
            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
            
              IF((SELECT COUNT(*) FROM CM_EMPLOYEE_LOG CEL WHERE CEL.EMP_CODE = @p_EMP_CODE) > 1)
              BEGIN
                    SET @TLNAME = (SELECT TOP 1 CEL.USER_DOMAIN FROM CM_EMPLOYEE_LOG CEL WHERE CEL.EMP_CODE = @p_EMP_CODE 
                              AND CEL.BRANCH_CODE = @MaDV_Cu_SYNC
                              AND (CEL.DEP_CODE = @C_Ma_PB_Cu OR @C_Ma_PB_Cu IS NULL))
              END
              ELSE
              BEGIN
                  SET @TLNAME = (SELECT TOP 1 CEL.USER_DOMAIN FROM CM_EMPLOYEE_LOG CEL WHERE CEL.EMP_CODE = @p_EMP_CODE)
              END

            --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 ISNULL(atmd.KHOI_ID_OLD,'') = ISNULL(@BLOCK_ID_OLD,'') AND ISNULL(atmd.KHOI_ID,'') = ISNULL(@BLOCK_ID_NEW,'')
                                                    AND ISNULL(atmd.CENTER_ID_OLD,'') = ISNULL(@CENTER_ID_OLD,'') AND ISNULL(atmd.CENTER_ID,'') = ISNULL(@CENTER_ID_NEW,'')
                                                    AND ISNULL(atmd.DEPT_ID_OLD,'') = ISNULL(@DEP_ID_OLD,'') AND ISNULL(atmd.DEPT_ID,'') = ISNULL(@DEP_ID_NEW,''))  
      																				 AND atmd.AUTH_STATUS_KT <> 'A') --ĐIỀU CHUYỂN TS TỪ ĐV NÀY SANG ĐV KHÁC CHO CÙNG NV
                              OR (atmd.EMP_ID_OLD = @I_EMP_ID AND atmd.EMP_ID <> @I_EMP_ID  
                                  AND atmd.BRANCH_ID_OLD = @C_Ma_DV_Cu 
                                  AND ISNULL(atmd.KHOI_ID_OLD,'') = ISNULL(@BLOCK_ID_OLD,'')
                                  AND ISNULL(atmd.CENTER_ID_OLD,'') = ISNULL(@CENTER_ID_OLD,'')
                                  AND ISNULL(atmd.DEPT_ID_OLD,'') = ISNULL(@DEP_ID_OLD,'')
                                  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 ISNULL(am.DEPT_ID,'') = ISNULL(@C_Ma_PB_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, DEP_ID_OLD, BRANCH_ID_NEW, DEP_ID_NEW, STATUS)
                VALUES (@I_EMP_ID, CONVERT(DATETIME,(SELECT TOP 1 TGTao FROM INSERTED),103), @C_Ma_DV_Cu,@C_Ma_PB_Cu, @C_Ma_DV_Moi, @C_Ma_PB_Moi, @I_STATUS);  
      
                --CHECK TRƯỜNG HỢP 1 NHÂN VIÊN CÓ QUYỀN 2 CHI NHÁNH TRỞ TRÊN (KHÁC TLNAME)

                --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(@p_BRANCH_ID,'') <> ''
                              AND ((ISNULL(@p_BRANCH_ID,'') = 'DV0001' AND ISNULL(@p_DEP_ID,'') <> '')
                                    OR (ISNULL(@p_BRANCH_ID,'') <> 'DV0001' AND ISNULL(@p_DEP_ID,'') = ''))
                              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 AND USER_DOMAIN = @TLNAME
--    
--                          UPDATE TL_USER SET TLSUBBRID = @p_BRANCH_ID, SECUR_CODE = @p_DEP_ID, DEP_ID = @p_DEP_ID
--                          WHERE TLNANME = @TLNAME
--                      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 AND USER_DOMAIN = @TLNAME
                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
ALTER PROCEDURE [dbo].[CM_EMPLOYEE_SYNC_INS]
@p_JSON_DATA NVARCHAR(MAX) = NULL
AS
----Phucvh 20/02/23 Store đồng bộ nhân viên từ DB HR
IF (@p_JSON_DATA IS NULL OR @p_JSON_DATA = '')
BEGIN  
SELECT '-1' as Result, '' ID, N'Data NULL Đồng bộ thất bại' ErrorDesc
RETURN '-1'
END

--LUU DATA DONG BO
UPDATE THREAD_TIME_SEND_LOG SET MESSAGE = MESSAGE + @p_JSON_DATA
WHERE TOOL_VALUE = 'SYNC_EMP' AND ID = (SELECT TOP 1 TTSL.ID FROM THREAD_TIME_SEND_LOG TTSL WHERE TTSL.TOOL_VALUE = 'SYNC_EMP' ORDER BY TTSL.ID DESC)

DECLARE @Tbl_Data_From_Json TABLE(
Id	UNIQUEIDENTIFIER,
Loai	INT,
MaNS nvarchar(500),
HoTen nvarchar(500),
MaDVCu nvarchar(500),
DVCu nvarchar(500),
MaDVMoi nvarchar(500),
DVMoi nvarchar(500),
MaCDCu nvarchar(500),
CDCu nvarchar(500),
MaCDMoi nvarchar(500),
CDMoi nvarchar(500),
LoaiHDLD nvarchar(500),
Email nvarchar(500),
Ngay VARCHAR(500),
So nvarchar(500),
NgayHLuc VARCHAR(500),
NgayHetHLuc VARCHAR(500),
TGTao VARCHAR(500)
)

INSERT INTO @Tbl_Data_From_Json
SELECT *
FROM OPENJSON(@p_JSON_DATA)
WITH 
(
Id	UNIQUEIDENTIFIER '$.Id',
Loai	INT '$.Loai',
MaNS nvarchar(500) '$.MaNS',
HoTen nvarchar(500) '$.HoTen',
MaDVCu nvarchar(500) '$.MaDVCu',
DVCu nvarchar(500) '$.DVCu',
MaDVMoi nvarchar(500) '$.MaDVMoi',
DVMoi nvarchar(500) '$.DVMoi',
MaCDCu nvarchar(500) '$.MaCDCu',
CDCu nvarchar(500) '$.CDCu',
MaCDMoi nvarchar(500) '$.MaCDMoi',
CDMoi nvarchar(500) '$.CDMoi',
LoaiHDLD nvarchar(500) '$.LoaiHDLD',
Email nvarchar(500) '$.Email',
Ngay VARCHAR(500) '$.Ngay',
So nvarchar(500) '$.So',
NgayHLuc VARCHAR(500) '$.NgayHLuc',
NgayHetHLuc VARCHAR(500) '$.NgayHetHLuc',
TGTao VARCHAR(500) '$.TGTao'
)

DECLARE 
@Id	UNIQUEIDENTIFIER, @Loai	INT, @MaNS nvarchar(500), @HoTen nvarchar(500), 
@MaDVCu nvarchar(500), @DVCu nvarchar(500), @MaDVMoi nvarchar(500), @DVMoi nvarchar(500), 
@MaCDCu nvarchar(500), @CDCu nvarchar(500), @MaCDMoi nvarchar(500), @CDMoi nvarchar(500), 
@LoaiHDLD nvarchar(500), @Email nvarchar(500), @Ngay VARCHAR(500), @So nvarchar(500), 
@NgayHLuc VARCHAR(500), @NgayHetHLuc VARCHAR(500), @TGTao VARCHAR(500)
                   

BEGIN TRANSACTION
    DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
    SELECT * FROM @Tbl_Data_From_Json
   
   OPEN cur
   
   FETCH NEXT FROM cur INTO @Id,@Loai,@MaNS,@HoTen,@MaDVCu,@DVCu,@MaDVMoi,@DVMoi,@MaCDCu,@CDCu,@MaCDMoi,@CDMoi,@LoaiHDLD,@Email,@Ngay,@So,@NgayHLuc,@NgayHetHLuc,@TGTao
   
   WHILE @@FETCH_STATUS = 0 BEGIN
      
      IF(NOT EXISTS(SELECT 1 FROM CM_EMPLOYEE_SYNC ces WHERE ces.Id = @Id))
      BEGIN
           	INSERT INTO CM_EMPLOYEE_SYNC (Id, Loai, MaNS, HoTen, MaDVCu, DVCu, MaDVMoi, DVMoi, MaCDCu, CDCu, MaCDMoi, CDMoi, LoaiHDLD, Email, Ngay, So, NgayHLuc, NgayHetHLuc, TGTao)
            VALUES(@Id,@Loai,@MaNS,@HoTen,@MaDVCu,@DVCu,@MaDVMoi,@DVMoi,@MaCDCu,@CDCu,@MaCDMoi,@CDMoi,@LoaiHDLD,@Email,CONVERT(DATETIME,@Ngay),@So,CONVERT(DATETIME,@NgayHLuc),CONVERT(DATETIME,@NgayHetHLuc),CONVERT(DATETIME,@TGTao))
      END      
     
     	FETCH NEXT FROM cur INTO @Id,@Loai,@MaNS,@HoTen,@MaDVCu,@DVCu,@MaDVMoi,@DVMoi,@MaCDCu,@CDCu,@MaCDMoi,@CDMoi,@LoaiHDLD,@Email,@Ngay,@So,@NgayHLuc,@NgayHetHLuc,@TGTao
   
   END
   
   CLOSE cur
   DEALLOCATE cur


		IF @@Error <> 0 GOTO ABORT
COMMIT TRANSACTION
SELECT '0' as Result, ''  ID, N'Đồng bộ chi phí sửa chữa tài sản thành công' ErrorDesc
RETURN '0'
ABORT:
BEGIN
    CLOSE cur
    DEALLOCATE cur
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ID, N'Đồng bộ chi phí sửa chữa tài sản thất bại' ErrorDesc
		RETURN '-1'
End
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[THREAD_GET_CONTENT_SEND_MAIL]'
GO
ALTER PROC [dbo].[THREAD_GET_CONTENT_SEND_MAIL]
@p_TIME_RUN_TOOL VARCHAR(50) = NULL,
@p_TIME_SEND_ID INT = NULL, 
@p_TIME_VALUE VARCHAR(100) = NULL,
@p_LOCATION VARCHAR(20) = NULL
AS
BEGIN 

  IF(@p_TIME_VALUE IS NULL OR @p_TIME_VALUE = '' OR @p_TIME_SEND_ID IS NULL OR @p_TIME_SEND_ID = '')
  RETURN '-1'

  DECLARE   @DISTANCE_TIME INT = NULL,
            @DISTANCE_TYPE VARCHAR(20) = NULL,
            @TOOL_NAME NVARCHAR(1000) = NULL,
            @TOOL_VALUE VARCHAR(100) = NULL,
            @EXEC_DT DATETIME = NULL,            
            @MESSAGE NVARCHAR(1000) = NULL,
            @EXEC_TYPE VARCHAR(20) = NULL

  IF(@p_LOCATION = 'EXEC')
  BEGIN
      SET @EXEC_TYPE = 'EXECUTE'
  END
  ELSE
  BEGIN
      SET @EXEC_TYPE = 'AUTO'
  END

  SELECT  @DISTANCE_TIME = TIME_SEND        
        ,@DISTANCE_TYPE = TIME_SEND_TYPE
        ,@TOOL_NAME = TIME_CONTENT
        ,@TOOL_VALUE = TIME_VALUE,
        @EXEC_DT = SENT_DATE                
  FROM THREAD_TIME_SEND 
  WHERE TIME_VALUE = @p_TIME_VALUE

	DECLARE	@TEMP TABLE
			(
				[ID]				VARCHAR(20),
				[EMAIL]				VARCHAR(50),
				[NF_MESSAGE_TYPE]	VARCHAR(500),
				[ROLE_TIFI_TYPE]	VARCHAR(500),
        IS_SENDMAIL BIT,
        IS_RUN_TOOL BIT,
        TOOL_NAME NVARCHAR(100),
        QUERY_SELECT NVARCHAR(MAX)
        
			)
	DECLARE	@TIME_SEND			INT = NULL,
			@TIME_SEND_TYPE		VARCHAR(20) = NULL,
			@TIME_VALUE			VARCHAR(25) = NULL,
			@NF_MESSAGE_TYPE	VARCHAR(50) = NULL,
			@ROLE_TIFI_TYPE		VARCHAR(50) = NULL,
      @SEND_DATE DATETIME = NULL,
      @QUERY_SELECT NVARCHAR(MAX) = NULL,

      @EXEC_DATE VARCHAR(50) = NULL

	SELECT @TIME_SEND = A.TIME_SEND,@TIME_SEND_TYPE = A.TIME_SEND_TYPE,
  @TIME_VALUE = A.TIME_VALUE,@NF_MESSAGE_TYPE = A.NF_MESSAGE_TYPE, 
  @ROLE_TIFI_TYPE = A.ROLE_TIFI_TYPE,@SEND_DATE = A.SENT_DATE
	FROM THREAD_TIME_SEND A
	WHERE (A.[STATUS] = 1 OR @p_LOCATION = 'EXEC') AND A.TIME_VALUE = @p_TIME_VALUE AND A.TIME_SEND_ID = @p_TIME_SEND_ID
	 
  
  --FIX THREAD NẾU START VÀ STOP TOOL NHIỀU LẦN THÌ BỊ DUP EXEC
  IF(@p_LOCATION <> 'EXEC' OR @p_LOCATION IS NULL OR @p_LOCATION = '')
  BEGIN
      IF(DATEDIFF(DAYOFYEAR,CONVERT(DATETIME,@p_TIME_RUN_TOOL,103),@SEND_DATE) <> 0)
      BEGIN  
      	  SET @MESSAGE = N'Ngày không hợp lệ'
          GOTO ABORT
      END
      
  END

  BEGIN --TRANSACTION T1

      IF(@p_TIME_VALUE = 'SYNC_EMP')
      BEGIN --TOOL ĐỒNG BỘ NGƯỜI DÙNG
          --SET @QUERY_SELECT = N'SELECT Id,Loai,MaNS,HoTen,MaDVCu,DVCu,MaDVMoi,DVMoi,MaCDCu,CDCu,MaCDMoi,CDMoi,LoaiHDLD,Email,Ngay,So,NgayHLuc,NgayHetHLuc,TGTao FROM CM_EMPLOYEE_SYNC WHERE DATEDIFF(DAYOFYEAR,CONVERT(DATETIME,''' + (SELECT ca.ParaValue FROM SYS_PARAMETERS ca WHERE ca.ParaKey = 'LAST_SYNC_DATE') + ''',103),TGTao) >= 0  ORDER BY TGTao'
          SET @QUERY_SELECT = N'SELECT Id,Loai,MaNS,HoTen,MaDVCu,DVCu,MaDVMoi,DVMoi,MaCDCu,CDCu,MaCDMoi,CDMoi,LoaiHDLD,Email,Ngay,So,NgayHLuc,NgayHetHLuc,TGTao FROM CM_EMPLOYEE_SYNC ORDER BY TGTao'
    
          INSERT INTO @TEMP (IS_RUN_TOOL, TOOL_NAME, QUERY_SELECT)
          VALUES (1,'CM_EMPLOYEE_SYNC',@QUERY_SELECT);
    
          IF(@p_LOCATION IS NULL)
          BEGIN
                IF @@Error <> 0 GOTO ABORT

                UPDATE SYS_PARAMETERS SET ParaValue = FORMAT(CONVERT(DATE,GETDATE(),103),'dd/MM/yyyy') WHERE ParaKey = 'LAST_SYNC_DATE' 
          END
    
      END  
      ELSE IF(@p_TIME_VALUE = 'CHECK_WARRANTY_DT')
      BEGIN --TOOL GỬI MAIL THÔNG BÁO TÀI SẢN SẮP HẾT HẠN BẢO HÀNH
          INSERT INTO @TEMP (IS_SENDMAIL, TOOL_NAME,NF_MESSAGE_TYPE,ROLE_TIFI_TYPE)
          VALUES (1,N'GỬI MAIL',@NF_MESSAGE_TYPE,@ROLE_TIFI_TYPE);
      END
      ELSE IF(@p_TIME_VALUE = 'CHECK_TRANS_NOT_APPROVE')
      BEGIN --GỬI MAIL NHỮNG GIAO DỊCH CHƯA ĐƯỢC PHÊ DUYỆT
          INSERT INTO @TEMP (IS_SENDMAIL, TOOL_NAME,NF_MESSAGE_TYPE,ROLE_TIFI_TYPE)
          
          SELECT 1,N'CHECK GIAO DỊCH CHƯA DUYỆT',@NF_MESSAGE_TYPE + '|' + PP.ID, @ROLE_TIFI_TYPE + '|' + PP.ID
          FROM (
              SELECT A.USER_MASTER_ID + '|ASS_USE|' + CASE WHEN A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'U' THEN 'KSV'	ELSE 'GDV' END AS ID
              FROM ASS_USE_MULTI_MASTER A
              LEFT JOIN ASS_USE_CONFIRM_MASTER B ON A.USER_MASTER_ID = B.USER_MASTER_ID
              WHERE (A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'E' AND B.USER_CONFIRM_MASTER_ID IS NOT NULL) 
                    OR (A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'U')
              UNION
              SELECT A.TRANS_MULTI_MASTER_ID + '|ASS_TRANSFER|' + CASE WHEN A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'U' THEN 'KSV'	ELSE 'GDV' END AS ID
              FROM ASS_TRANSFER_MULTI_MASTER A                  
              WHERE (A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'E')
                    OR (A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'U')
              UNION
              SELECT A.COL_MULTI_MASTER_ID + '|ASS_COLLECT|' + CASE WHEN A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'U' THEN 'KSV'	ELSE 'GDV' END AS ID
              FROM ASS_COLLECT_MULTI_MASTER A
              LEFT JOIN ASS_COLLECT_CONFIRM_MASTER B ON A.COL_MULTI_MASTER_ID = B.COL_MULTI_MASTER_ID
              WHERE (A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'E' AND B.COL_MULTI_MASTER_CONFIRM_ID IS NOT NULL)
                    OR (A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'U')
              UNION
              SELECT A.LIQ_ID + '|ASS_LIQ|' + CASE WHEN A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'U' THEN 'KSV'	ELSE 'GDV' END AS ID
              FROM ASS_LIQUIDATION A
              WHERE (A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'E')
                    OR (A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'U')
              UNION
              SELECT A.ADDNEW_ID + '|ASS_ADDNEW|' + CASE WHEN A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'U' THEN 'KSV'	ELSE 'GDV' END AS ID
              FROM ASS_ADDNEW A
              WHERE (A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'E')
                    OR (A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'U'))PP              
    
      END
      ELSE IF(@p_TIME_VALUE = 'KH')
      BEGIN --TOOL CHẠY KHẤU HAO             
          SET @EXEC_DATE = CONVERT(VARCHAR(50),@SEND_DATE)
          IF((SELECT (((DATEPART(DW, @SEND_DATE) - 1 ) + @@DATEFIRST ) % 7)) NOT IN ('0','6'))
          BEGIN -- NẾU KHÁC NGÀY T7 VÀ CN THÌ CHẠY
              EXEC ASS_AMORT_StartDO @p_EXECUTE_DATE =  @EXEC_DATE
                                    ,@p_MAKER_ID = 'bichnn'
                                    ,@p_CHECKER_ID = 'bichnn'
                                    ,@p_ASSET_TYPE = NULL
                                    ,@p_BRANCH_ID_LST = NULL   
                                    ,@p_EXEC_TYPE = @EXEC_TYPE
         END                                     
      END

      IF @@Error <> 0 GOTO ABORT
    
      IF(@p_LOCATION IS NULL OR @p_LOCATION = '')
      BEGIN -- NẾU CHẠY TOOL THÌ UPDATE NGÀY, EXEC TRỰC TIẾP THÌ KHÔNG UPDATE NGÀY
          IF(@TIME_SEND_TYPE = 'NGAY')
    			BEGIN
                UPDATE THREAD_TIME_SEND 
                SET SENT_DATE = DATEADD(DAY,@TIME_SEND,@SEND_DATE)
                WHERE TIME_SEND_ID = @p_TIME_SEND_ID AND TIME_VALUE = @p_TIME_VALUE
          END
    			ELSE IF(@TIME_SEND_TYPE = 'THANG')
    			BEGIN
                UPDATE THREAD_TIME_SEND 
                SET SENT_DATE = DATEADD(MONTH,@TIME_SEND,@SEND_DATE)
                WHERE TIME_SEND_ID = @p_TIME_SEND_ID AND TIME_VALUE = @p_TIME_VALUE
    			END
      END

      
  
--  COMMIT TRANSACTION T1
  END
  SELECT * FROM @TEMP  
  
  IF(@p_TIME_VALUE <> 'KH')
  BEGIN
      INSERT INTO THREAD_TIME_SEND_LOG (DISTANCE_TIME, DISTANCE_TYPE, TOOL_NAME, TOOL_VALUE, EXEC_DT, EXEC_TYPE, MESSAGE, CREATE_DT)
      VALUES (@DISTANCE_TIME,@DISTANCE_TYPE, @TOOL_NAME, @TOOL_VALUE, @EXEC_DT,@EXEC_TYPE, N'Thực thi thành công', GETDATE()); 
  END

  RETURN '0'
  ABORT:
  BEGIN
  		--ROLLBACK TRANSACTION T1
      --GHI LOG
        IF(@p_TIME_VALUE <> 'KH')
        BEGIN
            INSERT INTO THREAD_TIME_SEND_LOG (DISTANCE_TIME, DISTANCE_TYPE, TOOL_NAME, TOOL_VALUE, EXEC_DT, EXEC_TYPE, MESSAGE, CREATE_DT)
            VALUES (@DISTANCE_TIME,@DISTANCE_TYPE, @TOOL_NAME, @TOOL_VALUE, @EXEC_DT,@EXEC_TYPE, ISNULL(@MESSAGE,ERROR_MESSAGE()), GETDATE()); 
        END
      RETURN '-1'
  End

	
END