USE [IHBoxSystem]
GO


IF DATABASE_PRINCIPAL_ID('casaterShiftReport_readWrite') IS NULL
BEGIN
	CREATE ROLE casaterShiftReport_readWrite;
end
go


INSERT INTO [FrontEnd].[Routes]
           ([Route]
           ,[ModuleName]
           ,[ModulePath]
           ,[RequiredRole]
       )
     VALUES
           (
		   '!/caster-shift-report(/:productionDate)(/:shiftId)'
           , 'caster-shift-report'
		   , 'custom-screens/caster-shift-report/'
           , 'casaterShiftReport_readWrite'
           
		   )



INSERT INTO [FrontEnd].[Menu]
           ([Parent]
           ,[Name]
           ,[RouteID]
           ,[SortOrder]
           ,[Enabled]
           ,[Visible]
           ,[RequiredRole]
           ,[Group]
          )
     VALUES
           (
		   (select top 1 Id from FrontEnd.menu where Name = 'Caster' and Parent is null)
		   ,'Shift Report'
           ,@@IDENTITY
           ,(select Max(SortOrder)+1 from FrontEnd.Menu where Parent = (select Id from frontend.Menu where [Name] = 'Caster'))
           ,1
           ,1
           ,'casaterShiftReport_readWrite'
           ,NULL
         )
GO

USE IHBoxSystem

EXEC sp_addrolemember 'casaterShiftReport_readWrite', 'admin'

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [casaterShiftReport_readWrite] -- The role
,@name = N'securedsp-CAS.GetShiftReport' -- the procedure
,@value = N'' 

Go

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


/*------------------------------------------------------Profiles------------------------------------------------------*/
USE [IHBoxSystem]
GO

DECLARE @ModuleId INT

INSERT INTO [SECURITY].[Modules]
		   ([Code]
		   ,[Name])
	 VALUES
		   ('caster_shift_report'
		   ,'Caster Shift Report')

		  SET @ModuleId = @@IDENTITY

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



/*-----------------------------------------------------------------------------------------------------------------------*/
USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[GetShiftReport]    Script Date: 18/2/2022 11:51:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [CAS].[GetShiftReport] 
	@ProductionDate DATETIME = NULL
	,@ShiftId INT = 1
	,@timezoneCode CHAR(10) = NULL
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300)
		,
		--The name of the procedure with its schema.
		@procName VARCHAR(300)

	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	SET @finishEndMSG = @procName + ' finished.'

	PRINT @procName + '  @ProductionDate: ' + ISNULL(CAST(@ProductionDate AS VARCHAR), '') 
					+ ', @ShiftId: '	  + ISNULL(CAST(@ShiftId AS VARCHAR), '') 
					+ ', @timezoneCode: ' + ISNULL(CAST(@timezoneCode AS VARCHAR), '')

	-----------------------
	-- Variables
	-----------------------
	DECLARE @startUtc DATETIME, @endUtc DATETIME
	-----------------------
	-- Parameters default
	-----------------------
	IF (@timeZoneCode IS NULL)		SET @timeZoneCode = 'EDTIND'
	IF (@ProductionDate IS NULL)	SET @ProductionDate = dateadd(day,-1,getdate());
	IF (@ShiftId IS NULL)			SET @ShiftId = 1;

	-----------------------
	-- Set Delays variables
	-----------------------
	SELECT @startUtc = IH.ToUTC(dateadd(hour, 9 + 12 * (@ShiftID - 1), @ProductionDate), @timezoneCode)
	SET @endUtc = dateadd(hour, 12, @startUtc)

	-----------------------
	-- Begin Query
	-----------------------
		-----------------------------------
		-- |Main| Data - EAF Processes List
		-----------------------------------

		SELECT 
			 [Id] = process.Id
		INTO #records1
		FROM CAS.Processes process
		JOIN MES.Heats heat ON heat.Id = process.HeatId
		WHERE process.ProductionDate = @ProductionDate AND process.ShiftId = @ShiftId and process.[End] is not null

		----------------------------
		-- Table -- HEATS GANTT
		----------------------------
		DECLARE  @now  DATETIME= GETUTCDATE()
		SELECT  
			[CasterNumber] = case when process.CasterCode = 1 then 'Caster 1' else 'Caster 2' end,
			[HeatName] =  heat.[Name],
			[Start] = IH.ToLocal(process.[Start], @timezoneCode),
			[End] = IH.ToLocal(ISNULL(process.[End], @now), @timezoneCode),
			[PowerOn] = CAST(DATEDIFF(MINUTE, process.[Start], ISNULL(process.[End], @now)) AS VARCHAR) + ':' + CAST(DATEDIFF(SECOND, process.[Start], ISNULL(process.[End], @now)) % 60 AS VARCHAR),
			[Duration] = CAST(DATEDIFF(MINUTE, process.[Start], ISNULL(process.[End], @now)) AS VARCHAR),
			[EnabledS1] = case when process.CasterCode = 1 then ( select count(*) from cas.StrandProcesses z where z.processId = process.Id and z.StrandId = 1) else 1 end, 
			[EnabledS2] = case when process.CasterCode = 1 then ( select count(*) from cas.StrandProcesses z where z.processId = process.Id and z.StrandId = 2) else 1 end, 
			[EnabledS3] = case when process.CasterCode = 1 then ( select count(*) from cas.StrandProcesses z where z.processId = process.Id and z.StrandId = 3) else 1 end, 
			[EnabledS4] = case when process.CasterCode = 1 then ( select count(*) from cas.StrandProcesses z where z.processId = process.Id and z.StrandId = 4) else 1 end 
		FROM CAS.Processes process
			LEFT JOIN MES.Heats heat ON heat.Id = process.HeatId
				LEFT JOIN CAT.Ladles ladle ON ladle.Id = heat.LadleId
		WHERE    process.Id in (select Id from #records1)
	PRINT @finishEndMSG
	RETURN 0
END



