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_Ins

	@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

--Validation is here

DECLARE @ERRORSYS NVARCHAR(15) = '' 

	IF( @ERRORSYS <> '' )

	BEGIN

		SELECT '-1' as Result, ''  ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS

		RETURN 0

	END

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

	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

DECLARE @l_REQUEST_TEMPLATE_ID VARCHAR(100),

@l_SENT_TO_TEMPLATE_ID VARCHAR(20),

@string varchar(200), @hdoc int

DECLARE @DEP_BRANCH VARCHAR(20)

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

BEGIN

	SET @DEP_BRANCH=(SELECT D.DEP_CODE FROM TL_USER U LEFT JOIN CM_DEPARTMENT D ON U.DEP_ID=D.DEP_ID WHERE TLNANME=@p_MAKER_ID)

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

	BEGIN

		SET @DEP_BRANCH=(SELECT B.BRANCH_CODE FROM TL_USER U LEFT JOIN CM_BRANCH B ON U.TLSUBBRID=B.BRANCH_ID WHERE TLNANME=@p_MAKER_ID)

	END

	EXEC CM_REQUEST_TEMPLATE_Get_Report_No @DEP_BRANCH,@p_REPORT_NO OUT

END

		EXEC SYS_CodeMasters_Gen 'CM_REQUEST_TEMPLATE', @l_REQUEST_TEMPLATE_ID out

INSERT INTO [dbo].[CM_REQUEST_TEMPLATE]

           ([REQUEST_TEMPLATE_ID]

           ,[REQUEST_TEMPLATE_NAME]

           ,[NOTES]

           ,[RECORD_STATUS]

           ,[MAKER_ID]

           ,[CREATE_DT]

           ,[AUTH_STATUS]

           ,[CHECKER_ID]

           ,[APPROVE_DT]

           ,[CONTENT]

           ,[CREATE_LOCATION]

           ,[HEADER]

           ,[BRANCH_ID]

           ,[REPORT_NO]

           ,[TITLE]

           ,[REPORT_DT]

           ,[SCHEME]

		   ,TYPE_TEMPLATE_ID

		   ,IS_SENT_APPROVE)

     VALUES

           (@l_REQUEST_TEMPLATE_ID

           ,@p_REQUEST_TEMPLATE_NAME

           ,@p_NOTES

           ,@p_RECORD_STATUS

           ,@p_MAKER_ID

           ,CONVERT(DATETIME, @p_CREATE_DT, 103) 

           ,@p_AUTH_STATUS

           ,@p_CHECKER_ID

           ,CONVERT(DATETIME, @p_APPROVE_DT, 103) 

           ,@p_CONTENT

           ,@p_CREATE_LOCATION

           ,@p_HEADER

           ,@p_BRANCH_ID

           ,@p_REPORT_NO

           ,@p_TITLE

           ,CONVERT(DATETIME, @p_REPORT_DT, 103) 

           ,@p_SCHEME_OUT

		   ,@p_TYPE_TEMPLATE_ID

		   ,1)

		IF @@Error <> 0 GOTO ABORT

	--insert sent to

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

	BEGIN

		declare @sentToTlb table(CONTENT nvarchar(50))

		insert into @sentToTlb

		select *

		FROM WSISPLIT(@p_REQUEST_TEMPLATE_XML,',')

	END

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

	DECLARE @ind INT

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

	INSERT INTO [dbo].[CM_SENT_TO_TEMPLATE]

           ([TEMPLATE_ID]

           ,[SENT_TO_TEMPLATE_ID]

           ,[CONTENT])

	SELECT @l_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

	DECLARE @DONE_INS_APPROVE_GROUP BIT = 1,

			@ERROR_MESS NVARCHAR(MAX)

	EXEC CM_APPROVE_GROUP_Ins @l_REQUEST_TEMPLATE_ID,@p_GROUP_APPROVES,@DONE_INS_APPROVE_GROUP OUT, @ERROR_MESS OUT

	IF @DONE_INS_APPROVE_GROUP <> 1 GOTO ABORT_CHILD

	--ADD LOG

	DECLARE @p_DONE BIT = 1

	EXEC CM_TEMPLATE_LOG_Ins @l_REQUEST_TEMPLATE_ID,'ADD','',@p_MAKER_ID,NULL, @p_DONE OUT

	IF @p_DONE <> 1 GOTO ABORT

	--

COMMIT TRANSACTION

SELECT '0' as Result, @l_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

GO