AMS - VB

ALTER TABLE dbo.CM_EMPLOYEE_SYNC

ADD SYNC_DATE NVARCHAR(100) NULL 

UPDATE CM_EMPLOYEE_SYNC SET SYNC_DATE = '19/04/2023'

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