USE [IHBoxSystem]
GO

/* README 


*/

declare @parent int
select @parent=id from [FrontEnd].[Menu] where name='EAF'

delete from [FrontEnd].[Routes] where [ModuleName]='eaf-electrodes-report'
delete from [FrontEnd].[Menu] where [Name]='EAF Electrodes Report'


INSERT INTO [FrontEnd].[Routes]
           ([Route]
           ,[ModuleName]
           ,[ModulePath]
           ,[RequiredRole]
       )
     VALUES
           (
		   '!/eaf-electrodes-report(/:start)(/:end)(/:furnaceCode)'
           , 'eaf-electrodes-report'
		   , 'custom-screens/eaf-electrodes-report/'
           , 'EquipmentsRole'
           
		   )



INSERT INTO [FrontEnd].[Menu]
           ([Parent]
           ,[Name]
           ,[RouteID]
           ,[SortOrder]
           ,[Enabled]
           ,[Visible]
           ,[RequiredRole]
           ,[Group]
          )
     VALUES
           (
		   @parent
		   ,'EAF Electrodes Report'
           ,@@IDENTITY
           ,1
           ,1
           ,1
           ,'EquipmentsRole'
           ,NULL
         )
GO

USE IHBoxSystem

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

Go

USE [MES]
GO
/****** Object:  StoredProcedure [EAF].[GetElectrodesReportWeb]    Script Date: 4/16/2019 5:53:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [EAF].[GetElectrodesReportWeb]

@start datetime = NULL,
@end datetime = NULL,
@FurnaceCode int =1

as
begin

	if @end is NULL 
		set @end = GETUTCDATE()

	if @start is NULL 
		set @start = dateadd(DAY,-30,@end)
	
	print @start
	print @end

	declare @buf1 nvarchar(max)
	declare @buf2 nvarchar(max)
	declare @buf nvarchar(max)

	SET @buf1 = N'select
	heatid,
	ProductionDate,
	ShiftId,
	id,
	energy,
	poweron,
	rn  = Row_number() over (order by id asc)
	into #t2
	from EAF.Processes
	where FurnaceCode = ' + cast(@FurnaceCode as varchar) + '
	and Start  between '''+cast(@start as varchar)+''' and 
	'''+cast(@end as varchar)+'''
	and Electrode'
	
	SET @buf2 = N'Changed <> 0 

	select 
	ProductionDate=case when Grouping(h.Name)= 1 then NULL else max(c.ProductionDate) end,
	Shift=case when Grouping(h.Name)= 1 then NULL else max(c.ShiftId) end,
	HeatId=case when Grouping(h.Name)= 1 then ''Average'' else max(h.Name) end,
	HeatsBetweenChanges = avg(c.id - p.id),
	Energy = avg(c.energy),
	PowerOn = avg(c.poweron)
	from #t2 c
	join #t2 p  on c.rn = p.rn+1
	left join MES.Heats h on c.HeatId = h.id
	group by h.[name] with rollup
	order by ProductionDate Asc';
	  
	SET @buf = @buf1+'1'+@buf2
	EXECUTE sp_executesql @buf

	SET @buf = @buf1+'2'+@buf2
	EXECUTE sp_executesql @buf

	SET @buf = @buf1+'3'+@buf2
	EXECUTE sp_executesql @buf


end

