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 @SYNC_DATE VARCHAR(100) = FORMAT(GETDATE(),'dd/MM/yyyy')

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

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 (EXISTS(SELECT 1 FROM SYS_PARAMETERS WHERE ParaKey = ''GETALL_RECORD_EMPSYNC'' AND ParaValue = ''1'') OR (DATEDIFF(DAYOFYEAR,CONVERT(DATETIME,''' + (ISNULL((SELECT ca.ParaValue FROM SYS_PARAMETERS ca WHERE ca.ParaKey = 'LAST_SYNC_DATE'),GETDATE())) + ''',103),TGTao) >= 0))  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