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

ALTER PROCEDURE [dbo].[TR_REQUEST_DOC_App]

    @p_REQ_ID VARCHAR(15) = NULL,

	@p_AUTH_STATUS VARCHAR(1) = NULL,

	@p_CHECKER_ID varchar(15)  = NULL,

	@p_APPROVE_DT DATETIME = NULL,

	@p_ROLE_LOGIN VARCHAR(50) = NULL,

	@p_BRANCH_LOGIN VARCHAR(15),

	@p_PROCESS_DES NVARCHAR(500)

AS

	--Validation is here

DECLARE @ERRORSYS NVARCHAR(15) = '' 

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

	SET @ERRORSYS = 'REQ-00002'

IF @ERRORSYS <> '' 

BEGIN

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

	RETURN '0'

END 

BEGIN TRANSACTION

	-- LUCTV 20052020 BO SUNG CHECK NEU TRA VE THI KHONG DUYET DUOC

	IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND AUTH_STATUS ='R'))

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' Result, N'Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_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

	--- PHE DUYET TRUNG GIAN

	DECLARE @BRANCH_ID VARCHAR(20), @DEP_ID VARCHAR(20),@BRANCH_CREATE_N VARCHAR(20) ,@DEP_CREATE_N VARCHAR(20),@BRANCH_TYPE VARCHAR(10),

	@BRANCH_CREATE_TYPE VARCHAR(10)

	SELECT @BRANCH_CREATE_N=BRANCH_CREATE,@DEP_CREATE_N=DEP_CREATE FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID

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

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

	IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND SIGN_USER =@p_CHECKER_ID AND PROCESS_ID ='SIGN'))

	BEGIN

		DELETE FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID

		INSERT INTO dbo.PL_PROCESS

		(

			REQ_ID,

			PROCESS_ID,

			CHECKER_ID,

			APPROVE_DT,

			PROCESS_DESC,NOTES

		)

		VALUES

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

			'SIGN',        -- PROCESS_ID - varchar(10)

			@p_CHECKER_ID,        -- CHECKER_ID - varchar(15)

			@p_APPROVE_DT , -- APPROVE_DT - datetime

			N'Cấp phê duyệt trung gian xác nhận phiếu yêu cầu mua sắm',

			N'Cấp phê duyệt trung gian'

		)

	--- DUA CAP PHE DUYET TRUONG DON VI

		IF(@BRANCH_CREATE_TYPE='PGD')

			SET @BRANCH_CREATE_N=(SELECT FATHER_ID  FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE_N)

	    INSERT INTO dbo.PL_REQUEST_PROCESS

		(

		    REQ_ID,

		    PROCESS_ID,

		    STATUS,

		    ROLE_USER,

		    BRANCH_ID,

			DEP_ID,

		    CHECKER_ID,

		    APPROVE_DT,

		    PARENT_PROCESS_ID,

		    IS_LEAF,

		    COST_ID,

		    DVDM_ID,

		    NOTES,

		    IS_HAS_CHILD

		)

		VALUES

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

		    'APPNEW',        -- PROCESS_ID - varchar(10)

		    'C',        -- STATUS - varchar(5)

		    'GDDV',        -- ROLE_USER - varchar(50)

		    @BRANCH_CREATE_N,  

			@DEP_CREATE_N,      -- BRANCH_ID - varchar(15)

		    '',        -- CHECKER_ID - varchar(15)

		    NULL,      -- APPROVE_DT - datetime

		    '',        -- PARENT_PROCESS_ID - varchar(10)

		    'N',        -- IS_LEAF - varchar(1)

		    '',        -- COST_ID - varchar(15)

		    '',        -- DVDM_ID - varchar(15)

		    N'Chờ trưởng đơn vị phê duyệt',       -- NOTES - nvarchar(500)

		    NULL       -- IS_HAS_CHILD - bit

		 )

	--- UPDATE PROCESS_ID VE APP_NEW

	UPDATE TR_REQUEST_DOC SET PROCESS_ID ='APPNEW' WHERE REQ_ID =@p_REQ_ID

	END

	ELSE

	--- PHE DUYET GIU NGUYEN NHU THUONG

	BEGIN

		--- KIEM TRA XEM DA PHE DUYET TRUNG GIAN HAY CHUA

		IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND SIGN_USER IS NOT NULL AND SIGN_USER <> ''))

		BEGIN

			IF(NOT EXISTS (SELECT * FROM PL_PROCESS WHERE PROCESS_ID='SIGN' AND REQ_ID =@p_REQ_ID))

			BEGIN

				ROLLBACK TRANSACTION

				SELECT -1 Result, N'Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' đang đợi cấp phê duyệt trung gian xác nhận. Vui lòng đợi nhân viên '+(SELECT SIGN_USER FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)+' xác nhận phiếu!' ErrorDesc

				RETURN -1

			END

		END

		DELETE FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID

		DECLARE @CURR_PROCESS VARCHAR(20),@TOTAL_AMT DECIMAL(18,2),

		 @LIMIT_VALUE DECIMAL(18,0),@IS_NEXT BIT,@DMMS_ID VARCHAR(20),@BRANCH_CREATE VARCHAR(20),@BRANCH_HS VARCHAR(20),@DEP_CREATE VARCHAR(15)

		SET @BRANCH_HS= (SELECT TOP 1 BRANCH_ID FROM dbo.CM_BRANCH WHERE BRANCH_TYPE='HS')

		SET @LIMIT_VALUE=(SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE ROLE_ID='GDDV' AND LIMIT_TYPE='PYC_DVMC')

		SET @TOTAL_AMT=(SELECT TOTAL_AMT FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)

		SET @CURR_PROCESS = (SELECT PROCESS_ID FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)

		UPDATE  dbo.TR_REQUEST_DOC SET AUTH_STATUS='A',REQ_TYPE=0, CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=CAST(@p_APPROVE_DT AS DATE),BRANCH_DVMS=BRANCH_CREATE, PROCESS_ID='DMMS' WHERE REQ_ID=@p_REQ_ID

	--	SET @IS_NEXT=(SELECT dbo.FN_CHECK_LIMIT_TR_REQ(@p_REQ_ID,'GDDV'))

		SET @BRANCH_CREATE=(SELECT BRANCH_CREATE FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)

		SET @DEP_CREATE=(SELECT DEP_CREATE FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)

		IF(@BRANCH_CREATE_TYPE='PGD')

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

	IF(EXISTS(	SELECT RD.REQDT_ID FROM dbo.TR_REQUEST_DOC_DT RD 

		LEFT JOIN dbo.SYS_HH_GROUP_LIMIT HGL ON RD.HANGHOA_ID=HGL.HH_ID

		LEFT JOIN dbo.SYS_GROUP_LIMIT GL ON GL.GROUP_ID=HGL.GROUP_LIMIT_ID

		WHERE GL.IS_HO=1 AND RD.REQ_DOC_ID=@p_REQ_ID)  OR @BRANCH_CREATE=@BRANCH_HS)

		BEGIN

			IF(@TOTAL_AMT<=@LIMIT_VALUE AND NOT EXISTS(SELECT HC.DVCM_ID FROM dbo.TR_REQUEST_DOC_DT DT 

														LEFT JOIN dbo.CM_HANGHOA_DVCM HC ON HC.HH_ID=DT.HANGHOA_ID WHERE REQ_DOC_ID=@p_REQ_ID

						GROUP BY HC.DVCM_ID HAVING COUNT(REQDT_ID)>1))

			BEGIN

				SET @DMMS_ID= (SELECT DISTINCT HC.DVCM_ID FROM dbo.TR_REQUEST_DOC_DT DT 

														LEFT JOIN dbo.CM_HANGHOA_DVCM HC ON HC.HH_ID=DT.HANGHOA_ID WHERE REQ_DOC_ID=@p_REQ_ID)

				UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,REQ_TYPE=2 WHERE REQ_ID=@p_REQ_ID

			END

			ELSE

			BEGIN

				SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS)

				UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID

			END

		END

		ELSE

		BEGIN

			IF(@TOTAL_AMT<=@LIMIT_VALUE)

			BEGIN

				SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_CREATE)

				UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_CREATE,DMMS_ID=@DMMS_ID,REQ_TYPE=0 WHERE REQ_ID=@p_REQ_ID

			END

			ELSE

			BEGIN

				SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS)

				UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID

			END

		END

		INSERT INTO dbo.PL_REQUEST_PROCESS

		(

			REQ_ID,

			PROCESS_ID,

			STATUS,

			ROLE_USER,

			BRANCH_ID,

			CHECKER_ID,

			APPROVE_DT,

			PARENT_PROCESS_ID,

			IS_LEAF,

			COST_ID,

			DVDM_ID,

			NOTES,

			IS_HAS_CHILD,

			DEP_ID

		)

		VALUES

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

			'APPNEW',        -- PROCESS_ID - varchar(10)

			'P',        -- STATUS - varchar(5)

			'GDDV',        -- ROLE_USER - varchar(50)

			@BRANCH_CREATE,        -- BRANCH_ID - varchar(15)

			@p_CHECKER_ID,        -- CHECKER_ID - varchar(15)

			GETDATE(), -- APPROVE_DT - datetime

			'',        -- PARENT_PROCESS_ID - varchar(10)

			'',        -- IS_LEAF - varchar(1)

			'',        -- COST_ID - varchar(15)

			'',        -- DVDM_ID - varchar(15)

			N'Trưởng đơn vị phê duyệt',       -- NOTES - nvarchar(500)

			0 ,      -- IS_HAS_CHILD - bit

			@DEP_CREATE

			)

			INSERT INTO dbo.PL_REQUEST_PROCESS

		(

			REQ_ID,

			PROCESS_ID,

			STATUS,

			ROLE_USER,

			BRANCH_ID,

			CHECKER_ID,

			APPROVE_DT,

			PARENT_PROCESS_ID,

			IS_LEAF,

			COST_ID,

			DVDM_ID,

			NOTES,

			IS_HAS_CHILD

		)

		VALUES

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

			'DMMS',        -- PROCESS_ID - varchar(10)

			'C',        -- STATUS - varchar(5)

			'GDDV',        -- ROLE_USER - varchar(50)

			'',        -- BRANCH_ID - varchar(15)

			'',        -- CHECKER_ID - varchar(15)

			GETDATE(), -- APPROVE_DT - datetime

			'APPNEW',        -- PARENT_PROCESS_ID - varchar(10)

			'',        -- IS_LEAF - varchar(1)

			'',        -- COST_ID - varchar(15)

			'',        -- DVDM_ID - varchar(15)

			N'Chờ đầu mối mua sắm xử lý',       -- NOTES - nvarchar(500)

			1       -- IS_HAS_CHILD - bit

		 )

		INSERT INTO dbo.PL_PROCESS

		(

			REQ_ID,

			PROCESS_ID,

			CHECKER_ID,

			APPROVE_DT,

			PROCESS_DESC,NOTES

		)

		VALUES

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

			'APPNEW',        -- PROCESS_ID - varchar(10)

			@p_CHECKER_ID,        -- CHECKER_ID - varchar(15)

			@p_APPROVE_DT , -- APPROVE_DT - datetime

			@p_PROCESS_DES,

			 N'Trưởng đơn vị phê duyệt'        -- PROCESS_DESC - nvarchar(1000)

		)

	END

	IF @@Error <> 0 GOTO ABORT

COMMIT TRANSACTION

IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE AUTH_STATUS ='A' AND REQ_ID =@p_REQ_ID))

BEGIN

	SELECT '0' as Result, '' ErrorDesc

	RETURN '0'

END

ELSE

BEGIN

	SELECT '4' as Result, N'Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' đã được đợi cấp phê duyệt trung gian xác nhận thành công. Vui lòng đợi trưởng đơn vị phê duyệt' ErrorDesc

	RETURN '4'

END

ABORT:

BEGIN

		PRINT 'ERROR'

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ErrorDesc

		RETURN '-1'

End