CREATE FUNCTION [dbo].[FN_GET_USER_MANAGER_BY_DVCM] (@p_DVDM_ID VARCHAR(15),@p_ROLE VARCHAR(50) NULL)
RETURNS @result TABLE (
  TLNANME  VARCHAR(20),
	ROLE_ID VARCHAR(20)
)
AS
BEGIN
  IF(@p_ROLE IS NULL OR @p_ROLE='')
    SET @p_ROLE ='PTGD,GDK'
	INSERT INTO @result SELECT DISTINCT C.TLNANME, ROLE_NEW
	FROM dbo.PL_COSTCENTER_DT A 
		LEFT JOIN dbo.PL_COSTCENTER B ON B.COST_ID = A.COST_ID
		JOIN FN_GET_USER_BY_ROLE(@p_ROLE,'','') C ON (C.BRANCH_ID=A.BRANCH_ID AND C.DEP_ID=A.DEP_ID)
		LEFT JOIN dbo.CM_EMPLOYEE_LOG D ON D.USER_DOMAIN= C.TLNANME 
	WHERE B.DVDM_ID = @p_DVDM_ID
	RETURN;
END

GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[CON_LAYOUT_BLUEPRINT_GET_INFO_DOC_EDIT_FILE]'
GO

CREATE   PROCEDURE [dbo].[CON_LAYOUT_BLUEPRINT_GET_INFO_DOC_EDIT_FILE]
@p_REQ_ID	varchar(50)  = NULL
AS
DECLARE @MAKER NVARCHAR(500),@MAKER_CREATE_DT NVARCHAR(1000),@MAKER_TITLE NVARCHAR(500) = N'Người tạo phiếu',
@GDDV NVARCHAR(500),@GDDV_APPROVE_DT NVARCHAR(1000),@GDDV_TITLE  NVARCHAR(500)= N'Trưởng đơn vị',
@GDK_HT NVARCHAR(500),@GDK_APPROVE_DT NVARCHAR(1000),@GDK_TITLE  NVARCHAR(500) = N'Giám đốc khối',
@PTGD_TC NVARCHAR(500),@PTGD_TC_APPROVE_DT NVARCHAR(1000),@PTGD_TC_TITLE  NVARCHAR(500) = N'Phó tổng giám đốc tài chính',
@PTGD_VH NVARCHAR(500),@PTGD_VH_APPROVE_DT NVARCHAR(1000),@PTGD_VH_TITLE  NVARCHAR(500)  = N'Phó tổng giám đốc vận hành',
@TKTGD NVARCHAR(500),@TKTGD_APPROVE_DT NVARCHAR(1000),@TKTGD_TITLE  NVARCHAR(500)  = N'Thư kí TGD',
@TGD NVARCHAR(500),@TGD_APPROVE_DT NVARCHAR(1000),@TGD_TITLE  NVARCHAR(500) = N'Tổng giám đốc',
@LAYOUT_TYPE VARCHAR(10)
--Lấy tên của các user
SELECT @LAYOUT_TYPE =  A.LAYOUT_TYPE,@MAKER = B.TLFullName, @MAKER_CREATE_DT = N'Đã tạo lúc: ' + FORMAT(CREATE_DT, 'g', 'en-gb')
FROM CON_LAYOUT_BLUEPRINT A
LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
WHERE CON_LAYOUT_BLUEPRINT_ID = @p_REQ_ID

SELECT @GDDV = B.TLFullName,@GDDV_APPROVE_DT = N'Đã chấp thuận lúc: ' + FORMAT(A.APPROVE_DT , 'g', 'en-gb')
FROM PL_REQUEST_PROCESS A
LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'APPNEW'

SELECT @GDK_HT = B.TLFullName, @GDK_APPROVE_DT = N'Đã chấp thuận lúc: ' + FORMAT(A.APPROVE_DT , 'g', 'en-gb')
FROM PL_REQUEST_PROCESS A
LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'GDK_HT'

SELECT @PTGD_TC = B.TLFullName, @PTGD_TC_APPROVE_DT = N'Đã chấp thuận lúc: ' + FORMAT(A.APPROVE_DT , 'g', 'en-gb')
FROM PL_REQUEST_PROCESS A
LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'PTGD_TC'

SELECT @PTGD_VH = B.TLFullName, @PTGD_VH_APPROVE_DT = N'Đã chấp thuận lúc: ' + FORMAT(A.APPROVE_DT , 'g', 'en-gb')  
FROM PL_REQUEST_PROCESS A
LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'PTGD_VH'

SELECT @TKTGD = B.TLFullName, @TKTGD_APPROVE_DT = N'Đã chấp thuận lúc: ' + FORMAT(A.APPROVE_DT , 'g', 'en-gb') 
FROM PL_REQUEST_PROCESS A
LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'TKTGD'

SELECT @TGD = B.TLFullName, @TGD_APPROVE_DT = N'Đã chấp thuận lúc: ' + FORMAT(A.APPROVE_DT , 'g', 'en-gb') 
FROM PL_REQUEST_PROCESS A
LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'TGD'

SELECT '0' as Result, '' ErrorDesc,
@MAKER AS MAKER_NAME, @MAKER_CREATE_DT AS MAKER_CREATE_DT, @MAKER_TITLE AS MAKER_TITLE,
ISNULL(@GDDV,'') AS GDDV_NAME, ISNULL(@GDDV_APPROVE_DT,'') AS GDDV_APPROVE_DT, @GDDV_TITLE AS GDDV_TITLE,
ISNULL(@GDK_HT,'') AS GDK_NAME, ISNULL(@GDK_APPROVE_DT,'') AS GDK_APPROVE_DT,  @GDK_TITLE AS GDK_TITLE,
ISNULL(@PTGD_TC,'') AS PTGD_TC_NAME, ISNULL(@PTGD_TC_APPROVE_DT,'') AS PTGD_TC_APPROVE_DT, @PTGD_TC_TITLE AS PTGD_TC_TITLE,
ISNULL(@PTGD_VH,'') AS PTGD_VH_NAME, ISNULL(@PTGD_VH_APPROVE_DT,'') AS PTGD_VH_APPROVE_DT, @PTGD_VH_TITLE AS PTGD_VH_TITLE,
ISNULL(@TKTGD,'') AS TKTGD_NAME, ISNULL(@TKTGD_APPROVE_DT,'') AS TKTGD_APPROVE_DT,@TKTGD_TITLE AS TKTGD_TITLE,
ISNULL(@TGD,'') AS TGD_NAME,ISNULL(@TGD_APPROVE_DT,'') AS TGD_APPROVE_DT,@TGD_TITLE AS TGD_TITLE,
N'Phê duyệt của cấp có thẩm quyền' AS PDCCTQ,
N'Phê duyệt tổng giám đốc' AS PDTGD,
@LAYOUT_TYPE AS LAYOUT_TYPE
RETURN '0'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[CON_LAYOUT_BLUEPRINT_GET_INFO_FILE]'
GO


create   PROCEDURE [dbo].[CON_LAYOUT_BLUEPRINT_GET_INFO_FILE]
@p_REF_ID	varchar(50)  = NULL,
@p_TLNAME	varchar(15)  = NULL
AS
SELECT '0' as Result, '' ErrorDesc,A.*
FROM CM_ATTACH_FILE A
WHERE REF_ID = @p_REF_ID
RETURN '0'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[RET_MASTER_IMPORT]'
GO

/* 
[dbo].[RET_MASTER_Del] '' 
*/
CREATE PROCEDURE [dbo].[RET_MASTER_IMPORT]
-------------IMPORT QUẢN LÝ BẤT ĐỘNG SẢN---------------
AS
BEGIN TRANSACTION

  DECLARE @p_ASSET_ID VARCHAR(15) = NULL,
    @P_ASSET_CODE VARCHAR(100) = NULL,
    @P_OWNER NVARCHAR(500) = NULL,
    @p_LENGTH	DECIMAL(18,2) = NULL,
    @p_ADDR nvarchar(1000) = NULL, -- hungdv hieu chinh yeu cau anh toi 250520
    @p_WIDTH	DECIMAL(18,2) = NULL,
    @p_FLOORS int = NULL,
    @p_CURRENT_STATE	nvarchar(100)  = NULL,
    @p_RET_TYPE	varchar(15)  = NULL,
    @p_STATUS	varchar(15)  = NULL,
    @p_LAND_SQUARE	DECIMAL(18,2)  = NULL,
    @p_CONSTRUCT_SQUARE	DECIMAL(18,2)  = NULL,
    @p_TOTAL_SQUARE	DECIMAL(18,2)  = NULL,
    @p_HOUSEDES	nvarchar(100)  = NULL,
    @p_PURPOSE_IN_USE	nvarchar(500)  = NULL,
    @P_W_USE_CON NVARCHAR(100) = NULL,
    @p_USE_STATUS	varchar(15)  = NULL,
    @p_CONST_STATUS	varchar(15)  = NULL,
    @p_OWNER_TYPE	varchar(15)  = NULL,
    @p_USE_PERIOD	int  = NULL,
    @p_NOTES	nvarchar(1000)  = NULL,
    @p_RECORD_STATUS	varchar(1)  = NULL,
    @p_AUTH_STATUS	varchar(1)  = NULL,
    @p_MAKER_ID	varchar(15)  = NULL,
    @p_CREATE_DT	VARCHAR(20) = NULL,
    @p_CHECKER_ID	varchar(15)  = NULL,
    @p_APPROVE_DT	VARCHAR(20) = NULL,
    @P_BUY_DT VARCHAR(20) = NULL,
    @p_USE_FORM VARCHAR(1),
    @p_USE_FORM_DETAIL nvarchar(MAX),
    @p_USE_SOURCE nvarchar(MAX),
    @p_USE_PERIOD_DT DATETIME,
    @p_OWNER_TYPE_DETAIL NVARCHAR(MAX),
    @p_RET_SAVE_CODE VARCHAR(100),
    @p_BRANCH_USE nvarchar(500),
    @p_FLUCTUATING_DT varchar(20) = null,
    @p_REASON_FLUCTUATING nvarchar(max) = null,
    @p_RET_FORM VARCHAR(15) = NULL,
    @p_BRANCH_USE_ID VARCHAR(15) = NULL,
    @p_ASSET_NAME NVARCHAR(1000) = NULL,
    @p_ASSET_DESC NVARCHAR(MAX) = NULL,
    @p_BUY_PRICE DECIMAL(18,2) = NULL,
    @p_BRANCH_CREATE VARCHAR(15) = NULL,
    @p_REASON nvarchar(1000) = NULL
	
    DECLARE @p_BRANCH_USE_CODE VARCHAR(15),
            @p_BRANCH_USE_NAME NVARCHAR(200),
            @p_LOAI_BDS NVARCHAR(100),
            @p_HINH_THUC_SD NVARCHAR(100),
            @p_THOI_HAN_SD NVARCHAR(100),
            @p_THOI_HAN_SD_DEN_NGAY DATETIME,
            @p_HINH_THUC_BDS NVARCHAR(100),
            @p_TINH_TRANG_SD_DAT NVARCHAR(100),
            @p_TINH_TRANG_XD NVARCHAR(100),
            @p_HIEN_TRANG_PHAP_LY_TS NVARCHAR(100),
            @p_LOAI_SO_HUU NVARCHAR(100),
            @p_LOAI_SO_HUU_KHAC NVARCHAR(MAX),
            @p_BRANCH_CREATE_CODE VARCHAR(15),
            @p_BRANCH_CREATE_NAME NVARCHAR(200)

    DECLARE @l_RET_ID VARCHAR(15)

    DECLARE ImportCursor CURSOR FOR
      SELECT bi.MA_TAI_SAN,
            bi.TEN_TAI_SAN,
            bi.MO_TA,
            bi.NGUYEN_GIA_TAI_SAN,
            bi.MA_DV_TAO,
            bi.TEN_DV_TAO,
            bi.MA_DV_SU_DUNG,
            bi.TEN_DV_SU_DUNG,
            bi.DIA_CHI_BDS,
            bi.HIEN_TRANG_BDS,
            bi.LOAI_BDS,
            bi.DAI,
            bi.RONG,
            bi.DIEN_TICH_DAT,
            bi.NGUON_GOC_SD,
            bi.HINH_THUC_SD,
            bi.HINH_THUC_SD_KHAC,
            bi.DIEN_TICH_XD,
            bi.SO_TANG,
            bi.DIEN_TICH_SAN_SD,
            bi.NGAY_MUA_THUE_BDS,
            bi.THOI_HAN_SD,
            bi.THOI_HAN_SD_DEN_NGAY,
            bi.HINH_THUC_BDS,
            bi.TINH_TRANG_SD_DAT,
            bi.TINH_TRANG_XD,
            bi.MUC_DICH_SD_DAT,
            bi.CONG_NANG_SD,
            bi.KET_CAU_NHA,
            bi.HIEN_TRANG_PHAP_LY_TS,
            bi.GHI_CHU,
            bi.LOAI_SO_HUU,
            bi.LOAI_SO_HUU_KHAC,
            bi.CHU_SO_HUU,
            bi.MA_HS_LUU_TRU,
            bi.NGAY_CAP_NHAT_BIEN_DONG,
            bi.CAP_NHAT_BIEN_DONG
      FROM BDS_IMPORT bi

      OPEN ImportCursor

      FETCH NEXT FROM ImportCursor INTO 
        @P_ASSET_CODE, @p_ASSET_NAME, @p_ASSET_DESC, @p_BUY_PRICE, @p_BRANCH_CREATE_CODE, @p_BRANCH_CREATE_NAME,
        @p_BRANCH_USE_CODE, @p_BRANCH_USE_NAME,
        @p_ADDR, @p_CURRENT_STATE, @p_LOAI_BDS, @p_LENGTH, @p_WIDTH, @p_LAND_SQUARE, @p_USE_SOURCE, 
        @p_HINH_THUC_SD, @p_USE_FORM_DETAIL, @p_CONSTRUCT_SQUARE, @p_FLOORS, @p_TOTAL_SQUARE, @P_BUY_DT, @p_THOI_HAN_SD,
        @p_THOI_HAN_SD_DEN_NGAY, @p_HINH_THUC_BDS, @p_TINH_TRANG_SD_DAT, @p_TINH_TRANG_XD, @p_PURPOSE_IN_USE,
        @P_W_USE_CON, @p_HOUSEDES, @p_HIEN_TRANG_PHAP_LY_TS, @p_REASON, @p_LOAI_SO_HUU, @p_OWNER_TYPE_DETAIL,
        @P_OWNER, @p_RET_SAVE_CODE, @p_FLUCTUATING_DT, @p_REASON_FLUCTUATING

      WHILE @@fetch_status = 0
      BEGIN
         
        SET @P_ASSET_CODE = TRIM(@P_ASSET_CODE)
        SET @p_BRANCH_CREATE_CODE = TRIM(@p_BRANCH_CREATE_CODE)
        SET @p_BRANCH_USE_CODE = TRIM(@p_BRANCH_USE_CODE)
        SET @p_LOAI_BDS = TRIM(@p_LOAI_BDS)
        SET @p_HINH_THUC_SD = TRIM(@p_HINH_THUC_SD)
        SET @p_THOI_HAN_SD = TRIM(@p_THOI_HAN_SD)
        SET @p_HINH_THUC_BDS = TRIM(@p_HINH_THUC_BDS)
        SET @p_TINH_TRANG_SD_DAT = TRIM(@p_TINH_TRANG_SD_DAT)
        SET @p_TINH_TRANG_XD = TRIM(@p_TINH_TRANG_XD)
        SET @p_HIEN_TRANG_PHAP_LY_TS = TRIM(@p_HIEN_TRANG_PHAP_LY_TS)
        SET @p_LOAI_SO_HUU = TRIM(@p_LOAI_SO_HUU)


        --------------------MAPPING MÃ TÀI SẢN--------------------          
        IF(@P_ASSET_CODE IS NOT NULL AND @P_ASSET_CODE <> '')
        BEGIN
          SELECT TOP 1 @p_ASSET_ID = am.ASSET_ID, @P_ASSET_CODE = am.ASSET_CODE, @p_ASSET_NAME = am.ASSET_NAME,
            @p_ASSET_DESC = am.ASSET_DESC, @p_BUY_PRICE = am.BUY_PRICE
          FROM ASS_MASTER am
          WHERE am.ASSET_CODE = @P_ASSET_CODE
        END
        ELSE
        BEGIN
          SET @p_ASSET_ID = NULL
          SET @P_ASSET_CODE = NULL
        END

        -------------------MAPPING THÔNG TIN-------------------
        SET @p_BRANCH_CREATE = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @p_BRANCH_CREATE_CODE)
        SET @p_BRANCH_USE_ID = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @p_BRANCH_USE_CODE)
        SET @p_RET_TYPE = (SELECT TOP 1 ca.CDVAL FROM CM_ALLCODE ca 
            WHERE ca.CDTYPE = 'RET' AND ca.CDNAME = 'RET_TYPE_2' AND ca.CONTENT LIKE '%' + @p_LOAI_BDS + '%' )
        SET @p_USE_FORM = (SELECT TOP 1 ca.CDVAL FROM CM_ALLCODE ca 
            WHERE ca.CDTYPE = 'RET' AND ca.CDNAME = 'USE_FORM' AND ca.CONTENT LIKE '%' + @p_HINH_THUC_SD + '%' )
        IF(@p_USE_FORM <> 'K')
        BEGIN
          SET @p_USE_FORM_DETAIL = NULL
        END
        IF(@p_THOI_HAN_SD = 'LD')
        BEGIN
          SET @p_USE_PERIOD = 0
        END
        ELSE IF(@p_THOI_HAN_SD = 'CTH')
        BEGIN
          SET @p_USE_PERIOD = 1
          SET @p_USE_PERIOD_DT = @p_THOI_HAN_SD_DEN_NGAY
        END
        SET @p_RET_FORM = (SELECT TOP 1 ca.CDVAL FROM CM_ALLCODE ca 
            WHERE ca.CDTYPE = 'RET' AND ca.CDNAME = 'RET_FORM' AND ca.CONTENT LIKE '%' + @p_HINH_THUC_BDS + '%' )
        SET @p_USE_STATUS = (SELECT TOP 1 ca.CDVAL FROM CM_ALLCODE ca 
            WHERE ca.CDTYPE = 'RET' AND ca.CDNAME = 'USE_STATUS' AND ca.CONTENT LIKE '%' + @p_TINH_TRANG_SD_DAT + '%' )
        SET @p_CONST_STATUS = (SELECT TOP 1 ca.CDVAL FROM CM_ALLCODE ca 
            WHERE ca.CDTYPE = 'RET' AND ca.CDNAME = 'CONST_STATUS' AND ca.CONTENT LIKE '%' + @p_TINH_TRANG_XD + '%' )
        SET @p_STATUS = (SELECT TOP 1 ca.CDVAL FROM CM_ALLCODE ca 
            WHERE ca.CDTYPE = 'RET' AND ca.CDNAME = 'RET_STATUS' AND ca.CONTENT LIKE '%' + @p_HIEN_TRANG_PHAP_LY_TS + '%' )
        SET @p_OWNER_TYPE = (SELECT TOP 1 ca.CDVAL FROM CM_ALLCODE ca 
            WHERE ca.CDTYPE = 'RET' AND ca.CDNAME = 'OWNER_TYPE' AND ca.CONTENT LIKE '%' + @p_LOAI_SO_HUU + '%' )
        IF(@p_OWNER_TYPE <> 'K')
        BEGIN
          SET @p_OWNER_TYPE_DETAIL = NULL
        END

        ------------------TẠO ID HỆ THỐNG-------------------
        EXEC SYS_CodeMasters_Gen 'RET_MASTER', @l_RET_ID OUT
		    IF @l_RET_ID='' OR @l_RET_ID IS NULL GOTO ABORT

        INSERT INTO dbo.RET_MASTER
        (
          RET_ID
         ,ASSET_ID
         ,ASSET_CODE
         ,OWNER
         ,LENGTH
         ,WIDTH
         ,FLOORS
         ,CURRENT_STATE
         ,RET_TYPE
         ,STATUS
         ,LAND_SQUARE
         ,CONSTRUCT_SQUARE
         ,TOTAL_SQUARE
         ,BOUNDARY
         ,HOUSEDES
         ,PURPOSE_IN_USE
         ,W_USE_CON
         ,USE_STATUS
         ,CONST_STATUS
         ,OWNER_TYPE
         ,USE_PERIOD
         ,PERSON_HOLDER
         ,REASON
         ,NOTES
         ,RECORD_STATUS
         ,AUTH_STATUS
         ,MAKER_ID
         ,CREATE_DT
         ,CHECKER_ID
         ,APPROVE_DT
         ,BUY_DT
         ,USE_FORM
         ,USE_FORM_DETAIL
         ,USE_SOURCE
         ,USE_PERIOD_DT
         ,OWNER_TYPE_DETAIL
         ,RET_SAVE_CODE
         ,BRANCH_USE
         ,ADDR
         ,FLUCTUATING_DT
         ,REASON_FLUCTUATING
         ,IS_SEND_APPR
         ,SEND_APPR_DT
         ,SIGN_USER
         ,SIGN_DT
         ,RET_FORM
         ,BRANCH_USE_ID
         ,ASS_NAME
         ,DESCRIPTION
         ,ASSET_PRICE
         ,BRANCH_CREATE
        )
        VALUES
        (@l_RET_ID, @p_ASSET_ID, @P_ASSET_CODE, @P_OWNER, @p_LENGTH, @p_WIDTH, @p_FLOORS, @p_CURRENT_STATE, @p_RET_TYPE, @p_STATUS,
          @p_LAND_SQUARE, @p_CONSTRUCT_SQUARE, @p_TOTAL_SQUARE, NULL, @p_HOUSEDES, @p_PURPOSE_IN_USE, @P_W_USE_CON, @p_USE_STATUS,
          @p_CONST_STATUS, @p_OWNER_TYPE, @p_USE_PERIOD, NULL, @p_REASON, NULL, '1', 'A', 'Khangpth', GETDATE(), 'xuanpt', GETDATE(),
          @P_BUY_DT, @p_USE_FORM, @p_USE_FORM_DETAIL, @p_USE_SOURCE, @p_USE_PERIOD_DT, @p_OWNER_TYPE_DETAIL, @p_RET_SAVE_CODE, NULL,
          @p_ADDR, @p_FLUCTUATING_DT, @p_REASON_FLUCTUATING, 'Y', GETDATE(), NULL, NULL, @p_RET_FORM, @p_BRANCH_USE_ID, @p_ASSET_NAME,
          @p_ASSET_DESC, @p_BUY_PRICE, @p_BRANCH_CREATE
        )

        IF @@Error <> 0 GOTO ABORT

        FETCH NEXT FROM ImportCursor INTO 
        @P_ASSET_CODE, @p_ASSET_NAME, @p_ASSET_DESC, @p_BUY_PRICE, @p_BRANCH_CREATE_CODE, @p_BRANCH_CREATE_NAME,
        @p_BRANCH_USE_CODE, @p_BRANCH_USE_NAME,
        @p_ADDR, @p_CURRENT_STATE, @p_LOAI_BDS, @p_LENGTH, @p_WIDTH, @p_LAND_SQUARE, @p_USE_SOURCE, 
        @p_HINH_THUC_SD, @p_USE_FORM_DETAIL, @p_CONSTRUCT_SQUARE, @p_FLOORS, @p_TOTAL_SQUARE, @P_BUY_DT, @p_THOI_HAN_SD,
        @p_THOI_HAN_SD_DEN_NGAY, @p_HINH_THUC_BDS, @p_TINH_TRANG_SD_DAT, @p_TINH_TRANG_XD, @p_PURPOSE_IN_USE,
        @P_W_USE_CON, @p_HOUSEDES, @p_HIEN_TRANG_PHAP_LY_TS, @p_REASON, @p_LOAI_SO_HUU, @p_OWNER_TYPE_DETAIL,
        @P_OWNER, @p_RET_SAVE_CODE, @p_FLUCTUATING_DT, @p_REASON_FLUCTUATING
      END

      CLOSE ImportCursor
      DEALLOCATE ImportCursor

COMMIT TRANSACTION
SELECT '0' as Result, N'Import thành công' ErrorDesc
RETURN '0'

ABORT:
BEGIN
		ROLLBACK TRANSACTION
    CLOSE ImportCursor
    DEALLOCATE ImportCursor
		SELECT '-1' as Result, '' ErrorDesc
		RETURN '-1'
End







GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[FN_DATE_DIFF_BETWEEN]'
GO


CREATE FUNCTION [dbo].[FN_DATE_DIFF_BETWEEN]
(	@p_FROM_DATE DATETIME,
	@p_TO_DATE DATETIME,
	@p_TYPE VARCHAR(20)
)
RETURNS DECIMAL(18,2) AS 
BEGIN
	DECLARE @p_RESULT DECIMAL(18,2)
	DECLARE @p_START_DAY INT,
			@p_END_DAY INT,
			@p_END_MONTH INT,
			@p_END_DATE_TMP DATETIME,
			@p_TMP INT,
			@p_DAYS_IN_MONTH INT

	IF(@p_TYPE = 'MONTH')
	BEGIN
		SET @p_START_DAY = DAY(@p_FROM_DATE)
		SET @p_END_DAY = DAY(@p_TO_DATE)
		-----------Nếu ngày của thời điểm bắt đầu = ngày thời điểm kết thúc---------------
		IF(@p_START_DAY = @p_END_DAY)
		----------Tính tròn tháng---------------
		BEGIN
			SET @p_RESULT = DATEDIFF(MONTH, @p_FROM_DATE, @p_TO_DATE)
		END
		-----------Nếu ngày của thời điểm bắt đầu < ngày thời điểm kết thúc---------------
		ELSE IF(@p_START_DAY < @p_END_DAY)
		--------Tính số tháng tròn + số ngày dư / tổng số ngày trong tháng-------
		BEGIN
			SET @p_TMP = (@p_START_DAY - @p_END_DAY)
			SET @p_END_DATE_TMP = DATEADD(DAY, @p_TMP, @p_TO_DATE)

			SET @p_DAYS_IN_MONTH = DAY(EOMONTH(@p_TO_DATE))

			SET @p_RESULT = DATEDIFF(MONTH, @p_FROM_DATE, @p_END_DATE_TMP) 
				+ ((@p_END_DAY - @p_START_DAY) * 1.0 / @p_DAYS_IN_MONTH)
		END
		-----------Nếu ngày của thời điểm bắt đầu > ngày thời điểm kết thúc---------------
		ELSE IF(@p_START_DAY > @p_END_DAY)
		--------Tính số tháng tròn - số ngày dư / tổng số ngày trong tháng-------
		BEGIN
			SET @p_TMP = (@p_START_DAY - @p_END_DAY)
			SET @p_END_DATE_TMP = DATEADD(DAY, @p_TMP, @p_TO_DATE)

			SET @p_DAYS_IN_MONTH = DAY(EOMONTH(@p_TO_DATE))

			SET @p_RESULT = DATEDIFF(MONTH, @p_FROM_DATE, @p_END_DATE_TMP)
				- ((@p_START_DAY - @p_END_DAY) * 1.0 / @p_DAYS_IN_MONTH)
		END
	END

	RETURN @p_RESULT
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[REAL_ESTATE_R_H_IMPORT]'
GO

CREATE PROCEDURE [dbo].[REAL_ESTATE_R_H_IMPORT]
----------IMPORT QUẢN LÝ BĐS ĐI THUÊ-------------
AS
BEGIN TRANSACTION
    DECLARE @p_RET_R_H_ID VARCHAR(15),-- ID HỆ THỐNG
        @p_BRANCH_CREATE VARCHAR(15), -- ĐƠN VỊ TẠO
        @p_BRANCH_MANAGE VARCHAR(15), -- ĐƠN VỊ QUẢN LÝ
        @p_BRANCH_USE VARCHAR(15),    -- ĐƠN VỊ SỬ DỤNG
        @p_RET_ADDR NVARCHAR(MAX),    -- ĐỊA CHỈ
        @p_RET_R_H_TIME DECIMAL(18,2),  
        @p_START_DT DATETIME,         -- NGÀY BẮT ĐẦU
        @p_END_DT DATETIME,           -- NGÀY KẾT THÚC
        @p_R_LAND_SQUARE DECIMAL(18,2),   -- DIỆN TÍCH THUÊ
        @p_SCALE NVARCHAR(500),           -- QUY MÔ
        @p_CONSTRUCT_SQUARE DECIMAL(18,2),  -- DIỆN TÍCH XÂY DỰNG
        @p_DEPOSIT VARCHAR(1),              -- ĐẶT CỌC
        @p_DEPOSIT_PRICE DECIMAL(18,2),     -- SỐ TIỀN ĐẶT CỌC
        @p_DEPOSIT_RETURN_DT DATETIME,      -- THỜI ĐIỂM HOÀN LẠI TIỀN CỌC
        @p_NOTES NVARCHAR(1000),            -- GHI CHÚ
        @p_RECORD_STATUS VARCHAR(1),        
        @p_AUTH_STATUS VARCHAR(1),
        @p_EXTEND_DT DATETIME,              -- NGÀY GIA HẠN HỢP ĐỒNG
        @p_STRUCTURE NVARCHAR(500),         -- KẾT CẤU
        @p_IS_SEND_APPR VARCHAR(15),
        @p_SEND_APPR_DT DATETIME,
        @p_SIGN_USER VARCHAR(15),
        @p_SIGN_DT DATETIME,
        @p_EXTEND_PHASE NVARCHAR(1000),     -- LẦN GIA HẠN
        @p_TR_CONTRACT_ID VARCHAR(15),       -- ID HỢP ĐỒNG
        @p_CONTRACT_CODE NVARCHAR(150),     -- SỐ HỢP ĐỒNG
        @p_BRANCH_MANAGE_CODE VARCHAR(15),   -- MÃ ĐƠN VỊ QUẢN LÝ
        @p_BRANCH_USE_CODE VARCHAR(15),     -- MÃ ĐƠN VỊ SỬ DỤNG
		@p_DEFAULT_RECORD_STATUS VARCHAR(1) = '1',
		@p_DEFAULT_AUTH_STATUS VARCHAR(1) = 'A',
		@p_DEFAULT_IS_SEND_APPR VARCHAR(1) = 'Y',
        @p_MAKER_ID VARCHAR(15) = 'quoclnb',
        @p_CHECKER_ID VARCHAR(15) = 'xuanpt',
		@CURRENT_DATE DATETIME = GETDATE(),
		@ROW_COUNT INT = 1		-- TƯỢNG TRUNG CHO DÒNG TRONG FILE EXCEL

    DECLARE ImportRERHCursor CURSOR FOR
		SELECT	SO_HOP_DONG, MA_DON_VI_QUAN_LY, MA_DON_VI_SU_DUNG, DIA_CHI, NGAY_BAT_DAU,
				LAN_GIA_HAN, NGAY_KET_THUC, NGAY_GIA_HAN, DIEN_TICH_THUE, QUY_MO,
				DIEN_TICH_XAY_DUNG, KET_CAU, GHI_CHU, SO_TIEN_DAT_COC, THOI_DIEM_HOAN_TIEN_COC
		FROM BDS_DI_THUE_IMPORT
    
    OPEN ImportRERHCursor

    FETCH NEXT FROM ImportRERHCursor INTO
          @p_CONTRACT_CODE, @p_BRANCH_MANAGE_CODE, @p_BRANCH_USE_CODE, @p_RET_ADDR, @p_START_DT,
          @p_EXTEND_PHASE, @p_END_DT, @p_EXTEND_DT, @p_R_LAND_SQUARE, @p_SCALE, 
		  @p_CONSTRUCT_SQUARE, @p_STRUCTURE, @p_NOTES, @p_DEPOSIT_PRICE, @p_DEPOSIT_RETURN_DT
    
    WHILE @@FETCH_STATUS = 0
    BEGIN

    SET @p_CONTRACT_CODE = TRIM(@p_CONTRACT_CODE)
    SET @p_BRANCH_MANAGE_CODE = TRIM(@p_BRANCH_MANAGE_CODE)
    SET @p_BRANCH_USE_CODE = TRIM(@p_BRANCH_USE_CODE)

		SET @ROW_COUNT += 1

        -----------LẤY ID HỢP ĐỒNG THEO MÃ HỢP ĐỒNG IMPORT VÀO------------------
        -------------trường hợp ko có số hợp đồng thì tìm theo ID
		SET @p_TR_CONTRACT_ID = (SELECT TOP 1 CONTRACT_ID 
								 FROM TR_CONTRACT 
								 WHERE (CONTRACT_CODE = @p_CONTRACT_CODE OR CONTRACT_ID = @p_CONTRACT_CODE)
									AND AUTH_STATUS = @p_DEFAULT_AUTH_STATUS 
									AND RECORD_STATUS = @p_DEFAULT_RECORD_STATUS
									)
        SET @p_BRANCH_MANAGE = (SELECT TOP 1 BRANCH_ID 
								FROM CM_BRANCH 
								WHERE BRANCH_CODE = @p_BRANCH_MANAGE_CODE
									AND AUTH_STATUS = @p_DEFAULT_AUTH_STATUS 
									AND RECORD_STATUS = @p_DEFAULT_RECORD_STATUS)
        SET @p_BRANCH_USE = (SELECT TOP 1 BRANCH_ID 
							 FROM CM_BRANCH 
							 WHERE BRANCH_CODE = @p_BRANCH_USE_CODE
								AND AUTH_STATUS = @p_DEFAULT_AUTH_STATUS 
								AND RECORD_STATUS = @p_DEFAULT_RECORD_STATUS)
        SET @p_BRANCH_CREATE = @p_BRANCH_MANAGE
        IF(@p_DEPOSIT_PRICE IS NOT NULL)
        BEGIN
          SET @p_DEPOSIT = '1'
        END
        ELSE
        BEGIN
          SET @p_DEPOSIT = '0'
        END
        SET @p_RET_R_H_TIME = (SELECT dbo.FN_DATE_DIFF_BETWEEN(@p_START_DT, @p_END_DT, 'MONTH'))

		--START VALIDATION
		IF (@p_START_DT = '' OR @p_START_DT IS NULL)
		BEGIN
			SELECT '-1' Result, N'Dòng: ' +CAST(@ROW_COUNT AS NVARCHAR(10))+ N'. Ngày bắt đầu không được để trống' ErrorDesc
			GOTO ABORT1
		END
		IF ( @p_TR_CONTRACT_ID = '' OR @p_TR_CONTRACT_ID IS NULL)
		BEGIN
			SELECT '-1' Result, N'Dòng: ' +CAST(@ROW_COUNT AS NVARCHAR(10))+ N'. Số hợp đồng không hợp lệ' ErrorDesc
			GOTO ABORT1
		END
		IF (@p_BRANCH_MANAGE = '' OR @p_BRANCH_MANAGE IS NULL)
		BEGIN
			SELECT '-1' Result, N'Dòng: ' +CAST(@ROW_COUNT AS NVARCHAR(10))+ N'. Đơn vị quản lý không hợp lệ' ErrorDesc
			GOTO ABORT1
		END
		IF (@p_BRANCH_USE = '' OR @p_BRANCH_USE IS NULL)
		BEGIN
			SELECT '-1' Result, N'Dòng: ' +CAST(@ROW_COUNT AS NVARCHAR(10))+ N'. Đơn vị sử dụng không hợp lệ' ErrorDesc
			GOTO ABORT1
		END
		IF (@p_RET_ADDR = '' OR @p_RET_ADDR IS NULL)
		BEGIN
			SELECT '-1' Result, N'Dòng: ' +CAST(@ROW_COUNT AS NVARCHAR(10))+ N'. Địa chỉ không được để trống' ErrorDesc
			GOTO ABORT1
		END
    IF(@p_EXTEND_DT IS NOT NULL AND @p_EXTEND_DT < @p_END_DT AND @p_END_DT IS NOT NULL)
    BEGIN
      SELECT '-1' Result, N'Dòng: ' +CAST(@ROW_COUNT AS NVARCHAR(10))+ N'. Ngày gia hạn hợp đồng không được nhỏ hơn ngày kết thúc' ErrorDesc
			GOTO ABORT1
    END

		--END VALIDATION

        -----------------------TẠO ID HỆ THỐNG----------------
        EXEC dbo.SYS_CodeMasters_Gen 'REAL_ESTATE_R_H', @p_RET_R_H_ID OUT
    
		--EXEC dbo.REAL_ESTATE_R_H_Ins @p_BRANCH_MANAGE, @p_BRANCH_USE, @p_RET_ADDR, @p_RET_R_H_TIME, @p_START_DT,
		--							@p_END_DT, @p_R_LAND_SQUARE, @p_SCALE, @p_CONSTRUCT_SQUARE, NULL, @p_DEPOSIT, @p_DEPOSIT_PRICE, 
		--							@p_DEPOSIT_RETURN_DT, NULL, NULL, NULL, NULL, @p_NOTES, '1', 'A', @p_MAKER_ID, 
		--							@CURRENT_DATE, @p_CHECKER_ID, @CURRENT_DATE, NULL, @p_BRANCH_CREATE,
		--							@p_TR_CONTRACT_ID, @p_EXTEND_DT, @p_STRUCTURE, 'Y', 
		--							@CURRENT_DATE, NULL, NULL, @p_EXTEND_PHASE

        INSERT INTO REAL_ESTATE_R_H (
			RET_R_H_ID, BRANCH_MANAGE, BRANCH_USE, RET_ADDR, RET_R_H_TIME, START_DT, 
    		END_DT, R_LAND_SQUARE, SCALE, CONSTRUCT_SQUARE, DEPOSIT, DEPOSIT_PRICE, 
			DEPOSIT_RETURN_DT, PURCHASE_EACH_TERM, NOTES, RECORD_STATUS, AUTH_STATUS, MAKER_ID, 
			CREATE_DT, CHECKER_ID, APPROVE_DT, BRANCH_CREATE, MONTH_TIMES, PAY_AMT, 
			PAY_MONTH_TIMES, PAY_PAY_AMT, TR_CONTRACT_ID, EXTEND_DT, STRUCTURE, IS_SEND_APPR,
			SEND_APPR_DT, SIGN_USER, SIGN_DT, EXTEND_PHASE )
        VALUES (
			@p_RET_R_H_ID, @p_BRANCH_MANAGE, @p_BRANCH_USE, @p_RET_ADDR, @p_RET_R_H_TIME, @p_START_DT,
            @p_END_DT, @p_R_LAND_SQUARE, @p_SCALE, @p_CONSTRUCT_SQUARE, @p_DEPOSIT, @p_DEPOSIT_PRICE, 
			@p_DEPOSIT_RETURN_DT, NULL, @p_NOTES, @p_DEFAULT_RECORD_STATUS, @p_DEFAULT_AUTH_STATUS, @p_MAKER_ID, 
			@CURRENT_DATE, @p_CHECKER_ID, @CURRENT_DATE, @p_BRANCH_CREATE, NULL, NULL, 
			NULL, NULL, @p_TR_CONTRACT_ID, @p_EXTEND_DT, @p_STRUCTURE, @p_DEFAULT_IS_SEND_APPR, 
			@CURRENT_DATE, NULL, NULL, @p_EXTEND_PHASE )
    
        IF @@Error <> 0 GOTO ABORT

      -----------------------INSERT DETAIL-----------------------------------
      DECLARE @d_RET_PRICE_ID varchar(15), 
          		@d_PRICE	decimal(18,2), 
          		@d_PRICE_DT	DATETIME, 
          		@d_PRICE_END_DT DATETIME,
          		@d_PAY_PHASE NVARCHAR(1000),
          		@d_TOTAL_AMT_PAY_PHASE DECIMAL(18,2),
          		@d_NOTES NVARCHAR(MAX)

      DECLARE @p_ROW_COUNT_DETAIL INT = 0
      DECLARE RealEstateRHDTImportCursor CURSOR FOR
        SELECT bdtktti.KY_THANH_TOAN, bdtktti.TU_NGAY, bdtktti.DEN_NGAY, bdtktti.GIA_THUE, bdtktti.TONG_SO_TIEN_KY_THANH_TOA, bdtktti.GHI_CHU
        FROM BDS_DI_THUE_KY_THANH_TOAN_IMPORT bdtktti
        WHERE bdtktti.SO_HOP_DONG = @p_CONTRACT_CODE

      OPEN RealEstateRHDTImportCursor
      FETCH NEXT FROM RealEstateRHDTImportCursor INTO
        @d_PAY_PHASE, @d_PRICE_DT, @d_PRICE_END_DT, @d_PRICE, @d_TOTAL_AMT_PAY_PHASE, @d_NOTES

      WHILE @@fetch_status = 0
      BEGIN

        SET @p_ROW_COUNT_DETAIL = @p_ROW_COUNT_DETAIL + 1

        IF(@d_PRICE_DT IS NULL)
        BEGIN
          SELECT '-1' Result, 
          N'Sheet kỳ thanh toán, dòng: ' +CAST(@p_ROW_COUNT_DETAIL AS NVARCHAR(10))+ 
          N', số hợp đồng: ' +@p_CONTRACT_CODE+
          N': Từ ngày không được bỏ trống.' ErrorDesc
          GOTO VALIDATE_ABORT_DT
        END
        IF(@d_PRICE_END_DT IS NULL)
        BEGIN
          SELECT '-1' Result, 
          N'Sheet kỳ thanh toán, dòng: ' +CAST(@p_ROW_COUNT_DETAIL AS NVARCHAR(10))+ 
          N', số hợp đồng: ' +@p_CONTRACT_CODE+
          N': Từ ngày không được bỏ trống.' ErrorDesc
          GOTO VALIDATE_ABORT_DT
        END
        IF(@d_PRICE IS NULL)
        BEGIN
          SELECT '-1' Result, 
          N'Sheet kỳ thanh toán, dòng: ' +CAST(@p_ROW_COUNT_DETAIL AS NVARCHAR(10))+ 
          N', số hợp đồng: ' +@p_CONTRACT_CODE+
          N': Giá thuê/tháng(có tính thuế) không được bỏ trống.' ErrorDesc
          GOTO VALIDATE_ABORT_DT
        END

        IF(@d_PRICE_END_DT < @d_PRICE_DT)
        BEGIN
          SELECT '-1' Result, 
          N'Sheet kỳ thanh toán, dòng: ' +CAST(@p_ROW_COUNT_DETAIL AS NVARCHAR(10))+ 
          N', số hợp đồng: ' +@p_CONTRACT_CODE+
          N': Đến ngày không được nhỏ hơn từ ngày.' ErrorDesc
          GOTO VALIDATE_ABORT_DT
        END

        IF(@d_TOTAL_AMT_PAY_PHASE IS NULL)
        BEGIN
          DECLARE @d_MONTH_DIFF DECIMAL(18,2) = (SELECT dbo.FN_DATE_DIFF_BETWEEN(@d_PRICE_DT, @d_PRICE_END_DT, 'MONTH'))
          SET @d_TOTAL_AMT_PAY_PHASE = @d_MONTH_DIFF * @d_PRICE
        END

        EXEC SYS_CodeMasters_Gen 'RET_PRICE', @d_RET_PRICE_ID out
  			IF @d_RET_PRICE_ID ='' OR @d_RET_PRICE_ID IS NULL GOTO ABORT
  			INSERT INTO RET_PRICE
  				(RET_PRICE_ID, RET_R_H_ID, PRICE, PRICE_DT, PRICE_END_DT, PAY_PHASE, TOTAL_AMT_PAY_PHASE,
  				NOTES)
  			VALUES
        (@d_RET_PRICE_ID, @p_RET_R_H_ID, @d_PRICE, @d_PRICE_DT, @d_PRICE_END_DT, @d_PAY_PHASE, @d_TOTAL_AMT_PAY_PHASE, @d_NOTES)

        FETCH NEXT FROM RealEstateRHDTImportCursor INTO
          @d_PAY_PHASE, @d_PRICE_DT, @d_PRICE_END_DT, @d_PRICE, @d_TOTAL_AMT_PAY_PHASE, @d_NOTES
      END

      CLOSE RealEstateRHDTImportCursor
      DEALLOCATE RealEstateRHDTImportCursor
      -------------END INSERT DETAIL-----------------------------

        FETCH NEXT FROM ImportRERHCursor INTO
			@p_CONTRACT_CODE, @p_BRANCH_MANAGE_CODE, @p_BRANCH_USE_CODE, @p_RET_ADDR, @p_START_DT,
			@p_EXTEND_PHASE, @p_END_DT, @p_EXTEND_DT, @p_R_LAND_SQUARE, @p_SCALE, 
			@p_CONSTRUCT_SQUARE, @p_STRUCTURE, @p_NOTES, @p_DEPOSIT_PRICE, @p_DEPOSIT_RETURN_DT
    END

    CLOSE ImportRERHCursor
    DEALLOCATE ImportRERHCursor

COMMIT TRANSACTION
	--DELETE FROM BDS_DI_THUE_IMPORT		-- XÓA BẢNG TẠM SAU KHI ĐÃ THÊM DỮ LIỆU THÀNH CÔNG
	SELECT '0' as Result, N'Import thông tin quản lý BĐS đi thuêthành công' ErrorDesc
	RETURN '0'

ABORT:
BEGIN
	ROLLBACK TRANSACTION
    CLOSE ImportRERHCursor
    DEALLOCATE ImportRERHCursor
	SELECT '-1' as Result, '' ErrorDesc
	RETURN '-1'
END
ABORT1:
BEGIN
	ROLLBACK TRANSACTION
    CLOSE ImportRERHCursor
    DEALLOCATE ImportRERHCursor
	RETURN '-1'
END
ABORT_DT:
BEGIN
    ROLLBACK TRANSACTION
    CLOSE ImportRERHCursor
    DEALLOCATE ImportRERHCursor
    CLOSE RealEstateRHDTImportCursor
    DEALLOCATE RealEstateRHDTImportCursor
		SELECT '-1' as Result, '' ErrorDesc
		RETURN '-1'
END
VALIDATE_ABORT_DT:
BEGIN
    ROLLBACK TRANSACTION
    CLOSE ImportRERHCursor
    DEALLOCATE ImportRERHCursor
    CLOSE RealEstateRHDTImportCursor
    DEALLOCATE RealEstateRHDTImportCursor
    RETURN '-1'
END

GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[REAL_ESTATE_L_C_IMPORT]'
GO

CREATE PROCEDURE [dbo].[REAL_ESTATE_L_C_IMPORT]
----------IMPORT QUẢN LÝ BĐS ĐI THUÊ-------------
AS
BEGIN TRANSACTION
    DECLARE @p_RET_L_C_ID VARCHAR(15),-- ID HỆ THỐNG
        @p_BRANCH_CREATE VARCHAR(15), -- ĐƠN VỊ TẠO
        @p_BRANCH_MANAGE VARCHAR(15), -- ĐƠN VỊ QUẢN LÝ
        @p_BRANCH_USE VARCHAR(15),    -- ĐƠN VỊ SỬ DỤNG
		@p_RET_ID VARCHAR(15),
		@p_BUY_DT DATETIME,
        @p_RET_ADDR NVARCHAR(MAX),    -- ĐỊA CHỈ
        @p_R_LAND_SQUARE DECIMAL(18,2),   -- DIỆN TÍCH THUÊ
		@p_ASSET_PRICE DECIMAL(18,2),
        @p_CONSTRUCT_SQUARE DECIMAL(18,2),  -- DIỆN TÍCH XÂY DỰNG
        @p_AUTH_STATUS VARCHAR(1),
        @p_MAKER_ID VARCHAR(15) = 'Khangpth',
        @p_CHECKER_ID VARCHAR(15) = 'xuanpt',
        @p_BRANCH_MANAGE_CODE VARCHAR(15),   -- MÃ ĐƠN VỊ QUẢN LÝ
        @p_BRANCH_USE_CODE VARCHAR(15),     -- MÃ ĐƠN VỊ SỬ DỤNG
		@p_LAND_USE_RIGHTS_PRICE DECIMAL(18,2),
		@p_MARKET_PRICE DECIMAL(18,2),
		@p_RET_DESC NVARCHAR(MAX),
		@p_REASON NVARCHAR(500),
		@p_PROC_EXE NVARCHAR(500),
		@p_PROC_NEXT NVARCHAR(500),
		@p_END_DT DATETIME,
		@p_FINISH_DT_REAL DATETIME,
		@p_NOTES NVARCHAR(1000),
		@p_DEFAULT_RECORD_STATUS VARCHAR(1) = '1',
		@p_DEFAULT_AUTH_STATUS VARCHAR(1) = 'A',
		@p_DEFAULT_IS_SEND_APPR VARCHAR(1) = 'Y',
		@CURRENT_DATE DATETIME = GETDATE(),
		@ROW_COUNT INT = 1		-- TƯỢNG TRUNG CHO DÒNG TRONG FILE EXCEL

    DECLARE ImportRELCCursor CURSOR FOR
		SELECT	MA_DON_VI_QUAN_LY, MA_DON_VI_SU_DUNG, MA_BDS, NGAY_MUA, DIA_CHI, 
				DIEN_TICH_DAT, DIEN_TICH_XAY_DUNG, GT_TS_TREN_DAT, GT_QUYEN_SD_DAT, GIA_THI_TRUONG, 
				HIEN_TRANG, NGUYEN_NHAN, CAC_TT_DA_THUC_HIEN, CAC_TT_TIEP_THEO, TG_DU_KIEN_HT, 
				NGAY_HT_TTPL, GHI_CHU
		FROM BDS_H_T_TTPL_IMPORT
    
    OPEN ImportRELCCursor

    FETCH NEXT FROM ImportRELCCursor INTO
          @p_BRANCH_MANAGE_CODE, @p_BRANCH_USE_CODE, @p_RET_ID, @p_BUY_DT, @p_RET_ADDR,
          @p_R_LAND_SQUARE, @p_CONSTRUCT_SQUARE, @p_ASSET_PRICE, @p_LAND_USE_RIGHTS_PRICE, @p_MARKET_PRICE, 
		  @p_RET_DESC, @p_REASON, @p_PROC_EXE, @p_PROC_NEXT, @p_END_DT,
		  @p_FINISH_DT_REAL, @p_NOTES

    WHILE @@FETCH_STATUS = 0
    BEGIN
		SET @ROW_COUNT += 1

        SET @p_BRANCH_MANAGE = (SELECT TOP 1 BRANCH_ID 
								FROM CM_BRANCH 
								WHERE BRANCH_CODE = @p_BRANCH_MANAGE_CODE
									AND AUTH_STATUS = @p_DEFAULT_AUTH_STATUS 
									AND RECORD_STATUS = @p_DEFAULT_RECORD_STATUS)
        SET @p_BRANCH_USE = (SELECT TOP 1 BRANCH_ID 
							 FROM CM_BRANCH 
							 WHERE BRANCH_CODE = @p_BRANCH_USE_CODE
								AND AUTH_STATUS = @p_DEFAULT_AUTH_STATUS 
								AND RECORD_STATUS = @p_DEFAULT_RECORD_STATUS)
		SET @p_BRANCH_CREATE = @p_BRANCH_MANAGE

		--START VALIDATION
		IF (@p_RET_ID = '' OR @p_RET_ID IS NULL)
		BEGIN
			SELECT '-1' Result, N'Dòng: ' +CAST(@ROW_COUNT AS NVARCHAR(10))+ N'. Mã BĐS không hợp lệ' ErrorDesc
			GOTO ABORT1
		END
		IF (@p_BRANCH_MANAGE = '' OR @p_BRANCH_MANAGE IS NULL)
		BEGIN
			SELECT '-1' Result, N'Dòng: ' +CAST(@ROW_COUNT AS NVARCHAR(10))+ N'. Đơn vị quản lý không hợp lệ' ErrorDesc
			GOTO ABORT1
		END
		IF (@p_BRANCH_USE = '' OR @p_BRANCH_USE IS NULL)
		BEGIN
			SELECT '-1' Result, N'Dòng: ' +CAST(@ROW_COUNT AS NVARCHAR(10))+ N'. Đơn vị sử dụng không hợp lệ' ErrorDesc
			GOTO ABORT1
		END
		--END VALIDATION
    
        -----------------------TẠO ID HỆ THỐNG----------------
        EXEC dbo.SYS_CodeMasters_Gen 'REAL_ESTATE_L_C', @p_RET_L_C_ID OUT

		INSERT INTO REAL_ESTATE_L_C (
			RET_L_C_ID, RET_NAME, RET_ADDR, RET_DESC, REASON, PROC_EXE, PROC_NEXT,
			END_DT, NOTES, BRANCH_ID, BRANCH_CREATE, RECORD_STATUS, AUTH_STATUS,
			MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT, BRANCH_USE, BRANCH_MANAGE,
			BUY_DT, AREA, CONTRUCT_AREA, IS_SEND_APPR, SEND_APPR_DT,
			SIGN_USER, SIGN_DT, RET_ID, ASSET_PRICE, LAND_USE_RIGHTS_PRICE, MARKET_PRICE, FINISH_DT_REAL )
		VALUES (@p_RET_L_C_ID, NULL, @p_RET_ADDR, @p_RET_DESC, @p_REASON, @p_PROC_EXE, @p_PROC_NEXT,
				@p_END_DT, @p_NOTES, @p_BRANCH_CREATE, @p_BRANCH_CREATE, @p_DEFAULT_RECORD_STATUS, @p_DEFAULT_AUTH_STATUS,
				@p_MAKER_ID, @CURRENT_DATE, @p_CHECKER_ID, @CURRENT_DATE, @p_BRANCH_USE, @p_BRANCH_MANAGE,
				@p_BUY_DT, @p_R_LAND_SQUARE, @p_CONSTRUCT_SQUARE, @p_DEFAULT_IS_SEND_APPR, @CURRENT_DATE, NULL, NULL, @p_RET_ID, @p_ASSET_PRICE,
        @p_LAND_USE_RIGHTS_PRICE, @p_MARKET_PRICE, @p_FINISH_DT_REAL)

        IF @@Error <> 0 GOTO ABORT

        FETCH NEXT FROM ImportRELCCursor INTO
          @p_BRANCH_MANAGE_CODE, @p_BRANCH_USE_CODE, @p_RET_ID, @p_BUY_DT, @p_RET_ADDR,
          @p_R_LAND_SQUARE, @p_CONSTRUCT_SQUARE, @p_ASSET_PRICE, @p_LAND_USE_RIGHTS_PRICE, @p_MARKET_PRICE, 
		  @p_RET_DESC, @p_REASON, @p_PROC_EXE, @p_PROC_NEXT, @p_END_DT,
		  @p_FINISH_DT_REAL, @p_NOTES

    END

    CLOSE ImportRELCCursor
    DEALLOCATE ImportRELCCursor

COMMIT TRANSACTION
	--DELETE FROM BDS_H_T_TTPL_IMPORT		-- XÓA BẢNG TẠM SAU KHI ĐÃ THÊM DỮ LIỆU THÀNH CÔNG
	SELECT '0' as Result, N'Import thành công' ErrorDesc
	RETURN '0'

ABORT:
BEGIN
	ROLLBACK TRANSACTION
    CLOSE ImportRELCCursor
    DEALLOCATE ImportRELCCursor
	SELECT '-1' as Result, '' ErrorDesc
	RETURN '-1'
END
ABORT1:
BEGIN
	ROLLBACK TRANSACTION
    CLOSE ImportRELCCursor
    DEALLOCATE ImportRELCCursor
	RETURN '-1'
END




GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[BUD_MASTER_IMPORT]'
GO

CREATE PROCEDURE [dbo].[BUD_MASTER_IMPORT]
AS
------------IMPORT QUẢN LÝ TRỤ SỞ--------------------
BEGIN TRANSACTION
  DECLARE @p_BUILDING_ID VARCHAR(15),
          @p_ASSET_ID VARCHAR(15),
          @p_RET_ID VARCHAR(15),
          @p_BUILDING_NAME NVARCHAR(1000),
          @p_BRANCH_ID VARCHAR(15),
          @p_BRANCH_USE_ID VARCHAR(15),
          @p_ADDRESS NVARCHAR(800),
          @p_OWNER_OR_RENT VARCHAR(1),
          @p_NUM_FLOOR INT,
          @p_AREA DECIMAL(18,2),
          @p_TOTAL_AREA DECIMAL(18,2),
          @p_RENT_TOTAL_AREA DECIMAL(18,2),
          @p_UTILZED_AREA DECIMAL(18,2),
          @p_NOTES NVARCHAR(MAX),
          @p_RECORD_STATUS VARCHAR(1) = '1',
          @p_AUTH_STATUS VARCHAR(1) = 'A',
          @p_MAKER_ID VARCHAR(15) = 'Khangpth',
          @p_CREATE_DT DATETIME = GETDATE(),
          @p_CHECKER_ID VARCHAR(15) = 'xuanpt',
          @p_APPROVE_DT DATETIME = GETDATE(),
          @p_RENT_AMT DECIMAL,
          @p_DEPOSITS_AMT DECIMAL,
          @p_RENT_TIME INT,
          @p_CONTRACT_ID VARCHAR(15),
          @p_IS_SEND_APPR VARCHAR(15) = 'Y',
          @p_SEND_APPR_DT DATETIME = GETDATE(),
          @p_SIGN_USER VARCHAR(15) = NULL,
          @p_SIGN_DT DATETIME = NULL,
          @p_SUP_ID VARCHAR(15),
          @p_VAT INT,
          @p_LENGTH DECIMAL(18,2),
          @p_WIDTH DECIMAL(18,2)

  DECLARE @p_HINH_THUC_SO_HUU NVARCHAR(250),
          @p_MA_TAI_SAN VARCHAR(100),
          @p_MA_DV_TAO VARCHAR(15),
          @p_MA_DV_SU_DUNG VARCHAR(15),
          @p_SO_HOP_DONG VARCHAR(150)


  DECLARE ImportBudMasterCursor CURSOR FOR
    SELECT qtsi.HINH_THUC_SO_HUU, qtsi.MA_TAI_SAN, qtsi.MA_DV_TAO, qtsi.MA_DV_SU_DUNG,
           qtsi.SO_HOP_DONG, qtsi.TEN_TRU_SO, qtsi.DIA_CHI, qtsi.TONG_SO_TANG, qtsi.DIEN_TICH_MAT_SAN, qtsi.TONG_DIEN_TICH_M2, qtsi.DAI, qtsi.RONG,
           qtsi.GIA_THUE_HT_DA_THUE, qtsi.VAT, qtsi.GHI_CHU
    FROM QL_TRU_SO_IMPORT qtsi

  OPEN ImportBudMasterCursor

  DECLARE @p_ROW_COUNT INT = 0

  FETCH NEXT FROM ImportBudMasterCursor INTO
    @p_HINH_THUC_SO_HUU, @p_MA_TAI_SAN, @p_MA_DV_TAO, @p_MA_DV_SU_DUNG, @p_SO_HOP_DONG, @p_BUILDING_NAME, @p_ADDRESS, 
    @p_NUM_FLOOR, @p_AREA, @p_TOTAL_AREA, @p_LENGTH, @p_WIDTH, @p_RENT_AMT, @p_VAT, @p_NOTES

  WHILE @@fetch_status = 0
  BEGIN

    SET @p_HINH_THUC_SO_HUU = TRIM(@p_HINH_THUC_SO_HUU)
    SET @p_MA_TAI_SAN = TRIM(@p_MA_TAI_SAN)
    SET @p_MA_DV_TAO = TRIM(@p_MA_DV_TAO)
    SET @p_MA_DV_SU_DUNG = TRIM(@p_MA_DV_SU_DUNG)
    SET @p_SO_HOP_DONG = TRIM(@p_SO_HOP_DONG)

    SET @p_ROW_COUNT = @p_ROW_COUNT + 1
    
    ------------VALIDATE THÔNG TIN--------------------
    IF(@p_HINH_THUC_SO_HUU IS NULL OR @p_HINH_THUC_SO_HUU = '')
    BEGIN
      SELECT '-1' Result, 
        N'Sheet thông tin trụ sở, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Hình thức sở hữu không được để trống.' ErrorDesc
      GOTO VALIDATE_ABORT
    END
    IF(@p_MA_DV_TAO IS NULL OR @p_MA_DV_TAO = '')
    BEGIN
      SELECT '-1' Result, 
        N'Sheet thông tin trụ sở, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Mã đơn vị tạo không được để trống.' ErrorDesc
      GOTO VALIDATE_ABORT
    END
    IF(@p_BUILDING_NAME IS NULL OR @p_BUILDING_NAME = '')
    BEGIN
      SELECT '-1' Result, 
        N'Sheet thông tin trụ sở, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Tên trụ sở không được để trống.' ErrorDesc
      GOTO VALIDATE_ABORT
    END
    IF(EXISTS(SELECT bm.BUILDING_ID FROM BUD_MASTER bm WHERE bm.BUILDING_NAME = @p_BUILDING_NAME))
    BEGIN
      SELECT '-1' Result, 
        N'Sheet thông tin trụ sở, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Tên trụ sở đã tồn tại trong hệ thống, vui lòng chọn tên trụ sở khác.' ErrorDesc
      GOTO VALIDATE_ABORT
    END

    SET @p_OWNER_OR_RENT = (SELECT TOP 1 ca.CDVAL FROM CM_ALLCODE ca WHERE ca.CDNAME = 'OWNER_OR_RENT' AND ca.CDTYPE = 'BM' AND ca.CONTENT LIKE '%' + @p_HINH_THUC_SO_HUU + '%'  )
    ---------------MAPPING ĐƠN VỊ--------------------------
    SET @p_BRANCH_ID = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @p_MA_DV_TAO)
    SET @p_BRANCH_USE_ID = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @p_MA_DV_SU_DUNG)
    IF(@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
    BEGIN
      SELECT '-1' Result, 
        N'Sheet thông tin trụ sở, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Mã đơn vị tạo không hợp lệ.' ErrorDesc
      GOTO VALIDATE_ABORT
    END
     
    ---------------MAPPING CÁC THÔNG TIN THEO HỢP ĐỒNG-----------------------
    -------------trường hợp hợp đồng ko có số hợp đồng thì tìm theo ID------------------
    SET @p_CONTRACT_ID = (
      SELECT TOP 1 tc.CONTRACT_ID FROM TR_CONTRACT tc 
      WHERE (tc.CONTRACT_CODE = @p_SO_HOP_DONG OR tc.CONTRACT_ID = @p_SO_HOP_DONG)
          AND tc.AUTH_STATUS = 'A' AND tc.RECORD_STATUS = '1'
    )
    IF(@p_CONTRACT_ID IS NOT NULL AND @p_CONTRACT_ID <> '')
    BEGIN
      SET @p_SUP_ID = (SELECT TOP 1 tc.SUP_ID FROM TR_CONTRACT tc WHERE tc.CONTRACT_ID = @p_CONTRACT_ID)
      SET @p_RENT_TIME = (SELECT TOP 1 DATEDIFF(MONTH, CONVERT(DATETIME, tc.START_DT, 103), CONVERT(DATETIME, tc.END_DT, 103)) FROM TR_CONTRACT tc WHERE tc.CONTRACT_ID = @p_CONTRACT_ID)
      SET @p_DEPOSITS_AMT = (SELECT TOP 1 tc.DEPOSIT_AMT FROM TR_CONTRACT tc WHERE tc.CONTRACT_ID = @p_CONTRACT_ID)
    END
    
    ----------------NẾU LÀ TÀI SẢN NGÂN HÀNG--------------------
    IF(@p_OWNER_OR_RENT = 'O')
    BEGIN
    -----------------MAPPING + CHECK MÃ TÀI SẢN-------------------------
      IF(@p_MA_TAI_SAN IS NULL OR @p_MA_TAI_SAN = '')
      BEGIN
        SELECT '-1' Result, 
        N'Sheet thông tin trụ sở, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Hình thức sở hữu là tài sản ngân hàng thì mã tài sản không được để trống.' ErrorDesc
        GOTO VALIDATE_ABORT
      END
      SET @p_ASSET_ID = (SELECT TOP 1 am.ASSET_ID FROM ASS_MASTER am WHERE am.ASSET_CODE = @p_MA_TAI_SAN)
      IF(@p_ASSET_ID IS NULL OR @p_ASSET_ID = '')
      BEGIN
        SELECT '-1' Result, 
        N'Sheet thông tin trụ sở, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Mã tài sản không hợp lệ.' ErrorDesc
        GOTO VALIDATE_ABORT
      END
      SET @p_RET_ID = (SELECT TOP 1 rm.RET_ID FROM RET_MASTER rm WHERE rm.ASSET_ID = @p_ASSET_ID)
      IF(@p_RET_ID IS NOT NULL AND @p_RET_ID <> '' AND 
        EXISTS(SELECT TOP 1 bm.BUILDING_ID FROM BUD_MASTER bm WHERE bm.ASSET_ID = @p_ASSET_ID))
      BEGIN
        DECLARE @p_EXISTS_BUILDING_ID VARCHAR(15) = (SELECT TOP 1 bm.BUILDING_ID FROM BUD_MASTER bm WHERE bm.ASSET_ID = @p_ASSET_ID)
        DECLARE @p_EXISTS_BUD_NAME NVARCHAR(1000) = (SELECT TOP 1 bm.BUILDING_NAME FROM BUD_MASTER bm WHERE bm.BUILDING_ID = @p_EXISTS_BUILDING_ID)
        SELECT '-1' Result, 
        N'Sheet thông tin trụ sở, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Mã tài sản : ' +  @p_MA_TAI_SAN + N' đã được sử dụng để khai báo thông tin trụ sở : ' 
           + @p_EXISTS_BUD_NAME + ' ('+@p_EXISTS_BUILDING_ID+ N').' ErrorDesc
        GOTO VALIDATE_ABORT
      END
      IF(@p_RET_ID IS NULL OR @p_RET_ID = '')
      BEGIN
        SELECT '-1' Result, 
        N'Sheet thông tin trụ sở, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Mã tài sản chưa được sử dụng để khai báo thông tin bất động sản.' ErrorDesc
        GOTO VALIDATE_ABORT
      END
      SELECT @p_NUM_FLOOR = rm.FLOORS, 
             @p_AREA = rm.CONSTRUCT_SQUARE, 
             @p_TOTAL_AREA = rm.TOTAL_SQUARE, 
             @p_LENGTH = rm.LENGTH, 
             @p_WIDTH = rm.WIDTH
      FROM RET_MASTER rm WHERE rm.RET_ID = @p_RET_ID
      IF(@p_BUILDING_NAME IS NULL OR @p_BUILDING_NAME = '')
      BEGIN
        SET @p_BUILDING_NAME = (SELECT TOP 1 am.ASSET_NAME FROM RET_MASTER rm INNER JOIN ASS_MASTER am ON rm.ASSET_ID = am.ASSET_ID WHERE rm.RET_ID = @p_RET_ID)
      END
      IF(@p_ADDRESS IS NULL OR @p_ADDRESS = '')
      BEGIN
        SET @p_ADDRESS = (SELECT TOP 1 rm.ADDR FROM RET_MASTER rm WHERE rm.RET_ID = @p_RET_ID)
      END
    END
    ----------------NẾU LÀ TÀI SẢN THUÊ--------------------
    ELSE IF(@p_OWNER_OR_RENT = 'U')
    BEGIN
      SET @p_ASSET_ID = NULL
      SET @p_RET_ID = NULL
      -----------------NẾU LÀ TÀI SẢN THUÊ THÌ SỐ HỢP ĐỒNG BẮT BUỘC------------------
      IF(@p_SO_HOP_DONG IS NULL OR @p_SO_HOP_DONG = '')
      BEGIN
        SELECT '-1' Result, 
        N'Sheet thông tin trụ sở, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Hình thức sở hữu là tài sản thuê thì số hợp đồng không được để trống.' ErrorDesc
        GOTO VALIDATE_ABORT
      END
      IF(@p_CONTRACT_ID IS NULL OR @p_CONTRACT_ID = '')
      BEGIN
        SELECT '-1' Result, 
        N'Sheet thông tin trụ sở, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Số hợp đồng không hợp lệ.' ErrorDesc
        GOTO VALIDATE_ABORT
      END
      IF(@p_NUM_FLOOR IS NULL)
      BEGIN
        SELECT '-1' Result, 
        N'Sheet thông tin trụ sở, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Hình thức sở hữu là tài sản thuê thì tổng số tầng không được để trống.' ErrorDesc
        GOTO VALIDATE_ABORT
      END
      IF(@p_AREA IS NULL)
      BEGIN
        SELECT '-1' Result, 
        N'Sheet thông tin trụ sở, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Hình thức sở hữu là tài sản thuê thì diện tích mặt sàn không được để trống.' ErrorDesc
        GOTO VALIDATE_ABORT
      END
      IF(@p_TOTAL_AREA IS NULL)
      BEGIN
        SELECT '-1' Result, 
        N'Sheet thông tin trụ sở, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Hình thức sở hữu là tài sản thuê thì tổng diện tích không được để trống.' ErrorDesc
        GOTO VALIDATE_ABORT
      END
      IF(@p_VAT IS NULL)
      BEGIN
        SELECT '-1' Result, 
        N'Sheet thông tin trụ sở, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Hình thức sở hữu là tài sản thuê thì VAT(%) không được để trống.' ErrorDesc
        GOTO VALIDATE_ABORT
      END
    END

    --------------TẠO ID HỆ THỐNG-----------------------------
    --------------INSERT MASTER------------
    EXEC SYS_CodeMasters_Gen 'BUD_MASTER', @p_BUILDING_ID out
		IF @p_BUILDING_ID='' OR @p_BUILDING_ID IS NULL GOTO ABORT
		INSERT INTO BUD_MASTER([BUILDING_ID],[ASSET_ID],[RET_ID],[BUILDING_NAME],[BRANCH_ID],[BRANCH_USE_ID],[ADDRESS],[OWNER_OR_RENT],
		[NUM_FLOOR],[AREA],[TOTAL_AREA],[RENT_TOTAL_AREA],[UTILZED_AREA],[NOTES],[RECORD_STATUS],
		[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT],[XML_TEMP],[RENT_AMT],[DEPOSITS_AMT],[RENT_TIME],[IS_SEND_APPR],[SEND_APPR_DT],
					[SIGN_USER], [SIGN_DT], [CONTRACT_ID], [SUP_ID], [VAT], [LENGTH], [WIDTH]
		)
    VALUES(@p_BUILDING_ID, @p_ASSET_ID, @p_RET_ID, @p_BUILDING_NAME, @p_BRANCH_ID, @p_BRANCH_USE_ID, @p_ADDRESS, @p_OWNER_OR_RENT,@p_NUM_FLOOR, @p_AREA, 
            @p_TOTAL_AREA, 0, 0, @p_NOTES, @p_RECORD_STATUS, @p_AUTH_STATUS, @p_MAKER_ID, @p_CREATE_DT, @p_CHECKER_ID, @p_APPROVE_DT, NULL, @p_RENT_AMT,
            @p_DEPOSITS_AMT, @p_RENT_TIME, @p_IS_SEND_APPR, @p_SEND_APPR_DT, @p_SIGN_USER, @p_SIGN_DT, @p_CONTRACT_ID, @p_SUP_ID, @p_VAT, @p_LENGTH, @p_WIDTH)

    IF @@error <> 0 GOTO ABORT

    --------------INSERT DETAIL--------------------
    DECLARE @d_BUILDING_AREA_ID VARCHAR(15),
			      @d_FLOOR_NO INT,
				    @d_FLOOR_AREA decimal(18,2),
				    @d_INTERNAL_AREA decimal(18,2),
				    @d_RENT_AREA decimal(18,2),
				    @d_UTILZED_AREA decimal(18,2),
				    @d_NOTES	NVARCHAR(1000)

    DECLARE @p_ROW_COUNT_DETAIL INT = 0
    DECLARE ImportBudAreaDTCursor CURSOR FOR
      SELECT qtsdti.TANG, qtsdti.DIEN_TICH_TANG, qtsdti.DT_DA_SU_DUNG, 
             qtsdti.DT_CHO_THUE, qtsdti.GHI_CHU
      FROM QL_TRU_SO_DS_TANG_IMPORT qtsdti
      WHERE qtsdti.TEN_TRU_SO = @p_BUILDING_NAME

    OPEN ImportBudAreaDTCursor

    FETCH NEXT FROM ImportBudAreaDTCursor INTO
      @d_FLOOR_NO, @d_FLOOR_AREA, @d_INTERNAL_AREA, @d_UTILZED_AREA, @d_NOTES

    WHILE @@fetch_status = 0
    BEGIN
      SET @p_ROW_COUNT_DETAIL = @p_ROW_COUNT_DETAIL + 1
      EXEC SYS_CodeMasters_Gen 'BUD_AREA_DT', @d_BUILDING_AREA_ID out
			IF @d_BUILDING_AREA_ID='' OR @d_BUILDING_AREA_ID IS NULL GOTO ABORT

      IF(@d_FLOOR_NO IS NULL)
      BEGIN
        SELECT '-1' Result, 
        N'Sheet danh sách các tầng trụ sở, dòng: ' +CAST(@p_ROW_COUNT_DETAIL AS NVARCHAR(10))+ 
        N': Tầng không được để trống.' ErrorDesc
        GOTO VALIDATE_ABORT_DT
      END
      IF(@d_FLOOR_AREA IS NULL)
      BEGIN
        SELECT '-1' Result, 
        N'Sheet danh sách các tầng trụ sở, dòng: ' +CAST(@p_ROW_COUNT_DETAIL AS NVARCHAR(10))+ 
        N': Diện tích tầng không được để trống.' ErrorDesc
        GOTO VALIDATE_ABORT_DT
      END

      IF(@d_FLOOR_AREA - (@d_INTERNAL_AREA + @d_UTILZED_AREA) < 0)
      BEGIN
        SELECT '-1' Result, 
        N'Sheet danh sách các tầng trụ sở, dòng: ' +CAST(@p_ROW_COUNT_DETAIL AS NVARCHAR(10)) + N', Tên trụ sở: '+@p_BUILDING_NAME+ 
        N', Tầng: ' +CAST(@d_FLOOR_NO AS NVARCHAR(10))+ 
        N': Tổng diện tích sử dụng và diện tích cho thuê phải nhỏ hơn hoặc bằng diện tích tầng: ' +CAST(@d_FLOOR_AREA AS NVARCHAR(10)) ErrorDesc
        GOTO VALIDATE_ABORT_DT
      END
      IF(@d_FLOOR_AREA > @p_AREA)
      BEGIN
        SELECT '-1' Result, 
        N'Sheet danh sách các tầng trụ sở, dòng: ' +CAST(@p_ROW_COUNT_DETAIL AS NVARCHAR(10))+ N', Tên trụ sở: ' +@p_BUILDING_NAME+
        N', Tầng: ' +CAST(@d_FLOOR_NO AS NVARCHAR(10))+
        N': Diện tích tầng không được lớn hơn diện tích mặt sàn: ' +CAST(@p_AREA AS NVARCHAR(10)) ErrorDesc
        GOTO VALIDATE_ABORT_DT
      END

      INSERT INTO BUD_AREA_DT([BUILDING_AREA_ID],[BUILD_ID],[FLOOR_NO],[FLOOR_AREA],[INTERNAL_AREA],
			[RENT_AREA],[UTILZED_AREA],[NOTES],[RECORD_STATUS],[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT])
      VALUES(@d_BUILDING_AREA_ID, @p_BUILDING_ID, @d_FLOOR_NO, @d_FLOOR_AREA, @d_INTERNAL_AREA, 0, @d_UTILZED_AREA, @d_NOTES, @p_RECORD_STATUS,
              @p_AUTH_STATUS, @p_MAKER_ID, @p_CREATE_DT, @p_CHECKER_ID, @p_APPROVE_DT)

      FETCH NEXT FROM ImportBudAreaDTCursor INTO
      @d_FLOOR_NO, @d_FLOOR_AREA, @d_INTERNAL_AREA, @d_UTILZED_AREA, @d_NOTES
    END
    CLOSE ImportBudAreaDTCursor
    DEALLOCATE ImportBudAreaDTCursor
    ------------END INSERT DETAIL-------------------------


    FETCH NEXT FROM ImportBudMasterCursor INTO
    @p_HINH_THUC_SO_HUU, @p_MA_TAI_SAN, @p_MA_DV_TAO, @p_MA_DV_SU_DUNG, @p_SO_HOP_DONG, @p_BUILDING_NAME, @p_ADDRESS, @p_NUM_FLOOR,
    @p_AREA, @p_TOTAL_AREA, @p_LENGTH, @p_WIDTH, @p_RENT_AMT, @p_VAT, @p_NOTES

  END
  
  CLOSE ImportBudMasterCursor
  DEALLOCATE ImportBudMasterCursor

COMMIT TRANSACTION
SELECT '0' as Result, N'Import dữ liệu Quản lý trụ sở thành công' ErrorDesc
RETURN '0'

ABORT:
BEGIN
		ROLLBACK TRANSACTION
    CLOSE ImportBudMasterCursor
    DEALLOCATE ImportBudMasterCursor
		SELECT '-1' as Result, '' ErrorDesc
		RETURN '-1'
END

VALIDATE_ABORT:
BEGIN
    ROLLBACK TRANSACTION
    CLOSE ImportBudMasterCursor
    DEALLOCATE ImportBudMasterCursor
    RETURN '-1'
END
ABORT_DT:
BEGIN
    ROLLBACK TRANSACTION
    CLOSE ImportBudMasterCursor
    DEALLOCATE ImportBudMasterCursor
    CLOSE ImportBudAreaDTCursor
    DEALLOCATE ImportBudAreaDTCursor
		SELECT '-1' as Result, '' ErrorDesc
		RETURN '-1'
END
VALIDATE_ABORT_DT:
BEGIN
    ROLLBACK TRANSACTION
    CLOSE ImportBudMasterCursor
    DEALLOCATE ImportBudMasterCursor
    CLOSE ImportBudAreaDTCursor
    DEALLOCATE ImportBudAreaDTCursor
    RETURN '-1'
END





GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[BUD_CONTRACT_MASTER_IMPORT]'
GO

CREATE PROCEDURE [dbo].[BUD_CONTRACT_MASTER_IMPORT]
AS

BEGIN TRANSACTION
  DECLARE @p_CONTRACT_ID	varchar(15)  = NULL,
        @p_BUILDING_ID	varchar(15)  = NULL,
        @p_CONTRACT_NO	nvarchar(100)  = NULL,
        @p_VERSION_NO  int = 1,
        @p_CUST_ID	varchar(15)  = NULL,
        @p_INPUT_DT	DATETIME = NULL,
        @p_AMEND_DT	DATETIME = NULL,
        @p_FROM_DT	DATETIME = GETDATE(),
        @p_TO_DATE	DATETIME = GETDATE(),
        @p_CLOSE_DT	DATETIME = NULL,
        @p_MONTHS	int=0,
        @p_RENT_PRICE	numeric(18)  = NULL,
        @p_NOTES		NVARCHAR(MAX)= NULL,
        @p_BRANCH_ID  VARCHAR(15) = NULL,
        @p_RECORD_STATUS	varchar(1)  = '1',
        @p_AUTH_STATUS	varchar(1)  = 'A',
        @p_STATUS       VARCHAR(1) = 'N',
        @p_MAKER_ID	varchar(15)  = 'Khangpth',
        @p_CREATE_DT	DATETIME = GETDATE(),
        @p_CHECKER_ID	varchar(15)  = 'xuanpt',
        @p_APPROVE_DT	DATETIME = GETDATE(),
        @p_VAT INT,
        @p_VAT_AMT DECIMAL(18,0),
        @p_DEPOSITS_AMT DECIMAL(18,0),
        @p_PAYMENT_SHEDULE	NVARCHAR(500),
        @p_IS_SEND_APPR VARCHAR(15) = 'Y',
        @p_SEND_APPR_DT DATETIME = GETDATE(),
        @p_SIGN_USER VARCHAR(15) = NULL,
        @p_SIGN_DT DATETIME = NULL

  DECLARE @p_MA_DV_TAO VARCHAR(15),
          @p_TEN_TRU_SO NVARCHAR(1000)

  DECLARE ImportBudContractMasterCursor CURSOR FOR
    SELECT dnbi.MA_DV_TAO, dnbi.TEN_TRU_SO, dnbi.NGAY_BAT_DAU_SD, dnbi.NGAY_DUA_VAO_SD_SAU_CUNG, dnbi.GHI_CHU
    FROM DTSD_NOI_BO_IMPORT dnbi

  OPEN ImportBudContractMasterCursor

  DECLARE @p_ROW_COUNT INT = 0

  FETCH NEXT FROM ImportBudContractMasterCursor INTO  
    @p_MA_DV_TAO, @p_TEN_TRU_SO, @p_INPUT_DT, @p_AMEND_DT, @p_NOTES

  WHILE @@fetch_status = 0
  BEGIN

    SET @p_MA_DV_TAO = TRIM(@p_MA_DV_TAO)
    SET @p_TEN_TRU_SO = TRIM(@p_TEN_TRU_SO)

    SET @p_ROW_COUNT = @p_ROW_COUNT + 1

    SET @p_BRANCH_ID = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @p_MA_DV_TAO)
    IF(@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
    BEGIN
      SELECT '-1' Result, 
        N'Sheet Quản lý DTSD nội bộ, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Mã đơn vị tạo không hợp lệ.' ErrorDesc
      GOTO VALIDATE_ABORT
    END
    SET @p_BUILDING_ID = (SELECT TOP 1 bm.BUILDING_ID FROM BUD_MASTER bm WHERE bm.BUILDING_NAME = @p_TEN_TRU_SO)
    IF(@p_BUILDING_ID IS NULL OR @p_BUILDING_ID = '')
    BEGIN
      SELECT '-1' Result, 
        N'Sheet Quản lý DTSD nội bộ, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Tên trụ sở không hợp lệ.' ErrorDesc
        GOTO VALIDATE_ABORT
    END


    -----------------TẠO ID HỆ THỐNG--------------------
    ----------------INSERT MASTER-----------------------
    EXEC SYS_CodeMasters_Gen 'BUD_CONTRACT_MASTER', @p_CONTRACT_ID out
		IF @p_CONTRACT_ID='' OR @p_CONTRACT_ID IS NULL GOTO ABORT
		INSERT INTO BUD_CONTRACT_MASTER([CONTRACT_ID],[BUILDING_ID],[CONTRACT_NO],[VERSION_NO],[CUST_ID],
					[INPUT_DT],[AMEND_DT],[FROM_DT],[TO_DATE],[CLOSE_DT],[MONTHS],[RENT_PRICE],[NOTES],
					[BRANCH_ID],[RECORD_STATUS],[AUTH_STATUS],[STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],
					[APPROVE_DT],[XML_TEMP],[VAT],[VAT_AMT],[DEPOSITS_AMT],[PAYMENT_SHEDULE],[IS_SEND_APPR],[SEND_APPR_DT],
					[SIGN_USER], [SIGN_DT])
    VALUES(@p_CONTRACT_ID, @p_BUILDING_ID, NULL, 1, NULL, @p_INPUT_DT, @p_AMEND_DT, @p_FROM_DT, @p_TO_DATE, NULL, NULL, NULL, @p_NOTES,
            @p_BRANCH_ID, @p_RECORD_STATUS, @p_AUTH_STATUS, @p_STATUS, @p_MAKER_ID, @p_CREATE_DT, @p_CHECKER_ID, @p_APPROVE_DT, NULL,  NULL, NULL, NULL,
            NULL, @p_IS_SEND_APPR, @p_SEND_APPR_DT, @p_SIGN_USER, @p_SIGN_DT)

    IF @@error <> 0 GOTO ABORT

    ----------------INSERT DETAIL--------------------
    DECLARE @d_CONTRACT_DTID   VARCHAR(15),	
    				@d_VERSION_NO INT,
    				@d_BUILDING_AREA_ID VARCHAR(15),
    				@d_RENT_AREA DECIMAL(18,2),
    				@d_RENT_AREA_APP DECIMAL(18,2),
    				@d_AREA_REMAIN DECIMAL(18,2),
    				@d_RENT_PRICE DECIMAL,
    				@d_NOTES	NVARCHAR(MAX),
    				@d_DIEN_TICH_TANG DECIMAL(18,2),
    				@d_DIEN_TICH_SD_NOI_BO DECIMAL(18,2),
    				@d_DIEN_TICH_DA_CHO_THUE DECIMAL(18,2),
    				@d_DIEN_TICH_CON_LAI DECIMAL(18,2),
    				@d_DIEN_TICH_CAN_SD DECIMAL(18,2),
    				@d_REASON NVARCHAR(MAX),
    				@d_PRICE DECIMAL,
    				@d_TOTAL_AMT DECIMAL,
    				@d_DIEN_TICH_CON_LAI_FINAL DECIMAL(18,2),
    				@d_BRANCH_ID NVARCHAR(15),
    				@d_DEP_ID NVARCHAR(15),
    				@d_VAT INT,
    				@d_PAY_PHASE VARCHAR(15)

    DECLARE 
            @d_TANG INT,
            @d_MA_DV_CHIU_CHI_PHI VARCHAR(15),
            @d_MA_PHONG_BAN_CHIU_CHI_PHI VARCHAR(15),
            @d_KY_THANH_TOAN NVARCHAR(200)

    DECLARE @p_ROW_COUNT_DETAIL INT = 0

    DECLARE ImportBudContractDTCursor CURSOR FOR
      SELECT dnbdi.TANG, dnbdi.DT_CAN_SU_DUNG, dnbdi.MA_DV_CHIU_CHI_PHI, 
             dnbdi.MA_PHONG_BAN_CHIU_CHI_PHI, dnbdi.MUC_DICH_SU_DUNG, dnbdi.DON_GIA, dnbdi.VAT, dnbdi.KY_THANH_TOAN, dnbdi.GHI_CHU
      FROM DTSD_NOI_BO_DT_IMPORT dnbdi
      WHERE (dnbdi.TEN_TRU_SO = @p_TEN_TRU_SO)
        
    OPEN ImportBudContractDTCursor

    FETCH NEXT FROM ImportBudContractDTCursor INTO
      @d_TANG, @d_DIEN_TICH_CAN_SD, @d_MA_DV_CHIU_CHI_PHI, @d_MA_PHONG_BAN_CHIU_CHI_PHI, 
      @d_REASON, @d_PRICE, @d_VAT, @d_KY_THANH_TOAN, @d_NOTES
    
    WHILE @@fetch_status = 0
    BEGIN

      SET @d_MA_DV_CHIU_CHI_PHI = TRIM(@d_MA_DV_CHIU_CHI_PHI)
      SET @d_MA_PHONG_BAN_CHIU_CHI_PHI = TRIM(@d_MA_PHONG_BAN_CHIU_CHI_PHI)
      SET @d_KY_THANH_TOAN = TRIM(@d_KY_THANH_TOAN)

      PRINT  @d_MA_DV_CHIU_CHI_PHI
      PRINT @d_MA_PHONG_BAN_CHIU_CHI_PHI

      SET @p_ROW_COUNT_DETAIL = @p_ROW_COUNT_DETAIL + 1

      SET @d_BUILDING_AREA_ID = (SELECT TOP 1 bad.BUILDING_AREA_ID FROM BUD_AREA_DT bad WHERE bad.FLOOR_NO = @d_TANG AND bad.BUILD_ID = @p_BUILDING_ID)
      IF(@d_BUILDING_AREA_ID IS NULL OR @d_BUILDING_AREA_ID = '')
      BEGIN
        SELECT '-1' Result, 
        N'Sheet thông tin chi tiết DTSD nội bộ, dòng: ' +CAST(@p_ROW_COUNT_DETAIL AS NVARCHAR(10))+ 
        N', tên trụ sở: ' + @p_TEN_TRU_SO + N', tầng ' + CAST(@d_TANG AS NVARCHAR(10)) + 
        N': Tầng không hợp lệ.' ErrorDesc
        GOTO VALIDATE_ABORT_DT
      END

      SET @d_BRANCH_ID = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @d_MA_DV_CHIU_CHI_PHI)
      PRINT @d_BRANCH_ID
      IF(@d_BRANCH_ID IS NULL OR @d_BRANCH_ID = '')
      BEGIN
        SELECT '-1' Result, 
        N'Sheet thông tin chi tiết DTSD nội bộ, dòng: ' +CAST(@p_ROW_COUNT_DETAIL AS NVARCHAR(10))+ 
        N', tên trụ sở: ' + @p_TEN_TRU_SO + N', tầng ' + CAST(@d_TANG AS NVARCHAR(10)) + 
        N': Mã đơn vị chịu chi phí không hợp lệ.' ErrorDesc
        GOTO VALIDATE_ABORT_DT
      END
      SET @d_DEP_ID = NULL
      IF((@d_MA_DV_CHIU_CHI_PHI IS NOT NULL AND @d_MA_DV_CHIU_CHI_PHI <> '') AND (@d_MA_PHONG_BAN_CHIU_CHI_PHI IS NOT NULL AND @d_MA_PHONG_BAN_CHIU_CHI_PHI <> ''))
      BEGIN
        SET @d_DEP_ID = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @d_MA_PHONG_BAN_CHIU_CHI_PHI AND cd.BRANCH_ID = @d_BRANCH_ID)
        IF(@d_DEP_ID IS NULL OR @d_DEP_ID = '')
        BEGIN
          SELECT '-1' Result, 
          N'Sheet thông tin chi tiết DTSD nội bộ, dòng: ' +CAST(@p_ROW_COUNT_DETAIL AS NVARCHAR(10))+ 
          N', tên trụ sở: ' + @p_TEN_TRU_SO + N', tầng ' + CAST(@d_TANG AS NVARCHAR(10)) + 
          N': Mã phòng ban chịu chi phí không hợp lệ.' ErrorDesc
          GOTO VALIDATE_ABORT_DT
        END
      END
      SET @d_PAY_PHASE = (SELECT TOP 1 ca.CDVAL FROM CM_ALLCODE ca WHERE ca.CDTYPE = 'BUD_CONTRACT' AND ca.CDNAME = 'PAY_PHASE' AND ca.CONTENT LIKE '%' + @d_KY_THANH_TOAN + '%')

      DECLARE @d_FLOOR_AREA DECIMAL(18,2), -----TỔNG DT TẦNG
              @d_INTERNAL_AREA DECIMAL(18,2), -----DT NỘI BỘ SỬ DỤNG
              @d_UTILIZED_AREA DECIMAL(18,2) ----- DT ĐÃ CHO THUÊ

      SELECT @d_FLOOR_AREA = bad.FLOOR_AREA, @d_INTERNAL_AREA = bad.INTERNAL_AREA, @d_UTILIZED_AREA = bad.UTILZED_AREA
      FROM BUD_AREA_DT bad
      WHERE bad.BUILDING_AREA_ID = @d_BUILDING_AREA_ID AND bad.BUILD_ID = @p_BUILDING_ID

      DECLARE @d_REMAIN_FLOOR_AREA DECIMAL(18,2) = @d_FLOOR_AREA - (@d_INTERNAL_AREA + @d_UTILIZED_AREA)
      ---------------Nếu DT cần sử dụng < DT còn lại----------------------
      IF(@d_FLOOR_AREA - (@d_INTERNAL_AREA + @d_UTILIZED_AREA) < @d_DIEN_TICH_CAN_SD)
      BEGIN
        SELECT '-1' Result, 
        N'Sheet thông tin chi tiết DTSD nội bộ, dòng: ' +CAST(@p_ROW_COUNT_DETAIL AS NVARCHAR(10))+ 
        N', tên trụ sở: ' + @p_TEN_TRU_SO + N', tầng ' + CAST(@d_TANG AS NVARCHAR(10)) + 
        N': DT cần sử dụng không được vượt quá diện tích còn lại có thể sử dụng: ' +@d_REMAIN_FLOOR_AREA  ErrorDesc
        GOTO VALIDATE_ABORT_DT
      END

      SET @d_VERSION_NO = @p_VERSION_NO
      SET @d_DIEN_TICH_TANG = @d_FLOOR_AREA
      SET @d_DIEN_TICH_SD_NOI_BO = @d_INTERNAL_AREA + @d_DIEN_TICH_CAN_SD
      SET @d_DIEN_TICH_DA_CHO_THUE = @d_UTILIZED_AREA
      SET @d_DIEN_TICH_CON_LAI = @d_DIEN_TICH_TANG - (@d_DIEN_TICH_SD_NOI_BO + @d_DIEN_TICH_DA_CHO_THUE)
      SET @d_DIEN_TICH_CON_LAI_FINAL = @d_DIEN_TICH_CON_LAI
      SET @d_TOTAL_AMT = (@d_PRICE * @d_DIEN_TICH_CAN_SD) + (@d_PRICE * CAST(ISNULL(@d_VAT,0) AS DECIMAL(18,2))/100)

      EXEC SYS_CodeMasters_Gen 'BUD_CONTRACT_DT', @d_CONTRACT_DTID OUT
      INSERT INTO BUD_CONTRACT_DT([CONTRACT_DTID],[BUILD_ID],[CONTRACT_ID],[VERSION_NO],[BUILDING_AREA_ID],[RENT_AREA],[RENT_AREA_APP],
							[AREA_REMAIN],[RENT_PRICE],[NOTES], [DIEN_TICH_TANG], [DIEN_TICH_SD_NOI_BO],[DIEN_TICH_DA_CHO_THUE],[DIEN_TICH_CON_LAI],
							[DIEN_TICH_CAN_SD],[REASON],[PRICE],[TOTAL_AMT],[DIEN_TICH_CON_LAI_FINAL],[BRANCH_ID],[DEP_ID], [VAT], [PAY_PHASE])
      VALUES(@d_CONTRACT_DTID, @p_BUILDING_ID, @p_CONTRACT_ID, @d_VERSION_NO, @d_BUILDING_AREA_ID, 0, @d_DIEN_TICH_CAN_SD, @d_DIEN_TICH_TANG,
              0, @d_NOTES, @d_DIEN_TICH_TANG, @d_DIEN_TICH_SD_NOI_BO, @d_DIEN_TICH_DA_CHO_THUE, @d_DIEN_TICH_CON_LAI, @d_DIEN_TICH_CAN_SD, @d_REASON,
              @d_PRICE, @d_TOTAL_AMT, @d_DIEN_TICH_CON_LAI_FINAL, @d_BRANCH_ID, @d_DEP_ID, @d_VAT, @d_PAY_PHASE)
      
      IF @@error <> 0 GOTO ABORT_DT

      --------------------UPDATE LẠI DIỆN TÍCH TẦNG--------------------
      UPDATE BUD_AREA_DT SET INTERNAL_AREA = INTERNAL_AREA + @d_DIEN_TICH_CAN_SD 
      WHERE BUILDING_AREA_ID = @d_BUILDING_AREA_ID AND BUILD_ID = @p_BUILDING_ID

      --------------------INSERT VÀO BẢNG LOG----------------------
      DECLARE @l_UTIL_ID VARCHAR(15)
		  EXEC SYS_CodeMasters_Gen 'BUD_UTIL_LOG', @l_UTIL_ID OUT
		  IF @l_UTIL_ID='' OR @l_UTIL_ID IS NULL GOTO ABORT
      DECLARE @p_IS_FIRST_TIME VARCHAR(1)
      IF(
        NOT EXISTS(
    			SELECT * FROM BUD_CONTRACT_LOG WHERE CONTRACT_ID = @p_CONTRACT_ID
    				AND BUILDING_AREA_ID = @d_BUILDING_AREA_ID
    				AND CONTRACT_DT_ID = @d_CONTRACT_DTID
    				AND IS_FIRST_TIME = 'Y'
  			)
      )
      BEGIN
        SET @p_IS_FIRST_TIME = 'Y'
      END
      ELSE
      BEGIN
        SET @p_IS_FIRST_TIME = 'N'
      END


      INSERT INTO BUD_CONTRACT_LOG 
			([BUD_CONTRACT_LOG_ID], [CONTRACT_ID], [CONTRACT_DT_ID], [CUSTOMER_ID], [BUILDING_AREA_ID], [TYPE],
			[INTERNAL_AREA], [RENT_AREA], [REMAIN_AREA], [UNIT_PRICE], [VAT], [TOTAL_AMT], 
			[IS_FIRST_TIME], [BRANCH_RENT_ID], [DEP_RENT_ID], [MAKER_ID], [CREATE_DT], [CHECKER_ID],
			[APPROVE_DT], [AUTH_STATUS])
		VALUES
			(@l_UTIL_ID, @p_CONTRACT_ID, @d_CONTRACT_DTID, NULL, @d_BUILDING_AREA_ID, 'INTERNAL',
			@d_DIEN_TICH_CAN_SD, NULL, @d_DIEN_TICH_CON_LAI_FINAL, @d_PRICE, @d_VAT, @d_TOTAL_AMT,
			@p_IS_FIRST_TIME, @d_BRANCH_ID, @d_DEP_ID, @p_MAKER_ID, GETDATE(), @P_CHECKER_ID,
			GETDATE(), 'A')

      FETCH NEXT FROM ImportBudContractDTCursor INTO
      @d_TANG, @d_DIEN_TICH_CAN_SD, @d_MA_DV_CHIU_CHI_PHI, @d_MA_PHONG_BAN_CHIU_CHI_PHI, 
      @d_REASON, @d_PRICE, @d_VAT, @d_KY_THANH_TOAN, @d_NOTES
    END

    CLOSE ImportBudContractDTCursor
    DEALLOCATE ImportBudContractDTCursor
    
    ------------------UPDATE TỔNG SỐ TIỀN MASTER---------------------------
    DECLARE @p_SUM_TOTAL_AMT_DT DECIMAL = (
      SELECT ISNULL(SUM(bcd.TOTAL_AMT),0)
      FROM BUD_CONTRACT_DT bcd WHERE bcd.CONTRACT_ID = @p_CONTRACT_ID 
    )
    
    UPDATE BUD_CONTRACT_MASTER 
      SET  RENT_PRICE = @p_SUM_TOTAL_AMT_DT
    WHERE CONTRACT_ID = @p_CONTRACT_ID

    FETCH NEXT FROM ImportBudContractMasterCursor INTO  
    @p_MA_DV_TAO, @p_TEN_TRU_SO, @p_INPUT_DT, @p_AMEND_DT, @p_NOTES
  END

  CLOSE ImportBudContractMasterCursor
  DEALLOCATE ImportBudContractMasterCursor


COMMIT TRANSACTION
SELECT '0' as Result, N'Import dữ liệu Thông tin khai báo DTSD nội bộ thành công' ErrorDesc
RETURN '0'

ABORT:
BEGIN
  ROLLBACK TRANSACTION
  CLOSE ImportBudContractMasterCursor
  DEALLOCATE ImportBudContractMasterCursor
  SELECT '-1' as Result, '' ErrorDesc
	RETURN '-1'
END

VALIDATE_ABORT:
BEGIN
    ROLLBACK TRANSACTION
    CLOSE ImportBudContractMasterCursor
    DEALLOCATE ImportBudContractMasterCursor
    RETURN '-1'
END
ABORT_DT:
BEGIN
    ROLLBACK TRANSACTION
    CLOSE ImportBudContractMasterCursor
    DEALLOCATE ImportBudContractMasterCursor
    CLOSE ImportBudContractDTCursor
    DEALLOCATE ImportBudContractDTCursor
		SELECT '-1' as Result, '' ErrorDesc
		RETURN '-1'
END
VALIDATE_ABORT_DT:
BEGIN
    ROLLBACK TRANSACTION
    CLOSE ImportBudContractMasterCursor
    DEALLOCATE ImportBudContractMasterCursor
    CLOSE ImportBudContractDTCursor
    DEALLOCATE ImportBudContractDTCursor
    RETURN '-1'
END




GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[rpt_XE_DSM_BH_Excel]'
GO




CREATE   PROCEDURE [dbo].[rpt_XE_DSM_BH_Excel]
	@p_Fromdate varchar(20) = null,
	@p_Todate varchar(20) = null,
	@P_LEVEL varchar(10) = null,
	@p_BRANCH_LOGIN VARCHAR(15) = NULL,
	@P_BRANCH_ID VARCHAR(15) = NULL,
	@p_BIENSOXE VARCHAR(15)= NULL,
	@ISVPKV VARCHAR(1) = '1'--1: CHECK; 0: UNCHECK
AS
BEGIN
declare @tmp table(BRANCH_ID varchar(15))
insert into @tmp  SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)
declare @tmp_login table(BRANCH_ID varchar(15))
insert into @tmp_login  SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)
SELECT * FROM (
		SELECT 
		NULL STT,
		CONVERT(VARCHAR, Row_number() OVER(PARTITION BY E.BRANCH_ID ORDER BY A.CAR_INSU_ID)) AS STTT,
		CONVERT(VARCHAR, Row_number() OVER(PARTITION BY E.BRANCH_ID ORDER BY A.CAR_INSU_ID)) AS DON_VI,
		B.N_PLATE AS BIEN_SO,
		CONVERT(VARCHAR, A.INSU_DT,103) + ' - ' + CONVERT(VARCHAR, A.INSU_MAT_DT,103) AS THOI_HAN_BH,
		CONVERT(DATETIME, A.INSU_MAT_DT, 103) - CONVERT(DATETIME, A.INSU_DT, 103) AS SO_NGAY_THAM_GIA_BH,
		A.NOTES AS GHI_CHU,
		C.ASSET_NAME AS LOAI_XE,	
		D.CAR_TYPE_NAME AS SO_CHO_NGOI,
		B.MANUFACTURE_YEAR AS NAM_SX,
		E.BRANCH_ID AS BRANCH_ID,
		A.INSU_AMT AS TONG_PHI_BH
		FROM CAR_INSURE A 
		INNER JOIN CAR_MASTER B ON A.ASSET_ID = B.ASSET_ID
		INNER JOIN ASS_MASTER C ON B.ASSET_ID = C.ASSET_ID
		INNER JOIN CAR_TYPE D ON B.CAR_TYPE_ID = D.CAR_TYPE_ID
		LEFT JOIN CM_BRANCH E ON E.BRANCH_ID = C.BRANCH_ID
		LEFT JOIN CM_ALLCODE F ON A.INSU_TYPE = F.CDVAL AND F.CDNAME='INSU_TYPE' AND F.CDTYPE ='CAR'
		LEFT JOIN INSU_COMPANY IC ON A.INSU_COMPANY= IC.INSU_COMPANY_ID
		LEFT JOIN CM_DEPARTMENT DP ON DP.DEP_ID = C.DEPT_ID
		WHERE
		(A.APPROVE_DT >= CONVERT(datetime, @p_Fromdate, 103) OR @p_TOdate IS NULL OR @p_Fromdate = '')
		AND	(C.APPROVE_DT <= CONVERT(datetime, @p_Todate, 103) OR @p_Todate IS NULL OR @p_Todate = '')
		AND( B.N_PLATE =@p_BIENSOXE OR @p_BIENSOXE IS NULL OR @p_BIENSOXE='')
		AND ((@P_LEVEL = 'ALL' AND C.BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp))
		OR (@P_LEVEL = 'UNIT' AND C.BRANCH_ID = @p_BRANCH_ID) OR @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)
		AND ((@P_LEVEL = 'ALL' AND C.BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp_login))
		OR (@P_LEVEL = 'UNIT' AND C.BRANCH_ID = @p_BRANCH_LOGIN) OR @p_BRANCH_LOGIN = '' OR @p_BRANCH_LOGIN IS NULL 
		OR @p_BRANCH_ID IS NOT NULL OR @p_BRANCH_ID <> ''
		-- TUNT 19/03/2020 BAO CAO CHI LAY DU LIEU DEN LUC TAI SAN BI THANH LY
		AND (B.LIQUID_DATE IS NULL OR B.LIQUID_DATE = '' OR (B.LIQUID_DATE <= CONVERT(DATETIME,@p_Todate,103))))
		-- TUNT 19/11/2020 BO QUA XE CUA PHONG BAN VPKV
		AND ((@ISVPKV = '1' AND C.DEPT_ID NOT IN (SELECT DEP_ID FROM CM_DEPARTMENT WHERE GROUP_ID = 'CMDG00000000031'))
			OR @ISVPKV = '0' OR C.DEPT_ID IS NULL OR C.DEPT_ID = '')
	UNION ALL
		SELECT 
		Row_number() OVER(ORDER BY BRA.BRANCH_ID) AS STT,
		'0' AS STTT,
		'StartMerge.' AS DON_VI,
		NULL BIEN_SO,
		NULL THOI_HAN_BH,
		NULL SO_NGAY_THAM_GIA_BH,
		NULL AS GHI_CHU,
		'EndMerge.' + BRA.BRANCH_NAME AS LOAI_XE,
		NULL SO_CHO_NGOI,
		NULL NAM_SX,
		BRA.BRANCH_ID AS BRANCH_ID,
		NULL TONG_PHI_BH
		FROM CM_BRANCH BRA 
		WHERE BRA.BRANCH_ID IN (SELECT E.BRANCH_ID FROM CAR_INSURE A 
			INNER JOIN CAR_MASTER B ON A.ASSET_ID = B.ASSET_ID
			INNER JOIN ASS_MASTER C ON B.ASSET_ID = C.ASSET_ID
			LEFT JOIN CM_BRANCH E ON E.BRANCH_ID = C.BRANCH_ID
			WHERE
			(A.APPROVE_DT >= CONVERT(datetime, @p_Fromdate, 103) OR @p_TOdate IS NULL OR @p_Fromdate = '')
			AND	(C.APPROVE_DT <= CONVERT(datetime, @p_Todate, 103) OR @p_Todate IS NULL OR @p_Todate = '')
			AND( B.N_PLATE =@p_BIENSOXE OR @p_BIENSOXE IS NULL OR @p_BIENSOXE='')
			AND ((@P_LEVEL = 'ALL' AND C.BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp))
			OR (@P_LEVEL = 'UNIT' AND C.BRANCH_ID = @p_BRANCH_ID) OR @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)
			AND ((@P_LEVEL = 'ALL' AND C.BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp_login))
			OR (@P_LEVEL = 'UNIT' AND C.BRANCH_ID = @p_BRANCH_LOGIN) OR @p_BRANCH_LOGIN = '' OR @p_BRANCH_LOGIN IS NULL 
			OR @p_BRANCH_ID IS NOT NULL OR @p_BRANCH_ID <> ''
			-- TUNT 19/03/2020 BAO CAO CHI LAY DU LIEU DEN LUC TAI SAN BI THANH LY
			AND (B.LIQUID_DATE IS NULL OR B.LIQUID_DATE = '' OR (B.LIQUID_DATE <= CONVERT(DATETIME,@p_Todate,103))))
			-- TUNT 19/11/2020 BO QUA XE CUA PHONG BAN VPKV
			AND ((@ISVPKV = '1' AND C.DEPT_ID NOT IN (SELECT DEP_ID FROM CM_DEPARTMENT WHERE GROUP_ID = 'CMDG00000000031'))
					OR @ISVPKV = '0' OR C.DEPT_ID IS NULL OR C.DEPT_ID = ''))
	) DSX
	ORDER BY DSX.BRANCH_ID, DSX.STTT

END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[BUD_CONTRACT_CUST_MASTER_IMPORT]'
GO

CREATE PROCEDURE [dbo].[BUD_CONTRACT_CUST_MASTER_IMPORT]
AS
BEGIN TRANSACTION
  DECLARE @p_CONTRACT_ID	varchar(15)  = NULL,
          @p_BUILDING_ID	varchar(15)  = NULL,
          @p_CONTRACT_NO	nvarchar(100)  = NULL,
          @p_VERSION_NO  int = 1,
          @p_TR_CONTRACT_ID VARCHAR(15) = NULL,
          @p_CUST_ID	varchar(15)  = NULL,
          @p_INPUT_DT	DATETIME = NULL,
          @p_AMEND_DT	DATETIME = NULL,
          @p_FROM_DT	DATETIME = NULL,
          @p_TO_DATE	DATETIME = NULL,
          @p_CLOSE_DT	DATETIME = NULL,
          @p_MONTHS	int = NULL,
          @p_RENT_PRICE	numeric(18)  = NULL,
          @p_NOTES		NVARCHAR(MAX)= NULL,
          @p_BRANCH_ID  VARCHAR(15) = NULL,
          @p_RECORD_STATUS	varchar(1)  = '1',
          @p_AUTH_STATUS	varchar(1)  = 'A',
          @p_STATUS       VARCHAR(1) = NULL,
          @p_MAKER_ID	varchar(15)  = 'quoclnb',
          @p_CREATE_DT	DATETIME = GETDATE(),
          @p_CHECKER_ID	varchar(15)  = 'xuanpt',
          @p_APPROVE_DT	DATETIME = GETDATE(),
          @p_VAT INT = NULL,
          @p_VAT_AMT DECIMAL(18,0) = NULL,
          @p_DEPOSITS_AMT DECIMAL(18,0) = NULL,
          @p_PAYMENT_SHEDULE	NVARCHAR(500) = NULL,
          @p_PAYMENT_DT VARCHAR(20) = NULL,
          @p_EXTEND_DT VARCHAR(20) = NULL,
          @p_IS_SEND_APPR VARCHAR(15) = 'Y',
          @p_SEND_APPR_DT VARCHAR(20) = GETDATE(),
          @p_SIGN_USER VARCHAR(15) = NULL,
          @p_SIGN_DT VARCHAR(15) = NULL

  DECLARE @p_MA_DV_TAO VARCHAR(15),
          @p_MA_TRU_SO VARCHAR(15),
          @p_TEN_TRU_SO NVARCHAR(1000),
          @p_SO_HOP_DONG NVARCHAR(150)

  DECLARE BudContractCustMasterImportCursor CURSOR FOR
    SELECT dkti.MA_DV_TAO, dkti.SO_HOP_DONG, dkti.TEN_TRU_SO, dkti.NGAY_GIA_HAN_HOP_DONG, dkti.GHI_CHU,
            dkti.VAT, dkti.SO_TIEN_DAT_COC, dkti.NGAY_DINH_KY_THANH_TOAN, dkti.LICH_THANH_TOAN
    FROM DT_KHACH_THUE_IMPORT dkti    

  OPEN BudContractCustMasterImportCursor

  DECLARE @p_ROW_COUNT INT = 0

  FETCH NEXT FROM BudContractCustMasterImportCursor INTO
    @p_MA_DV_TAO, @p_SO_HOP_DONG, @p_TEN_TRU_SO, @p_EXTEND_DT, @p_NOTES, 
    @p_VAT, @p_DEPOSITS_AMT, @p_PAYMENT_DT, @p_PAYMENT_SHEDULE

  WHILE @@fetch_status = 0
  BEGIN

    SET @p_MA_DV_TAO = TRIM(@p_MA_DV_TAO)
    SET @p_SO_HOP_DONG = TRIM(@p_SO_HOP_DONG)
    SET @p_TEN_TRU_SO = TRIM(@p_TEN_TRU_SO)

    SET @p_ROW_COUNT = @p_ROW_COUNT + 1

    SET @p_BRANCH_ID = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @p_MA_DV_TAO)
    IF(@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
    BEGIN
      SELECT '-1' Result, 
        N'Sheet Hợp đồng khách thuê, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Mã đơn vị tạo không hợp lệ.' ErrorDesc
      GOTO VALIDATE_ABORT
    END
    SET @p_BUILDING_ID = (SELECT bm.BUILDING_ID FROM BUD_MASTER bm WHERE bm.BUILDING_NAME = @p_TEN_TRU_SO)
      IF(@p_BUILDING_ID IS NULL OR @p_BUILDING_ID = '')
      BEGIN
        SELECT '-1' Result, 
        N'Sheet Hợp đồng khách thuê, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Tên trụ sở không hợp lệ.' ErrorDesc
        GOTO VALIDATE_ABORT
      END
    
    SET @p_TR_CONTRACT_ID = (SELECT TOP 1 tc.CONTRACT_ID FROM TR_CONTRACT tc WHERE (tc.CONTRACT_CODE = @p_SO_HOP_DONG OR tc.CONTRACT_ID = @p_SO_HOP_DONG) AND tc.AUTH_STATUS = 'A' AND tc.RECORD_STATUS = '1')
    IF ( @p_TR_CONTRACT_ID = '' OR @p_TR_CONTRACT_ID IS NULL)
		BEGIN
			SELECT '-1' Result, 
      N'Sheet Hợp đồng khách thuê, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Số hợp đồng không hợp lệ' ErrorDesc
			GOTO VALIDATE_ABORT
		END
    IF(@p_TR_CONTRACT_ID IS NOT NULL AND @p_TR_CONTRACT_ID <> '' 
      AND EXISTS(SELECT bccm.CONTRACT_ID FROM BUD_CONTRACT_CUST_MASTER bccm WHERE bccm.TR_CONTRACT_ID = @p_TR_CONTRACT_ID)
    )
    BEGIN
      SELECT '-1' Result, 
      N'Sheet Hợp đồng khách thuê, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ 
      N'. Số hợp đồng: ' +@p_SO_HOP_DONG+ N' đã được sử dụng để khai báo hợp đồng khách thuê. Vui lòng chọn hợp đồng khác.' ErrorDesc
			GOTO VALIDATE_ABORT
    END
    ELSE
    BEGIN
      SET @p_CONTRACT_NO = (SELECT TOP 1 tc.CONTRACT_CODE FROM TR_CONTRACT tc WHERE tc.CONTRACT_ID = @p_TR_CONTRACT_ID)
      SET @p_CUST_ID =(SELECT TOP 1 tc.SUP_ID FROM TR_CONTRACT tc WHERE tc.CONTRACT_ID = @p_TR_CONTRACT_ID)
      SET @p_INPUT_DT = (SELECT TOP 1 tc.SIGN_DT FROM TR_CONTRACT tc WHERE tc.CONTRACT_ID = @p_TR_CONTRACT_ID)
      SET @p_AMEND_DT = (SELECT TOP 1 tc.SIGN_DT FROM TR_CONTRACT tc WHERE tc.CONTRACT_ID = @p_TR_CONTRACT_ID)
      SET @p_FROM_DT = (SELECT TOP 1 tc.START_DT FROM TR_CONTRACT tc WHERE tc.CONTRACT_ID = @p_TR_CONTRACT_ID)
      SET @p_TO_DATE = (SELECT TOP 1 tc.END_DT FROM TR_CONTRACT tc WHERE tc.CONTRACT_ID = @p_TR_CONTRACT_ID)
      
      IF(@p_FROM_DT IS NULL)
      BEGIN
        SELECT '-1' Result, 
        N'Sheet Hợp đồng khách thuê, dòng: ' +CAST(@p_ROW_COUNT AS NVARCHAR(10))+ N'. Số hợp đồng này không có ngày hiệu lực. Vui lòng chọn số hợp đồng có ngày hiệu lực.' ErrorDesc
  			GOTO VALIDATE_ABORT
      END

      IF(@p_TO_DATE IS NOT NULL)
      BEGIN
        SET @p_MONTHS = (SELECT dbo.FN_DATE_DIFF_BETWEEN(@p_FROM_DT, @p_TO_DATE, 'MONTH')) 
      END
      ELSE
      BEGIN
        SET @p_MONTHS = 1
      END
      IF((@p_TO_DATE IS NOT NULL AND @p_TO_DATE >= GETDATE()) OR @p_TO_DATE IS NULL)
      BEGIN
        SET @p_STATUS = 'N'
      END
      ELSE
      BEGIN
        SET @p_STATUS = 'C'
      END
    END

     -----------------TẠO ID HỆ THỐNG--------------------
    ----------------INSERT MASTER-----------------------
		EXEC SYS_CodeMasters_Gen 'BUD_CONTRACT_MASTER', @p_CONTRACT_ID out
		IF @p_CONTRACT_ID='' OR @p_CONTRACT_ID IS NULL GOTO ABORT
    INSERT INTO BUD_CONTRACT_CUST_MASTER([CONTRACT_ID],[BUILDING_ID],[CONTRACT_NO],[VERSION_NO],
					[TR_CONTRACT_ID],[CUST_ID],[INPUT_DT],[AMEND_DT],[FROM_DT],[TO_DATE],[CLOSE_DT],[MONTHS],
					[RENT_PRICE],[NOTES],[BRANCH_ID],[RECORD_STATUS],[AUTH_STATUS],[STATUS],[MAKER_ID],
					[CREATE_DT],[CHECKER_ID],[APPROVE_DT],[XML_TEMP],[VAT],[VAT_AMT],[DEPOSITS_AMT],
					[PAYMENT_SHEDULE], [PAYMENT_DT],[EXTEND_DT],[IS_SEND_APPR],[SEND_APPR_DT],[SIGN_USER],[SIGN_DT])
    VALUES(@p_CONTRACT_ID, @p_BUILDING_ID, @p_CONTRACT_NO, @p_VERSION_NO, @p_TR_CONTRACT_ID, @p_CUST_ID, @p_INPUT_DT, @p_AMEND_DT,
            @p_FROM_DT, @p_TO_DATE, @p_CLOSE_DT, @p_MONTHS, @p_RENT_PRICE, @p_NOTES, @p_BRANCH_ID, @p_RECORD_STATUS, @p_AUTH_STATUS, 
            @p_STATUS, @p_MAKER_ID, @p_CREATE_DT, @p_CHECKER_ID, @p_APPROVE_DT, NULL, @p_VAT,@p_VAT_AMT, @p_DEPOSITS_AMT, @p_PAYMENT_SHEDULE,
            @p_PAYMENT_DT, @p_EXTEND_DT, @p_IS_SEND_APPR, @p_SEND_APPR_DT, @p_SIGN_USER, @p_SIGN_DT)

    IF @@error <> 0 GOTO ABORT

    ------------------INSERT DETAIL-------------------------
    DECLARE @d_CONTRACT_DTID   VARCHAR(15),	
    				@d_BUILD_ID VARCHAR(15),
    				@d_VERSION_NO INT,
    				@d_BUILDING_AREA_ID VARCHAR(15),
    				@d_RENT_AREA DECIMAL(18,2),
    				@d_RENT_AREA_APP DECIMAL(18,2),
    				@d_AREA_REMAIN DECIMAL(18,2),
    				@d_RENT_PRICE DECIMAL,
    				@d_NOTES NVARCHAR(MAX),
    				@d_DIEN_TICH_TANG DECIMAL(18,2),
    				@d_DIEN_TICH_SD_NOI_BO DECIMAL(18,2),
    				@d_DIEN_TICH_DA_CHO_THUE DECIMAL(18,2),
    				@d_DIEN_TICH_CON_LAI DECIMAL(18,2),
    				@d_DIEN_TICH_CAN_CHOTHUE DECIMAL(18,2),
    				@d_PRICE DECIMAL,
    				@d_TOTAL_AMT DECIMAL,
    				@d_DIEN_TICH_CON_LAI_FINAL DECIMAL(18,2)		

    DECLARE @p_ROW_COUNT_DETAIL INT = 0

    DECLARE @d_TANG INT
            

    DECLARE BudContractCustMasterDTImportCursor CURSOR FOR
      SELECT dktdiH.TANG, dktdiH.DT_KHACH_HANG_THUE, dktdiH.DON_GIA, dktdiH.GHI_CHU
      FROM DT_KHACH_THUE_DT_IMPORT dktdiH
      WHERE (dktdiH.TEN_TRU_SO = @p_TEN_TRU_SO)

    OPEN BudContractCustMasterDTImportCursor

    FETCH NEXT FROM BudContractCustMasterDTImportCursor INTO
      @d_TANG, @d_DIEN_TICH_CAN_CHOTHUE, @d_PRICE, @d_NOTES

    WHILE @@fetch_status = 0
    BEGIN
      SET @p_ROW_COUNT_DETAIL = @p_ROW_COUNT_DETAIL + 1

      SET @d_BUILDING_AREA_ID = (SELECT TOP 1 bad.BUILDING_AREA_ID FROM BUD_AREA_DT bad WHERE bad.BUILD_ID = @p_BUILDING_ID AND bad.FLOOR_NO = @d_TANG)
      IF(@d_BUILDING_AREA_ID IS NULL OR @d_BUILDING_AREA_ID = '')
      BEGIN
        SELECT '-1' Result, 
        N'Sheet thông tin chi tiết hợp đồng, dòng: ' +CAST(@p_ROW_COUNT_DETAIL AS NVARCHAR(10))+ 
        N', tên trụ sở: ' + @p_TEN_TRU_SO + N', tầng ' + CAST(@d_TANG AS NVARCHAR(10)) + 
        N': Tầng không hợp lệ.' ErrorDesc
        GOTO VALIDATE_ABORT_DT
      END

      
      DECLARE @d_FLOOR_AREA DECIMAL(18,2),
              @d_INTERNAL_AREA DECIMAL(18,2),
              @d_UTILIZED_AREA DECIMAL(18,2)
              
      SELECT @d_FLOOR_AREA = bad.FLOOR_AREA, @d_INTERNAL_AREA = bad.INTERNAL_AREA, @d_UTILIZED_AREA = bad.UTILZED_AREA
      FROM BUD_AREA_DT bad
      WHERE bad.BUILD_ID = @p_BUILDING_ID AND bad.BUILDING_AREA_ID = @d_BUILDING_AREA_ID
      
      DECLARE @d_REMAIN_AREA DECIMAL(18,2) = @d_FLOOR_AREA - (@d_INTERNAL_AREA + @d_UTILIZED_AREA)
      IF(@d_REMAIN_AREA < @d_DIEN_TICH_CAN_CHOTHUE)
      BEGIN
        SELECT '-1' Result, 
        N'Sheet thông tin chi tiết hợp đồng, dòng: ' +CAST(@p_ROW_COUNT_DETAIL AS NVARCHAR(10))+ 
        N', tên trụ sở: ' + @p_TEN_TRU_SO + N', tầng ' + CAST(@d_TANG AS NVARCHAR(10)) + 
        N': DT cho khách hàng thuê không được vượt quá diện tích còn lại có thể sử dụng: ' +@d_REMAIN_AREA  ErrorDesc
        GOTO VALIDATE_ABORT_DT
      END
      
      SET @d_VERSION_NO = @p_VERSION_NO
      SET @d_DIEN_TICH_TANG = @d_FLOOR_AREA
      SET @d_DIEN_TICH_SD_NOI_BO = @d_INTERNAL_AREA
      SET @d_DIEN_TICH_DA_CHO_THUE = @d_UTILIZED_AREA + @d_DIEN_TICH_CAN_CHOTHUE
      SET @d_DIEN_TICH_CON_LAI = @d_DIEN_TICH_TANG - (@d_DIEN_TICH_SD_NOI_BO + @d_DIEN_TICH_DA_CHO_THUE)
      SET @d_DIEN_TICH_CON_LAI_FINAL = @d_DIEN_TICH_CON_LAI
      SET @d_TOTAL_AMT =  @d_DIEN_TICH_CAN_CHOTHUE * @d_PRICE

      --------------------TẠO ID DETAIL-------------------------
      EXEC SYS_CodeMasters_Gen 'BUD_CONTRACT_DT', @d_CONTRACT_DTID out
			IF @d_CONTRACT_DTID='' OR @d_CONTRACT_DTID IS NULL GOTO ABORT
						INSERT INTO BUD_CONTRACT_CUST_DT([CONTRACT_DTID],[BUILD_ID],[CONTRACT_ID],[VERSION_NO],[BUILDING_AREA_ID],[RENT_AREA],[RENT_AREA_APP],
							[AREA_REMAIN],[RENT_PRICE],[NOTES], [DIEN_TICH_TANG], [DIEN_TICH_SD_NOI_BO],[DIEN_TICH_DA_CHO_THUE],[DIEN_TICH_CON_LAI],
							[DIEN_TICH_CAN_CHOTHUE],[PRICE],[TOTAL_AMT],[DIEN_TICH_CON_LAI_FINAL])
      VALUES(@d_CONTRACT_DTID, @p_BUILDING_ID, @p_CONTRACT_ID, @d_VERSION_NO, @d_BUILDING_AREA_ID, 0, @d_DIEN_TICH_CAN_CHOTHUE, @d_DIEN_TICH_TANG,
            0, @d_NOTES, @d_DIEN_TICH_TANG, @d_DIEN_TICH_SD_NOI_BO, @d_DIEN_TICH_DA_CHO_THUE, @d_DIEN_TICH_CON_LAI, @d_DIEN_TICH_CAN_CHOTHUE,
            @d_PRICE, @d_TOTAL_AMT, @d_DIEN_TICH_CON_LAI_FINAL)
      IF @@error <> 0 GOTO ABORT_DT

      ---------------------UPDATE LẠI DIỆN TÍCH TẦNG-------------------
      UPDATE BUD_AREA_DT
    		SET UTILZED_AREA = UTILZED_AREA + @d_DIEN_TICH_CAN_CHOTHUE
    	WHERE BUILDING_AREA_ID =@d_BUILDING_AREA_ID AND BUILD_ID = @p_BUILDING_ID

      ---------------------INSERT VÀO BUD_CONTRACT_LOG------------------
  
      DECLARE @p_CUSTOMER_ID VARCHAR(15) = 
				(SELECT CUST_ID FROM BUD_CONTRACT_CUST_MASTER WHERE CONTRACT_ID = @p_CONTRACT_ID)

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

      DECLARE @p_IS_FIRST_TIME VARCHAR(1)
      IF(NOT EXISTS 
    			(SELECT * FROM BUD_CONTRACT_LOG 
    				WHERE CONTRACT_ID = @p_CONTRACT_ID 
    				AND BUILDING_AREA_ID = @d_BUILDING_AREA_ID
    				AND CUSTOMER_ID = @p_CUSTOMER_ID
    				AND IS_FIRST_TIME = 'Y')
      )
  		BEGIN
  			SET @p_IS_FIRST_TIME = 'Y'
  		END
  		ELSE
  		BEGIN
  			SET @p_IS_FIRST_TIME = 'N'
  		END

      
      DECLARE @p_VAT_INIT NUMERIC(18,0)
		    SET @p_VAT_INIT = (SELECT VAT FROM BUD_CONTRACT_CUST_MASTER WHERE CONTRACT_ID = @p_CONTRACT_ID)

      INSERT INTO BUD_CONTRACT_LOG 
  			([BUD_CONTRACT_LOG_ID], [CONTRACT_ID], [CONTRACT_DT_ID], [CUSTOMER_ID], [BUILDING_AREA_ID], [TYPE],
  			[INTERNAL_AREA], [RENT_AREA], [REMAIN_AREA], [UNIT_PRICE], [VAT], [TOTAL_AMT], 
  			[IS_FIRST_TIME], [BRANCH_RENT_ID], [DEP_RENT_ID], [MAKER_ID], [CREATE_DT], [CHECKER_ID],
  			[APPROVE_DT], [AUTH_STATUS])
      VALUES(@l_UTIL_ID, @p_CONTRACT_ID, @d_CONTRACT_DTID, @p_CUSTOMER_ID, @d_BUILDING_AREA_ID, 'CUSTOMER',
             NULL, @d_DIEN_TICH_CAN_CHOTHUE, @d_DIEN_TICH_CON_LAI_FINAL, @d_PRICE, @p_VAT_INIT, @d_TOTAL_AMT,
             @p_IS_FIRST_TIME, NULL, NULL, @p_MAKER_ID, GETDATE(), @p_CHECKER_ID, GETDATE(), @p_AUTH_STATUS)

      FETCH NEXT FROM BudContractCustMasterDTImportCursor INTO
        @d_TANG, @d_DIEN_TICH_CAN_CHOTHUE, @d_PRICE, @d_NOTES
    END

    CLOSE BudContractCustMasterDTImportCursor
    DEALLOCATE BudContractCustMasterDTImportCursor

    ----------------UPDATE TỔNG SỐ TIỀN THUÊ MASTER---------------------
    DECLARE @p_SUM_TOTAL_AMT_DT DECIMAL = (
      SELECT ISNULL(SUM(bccd.TOTAL_AMT),0)
      FROM BUD_CONTRACT_CUST_DT bccd WHERE bccd.CONTRACT_ID = @p_CONTRACT_ID
    )
    UPDATE BUD_CONTRACT_CUST_MASTER 
      SET RENT_PRICE = @p_SUM_TOTAL_AMT_DT * @p_MONTHS,
          VAT_AMT = (RENT_PRICE * (ISNULL(VAT, 0) / 100)) + RENT_PRICE 
    WHERE CONTRACT_ID = @p_CONTRACT_ID

    FETCH NEXT FROM BudContractCustMasterImportCursor INTO
      @p_MA_DV_TAO, @p_SO_HOP_DONG, @p_TEN_TRU_SO, @p_EXTEND_DT, @p_NOTES, 
      @p_VAT, @p_DEPOSITS_AMT, @p_PAYMENT_DT, @p_PAYMENT_SHEDULE
  END

  CLOSE BudContractCustMasterImportCursor
  DEALLOCATE BudContractCustMasterImportCursor

COMMIT TRANSACTION
SELECT '0' as Result, N'Import dữ liệu Quản lý hợp đồng khách thuê thành công' ErrorDesc
RETURN '0'


ABORT:
BEGIN
  ROLLBACK TRANSACTION
  CLOSE BudContractCustMasterImportCursor
  DEALLOCATE BudContractCustMasterImportCursor
  SELECT '-1' as Result, '' ErrorDesc
	RETURN '-1'
END
VALIDATE_ABORT:
BEGIN
    ROLLBACK TRANSACTION
    CLOSE BudContractCustMasterImportCursor
    DEALLOCATE BudContractCustMasterImportCursor
    RETURN '-1'
END
ABORT_DT:
BEGIN
    ROLLBACK TRANSACTION
    CLOSE BudContractCustMasterImportCursor
    DEALLOCATE BudContractCustMasterImportCursor
    CLOSE BudContractCustMasterDTImportCursor
    DEALLOCATE BudContractCustMasterDTImportCursor
		SELECT '-1' as Result, '' ErrorDesc
		RETURN '-1'
END
VALIDATE_ABORT_DT:
BEGIN
    ROLLBACK TRANSACTION
    CLOSE BudContractCustMasterImportCursor
    DEALLOCATE BudContractCustMasterImportCursor
    CLOSE BudContractCustMasterDTImportCursor
    DEALLOCATE BudContractCustMasterDTImportCursor
    RETURN '-1'
END

GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[rpt_TR_REQUEST_CAR]'
GO

CREATE PROC [dbo].[rpt_TR_REQUEST_CAR]
@p_REQ_ID		VARCHAR(15) = NULL
AS
BEGIN
	SELECT A.REQ_CODE,B.TLFullName AS EMP_NAME,B.BRANCH_NAME,A.QUANTITY_TRIP,A.MAKER_PHONE,A.ORIGN,a.DESTINATION,CONVERT(VARCHAR,DAY(A.FROM_DATE),2) +'/'+CONVERT(VARCHAR,MONTH(A.FROM_DATE),2) +'/'+CONVERT(VARCHAR,YEAR(A.FROM_DATE),2) AS FROM_DATE,CONVERT(VARCHAR,DAY(A.TO_DATE),2) +'/'+CONVERT(VARCHAR,MONTH(A.TO_DATE),2) +'/'+CONVERT(VARCHAR,YEAR(A.TO_DATE),2) AS TO_DATE,N' Ngày '+CONVERT(VARCHAR,DAY(GETDATE()),2) +N' Tháng '+CONVERT(VARCHAR,MONTH(GETDATE()),2) +N' Năm '+CONVERT(VARCHAR,YEAR(GETDATE()),2) AS [DATE],A.NOTES
	FROM dbo.TR_REQUEST_CAR A
	LEFT JOIN dbo.TL_USER B ON B.TLNANME= A.MAKER_ID
	WHERE REQ_ID=@p_REQ_ID

	--Phần chữ kí
	DECLARE @S1 NVARCHAR(100),@S2 NVARCHAR(100),@S3 NVARCHAR(100),@S4 NVARCHAR(100),@S5 NVARCHAR(100)
	SET @S1=(SELECT B.TLFullName + CHAR(10) +CHAR(10) + N' Đã xác nhận vào lúc '+ CHAR(10) +CHAR(10) + ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM dbo.PL_REQUEST_PROCESS A LEFT JOIN dbo.TL_USER B ON (A.CHECKER_ID=B.TLNANME) WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='CV_APP')
	SET @S2=(SELECT B.TLFullName + CHAR(10) + CHAR(10) + N' Đã xác nhận vào lúc '+ CHAR(10) +CHAR(10)  + ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM dbo.PL_REQUEST_PROCESS A LEFT JOIN dbo.TL_USER B ON (A.CHECKER_ID=B.TLNANME) WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='APPNEW')
	SET @S3=(SELECT B.TLFullName + CHAR(10) + CHAR(10) + N' Đã xác nhận vào lúc '+ CHAR(10) +CHAR(10)  + ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM dbo.PL_REQUEST_PROCESS A LEFT JOIN dbo.TL_USER B ON (A.CHECKER_ID=B.TLNANME) WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='CV_XL')
	SET @S4=(SELECT B.TLFullName + CHAR(10) + CHAR(10) + N' Đã xác nhận vào lúc '+ CHAR(10) +CHAR(10)  + ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM dbo.PL_REQUEST_PROCESS A LEFT JOIN dbo.TL_USER B ON (A.CHECKER_ID=B.TLNANME) WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='TDV_C_APP')
	SET @S5=(SELECT B.TLFullName + CHAR(10) + CHAR(10) + N' Đã xác nhận vào lúc '+ CHAR(10) +CHAR(10)  + ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM dbo.PL_REQUEST_PROCESS A LEFT JOIN dbo.TL_USER B ON (A.CHECKER_ID=B.TLNANME) WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DONE')

	SELECT @S1 AS S1,@S2 AS S2,@S3 AS S3,@S4 AS S4,@S5 AS S5
END

GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[rpt_TR_REQUEST_JOB_FORM]'
GO

CREATE PROCEDURE [dbo].[rpt_TR_REQUEST_JOB_FORM]
@p_REQ_ID	varchar(50)	= NULL
AS
BEGIN 
	DECLARE @TYPE_WORK VARCHAR(15)
	SELECT @TYPE_WORK = TYPE_WORK FROM dbo.TR_REQUEST_JOB_FORM WHERE REQ_ID=@p_REQ_ID

	IF(@TYPE_WORK='FOREIGN')
		BEGIN
			SELECT 
			ROW_NUMBER() OVER (ORDER BY A.TRAIN_ID DESC) AS STT,A.FROM_TRAIN,A.TO_TRAIN,CONVERT(VARCHAR, A.DAY_TRAIN,103) AS DAY_TRAIN,A.TIME_TRAIN, IIF(A.IS_OLD=1,N'Bị hủy','') AS [STATUS]
			FROM dbo.TR_REQUEST_JOB_TRAIN A
			WHERE REQ_ID=@p_REQ_ID

		END
	ELSE
		BEGIN
			SELECT 
			ROW_NUMBER() OVER (ORDER BY A.TRAIN_ID DESC) AS STT,B.PRO_NAME AS FROM_TRAIN,C.PRO_NAME AS TO_TRAIN,CONVERT(VARCHAR, A.DAY_TRAIN,103) AS DAY_TRAIN,A.TIME_TRAIN, IIF(A.IS_OLD=1,N'Bị hủy','') AS [STATUS]
			FROM dbo.TR_REQUEST_JOB_TRAIN A
			LEFT JOIN CM_PROVINCE B ON A.FROM_TRAIN=B.PRO_ID
			LEFT JOIN CM_PROVINCE C ON A.TO_TRAIN=C.PRO_ID
			WHERE REQ_ID=@p_REQ_ID
		END
		SELECT ROW_NUMBER() OVER (ORDER BY FLY_ID DESC) AS STT,TIME_FLY,CONVERT(VARCHAR, DAY_FLY,103) AS DAY_FLY,JOURNEY,AIRLINE_FLY, IIF(IS_OLD=1,N'Bị hủy','') AS [STATUS] FROM dbo.TR_REQ_JOB_FLY WHERE REQ_ID = @p_REQ_ID

		---Chữ ký
		DECLARE @TDV NVARCHAR(100),@HC NVARCHAR(100)
		SET @TDV=(SELECT TOP(1)(B.TLFullName + CHAR(10) +CHAR(10) + N' Đã xác nhận vào lúc '+ CHAR(10) +CHAR(10) + ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'')) FROM dbo.PL_REQUEST_PROCESS A LEFT JOIN dbo.TL_USER B ON (A.CHECKER_ID=B.TLNANME) WHERE REQ_ID =@p_REQ_ID AND PROCESS_ID IN('APPNEW','TDV_APP') AND STATUS='P' ORDER BY A.ID DESC)
		SET @HC=(SELECT TOP(1)(B.TLFullName + CHAR(10) +CHAR(10) + N' Đã xác nhận vào lúc '+ CHAR(10) +CHAR(10) + ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'')) FROM dbo.PL_REQUEST_PROCESS A LEFT JOIN dbo.TL_USER B ON (A.CHECKER_ID=B.TLNANME) WHERE REQ_ID =@p_REQ_ID AND PROCESS_ID IN('HO_APP','HOAPP') AND STATUS='P' ORDER BY A.ID DESC)
	
		SELECT @TDV AS TDV,@HC AS HC
END	


GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[TR_BUDGET_BRANCH_TAKE_COST_Search]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[TR_REQUEST_JOB_FLY_Log]'
GO

CREATE PROC [dbo].[TR_REQUEST_JOB_FLY_Log]
@p_REQ_ID varchar(15) = NULL,
@p_IS_OLD bit = 0,
@p_TOP int = NULL
AS
BEGIN -- PAGING
IF(@p_TOP IS NULL OR @p_TOP=0)
BEGIN  
-- PAGING BEGIN
    SELECT A.*
-- SELECT END
    FROM TR_REQ_JOB_FLY A
    WHERE 1=1
		AND A.REQ_ID =@p_REQ_ID
		AND A.IS_OLD = @p_IS_OLD 
		ORDER BY  A.FLY_ID 
-- PAGING END
    END
    ELSE 
	BEGIN
-- PAGING BEGIN
    SELECT TOP(@p_TOP)  A.*
-- SELECT END
    FROM TR_REQ_JOB_FLY A
    WHERE 1=1
		AND A.REQ_ID =@p_REQ_ID
		AND A.IS_OLD = @p_IS_OLD
		ORDER BY A.FLY_ID 

-- PAGING END
    END
END -- PAGING
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[CON_REQUEST_DOC_SendApp]'
GO

CREATE   PROCEDURE [dbo].[CON_REQUEST_DOC_SendApp]
@CONST_ID VARCHAR(15),
@TLNAME VARCHAR(100)
AS
BEGIN TRANSACTION
		UPDATE CON_MASTER 
		SET [AUTH_STATUS] = 'U'
		WHERE CONSTRUCT_ID = @CONST_ID
		IF @@Error <> 0 GOTO ABORT

		UPDATE CON_REQUEST_DOC SET [AUTH_STATUS] = 'U'
		WHERE CONSTRUCT_ID = @CONST_ID
		IF @@Error <> 0 GOTO ABORT

		INSERT INTO dbo.PL_PROCESS
					(
					    REQ_ID,
					    PROCESS_ID,
					    CHECKER_ID,
					    APPROVE_DT,
					    PROCESS_DESC,
					    NOTES
					)
					VALUES
					(   @CONST_ID,        -- REQ_ID - varchar(15)
					    --@p_PROCESS_ID,        -- PROCESS_ID - varchar(10)
						'SEND',
					    @TLNAME,        -- CHECKER_ID - varchar(15)
					    GETDATE(), -- APPROVE_DT - datetime
					    N'Nhân viên tạo phiếu và gửi phê duyệt thành công' ,       -- PROCESS_DESC - nvarchar(1000)
					    N'Gửi phê duyệt'       -- NOTES - nvarchar(1000)
					 )
COMMIT TRANSACTION
SELECT '0' as Result, '' ErrorDesc
RETURN '0'

ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ErrorDesc
		RETURN '-1'
END