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

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

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


	CREATE ROLE prepurchasedEnergy_readWrite AUTHORIZATION dbo 
	GO  

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

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


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

	INSERT INTO [FrontEnd].[Routes]
			   ([Route]
			   ,[ModuleName]
			   ,[ModulePath]
			   ,[RequiredRole]
		   )
		 VALUES 
			   (
			   '!/prepurchased-energy(/:start)(/:end)(/)'
			   , 'prepurchased-energy'
			   , 'custom-screens/prepurchased-energy/'
			   , 'prepurchasedEnergy_read'           
			   )
			   SET @result_routeId = @@IDENTITY
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			  )
		 VALUES
			   (
			   @parent
			   ,'Prepurchased Energy'
			   ,@result_routeId 
			   ,5
			   ,1
			   ,1
			   ,'prepurchasedEnergy_read'
			   ,NULL
			 )
	GO


-------------------
-- SP Permissions
-------------------
EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [prepurchasedEnergy_readWrite] -- The role
,@name = N'securedsp-PWR.UpsertPrepurchasedEnergy' -- the procedure
,@value = N'SendCurrentUser=TRUE' 
GO

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

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

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


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

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


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

	DECLARE @ModuleId INT

	INSERT INTO [SECURITY].[Modules]([Code],[Name])
	VALUES('prepurchased_energy','Prepurchased Energy')

	SET @ModuleId = @@IDENTITY

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


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

---------------------
-- Stored Procedures
---------------------
USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[AddProductManually]    Script Date: 15 nov. 2021 12:02:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [PWR].[UpsertPrepurchasedEnergy]
	@Year INT
	,@MWHLimit decimal(18,2)	
	,@Price decimal(18,2)
	,@currentUser VARCHAR(MAX) = 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)

	PRINT @procName + ' Parameters:'


	DECLARE  @logMsg varchar(max)



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

	DECLARE @PreviousMWHLimit decimal(18,2)
	DECLARE @PreviousPrice    decimal(18,2)

	SELECT
		@PreviousMWHLimit = MWHLimit,
		@PreviousPrice    = Price
	FROM PWR.catPrepurchaseContracts
	where [Year] = @Year


	update PWR.catPrepurchaseContracts
		set MWHLimit = @MWHLimit
		,Price = @Price
	where [Year] = @Year

	


	set @logMsg = 'The Entry was updated. New values: Year ' +  cast(@Year as varchar(max)) + ' MWHLimit: ' + cast(@MWHLimit as varchar(max))  + ' Price: ' + cast(@Price as varchar(max))
					+ ' Previous values: MWHLimit ' +  cast(@PreviousMWHLimit as varchar(max)) + ' Price: ' + cast(@PreviousPrice as varchar(max))  + ' : ' + cast(@Price as varchar(max))										
					+ ' Modifed by: '+ cast(isnull(@currentUser,'-') as varchar(max))
	EXEC SYSTEM.LogMsg @message = @logMsg
		,@procedure = @procName
		,@typeCode = 'DEBUG'
		,@logError = 1


	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG
	
	select [Year] = @Year
END

GO


USE [MES]
GO
/****** Object:  StoredProcedure [PWR].[GetPrepurchasedEnergy]    Script Date: 15 nov. 2021 12:52:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [PWR].[GetPrepurchasedEnergy]
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)

	PRINT @procName + ' Parameters:'


	DECLARE  @logMsg varchar(max)



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

	select * from PWR.catPrepurchaseContracts

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

END

GO