USE [IHBoxSystem]
GO

delete from [FrontEnd].[Routes] where [ModuleName]='delays-4'
INSERT INTO [FrontEnd].[Routes]
           ([Route]
           ,[ModuleName]
           ,[ModulePath]
       )
     VALUES
           (
		   '!/delays/delays4'
           , 'delays-4'
		   , 'custom-screens/delays/delays-4/'    
		   )

GO


USE [IHDelays2]
GO
/****** Object:  StoredProcedure [WebApp].[GetDelaysTimeSummary]    Script Date: 9/19/2023 5:48:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [WebApp].[GetDelaysTimeSummary]
	@ProductionItemId INT = NULL,
	@ShiftId INT = NULL, 
	@CrewId CHAR(1) = NULL, 
	@ProductId INT = NULL, 
	@From DATETIME,
	@To DATETIME, 
	@Timezone VARCHAR(10),

	@delayCategoryId INT = NULL
AS
BEGIN

		SET @From = dateadd(HOUR, 9, @From)
		SET	@To = dateadd(HOUR, 9, @To)

		DECLARE @now DATETIME = GETUTCDATE()
		DECLARE @fromUTC DATETIME, @toUTC DATETIME

		SET @fromUTC = dbo.IHBoxSystem_ToUTC(@From, @Timezone)
		SET @toUTC = dbo.IHBoxSystem_ToUTC(@To, @Timezone)


		SELECT
			d.[Id], 
			d.[DelayCodeId], 
			[DelayCodeParentId] = dc.ParentId, 
			[Start] = CASE WHEN d.[Start] < @fromUTC THEN @fromUTC ELSE d.[Start] END, 
			[End] = ISNULL(CASE WHEN d.[End] > @toUTC THEN @toUTC ELSE d.[End] END, CASE WHEN @now > @toUTC THEN @toUTC ELSE @now END), 
			d.[Comments], 
			[ForceIncludeInAvailability] = d.ForceIncludeInAvailability, 
			[ExcludeFromAvailability] = CONVERT(BIT, ISNULL(dc.ExcludeFromAvailability, 0)),
			[ProductionItem] = pitems.[Name]
		INTO #delays
		FROM PRD.Delays d WITH(NOLOCK)
		JOIN CAT.ProductionItems pitems ON pitems.Id = d.MachineProductionItemId
		LEFT JOIN PRD.Delays children WITH(NOLOCK) ON children.ParentId = d.Id
		LEFT JOIN CAT.DelayCodes dc ON dc.Id = d.DelayCodeId
		LEFT JOIN CAT.Shifts shifts ON shifts.Id = d.ShiftId
		LEFT JOIN PRD.[Batches] bs ON bs.Id = d.BatchId
		LEFT JOIN CAT.Products p ON p.Id = bs.ProductId
		CROSS APPLY MES.[system].GetShiftDataForDatetime(d.Start) SD
		WHERE
		d.[Start]  < @toUTC and d.[End] > @fromUTC
		AND 
		(
			@ProductionItemId IS NULL
			OR
			(pitems.Id = @ProductionItemId OR pitems.ParentId = @ProductionItemId)
		)
		AND (@ShiftId IS NULL OR shifts.Id = @ShiftId)
		AND (@CrewId IS NULL OR  sd.CrewId = @CrewId)
		AND (@ProductId IS NULL OR p.Id = @ProductId)
		AND children.Id IS NULL
		AND d.Deleted <> 1
		-- if it has shift then it's a valid delay
		AND d.ShiftId IS NOT NULL

		SELECT
			[Id] = dc.Id, 
			[Code] = MAX(dc.Code),
			--[Category] = MAX(c.Name),
			[Category] = MAX(c.[Name]),

			[Description] = MAX(c.[Description]), 
			[Color] = MAX(c.Color), 
			[Duration] = ISNULL(SUM(ISNULL(DATEDIFF(SECOND, d.[Start], d.[End]), 0)), 0),
			[ProductionItem] = MAX(d.ProductionItem)
			,[Count] = count(*)
		INTO #delayCodesDuration
		FROM CAT.DelayCodes dc
		LEFT JOIN CAT.DelayCodes children ON children.ParentId = dc.Id
		LEFT JOIN #delays d ON d.[DelayCodeId] = dc.Id
		LEFT JOIN CAT.SRDCategories c on c.DelayCodeId = IHDelays2.[dbo].[GetRootParentByDelayCodeId](dc.Id)     --------------------------------> new
		--LEFT JOIN CAT.DelayCategories c ON c.Id = dc.CategoryId --------------------------------> old 
		WHERE dc.isActive = 1
		AND dc.Internal = 0
		AND children.Id IS NULL
		AND (@delayCategoryId IS NULL OR c.SRDCategoryId = @delayCategoryId)     --------------------------------> new ----> remember that @delayCategoryId has a wrong name
		--AND (@delayCategoryId IS NULL OR c.Id = @delayCategoryId)     --------------------------------> old 
		-- excluding delays that do not alter availability
		AND (dc.ExcludeFromAvailability = 0 OR d.ForceIncludeInAvailability = 1)
		AND d.Id is not null

		GROUP BY dc.Id
		UNION ALL
		SELECT
		[Id] = -1, 
		[Code] = 'NOTDECLARED', 
		[Category] = 'NOTDECLARED', 
		[Description] = 'Not Declared', 
		[Color] = '#f84e4e', 
		[Duration] = ISNULL((SELECT SUM(ISNULL(DATEDIFF(SECOND, d.[Start], d.[End]), 0))
					  FROM #delays d
					  WHERE d.DelayCodeId IS NULL
					  GROUP BY d.DelayCodeParentId), 0)
		,[ProductionItem] = 'NOTDECLARED'
		,[Count] = count(*)
		-- Removed as requested by the customer

		--UNION ALL
		--SELECT
		--[Id] = -3, 
		--[Code] = 'MICRODELAY', 
		--[Category] = 'MICRODELAY', 
		--[Description] = 'Micro Delay', 
		--[Color] = '#008CFF', 
		--[Duration] = (SELECT ISNULL(SUM(DATEDIFF(SECOND, d.[Start], d.[End])), 0)
		--			  FROM #delays d 
		--			  WHERE d.DelayCodeId = dbo.GetMicroDelayCodeId())

		ORDER BY Code ASC

		SELECT
		[Id] = dcd.Id, 
		[Code] = dcd.Code, 
		[Category] = dcd.Category, 
		[Description] = dcd.[Description],
		[Color] = dcd.Color, 
		[Duration] = dcd.Duration
		,[DurationDaysAndTime] = CONVERT(varchar, dcd.Duration / 86400 ) + ' ' + -- Days
			CONVERT(varchar, DATEADD(ms, ( dcd.Duration % 86400 ) * 1000, 0), 108)	
		,[Count] = dcd.[Count]
		,[ProductionItem] = dcd.ProductionItem
		FROM #delayCodesDuration dcd
		ORDER BY dcd.Duration DESC

		DROP TABLE #delayCodesDuration
		DROP TABLE #delays

END


