ALTER PROCEDURE [dbo].[TR_CHECK_ROLE_APPROVE]
@TYPE VARCHAR(15) = NULL,
@p_REQ_ID	varchar(15) = NULL,
@p_USER_LOGIN varchar(15) = NULL
AS
BEGIN TRANSACTION
IF(@TYPE <> 'ADV_PAY')
BEGIN
	IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_ID AND STATUS_JOB ='C' AND TLNAME =@p_USER_LOGIN AND TYPE_JOB IN ('TP','KS')))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '0' as Result, '' ErrorDesc
			RETURN '0'
		END
END
-- KHAI BAO THEM ROLE NHAN UY QUYEN
DECLARE @TABLE_ROLE TABLE (ROLE_AUTH VARCHAR(50))
DECLARE @ROLE_ID VARCHAR(50)
SET @ROLE_ID = (SELECT ROLENAME FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)
INSERT INTO @TABLE_ROLE SELECT @ROLE_ID
INSERT INTO @TABLE_ROLE SELECT ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =@ROLE_ID AND 
(CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) OR (EFF_DATE IS NULL OR EXP_DATE IS NULL))

	DECLARE @AUTHOR TABLE
	(
		ROLE_ID VARCHAR(20),
		BRANCH_ID VARCHAR(20),
		DEP_ID VARCHAR(20),
		BRANCH_TYPE VARCHAR(20)
	)
	DECLARE @AUTHOR_DVDM TABLE
	(
		ROLE_ID VARCHAR(20),
		BRANCH_ID VARCHAR(20),
		DEP_ID VARCHAR(20),
		DVDM_ID VARCHAR(20)
	)

		DECLARE @AUTHOR_DMMS TABLE
	(
		ROLE_ID VARCHAR(20),
		BRANCH_ID VARCHAR(20),
		DEP_ID VARCHAR(20),
		DMMS_ID VARCHAR(20)
	)

	INSERT INTO @AUTHOR
	(
	    ROLE_ID,
	    BRANCH_ID,
	    DEP_ID,
		BRANCH_TYPE
	)
	SELECT RoleName,TLSUBBRID,SECUR_CODE,CB.BRANCH_TYPE FROM dbo.TL_USER TU
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
	WHERE TLNANME=@p_USER_LOGIN
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TLSUBBRID,SECUR_CODE,CB.BRANCH_TYPE FROM dbo.TL_USER TU
	LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
	WHERE TLNANME=@p_USER_LOGIN
	UNION ALL
	SELECT TM1.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,CB.BRANCH_TYPE FROM dbo.TL_SYS_ROLE_MAPPING TM1
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
	WHERE TM1.TLNAME=@p_USER_LOGIN AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,CB.BRANCH_TYPE FROM dbo.TL_SYS_ROLE_MAPPING TM1
	LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TM1.ROLE_NEW AND( TM.TLNAME IS NULL OR TM.TLNAME='')
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
	WHERE TM1.TLNAME=@p_USER_LOGIN AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)

	INSERT INTO @AUTHOR_DVDM
	(
	    ROLE_ID,
	    BRANCH_ID,
	    DEP_ID,
	    DVDM_ID
	)
	SELECT TU.RoleName,TU.TLSUBBRID,TU.SECUR_CODE,PC.DVDM_ID FROM dbo.TL_USER TU 
	LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
	LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
	WHERE TU.TLNANME=@p_USER_LOGIN
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TU.TLSUBBRID,TU.SECUR_CODE,PC.DVDM_ID FROM dbo.TL_USER TU 
	LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
	LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
	LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
	WHERE TU.TLNANME=@p_USER_LOGIN
	UNION ALL
	SELECT TU.ROLE_NEW RoleName,TU.BRANCH_ID TLSUBBRID,TU.DEP_ID SECUR_CODE,PC.DVDM_ID FROM dbo.TL_SYS_ROLE_MAPPING TU 
	LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.BRANCH_ID AND DT.DEP_ID=TU.DEP_ID
	LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
	WHERE TU.TLNAME=@p_USER_LOGIN AND CAST(EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TU.BRANCH_ID TLSUBBRID,TU.DEP_ID SECUR_CODE,PC.DVDM_ID FROM dbo.TL_SYS_ROLE_MAPPING TU 
	LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.BRANCH_ID AND DT.DEP_ID=TU.DEP_ID
	LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
	LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.ROLE_NEW AND( TM.TLNAME IS NULL OR TM.TLNAME='')
	WHERE TU.TLNAME=@p_USER_LOGIN AND CAST(TU.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)

	INSERT INTO @AUTHOR_DMMS
	(
	    ROLE_ID,
	    BRANCH_ID,
	    DEP_ID,
		DMMS_ID
	)
	SELECT RoleName,TLSUBBRID,SECUR_CODE,MS.DMMS_ID FROM dbo.TL_USER TU
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
	LEFT JOIN dbo.CM_DMMS MS ON MS.BRANCH_ID = CB.BRANCH_ID AND (TU.SECUR_CODE=MS.DEP_ID OR CB.BRANCH_TYPE <>'HS')
	WHERE TLNANME=@p_USER_LOGIN
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TLSUBBRID,SECUR_CODE,MS.DMMS_ID FROM dbo.TL_USER TU
	LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
	LEFT JOIN dbo.CM_DMMS MS ON MS.BRANCH_ID = CB.BRANCH_ID AND (TU.SECUR_CODE=MS.DEP_ID OR CB.BRANCH_TYPE <>'HS')
	WHERE TLNANME=@p_USER_LOGIN
	UNION ALL
	SELECT TM1.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,MS.DMMS_ID FROM dbo.TL_SYS_ROLE_MAPPING TM1
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
	LEFT JOIN dbo.CM_DMMS MS ON MS.BRANCH_ID = CB.BRANCH_ID AND (TM1.DEP_ID=MS.DEP_ID OR CB.BRANCH_TYPE <>'HS')
	WHERE TM1.TLNAME=@p_USER_LOGIN AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,MS.DMMS_ID FROM dbo.TL_SYS_ROLE_MAPPING TM1
	LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TM1.ROLE_NEW AND( TM.TLNAME IS NULL OR TM.TLNAME='')
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
	LEFT JOIN dbo.CM_DMMS MS ON MS.BRANCH_ID = CB.BRANCH_ID AND (TM1.DEP_ID=MS.DEP_ID OR CB.BRANCH_TYPE <>'HS')
	WHERE TM1.TLNAME=@p_USER_LOGIN AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)

	IF(@TYPE='ADV_PAY')
	BEGIN
		IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS ='A' AND REQ_PAY_ID =@p_REQ_ID))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' ErrorDesc
			RETURN '-1'
		END
		IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND  ROLE_USER IN (SELECT * FROM @TABLE_ROLE)) )
		BEGIN
			IF(NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='C' AND ROLE_USER IN (SELECT * FROM @TABLE_ROLE)))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, '' ErrorDesc
				RETURN '-1'
			END
		END
	END
	ELSE
	BEGIN
		IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_ID AND STATUS_JOB ='C' AND TLNAME =@p_USER_LOGIN))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '0' as Result, '' ErrorDesc
			RETURN '0'
		END
		IF(NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS PR WHERE PR.REQ_ID =@p_REQ_ID AND STATUS ='C' AND (
									EXISTS(SELECT * FROM @AUTHOR AUTH WHERE AUTH.ROLE_ID=PR.ROLE_USER AND AUTH.BRANCH_ID=PR.BRANCH_ID AND (AUTH.DEP_ID=PR.DEP_ID OR AUTH.BRANCH_TYPE<>'HS'))
									OR EXISTS(SELECT  AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PR.ROLE_USER AND PR.DVDM_ID=AUTH.DVDM_ID)
									OR EXISTS(SELECT  AUTH.ROLE_ID FROM @AUTHOR_DMMS AUTH WHERE AUTH.ROLE_ID=PR.ROLE_USER AND EXISTS (SELECT * FROM dbo.TR_REQUEST_DOC A  WHERE A.REQ_ID=@p_REQ_ID AND A.DMMS_ID=AUTH.DMMS_ID AND A.PROCESS_ID='DMMS'))
									OR EXISTS(SELECT  AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PR.ROLE_USER AND EXISTS (SELECT * FROM dbo.TR_REQUEST_DOC A  WHERE A.REQ_ID=@p_REQ_ID AND A.DMMS_ID=AUTH.DVDM_ID AND A.PROCESS_ID='DMMS'))
									OR EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PR.ID AND  TLNAME=@p_USER_LOGIN AND TYPE_JOB='KS' AND STATUS_JOB='C')
									OR ( (PR.BRANCH_ID IS NULL OR PR.BRANCH_ID='') AND (PR.DVDM_ID ='' OR PR.DVDM_ID  IS NULL) )
									)))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' ErrorDesc
			RETURN '-1'
		END
		IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND PROCESS_ID ='APPROVE'))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' ErrorDesc
			RETURN '-1'
		END
		IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND PROCESS_ID ='APPROVE'))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' ErrorDesc
			RETURN '-1'
		END
	END
	COMMIT TRANSACTION
	SELECT '0' as Result, '' ErrorDesc
	RETURN '0'

	ABORT:
	BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' ErrorDesc
			RETURN '-1'
	End