

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


-------------------
-- SCHEMA
-------------------

-------------------
-- TABLES
-------------------
USE [MES]
GO

/****** Object:  Table [LAB].[TypeSamples]    Script Date: 8/7/2023 9:55:23 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [LAB].[TypeSamples](
	[TypeSampleId] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL
) ON [PRIMARY]
GO


USE [MES]
GO

INSERT INTO [LAB].[TypeSamples] ([Name]) VALUES ('EAFFCE')
INSERT INTO [LAB].[TypeSamples] ([Name]) VALUES ('LMFLD')
INSERT INTO [LAB].[TypeSamples] ([Name]) VALUES ('CASTD')
INSERT INTO [LAB].[TypeSamples] ([Name]) VALUES ('BLMYD')
INSERT INTO [LAB].[TypeSamples] ([Name]) VALUES ('FINISH')
INSERT INTO [LAB].[TypeSamples] ([Name]) VALUES ('DRIFT')
INSERT INTO [LAB].[TypeSamples] ([Name]) VALUES ('VTDLD')
GO







---------------------------------------------------------------------------------------------
----	CATALOGS	 ----
-------------------------


USE [MES]
GO
/****** Object:  StoredProcedure [LAB].[GetChemistrySamplesCatalogs]    Script Date: 8/7/2023 9:51:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER PROCEDURE [LAB].[GetLabHeatReportEditCatalogs]
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.'


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

		-----------------------
	-- list of catalogs
	-----------------------
	select [Index]=1, [Name]='TYPESAMPLE'  
	-----------------------
	-- Ratings
	-----------------------
	 SELECT TypeSampleId, [Name]  FROM LAB.TypeSamples order by [Name] asc
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END

---------------------------------------------------------------------------------------------
----	UPDATE CHEMISTRY	 ----
-------------------------

USE [MES]
GO
/****** Object:  StoredProcedure [LAB].[UpdateChemistrySampleFromModal]    Script Date: 8/24/2023 2:56:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   PROCEDURE [LAB].[UpdateChemistrySampleFromModal]
	@Id INT,
	@TypeCode VARCHAR(100)= NULL,
	@SampleIdCode VARCHAR(100) = NULL,
	@Timestamp DATETIME,
	@currentUser VARCHAR(MAX) = NULL
AS
BEGIN
	DECLARE @OldTypeCode VARCHAR(100);
	DECLARE @OldSampleIdCode VARCHAR(100);
	DECLARE @OldTimestamp DATETIME;
	DECLARE @procName VARCHAR(300);
	DECLARE @logMsg VARCHAR(MAX);

	SELECT @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID);

	BEGIN TRY
		-- Get the old values before the update
		SELECT @OldTypeCode = TypeCode,
			   @OldSampleIdCode = SampleIdCode,
			   @OldTimestamp = Timestamp
		FROM LAB.ChemistrySamples -- Replace with your actual table name
		WHERE Id = @Id;

		-- Initial Log --
		SET @logMsg = 'Executing ' + @procName + ' for Id: ' + CAST(@Id AS VARCHAR) +
					  '. Old values: TypeCode=' + ISNULL(@OldTypeCode, 'NULL') +
					  ', SampleIdCode=' + ISNULL(@OldSampleIdCode, 'NULL') +
					  ', Timestamp=' + ISNULL(CONVERT(VARCHAR, @OldTimestamp, 120), 'NULL') +
					  ', CurrenUser=' + ISNULL(CONVERT(VARCHAR, @currentUser, 120), 'NULL');

		EXEC SYSTEM.LogMsg @message = @logMsg
						   ,@procedure = @procName
						   ,@typeCode = 'DEBUG'
						   ,@logError = 0;

		-- Update the record
		UPDATE LAB.ChemistrySamples -- Replace with your actual table name
		SET TypeCode = ISNULL(@TypeCode, @OldTypeCode),
			SampleIdCode = ISNULL(@SampleIdCode, @OldSampleIdCode),
			Timestamp = @Timestamp
		WHERE Id = @Id;

		-- Log the new values
		SET @logMsg = 'Executed ' + @procName + ' for Id: ' + CAST(@Id AS VARCHAR) +
					  '. New values: TypeCode=' + ISNULL(@TypeCode, 'NULL') +
					  ', SampleIdCode=' + ISNULL(@SampleIdCode, 'NULL') +
					  ', Timestamp=' + ISNULL(CONVERT(VARCHAR, @Timestamp, 120), 'NULL')+
					  ', CurrenUser=' + ISNULL(CONVERT(VARCHAR, @currentUser, 120), 'NULL');;

		EXEC SYSTEM.LogMsg @message = @logMsg
						   ,@procedure = @procName
						   ,@typeCode = 'DEBUG'
						   ,@logError = 0;
	END TRY
	BEGIN CATCH
		SELECT @logMsg = ERROR_MESSAGE();

		SET @logMsg = 'Failed ' + @logMsg;
		RAISERROR(@logMsg, 0, 1) WITH NOWAIT;

		EXEC SYSTEM.LogMsg @message = @logMsg
						   ,@procedure = @procName
						   ,@typeCode = 'ERROR'
						   ,@logError = 1;
	END CATCH;
END;



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

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