USE [IHBoxSystem]
GO

---------------------------------------- CREATE ROLES
IF DATABASE_PRINCIPAL_ID('logbook_readWrite') IS NULL
BEGIN
	CREATE ROLE logbook_readWrite;
end
go
IF DATABASE_PRINCIPAL_ID('logbook_read') IS NULL
BEGIN
	CREATE ROLE logbook_read;
end

---------------------------------------- CREATE MODULE 
declare @id int
insert into [Security].[Modules] (Code,[Name])
values ('log-book' ,'Log Book')

set @id = @@IDENTITY

insert into [Security].[ModuleActions] (ModuleID,Code,[Name],[Role])
values (@id,'readWrite','Read/Write','logbook_readWrite')

insert into [Security].[ModuleActions] (ModuleID,Code,[Name],[Role])
values (@id,'read','Read','logbook_read')

delete from frontend.menu where  name = 'Log Book'
delete from frontend.routes where ModuleName='log-book'

INSERT INTO [FrontEnd].[Routes]
           ([Route]
           ,[ModuleName]
           ,[ModulePath]
           ,[RequiredRole]
       )
     VALUES
           (
		   '!/log-book(/:start)(/:end)(/:mode)(/:crewId)(/:shiftId)(/:logEntryTypeId)(/:id)(/:searchText)'
           , 'log-book'
		   , 'custom-screens/log-book/'
           , 'logbook_read'       
		   )

		   USE [IHBoxSystem]
GO




INSERT INTO [FrontEnd].[Menu]
           ([Parent]
           ,[Name]
           ,[RouteID]
           ,[SortOrder]
           ,[Enabled]
           ,[Visible]
           ,[RequiredRole]
           ,[Group]
          )
     VALUES
           (
			(select id from FrontEnd.Menu where [name]='MES' and Parent is null)
		   ,'Log Book'
           ,@@IDENTITY
           ,1
           ,1
           ,1
           ,'logbook_read'
           ,NULL
           )
GO

EXEC sp_addrolemember 'logbook_readWrite', 'admin'
EXEC sp_addrolemember 'logbook_read', 'admin'

EXEC sp_addrolemember 'logbook_readWrite', 'test'
EXEC sp_addrolemember 'logbook_read', 'test'


EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [logbook_readWrite] ,@name = N'securedsp-LB.DeleteLogEntry'		  ,@value =  N'SendCurrentUser=TRUE'  
EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [logbook_readWrite] ,@name = N'securedsp-LB.GetDefaultLogEntry'   ,@value =  N'SendCurrentUser=TRUE' 
EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [logbook_readWrite] ,@name = N'securedsp-LB.GetLogBook'			  ,@value = N'null'
EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [logbook_readWrite] ,@name = N'securedsp-LB.GetLogBookCatalogs'   ,@value =  N'null'
EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [logbook_readWrite] ,@name = N'securedsp-LB.UpsertLogEntry'		  ,@value =  N'SendCurrentUser=TRUE'  


EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [logbook_read] ,@name = N'securedsp-LB.GetDefaultLogEntry'   ,@value =  N'null'
EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [logbook_read] ,@name = N'securedsp-LB.GetLogBook'			  ,@value =  N'null'
EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [logbook_read] ,@name = N'securedsp-LB.GetLogBookCatalogs'   ,@value =  N'null'





USE [LogBook]
GO
ALTER TABLE [LB].[LogEntries]
add [EquipmentID] [int] NULL
GO

ALTER TABLE [LB].[LogEntries]
add [Time] [DATETIME] NULL
GO
/*
Returns a table with log entries that match the criteria
Union with Process data 
if ID is specified it returns all log entries from the corresponding productiondate/shift 
*/

/*----------------------------------------------------------------GET LOG BOOK----------------------------------------------------------------------------------*/
USE [LogBook]
GO
/****** Object:  StoredProcedure [LB].[GetLogBook]    Script Date: 20 ene. 2020 13:59:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [LB].[GetLogBook] @Start DATETIME = NULL
	,@End DATETIME = NULL
	,@ID INT = NULL
	,@CrewID CHAR(1) = NULL
	,@ShiftID CHAR(1) = NULL
	,@LogEntryTypeID INT = NULL
	,@EquipmentId INT = NULL
	,@SearchText VARCHAR(200) = NULL
	,@Author VARCHAR(200) = NULL
	,@TimeZoneCode CHAR(10) = NULL
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE @procName VARCHAR(300)
		,@Call VARCHAR(max)

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

	SET @Call = @procName + ' ' + ', @ID: ' + ISNULL(CAST(@ID AS VARCHAR), 'NULL') + ', @Start: ' + ISNULL(CAST(@Start AS VARCHAR), 'NULL') + ', @End: ' + ISNULL(CAST(@End AS VARCHAR), 'NULL') + ', @ShiftID: ' + ISNULL(CAST(@ShiftID AS VARCHAR), 'NULL') + ', @CrewID: ' + ISNULL(CAST(@CrewID AS VARCHAR), 'NULL') + ', @LogEntryTypeID: ' + ISNULL(CAST(@LogEntryTypeID AS VARCHAR), 'NULL') + ', @SearchText: ' + ISNULL(CAST(@SearchText AS VARCHAR), 'NULL') + ISNULL(CAST(@Author AS VARCHAR), 'NULL')

	-----------------------
	-- Parameter defaults
	-----------------------
	IF @TimeZoneCode IS NULL
		SET @TimeZoneCode = 'EDTIND'

	IF @End IS NULL
		SELECT @End = ProductionDate
		FROM LB.GetShiftData(NULL)

	IF @Start IS NULL
		SELECT @Start = ProductionDate
		FROM LB.GetShiftData(NULL)

	DECLARE @SearchTextString VARCHAR(100)

	SET @searchTextString = '%' + @SearchText + '%'

	IF @ID > 0
	BEGIN
		SELECT @Start = ProductionDate
			,@End = ProductionDate
			,@ShiftID = ShiftID
			,@LogEntryTypeID = LogEntryTypeID
		FROM LogEntries
		WHERE ID = @ID
	END

	-----------------------
	-- Validation
	-----------------------
	IF @ShiftID NOT IN (
			NULL
			,'D'
			,'N'
			)
		RAISERROR (
				'ShiftID must be D or N'
				,12
				,1
				)

	IF @CrewID NOT IN (
			NULL
			,'A'
			,'B'
			,'C'
			,'D'
			)
		RAISERROR (
				'CrewID must be A,B,C or D'
				,13
				,1
				)

	IF NOT EXISTS (
			SELECT *
			FROM catLogEntryTypes
			WHERE ID = @LogEntryTypeID
			)
		AND @LogEntryTypeID IS NOT NULL
		RAISERROR (
				'LogEntryTypeID is invalid'
				,14
				,1
				)

	IF NOT EXISTS (
			SELECT *
			FROM IHBoxSystem.EQUIP.Equipments
			WHERE ID = @EquipmentId
			)
		AND @EquipmentId IS NOT NULL
		RAISERROR (
				'EquimentID is invalid'
				,15
				,1
				)

	-----------------------
	-- Execution
	-----------------------
	SELECT le.[ID]
		,[Author]
		,[ProductionDate]
		,[ShiftID]
		,[Time] = IH.ToLocal([Time], @timeZoneCode)
		,[CrewID]
		,[Title]
		,[Description]
		,[CreateTimestamp]
		,[UpdateTimestamp]
		,[LogEntryTypeID]
		,[TypeName] = let.[Name]
		,[EquipmentId] = e.id
		,[EquipmentName] = e.[Name]
		,[AutoGenerated] = 0
	FROM [LB].[LogEntries] le
	LEFT JOIN IHBoxSystem.EQUIP.Equipments e ON e.ID = le.EquipmentId
	LEFT JOIN IHBoxSystem.EQUIP.EquipmentTemplates ete ON ete.EquipmentId = e.Id
	LEFT JOIN [LB].[catLogEntryTypes] let ON let.ID = le.LogEntryTypeID
	WHERE ProductionDate BETWEEN @Start
			AND @End
		AND (
			@ShiftID IS NULL
			OR ShiftID = @ShiftID
			)
		AND (
			@LogEntryTypeID IS NULL
			OR LogEntryTypeID = @LogEntryTypeID
			)
		AND (
			@CrewID IS NULL
			OR CrewID = @CrewID
			)
		AND (
			@LogEntryTypeID IS NULL
			OR LogEntryTypeID = @LogEntryTypeID
			)
		AND (
			@EquipmentId IS NULL
			OR LB.isAncestorOrSame(le.EquipmentID, @EquipmentId) = 1
			)
		AND (
			@Author IS NULL
			OR le.Author = @Author
			)
		AND (
			@SearchTextString IS NULL
			OR Title LIKE @SearchTextString
			OR [Description] LIKE @SearchTextString
			OR [Author] LIKE @SearchTextString
			)
	
	UNION
	
	SELECT NULL
		,'EAF Op'
		,ep.ProductionDate
		,CASE 
			WHEN ep.ShiftId = 2
				THEN 'N'
			ELSE 'D'
			END
		,IH.ToLocal(ep.Start, @TimeZoneCode)
		,ep.CrewId
		,h.[Name]
		,ep.Comments
		,IH.ToLocal(ep.StartTap, @TimeZoneCode)
		,NULL
		,4
		,let.[Name]
		,NULL
		,NULL
		,1
	FROM [MES].EAF.Processes AS ep
	LEFT JOIN [MES].MES.Heats AS h ON ep.HeatId = h.Id
	LEFT JOIN [catLogEntryTypes] let ON let.ID = 4
	WHERE
		--JUAN -- eliminate repeated 
		ep.ProductionDate BETWEEN @Start
			AND @End
		AND (
			@ShiftID IS NULL
			OR CASE 
				WHEN ep.ShiftId = 2
					THEN 'N'
				ELSE 'D'
				END = @ShiftID
			)
		AND (
			@LogEntryTypeID IS NULL
			OR 4 = @LogEntryTypeID
			)
		AND (
			@CrewID IS NULL
			OR ep.CrewId = @CrewID
			)
		AND (
			@Author IS NULL
			OR 'EAF Op' = @Author
			)
		AND (
			@SearchTextString IS NULL
			OR ep.[Comments] LIKE @SearchTextString
			OR 'EAF Op' LIKE @SearchTextString
			)
	--ORDER BY CreateTimestamp ASC
	ORDER BY ProductionDate DESC
		,[Time] DESC

	RETURN 0
END
GO

/*----------------------------------------------------------------GET LOG BOOK CATALOGS----------------------------------------------------------------------------------*/

USE [LogBook]
GO
/****** Object:  StoredProcedure [LB].[GetLogBookCatalogs]    Script Date: 20 ene. 2020 13:58:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Returns a list of catalogs used to filter log entries
*/
ALTER PROCEDURE [LB].[GetLogBookCatalogs]
AS
BEGIN
	
	-----------------------
	-- list of catalogs
	-----------------------
	select [Index]=1, Name='CREWS'  
	union select 2,'SHIFTS'
	union select 3, 'LOGENTRYTYPES'
	union select 4, 'EQUIPMENTS'
	union select 5, 'USERS'
	-----------------------
	-- Crews
	-----------------------
	select Crew = 'A' 
	union select 'B'
	union select 'C'
	union select 'D'

	-----------------------
	-- Shifts
	-----------------------
	select [Shift]='D'
	union select 'N'

	-----------------------
	-- Log Entry Types
	-----------------------
	select 
	[LogEntryTypeId] = [ID] 
	,[LogEntryTypeName] = [name]
	from LB.catLogEntryTypes

	-----------------------
	-- Equipment Tree
	-----------------------
	select 
	ete.Id
	,ParentId = case when ParentId= -1 then null else ParentId end
	,[TypeName] = et.[Name]
	,[EquipmentName] = e.[Name]
	,[EquipmentId] = e.Id
	from IHBoxSystem.EQUIP.EquipmentTemplates ete
	left join IHBoxSystem.EQUIP.EquipmentTypes et on ete.Typeid = et.ID
	left join  IHBoxSystem.EQUIP.Equipments e on e.ID = ete.EquipmentId
	where not ParentId is null

	-----------------------
	-- Current Shift data (?)
	-----------------------
	select * from LB.GetShiftData(NULL)

	-----------------------
	-- Users
	-----------------------
	SELECT DISTINCT (Author) FROM LB.LogEntries WHERE ProductionDate > GETDATE()-100
	-----------------------
	-- Finish procedure
	-----------------------
	RETURN 0
END

GO



/*----------------------------------------------------------------UPSERT LOG ENTRY----------------------------------------------------------------------------------*/
USE [LogBook]
GO
/****** Object:  StoredProcedure [LB].[UpsertLogEntry]    Script Date: 21 ene. 2020 09:01:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [LB].[UpsertLogEntry] 
	@ID INT = NULL
	,@Datetime DATETIME = NULL
	,@LogEntryTypeID INT
	,@Title VARCHAR(200)
	,@Author VARCHAR(50) = NULL
	,@EquipmentID INT = NULL
	,@Description VARCHAR(max)
	,@CurrentUser VARCHAR(50) -- auto included by 4i
	,@TimeZoneCode CHAR(10) = NULL
	,@NewEntry BIT = 0
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE @Call VARCHAR(max)
		,@procName VARCHAR(300)
		,@IdP INT

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

	SET @Call = @procName + ' ' + ', @ID= ' + ISNULL(CAST(@ID AS VARCHAR), 'NULL') + ', @Time= ' + ISNULL('''' + CAST(@Datetime AS VARCHAR) + '''', 'NULL') + ', @LogEntryTypeID= ' + ISNULL('''' + CAST(@LogEntryTypeID AS VARCHAR) + '''', 'NULL') + ', @EquipmentID= ' + ISNULL('''' + CAST(@EquipmentID AS VARCHAR) + '''', 'NULL') + ', @Title= ' + ISNULL('''' + CAST(@Title AS VARCHAR) + '''', 'NULL') + ', @Author= ' + ISNULL('''' + CAST(@Author AS VARCHAR) + '''', 'NULL') + ', @Description= ' + ISNULL('''' + CAST(@Description AS VARCHAR) + '''', 'NULL') + ', @CurrentUser= ' + ISNULL('''' + CAST(@CurrentUser AS VARCHAR) + '''', 'NULL')

	-----------------------
	-- Validation
	-----------------------
	IF NOT EXISTS (
			SELECT *
			FROM catLogEntryTypes
			WHERE ID = @LogEntryTypeID
			)
		AND @LogEntryTypeID IS NOT NULL
		RAISERROR (
				'LogEntryTypeID is invalid'
				,14
				,1
				)

	IF NOT EXISTS (
			SELECT *
			FROM IHBoxSystem.EQUIP.Equipments
			WHERE ID = @EquipmentId
			)
		AND @EquipmentId IS NOT NULL
		RAISERROR (
				'EquimentID is invalid'
				,15
				,1
				)

	-----------------------
	-- Default Values
	-----------------------
	IF @TimeZoneCode IS NULL
		SET @TimeZoneCode = 'EDTIND'

	DECLARE @ProductionDate DATETIME
		,@CrewID CHAR(1)
		,@ShiftID CHAR(1)

	SELECT @ProductionDate = gsd.ProductionDate
		,@CrewID = gsd.CrewID
		,@ShiftID = gsd.ShiftID
	FROM LB.GetShiftData(IH.ToUTC(@Datetime, @TimeZoneCode)) AS gsd

	DECLARE @CurrentAuthor VARCHAR(50)

	SET @CurrentAuthor = coalesce(@Author, @CurrentUser)

	-----------------------
	-- Execution
	-----------------------
	BEGIN TRY
		IF (@ID IS NULL AND @NewEntry = 0)
		BEGIN
			SELECT @IdP = MAX(ep.Id)
			FROM MES.Mes.Heats h
			LEFT JOIN MES.EAF.Processes ep ON h.Id = ep.HeatId
			WHERE @Title = h.[Name]

			UPDATE MES.EAF.Processes
				SET Comments = @Description
			WHERE Id = @IdP
			SET @ID = @@IDENTITY
		END
		ELSE IF (SELECT count(*) FROM LogEntries WHERE ID = @ID	) > 0
		BEGIN
			UPDATE LogEntries
			SET [Author] = @CurrentAuthor
				,[ProductionDate] = @ProductionDate
				,[ShiftID] = CASE 
					WHEN @ShiftID = 1
						THEN 'D'
					ELSE 'N'
					END
				,[Time] = IH.ToUTC(@Datetime, @timeZoneCode)
				,[CrewID] = @CrewID
				,[LogEntryTypeID] = @LogEntryTypeID
				,[EquipmentID] = @EquipmentID
				,[Title] = @Title
				,[Description] = @Description
				,[UpdateTimestamp] = GETUTCDATE()
			WHERE ID = @ID
		END
		ELSE
		IF (@NewEntry = 1)
		BEGIN
			INSERT INTO LogEntries (
				[Author]
				,[ProductionDate]
				,[ShiftID]
				,[Time]
				,[CrewID]
				,[LogEntryTypeID]
				,[EquipmentID]
				,[Title]
				,[Description]
				,[CreateTimestamp]
				,[UpdateTimestamp]
				)
			VALUES (
				@CurrentAuthor
				,@ProductionDate
				,@ShiftID
				,IH.ToUTC(@Datetime, @timeZoneCode)
				,@CrewID
				,@LogEntryTypeID
				,@EquipmentID
				,@Title
				,@Description
				,GETUTCDATE()
				,NULL
				)

			SET @ID = @@IDENTITY
		END
	END TRY

	BEGIN CATCH
		DECLARE @logmsg VARCHAR(MAX)

		SET @logmsg = ERROR_MESSAGE() + ' ' + @Call

		EXEC [LB].LogMsg @message = @logmsg
			,@procedure = @ProcName

		RAISERROR (
				'Procedure Execution Error'
				,14
				,1
				)
	END CATCH

	-----------------------
	-- AUDIT LOG
	-----------------------
	BEGIN TRY
		DECLARE @msg VARCHAR(MAX)
			,@moduleName VARCHAR(200)

		SET @moduleName = 'LogBook'
		SET @msg = 'Updated Log Entry:' + @Call

		EXEC [LB].AuditMsg @message = @msg
			,@moduleName = @moduleName
			,@currentUser = @currentUser
	END TRY

	BEGIN CATCH
		PRINT ERROR_MESSAGE() + ': Error on message audit.'
	END CATCH

	-----------------------
	-- Return table of 1 record with the inserted/updated id
	-----------------------
	SELECT ID = @ID

	RETURN 0
END
GO

/*----------------------------------------------------------------GET DEFAULT LOG ENTRY----------------------------------------------------------------------------------*/

ALTER PROCEDURE [LB].[GetDefaultLogEntry]
@Datetime datetime = NULL,
@TimeZoneCode char(10) = NULL,
@CurrentUser varchar(50) = NULL
AS
BEGIN
IF @TimeZoneCode IS NULL
SET @TimeZoneCode = 'EDTIND'

	-----------------------
	-- Execution 
	-----------------------
	select 
		ProductionDate = gsd.ProductionDate
		,CrewID = gsd.CrewID
		,ShiftID = gsd.ShiftID
		,CurrentUser = @CurrentUser
		,LogEntryTypeID = 2
 	from LB.GetShiftData(IH.ToUTC(@Datetime,@TimeZoneCode)) as gsd

	-----------------------
	-- Finish procedure
	-----------------------

	RETURN 0
END

GO
/*----------------------------------------------------------------DELETE LOG ENTRY----------------------------------------------------------------------------------*/
ALTER PROCEDURE [LB].[DeleteLogEntry]
	@ID int,
	@CurrentUser varchar(50) 
AS
BEGIN
	-----------------------
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		@Call VARCHAR(max),
		@procName VARCHAR(300)


	SELECT
		@procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)	
	
	SET @Call = @procName + ' '
		+ ', @ID= ' + ISNULL(CAST(@ID AS VARCHAR), 'NULL')
		+ ', @CurrentUser= ' + ISNULL(''''+CAST(@CurrentUser AS VARCHAR)+'''', 'NULL')
		
	-----------------------
	-- Validation
	-----------------------
	IF  NOT EXISTS(select * from LogEntries where ID = @ID)
		RAISERROR('Not such ID', 11, 1)
	
	-----------------------
	-- Execution
	-----------------------
	BEGIN TRY
	
		DELETE FROM LogEntries
		WHERE ID = @ID
		
	END TRY
	-----------------------
	-- Log Error if Execution fails
	-----------------------
	BEGIN CATCH
		DECLARE @logmsg VARCHAR(MAX)
		SET @logmsg = ERROR_MESSAGE() + ' ' + @Call

		EXEC [LB].LogMsg 
			@message =  @logmsg,
			@procedure = @ProcName
		RAISERROR('Procedure Execution Error', 14, 1)
	END CATCH	
	
	-----------------------
	-- AUDIT LOG
	-----------------------
	BEGIN TRY
		DECLARE @msg VARCHAR(MAX),
				@moduleName VARCHAR(200) 
	
		SET @moduleName = 'LogBook'

		SET @msg = 'Deleted Log Entry:' + @Call

		EXEC [LB].AuditMsg
			@message = @msg,
			@moduleName = @moduleName,
			@currentUser = @currentUser
	END TRY
	BEGIN CATCH
		PRINT ERROR_MESSAGE() + ': Error on message audit.'
	END CATCH
	
	return 0
END
GO




------------------------------------------------------------------------------------------------------------------------

update FrontEnd.Menu 
set [Name] = 'Melting Log Book'
where [Name] = 'Log Book'