AMS - VB

ALTER PROC dbo.CM_PROCESS_DT_Create

@p_ID VARCHAR(15) = NULL,

@p_REQ_ID VARCHAR(50)=NULL,

@p_TLNAME VARCHAR(200)=NULL

AS

BEGIN

DECLARE @BRANCH_ID VARCHAR(20), @DEP_ID VARCHAR(20), @ROLE VARCHAR(20), @CURRENTSTATE VARCHAR(15), @CONDITION_STATUS VARCHAR(1), @DESCRIBE NVARCHAR(MAX), @COUNTDVCM INT, @TEMPDVCM_ID INT

DECLARE @TEMPDVCM TABLE (

  ID INT NOT NULL IDENTITY(1,1),

  COST_ID VARCHAR(15),

  REQ_ID VARCHAR(15)

  --,RANGE_PROCESS VARCHAR(15)

) 

BEGIN TRANSACTION

SET @CURRENTSTATE = (SELECT crp.[STATUS] FROM CM_REQUEST_PROCESS crp WHERE crp.REQ_ID = @p_REQ_ID AND  PROCESS_ID=CONVERT(INT, @p_ID))

SET @CONDITION_STATUS = (SELECT  [STATUS] FROM CM_PROCESS WHERE ID=CONVERT(INT, @p_ID))    

SET @ROLE = (SELECT ROLE FROM CM_PROCESS cp WHERE CONDITION_STATUS IN (SELECT  [STATUS] FROM CM_PROCESS WHERE ID=CONVERT(INT, @p_ID)) AND CONDITION_STATUS<>[STATUS] )

INSERT INTO @TEMPDVCM SELECT trsc.COST_ID, trsc.REQ_ID FROM TR_REQUEST_SHOP_COSTCENTER trsc  

                      WHERE trsc.REQ_ID = @p_REQ_ID 

                      AND trsc.COST_ID NOT IN (SELECT crp.DEPT_ID FROM CM_REQUEST_PROCESS crp WHERE crp.REQ_ID = @p_REQ_ID AND crp.STATUS = 'G' AND crp.PROCESS_ID = 32) 

SET @TEMPDVCM_ID = 1

SET @COUNTDVCM = (SELECT COUNT(*) FROM @TEMPDVCM WHERE [@TEMPDVCM].REQ_ID = @p_REQ_ID GROUP BY [@TEMPDVCM].REQ_ID)	

  INSERT INTO [dbo].[PL_PROCESS]

           ([REQ_ID]

           ,[PROCESS_ID]

           ,[CHECKER_ID]

           ,[APPROVE_DT]

           ,[PROCESS_DESC]

           ,[NOTES]

           ,[AUTH_STATUS])

    SELECT @p_REQ_ID

	,ID

	,@p_TLNAME

	,GETDATE()

	,UPPER(LEFT(cp.DESCRIPTION,1))+LOWER(SUBSTRING(cp.DESCRIPTION,2,LEN(cp.DESCRIPTION))) --uppercase/capitalize the first letter vs just wanted to change it only for displaying and do not need the actual data in table to change

	,cp.NOTES

	,[STATUS] FROM CM_PROCESS cp WHERE ID=CONVERT(INT, @p_ID)

	DECLARE @TABLE VARCHAR(50)=NULL

	--cắt chuỗi lấy PREFIX để xác định Thêm mới, Xuất, Điều chuyển, Thu hồi, Thanh lý,...

		DECLARE @SYS_PREFIX VARCHAR(20) =(SELECT LEFT(Val,PATINDEX('%[^a-z]%', Val+'0')-1) from(

		  SELECT SUBSTRING(@p_REQ_ID, PATINDEX('%[a-z]%', @p_REQ_ID), LEN(@p_REQ_ID)) Val

		)x)

	SET @TABLE=(SELECT sp.ID FROM SYS_PREFIX sp WHERE sp.Prefix = @SYS_PREFIX)

  SELECT @BRANCH_ID=BRANCH_ID,@DEP_ID=DEP_ID FROM ASS_LIQ_REQUEST ALR WHERE ALR.LIQ_REQ_ID = @p_REQ_ID

	--UPDATE STATUS DONE CM_REQUEST_PROCESS

	UPDATE CM_REQUEST_PROCESS SET DONE=1 

	WHERE REQ_ID=@p_REQ_ID 

	AND PROCESS_ID=CONVERT(INT, @p_ID)

	AND(DEPT_ID=(SELECT DEP_ID FROM TL_USER WHERE TLNANME=@p_TLNAME) OR DEPT_ID IS NULL OR DEPT_ID='')

	DELETE CM_REQUEST_PROCESS

	WHERE (DONE<>1 OR DONE IS NULL)

	AND REQ_ID=@p_REQ_ID 

	AND PROCESS_ID<>CONVERT(INT, @p_ID)

	AND CONDITION_STATUS=(SELECT TOP 1 CONDITION_STATUS FROM CM_REQUEST_PROCESS WHERE PROCESS_ID=CONVERT(INT, @p_ID) AND REQ_ID=@p_REQ_ID)

	AND(DEPT_ID=(SELECT DEP_ID FROM TL_USER WHERE TLNANME=@p_TLNAME) OR DEPT_ID IS NULL OR DEPT_ID='')

   IF @ROLE LIKE '%QLTS'

    BEGIN

      SET @BRANCH_ID = 'DV0001'

     SET @DEP_ID = 'DEP000000000048'

    END

  IF(@CURRENTSTATE = 'E' AND NOT EXISTS (SELECT * FROM TR_REQUEST_SHOP_COSTCENTER WHERE REQ_ID=@p_REQ_ID))

  BEGIN

    SET @p_ID = (SELECT cp.ID FROM CM_PROCESS cp WHERE cp.[STATUS] = 'G')

    SET @BRANCH_ID = 'DV0001' 

    SET @DEP_ID = 'DEP000000000048'

    SET @CONDITION_STATUS = 'E'

    SET @ROLE = 'QLTS'

  END

   IF @ROLE LIKE '%DVCM' --AND @COUNTDVCM = 1     

   BEGIN  

    SET @BRANCH_ID = 'DV0001' 

    SET @DEP_ID = (SELECT COST_ID FROM @TEMPDVCM WHERE REQ_ID = @p_REQ_ID AND ID = 1)--(SELECT TRS.COST_ID FROM TR_REQUEST_SHOP_COSTCENTER TRS WHERE TRS.REQ_ID=@p_REQ_ID)

   END

--   IF @ROLE LIKE 'DVCM' AND @COUNTDVCM > 1 AND NOT EXISTS (SELECT * FROM CM_REQUEST_PROCESS crp WHERE crp.REQ_ID = @p_REQ_ID AND crp.STATUS = 'F' AND crp.ROLE LIKE 'DVCM')

--   BEGIN

--    SET @BRANCH_ID = 'DV0001' 

--    SET @DEP_ID = (SELECT COST_ID FROM @TEMPDVCM WHERE REQ_ID = @p_REQ_ID AND ID = 1)

--   END

   IF(@ROLE LIKE 'GDDVCM')

   BEGIN

    SET @ROLE = 'GDDV'

    SET @BRANCH_ID = 'DV0001'

    SET @DEP_ID = (SELECT COST_ID FROM @TEMPDVCM WHERE REQ_ID = @p_REQ_ID AND ID = 1)--(SELECT TRS.COST_ID FROM TR_REQUEST_SHOP_COSTCENTER TRS WHERE TRS.REQ_ID=@p_REQ_ID)

   END 

	--CHECK STEP DVCM IN PROCESS

	IF(EXISTS(SELECT * FROM CM_PROCESS WHERE  ID=CONVERT(INT,@p_ID) AND [STATUS] IN (SELECT CONDITION_STATUS FROM CM_PROCESS WHERE RANGE_PROCESS='DEPT'))

	AND NOT EXISTS (SELECT * FROM TR_REQUEST_SHOP_COSTCENTER WHERE REQ_ID=@p_REQ_ID))

	BEGIN

		--CHANGE ID TO NEXT STEP

		SET @p_ID=(SELECT TOP(1) ID FROM CM_PROCESS WHERE [ORDER]>(SELECT [ORDER] FROM CM_PROCESS WHERE ID=CONVERT(INT, @p_ID)) AND (RANGE_PROCESS='DEPT') ORDER BY [ORDER] DESC)

    SET @DEP_ID = (SELECT TRS.COST_ID FROM TR_REQUEST_SHOP_COSTCENTER TRS WHERE TRS.REQ_ID=@p_REQ_ID)

	END

  IF(@CURRENTSTATE = 'G' AND EXISTS(SELECT * FROM @TEMPDVCM WHERE COST_ID IS NOT NULL AND REQ_ID = @p_REQ_ID))  

  BEGIN 

    SET @p_ID = (SELECT cp.ID FROM CM_PROCESS cp WHERE cp.[STATUS] = 'E') 

    SET @BRANCH_ID = 'DV0001'

    SET @DEP_ID = (SELECT COST_ID FROM @TEMPDVCM WHERE REQ_ID = @p_REQ_ID AND ID = 1)

    SET @CONDITION_STATUS = 'G'

    SET @ROLE = 'DVCM'

  END 

	--INSERT INTO PL_REQUEST_PROCESS

	IF(

	NOT EXISTS(SELECT * FROM CM_REQUEST_PROCESS WHERE PROCESS_ID=CONVERT(INT,@p_ID) AND REQ_ID=@p_REQ_ID AND (DONE<>1 OR DONE IS NULL OR DONE ='') AND [ACTION]='APPROVE')

--	AND

--	NOT EXISTS(SELECT * FROM CM_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND [STATUS] IN(SELECT [STATUS] FROM CM_PROCESS WHERE CONDITION_STATUS=(SELECT  [STATUS] FROM CM_PROCESS WHERE ID=CONVERT(INT, @p_ID)) AND CONDITION_STATUS<>[STATUS] ) )

  )

	BEGIN

		INSERT INTO [dbo].[CM_REQUEST_PROCESS]

			   ([REQ_ID]

			   ,PROCESS_KEY

			   ,[PROCESS_ID]

			   ,[STATUS]

			   ,[ROLE]

			   ,[BRANCH_ID]

         ,[DEPT_ID]

			   ,[DEP_ID]

			   ,[NOTES]

			   ,[ORDER]

			   ,[ACTION]

			   ,[FROM_STATUS]

			   ,[FROM]

			   ,[CONDITION_STATUS]

			   ,[DESCRIBE]

			   ,[NAME_ACTION]

			   ,[RANGE_PROCESS]

         ,[MARKER_ID]

         ,CHECKER_ID

         ,APPROVE_DT)

			   SELECT 

			   @p_REQ_ID

			   ,CP.PROCESS_KEY

			   ,CP.ID

			   ,CP.[STATUS]

			   ,@ROLE

			   ,@BRANCH_ID

         ,[@TEMPDVCM].COST_ID --TRSC.COST_ID--

			   ,@DEP_ID

			   ,CP.NOTES

			   ,CP.[ORDER]

			   ,[ACTION]

			   ,FROM_STATUS

			   ,[STATUS]

			   ,@CONDITION_STATUS

			   ,DESCRIBE

			   ,NAME_ACTION

			   ,RANGE_PROCESS

         ,alr.MAKER_ID

         ,alr.CHECKER_ID

         ,CONVERT(DATETIME,GETDATE(),103)

			   FROM CM_PROCESS CP

         LEFT JOIN ASS_LIQ_REQUEST alr ON alr.LIQ_REQ_ID = @p_REQ_ID

         --LEFT JOIN TR_REQUEST_SHOP_COSTCENTER TRSC ON TRSC.REQ_ID=@p_REQ_ID AND CP.RANGE_PROCESS='DEPT'

         LEFT JOIN @TEMPDVCM ON [@TEMPDVCM].REQ_ID = @p_REQ_ID AND CP.RANGE_PROCESS='DEPT' AND [@TEMPDVCM].ID = 1--AND TRSC.COST_ID = [@TEMPDVCM].COST_ID   

			   WHERE CONDITION_STATUS IN (SELECT [STATUS] FROM CM_PROCESS WHERE ID=CONVERT(INT, @p_ID))

			   AND CONDITION_STATUS <> [STATUS] 

         --IF @@ERROR <> 0 GOTO ABORT

	END

	-- END INSERT PL_REQUEST_PROCESS

COMMIT TRANSACTION

SELECT '0' as Result, @p_REQ_ID  REQ_ID, '' ErrorDesc, @p_ID ID, @BRANCH_ID BRANCH_ID, @DEP_ID DEP_ID, @ROLE [ROLE], @CURRENTSTATE CURRENTSTATE, @COUNTDVCM COUNTDVCM, @TEMPDVCM_ID TEMPDVCM_ID  

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

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

		RETURN '-1'

End

END