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

ALTER PROCEDURE [dbo].[CM_REQUEST_TEMPLATE_Upd] 

	@p_REQUEST_TEMPLATE_ID nvarchar(200)  = NULL,

	@p_REQUEST_TEMPLATE_NAME	nvarchar(200)  = NULL,

	@p_REQUEST_TEMPLATE_CODE	nvarchar(100)  = NULL,

	@p_CREATE_LOCATION NVARCHAR(100)=NULL,

	@p_HEADER NVARCHAR(max)=NULL,

	@p_CONTENT NVARCHAR(max)=NULL,

	@p_BRANCH_ID NVARCHAR(50)=NULL,

	@p_REPORT_NO NVARCHAR(50)=NULL,

	@p_TITLE NVARCHAR(500)=NULL,

	@p_REPORT_DT	VARCHAR(20) = NULL,

	@p_SCHEME_OUT VARCHAR(20),

	@p_TYPE_TEMPLATE_ID NVARCHAR(20)=NULL,

	@p_NOTES	nvarchar(max)  = 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_REQUEST_TEMPLATE_XML nvarchar(MAX) = NULL,

	@p_GROUP_APPROVES NVARCHAR(MAX)=NULL

AS

BEGIN

	--Validation is here

	DECLARE @ERRORSYS NVARCHAR(15) = '', 

		@hdoc int,

		@l_REQUEST_TEMPLATE_DETAIL_ID varchar(15),

		@l_REQUEST_TEMPLATE_DETAIL_CONTENT nvarchar(max),

		@l_REQUEST_TEMPLATE_DETAIL_CODE varchar(15),

		@l_PAGE_SIZE varchar(5),

		@l_NOTES nvarchar(max)

	IF @ERRORSYS <> '' 

	BEGIN

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

		RETURN '0'

	END

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

	BEGIN

		SELECT '-1' Result, '' REQ_ID, N'Số tờ trình không được bỏ trống."' ErrorDesc 

		RETURN '-1'

	END

	IF EXISTS(SELECT * FROM CM_REQUEST_TEMPLATE WHERE REPORT_NO = @p_REPORT_NO AND RECORD_STATUS = 1 AND REQUEST_TEMPLATE_ID <> @p_REQUEST_TEMPLATE_ID)

	BEGIN

		SELECT '-1' Result, '' REQ_ID, N'Tờ trình số: ' + @p_REPORT_NO + N' đã tồn tại trong hệ thống!"' ErrorDesc 

		RETURN '-1'

	END

	BEGIN TRANSACTION  

	IF(EXISTS(SELECT * FROM CM_APPROVE_GROUP WHERE REQ_ID=@p_REQUEST_TEMPLATE_ID AND IS_REJECT=1) OR (SELECT AUTH_STATUS FROM CM_REQUEST_TEMPLATE WHERE REQUEST_TEMPLATE_ID=@p_REQUEST_TEMPLATE_ID )='U')

	BEGIN

	UPDATE [dbo].[CM_REQUEST_TEMPLATE]

	SET [REQUEST_TEMPLATE_NAME] = @p_REQUEST_TEMPLATE_NAME

      ,[REQUEST_TEMPLATE_CODE] = @p_REQUEST_TEMPLATE_CODE

      ,[NOTES] = @p_NOTES

      ,[MAKER_ID] = @p_MAKER_ID

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

      ,[CONTENT] = @p_CONTENT

      ,[CREATE_LOCATION] = @p_CREATE_LOCATION

      ,[HEADER] = @p_HEADER

      ,[BRANCH_ID] = @p_BRANCH_ID

      ,[REPORT_NO] = @p_REPORT_NO

      ,[TITLE] = @p_TITLE

      ,[REPORT_DT] = CONVERT(DATETIME, @p_REPORT_DT, 103) 

      ,[SCHEME] = @p_SCHEME_OUT

      ,[TYPE_TEMPLATE_ID] = @p_TYPE_TEMPLATE_ID

	  WHERE [REQUEST_TEMPLATE_ID] = @p_REQUEST_TEMPLATE_ID

		IF @@Error <> 0 GOTO ABORT

	END

	DELETE CM_SENT_TO_TEMPLATE WHERE TEMPLATE_ID=@p_REQUEST_TEMPLATE_ID

	declare @sentToTlb table(CONTENT nvarchar(50))

	IF(@p_REQUEST_TEMPLATE_XML <> '' AND @p_REQUEST_TEMPLATE_XML IS NOT NULL)

	BEGIN

		insert into @sentToTlb

		select *

		FROM WSISPLIT(@p_REQUEST_TEMPLATE_XML,',')

	END

	DECLARE @TOT INT = (SELECT COUNT(CONTENT) from @sentToTlb)

	DECLARE @ind INT,@p_SENT_TO_TEMPLATE_ID nvarchar(50)

	EXEC SYS_CodeMasters_Gen_Mult 'CM_SENT_TO_TEMPLATE',@TOT, @p_SENT_TO_TEMPLATE_ID out, @ind out

	--EXEC SYS_CodeMasters_Gen 'CM_SENT_TO_TEMPLATE', @l_SENT_TO_TEMPLATE_ID out

	INSERT INTO [dbo].[CM_SENT_TO_TEMPLATE]

		   ([TEMPLATE_ID]

		   ,[SENT_TO_TEMPLATE_ID]

		   ,[CONTENT])

	SELECT @p_REQUEST_TEMPLATE_ID

		   ,('CSTM' + right('0000000000000'+convert(varchar(15),(@ind -1 + row_number() over(order by (select 1)))),10)),

		CONTENT

	FROM @sentToTlb

	IF @@Error <> 0 GOTO ABORT

	--EDIT USER APPROVE

	DECLARE @CURRENT_STEP INT =(SELECT CURRENT_STEP FROM CM_REQUEST_TEMPLATE WHERE REQUEST_TEMPLATE_ID=@p_REQUEST_TEMPLATE_ID)

	DECLARE @DONE_INS_APPROVE_GROUP BIT=1,

			@ERROR_MESS NVARCHAR(MAX)

	EXEC CM_APPROVE_GROUP_Upd @p_REQUEST_TEMPLATE_ID,@p_GROUP_APPROVES,@CURRENT_STEP,@DONE_INS_APPROVE_GROUP OUT, @ERROR_MESS OUT

	IF @DONE_INS_APPROVE_GROUP <> 1 GOTO ABORT_CHILD

	--

	--ADD LOG

	IF(EXISTS(SELECT 1 FROM CM_REQUEST_TEMPLATE WHERE IS_SENT_APPROVE = 0 AND REQUEST_TEMPLATE_ID = @P_REQUEST_TEMPLATE_ID))

	BEGIN

		DECLARE @p_DONE BIT = 1

		EXEC CM_TEMPLATE_LOG_Ins @p_REQUEST_TEMPLATE_ID,'UPD','',@p_MAKER_ID,NULL, @p_DONE OUT

		IF @p_DONE <> 1 GOTO ABORT

	END

	--

COMMIT TRANSACTION

SELECT '0' as Result, @p_REQUEST_TEMPLATE_ID  ID, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

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

		RETURN '-1'

END

ABORT_CHILD:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ID, @ERROR_MESS ErrorDesc

		RETURN '-1'

End

END