ALTER   PROCEDURE [dbo].[TR_CONTRACT_PAYMENT_ById]
@CONTRACT_ID varchar(15)
AS
IF((SELECT CONT_TYPE FROM TR_CONTRACT WHERE CONTRACT_ID =@CONTRACT_ID) ='DK')
BEGIN

	SELECT '' AS PAY_ID,@CONTRACT_ID AS CONTRACT_ID,B.PAY_PHASE AS PAY_PHASE, C.APPROVE_DT_KT AS EXPECTED_DT,100.0 AS [PERCENT],
	B.AMT_PAY AS AMOUNT,'' AS PAY_STATUS,B.REASON AS NOTES,'' AS MAKER_ID,B.PAY_DT_REAL AS CREATE_DT,'A' AS AUTH_STATUS,'' AS CHECKER_ID,C.APPROVE_DT_KT AS APPROVE_DT,
	C.APPROVE_DT_KT AS PAY_DT, B.AMT_PAY AS PAY_AMOUNT,'' AS CONTRACT_CODE,
	'' AS REQUEST_ID,'' AS CONTRACT_PARENT,
	B.AMT_PAY AS AMT_ADVANCE, 0.0 AS AMT_PAY_DO,
	CASE WHEN B.PROCESS ='2' THEN 'TTX' ELSE  'DTU' END AS CDVAL,'' AS CONTENT, 0.0 AS AMT_LINK,  0.0 AS REMAIN_AMT
	FROM TR_REQ_PAY_PERIOD B
	INNER JOIN TR_REQ_ADVANCE_PAYMENT C ON B.REQ_PAY_ID = C.REQ_PAY_ID -- 2021928 LUCTV EDIT
	WHERE B.PAY_TYPE ='ADV_PAY' AND B.AUTH_STATUS_KT ='A' AND B.CONTRACT_ID =@CONTRACT_ID AND B.AUTH_STATUS_KT IS NOT NULL
	AND (B.REQ_PAY_ID IN(SELECT REQ_PAY_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE RECORD_STATUS <> '0'))
	UNION
	SELECT '' AS PAY_ID,@CONTRACT_ID AS CONTRACT_ID,B.PAY_PHASE AS PAY_PHASE, C.APPROVE_DT_KT AS EXPECTED_DT,100 AS [PERCENT],
	B.AMT_PAY AS AMOUNT,'' AS PAY_STATUS,B.REASON AS NOTES,'' AS MAKER_ID,B.PAY_DT_REAL AS CREATE_DT,'A' AS AUTH_STATUS,'' AS CHECKER_ID,C.APPROVE_DT_KT AS APPROVE_DT,
	C.APPROVE_DT_KT AS PAY_DT, B.AMT_PAY AS PAY_AMOUNT,'' AS CONTRACT_CODE,
	'' AS REQUEST_ID,'' AS CONTRACT_PARENT,
	0.0 AS AMT_ADVANCE, B.AMT_PAY AS AMT_PAY_DO,
	'TTX' CDVAL,'' AS CONTENT, 0.0 AS AMT_LINK,  0.0 AS REMAIN_AMT
	FROM TR_REQ_PAY_PERIOD B
	INNER JOIN TR_REQ_PAYMENT C ON B.REQ_PAY_ID = C.REQ_PAY_ID
	WHERE B.PAY_TYPE ='PAY' 
	--AND B.PROCESS ='1' -- chi hien cac phieu thanh toan khac hoan ung
	AND B.AUTH_STATUS_KT ='A'
	AND B.CONTRACT_ID =@CONTRACT_ID
	ORDER BY C.APPROVE_DT_KT DESC
END
ELSE
BEGIN
	SELECT  A.PAY_ID
      ,A.CONTRACT_ID
      ,A.PAY_PHASE
      ,A.EXPECTED_DT
      ,A.[PERCENT]
      ,A.AMOUNT
      ,A.PAY_STATUS
      ,A.NOTES
      ,A.RECORD_STATUS
      ,A.MAKER_ID
      ,A.CREATE_DT
      ,A.AUTH_STATUS
      ,A.CHECKER_ID
      ,A.APPROVE_DT
      ,A.PAY_DT
      ,A.PAY_AMOUNT
      ,A.REQUEST_ID
      ,A.CONTRACT_PARENT
      ,A.BID_ID, ISNULL(B.AMT_ADVANCE,0) AS AMT_ADVANCE, ISNULL(B2.AMT_PAY_DO,0) AS AMT_PAY_DO , 
	ISNULL(C.CDVAL,(CASE WHEN ISNULL(B.AMT_ADVANCE,0) >0 AND ISNULL(B2.AMT_PAY_DO,0) =0 THEN 'DTU' WHEN ISNULL(B2.AMT_PAY_DO,0) >0 AND ISNULL((A.AMOUNT - ISNULL(B2.AMT_PAY_DO,0) - ISNULL(B.AMT_ADVANCE,0)-ISNULL(B3.AMT_LINK,0)),0) >0 THEN 'DTT' WHEN ISNULL((A.AMOUNT - ISNULL(B2.AMT_PAY_DO,0) - ISNULL(B.AMT_ADVANCE,0)-ISNULL(B3.AMT_LINK,0)),0) =0 THEN 'TTX' ELSE 'CTT' END)) AS CDVAL, 
	C.CONTENT, ISNULL(B3.AMT_LINK,0) AS AMT_LINK,  ISNULL((A.AMOUNT - ISNULL(B2.AMT_PAY_DO,0) - ISNULL(B.AMT_ADVANCE,0) - ISNULL(B3.AMT_LINK,0)),0) AS REMAIN_AMT
	,TRC.CONTRACT_CODE
	FROM TR_CONTRACT_PAYMENT A
	LEFT JOIN TR_CONTRACT TRC ON A.CONTRACT_ID = TRC.CONTRACT_ID
	--- TINH SO TIEN DA TAM UNG
	LEFT JOIN 
	(
	 SELECT P.PAY_ID , SUM(P.AMT_PAY_REAL) AMT_ADVANCE,
	 MAX(P.AMT_PAY_DO) AMT_PAY_DO, MAX(P.AMT_PAY) AMT_PAY, MAX(P.PAYMENT_STATUS) PAYMENT_STATUS 
	 FROM TR_REQ_PAY_SCHEDULE P 
	 WHERE P.AUTH_STATUS_KT='A' AND TRN_TYPE='ADV_PAY'
	 GROUP BY P.PAY_ID 
	)
	 B ON A.PAY_ID = B.PAY_ID 
	LEFT JOIN 
	(
	SELECT P.PAY_ID , ISNULL(MAX(P.AMT_ADVANCE),0) AMT_ADVANCE,
	 ISNULL(SUM(P.AMT_PAY_REAL),0) AMT_PAY_DO, ISNULL(MAX(P.AMT_PAY),0) AMT_PAY, MAX(P.PAYMENT_STATUS) PAYMENT_STATUS 
	 FROM TR_REQ_PAY_SCHEDULE P 
	 WHERE P.AUTH_STATUS_KT='A' AND TRN_TYPE='PAY'
	 GROUP BY P.PAY_ID 
	)
	 B2 ON A.PAY_ID = B2.PAY_ID 
	  -- LEFT JOIN DE LAY SO TIEN LINK TOI PHIEU TAM UNG
	 LEFT JOIN 
	(
	SELECT P.PAY_ID , ISNULL(MAX(P.AMT_REMAIN),0) AS AMT_LINK,
	 SUM((P.AMT_PAY_REAL)) AMT_PAY_DO, ISNULL(MAX(P.AMT_PAY),0) AMT_PAY, MAX(P.PAYMENT_STATUS) PAYMENT_STATUS 
	 FROM TR_REQ_PAY_SCHEDULE P 
	 WHERE P.AUTH_STATUS_KT='A' AND TRN_TYPE='PAY' AND P.REQ_ADV_ID IS NOT NULL AND P.REQ_ADV_ID <>''
	 GROUP BY P.PAY_ID 
	)
	 B3 ON A.PAY_ID = B3.PAY_ID 
	LEFT JOIN CM_ALLCODE C ON C.CDVAL = B.PAYMENT_STATUS AND C.CDNAME = 'PAYMENT_STATUS'
	WHERE  A.CONTRACT_ID= @CONTRACT_ID
END