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
	-- LUCTV BO SUNG TRUONG DON VI KIEM TRA NEU VUOT QUA HAN MUC CUA TO TRINH THI KHONG CHO DUYET
	DECLARE @TTCT_ID VARCHAR(15),@TTCT_CODE VARCHAR(15)
	SET @TTCT_ID =(SELECT PL_REQ_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)
	SET @TTCT_CODE =(SELECT REQ_CODE FROM PL_REQUEST_DOC WHERE REQ_ID =@TTCT_ID)
	-- THONG  TIN TONG SO TIEN PYC MS DANG LINK TOI TO TRINH
	DECLARE @TONG_PYCMS DECIMAL(18,2) =0, @TONG_TTCT DECIMAL(18,2) =0
	SET @TONG_PYCMS  = (SELECT SUM(TOTAL_AMT) FROM TR_REQUEST_DOC_DT WHERE  REQ_DOC_ID IN (SELECT PL_REQ_ID FROM TR_REQUEST_DOC WHERE PL_REQ_ID =@TTCT_ID))
	SET @TONG_TTCT =(SELECT TOTAL_AMT FROM PL_REQUEST_DOC WHERE REQ_ID =@TTCT_ID)
	IF(@TONG_PYCMS>@TONG_TTCT)
	BEGIN
		ROLLBACK TRANSACTION
			SELECT '-1' Result, N'Tổng số tiền sử dụng ngân sách của phiếu yêu cầu mua sắm đang vượt hạn mức trình chủ trương theo tờ trình số: '+@TTCT_CODE +
			+CHAR(10)+ N'Số tiền sử dụng ngân sách cho phiếu yêu cầu mua sắm hiện tại vượt '+FORMAT(@TONG_PYCMS -@TONG_TTCT,'#,#', 'vi-VN') 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
	    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(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  @IS_NEXT=1 OR @BRANCH_CREATE=@BRANCH_HS)
		BEGIN
			IF(@TOTAL_AMT<=@LIMIT_VALUE AND NOT EXISTS(SELECT DVDM_ID FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID
						GROUP BY DVDM_ID HAVING COUNT(REQDT_ID)>1))
			BEGIN
				SET @DMMS_ID= (SELECT DISTINCT DVDM_ID FROM dbo.TR_REQUEST_DOC_DT 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
			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
		ELSE
		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
		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