
USE [IHBoxSystem]
GO
CREATE ROLE wr_rw AUTHORIZATION dbo 
GO  

USE [IHBoxSystem]
GO
CREATE ROLE wr_r AUTHORIZATION dbo 
GO  

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

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

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

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


EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [wr_r] -- The role
,@name = N'securedsp-MES.GetHeatWeights' -- 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
           (
		   '!/weights-report(/:start)(/:end)(/:shiftId)(/:crewId)'
           , 'weights-report'
		   , 'custom-screens/weights-report/'
           , 'wr_r'           
		   )
		   SET @result_routeId = @@IDENTITY
INSERT INTO [FrontEnd].[Menu]
           ([Parent]
           ,[Name]
           ,[RouteID]
           ,[SortOrder]
           ,[Enabled]
           ,[Visible]
           ,[RequiredRole]
           ,[Group]
          )
     VALUES
           (
		   @parent
		   ,'Weights Report'
           ,@result_routeId 
           ,21
           ,1
           ,1
           ,'wr_r'
           ,NULL
         )
GO


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

DECLARE @ModuleId INT

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

		  SET @ModuleId = @@IDENTITY

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

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



/*------------------------------------------------------SP------------------------------------------------------*/
USE [MES]
GO
/****** Object:  StoredProcedure [MES].[GetHeatWeights]    Script Date: 1/29/2024 1:49:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MES].[GetHeatWeights]
	@Start DATETIME = NULL, 
	@End DATETIME = NULL,
	@ShiftId int = null,
	@CrewId char(1) = null,

	@sortType VARCHAR(50) = NULL,
	@fromRow INT = null, -- From what row to start fetching from
	@toRow INT = null, -- 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 @startUTC DATETIME = IH.ToUTC(@Start, null)
	DECLARE @endUTC DATETIME = IH.ToUTC(@End, null)
	
	
	-----------------------
	---- Begin Query ------
	-----------------------

	select
		HeatName = h.[Name]
		,ShiftId = case when h.ShiftId = 1 then 'D' else 'N' end
		,h.CrewId
		,h.SteelWeight
		,h.TareWeight
		,h.FinalArmWeight
		,h.InitialArmWeight
		,h.AlloyWeight		
		,[BucketScrapWeight] = (	select
										sum(z.ScrapWeight )
									from eaf.processes y
									left join eaf.buckets z on z.ProcessId = y.Id
									where y.heatId = h.Id	
								)
		,[BucketOtherWeight] = ( select
										sum(z.OthersWeight )
									from eaf.processes y
									left join eaf.buckets z on z.ProcessId = y.Id
									where y.heatId = h.Id)

		,[StartTap] = IH.ToLocal(p.[StartTap],null)
		,DrainWeight = cast(p.DrainWeight  as decimal(10,2)) 
		,p.PourbackWeight

		,[CasterInitialGrossWeight] = h.InitialArmWeight
		,[CasterFinalGrossWeight] = h.FinalArmWeight
		,[CasterProcessedWeight] = h.InitialArmWeight - h.FinalArmWeight
		,[ProductsWeight] = (
					select 
						cast(sum(z.[Weight]) as decimal(10,2)) 
			from cas.processes y
			left join cas.Products z on z.ProcessId = y.Id 
			where z.HeatId = h.Id
			)
		,[EndCast]  = (
				select 
					IH.ToLocal(max(z.CastEnd),null)
				from cas.Processes z
				where z.HeatId = h.Id
		)
		
	into #temp
	from eaf.Processes p
	left join mes.heats h on h.Id = p.HeatId
	where p.ProductionDate between @startUTC and @endUTC
	and @ShiftId is null or @ShiftId = h.ShiftId
	and @CrewId is null or @CrewId = h.CrewId
	order by h.ProductionDate desc


	SELECT *
		FROM #temp record
		ORDER BY CASE 
				WHEN @sortType IS NULL
					THEN record.[StartTap]
				END desc
		OFFSET @fromRow ROWS
		FETCH NEXT @rowsToFetch ROWS ONLY
END
go
EXEC MES.GetHeatWeights '2023-11-01', '2023-11-02',null,null,null,0,100,100