USE [AMS_GD3_LOCAL]
GO
/****** Object:  StoredProcedure [dbo].[rpt_BDS_BC01_Excel]    Script Date: 27/09/2022 2:31:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[rpt_BDS_BC01_Excel]
	@p_Fromdate varchar(20) = null,
	@p_Todate VARCHAR(20) = NULL,
	@p_BRANCH_LOGIN VARCHAR(15) = NULL,
	@P_BRANCH_ID VARCHAR(15) = NULL,
	@P_LEVEL VARCHAR(15) = 'ALL'
AS

BEGIN
declare @tmp table(BRANCH_ID varchar(15))

IF(@P_LEVEL = 'ALL')
BEGIN
	insert into @tmp  SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)
END

IF(@P_LEVEL = 'UNIT')
BEGIN
	insert into @tmp SELECT @P_BRANCH_ID
END
--declare @tmp_login table(BRANCH_ID varchar(15))
--insert into @tmp_login  SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)

	-- khangth, 15/05/2020, close code --------------------------------------------------------

	--SELECT  --ROW_NUMBER() OVER (ORDER BY D.BRANCH_ID ASC) AS STT,
	--[dbo].[FN_GET_CHINHANH](D.BRANCH_ID,'KV') AS KHU_VUC,
	--[dbo].[FN_GET_CHINHANH](D.BRANCH_ID,'CN') AS CHI_NHANH, 
	--[dbo].[FN_GET_CHINHANH](D.BRANCH_ID,'PGD') AS PGD,
	--CASE WHEN D.BRANCH_TYPE = 'CN' AND C.OWNER_TYPE = 'M' THEN 1 ELSE 0 END AS MUA_CHI_NHANH,
	----'xbadasd' MUA_CN,
	--CASE WHEN D.BRANCH_TYPE = 'CN' AND C.OWNER_TYPE = 'TM' THEN 1 ELSE 0 END AS THUE_MUA_CN,
	--CASE WHEN D.BRANCH_TYPE = 'CN' AND C.OWNER_TYPE IS NULL THEN 1 ELSE 0  END AS THUE_CHI_NHANH,
	--CASE WHEN D.BRANCH_TYPE = 'PGD' AND C.OWNER_TYPE = 'M' THEN 1 ELSE 0 END AS MUA_PHONG_GD,
	--CASE WHEN D.BRANCH_TYPE = 'PGD' AND C.OWNER_TYPE = 'TM' THEN 1 ELSE 0 END AS THUE_MUA_PGD,
	--CASE WHEN D.BRANCH_TYPE = 'PGD' AND C.OWNER_TYPE IS NULL THEN 1 ELSE 0 END AS THUE_PHONG_GD
	--FROM CM_DIVISION B
	--LEFT JOIN ASS_MASTER A ON B.DIV_ID = A.DIVISION_ID
	--LEFT JOIN RET_MASTER C ON A.ASSET_ID = C.ASSET_ID 
	--LEFT JOIN CM_BRANCH D ON B.BRANCH_ID = D.BRANCH_ID
	------WHERE C.RECORD_STATUS = '1' AND C.AUTH_STATUS = 'A'
	------and (D.BRANCH_TYPE = 'PGD' OR D.BRANCH_TYPE = 'CN')

	------AND (A.USE_DATE >= CONVERT(DATETIME,@p_Fromdate,103) OR @p_Fromdate IS NULL OR @p_Fromdate = '')
	------AND (A.USE_DATE <= CONVERT(DATETIME,@p_Todate,103) OR @p_Todate IS NULL OR @p_Todate = '')

	------AND ((@P_LEVEL = 'ALL' AND B.BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp))
	------OR (@P_LEVEL = 'UNIT' AND B.BRANCH_ID = @p_BRANCH_ID) OR @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)
	------AND ((@P_LEVEL = 'ALL' AND B.BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp_login))
	------OR (@P_LEVEL = 'UNIT' AND B.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 <> '')

	--//-----------------------------------------------------------------------

	-- khangth, 15/05/2020, lay du lieu theo chuc nang moi phat trien ------------

	-- HUNGDV SUA MOI

	DECLARE @FROM_DT DATETIME = CONVERT(DATE,@p_Fromdate,103)
	DECLARE @TO_DT DATETIME = CONVERT(DATE, @p_Todate,103)
		;WITH RET AS (
			SELECT 
			CASE
				WHEN RET.BRANCH_USE_ID IS NOT NULL OR RET.BRANCH_USE_ID <> '' THEN RET.BRANCH_USE_ID
				ELSE A.BRANCH_ID
			END
			AS BRANCH_ID, -- đơn vị sử dụng
			[dbo].[FN_GET_CHINHANH](RET.BRANCH_USE_ID,'KV') AS KHU_VUC,
			[dbo].[FN_GET_CHINHANH](RET.BRANCH_USE_ID,'CN') AS CHI_NHANH, 
			[dbo].[FN_GET_CHINHANH](RET.BRANCH_USE_ID,'PGD') AS PGD
			,ADDR AS RET_ADDR,
			--CASE
			--	WHEN RET.OWNER_TYPE = 'TCTH' THEN 'X'
			--	ELSE ''
			--END
			--AS THUE,
			--CASE
			--	WHEN RET.OWNER_TYPE = 'M' THEN 'X'
			--	ELSE ''
			--END
			--AS MUA
			'' AS THUE, 'X' AS MUA
			FROM RET_MASTER  RET
			INNER JOIN ASS_MASTER A ON RET.ASSET_ID = A.ASSET_ID
			WHERE RET.RECORD_STATUS = '1' 
			AND RET.AUTH_STATUS = 'A'
			AND (@p_Fromdate IS NULL OR @p_Fromdate = '' OR RET.BUY_DT >= @FROM_DT)
			AND (@p_Todate IS NULL OR @p_Todate = '' OR RET.BUY_DT <= @TO_DT)
			--AND (RET.BRANCH_USE_ID IS NOT NULL AND RET.BRANCH_USE_ID <> '')
		),
		RET_RH AS (
			SELECT BRANCH_USE AS BRANCH_ID,
			[dbo].[FN_GET_CHINHANH](BRANCH_USE,'KV') AS KHU_VUC,
			[dbo].[FN_GET_CHINHANH](BRANCH_USE,'CN') AS CHI_NHANH, 
			[dbo].[FN_GET_CHINHANH](BRANCH_USE,'PGD') AS PGD,
			RET_ADDR, 'X' AS THUE, '' AS MUA
			FROM REAL_ESTATE_R_H
			WHERE RECORD_STATUS = '1' 
			AND AUTH_STATUS = 'A'
			AND (@p_Fromdate IS NULL OR @p_Fromdate = '' OR START_DT >= @FROM_DT)
			AND (@p_Todate IS NULL OR @p_Todate = '' OR START_DT <= @TO_DT)
			AND (BRANCH_USE IS NOT NULL  AND BRANCH_USE <> '')
			--AND (@p_Todate IS NULL OR @p_Todate = '' OR END_DT <= @TO_DT)
		),
		RET_LC AS (
			SELECT BRANCH_USE AS BRANCH_ID, 
			[dbo].[FN_GET_CHINHANH](BRANCH_USE,'KV') AS KHU_VUC,
			[dbo].[FN_GET_CHINHANH](BRANCH_USE,'CN') AS CHI_NHANH, 
			[dbo].[FN_GET_CHINHANH](BRANCH_USE,'PGD') AS PGD,
			RET_ADDR, '' AS THUE, 'X' AS MUA
			FROM REAL_ESTATE_L_C
			WHERE RECORD_STATUS = '1' 
			AND AUTH_STATUS = 'A'
			AND (@p_Fromdate IS NULL OR @p_Fromdate = '' OR BUY_DT >= @FROM_DT)
			AND (@p_Todate IS NULL OR @p_Todate = '' OR BUY_DT <= @TO_DT)
			AND (BRANCH_USE IS NOT NULL AND BRANCH_USE <> '')
		)
		SELECT BRN.*
		FROM
		(
			(SELECT * FROM RET)
			UNION ALL (SELECT * FROM RET_RH)
			UNION ALL (SELECT * FROM RET_LC)
		) BRN
		INNER JOIN @tmp T ON BRN.BRANCH_ID = T.BRANCH_ID
		WHERE 1=1
		ORDER BY BRN.KHU_VUC, BRN.CHI_NHANH, BRN.PGD
	--//-------------------------------------------------------------
END