ALTER PROCEDURE dbo.TR_CONTRACT_Ins
@p_CONTRACT_CODE	varchar(150)  = NULL,
@p_CONTRACT_NAME	nvarchar(200)  = NULL,
@p_CONTRACT_TYPE	varchar(1)  = NULL,
@p_BID_ID	varchar(15)  = NULL,
@p_SUP_ID	varchar(15)  = NULL,
@p_TOTAL_AMT	decimal(18)  = NULL,
@p_DELIVERY_DT	VARCHAR(20) = NULL,
@p_FORM1	varchar(4)  = NULL,
@p_VOUCHER_ID1	varchar(20)  = NULL,
@p_BANK1	varchar(4)  = NULL,
@p_AMOUNT1	decimal(18)  = NULL,
@p_EXP_DT1	VARCHAR(20) = NULL,
@p_RATE1	decimal(18,2)  = NULL,
@p_FORM2	varchar(4)  = NULL,
@p_VOUCHER_ID2	varchar(20)  = NULL,
@p_BANK2	varchar(4)  = NULL,
@p_AMOUNT2	decimal(18)  = NULL,
@p_EXP_DT2	VARCHAR(20) = NULL,
@p_RATE2	decimal(18,2)  = NULL,
@p_REQ_DOC_ID	nvarchar(50)  = NULL,
@p_REQ_DOC_CONTENT	nvarchar(1000)  = NULL,
@p_APPROVE_VALUE	decimal(18)  = NULL,
@p_SIGN_DT	VARCHAR(20) = NULL,
@p_CONSTRUCT_PROGRESS	decimal(18,2)  = 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_START_DT	VARCHAR(20) = NULL,
@p_END_DT VARCHAR(20) = NULL,
@p_CONT_TYPE VARCHAR(10)=NULL,
@p_CUST_ID VARCHAR(15) = NULL,
@p_BRANCH_ID VARCHAR(15)= NULL,
@p_EXP_CONTRACT INT = NULL,
@p_DEPOSIT_AMT DECIMAL(18,0) = NULL,
@p_TYPE_PERIOD VARCHAR(15) = NULL,
@p_DEPOSIT_NOTE NVARCHAR(1000) = NULL,
@p_LISTASSET XML = NULL,
@p_ListTrREQFile XML,
@p_LISTPAYMENT XML = NULL,
-----------------------BAODNQ 25/2/2022 : Thêm tham số-------------
@p_IS_SEND_APPR VARCHAR(1) = NULL,
@p_SEND_APPR_DT VARCHAR(20) = NULL,
@p_ADDR NVARCHAR(1000) = NULL,
@p_SUP_CODE VARCHAR(20) = NULL
AS
	DECLARE @sErrorCode VARCHAR(20) = ''
	DECLARE @TEMP TABLE
			(
				[KEY] varchar(15),
				[REF_ID] varchar(15),
				[TYPE] varchar(50)
			)
	--IF (@p_CONTRACT_CODE IS NOT NULL AND @p_CONTRACT_CODE <> '')
	--BEGIN
	--	IF EXISTS(SELECT * FROM TR_CONTRACT A WHERE A.CONTRACT_CODE = @p_CONTRACT_CODE)
	--	BEGIN
	--		SET @sErrorCode = 'TR-00001'
	--	END
	--END
	----------------BAODNQ 8/11/2022 : BỎ RÀNG BUỘC TRÙNG SỐ HỢP ĐỒNG------------
	--IF(@p_CONTRACT_CODE IS NOT NULL AND @p_CONTRACT_CODE <> '')
	--BEGIN
	--	IF(EXISTS (SELECT * FROM TR_CONTRACT WHERE CONTRACT_CODE = @p_CONTRACT_CODE))
	--	BEGIN
	--		DECLARE @p_EXISTS_CONTRACT_ID VARCHAR(15) = (
	--			SELECT TOP 1 CONTRACT_ID FROM TR_CONTRACT WHERE CONTRACT_CODE = @p_CONTRACT_CODE
	--		)
	--		SELECT '-1' AS Result, '' CONTRACT_ID,
	--			N'Số hợp đồng: ' + @p_CONTRACT_CODE + N' đã tồn tại trên hệ thống ứng với ID hợp đồng: ' 
	--			+ @p_EXISTS_CONTRACT_ID + N'. Vui lòng nhập số hợp đồng khác' ErrorDesc
	--		RETURN '-1'
	--	END
	--END

	IF 	@sErrorCode <> ''
	BEGIN
		SELECT '-1' as Result, ''  CONTRACT_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @sErrorCode
		RETURN '-1'
	END

	----------------BAODNQ 14/11/2022 : NẾU LOẠI HỢP ĐỒNG = DK, KO BẮT BUỘC NHẬP LƯỚI DANH SÁCH HÀNG HÓA------------------
	---------------NGƯỢC LẠI NẾU LÀ (MS, NT), BẮT BUỘC NHẬP LƯỚI DANH SÁCH HÀNG HÓA----------------
	DECLARE @p_TR_CONTRACT_DT_XML_COUNT INT
	SET @p_TR_CONTRACT_DT_XML_COUNT = (
		SELECT @p_LISTASSET.value('count(/Root/AssetDetail/GOODS_ID)', 'INT') AS 'COUNT'
	)
	PRINT @p_TR_CONTRACT_DT_XML_COUNT
	IF(@p_CONT_TYPE <> 'DK' AND @p_TR_CONTRACT_DT_XML_COUNT <= 0)
	BEGIN
		SELECT '-1' Result, '' AS CONTRACT_ID, N'Đối với hợp đồng không phải là hợp đồng định kỳ, lưới danh sách hàng hóa bắt buộc nhập' AS ErrorDesc 
		RETURN '-1'
	END
	-----------------ENDBAODNQ-------------------

	Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output,@p_LISTASSET
	DECLARE AssetDetail CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/AssetDetail',2)
	WITH 
	(
		GOODS_ID	varchar(15),
		UNIT_ID	varchar(15),
		QUANTITY	decimal(18),
		PRICE	decimal(18),
		NOTES	nvarchar(1000),
		GD_NAME_REAL NVARCHAR(500),
		RECURRING  VARCHAR(5),
		UNIT_NAME_REAL NVARCHAR(100),
		-------------------BAODNQ 23/2/2022: Thêm cột vào bảng TR_CONTRACT_DT-------------
		RENT_PRICE DECIMAL(18,0),
		BUILDING_ID VARCHAR(15)
	)
	OPEN AssetDetail

	Exec sp_xml_preparedocument @hdoc Output,@p_LISTPAYMENT
	DECLARE PaymentDetail CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/PaymentDetail',2)
	WITH 
	(
		PAY_PHASE	NVARCHAR(1000),
		EXPECTED_DT	VARCHAR(20),
		[PERCENT]	decimal(18,2),
		[AMOUNT]	decimal(18),
		PAY_STATUS varchar(1),
		NOTES	nvarchar(1000),
		PAY_AMOUNT DECIMAL(18),
		PAY_DT VARCHAR(20)
	)
	OPEN PaymentDetail
	PRINT 'PASS KHOI TAO'
BEGIN TRANSACTION
		--- 03.04.2023 KIỂM TRA NẾU NHÀ CUNG CẤP HIỆN HÀNH ĐANG TRỎ TỚI PO THÌ PHẢI CẢNH BÁO XEM XÉT LẠI & XÓA PO LƯU NHÁP ĐI
		IF(EXISTS(SELECT * FROM TR_PO_MASTER WHERE REQ_DOC_ID=@p_REQ_DOC_ID AND SUP_ID =@p_SUP_ID))
		BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, '' AS CONTRACT_ID, N'Tạo hợp đồng thất bại. Hiện phiếu yêu cầu mua sắm này đang có PO số: ' +
							(SELECT TOP 1 ISNULL(PO_CODE,'') FROM TR_PO_MASTER WHERE REQ_DOC_ID=@p_REQ_DOC_ID AND SUP_ID =@p_SUP_ID)+
								N' sử dụng. Vui lòng kiểm tra lại PO và xóa PO này khỏi hệ thống' AS ErrorDesc 
				RETURN '-1'
		END
		--- END 03.04.2023
		IF(@p_CONTRACT_NAME ='' OR @p_CONTRACT_NAME IS  NULL)
		BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, '' AS CONTRACT_ID, N'Tên hợp đồng không được để trống' AS ErrorDesc 
				RETURN '-1'
		END

		---------------BAODNQ 12/12/2022 : HOT_FIX TẠM THỜI-------------------
		--------------NẾU KO TRUYỀN SUP_ID THÌ TỪ SUP_CODE TÌM KIẾM RA SUP_ID---------
		IF((@p_SUP_ID IS NULL OR @p_SUP_ID = '') AND @p_SUP_CODE IS NOT NULL AND @p_SUP_CODE <> '')
		BEGIN
			SET @p_SUP_ID = (SELECT TOP 1 SUP_ID FROM CM_SUPPLIER WHERE SUP_CODE = @p_SUP_CODE)
		END
		--------------ENDBAODNQ 12/12/2022----------------------------

		--insert master
		DECLARE @l_CONTRACT_ID VARCHAR(15)
		EXEC SYS_CodeMasters_Gen 'TR_CONTRACT', @l_CONTRACT_ID out
		IF @l_CONTRACT_ID='' OR @l_CONTRACT_ID IS NULL GOTO ABORT
		PRINT'PASS CAP ID'
		-----------------------BAODNQ 25/2/2022 : Thêm cột vào bảng TR_CONTRACT-------------
		INSERT INTO TR_CONTRACT([CONTRACT_ID],[CONTRACT_CODE],[CONTRACT_NAME],[CONTRACT_TYPE],[BID_ID],[SUP_ID],
		[TOTAL_AMT],[DELIVERY_DT],[FORM1],[VOUCHER_ID1],[BANK1],[AMOUNT1],
		[EXP_DT1],[RATE1],[FORM2],[VOUCHER_ID2],[BANK2],[AMOUNT2],[EXP_DT2],
		[RATE2],[REQ_DOC_ID],[REQ_DOC_CONTENT],[APPROVE_VALUE],[SIGN_DT],[CONSTRUCT_PROGRESS],
		[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[START_DT],[END_DT],CONT_TYPE, CUST_ID, 
		BRANCH_ID,EXP_CONTRACT,DEPOSIT_AMT,TYPE_PERIOD,DEPOSIT_NOTE, IS_SEND_APPR, SEND_APPR_DT, [ADDRESS])
		VALUES(@l_CONTRACT_ID ,@p_CONTRACT_CODE ,@p_CONTRACT_NAME, @p_CONTRACT_TYPE ,@p_BID_ID ,@p_SUP_ID,
		CONVERT(DECIMAL,@p_TOTAL_AMT) ,
		(CASE WHEN @p_DELIVERY_DT  IS NOT NULL AND @p_DELIVERY_DT <> '' then CONVERT(DATETIME, @p_DELIVERY_DT, 103) ELSE NULL END) ,
		@p_FORM1 ,@p_VOUCHER_ID1 ,@p_BANK1 ,CONVERT(DECIMAL,@p_AMOUNT1) ,(CASE WHEN @p_EXP_DT1 IS NOT NULL AND @p_EXP_DT1 <> '' then CONVERT(DATETIME, @p_EXP_DT1, 103) ELSE NULL END) 
		,CONVERT(DECIMAL,@p_RATE1) ,@p_FORM2 ,@p_VOUCHER_ID2 ,@p_BANK2 ,CONVERT(DECIMAL,@p_AMOUNT2),
		(CASE WHEN @p_EXP_DT2 IS NOT NULL AND @p_EXP_DT2 <> '' then CONVERT(DATETIME, @p_EXP_DT2, 103) ELSE NULL END) ,CONVERT(DECIMAL,@p_RATE2) ,@p_REQ_DOC_ID ,@p_REQ_DOC_CONTENT,
		CONVERT(DECIMAL,@p_APPROVE_VALUE),(CASE WHEN @p_SIGN_DT IS NOT NULL AND @p_SIGN_DT <> '' then CONVERT(DATETIME, @p_SIGN_DT, 103) ELSE NULL END),@p_CONSTRUCT_PROGRESS ,
		@p_NOTES ,'1' ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,'E',
		@p_CHECKER_ID ,
		(CASE WHEN @p_APPROVE_DT IS NOT NULL AND @p_APPROVE_DT <> '' then CONVERT(DATETIME, @p_APPROVE_DT, 103) ELSE NULL END),
		(CASE WHEN @p_START_DT  IS NOT NULL AND @p_START_DT <> '' then CONVERT(DATETIME,@p_START_DT,103) ELSE NULL END),
		(CASE WHEN @p_END_DT  IS NOT NULL AND @p_END_DT <> '' then CONVERT(DATETIME,@p_END_DT,103) ELSE NULL END),@p_CONT_TYPE,@p_CUST_ID,@p_BRANCH_ID,@p_EXP_CONTRACT,@p_DEPOSIT_AMT,@p_TYPE_PERIOD,@p_DEPOSIT_NOTE, @p_IS_SEND_APPR, CONVERT(DATETIME, @p_SEND_APPR_DT, 103),
		@p_ADDR)
		IF @@Error <> 0 GOTO ABORT
		PRINT 'INSERT MASTER SUCCESS'
		--insert asset detail tương đương như NỘI DUNG trong bảng contract detail
		Declare 
		@GOODS_ID	varchar(15),
		@UNIT_ID	varchar(15),
		@QUANTITY	decimal(18),
		@pRICE	decimal(18),
		@NOTES	nvarchar(1000),
		@pAY_PHASE	NVARCHAR(1000),
		@EXPECTED_DT	VARCHAR(20),
		@pERCENT	decimal(18,2),
		@AMOUNT	decimal(18),
		@pAYMENT_STATUS VARCHAR(1),
		@pAY_DT VARCHAR(20),
		@pAY_AMOUNT DECIMAL(18),
		@GD_NAME_REAL NVARCHAR(50),
		@RECURRING VARCHAR(5),
		@UNIT_NAME_REAL NVARCHAR(100),
		-------------------BAODNQ 23/2/2022: Thêm cột vào bảng TR_CONTRACT_DT-------------
		@RENT_PRICE DECIMAL(18,0),
		@BUILDING_ID VARCHAR(15)

		--Insert contract detail
		DECLARE @l_CD_ID VARCHAR(15)
		FETCH NEXT FROM AssetDetail INTO @GOODS_ID,@UNIT_ID,@QUANTITY,@pRICE,@NOTES,@GD_NAME_REAL,@RECURRING,@UNIT_NAME_REAL, @RENT_PRICE, @BUILDING_ID
		WHILE @@FETCH_STATUS = 0	
		BEGIN
			EXEC SYS_CodeMasters_Gen 'TR_CONTRACT_DT', @l_CD_ID out
			IF @l_CD_ID='' OR @l_CD_ID IS NULL GOTO ABORT
			INSERT INTO TR_CONTRACT_DT([CD_ID],[CONTRACT_ID],[GOODS_ID],[UNIT_ID],[QUANTITY],[PRICE],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT], [GD_NAME_REAL], QUANTITY_USE,RECURRING,UNIT_NAME_REAL,RENT_PRICE,BUILDING_ID)
			VALUES(@l_CD_ID ,@l_CONTRACT_ID,@GOODS_ID,@UNIT_ID,@QUANTITY,@pRICE,@NOTES,@p_RECORD_STATUS,
				@p_MAKER_ID,CONVERT(DATETIME, @p_CREATE_DT, 103),@p_AUTH_STATUS,@p_CHECKER_ID,(CASE WHEN @p_APPROVE_DT IS NOT NULL AND @p_APPROVE_DT <> '' then CONVERT(DATETIME, @p_APPROVE_DT, 103) ELSE NULL END),@GD_NAME_REAL,0,@RECURRING,@UNIT_NAME_REAL, @RENT_PRICE, @BUILDING_ID)
			IF @@Error <> 0 GOTO ABORT			
		-- next Group_Id
			FETCH NEXT FROM AssetDetail INTO @GOODS_ID,@UNIT_ID,@QUANTITY,@pRICE,@NOTES,@GD_NAME_REAL,@RECURRING,@UNIT_NAME_REAL, @RENT_PRICE, @BUILDING_ID
		END
		
		--insert payment detail
		DECLARE @l_PAY_ID VARCHAR(15)
		FETCH NEXT FROM PaymentDetail INTO @pAY_PHASE,@EXPECTED_DT,@pERCENT,@AMOUNT,@pAYMENT_STATUS,@NOTES,@pAY_AMOUNT,@pAY_DT

		WHILE @@FETCH_STATUS = 0
		BEGIN
			EXEC SYS_CodeMasters_Gen 'TR_CONTRACT_PAYMENT', @l_PAY_ID out
			IF @l_PAY_ID='' OR @l_PAY_ID IS NULL GOTO ABORT
			INSERT INTO TR_CONTRACT_PAYMENT([PAY_AMOUNT],[PAY_DT], [PAY_ID],[CONTRACT_ID],[PAY_PHASE],[EXPECTED_DT],[PERCENT],[AMOUNT],[PAY_STATUS],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT])
			VALUES(@pAY_AMOUNT,(CASE WHEN @pAY_DT IS NOT NULL AND @pAY_DT <> '' then CONVERT(DATETIME, @pAY_DT, 103) ELSE NULL END), @l_PAY_ID ,@l_CONTRACT_ID ,@pAY_PHASE ,CONVERT(DATETIME, @EXPECTED_DT, 103) ,@pERCENT,@AMOUNT,@pAYMENT_STATUS ,@NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,(CASE WHEN @p_APPROVE_DT IS NOT NULL AND @p_APPROVE_DT <> '' then CONVERT(DATETIME, @p_APPROVE_DT, 103) ELSE NULL END))
			IF @@Error <> 0 GOTO ABORT
			FETCH NEXT FROM PaymentDetail INTO @pAY_PHASE,@EXPECTED_DT,@pERCENT,@AMOUNT,@pAYMENT_STATUS,@NOTES,@pAY_AMOUNT,@pAY_DT
		END
		CLOSE AssetDetail
		DEALLOCATE AssetDetail
		CLOSE PaymentDetail
		DEALLOCATE PaymentDetail

		--Insert into TABLE TR_REQUEST_DOC_FILE

		DECLARE @tableTrREQFile TABLE(
			TR_REQUEST_DOC_FILE_ID	varchar(20)  ,
			IS_VIEW	bit,
			REQ_ID varchar(20),
			NOTES  nvarchar(200)
		)


		Declare @fdoc INT
		Exec sp_xml_preparedocument @fdoc Output,@p_ListTrREQFile

		INSERT INTO @tableTrREQFile
		SELECT *
		FROM OPENXML(@fDoc,'/Root/ListTrREQ',2)
		WITH 
		(
			TR_REQUEST_DOC_FILE_ID	varchar(20)  ,
			IS_VIEW	bit,
			REQ_ID varchar(20),
			NOTES  nvarchar(200)
		)

		DECLARE ListTrREQFile  CURSOR FOR
		SELECT * FROM @tableTrREQFile
		OPEN ListTrREQFile

		Declare 
		@TR_REQUEST_DOC_FILE_ID	varchar(20)  ,
		@IS_VIEW	bit,
		@REQ_ID varchar(20),
		@_NOTES nvarchar(200)

		FETCH NEXT FROM ListTrREQFile INTO @TR_REQUEST_DOC_FILE_ID,@IS_VIEW,@REQ_ID,@_NOTES
		WHILE @@FETCH_STATUS = 0	
		BEGIN
			DECLARE @l_REQFile_ID VARCHAR(15)
			EXEC SYS_CodeMasters_Gen 'TR_REQUEST_DOC_FILE', @l_REQFile_ID out
			--select @l_REQFile_ID
			--select * from TR_REQUEST_DOC_FILE where [TR_REQUEST_DOC_FILE_ID]= 'TRDF00000000523'
			IF @l_REQFile_ID='' OR @l_REQFile_ID IS NULL GOTO ABORT

			INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@l_REQFile_ID, 'TR_CONTRACT')

			INSERT INTO dbo.TR_REQUEST_DOC_FILE
			(
			     [TR_REQUEST_DOC_FILE_ID]
				,[REQ_ID]
				,[NOTES]
			)	
			VALUES
			(   
				 @l_REQFile_ID
				,@l_CONTRACT_ID
				,@_NOTES
			)
			IF @@ERROR <> 0 GOTO ABORT
			FETCH NEXT FROM ListTrREQFile INTO @TR_REQUEST_DOC_FILE_ID,@IS_VIEW,@REQ_ID,@_NOTES
		END
		CLOSE ListTrREQFile
		DEALLOCATE ListTrREQFile
		IF @@Error <> 0 GOTO ABORT
		
		if(select count(*) from @TEMP) = 0
		begin
			INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@l_REQFile_ID, 'TR_CONTRACT')
		end

		------------------BAODNQ 24/2/2022 INSERT 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_CONTRACT_ID,       
					'INSERT',
					@p_MAKER_ID,        
					GETDATE(), 
					N'Thêm mới thông tin hợp đồng thành công' ,      
					N'Thêm mới thông tin hợp đồng'       
				)


COMMIT TRANSACTION
SELECT '0' as Result, @l_CONTRACT_ID  CONTRACT_ID, @p_CONTRACT_CODE CONTRACT_CODE,
	N'Hợp đồng có ID: '+ @l_CONTRACT_ID +N' đã được tạo bản nháp thành công. Bạn có thể bấm gửi phê duyệt' ErrorDesc,[REF_ID], [TYPE]  from @TEMP
RETURN '0'
ABORT:
BEGIN
		CLOSE AssetDetail
		DEALLOCATE AssetDetail
		CLOSE PaymentDetail
		DEALLOCATE PaymentDetail
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' CONTRACT_ID, '' ErrorDesc
		RETURN '-1'
End