ALTER TABLE EAF.Processes 
ADD NCC_1			 VARCHAR (200) NULL,
	NCC_2 			 VARCHAR (200) NULL,
	NCC_3 			 VARCHAR (200) NULL,
	CaveInCounterPhase1 VARCHAR (200) NULL,
	CaveInCounterPhase2 VARCHAR (200) NULL,
	CaveInCounterPhase3 VARCHAR (200) NULL;

/*-------------------------------------------------------------------------------------------------------------------------------------------*/

USE [MES]
GO
/****** Object:  StoredProcedure [EAF].[UpdateProcessManual]    Script Date: 17 feb. 2020 10:46:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [EAF].[UpdateProcessManual]
	@id INT = NULL,
	
	@comments VARCHAR(200) = NULL,
    @gradeId INT = NULL,
	@initialGradeId INT = NULL,

	@arcLengthSettingId INT = NULL,
	@electrode1Changed BIT = NULL,
	@electrode2Changed BIT = NULL,
	@electrode3Changed BIT = NULL,
    @end DATETIME = NULL,
	@endUtc DATETIME = NULL,
    @endTap DATETIME = NULL,
    @endTapUtc DATETIME = NULL,
    @shipTimestamp DATETIME = NULL,
	@shipTimestampUtc DATETIME = NULL,
    @energy FLOAT = NULL,
    @heel FLOAT = NULL,
	@operatorId INT = NULL,
	@pourbackHeatName VARCHAR(50) = NULL,
	@pourbackWeight FLOAT = NULL,
    @powerOn INT = NULL,
	@regulationModeId INT = NULL,
	@start DATETIME = NULL,
	@startUtc DATETIME = NULL,
    @startTap DATETIME = NULL,
    @startTapUtc DATETIME = NULL,

    @ladleId INT = NULL,
	@steelWeight FLOAT = NULL,

	@timeZoneCode CHAR(10) = NULL,
	@currentUser VARCHAR(MAX) = NULL,

	@melter VARCHAR(200) = NULL,
	@burnerProgram VARCHAR(200) = NULL,
	@powerProgram VARCHAR(200) = NULL
AS
BEGIN
	DECLARE @result INT

	--Regex find: @([a-zA-Z0-9]+) .+ = NULL,
	--Regex replace: @$1 = @$1,
	EXEC @result = EAF.UpdateProcess
		@id = @id,
		@comments = @comments,
		@gradeId = @gradeId,
		@initialGradeId = @initialGradeId,
		@arcLengthSettingId = @arcLengthSettingId,
		@electrode1Changed = @electrode1Changed,
		@electrode2Changed = @electrode2Changed,
		@electrode3Changed = @electrode3Changed,
		@end = @end,
		@endUtc = @endUtc,
		@endTap = @endTap,
		@endTapUtc = @endTapUtc,
		@shipTimestamp = @shipTimestamp,
		@shipTimestampUtc = @shipTimestampUtc,
		@energy = @energy,
		@heel = @heel,
		@operatorId = @operatorId,
		@pourbackHeatName = @pourbackHeatName,
		@pourbackWeight = @pourbackWeight,
		@powerOn = @powerOn,
		@regulationModeId = @regulationModeId,
		@start = @start,
		@startUtc = @startUtc,
		@startTap = @startTap,
		@startTapUtc = @startTapUtc,

		@ladleId = @ladleId,
		@steelWeight = @steelWeight,
		@melter = @melter,
		@burnerProgram = @burnerProgram,
		@powerProgram  = @powerProgram, 
		@automatic = 0,
		@timeZoneCode = @timeZoneCode,
		@currentUser = @currentUser,
		@returnData = 1

	RETURN @result
END
GO


/*-------------------------------------------------------------------------------------------------------------------------------------------*/
USE [MES]
GO
/****** Object:  StoredProcedure [EAF].[UpdateProcess]    Script Date: 17 feb. 2020 10:49:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [EAF].[UpdateProcess]
	@id INT = NULL,

	@comments VARCHAR(200) = NULL,
    @gradeId INT = NULL,
	@initialGradeId INT = NULL,

	@arcLengthSettingId INT = NULL,
	@electrode1Changed BIT = NULL,
	@electrode2Changed BIT = NULL,
	@electrode3Changed BIT = NULL,
    @end DATETIME = NULL,
	@endUtc DATETIME = NULL,
    @endTap DATETIME = NULL,
    @endTapUtc DATETIME = NULL,
    @shipTimestamp DATETIME = NULL,
	@shipTimestampUtc DATETIME = NULL,
    @energy FLOAT = NULL,
    @heel FLOAT = NULL,
	@operatorId INT = NULL,
	@pourbackHeatName VARCHAR(50) = NULL,
	@pourbackWeight FLOAT = NULL,
    @powerOn INT = NULL,
	@regulationModeId INT = NULL,
	@start DATETIME = NULL,
	@startUtc DATETIME = NULL,
    @startTap DATETIME = NULL,
    @startTapUtc DATETIME = NULL,
    @ladleId INT = NULL,
	@melter VARCHAR(200) = NULL,
	@burnerProgram VARCHAR(200) = NULL,
	@powerProgram VARCHAR(200) = NULL, 
	@steelWeight FLOAT = NULL,
		
	@MODOperations INT = NULL,
	@tapChanges INT = NULL,
	@switchOperations INT = NULL,

	@automatic BIT = NULL,
	@nullVariables VARCHAR(MAX) = NULL,
	@nullVariablesSeparator VARCHAR(10) = NULL,
	@timeZoneCode CHAR(10) = NULL,
	@currentUser VARCHAR(MAX) = NULL,
	@returnData BIT = 1
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--This prefix is used on all the prints of the procedure to tabulate them depending
		--its nested level.
		@printPrefix VARCHAR(200),
		--The name of the procedure with its schema.
		@procName VARCHAR(300),
		--The result of the procedure. If all goes ok it will be 0, else it will contain the code
		--of the error that has been thrown.
		@result INT


	SELECT
		@procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID),
		@printPrefix = REPLICATE('    ', @@NESTLEVEL - 1),
		@result = 0
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ '@id: ' + ISNULL(CAST(@id AS VARCHAR), 'null')
		+ ', @comments: ' + ISNULL(@comments, 'null')
		+ ', @gradeId: ' + ISNULL(CAST(@gradeId AS VARCHAR), 'null')
		+ ', @initialGradeId: ' + ISNULL(CAST(@initialGradeId AS VARCHAR), 'null')
		+ ', @arcLengthSettingId: ' + ISNULL(CAST(@arcLengthSettingId AS VARCHAR), 'null')
		+ ', @electrode1Changed: ' + ISNULL(CAST(@electrode1Changed AS VARCHAR), 'null')
		+ ', @electrode2Changed: ' + ISNULL(CAST(@electrode2Changed AS VARCHAR), 'null')
		+ ', @electrode3Changed: ' + ISNULL(CAST(@electrode3Changed AS VARCHAR), 'null')
		+ ', @end: ' + ISNULL(CAST(@end AS VARCHAR), 'null')
		+ ', @endUtc: ' + ISNULL(CAST(@endUtc AS VARCHAR), 'null')
		+ ', @endTap: ' + ISNULL(CAST(@endTap AS VARCHAR), 'null')
		+ ', @endTapUtc: ' + ISNULL(CAST(@endTapUtc AS VARCHAR), 'null')
		+ ', @shipTimestamp: ' + ISNULL(CAST(@shipTimestamp AS VARCHAR), 'null')
		+ ', @shipTimestampUtc: ' + ISNULL(CAST(@shipTimestampUtc AS VARCHAR), 'null')
		+ ', @energy: ' + ISNULL(CAST(@energy AS VARCHAR), 'null')
		+ ', @heel: ' + ISNULL(CAST(@heel AS VARCHAR), 'null')
		+ ', @operatorId: ' + ISNULL(CAST(@operatorId AS VARCHAR), '')
		+ ', @pourbackHeatName: ' + ISNULL(CAST(@pourbackHeatName AS VARCHAR), 'null')
		+ ', @pourbackWeight: ' + ISNULL(CAST(@pourbackWeight AS VARCHAR), 'null')
		+ ', @powerOn: ' + ISNULL(CAST(@powerOn AS VARCHAR), 'null')
		+ ', @regulationModeId: ' + ISNULL(CAST(@regulationModeId AS VARCHAR), 'null')
		+ ', @start: ' + ISNULL(CAST(@start AS VARCHAR), 'null')
		+ ', @startUtc: ' + ISNULL(CAST(@startUtc AS VARCHAR), 'null')
		+ ', @startTap: ' + ISNULL(CAST(@startTap AS VARCHAR), 'null')
		+ ', @startTapUtc: ' + ISNULL(CAST(@startTapUtc AS VARCHAR), 'null')
		+ ', @ladleId: ' + ISNULL(CAST(@ladleId AS VARCHAR), 'null')
		+ ', @melter: ' + ISNULL(CAST(@melter AS VARCHAR), 'null')
		+ ', @burnerProgram: ' + ISNULL(CAST(@burnerProgram AS VARCHAR), 'null')
		+ ', @powerProgram : ' + ISNULL(CAST(@powerProgram  AS VARCHAR), 'null')
		+ ', @steelWeight: ' + ISNULL(CAST(@steelWeight AS VARCHAR), 'null')
		+ ', @MODOperations: ' + ISNULL(CAST(@MODOperations AS VARCHAR), 'null')
		+ ', @tapChanges: ' + ISNULL(CAST(@tapChanges AS VARCHAR), 'null')
		+ ', @switchOperations: ' + ISNULL(CAST(@switchOperations AS VARCHAR), 'null')
		+ ', @automatic: ' + ISNULL(CAST(@automatic AS VARCHAR), 'null')
		+ ', @nullVariables: ' + ISNULL(CAST(@nullVariables AS VARCHAR), 'null')
		+ ', @nullVariablesSeparator: ' + ISNULL(CAST(@nullVariablesSeparator AS VARCHAR), 'null')
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), 'null')
		+ ', @currentUser: ' + ISNULL(CAST(@currentUser AS VARCHAR), 'null')
		+ ', @returnData: ' + ISNULL(CAST(@returnData AS VARCHAR), 'null')


	-----------------------
	-- Transaction variables
	-----------------------
	DECLARE
		--This variable is used as a checkpoint for handle nested transactions.
		@rollbackPoint NCHAR(32),
		--Indicates if the transaction to be commited is owned by the current procedure.
		@ownTransaction BIT

	SET @ownTransaction = 0
		

	-----------------------
	-- Variables
	-----------------------
	DECLARE @heatId INT,
			@treatment INT,
			@module VARCHAR(20),
			@lav VARCHAR(MAX),
			@arcLengthSettingIdMA INT,
			@electrode1ChangedMA BIT,
			@electrode2ChangedMA BIT,
			@electrode3ChangedMA BIT,
			@endMA DATETIME,
			@endTapMA DATETIME,
			@shipTimestampMA DATETIME,
			@energyMA FLOAT,
			@heelMA FLOAT,
			@powerOnMA INT,
			@regulationModeIdMA INT,
			@startMA DATETIME,
			@startTapMA DATETIME,
			@arcLengthSettingIdCurVal INT,
			@commentsCurVal VARCHAR(200),
			@electrode1ChangedCurVal BIT,
			@electrode2ChangedCurVal BIT,
			@electrode3ChangedCurVal BIT,
			@endCurVal DATETIME,
			@endTapCurVal DATETIME,
			@shipTimestampCurVal DATETIME,
			@energyCurVal FLOAT,
			@gradeIdCurVal INT,
			@heelCurVal FLOAT,
			@initialGradeIdCurVal INT,
			@operatorIdCurVal INT,
			@powerOnCurVal INT,
			@pourbackHeatNameCurVal VARCHAR(50),
			@pourbackWeightCurVal FLOAT,
			@regulationModeIdCurVal INT,
			@startCurVal DATETIME,
			@startTapCurVal DATETIME,
			@shiftIdCurVal CHAR(1),
			@shiftStartHourCurVal INT,
			@shiftEndHourCurVal INT,
			@productionDateCurVal DATE,
			@crewIdCurVal CHAR(1),
			@arcLengthSettingIdVarName VARCHAR(50),
			@electrode1ChangedVarName VARCHAR(50),
			@electrode2ChangedVarName VARCHAR(50),
			@electrode3ChangedVarName VARCHAR(50),
			@endVarName VARCHAR(50),
			@endTapVarName VARCHAR(50),
			@shipTimestampVarName VARCHAR(50),
			@energyVarName VARCHAR(50),
			@heelVarName VARCHAR(50),
			@powerOnVarName VARCHAR(50),
			@regulationModeIdVarName VARCHAR(50),
			@startVarName VARCHAR(50),
			@startTapVarName VARCHAR(50),
			@shiftId CHAR(1),
			@shiftStartHour INT,
			@shiftEndHour INT,
			@productionDate DATE,
			@crewId CHAR(1)

	DECLARE @nullVariablesT SYSTEM.StringSplit


	-----------------------
	-- Parameters default
	-----------------------
	IF (@returnData IS NULL)
		SET @returnData = 1

	IF (@nullVariables IS NULL)
		SET @nullVariables = ''


	-----------------------
	-- Parameters validation
	-----------------------
	IF (@result = 0)
	BEGIN
		EXEC @result = EAF.ValidateProcess
			@id = @id
	END

	IF ((@result = 0) AND (@automatic IS NULL))
	BEGIN
		RAISERROR('Arguments %s can not be null.', 11, 1, '"Automatic"')
		SET @result = @@ERROR
	END


	-----------------------
	-- Set variables
	-----------------------
	IF (@result = 0)
	BEGIN
		SET @heatId = EAF.GetProcessHeatId(@id)

		IF (@startUtc IS NULL)
			SET @startUtc = IH.ToUtc(@start, @timeZoneCode)

		IF (@startTapUtc IS NULL)
			SET @startTapUtc = IH.ToUtc(@startTap, @timeZoneCode)

		IF (@endUtc IS NULL)
			SET @endUtc = IH.ToUtc(@end, @timeZoneCode)

		IF (@endTapUtc IS NULL)
			SET @endTapUtc = IH.ToUtc(@endTap, @timeZoneCode)

		IF (@shipTimestampUtc IS NULL)
			SET @shipTimestampUtc = IH.ToUtc(@shipTimestamp, @timeZoneCode)

		SELECT
			@module = 'EAF',
			@treatment = 1

		--Regex find: @(([a-zA-Z0-9]+)VarName) VARCHAR\(50\)
		--Regex replace: @$1 = '$2'
		SELECT
			@arcLengthSettingIdVarName = 'ARCLENGTHSETTING',
			@electrode1ChangedVarName = 'ELECTRODE1CHANGED',
			@electrode2ChangedVarName = 'ELECTRODE2CHANGED',
			@electrode3ChangedVarName = 'ELECTRODE3CHANGED',
			@endVarName = 'END',
			@endTapVarName = 'ENDTAP',
			@shipTimestampVarName = 'SHIPTIMESTAMP',
			@energyVarName = 'ENERGY',
			@heelVarName = 'HEEL',
			@powerOnVarName = 'POWERON',
			@regulationModeIdVarName = 'REGULATIONMODE',
			@startVarName = 'START',
			@startTapVarName = 'STARTTAP'

		--Regex find: @([a-zA-Z0-9]+)CurVal.+
		--Regex replace: @$1CurVal = $1,
		SELECT
			@arcLengthSettingIdCurVal = ArcLengthSettingId,
			@commentsCurVal = Comments,
			@electrode1ChangedCurVal = Electrode1Changed,
			@electrode2ChangedCurVal = Electrode2Changed,
			@electrode3ChangedCurVal = Electrode3Changed,
			@endCurVal = [End],
			@endTapCurVal = EndTap,
			@shipTimestampCurVal = ShipTimestamp,
			@energyCurVal = Energy,
			@gradeIdCurVal = GradeId,
			@heelCurVal = Heel,
			@initialGradeIdCurVal = InitialGradeId,
			@operatorIdCurVal = OperatorId,
			@pourbackHeatNameCurVal = PourbackHeatName,
			@pourbackWeightCurVal = PourbackWeight,
			@powerOnCurVal = PowerOn,
			@regulationModeIdCurVal = RegulationModeId,
			@startCurVal = Start,
			@startTapCurVal = StartTap,
			@shiftIdCurVal = ShiftId,
			@shiftStartHourCurVal = ShiftStartHour,
			@shiftEndHourCurVal = ShiftEndHour,
			@productionDateCurVal = ProductionDate,
			@crewIdCurVal = CrewId
		FROM EAF.Processes
		WHERE
			Id = @id

		INSERT INTO @nullVariablesT
		SELECT * FROM SYSTEM.GetTableFromString(@nullVariables, @nullVariablesSeparator)
	END


	-----------------------
	-- Begin Transaction
	-----------------------
	IF (@result = 0)
	BEGIN
		IF (@@TRANCOUNT > 0)
		BEGIN
			SET @rollbackPoint = REPLACE(CONVERT(NCHAR(36), NEWID()), N'-', N'')
			SAVE TRANSACTION @rollbackPoint
		END
		ELSE
		BEGIN
			SET @ownTransaction = 1
			BEGIN TRANSACTION
		END

		SET @result = @@ERROR


		-----------------------
		-- Update heat
		-----------------------
		IF (@result = 0)
		BEGIN
			EXEC @result = MES.UpdateHeat
				@id = @heatId, 
				@initialGradeId = @initialGradeId,
				@gradeId = @gradeId,
				@ladleId = @ladleId,
				@steelWeight = @steelWeight,
				@automatic = @automatic,
				@currentUser = @currentUser,
				@returnData = 0
		END


		-----------------------
		-- Set manual-automatic variables
		-----------------------
		IF (@result = 0)
		BEGIN
			IF (@automatic = 0)
			BEGIN
				IF(@arcLengthSettingId IS NOT NULL)
				BEGIN
					SET @arcLengthSettingIdMA = @arcLengthSettingId

					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @arcLengthSettingIdVarName) = 1)
					BEGIN
						SET @lav = @arcLengthSettingIdCurVal

						EXEC @result = MES.CreateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @arcLengthSettingIdVarName
					END
				END
				IF(@electrode1Changed IS NOT NULL)
				BEGIN
					SET @electrode1ChangedMA = @electrode1Changed

					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @electrode1ChangedVarName) = 1)
					BEGIN
						SET @lav = @electrode1ChangedCurVal

						EXEC @result = MES.CreateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @electrode1ChangedVarName
					END
				END
				IF(@electrode2Changed IS NOT NULL)
				BEGIN
					SET @electrode2ChangedMA = @electrode2Changed

					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @electrode2ChangedVarName) = 1)
					BEGIN
						SET @lav = @electrode2ChangedCurVal

						EXEC @result = MES.CreateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @electrode2ChangedVarName
					END
				END
				IF(@electrode3Changed IS NOT NULL)
				BEGIN
					SET @electrode3ChangedMA = @electrode3Changed

					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @electrode3ChangedVarName) = 1)
					BEGIN
						SET @lav = @electrode3ChangedCurVal

						EXEC @result = MES.CreateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @electrode3ChangedVarName
					END
				END
				IF(@endUtc IS NOT NULL)
				BEGIN
					SET @endMA = @endUtc

					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @endVarName) = 1)
					BEGIN
						SET @lav = CONVERT(VARCHAR, @endCurVal, 121)

						EXEC @result = MES.CreateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @endVarName
					END
				END
				IF(@endTapUtc IS NOT NULL)
				BEGIN
					SET @endTapMA = @endTapUtc

					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @endTapVarName) = 1)
					BEGIN
						SET @lav = CONVERT(VARCHAR, @endTapCurVal, 121)

						EXEC @result = MES.CreateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @endTapVarName
					END
				END
				IF(@shipTimestampUtc IS NOT NULL)
				BEGIN
					SET @shipTimestampMA = @shipTimestampUtc

					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @shipTimestampVarName) = 1)
					BEGIN
						SET @lav = CONVERT(VARCHAR, @shipTimestampCurVal, 121)

						EXEC @result = MES.CreateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @shipTimestampVarName
					END
				END
				IF(@energy IS NOT NULL)
				BEGIN
					SET @energyMA = @energy

					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @energyVarName) = 1)
					BEGIN
						SET @lav = @energyCurVal

						EXEC @result = MES.CreateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @energyVarName
					END
				END
				IF(@heel IS NOT NULL)
				BEGIN
					SET @heelMA = @heel

					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @heelVarName) = 1)
					BEGIN
						SET @lav = @heelCurVal

						EXEC @result = MES.CreateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @heelVarName
					END
				END
				IF(@powerOn IS NOT NULL)
				BEGIN
					SET @powerOnMA = @powerOn

					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @powerOnVarName) = 1)
					BEGIN
						SET @lav = @powerOnCurVal

						EXEC @result = MES.CreateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @powerOnVarName
					END
				END
				IF(@regulationModeId IS NOT NULL)
				BEGIN
					SET @regulationModeIdMA = @regulationModeId

					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @regulationModeIdVarName) = 1)
					BEGIN
						SET @lav = @regulationModeIdCurVal

						EXEC @result = MES.CreateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @regulationModeIdVarName
					END
				END
				IF(@startUtc IS NOT NULL)
				BEGIN
					SET @startMA = @startUtc

					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @startVarName) = 1)
					BEGIN
						SET @lav = CONVERT(VARCHAR, @startCurVal, 121)

						EXEC @result = MES.CreateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @startVarName
					END
				END
				IF(@startTapUtc IS NOT NULL)
				BEGIN
					SET @startTapMA = CONVERT(VARCHAR, @startTapUtc, 121)

					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @startTapVarName) = 1)
					BEGIN
						SET @lav = @startTapCurVal

						EXEC @result = MES.CreateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @startTapVarName
					END
				END
			END
			ELSE
			BEGIN
				IF (@arcLengthSettingId IS NOT NULL)
				BEGIN
					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @arcLengthSettingIdVarName) = 1)
					BEGIN
						SET @arcLengthSettingIdMA = @arcLengthSettingId
					END
					ELSE
					BEGIN
						SET @lav = @arcLengthSettingId

						EXEC @result = MES.UpdateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @arcLengthSettingIdVarName
					END
				END
				IF (@electrode1Changed IS NOT NULL)
				BEGIN
					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @electrode1ChangedVarName) = 1)
					BEGIN
						SET @electrode1ChangedMA = @electrode1Changed
					END
					ELSE
					BEGIN
						SET @lav = @electrode1Changed

						EXEC @result = MES.UpdateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @electrode1ChangedVarName
					END
				END
				IF (@electrode2Changed IS NOT NULL)
				BEGIN
					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @electrode2ChangedVarName) = 1)
					BEGIN
						SET @electrode2ChangedMA = @electrode2Changed
					END
					ELSE
					BEGIN
						SET @lav = @electrode2Changed

						EXEC @result = MES.UpdateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @electrode2ChangedVarName
					END
				END
				IF (@electrode3Changed IS NOT NULL)
				BEGIN
					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @electrode3ChangedVarName) = 1)
					BEGIN
						SET @electrode3ChangedMA = @electrode3Changed
					END
					ELSE
					BEGIN
						SET @lav = @electrode3Changed

						EXEC @result = MES.UpdateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @electrode3ChangedVarName
					END
				END
				IF (@endUtc IS NOT NULL)
				BEGIN
					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @endVarName) = 1)
					BEGIN
						SET @endMA = @endUtc
					END
					ELSE
					BEGIN
						SET @lav = @endUtc

						EXEC @result = MES.UpdateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @endVarName
					END
				END
				IF (@endTapUtc IS NOT NULL)
				BEGIN
					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @endTapVarName) = 1)
					BEGIN
						SET @endTapMA = @endTapUtc
					END
					ELSE
					BEGIN
						SET @lav = @endTapUtc

						EXEC @result = MES.UpdateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @endTapVarName
					END
				END
				IF (@shipTimestampUtc IS NOT NULL)
				BEGIN
					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @shipTimestampVarName) = 1)
					BEGIN
						SET @shipTimestampMA = @shipTimestampUtc
					END
					ELSE
					BEGIN
						SET @lav = @shipTimestampUtc

						EXEC @result = MES.UpdateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @shipTimestampVarName
					END
				END
				IF (@energy IS NOT NULL)
				BEGIN
					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @energyVarName) = 1)
					BEGIN
						SET @energyMA = @energy
					END
					ELSE
					BEGIN
						SET @lav = @energy

						EXEC @result = MES.UpdateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @energyVarName
					END
				END
				IF (@heel IS NOT NULL)
				BEGIN
					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @heelVarName) = 1)
					BEGIN
						SET @heelMA = @heel
					END
					ELSE
					BEGIN
						SET @lav = @heel

						EXEC @result = MES.UpdateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @heelVarName
					END
				END
				IF (@powerOn IS NOT NULL)
				BEGIN
					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @powerOnVarName) = 1)
					BEGIN
						SET @powerOnMA = @powerOn
					END
					ELSE
					BEGIN
						SET @lav = @powerOn

						EXEC @result = MES.UpdateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @powerOnVarName
					END
				END
				IF (@regulationModeId IS NOT NULL)
				BEGIN
					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @regulationModeIdVarName) = 1)
					BEGIN
						SET @regulationModeIdMA = @regulationModeId
					END
					ELSE
					BEGIN
						SET @lav = @regulationModeId

						EXEC @result = MES.UpdateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @regulationModeIdVarName
					END
				END
				IF (@startUtc IS NOT NULL)
				BEGIN
					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @startVarName) = 1)
					BEGIN
						SET @startMA = @startUtc
					END
					ELSE
					BEGIN
						SET @lav = @startUtc

						EXEC @result = MES.UpdateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @startVarName
					END
				END
				IF (@startTapUtc IS NOT NULL)
				BEGIN
					IF (MES.GetHeatVariableMode(@heatId, @treatment, @module, @startTapVarName) = 1)
					BEGIN
						SET @startTapMA = @startTapUtc
					END
					ELSE
					BEGIN
						SET @lav = @startTapUtc

						EXEC @result = MES.UpdateHeatManualVariable @heatId = @heatId, @treatment = @treatment, @module = @module, @lav = @lav, @currentUser = @currentUser,
							@name = @startTapVarName
					END
				END
			END			
		END


		-----------------------
		-- Update
		-----------------------
		IF (@result = 0)
		BEGIN
			IF (@startMA IS NOT NULL)
			BEGIN
				SELECT 
					TOP 1
					@shiftId = Id,
					@shiftStartHour = StartHour,
					@shiftEndHour = EndHour,
					@productionDate = ProductionDate,
					@crewId = CrewId
				FROM SYSTEM.GetShiftDataForDatetime(@startMA)
			END


			UPDATE EAF.Processes
			SET
				Comments = CASE WHEN (@comments IS NULL) THEN
									Comments
								ELSE
									@comments
							END,
				GradeId = CASE WHEN (@gradeId IS NULL) THEN
									GradeId
								ELSE
									@gradeId
							END,
				InitialGradeId = CASE WHEN (@initialGradeId IS NULL) THEN
										InitialGradeId
									ELSE
										@initialGradeId
								END,
				ArcLengthSettingId = CASE WHEN ((@arcLengthSettingIdMA IS NULL) AND (NOT EXISTS(SELECT * FROM @nullVariablesT WHERE Value = @arcLengthSettingIdVarName))) THEN
											ArcLengthSettingId
										ELSE
											@arcLengthSettingIdMA
									END,
				Electrode1Changed = CASE WHEN ((@electrode1ChangedMA IS NULL) AND (NOT EXISTS(SELECT * FROM @nullVariablesT WHERE Value = @electrode1ChangedVarName))) THEN
											Electrode1Changed
										ELSE
											@electrode1ChangedMA
									END,
				Electrode2Changed = CASE WHEN ((@electrode2ChangedMA IS NULL) AND (NOT EXISTS(SELECT * FROM @nullVariablesT WHERE Value = @electrode2ChangedVarName))) THEN
											Electrode2Changed
										ELSE
											@electrode2ChangedMA
									END,
				Electrode3Changed = CASE WHEN ((@electrode3ChangedMA IS NULL) AND (NOT EXISTS(SELECT * FROM @nullVariablesT WHERE Value = @electrode3ChangedVarName))) THEN
											Electrode3Changed
										ELSE
											@electrode3ChangedMA
									END,
				[End] = CASE WHEN ((@endMA IS NULL) AND (NOT EXISTS(SELECT * FROM @nullVariablesT WHERE Value = @endVarName))) THEN
								[End]
							ELSE
								@endMA
						END,
				EndTap = CASE WHEN ((@endTapMA IS NULL) AND (NOT EXISTS(SELECT * FROM @nullVariablesT WHERE Value = @endTapVarName))) THEN
								EndTap
							ELSE
								@endTapMA
						END,
				ShipTimestamp = CASE WHEN ((@shipTimestampMA IS NULL) AND (NOT EXISTS(SELECT * FROM @nullVariablesT WHERE Value = @shipTimestampVarName))) THEN
								ShipTimestamp
							ELSE
								@shipTimestampMA
						END,
				Energy = CASE WHEN ((@energyMA IS NULL) AND (NOT EXISTS(SELECT * FROM @nullVariablesT WHERE Value = @energyVarName))) THEN
								Energy
							ELSE
								@energyMA
						END,
				Heel = CASE WHEN ((@heelMA IS NULL) AND (NOT EXISTS(SELECT * FROM @nullVariablesT WHERE Value = @heelVarName))) THEN
								Heel
							ELSE
								@heelMA
						END,
				OperatorId = CASE WHEN (@operatorId IS NULL) THEN
									OperatorId
								ELSE
									@operatorId
							END,
				Melter = CASE WHEN (@melter IS NULL) THEN
						Melter
					ELSE
						@melter
				END,
				BurnerProgram = CASE WHEN (@burnerProgram IS NULL) THEN
						BurnerProgram
					ELSE
						@burnerProgram
				END,
					PowerProgram = CASE WHEN (@powerProgram IS NULL) THEN
						PowerProgram
					ELSE
						@powerProgram
				END,
				PourbackHeatName = CASE WHEN (@pourbackHeatName IS NULL) THEN
										PourbackHeatName
									ELSE
										@pourbackHeatName
								END,
				PourbackWeight = CASE WHEN (@pourbackWeight IS NULL) THEN
										PourbackWeight
									ELSE
										@pourbackWeight
								END,
				PowerOn = CASE WHEN ((@powerOnMA IS NULL) AND (NOT EXISTS(SELECT * FROM @nullVariablesT WHERE Value = @powerOnVarName))) THEN
								PowerOn
							ELSE
								@powerOnMA
						END,
				RegulationModeId = CASE WHEN ((@regulationModeIdMA IS NULL) AND (NOT EXISTS(SELECT * FROM @nullVariablesT WHERE Value = @regulationModeIdVarName))) THEN
											RegulationModeId
										ELSE
											@regulationModeIdMA
									END,
				-- Start field can not be null. So it is not necessary check if it is specified null variables parameter.
				Start = CASE WHEN (@startMA IS NULL) THEN
								Start
							ELSE
								@startMA
						END,
				StartTap = CASE WHEN ((@startTapMA IS NULL) AND (NOT EXISTS(SELECT * FROM @nullVariablesT WHERE Value = @startTapVarName))) THEN
									StartTap
								ELSE
									@startTapMA
							END,
				-- If start field changed, update also production data.
				ShiftId = CASE WHEN (@startMA IS NULL) THEN
									ShiftId
								ELSE
									@shiftId
							END,
				ShiftStartHour = CASE WHEN (@startMA IS NULL) THEN
									ShiftStartHour
								ELSE
									@shiftStartHour
							END,
				ShiftEndHour = CASE WHEN (@startMA IS NULL) THEN
									ShiftEndHour
								ELSE
									@shiftEndHour
							END,
				ProductionDate = CASE WHEN (@startMA IS NULL) THEN
									ProductionDate
								ELSE
									@productionDate
							END,
				CrewId = CASE WHEN (@startMA IS NULL) THEN
									CrewId
								ELSE
									@crewId
							END,

				MODOperations = @MODOperations,
				tapChanges = @tapChanges,
				switchOperations = @switchOperations
			WHERE
				Id = @id

			SET @result = @@ERROR
		END


		-----------------------
		-- End Transaction
		-----------------------
		--If @result = 0, attempt to commit the transaction. Else rollback it.
		IF (@result = 0)
		BEGIN
			IF (@ownTransaction = 1)
				COMMIT TRANSACTION
		END
		ELSE
		BEGIN
			IF ((@rollbackPoint IS NOT NULL) AND (XACT_STATE() = 1))
				ROLLBACK TRANSACTION @rollbackPoint;
			ELSE IF (@ownTransaction = 1)
				ROLLBACK TRANSACTION;
		END
	END


	-----------------------
	-- Audit
	-----------------------
	BEGIN TRY
		DECLARE @msg VARCHAR(MAX),
				@moduleName VARCHAR(200) 

		SELECT
			@moduleName = EAF.GetAuditModuleName(),
			@msg = ''


		SET @msg = MES.GetVariableChangeMessage(CAST(CAT.GetArcLengthSettingName(@arcLengthSettingId) AS VARCHAR), CAST(CAT.GetArcLengthSettingName(@arcLengthSettingIdCurVal) AS VARCHAR), 'Arc length setting', NULL, @arcLengthSettingIdVarName, @nullVariables)
			+ MES.GetVariableChangeMessage(CAST(@comments AS VARCHAR), CAST(@commentsCurVal AS VARCHAR), 'Comments', 0, NULL, NULL)
			+ MES.GetVariableChangeMessage(CAST(@crewId AS VARCHAR), CAST(@crewIdCurVal AS VARCHAR), 'Crew', NULL, @startVarName, @nullVariables)
			+ MES.GetVariableChangeMessage(CAST(@electrode1ChangedMA AS VARCHAR), CAST(@electrode1ChangedCurVal AS VARCHAR), 'Electrode 1 changed', NULL, @electrode1ChangedVarName, @nullVariables)
			+ MES.GetVariableChangeMessage(CAST(@electrode2ChangedMA AS VARCHAR), CAST(@electrode2ChangedCurVal AS VARCHAR), 'Electrode 2 changed', NULL, @electrode2ChangedVarName, @nullVariables)
			+ MES.GetVariableChangeMessage(CAST(@electrode3ChangedMA AS VARCHAR), CAST(@electrode3ChangedCurVal AS VARCHAR), 'Electrode 3 changed', NULL, @electrode3ChangedVarName, @nullVariables)
			+ MES.GetVariableChangeMessageTimestamp(CONVERT(VARCHAR, @endMA, 121), CONVERT(VARCHAR, @endCurVal, 121), 'End timestamp', NULL, @endVarName, @nullVariables)
			+ MES.GetVariableChangeMessageTimestamp(CONVERT(VARCHAR, @endTapMA, 121), CONVERT(VARCHAR, @endTapCurVal, 121), 'End tap timestamp', NULL, @endTapVarName, @nullVariables)
			+ MES.GetVariableChangeMessageTimestamp(CONVERT(VARCHAR, @shipTimestampMA, 121), CONVERT(VARCHAR, @shipTimestampCurVal, 121), 'Ship timestamp', NULL, @shipTimestampVarName, @nullVariables)
			+ MES.GetVariableChangeMessage(CAST(@energyMA AS VARCHAR), CAST(@energyCurVal AS VARCHAR), 'Energy', NULL, @energyVarName, @nullVariables)
			+ MES.GetVariableChangeMessage(CAST(CAT.GetGradeName(@gradeId) AS VARCHAR), CAST(CAT.GetGradeName(@gradeIdCurVal) AS VARCHAR), 'Grade', 0, NULL, NULL)
			+ MES.GetVariableChangeMessage(CAST(@heelMA AS VARCHAR), CAST(@heelCurVal AS VARCHAR), 'Heel', NULL, @heelVarName, @nullVariables)
			+ MES.GetVariableChangeMessage(CAST(CAT.GetGradeName(@initialGradeId) AS VARCHAR), CAST(CAT.GetGradeName(@initialGradeIdCurVal) AS VARCHAR), 'Initial grade', 0, NULL, NULL)
			+ MES.GetVariableChangeMessage(CAST(CAT.GetOperatorName(@operatorId) AS VARCHAR), CAST(CAT.GetOperatorName(@operatorIdCurVal) AS VARCHAR), 'Operator', 0, NULL, NULL)
			+ MES.GetVariableChangeMessage(CAST(@pourbackHeatName AS VARCHAR), CAST(@pourbackHeatNameCurVal AS VARCHAR), 'Pourback heat', 0, NULL, NULL)
			+ MES.GetVariableChangeMessage(CAST(@pourbackWeight AS VARCHAR), CAST(@pourbackWeightCurVal AS VARCHAR), 'Pourback weight', 0, NULL, NULL)
			+ MES.GetVariableChangeMessage(CAST(@powerOnMA AS VARCHAR), CAST(@powerOnCurVal AS VARCHAR), 'Power on', NULL, @powerOnVarName, @nullVariables)
			+ MES.GetVariableChangeMessage(SUBSTRING(CONVERT(VARCHAR, @productionDate, 121), 1, 10), SUBSTRING(CONVERT(VARCHAR, @productionDateCurVal, 121), 1, 10), 'Production date', NULL, @startVarName, @nullVariables)
			-- Regulation modes are different for each furnace. But this is temporaly.
			-- That is why items for furnace B are hardcoded. In the future both
			-- furnaces will have the same list of items.
			-- Revert all this stuff when both furnaces have the same items.
			--+ MES.GetVariableChangeMessage(CAST(CAT.GetRegulationModeName(@regulationModeIdMA) AS VARCHAR), CAST(CAT.GetRegulationModeName(@regulationModeIdCurVal) AS VARCHAR), 'Regulation mode', NULL, @regulationModeIdVarName, @nullVariables)
			+ MES.GetVariableChangeMessage(CAST(CAT.GetRegulationModeName(@regulationModeIdMA, EAF.GetProcessFurnaceCode(@id)) AS VARCHAR), CAST(CAT.GetRegulationModeName(@regulationModeIdCurVal, EAF.GetProcessFurnaceCode(@id)) AS VARCHAR), 'Regulation mode', NULL, @regulationModeIdVarName, @nullVariables)
			+ MES.GetVariableChangeMessage(CAST(@shiftId AS VARCHAR), CAST(@shiftIdCurVal AS VARCHAR), 'Shift', NULL, @startVarName, @nullVariables)
			+ MES.GetVariableChangeMessageTimestamp(CONVERT(VARCHAR, @startMA, 121), CONVERT(VARCHAR, @startCurVal, 121), 'Start timestamp', NULL, @startVarName, @nullVariables)
			+ MES.GetVariableChangeMessageTimestamp(CONVERT(VARCHAR, @startTapMA, 121), CONVERT(VARCHAR, @startTapCurVal, 121), 'Start tap timestamp', NULL, @startTapVarName, @nullVariables)


		IF (@msg <> '')
		BEGIN
			-- Remove last comma
			IF (@msg <> '') SET @msg = SUBSTRING(@msg, 1, LEN(@msg) - 1)

			SET @msg = 'Eaf process '
						-- First fields that make it unique.
						+ '"' + ISNULL(MES.GetHeatName(@heatId), 'null') + '" '
						+ CASE WHEN (@result = 0) THEN 'modified' ELSE 'failed to be modified' END
						-- Extra fields.
						+ ':' + @msg + '.'


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


	-----------------------
	-- Return current data
	-----------------------
	IF ((@result = 0) AND (@returnData = 1))
	BEGIN
		EXEC EAF.GetProcesses
			@id = @id
	END


	-----------------------
	-- Finish procedure
	-----------------------
	IF (@result = 0)
		PRINT @printPrefix + @finishEndMSG

	RETURN @result
END
GO

/*-------------------------------------------------------------------------------------------------------------------------------------------*/
USE [MES]
GO
/****** Object:  StoredProcedure [EAF].[GetProcesses]    Script Date: 19 feb. 2020 17:08:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [EAF].[GetProcesses]
	@id INT = NULL,
	@furnaceCode INT = NULL,
	@heatId INT = NULL,
	@search VARCHAR(MAX) = NULL,
	@start DATETIME = NULL,
	@end DATETIME = NULL,
	@heatName VARCHAR(50) = NULL,
	@crewId CHAR(1) = NULL,
	@shiftId INT = NULL,
	@showHighTemps BIT = NULL,
	@filterByProductionData BIT = NULL,
	@summarizedData BIT = NULL,
	@includeValidLimits BIT = 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
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:'
		+ '@id: ' + ISNULL(CAST(@id AS VARCHAR), '')
		+ ', @furnaceCode: ' + ISNULL(CAST(@furnaceCode AS VARCHAR), '')
		+ ', @heatId: ' + ISNULL(CAST(@heatId AS VARCHAR), '')
		+ ', @search: ' + ISNULL(CAST(@search AS VARCHAR), '')
		+ ', @start: ' + ISNULL(CAST(@start AS VARCHAR), '')
		+ ', @end: ' + ISNULL(CAST(@end AS VARCHAR), '')
		+ ', @filterByProductionData: ' + ISNULL(CAST(@filterByProductionData AS VARCHAR), '')
		+ ', @summarizedData: ' + ISNULL(CAST(@summarizedData AS VARCHAR), '')
		+ ', @includeValidLimits: ' + ISNULL(CAST(@includeValidLimits 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), '')


	-----------------------
	-- Variables
	-----------------------
	DECLARE @now DATETIME,
			@startUtc DATETIME,
			@endUtc DATETIME

	DECLARE @currentProcesses TABLE (Id INT)


	SET @now = GETUTCDATE()


	-----------------------
	-- Parameters default
	-----------------------
	IF (@id <= 0)
		SET @id = EAF.GetCurrentProcessId(@furnaceCode)

	IF (@id IS NULL and @heatId IS NOT NULL)
		SET @id = (SELECT TOP 1 Id from eaf.Processes where HeatId=@heatId)

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

	IF (@rowsToFetch > 0)
	BEGIN
		IF (@fromRow IS NULL)
			SET @fromRow = 1

		IF (@toRow IS NULL)
			SET @toRow = @fromRow + @rowsToFetch
	END

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

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

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

	IF (@filterByProductionData = 0)
	BEGIN
		IF (@start IS NOT NULL)
			SET @startUtc = IH.ToUtc(@start, @timeZoneCode)

		IF (@end IS NOT NULL)
			SET @endUtc = IH.ToUtc(@end, @timeZoneCode)
	END


	-----------------------
	-- Begin Query
	-----------------------

	-----------------------
	-- Current processes
	-----------------------
	INSERT INTO @currentProcesses
	SELECT
		process.Id
	FROM EAF.Processes process,
		(SELECT Timestamp = MAX(Start) FROM EAF.Processes WHERE EndTap IS NULL GROUP BY FurnaceCode) AS currentProcess
	WHERE
		process.Start = currentProcess.Timestamp
		

	-----------------------
	-- PHASES
	-----------------------
	CREATE TABLE #phases (
		ProcessId INT,
		BoreInTime INT,
		MeltTime INT,
		RefineTime INT
	);

	INSERT INTO #phases
           (ProcessId 
           ,BoreInTime  
           ,MeltTime 
           ,RefineTime) 
     VALUES
           ((@Id) 
           ,(select top 1 datediff(SS,[Start],isnull([End],getutcdate())) from eaf.phases z where z.PhaseId = 1 and z.ProcessId = @Id)
           ,(select top 1 datediff(SS,[Start],isnull([End],getutcdate())) from eaf.phases z where z.PhaseId = 2 and z.ProcessId = @Id)
           ,(select top 1 datediff(SS,[Start],isnull([End],getutcdate())) from eaf.phases z where z.PhaseId = 3 and z.ProcessId = @Id))


	-----------------------
	-- Processes
	-----------------------
	SELECT 
		--Not online fields
		process.Id,
		process.HeatId, 
		process.FurnaceCode, 
		process.InitialGradeId, 
		process.GradeId, 
		process.Start, 
		process.StartTap, 
		process.[End], 
		process.EndTap, 
		process.Comments, 
		process.Electrode1Changed, 
		process.Electrode2Changed, 
		process.Electrode3Changed, 
		process.Heel,
		process.OperatorId,
		--process.PourbackHeatId, 
		process.PourbackHeatName, 
		process.PourbackWeight, 
		process.PowerOff, 
		process.TappingTime, 
		process.ProductionDate, 
		process.ShiftId, 
		process.ShiftStartHour, 
		process.ShiftEndHour, 
		process.CrewId,
		heat.LadleId,
		--Datetime fields to string
		ProductionDateString = CONVERT(VARCHAR, process.ProductionDate, 120),
		StartString = CONVERT(VARCHAR, IH.ToLocal(process.Start, @timeZoneCode), 121),
		StartTapString = CONVERT(VARCHAR, IH.ToLocal(process.StartTap, @timeZoneCode), 121),
		EndString = CONVERT(VARCHAR, IH.ToLocal(process.[End], @timeZoneCode), 121),
		EndTapString = CONVERT(VARCHAR, IH.ToLocal(process.EndTap, @timeZoneCode), 121),
		PowerOnString = CONVERT(VARCHAR, DATEADD(second,process.PowerOn,0),108),
		PowerOffString = CONVERT(VARCHAR, DATEADD(second,process.PowerOff,0),108),
		--Catalog fields name
		ArcLengthSettingName = arcLengthSetting.Name,
		FurnaceName = furnace.Name,
		GradeName = grade.Name,
		HeatName = heat.Name,
		HeatStatusCode = heat.StatusCode,
		HeatStatusName = heatStatus.Name,
		InitialGradeName = initGrade.Name,
		LadleName = ladle.Name,
		OperatorName = operator.Name,
		--PourbackHeatName = pourbackHeat.Name,
		-- Regulation modes are different for each furnace. But this is temporaly.
		-- That is why items for furnace B are hardcoded. In the future both
		-- furnaces will have the same list of items.
		-- Revert all this stuff when both furnaces have the same items.
		--RegulationModeName = regulationMode.Name,
		ShiftName = shifts.[Name],
		RegulationModeName = CAT.GetRegulationModeName(process.RegulationModeId, process.FurnaceCode),

		--Online fields
		ArcLengthSettingId = CASE WHEN ((process.ArcLengthSettingId IS NOT NULL) OR (currentProcess.Id IS NULL)) THEN
									process.ArcLengthSettingId
								ELSE
									CAT.GetArcLengthSettingIdByCode(EAF.GetArcLengthSettingOnline(process.FurnaceCode))
							END,
		Energy = CASE WHEN ((process.Energy IS NOT NULL) OR (currentProcess.Id IS NULL)) THEN
						process.Energy
						ELSE
							EAF.GetEnergyOnline(process.FurnaceCode)
					END, 
		PowerOn = CASE WHEN ((process.PowerOn IS NOT NULL) OR (currentProcess.Id IS NULL)) THEN
							process.PowerOn
						ELSE
							EAF.GetPowerOnOnline(process.FurnaceCode) / 1000 --Convert from milliseconds to seconds
					END, 
		RegulationModeId = CASE WHEN ((process.RegulationModeId IS NOT NULL) OR (currentProcess.Id IS NULL)) THEN
									process.RegulationModeId
								ELSE
									CAT.GetRegulationModeIdByCode(EAF.GetRegulationModeOnline(process.FurnaceCode))
							END,
		SteelWeight = CASE WHEN ((heat.SteelWeight IS NOT NULL) OR (currentProcess.Id IS NULL)) THEN
								heat.SteelWeight
							ELSE
								MES.GetSteelWeightOnline(process.FurnaceCode)
						END, 
		

		--Other fields
		IsCurrentProcess = CAST(CASE WHEN (currentProcess.Id IS NOT NULL) THEN 1 ELSE 0 END AS BIT),
							
		--FurnaceHeatsCount = 54,
		PrevTap = (SELECT TOP 1 EndTap FROM EAF.Processes z WHERE z.[Start] < process.Start AND z.FurnaceCode = Process.FurnaceCode AND z.EndTap IS NOT NULL ORDER BY z.Start DESC),

		RowNum = ROW_NUMBER() OVER (ORDER BY process.Start DESC)


		,process.Melter
		,process.PowerProgram
		,process.BurnerProgram
		,ChargeWeight = (select sum(ScrapWeight) from EAF.Buckets z where z.ProcessId = @id) / 1000.0
		,ph.BoreInTime
		,ph.MeltTime 
		,ph.RefineTime
	INTO #records
	FROM EAF.Processes process
		JOIN MES.Heats heat ON heat.Id = process.HeatId
			LEFT JOIN CAT.HeatStatus heatStatus ON heatStatus.Code = heat.StatusCode
			LEFT JOIN CAT.Ladles ladle ON ladle.Id = heat.LadleId
		--LEFT JOIN MES.Heats pourbackHeat ON pourbackHeat.Id = process.PourbackHeatId
		LEFT JOIN Delays.Shifts shifts ON shifts.Id = process.ShiftId
		LEFT JOIN CAT.Furnaces furnace ON furnace.Code = process.FurnaceCode
		LEFT JOIN CAT.Grades grade ON grade.Id = process.GradeId
		LEFT JOIN CAT.Grades initGrade ON initGrade.Id = process.InitialGradeId
		LEFT JOIN CAT.Operators operator ON operator.Id = process.OperatorId
		LEFT JOIN CAT.ArcLengthSettings arcLengthSetting ON arcLengthSetting.Id = process.ArcLengthSettingId
		LEFT JOIN CAT.RegulationModes regulationMode ON regulationMode.Id = process.RegulationModeId
		LEFT JOIN @currentProcesses currentProcess ON currentProcess.Id = process.Id		
		LEFT JOIN #phases ph ON ph.ProcessId =process.Id
	WHERE
		(
			@id IS NOT NULL
			AND
			process.Id = @id
		)
		OR
		(
			@id IS NULL
			AND
			(
				(
					@furnaceCode IS NULL
					OR
					process.FurnaceCode = @furnaceCode
				)
				AND
				(
					@heatId IS NULL
					OR
					process.HeatId = @heatId
				)
				AND
				(
					@crewId IS NULL
					OR
					process.CrewId = @crewId
				)
				AND
				(
					@shiftId IS NULL
					OR
					process.ShiftId = @shiftId
				)
				AND
				(
					(
						@showHighTemps IS NULL
						OR
						@showHighTemps = 0
					)	
					OR
					(SELECT TOP 1 Temperature FROM EAF.Temperatures temp WHERE temp.ProcessId = process.Id ORDER BY Timestamp DESC) > EAF.GetTapTemperature(process.FurnaceCode)
				)
				AND
				(
					(
						@filterByProductionData = 1
						AND
						(
							@start IS NULL
							OR
							process.ProductionDate >= @start
						)
						AND
						(
							@end IS NULL
							OR
							process.ProductionDate <= @end
						)
					)
					OR
					(
						@filterByProductionData = 0
						AND
						(
							(
								(
									@startUtc IS NULL
									OR
									process.Start >= @startUtc
									OR
									process.[End] >= @startUtc
								)
								AND
								(
									@endUtc IS NULL
									OR
									process.Start <= @endUtc
									OR
									process.[End] <= @endUtc
								)
							)
							OR
							(
								@startUtc IS NOT NULL
								AND
								@endUtc IS NOT NULL
								AND
								@startUtc >= process.Start
								AND
								@endUtc <= process.[End]
							)
						)
					)
				)
				AND
				(
					@search IS NULL
					OR
					heat.Name LIKE '%' + @search + '%'
					OR
					CONVERT(VARCHAR, IH.ToLocal(process.Start, @timeZoneCode), 121) LIKE '%' + @search + '%'
				)
			)
		)


	SELECT 
		record.*,
		KwhTon =  record.Energy * 1000.0 / nullif(record.SteelWeight,0) ,
		MwhMin =  record.Energy / nullif(record.PowerOn,0) *60,
		TapToTap = CASE WHEN (record.EndTap IS NOT NULL) THEN
							ISNULL(DATEDIFF(
								SECOND,
								record.PrevTap,
								record.EndTap
							), 0)
						ELSE
							0
					END,
		
		TapToTapString =  CONVERT(VARCHAR, DATEADD(second,CASE WHEN (record.EndTap IS NOT NULL) THEN
							ISNULL(DATEDIFF(
								SECOND,
								record.PrevTap,
								record.EndTap
							), 0)
						ELSE
							0
					END,0),108),

		TapTemp = EAF.GetTapTemperature(record.FurnaceCode)
		
		

	FROM #records record
	WHERE
		@rowsToFetch = -1
		OR
		(
			record.RowNum >= @fromRow
			AND 
			record.RowNum <= @toRow
		)
	ORDER BY 
		record.RowNum


	-----------------------
	-- Manual variables
	-----------------------
	IF ((@id > 0) OR ((@heatId > 0)))
	BEGIN
		SELECT 
			manual.* 
		FROM MES.HeatManualVariables manual
			JOIN EAF.Processes process ON process.HeatId = manual.HeatId
		WHERE
			manual.Module IN ('EAF', 'Heat')
			AND
			(
				process.Id = @id
				OR
				process.HeatId = @heatId
			)
	END


	-----------------------
	-- Summarized data
	-----------------------
	IF (@summarizedData = 1)
	BEGIN
		SELECT 
			Id = record.Id,
			BucketsWeight = ISNULL(buckets.[Weight], 0),
			ConsumptionsInjectedCarbon = consumptions.InjectedCarbon,
			ConsumptionsCarbon = consumptions.InjectedCarbon,
			ConsumptionsInjectedLime = consumptions.InjectedLime,
			ConsumptionsNaturalGas = consumptions.NaturalGas,
			ConsumptionsOxygen = consumptions.Oxygen,
			ConsumptionsO2SS = consumptions.O2SS,
			FluxesHiCal = ISNULL((Select sum([Weight]) from Scrap.Charges c left join Scrap.ChargeFluxes cf on c.id = cf.ChargeId where c.HeatId = record.HeatId and FluxMaterialId=1),0),
			FluxesCarbon = ISNULL((Select sum([Weight]) from Scrap.Charges c left join Scrap.ChargeFluxes cf on c.id = cf.ChargeId where c.HeatId = record.HeatId and FluxMaterialId=2),0),
			FluxesProSlag = ISNULL((Select sum([Weight]) from Scrap.Charges c left join Scrap.ChargeFluxes cf on c.id = cf.ChargeId where c.HeatId = record.HeatId and FluxMaterialId=3),0),
			FluxesDolomite = ISNULL((Select sum([Weight]) from Scrap.Charges c left join Scrap.ChargeFluxes cf on c.id = cf.ChargeId where c.HeatId = record.HeatId and FluxMaterialId=4),0),
			ScrapRecipe = (
				SELECT TOP 1 recipes.[Name]
				FROM SCRAP.Charges charges 
				JOIN SCRAP.ScrapRecipes recipes ON recipes.Id = charges.ScrapRecipeId
				WHERE charges.HeatId = record.HeatId
			),
			LatestOxygen = (SELECT TOP 1 Oxygen FROM EAF.Temperatures temp WHERE temp.ProcessId = record.Id ORDER BY Timestamp DESC),
			LastTemperature = (SELECT TOP 1 Temperature FROM EAF.Temperatures temp WHERE temp.ProcessId = record.Id ORDER BY Timestamp DESC),
			-- In percentage
			Yield =  CASE WHEN ((buckets.[Weight] IS NOT NULL) AND (buckets.[Weight] <> 0)) THEN
							record.SteelWeight * 2000
							/ buckets.[Weight]
							* 100
						ELSE
							NULL
					END
	
		FROM #records record
			LEFT JOIN (SELECT
					ProcessId,
					[Weight] = SUM(ISNULL(ScrapWeight, 0)) 
				FROM EAF.Buckets
				WHERE
					ProcessId IN (SELECT Id FROM #records)
				GROUP BY
					ProcessId) AS buckets ON buckets.ProcessId = record.Id
			LEFT JOIN (SELECT
					ProcessId,
					InjectedCarbon = SUM(CASE WHEN (Code IN ('CARBON', 'LANCE_CARBON', 'SIDEWALL_CARBON')) THEN [Counter] ELSE 0 END),
					InjectedLime = SUM(CASE WHEN (Code IN ('INJECTED_LIME')) THEN [Counter] ELSE 0 END),
					NaturalGas = SUM(CASE WHEN (Code IN ('NATURAL_GAS_BURNER_1', 'NATURAL_GAS_BURNER_2', 'NATURAL_GAS_BURNER_3', 'NATURAL_GAS_BURNER_4', 'NATURAL_GAS_BURNER_5')) THEN [Counter] ELSE 0 END),
					Oxygen = SUM(CASE WHEN (Code IN ('LANCE_OXYGEN', 'OXYGEN_BURNER_1', 'OXYGEN_BURNER_2', 'OXYGEN_BURNER_3', 'OXYGEN_BURNER_4', 'OXYGEN_BURNER_5')) THEN [Counter] ELSE 0 END),
					O2SS = SUM(CASE WHEN (Code IN ('O2SS_BURNER_1', 'O2SS_BURNER_2', 'O2SS_BURNER_3', 'O2SS_BURNER_4', 'O2SS_BURNER_5')) THEN [Counter] ELSE 0 END)
				FROM EAF.Consumptions
				WHERE
					ProcessId IN (SELECT Id FROM #records)
					
				GROUP BY
					ProcessId) AS consumptions ON consumptions.ProcessId = record.Id			
		WHERE	
			@rowsToFetch = -1
			OR
			(
				record.RowNum >= @fromRow
				AND 
				record.RowNum <= @toRow
			)
	ORDER BY 
		record.RowNum
	END


	-----------------------
	-- Valid limits
	-----------------------
	IF (@includeValidLimits = 1)
	BEGIN
		SELECT 
			EnergyPerTon = 370,
			Oxygen = 140000,
			O2SS = 00,
			PowerOn = 32 * 60 -- In seconds
			
	END


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

	RETURN 0
END
GO
/*-------------------------------------------------------------------------------------------------------------------------------------------*/

USE [MES]
GO
/****** Object:  StoredProcedure [EAF].[GetPhases]    Script Date: 18 feb. 2020 09:35:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [EAF].[GetPhases] 
	@id INT = NULL,
	@furnaceCode INT = NULL,
	@processId INT = NULL,
	@heatId INT = NULL,

	@timeZoneCode CHAR(10) = NULL,

	--Not used for now but do not delete.
	@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
	-----------------------
	-- 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:'
		+ '@id: ' + ISNULL(CAST(@id AS VARCHAR), '')
		+ ', @furnaceCode: ' + ISNULL(CAST(@furnaceCode AS VARCHAR), '')
		+ ', @processId: ' + ISNULL(CAST(@processId AS VARCHAR), '')
		+ ', @heatId: ' + ISNULL(CAST(@heatId 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), '')


	-----------------------
	-- Variables
	-----------------------
	DECLARE @eafHeatDurationStandard INT,
			@nowUtc DATETIME

	SELECT
		@eafHeatDurationStandard = CAST(SYSTEM.GetConfigVariable('EAF_HEAT_DURATION_STANDARD_SECONDS') AS INT),
		@nowUtc = GETUTCDATE()


	-----------------------
	-- Parameters default
	-----------------------
	IF (@processId <= 0)
		SET @processId = EAF.GetCurrentProcessId(@furnaceCode)


	IF (@processId IS NULL and @heatId IS NOT NULL)
		SET @processId = (SELECT TOP 1 Id from eaf.Processes where HeatId=@heatId)

	if @ProcessId > 54026 -- NEW PHASE TRACKING
	BEGIN

		select PhaseId=1,Code =cast('Bore In' as varchar(50)) into #Codes
		insert into #Codes values (2,'Melting')
		insert into #Codes values (3,'Refining')
		insert into #Codes values (4,'Tapping')
			
		SELECT
		Id = ph.id
		,ProcessId = ph.ProcessId
		,Code = cast(c.Code as varchar(10))
		,[Order] = row_number() over (partition by ProcessId order by ph.Start)
		,Energy
		,Carbon
		,NaturalGas
		,O2SS
		,[Timestamp]
		,Duration = datediff(SS,Start,isnull([End],getutcdate()))
		,ProcessDuration = (select (datediff(SS,Start,isnull([End],getutcdate()))) from EAF.Processes z where z.id = @processId)

		FROM EAF.Phases ph
		left join #codes c on c.PhaseId = ph.PhaseId
		where ph.ProcessId = @ProcessId and [Order]=0


	END
	ELSE 
	BEGIN -- OLD PHASE TRACKING
	-----------------------
	-- Begin Query
	-----------------------
	SELECT
		*,
		ProcessDuration = CASE WHEN ((ProcessEnd IS NOT NULL) OR (DATEDIFF(SS, ProcessStart, @nowUtc) > @eafHeatDurationStandard)) THEN
							DATEDIFF(SS, ProcessStart, ISNULL(ProcessEnd, @nowUtc))
						ELSE
							@eafHeatDurationStandard
				END
	FROM (SELECT 
			phase.Id, 
			phase.ProcessId, 
			phase.Code, 
			phase.[Order], 
			Energy = phase.Energy - ISNULL(prevPhase.Energy, 0),
			Carbon = phase.Carbon - ISNULL(prevPhase.Carbon, 0),
			NaturalGas = phase.NaturalGas - ISNULL(prevPhase.NaturalGas, 0),
			Oxygen = phase.Oxygen - ISNULL(prevPhase.Oxygen, 0),
			O2SS = phase.O2SS - ISNULL(prevPhase.O2SS, 0),
			phase.[Timestamp],

			Duration = CASE WHEN (nextPhase.ProcessId IS NOT NULL) THEN
								DATEDIFF(SS, phase.[Timestamp], nextPhase.[Timestamp])
							WHEN (process.[End] IS NOT NULL) THEN
								DATEDIFF(SS, phase.[Timestamp], process.[End])
							WHEN (process.[End] IS NULL) THEN
								DATEDIFF(SS, phase.[Timestamp], @nowUtc)
						END,
			ProcessStart = process.Start,
			ProcessEnd = process.[End],
			TimestampString = CONVERT(VARCHAR, IH.ToLocal(phase.[Timestamp], @timeZoneCode), 121)
		FROM EAF.Phases phase
			LEFT JOIN EAF.Phases nextPhase ON nextPhase.ProcessId = phase.ProcessId AND nextPhase.[Order] = phase.[Order] + 1
			LEFT JOIN EAF.Phases prevPhase ON prevPhase.ProcessId = phase.ProcessId AND prevPhase.[Order] = phase.[Order] - 1
			JOIN EAF.Processes process ON process.Id = phase.ProcessId
		WHERE
			(
				@id IS NOT NULL
				AND
				phase.Id = @id
			)
			OR
			(
				@id IS NULL
				AND
				(
					(
						@processId IS NULL
						OR
						phase.ProcessId = @processId
					)
					AND
					(
						@heatId IS NULL
						OR
						process.HeatId = @heatId
					)
				)
			)) AS records
	ORDER BY
		records.[Order] ASC
	END

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

	RETURN 0
END


GO