CREATE PROCEDURE [dbo].[MCC_InsertPDACreatedTaskIntoFollowup] @ForUserID int, @ByUserID int, @PicassoInternalRoomNo int, @Note nvarchar(4000), @HotelNumber int AS
-- ====================================================================================================================================================================================
-- Author:		<Carsten Cremer>
-- Create date: <20160422>
-- Description:	<Functionality to insert task created by maintenance (cleaning) staff in a PDA using MCC>
-- ====================================================================================================================================================================================

BEGIN TRANSACTION 
BEGIN TRY

		-- SET NOCOUNT ON added to prevent extra result sets from
		-- interfering with SELECT statements.
		SET NOCOUNT ON;

		DECLARE @GrpIdentity int
		DECLARE @CatIdentity int
		DECLARE @TaskDate DateTime
			

		SET @GrpIdentity = 0
		SET @CatIdentity = 0	
		SELECT @TaskDate = dbo.getSystemDate()
		SET @TaskDate = CONVERT(DATETIME, @TaskDate) -- CONVERT(DATE,GETDATE())
		

-- In order for the task to be visible in Maintenance Center ( NOT MCC ) two followup lines are needed: One Group and one category.	
-- First we create the group followup record and store the identity number in a temp variable. 
		INSERT INTO dbo.FollowUp WITH (ROWLOCK) 
		  (UserCreated,[Type],SubType,[State],[Priority],OverWritten,Note,HotelNumber,[Date],CreatedDate,CreatedBy, Deviation) 
		  VALUES ( 1 ,19 , 0 , 0 , 17 , 0 , 'From housekeeping', @HotelNumber , @TaskDate , CURRENT_TIMESTAMP , @ByUserID, 1 )

		SELECT @@Identity
		SET @GrpIdentity = @@Identity

-- Then we create the category followup record and store the identity number in a temp variable.
		
		INSERT INTO dbo.FollowUp WITH (ROWLOCK) 
		  (UserCreated,[Type],SubType,[State],[Priority],OverWritten,Note,HotelNumber,[Date],CreatedDate,CreatedBy,Deviation ) 
		  VALUES ( 1 ,19 , 0 , 0 , 17 , 0 , @Note , @HotelNumber , @TaskDate , CURRENT_TIMESTAMP , @ByUserID, 1 )

		SELECT @@Identity
		SET @CatIdentity = @@Identity


-- Then we need to create relations between the followups and a) a staff, b) a room and c) themselves ( followup <-> followup )

-- 1a. Staff <-> Group
		INSERT INTO dbo.PersonalFollowUpXTab WITH (ROWLOCK) (UserIDNumber,FollowUpNumber) VALUES ( @ForUserID , @GrpIdentity )
-- 2a. Staff <-> Category
		INSERT INTO dbo.PersonalFollowUpXTab WITH (ROWLOCK) (UserIDNumber,FollowUpNumber) VALUES ( @ForUserID , @CatIdentity )
		
		
		
-- 2a. Room <-> Group
		INSERT INTO dbo.FollowupRoomsXtab WITH (ROWLOCK) (FK_RoomNumber,FK_FollowupNumber) VALUES ( @PicassoInternalRoomNo , @GrpIdentity )
-- 2b. Room <-> Category
		INSERT INTO dbo.FollowupRoomsXtab WITH (ROWLOCK) (FK_RoomNumber,FK_FollowupNumber) VALUES ( @PicassoInternalRoomNo , @CatIdentity )
		
-- 3 Followup <-> Followup
		INSERT INTO dbo.FollowupFollowupXTab WITH (ROWLOCK) (FK_FollowupNumberSub,FK_FollowupNumber) VALUES ( @CatIdentity , @GrpIdentity )


-- That's all folks!
  
  
END TRY
BEGIN CATCH
 
	 IF @@TRANCOUNT > 0
			ROLLBACK
END CATCH
 
 IF @@TRANCOUNT > 0
  COMMIT