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_Ins]

	@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

	)

	DECLARE @p_RATE_REQ_NO VARCHAR(100)

	-----------------TẠO SỐ PHIẾU----------------

	EXEC TR_RATE_SUPPLIER_CODE_GenKey 

		'TR_RATE_SUPPLIER_MASTER', '', '', @p_BRANCH_ID, @p_DEP_ID, @p_RATE_REQ_NO OUT

	PRINT @p_RATE_REQ_NO

	IF(EXISTS(SELECT * FROM TR_RATE_SUPPLIER_MASTER WHERE RATE_REQ_NO = @p_RATE_REQ_NO))

	BEGIN

		ROLLBACK TRANSACTION

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

			N'Phiếu đánh giá NCC số: ' +@p_RATE_REQ_NO+ N' đã tồn tại'  ErrorDesc

		RETURN '-1'

	END

	-------INSERT MASTER--------

	DECLARE @p_RATE_ID VARCHAR(15)

	EXEC SYS_CodeMasters_Gen 'TR_RATE_SUPPLIER_MASTER' ,@p_RATE_ID OUT

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

	PRINT 'PASS TAO ID'

	IF(@p_BRANCH_TYPE <> 'HS')

	BEGIN

		SET @p_DEP_ID = NULL

	END

	INSERT INTO TR_RATE_SUPPLIER_MASTER

		(RATE_ID, RATE_REQ_NO, SUP_ID, RATE_FROM_DT, RATE_TO_DT, HH_ID, RECORD_STATUS, CREATE_DT, AUTH_STATUS,

		MAKER_ID, CHECKER_ID, APPROVE_DT, PROCESS_STATUS, BRANCH_ID, TOTAL_POINT, COMMENT, PROPOSE, 

		DECISION, NOTES, IS_SEND_APPR, SEND_APPR_DT, SIGN_USER, SIGN_DT, DEP_ID, CONTRACT_ID, ATTENTION_NOTES,

		BASE_POINT)

	VALUES(@p_RATE_ID, @p_RATE_REQ_NO, @p_SUP_ID, CONVERT(DATETIME, @p_RATE_FROM_DT, 103), CONVERT(DATETIME, @p_RATE_TO_DT, 103), @p_HH_ID, @p_RECORD_STATUS,

			CONVERT(DATETIME , @p_CREATE_DT, 103), @p_AUTH_STATUS, @p_MAKER_ID, @p_CHECKER_ID, CONVERT(DATETIME, @p_APPROVE_DT, 103),

			@p_PROCESS_STATUS, @p_BRANCH_ID, @p_TOTAL_POINT, @p_COMMENT, @p_PROPOSE, @p_DECISION, @p_NOTES,

			@p_IS_SEND_APPR, CONVERT(DATETIME, @p_SEND_APPR_DT, 103), @p_SIGN_USER, CONVERT(DATETIME, @p_SIGN_DT, 103),

			@p_DEP_ID, @p_CONTRACT_ID, @p_ATTENTION_NOTES, @p_BASE_POINT)

	IF @@ERROR <> 0 GOTO ABORT

	PRINT 'PASS INSERT MASTER'

	----------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 'INSERT COMPLETE'

	--------------INSERT lưu bước xử lý----------

	INSERT INTO dbo.PL_REQUEST_PROCESS

		(

		    REQ_ID,

		    PROCESS_ID,

		    STATUS,

		    ROLE_USER,

		    BRANCH_ID,

		    CHECKER_ID,

		    APPROVE_DT,

		    PARENT_PROCESS_ID,

		    IS_LEAF,

		    COST_ID,

		    DVDM_ID,

		    NOTES,

		    IS_HAS_CHILD

		)

		VALUES

		(   @p_RATE_ID,        -- REQ_ID - varchar(15)

		    'NEW',        -- PROCESS_ID - varchar(10)

		    'C',        -- STATUS - varchar(5)

		    '',        -- ROLE_USER - varchar(50)

		    '',        -- 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ờ gửi phê duyệt',       -- NOTES - nvarchar(500)

		    NULL       -- IS_HAS_CHILD - bit

		)

COMMIT TRANSACTION

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

RETURN '0'

ABORT:

BEGIN

	CLOSE RateSupDetail

	DEALLOCATE RateSupDetail

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

	RETURN '-1'

END