
USE [IHBoxSystem]
GO
CREATE ROLE swr_rw AUTHORIZATION dbo 
GO  

USE [IHBoxSystem]
GO
CREATE ROLE swr_r AUTHORIZATION dbo 
GO  

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

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

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

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


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



declare @parent int, @result_routeId INT
select @parent=id from [FrontEnd].[Menu] where name='MES'

INSERT INTO [FrontEnd].[Routes]
           ([Route]
           ,[ModuleName]
           ,[ModulePath]
           ,[RequiredRole]
       )
     VALUES
           (
		   '!/sequence-weights-report(/:start)(/:end)(/:shiftId)(/:crewId)'
           , 'sequence-weights-report'
		   , 'custom-screens/sequence-weights-report/'
           , 'swr_r'           
		   )
		   SET @result_routeId = @@IDENTITY
INSERT INTO [FrontEnd].[Menu]
           ([Parent]
           ,[Name]
           ,[RouteID]
           ,[SortOrder]
           ,[Enabled]
           ,[Visible]
           ,[RequiredRole]
           ,[Group]
          )
     VALUES
           (
		   @parent
		   ,'Sequence Weights Report'
           ,@result_routeId 
           ,22
           ,1
           ,1
           ,'swr_r'
           ,NULL
         )
GO


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

DECLARE @ModuleId INT

INSERT INTO [SECURITY].[Modules]
           ([Code]
           ,[Name])
     VALUES
           ('sequence_weights_report'
           ,'Sequence Weights Report')

		  SET @ModuleId = @@IDENTITY

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

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



/*------------------------------------------------------SP------------------------------------------------------*/

USE [MES]
GO
/****** Object:  StoredProcedure [MES].[GetSequenceWeightsReport]    Script Date: 1/2/2024 15:45:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [MES].[GetSequenceWeightsReport]
	@Start DATETIME = NULL, 
	@End DATETIME = NULL,
	@CasterCode int = null,	
	@ExportToExcel bit = 0,

	@sortType VARCHAR(50) = NULL,
	@fromRow INT = 0, -- From what row to start fetching from
	@toRow INT = 100, -- To row (@toRow row num not included in result data)
	@rowsToFetch INT = null -- Amount of rows to fetch


AS
BEGIN

	------------------------------------------
	---- Variables and Table Definition ------
	------------------------------------------
	IF @End is null set @End = GETUTCDATE()
	IF @Start is null set @Start = DATEADD(DAY,-5,@End)


	DECLARE @startNoTime DATETIME = CONVERT(DATETIME, CONVERT(DATE, @Start))
	DECLARE @endNoTime DATETIME = CONVERT(DATETIME, CONVERT(DATE, @End))

	DECLARE @startAdjusted DATETIME = DATEADD(HOUR, 9, @startNoTime)
	DECLARE @endAdjusted DATETIME = DATEADD(HOUR, 21, @endNoTime)

	DECLARE @startUTC DATETIME = IH.ToUTC(@startAdjusted, null)
	DECLARE @endUTC DATETIME = IH.ToUTC(@endAdjusted, null)
	
	
	-----------------------
	---- Begin Query ------
	-----------------------

	select
		*
		,SteelWeight = (select 
							sum(SteelWeight) 
						from mes.Heats m
						where m.Id in (
							select HeatId from cas.Processes zz
							where zz.[SequenceId] = p.Id)
						)
		,AlloyWeight = (select 
							sum(AlloyWeight) 
						from mes.Heats m
						where m.Id in (
							select HeatId from cas.Processes zz
							where zz.[SequenceId] = p.Id)
						)
		,[BucketScrapWeight] = (	select
									sum(z.ScrapWeight ) / 2000.0
								from eaf.processes y
								left join eaf.buckets z on z.ProcessId = y.Id
								where y.heatId in (
										select HeatId from cas.Processes zz
									where zz.[SequenceId] = p.Id
								)	
							)
	,[BucketOtherWeight] = (	select
									sum(z.OthersWeight ) / 2000.0
								from eaf.processes y
								left join eaf.buckets z on z.ProcessId = y.Id
								where y.heatId in (
										select HeatId from cas.Processes zz
									where zz.[SequenceId] = p.Id
								)	
							)
	into #temp
	from cas.[Sequences] p
	where p.[CastStart] between @startUTC and @endUTC
	and (@CasterCode is null or @CasterCode = p.CasterCode)
	and CastEnd is not null


	if (@ExportToExcel = 0)
		begin
			SELECT *
				FROM #temp record
				ORDER BY CASE 
						WHEN @sortType IS NULL
							THEN record.CastStart
						END desc
				OFFSET @fromRow ROWS
				FETCH NEXT @rowsToFetch ROWS ONLY

		end
		else
			select * from #temp record order by record.CastStart


	
		select @startutc, @endutc	
END


go
exec [MES].[GetSequenceWeightsReport] '2023-11-01'