
/****** 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>
-- ====================================================================================================================================================================================

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
			

		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 )
		
-- CC 20180412 START

-- 4 Add the image 
-- 4a.
		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 )

 
-- CC 20180412 END

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

