
/****** Object:  StoredProcedure [dbo].[MCC_InsertPDACreatedTaskWithPhotoIntoFollowup]    Script Date: 20-04-2018 06:56:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[MCC_InsertPDACreatedTaskWithPhotoIntoFollowup] @ForUserID int, @ByUserID int, @PicassoInternalRoomNo int, @Note nvarchar(4000), @Photo_ba image, @HotelNumber int AS

-- ====================================================================================================================================================================================
-- Author:		<Carsten Cremer>
-- Create date: <20180412>
-- Description:	<Functionality to insert task created by maintenance (cleaning) staff in a PDA using MCC. Based on MCC_InsertPDACreatedTaskWithIntoFollowup with added photo option. 
--	Created as a separate procedure so that functionality update does not have to be synchronized between customers>
-- 20240625-27 Clean up someone elses mess and making it compatible with timezones 
-- 20250915 CC JanitorTaskAlsoAssignedToItsCreator = When a janitor task is created by this function, it is assigned not only to the janitor but also to the one creating it. 
-- 20250915 CC Use the same SP for both photo and none photo option when all customers have been updated, the MCC_InsertPDACreatedTaskIntoFollowup stored procedure can be deleted
-- ====================================================================================================================================================================================

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 @PicIdentity int -- CC 20180412
		DECLARE @TaskDate DateTime
		DECLARE @TaskDateTimeStamp DateTime -- CC 20240625 What's up with the date all of a sudden!? Looks like someone secretly modified the code causing bugs! Let's change it correctly.

		-- 20250915 Allow for the janitor task to have the creator of it as one of the users START
		DECLARE @RESULT VarChar(2) ='0' 
		DECLARE @JanitorTaskAlsoAssignedToItsCreator TINYINT = 1;
		EXECUTE GPIGetHotelSetupData 'Interface.Piccoline.JanitorTaskAlsoAssignedToItsCreator',-1,'0', @RESULT OUT;	
		SET @JanitorTaskAlsoAssignedToItsCreator = @RESULT	
	    -- 20250915 Allow for the task to have the creator of it as one of the users STOP			

		SET @GrpIdentity = 0
		SET @CatIdentity = 0	
-- CC 20240625		SELECT @TaskDate = dbo.getSystemDate()
		SET	@TaskDate = CAST((SELECT TOP 1 SystemDate FROM System WITH (NOLOCK)) AS DATETIME) -- CC 20240625 What's up with the date all of a sudden!? Looks like someone secretly modified the code causing bugs! Let's change it correctly
		SET @TaskDate = CONVERT(DATETIME, @TaskDate) -- CONVERT(DATE,GETDATE())
		
			--	EXEC SystemDateTime '', @TaskDate OUTPUT-- CC 20240625 What's up with the date all of a sudden!? Looks like someone secretly modified the code causing bugs! Let's change it correctly
		EXEC SystemDateTime '', @TaskDateTimeStamp OUTPUT-- CC 20240625 What's up with the date all of a sudden!? Looks like someone secretly modified the code causing bugs! Let's change it correctly



-- 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 ) -- CC 20240627
		  VALUES ( 1 ,19 , 0 , 0 , 17 , 0 , 'From housekeeping', @HotelNumber , @TaskDate , @TaskDateTimeStamp , @ByUserID, 1 ) -- CC 20240627

		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 ) -- CC 20240627
		  VALUES ( 1 ,19 , 0 , 0 , 17 , 0 , @Note , @HotelNumber , @TaskDate , @TaskDateTimeStamp , @ByUserID, 1 ) -- CC 20240627

		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 )
		
		-- 20250915 Allow for the janitor task to have the creator of it as one of the users START
		IF ( @JanitorTaskAlsoAssignedToItsCreator >0  )
		BEGIN
		--1a.
		  INSERT INTO dbo.PersonalFollowUpXTab WITH (ROWLOCK) (UserIDNumber,FollowUpNumber) VALUES ( @ByUserID , @GrpIdentity )
		--1b.
		  INSERT INTO dbo.PersonalFollowUpXTab WITH (ROWLOCK) (UserIDNumber,FollowUpNumber) VALUES ( @ByUserID , @CatIdentity )
	    END;
		-- 20250915 Allow for the janitor task to have the creator of it as one of the users STOP
		
-- 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 )
		
-- CC 20180412 START

-- 4 Add the image 
-- 4a.
		IF @Photo_ba IS NOT NULL -- CC 20250915 Use the same SP for both photo and none photo option
		BEGIN					 -- CC 20250915 Use the same SP for both photo and none photo option 
			INSERT INTO dbo.Image WITH ( ROWLOCK ) 
	--		   ( base64Picture )
			   ( Picture )
	--		   VALUES ( @Photo_b64 )
	--		   ( bytearrayPicture )
			   VALUES ( @Photo_ba )

			SELECT @@Identity
			SET @PicIdentity = @@Identity

	-- 4b. Followup <--> Image
		   INSERT INTO dbo.ImageXTab WITH (ROWLOCK) (FKFollowupNumber,FKImageId) VALUES ( @CatIdentity, @PicIdentity )
	   END						-- CC 20250915 Use the same SP for both photo and none photo option
 
-- CC 20180412 END

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