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