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)
	-- LUCTV 08122020 KIỂM TRA NẾU TỜ TRÌNH LINK TỚI PYCMS LÀ TỜ TRÌNH TOÀN HÀNG THÌ ĐMMS GIAO CHO ĐƠN VỊ TỰ MUA SẮM
	DECLARE @PL_REQ_REF VARCHAR(15), @ISCHECK_ALL VARCHAR(1)
	SET @PL_REQ_REF =(SELECT TOP 1 PL_REQ_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)
	SET @ISCHECK_ALL =(SELECT TOP 1 IS_CHECKALL FROM PL_REQUEST_DOC WHERE REQ_ID =@PL_REQ_REF)
	-- LUCTV 08122020: END
	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
		DECLARE @ROLE_USER_LOGIN VARCHAR(15)=''
		SET @ROLE_USER_LOGIN =(SELECT RoleName FROM TL_USER WHERE TLNANME =@p_CHECKER_ID)
		--- 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
			IF(@p_CHECKER_ID = (SELECT SIGN_USER FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID) AND @BRANCH_CREATE_TYPE ='HS')
			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 trưởng đơn vị phê duyệt. Bạn không có thẩm quyền phê duyệt cấp trưởng đơn vị! Vui lòng xem lịch sử xử lý phiếu' ErrorDesc
				RETURN '-1'
			END
		END
		-- LUCTV: 08 12 2020: KIỂM TRA NẾU CẤP DUYỆT KHÔNG NẰM TRONG CẤP TP, PP THÌ KHÔNG CHO DUYỆT TRƯỞNG ĐƠN VỊ
		--IF(
		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), @LIMIT_DVCM 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  LIMIT_TYPE='PYCMS_DVKD')
		SET @LIMIT_DVCM=(SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE  LIMIT_TYPE='PYCMS_DVCM')
		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 BRANCH_ID  FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE)

IF(EXISTS (SELECT BRANCH_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE AND BRANCH_TYPE='HS'))
BEGIN
    IF(@TOTAL_AMT <= @LIMIT_DVCM)
	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,DMMS_ID=@DMMS_ID,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(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))
	BEGIN
		    IF(@TOTAL_AMT <= @LIMIT_DVCM)
			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,DMMS_ID=@DMMS_ID,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
END
-- LUCTV 08122020 KIỂM TRA NẾU TỜ TRÌNH LINK TỚI PYCMS LÀ TỜ TRÌNH TOÀN HÀNG THÌ ĐMMS GIAO CHO ĐƠN VỊ TỰ MUA SẮM
IF(@ISCHECK_ALL ='1')
BEGIN
	SET @DMMS_ID= (SELECT TOP 1 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
IF(@TOTAL_AMT > 20000000)
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

-- LUCTV 08122020

	--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,DMMS_ID=@DMMS_ID,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