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

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

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


	CREATE ROLE powerconsumedforecast_readWrite AUTHORIZATION dbo 
	GO  

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

	USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'powerconsumedforecast_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
	select @parent=id from [FrontEnd].[Menu] where [Name] = 'Power' and Parent = @parent

	INSERT INTO [FrontEnd].[Routes]
			   ([Route]
			   ,[ModuleName]
			   ,[ModulePath]
			   ,[RequiredRole]
		   )
		 VALUES 
			   (
			   '!/power-consumed-forecast'
			   , 'power-consumed-forecast'
			   , 'custom-screens/power-consumed-forecast/'
			   , 'powerconsumedforecast_read'           
			   )
			   SET @result_routeId = @@IDENTITY
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			  )
		 VALUES
			   (
			   @parent
			   ,'PJM Power Forecast'
			   ,@result_routeId 
			   ,18
			   ,1
			   ,1
			   ,'powerconsumedforecast_read'
			   ,NULL
			 )
	GO


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

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

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

	DECLARE @ModuleId INT

	INSERT INTO [SECURITY].[Modules]([Code],[Name])
	VALUES('consumed_power_forecast','Consumed Power Forecast')

	SET @ModuleId = @@IDENTITY

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


	INSERT INTO [SECURITY].ModuleActions(ModuleId,Code,[Name],[Role])
	VALUES (@ModuleId,'read','Read','powerconsumedforecast_read')
-------------------
--	Table
-------------------
	USE [MES]
	GO
	CREATE TABLE [PWR].[ForeCastLoad](
	[ForecastLoadId] [int] IDENTITY(1,1) NOT NULL,
	[ForeCastArea] [varchar](100) NOT NULL,
	[EvaluatedDateTime] [datetime] NOT NULL,
	[StartDateTime] [datetime] NOT NULL,
	[EndDateTime] [datetime] NOT NULL,
	[LoadMw] [float] NOT NULL,
	 CONSTRAINT [PK_ForeCast] PRIMARY KEY CLUSTERED 
	(
		[ForecastLoadId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
	) ON [PRIMARY]
	GO

	CREATE TABLE [PWR].[EventConfig](
	[EventConfigId] [int] IDENTITY(1,1) NOT NULL,
	[Code] [varchar](50) NOT NULL,
	[Description] [varchar](100) NOT NULL,
	[WatchValue] [float] NOT NULL,
	[WarnValue] [float] NOT NULL,
	 CONSTRAINT [PK_EventConfig] PRIMARY KEY CLUSTERED 
	(
		[EventConfigId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
	) ON [PRIMARY]

	GO
	CREATE TABLE [PWR].[MeteredLoad](
	[MeteredLoadId] [int] IDENTITY(1,1) NOT NULL,
	[StartDateTime] [datetime] NOT NULL,
	[NercRegion] [varchar](100) NOT NULL,
	[MarketRegion] [varchar](100) NOT NULL,
	[Zone] [varchar](100) NOT NULL,
	[LoadArea] [varchar](100) NOT NULL,
	[Mw] [float] NOT NULL,
	[IsVerified] [bit] NOT NULL,
	 CONSTRAINT [PK_MeteredLoad] PRIMARY KEY CLUSTERED 
	(
		[MeteredLoadId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
	) ON [PRIMARY]
	GO

	CREATE TABLE [PWR].[EventEmail](
	[EventEmailId] [int] IDENTITY(1,1) NOT NULL,
	[ForecastLoadId] [int] NULL,
	[MeteredLoadId] [int] NULL,
	[Sent] [bit] NOT NULL,
	[Code] [varchar](50) NOT NULL,
	[CreatedDateTime] [datetime] NOT NULL,
	CONSTRAINT [PK_EventEmail] PRIMARY KEY CLUSTERED 
	(
		[EventEmailId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
	) ON [PRIMARY]
	GO




---------------------
-- Stored Procedures
---------------------
USE [MES]
GO


CREATE PROCEDURE [PWR].[GetConsumedPowerForecast]
	@StartDate DATE = NULL,
	@EndDate DATE = NULL,
	@AreaName VARCHAR(100) = 'AEP',
	@timeZoneCode CHAR(10) = 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:'
		+ ', @StartDate: ' + ISNULL(CAST(@StartDate AS VARCHAR), '')
		+ ', @EndDate: ' + ISNULL(CAST(@EndDate AS VARCHAR), '')
		+ ', @AreaName: ' + ISNULL(CAST(@AreaName AS VARCHAR), '')
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')

	

	------------------------------------
	-- Tables and Variables Declaration
	------------------------------------		
	DECLARE @forecast TABLE ([Date] varchar(10), [MWHLoad] float)
	
	DECLARE @t TABLE ([Date] varchar(10), [Hour] int, [MWHLimit] float)
	DECLARE @DateColumns TABLE ([Date] varchar(10), [RowNumber] int identity(1,1))
	DECLARE @missingDates TABLE ([Date] varchar(10), [Hour] int, [MWHLimit] float)

	DECLARE @hourRange TABLE ([Hour] int)
	DECLARE @calendar TABLE ([Date] varchar(10))

	DECLARE @End DATETIME , @Start DATETIME, @StartHourly DATETIME

	DECLARE @WarnForecast FLOAT, @WatchForecast FLOAT;
	DECLARE @WarnHourly FLOAT, @WatchHourly FLOAT;
		

	-----------------------
	-- Begin Query
	-----------------------
	IF (@timeZoneCode IS NULL)
			SET @timeZoneCode = 'EDTIND'
	IF @StartDate IS NULL
			SET @StartDate = GETDATE()	
	IF @EndDate IS NULL
			SET @EndDate = DATEADD(DAY,7,@StartDate)

	SET @End =IH.ToUTC(@EndDate,@timeZoneCode);
	SET @Start =IH.ToUTC(@StartDate,@timeZoneCode);
	SET @StartHourly = DATEADD(DAY,-7,@StartDate);
	SET @StartHourly =IH.ToUTC(@StartHourly,@timeZoneCode);

	SELECT @WarnForecast = WarnValue,@WatchForecast = WatchValue FROM PWR.EventConfig WHERE UPPER(Code) = 'FORECAST';
	SELECT @WarnHourly = WarnValue,@WatchHourly = WatchValue FROM PWR.EventConfig WHERE UPPER(Code) = 'HOURLY';

	SELECT 		
		[StartDateTime]
		,[LoadMw]
		,@WatchForecast AS [Watch]
		,@WarnForecast AS [Warn]
	FROM 
		[PWR].[ForeCastLoad]
	WHERE 
		[StartDateTime] BETWEEN @Start AND @End
	ORDER BY 
		[StartDateTime] ASC

	SELECT 		
		[StartDateTime]
		,SUM(Mw) AS Mw
		,@WatchHourly AS [Watch]
		,@WarnHourly AS [Warn]
	FROM 
		[PWR].[MeteredLoad]
	WHERE 
		[StartDateTime] BETWEEN @StartHourly AND @Start
		AND [Zone] = @AreaName
		--AND [LoadArea] = 'AEPAPT'
	GROUP BY 
		[StartDateTime]
	ORDER BY 
		[StartDateTime] ASC



END

GO

GO
ALTER PROCEDURE [PWR].[UpsertForeCastLoad]
	@EvaluatedDate DATETIME,
	@StartDate DATETIME,
	@EndDate DATETIME,
	@AreaName VARCHAR(100),
	@LoadMw FLOAT
AS
BEGIN
	DECLARE @id INT;
	DECLARE @watch FLOAT, @warn FLOAT, @loadValue FLOAT;
	SELECT @watch = WatchValue, @warn = WarnValue FROM PWR.EventConfig WHERE Code = 'FORECAST';

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SELECT @id= ForecastLoadId, @loadValue = LoadMw FROM PWR.ForeCastLoad WHERE StartDateTime = @StartDate AND UPPER(ForecastArea) = UPPER(@AreaName) ;
	IF(@id = 0 OR @id IS NULL)
	BEGIN
		INSERT INTO PWR.ForeCastLoad(EvaluatedDateTime,StartDateTime,EndDateTime,ForecastArea,LoadMw) 
		VALUES (@EvaluatedDate,@StartDate,@EndDate,@AreaName,@LoadMw);
		SELECT  @id = @@IDENTITY
		IF @LoadMw >= @watch AND @LoadMw <@warn
			INSERT INTO PWR.EventEmail(ForecastLoadId,[Sent],Code,CreatedDateTime) VALUES(@id,0,'WATCH', GETUTCDATE());	
		IF @LoadMw >= @warn
			INSERT INTO PWR.EventEmail(ForecastLoadId,[Sent],Code,CreatedDateTime) VALUES(@id,0,'WARN', GETUTCDATE());
	END
	ELSE
	BEGIN
		UPDATE PWR.ForeCastLoad SET EvaluatedDateTime = @EvaluatedDate,LoadMw = @LoadMw WHERE ForecastLoadId =@id;
		IF @LoadMw >= @watch AND @LoadMw <@warn AND @LoadMw <> @loadValue
			INSERT INTO PWR.EventEmail(ForecastLoadId,[Sent],Code,CreatedDateTime) VALUES(@id,0,'WATCH', GETUTCDATE())	
		IF @LoadMw >= @warn AND @LoadMw <> @loadValue
			INSERT INTO PWR.EventEmail(ForecastLoadId,[Sent],Code,CreatedDateTime) VALUES(@id,0,'WARN', GETUTCDATE())
	END
	SET NOCOUNT ON;
END
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [PWR].[UpsertMeteredLoad]	
	@StartDate DATETIME,
	@NercRegion VARCHAR(100),
	@MarketRegion VARCHAR(100),
	@Zone VARCHAR(100),
	@LoadArea VARCHAR(100),
	@Mw FLOAT,
	@IsVerified BIT
AS
BEGIN
	DECLARE @id INT;
	DECLARE @watch FLOAT, @warn FLOAT, @loadValue FLOAT;
	SELECT @watch = WatchValue, @warn = WarnValue FROM PWR.EventConfig WHERE Code = 'HOURLY';

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SELECT 
		@id= MeteredLoadId ,
		@loadValue = Mw
	FROM 
		PWR.MeteredLoad 
	WHERE 
		StartDateTime = @StartDate 
		AND UPPER(NercRegion) = UPPER(@NercRegion) 
		AND UPPER(MarketRegion) = UPPER(@MarketRegion) 
		AND UPPER([Zone]) = UPPER(@Zone) 
		AND UPPER(LoadArea) = UPPER(@LoadArea) 
	;
	IF(@id = 0 OR @id IS NULL)
	BEGIN
		INSERT INTO PWR.MeteredLoad(StartDateTime,NercRegion,MarketRegion,[Zone],LoadArea,Mw,IsVerified) 
		VALUES (@StartDate,@NercRegion,@MarketRegion,@Zone,@LoadArea,@Mw,@IsVerified);
		SELECT  @id = @@IDENTITY
		IF @Mw >= @watch AND @Mw <@warn
			INSERT INTO PWR.EventEmail(MeteredLoadId,[Sent],Code,CreatedDateTime) VALUES(@id,0,'WATCH', GETUTCDATE());	
		IF @Mw >= @warn
			INSERT INTO PWR.EventEmail(MeteredLoadId,[Sent],Code,CreatedDateTime) VALUES(@id,0,'WARN', GETUTCDATE());
	END
	ELSE
	BEGIN
		UPDATE 
			PWR.MeteredLoad 
		SET 
			IsVerified = @IsVerified,
			Mw = @Mw 
		WHERE 
			MeteredLoadId =@id;
		IF @Mw >= @watch AND @Mw <@warn AND @Mw <> @loadValue
			INSERT INTO PWR.EventEmail(MeteredLoadId,[Sent],Code,CreatedDateTime) VALUES(@id,0,'WATCH', GETUTCDATE())	
		IF @Mw >= @warn  AND @Mw <> @loadValue
			INSERT INTO PWR.EventEmail(MeteredLoadId,[Sent],Code,CreatedDateTime) VALUES(@id,0,'WARN', GETUTCDATE())
	END
	SET NOCOUNT ON;
END
GO


CREATE PROCEDURE [PWR].[SendEventEmail] 
	
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	DECLARE @recipients VARCHAR(MAX) = 'jsagasti@dexteel.com;dsilvestri@dexteel.com;fcordova@dexteel.com';
	DECLARE @subject VARCHAR(MAX) = 'MES - PJM Email Alerts';
	DECLARE @mailbody VARCHAR(MAX) = ''
    DECLARE @procName VARCHAR(300)
	DECLARE @WarnForecast FLOAT, @WatchForecast FLOAT;
	DECLARE @WarnHourly FLOAT, @WatchHourly FLOAT;

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

    PRINT @mailbody;

    --------------------------
    -- Email Variables Declaration
    --------------------------

    DECLARE @result INT;
    DECLARE @profileName VARCHAR(10) = 'MES';
    DECLARE @fromAddress VARCHAR(100) = 'srd-mes@steeldynamics.com';    
    DECLARE @bodyFormat VARCHAR(4) = 'HTML';
    DECLARE @msg VARCHAR(MAX) = 'Generic Email';    
    	----------------------
	--- DROP TEMP TABLES
	----------------------
	IF OBJECT_ID('tempdb..##ttcatF') IS NOT NULL DROP TABLE ##ttcatF
	IF OBJECT_ID('tempdb..##tttcatF') IS NOT NULL DROP TABLE ##tttcatF
	------------------------------
	--- All Values Into Temp Table
	-------------------------------
	SELECT @WarnForecast = WarnValue,@WatchForecast = WatchValue FROM PWR.EventConfig WHERE UPPER(Code) = 'FORECAST';
	SELECT @WarnHourly = WarnValue,@WatchHourly = WatchValue FROM PWR.EventConfig WHERE UPPER(Code) = 'HOURLY';



	SELECT
		temp.EventEmailId,
		CASE WHEN temp.Sort = 0 THEN 'FORECAST LOAD' ELSE 'METERED HOURLY LOAD' END AS [Description],
		temp.Code AS [Type],
		temp.StartDateTime AS [Date],
		temp.[Value] AS MW,
		temp.WatchValue AS [Watch Value],
		temp.WarnValue AS [Warn Value]
	INTO  ##ttcatF
	FROM
	(
	SELECT 		
		0 AS Sort,
		e.EventEmailId,
		e.Code,		
		IH.ToLocal(f.StartDateTime,'EDTIND') AS StartDateTime,
		f.LoadMw AS [Value],
		@WatchForecast AS WatchValue,
		@WarnForecast AS WarnValue
	FROM 
		[PWR].[EventEmail]  e
	INNER JOIN PWR.ForeCastLoad f ON e.ForecastLoadId = f.ForecastLoadId
	WHERE 
		e.[Sent] = 0	
	UNION ALL
		SELECT 		
		1 AS Sort,
		e.EventEmailId,
		e.Code,		
		IH.ToLocal(f.StartDateTime,'EDTIND') AS StartDateTime,
		f.Mw AS [Value],
		@WatchHourly AS WatchValue,
		@WarnHourly AS WarnValue
	FROM 
		[PWR].[EventEmail]  e
	INNER JOIN PWR.MeteredLoad f ON e.MeteredLoadId = f.MeteredLoadId
	WHERE 
		e.[Sent] = 0
	) AS temp
	ORDER BY
		temp.Sort, temp.Code, temp.StartDateTime

	

	select temp2.[Description],temp2.[Type], temp2.[Date],temp2.MW, temp2.[Watch Value],temp2.[Warn Value]  into ##tttcatF from ##ttcatF AS temp2
	select * from ##tttcatF

	EXEC [SYSTEM].[SqlTableToHtml] @TABLENAME = ##tttcatF,
                                   @OUTPUT = @mailbody OUT,
                                   @TD_STYLE = 'style="background-color:#EEE;border:1px solid #888;padding:4px"',
                                   @HDR_STYLE = 'style="background-color:#AAA;border:1px solid #888"';


    PRINT @mailbody;


    --------------------------------------------------------------------------------------------------------------------------------------
    ---------------------------------------------------------- EMAIL -----------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------------------------


    IF @mailbody IS NOT NULL
        SET @mailbody = '<html><body>' + @mailbody + '</body></html>';

    SELECT [MailBody] = @mailbody;
    ----------------
    -- Send Email
    ----------------
	--set @result = 0
	IF (SELECT COUNT(1) FROM ##tttcatF) > 0
    EXEC @result =  msdb.dbo.sp_send_dbmail @profile_name = @profileName,
                                           @recipients = @recipients,
                                           @from_address = @fromAddress,
                                           @subject = @subject,
                                           @body = @mailbody,
                                           @body_format = @bodyFormat;

    EXEC SYSTEM.LogMsg @procedure = @procName, @message = @msg;


    ----------------
    -- Email failed
    ----------------
    IF @result = 1
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail @profile_name = 'MES',
                                     @recipients = 'dsilvestri@dexteel.com;jsagasti@dexteel.com;fcordova@dexteel.com',
                                     @subject = 'Generic Email FAILED',
                                     @body = 'Data generation has been failed';
    END;
	IF @result = 0
	BEGIN	
		UPDATE e
		SET
			e.[Sent] = 1
		FROM
			PWR.EventEmail  e
		INNER JOIN ##ttcatF f ON e.EventEmailId = f.EventEmailId
	END
END;
GO

USE [MES]
GO

/****** Object:  StoredProcedure [PWR].[GenerateSDIConsumption]    Script Date: 5/18/2023 12:47:25 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [PWR].[GenerateSDIConsumption] 
@StartDate DATETIME = NULL
AS
BEGIN

DECLARE @EndDate DATETIME;
DECLARE @Start BIGINT , @End BIGINT;
DECLARE @Value REAL;
IF @StartDate IS NULL
	SET @StartDate = GETUTCDATE()
SET @StartDate = DATEADD(HOUR,-1,@StartDate) -- LAST HOUR
SET @StartDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, @StartDate), 0) -- Remove MINUTES AND SECONDS
SET @EndDate = DATEADD(HOUR,1,@StartDate) -- FOR THE NEXT HOUR
SET @EndDate = DATEADD(SECOND,-1,@EndDate) 
SET @Start = IH.Date2MS(@StartDate);
SET @End = IH.Date2MS(@EndDate);


SELECT
	  @Value =
(	ISNULL([IH].[GetSumAreaByTagId](171106, @Start,@End),0) 
	+ ISNULL([IH].[GetSumAreaByTagId](171107, @Start,@End),0)
	+ ISNULL([IH].[GetSumAreaByTagId](171108, @Start,@End),0)
	+ ISNULL([IH].[GetSumAreaByTagId](171109, @Start,@End),0))  / 100000

IF EXISTS (SELECT * FROM PWR.MeteredLoad WHERE [StartDateTime] = @StartDate AND [Zone] = 'SDI')
BEGIN
    UPDATE PWR.MeteredLoad SET [Mw] = @Value WHERE [StartDateTime] = @StartDate AND [Zone] = 'SDI'
END
ELSE
BEGIN
    INSERT  PWR.MeteredLoad([StartDateTime],[NercRegion],[MarketRegion],[Zone],[LoadArea],[Mw],[IsVerified]) 
	VALUES (@StartDate, 'SDI','SDI','SDI','SDI',@Value,1)
END

END


GO






