

USE [IHBoxSystem]
GO


---------------------------------------- CREATE ROLES
IF DATABASE_PRINCIPAL_ID('casting_logbook_readWrite') IS NULL
BEGIN
	CREATE ROLE casting_logbook_readWrite;
end
go
IF DATABASE_PRINCIPAL_ID('casting_logbook_read') IS NULL
BEGIN
	CREATE ROLE casting_logbook_read;
end


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

set @id = @@IDENTITY

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

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




EXEC sp_addrolemember 'casting_logbook_readWrite', 'admin'
EXEC sp_addrolemember 'casting_logbook_read', 'admin'


EXEC sp_addrolemember 'casting_logbook_readWrite', 'SDI/australtek'
EXEC sp_addrolemember 'casting_logbook_read', 'SDI/australtek'

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


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


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

insert into FrontEnd.Menu
		([Name],SortOrder,[Enabled],Visible,[Group],IHBoxModule)
		VALUES
			(
			'Caster'
			,8
			,1
			,1
			,'navbar_left_with_modules'
			,'MES'
			)


declare @parent int, @result_routeId INT
select @parent=id from [FrontEnd].[Menu] where name='Caster'


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

INSERT INTO [FrontEnd].[Menu]
           ([Parent]
           ,[Name]
           ,[RouteID]
           ,[SortOrder]
           ,[Enabled]
           ,[Visible]
           ,[RequiredRole]
           ,[Group]
          )
     VALUES
           (
			@parent
		   ,'Casting Log Book'
           ,@@IDENTITY
           ,1
           ,1
           ,1
           ,'casting_logbook_read'
           ,NULL
           )
GO



-----------------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE [LogBook].[LB].[LogEntries]
ADD LogBookTypeCode char(1);

update Lb.LogEntries
	set LogBookTypeCode = 'M'
where LogBookTypeCode is null

GO


select * from FrontEnd.Menu where name = 'Log Book'

update FrontEnd.Menu 
set name = 'Melting Log Book'
where name = 'Log Book'



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

USE [LogBook]
GO
/****** Object:  StoredProcedure [LB].[UpsertLogEntry]    Script Date: 11 oct. 2020 21:02:37 ******/
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
	,@LogBookTypeCode char(1) = NULL
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 @LogBookTypeCode is null
		set @LogBookTypeCode = 'M'

	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
	-----------------------
	IF @LogBookTypeCode = 'M'
	BEGIN
		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 = SUBSTRING(@Description, 1, 199)
				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]
					,[LogBookTypeCode]
					)
				VALUES (
					@CurrentAuthor
					,@ProductionDate
					,@ShiftID
					,IH.ToUTC(@Datetime, @timeZoneCode)
					,@CrewID
					,@LogEntryTypeID
					,@EquipmentID
					,@Title
					,@Description
					,GETUTCDATE()
					,NULL
					,'M'
					)

				SET @ID = @@IDENTITY
			END
		END TRY

		BEGIN CATCH
			DECLARE @logmsg VARCHAR(MAX)

			SET @logmsg = ERROR_MESSAGE() + ' MELTING LOG BOOK ' + @Call

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

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

	END
	ELSE IF @LogBookTypeCode = 'C'
	BEGIN
		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 = SUBSTRING(@Description, 1, 199)
			--	WHERE Id = @IdP
			--	SET @ID = @@IDENTITY
			--END
			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]
					,[LogBookTypeCode]
					)
				VALUES (
					@CurrentAuthor
					,@ProductionDate
					,@ShiftID
					,IH.ToUTC(@Datetime, @timeZoneCode)
					,@CrewID
					,@LogEntryTypeID
					,@EquipmentID
					,@Title
					,@Description
					,GETUTCDATE()
					,NULL
					,'C'
					)

				SET @ID = @@IDENTITY
			END
		END TRY

		BEGIN CATCH

			SET @logmsg = ERROR_MESSAGE() + ' CASTING LOG BOOK ' + @Call 

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

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

	

	-----------------------
	-- 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


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

USE [LogBook]
GO
/****** Object:  StoredProcedure [LB].[GetLogBook]    Script Date: 11 oct. 2020 20:54:47 ******/
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

	,@LogBookTypeCode char(1) = 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
	-----------------------
	-- 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 @LogBookTypeCode is null
		set @LogBookTypeCode = 'M'


	IF (@rowsToFetch IS NULL)
	SET @rowsToFetch = -1
	
	IF (@rowsToFetch > 0)
	BEGIN
	IF (@fromRow IS NULL)
	SET @fromRow = 1
	
	IF (@toRow IS NULL)
	SET @toRow = @fromRow + @rowsToFetch
	END



	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 @LogBookTypeCode NOT IN ('M','C')
		RAISERROR (
				'Log Book Type must be CASTING or MELTING'
				,16
				,1
				)

	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
	-----------------------
	IF @LogBookTypeCode = 'M'
	BEGIN
		-----------------------
		-- Log Book -- MELTING
		-----------------------
			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
			INTO #temp1
			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
					)
				AND le.LogBookTypeCode = 'M'

			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


			SELECT 
			t1.*,
			RowNum = ROW_NUMBER() OVER (ORDER BY t1.[ProductionDate] DESC)
			INTO #temp2
			FROM #temp1 t1

			SELECT
			*
			FROM #temp2 t2
			WHERE
			(@rowsToFetch = -1 OR (t2.RowNum BETWEEN @fromRow AND  @toRow))
				ORDER BY t2.ProductionDate DESC
				,t2.[Time] DESC

	END
	ELSE IF @LogBookTypeCode = 'C'
	BEGIN
		-----------------------
		-- Log Book -- CASTING
		-----------------------
			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
			INTO #temp1c
			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 (
					@Author IS NULL
					OR le.Author = @Author
					)
				AND (
					@SearchTextString IS NULL
					OR Title LIKE @SearchTextString
					OR [Description] LIKE @SearchTextString
					OR [Author] LIKE @SearchTextString
					)
				AND le.LogBookTypeCode = 'C'


					
			SELECT 
			t1.*,
			RowNum = ROW_NUMBER() OVER (ORDER BY t1.[ProductionDate] DESC)
			INTO #temp2c
			FROM #temp1c t1

			SELECT
			*
			FROM #temp2c t2
			WHERE
			(@rowsToFetch = -1 OR (t2.RowNum BETWEEN @fromRow AND  @toRow))
				ORDER BY t2.ProductionDate DESC
				,t2.[Time] DESC

	END

		


	RETURN 0
END
GO


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


  insert into FrontEnd.MenuProperties
  values(
  (select top 1 Id from frontEnd.Menu where Parent is null and [Name] = 'VTD')
  ,'icon',
  'fa fa-share-square-o')

  
   insert into FrontEnd.MenuProperties
  values(
  (select top 1 Id from frontEnd.Menu where Parent is null and [Name] = 'Ladles')
  ,'icon',
  'fa fa-bitbucket-square')

   insert into FrontEnd.MenuProperties
  values(
  (select top 1 Id from frontEnd.Menu where Parent is null and [Name] = 'Caster')
  ,'icon',
  'fa fa-bitbucket-square')

  -------------------------------------------------------------------------------------------------------------------------------------------------------
  -------------------------------------------------------------------------------------------------------------------------------------------------------
  -------------------------------------------------------------------------------------------------------------------------------------------------------
  ------------------------------------------------------------13/02/2022 --------------------------------------------------------------------------------
  ------------------------------------------------------------13/02/2022 --------------------------------------------------------------------------------
  ------------------------------------------------------------13/02/2022 --------------------------------------------------------------------------------
  ------------------------------------------------------------13/02/2022 --------------------------------------------------------------------------------
  ------------------------------------------------------------13/02/2022 --------------------------------------------------------------------------------
  -------------------------------------------------------------------------------------------------------------------------------------------------------
  -------------------------------------------------------------------------------------------------------------------------------------------------------

  use MES


CREATE TABLE [MES].catAssets (
    [AssetId]       INT          IDENTITY (1, 1) NOT NULL,
    [ParentAssetId] INT          NULL,
    [AssetName]     VARCHAR (50) NOT NULL,
    [IsAsset]       BIT          CONSTRAINT [DF_Assets_isAsset] DEFAULT ((1)) NOT NULL,
    CONSTRAINT [PK_Assets] PRIMARY KEY CLUSTERED ([AssetId] ASC)
);



	
	
	SET IDENTITY_INSERT [MES].[catAssets] ON 	
		INSERT INTO mes.catAssets(AssetId,ParentAssetId,AssetName,isAsset)
		values
		(1,	NULL,'Plant',1) 
			,(1000,	1,'Caster 1',1)
				,(1100,	1000,'Cross Transfer',0)
				,(1101,	1000,'Ducking Dog Pushers',0)
				,(1102,	1000,'Grease System',0)
				,(1103,	1000,'HMI',0)
				,(1104,	1000,'Hot Charge',0)
				,(1105,	1000,'Main Hydraulics',0)
				,(1106,	1000,'Pusher Hydraulics',0)
				,(1107,	1000,'Machine Water',0)
				,(1108,	1000,'Mold Level',0)
				,(1109,	1000,'Mold Lube',0)
				,(1110,	1000,'Mold Water',0)
				,(1111,	1000,'OS1 Panel',0)
				,(1112,	1000,'PLC',0)
				,(1113,	1000,'Runout Table 1',0)
				,(1114,	1000,'Runout Table 2',0)
				,(1115,	1000,'Ladle Slide Gate',0)
				,(1116,	1000,'Spray Chamber Fans',0)
				,(1117,	1000,'Strand 1 Straighteners',0)
				,(1118,	1000,'Strand 2 Straighteners',0)
				,(1119,	1000,'Strand 3 Straighteners',0)
				,(1120,	1000,'Strand 4 Straighteners',0)
				,(1121,	1000,'Strand 1 Weigh System',0)
				,(1122,	1000,'Strand 2 Weigh System',0)
				,(1123,	1000,'Strand 3 Weigh System',0)
				,(1124,	1000,'Strand 4 Weigh System',0)
				,(1125,	1000,'Tagging Machine',0)
				,(1126,	1000,'Torch Ducking Rolls',0)
				,(1127,	1000,'Tundish Car 1',0)
				,(1128,	1000,'Tundish Car 2',0)
				,(1129,	1000,'General',0)


			,(2000,	1,		'Caster 2',1)
				,(2100,	2000,'EMS Stirrer',0)
				,(2101,	2000,'Soft Reduction',0)
				,(2102,	2000,'Bloom Covers',0)
				,(2103,	2000,'Closed Machine Cooling',0)
				,(2104,	2000,'CTM',0)
				,(2105,	2000,'Ducking Rolls',0)
				,(2106,	2000,'General',0)
				,(2107,	2000,'HMI',0)
				,(2108,	2000,'Main Hydraulics',0)
				,(2119,	2000,'Ladle Cover Manipulator',0)
				,(2110,	2000,'Level 2',0)
				,(2111,	2000,'Ladle Slide Gate',0)
				,(2112,	2000,'Grease Systems',0)
				,(2113,	2000,'North Tagger',0)
				,(2114,	2000,'South Tagger',0)
				,(2115,	2000,'Mold Lube System',0)
				,(2116,	2000,'Mold Oscillator Hydrauics',0)
				,(2117,	2000,'PLC',0)
				,(2118,	2000,'Runout Table 1',0)
				,(2129,	2000,'Runout Table 2',0)
				,(2120,	2000,'Discharge Table',0)
				,(2121,	2000,'Mold Level',0)
				,(2122,	2000,'Walking Bed',0)
				,(2123,	2000,'Turret',0)
				,(2124,	2000,'Strand 5 Straighteners',0)
				,(2125,	2000,'Strand 6 Straighteners',0)
				,(2126,	2000,'Strand 8 Straighteners',0)
				,(2127,	2000,'Strand 9 Straighteners',0)
				,(2128,	2000,'Torch Cutting Machines',0)
					


			,(3000,	1,'LMF',1)
				,(3100,	3000,'1A Belt',0)
				,(3101,	3000,'1A-1 Belt',0)
				,(3102,	3000,'2A Belt',0)
				,(3103,	3000,'3A Belt',0)
				,(3104,	3000,'4A-1 Belt',0)
				,(3105,	3000,'4A-2 Belt',0)
				,(3106,	3000,'Alloy/Lime',0)
				,(3107,	3000,'Big Joe',0)
				,(3108,	3000,'Carbon Injection Silo',0)
				,(3119,	3000,'Center Car',0)
				,(3110,	3000,'General',0)
				,(3111,	3000,'Grizzly',0)
				,(3112,	3000,'Lime Silo',0)
				,(3113,	3000,'LMF1',0)
				,(3114,	3000,'LMF2',0)
				,(3115,	3000,'Main Hydraulics',0)
				,(3116,	3000,'Micro Alloy System',0)
				,(3117,	3000,'South Car',0)
				,(3118,	3000,'5th car',0)   
				   
			,(4000,	1,		'Pulse Jet Baghouse',1)
				,(4100,	4000,'800 HP fans',0)
				,(4101,	4000,'CEMS System',0)
				,(4102,	4000,'General',0)
				,(4103,	4000,'LMF Booster Fan',0)		

			,(5000,	1,		'Refractory',1)
				,(5100,	5000,'North Dryer',0)
				,(5101,	5000,'South Dryer',0)
				,(5102,	5000,'Caster 1 Preheaters',0)
				,(5103,	5000,'Caster 2 Preheaters',0)
				,(5104,	5000,'Ladle Wall',0)
				,(5105,	5000,'AMEPA',0)

			,(6000,	1,		'VTD',1)
				,(6100,	6000,'Boiler',0)
				,(6101,	6000,'Hot Well Pumps',0)
				,(6102,	6000,'Cold Well Pumps',0)
				,(6103,	6000,'General',0)
				,(6104,	6000,'U-Tube',0)
				,(6105,	6000,'HMI',0)
				,(6106,	6000,'PLC',0)
				,(6107,	6000,'Steam',0)
				,(6108,	6000,'Ejectors',0)





		SET IDENTITY_INSERT [MES].[catAssets] OFF














use LogBook

CREATE TABLE [LB].catStatusCodes (
    [StatusCodeId]       INT          IDENTITY (1, 1) NOT NULL,
    [StatusCode] CHAR(2)          NULL,
    [StatusCodeName]     VARCHAR (50) NOT NULL
);


insert into LB.catStatusCodes
values
('DO','Done')
,('NP','Need Parts')
,('TS','Turn/size change work')
,('DD','Down Day')




update lb.LogEntries
	set StatusCodeId = null
	,AssetId = (select assetId from lb.catAssets where assetName = 'Casters')
	where LogBookTypeCode = 'C'



	USE [LogBook]
GO
/****** Object:  UserDefinedFunction [LB].[IsAncestorOrSame]    Script Date: 14/2/2022 10:38:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [LB].[AssetIsAncestorOrSame]
(
	@EquipmentID INT,
	@PotentialAncestorID INT
)
RETURNS  BIT
AS
BEGIN
declare @return bit;

declare @TemplateID int 
declare @TemplateAncestorID int

select @TemplateID			= AssetId from LB.catAssets et where et.AssetId=@EquipmentID
select @TemplateAncestorID  = AssetId from LB.catAssets et where et.AssetId=@PotentialAncestorID

  ; with name_tree as (
   select AssetId, ParentAssetId
   from LB.catAssets 
   where AssetId = @TemplateID  -- this is the starting point you want in your recursion
   union all
   select c.AssetId, c.ParentAssetId
   from LB.catAssets   c
     join name_tree p on p.ParentAssetId = c.AssetId  -- this is the recursion
) 
select @return  = case when count(*)>0 then 1 else 0 end  from name_tree where AssetId = @TemplateAncestorID

return @return
END




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




CREATE TABLE [lb].catAssets (
    [AssetId]       INT          IDENTITY (1, 1) NOT NULL,
    [ParentAssetId] INT          NULL,
    [AssetName]     VARCHAR (50) NOT NULL,
    [IsAsset]       BIT          CONSTRAINT [DF_Assets_isAsset] DEFAULT ((1)) NOT NULL,
    CONSTRAINT [PK_Assets] PRIMARY KEY CLUSTERED ([AssetId] ASC)
);



	
	
	SET IDENTITY_INSERT [lb].[catAssets] ON 	
		INSERT INTO lb.catAssets(AssetId,ParentAssetId,AssetName,isAsset)
		values
		(1,	NULL,'Casters',1) 
			,(1000,	1,'Caster 1',1)
				,(1100,	1000,'Cross Transfer',0)
				,(1101,	1000,'Ducking Dog Pushers',0)
				,(1102,	1000,'Grease System',0)
				,(1103,	1000,'HMI',0)
				,(1104,	1000,'Hot Charge',0)
				,(1105,	1000,'Main Hydraulics',0)
				,(1106,	1000,'Pusher Hydraulics',0)
				,(1107,	1000,'Machine Water',0)
				,(1108,	1000,'Mold Level',0)
				,(1109,	1000,'Mold Lube',0)
				,(1110,	1000,'Mold Water',0)
				,(1111,	1000,'OS1 Panel',0)
				,(1112,	1000,'PLC',0)
				,(1113,	1000,'Runout Table 1',0)
				,(1114,	1000,'Runout Table 2',0)
				,(1115,	1000,'Ladle Slide Gate',0)
				,(1116,	1000,'Spray Chamber Fans',0)
				,(1117,	1000,'Strand 1 Straighteners',0)
				,(1118,	1000,'Strand 2 Straighteners',0)
				,(1119,	1000,'Strand 3 Straighteners',0)
				,(1120,	1000,'Strand 4 Straighteners',0)
				,(1121,	1000,'Strand 1 Weigh System',0)
				,(1122,	1000,'Strand 2 Weigh System',0)
				,(1123,	1000,'Strand 3 Weigh System',0)
				,(1124,	1000,'Strand 4 Weigh System',0)
				,(1125,	1000,'Tagging Machine',0)
				,(1126,	1000,'Torch Ducking Rolls',0)
				,(1127,	1000,'Tundish Car 1',0)
				,(1128,	1000,'Tundish Car 2',0)
				,(1129,	1000,'General',0)


			,(2000,	1,		'Caster 2',1)
				,(2100,	2000,'EMS Stirrer',0)
				,(2101,	2000,'Soft Reduction',0)
				,(2102,	2000,'Bloom Covers',0)
				,(2103,	2000,'Closed Machine Cooling',0)
				,(2104,	2000,'CTM',0)
				,(2105,	2000,'Ducking Rolls',0)
				,(2106,	2000,'General',0)
				,(2107,	2000,'HMI',0)
				,(2108,	2000,'Main Hydraulics',0)
				,(2119,	2000,'Ladle Cover Manipulator',0)
				,(2110,	2000,'Level 2',0)
				,(2111,	2000,'Ladle Slide Gate',0)
				,(2112,	2000,'Grease Systems',0)
				,(2113,	2000,'North Tagger',0)
				,(2114,	2000,'South Tagger',0)
				,(2115,	2000,'Mold Lube System',0)
				,(2116,	2000,'Mold Oscillator Hydrauics',0)
				,(2117,	2000,'PLC',0)
				,(2118,	2000,'Runout Table 1',0)
				,(2129,	2000,'Runout Table 2',0)
				,(2120,	2000,'Discharge Table',0)
				,(2121,	2000,'Mold Level',0)
				,(2122,	2000,'Walking Bed',0)
				,(2123,	2000,'Turret',0)
				,(2124,	2000,'Strand 5 Straighteners',0)
				,(2125,	2000,'Strand 6 Straighteners',0)
				,(2126,	2000,'Strand 8 Straighteners',0)
				,(2127,	2000,'Strand 9 Straighteners',0)
				,(2128,	2000,'Torch Cutting Machines',0)
					






		SET IDENTITY_INSERT [LB].[catAssets] OFF


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

		
Update FrontEnd.Routes
set [Route] =  '!/casting-log-book(/:start)(/:end)(/:mode)(/:crewId)(/:shiftId)(/:logEntryTypeId)(/:id)(/:casterProcessId)(/:searchText)'
where Route like '%casting-log-book%'


EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [casting_logbook_readWrite] ,@name = N'securedsp-LB.InsertCasterLogEntry'			  ,@value = N'null'


USE [LogBook]
GO
/****** Object:  StoredProcedure [LB].[InsertAutomaticCasterLogEntry]    Script Date: 20/2/2022 18:29:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [LB].[InsertCasterLogEntry] 
	@CasterProcessId INT

AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE @Call VARCHAR(max)
		,@procName VARCHAR(300)

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

	DECLARE @ProductionDate DATETIME
		,@CrewID CHAR(1)
		,@ShiftID CHAR(1)
		
	-----------------------
	-- Default Values
	-----------------------
	DECLARE @LadleOpenDatetime DATETIME = (select LadleOpen from mes.cas.Processes where Id = @CasterProcessId)
	DECLARE @Title VARCHAR(200) = 'Heat - ' + cast((select HeatName from mes.cas.Processes where Id = @CasterProcessId) as varchar)

	DECLARE @CasterName VARCHAR(50) = case when (select CasterCode from mes.cas.Processes where Id = @CasterProcessId) = 1 then 'Caster 1' else 'Caster 2' end

	DECLARE @AssetId INT = (select AssetId from lb.catAssets where [AssetName] = @CasterName)


	DECLARE @LogBookTypeCode char(1) = 'T'
	DECLARE @LogEntryTypeID INT = (select top 1 [Id] from lb.catLogEntryTypes where [Name] = 'Heat')

	SELECT @ProductionDate = gsd.ProductionDate
		,@CrewID = gsd.CrewID
		,@ShiftID = gsd.ShiftID
	FROM LB.GetShiftData(@LadleOpenDatetime) AS gsd
	-----------------------
	-- Execution
	-----------------------
	IF (select count(*) from lb.LogEntries where CasterProcessId = @CasterProcessId) = 0
	BEGIN

		INSERT INTO LogEntries ([ProductionDate],[ShiftID],[Time],[CrewID]
				,[LogEntryTypeID],[AssetId],[Title]
				,[CreateTimestamp],[LogBookTypeCode],[CasterProcessId]
				)
			VALUES (
				@ProductionDate
				, CASE WHEN @ShiftID = 1 THEN 'D' ELSE 'N' END
				,@LadleOpenDatetime
				,@CrewID
				,@LogEntryTypeID
				,@AssetId
				,@Title
				,GETUTCDATE()
				,@LogBookTypeCode
				,@CasterProcessId
				)
	END

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

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

		EXEC [LB].AuditMsg @message = @msg
			,@moduleName = @moduleName
			,@currentUser = ' Caster Heat Report'
	END TRY

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


	RETURN 0
END




/*---------------------------------------------------------------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------------------------------------------------------------*/
EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [casting_logbook_readWrite] ,@name = N'securedsp-LB.GetCasterLogBookPrint'   ,@value =  N'null'


USE [LogBook]
GO
/****** Object:  StoredProcedure [LB].[GetLogBook]    Script Date: 24/2/2022 00:25:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [LB].[GetCasterLogBookPrint] 
	
	@End DATETIME = 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 
	-----------------------
	-- Parameter defaults
	-----------------------
	IF @End is NULL SET @End = GETUTCDATE()
	DECLARE @Start DATETIME = DATEADD(DAY,-14,@End)
	DECLARE @timeZoneCode CHAR(10) = 'EDTIND'

		-----------------------
		-- Log Book -- CASTING
		-----------------------

			SELECT 
			
				[Datetime] = CONVERT(varchar, IH.ToLocal(le.[Time], @timeZoneCode), 120)
				,le.[CrewID]
				,le.[Title]				
				,le.[Description]
				,[TypeName] = let.[Name]
				,s.StatusCodeName
			
			FROM [LB].[LogEntries] le
			LEFT JOIN [LB].[catLogEntryTypes] let ON let.ID = le.LogEntryTypeID
			LEFT JOIN LB.catAssets ca on ca.AssetId = le.AssetId
			LEFT JOIN LB.catStatusCodes s on s.StatusCodeId = le.StatusCodeId
			WHERE 
			    le.ProductionDate BETWEEN @Start AND @End					
				AND 
				le.LogBookTypeCode = 'C'
				AND s.StatusCodeName <> 'Done' 
				AND le.StatusCodeId is not null
				
			order by ProductionDate asc

							


					

	RETURN 0
END

GO 
EXEC [LB].[GetCasterLogBookPrint]  '2022-02-13 00:00:00.000'