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

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

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

	USE [IHBoxSystem]
	GO
	CREATE ROLE morders_w AUTHORIZATION dbo 
	GO  
		USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'morders_w', 'admin'
	GO

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



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

	INSERT INTO [FrontEnd].[Routes]
			   ([Route]
			   ,[ModuleName]
			   ,[ModulePath]
			   ,[RequiredRole]
		   )
		 VALUES 
			   (
			   '!/melt-orders-assignments(/:heatName)(/)'
			   , 'melt-orders-assignments'
			   , 'pages/caster/melt-orders-assignments/'
			   , 'morders_r'           
			   )
			   SET @result_routeId = @@IDENTITY

	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			  )
		 VALUES
			   (
			   @parent
			   ,'Melt Orders Assignments'
			   ,@result_routeId 
			   ,1
			   ,1
			   ,1
			   ,'morders_r'
			   ,NULL
			 )
	GO


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

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [morders_r] -- The role
,@name = N'securedsp-MES.Get_MESvsSDIProductComparison' -- the procedure
,@value = N'' 
GO

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [morders_r] -- The role
,@name = N'securedsp-MES.GetAutocompleteMeltOrdersAndGrades' -- the procedure
,@value = N'' 
GO

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [morders_r] -- The role
,@name = N'securedsp-MES.Change_ProductMeltOrderAndGrade' -- the procedure
,@value = N'SendCurrentUser=TRUE' 
GO



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

	DECLARE @ModuleId INT

	INSERT INTO [SECURITY].[Modules]([Code],[Name])
	VALUES('morders_r','Melt Order Assignments')

	SET @ModuleId = @@IDENTITY


	INSERT INTO [SECURITY].ModuleActions(ModuleId,Code,[Name],[Role])
	VALUES (@ModuleId,'melt-orders-assignments-read','Read','morders_r')

		USE [IHBoxSystem]
	GO

	DECLARE @ModuleId INT

	INSERT INTO [SECURITY].[Modules]([Code],[Name])
	VALUES('morders_w','Melt Order Assignments')

	SET @ModuleId = @@IDENTITY


	INSERT INTO [SECURITY].ModuleActions(ModuleId,Code,[Name],[Role])
	VALUES (@ModuleId,'melt-orders-assignments-write','Write','morders_w')


----------------------------
------ Procedures ----------
----------------------------

USE [SDI_XFER]
GO
/****** Object:  StoredProcedure [MES].[Change_ProductMeltOrderAndGrade]    Script Date: 4/12/2024 09:18:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MES].[Change_ProductMeltOrderAndGrade]
	@MeltOrder VARCHAR(200) = null,
	@Grade VARCHAR(200) = null,
	@LayoutProducts XML = null,
	@ModifyMeltOrder bit = 0,
	@ModifyGrade bit = 0,	
	@CurrentUser VARCHAR(50) -- auto included by 4i
	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),
		-- Logging variable to store previous values
		@ChangeLog VARCHAR(MAX) = ''
	
	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'
	
	-------------------------------------
	---- HEAT STATUS Table
	--------------------------------------	
	DECLARE @HeatStatusMessages TABLE (MESSAGE_ID INT)

	-------------------------------------
	---- Products XML To Table
	--------------------------------------
	DECLARE @LayoutTProducts TABLE(
		[MESSAGE_ID] INT, 
		[PREVIOUS_MELT_ORDER] VARCHAR(200),
		[PREVIOUS_PIECE_ID] VARCHAR(200),
		[PREVIOUS_GRADE_ID] VARCHAR(200)
	)

	-- Insert MESSAGE_IDs and capture previous values
	INSERT INTO @LayoutTProducts(
		[MESSAGE_ID], 
		[PREVIOUS_MELT_ORDER], 
		[PREVIOUS_PIECE_ID], 
		[PREVIOUS_GRADE_ID]
	)
	SELECT
		[ProcessId] = T.Variable.value('@MESSAGE_ID','INT'),
		[PreviousMeltOrder] = (
			SELECT MELT_ORDER_ID 
			FROM [SDI_XFER].[SRD_SEND].[PRODUCT_DATA] 
			WHERE Id = T.Variable.value('@MESSAGE_ID','INT')
		),
		[PreviousPieceId] = (
			SELECT PIECE_ID 
			FROM [SDI_XFER].[SRD_SEND].[PRODUCT_DATA] 
			WHERE Id = T.Variable.value('@MESSAGE_ID','INT')
		),
		[PreviousGradeId] = (
			SELECT GRADE_ID 
			FROM [SDI_XFER].[SRD_SEND].[PRODUCT_DATA] 
			WHERE Id = T.Variable.value('@MESSAGE_ID','INT')
		)
		FROM
		@LayoutProducts.nodes('XMLRoot/Row') AS T(Variable)


	----------------------------------------------
	--- MELT ORDER AND/OR GRADE CHANGE LOGGING ---
	----------------------------------------------
	-- Construct a detailed logging string
	SELECT @ChangeLog = @ChangeLog + 
		'CurrentUser: ' + @CurrentUser + 
		', MESSAGE_ID: ' + CAST(MESSAGE_ID AS VARCHAR(10)) + 
		', Previous MeltOrder: ' + ISNULL(PREVIOUS_MELT_ORDER, 'NULL') + 
		CASE WHEN @ModifyMeltOrder = 1 THEN ', New MeltOrder: ' + @MeltOrder ELSE '' END + 
		CASE WHEN @ModifyGrade = 1 THEN ', New Grade: ' + @Grade ELSE '' END + 
		', Previous PieceId: ' + ISNULL(PREVIOUS_PIECE_ID, 'NULL') +
		', Previous GradeId: ' + ISNULL(PREVIOUS_GRADE_ID, 'NULL') + '; '
	FROM @LayoutTProducts
	
	-- Optional: You might want to log this to a separate table or log file
	PRINT 'Melt Order / Grade Change Log: ' + @ChangeLog


	--- Variables 
	declare @FirstProductMessageId int = (select top 1 MESSAGE_ID from @LayoutTProducts)
	declare @HeatName varchar(7) = (select TOP 1 HEAT_ID from [SDI_XFER].[SRD_SEND].[PRODUCT_DATA] where ID = @FirstProductMessageId )
	declare @HeatId int = (select top 1 Id from MES.MES.Heats where [Name] = @HeatName)		
	declare @MeltOrderId int = (select Id from MES.mes.MeltOrders where MeltOrderNumber = @MeltOrder)
	declare @GradeId int = (SELECT TOP 1 Id FROM MES.CAT.Grades WHERE [Name] = @Grade)


	-----------------
	-- Validations --
	-----------------

	-- Check Heat Name
	IF @HeatName is null or @HeatId is null
	BEGIN
		SET @ChangeLog = 'Failed SDI_XFER.[MES].[Change_ProductMeltOrderAndGrade] : The Heat Name is incorrect' + @ChangeLog

		EXEC MES.[SYSTEM].LogMsg @message = @ChangeLog
		,@procedure = @procName
		,@typeCode = 'ERROR'
		,@logError = 1

		RAISERROR(@ChangeLog , 16, 1);
		RETURN;
	END

	IF @ModifyGrade = 1 and @GradeId is null
	BEGIN
		SET @ChangeLog = 'Failed SDI_XFER.[MES].[Change_ProductMeltOrderAndGrade] : The Grade is incorrect' + @ChangeLog

		EXEC MES.[SYSTEM].LogMsg @message = @ChangeLog
		,@procedure = @procName
		,@typeCode = 'ERROR'
		,@logError = 1

		RAISERROR(@ChangeLog , 16, 1);
		RETURN;
	END

	IF @ModifyMeltOrder = 1 and @MeltOrderId is null
	BEGIN
		SET @ChangeLog = 'Failed SDI_XFER.[MES].[Change_ProductMeltOrderAndGrade] : The Melt Order is incorrect' + @ChangeLog

		EXEC MES.[SYSTEM].LogMsg @message = @ChangeLog
		,@procedure = @procName
		,@typeCode = 'ERROR'
		,@logError = 1

		RAISERROR(@ChangeLog , 16, 1);
		RETURN;

	END

	INSERT INTO @HeatStatusMessages(MESSAGE_ID)
	select MESSAGE_ID from [SDI_XFER].SRD_SEND.HEAT_STATUS  where HEAT_ID   = @HeatName
  
	--------------------------
	--- MELT ORDER CHANGE ---
	--------------------------
	IF @ModifyMeltOrder = 1 
	BEGIN
		BEGIN TRY
			-- Validate if @MeltOrder exists in MES.MeltOrders
			IF @MeltOrderId is null
			BEGIN
				SET @ChangeLog = 'Failed SDI_XFER.[MES].[Change_ProductMeltOrderAndGrade] : The provided Melt Order does not exist in MES.MeltOrders' + @ChangeLog

				EXEC MES.[SYSTEM].LogMsg @message = @ChangeLog
				,@procedure = @procName
				,@typeCode = 'ERROR'
				,@logError = 1

				RAISERROR(@ChangeLog , 16, 1);
				RETURN;
			END
					   

			-------------------------
			---- SDI_XFER DATABASE --
			-------------------------
			-- Update Product Data
			update [SDI_XFER].[SRD_SEND].[PRODUCT_DATA] 
					set MELT_ORDER_ID = @MeltOrder
			where Id in (select MESSAGE_ID from @LayoutTProducts)

			-- Update Heat Status
			UPDATE [SDI_XFER].SRD_SEND.HEAT_STATUS 
				set HEAT_ORDER_ID = @MeltOrder
			where MESSAGE_ID in ( select MESSAGE_ID from @HeatStatusMessages)

	
			-------------------
			-- MES DATABASE --
			-------------------
			-- Update MES.Heats
			UPDATE TOP(1) MES.MES.Heats
				set MeltOrderId = @MeltOrderId 
			WHERE Id = @HeatID 

			-- Update LAD.Heats
			UPDATE TOP(1) MES.LAD.Heats
				set MeltOrderNumber = @MeltOrder 
			WHERE HeatNumber = @HeatName 
			
		END TRY
		BEGIN CATCH 
			SET @ChangeLog = 'Failed SDI_XFER.[MES].[Change_ProductMeltOrderAndGrade] : Error when trying to update the Melt Order '  + @ChangeLog
			RAISERROR( @ChangeLog ,0,1) WITH NOWAIT
			
			EXEC MES.[SYSTEM].LogMsg @message = @ChangeLog
				,@procedure = @procName
				,@typeCode = 'ERROR'
				,@logError = 1

		END CATCH 
	END



	--------------------------
	--- GRADE CHANGE ---
	--------------------------
	IF @ModifyGrade = 1 
	BEGIN
		BEGIN TRY
			-------------------------
			---- SDI_XFER DATABASE --
			-------------------------
			-- Update Product Data		
			update [SDI_XFER].[SRD_SEND].[PRODUCT_DATA] 
					set GRADE_ID = @Grade
			where Id in (select MESSAGE_ID from @LayoutTProducts)

			
			-- Update Heat Status
			UPDATE [SDI_XFER].SRD_SEND.HEAT_STATUS 
				set INTERNAL_GRADE_ID = @Grade
			where MESSAGE_ID in ( select MESSAGE_ID from @HeatStatusMessages)

			
			-------------------
			-- MES DATABASE --
			-------------------
			-- Update MES.Heats
			UPDATE TOP(1) MES.MES.Heats
				set GradeId = @GradeId 
			WHERE Id = @HeatID 


		END TRY
		BEGIN CATCH
			SET @ChangeLog = 'Failed SDI_XFER.[MES].[Change_ProductMeltOrderAndGrade] : Error when trying to update the Grade'  + @ChangeLog
			RAISERROR( @ChangeLog ,0,1) WITH NOWAIT
			
			EXEC MES.[SYSTEM].LogMsg @message = @ChangeLog
				,@procedure = @procName
				,@typeCode = 'ERROR'
				,@logError = 1
		END CATCH
	

	END 


	-------------------------------------
	---- SEND TO L3 and Log Changes -----
	-------------------------------------

	IF @ModifyGrade = 1 or @ModifyMeltOrder = 1
	BEGIN
		-- 1. Update : [SDI_XFER].dbo.msg_header : Status = 1 to resend to L3 --> [SDI_XFER].[SRD_SEND].[PRODUCT_DATA]
		
		update [SDI_XFER].dbo.msg_header 
				set [STATUS] =1
		where Id in (select MESSAGE_ID from @LayoutTProducts)

		--2.  Update : [SDI_XFER].dbo.msg_header : Status = 1 to resend to L3 --> [SDI_XFER].SRD_SEND.HEAT_STATUS 
		update [SDI_XFER].dbo.msg_header 
				set [STATUS] =1
		where Id in ( select MESSAGE_ID from @HeatStatusMessages)

		-- calculate the correct Ids to send to L3
		-- 3. Log changes: 
		EXEC [MES].[SYSTEM].LogMsg @message = @ChangeLog,
                                @procedure = @procName,
                                @typeCode = 'DEBUG',
                                @logError = 0

	END


	


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


END



GO

----------------------------
------ Procedures ----------
----------------------------
----------------------------
------ Procedures ----------
----------------------------
----------------------------
------ Procedures ----------
----------------------------
----------------------------
------ Procedures ----------
----------------------------
----------------------------
------ Procedures ----------
----------------------------
----------------------------
------ Procedures ----------
----------------------------

USE [SDI_XFER]
GO
/****** Object:  StoredProcedure [MES].[Get_MESvsSDIProductComparison]    Script Date: 4/12/2024 12:27:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [MES].[Get_MESvsSDIProductComparison]
	@HeatName varchar(7)
as
BEGIN

	SELECT
		 pr.Id 
		,pr.ProcessId
		,[Product] = pr.HeatName + '' + CAST(pr.StrandId AS VARCHAR) + '' + 
                 (CASE 
                      WHEN pr.[Sequence] < 10 THEN '0' + CAST(pr.[Sequence] AS VARCHAR)
                      ELSE CAST(pr.[Sequence] AS VARCHAR)
                  END)
		,pr.HeatId
		,pr.HeatName
		,pr.StrandId
		,pr.[Sequence]
		,h.MeltOrderId
		,[MeltOrder] = mo.MeltOrderNumber
		,h.GradeId
		,[Grade] = g.[Name]
	FROM MES.CAS.Products pr
	LEFT JOIN MES.mes.heats h on h.Id = pr.HeatId
	LEFT JOIN MES.CAS.Processes p on p.Id = pr.ProcessId
	LEFT JOIN MES.mes.MeltOrders mo on mo.Id = h.MeltOrderId
	LEFT JOIN MES.cat.Grades g on g.Id = h.GradeId
	where p.HeatName=@HeatName
	order by pr.StrandId, pr.[Sequence]

	-- Products in SDI : See step 
	select 
		MESSAGE_ID = Id,
		PIECE_ID,
		HEAT_ID,
		STRAND,
		PIECE_INDEX,
		MELT_ORDER_ID,
		GRADE_ID

	from [SDI_XFER].[SRD_SEND].[PRODUCT_DATA] where HEAT_ID = @HeatName
	order by STRAND,PIECE_ID


END 

go

exec [MES].[Get_MESvsSDIProductComparison] 'B249280'
GO








/*************************************************************************************************************/
/*****************************************PROCEDURE**********************************************************/
/*************************************************************************************************************/











SET QUOTED_IDENTIFIER ON
GO
USE [MES]
GO
/****** Object:  StoredProcedure [MES].[GetAutocompleteMeltOrdersAndGrades]    Script Date: 2/12/2024 15:03:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [MES].[GetAutocompleteMeltOrdersAndGrades]
 	@MeltOrder VARCHAR(200) = null,
	@Grade VARCHAR(200) = null,
	@AutocompleteMeltOrders bit = 0,
	@AutocompleteGrades bit = 0,
	@SelectedMeltOrder VARCHAR(200) = NULL,
	@SelectedGrade VARCHAR(200) = NULL
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:'
		+ ', @MeltOrder: ' + ISNULL(CAST(@MeltOrder AS VARCHAR), '')

	DECLARE @SearchTextMeltOrderString VARCHAR(100)
	DECLARE @SearchTextGradeString VARCHAR(100)

	SET @SearchTextMeltOrderString = '%' + @MeltOrder + '%'
	SET @SearchTextGradeString = '%' + @Grade + '%'

	declare @MeltOrders table (MeltOrderId int, MeltOrderName varchar(200),isActive bit,LastUpdate datetime)
	declare @Grades table (GradeId int, GradeName varchar(200),isActive bit)

	-----------------------
	-- Begin Query
	-----------------------
	IF @SelectedMeltOrder is not null and @MeltOrder is null
	BEGIN
		insert into @MeltOrders
		select
			Id,
			lmo.MeltOrderNumber,
			lmo.IsActive,
			lmo.LastUpdate
		from MES.MeltOrders mmo
		LEFT JOIN LAD.MeltOrders lmo on lmo.MeltOrderNumber = mmo.MeltOrderNumber
		where mmo.MeltOrderNumber = @SelectedMeltOrder
	END
	ELSE
	BEGIN
		IF @SelectedMeltOrder is not null
		insert into @MeltOrders
		select
			Id,
			lmo.MeltOrderNumber,
			lmo.IsActive,
			lmo.LastUpdate
		from MES.MeltOrders mmo
		LEFT JOIN LAD.MeltOrders lmo on lmo.MeltOrderNumber = mmo.MeltOrderNumber
		where mmo.MeltOrderNumber = @SelectedMeltOrder

		insert into @MeltOrders
		select top 20
			Id,
			lmo.MeltOrderNumber,
			lmo.IsActive,
			lmo.LastUpdate
		from MES.MeltOrders mmo
		LEFT JOIN LAD.MeltOrders lmo on lmo.MeltOrderNumber = mmo.MeltOrderNumber
		where (@MeltOrder is null or lmo.MeltOrderNumber like @SearchTextMeltOrderString) 
		--and datediff(day,LastUpdate,GETUTCDATE()) <  30
		order by 
		lmo.isActive desc,lmo.MeltOrderNumber asc	

	END


	-----------------------
	-- GRADES
	-----------------------
	IF @SelectedGrade is not null and @Grade is null
	BEGIN
		insert into @Grades(GradeId,GradeName,isActive)
		select 
			Id, 
			[Name],
			[Active]
		from cat.Grades
		where [Name] = @SelectedGrade 
		order by id desc
	END
	ELSE
	BEGIN	
		if @SelectedGrade is not null
		insert into @Grades(GradeId,GradeName,isActive)
		select 
			Id, 
			[Name],
			[Active]
		from cat.Grades
		where [Name] = @SelectedGrade 
		order by id desc

		insert into @Grades(GradeId,GradeName,isActive)
		select 
			top 20
			Id, 
			[Name],
			[Active]
		from cat.Grades
		where (@Grade is null or [Name] like @SearchTextGradeString) 
		order by id desc

	END




	------------------
	---- RESPONSE ----
	------------------
	-- Melt Orders
	IF @AutocompleteMeltOrders = 1 
	BEGIN
		SELECT 
			[value] = MeltOrderName,
			[label] = MeltOrderName,
			MeltOrderId,
			LastUpdate
		FROM (
			SELECT DISTINCT 
				MeltOrderName, 
				MeltOrderId, 
				LastUpdate, 
				isActive
			FROM @MeltOrders
		) AS UniqueMeltOrders
		ORDER BY isActive DESC, LastUpdate DESC;
	END

	-- Grades
	IF @AutocompleteGrades = 1 
	BEGIN
		SELECT 
			[value] = GradeName,
			[label] = GradeName,
			GradeId
		FROM (
			SELECT DISTINCT 
				GradeName, 
				GradeId, 
				isActive
			FROM @Grades
		) AS UniqueGrades
		ORDER BY isActive DESC, GradeId DESC;
	END

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

	RETURN 0
END

go

exec  [MES].[GetAutocompleteMeltOrdersAndGrades] @AutocompleteMeltOrders = 1 ,@SelectedMeltOrder='M048596', @AutocompleteGrades=1, @SelectedGrade='A992-13V40', @Grade='GSA57250-C'