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

ALTER   PROCEDURE [dbo].[PL_REQUEST_PROCESS_App]

    @p_REQ_ID VARCHAR(15) = NULL,

	@p_AUTH_STATUS VARCHAR(1) = NULL,

	@p_CHECKER_ID varchar(15)  = NULL,

	@p_APPROVE_DT VARCHAR(20) = NULL,

	@p_ROLE_LOGIN VARCHAR(50) = NULL,

	@p_BRANCH_LOGIN VARCHAR(15),

	@p_PROCESS_DESC NVARCHAR(MAX),

	@p_IS_AUTHORITY BIT = 0

AS

--SET @p_APPROVE_DT= CAST(@p_APPROVE_DT AS DATE)

	--Validation is here

DECLARE @ERRORSYS NVARCHAR(15) = '' 

  IF ( NOT EXISTS ( SELECT * FROM PL_REQUEST_DOC WHERE  REQ_ID = @p_REQ_ID))

	SET @ERRORSYS = 'REQ-00002'

IF @ERRORSYS <> '' 

BEGIN

    --ROLLBACK TRANSACTION

	SELECT ErrorCode Result, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS

	RETURN '0'

END 

-- LUCTV 06 - 05 - 2021 BO SUNG UY QUYEN KIEM NHIEM

		DECLARE @TABLE_ROLE TABLE (ROLE_AUTH VARCHAR(50))

		INSERT INTO @TABLE_ROLE SELECT (SELECT ROLENAME FROM TL_USER WHERE TLNANME =@p_CHECKER_ID)

		INSERT INTO @TABLE_ROLE SELECT ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =(SELECT ROLENAME FROM TL_USER WHERE TLNANME =@p_CHECKER_ID) 

		AND (CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) OR EFF_DATE IS NULL)

		AND (CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) OR EXP_DATE IS NULL)

-- KHAI BAO UY QUYEN DON VI DAU MOI

			DECLARE @AUTHOR_DVDM TABLE

		(

		ROLE_ID VARCHAR(100),

		BRANCH_ID VARCHAR(20),

		DEP_ID VARCHAR(20),

		DVDM_ID VARCHAR(20)

		)

	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_CHECKER_ID

	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_CHECKER_ID

	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_CHECKER_ID 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_CHECKER_ID AND CAST(TU.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)

-- END

BEGIN TRANSACTION

		IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_ID AND STATUS_JOB ='R') OR (EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND AUTH_STATUS ='R'))

		OR(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='R')))

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' as Result,N'Tờ trình chủ trương số: '+(SELECT REQ_CODE FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)+N' đang bị từ chối. Vui lòng đợi nhân viên xử lý phiếu và gửi phê duyệt lại!' ErrorDesc

			RETURN '-1'

		END

	--UPDATE dbo.PL_REQUEST_TRANSFER SET AUTH_STATUS = @P_AUTH_STATUS, CHECKER_ID = @P_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@P_APPROVE_DT,103)

	--WHERE REQ_DOC_ID = @p_REQ_ID AND FR_BRN_ID IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_BRANCH_LOGIN))

	 DECLARE

	@Result VARCHAR(5),

	@PROCESS_CURR VARCHAR(10),

	@STEP_CURR INT,

	@STEP_NEXT INT,

	@PROCESS_NEXT VARCHAR(10),

	@ROLE_USER_NOTIFI VARCHAR(50),

	@DEP_ID VARCHAR(15),

	@IS_LEAF VARCHAR(1),

	@NOTES NVARCHAR(500),

	@IS_NEXT BIT,@ROLE_USER VARCHAR(20),@ROLE_NEXT VARCHAR(20),@LIMTT_MAX  DECIMAL(18,2),@LIMIT_APP DECIMAL(18,2) ,@KHOI_ID_TF VARCHAR(20),@TOTAL_TRANSFER DECIMAL(18,2),@PROCESS_ID_NEXT VARCHAR(20),

	@IS_NEXT_CDT BIT,

	@TOTAL_AMT DECIMAL(18,2),

	@STEP_PARENT VARCHAR(20),

	@NOTES_CDT VARCHAR(20),

	@ROLE_CDT VARCHAR(20),

	@DVDM_CDT VARCHAR(20),

	@LIMIT_VALUE_CDT DECIMAL(18,2),

	@DVDM_ID_TT VARCHAR(20),

	@TOTAL_AMT_GD DECIMAL(18,2),

	@BRANCH_CREATE VARCHAR(15),

	@BRANCH_CREATE_TYPE VARCHAR(15),

	@DEP_CREATE VARCHAR(15),

	@BRANCH_PARENT VARCHAR(15)

	DECLARE @PROCESS_ID VARCHAR(5),@DVDM_NAME NVARCHAR(20),@ROLE_ID VARCHAR(20),@DVDM_ID_CDT VARCHAR(20)

	SET @DEP_ID =(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_CHECKER_ID)

	SET @PROCESS_CURR= (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C')

	SET @PROCESS_NEXT=(SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@PROCESS_CURR)

	DECLARE @DATA_DVDM TABLE

(

    DVDM_ID VARCHAR(20),

    TOTAL_AMT DECIMAL(12, 0),

	IS_PTGD BIT

);

		SET @NOTES =(SELECT CONTENT FROM dbo.CM_ALLCODE WHERE (@PROCESS_CURR LIKE CDVAL + '%') AND CDNAME='PROCESS_ID' AND CDTYPE='REQ')   

	DECLARE @DVDM_ID TABLE (

		DVDM_ID VARCHAR(15)

	)

DECLARE  @ERROR BIT ,@EROOR_DES NVARCHAR(500)

IF(@PROCESS_CURR LIKE '%_DC')

BEGIN

	SELECT @ERROR=ERROR,

       @EROOR_DES=ERROR_DES FROM dbo.FN_CHECK_VALIDATE_APP(@p_REQ_ID,'PDDC','PL_REQUEST_DOC',@p_CHECKER_ID,@PROCESS_CURR)

		IF(@ERROR=1)

		BEGIN

		 ROLLBACK TRANSACTION;

		SELECT '-1'  Result,

           @EROOR_DES ErrorDesc

    RETURN '0';

	END

END

SELECT @ERROR=ERROR,

       @EROOR_DES=ERROR_DES FROM dbo.FN_CHECK_VALIDATE_APP(@p_REQ_ID,'PDTT','PL_REQUEST_DOC',@p_CHECKER_ID,@PROCESS_CURR)

		IF(@ERROR=1)

		BEGIN

		 ROLLBACK TRANSACTION;

		SELECT '-1'  Result,

           @EROOR_DES ErrorDesc

    RETURN '0';

END

	INSERT INTO @DATA_DVDM

	SELECT KHOI_ID,

		   SUM(TOTAL_AMT) AS TOTAL_AMT,DM.IS_PTGD

	FROM dbo.PL_REQUEST_DOC_DT DT

	LEFT JOIN CM_DVDM DM ON DM.DVDM_ID=DT.KHOI_ID AND DM.IS_KHOI=1

	WHERE REQ_ID = @p_REQ_ID AND DT.KHOI_ID IS NOT NULL AND DT.KHOI_ID <>''

	GROUP BY KHOI_ID,DM.IS_PTGD;

	SET @DVDM_CDT = (SELECT DVDM_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='CDT')

	INSERT INTO @DVDM_ID

	SELECT DVDM_ID FROM dbo.PL_COSTCENTER WHERE COST_ID IN (SELECT COST_ID FROM dbo.PL_COSTCENTER_DT WHERE DEP_ID=@DEP_ID AND BRANCH_ID=@p_BRANCH_LOGIN) GROUP BY DVDM_ID

	IF(NOT EXISTS (SELECT REQ_ID FROM dbo.PL_REQUEST_PROCESS WHERE STATUS='C' AND  PROCESS_ID=@PROCESS_CURR AND 

	REQ_ID=@p_REQ_ID AND (EXISTS(SELECT ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE  AUTH.ROLE_ID=ROLE_USER AND ( AUTH.DVDM_ID = PL_REQUEST_PROCESS.DVDM_ID OR PL_REQUEST_PROCESS.DVDM_ID ='' OR PL_REQUEST_PROCESS.DVDM_ID IS NULL)))))

	BEGIN

		ROLLBACK TRANSACTION

		SELECT '1' as Result , N'Bạn không nằm trong nhóm quyền được phê duyệt tờ trình này' AS  ROLE_NOTIFI, '' ErrorDesc

		RETURN '1'

	END		

	UPDATE dbo.PL_REQUEST_PROCESS SET [STATUS]='P',NOTES=@NOTES+N' đã phê duyệt',CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=CONVERT(DATETIME,@p_APPROVE_DT,103) WHERE PROCESS_ID=@PROCESS_CURR AND REQ_ID=@p_REQ_ID

	AND (EXISTS(SELECT ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE  AUTH.ROLE_ID=ROLE_USER AND ( AUTH.DVDM_ID = PL_REQUEST_PROCESS.DVDM_ID OR PL_REQUEST_PROCESS.DVDM_ID ='' OR PL_REQUEST_PROCESS.DVDM_ID IS NULL)))

	IF(@PROCESS_CURR <>'DVCM' AND @PROCESS_CURR <>'DVDM' AND @PROCESS_CURR <>'TC')

	BEGIN

		UPDATE dbo.PL_REQUEST_DOC_DT SET CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=CONVERT(DATETIME,@p_APPROVE_DT,103) WHERE REQ_ID=@p_REQ_ID AND 

		( KHOI_ID IN (SELECT DVDM_ID FROM @DVDM_ID) OR @p_ROLE_LOGIN='TGD' OR @p_ROLE_LOGIN='HDQT' OR @p_ROLE_LOGIN='GDDV' OR 

		@p_ROLE_LOGIN IN (SELECT ROLE_OLD FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_NEW='GDDV') )

	END

	IF(@PROCESS_CURR LIKE '%_DC')

	BEGIN

		UPDATE dbo.PL_REQUEST_TRANSFER SET CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=CONVERT(DATETIME,@p_APPROVE_DT,103) WHERE REQ_DOC_ID=@p_REQ_ID AND  ( FR_KHOI_ID IN (SELECT DVDM_ID FROM @DVDM_ID) OR @p_ROLE_LOGIN='TGD' OR @p_ROLE_LOGIN='HDQT' OR @p_ROLE_LOGIN IN (SELECT ROLE_OLD FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_NEW='GDDV' ) )

	END

	INSERT INTO dbo.PL_PROCESS

			(

				REQ_ID,

				PROCESS_ID,

				CHECKER_ID,

				APPROVE_DT,

				PROCESS_DESC,NOTES

			)

			VALUES

			(   @p_REQ_ID,        -- REQ_ID - varchar(15)

				@PROCESS_CURR,        -- PROCESS_ID - varchar(10)

				@p_CHECKER_ID,        -- CHECKER_ID - varchar(15)

				GETDATE(), -- APPROVE_DT - datetime

				@p_PROCESS_DESC ,@NOTES+N' đã phê duyệt'       -- PROCESS_DESC - nvarchar(1000)

			)	

	SELECT @BRANCH_CREATE=BRANCH_CREATE,@DEP_CREATE=DEP_CREATE FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID

	SET @BRANCH_CREATE_TYPE=(SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE)

	SET @Result='1'

	IF(NOT EXISTS(SELECT PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@PROCESS_CURR AND [STATUS] <> 'P'))

	BEGIN	

			--- LUCTV 18.10.22 KIEM TRA NEU WORKFLOW CO PTGD KHOI HO TRO THI REMOVE KHOI QUY TRINH

			IF((SELECT COUNT(*) FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND PROCESS_ID ='PTGDK_TT')=1)

			BEGIN

				IF(EXISTS(SELECT *  FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND PROCESS_ID ='PTGDK_TT' AND DVDM_ID ='DM0000000000015'))

				BEGIN

					DELETE FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND PROCESS_ID ='PTGDK_TT' AND DVDM_ID ='DM0000000000015'

					UPDATE PL_REQUEST_PROCESS SET PARENT_PROCESS_ID =@PROCESS_CURR WHERE REQ_ID =@p_REQ_ID AND PARENT_PROCESS_ID ='PTGDK_TT'

					SET @PROCESS_NEXT=(SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@PROCESS_CURR)

				END

			END

			ELSE

			BEGIN

				IF((SELECT COUNT(*) FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND PROCESS_ID ='PTGDK_TT')=1)

			BEGIN

				IF(EXISTS(SELECT *  FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND PROCESS_ID ='PTGDK_TT' AND DVDM_ID ='DM0000000000015'))

				BEGIN

					DELETE FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND PROCESS_ID ='PTGDK_TT' AND DVDM_ID ='DM0000000000015'

					SET @PROCESS_NEXT=(SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@PROCESS_CURR)

				END

			END

			END

			--- END LUCTV 18.10.22

			UPDATE dbo.PL_REQUEST_PROCESS SET [STATUS]='C' WHERE PARENT_PROCESS_ID=@PROCESS_CURR AND REQ_ID=@p_REQ_ID

			UPDATE dbo.PL_REQUEST_DOC SET PROCESS_ID=@PROCESS_NEXT WHERE REQ_ID=@p_REQ_ID

				SET @IS_LEAF=(SELECT TOP 1 IS_LEAF FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@PROCESS_CURR)

			IF (@p_ROLE_LOGIN = 'HDQT') UPDATE dbo.PL_REQUEST_DOC SET IS_AUTHORITY = @p_IS_AUTHORITY WHERE REQ_ID = @p_REQ_ID

			IF (@p_ROLE_LOGIN = 'TGD') UPDATE dbo.PL_REQUEST_DOC SET TGD_NOTES = @p_PROCESS_DESC WHERE REQ_ID = @p_REQ_ID

			IF(@IS_LEAF='Y')

			BEGIN

					print 'haha'

					EXEC dbo.PL_REQ_DOC_UPDATE_AFTER_APPROVE @p_REQ_ID = @p_REQ_ID 

					EXEC dbo.PL_REQ_DOC_Ins_To_TR_REQ_DOC @p_PL_REQ_ID = @p_REQ_ID

				SET @Result='0'

			END

	END

	IF @@Error <> 0 GOTO ABORT

COMMIT TRANSACTION

-- BAY GIỜ KHÔNG CẦN PHÂN CHIA XÁC NHẬN - DUYỆT NỮA. KHI CẤP LÃNH ĐẠO (GĐK, PTGĐ, TGĐ) BẤM VÀO NÚT DUYỆT THÌ THÔNG BÁO DUYỆT THÀNH CÔNG. KHÔNG CẦN QUAN TÂM CẤP CUỐI CÙNG

SELECT '0' as Result , @ROLE_USER AS  ROLE_NOTIFI, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ROLE_NOTIFI , ERROR_MESSAGE() ErrorDesc

		RETURN '-1'

End