-------------
-- ROLES
-------------
	USE [IHBoxSystem]
	GO
	CREATE ROLE vtdproduction_read AUTHORIZATION dbo 
	GO  

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

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


	CREATE ROLE vtdproduction_readWrite AUTHORIZATION dbo 
	GO  

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

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


-------------------
-- Menu and Route
-------------------
	declare @parent int, @result_routeId INT
	select @parent=id from [FrontEnd].[Menu] where [Name] = 'VTD' and Parent is null

	INSERT INTO [FrontEnd].[Routes]
			   ([Route]
			   ,[ModuleName]
			   ,[ModulePath]
			   ,[RequiredRole]
		   )
		 VALUES 
			   (
			   '!/vtd-production(/:start)(/:end)(/:tankCode)(/:crewId)(/:shiftId)(/)'
			   , 'vtd-production'
			   , 'custom-screens/vtd-production/'
			   , 'vtdproduction_read'           
			   )
			   SET @result_routeId = @@IDENTITY
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			  )
		 VALUES
			   (
			   @parent
			   ,'Production'
			   ,@result_routeId 
			   ,1
			   ,1
			   ,1
			   ,'vtdproduction_read'
			   ,NULL
			 )
	GO


-------------------
-- SP Permissions
-------------------
EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [vtdproduction_readWrite] -- The role
,@name = N'securedsp-VTD.GetProcesses' -- the procedure
,@value = N'' 
GO

-------------------
--	Profiles
-------------------
	USE [IHBoxSystem]
	GO

	DECLARE @ModuleId INT

	INSERT INTO [SECURITY].[Modules]([Code],[Name])
	VALUES('vtd_production','VTD Production')

	SET @ModuleId = @@IDENTITY

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


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











	
		-----------------------
		-- Stored Procedure
		-----------------------
USE [MES]
GO
/****** Object:  StoredProcedure [VTD].[GetProcesses]    Script Date: 2/6/2024 10:04:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [VTD].[GetProcesses]
	@start DATETIME = NULL,
	@end DATETIME = NULL,
	@tankCode INT = NULL,
	@crewId CHAR(1) = NULL,
	@shiftId INT = NULL,
	
	@timeZoneCode CHAR(10) = 'EDTIND',

	@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
	@sortType VARCHAR(50) = NULL -- To specify how to sort the data
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 + ' Parameters:'
		+ ', @start: ' + ISNULL(CAST(@start AS VARCHAR), '')
		+ ', @end: ' + ISNULL(CAST(@end AS VARCHAR), '')
		+ ', @crewId: ' + ISNULL(CAST(@crewId AS VARCHAR), '')
		+ ', @shiftId: ' + ISNULL(CAST(@shiftId AS VARCHAR), '')
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')
		+ ', @fromRow: ' + ISNULL(CAST(@fromRow AS VARCHAR), '')
		+ ', @toRow: ' + ISNULL(CAST(@toRow AS VARCHAR), '')
		+ ', @rowsToFetch: ' + ISNULL(CAST(@rowsToFetch AS VARCHAR), '')
		+ ', @sortType: ' + ISNULL(CAST(@sortType AS VARCHAR), '')

	-----------------------
	-- Parameters default
	-----------------------

	IF ((@rowsToFetch IS NULL)	OR (@rowsToFetch = - 1))
		SET @rowsToFetch = 100000

	IF (@fromRow IS NULL)
		SET @fromRow = 0

	IF (@Start = @End) SET @END = DATEADD(DAY,1,@Start)
	-----------------------
	-- Begin Query
	-----------------------
	select 
		[ShiftName] = case when p.ShiftId = 1 then 'D' else 'N' end		
		,GradeName = g.[Name]	
		,[StartLocal] = CONVERT(VARCHAR, IH.ToLocal(p.[Start], @timeZoneCode),120)
		,[EndLocal] = CONVERT(VARCHAR, IH.ToLocal(p.[End], @timeZoneCode),120)
		,HeatName= h.[Name]	
		,MeltOrder = LAD.GetMeltOrderString(l.MeltOrderNumber,l.MeltOrderSequence)
		--,p.Treatment
		
		,SteelWeight = cast (h.SteelWeight as decimal(18,2))
		,TotalTreatmentTime = DATEDIFF(SECOND,p.[Start],p.[End])
		,TotalSequenceTime = DATEDIFF(SECOND,p.[StartSequence],p.[EndSequence])
		,MinTorrTime = RIGHT(CONVERT(CHAR(8),DATEADD(second,MinimumTorrTime,0),108),5)
		,VacTime = RIGHT(CONVERT(CHAR(8),DATEADD(second,VacuumTime,0),108),5)
		,p.*
		,h.LadleId
		,TotalTime = (SELECT CONVERT(VARCHAR, DATEADD(SECOND, DATEDIFF(SECOND, p.[Start], p.[End]), 0), 108))
		, Temperature = CAST((SELECT MAX(pr.[Value]) FROM VTD.Probes pr WHERE pr.ProbeType = 'T' AND pr.ProcessId = p.Id) AS DECIMAL(10, 2))
, WireAdditions = CAST((SELECT SUM(WeightPV) FROM vtd.AdditionBatches b LEFT JOIN vtd.AdditionBatchDetails d ON d.AdditionBatchID = b.ID
                      WHERE b.ProcessId = p.Id AND b.SourceCode = 'W') AS DECIMAL(10, 2))
		,Aluminum = (SELECT SUM(ac.WeightValue) from VTD.AlloysConsumption ac where ac.MaterialId = 76 and ac.ProcessId = p.Id )
		,CaSi = (SELECT SUM(ac.WeightValue) from VTD.AlloysConsumption ac where ac.MaterialId = 82 and ac.ProcessId = p.Id )
		,FeTi = (SELECT SUM(ac.WeightValue) from VTD.AlloysConsumption ac where ac.MaterialId = 84 and ac.ProcessId = p.Id )
		INTO #temp

	from vtd.Processes p	
	left join mes.heats h on h.id = p.HeatId
		left join lad.Heats l on l.HeatNumber = h.[Name]
		left join cat.grades g on g.Id = h.GradeId
	WHERE 
		p.productionDate between @Start and DATEADD(DAY,1,@End)
		AND (@shiftId	IS NULL OR p.ShiftId = @shiftId)
		AND (@crewId	IS NULL OR p.CrewId = @crewId)		
		and (@tankCode is null or p.TankCode=@tankCode)
		AND p.[End] is not null
	order by p.id desc

	SELECT *
	FROM #temp record 
	ORDER BY CASE 
			WHEN @sortType IS NULL
				THEN record.Start
			END DESC OFFSET @fromRow ROWS

	FETCH NEXT @rowsToFetch ROWS ONLY

	
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0

END






------------------------------------------------------------------------

ALTER PROCEDURE VTD.UpdateFoamedStirredWell
 @Option CHAR,
 @ProcessId INT,
 @Enabled bit,
@CurrentUser varchar(50) = NULL
AS
BEGIN
------------------
-- Options  ---
------------------
-- F: isFoamed
-- S: Stirred Well

	IF(@Option = 'F')
	begin
		UPDATE VTD.Processes set isFoamed = @Enabled where Id = @ProcessId
	end

	IF(@Option = 'S')
	begin
		UPDATE VTD.Processes set isStirredWell = @Enabled where Id = @ProcessId
	end

	-----------------------
	-- Audit
	-----------------------
	BEGIN TRY
		DECLARE @msg VARCHAR(MAX)
		SET @msg = 'Updating: '
					+ ISNULL(CASE WHEN @Option = 'F' THEN 'isFoamed' ELSE 'isStirredWell' END, 'null') 
					+ ' On Process ID:' + ISNULL(@ProcessId,'null') + ' to ' + ISNULL(CASE WHEN @Enabled = 1 then 'Enabled' else 'Disabled' END ,'null')
		EXEC SYSTEM.AuditMsg @message = @msg, @moduleName = 'VTD Production', @currentUser = @CurrentUser
		print @msg
	END TRY
	BEGIN CATCH
		PRINT 'Error on message audit. ' + ERROR_MESSAGE()
	END CATCH

END


-------------------
-- SP Permissions
-------------------
EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [vtdp_w] -- The role
,@name = N'securedsp-VTD.UpdateFoamedStirredWell' -- the procedure
,@value = N'' 
GO