AMS - VB

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