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

ALTER   PROCEDURE [dbo].[TR_RATE_SUPPLIER_MASTER_Upd]

	@p_RATE_ID VARCHAR(15) = NULL,

	@p_RATE_REQ_NO VARCHAR(50) = NULL,

	@p_SUP_ID VARCHAR(15) = NULL,

	@p_RATE_FROM_DT VARCHAR(20) = NULL,

	@p_RATE_TO_DT VARCHAR(20) = NULL,

	@p_HH_ID VARCHAR(15) = NULL,

	@p_RECORD_STATUS VARCHAR(1) = NULL,

	@p_CREATE_DT VARCHAR(20) = NULL,

	@p_AUTH_STATUS VARCHAR(1) = NULL,

	@p_MAKER_ID VARCHAR(15) = NULL,

	@p_CHECKER_ID VARCHAR(15) = NULL,

	@p_APPROVE_DT VARCHAR(20) = NULL,

	@p_PROCESS_STATUS VARCHAR(15) = NULL,

	@p_BRANCH_ID VARCHAR(15) = NULL,

	@p_DEP_ID VARCHAR(15) = NULL,

	@p_TOTAL_POINT INT = 0,

	@p_COMMENT nvarchar(MAX) = NULL,

	@p_PROPOSE nvarchar(MAX) = NULL,

	@p_DECISION varchar(15) = NULL,

	@p_NOTES nvarchar(MAX) = NULL,

	@p_IS_SEND_APPR VARCHAR(1) = NULL,

	@p_SEND_APPR_DT VARCHAR(20) = NULL,

	@p_SIGN_USER VARCHAR(15) = NULL,

	@p_SIGN_DT VARCHAR(20) = NULL,

	@p_CONTRACT_ID VARCHAR(15) = NULL,

	@p_ATTENTION_NOTES NVARCHAR(1000) = NULL,

	@p_BASE_POINT INT = NULL,

	@p_TR_RATE_SUP_DT_XML XML = NULL

AS

IF(CONVERT(DATETIME, @p_RATE_TO_DT, 103) < CONVERT(DATETIME, @p_RATE_FROM_DT, 103))

BEGIN

	SELECT '-1' as Result, ''  RATE_ID,

		N'Thời gian đánh giá đến không được nhỏ hơn thời gian đánh giá từ' ErrorDesc

	RETURN '-1'

END

IF(ISNULL(@p_CONTRACT_ID,'')='')

BEGIN

	SELECT '-1' as Result, ''  RATE_ID,

		N'Dữ liệu nhập không hợp lệ. Hợp đồng tham chiếu không được để trống' ErrorDesc

	RETURN '-1'

END

BEGIN TRANSACTION

	DECLARE @p_BRANCH_TYPE VARCHAR(50) = (

		SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_ID

	)

	IF(EXISTS(SELECT * FROM TR_RATE_SUPPLIER_MASTER WHERE RATE_REQ_NO = @p_RATE_REQ_NO AND RATE_ID <> @p_RATE_ID))

	BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, ''  RATE_ID, 

			N'Mã phiếu đánh giá NCC đã tồn tại' ErrorDesc

		RETURN '-1'

	END

	------nếu BRANCH_ID <> 'HS', ko cần lấy DEP_ID

	IF(@p_BRANCH_TYPE <> 'HS')

	BEGIN

		SET @p_DEP_ID = ''

	END

	--------UPDATE MASTER-------

	UPDATE TR_RATE_SUPPLIER_MASTER SET

		RATE_REQ_NO = @p_RATE_REQ_NO,

		SUP_ID = @p_SUP_ID,

		RATE_FROM_DT = CONVERT(DATETIME,@p_RATE_FROM_DT,103),

		RATE_TO_DT = CONVERT(DATETIME,@p_RATE_TO_DT,103),

		HH_ID = @p_HH_ID,

		RECORD_STATUS = @p_RECORD_STATUS,

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

		AUTH_STATUS = @p_AUTH_STATUS,

		MAKER_ID = @p_MAKER_ID,

		CHECKER_ID = @p_CHECKER_ID,

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

		PROCESS_STATUS = @p_PROCESS_STATUS,

		BRANCH_ID = @p_BRANCH_ID,

		TOTAL_POINT = @p_TOTAL_POINT,

		COMMENT = @p_COMMENT,

		PROPOSE = @p_PROPOSE,

		DECISION = @p_DECISION,

		NOTES = @p_NOTES,

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

		DEP_ID = @p_DEP_ID,

		CONTRACT_ID = @p_CONTRACT_ID,

		ATTENTION_NOTES = @p_ATTENTION_NOTES,

		BASE_POINT = @p_BASE_POINT

	WHERE RATE_ID = @p_RATE_ID

	IF @@ERROR <> 0 GOTO ABORT

	PRINT 'PASS UPDATE MASTER'

	IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_RATE_ID AND STATUS = 'R' AND PROCESS_ID = 'APPNEW'))

	BEGIN

		UPDATE PL_REQUEST_PROCESS SET

			STATUS = 'U'

		WHERE REQ_ID = @p_RATE_ID AND PROCESS_ID = 'APPNEW' AND STATUS = 'R'

	END

	DELETE TR_RATE_SUPPLIER_DT WHERE RATE_ID = @p_RATE_ID

	IF @@ERROR <> 0 GOTO ABORT

	----------GET XML----------

	DECLARE @hdoc INT

	Exec sp_xml_preparedocument @hdoc Output,@p_TR_RATE_SUP_DT_XML

	DECLARE RateSupDetail CURSOR FOR

	SELECT *

	FROM OPENXML(@hdoc, 'Root/RateSupDetail', 2)

	WITH

	(

		RATE_CRITERIA NVARCHAR(500),

		RATE_POINT INT,

		SUP_ID_1 VARCHAR(15),

		TR_CONTRACT_ID_1 VARCHAR(15),

		SUP_POINT_1 INT,

		SUP_ID_2 VARCHAR(15),

		TR_CONTRACT_ID_2 VARCHAR(15),

		SUP_POINT_2 INT,

		SUP_ID_3 VARCHAR(15),

		TR_CONTRACT_ID_3 VARCHAR(15),

		SUP_POINT_3 INT,

		SUP_ID_4 VARCHAR(15),

		TR_CONTRACT_ID_4 VARCHAR(15),

		SUP_POINT_4 INT,

		NOTES NVARCHAR(MAX)

	)

	OPEN RateSupDetail

	PRINT 'PASS OPEN CURSOR'

	DECLARE @p_RATE_CRITERIA NVARCHAR(500),

			@p_RATE_POINT INT,

			@p_SUP_ID_1 VARCHAR(15),

			@p_TR_CONTRACT_ID_1 VARCHAR(15),

			@p_SUP_POINT_1 INT,

			@p_SUP_ID_2 VARCHAR(15),

			@p_TR_CONTRACT_ID_2 VARCHAR(15),

			@p_SUP_POINT_2 INT,

			@p_SUP_ID_3 VARCHAR(15),

			@p_TR_CONTRACT_ID_3 VARCHAR(15),

			@p_SUP_POINT_3 INT,

			@p_SUP_ID_4 VARCHAR(15),

			@p_TR_CONTRACT_ID_4 VARCHAR(15),

			@p_SUP_POINT_4 INT,

			@p_NOTES_DT NVARCHAR(MAX)

	------INSERT DT---------

	DECLARE @p_RATE_DT_ID VARCHAR(15)

	FETCH NEXT FROM RateSupDetail INTO

		@p_RATE_CRITERIA,

		@p_RATE_POINT,

		@p_SUP_ID_1,

		@p_TR_CONTRACT_ID_1,

		@p_SUP_POINT_1,

		@p_SUP_ID_2,

		@p_TR_CONTRACT_ID_2,

		@p_SUP_POINT_2,

		@p_SUP_ID_3,

		@p_TR_CONTRACT_ID_3,

		@p_SUP_POINT_3,

		@p_SUP_ID_4,

		@p_TR_CONTRACT_ID_4,

		@p_SUP_POINT_4,

		@p_NOTES_DT

	WHILE @@FETCH_STATUS = 0

	BEGIN

		EXEC SYS_CodeMasters_Gen 'TR_RATE_SUPPLIER_DT' ,@p_RATE_DT_ID OUT

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

		INSERT INTO TR_RATE_SUPPLIER_DT

			(RATE_DT_ID, RATE_ID, RATE_CRITERIA, RATE_POINT, SUP_ID_1, TR_CONTRACT_ID_1, SUP_POINT_1,

			SUP_ID_2, TR_CONTRACT_ID_2, SUP_POINT_2, SUP_ID_3,TR_CONTRACT_ID_3, SUP_POINT_3, 

			SUP_ID_4, TR_CONTRACT_ID_4, SUP_POINT_4, NOTES)

		VALUES(@p_RATE_DT_ID, @p_RATE_ID, @p_RATE_CRITERIA, @p_RATE_POINT, @p_SUP_ID_1, @p_TR_CONTRACT_ID_1, 

				@p_SUP_POINT_1, @p_SUP_ID_2, @p_TR_CONTRACT_ID_2, @p_SUP_POINT_2, @p_SUP_ID_3, @p_TR_CONTRACT_ID_3, 

				@p_SUP_POINT_3, @p_SUP_ID_4, @p_TR_CONTRACT_ID_4, @p_SUP_POINT_4, @p_NOTES_DT)

		IF @@ERROR <> 0 GOTO ABORT

		PRINT 'PASS INSERT DT'

		----FETCH NEXT----

		FETCH NEXT FROM RateSupDetail INTO

			@p_RATE_CRITERIA,

			@p_RATE_POINT,

			@p_SUP_ID_1,

			@p_TR_CONTRACT_ID_1,

			@p_SUP_POINT_1,

			@p_SUP_ID_2,

			@p_TR_CONTRACT_ID_2,

			@p_SUP_POINT_2,

			@p_SUP_ID_3,

			@p_TR_CONTRACT_ID_3,

			@p_SUP_POINT_3,

			@p_SUP_ID_4,

			@p_TR_CONTRACT_ID_4,

			@p_SUP_POINT_4,

			@p_NOTES_DT

	END

	CLOSE RateSupDetail

	DEALLOCATE RateSupDetail

	PRINT 'PASS INSERT DT'

COMMIT TRANSACTION

SELECT '0' as Result, @p_RATE_ID  RATE_ID, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

	CLOSE RateSupDetail

	DEALLOCATE RateSupDetail

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

	RETURN '-1'

END