ALTER PROCEDURE [dbo].[RET_REPAIR_Search]
@p_RP_ID VARCHAR(15) = NULL,
@p_RET_ID VARCHAR(15) = NULL,
@P_ASSET_CODE VARCHAR(15) = NULL,
@p_ASSET_ID	varchar(15)  = NULL,
@p_ASSET_NAME	nvarchar(200)  = NULL,
@p_OFFER_NO	nvarchar(50)  = NULL,
@p_OFFER_DT	VARCHAR(20) = NULL,
@p_FINISH_DT	VARCHAR(20) = NULL,
@p_OFFER_REP_DIVISION	nvarchar(200)  = NULL,
@p_OFFER_BRANCH	varchar(15)  = NULL,
@p_OFFER_PERSON	varchar(15)  = NULL,
@p_OFFER_AMT	decimal = NULL,
@p_OFFER_REASON	nvarchar(1000)  = NULL,
@p_OFFER_CONTENT	nvarchar(1000)  = NULL,
@p_OFFER_NOTE	nvarchar(1000)  = NULL,
@p_OFFER_STATUS	varchar(1)  = NULL,
@p_REPAIR_DT	VARCHAR(20) = NULL,
@p_REPAIR_DIVISION	nvarchar(200)  = NULL,
@p_REPAIR_AMT	decimal = NULL,
@p_REPAIR_CONTENT	nvarchar(1000)  = NULL,
@p_REPAIR_NOTE	nvarchar(1000)  = NULL,
@p_ISLEAF	varchar(1)  = NULL,
@p_PARENT_ID	varchar(15)  = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(12)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(12)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@P_SEARCH_TYPE VARCHAR(1) = 'A',
@p_TOP	INT = 300,
@P_LEVEL varchar(10) = NULL,
-------BAODNQ 16/2/2022: Truyền thêm tham số------
@p_USER_LOGIN VARCHAR(15)

AS
	--Validation is here
/*
	DECLARE @ERRORSYS NVARCHAR(15) = '' 
	IF ( [NOT] EXISTS ( SELECT * FROM [TABLE] WHERE  [CONDITION] ))
		 SET @ERRORSYS = ''
	IF @ERRORSYS <> '' 
	BEGIN
		SELECT ErrorCode Result, ''  CAR_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
		RETURN '0'
SELECT * FROM CM_AUTH_STATUS
	END */
BEGIN -- PAGING
	declare @tmp table(BRANCH_ID varchar(15))
	insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_OFFER_BRANCH)

	--DECLARE @p_USER_LOGIN_ROLE VARCHAR(50) = (SELECT RoleName FROM TL_USER WHERE TLNANME = @p_USER_LOGIN)
	DECLARE @t_USER_LOGIN_ROLE_TABLE TABLE (BRANCH_ID VARCHAR(20), DEPT_ID VARCHAR(15), ROLE_ID VARCHAR(20))

	INSERT INTO @t_USER_LOGIN_ROLE_TABLE(BRANCH_ID, DEPT_ID, ROLE_ID)
	SELECT * FROM [dbo].[TL_USER_GET_ROLES](@p_USER_LOGIN)

	DECLARE @p_BRANCH_LOGIN VARCHAR(15) = (SELECT TLSUBBRID FROM TL_USER WHERE TLNANME = @p_USER_LOGIN)

	IF(@p_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)
-- PAGING BEGIN
	SELECT A.RP_ID, A.RET_ID, A.OFFER_DT, A.REPAIR_DT, A.FINISH_DT, A.REPAIR_AMT, A.REPAIR_CONTENT,C.ASSET_CODE,C.ASSET_NAME ,G.DIV_CODE,G.DIV_NAME,G.ADDR,D.AUTH_STATUS_NAME,F.EMP_NAME,dbo.FN_GET_CHINHANH(E.BRANCH_ID,'KV') KHU_VUC,
		dbo.FN_GET_CHINHANH(E.BRANCH_ID,'CN') CHI_NHANH,E.BRANCH_NAME PGD,
		(SELECT ISNULL(SUM(ASP.PAID_AMT),0) FROM ASS_PAYMENT_DT ASP WHERE ASP.REF_MASTER_ID = A.RET_ID AND ASP.REF_DETAIL_ID = A.RP_ID) AS PAID_AMT,
		A.CREATE_DT, TUM.TLFullName AS MAKER_NAME
-- SELECT END
	FROM RET_REPAIR A 
	LEFT JOIN ASS_MASTER C ON A.ASSET_ID = C.ASSET_ID
	LEFT JOIN CM_AUTH_STATUS D ON A.AUTH_STATUS = D.AUTH_STATUS
	LEFT JOIN CM_BRANCH E ON A.OFFER_BRANCH=E.BRANCH_ID
	LEFT JOIN CM_EMPLOYEE F ON A.OFFER_PERSON=F.EMP_ID
	LEFT JOIN CM_DIVISION G ON G.DIV_ID=C.DIVISION_ID
	LEFT JOIN TL_USER TUM ON A.MAKER_ID = TUM.TLNANME
	 WHERE 1 = 1
		AND (A.RP_ID LIKE '%' + @p_RP_ID + '%' OR  @p_RP_ID IS NULL OR @p_RP_ID = '')
		AND (A.RET_ID LIKE '%' + @P_RET_ID + '%' OR  @P_RET_ID IS NULL OR @P_RET_ID = '')
		AND (A.ASSET_ID LIKE '%' + @p_ASSET_ID + '%' OR  @p_ASSET_ID IS NULL OR @p_ASSET_ID = '')
		AND (A.ASSET_NAME LIKE '%' + @p_ASSET_NAME + '%' OR  @p_ASSET_NAME IS NULL OR @p_ASSET_NAME = '')
		AND (A.OFFER_NO LIKE '%' + @p_OFFER_NO + '%' OR  @p_OFFER_NO IS NULL OR @p_OFFER_NO = '')
		AND (DATEDIFF(DAY,A.OFFER_DT ,CONVERT(DATETIME, @p_OFFER_DT, 103)) = 0 OR  @p_OFFER_DT IS NULL OR @p_OFFER_DT = '')
		AND (DATEDIFF(DAY,A.FINISH_DT ,CONVERT(DATETIME, @p_FINISH_DT, 103))  =0 OR  @p_FINISH_DT IS NULL OR @p_FINISH_DT = '')
		AND (A.OFFER_REP_DIVISION LIKE '%' + @p_OFFER_REP_DIVISION + '%' OR  @p_OFFER_REP_DIVISION IS NULL OR @p_OFFER_REP_DIVISION = '')
		--AND (A.OFFER_BRANCH IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_OFFER_BRANCH)))

		AND ((@P_LEVEL = 'ALL' AND A.OFFER_BRANCH IN (SELECT BRANCH_ID from @tmp)
		OR (@P_LEVEL = 'UNIT' AND A.OFFER_BRANCH = @p_OFFER_BRANCH)) OR @p_OFFER_BRANCH = '' OR @p_OFFER_BRANCH IS NULL

		)

		--AND (A.OFFER_BRANCH LIKE '%' + @p_OFFER_BRANCH + '%' OR  @p_OFFER_BRANCH IS NULL OR @p_OFFER_BRANCH = '')
		AND (A.OFFER_PERSON LIKE '%' + @p_OFFER_PERSON + '%' OR  @p_OFFER_PERSON IS NULL OR @p_OFFER_PERSON = '')
		AND (A.OFFER_AMT = @p_OFFER_AMT OR  @p_OFFER_AMT IS NULL OR @p_OFFER_AMT=0)
		AND (A.OFFER_REASON LIKE '%' + @p_OFFER_REASON + '%' OR  @p_OFFER_REASON IS NULL OR @p_OFFER_REASON = '')
		AND (A.OFFER_CONTENT LIKE '%' + @p_OFFER_CONTENT + '%' OR  @p_OFFER_CONTENT IS NULL OR @p_OFFER_CONTENT = '')
		AND (A.OFFER_NOTE LIKE '%' + @p_OFFER_NOTE + '%' OR  @p_OFFER_NOTE IS NULL OR @p_OFFER_NOTE = '')
		AND (A.OFFER_STATUS LIKE '%' + @p_OFFER_STATUS + '%' OR  @p_OFFER_STATUS IS NULL OR @p_OFFER_STATUS = '')
		AND (DATEDIFF(DAY,A.REPAIR_DT ,CONVERT(DATETIME, @p_REPAIR_DT, 103))  = 0 OR  @p_REPAIR_DT IS NULL OR @p_REPAIR_DT = '')
		AND (A.REPAIR_DIVISION LIKE '%' + @p_REPAIR_DIVISION + '%' OR  @p_REPAIR_DIVISION IS NULL OR @p_REPAIR_DIVISION = '')
		AND (A.REPAIR_AMT = @p_REPAIR_AMT OR  @p_REPAIR_AMT IS NULL OR @p_REPAIR_AMT=0)
		AND (A.REPAIR_CONTENT LIKE '%' + @p_REPAIR_CONTENT + '%' OR  @p_REPAIR_CONTENT IS NULL OR @p_REPAIR_CONTENT = '')
		AND (A.REPAIR_NOTE LIKE '%' + @p_REPAIR_NOTE + '%' OR  @p_REPAIR_NOTE IS NULL OR @p_REPAIR_NOTE = '')
		AND (A.ISLEAF LIKE '%' + @p_ISLEAF + '%' OR  @p_ISLEAF IS NULL OR @p_ISLEAF = '')
		AND (A.PARENT_ID LIKE '%' + @p_PARENT_ID + '%' OR  @p_PARENT_ID IS NULL OR @p_PARENT_ID = '')
		AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR  @p_NOTES IS NULL OR @p_NOTES = '')
		AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR  @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
		AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR  @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
		AND (DATEDIFF(DAY,A.CREATE_DT,CONVERT(DATETIME, @p_CREATE_DT, 103)) = 0 OR  @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
		AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR  @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND (DATEDIFF(DAY,A.APPROVE_DT,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
		 AND ((@P_SEARCH_TYPE = 'N' AND A.ISLEAF = 'Y' AND A.AUTH_STATUS='A' AND A.RET_ID = @p_RET_ID OR @p_RET_ID IS NULL OR @p_RET_ID='')
														
					OR ( @P_SEARCH_TYPE = 'A'))
		AND A.RECORD_STATUS = '1'
		--AND ((@P_SEARCH_TYPE = 'N' 
		--AND A.REPAIR_DT = (	SELECT MAX(T1.REPAIR_DT) 
		--					FROM RET_REPAIR T1
		--					WHERE A.RECORD_STATUS = '1' AND A.OFFER_STATUS = '1'
		--				   )
		--	)OR ( @P_SEARCH_TYPE = 'A' AND 1 = 1 ))
	 --   ORDER BY A.REPAIR_DT
		-------BAODNQ 16/2/2022: Thêm điều kiện search-------
		--AND	(A.MAKER_ID = @p_USER_LOGIN		---- user login là ng tạo
		--	OR (A.SIGN_USER = @p_USER_LOGIN AND A.IS_SEND_APPR = 'Y') --- user login là cấp duyệt trung gian và đã gửi YC phê duyệt
		--	OR	((@p_USER_LOGIN_ROLE IN('GDDV','PGD', 'TP', 'PP','TPGD','PPGD', 'TBP')) --- user login là trưởng ĐV và đã gửi YC phê duyệt
		--		AND A.IS_SEND_APPR = 'Y'
		--		AND	(
		--				ISNULL(A.SIGN_USER,'') = '' OR (ISNULL(A.SIGN_USER,'') <> '' AND A.SIGN_DT IS NOT NULL)
		--			)
			
		--		)
		--	)
		--AND	(A.MAKER_ID = @p_USER_LOGIN		---- user login là ng tạo
		--	OR (A.SIGN_USER = @p_USER_LOGIN AND A.IS_SEND_APPR = 'Y') --- user login là cấp duyệt trung gian và đã gửi YC phê duyệt
		--	OR	(	
		--			--user login là trưởng ĐV và đã gửi YC phê duyệt
		--			(
		--				------user login là trưởng ĐV ở hội sở--------
		--				(
		--					EXISTS(SELECT * FROM CM_BRANCH WHERE BRANCH_ID = A.OFFER_BRANCH AND BRANCH_TYPE = 'HS')
		--					AND (SELECT DEP_ID FROM TL_USER WHERE TLNANME = A.MAKER_ID) 
		--						= (SELECT TOP 1 DEPT_ID FROM @t_USER_LOGIN_ROLE_TABLE WHERE ROLE_ID IN ('GDDV', 'TBP', 'TP', 'PP'))
		--				)
		--				------user login là trưởng ĐV ở CN/PGD
		--				OR
		--				(
		--					NOT EXISTS(SELECT * FROM CM_BRANCH WHERE BRANCH_ID = A.OFFER_BRANCH AND BRANCH_TYPE = 'HS')
		--					AND A.OFFER_BRANCH = (SELECT TOP 1 BRANCH_ID FROM @t_USER_LOGIN_ROLE_TABLE WHERE ROLE_ID IN ('GDDV', 'PGD', 'TPGD', 'PPGD'))
		--				)
		--			)
		--			AND A.IS_SEND_APPR = 'Y'
		--			AND	(
		--					ISNULL(A.SIGN_USER,'') = '' OR (ISNULL(A.SIGN_USER,'') <> '' AND A.SIGN_DT IS NOT NULL)
		--				)
		--		)
		--)

		AND(
			A.OFFER_BRANCH = @p_BRANCH_LOGIN
			AND(
				(SELECT DEP_ID FROM TL_USER WHERE TLNANME = A.MAKER_ID) = (SELECT TOP 1 DEPT_ID FROM @t_USER_LOGIN_ROLE_TABLE)
				OR(SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_LOGIN) <> 'HS'
			)
		)

-- PAGING END
	ELSE
-- PAGING BEGIN
	SELECT TOP(CONVERT(INT,@P_TOP))A.*,C.ASSET_CODE,G.DIV_CODE,G.DIV_NAME,G.ADDR,D.AUTH_STATUS_NAME,F.EMP_NAME,dbo.FN_GET_CHINHANH(E.BRANCH_ID,'KV') KHU_VUC,
		dbo.FN_GET_CHINHANH(E.BRANCH_ID,'CN') CHI_NHANH,E.BRANCH_NAME PGD,
		(SELECT ISNULL(SUM(ASP.PAID_AMT),0) FROM ASS_PAYMENT_DT ASP WHERE ASP.REF_MASTER_ID = A.RET_ID AND ASP.REF_DETAIL_ID = A.RP_ID) AS PAID_AMT,
		TUM.TLFullName AS MAKER_NAME
	-- SELECT END
	FROM RET_REPAIR A 
	LEFT JOIN ASS_MASTER C ON A.ASSET_ID = C.ASSET_ID
	LEFT JOIN CM_AUTH_STATUS D ON A.AUTH_STATUS = D.AUTH_STATUS
	LEFT JOIN CM_BRANCH E ON A.OFFER_BRANCH=E.BRANCH_ID
	LEFT JOIN CM_EMPLOYEE F ON A.OFFER_PERSON=F.EMP_ID
	LEFT JOIN CM_DIVISION G ON G.DIV_ID=C.DIVISION_ID
	LEFT JOIN TL_USER TUM ON A.MAKER_ID = TUM.TLNANME
	 WHERE 1 = 1
		AND (A.RP_ID LIKE '%' + @p_RP_ID + '%' OR  @p_RP_ID IS NULL OR @p_RP_ID = '')
		AND (A.RET_ID LIKE '%' + @P_RET_ID + '%' OR  @P_RET_ID IS NULL OR @P_RET_ID = '')
		AND (A.ASSET_ID LIKE '%' + @p_ASSET_ID + '%' OR  @p_ASSET_ID IS NULL OR @p_ASSET_ID = '')
		AND (A.ASSET_NAME LIKE '%' + @p_ASSET_NAME + '%' OR  @p_ASSET_NAME IS NULL OR @p_ASSET_NAME = '')
		AND (A.OFFER_NO LIKE '%' + @p_OFFER_NO + '%' OR  @p_OFFER_NO IS NULL OR @p_OFFER_NO = '')
		AND (DATEDIFF(DAY,A.OFFER_DT ,CONVERT(DATETIME, @p_OFFER_DT, 103)) = 0 OR  @p_OFFER_DT IS NULL OR @p_OFFER_DT = '')
		AND (DATEDIFF(DAY,A.FINISH_DT ,CONVERT(DATETIME, @p_FINISH_DT, 103))  =0 OR  @p_FINISH_DT IS NULL OR @p_FINISH_DT = '')
		AND (A.OFFER_REP_DIVISION LIKE '%' + @p_OFFER_REP_DIVISION + '%' OR  @p_OFFER_REP_DIVISION IS NULL OR @p_OFFER_REP_DIVISION = '')
		--AND (A.OFFER_BRANCH IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_OFFER_BRANCH)))
		AND ((@P_LEVEL = 'ALL' AND A.OFFER_BRANCH IN (SELECT BRANCH_ID from @tmp)
		OR (@P_LEVEL = 'UNIT' AND A.OFFER_BRANCH = @p_OFFER_BRANCH)) OR @p_OFFER_BRANCH = '' OR @p_OFFER_BRANCH IS NULL

		)
		--AND (A.OFFER_BRANCH LIKE '%' + @p_OFFER_BRANCH + '%' OR  @p_OFFER_BRANCH IS NULL OR @p_OFFER_BRANCH = '')
		AND (A.OFFER_PERSON LIKE '%' + @p_OFFER_PERSON + '%' OR  @p_OFFER_PERSON IS NULL OR @p_OFFER_PERSON = '')
		AND (A.OFFER_AMT = @p_OFFER_AMT OR  @p_OFFER_AMT IS NULL OR @p_OFFER_AMT=0)
		AND (A.OFFER_REASON LIKE '%' + @p_OFFER_REASON + '%' OR  @p_OFFER_REASON IS NULL OR @p_OFFER_REASON = '')
		AND (A.OFFER_CONTENT LIKE '%' + @p_OFFER_CONTENT + '%' OR  @p_OFFER_CONTENT IS NULL OR @p_OFFER_CONTENT = '')
		AND (A.OFFER_NOTE LIKE '%' + @p_OFFER_NOTE + '%' OR  @p_OFFER_NOTE IS NULL OR @p_OFFER_NOTE = '')
		AND (A.OFFER_STATUS LIKE '%' + @p_OFFER_STATUS + '%' OR  @p_OFFER_STATUS IS NULL OR @p_OFFER_STATUS = '')
		AND (DATEDIFF(DAY,A.REPAIR_DT ,CONVERT(DATETIME, @p_REPAIR_DT, 103))  = 0 OR  @p_REPAIR_DT IS NULL OR @p_REPAIR_DT = '')
		AND (A.REPAIR_DIVISION LIKE '%' + @p_REPAIR_DIVISION + '%' OR  @p_REPAIR_DIVISION IS NULL OR @p_REPAIR_DIVISION = '')
		AND (A.REPAIR_AMT = @p_REPAIR_AMT OR  @p_REPAIR_AMT IS NULL OR @p_REPAIR_AMT=0)
		AND (A.REPAIR_CONTENT LIKE '%' + @p_REPAIR_CONTENT + '%' OR  @p_REPAIR_CONTENT IS NULL OR @p_REPAIR_CONTENT = '')
		AND (A.REPAIR_NOTE LIKE '%' + @p_REPAIR_NOTE + '%' OR  @p_REPAIR_NOTE IS NULL OR @p_REPAIR_NOTE = '')
		AND (A.ISLEAF LIKE '%' + @p_ISLEAF + '%' OR  @p_ISLEAF IS NULL OR @p_ISLEAF = '')
		AND (A.PARENT_ID LIKE '%' + @p_PARENT_ID + '%' OR  @p_PARENT_ID IS NULL OR @p_PARENT_ID = '')
		AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR  @p_NOTES IS NULL OR @p_NOTES = '')
		AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR  @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
		AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR  @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
		AND (DATEDIFF(DAY,A.CREATE_DT,CONVERT(DATETIME, @p_CREATE_DT, 103)) = 0 OR  @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
		AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR  @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND (DATEDIFF(DAY,A.APPROVE_DT,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
		 AND ((@P_SEARCH_TYPE = 'N' AND A.ISLEAF = 'Y' AND A.AUTH_STATUS='A' AND A.RET_ID = @p_RET_ID OR @p_RET_ID IS NULL OR @p_RET_ID='')
														
					OR ( @P_SEARCH_TYPE = 'A'))
		AND A.RECORD_STATUS = '1'
		--AND ((@P_SEARCH_TYPE = 'N' 
		--AND A.REPAIR_DT = (	SELECT MAX(T1.REPAIR_DT) 
		--					FROM RET_REPAIR T1
		--					WHERE A.RECORD_STATUS = '1' AND A.OFFER_STATUS = '1'
		--				   )
		--	)OR ( @P_SEARCH_TYPE = 'A' AND 1 = 1 ))
	 --   ORDER BY A.REPAIR_DT
		-------BAODNQ 16/2/2022: Thêm điều kiện search-------
		--AND	(A.MAKER_ID = @p_USER_LOGIN		---- user login là ng tạo
		--	OR (A.SIGN_USER = @p_USER_LOGIN AND A.IS_SEND_APPR = 'Y') --- user login là cấp duyệt trung gian và đã gửi YC phê duyệt
		--	OR	((@p_USER_LOGIN_ROLE IN('GDDV','PGD', 'TP', 'PP','TPGD','PPGD', 'TBP')) --- user login là trưởng ĐV và đã gửi YC phê duyệt
		--		AND A.IS_SEND_APPR = 'Y'
		--		AND	(
		--				ISNULL(A.SIGN_USER,'') = '' OR (ISNULL(A.SIGN_USER,'') <> '' AND A.SIGN_DT IS NOT NULL)
		--			)
			
		--		)
		--	)
		--AND	(A.MAKER_ID = @p_USER_LOGIN		---- user login là ng tạo
		--	OR (A.SIGN_USER = @p_USER_LOGIN AND A.IS_SEND_APPR = 'Y') --- user login là cấp duyệt trung gian và đã gửi YC phê duyệt
		--	OR	(	
		--			--user login là trưởng ĐV và đã gửi YC phê duyệt
		--			(
		--				------user login là trưởng ĐV ở hội sở--------
		--				(
		--					EXISTS(SELECT * FROM CM_BRANCH WHERE BRANCH_ID = A.OFFER_BRANCH AND BRANCH_TYPE = 'HS')
		--					AND (SELECT DEP_ID FROM TL_USER WHERE TLNANME = A.MAKER_ID) 
		--						= (SELECT TOP 1 DEPT_ID FROM @t_USER_LOGIN_ROLE_TABLE WHERE ROLE_ID IN ('GDDV', 'TBP', 'TP', 'PP'))
		--				)
		--				------user login là trưởng ĐV ở CN/PGD
		--				OR
		--				(
		--					NOT EXISTS(SELECT * FROM CM_BRANCH WHERE BRANCH_ID = A.OFFER_BRANCH AND BRANCH_TYPE = 'HS')
		--					AND A.OFFER_BRANCH = (SELECT TOP 1 BRANCH_ID FROM @t_USER_LOGIN_ROLE_TABLE WHERE ROLE_ID IN ('GDDV', 'PGD', 'TPGD', 'PPGD'))
		--				)
		--			)
		--			AND A.IS_SEND_APPR = 'Y'
		--			AND	(
		--					ISNULL(A.SIGN_USER,'') = '' OR (ISNULL(A.SIGN_USER,'') <> '' AND A.SIGN_DT IS NOT NULL)
		--				)
			
		--		)
		--)

		AND(
			A.OFFER_BRANCH = @p_BRANCH_LOGIN
			AND(
				(SELECT DEP_ID FROM TL_USER WHERE TLNANME = A.MAKER_ID) = (SELECT TOP 1 DEPT_ID FROM @t_USER_LOGIN_ROLE_TABLE)
				OR(SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_LOGIN) <> 'HS'
			)
		)

-- PAGING END
END -- PAGING

GO


ALTER PROCEDURE [dbo].[RET_REPAIR_SendAppr]
@p_RP_ID VARCHAR(15) = NULL,
@p_USER_LOGIN VARCHAR(15) = NULL,
@p_SEND_APPR_DT VARCHAR(20) = NULL

AS

BEGIN TRANSACTION
	
	IF(EXISTS (SELECT * FROM RET_REPAIR WHERE RP_ID = @p_RP_ID AND IS_SEND_APPR ='Y'))
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' RP_ID, '' IS_SEND_APPR, 
			N'Gửi yêu cầu phê duyệt thất bại. Thông tin sửa chữa BĐS đã được gửi yêu cầu phê duyệt trước đó' ErrorDesc
		RETURN '-1'
	END

	UPDATE RET_REPAIR SET
		IS_SEND_APPR = 'Y',
		SEND_APPR_DT = CONVERT(DATETIME, @p_SEND_APPR_DT, 103),
		SIGN_DT = NULL,
		AUTH_STATUS = 'U'
	WHERE RP_ID = @p_RP_ID
	IF @@ERROR <> 0 GOTO ABORT

	DECLARE @p_IS_SEND_APPR VARCHAR(15) = (SELECT IS_SEND_APPR FROM RET_REPAIR WHERE RP_ID = @p_RP_ID)
	DECLARE @p_MESSAGE NVARCHAR(500)
	DECLARE @p_RET_ID VARCHAR(15) = (SELECT RET_ID FROM RET_REPAIR WHERE RP_ID = @p_RP_ID)

	------------------TH gửi phê duyệt có cấp phê duyệt trung gian--------------
	IF(EXISTS(SELECT * FROM RET_REPAIR WHERE RP_ID = @p_RP_ID AND SIGN_USER IS NOT NULL))
	BEGIN
		SET @p_MESSAGE = N'Thông tin sửa chữa BĐS: ' +@p_RET_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.'
	END
	------------------TH gửi phê duyệt ko có cấp phê duyệt trung gian(gửi thẳng trưởng DV)--------------
	ELSE
	BEGIN
		SET @p_MESSAGE = N'Thông tin sửa chữa BĐS: ' +@p_RET_ID+
			N' đã được gửi phê duyệt thành công. Vui lòng đợi trưởng đơn vị phê duyệt.'
	END

	--------BAODNQ 15/02/2022: Insert vào PL_PROCESS lưu lịch sử xử lý---------
		INSERT INTO dbo.PL_PROCESS
				(
					REQ_ID,
					PROCESS_ID,
					CHECKER_ID,
					APPROVE_DT,
					PROCESS_DESC,
					NOTES
				)
				VALUES
				(	@P_RP_ID,       
					'SEND',
					@p_USER_LOGIN,        
					GETDATE(), 
					N'Người tạo gửi phê duyệt thành công' ,      
					N'Người tạo gửi phê duyệt'       
				)
		IF @@ERROR <> 0 GOTO ABORT


COMMIT TRANSACTION
SELECT '0' as Result, @p_RP_ID RP_ID, @p_IS_SEND_APPR IS_SEND_APPR, @p_MESSAGE ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' RP_ID, '' IS_SEND_APPR, '' ErrorDesc
		RETURN '-1'
End


GO



ALTER PROCEDURE [dbo].[RET_REPAIR_Confirm]
@p_RP_ID VARCHAR(15) = NULL,
@p_SIGN_USER VARCHAR(15) = NULL,
@p_SIGN_DT VARCHAR(20) = NULL

AS

BEGIN TRANSACTION

	IF(EXISTS (
		SELECT * FROM RET_REPAIR WHERE RP_ID = @p_RP_ID AND  IS_SEND_APPR = 'Y' AND (SIGN_DT IS NOT NULL OR SIGN_DT <> '')))
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' RP_ID,
			N'Xác nhận thất bại. Thông tin sửa chữa BĐS đã được cấp trung gian xác nhận trước đó' ErrorDesc
		RETURN '-1'
	END

	IF(EXISTS (SELECT * FROM RET_REPAIR WHERE RP_ID = @p_RP_ID AND AUTH_STATUS = 'R'))
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' RP_ID,
			N'Xác nhận thất bại. Thông tin sửa chữa BĐS đang bị trả về' ErrorDesc
		RETURN '-1'
	END

	IF(EXISTS(SELECT RP_ID FROM RET_REPAIR WHERE RP_ID = @p_RP_ID AND AUTH_STATUS = 'E' AND IS_SEND_APPR = 'N'))
	BEGIN
		SELECT '-1' as Result, '' RET_ID,
			N'Xác nhận thất bại. Thông tin sửa chữa BĐS đang ở trạng thái lưu nháp. Vui lòng đợi người tạo gửi yêu cầu phê duyệt' ErrorDesc
		RETURN '-1'
	END

	UPDATE RET_REPAIR SET
		AUTH_STATUS = 'U',
		SIGN_DT = CONVERT(DATETIME, @p_SIGN_DT, 103)
	WHERE RP_ID = @p_RP_ID
	IF @@ERROR <> 0 GOTO ABORT

	DECLARE @p_RET_ID VARCHAR(15) = (SELECT RET_ID FROM RET_REPAIR WHERE RP_ID = @p_RP_ID)
	--------BAODNQ 15/02/2022: Insert vào PL_PROCESS lưu lịch sử xử lý---------
		INSERT INTO dbo.PL_PROCESS
				(
					REQ_ID,
					PROCESS_ID,
					CHECKER_ID,
					APPROVE_DT,
					PROCESS_DESC,
					NOTES
				)
				VALUES
				(	@P_RP_ID,       
					'APPROVE',
					@p_SIGN_USER,        
					GETDATE(), 
					N'Cấp trung gian xác nhận thành công' ,      
					N'Cấp trung gian xác nhận'       
				)
		IF @@ERROR <> 0 GOTO ABORT


COMMIT TRANSACTION
SELECT '0' as Result, @p_RP_ID RP_ID,
			N' Thông tin sửa chữa BĐS: ' +@p_RET_ID+
			N' đã được xác nhận thành công. Vui lòng đợi trưởng đơn vị phê duyệt.' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' RP_ID,'' ErrorDesc
		RETURN '-1'
End



GO

 
ALTER PROCEDURE [dbo].[RET_REPAIR_App]
@P_RP_ID VARCHAR(15),
@P_AUTH_STATUS VARCHAR(1),
@P_CHECKER_ID VARCHAR(12),
@P_APPROVE_DT VARCHAR(20) = NULL

AS
--Validation is here
DECLARE @ERRORSYS NVARCHAR(15) = '' 
IF ( NOT EXISTS ( SELECT * FROM RET_REPAIR WHERE RP_ID = @P_RP_ID))
	SET @ERRORSYS = 'RETR-00001'
IF @ERRORSYS <> '' 
BEGIN
	SELECT ErrorCode Result, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
	RETURN '-1'
END 

IF(EXISTS (SELECT * FROM RET_REPAIR WHERE RP_ID = @P_RP_ID AND AUTH_STATUS = 'R'))
BEGIN
	SELECT '-1' AS Result,  
		N'Phê duyệt thất bại. Thông tin sửa chữa BĐS đang được trả về' ErrorDesc 
	RETURN '-1'
END

IF(EXISTS (SELECT * FROM RET_REPAIR WHERE RP_ID = @P_RP_ID AND AUTH_STATUS = 'A'))
BEGIN
	SELECT '-1' AS Result,  
		N'Phê duyệt thất bại. Thông tin sửa chữa BĐS đã được phê duyệt trước đó' ErrorDesc 
	RETURN '-1'
END

IF(EXISTS(SELECT RP_ID FROM RET_REPAIR WHERE RP_ID = @P_RP_ID AND AUTH_STATUS = 'E' AND IS_SEND_APPR = 'N'))
BEGIN
	SELECT '-1' as Result, '' RET_ID,
		N'Phê duyệt thất bại. Thông tin sửa chữa BĐS đang ở trạng thái lưu nháp. Vui lòng đợi người tạo gửi yêu cầu phê duyệt' ErrorDesc
	RETURN '-1'
END

IF(EXISTS(
	SELECT RP_ID FROM RET_REPAIR WHERE RP_ID = @P_RP_ID AND IS_SEND_APPR = 'Y' AND AUTH_STATUS = 'U'
	AND (SIGN_USER IS NOT NULL AND SIGN_USER <> '') AND (SIGN_DT IS NULL OR SIGN_DT = '')
))
BEGIN
	SELECT '-1' as Result, '' RET_ID,
		N'Phê duyệt thất bại. Thông tin sửa chữa BĐS đang chờ cấp phê duyệt trung gian xác nhận. Vui lòng đợi cấp phê duyệt trung gian xác nhận' ErrorDesc
	RETURN '-1'
END

BEGIN TRANSACTION
	UPDATE RET_REPAIR SET AUTH_STATUS = @P_AUTH_STATUS, CHECKER_ID = @P_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@P_APPROVE_DT,103)
	WHERE RP_ID = @P_RP_ID	IF @@Error <> 0 GOTO ABORT

	--------BAODNQ 15/02/2022: Insert vào PL_PROCESS lưu lịch sử xử lý---------
		INSERT INTO dbo.PL_PROCESS
				(
					REQ_ID,
					PROCESS_ID,
					CHECKER_ID,
					APPROVE_DT,
					PROCESS_DESC,
					NOTES
				)
				VALUES
				(	@P_RP_ID,       
					'APPROVE',
					@P_CHECKER_ID,        
					GETDATE(), 
					N'Trưởng đơn vị phê duyệt thành công' ,      
					N'Trưởng đơn vị phê duyệt'       
				)
		IF @@ERROR <> 0 GOTO ABORT


COMMIT TRANSACTION
SELECT '0' as Result, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ErrorDesc
		RETURN '-1'
End



GO



ALTER PROCEDURE [dbo].[RET_REPAIR_Ins]
@P_RET_ID	varchar(15)  = NULL,
@p_ASSET_ID	varchar(15)  = NULL,
@p_ASSET_NAME	nvarchar(200)  = NULL,
@p_OFFER_NO	nvarchar(50)  = NULL,
@p_OFFER_DT	VARCHAR(20) = NULL,
@p_FINISH_DT	VARCHAR(20) = NULL,
@p_OFFER_REP_DIVISION	nvarchar(200)  = NULL,
@p_OFFER_BRANCH	varchar(15)  = NULL,
@p_OFFER_PERSON	varchar(15)  = NULL,
@p_OFFER_AMT	decimal(18)  = NULL,
@p_OFFER_REASON	nvarchar(1000)  = NULL,
@p_OFFER_CONTENT	nvarchar(1000)  = NULL,
@p_OFFER_NOTE	nvarchar(1000)  = NULL,
@p_OFFER_STATUS	varchar(1)  = NULL,
@p_REPAIR_DT	VARCHAR(20) = NULL,
@p_REPAIR_DIVISION	Nvarchar(200)  = NULL,
@p_REPAIR_AMT	decimal(18)  = NULL,
@p_REPAIR_CONTENT	nvarchar(1000)  = NULL,
@p_REPAIR_NOTE	nvarchar(1000)  = NULL,
@p_ISLEAF	varchar(1)  = NULL,
@p_PARENT_ID	varchar(15)  = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(12)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(12)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_PERFORMANCE	VARCHAR(1) = NULL,
----BAODNQ 15/2/2022: Thêm cột vào bảng RET_REPAIR------
@p_IS_SEND_APPR VARCHAR(15) = NULL,
@p_SEND_APPR_DT VARCHAR(20) = NULL,
@p_SIGN_USER VARCHAR(15) = NULL,
@p_SIGN_DT VARCHAR(20) = NULL


AS
DECLARE @ERRORSYS NVARCHAR(15) = '' 
	IF (  EXISTS ( SELECT * FROM RET_REPAIR WHERE  RET_ID = @P_RET_ID ) )
	    BEGIN
		IF((SELECT COUNT(A.RET_ID) FROM RET_REPAIR A WHERE A.RET_ID = @P_RET_ID AND OFFER_STATUS = '0') > 0)
			SET @ERRORSYS = 'RETR-00002'
	    ELSE IF(NOT EXISTS(SELECT * FROM RET_REPAIR A WHERE  A.AUTH_STATUS='A' AND A.ISLEAF = 'Y'AND (A.RET_ID = @p_RET_ID OR @p_RET_ID IS NULL OR @p_RET_ID='')))					
			SET @ERRORSYS='RETR-00003'
	    END
	IF @ERRORSYS <> '' 
	BEGIN
		SELECT ErrorCode Result, ''  RET_REPAIR_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
	RETURN '-1'
	END 

	IF (CONVERT(DATETIME, @p_FINISH_DT, 103) < CONVERT(DATETIME, @p_OFFER_DT, 103))
	BEGIN
		SELECT '-1' AS Result, ''  RET_REPAIR_ID, 
			N'Thông tin đề xuất sửa chữa : Ngày dự kiến sửa xong không được nhỏ hơn ngày đề xuất' ErrorDesc
		RETURN '-1'
	END

BEGIN TRANSACTION
DECLARE @l_RET_REPAIR_ID VARCHAR(15)
		
		EXEC SYS_CodeMasters_Gen 'RET_REPAIR', @l_RET_REPAIR_ID out
		IF @p_OFFER_DT = ''  SET @p_OFFER_DT = NULL
		IF @p_FINISH_DT = '' SET @p_FINISH_DT = NULL
		IF @p_REPAIR_DT = '' SET @p_REPAIR_DT = NULL
		IF @p_CREATE_DT = '' SET @p_CREATE_DT = NULL
		IF @p_APPROVE_DT = '' SET @p_APPROVE_DT = NULL
		
		DECLARE @PARENT VARCHAR(15) = NULL
		SELECT @PARENT = RP_ID FROM RET_REPAIR WHERE RET_ID = @P_RET_ID AND ISLEAF = 'Y' AND RECORD_STATUS = '1'
		UPDATE RET_REPAIR SET ISLEAF = 'N' WHERE RP_ID = @PARENT 
		
	
		IF @l_RET_REPAIR_ID ='' OR @l_RET_REPAIR_ID IS NULL GOTO ABORT
		INSERT INTO RET_REPAIR(RP_ID,[RET_ID],[ASSET_ID],[ASSET_NAME],[OFFER_NO],[OFFER_DT],[FINISH_DT],
			[OFFER_REP_DIVISION],[OFFER_BRANCH],[OFFER_PERSON],[OFFER_AMT],[OFFER_REASON],[OFFER_CONTENT],
			[OFFER_NOTE],[OFFER_STATUS],[REPAIR_DT],[REPAIR_DIVISION],[REPAIR_AMT],[REPAIR_CONTENT],
			[REPAIR_NOTE],[ISLEAF],[PARENT_ID],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],
			[CHECKER_ID],[APPROVE_DT],[PERFORMANCE], [IS_SEND_APPR], [SEND_APPR_DT], [SIGN_USER], [SIGN_DT])
		VALUES(@l_RET_REPAIR_ID ,@P_RET_ID ,@p_ASSET_ID ,@p_ASSET_NAME ,@p_OFFER_NO ,
			CONVERT(DATETIME, @p_OFFER_DT, 103) ,CONVERT(DATETIME, @p_FINISH_DT, 103) ,@p_OFFER_REP_DIVISION ,
			@p_OFFER_BRANCH ,@p_OFFER_PERSON ,@p_OFFER_AMT ,@p_OFFER_REASON ,@p_OFFER_CONTENT ,@p_OFFER_NOTE ,
			'0',CONVERT(DATETIME, @p_REPAIR_DT, 103) ,@p_REPAIR_DIVISION ,@p_REPAIR_AMT ,@p_REPAIR_CONTENT ,
			@p_REPAIR_NOTE ,'Y',@PARENT ,@p_NOTES ,'1',@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS,
			@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103),@p_PERFORMANCE, @p_IS_SEND_APPR, CONVERT(DATETIME, @p_SEND_APPR_DT, 103),
			@p_SIGN_USER, CONVERT(DATETIME, @p_SIGN_DT, 103))
		IF @@Error <> 0 GOTO ABORT

		--------BAODNQ 15/02/2022: Insert vào PL_PROCESS lưu lịch sử xử lý---------
		INSERT INTO dbo.PL_PROCESS
				(
					REQ_ID,
					PROCESS_ID,
					CHECKER_ID,
					APPROVE_DT,
					PROCESS_DESC,
					NOTES
				)
				VALUES
				(	@l_RET_REPAIR_ID,       
					'INSERT',
					@p_MAKER_ID,        
					GETDATE(), 
					N'Thêm mới thông tin sửa chữa BĐS thành công' ,      
					N'Thêm mới thông tin sửa chữa BĐS'       
				)
		IF @@ERROR <> 0 GOTO ABORT

		--------BAODNQ 15/3/2022: Lấy tên BDS------
		DECLARE @p_RET_NAME NVARCHAR(MAX) = (
			SELECT ASSET_NAME
			FROM RET_REPAIR
			WHERE RP_ID = @l_RET_REPAIR_ID
		)

COMMIT TRANSACTION
SELECT '0' as Result, @l_RET_REPAIR_ID  RET_REPAIR_ID, @p_RET_NAME RET_NAME,'' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' RET_REPAIR_ID, '' RET_NAME,'' ErrorDesc
		RETURN '-1'
End

GO



ALTER PROCEDURE [dbo].[RET_REPAIR_Upd]
@p_RET_REPAIR_ID	varchar(15) = null ,
@p_RET_ID	varchar(15) = NULL ,
@p_ASSET_ID	varchar(15) = NULL ,
@p_ASSET_NAME	nvarchar(200) = NULL ,
@p_OFFER_NO	nvarchar(50) = NULL ,
@p_OFFER_DT	VARCHAR(20) = NULL,
@p_FINISH_DT	VARCHAR(20) = NULL,
@p_OFFER_REP_DIVISION	nvarchar(200) = NULL ,
@p_OFFER_BRANCH	varchar(15) = NULL ,
@p_OFFER_PERSON	varchar(15) = NULL ,
@p_OFFER_AMT	decimal(17) = NULL ,
@p_OFFER_REASON	nvarchar(1000) = NULL ,
@p_OFFER_CONTENT	nvarchar(1000) = NULL ,
@p_OFFER_NOTE	nvarchar(1000) = NULL ,
@p_OFFER_STATUS	varchar(1) = NULL ,
@p_REPAIR_DT	VARCHAR(20) = NULL,
@p_REPAIR_DIVISION	Nvarchar(200) = NULL ,
@p_REPAIR_AMT	decimal(17) = NULL ,
@p_REPAIR_CONTENT	nvarchar(1000) = NULL ,
@p_REPAIR_NOTE	nvarchar(1000) = NULL ,
@p_ISLEAF	varchar(1) = NULL ,
@p_PARENT_ID	varchar(15) = NULL ,
@p_NOTES	nvarchar(1000) = NULL ,
@p_RECORD_STATUS	varchar(1) = NULL ,
@p_MAKER_ID	varchar(12) = NULL ,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS	varchar(50) = NULL ,
@p_CHECKER_ID	varchar(12) = NULL ,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_PERFORMANCE	VARCHAR(1) = NULL,
----BAODNQ 15/2/2022: Thêm cột vào bảng RET_REPAIR------
@p_IS_SEND_APPR VARCHAR(15) = NULL,
@p_SEND_APPR_DT VARCHAR(20) = NULL,
@p_SIGN_USER VARCHAR(15) = NULL,
@p_SIGN_DT VARCHAR(20) = NULL

AS
--Validation is here
	DECLARE @ERRORSYS NVARCHAR(15) = '' 
	IF ( NOT EXISTS ( SELECT * FROM RET_REPAIR WHERE RP_ID = @P_RET_REPAIR_ID))
		SET @ERRORSYS = 'RETR-00001'
	IF @ERRORSYS <> '' 
	BEGIN
		SELECT ErrorCode Result, ''  RP_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
		RETURN '0'
	END

	IF(EXISTS(SELECT * FROM RET_REPAIR WHERE RP_ID = @p_RET_REPAIR_ID AND IS_SEND_APPR = 'Y' AND AUTH_STATUS = 'U'))
	BEGIN
		SELECT '-1' AS Result, ''  RET_ID, 
			N'Không thể chỉnh sửa. Thông tin sửa chữa BĐS đã được gửi yêu cầu phê duyệt' ErrorDesc 
		RETURN '-1'
	END

	IF (CONVERT(DATETIME, @p_FINISH_DT, 103) < CONVERT(DATETIME, @p_OFFER_DT, 103))
	BEGIN
		SELECT '-1' AS Result, ''  RET_REPAIR_ID, 
			N'Thông tin đề xuất sửa chữa : Ngày dự kiến sửa xong không được nhỏ hơn ngày đề xuất' ErrorDesc
		RETURN '-1'
	END

	IF (CONVERT(DATETIME, @p_REPAIR_DT, 103) < CONVERT(DATETIME, @p_OFFER_DT, 103))
	BEGIN
		SELECT '-1' AS Result, ''  RET_REPAIR_ID, 
			N'Thông tin sửa chữa thực tế : Ngày sửa chữa không được nhỏ hơn ngày đề xuất' ErrorDesc
		RETURN '-1'
	END

BEGIN TRANSACTION

	IF(EXISTS (SELECT * FROM RET_REPAIR WHERE RP_ID = @p_RET_REPAIR_ID AND AUTH_STATUS = 'A'))
	BEGIN
		--------------------CẬP NHẬT LẠI SAU KHI ĐÃ DUYỆT----------------
		SET @p_CHECKER_ID = NULL
		SET @p_APPROVE_DT = NULL
		SET @p_SEND_APPR_DT = NULL
		SET @p_SIGN_DT = NULL
	END

		UPDATE RET_REPAIR 
		SET [RET_ID] = @p_RET_ID,
		[ASSET_ID] = @p_ASSET_ID,
		[ASSET_NAME] = @p_ASSET_NAME,
		[OFFER_NO] = @p_OFFER_NO,
		[OFFER_DT] = CONVERT(DATETIME, @p_OFFER_DT, 103),
		[FINISH_DT] = CONVERT(DATETIME, @p_FINISH_DT, 103),
		[OFFER_REP_DIVISION] = @p_OFFER_REP_DIVISION,
		[OFFER_BRANCH] = @p_OFFER_BRANCH,
		[OFFER_PERSON] = @p_OFFER_PERSON,
		[OFFER_AMT] = @p_OFFER_AMT,
		[OFFER_REASON] = @p_OFFER_REASON,
		[OFFER_CONTENT] = @p_OFFER_CONTENT,
		[OFFER_NOTE] = @p_OFFER_NOTE,
		[OFFER_STATUS] = '1',
		[REPAIR_DT] = CONVERT(DATETIME, @p_REPAIR_DT, 103),
		[REPAIR_DIVISION] = @p_REPAIR_DIVISION,
		[REPAIR_AMT] = @p_REPAIR_AMT,
		[REPAIR_CONTENT] = @p_REPAIR_CONTENT,
		[REPAIR_NOTE] = @p_REPAIR_NOTE,
		[ISLEAF] = @p_ISLEAF,
		[PARENT_ID] = @p_PARENT_ID,
		[NOTES] = @p_NOTES,
		[RECORD_STATUS] = @p_RECORD_STATUS,
		[MAKER_ID] = @p_MAKER_ID,
		[CREATE_DT] = CONVERT(DATETIME, @p_CREATE_DT, 103),
		[AUTH_STATUS] = @p_AUTH_STATUS,
		[CHECKER_ID] = @p_CHECKER_ID,
		[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103), 
		[PERFORMANCE] = @p_PERFORMANCE,
		[IS_SEND_APPR] = @p_IS_SEND_APPR,
		[SEND_APPR_DT] = CONVERT(DATETIME, @p_SEND_APPR_DT, 103),
		[SIGN_USER] = @p_SIGN_USER,
		[SIGN_DT] = CONVERT(DATETIME, @p_SIGN_DT, 103)

WHERE   RP_ID = @p_RET_REPAIR_ID
		IF @@Error <> 0 GOTO ABORT

		---------------BAODNQ : TH NGƯỜI TẠO CẬP NHẬT NHIỀU LẦN THÌ CHỈ LƯU LỊCH SỬ XỬ LÝ CẬP NHẬT GẦN NHẤT----------------
		IF((SELECT TOP 1 PROCESS_ID FROM PL_PROCESS WHERE REQ_ID = @p_RET_REPAIR_ID ORDER BY APPROVE_DT DESC) = 'UPDATE')
		BEGIN
			WITH RESULT AS(
				SELECT TOP 1 * FROM PL_PROCESS WHERE REQ_ID = @p_RET_REPAIR_ID
				ORDER BY APPROVE_DT DESC
			)
			DELETE FROM RESULT
		END

		--------BAODNQ 15/02/2022: Insert vào PL_PROCESS lưu lịch sử xử lý---------
		INSERT INTO dbo.PL_PROCESS
				(
					REQ_ID,
					PROCESS_ID,
					CHECKER_ID,
					APPROVE_DT,
					PROCESS_DESC,
					NOTES
				)
				VALUES
				(	@p_RET_REPAIR_ID,       
					'UPDATE',
					@p_MAKER_ID,        
					GETDATE(), 
					N'Cập nhật thông tin sửa chữa BĐS thành công' ,      
					N'Cập nhật thông tin sửa chữa BĐS'       
				)
		IF @@ERROR <> 0 GOTO ABORT


COMMIT TRANSACTION
		SELECT '0' as Result, @p_RET_REPAIR_ID  RP_ID, '' ErrorDesc
		RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' RP_ID, '' ErrorDesc
		RETURN '-1'
End

GO