ALTER PROCEDURE [dbo].[PL_REQUEST_DOC_SendApp]
@p_REQ_ID VARCHAR(20),
@p_PROCESS_ID VARCHAR(20),
@p_TLNAME VARCHAR(20),
@p_MAKER_ID VARCHAR(20)
AS
BEGIN TRANSACTION

	IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND (PL_BASED_ID IS  NULL OR PL_BASED_ID ='')))
	BEGIN
	--- KIEM TRA NGAN SACH LUCTV 18052020
	DECLARE  @ERROR BIT ,@EROOR_DES NVARCHAR(500)
	SELECT @ERROR=ERROR, @EROOR_DES=ERROR_DES FROM dbo.FN_CHECK_VALIDATE_APP(@p_REQ_ID,'APPNEW','PL_REQUEST_DOC',@p_TLNAME,@p_PROCESS_ID)
	IF(@ERROR=1)
	BEGIN
	ROLLBACK TRANSACTION;
		SELECT '-1'  Result, @EROOR_DES ErrorDesc
		RETURN '-1';
	END
	-----
	DECLARE @BRANCH_ID VARCHAR(20), @DEP_ID VARCHAR(20),@BRANCH_CREATE VARCHAR(20) ,@DEP_CREATE VARCHAR(20),@BRANCH_TYPE VARCHAR(10),
	@BRANCH_CREATE_TYPE VARCHAR(10)

	IF(EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE  REQ_ID=@p_REQ_ID AND  ( CAST(REQ_DT AS DATE) > CAST(GETDATE() AS DATE))) )
	BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' AS Result ,'' REQ_CODE, ''  REQ_ID, N'Ngày tạo không được lớn hơn ngày hiện tại' ErrorDesc
			RETURN '-1'
	END

	IF(EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE  REQ_ID=@p_REQ_ID AND  (REQ_NAME IS NULL OR REQ_NAME='')) )
	BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' AS Result ,'' REQ_CODE, ''  REQ_ID, N'Tên tờ trình bắt buộc nhập' ErrorDesc
			RETURN '-1'
	END
	IF(EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE  REQ_ID=@p_REQ_ID AND  (REQ_CONTENT IS NULL OR REQ_CONTENT='')) )
	BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' AS Result ,'' REQ_CODE, ''  REQ_ID, N'Nội dung tờ trình bắt buộc nhập' ErrorDesc
			RETURN '-1'
	END
	IF(EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE  REQ_ID=@p_REQ_ID AND ( REQ_REASON IS NULL OR REQ_REASON='')) )
	BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' AS Result ,'' REQ_CODE, ''  REQ_ID, N'Lý do bắt buộc nhập' ErrorDesc
			RETURN '-1'
	END

	IF(NOT EXISTS(SELECT REQDT_ID FROM dbo.PL_REQUEST_DOC_DT WHERE  REQ_ID=@p_REQ_ID ) )
	BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' AS Result ,'' REQ_CODE, ''  REQ_ID, N'Danh sách hàng hóa bắt buộc nhập' ErrorDesc
			RETURN '-1'
	END

	IF(EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND PL_BASED_ID IS NOT NULL AND PL_BASED_ID <>''))
	BEGIN
	DECLARE @PL_BASED_ID VARCHAR(20)
	SET @PL_BASED_ID=(SELECT PL_BASED_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID )
		IF( EXISTS(SELECT DT.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT DT WHERE DT.REQ_ID=@p_REQ_ID AND NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC_DT DTB WHERE DTB.REQ_ID=@PL_BASED_ID AND DTB.TRADE_ID=DT.TRADE_ID AND DTB.HANGHOA_ID=DT.HANGHOA_ID)))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' AS Result ,'' REQ_CODE, ''  REQ_ID, N'Danh sách hàng hóa không có trong tờ trình căn cứ' ErrorDesc
			RETURN '-1'
		END
	END



	IF(EXISTS(SELECT REQDT_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID AND  (HANGHOA_ID IS NULL OR HANGHOA_ID ='')) )
	BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' AS Result ,'' REQ_CODE, ''  REQ_ID, N'Hàng hóa bắt buộc nhập' ErrorDesc
			RETURN '-1'
	END
	IF(EXISTS(SELECT REQDT_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID AND  (GOODS_ID IS NULL OR GOODS_ID ='')) )
	BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' AS Result ,'' REQ_CODE, ''  REQ_ID, N'Hạng mục ngân sách bắt buộc nhập' ErrorDesc
			RETURN '-1'
	END

	IF(EXISTS(SELECT REQ_TRANSFER_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID AND (TOTAL_AMT IS NULL OR TOTAL_AMT = 0)) )
	BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' AS Result ,'' REQ_CODE, ''  REQ_ID, N'Vui lòng nhập số tiền điều chuyển' ErrorDesc
			RETURN '-1'
	END
	SELECT @BRANCH_CREATE=BRANCH_CREATE,@DEP_CREATE=DEP_CREATE FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID
	-- KIEM TRA NEU HINH THUC CHI DINH THAU LA THEO QUY DINH VCCB THI KHONG CHO PHEP CHON NHA CUNG CAP
	IF(EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE TRADE_TYPE ='VCCB' AND SUP_ID <>'' AND SUP_ID IS NOT NULL AND REQ_ID =@p_REQ_ID))
			BEGIN
				SELECT 'REQ-00001' Result, '' REQ_ID, N'Nếu hình thức mua sắm theo quy định VCCB thì bạn không được phép chọn nhà cung cấp. Vui lòng để trống nhà cung cấp' ErrorDesc 
				ROLLBACK TRANSACTION
				RETURN '-1'
	END
	--
	IF(EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE TRADE_TYPE ='CDT' AND ( SUP_ID ='' OR SUP_ID IS NULL) AND REQ_ID =@p_REQ_ID))
			BEGIN
				SELECT 'REQ-00001' Result, '' REQ_ID, N'Nếu hình thức mua sắm chỉ định thầu thì bạn không được phép bỏ trống nhà cung cấp. Vui lòng chọn nhà cung cấp' ErrorDesc 
				ROLLBACK TRANSACTION
				RETURN '-1'
	END
	----
	IF(EXISTS(SELECT REQ_TRANSFER_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID AND FR_BRN_ID=@BRANCH_CREATE AND (FR_GOOD_ID IS NULL OR FR_GOOD_ID='')
	AND ((FR_DEP_ID=@DEP_CREATE) OR ((@DEP_CREATE IS NULL OR @DEP_CREATE='') AND (FR_DEP_ID IS NULL OR FR_DEP_ID=''))) ))
	BEGIN
		ROLLBACK TRANSACTION
			SELECT '-1' AS Result ,'' REQ_CODE, ''  REQ_ID, N'Bạn là đơn vị chuyển ngân sách, vui lòng chọn hạn mục ngân sách chuyển' ErrorDesc
			RETURN '-1'
	END
	IF(EXISTS(SELECT Temp.TRADE_ID FROM (
		SELECT TB.TRADE_ID ,SUM(TB.TOTAL_AMT) AS TOTAL_AMT_EXE,
		SUM(ISNULL(PT.AMT_APP,0) +  ISNULL(PT.AMT_RECEIVE_TF,0) - ISNULL(PT.AMT_TF,0) - ISNULL(PT.AMT_ETM,0)) AS TOTAL_AMT_REMAIN 
		FROM dbo.PL_REQUEST_DOC_DT TB
		LEFT JOIN dbo.PL_TRADEDETAIL PT ON PT.TRADE_ID=TB.TRADE_ID
		WHERE   TB.REQ_ID=@p_REQ_ID AND  TB.REQDT_TYPE='I' 
		GROUP BY TB.TRADE_ID
		)Temp
		WHERE Temp.TOTAL_AMT_EXE > Temp.TOTAL_AMT_REMAIN))
		BEGIN
					ROLLBACK TRANSACTION
					SELECT '-1' AS RESULT , '' REQ_CODE,''  REQ_ID, N'Số tiền sử dụng lớn hơn số tiền dự kiến còn lại của hạng mục trong ngân sách' ErrorDesc
					RETURN '-1'
		END

	IF( EXISTS(SELECT GOODS_ID FROM dbo.PL_REQUEST_DOC_DT WHERE  REQ_ID=@p_REQ_ID AND  REQDT_TYPE='O') AND  NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_TRANSFER WHERE  REQ_DOC_ID=@p_REQ_ID AND  TO_GOOD_ID IN (SELECT GOODS_ID FROM dbo.PL_REQUEST_DOC_DT WHERE  REQ_ID=@p_REQ_ID AND REQDT_TYPE='O')))
		BEGIN
					ROLLBACK TRANSACTION
					SELECT '-1' AS RESULT ,'' REQ_CODE, ''  REQ_ID, N'Hạng mục ngoài ngân sách, vui lòng thêm danh sách điều chuyển ngân sách' ErrorDesc
					RETURN '-1'
		END
		
	IF(EXISTS(SELECT REQDT_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID AND  (PRICE IS NULL OR PRICE=0 OR QUANTITY IS NULL OR QUANTITY=0)) )
	BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' AS Result ,'' REQ_CODE, ''  REQ_ID, N'Số luợng, đơn giá của hàng hóa bắt buộc nhập' ErrorDesc
			RETURN '-1'
	END
	IF(EXISTS(SELECT REQDT_ID FROM dbo.PL_REQUEST_DOC_DT WHERE  REQ_ID=@p_REQ_ID AND  (DESCRIPTION IS NULL OR DESCRIPTION='')) )
	BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' AS Result ,'' REQ_CODE, ''  REQ_ID, N'Quy cách kỹ thuật bắt buộc nhập' ErrorDesc
			RETURN '-1'
	END

	IF(EXISTS(SELECT REQDT_ID FROM dbo.PL_REQUEST_DOC_DT WHERE  REQ_ID=@p_REQ_ID AND  (DESCRIPTION IS NULL OR DESCRIPTION='')) )
	BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' AS Result ,'' REQ_CODE, ''  REQ_ID, N'Quy cách kỹ thuật bắt buộc nhập' ErrorDesc
			RETURN '-1'
	END			
	--DECLARE lstTransfer CURSOR FOR 
	--SELECT FR_GOOD_ID,TO_GOOD_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID
	--OPEN lstTransfer
	--DECLARE @FR_GOOD_ID VARCHAR(20),@TO_GOOD_ID VARCHAR(20)

	--FETCH NEXT FROM lstTransfer INTO @FR_GOOD_ID,@TO_GOOD_ID
	--WHILE @@FETCH_STATUS=0
	--BEGIN
	--	IF(@FR_GOOD_ID IS NOT NULL AND @FR_GOOD_ID<>'')
	--		BEGIN
	--			DECLARE @FR_GD_TYPE VARCHAR(20),@TO_GD_TYPE VARCHAR(20)

	--			SET @FR_GD_TYPE =(SELECT GT.GD_TYPE_ID FROM 
	--			(
	--				SELECT GD_CODE FROM dbo.CM_GOODS WHERE GD_ID=@FR_GOOD_ID
	--			) CG
	--			LEFT JOIN dbo.CM_GOODSTYPE GT ON  CG.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%')


	--			SET @TO_GD_TYPE =(SELECT GT.GD_TYPE_ID FROM 
	--			(
	--				SELECT GD_CODE FROM dbo.CM_GOODS WHERE GD_ID=@TO_GOOD_ID
	--			) CG
	--			LEFT JOIN dbo.CM_GOODSTYPE GT ON  CG.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%')

	--			IF(NOT EXISTS(SELECT * FROM dbo.CM_GOODSTYPE WHERE GD_TYPE_ID= @FR_GD_TYPE AND @TO_GD_TYPE IN (SELECT value FROM dbo.wsiSplit(NOTES,';')) )  )
	--			BEGIN
	--				DECLARE @FR_GD_TYPE_NAME NVARCHAR(100),@TO_GD_TYPE_NAME NVARCHAR(100)
	--				SET @FR_GD_TYPE_NAME=(SELECT GD_TYPE_NAME FROM dbo.CM_GOODSTYPE WHERE GD_TYPE_ID=@FR_GD_TYPE)
	--				SET @TO_GD_TYPE_NAME=(SELECT GD_TYPE_NAME FROM dbo.CM_GOODSTYPE WHERE GD_TYPE_ID=@TO_GD_TYPE)
					
	--					ROLLBACK TRANSACTION
	--					SELECT '-1' AS Result ,'' REQ_CODE, ''  REQ_ID, N'Không được điều chuyển từ ' + @FR_GD_TYPE_NAME +N' sang ' + @TO_GD_TYPE_NAME ErrorDesc
	--					RETURN '-1'
	--					CLOSE lstTransfer
	--					DEALLOCATE lstTransfer
	--			END
	--		END
	--	FETCH NEXT FROM lstTransfer INTO @FR_GOOD_ID,@TO_GOOD_ID
	--END
	--CLOSE lstTransfer
	--DEALLOCATE lstTransfer
	END





	UPDATE dbo.PL_REQUEST_DOC SET AUTH_STATUS='U',PROCESS_ID='APPNEW' WHERE REQ_ID=@p_REQ_ID
	SELECT @BRANCH_ID =BRANCH_ID,@DEP_ID=DEP_ID,@BRANCH_CREATE=BRANCH_CREATE,@DEP_CREATE=DEP_CREATE FROM dbo.PL_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)

	
	--IF(@BRANCH_TYPE='PGD')
	--	SET @BRANCH_ID=(SELECT FATHER_ID  FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_ID)
	
	
	DELETE FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID
	---KIEM TRA XEM CO CAP PHE DUYET TRUNG GIAN HAY KHONG 20-05-2020 LUCTV
	IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND SIGN_USER IS NOT NULL AND SIGN_USER <>''))
	BEGIN
		DECLARE @BRANCH_SIGN_ID VARCHAR(20),@DEP_SIGN_ID VARCHAR(20),@BRANCH_SIGN_TYPE VARCHAR(10),@ROLE_SIGN VARCHAR(20)

		SELECT @BRANCH_SIGN_ID=TLSUBBRID,@DEP_SIGN_ID=SECUR_CODE,@BRANCH_SIGN_TYPE=BRANCH_TYPE,@ROLE_SIGN=RoleName FROM dbo.TL_USER WHERE TLNANME =(SELECT SIGN_USER FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)



		UPDATE dbo.PL_REQUEST_DOC SET AUTH_STATUS='U',PROCESS_ID='SIGN' WHERE REQ_ID=@p_REQ_ID
		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)
		    'SIGN',        -- PROCESS_ID - varchar(10)
		    'C',        -- STATUS - varchar(5)
		    @ROLE_SIGN,  -- ROLE_USER - varchar(50)
		    @BRANCH_SIGN_ID,  
			CASE WHEN @BRANCH_SIGN_TYPE='HS' THEN @DEP_SIGN_ID ELSE '' END,      -- 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ờ cấp phê duyệt trung gian xác nhận phiếu',       -- NOTES - nvarchar(500)
		    NULL       -- IS_HAS_CHILD - bit
		 )
	END
	ELSE -- NGUOC LAI KHONG CO CAP PHE DUYET TRUNG GIAN
	BEGIN
	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_ID,  
			@DEP_ID,      -- 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)
		    N'Chờ trưởng đơn vị phê duyệt' ,
		    NULL       -- 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)
					    --@p_PROCESS_ID,        -- PROCESS_ID - varchar(10)
						'SEND',
					    @p_TLNAME,        -- CHECKER_ID - varchar(15)
					    GETDATE(), -- APPROVE_DT - datetime
					    N'Người tạo tờ trình gửi phê duyệt thành công' ,       -- PROCESS_DESC - nvarchar(1000)
					    N'Nhân viên gửi phê duyệt '        -- NOTES - nvarchar(1000)
					 )
	END
IF @@Error <> 0 GOTO ABORT
COMMIT TRANSACTION
IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND PROCESS_ID ='SIGN'))
BEGIN
	SELECT '4' as Result,'' REQ_CODE,''REQ_ID, N'Tờ trình số: '+(SELECT REQ_CODE FROM PL_REQUEST_DOC WHERE REQ_ID  = @p_REQ_ID)+N' đã được gửi phê duyệt thành công. Vui lòng đợi cấp phê duyệt trung gian xác nhận phiếu' ErrorDesc
	RETURN '4'
END
ELSE
BEGIN
	SELECT '0' as Result,'' REQ_CODE,''REQ_ID, '' ErrorDesc
	RETURN '0'
END
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result,'' REQ_CODE,''REQ_ID, '' ErrorDesc
		RETURN '-1'
End