DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

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