ALTER   PROCEDURE [dbo].[rpt_CON_REQUEST_DOC_BC01]
(
	@p_LEVEL varchar(15) = NULL,
	@p_BRANCH_ID varchar(15) = NULL,
	@p_BRANCH_LOGIN varchar(15) = NULL,
	@p_REQ_CODE nvarchar(100) = NULL, -- SO HIEU TO TRINH
	@p_TERM_BID nvarchar(100) = NULL, -- HANG MUC
	@p_YEAR_BUDGET int = NULL, -- NAM NGAN SACH
	@p_REQ_BUDGET  varchar(20) = NULL -- LOAI NGAN SACH
)
AS
BEGIN
	IF(@p_BRANCH_ID IS NULL)
		SET @p_BRANCH_ID = @p_BRANCH_LOGIN

	--IF(@p_YEAR_BUDGET IS NULL)
	--	SET @p_YEAR_BUDGET = YEAR(GETDATE())

	--DECLARE @F_DAY_THIS_YEAR DATETIME = CONVERT(DATE,CONVERT(varchar(20), @p_YEAR_BUDGET - 1) + '-12-01')
	--DECLARE @L_DAY_THIS_YEAR DATETIME = CONVERT(DATE,CONVERT(varchar(20), @p_YEAR_BUDGET) + '-11-30')

	--DECLARE @F_DAY_PRE_YEAR DATETIME = CONVERT(DATE,CONVERT(varchar(20), @p_YEAR_BUDGET - 2) + '-12-01')
	--DECLARE @L_DAY_PRE_YEAR DATETIME = CONVERT(DATE,CONVERT(varchar(20), @p_YEAR_BUDGET - 1) + '-11-30')
	
	declare @tmp table(BRANCH_ID varchar(15))
	insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)

	--SELECT ROW_NUMBER() OVER(ORDER BY(SELECT 1)) AS STT,
	--dbo.FN_GET_CHINHANH(BR.BRANCH_ID,'KV') AS KV,
	--CASE WHEN C.BRANCH_NAME_ETX IS NULL OR C.BRANCH_NAME_ETX = '' THEN BR.BRANCH_NAME ELSE C.BRANCH_NAME_ETX END CONSTRUCT_NAME, -- CÔNG TRÌNH
	--CON_HQ_T.CONTENT HQ_TYPE,
	--P.REQ_CODE,
	--P.APPROVE_DT,
	--P.TOTAL_AMT,
	--PAID_PRE_YEAR,
	--PAID_THIS_YEAR,
	--'' AS NOTES
	--FROM CON_MASTER C
	--LEFT JOIN CM_BRANCH BR ON C.BRANCH_ID = BR.BRANCH_ID
	--INNER JOIN CON_REQUEST_DOC_PARENT_VIEW P ON C.REQUEST_ID = P.REQ_ID
	--LEFT JOIN (
	--	SELECT REQUEST_ID,
	--	SUM(CASE WHEN PAY_DT >= @F_DAY_THIS_YEAR AND PAY_DT <= @L_DAY_THIS_YEAR THEN AMOUNT ELSE 0 END) AS PAID_THIS_YEAR,
	--	SUM(CASE WHEN PAY_DT >= @F_DAY_PRE_YEAR AND PAY_DT <= @L_DAY_PRE_YEAR THEN AMOUNT ELSE 0 END) AS PAID_PRE_YEAR
	--	FROM TR_CONTRACT_PAYMENT PAY
	--	WHERE REQUEST_ID IS NOT NULL
	--	GROUP BY REQUEST_ID
	--) PAY ON C.REQUEST_ID = PAY.REQUEST_ID
	--LEFT JOIN CM_ALLCODE CON_HQ_T ON CON_HQ_T.CDNAME='REQ_HQ_TYPE' AND CON_HQ_T.CDType='CON' AND C.HQ_TYPE = CON_HQ_T.CDVAL
	--WHERE ((@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_REQ_CODE IS NULL OR @p_REQ_CODE = '' OR P.REQ_CODE LIKE '%' + @p_REQ_CODE + '%')
	--AND (@p_TERM_BID IS NULL OR @p_TERM_BID = '' OR EXISTS (SELECT * FROM BID_MASTER B WHERE B.REQUEST_ID = P.REQ_ID AND B.TERM_BID LIKE '%' + @p_TERM_BID + '%'))
	--AND (@p_YEAR_BUDGET IS NULL OR @p_YEAR_BUDGET = '' OR P.YEAR_BUDGET = @p_YEAR_BUDGET)
	--AND (@p_REQ_BUDGET IS NULL OR @p_REQ_BUDGET = '' OR P.BUDGET = @p_REQ_BUDGET)
	--AND (REQ_DT >= @F_DAY_THIS_YEAR AND REQ_DT <= @L_DAY_THIS_YEAR)
	--AND C.RECORD_STATUS = '1' AND C.AUTH_STATUS = 'A'--THIEUVQ 18/1/2021

	--SELECT	N'THEO DÕI NGÂN SÁCH XDCB NĂM ' + CONVERT(VARCHAR(100),@p_YEAR_BUDGET) AS TITLE, 
	--		N'Năm ' + CONVERT(VARCHAR(100),@p_YEAR_BUDGET - 1) + N' dở dang' AS PAID_PRE_YEAR,
	--		N'Năm ' + CONVERT(VARCHAR(100),@p_YEAR_BUDGET) AS PAID_THIS_YEAR,
	--		N'Khoản thanh toán của công trình thuộc ngân sách năm ' + CONVERT(VARCHAR(100),@p_YEAR_BUDGET - 1) + N' có ngày thanh toán từ 1/12/' + CONVERT(VARCHAR(100),@p_YEAR_BUDGET - 1) AS NOTE_PRE_YEAR,
	--		N'Khoản thanh toán của công trình thuộc ngân sách năm ' + CONVERT(VARCHAR(100),@p_YEAR_BUDGET) + N' có ngày thanh toán từ 1/12/' + CONVERT(VARCHAR(100),@p_YEAR_BUDGET - 1) AS NOTE_THIS_YEAR


	SELECT ROW_NUMBER() OVER(ORDER BY(SELECT 1)) AS STT,
	dbo.FN_GET_CHINHANH(BR.BRANCH_ID,'KV') AS KV,
	--CASE WHEN C.BRANCH_NAME_ETX IS NULL OR C.BRANCH_NAME_ETX = '' THEN BR.BRANCH_NAME ELSE C.BRANCH_NAME_ETX END CONSTRUCT_NAME, -- CÔNG TRÌNH
	C.CONSTRUCT_NAME AS CONSTRUCT_NAME,
	'' AS NOTES,
	RQ.REQ_CODE, RM.REQ_NAME, RQ.TOTAL_AMT, RM.EFFEC_DT AS APPROVE_DT,CON_HQ_T.CONTENT HQ_TYPE, ISNULL(TU.TONG_TAM_UNG,0) AS TOTAL_AD, 
	ISNULL(TT.TONG_THANH_TOAN,0) + ISNULL(SV.TONG_THANH_TOAN_TRUC_TIEP,0) AS TOTAL_PAY, ISNULL(RQ.TOTAL_AMT,0)- ISNULL(TU.TONG_TAM_UNG,0) - ISNULL(TT.TONG_THANH_TOAN,0) -ISNULL(SV.TONG_THANH_TOAN_TRUC_TIEP,0) AS REMAIN
	FROM CON_MASTER C
	LEFT JOIN CM_BRANCH BR ON C.BRANCH_ID = BR.BRANCH_ID
	LEFT JOIN CON_REQUEST_DOC RQ ON C.CONSTRUCT_ID = RQ.CONSTRUCT_ID
	LEFT JOIN PL_REQUEST_DOC RM ON RQ.REQ_ID = RM.REQ_ID
	LEFT JOIN CM_ALLCODE CON_HQ_T ON CON_HQ_T.CDNAME='REQ_HQ_TYPE' AND CON_HQ_T.CDType='CON' AND C.HQ_TYPE = CON_HQ_T.CDVAL
	--LEFT JOIN PL_REQUEST_DOC_DT RDT ON RM.REQ_ID = RDT.REQ_ID
	--LEFT JOIN CM_GOODS GD ON RDT.GOODS_ID = GD.GD_ID
	LEFT JOIN
	(
		SELECT DOC.PL_REQ_ID, SUM (AD.REQ_AMT) AS TONG_TAM_UNG FROM TR_REQ_ADVANCE_PAYMENT AD
		INNER JOIN TR_REQ_ADVANCE_DT DT ON AD.REQ_PAY_ID = DT.REQ_PAY_ID
		INNER JOIN TR_CONTRACT CT ON DT.REF_ID = CT.CONTRACT_ID
		INNER JOIN TR_REQUEST_DOC DOC ON CT.REQ_DOC_ID = DOC.REQ_ID
		WHERE AD.AUTH_STATUS_KT ='A'
		GROUP BY DOC.PL_REQ_ID
	) AS TU ON RQ.REQ_ID = TU.PL_REQ_ID
	LEFT JOIN
	(
		SELECT DOC.PL_REQ_ID, SUM (PAY.REQ_AMT) AS TONG_THANH_TOAN FROM TR_REQ_PAYMENT PAY
		INNER JOIN TR_REQ_ADVANCE_DT DT ON PAY.REQ_PAY_ID = DT.REQ_PAY_ID
		INNER JOIN TR_CONTRACT CT ON DT.REF_ID = CT.CONTRACT_ID
		INNER JOIN TR_REQUEST_DOC DOC ON CT.REQ_DOC_ID = DOC.REQ_ID
		WHERE PAY.AUTH_STATUS_KT ='A'
		GROUP BY DOC.PL_REQ_ID
	) AS TT ON RQ.REQ_ID = TT.PL_REQ_ID
	LEFT JOIN
	(
		SELECT DT.EMP_ID AS PL_REQ_ID, SUM (PAY.REQ_AMT) AS TONG_THANH_TOAN_TRUC_TIEP FROM TR_REQ_PAYMENT PAY
		INNER JOIN TR_REQ_PAY_SERVICE DT ON PAY.REQ_PAY_ID = DT.REQ_PAY_ID
		WHERE PAY.AUTH_STATUS_KT ='A'
		GROUP BY DT.EMP_ID
	) AS SV ON RQ.REQ_ID = SV.PL_REQ_ID
	WHERE 1=1
	AND (RQ.REQ_CODE LIKE '%' +@p_REQ_CODE +'%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE='')
	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 C.AUTH_STATUS ='A'
	AND (YEAR(RM.EFFEC_DT) =@p_YEAR_BUDGET OR @p_YEAR_BUDGET IS NULL)
	--AND (GD.GD_NAME LIKE N'%' +@p_TERM_BID +'%' OR @p_TERM_BID= '' OR @p_TERM_BID IS NULL)
	AND (RM.REQ_ID IN (SELECT D.REQ_ID FROM CON_REQUEST_DOC D WHERE D.REQ_ID IN (SELECT DT.REQ_ID
																					FROM PL_REQUEST_DOC_DT DT 
																					INNER JOIN CM_GOODS GD ON DT.GOODS_ID = GD.GD_ID
																					WHERE GD.GD_NAME LIKE N'%' +@p_TERM_BID +'%' )) OR @p_TERM_BID= '' OR @p_TERM_BID IS NULL )
	IF(@p_YEAR_BUDGET IS NULL)
	BEGIN
		SET @p_YEAR_BUDGET = YEAR(GETDATE())
		SELECT	N'THEO DÕI NGÂN SÁCH XDCB ĐẾN NĂM ' + CONVERT(VARCHAR(100),@p_YEAR_BUDGET) AS TITLE
	END
	ELSE
	BEGIN
		SELECT	N'THEO DÕI NGÂN SÁCH XDCB NĂM ' + CONVERT(VARCHAR(100),@p_YEAR_BUDGET) AS TITLE
	END
END