-------------
-- ROLES
-------------
	USE [IHBoxSystem]
	GO
	CREATE ROLE mes_config_run_schedule_read AUTHORIZATION dbo 
	GO  

	USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'mes_config_run_schedule_read', 'admin'
	GO

	USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'mes_config_run_schedule_read', 'SDI/australtek'
	GO


	CREATE ROLE mes_config_run_schedule_readWrite AUTHORIZATION dbo 
	GO  

	USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'mes_config_run_schedule_readWrite', 'admin'
	GO

	USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'mes_config_run_schedule_readWrite', 'SDI/australtek'
	GO


-------------------
-- Menu and Route
-------------------
	declare @parent int, @result_routeId INT
	select @parent=id from [FrontEnd].[Menu] where [Name] = 'MES Configuration' and Parent is null

	INSERT INTO [FrontEnd].[Routes]
			   ([Route]
			   ,[ModuleName]
			   ,[ModulePath]
			   ,[RequiredRole]
			   ,[Group]
		   )
		 VALUES 
			   (
			   'forecast-run-schedule-configuration'
			   , 'forecast-run-schedule-configuration'
			   , 'custom-screens/forecast-run-schedule-configuration/'
			   , 'mes_config_run_schedule_readWrite' 
			   , 'configuration-dashboard'
			   )
			   SET @result_routeId = 9
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			   ,[Code]
			   ,[IHBoxModule]
			  )
		 VALUES
			   (
			   @parent
			   ,'Forecast Run Schedule Configuration'
			   ,9 
			   ,1
			   ,1
			   ,1
			   ,'mes_config_run_schedule_readWrite'
			   ,NULL
			   ,'forecast-run-schedule-configuration'
			   ,'MES'
			 )
	GO

-------------------------------------------------------------------
-- Steps to follow to add a new screen in MES Configuration Section
-------------------------------------------------------------------
 insert into [FrontEnd].MenuProperties(MenuId,[Name],[Value])
 values(
 (select top 1 Id from FrontEnd.Menu where [Name] = 'Forecast Run Schedule Configuration'),('data-screenid'),('forecast-run-schedule-configuration')
)

 insert into [FrontEnd].[MenuLinkParameters]([MenuLinkID],[Name],[Value])
 values(
 (select top 1 Id from FrontEnd.Menu where [Name] = 'Forecast Run Schedule Configuration'),('section'),('forecast-run-schedule-configuration')
)


-------------------
-- SP Permissions
-------------------
EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [mes_config_run_schedule_readWrite] -- The role
,@name = N'securedsp-MES.UploadRunSchedule' -- the procedure
,@value = N'SendCurrentUser=TRUE' 
GO


-------------------
--	Profiles
-------------------
	USE [IHBoxSystem]
	GO

	DECLARE @ModuleId INT

	INSERT INTO [SECURITY].[Modules]([Code],[Name])
	VALUES('forecast_run_schedule_configuraton','Forecast Run Schedule Configuration')

	SET @ModuleId = @@IDENTITY

	INSERT INTO [SECURITY].[ModuleActions]([ModuleId],[Code],[Name],[Role])
	VALUES(@ModuleId,'readWrite','Read/Write','mes_config_run_schedule_readWrite')


	INSERT INTO [SECURITY].ModuleActions(ModuleId,Code,[Name],[Role])
	VALUES (@ModuleId,'read','Read','mes_config_run_schedule_read')


	
--------------------------
--- Stored Procedures ---
--------------------------

	USE [MES]
GO
/****** Object:  StoredProcedure [MES].[UploadRunSchedule]    Script Date: 6/2/2023 19:59:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [MES].[UploadRunSchedule]
	@Layout XML = NULL,
	@Save bit = 0,
	@CurrentUser VARCHAR(50) = NULL -- auto included by 4i
AS
BEGIN
	-------------------------
	--- Common Variables ----
	-------------------------
	
	declare @procName VARCHAR(300)
	declare @finishEndMSG VARCHAR(300)
	declare @logMsg VARCHAR(MAX)
	SELECT @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	DECLARE @buf varchar(200) = ''
	SET NOCOUNT ON	
	-----------------------
	-- Variables declaration
	-----------------------
	declare @datetime datetime = GETUTCDATE()
	declare @layoutTable table (
		UploadTimestamp datetime,
		StringDate		varchar(200),
		[DATE]			date,
		[SHIFT]			char(1),
		[CREW]			char(1),
		[HEATS]			int,
		[C1MOLD]		varchar(200),
		[C1MILL]		varchar(200),
		[C1SIZE]		varchar(200),
		[C1HEATS]		int,
		[C2MOLD]		varchar(200),
		[C2MILL]		varchar(200),
		[C2SIZE]		varchar(200),
		[C2HEATS]		int,
		C1Size1			varchar(200),
		C1Size2			varchar(200),
		C1Size3			varchar(200),
		C2Size1			varchar(200),
		C2Size2			varchar(200),
		C2Size3			varchar(200)
	)
	-----------------------
	-- Query
	-----------------------
	;with cte as (
		SELECT
			[StringDate]=	T.Variable.value('@DATE','NVARCHAR(MAX)'),
			[SHIFT]		=	T.Variable.value('@SHIFT','NVARCHAR(MAX)'),
			[CREW]		=	T.Variable.value('@CREW','NVARCHAR(MAX)'),
			[HEATS]		=	T.Variable.value('@HEATS','VARCHAR(MAX)'),
			[C1MOLD]	=  T.Variable.value('@C1MOLD','VARCHAR(MAX)'),
			[C1MILL]	=  T.Variable.value('@C1MILL','VARCHAR(MAX)'),
			[C1SIZE]	=  T.Variable.value('@C1SIZE','VARCHAR(MAX)'),
			[C1HEATS]	= T.Variable.value('@C1HEATS','VARCHAR(MAX)'),
			[C2MOLD]	=  T.Variable.value('@C2MOLD','VARCHAR(MAX)'),
			[C2MILL]	=  T.Variable.value('@C2MILL','VARCHAR(MAX)'),
			[C2SIZE]	=  T.Variable.value('@C2SIZE','VARCHAR(MAX)'),
			[C2HEATS]	= T.Variable.value('@C2HEATS','VARCHAR(MAX)'),
			C1Size1		=	T.Variable.value('@C1Size1','VARCHAR(MAX)'),
			C1Size2		=	T.Variable.value('@C1Size2','VARCHAR(MAX)'),
			C1Size3		=	T.Variable.value('@C1Size3','VARCHAR(MAX)'),
			C2Size1		=	T.Variable.value('@C2Size1','VARCHAR(MAX)'),
			C2Size2		=	T.Variable.value('@C2Size2','VARCHAR(MAX)'),
			C2Size3		=	T.Variable.value('@C2Size3','VARCHAR(MAX)')
		FROM
		@Layout.nodes('XMLRoot/Row') AS T(Variable)	
	)
	insert into @layoutTable(UploadTimestamp,StringDate,[DATE],[SHIFT],[CREW],[HEATS]
		,[C1MOLD],[C1MILL],[C1SIZE],[C1HEATS],[C2MOLD],[C2MILL],[C2SIZE],[C2HEATS]
		,C1Size1,C1Size2,C1Size3,C2Size1,C2Size2,C2Size3)
	select 
		@datetime
		,StringDate
		,case 
			when 
				DATEDIFF(MONTH,@datetime, CAST( StringDate + CAST(YEAR(GETUTCDATE()) as varchar) as datetime) ) < 3
			then
				CAST(CAST( StringDate + CAST(YEAR(GETUTCDATE()) as varchar) as datetime) as date) 
			when 
				DATEDIFF(MONTH,@datetime, CAST( StringDate + CAST(YEAR(GETUTCDATE()) + 1 as varchar) as datetime) ) < 3
			then			
				CAST(CAST( StringDate + CAST(YEAR(GETUTCDATE()) + 1 as varchar) as datetime) as date)
			else
				null
			end
		,cast([SHIFT] as char(1))		
		,cast([CREW]  as char(1))	
		,cast([HEATS] as int)	
		,[C1MOLD]	
		,[C1MILL]	
		,[C1SIZE]	
		,cast([C1HEATS] as int)	
		,[C2MOLD]	
		,[C2MILL]	
		,[C2SIZE]	
		,cast([C2HEATS]	as int)
		,C1Size1
		,C1Size2
		,C1Size3
		,C2Size1
		,C2Size2
		,C2Size3
	from cte

	delete from @layoutTable 
	where
		[DATE]		is null and
		[CREW]		is null and
		[HEATS]		is null and
		[C1MOLD]	is null and
		[C1MILL]	is null and
		[C1SIZE]	is null and
		[C1HEATS] 	is null and
		[C2MOLD]	is null and
		[C2MILL]	is null and
		[C2SIZE]	is null and
		[C2HEATS]	is null

	if (@Save = 0)
	begin
		select * from @layoutTable
		return
	end

	BEGIN TRY
		
		MERGE MES.RunSchedule AS TARGET
		USING @layoutTable AS SOURCE 
		ON TARGET.[StringDate] = SOURCE.[StringDate]  AND TARGET.[SHIFT] = SOURCE.[SHIFT]												  
		WHEN MATCHED THEN 
		UPDATE SET 
				TARGET.[UploadTimestamp]	= SOURCE.[UploadTimestamp]	
				,TARGET.[DATE]				= SOURCE.[DATE]				
				,TARGET.[CREW]				= SOURCE.[CREW]
				,TARGET.[HEATS]		= SOURCE.[HEATS]	
				,TARGET.[C1MOLD]	= SOURCE.[C1MOLD]
				,TARGET.[C1MILL]	= SOURCE.[C1MILL]		
				,TARGET.[C1SIZE]	= SOURCE.[C1SIZE]
				,TARGET.[C1HEATS]	= SOURCE.[C1HEATS]
				,TARGET.[C2MOLD]	= SOURCE.[C2MOLD]
				,TARGET.[C2MILL]	= SOURCE.[C2MILL]		
				,TARGET.[C2SIZE]	= SOURCE.[C2SIZE]
				,TARGET.[C2HEATS]	= SOURCE.[C2HEATS]
				,TARGET.C1Size1 = SOURCE.C1Size1
				,TARGET.C1Size2 = SOURCE.C1Size2
				,TARGET.C1Size3 = SOURCE.C1Size3
				,TARGET.C2Size1 = SOURCE.C2Size1
				,TARGET.C2Size2 = SOURCE.C2Size2
				,TARGET.C2Size3 = SOURCE.C2Size3

		WHEN NOT MATCHED BY TARGET THEN 
		INSERT ([UploadTimestamp],[StringDate],[DATE],[SHIFT],[CREW],[HEATS],[C1MOLD], [C1MILL],[C1SIZE],[C1HEATS],[C2MOLD], [C2MILL],[C2SIZE],[C2HEATS]
				,C1Size1,C1Size2,C1Size3,C2Size1,C2Size2,C2Size3) 
		VALUES (
			SOURCE.[UploadTimestamp]
			,SOURCE.[StringDate]
			,SOURCE.[DATE]
			,SOURCE.[SHIFT]
			,SOURCE.[CREW]
			,SOURCE.[HEATS]
			,SOURCE.[C1MOLD]
			,SOURCE.[C1MILL]
			,SOURCE.[C1SIZE]
			,SOURCE.[C1HEATS]
			,SOURCE.[C2MOLD]
			,SOURCE.[C2MILL]
			,SOURCE.[C2SIZE]
			,SOURCE.[C2HEATS]
			,SOURCE.C1Size1
			,SOURCE.C1Size2
			,SOURCE.C1Size3
			,SOURCE.C2Size1
			,SOURCE.C2Size2
			,SOURCE.C2Size3
			);
	
		declare @rc int =@@ROWCOUNT
		if @rc>0
		begin
			set @logMsg = cast(@rc as varchar) + ' rows affected '  + @buf
			EXEC SYSTEM.LogMsg @message = @logMsg
			,@procedure = @procName
			,@typeCode = 'DEBUG'
			,@logError = 0
		end
		------------------
		---- Results -----
		------------------
		select * from MES.RunSchedule where UploadTimestamp = @datetime

	END TRY
	BEGIN CATCH

		SELECT 	@logMsg = ERROR_MESSAGE()

		SET @logMsg = 'Failed ' + @logMsg
			RAISERROR( @logMsg ,0,1) WITH NOWAIT

		EXEC SYSTEM.LogMsg @message = @logMsg
			,@procedure = @procName
			,@typeCode = 'ERROR'
			,@logError = 1

	END CATCH


	   
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END














-------------------------------------------
----- 10/2/2023 ---------------------------
-------------------------------------------
USE [MES]
GO
/****** Object:  StoredProcedure [MES].[UploadRunSchedule]    Script Date: 10/2/2023 09:39:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [MES].[UploadRunSchedule]
	@Layout XML = NULL,
	@Save bit = 0,
	@CurrentUser VARCHAR(50) = NULL -- auto included by 4i
AS
BEGIN
	-------------------------
	--- Common Variables ----
	-------------------------
	
	declare @procName VARCHAR(300)
	declare @finishEndMSG VARCHAR(300)
	declare @logMsg VARCHAR(MAX)
	SELECT @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	DECLARE @buf varchar(200) = ''
	SET NOCOUNT ON	
	-----------------------
	-- Variables declaration
	-----------------------
	declare @datetime datetime = GETUTCDATE()
	declare @layoutTable table (
		UploadTimestamp datetime,
		StringDate		varchar(200),
		[DATE]			date,
		[SHIFT]			char(1),
		[CREW]			char(1),
		[HEATS]			int,
		[C1MOLD]		varchar(200),
		[C1MILL]		varchar(200),
		[C1SIZE]		varchar(200),
		[C1HEATS]		int,
		[C2MOLD]		varchar(200),
		[C2MILL]		varchar(200),
		[C2SIZE]		varchar(200),
		[C2HEATS]		int,
		Sizes			varchar(MAX)
	)
	-----------------------
	-- Query
	-----------------------
	;with cte as (
		SELECT
			[StringDate]=	T.Variable.value('@DATE','NVARCHAR(MAX)'),
			[SHIFT]		=	T.Variable.value('@SHIFT','NVARCHAR(MAX)'),
			[CREW]		=	T.Variable.value('@CREW','NVARCHAR(MAX)'),
			[HEATS]		=	T.Variable.value('@HEATS','VARCHAR(MAX)'),
			[C1MOLD]	=  T.Variable.value('@C1MOLD','VARCHAR(MAX)'),
			[C1MILL]	=  T.Variable.value('@C1MILL','VARCHAR(MAX)'),
			[C1SIZE]	=  T.Variable.value('@C1SIZE','VARCHAR(MAX)'),
			[C1HEATS]	= T.Variable.value('@C1HEATS','VARCHAR(MAX)'),
			[C2MOLD]	=  T.Variable.value('@C2MOLD','VARCHAR(MAX)'),
			[C2MILL]	=  T.Variable.value('@C2MILL','VARCHAR(MAX)'),
			[C2SIZE]	=  T.Variable.value('@C2SIZE','VARCHAR(MAX)'),
			[C2HEATS]	= T.Variable.value('@C2HEATS','VARCHAR(MAX)'),
			Sizes		=	T.Variable.value('@Sizes','VARCHAR(MAX)')
		FROM
		@Layout.nodes('XMLRoot/Row') AS T(Variable)	
	)
	insert into @layoutTable(UploadTimestamp,StringDate,[DATE],[SHIFT],[CREW],[HEATS]
		,[C1MOLD],[C1MILL],[C1SIZE],[C1HEATS],[C2MOLD],[C2MILL],[C2SIZE],[C2HEATS]
		,Sizes)
	select 
		@datetime
		,StringDate
		,case 
			when 
				DATEDIFF(MONTH,@datetime, CAST( StringDate + CAST(YEAR(GETUTCDATE()) as varchar) as datetime) ) < 3
			then
				CAST(CAST( StringDate + CAST(YEAR(GETUTCDATE()) as varchar) as datetime) as date) 
			when 
				DATEDIFF(MONTH,@datetime, CAST( StringDate + CAST(YEAR(GETUTCDATE()) + 1 as varchar) as datetime) ) < 3
			then			
				CAST(CAST( StringDate + CAST(YEAR(GETUTCDATE()) + 1 as varchar) as datetime) as date)
			else
				null
			end
		,cast([SHIFT] as char(1))		
		,cast([CREW]  as char(1))	
		,cast([HEATS] as int)	
		,[C1MOLD]	
		,[C1MILL]	
		,[C1SIZE]	
		,cast([C1HEATS] as int)	
		,[C2MOLD]	
		,[C2MILL]	
		,[C2SIZE]	
		,cast([C2HEATS]	as int)
		,Sizes
	from cte

	delete from @layoutTable 
	where
		[DATE]		is null and
		[CREW]		is null and
		[HEATS]		is null and
		[C1MOLD]	is null and
		[C1MILL]	is null and
		[C1SIZE]	is null and
		[C1HEATS] 	is null and
		[C2MOLD]	is null and
		[C2MILL]	is null and
		[C2SIZE]	is null and
		[C2HEATS]	is null

	if (@Save = 0)
	begin
		select 
			[DATE] = CONVERT(varchar,[DATE],1)
			,[SHIFT]
			,CREW
			,HEATS
			,C1MOLD
			,C1MILL
			,C1SIZE
			,C1HEATS
			,C2MOLD
			,C2MILL
			,C2SIZE
			,C2HEATS
		from @layoutTable
		return
	end

	BEGIN TRY
		
		MERGE MES.RunSchedule AS TARGET
		USING @layoutTable AS SOURCE 
		ON TARGET.[StringDate] = SOURCE.[StringDate]  AND TARGET.[SHIFT] = SOURCE.[SHIFT]												  
		WHEN MATCHED THEN 
		UPDATE SET 
				TARGET.[UploadTimestamp]	= SOURCE.[UploadTimestamp]	
				,TARGET.[DATE]				= SOURCE.[DATE]				
				,TARGET.[CREW]				= SOURCE.[CREW]
				,TARGET.[HEATS]		= SOURCE.[HEATS]	
				,TARGET.[C1MOLD]	= SOURCE.[C1MOLD]
				,TARGET.[C1MILL]	= SOURCE.[C1MILL]		
				,TARGET.[C1SIZE]	= SOURCE.[C1SIZE]
				,TARGET.[C1HEATS]	= SOURCE.[C1HEATS]
				,TARGET.[C2MOLD]	= SOURCE.[C2MOLD]
				,TARGET.[C2MILL]	= SOURCE.[C2MILL]		
				,TARGET.[C2SIZE]	= SOURCE.[C2SIZE]
				,TARGET.[C2HEATS]	= SOURCE.[C2HEATS]
				,TARGET.Sizes = SOURCE.Sizes

		WHEN NOT MATCHED BY TARGET THEN 
		INSERT ([UploadTimestamp],[StringDate],[DATE],[SHIFT],[CREW],[HEATS],[C1MOLD], [C1MILL],[C1SIZE],[C1HEATS],[C2MOLD], [C2MILL],[C2SIZE],[C2HEATS]
				,Sizes) 
		VALUES (
			SOURCE.[UploadTimestamp]
			,SOURCE.[StringDate]
			,SOURCE.[DATE]
			,SOURCE.[SHIFT]
			,SOURCE.[CREW]
			,SOURCE.[HEATS]
			,SOURCE.[C1MOLD]
			,SOURCE.[C1MILL]
			,SOURCE.[C1SIZE]
			,SOURCE.[C1HEATS]
			,SOURCE.[C2MOLD]
			,SOURCE.[C2MILL]
			,SOURCE.[C2SIZE]
			,SOURCE.[C2HEATS]
			,SOURCE.Sizes
			);
	
		declare @rc int =@@ROWCOUNT
		if @rc>0
		begin
			set @logMsg = cast(@rc as varchar) + ' rows affected '  + @buf
			EXEC SYSTEM.LogMsg @message = @logMsg
			,@procedure = @procName
			,@typeCode = 'DEBUG'
			,@logError = 0
		end
		------------------
		---- Results -----
		------------------
		select * from MES.RunSchedule where UploadTimestamp = @datetime

	END TRY
	BEGIN CATCH

		SELECT 	@logMsg = ERROR_MESSAGE()

		SET @logMsg = 'Failed ' + @logMsg
			RAISERROR( @logMsg ,0,1) WITH NOWAIT

		EXEC SYSTEM.LogMsg @message = @logMsg
			,@procedure = @procName
			,@typeCode = 'ERROR'
			,@logError = 1

	END CATCH


	   
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END
GO