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

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

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

	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

		IF(@p_CONTRACT_NAME ='' OR @p_CONTRACT_NAME IS  NULL)

		BEGIN

				ROLLBACK TRANSACTION

				SELECT '-1' Result, '' AS PO_ID, N'Tên hợp đồng không được để trống' AS ErrorDesc 

				RETURN '-1'

		END

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