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

ALTER  PROCEDURE [dbo].[rpt_TR_REQUEST_DOC_ReportPrice10To100M]  

@p_REQ_ID varchar(15)  

AS  

	----Nhà cung cấp-------

	SELECT DISTINCT A.SUPPLIER_1, A.SUPPLIER_2, A.SUPPLIER_3, A.SUPPLIER_4, A.SUPPLIER_5

	FROM TR_REQ_DOC_XETGIA_DUOI_100M_DT A

	LEFT JOIN TR_REQ_DOC_XETGIA_DUOI_100M B ON A.RECORD_MASTER_ID = B.RECORD_ID

	WHERE B.REQ_DOC_ID = @p_REQ_ID

	-------Thông tin chi tiết xét giá-----------

	SELECT ROW_NUMBER() OVER (ORDER BY RESULT.RECORD_DT_ID) AS STT, RESULT.* FROM  

	(   

		SELECT 

		A.RECORD_DT_ID,

		A.GOOD_NAME,

		A.TECH_SPECIFICATION,

		A.UNIT,

		A.QUANTITY,

		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.UNIT_PRICE_1)) 

		AS UNIT_PRICE_1,

		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.TOTAL_AMT_1)) 

		AS TOTAL_AMT_1,

		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.UNIT_PRICE_2)) 

		AS UNIT_PRICE_2,

		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.TOTAL_AMT_2)) 

		AS TOTAL_AMT_2,

		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.UNIT_PRICE_3)) 

		AS UNIT_PRICE_3,

		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.TOTAL_AMT_3)) 

		AS TOTAL_AMT_3,

		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.UNIT_PRICE_4)) 

		AS UNIT_PRICE_4,

		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.TOTAL_AMT_4)) 

		AS TOTAL_AMT_4,

		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.UNIT_PRICE_5)) 

		AS UNIT_PRICE_5,

		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.TOTAL_AMT_5)) 

		AS TOTAL_AMT_5

		FROM TR_REQ_DOC_XETGIA_DUOI_100M_DT A

		LEFT JOIN TR_REQ_DOC_XETGIA_DUOI_100M B ON A.RECORD_MASTER_ID = B.RECORD_ID

		WHERE B.REQ_DOC_ID = @p_REQ_ID

	) 

	AS RESULT

	-------Thành tiền trước thuế-----------

	SELECT

	--CAST(ISNULL(SUM(A.TOTAL_AMT_1),0) AS DECIMAL(18,2)) 

	--AS SUM_TOTAL_AMT_1,

	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_1),0))) 

	AS SUM_TOTAL_AMT_1,

	--CAST(ISNULL(SUM(A.TOTAL_AMT_2),0) AS DECIMAL(18,2)) 

	--AS SUM_TOTAL_AMT_2,

	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_2),0))) 

	AS SUM_TOTAL_AMT_2,

	--CAST(ISNULL(SUM(A.TOTAL_AMT_3),0) AS DECIMAL(18,2)) 

	--AS SUM_TOTAL_AMT_3,

	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_3),0))) 

	AS SUM_TOTAL_AMT_3,

	--CAST(ISNULL(SUM(A.TOTAL_AMT_4),0) AS DECIMAL(18,2)) 

	--AS SUM_TOTAL_AMT_4,

	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_4),0))) 

	AS SUM_TOTAL_AMT_4,

	--CAST(ISNULL(SUM(A.TOTAL_AMT_5),0) AS DECIMAL(18,2)) 

	--AS SUM_TOTAL_AMT_5

	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_5),0))) 

	AS SUM_TOTAL_AMT_5

	FROM TR_REQ_DOC_XETGIA_DUOI_100M_DT A

	LEFT JOIN TR_REQ_DOC_XETGIA_DUOI_100M B ON A.RECORD_MASTER_ID = B.RECORD_ID

	WHERE B.REQ_DOC_ID = @p_REQ_ID

	-------số tiền thuế----------

	SELECT

	--CAST(ISNULL(SUM(A.VAT_AMT_1),0) AS DECIMAL(18,2)) 

	--AS VAT_TOTAL_AMT_1,

	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.VAT_AMT_1 * A.QUANTITY),0))) 

	AS VAT_TOTAL_AMT_1,

	--CAST(ISNULL(SUM(A.VAT_AMT_2),0) AS DECIMAL(18,2)) 

	--AS VAT_TOTAL_AMT_2,

	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.VAT_AMT_2 * A.QUANTITY),0))) 

	AS VAT_TOTAL_AMT_2,

	--CAST(ISNULL(SUM(A.VAT_AMT_3),0) AS DECIMAL(18,2)) 

	--AS VAT_TOTAL_AMT_3,

	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.VAT_AMT_3 * A.QUANTITY),0))) 

	AS VAT_TOTAL_AMT_3,

	--CAST(ISNULL(SUM(A.VAT_AMT_4),0) AS DECIMAL(18,2)) 

	--AS VAT_TOTAL_AMT_4,

	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.VAT_AMT_4 * A.QUANTITY),0))) 

	AS VAT_TOTAL_AMT_4,

	--CAST(ISNULL(SUM(A.VAT_AMT_5),0) AS DECIMAL(18,2)) 

	--AS VAT_TOTAL_AMT_5

	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.VAT_AMT_5 * A.QUANTITY),0))) 

	AS VAT_TOTAL_AMT_5

	FROM TR_REQ_DOC_XETGIA_DUOI_100M_DT A

	LEFT JOIN TR_REQ_DOC_XETGIA_DUOI_100M B ON A.RECORD_MASTER_ID = B.RECORD_ID

	LEFT JOIN TR_REQUEST_DOC C ON B.REQ_DOC_ID = C.REQ_ID

	WHERE B.REQ_DOC_ID = @p_REQ_ID

	-------thành tiền sau thuế------------

	SELECT

	--ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_1), 0) 

	--AS TOTAL_AMT_AFTER_VAT_1,

	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_1),0))) 

	AS TOTAL_AMT_AFTER_VAT_1,

	--ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_2), 0) 

	--AS TOTAL_AMT_AFTER_VAT_2,

	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_2),0))) 

	AS TOTAL_AMT_AFTER_VAT_2,

	--ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_3), 0)

	--AS TOTAL_AMT_AFTER_VAT_3,

	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_3),0))) 

	AS TOTAL_AMT_AFTER_VAT_3,

	--ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_4), 0)

	--AS TOTAL_AMT_AFTER_VAT_4,

	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_4),0))) 

	AS TOTAL_AMT_AFTER_VAT_4,

	--ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_5), 0)

	--AS TOTAL_AMT_AFTER_VAT_5

	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_5),0))) 

	AS TOTAL_AMT_AFTER_VAT_5

	FROM TR_REQ_DOC_XETGIA_DUOI_100M_DT A

	LEFT JOIN TR_REQ_DOC_XETGIA_DUOI_100M B ON A.RECORD_MASTER_ID = B.RECORD_ID

	LEFT JOIN TR_REQUEST_DOC C ON B.REQ_DOC_ID = C.REQ_ID

	WHERE B.REQ_DOC_ID = @p_REQ_ID

	-----Tên đơn vị DMMS--------

	--SELECT N'Trưởng ĐMMS ' + RESULT.DVDM_NAME + ' ' + RESULT.DVDM_BRANCH AS DMMS_TITLE

	--FROM(

	--	SELECT

	--	CASE

	--		----TH có DMMS---

	--		WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME

	--		---TH DMMS là đơn vị tạo---

	--		ELSE BR.BRANCH_NAME

	--	END AS DVDM_NAME,

	--	CASE

	--		---Nếu DMMS nằm trong hội sở

	--		WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN N'Hội sở'

	--		ELSE ''

	--	END AS DVDM_BRANCH

	--	FROM PL_REQUEST_PROCESS PL

	--	LEFT JOIN CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID

	--	LEFT JOIN CM_BRANCH BR ON PL.DVDM_ID = BR.BRANCH_ID

	--	WHERE PL.REQ_ID = @p_REQ_ID

	--) AS RESULT

	--WHERE RESULT.DVDM_NAME IS NOT NULL

	---------------Kết luận + ghi chú-----------------------

	SELECT A.CONCLUSION, A.NOTES

	FROM TR_REQ_DOC_XETGIA_DUOI_100M A

	LEFT JOIN TR_REQUEST_DOC B ON A.REQ_DOC_ID = B.REQ_ID

	WHERE A.REQ_DOC_ID = @p_REQ_ID

	----------------------CHỮ KÝ-----------------------------

	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_REQ_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_REQ_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

	-------------KSV DMMS-------------------

	--SELECT TOP 1 ISNULL(FORMAT(D.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT, 

	--C.TLFullName AS KSV_NAME

	--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_REQ_ID

	--AND A.TYPE_JOB = 'KS'

	--AND A.STATUS_JOB = 'P'

	--ORDER BY D.ID DESC

	---------Trưởng đơn vị DMMS--------

	--SELECT DISTINCT C.TLFullName AS DMMS_NAME

	--FROM PL_REQUEST_PROCESS A

	--LEFT JOIN PL_REQUEST_PROCESS_CHILD B ON A.ID = B.PROCESS_ID

	--LEFT JOIN TL_USER C ON B.TLNAME = C.TLNANME

	--WHERE A.REQ_ID = @p_REQ_ID

	--AND B.TYPE_JOB = 'TP'

	--AND A.PROCESS_ID = 'DMMS'

	--AND B.STATUS_JOB = 'P'

	--SELECT 

	--DISTINCT ISNULL(FORMAT(A.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT,

	--B.TLFullName AS DMMS_NAME

	--FROM PL_REQUEST_PROCESS A

	--LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME

	--WHERE A.REQ_ID = @p_REQ_ID

	--AND A.PROCESS_ID = 'DMMS'

	--AND A.STATUS = 'P'