CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_DT_ById]
	@p_IMP_MASTER_ID	VARCHAR(15)
AS	
SELECT A.*,B.BRANCH_NAME,C.DEP_NAME,D.EMP_NAME,E.ASSET_CODE,
F.BRANCH_NAME AS BRANCH_NAME_OLD, G.DEP_NAME AS DEP_NAME_OLD, H.EMP_NAME AS EMP_NAME_OLD
FROM ASS_IMPORT_UPDATE_DT A
LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
LEFT JOIN CM_EMPLOYEE D ON A.EMP_ID = D.EMP_ID
LEFT JOIN ASS_MASTER E ON A.ASSET_ID = E.ASSET_ID

LEFT JOIN CM_BRANCH F ON A.BRANCH_ID_OLD = F.BRANCH_ID
LEFT JOIN CM_DEPARTMENT G ON A.DEP_ID_OLD = G.DEP_ID
LEFT JOIN CM_EMPLOYEE H ON A.EMP_ID_OLD = H.EMP_ID

WHERE A.IMP_MASTER_ID = @p_IMP_MASTER_ID
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_App]'
GO
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_App]
	@p_IMP_MASTER_ID	VARCHAR(15),
  @p_CHECKER_ID VARCHAR(100),
  @p_APPROVE_DT VARCHAR(50)
AS	
	DECLARE @MESSAGE_VALIDATION NVARCHAR(MAX)

	IF(EXISTS(SELECT 1 FROM ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID AND AUTH_STATUS = 'A'))
	BEGIN
		SELECT '-1' Result, N'Phiếu đã được phê duyệt' ErrorDesc 
		RETURN '-1'
	END	

  IF(EXISTS(SELECT 1 FROM ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID AND AUTH_STATUS = 'R'))
	BEGIN
		SELECT '-1' Result, N'Phiếu đang được trả về' ErrorDesc 
		RETURN '-1'
	END	


BEGIN TRANSACTION
		UPDATE ASS_IMPORT_UPDATE_MASTER SET AUTH_STATUS = 'A' , CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@p_APPROVE_DT,103)
    WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID

    --kiểm tra branch,dep của tài sản hiện tại có giống branch,dep import không
    SET @MESSAGE_VALIDATION = (SELECT REPLACE((SELECT 
                                    CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT 0)) = 1 THEN ''
                                    ELSE '|' END 
                                    + N'Tài sản ' + C.ASSET_CODE + N' đã bị thay đổi Đơn vị/Phòng ban so với File Import'
                              FROM ( SELECT B.ASSET_CODE
                                    FROM ASS_IMPORT_UPDATE_DT A
                                    LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID
                                    WHERE (ISNULL(A.BRANCH_ID,'') <> ISNULL(B.BRANCH_ID,'') 
                                          OR ISNULL(A.DEP_ID,'') <> ISNULL(B.DEPT_ID,''))
                                          AND A.IMP_MASTER_ID = @p_IMP_MASTER_ID) C
                              FOR XML PATH (''))
                            , '|', '<br />'))    

    IF(@MESSAGE_VALIDATION IS NOT NULL AND @MESSAGE_VALIDATION <> '')
    BEGIN
        ROLLBACK TRANSACTION
    		SELECT '-1' Result, @MESSAGE_VALIDATION ErrorDesc 
    		RETURN '-1'
    END

    --check branch,dep của tài sản hiện tại có giống với branch,dep của người dùng không
    SET @MESSAGE_VALIDATION = (SELECT REPLACE((SELECT 
                                    CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT 0)) = 1 THEN ''
                                    ELSE '|' END 
                                    + N'Người dùng ' + C.EMP_NAME + '(' + C.EMP_CODE + ')' + N' không cùng đơn vị/phòng ban với tài sản ' + C.ASSET_CODE
                              FROM ( SELECT B.ASSET_CODE,D.EMP_CODE,D.EMP_NAME
                                    FROM ASS_IMPORT_UPDATE_DT A
                                    LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID
                                    LEFT JOIN CM_EMPLOYEE D ON A.EMP_ID = D.EMP_ID
                                    WHERE (ISNULL(D.BRANCH_ID,'') <> ISNULL(B.BRANCH_ID,'') 
                                          OR ISNULL(D.DEP_ID,'') <> ISNULL(B.DEPT_ID,''))
                                          AND A.IMP_MASTER_ID = @p_IMP_MASTER_ID) C
                              FOR XML PATH (''))
                            , '|', '<br />'))     

    IF(@MESSAGE_VALIDATION IS NOT NULL AND @MESSAGE_VALIDATION <> '')
    BEGIN
        ROLLBACK TRANSACTION
    		SELECT '-1' Result, @MESSAGE_VALIDATION ErrorDesc 
    		RETURN '-1'
    END

    UPDATE B SET B.EMP_ID = A.EMP_ID, B.ASSET_SERIAL_NO = A.ASSET_SERIAL_NO, B.ASSET_DESC = A.ASSET_DESC, B.NOTES = A.NOTES
    FROM ASS_IMPORT_UPDATE_DT A
    LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID
    WHERE A.IMP_MASTER_ID = @p_IMP_MASTER_ID

    INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES)
    VALUES (@p_IMP_MASTER_ID, 'APPROVE', @p_CHECKER_ID, GETDATE(), N'Duyệt phiếu cập nhật tài sản', N'Duyệt phiếu cập nhật tài sản thành công');

		IF @@Error <> 0 GOTO ABORT
		
COMMIT TRANSACTION
SELECT '0' as Result, '' ErrorDesc
RETURN '0'

ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ErrorDesc
		RETURN '-1'
End
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_ById]'
GO
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_ById]
	@p_IMP_MASTER_ID	VARCHAR(15)
AS	
SELECT A.* , B.TLFullName AS MAKER_NAME, C.AUTH_STATUS_NAME
FROM ASS_IMPORT_UPDATE_MASTER A
LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS = C.AUTH_STATUS
WHERE A.IMP_MASTER_ID = @p_IMP_MASTER_ID
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_Del]'
GO
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_Del]
	@p_IMP_MASTER_ID	VARCHAR(15)
AS	
	
	IF(EXISTS(SELECT 1 FROM ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID AND AUTH_STATUS = 'U'))
	BEGIN
		SELECT '-1' Result, N'Phiếu đang chờ phê duyệt. Xoá thất bại' ErrorDesc 
		RETURN '-1'
	END	

  IF(EXISTS(SELECT 1 FROM ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID AND AUTH_STATUS = 'A'))
	BEGIN
		SELECT '-1' Result, N'Phiếu đã được phê duyệt. Xoá thất bại' ErrorDesc 
		RETURN '-1'
	END	


BEGIN TRANSACTION
		DELETE ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID
    DELETE ASS_IMPORT_UPDATE_DT WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID

		IF @@Error <> 0 GOTO ABORT
		
COMMIT TRANSACTION
SELECT '0' as Result, '' ErrorDesc
RETURN '0'

ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ErrorDesc
		RETURN '-1'
End
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_Ins]'
GO
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_Ins]
@p_NOTES NVARCHAR(4000) = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID VARCHAR(100)  = NULL,
@p_CREATE_DT	VARCHAR(50) = NULL,
@p_AUTH_STATUS	VARCHAR(50)  = NULL,
@p_CHECKER_ID VARCHAR(100)  = NULL,
@p_APPROVE_DT	VARCHAR(50) = NULL,
@p_JSON_DATA NVARCHAR(MAX) = NULL
AS

  DECLARE 
          @ASSET_ID VARCHAR(15),
          @BRANCH_ID VARCHAR(15),
          @DEP_ID VARCHAR(15),
          @EMP_ID VARCHAR(15),
          @NOTES NVARCHAR(4000),
          @ASSET_DESC NVARCHAR(4000),
          @ASSET_SERIAL_NO NVARCHAR(4000),
          @ASSET_ID_OLD VARCHAR(15),
          @BRANCH_ID_OLD VARCHAR(15),
          @DEP_ID_OLD VARCHAR(15),
          @EMP_ID_OLD VARCHAR(15),
          @NOTES_OLD NVARCHAR(4000),
          @ASSET_DESC_OLD NVARCHAR(4000),
          @ASSET_SERIAL_NO_OLD NVARCHAR(4000)
  
  DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
  SELECT *
  FROM OPENJSON(@P_JSON_DATA)
  WITH 
  (
  ASSET_ID	VARCHAR(15) '$.ASSET_ID',
  BRANCH_ID	VARCHAR(15) '$.BRANCH_ID', 
  DEP_ID VARCHAR(15) '$.DEP_ID',
  EMP_ID VARCHAR(15) '$.EMP_ID',
  NOTES NVARCHAR(4000) '$.NOTES', 
  ASSET_DESC NVARCHAR(4000) '$.ASSET_DESC', 
  ASSET_SERIAL_NO NVARCHAR(4000) '$.ASSET_SERIAL_NO',  
  BRANCH_ID_OLD	VARCHAR(15) '$.BRANCH_ID_OLD', 
  DEP_ID_OLD VARCHAR(15) '$.DEP_ID_OLD',
  EMP_ID_OLD VARCHAR(15) '$.EMP_ID_OLD',
  NOTES_OLD NVARCHAR(4000) '$.NOTES_OLD', 
  ASSET_DESC_OLD NVARCHAR(4000) '$.ASSET_DESC_OLD', 
  ASSET_SERIAL_NO_OLD NVARCHAR(4000) '$.ASSET_SERIAL_NO_OLD'
  )	
  
  OPEN cur
  

BEGIN TRANSACTION

    DECLARE @p_IMP_MASTER_ID VARCHAR(15)
		EXEC SYS_CodeMasters_Gen 'ASS_IMPORT_UPDATE_MASTER', @p_IMP_MASTER_ID out
		IF @p_IMP_MASTER_ID ='' OR @p_IMP_MASTER_ID IS NULL GOTO ABORT

    INSERT INTO ASS_IMPORT_UPDATE_MASTER (IMP_MASTER_ID, NOTES, RECORD_STATUS, AUTH_STATUS, MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT)
    VALUES (@p_IMP_MASTER_ID, @p_NOTES, '1', 'E', @p_MAKER_ID, GETDATE(), NULL, NULL);
    
    
    FETCH NEXT FROM cur INTO @ASSET_ID,@BRANCH_ID,@DEP_ID,@EMP_ID,@NOTES,@ASSET_DESC,@ASSET_SERIAL_NO,@BRANCH_ID_OLD,@DEP_ID_OLD,@EMP_ID_OLD,@NOTES_OLD,@ASSET_DESC_OLD,@ASSET_SERIAL_NO_OLD
    
    WHILE @@FETCH_STATUS = 0 BEGIN
    
      DECLARE @l_IMP_DT_ID VARCHAR(15)
  		EXEC SYS_CodeMasters_Gen 'ASS_IMPORT_UPDATE_DT', @l_IMP_DT_ID out
  		IF @l_IMP_DT_ID='' OR @l_IMP_DT_ID IS NULL GOTO ABORT
  
      INSERT INTO ASS_IMPORT_UPDATE_DT (IMP_DT_ID, IMP_MASTER_ID, ASSET_ID, BRANCH_ID, DEP_ID, EMP_ID, NOTES, ASSET_DESC, ASSET_SERIAL_NO, BRANCH_ID_OLD, DEP_ID_OLD, EMP_ID_OLD, NOTES_OLD, ASSET_DESC_OLD, ASSET_SERIAL_NO_OLD)
      VALUES (@l_IMP_DT_ID, @p_IMP_MASTER_ID, @ASSET_ID, @BRANCH_ID, @DEP_ID, @EMP_ID, @NOTES, @ASSET_DESC, @ASSET_SERIAL_NO,  @BRANCH_ID_OLD, @DEP_ID_OLD, @EMP_ID_OLD, @NOTES_OLD, @ASSET_DESC_OLD, @ASSET_SERIAL_NO_OLD);
    
    FETCH NEXT FROM cur INTO @ASSET_ID,@BRANCH_ID,@DEP_ID,@EMP_ID,@NOTES,@ASSET_DESC,@ASSET_SERIAL_NO,@BRANCH_ID_OLD,@DEP_ID_OLD,@EMP_ID_OLD,@NOTES_OLD,@ASSET_DESC_OLD,@ASSET_SERIAL_NO_OLD
    
    END
    
    CLOSE cur
    DEALLOCATE cur		
		


    INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES)
    VALUES (@p_IMP_MASTER_ID, 'CREATE', @p_MAKER_ID, GETDATE(), N'Nhân viên thêm mới', N'Thêm mới import tài sản thành công');


		IF @@ERROR <> 0 GOTO ABORT

COMMIT TRANSACTION
SELECT '0' as Result,@p_IMP_MASTER_ID ID, N'Thêm mới thành công' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' AS RESULT
		RETURN '-1'
End
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_Search]'
GO
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_Search]
		@p_IMP_MASTER_ID	VARCHAR(15)  = NULL,
		@p_NOTES	NVARCHAR(4000)  = NULL,
		@p_MAKER_NAME	NVARCHAR(1000)  = NULL,
		@p_TOP INT		= NULL
AS 
BEGIN -- PAGING

	IF @p_TOP IS NULL OR @p_TOP  = ''
	BEGIN
	-- PAGING BEGIN
		SELECT A.*,B.TLFullName AS MAKER_NAME, C.AUTH_STATUS_NAME
		-- SELECT END
		FROM ASS_IMPORT_UPDATE_MASTER A
    LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
    LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS = C.AUTH_STATUS
    WHERE 1=1
    AND (@p_IMP_MASTER_ID = A.IMP_MASTER_ID OR @p_IMP_MASTER_ID IS NULL OR @p_IMP_MASTER_ID = '')
    AND (B.TLFullName  COLLATE Latin1_general_CI_AI LIKE N'%' + @p_MAKER_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_MAKER_NAME IS NULL OR @p_MAKER_NAME = '')
    AND (A.NOTES  COLLATE Latin1_general_CI_AI LIKE N'%' + @p_NOTES + N'%' COLLATE Latin1_general_CI_AI OR @p_NOTES IS NULL OR @p_NOTES = '')
		ORDER BY A.CREATE_DT DESC
	-- PAGING END
	END
	ELSE
	BEGIN
	-- PAGING BEGIN
		SELECT TOP (@p_TOP)	A.*, B.TLFullName AS MAKER_NAME, C.AUTH_STATUS_NAME
		-- SELECT END
		FROM ASS_IMPORT_UPDATE_MASTER A
    LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
    LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS = C.AUTH_STATUS
    WHERE 1=1
    AND (@p_IMP_MASTER_ID = A.IMP_MASTER_ID OR @p_IMP_MASTER_ID IS NULL OR @p_IMP_MASTER_ID = '')
    AND (B.TLFullName  COLLATE Latin1_general_CI_AI LIKE N'%' + @p_MAKER_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_MAKER_NAME IS NULL OR @p_MAKER_NAME = '')
    AND (A.NOTES  COLLATE Latin1_general_CI_AI LIKE N'%' + @p_NOTES + N'%' COLLATE Latin1_general_CI_AI OR @p_NOTES IS NULL OR @p_NOTES = '')
		ORDER BY A.CREATE_DT DESC
	-- PAGING END
	END
END -- PAGING
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_SendAppr]'
GO
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_SendAppr]
	@p_IMP_MASTER_ID	VARCHAR(15)
AS	
	
	IF(EXISTS(SELECT 1 FROM ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID AND AUTH_STATUS = 'U'))
	BEGIN
		SELECT '-1' Result, N'Phiếu đã được gửi phê duyệt' ErrorDesc 
		RETURN '-1'
	END	

  IF(EXISTS(SELECT 1 FROM ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID AND AUTH_STATUS = 'A'))
	BEGIN
		SELECT '-1' Result, N'Phiếu đã được phê duyệt' ErrorDesc 
		RETURN '-1'
	END	


BEGIN TRANSACTION
		UPDATE ASS_IMPORT_UPDATE_MASTER SET AUTH_STATUS = 'U' WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID

		IF @@Error <> 0 GOTO ABORT
		
COMMIT TRANSACTION
SELECT '0' as Result, N'Gửi phê duyệt thành công' ErrorDesc
RETURN '0'

ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ErrorDesc
		RETURN '-1'
End
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_Upd]'
GO
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_Upd]
@p_IMP_MASTER_ID VARCHAR(15) = NULL,
@p_NOTES NVARCHAR(4000) = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID VARCHAR(100)  = NULL,
@p_CREATE_DT	VARCHAR(50) = NULL,
@p_AUTH_STATUS	VARCHAR(50)  = NULL,
@p_CHECKER_ID VARCHAR(100)  = NULL,
@p_APPROVE_DT	VARCHAR(50) = NULL,
@p_JSON_DATA NVARCHAR(MAX) = NULL
AS

  DECLARE 
          @ASSET_ID VARCHAR(15),
          @BRANCH_ID VARCHAR(15),
          @DEP_ID VARCHAR(15),
          @EMP_ID VARCHAR(15),
          @NOTES NVARCHAR(4000),
          @ASSET_DESC NVARCHAR(4000),
          @ASSET_SERIAL_NO NVARCHAR(4000),
          @ASSET_ID_OLD VARCHAR(15),
          @BRANCH_ID_OLD VARCHAR(15),
          @DEP_ID_OLD VARCHAR(15),
          @EMP_ID_OLD VARCHAR(15),
          @NOTES_OLD NVARCHAR(4000),
          @ASSET_DESC_OLD NVARCHAR(4000),
          @ASSET_SERIAL_NO_OLD NVARCHAR(4000)
  
  DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
  SELECT *
  FROM OPENJSON(@P_JSON_DATA)
  WITH 
  (
  ASSET_ID	VARCHAR(15) '$.ASSET_ID',
  BRANCH_ID	VARCHAR(15) '$.BRANCH_ID', 
  DEP_ID VARCHAR(15) '$.DEP_ID',
  EMP_ID VARCHAR(15) '$.EMP_ID',
  NOTES NVARCHAR(4000) '$.NOTES', 
  ASSET_DESC NVARCHAR(4000) '$.ASSET_DESC', 
  ASSET_SERIAL_NO NVARCHAR(4000) '$.ASSET_SERIAL_NO',  
  BRANCH_ID_OLD	VARCHAR(15) '$.BRANCH_ID_OLD', 
  DEP_ID_OLD VARCHAR(15) '$.DEP_ID_OLD',
  EMP_ID_OLD VARCHAR(15) '$.EMP_ID_OLD',
  NOTES_OLD NVARCHAR(4000) '$.NOTES_OLD', 
  ASSET_DESC_OLD NVARCHAR(4000) '$.ASSET_DESC_OLD', 
  ASSET_SERIAL_NO_OLD NVARCHAR(4000) '$.ASSET_SERIAL_NO_OLD'
  )	
  
  OPEN cur
  

BEGIN TRANSACTION

    UPDATE ASS_IMPORT_UPDATE_MASTER
    SET NOTES = @p_NOTES
    WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID
    
    
    FETCH NEXT FROM cur INTO @ASSET_ID,@BRANCH_ID,@DEP_ID,@EMP_ID,@NOTES,@ASSET_DESC,@ASSET_SERIAL_NO,@BRANCH_ID_OLD,@DEP_ID_OLD,@EMP_ID_OLD,@NOTES_OLD,@ASSET_DESC_OLD,@ASSET_SERIAL_NO_OLD
    
    WHILE @@FETCH_STATUS = 0 BEGIN
    
      DECLARE @l_IMP_DT_ID VARCHAR(15)
  		EXEC SYS_CodeMasters_Gen 'ASS_IMPORT_UPDATE_DT', @l_IMP_DT_ID out
  		IF @l_IMP_DT_ID='' OR @l_IMP_DT_ID IS NULL GOTO ABORT
  
      INSERT INTO ASS_IMPORT_UPDATE_DT (IMP_DT_ID, IMP_MASTER_ID, ASSET_ID, BRANCH_ID, DEP_ID, EMP_ID, NOTES, ASSET_DESC, ASSET_SERIAL_NO, BRANCH_ID_OLD, DEP_ID_OLD, EMP_ID_OLD, NOTES_OLD, ASSET_DESC_OLD, ASSET_SERIAL_NO_OLD)
      VALUES (@l_IMP_DT_ID, @p_IMP_MASTER_ID, @ASSET_ID, @BRANCH_ID, @DEP_ID, @EMP_ID, @NOTES, @ASSET_DESC, @ASSET_SERIAL_NO,  @BRANCH_ID_OLD, @DEP_ID_OLD, @EMP_ID_OLD, @NOTES_OLD, @ASSET_DESC_OLD, @ASSET_SERIAL_NO_OLD);
    
    FETCH NEXT FROM cur INTO @ASSET_ID,@BRANCH_ID,@DEP_ID,@EMP_ID,@NOTES,@ASSET_DESC,@ASSET_SERIAL_NO,@BRANCH_ID_OLD,@DEP_ID_OLD,@EMP_ID_OLD,@NOTES_OLD,@ASSET_DESC_OLD,@ASSET_SERIAL_NO_OLD
    
    END
    
    CLOSE cur
    DEALLOCATE cur			
		


    INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES)
    VALUES (@p_IMP_MASTER_ID, 'UPDATE', @p_MAKER_ID, GETDATE(), N'Nhân viên cập nhật', N'Cập nhật import tài sản thành công');


		IF @@ERROR <> 0 GOTO ABORT

COMMIT TRANSACTION
SELECT '0' as Result,@p_IMP_MASTER_ID ID, N'Cập nhật thành công' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' AS RESULT
		RETURN '-1'
End
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[CM_DEPARTMENT_GET_BLOCK_CENTER]'
GO
CREATE PROCEDURE [dbo].[CM_DEPARTMENT_GET_BLOCK_CENTER]
	@p_DEP_ID_INPUT VARCHAR(15),
	@p_BLOCK_ID VARCHAR(15)  OUT,
  @p_CENTER_ID VARCHAR(15)  OUT,
  @p_DEP_ID VARCHAR(15)  OUT
AS

SELECT 
@p_BLOCK_ID = (CASE WHEN DP.TYPE = 'K' THEN DP.DEP_ID ELSE K.DEP_ID END),
@p_CENTER_ID = (CASE WHEN DP.TYPE = 'TT' THEN DP.DEP_ID ELSE TT.DEP_ID END),
@p_DEP_ID = (CASE WHEN DP.TYPE = 'PB' THEN DP.DEP_ID ELSE NULL END)

FROM CM_DEPARTMENT DP
LEFT JOIN CM_DEPARTMENT TT ON DP.FATHER_ID = TT.DEP_ID --TRUNG TÂM
LEFT JOIN CM_DEPARTMENT K ON DP.KHOI_ID = K.DEP_ID --KHỐI
WHERE DP.DEP_ID = @p_DEP_ID_INPUT
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_Import]'
GO
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_Import] 	
	@P_JSON_DATA NVARCHAR(MAX)
AS
BEGIN

	DECLARE @TMPASSETCODE TABLE(
		ASSET_CODE		VARCHAR(100),
		BRANCH_CODE		VARCHAR(100),
		DEP_CODE		VARCHAR(100),
    EMP_CODE		VARCHAR(100),
    NOTES NVARCHAR(4000),
    ASSET_DESC NVARCHAR(4000),
    ASSET_SERIAL_NO NVARCHAR(4000)
    
	)

	INSERT INTO @TMPASSETCODE
  SELECT *
  FROM OPENJSON(@P_JSON_DATA)
  WITH 
  (
  ASSET_CODE	VARCHAR(100) '$.ASSET_CODE',
  BRANCH_CODE	VARCHAR(1000) '$.BRANCH_CODE', 
  DEP_CODE VARCHAR(100) '$.DEP_CODE',
  EMP_CODE VARCHAR(100) '$.EMP_CODE',
  NOTES NVARCHAR(4000) '$.NOTES', 
  ASSET_DESC NVARCHAR(4000) '$.ASSET_DESC', 
  ASSET_SERIAL_NO NVARCHAR(4000) '$.ASSET_SERIAL_NO'
  )	

	DECLARE @ERROR_MESSAGE NVARCHAR(MAX)

  SELECT @ERROR_MESSAGE = STUFF( (
  SELECT ',' + A.ROW FROM (
  SELECT CONVERT(VARCHAR(10),ROW_NUMBER() OVER (ORDER BY (SELECT 1))) AS ROW, T.ASSET_CODE
  FROM @TMPASSETCODE T ) A
  WHERE A.ASSET_CODE IS NULL
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

	IF(LEN(@ERROR_MESSAGE) > 0)
	BEGIN
		SET @ERROR_MESSAGE = N'Dòng: ' + @ERROR_MESSAGE + N' mã tài sản không được để trống'
		EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
		RETURN '-1'
	END

  SELECT @ERROR_MESSAGE = STUFF( (
  SELECT ',' + A.ROW FROM (
  SELECT CONVERT(VARCHAR(10),ROW_NUMBER() OVER (ORDER BY (SELECT 1))) AS ROW, T.BRANCH_CODE
  FROM @TMPASSETCODE T 
  LEFT JOIN ASS_MASTER B ON T.ASSET_CODE = B.ASSET_CODE
  WHERE B.BRANCH_ID IS NOT NULL AND B.BRANCH_ID <> '' ) A
  WHERE A.BRANCH_CODE IS NULL 
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

	IF(LEN(@ERROR_MESSAGE) > 0)
	BEGIN
		SET @ERROR_MESSAGE = N'Dòng: ' + @ERROR_MESSAGE + N' mã đơn vị không được để trống'
		EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
		RETURN '-1'
	END

  SELECT @ERROR_MESSAGE = STUFF( (
  SELECT T.EMP_CODE
  FROM @TMPASSETCODE T 
  LEFT JOIN CM_EMPLOYEE E ON T.EMP_CODE = E.EMP_CODE
  WHERE E.EMP_ID IS NULL AND T.EMP_CODE IS NOT NULL AND T.EMP_CODE <> ''
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

	IF(LEN(@ERROR_MESSAGE) > 0)
	BEGIN
		SET @ERROR_MESSAGE = N'Người dùng không tồn tại trên hệ thống: ' + @ERROR_MESSAGE
		EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
		RETURN '-1'
	END

  SELECT @ERROR_MESSAGE = STUFF( (
  SELECT N'Người dùng '  + A.EMP_CODE + N' và tài sản ' + A.ASSET_CODE + N' không cùng Đơn vị/Phòng ban với nhau </br>'
  FROM @TMPASSETCODE A
  LEFT JOIN ASS_MASTER B ON A.ASSET_CODE = B.ASSET_CODE
  LEFT JOIN CM_EMPLOYEE E ON A.EMP_CODE = E.EMP_CODE
  WHERE A.EMP_CODE IS NOT NULL AND A.EMP_CODE <> '' AND (ISNULL(E.BRANCH_ID,'') <> ISNULL(B.BRANCH_ID,'') OR ISNULL(E.DEP_ID,'') <> ISNULL(B.DEPT_ID,''))
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

	IF(LEN(@ERROR_MESSAGE) > 0)
	BEGIN
		SET @ERROR_MESSAGE = @ERROR_MESSAGE
		EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
		RETURN '-1'
	END



	SELECT @ERROR_MESSAGE = STUFF( (
	SELECT ',' + T.ASSET_CODE 
  FROM @TMPASSETCODE T 
	LEFT JOIN ASS_MASTER B ON T.ASSET_CODE = B.ASSET_CODE 
  WHERE B.ASSET_ID IS NULL
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

	IF(LEN(@ERROR_MESSAGE) > 0)
	BEGIN
		SET @ERROR_MESSAGE = N'Mã tài sản không tồn tại trong hệ thống: ' + @ERROR_MESSAGE
		EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
		RETURN '-1'
	END


	SELECT @ERROR_MESSAGE = STUFF( (
	SELECT ',' + T.ASSET_CODE FROM @TMPASSETCODE T 
  GROUP BY T.ASSET_CODE
  HAVING COUNT(*) > 1
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

	IF(LEN(@ERROR_MESSAGE) > 0)
	BEGIN
		SET @ERROR_MESSAGE = N'Mã tài sản bị trùng: ' + @ERROR_MESSAGE
		EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
		RETURN '-1'
	END


	SELECT @ERROR_MESSAGE = STUFF( (
	SELECT ',' + T.BRANCH_CODE FROM @TMPASSETCODE T 
	WHERE T.BRANCH_CODE IS NOT NULL AND T.BRANCH_CODE <> '' 
  AND NOT EXISTS(SELECT 1 FROM CM_BRANCH B WHERE B.BRANCH_CODE = T.BRANCH_CODE AND B.RECORD_STATUS = '1') 
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

	IF(LEN(@ERROR_MESSAGE) > 0)
	BEGIN
		SET @ERROR_MESSAGE = N'Mã đơn vị không tồn tại trong hệ thống: ' + @ERROR_MESSAGE
    EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
		RETURN '-1'
	END


	SELECT @ERROR_MESSAGE = STUFF( (
	  SELECT ',' + A.ASSET_CODE
    FROM @TMPASSETCODE A
    LEFT JOIN ASS_MASTER B ON A.ASSET_CODE = B.ASSET_CODE
    LEFT JOIN CM_BRANCH C ON B.BRANCH_ID = C.BRANCH_ID
    LEFT JOIN CM_DEPARTMENT D ON B.DEPT_ID = D.DEP_ID
    WHERE (ISNULL(C.BRANCH_CODE,'') <> ISNULL(A.BRANCH_CODE,'')
          OR ISNULL(D.DEP_CODE,'') <> ISNULL(A.DEP_CODE,'')) AND A.BRANCH_CODE IS NOT NULL AND A.BRANCH_CODE <> ''
    FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

	IF(LEN(@ERROR_MESSAGE) > 0)
	BEGIN
		SET @ERROR_MESSAGE = N'Những tài sản có Đơn vị/Phòng ban không khớp với tài sản trên hệ thống: ' + @ERROR_MESSAGE
    EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
		RETURN '-1'
	END	

  
  SELECT B.ASSET_ID,B.ASSET_CODE,B.ASSET_NAME,
          G.BRANCH_ID,G.BRANCH_CODE,G.BRANCH_NAME,
          H.DEP_ID,H.DEP_CODE,H.DEP_NAME,
          E.EMP_ID,E.EMP_CODE,E.EMP_NAME,
          A.NOTES,A.ASSET_DESC,A.ASSET_SERIAL_NO,
          
          B.ASSET_ID AS ASSET_ID_OLD,B.ASSET_CODE AS ASSET_CODE_OLD,B.ASSET_NAME AS ASSET_NAME_OLD,
          C.BRANCH_ID AS BRANCH_ID_OLD,C.BRANCH_CODE AS BRANCH_CODE_OLD,C.BRANCH_NAME AS BRANCH_NAME_OLD,
          D.DEP_ID AS DEP_ID_OLD,D.DEP_CODE AS DEP_CODE_OLD,D.DEP_NAME AS DEP_NAME_OLD,
          F.EMP_ID AS EMP_ID_OLD,F.EMP_CODE AS EMP_CODE_OLD,F.EMP_NAME AS EMP_NAME_OLD,
          B.NOTES AS NOTES_OLD,B.ASSET_DESC AS ASSET_DESC_OLD,B.ASSET_SERIAL_NO AS ASSET_SERIAL_NO_OLD
        
        
  FROM @TMPASSETCODE A
  LEFT JOIN ASS_MASTER B ON A.ASSET_CODE = B.ASSET_CODE

  LEFT JOIN CM_BRANCH C ON B.BRANCH_ID = C.BRANCH_ID
  LEFT JOIN CM_DEPARTMENT D ON B.DEPT_ID = D.DEP_ID
  LEFT JOIN CM_EMPLOYEE F ON B.EMP_ID = F.EMP_ID


  LEFT JOIN CM_BRANCH G ON A.BRANCH_CODE = G.BRANCH_CODE
  LEFT JOIN CM_DEPARTMENT H ON A.DEP_CODE = H.DEP_CODE
  LEFT JOIN CM_EMPLOYEE E ON A.EMP_CODE = E.EMP_CODE
  
	
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_COST_ALLOCATION_Import]'
GO
CREATE PROCEDURE [dbo].[ASS_COST_ALLOCATION_Import]
@p_XmlData XML = NULL

AS
DECLARE 
@BRANCH_CODE VARCHAR(25),
@DEP_CODE VARCHAR(25),
@COST_AMOUNT DECIMAL(18,2),
@COST_MONTH INT,
@COST_RATE DECIMAL(18,2)
	

DECLARE @TableCost  TABLE (
BRANCH_ID   VARCHAR(25),
DEPT_ID     VARCHAR(25),
COST_AMOUNT VARCHAR(25),
COST_RATE   VARCHAR(25),
COST_MONTH  VARCHAR(25)
)

  Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output,@p_XmlData

 SELECT T.BRANCH_CODE
       ,T.DEP_CODE
       ,T.COST_AMOUNT
       ,T.COST_MONTH
       ,T.COST_RATE,
       cb.BRANCH_ID,
       cb.BRANCH_NAME,
       cd.DEP_ID,
       cd.DEP_NAME FROM 
  (
	SELECT BRANCH_CODE,DEP_CODE,COST_AMOUNT,COST_MONTH,COST_RATE
	FROM OPENXML(@hdoc,'/Root/XmlData',2)
	WITH 
	(
		BRANCH_CODE varchar(15),
		DEP_CODE	VARCHAR(15),
		COST_AMOUNT DECIMAL(18,2),
    COST_MONTH INT,
		COST_RATE DECIMAL(18,2)		
	)

) T
LEFT JOIN CM_BRANCH cb ON cb.BRANCH_CODE=T.BRANCH_CODE
LEFT JOIN CM_DEPARTMENT cd ON cd.DEP_CODE=T.DEP_CODE