ALTER PROCEDURE [dbo].[rpt_TR_REQUEST_DOC_ReportPriceOver500M]
	@p_TR_REQ_DOC_ID	varchar(15) NULL
AS
	
	----nhà cung cấp đánh giá KNNLNT------
	SELECT DISTINCT
	A.SUPPLIER_1, A.SUPPLIER_2, A.SUPPLIER_3, A.SUPPLIER_4, A.SUPPLIER_5, A.SUPPLIER_6, A.SUPPLIER_7  
	FROM TR_REQ_DOC_XETGIA_TREN_500M_DT_KNNLNT A
	LEFT JOIN TR_REQ_DOC_XETGIA_TREN_500M B ON A.RECORD_MASTER_ID = B.RECORD_ID
	WHERE B.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	--------đánh giá KNNLNT---------
	SELECT ROW_NUMBER() OVER (ORDER BY RESULT.RECORD_DT_ID) AS STT, RESULT.*
	FROM
	(
		SELECT 
		A.RECORD_DT_ID, A.CONTENT_ASSESSMENT, A.ASSESSMENT_OF_HSMT,A.ASSESSMENT_RESULT_1, 
		A.ASSESSMENT_RESULT_2, A.ASSESSMENT_RESULT_3, A.ASSESSMENT_RESULT_4, A.ASSESSMENT_RESULT_5,
		A.ASSESSMENT_RESULT_6, A.ASSESSMENT_RESULT_7
		FROM TR_REQ_DOC_XETGIA_TREN_500M_DT_KNNLNT A
		LEFT JOIN TR_REQ_DOC_XETGIA_TREN_500M B ON A.RECORD_MASTER_ID = B.RECORD_ID
		WHERE B.REQ_DOC_ID = @p_TR_REQ_DOC_ID
	) AS RESULT

	----nhà cung cấp đánh giá về kỹ thuật------
	SELECT DISTINCT
	A.SUPPLIER_1, A.SUPPLIER_2, A.SUPPLIER_3, A.SUPPLIER_4, A.SUPPLIER_5, A.SUPPLIER_6, A.SUPPLIER_7
	FROM TR_REQ_DOC_XETGIA_TREN_500M_DT_KT A
	LEFT JOIN TR_REQ_DOC_XETGIA_TREN_500M B ON A.RECORD_MASTER_ID = B.RECORD_ID
	WHERE B.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	----đánh giá về kỹ thuật-----
	SELECT ROW_NUMBER() OVER (ORDER BY RESULT.RECORD_DT_ID) AS STT, RESULT.*
	FROM
	(
		SELECT 
		A.RECORD_DT_ID, A.CONTENT_ASSESSMENT, A.ASSESSMENT_OF_HSMT,A.ASSESSMENT_1, A.ASSESSMENT_2,
		A.ASSESSMENT_3, A.ASSESSMENT_4, A.ASSESSMENT_5,A.ASSESSMENT_6, A.ASSESSMENT_7
		FROM TR_REQ_DOC_XETGIA_TREN_500M_DT_KT A
		LEFT JOIN TR_REQ_DOC_XETGIA_TREN_500M B ON A.RECORD_MASTER_ID = B.RECORD_ID
		WHERE B.REQ_DOC_ID = @p_TR_REQ_DOC_ID
	) AS RESULT

	--đánh giá về giá----
	SELECT ROW_NUMBER() OVER (ORDER BY RESULT.RECORD_DT_ID) AS STT, RESULT.*
	FROM
	(
		SELECT 
		A.RECORD_DT_ID, 
		A.SUPPLIER,
		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.FIRST_PRICE))
		AS FIRST_PRICE,
		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.REDUCED_PRICE))
		AS REDUCED_PRICE,
		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.OFFICIAL_FIRST_PRICE))
		AS OFFICIAL_FIRST_PRICE
		FROM TR_REQ_DOC_XETGIA_TREN_500M_DT_DGVG A
		LEFT JOIN TR_REQ_DOC_XETGIA_TREN_500M B ON A.RECORD_MASTER_ID = B.RECORD_ID
		WHERE B.REQ_DOC_ID = @p_TR_REQ_DOC_ID
	) AS RESULT

	---xếp hạng NCC đạt yêu cầu-----
	SELECT ROW_NUMBER() OVER (ORDER BY RESULT.RATINGS) AS STT, RESULT.*
	FROM
	(
		SELECT A.RECORD_DT_ID ,A.SUPPLIER, A.RATINGS, A.NOTES
		FROM TR_REQ_DOC_XETGIA_TREN_500M_DT_XH A
		LEFT JOIN TR_REQ_DOC_XETGIA_TREN_500M B ON A.RECORD_MASTER_ID = B.RECORD_ID
		WHERE B.REQ_DOC_ID = @p_TR_REQ_DOC_ID
	) AS RESULT
	WHERE RESULT.RATINGS IS NOT NULL AND RESULT.RATINGS <> ''
	ORDER BY RESULT.RATINGS ASC

	------------------------Nội dung điều khoản cần thg thảo---------------
	SELECT TOP 1
		CASE
			WHEN A.NEGOTIATE_TERMS IS NOT NULL AND A.NEGOTIATE_TERMS <> ''
				THEN N'6)   Các điều khoản cần thương thảo:'
			ELSE NULL
		END
		AS TERMS_TITLE,
		A.NEGOTIATE_TERMS AS TERMS,
		CASE
			WHEN A.OTHER_PROPOSE IS NOT NULL AND A.OTHER_PROPOSE <> '' AND (A.NEGOTIATE_TERMS IS NOT NULL AND A.NEGOTIATE_TERMS <> '')
				THEN N'7)   Các đề xuất khác (nếu có):'
			WHEN A.OTHER_PROPOSE IS NOT NULL AND A.OTHER_PROPOSE <> '' AND (A.NEGOTIATE_TERMS IS NULL OR A.NEGOTIATE_TERMS = '')
				THEN N'6)   Các đề xuất khác (nếu có):'
			ELSE NULL
		END
		AS PROPOSES_TITLE,
		A.OTHER_PROPOSE AS PROPOSES
	FROM TR_REQ_DOC_XETGIA_TREN_500M_DT_XH A
	LEFT JOIN TR_REQ_DOC_XETGIA_TREN_500M B ON A.RECORD_MASTER_ID = B.RECORD_ID
	WHERE B.REQ_DOC_ID = @p_TR_REQ_DOC_ID
	AND A.RATINGS IS NOT NULL AND A.RATINGS <> ''
	ORDER BY A.RATINGS ASC

	-------------------------CHỮ KÝ DMMS--------------------------
	DECLARE @t_SIGN_TABLE TABLE(APPROVE_DT VARCHAR(30), SIGN_NAME NVARCHAR(50), TYPE VARCHAR(15))
	DECLARE @p_APPROVE_DT_KSV VARCHAR(25), @p_KSV_NAME NVARCHAR(50), --KSV
			@p_APPROVE_DT_GDDV VARCHAR(25), @p_GDDV_NAME NVARCHAR(50) --GDDV

	INSERT INTO @t_SIGN_TABLE
		-----------------KSV DMMS----------------
		SELECT TOP 1 ISNULL(FORMAT(D.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT, 
		C.TLFullName AS SIGN_NAME,
		'KSV' AS TYPE
		FROM PL_REQUEST_PROCESS_CHILD A
		LEFT JOIN PL_REQUEST_PROCESS B ON A.PROCESS_ID = B.ID
		LEFT JOIN TL_USER C ON A.TLNAME = C.TLNANME
		LEFT JOIN PL_PROCESS D ON B.REQ_ID = D.REQ_ID AND A.TLNAME = D.CHECKER_ID
		WHERE B.REQ_ID = @p_TR_REQ_DOC_ID
		AND A.TYPE_JOB = 'KS'
		AND A.STATUS_JOB = 'P'
		AND B.PROCESS_ID = 'DMMS'
		ORDER BY D.ID DESC

	INSERT INTO @t_SIGN_TABLE
		---------------Trưởng đơn vị DMMS------------
		SELECT 
		DISTINCT ISNULL(FORMAT(A.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT,
		B.TLFullName AS SIGN_NAME,
		'GDDV' AS TYPE
		FROM PL_REQUEST_PROCESS A
		LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
		WHERE A.REQ_ID = @p_TR_REQ_DOC_ID
		AND A.PROCESS_ID = 'DMMS'
		AND A.STATUS = 'P'

	------KSV DMMS-----
	SET @p_APPROVE_DT_KSV = (SELECT A.APPROVE_DT FROM @t_SIGN_TABLE A WHERE A.TYPE = 'KSV')
	SET @p_KSV_NAME = (SELECT A.SIGN_NAME FROM @t_SIGN_TABLE A WHERE A.TYPE = 'KSV')
	------GDDV DMMS----
	SET @p_APPROVE_DT_GDDV = (SELECT A.APPROVE_DT FROM @t_SIGN_TABLE A WHERE A.TYPE = 'GDDV')
	SET @p_GDDV_NAME = (SELECT A.SIGN_NAME FROM @t_SIGN_TABLE A WHERE A.TYPE = 'GDDV')

	
	------------LẤY TÊN NG DUYỆT---------------
	SELECT	ISNULL(@p_KSV_NAME, '') AS KSV_NAME, 
			ISNULL(@p_GDDV_NAME,'') AS DMMS_NAME
	-------------LẤY NGÀY DUYỆT--------------
	SELECT	ISNULL(@p_APPROVE_DT_KSV, '') AS APPROVE_DT_KSV, 
			ISNULL(@p_APPROVE_DT_GDDV, '') AS APPROVE_DT_GDDV

	----------------CHỮ KÝ PHÒNG KẾ TOÁN-------------------
	SELECT DISTINCT B.TLFullName AS KT_NAME,
	ISNULL(FORMAT(A.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT_KT
	FROM PL_REQUEST_PROCESS A
	LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
	WHERE A.REQ_ID = @p_TR_REQ_DOC_ID
	AND A.PROCESS_ID = 'DVCM'
	AND A.DVDM_ID = 'DM0000000000006' --DVCM là phòng KT
	AND A.STATUS = 'P'

	--------------- CHỮ KÝ CẤP CÓ THẨM QUYỀN (CẤP DUYỆT CUỐI)-----------------
	SELECT DISTINCT C.POS_NAME AS CHUC_DANH, 
	B.TLFullName AS FINAL_NAME,
	ISNULL(FORMAT(A.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT_FINAL
	FROM PL_REQUEST_PROCESS A
	LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
	LEFT JOIN CM_EMPLOYEE_LOG C ON C.USER_DOMAIN = B.TLNANME
	WHERE A.REQ_ID = @p_TR_REQ_DOC_ID
	AND A.PROCESS_ID = 
		(
			SELECT TOP 1 PARENT_PROCESS_ID 
			FROM PL_REQUEST_PROCESS B 
			WHERE B.PROCESS_ID = 'APPROVE' 
			AND B.REQ_ID = @p_TR_REQ_DOC_ID
		)
	AND A.STATUS = 'P'

	------------------LẤY ĐẠI DIỆN ĐMMS--------------------
	DECLARE @t_MEMBER_DMMS_TABLE TABLE 
		(STT INT, MEMBER_DMMS_NAME NVARCHAR(100), POSITION_DMMS NVARCHAR(100))
	INSERT INTO @t_MEMBER_DMMS_TABLE
		-------------THÀNH VIÊN 1-------------------
		SELECT 1 AS STT,
			N'Ông/ Bà: ' + TU_1.TLFullName AS MEMBER_DMMS_NAME,
			N'Chức vụ: ' + A.ROLE_DMMS_1 AS POSITION_DMMS
			FROM TR_REQ_DOC_XETGIA_TREN_500M A
			LEFT JOIN TL_USER TU_1 ON A.MEMBER_DMMS_1 = TU_1.TLNANME
			WHERE A.REQ_DOC_ID = @p_TR_REQ_DOC_ID
		UNION
		-------------THÀNH VIÊN 2-------------------
		SELECT 2 AS STT,
			N'Ông/ Bà: ' + TU_2.TLFullName AS MEMBER_DMMS_NAME,
			N'Chức vụ: ' + A.ROLE_DMMS_2 AS POSITION_DMMS
			FROM TR_REQ_DOC_XETGIA_TREN_500M A
			LEFT JOIN TL_USER TU_2 ON A.MEMBER_DMMS_2 = TU_2.TLNANME
			WHERE A.REQ_DOC_ID = @p_TR_REQ_DOC_ID
		UNION
		-------------THÀNH VIÊN 3-------------------
		SELECT 2 AS STT,
			N'Ông/ Bà: ' + TU_3.TLFullName AS MEMBER_DMMS_NAME,
			N'Chức vụ: ' + A.ROLE_DMMS_3 AS POSITION_DMMS
			FROM TR_REQ_DOC_XETGIA_TREN_500M A
			LEFT JOIN TL_USER TU_3 ON A.MEMBER_DMMS_3 = TU_3.TLNANME
			WHERE A.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	SELECT A.MEMBER_DMMS_NAME, A.POSITION_DMMS FROM @t_MEMBER_DMMS_TABLE A
	WHERE A.MEMBER_DMMS_NAME IS NOT NULL AND A.POSITION_DMMS IS NOT NULL
	ORDER BY A.STT

		------------------LẤY ĐẠI DIỆN KẾ TOÁN--------------------
	DECLARE @t_MEMBER_KT_TABLE TABLE 
		(STT INT, MEMBER_KT_NAME NVARCHAR(100), POSITION_KT NVARCHAR(100))
	INSERT INTO @t_MEMBER_KT_TABLE
			-------------THÀNH VIÊN 1-------------------
		SELECT 1 AS STT,
			N'Ông/ Bà: ' + TU_1.TLFullName AS MEMBER_KT_NAME,
			N'Chức vụ: ' + A.ROLE_KT_1 AS POSITION_KT
			FROM TR_REQ_DOC_XETGIA_TREN_500M A
			LEFT JOIN TL_USER TU_1 ON A.MEMBER_KT_1 = TU_1.TLNANME
			WHERE A.REQ_DOC_ID = @p_TR_REQ_DOC_ID
		UNION
			-------------THÀNH VIÊN 2-------------------
		SELECT 2 AS STT,
			N'Ông/ Bà: ' + TU_2.TLFullName AS MEMBER_KT_NAME,
			N'Chức vụ: ' + A.ROLE_KT_2 AS POSITION_KT
			FROM TR_REQ_DOC_XETGIA_TREN_500M A
			LEFT JOIN TL_USER TU_2 ON A.MEMBER_KT_2 = TU_2.TLNANME
			WHERE A.REQ_DOC_ID = @p_TR_REQ_DOC_ID
		UNION
			-------------THÀNH VIÊN 3-------------------
		SELECT 3 AS STT,
			N'Ông/ Bà: ' + TU_3.TLFullName AS MEMBER_KT_NAME,
			N'Chức vụ: ' + A.ROLE_KT_3 AS POSITION_KT
			FROM TR_REQ_DOC_XETGIA_TREN_500M A
			LEFT JOIN TL_USER TU_3 ON A.MEMBER_KT_3 = TU_3.TLNANME
			WHERE A.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	SELECT A.MEMBER_KT_NAME, A.POSITION_KT FROM @t_MEMBER_KT_TABLE A
	WHERE A.MEMBER_KT_NAME IS NOT NULL AND A.POSITION_KT IS NOT NULL
	ORDER BY A.STT

	-------------------------Đàm phán giá----------------------
	SELECT ROW_NUMBER() OVER (ORDER BY RESULT.RECORD_DT_ID) AS STT, RESULT.*
	FROM
	(
		SELECT 
		A.RECORD_DT_ID, 
		A.SUPPLIER,
		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.TOTAL_DP))
		AS TOTAL_AMT
		FROM TR_REQ_DOC_XETGIA_TREN_500M_DT_DPG A
		LEFT JOIN TR_REQ_DOC_XETGIA_TREN_500M B ON A.RECORD_MASTER_ID = B.RECORD_ID
		WHERE B.REQ_DOC_ID = @p_TR_REQ_DOC_ID
	) AS RESULT