DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

ALTER PROC [dbo].[TR_REQ_PAY_DETAIL]

@p_REQ_PAY_ID VARCHAR(15) = NULL

AS

BEGIN

	DECLARE @TABLE_RETURN_01 TABLE ([NAME] NVARCHAR(250))

	DECLARE @TABLE_RETURN_01_DT TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(250))

	DECLARE @TABLE_RETURN_02 TABLE ([NAME] NVARCHAR(250))

	DECLARE @TABLE_RETURN_02_DT TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(250))

	DECLARE @TABLE_RETURN_03 TABLE  (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(250), AMT DECIMAL(18,0))

	DECLARE @TABLE_RETURN_04 TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(250), AMT DECIMAL(18,0))

	DECLARE @TABLE_RETURN_06 TABLE ([NAME] NVARCHAR(250), AMT DECIMAL(18,0))

	DECLARE @INDEX INT =1

	DECLARE @INDEX_TT INT =0

	IF(EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='1'))

	BEGIN

	  SET @INDEX_TT = @INDEX_TT+1

	--INSERT INTO @TABLE_RETURN

	--SELECT N'Tiền mặt' AS PHUONG_THUC, ISNULL(SUM (TOTAL_AMT),0) AS T1 FROM TR_REQ_PAY_METHOD

	--WHERE REQ_PAY_TYPE ='1' AND REQ_PAY_ID = @p_REQ_PAY_ID

	INSERT INTO @TABLE_RETURN_03

	SELECT N'. Người nhận: '+ ACC_NAME + CHAR(10) + N'    CMND số: ' + ACC_NO + char(10) + N'    Được cấp bởi: '+ ISSUED_BY + N' ngày cấp: '+ CONVERT(VARCHAR,ISSUED_DT,103) AS ACC_NAME, SUM(TOTAL_AMT) AS T2 

	FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='1'

	GROUP BY ACC_NO, ACC_NAME, ISSUED_BY, ISSUED_DT

	-- INSERT BANG TAM THE HIEN SO TIEN

	INSERT INTO @TABLE_RETURN_04

	SELECT 'N'+ CHAR(10) + 'D'+char(10) AS ACC_NAME, SUM(TOTAL_AMT) AS T2 

	FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='1'

	GROUP BY ACC_NO, ACC_NAME, ISSUED_BY, ISSUED_DT

	END

	IF(EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='0'))

	BEGIN

		SET @INDEX_TT = @INDEX_TT+1

		--INSERT INTO @TABLE_RETURN

		--SELECT N'Chuyển khoản' AS PHUONG_THUC,ISNULL(SUM (TOTAL_AMT),0) AS T3 FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_TYPE ='0' AND REQ_PAY_ID = @p_REQ_PAY_ID

		INSERT INTO @TABLE_RETURN_03

		SELECT N'. Đơn vị thụ hưởng: '+ ACC_NAME + CHAR(10)+ N'    Số tài khoản: ' + ACC_NO + char(10) + N'    Tại ngân hàng: '+ ISSUED_BY AS ACC_NAME, SUM(TOTAL_AMT) AS T4 

		FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='0'

		GROUP BY ACC_NO, ACC_NAME, ISSUED_BY

		-- INSERT BANG TAM THE HIEN SO TIEN

		INSERT INTO @TABLE_RETURN_04

		SELECT  'N'+ CHAR(10)+ 'S'+ char(10) AS ACC_NAME, SUM(TOTAL_AMT) AS T4 

		FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='0'

		GROUP BY ACC_NO, ACC_NAME, ISSUED_BY

	END

	-- KIEM TRA NOI DUNG SU DUNG CHI PHI VA NGAN SACH

	IF(EXISTS(SELECT * FROM TR_REQ_PAY_PERIOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID) OR EXISTS(SELECT * FROM TR_REQ_PAY_SERVICE WHERE REQ_PAY_ID =@p_REQ_PAY_ID) OR EXISTS(SELECT * FROM TR_REQ_PAY_SCHEDULE WHERE REQ_PAY_ID =@p_REQ_PAY_ID))

	BEGIN

		--INSERT INTO @TABLE_RETURN_01 VALUES (CONVERT(VARCHAR(15),@INDEX) +N'. Nội dung tạm ứng: ')

		INSERT INTO @TABLE_RETURN_01 VALUES( N'A - Nội dung thanh toán: ')

		--INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.REQ_PAY_REASON FROM TR_REQ_PAY_METHOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID

		--INSERT INTO @TABLE_RETURN_06 SELECT 'A', A.TOTAL_AMT FROM TR_REQ_PAY_METHOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID	

		INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.REASON FROM TR_REQ_PAY_PERIOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID

		INSERT INTO @TABLE_RETURN_06 SELECT 'A', A.AMT_PAY FROM  TR_REQ_PAY_PERIOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID	

		INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.REQ_PAY_DESC FROM TR_REQ_PAY_SCHEDULE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID

		INSERT INTO @TABLE_RETURN_06 SELECT 'A', A.AMT_PAY_REAL FROM  TR_REQ_PAY_SCHEDULE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID	

		INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.[SERVICE_NAME] FROM TR_REQ_PAY_SERVICE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID

		INSERT INTO @TABLE_RETURN_06 SELECT 'A', A.TOTAL_AMT FROM  TR_REQ_PAY_SERVICE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID	

	END

	--- HĐ & CHỨNG TỪ ĐÍNH KÈM

	IF(EXISTS(SELECT * FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID =@p_REQ_PAY_ID))

	BEGIN

		SET @INDEX = @INDEX+1

		--INSERT INTO @TABLE_RETURN_01 SELECT CONVERT(VARCHAR(15),@INDEX)+ N'. Chứng từ đính kèm: '

		INSERT INTO @TABLE_RETURN_02 SELECT N'B - Chứng từ đính kèm: '

	END

	IF(EXISTS(SELECT * FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND TYPE ='C'))

	BEGIN

		--INSERT INTO @TABLE_RETURN_01 SELECT N'Thông tin hợp đồng đính kèm: ' 

		INSERT INTO @TABLE_RETURN_02_DT SELECT ' '+ N'. Hợp đồng số ' + REF_CODE + ISNULL(N' ngày '+  FORMAT(REF_DT,'dd/MM/yyyy'),'')+ CASE WHEN AMT >0 THEN  N', số tiền '+ FORMAT( ISNULL(AMT,0),'#,#', 'vi-VN') ELSE '' END  AS T1 FROM TR_REQ_PAY_ATTACH

		WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND [TYPE] ='C'

	END

	IF(EXISTS(SELECT * FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND TYPE ='R'))

	BEGIN

		--INSERT INTO @TABLE_RETURN_01 SELECT N'Thông tin chứng từ khác đính kèm: ' 

		INSERT INTO @TABLE_RETURN_02_DT SELECT N'. '+ [DESCRIPTION]+ CASE WHEN AMT >0 THEN  N', số tiền '+ FORMAT( ISNULL(AMT,0),'#,#', 'vi-VN') ELSE '' END AS T1 FROM TR_REQ_PAY_ATTACH

		WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND [TYPE] ='R'

	END

	IF(EXISTS(SELECT * FROM TR_REQ_PAY_INVOICE WHERE REQ_PAY_ID =@p_REQ_PAY_ID))

	BEGIN

		--INSERT INTO @TABLE_RETURN_01 SELECT N'Thông tin chứng từ khác đính kèm: ' 

		INSERT INTO @TABLE_RETURN_02_DT SELECT N'. Số hóa đơn '+ INVOICE_NO +ISNULL(N' ngày '+  FORMAT(INVOICE_DT,'dd/MM/yyyy'),'')+ N', số tiền '+ FORMAT( ISNULL(PRICE+VAT,0),'#,#', 'vi-VN') AS T1 FROM TR_REQ_PAY_INVOICE

		WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND TYPE_FUNC='HC'

	END

	-- LAY THONG TIN CAP PHE DUYET

	IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_TYPE ='I'))

	BEGIN

		DECLARE @TABLE_RETURN_07 TABLE (HDQT NVARCHAR(50),TGD NVARCHAR(50),PTGD NVARCHAR(50),GDK NVARCHAR(50),GDDV NVARCHAR(50))

		DECLARE @GDDV NVARCHAR(50),@GDK NVARCHAR(50),@PTGD NVARCHAR(50),@TGD NVARCHAR(50),@HDQT NVARCHAR(50)

		DECLARE @APPR VARCHAR(15),@APPR1 VARCHAR(15),@APPR2 VARCHAR(15),@APPR3 VARCHAR(15),@APPR4 VARCHAR(15),@APPR5 VARCHAR(15)

		DECLARE @POS NVARCHAR(50),@POS1 NVARCHAR(50),@POS2 NVARCHAR(50),@POS3 NVARCHAR(50),@POS4 NVARCHAR(50)

		DECLARE @DATE NVARCHAR(50),@DATE1 NVARCHAR(50),@DATE2 NVARCHAR(50),@DATE3 NVARCHAR(50),@DATE4 NVARCHAR(50)

		SET @APPR =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='1')

		SET @POS =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR)

		SET @DATE =(SELECT ISNULL( FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='1')

		--IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR) ='GDDV') BEGIN SET @GDDV =@APPR END

		--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR) ='GDK')BEGIN SET @GDK =@APPR END

		--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR) ='PTGD') BEGIN SET @PTGD =@APPR END

		--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR) ='TGD') BEGIN	SET @TGD =@APPR	END

		--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR) ='HDQT') BEGIN SET @HDQT =@APPR END

		-------------

		SET @APPR1 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='2')

		SET @POS1 =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR1)

		SET @DATE1 =(SELECT ISNULL( FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='2')

		--IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='GDDV') BEGIN SET @GDDV =@APPR1 END

		--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='GDK')BEGIN SET @GDK =@APPR1 END

		--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='PTGD') BEGIN SET @PTGD =@APPR1 END

		--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='TGD') BEGIN	SET @TGD =@APPR1	END

		--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='HDQT')	BEGIN SET @HDQT =@APPR1 END

		------------

		SET @APPR2 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='3')

		SET @POS2 =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR2)

		SET @DATE2 =(SELECT ISNULL( FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='3')

		--IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='GDDV') BEGIN SET @GDDV =@APPR2 END

		--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='GDK')BEGIN SET @GDK =@APPR2 END

		--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='PTGD') BEGIN SET @PTGD =@APPR2 END

		--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='TGD') BEGIN	SET @TGD =@APPR2	END

		--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='HDQT')	BEGIN SET @HDQT =@APPR2 END

		SET @APPR3 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='4')

		SET @POS3 =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR3)

		SET @DATE3 =(SELECT ISNULL( FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='4')

		SET @APPR4 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='5')

		SET @POS4 =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR4)

		SET @DATE4 =(SELECT ISNULL( FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='5')

		DECLARE @BRANCH_TYPE VARCHAR(15), @BRANCH_RQ VARCHAR(15)

		SET @BRANCH_RQ =(SELECT BRANCH_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)

		SET @BRANCH_TYPE  = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ)

		IF(@BRANCH_TYPE='CN')

		BEGIN

			INSERT INTO @TABLE_RETURN_07 VALUES ((SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR4),(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR3),

		(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR2),(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME =  @APPR1),'')

		INSERT INTO @TABLE_RETURN_07 VALUES (@POS4,@POS3,@POS2,@POS1,'')

		INSERT INTO @TABLE_RETURN_07 VALUES (@DATE4,@DATE3,@DATE2,@DATE1,'')

		END

		ELSE

		BEGIN

			INSERT INTO @TABLE_RETURN_07 VALUES ((SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR4),(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR3),

		(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR2),(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME =  @APPR1),(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR))

		INSERT INTO @TABLE_RETURN_07 VALUES (@POS4,@POS3,@POS2,@POS1,@POS)

		INSERT INTO @TABLE_RETURN_07 VALUES (@DATE4,@DATE3,@DATE2,@DATE1,@DATE)

		END

		--IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='GDDV') BEGIN SET @GDDV =@APPR2 END

		--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='GDK')BEGIN SET @GDK =@APPR2 END

		--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='PTGD') BEGIN SET @PTGD =@APPR2 END

		--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='TGD') BEGIN	SET @TGD =@APPR2	END

		--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='HDQT')	BEGIN SET @HDQT =@APPR2 END

	END

	SELECT * FROM @TABLE_RETURN_01

	SELECT * FROM @TABLE_RETURN_01_DT

	SELECT * FROM @TABLE_RETURN_02

    SELECT * FROM @TABLE_RETURN_02_DT

	SELECT * FROM @TABLE_RETURN_03

	SELECT * FROM @TABLE_RETURN_04

	SELECT * FROM @TABLE_RETURN_06

	SELECT * FROM  @TABLE_RETURN_07

END