USE [IHBoxSystem]
GO


USE [IHBoxSystem]
GO
CREATE ROLE eafchangeequipment_readWrite AUTHORIZATION dbo 
GO  

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

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

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

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

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

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

Go

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

INSERT INTO [FrontEnd].[Routes]
           ([Route]
           ,[ModuleName]
           ,[ModulePath]
           ,[RequiredRole]
       )
     VALUES
           (
		   '!/eaf-change-equipment(/:furnaceCode)'
           , 'eaf-change-equipment'
		   , 'custom-screens/eaf-change-equipment/'
           , 'eafchangeequipment_readWrite'           
		   )
		   SET @result_routeId = @@IDENTITY
INSERT INTO [FrontEnd].[Menu]
           ([Parent]
           ,[Name]
           ,[RouteID]
           ,[SortOrder]
           ,[Enabled]
           ,[Visible]
           ,[RequiredRole]
           ,[Group]
          )
     VALUES
           (
		   @parent
		   ,'EAF Change Equipment'
           ,@result_routeId 
           ,3
           ,1
           ,1
           ,'eafchangeequipment_readWrite'
           ,NULL
         )
GO


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

DECLARE @ModuleId INT

INSERT INTO [SECURITY].[Modules]
           ([Code]
           ,[Name])
     VALUES
           ('eaf_change_equipment'
           ,'EAF Change Equipment')

		  SET @ModuleId = @@IDENTITY

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





/*-----------------------------------------------------------------------GET-------------------------------------------------------------------------------*/
USE [MES]
GO
/****** Object:  StoredProcedure [EAF].[GetComponentsWeb]    Script Date: 2 ene. 2020 09:31:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [EAF].[GetComponentsWeb]
@FurnaceCode int,
@timeZoneCode CHAR(10) = NULL

as
begin
	

	select
	cc.*
	,LastChange = cce.[DateTime]
	,[DaysSinceLastChange] = datediff(day,cce.datetime,getutcdate())
	,ChangeSource = cce.EventSourceCode
	,Comments = cce.Comments
	,EquipmentTypeName = cet.[Name]
	,EquipmentName = ce.[Name]
	from EAF.GetComponentsCounters(@FurnaceCode, NULL) cc
	left join EAF.ComponentChangeEvents cce on cce.id = cc.ChangeId
	left join EAF.catEquipmentTypes cet on cet.Id =cc.EquipmentTypeId
	left join EAF.catEquipments ce on ce.Id = cc.EquipmentId


end
GO

/*-----------------------------------------------------------------------Insert-------------------------------------------------------------------------------*/
/****** Object:  StoredProcedure [EAF].[ChangeComponent]    Script Date: 12/31/2019 12:16:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [EAF].[ChangeComponent]
	@datetime datetime = NULL,
	@FurnaceCode int ,
	@TypeId int,
	@IdFromPLC int = NULL,
	@EventSourceId int = 0,
	@Comments varchar(250) ='' 
as
begin

-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		@procName VARCHAR(300),
		@result INT,
		@resultmsg varchar(MAX) = 'OK'


	SELECT
		@procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID),
		@result = 0
	

	PRINT @procName + ' Parameters:'
		+ '@datetime: ' + ISNULL(CAST(@datetime AS VARCHAR), 'null')
		+ ', @furnaceCode: ' + ISNULL(CAST(@furnaceCode AS VARCHAR), 'null')
		+ ', @LocationId: ' + ISNULL(CAST(@typeId AS VARCHAR), 'null')
		+ ', @IdFromPLC: ' + ISNULL(CAST(@IdFromPLC AS VARCHAR), 'null')

		if (@datetime is null)
			Select @datetime = GETUTCDATE()

		if (@FurnaceCode is null or @typeId is null)
		begin
			set @result=1
			set @resultmsg = 'wrong parameters furnacecode or typeid'
		end
		if (@result=0)
		BEGIN TRY

		declare @EquipmentId int
		select @EquipmentId = Id from EAF.catEquipments where EquipmentTypeId=@TypeId and IdFromPLC=@IdFromPLC


		INSERT INTO [EAF].[ComponentChangeEvents]
				   ([DateTime]
				   ,[FurnaceCode]
				   ,[EquipmentLocationId]
				   ,[EquipmentID]
				   ,[EventSourceCode]
				   ,[Comments])
			 VALUES
				   (@datetime, @FurnaceCode,@TypeId,@EquipmentId,@EventSourceId,@Comments)

		END TRY
		BEGIN CATCH
			set @result = 2
			set @resultmsg = ERROR_MESSAGE()

		END CATCH

		BEGIN TRY
			DECLARE @msg varchar(max)

			SET @msg = 
			 @procName + ' Parameters:'
			+ '@datetime: ' + ISNULL(CAST(@datetime AS VARCHAR), 'null')
			+ ', @furnaceCode: ' + ISNULL(CAST(@furnaceCode AS VARCHAR), 'null')
			+ ', @LocationId: ' + ISNULL(CAST(@typeId AS VARCHAR), 'null')
			+ ', @EquipmentIdFromPLC: ' + ISNULL(CAST(@IdFromPLC AS VARCHAR), 'null')
			+ '-- result '+ ISNULL(CAST(@resultmsg AS VARCHAR), 'null')


			EXEC SYSTEM.LogMsg @message = @msg, @procedure=@procName  
		END TRY
		BEGIN CATCH
			PRINT 'Error on message log. ' + ERROR_MESSAGE()
		END CATCH
	

end


GO
/****** Object:  StoredProcedure [EAF].[ChangeComponentWeb]    Script Date: 12/31/2019 12:16:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [EAF].[ChangeComponentWeb]
	@datetime datetime = NULL,
	@FurnaceCode int ,
	@EquipmentLocationId int,
	@EquipmentId int = NULL,
	@Comments varchar(max) ='' 
as
begin

-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		@procName VARCHAR(300),
		@result INT,
		@resultmsg varchar(MAX) = 'OK'


	SELECT
		@procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID),
		@result = 0
	

	PRINT @procName + ' Parameters:'
		+ '@datetime: ' + ISNULL(CAST(@datetime AS VARCHAR), 'null')
		+ ', @furnaceCode: ' + ISNULL(CAST(@furnaceCode AS VARCHAR), 'null')
		+ ', @LocationId: ' + ISNULL(CAST(@EquipmentLocationId AS VARCHAR), 'null')
		+ ', @EquipmentId: ' + ISNULL(CAST(@EquipmentID AS VARCHAR), 'null')

		if (@datetime is null)
			Select @datetime = GETUTCDATE()

		if (@FurnaceCode is null or @EquipmentLocationId is null)
				RAISERROR('Wrong FurnaceCode or Location',1,1)
		
		if @EquipmentId is not null and (select id from eaf.catEquipments where id=@EquipmentId)=0
			RAISERROR('Equipment does not exist',2,1)

			
		if @EquipmentId is not  null and (select EquipmentTypeid from eaf.catEquipments where id=@EquipmentId)<>(select EquipmentTypeId from eaf.catEquipmentLocations where id=@EquipmentLocationId)
			RAISERROR('Equipment not right type',3,1)

		if @EquipmentId is not null and @EquipmentId in
		(select EquipmentId from EAF.EquipmentLastChanges elc left join EAF.ComponentChangeEvents cce on cce.id=elc.LastChangeId where Equipmentid is not null)
				RAISERROR('Equipment being used',4,1)

	

		INSERT INTO [EAF].[ComponentChangeEvents]
				   ([DateTime]
				   ,[FurnaceCode]
				   ,[EquipmentLocationId]
				   ,[EquipmentID]
				   ,[EventSourceCode]
				   ,[Comments])
			 VALUES
				   (@datetime, @FurnaceCode,@EquipmentLocationId,@EquipmentId,'W',@Comments)

		
		BEGIN TRY
			DECLARE @msg varchar(max)

			SET @msg = 				 @procName + ' Parameters:'
		+ '@datetime: ' + ISNULL(CAST(@datetime AS VARCHAR), 'null')
		+ ', @furnaceCode: ' + ISNULL(CAST(@furnaceCode AS VARCHAR), 'null')
		+ ', @LocationId: ' + ISNULL(CAST(@EquipmentLocationId AS VARCHAR), 'null')
		+ ', @EquipmentId: ' + ISNULL(CAST(@EquipmentID AS VARCHAR), 'null')


			EXEC SYSTEM.AuditMsg @message = @msg  
		END TRY
		BEGIN CATCH
			PRINT 'Error on message audit. ' + ERROR_MESSAGE()
		END CATCH
	

end


GO
/*-----------------------------------------------------------------------Pickers-------------------------------------------------------------------------------*/
/****** Object:  StoredProcedure [EAF].[GetAvailableSparesWeb]    Script Date: 12/31/2019 12:16:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [EAF].[GetAvailableSparesWeb]
@EquipmentTypeId int
as
begin


	select * from EAF.catEquipments e
	where e.EquipmentTypeId = @EquipmentTypeId
	
	and e.id not in (select equipmentId from EAF.EquipmentlastChanges elc left join EAF.ComponentChangeEvents cce on elc.LastChangeId=cce.id where not equipmentId is null)
	
				



end

GO
/*-------------------------------------------------------------------Others----------------------------------------------------------------------------------------*/
USE [MES]
GO
/****** Object:  UserDefinedFunction [EAF].[GetComponentsCounters]    Script Date: 12/31/2019 8:07:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [EAF].[GetComponentsCounters] 
(
	@FurnaceCode int =1 ,
	@DateTime datetime = NULL
)
RETURNS 

@table TABLE 
(
	[EquipmentLocationId] int,	
	[EquipmentTypeId] int,	
	[EquipmentId] int,
	[ChangeId] int,
	[LocationName] varchar(max),
	[CounterColumn] varchar(max),
	[Counter] real
)

AS
BEGIN
	if @DateTime is NULL
	 set @DateTime = GETUTCDATE()

	 insert into @table
	 select 
	 EquipmentLocationId = el.id
	 ,EquipmentTypeId = et.id
	 ,EquipmentId = e.id
	 ,ChangeId= cce.Id
	 ,coalesce(et.Name + ' ' ,'') + coalesce(el.Name,'') 
	 ,CounterColumn = coalesce(etc.CounterColumn,'Heats')
	 ,[Counter] = 
	 (
		select 
		case lower(etc.CounterColumn)
		when 'modoperations' then sum(ep.MODOperations) 
		when 'switchoperations' then sum(ep.switchOperations) 
		when 'tapchanges' then  sum(ep.tapChanges) 
		ELSE count(*)
		END
	 from MES.EAF.Processes ep where ep.FurnaceCode =@FurnaceCode and ep.EndTap between coalesce(cce.[DateTime],'2019-1-1') and @DateTime)

	 from EAF.catEquipmentLocations el
	 left join EAF.catEquipmentTypes et on el.EquipmentTypeId = et.id
	 left join EAF.catEquipmentLocationCounters etc on etc.EquipmentLocationId = el.id
	 left join (
	 select -- table that has the current equipment
	   EquipmentLocationId = el.id
	   ,LastChangeId = (select top 1 Id from EAF.ComponentChangeEvents z where z.EquipmentLocationID=el.Id and z.FurnaceCode=@FurnaceCode and z.[Datetime] < @datetime order by z.[DateTime] desc)
	   from EAF.catEquipmentLocations el
	 ) ce on el.id = ce.EquipmentLocationId
	 left join ComponentChangeEvents cce on ce.LastChangeId=cce.Id
	  left join EAF.catEquipments e on e.Id = cce.EquipmentID
	
	
	
	RETURN 
END
go



USE [MES]
GO
/****** Object:  View [EAF].[EquipmentLastChanges]    Script Date: 12/31/2019 6:43:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [EAF].[EquipmentLastChanges]
as
select 
furnacecode, equipmentLocationid
,LastChangeid = (select top 1 id from EAF.ComponentChangeEvents z where z.FurnaceCode=cce.furnacecode and z.equipmentLocationId=cce.EquipmentLocationId order by datetime desc)
from 
EAF.ComponentChangeEvents cce
group by cce.FurnaceCode, cce.EquipmentLocationId
GO


