USE [IHBoxSystem]
GO
CREATE ROLE mc_r AUTHORIZATION dbo 
GO  

USE [IHBoxSystem]
GO
CREATE ROLE mc_w AUTHORIZATION dbo 
GO  

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

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

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

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



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

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [mc_w] -- The role
,@name = N'securedsp-SCRAP.DeleteLinkMaterialCommodity' -- the procedure
,@value = N'SendCurrentUser=TRUE'

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [mc_w] -- The role
,@name = N'securedsp-SCRAP.UpsertLinkMaterialCommodity' -- the procedure
,@value = N'SendCurrentUser=TRUE'

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [mc_w] -- The role
,@name = N'securedsp-SCRAP.HandleMaterial' -- the procedure
,@value = N'SendCurrentUser=TRUE'

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

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

	INSERT INTO [FrontEnd].[Routes]
			   ([Route]
			   ,[ModuleName]
			   ,[ModulePath]
			   ,[RequiredRole]
			   ,[Group]
		   )
		 VALUES 
			   (
			   'materials-commodities'
			   , 'materials-commodities'
			   , 'custom-screens/materials-commodities/'
			   , 'mc_r' 
			   , 'configuration-dashboard'
			   )
			   SET @result_routeId = 9
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			   ,[Code]
			   ,[IHBoxModule]
			  )
		 VALUES
			   (
			   @parent
			   ,'Materials Commodities'
			   ,9 
			   ,1
			   ,1
			   ,1
			   ,'mc_r'
			   ,NULL
			   ,'materials-commodities'
			   ,'MES'
			 )
	GO

	
	 insert into [FrontEnd].MenuProperties(MenuId,[Name],[Value])
 values(
 (select top 1 Id from FrontEnd.Menu where [Name] = 'Materials Commodities'),('data-screenid'),('materials-commodities')
)

 insert into [FrontEnd].[MenuLinkParameters]([MenuLinkID],[Name],[Value])
 values(
 (select top 1 Id from FrontEnd.Menu where [Name] = 'Materials Commodities'),('section'),('materials-commodities')
)


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

DECLARE @ModuleId INT

INSERT INTO [SECURITY].[Modules]
		   ([Code]
		   ,[Name])
	 VALUES
		   ('materials_commodities'
		   ,'Materials Commodities')

		  SET @ModuleId = @@IDENTITY
		   		   
INSERT INTO [SECURITY].[ModuleActions]
		   ([ModuleId]
		   ,[Code]
		   ,[Name]
		   ,[Role])
	 VALUES
		   (@ModuleId
		   ,'read'
		   ,'Read'
		   ,'mc_r')
GO

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

CREATE TABLE SCRAP.Commodities (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    InventoryCode VARCHAR(4),
    CommodityName VARCHAR(50)
);

ALTER TABLE SCRAP.Commodities
ADD CONSTRAINT UC_CommodityName UNIQUE (CommodityName);

USE [MES]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [SCRAP].[JobImportCommodityData]
AS
BEGIN
    MERGE INTO SCRAP.Commodities AS target
    USING SCRAP.Scrap_Comodity AS source
    ON target.InventoryCode = source.InvCode
    WHEN NOT MATCHED THEN
    INSERT (InventoryCode, CommodityName)
    VALUES (source.InvCode, source.[Name]);
END;


--Changed CommodityId to be Foreign Key to CommodityId
CREATE TABLE SCRAP.MaterialToCommodities (
    ScrapMaterialName VARCHAR(100),
    CommodityId INT,
    [Percentage] INT,
	CONSTRAINT PK_MaterialToCommodities PRIMARY KEY (ScrapMaterialName,CommodityId)
);

ALTER TABLE SCRAP.MaterialToCommodities
ADD CONSTRAINT FK_MaterialToCommodities_CommodityId
FOREIGN KEY (CommodityId) REFERENCES SCRAP.Commodities(Id);
GO
--------------------------- SP -----------------------------------------
USE [MES]
GO
/****** Object:  StoredProcedure [SCRAP].[GetMaterialsCommodities]    Script Date: 12/13/2023 11:17:58 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [SCRAP].[GetMaterialsCommodities]
  
AS
BEGIN
	-- GET Materials Link with Commodities
    SELECT ScrapMaterialName, CommodityId, com.CommodityName , [Percentage] FROM SCRAP.MaterialToCommodities left join SCRAP.Commodities com on CommodityId = com.Id order by ScrapMaterialName

	-- GET Materials 
	SELECT [Id] = MAX(Id),[Name] = MAX([Name]), [Version] = MAX([Version]),
	[CreationTimeStamp] =  MAX(CreationTimestamp),[isAllowed] = (
        SELECT TOP 1 [isAllowed] 
        FROM Scrap.ScrapMaterials 
        WHERE Id = MAX(s.Id)
    ) FROM Scrap.ScrapMaterials s group by ([Name]) order by [Name] 

	-- GET Commodities
	SELECT * FROM SCRAP.Commodities order by [CommodityName]

END;



------------------------------------------------------------------------------------------
USE [MES]
GO
/****** Object:  StoredProcedure [SCRAP].[UpsertLinkMaterialCommodity]    Script Date: 12/13/2023 2:50:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [SCRAP].[UpsertLinkMaterialCommodity]
	@ScrapMaterialName VARCHAR(100),
	@CommodityId INT,
	@Percentage INT,
	@currentUser VARCHAR(MAX) = NULL
AS
BEGIN

	-------------------------
	--- Common Variables ----
	-------------------------
	
	declare @procName VARCHAR(300)
	declare @logMsg VARCHAR(MAX)
	SELECT @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	DECLARE @buf varchar(200) = ''
	SET NOCOUNT ON	

	declare @rowExists INT
	declare @sum FLOAT
	BEGIN TRY
			-- check if the element exists to make an update or upsert
			set @rowExists = (SELECT COUNT(*) from SCRAP.MaterialToCommodities WHERE ScrapMaterialName = @ScrapMaterialName AND CommodityId = @CommodityId)
			IF @rowExists > 0 
				BEGIN
					set @sum  = (Select SUM([Percentage]) FROM SCRAP.MaterialToCommodities where ScrapMaterialName = @ScrapMaterialName)
					update SCRAP.MaterialToCommodities set Percentage = (((100 - @Percentage) / @sum) * [Percentage]) where ScrapMaterialName = @ScrapMaterialName and CommodityId <> @CommodityId
					--UPDATE SCRAP.MaterialToCommodities SET [Percentage] = @Percentage WHERE ScrapMaterialName = @ScrapMaterialName AND CommodityId = @CommodityId
					BEGIN
					set @logMsg = 'Updated row on SCRAP.MaterialToCommodities with ScrapMaterialName: ' + @ScrapMaterialName
					+ ' , CommodityId: ' + cast(@CommodityId as varchar) + ' , and Percentage: ' + cast(@Percentage as varchar) 
					EXEC SYSTEM.LogMsg @message = @logMsg
					,@procedure = @procName
					,@typeCode = 'DEBUG'
					,@logError = 0
					END
				END

			set @sum  = (Select SUM([Percentage]) FROM SCRAP.MaterialToCommodities where ScrapMaterialName = @ScrapMaterialName)
			update SCRAP.MaterialToCommodities set Percentage = (((100 - @Percentage) / @sum) * Percentage) where ScrapMaterialName = @ScrapMaterialName and CommodityId <> @CommodityId
			INSERT INTO SCRAP.MaterialToCommodities VALUES (@ScrapMaterialName, @CommodityId, @Percentage)
			DECLARE @mtcInsertRC int = @@ROWCOUNT
			IF @mtcInsertRC > 0
				BEGIN
					set @logMsg = 'Inserted row on SCRAP.MaterialToCommodities with ScrapMaterialName: ' + @ScrapMaterialName
					+ ' , CommodityId: ' + cast(@CommodityId as varchar) + ' , and Percentage: ' + cast(@Percentage as varchar) 
					EXEC SYSTEM.LogMsg @message = @logMsg
					,@procedure = @procName
					,@typeCode = 'DEBUG'
					,@logError = 0
					END
			
				IF  @mtcInsertRC = 0
				BEGIN
				SET @logMsg = 'Error Inserting row on SCRAP.MaterialToCommodities'
				RAISERROR( @logMsg ,0,1) WITH NOWAIT
				END
	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;
GO



----------------------------------------------------------------------------------------------
USE [MES]
GO
/****** Object:  StoredProcedure [SCRAP].[DeleteLinkMaterialCommodity]    Script Date: 29/11/2023 10:02:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [SCRAP].[DeleteLinkMaterialCommodity]
    @ScrapMaterialName VARCHAR(100),
    @CommodityId INT,
	@currentUser VARCHAR(MAX) = NULL
AS
BEGIN
    DECLARE @procName VARCHAR(300)
    DECLARE @logMsg VARCHAR(MAX)
    SELECT @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
    DECLARE @buf varchar(200) = ''
    SET NOCOUNT ON	

    BEGIN TRY
        DELETE FROM SCRAP.MaterialToCommodities 
        WHERE ScrapMaterialName = @ScrapMaterialName AND CommodityId = @CommodityId;

        IF @@ROWCOUNT > 0
        BEGIN
			-- recalculate Percentages?
			declare @sum float = (Select SUM([Percentage]) FROM SCRAP.MaterialToCommodities where ScrapMaterialName = @ScrapMaterialName)
			update SCRAP.MaterialToCommodities set Percentage = ((100 / @sum) * [Percentage]) where ScrapMaterialName = @ScrapMaterialName and CommodityId <> @CommodityId
            SET @logMsg = 'Deleted row on SCRAP.MaterialToCommodities with ScrapMaterialName: ' + @ScrapMaterialName
            + ' and CommodityId: ' + CAST(@CommodityId AS VARCHAR)
            EXEC SYSTEM.LogMsg @message = @logMsg
                ,@procedure = @procName
                ,@typeCode = 'DEBUG'
                ,@logError = 0
        END
        ELSE
        BEGIN
            SET @logMsg = 'No rows were deleted on SCRAP.MaterialToCommodities with ScrapMaterialName: ' + @ScrapMaterialName
            + ' and CommodityId: ' + CAST(@CommodityId AS VARCHAR)
            RAISERROR(@logMsg, 0, 1) WITH NOWAIT
        END
    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;

GO

---------------------------------------------------------------------------
USE [MES]
GO
/****** Object:  StoredProcedure [SCRAP].[HandleMaterial]    Script Date: 12/13/2023 11:06:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [SCRAP].[HandleMaterial]
    @ScrapMaterialName VARCHAR(100),
    @Action VARCHAR(10),
	@currentUser VARCHAR(MAX) = NULL
AS
BEGIN
    DECLARE @logMsg NVARCHAR(MAX)
    DECLARE @procName NVARCHAR(100)
    SET @procName = OBJECT_NAME(@@PROCID)

    IF @Action = 'DELETE'
    BEGIN
        BEGIN
            -- Dissallow the existing record
            UPDATE SCRAP.ScrapMaterials SET [isAllowed] = 0 WHERE [Name] = @ScrapMaterialName

            SET @logMsg = 'Dissallow Material on SCRAP.ScrapMaterials with ScrapMaterialName: ' + @ScrapMaterialName
            EXEC SYSTEM.LogMsg @message = @logMsg,
                                @procedure = @procName,
                                @typeCode = 'DEBUG',
                                @logError = 0
        END
    END
    ELSE IF @Action = 'INSERT'
    BEGIN
        -- Insert a new record
        INSERT INTO SCRAP.ScrapMaterials([Name], CreationTimestamp, [Version], [Enabled], [isAllowed]) 
		VALUES (@ScrapMaterialName, 
		GETUTCDATE(), 
		ISNULL((SELECT MAX([Version]) + 1 FROM SCRAP.ScrapMaterials WHERE [Name] = @ScrapMaterialName), 1), 
		1,1)

        SET @logMsg = 'Inserted row on SCRAP.ScrapMaterials with ScrapMaterialName: ' + @ScrapMaterialName
        EXEC SYSTEM.LogMsg @message = @logMsg,
                            @procedure = @procName,
                            @typeCode = 'DEBUG',
                            @logError = 0
    END
	 ELSE IF @Action = 'ALLOW'
    BEGIN
        -- Allow a Material
        UPDATE SCRAP.ScrapMaterials SET [isAllowed] = 1 WHERE [Name] = @ScrapMaterialName AND [Enabled] = 1 AND [isAllowed] = 0

        SET @logMsg = 'Allowed Material SCRAP.ScrapMaterials with ScrapMaterialName: ' + @ScrapMaterialName
        EXEC SYSTEM.LogMsg @message = @logMsg,
                            @procedure = @procName,
                            @typeCode = 'DEBUG',
                            @logError = 0
    END

END;





----------------------------------------------------------
USE [MES]
GO
/****** Object:  StoredProcedure [SCRAP].[GetRecipesByMaterialId]    Script Date: 12/13/2023 3:56:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [SCRAP].[GetRecipesByMaterialId]

@MaterialId INT 
  
AS
BEGIN
	
    SELECT rec.[Name] From SCRAP.ScrapRecipes rec 
	left join SCRAP.ScrapRecipeLayers srl on rec.Id = srl.ScrapRecipeId
	Where  rec.[Enabled] = 1 and srl.ScrapMaterialId = @MaterialId group by rec.[Name]

END;

