DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

ALTER PROCEDURE [dbo].[RET_MASTER_Search]

@p_RET_ID	varchar(15)  = NULL,

@p_ASSET_ID VARCHAR(15) = NULL,

@P_ASSET_CODE VARCHAR(15) = NULL,

@P_DIV_ID VARCHAR(15) = NULL,

@P_DIV_CODE VARCHAR(15) = NULL,

@P_OWNER VARCHAR(50) = NULL,

@p_LENGTH	DECIMAL(18,2) = NULL,

@p_WIDTH	DECIMAL(18,2) = NULL,

@p_CURRENT_STATE	nvarchar(100)  = NULL,

@p_RET_TYPE	varchar(15)  = NULL,

@p_STATUS	varchar(15)  = NULL,

@p_LAND_SQUARE	DECIMAL(18,2)  = NULL,

@p_CONSTRUCT_SQUARE	DECIMAL(18,2)  = NULL,

@p_TOTAL_SQUARE	DECIMAL(18,2)  = NULL,

@p_BOUNDARY	nvarchar(100)  = NULL,

@p_HOUSEDES	nvarchar(100)  = NULL,

@p_PURPOSE_IN_USE	nvarchar(100)  = NULL,

@P_W_USE_CON NVARCHAR(100) = NULL,

@p_USE_STATUS	varchar(15)  = NULL,

@p_CONST_STATUS	varchar(15)  = NULL,

@p_OWNER_TYPE	varchar(15)  = NULL,

@p_USE_PERIOD	int  = NULL,

@p_PERSON_HOLDER	nvarchar(100)  = NULL,

@p_NOTES	nvarchar(100)  = NULL,

@p_RECORD_STATUS	varchar(1)  = NULL,

@p_AUTH_STATUS	varchar(1)  = NULL,

@p_MAKER_ID	varchar(15)  = NULL,

@p_CREATE_DT	VARCHAR(20) = NULL,

@p_CHECKER_ID	varchar(15)  = NULL,

@p_APPROVE_DT	VARCHAR(20) = NULL,

@p_TOP	INT = 300,

------------------BAODNQ 16/2/2022: Thêm tham số------------

@p_USER_LOGIN VARCHAR(15),

@p_BRANCH_ID VARCHAR(15) = NULL,

@p_RET_FORM VARCHAR(15) = NULL,

@p_ASSET_STATUS VARCHAR(15) = NULL,

@p_ADDR NVARCHAR(1000) = NULL,

@p_RET_SEARCH_TYPE VARCHAR(20) = NULL 

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'

	END */

BEGIN -- PAGING

--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), 

		@p_DEP_LOGIN VARCHAR(15) = (SELECT DEP_ID FROM TL_USER WHERE TLNANME = @p_USER_LOGIN)

IF(@p_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)

-- PAGING BEGIN

SELECT A.*,(CASE WHEN USE_PERIOD = 0 THEN N'Lâu dài' ELSE convert(varchar(100),USE_PERIOD_DT, 103) END) AS USE_PERIOD_NAME, B.AUTH_STATUS_NAME, C.CONTENT, 

--CASE 

--	WHEN BRU.BRANCH_TYPE = 'HS' THEN DP.DEP_NAME 

--	ELSE BRU.BRANCH_NAME 

--END AS BRANCH_NAME, -- ĐV sử dụng

BRU.BRANCH_NAME AS BRANCH_USE_NAME, -- ĐV sử dụng

G.ADDR as ASS_ADDR,

G.DIV_ID,G.DIV_CODE,G.DIV_NAME,E.CONTENT AS RET_STATUS, AST.TYPE_NAME AS ASSET_TYPE, ASG.GROUP_NAME AS ASS_GROUP,

AAS.STATUS_NAME AS ASSET_STATUS_NAME,

D.BRANCH_ID,

BRC.BRANCH_NAME AS BRANCH_CREATE_NAME, -- ĐV quản lý

TUM.TLFullName AS MAKER_NAME,

-----------BAODNQ 15/3/2022 Lấy thông tin xuất excel

C.CONTENT AS RET_TYPE_NAME, --loại BDS

UF.CONTENT AS USE_FORM_NAME, -- hình thức sử dụng

US.CONTENT AS USE_STATUS_NAME, --tình trạng sử dụng đất

CS.CONTENT AS CONST_STATUS_NAME, --tình trạng xây dựng

E.CONTENT AS RET_STATUS_NAME, --hiện trạng pháp lý gắn liền vs đất

OT.CONTENT AS OWNER_TYPE_NAME, -- loại sỡ hữu

----------BAODNQ 18/4/2022: Check điều kiện khi lấy thông tin tài sản-----

CASE

	WHEN A.ASSET_ID IS NULL OR A.ASSET_ID = '' THEN A.ASS_NAME

	ELSE D.ASSET_NAME

END AS ASSET_NAME,

CASE

	WHEN A.ASSET_ID IS NULL OR A.ASSET_ID = '' THEN A.DESCRIPTION

	ELSE D.ASSET_DESC

END AS ASSET_DESC,

CASE

	WHEN A.ASSET_ID IS NULL OR A.ASSET_ID = '' THEN A.ASSET_PRICE

	ELSE D.BUY_PRICE

END AS BUY_PRICE

-------------------------END BAODNQ---------------

-- SELECT END

FROM RET_MASTER A 

INNER JOIN CM_AUTH_STATUS B ON B.AUTH_STATUS = A.AUTH_STATUS

--INNER JOIN CM_ALLCODE C ON C.CDVAL = A.RET_TYPE AND C.CDNAME = 'RET_TYPE'AND C.CDTYPE = 'RET'

INNER JOIN CM_ALLCODE C ON C.CDVAL = A.RET_TYPE AND C.CDNAME = 'RET_TYPE_2'AND C.CDTYPE = 'RET'

INNER JOIN CM_ALLCODE E ON E.CDVAL = A.[STATUS] AND E.CDNAME = 'RET_STATUS' AND E.CDTYPE = 'RET'

LEFT JOIN ASS_MASTER D ON D.ASSET_ID = A.ASSET_ID

LEFT JOIN CM_DIVISION G ON G.DIV_ID=D.DIVISION_ID

LEFT JOIN CM_BRANCH BRC ON D.BRANCH_CREATE = BRC.BRANCH_ID -- ĐV quản lý

--LEFT JOIN CM_BRANCH BRU ON D.BRANCH_ID = BRU.BRANCH_ID -- ĐV sử dụng

LEFT JOIN CM_BRANCH BRU ON A.BRANCH_USE_ID = BRU.BRANCH_ID -- ĐV sử dụng

LEFT JOIN CM_DEPARTMENT DP ON DP.DEP_ID = D.DEPT_ID

--LEFT JOIN ASS_MASTER AMS ON A.ASSET_ID = AMS.ASSET_ID

LEFT JOIN ASS_TYPE AST ON D.TYPE_ID = AST.TYPE_ID

LEFT JOIN ASS_GROUP ASG ON D.GROUP_ID = ASG.GROUP_ID

LEFT JOIN ASS_AMORT_STATUS AAS ON D.AMORT_STATUS = AAS.STATUS_CODE

LEFT JOIN TL_USER TUM ON A.MAKER_ID = TUM.TLNANME

----------------------BAODNQ 15/3/2022: Lấy thông tin xuất excel-----

LEFT JOIN CM_ALLCODE UF ON A.USE_FORM = UF.CDVAL AND UF.CDNAME = 'USE_FORM' AND UF.CDTYPE = 'RET'

LEFT JOIN CM_ALLCODE US ON A.USE_STATUS = US.CDVAL AND US.CDNAME = 'USE_STATUS' AND US.CDTYPE = 'RET'

LEFT JOIN CM_ALLCODE CS ON A.CONST_STATUS = CS.CDVAL AND CS.CDNAME = 'CONST_STATUS' AND CS.CDTYPE = 'RET'

LEFT JOIN CM_ALLCODE OT ON A.OWNER_TYPE = OT.CDVAL AND OT.CDNAME = 'OWNER_TYPE' AND OT.CDTYPE = 'RET'

 WHERE 1 = 1

	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_CODE LIKE '%' + @P_ASSET_CODE + '%' OR  @P_ASSET_CODE IS NULL OR @P_ASSET_CODE = '')

	AND (D.ASSET_CODE LIKE '%' + @P_ASSET_CODE + '%' OR  @P_ASSET_CODE IS NULL OR @P_ASSET_CODE = '')

	AND (A.OWNER LIKE '%' + @P_OWNER + '%' OR  @P_OWNER IS NULL OR @P_OWNER = '')

	AND (A.LENGTH = @p_LENGTH OR  @p_LENGTH IS NULL)

	AND (A.WIDTH = @p_WIDTH OR  @p_WIDTH IS NULL)

	AND (A.CURRENT_STATE LIKE '%' + @p_CURRENT_STATE + '%' OR  @p_CURRENT_STATE IS NULL OR @p_CURRENT_STATE = '')

	AND (A.RET_TYPE LIKE '%' + @p_RET_TYPE + '%' OR  @p_RET_TYPE IS NULL OR @p_RET_TYPE = '')

	AND (A.STATUS LIKE '%' + @p_STATUS + '%' OR  @p_STATUS IS NULL OR @p_STATUS = '')

	AND (A.LAND_SQUARE = @p_LAND_SQUARE OR  @p_LAND_SQUARE IS NULL)

	AND (A.CONSTRUCT_SQUARE = @p_CONSTRUCT_SQUARE OR  @p_CONSTRUCT_SQUARE IS NULL)

	AND (A.TOTAL_SQUARE = @p_TOTAL_SQUARE OR  @p_TOTAL_SQUARE IS NULL)

	AND (A.BOUNDARY LIKE '%' + @p_BOUNDARY + '%' OR  @p_BOUNDARY IS NULL OR @p_BOUNDARY = '')

	AND (A.HOUSEDES LIKE '%' + @p_HOUSEDES + '%' OR  @p_HOUSEDES IS NULL OR @p_HOUSEDES = '')

	AND (A.PURPOSE_IN_USE LIKE '%' + @p_PURPOSE_IN_USE + '%' OR  @p_PURPOSE_IN_USE IS NULL OR @p_PURPOSE_IN_USE = '')

	AND (A.W_USE_CON LIKE '%' + @P_W_USE_CON + '%' OR  @P_W_USE_CON IS NULL OR @P_W_USE_CON = '')

	AND (A.USE_STATUS LIKE '%' + @p_USE_STATUS + '%' OR  @p_USE_STATUS IS NULL OR @p_USE_STATUS = '')

	AND (A.CONST_STATUS LIKE '%' + @p_CONST_STATUS + '%' OR  @p_CONST_STATUS IS NULL OR @p_CONST_STATUS = '')

	AND (A.OWNER_TYPE LIKE '%' + @p_OWNER_TYPE + '%' OR  @p_OWNER_TYPE IS NULL OR @p_OWNER_TYPE = '')

	AND (A.USE_PERIOD = @p_USE_PERIOD OR  @p_USE_PERIOD IS NULL)

	AND (A.PERSON_HOLDER LIKE '%' + @p_PERSON_HOLDER + '%' OR  @p_PERSON_HOLDER IS NULL OR @p_PERSON_HOLDER = '')

	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.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_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.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 (A.RECORD_STATUS = '1')

	AND (G.DIV_ID LIKE '%' + @P_DIV_ID + '%' OR @P_DIV_ID = '' OR @P_DIV_ID IS NULL)

	AND (G.DIV_CODE LIKE '%' + @P_DIV_CODE + '%' OR @P_DIV_CODE = '' OR @P_DIV_CODE IS NULL)

	AND (

			D.BRANCH_CREATE = @p_BRANCH_ID 

			OR A.BRANCH_CREATE = @p_BRANCH_ID

			OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = ''

		)

	--AND (A.BRANCH_CREATE = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')

	AND (A.RET_FORM LIKE '%' + @p_RET_FORM + '%' OR @p_RET_FORM IS NULL OR @p_RET_FORM = '')

	AND (A.ADDR LIKE '%' + @p_ADDR + '%' OR @p_ADDR IS NULL OR @p_ADDR = '')

	AND (

			(@p_ASSET_STATUS = 'DTL' AND D.AMORT_STATUS LIKE '%' + @p_ASSET_STATUS + '%') 

			OR (@p_ASSET_STATUS = 'BT' AND D.AMORT_STATUS NOT LIKE '%DTL%')

			OR (@p_ASSET_STATUS IS NULL OR @p_ASSET_STATUS = '')

		)

	AND (

			(@p_RET_SEARCH_TYPE = 'IS_ASSET' AND A.ASSET_ID IS NOT NULL) -- Tìm kiếm BDS đã là tài sản NH

			OR (@p_RET_SEARCH_TYPE = 'NOT_ASSET' AND A.ASSET_ID IS NULL) -- Tìm kiếm BDS chưa là tài sản NH (đang hoàn thiện pháp lý)

			OR (@p_RET_SEARCH_TYPE IS NULL) -- Tìm toàn bộ

		)

	-------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	(	

		--			--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.BRANCH_CREATE 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.BRANCH_CREATE AND BRANCH_TYPE = 'HS')

		--					AND A.BRANCH_CREATE = (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)

		--				)

		--		)

		--)

		-----------BAODNQ: NẾU Ở HỘI SỞ TÌM THẤY NHỮNG PHIẾU THEO PHÒNG BAN------------------

		----------------NẾU Ở DVKD TÌM THẤY PHIẾU THEO ĐƠN VỊ--------------------

		AND(

			A.BRANCH_CREATE = @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'

			)

		)

		ORDER BY A.CREATE_DT DESC

-- PAGING END

ELSE

-- PAGING BEGIN

SELECT TOP(CONVERT(INT,@P_TOP)) A.*, (CASE WHEN USE_PERIOD = 0 THEN N'Lâu dài' ELSE convert(varchar(100),USE_PERIOD_DT, 103) END) AS USE_PERIOD_NAME,

B.AUTH_STATUS_NAME, C.CONTENT, 

--CASE 

--	WHEN BRU.BRANCH_TYPE = 'HS' THEN DP.DEP_NAME 

--	ELSE BRU.BRANCH_NAME 

--END AS BRANCH_NAME, -- ĐV sử dụng

BRU.BRANCH_NAME AS BRANCH_USE_NAME, -- ĐV sử dụng

G.DIV_ID,G.DIV_CODE,G.DIV_NAME, E.CONTENT AS RET_STATUS,

 AAS.STATUS_NAME AS ASSET_STATUS_NAME,

D.BRANCH_ID,

BRC.BRANCH_NAME AS BRANCH_CREATE_NAME, -- ĐV quản lý

TUM.TLFullName AS MAKER_NAME,

 -----------BAODNQ 15/3/2022 Lấy thông tin xuất excel

C.CONTENT AS RET_TYPE_NAME, --loại BDS

UF.CONTENT AS USE_FORM_NAME, -- hình thức sử dụng

US.CONTENT AS USE_STATUS_NAME, --tình trạng sử dụng đất

CS.CONTENT AS CONST_STATUS_NAME, --tình trạng xây dựng

E.CONTENT AS RET_STATUS_NAME, --hiện trạng pháp lý gắn liền vs đất

OT.CONTENT AS OWNER_TYPE_NAME, -- loại sỡ hữu

----------BAODNQ 18/4/2022: Check điều kiện khi lấy thông tin tài sản-----

CASE

	WHEN A.ASSET_ID IS NULL OR A.ASSET_ID = '' THEN A.ASS_NAME

	ELSE D.ASSET_NAME

END AS ASSET_NAME,

CASE

	WHEN A.ASSET_ID IS NULL OR A.ASSET_ID = '' THEN A.DESCRIPTION

	ELSE D.ASSET_DESC

END AS ASSET_DESC,

CASE

	WHEN A.ASSET_ID IS NULL OR A.ASSET_ID = '' THEN A.ASSET_PRICE

	ELSE D.BUY_PRICE

END AS BUY_PRICE

-------------------------END BAODNQ---------------

 -- SELECT END

FROM RET_MASTER A 

INNER JOIN CM_AUTH_STATUS B ON B.AUTH_STATUS = A.AUTH_STATUS

--INNER JOIN CM_ALLCODE C ON C.CDVAL = A.RET_TYPE AND C.CDNAME = 'RET_TYPE'AND C.CDTYPE = 'RET'

INNER JOIN CM_ALLCODE C ON C.CDVAL = A.RET_TYPE AND C.CDNAME = 'RET_TYPE_2'AND C.CDTYPE = 'RET'

INNER JOIN CM_ALLCODE E ON E.CDVAL = A.[STATUS] AND E.CDNAME = 'RET_STATUS' AND E.CDTYPE = 'RET'

LEFT JOIN ASS_MASTER D ON D.ASSET_ID = A.ASSET_ID

LEFT JOIN CM_DIVISION G ON G.DIV_ID=D.DIVISION_ID

LEFT JOIN CM_BRANCH BRC ON D.BRANCH_CREATE = BRC.BRANCH_ID -- ĐV quản lý

--LEFT JOIN CM_BRANCH BRU ON D.BRANCH_ID = BRU.BRANCH_ID -- ĐV sử dụng

LEFT JOIN CM_BRANCH BRU ON A.BRANCH_USE_ID = BRU.BRANCH_ID -- ĐV sử dụng

LEFT JOIN CM_DEPARTMENT DP ON DP.DEP_ID = D.DEPT_ID

LEFT JOIN ASS_AMORT_STATUS AAS ON D.AMORT_STATUS = AAS.STATUS_CODE

LEFT JOIN TL_USER TUM ON A.MAKER_ID = TUM.TLNANME

----------------------BAODNQ 15/3/2022: Lấy thông tin xuất excel-----

LEFT JOIN CM_ALLCODE UF ON A.USE_FORM = UF.CDVAL AND UF.CDNAME = 'USE_FORM' AND UF.CDTYPE = 'RET'

LEFT JOIN CM_ALLCODE US ON A.USE_STATUS = US.CDVAL AND US.CDNAME = 'USE_STATUS' AND US.CDTYPE = 'RET'

LEFT JOIN CM_ALLCODE CS ON A.CONST_STATUS = CS.CDVAL AND CS.CDNAME = 'CONST_STATUS' AND CS.CDTYPE = 'RET'

LEFT JOIN CM_ALLCODE OT ON A.OWNER_TYPE = OT.CDVAL AND OT.CDNAME = 'OWNER_TYPE' AND OT.CDTYPE = 'RET'

 WHERE 1 = 1

	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_CODE LIKE '%' + @P_ASSET_CODE + '%' OR  @P_ASSET_CODE IS NULL OR @P_ASSET_CODE = '')

	AND (D.ASSET_CODE LIKE '%' + @P_ASSET_CODE + '%' OR  @P_ASSET_CODE IS NULL OR @P_ASSET_CODE = '')

	AND (A.OWNER LIKE '%' + @P_OWNER + '%' OR  @P_OWNER IS NULL OR @P_OWNER = '')

	AND (A.LENGTH = @p_LENGTH OR  @p_LENGTH IS NULL)

	AND (A.WIDTH = @p_WIDTH OR  @p_WIDTH IS NULL)

	AND (A.CURRENT_STATE LIKE '%' + @p_CURRENT_STATE + '%' OR  @p_CURRENT_STATE IS NULL OR @p_CURRENT_STATE = '')

	AND (A.RET_TYPE LIKE '%' + @p_RET_TYPE + '%' OR  @p_RET_TYPE IS NULL OR @p_RET_TYPE = '')

	AND (A.STATUS LIKE '%' + @p_STATUS + '%' OR  @p_STATUS IS NULL OR @p_STATUS = '')

	AND (A.LAND_SQUARE = @p_LAND_SQUARE OR  @p_LAND_SQUARE IS NULL)

	AND (A.CONSTRUCT_SQUARE = @p_CONSTRUCT_SQUARE OR  @p_CONSTRUCT_SQUARE IS NULL)

	AND (A.TOTAL_SQUARE = @p_TOTAL_SQUARE OR  @p_TOTAL_SQUARE IS NULL)

	AND (A.BOUNDARY LIKE '%' + @p_BOUNDARY + '%' OR  @p_BOUNDARY IS NULL OR @p_BOUNDARY = '')

	AND (A.HOUSEDES LIKE '%' + @p_HOUSEDES + '%' OR  @p_HOUSEDES IS NULL OR @p_HOUSEDES = '')

	AND (A.PURPOSE_IN_USE LIKE '%' + @p_PURPOSE_IN_USE + '%' OR  @p_PURPOSE_IN_USE IS NULL OR @p_PURPOSE_IN_USE = '')

	AND (A.W_USE_CON LIKE '%' + @P_W_USE_CON + '%' OR  @P_W_USE_CON IS NULL OR @P_W_USE_CON = '')

	AND (A.USE_STATUS LIKE '%' + @p_USE_STATUS + '%' OR  @p_USE_STATUS IS NULL OR @p_USE_STATUS = '')

	AND (A.CONST_STATUS LIKE '%' + @p_CONST_STATUS + '%' OR  @p_CONST_STATUS IS NULL OR @p_CONST_STATUS = '')

	AND (A.OWNER_TYPE LIKE '%' + @p_OWNER_TYPE + '%' OR  @p_OWNER_TYPE IS NULL OR @p_OWNER_TYPE = '')

	AND (A.USE_PERIOD = @p_USE_PERIOD OR  @p_USE_PERIOD IS NULL)

	AND (A.PERSON_HOLDER LIKE '%' + @p_PERSON_HOLDER + '%' OR  @p_PERSON_HOLDER IS NULL OR @p_PERSON_HOLDER = '')

	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.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_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.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 (A.RECORD_STATUS = '1')

	AND (G.DIV_ID LIKE '%' + @P_DIV_ID + '%' OR @P_DIV_ID = '' OR @P_DIV_ID IS NULL)

	AND (G.DIV_CODE LIKE '%' + @P_DIV_CODE + '%' OR @P_DIV_CODE = '' OR @P_DIV_CODE IS NULL)

	AND (

			D.BRANCH_CREATE = @p_BRANCH_ID 

			OR A.BRANCH_CREATE = @p_BRANCH_ID

			OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = ''

		)

	--AND (A.BRANCH_CREATE = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')

	AND (A.RET_FORM LIKE '%' + @p_RET_FORM + '%' OR @p_RET_FORM IS NULL OR @p_RET_FORM = '')

	AND (A.ADDR LIKE '%' + @p_ADDR + '%' OR @p_ADDR IS NULL OR @p_ADDR = '')

	AND (

			(@p_ASSET_STATUS = 'DTL' AND D.AMORT_STATUS LIKE '%' + @p_ASSET_STATUS + '%') 

			OR (@p_ASSET_STATUS = 'BT' AND D.AMORT_STATUS NOT LIKE '%DTL%')

			OR (@p_ASSET_STATUS IS NULL OR @p_ASSET_STATUS = '')

		)

	AND (

			(@p_RET_SEARCH_TYPE = 'IS_ASSET' AND A.ASSET_ID IS NOT NULL) -- Tìm kiếm BDS đã là tài sản NH

			OR (@p_RET_SEARCH_TYPE = 'NOT_ASSET' AND A.ASSET_ID IS NULL) -- Tìm kiếm BDS chưa là tài sản NH (đang hoàn thiện pháp lý)

			OR (@p_RET_SEARCH_TYPE IS NULL) -- Tìm toàn bộ

		)

	-------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	(

		--			((SELECT ROLE_NAME FROM @t_USER_LOGIN_ROLE_TABLE) 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.BRANCH_CREATE 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.BRANCH_CREATE AND BRANCH_TYPE = 'HS')

		--					AND A.BRANCH_CREATE = (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)

		--				)

		--		)

		--)

		-----------BAODNQ: NẾU Ở HỘI SỞ TÌM THẤY NHỮNG PHIẾU THEO PHÒNG BAN------------------

		----------------NẾU Ở DVKD TÌM THẤY PHIẾU THEO ĐƠN VỊ--------------------

		AND(

			A.BRANCH_CREATE = @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'

			)

		)

		ORDER BY A.CREATE_DT DESC

-- PAGING END

END -- PAGING

GO

ALTER PROCEDURE [dbo].[RET_MASTER_SendAppr]

@p_RET_ID VARCHAR(15),

@p_USER_LOGIN VARCHAR(15),

@p_SEND_APPR_DT VARCHAR(20) = NULL

AS

IF(EXISTS (SELECT * FROM RET_MASTER WHERE RET_ID = @p_RET_ID AND IS_SEND_APPR = 'Y'))

BEGIN

	SELECT '-1' as Result, '' RET_ID, '' IS_SEND_APPR,

		N'Gửi phê duyệt thất bại. Thông tin bất động sản đã được gửi yêu cầu phê duyệt trước đó' ErrorDesc

	RETURN '-1'

END

BEGIN TRANSACTION

	UPDATE RET_MASTER SET 

		IS_SEND_APPR = 'Y', 

		SEND_APPR_DT = CONVERT(DATETIME, @p_SEND_APPR_DT, 103),

		SIGN_DT = NULL,

		AUTH_STATUS = 'U'

	WHERE RET_ID = @p_RET_ID

	IF @@ERROR <> 0 GOTO ABORT

	DECLARE @p_IS_SEND_APPR VARCHAR(15) = (SELECT IS_SEND_APPR FROM RET_MASTER WHERE RET_ID = @p_RET_ID)

	DECLARE @p_MESSAGE NVARCHAR(500)

	DECLARE @p_RET_NAME NVARCHAR(1000) = (

		SELECT

		CASE

			WHEN RM.ASSET_ID IS NULL OR RM.ASSET_ID = '' THEN RM.ASS_NAME

			ELSE AM.ASSET_NAME

		END

		FROM RET_MASTER RM

		LEFT JOIN ASS_MASTER AM ON RM.ASSET_ID = AM.ASSET_ID

		WHERE RM.RET_ID = @p_RET_ID

	)

	--------------------------TH gửi phê duyệt có cấp phê duyệt trung gian---------------

	IF(EXISTS(SELECT * FROM RET_MASTER WHERE RET_ID = @p_RET_ID AND SIGN_USER IS NOT NULL))

	BEGIN

		SET @p_MESSAGE = N'Thông tin bất động sản: '+@p_RET_NAME+

			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 bất động sản: '+@p_RET_NAME+

			N' đã được gửi phê duyệt thành công. Vui lòng đợi trưởng đơn vị phê duyệt.'

	END

	--------BAODNQ 16/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_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_RET_ID RET_ID,  @p_IS_SEND_APPR IS_SEND_APPR ,@p_MESSAGE ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' RET_ID, '' IS_SEND_APPR,'' ErrorDesc

		RETURN '-1'

End

GO

ALTER PROCEDURE [dbo].[RET_MASTER_Confirm]

@p_RET_ID VARCHAR(15),

@p_SIGN_USER VARCHAR(15),

@p_SIGN_DT VARCHAR(20) = NULL

AS

IF(EXISTS (SELECT * FROM RET_MASTER WHERE RET_ID = @p_RET_ID AND IS_SEND_APPR = 'Y' AND (SIGN_DT IS NOT NULL OR SIGN_DT <> '')))

BEGIN

	SELECT '-1' as Result, '' RET_ID,

		N'Xác nhận thất bại. Thông tin bất động sản đã được cấp trung gian xác nhận trước đó' ErrorDesc

	RETURN '-1'

END

IF(EXISTS (SELECT * FROM RET_MASTER WHERE RET_ID = @p_RET_ID AND AUTH_STATUS = 'R'))

BEGIN

	SELECT '-1' as Result, '' RET_ID,

		N'Xác nhận thất bại. Thông tin bất động sản đang bị trả về' ErrorDesc

	RETURN '-1'

END

IF(EXISTS(SELECT RET_ID FROM RET_MASTER WHERE RET_ID = @p_RET_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 bất động sản đ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

BEGIN TRANSACTION

	--------------------------

	UPDATE RET_MASTER SET 

		AUTH_STATUS = 'U', 

		SIGN_DT = CONVERT(DATETIME, @p_SIGN_DT, 103)

	WHERE RET_ID = @p_RET_ID

	IF @@ERROR <> 0 GOTO ABORT

	DECLARE @p_RET_NAME NVARCHAR(1000) = (

		SELECT

		CASE

			WHEN RM.ASSET_ID IS NULL OR RM.ASSET_ID = '' THEN RM.ASS_NAME

			ELSE AM.ASSET_NAME

		END

		FROM RET_MASTER RM

		LEFT JOIN ASS_MASTER AM ON RM.ASSET_ID = AM.ASSET_ID

		WHERE RM.RET_ID = @p_RET_ID

	)

	DECLARE @p_MESSAGE NVARCHAR(500) = N' Thông tin bất động sản: ' +@p_RET_NAME+

			N' đã được xác nhận thành công. Vui lòng đợi trưởng đơn vị phê duyệt.'

	--------BAODNQ 16/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_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_RET_ID RET_ID, @p_MESSAGE ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' RET_ID,'' ErrorDesc

		RETURN '-1'

End

GO

ALTER PROCEDURE [dbo].[RET_MASTER_App]

@P_RET_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_MASTER R WHERE R.RET_ID = @P_RET_ID))

	SET @ERRORSYS = 'RETM-00001'

IF @ERRORSYS <> '' 

	BEGIN

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

	RETURN '-1'

END

IF(EXISTS (SELECT * FROM RET_MASTER WHERE RET_ID = @P_RET_ID AND AUTH_STATUS = 'R'))

BEGIN

	SELECT '-1' as Result, 

		N'Phê duyệt thất bại. Thông tin bất động sản đang bị trả về' ErrorDesc

	RETURN '-1'

END

IF(EXISTS (SELECT * FROM RET_MASTER WHERE RET_ID = @P_RET_ID AND AUTH_STATUS = 'A'))

BEGIN

	SELECT '-1' as Result, 

		N'Phê duyệt thất bại. Thông tin bất động sản đã được phê duyệt' ErrorDesc

	RETURN '-1'

END

IF(EXISTS(SELECT RET_ID FROM RET_MASTER WHERE RET_ID = @P_RET_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 bất động sản đ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 RET_ID FROM RET_MASTER WHERE RET_ID = @P_RET_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 bất động sản đ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

	DECLARE @p_ASSET_ID VARCHAR(15)

	SET @p_ASSET_ID = (SELECT ASSET_ID FROM RET_MASTER WHERE RET_ID = @P_RET_ID)

	UPDATE RET_MASTER SET AUTH_STATUS = @P_AUTH_STATUS, CHECKER_ID = @P_CHECKER_ID,

	APPROVE_DT = CONVERT(DATETIME,@P_APPROVE_DT,103)

	WHERE RET_ID = @P_RET_ID	

	--THAY DOI LOAI TAI SAN LA 2

	UPDATE ASS_MASTER SET ASS_TYPE = '3' WHERE ASSET_ID = @p_ASSET_ID

	IF @@Error <> 0 GOTO ABORT

	--------BAODNQ 16/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_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_MASTER_Upd]

@p_RET_ID	varchar(15) = null ,

@p_ASSET_ID VARCHAR(100) = NULL,

@P_ASSET_CODE VARCHAR(100) = NULL,

@P_OWNER NVARCHAR(500) = NULL,

@p_LENGTH	DECIMAL(18,2) = NULL,

@p_WIDTH	DECIMAL(18,2) = NULL,

@p_FLOORS int = NULL,

@p_CURRENT_STATE	nvarchar(100)  = NULL,

@p_RET_TYPE	varchar(15)  = NULL,

@p_STATUS	varchar(15)  = NULL,

@p_LAND_SQUARE	DECIMAL(18,2)  = NULL,

@p_ADDR nvarchar(1000) = NULL, -- hungdv hieu chinh yeu cau anh toi 250520

@p_CONSTRUCT_SQUARE	DECIMAL(18,2)  = NULL,

@p_TOTAL_SQUARE	DECIMAL(18,2)  = NULL,

@p_BOUNDARY	nvarchar(100)  = NULL,

@p_HOUSEDES	nvarchar(100)  = NULL,

@p_PURPOSE_IN_USE	nvarchar(500)  = NULL,

@P_W_USE_CON NVARCHAR(100) = NULL,

@p_USE_STATUS	varchar(15)  = NULL,

@p_CONST_STATUS	varchar(15)  = NULL,

@p_OWNER_TYPE	varchar(15)  = NULL,

@p_USE_PERIOD	int  = NULL,

@p_PERSON_HOLDER	nvarchar(1000)  = NULL,

@p_REASON nvarchar(1000) = NULL,

@p_NOTES	nvarchar(1000)  = NULL,

@p_RECORD_STATUS	varchar(1)  = NULL,

@p_AUTH_STATUS	varchar(1)  = NULL,

@p_MAKER_ID	varchar(15)  = NULL,

@p_CREATE_DT	VARCHAR(20) = NULL,

@p_CHECKER_ID	varchar(15)  = NULL,

@p_APPROVE_DT	VARCHAR(20) = NULL,

@P_BUY_DT VARCHAR(20) = NULL,

@p_USE_FORM VARCHAR(1),

@p_USE_FORM_DETAIL nvarchar(MAX),

@p_USE_SOURCE nvarchar(MAX),

@p_USE_PERIOD_DT VARCHAR(20),

@p_OWNER_TYPE_DETAIL NVARCHAR(MAX),

@p_RET_SAVE_CODE VARCHAR(100),

@p_BRANCH_USE nvarchar(500),

@p_RET_TAX_SCHEDULE XML = NULL,

@p_RET_REPAIR_SUGGEST XML = NULL,

@p_RET_REPAIR_REAL XML = NULL,

@p_FLUCTUATING_DT varchar(20) = null,

@p_REASON_FLUCTUATING nvarchar(max) = null,

----------------BAODNQ 16/2/2022: Thêm cột vào bảng RET_MASTER---------

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

@p_RET_FORM VARCHAR(15) = NULL,

@p_BRANCH_USE_ID VARCHAR(15) = NULL,

@p_ASSET_NAME NVARCHAR(1000) = NULL,

@p_ASSET_DESC NVARCHAR(MAX) = NULL,

@p_BUY_PRICE DECIMAL(18,2) = NULL,

@p_BRANCH_CREATE VARCHAR(15) = NULL

AS

--Validation is here

	DECLARE @ERRORSYS NVARCHAR(15) = ''

	IF ( NOT EXISTS ( SELECT * FROM RET_MASTER WHERE RET_ID = @p_RET_ID))

		SET @ERRORSYS = 'RETM-00001'

	IF @ERRORSYS <> '' 

	BEGIN

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

		RETURN '0'

	END

	IF(EXISTS(SELECT * FROM RET_MASTER WHERE RET_ID = @p_RET_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 bất động sản đã được gửi yêu cầu phê duyệt' ErrorDesc 

		RETURN '-1'

	END

	DECLARE @p_RET_TAX_ROW_NO INT = 0,

			@p_RET_REPAIR_SUG_ROW_NO INT = 0,

			@p_RET_REPAIR_REAL_ROW_NO INT = 0

	Declare @hdoc INT

	Exec sp_xml_preparedocument @hdoc Output,@p_RET_TAX_SCHEDULE

	DECLARE RetTaxSchedule CURSOR FOR

	SELECT *

	FROM OPENXML(@hDoc,'/Root/RetTaxSchedule',2)

	WITH 

	(

				TAX_SCHEDULE_ID VARCHAR(15),

				REQ_PAY_ID VARCHAR(15),

				FRM_DATE varchar(30),

				TO_DATE varchar(30),

				TOTAL_AMT decimal

	)

	OPEN RetTaxSchedule

	Exec sp_xml_preparedocument @hdoc Output,@p_RET_REPAIR_SUGGEST

	DECLARE RetRepairSuggest CURSOR FOR

	SELECT *

	FROM OPENXML(@hDoc,'/Root/RetRepairSuggest',2)

	WITH 

	(

		RET_SUG_REPAIR_ID VARCHAR(15),

		REQ_ID	varchar(15),

		REPAIR_SUG_DT	varchar(30) ,		

		BRANCH_ID varchar(15) ,

		BRANCH_SUGGEST nvarchar(4000),

		TOTAL_AMT_REPAIR decimal(18,2),

		TOTAL_AMT_CONSTRUCT DECIMAL(18,2),

		USER_SUGGEST varchar(15),

		REPAIR_REASON nvarchar(4000),

		REPAIR_CONTENT	nvarchar(4000) 

	)

	OPEN RetRepairSuggest

	Exec sp_xml_preparedocument @hdoc Output,@p_RET_REPAIR_REAL

	DECLARE RetRepairReal CURSOR FOR

	SELECT *

	FROM OPENXML(@hDoc,'/Root/RetRepairReal',2)

	WITH 

	(

		RET_REPAIR_REAL_ID varchar(15),

		REQ_PAY_ID	varchar(15),

		REPAIR_DT	varchar(30) ,		

		BRANCH_REPAIR nvarchar(4000),

		TOTAL_AMT_REPAIR decimal(18,2),

		TOTAL_AMT_CONSTRUCT DECIMAL(18,2),

		REPAIR_CONTENT_REAL nvarchar(4000),

		NOTES	nvarchar(4000) 

	)

	OPEN RetRepairReal

BEGIN TRANSACTION

	IF(EXISTS (SELECT * FROM RET_MASTER WHERE RET_ID = @p_RET_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_MASTER

	SET [BUY_DT] = CONVERT(DATETIME, @P_BUY_DT, 103),

		[ASSET_ID] = @p_ASSET_ID,

		[ASSET_CODE] = @P_ASSET_CODE,

		[OWNER] = @P_OWNER,

		[LENGTH] = @p_LENGTH,

		[WIDTH] = @p_WIDTH,

		[FLOORS] = @p_FLOORS,

		[CURRENT_STATE] = @p_CURRENT_STATE,

		[RET_TYPE] = @p_RET_TYPE,

		[STATUS] = @p_STATUS,

		[LAND_SQUARE] = @p_LAND_SQUARE,

		[CONSTRUCT_SQUARE] = @p_CONSTRUCT_SQUARE,

		[TOTAL_SQUARE] = @p_TOTAL_SQUARE,

		[BOUNDARY] = @p_BOUNDARY,

		[HOUSEDES] = @p_HOUSEDES,

		[PURPOSE_IN_USE] = @p_PURPOSE_IN_USE,

		[W_USE_CON] = @P_W_USE_CON,

		[USE_STATUS] = @p_USE_STATUS,

		[CONST_STATUS] = @p_CONST_STATUS,

		[OWNER_TYPE] = @p_OWNER_TYPE,

		[USE_PERIOD] = @p_USE_PERIOD,

		[PERSON_HOLDER] = @p_PERSON_HOLDER,

		[NOTES] = @p_NOTES,

		[REASON] = @p_REASON,

		[RECORD_STATUS] = @p_RECORD_STATUS,

		[AUTH_STATUS] = @p_AUTH_STATUS,

		[MAKER_ID] = @p_MAKER_ID,

		[CREATE_DT] = CONVERT(DATETIME, @p_CREATE_DT, 103),

		[CHECKER_ID] = @p_CHECKER_ID,

		[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103),

		[USE_FORM] = @p_USE_FORM,

		[USE_FORM_DETAIL] = @p_USE_FORM_DETAIL,

		[USE_SOURCE] = @p_USE_SOURCE,

		[USE_PERIOD_DT] = CONVERT(DATETIME, @p_USE_PERIOD_DT, 103),

		[OWNER_TYPE_DETAIL] = @p_OWNER_TYPE_DETAIL,

		[RET_SAVE_CODE] = @p_RET_SAVE_CODE,

		[BRANCH_USE] = @p_BRANCH_USE,

		ADDR = @p_ADDR, -- hungdv hieu chinh yeu cau anh toi 250520

		[FLUCTUATING_DT] = CONVERT(DATETIME, @p_FLUCTUATING_DT, 103),

		[REASON_FLUCTUATING] = @p_REASON_FLUCTUATING,

		----------------BAODNQ 16/2/2022: Thêm cột vào bảng RET_MASTER---------

		[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),

		[RET_FORM] = @p_RET_FORM,

		[BRANCH_USE_ID] = @p_BRANCH_USE_ID,

		[ASS_NAME] = @p_ASSET_NAME,

		[DESCRIPTION] = @p_ASSET_DESC,

		[ASSET_PRICE] = @p_BUY_PRICE,

		[BRANCH_CREATE] = @p_BRANCH_CREATE

	WHERE RET_ID = @p_RET_ID;

	IF @@Error <> 0 GOTO ABORT

		--UPDATE DETAIL 

		DECLARE

				@TAX_SCHEDULE_ID VARCHAR(15),

				@RET_SUG_REPAIR_ID VARCHAR(15), 

				@RET_REPAIR_REAL_ID varchar(15),

				@d_REQ_PAY_ID VARCHAR(15),

				@d_FRM_DATE varchar(30),

				@d_TO_DATE varchar(30),

				@d_TOTAL_AMT decimal,

				@RET_ID	varchar(15),

				@REQ_ID varchar(15),

				@REPAIR_SUG_DT varchar(30),

				@BRANCH_ID varchar(15),

				@REPAIR_DT varchar(20),

				@BRANCH_REPAIR nvarchar(4000),

				@REPAIR_CONTENT_REAL nvarchar(4000),

				@NOTES nvarchar(4000),

				@BRANCH_SUGGEST nvarchar(4000),

				@TOTAL_AMT decimal,

				@USER_SUGGEST varchar(15),

				@REPAIR_REASON nvarchar(4000),

				@REPAIR_CONTENT nvarchar(4000),

				@d_TOTAL_AMT_REPAIR DECIMAL(18,2),

				@d_TOTAL_AMT_CONSTRUCT DECIMAL(18,2)

		--UPDATE ACCESSORY DETAIL

		DELETE FROM RET_TAX_SCHEDULE WHERE RET_ID = @p_RET_ID

		FETCH NEXT FROM RetTaxSchedule INTO @TAX_SCHEDULE_ID, @d_REQ_PAY_ID,@d_FRM_DATE,@d_TO_DATE,@d_TOTAL_AMT

		WHILE @@FETCH_STATUS = 0

		BEGIN

			print LEN(@TAX_SCHEDULE_ID)

			SET @p_RET_TAX_ROW_NO = @p_RET_TAX_ROW_NO + 1

			---------------PDN thanh toán chặn---------------

			------TH KO CÓ PDN thanh toán------

			IF(NOT EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @d_REQ_PAY_ID))

			BEGIN

				ROLLBACK TRANSACTION

				SELECT '-1' as Result, '' RET_ID, 

				N'Lưới thông tin quản lý định kỳ thuế nhà đất, dòng ' + CONVERT(VARCHAR, @p_RET_TAX_ROW_NO) + 

				N'Phiếu đề nghị thanh toán không tồn tại trong hệ thống' ErrorDesc

				RETURN '-1'

			END

			---------TH PDN thanh toán CHƯA ĐƯỢC DUYỆT---------

			IF(NOT EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @d_REQ_PAY_ID AND AUTH_STATUS_KT = 'A'))

			BEGIN

				ROLLBACK TRANSACTION

				SELECT '-1' as Result, '' RET_ID, 

				N'Lưới thông tin quản lý định kỳ thuế nhà đất, dòng ' + CONVERT(VARCHAR, @p_RET_TAX_ROW_NO) +

				N'Phiếu đề nghị thanh toán chưa được duyệt' ErrorDesc

				RETURN '-1'

			END

			IF(CONVERT(DATETIME, @d_TO_DATE,103) <= CONVERT(DATETIME, @d_FRM_DATE, 103))

			BEGIN

				ROLLBACK TRANSACTION

				SELECT '-1' as Result, '' RET_ID, 

					N'Lưới thông tin quản lý định kỳ thuế nhà đất, dòng ' + CONVERT(VARCHAR, @p_RET_TAX_ROW_NO) + 

					N': Thời gian kết thúc đóng thuế phải lớn hơn thời gian bắt đầu đóng thuế' ErrorDesc

				RETURN '-1'

			END

			IF(LEN(@TAX_SCHEDULE_ID)  = 0)

			BEGIN

				EXEC SYS_CodeMasters_Gen 'RET_TAX_SCHEDULE', @TAX_SCHEDULE_ID out

				IF @TAX_SCHEDULE_ID ='' OR @TAX_SCHEDULE_ID IS NULL GOTO ABORT

			END

			PRINT @TAX_SCHEDULE_ID

			INSERT INTO RET_TAX_SCHEDULE([TAX_SCHEDULE_ID],[RET_ID],[REQ_PAY_ID],[FRM_DATE],[TO_DATE],[TOTAL_AMT],[AUTH_STATUS],[RECORD_STATUS],[MAKER_ID])

			VALUES(@TAX_SCHEDULE_ID,@p_RET_ID ,@d_REQ_PAY_ID , CONVERT(DATETIME, @d_FRM_DATE, 103) ,CONVERT(DATETIME, @d_TO_DATE, 103) ,@d_TOTAL_AMT ,@p_AUTH_STATUS,@p_RECORD_STATUS  ,@p_MAKER_ID  )

			IF @@ERROR <> '' GOTO ABORT

			FETCH NEXT FROM RetTaxSchedule INTO @TAX_SCHEDULE_ID, @d_REQ_PAY_ID,@d_FRM_DATE,@d_TO_DATE,@d_TOTAL_AMT

		END

		PRINT 'RetTaxSchedule'

		--UPDATE RetRepairSuggest

		DELETE FROM RET_REPAIR_SUGGEST WHERE RET_ID = @p_RET_ID

		FETCH NEXT FROM RetRepairSuggest INTO 

			@RET_SUG_REPAIR_ID,   

			@REQ_ID,@REPAIR_SUG_DT,

			@BRANCH_ID,

			@BRANCH_SUGGEST,

			@d_TOTAL_AMT_REPAIR,

			@d_TOTAL_AMT_CONSTRUCT,

			@USER_SUGGEST,

			@REPAIR_REASON,

			@REPAIR_CONTENT

		WHILE @@FETCH_STATUS = 0

		BEGIN

			SET @p_RET_REPAIR_SUG_ROW_NO = @p_RET_REPAIR_SUG_ROW_NO + 1

			---------------TTCT chặn---------------

			------TH KO CÓ TTCT------

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

			BEGIN

				ROLLBACK TRANSACTION

				SELECT '-1' as Result, '' RET_ID, 

				N'Lưới thông tin sửa chữa dự kiến, dòng ' +CONVERT(VARCHAR, @p_RET_REPAIR_SUG_ROW_NO)+

				N'Tờ trình chủ trương không tồn tại trong hệ thống' ErrorDesc

				RETURN '-1'

			END

			---------TH TTCT CHƯA ĐƯỢC DUYỆT---------

			IF(NOT EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID = @REQ_ID AND PROCESS_ID = 'APPROVE'))

			BEGIN

				ROLLBACK TRANSACTION

				SELECT '-1' as Result, '' RET_ID, 

				N'Lưới thông tin sửa chữa dự kiến, dòng ' +CONVERT(VARCHAR, @p_RET_REPAIR_SUG_ROW_NO)+

				N'Tờ trình chủ trương chưa được duyệt' ErrorDesc

				RETURN '-1'

			END

			DECLARE @l_RET_SUG_REPAIR_ID  VARCHAR(15)

			IF LEN(@RET_SUG_REPAIR_ID ) = 0

			BEGIN

				EXEC SYS_CodeMasters_Gen 'RET_REPAIR_SUGGEST', @RET_SUG_REPAIR_ID out

				PRINT @RET_SUG_REPAIR_ID

				IF @RET_SUG_REPAIR_ID ='' OR @RET_SUG_REPAIR_ID IS NULL GOTO ABORT

			END

			INSERT INTO RET_REPAIR_SUGGEST

				([RET_SUG_REPAIR_ID],[RET_ID],[REQ_ID],[REPAIR_SUG_DT],[BRANCH_ID],[BRANCH_SUGGEST],

				[TOTAL_AMT_REPAIR],[TOTAL_AMT_CONSTRUCT],[USER_SUGGEST],[REPAIR_REASON],[REPAIR_CONTENT],[AUTH_STATUS],[RECORD_STATUS],

				[MAKER_ID])

			VALUES

				(@RET_SUG_REPAIR_ID,@p_RET_ID ,@REQ_ID ,CONVERT(DATETIME, @REPAIR_SUG_DT, 103) ,@BRANCH_ID  ,

				@BRANCH_SUGGEST,@d_TOTAL_AMT_REPAIR,@d_TOTAL_AMT_CONSTRUCT,@USER_SUGGEST,@REPAIR_REASON,@REPAIR_CONTENT ,@p_AUTH_STATUS  ,

				@p_RECORD_STATUS ,@p_MAKER_ID  )

			IF @@ERROR <> '' GOTO ABORT

			FETCH NEXT FROM RetRepairSuggest INTO 

				@RET_SUG_REPAIR_ID, 

				@REQ_ID,

				@REPAIR_SUG_DT,

				@BRANCH_ID,

				@BRANCH_SUGGEST,

				@d_TOTAL_AMT_REPAIR,

				@d_TOTAL_AMT_CONSTRUCT,

				@USER_SUGGEST,

				@REPAIR_REASON,

				@REPAIR_CONTENT

		END

		--UPDATE RetRepairReal

		DELETE FROM RET_REPAIR_REAL WHERE RET_ID = @p_RET_ID

		FETCH NEXT FROM RetRepairReal INTO 

			@RET_REPAIR_REAL_ID, 

			@d_REQ_PAY_ID,

			@REPAIR_DT,

			@BRANCH_REPAIR,

			@d_TOTAL_AMT_REPAIR,

			@d_TOTAL_AMT_CONSTRUCT,

			@REPAIR_CONTENT_REAL,

			@NOTES

		WHILE @@FETCH_STATUS = 0

		BEGIN

			SET @p_RET_REPAIR_REAL_ROW_NO = @p_RET_REPAIR_REAL_ROW_NO + 1

			---------------PDN thanh toán chặn---------------

			------TH KO CÓ PDN thanh toán------

			IF(NOT EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @d_REQ_PAY_ID))

			BEGIN

				ROLLBACK TRANSACTION

				SELECT '-1' as Result, '' RET_ID, 

				N'Lưới thông tin sửa chữa thực tế, dòng ' +CONVERT(VARCHAR, @p_RET_REPAIR_REAL_ROW_NO)+

				N'Phiếu đề nghị thanh toán không tồn tại trong hệ thống' ErrorDesc

				RETURN '-1'

			END

			---------TH PDN thanh toán CHƯA ĐƯỢC DUYỆT---------

			IF(NOT EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @d_REQ_PAY_ID AND AUTH_STATUS_KT = 'A'))

			BEGIN

				ROLLBACK TRANSACTION

				SELECT '-1' as Result, '' RET_ID, 

				N'Lưới thông tin sửa chữa thực tế, dòng ' +CONVERT(VARCHAR, @p_RET_REPAIR_REAL_ROW_NO)+

				N'Phiếu đề nghị thanh toán chưa được duyệt' ErrorDesc

				RETURN '-1'

			END

			DECLARE @l_RET_REPAIR_REAL_ID  VARCHAR(15)

			IF LEN(@RET_REPAIR_REAL_ID ) = 0

			BEGIN

				EXEC SYS_CodeMasters_Gen 'RET_REPAIR_REAL', @RET_REPAIR_REAL_ID out

				PRINT @RET_REPAIR_REAL_ID

				IF @RET_REPAIR_REAL_ID ='' OR @RET_REPAIR_REAL_ID IS NULL GOTO ABORT

			END

			INSERT INTO RET_REPAIR_REAL

				([RET_REPAIR_REAL_ID],[RET_ID],[REQ_PAY_ID],[REPAIR_DT],[BRANCH_REPAIR],[TOTAL_AMT_REPAIR],

				[TOTAL_AMT_CONSTRUCT],[REPAIR_CONTENT_REAL],[NOTES] ,  [AUTH_STATUS],[RECORD_STATUS],[MAKER_ID])

			VALUES

				(@RET_REPAIR_REAL_ID,@p_RET_ID,@d_REQ_PAY_ID  ,CONVERT(DATETIME, @REPAIR_DT, 103) ,

				@BRANCH_REPAIR ,@d_TOTAL_AMT_REPAIR,@d_TOTAL_AMT_CONSTRUCT,@REPAIR_CONTENT_REAL,@NOTES ,@p_AUTH_STATUS  ,@p_RECORD_STATUS ,

				@p_MAKER_ID  )

			IF @@ERROR <> '' GOTO ABORT

			FETCH NEXT FROM RetRepairReal INTO 

				@RET_REPAIR_REAL_ID, 

				@d_REQ_PAY_ID,

				@REPAIR_DT,

				@BRANCH_REPAIR,

				@d_TOTAL_AMT_REPAIR,

				@d_TOTAL_AMT_CONSTRUCT,

				@REPAIR_CONTENT_REAL,

				@NOTES

		END

		CLOSE RetTaxSchedule

		DEALLOCATE RetTaxSchedule

		CLOSE RetRepairSuggest

		DEALLOCATE RetRepairSuggest

		CLOSE RetRepairReal

		DEALLOCATE RetRepairReal

		---------------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_ID ORDER BY APPROVE_DT DESC) = 'UPDATE')

		BEGIN

			WITH RESULT AS(

				SELECT TOP 1 * FROM PL_PROCESS WHERE REQ_ID = @p_RET_ID

				ORDER BY APPROVE_DT DESC

			)

			DELETE FROM RESULT

		END

		--------BAODNQ 16/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_ID,       

					'UPDATE',

					@p_MAKER_ID,        

					GETDATE(), 

					N'Cập nhật thông tin bất động sản thành công' ,      

					N'Cập nhật thông tin bất động sản'       

				)

		IF @@ERROR <> 0 GOTO ABORT

		---------BAODNQ 15/3/2022: Lấy tên BDS-----

		DECLARE @p_RET_NAME NVARCHAR(MAX) =(

			SELECT  B.ASSET_NAME

			FROM RET_MASTER A

			LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID

			WHERE A.RET_ID = @p_RET_ID

		)

COMMIT TRANSACTION

		SELECT '0' as Result, @p_RET_ID  RET_ID, @p_RET_NAME RET_NAME,'' ErrorDesc

		RETURN '0'

ABORT:

BEGIN

		CLOSE RetTaxSchedule

		DEALLOCATE RetTaxSchedule

		CLOSE RetRepairSuggest

		DEALLOCATE RetRepairSuggest

		CLOSE RetRepairReal

		DEALLOCATE RetRepairReal

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' RET_ID, '' RET_NAME,'' ErrorDesc

		RETURN '-1'

End