ALTER PROCEDURE [dbo].[rpt_RET_CN_PGD_RENT_Excel]
	@p_FROM_DT VARCHAR(20) = null,
	@p_TO_DT VARCHAR(20) = null,
	@p_BRANCH_ID VARCHAR(15) = NULL,
	@P_LEVEL VARCHAR(15) = 'ALL'
AS
BEGIN
	DECLARE @t_BRANCH_TABLE TABLE(BRANCH_ID VARCHAR(15))

	IF(@P_LEVEL = 'ALL')
	BEGIN
		INSERT INTO @t_BRANCH_TABLE SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)
	END
	IF(@P_LEVEL = 'UNIT')
	BEGIN
		INSERT INTO @t_BRANCH_TABLE SELECT @p_BRANCH_ID
	END

	DECLARE @FROM_DT DATETIME = CONVERT(DATETIME, @p_FROM_DT,103),
			@TO_DT DATETIME = CONVERT(DATETIME, @p_TO_DT,103)

	DECLARE @t_RET_PRICE_TABLE TABLE (RET_R_H_ID VARCHAR(15), PRICE_END_DT DATETIME)

	INSERT INTO @t_RET_PRICE_TABLE 
		SELECT RP.RET_R_H_ID,RP.PRICE_END_DT
		FROM RET_PRICE RP
		WHERE (RP.PRICE_DT >= @FROM_DT OR @FROM_DT IS NULL OR @FROM_DT = '')
		AND (RP.PRICE_END_DT <= @TO_DT OR @TO_DT IS NULL OR @TO_DT = '')


	SELECT
	ROW_NUMBER() OVER (ORDER BY A.RET_R_H_ID ASC) AS STT,
	[dbo].[FN_GET_CHINHANH](A.BRANCH_CREATE,'KV') AS KHU_VUC,
	[dbo].[FN_GET_CHINHANH](A.BRANCH_CREATE,'CN') AS CHI_NHANH, 
	[dbo].[FN_GET_CHINHANH](A.BRANCH_CREATE,'PGD') AS PGD,
	A.RET_ADDR AS ADDRESS,
	B.CONTRACT_CODE,
	B.CONTRACT_ID,
	A.START_DT,
	A.END_DT,
	-----GIÁ THUÊ(THÁNG) ĐÃ THUẾ KỲ ĐẦU TIÊN--------
	(
		SELECT TOP 1 ISNULL(RP.PRICE, 0)
		FROM RET_PRICE RP
		WHERE RP.RET_R_H_ID = A.RET_R_H_ID
	) 
	AS INIT_PRICE,
	0 AS INIT_VAT,
	-----GIÁ THUÊ(THÁNG) ĐÃ THUẾ HIỆN TẠI--------
	(
		SELECT TOP 1 ISNULL(RP.PRICE, 0)
		FROM RET_PRICE RP
		WHERE RP.RET_R_H_ID = A.RET_R_H_ID
		AND (RP.PRICE_DT >= @FROM_DT OR @FROM_DT IS NULL OR @FROM_DT = '')
		AND (RP.PRICE_END_DT <= @TO_DT OR @TO_DT IS NULL OR @TO_DT = '')
		ORDER BY RP.PRICE_DT DESC
	)
	AS CURRENT_PRICE,
	0 AS CURRENT_VAT,
	(
		SELECT TOP 1 RP.PAY_PHASE
		FROM RET_PRICE RP
		WHERE RP.RET_R_H_ID = A.RET_R_H_ID
		AND (RP.PRICE_DT >= @FROM_DT OR @FROM_DT IS NULL OR @FROM_DT = '')
		AND (RP.PRICE_END_DT <= @TO_DT OR @TO_DT IS NULL OR @TO_DT = '')
		ORDER BY RP.PRICE_DT DESC
	)
	AS PAY_PHASE,
	(
		SELECT TOP 1 RP.TOTAL_AMT_PAY_PHASE
		FROM RET_PRICE RP
		WHERE RP.RET_R_H_ID = A.RET_R_H_ID
		AND (RP.PRICE_DT >= @FROM_DT OR @FROM_DT IS NULL OR @FROM_DT = '')
		AND (RP.PRICE_END_DT <= @TO_DT OR @TO_DT IS NULL OR @TO_DT = '')
		ORDER BY RP.PRICE_DT DESC
	)
	AS PAY_AMOUNT,
	(
		SELECT TOP 1 RP.TOTAL_AMT_PAY_PHASE
		FROM RET_PRICE RP
		WHERE RP.RET_R_H_ID = A.RET_R_H_ID
		AND RP.PRICE_DT >= DATEADD(day, 1, 
			(
				SELECT TOP 1 PRICE_END_DT
				FROM @t_RET_PRICE_TABLE TP
				WHERE TP.RET_R_H_ID = A.RET_R_H_ID
				ORDER BY PRICE_END_DT DESC
			 )
		)
	)
	AS AMT_NEXT_PAYMENT_PHASE

	FROM REAL_ESTATE_R_H A
	LEFT JOIN TR_CONTRACT B ON A.TR_CONTRACT_ID = B.CONTRACT_ID

	WHERE A.AUTH_STATUS = 'A'
	AND A.RECORD_STATUS = '1'
	--AND (CONVERT(DATETIME,A.START_DT, 103) >= CONVERT(DATETIME, @p_FROM_DT, 103) OR @p_FROM_DT IS NULL OR @p_FROM_DT = '')
	--AND (CONVERT(DATETIME, A.END_DT, 103) <= CONVERT(DATETIME, @p_TO_DT, 103) OR @p_TO_DT IS NULL OR @p_TO_DT = '')
	--AND A.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @t_BRANCH_TABLE)

END