 USE [IHBoxSystem]
GO

USE [IHBoxSystem]
GO
CREATE ROLE emails_configuration_read AUTHORIZATION dbo 
GO  

USE [IHBoxSystem]
GO
CREATE ROLE emails_configuration_readWrite AUTHORIZATION dbo 
GO  




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

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

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

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


-------------------
-- Menu and Route
-------------------
	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 
			   (
			   'emails-configuration'
			   , 'emails-configuration'
			   , 'custom-screens/emails-configuration/'
			   , 'emails_configuration_read' 
			   , 'configuration-dashboard'
			   )
			   SET @result_routeId = 9
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			   ,[Code]
			   ,[IHBoxModule]
			  )
		 VALUES
			   (
			   @parent
			   ,'Emails Configuration'
			   ,9 
			   ,1
			   ,1
			   ,1
			   ,'emails_configuration_read'
			   ,NULL
			   ,'emails-configuration'
			   ,'MES'
			 )
	GO

-------------------------------------------------------------------
-- Steps to follow to add a new screen in MES Configuration Section
-------------------------------------------------------------------
 insert into [FrontEnd].MenuProperties(MenuId,[Name],[Value])
 values(
 (select top 1 Id from FrontEnd.Menu where [Name] = 'Emails Configuration'),('data-screenid'),('emails-configuration')
)

 insert into [FrontEnd].[MenuLinkParameters]([MenuLinkID],[Name],[Value])
 values(
 (select top 1 Id from FrontEnd.Menu where [Name] = 'Emails Configuration'),('section'),('emails-configuration')
)


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

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


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

	DECLARE @ModuleId INT

	INSERT INTO [SECURITY].[Modules]([Code],[Name])
	VALUES('emails_configuraton','Emails Configuration')

	SET @ModuleId = @@IDENTITY

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


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


	
--------------------------
--- Stored Procedures ---
--------------------------


USE [MES]
GO
/****** Object:  StoredProcedure [MAIL].[GetEventsScheduleRecipients]    Script Date: 6/23/2023 1:39:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER    PROCEDURE [MAIL].[GetEventsScheduleRecipients]
	@id VARCHAR(20) = NULL,
	@area VARCHAR(10) = NULL,
	@furnace INT = NULL,
	@onlyActives BIT = NULL,

	@fromRow INT = NULL, -- From what row to start fetching from
	@toRow INT = NULL, -- To row (@toRow row num not included in result data)
	@rowsToFetch INT = NULL -- Amount of rows to fetch
AS
BEGIN
	PRINT OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID) + ' Parameters:'
		+ '@id: ' + ISNULL(CAST(@id AS VARCHAR), '')
		+ ', @onlyActives: ' + ISNULL(CAST(@onlyActives AS VARCHAR), '')
		+ ', @fromRow: ' + ISNULL(CAST(@fromRow AS VARCHAR), '')
		+ ', @toRow: ' + ISNULL(CAST(@toRow AS VARCHAR), '')
		+ ', @rowsToFetch: ' + ISNULL(CAST(@rowsToFetch AS VARCHAR), '')


	-----------------------
	-- Parameters default
	-----------------------
	IF (@onlyActives IS NULL)
		SET @onlyActives = 1

	IF (@fromRow IS NULL)
		SET @fromRow = ISNULL(@fromRow, 1)

	IF (@rowsToFetch IS NULL)
		SET @rowsToFetch = ISNULL(@rowsToFetch, 20)

	IF (@toRow IS NULL)
		SET @toRow = ISNULL(@toRow, @fromRow + @rowsToFetch)


	-----------------------
	-- Begin Query
	-----------------------
	SELECT e.EventId, e.EventName, Schedule = 'Once a Week', e.Recipients from MAIL.[Events] e;

END


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



USE [MES]
GO
/****** Object:  StoredProcedure [MAIL].[UpdateEventRecipients]    Script Date: 6/23/2023 2:41:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   PROCEDURE [MAIL].[UpdateEventRecipients]
	@eventId INT,
	@recipients VARCHAR(MAX), 

	@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:'
		+ ', @eventId: ' + ISNULL(CAST(@eventId AS VARCHAR), '')
		+ ', @recipients: ' + ISNULL(CAST(@recipients AS VARCHAR), '')
		+ ', @currentUser: ' + ISNULL(CAST(@currentUser AS VARCHAR), '')


	DECLARE  @logMsg varchar(max),
			@result INT
	-----------------------
	-- Begin Query
	-----------------------

	SET @result = 0

	BEGIN TRY

	UPDATE MAIL.Events SET Recipients = @recipients where EventId = @eventId;

	END TRY

	BEGIN CATCH

	SET @result = ERROR_NUMBER()

	SELECT [Message] = ERROR_MESSAGE()

	END CATCH
END



----- SCHEMAS -------

CREATE SCHEMA SCH;
GO
CREATE SCHEMA MAIL;
GO



----- TABLES -------
create table SCH.ScheduleTypes(
ScheduleTypeId int not null identity(1,1),
[TypeId] varchar(20),-- recurring / One Time
PRIMARY KEY (ScheduleTypeId)
)

create table SCH.DailyFrequencies(
DailyFrequencyId int not null identity(1,1),
[TypeId] int,
PRIMARY KEY (DailyFrequencyId)
)

create table SCH.Frequencies(
FrequencyId int not null identity(1,1),
[TypeId] int,
PRIMARY KEY (FrequencyId)
)

create table SCH.Durations(
DurationId int not null identity(1,1),
[TypeId] int,
PRIMARY KEY (DurationId)
)

create table SCH.ScheduleDetail(
ScheduleDetailId int not null identity(1,1),
ScheduleTypeId int,
DailyFrequencyId int,
FrequencyId int,
DurationId int,

PRIMARY KEY (ScheduleDetailId),
FOREIGN KEY (ScheduleTypeId) REFERENCES SCH.ScheduleTypes(ScheduleTypeId),
FOREIGN KEY (DailyFrequencyId) REFERENCES SCH.DailyFrequencies(DailyFrequencyId),
FOREIGN KEY (FrequencyId) REFERENCES SCH.Frequencies(FrequencyId),
FOREIGN KEY (DurationId) REFERENCES SCH.Durations(DurationId)

)

create table MAIL.[Schedules](
ScheduleId int not null identity(1,1),
EventId int not null,
ScheduleDetailId int,
PRIMARY KEY (ScheduleId),
FOREIGN KEY (ScheduleDetailId) REFERENCES SCH.ScheduleDetail(ScheduleDetailId)
)

create table MAIL.[Events](
EventId int not null identity(1,1),
EventName varchar(100),
ProcedureName varchar(100),
Recipients varchar(MAX),
ScheduleId int,
[Status] char(1), -- E: Error / R: Running / S:Stopped / Successfull
[LastRun] datetime,
[NextRun] datetime,
 PRIMARY KEY (EventId),
 FOREIGN KEY (ScheduleId) REFERENCES MAIL.Schedules(ScheduleId)
 
)



/* TO INSERT DATA ON TABLES */

GO
DECLARE @idInserted INT;

INSERT INTO MAIL.[Events] (EventName, ProcedureName) VALUES ('CasterLogBookMail', 'SYSTEM.SendCasterLogBookMail');
SET @idInserted = SCOPE_IDENTITY();
INSERT INTO MAIL.Recipients (EventId, EmailAddresses) VALUES (@idInserted, 'Matt.Anderson@steeldynamics.com,dsilvestri@dexteel.com,Bill.Wyant@steeldynamics.com,sergiy.Remenyuk@steeldynamics.com');

INSERT INTO MAIL.[Events] (EventName, ProcedureName) VALUES ('CasterShiftReportMail', 'SYSTEM.SendCasterShiftReportMail');
SET @idInserted = SCOPE_IDENTITY();
INSERT INTO MAIL.Recipients (EventId, EmailAddresses) VALUES (@idInserted, 'dsilvestri@dexteel.com,SRD-CastShiftReport@steeldynamics.com,Bill.Wyant@steeldynamics.com,sergiy.Remenyuk@steeldynamics.com');

INSERT INTO MAIL.[Events] (EventName, ProcedureName) VALUES ('DiagnosticsEmail', 'SYSTEM.SendDiagnosticsEmail');
SET @idInserted = SCOPE_IDENTITY();
INSERT INTO MAIL.Recipients (EventId, EmailAddresses) VALUES (@idInserted, 'jsagasti@dexteel.com,dsilvestri@dexteel.com,bill.wyant@steeldynamics.com,sergiy.remenyuk@steeldynamics.com');

INSERT INTO MAIL.[Events] (EventName, ProcedureName) VALUES ('EAFShiftReportMail', 'SYSTEM.SendEAFShiftReportMail');
SET @idInserted = SCOPE_IDENTITY();
INSERT INTO MAIL.Recipients (EventId, EmailAddresses) VALUES (@idInserted, 'srd-ms-shiftreports@steeldynamics.com,dsilvestri@dexteel.com,Bill.Wyant@steeldynamics.com,sergiy.remenyuk@steeldynamics.com');

INSERT INTO MAIL.[Events] (EventName, ProcedureName) VALUES ('EventPJMAlertMail', 'SYSTEM.SendEventPJMAlertMail');
SET @idInserted = SCOPE_IDENTITY();
INSERT INTO MAIL.Recipients (EventId, EmailAddresses) VALUES (@idInserted, 'jsagasti@dexteel.com,dsilvestri@dexteel.com,fcordova@dexteel.com,joshua.hausbach@steeldynamics.com,Bill.Wyant@steeldynamics.com,sergiy.Remenyuk@steeldynamics.com');

INSERT INTO MAIL.[Events] (EventName, ProcedureName) VALUES ('HeatUnknownMail', 'SYSTEM.SendHeatUnknownMail');
SET @idInserted = SCOPE_IDENTITY();
INSERT INTO MAIL.Recipients (EventId, EmailAddresses) VALUES (@idInserted, 'juan.sagasti@dexteel.com,david.silvestri@dexteel.com,Bill.Wyant@steeldynamics.com,sergiy.Remenyuk@steeldynamics.com');

INSERT INTO MAIL.[Events] (EventName, ProcedureName) VALUES ('LadleResetMail', 'SYSTEM.SendLadleResetMail');
SET @idInserted = SCOPE_IDENTITY();
INSERT INTO MAIL.Recipients (EventId, EmailAddresses) VALUES (@idInserted, 'srd-ladlereset@steeldynamics.com,dsilvestri@dexteel.com,Bill.Wyant@steeldynamics.com,sergiy.Remenyuk@steeldynamics.com');

INSERT INTO MAIL.[Events] (EventName, ProcedureName) VALUES ('SecondSiloStatusMail_HTML', 'SYSTEM.SendSecondSiloStatusMail_HTML');
SET @idInserted = SCOPE_IDENTITY();
INSERT INTO MAIL.Recipients (EventId, EmailAddresses) VALUES (@idInserted, 'Dustin.Arvola@steeldynamics.com,silo_reading@hillercarbon.com,brenth@agtrucking.com,Jannj@agtrucking.com,ehowe@custommaintenance.solutions,
brandon@custommaintenance.solutions,rebecca.mccusker@carmeuse.com,zach.richardson@carmeuse.com,jake.zagar@carmeuse.com,eric.ables@carmeuse.com,Tara.Perriello@carmeusena.com,amanda.zaluski@carmeusena.com,
twilliams@laidlaw.ca,danielp@agtrucking.com,Michael.Musselman@steeldynamics.com,Bill.Wyant@steeldynamics.com,sergiy.Remenyuk@steeldynamics.com,dsilvestri@dexteel.com');

INSERT INTO MAIL.[Events] (EventName, ProcedureName) VALUES ('ShiftReportMail', 'SYSTEM.SendShiftReportMail');
SET @idInserted = SCOPE_IDENTITY();
INSERT INTO MAIL.Recipients (EventId, EmailAddresses) VALUES (@idInserted, 'dsilvestri@dexteel.com');

INSERT INTO MAIL.[Events] (EventName, ProcedureName) VALUES ('ShiftReportMail_New', 'SYSTEM.SendShiftReportMail_New');
SET @idInserted = SCOPE_IDENTITY();
INSERT INTO MAIL.Recipients (EventId, EmailAddresses) VALUES (@idInserted, 'srd-ms-shiftreports@steeldynamics.com,dsilvestri@dexteel.com,Bill.Wyant@steeldynamics.com,sergiy.Remenyuk@steeldynamics.com');

INSERT INTO MAIL.[Events] (EventName, ProcedureName) VALUES ('SilosReportMail', 'SYSTEM.SendSilosReportMail');
SET @idInserted = SCOPE_IDENTITY();
INSERT INTO MAIL.Recipients (EventId, EmailAddresses) VALUES (@idInserted, 'Joshua.Hausbach@steeldynamics.com,Michael.Musselman@steeldynamics.com,julee.moreno@steeldynamics.com,Jason.Kimmel@steeldynamics.com,
Dave.Lunceford@steeldynamics.com,Clay.Gross@steeldynamics.com,SRD-FurnaceSupervisors@steeldynamics.com,SRD-EAFHelpers@steeldynamics.com,danielp@agtrucking.com,dsilvestri@dexteel.com,Bill.Wyant@steeldynamics.com,sergiy.Remenyuk@steeldynamics.com');

INSERT INTO MAIL.[Events] (EventName, ProcedureName) VALUES ('SilosReportSuppliersMail', 'SYSTEM.SendSilosReportSuppliersMail');
SET @idInserted = SCOPE_IDENTITY();
INSERT INTO MAIL.Recipients (EventId, EmailAddresses) VALUES (@idInserted, 'Dustin.Arvola@steeldynamics.com,silo_reading@hillercarbon.com,lizab@agtrucking.com,Jannj@agtrucking.com,ehowe@custommaintenance.solutions,
brandon@custommaintenance.solutions,rebecca.mccusker@carmeuse.com,peter.lenko@carmeuse.com,jake.zagar@carmeuse.com,eric.ables@carmeuse.com,
Tara.Perriello@carmeusena.com,amanda.zaluski@carmeusena.com,twilliams@laidlaw.ca,danielp@agtrucking.com,melissaj@racoal.com,dsilvestri@dexteel.com,derekd@agtrucking.com,Bill.Wyant@steeldynamics.com,sergiy.Remenyuk@steeldynamics.com');

INSERT INTO MAIL.[Events] (EventName, ProcedureName) VALUES ('SiloStatusMail', 'SYSTEM.SendSiloStatusMail');
SET @idInserted = SCOPE_IDENTITY();
INSERT INTO MAIL.Recipients (EventId, EmailAddresses) VALUES (@idInserted, ';Joshua.Hausbach@steeldynamics.com,julee.moreno@steeldynamics.com,Jason.Kimmel@steeldynamics.com,Dave.Lunceford@steeldynamics.com,
Clay.Gross@steeldynamics.com,Michael.Musselman@steeldynamics.com,SRD-FurnaceSupervisors@steeldynamics.com,SRD-EAFHelpers@steeldynamics.com,danielp@agtrucking.com,jsagasti@dexteel.com,dsilvestri@dexteel.com,
Bill.Wyant@steeldynamics.com,sergiy.Remenyuk@steeldynamics.com');

INSERT INTO MAIL.[Events] (EventName, ProcedureName) VALUES ('TankLevelMail', 'SYSTEM.SendTankLevelMail');
SET @idInserted = SCOPE_IDENTITY();
INSERT INTO MAIL.Recipients (EventId, EmailAddresses) VALUES (@idInserted, 'SRD-MC-HydraulicTankLevel@steeldynamics.com,Bill.Wyant@steeldynamics.com,sergiy.Remenyuk@steeldynamics.com,dsilvestri@dexteel.com');


INSERT INTO MAIL.[Events] (EventName, ProcedureName) VALUES ('TemperatureMail', 'SYSTEM.SendTemperatureMail');
SET @idInserted = SCOPE_IDENTITY();

INSERT INTO MAIL.Recipients (EventId, EmailAddresses) VALUES (@idInserted, 'clay.gross@steeldynamics.com,ted.reeser@steeldynamics.com,Dave.Lunceford@steeldynamics.com,Dave.Talley@steeldynamics.com,
Dave.Hoff@steeldynamics.com,Tom.Geiger@steeldynamics.com,Tim.Raleigh@steeldynamics.com,Dustin.Arvola@steeldynamics.com,Valoree.Varick@steeldynamics.com,Jamison.Alles@steeldynamics.com,
Bradley.Beazell@steeldynamics.com,Trent.Conrad@steeldynamics.com,Nate.Keiser@steeldynamics.com,Josh.Otis@steeldynamics.com,Kevin.Parrish@steeldynamics.com,Austin.Smith@steeldynamics.com,
Doug.Wilcoxson@steeldynamics.com,Jon.Zurbrick@steeldynamics.com,Joshua.Hausbach@steeldynamics.com,jsagasti@dexteel.com,Bill.Wyant@steeldynamics.com,sergiy.Remenyuk@steeldynamics.com,dsilvestri@dexteel.com');

GO



