USE [IHDelays2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [WebApp].[GetDelaysTimeSummaryByDate]

@ProductionItemId INT = NULL,
@ShiftId INT = NULL, 
@ProductId INT = NULL, 
@From DATETIME,
@To DATETIME, 
@Timezone VARCHAR(10),

@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

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

DECLARE @now DATETIME = GETUTCDATE()
DECLARE @fromUTC DATETIME, @toUTC DATETIME
SET @fromUTC = dbo.IHBoxSystem_ToUTC(@From, @Timezone)
SET @toUTC = dbo.IHBoxSystem_ToUTC(@To, @Timezone)

DECLARE @daysT TABLE ([Start] DATETIME, [End] DATETIME)


INSERT INTO @daysT
SELECT 
	Start =  dbo.IHBoxSystem_ToUTC([Timestamp], @Timezone),
	[End] = DATEADD(DAY, 1, dbo.IHBoxSystem_ToUTC([Timestamp], @Timezone))
FROM dbo.GetTimeRange(@fromUTC, @toUTC, 'DAY')

SELECT
	@fromUTC = MIN(Start),
	@toUTC = MAX([End])
FROM @daysT


SELECT
d.[DelayCodeId], 
[DelayCodeParentId] = dc.ParentId, 
d.Start,
d.[End],
d.[Comments], 
[ForceIncludeInAvailability] = d.ForceIncludeInAvailability, 
[ExcludeFromAvailability] = CONVERT(BIT, ISNULL(dc.ExcludeFromAvailability, 0))
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
WHERE
((d.Start >= @fromUTC and d.[Start] <= @toUTC)
OR (d.[End] >= @fromUTC and d.[End] <= @toUTC)
OR (d.[Start] <= @fromUTC and d.[End] >= @toUTC))
AND 
(
	@ProductionItemId IS NULL
	OR
	(pitems.Id = @ProductionItemId OR pitems.ParentId = @ProductionItemId)
)
AND (@ShiftId IS NULL OR shifts.Id = @ShiftId)
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
	[Date] = SUBSTRING(CONVERT(VARCHAR, dateRow.[Start], 121), 1, 10),
	[Category] = MAX(c.Name),
	[Description] = MAX(c.[Description]),
	[Duration] = ISNULL(SUM(ISNULL(DATEDIFF(
					SECOND, 
					CASE WHEN d.[Start] < dateRow.Start THEN dateRow.Start ELSE d.[Start] END,
					ISNULL(CASE WHEN d.[End] > dateRow.[End] THEN dateRow.[End] ELSE d.[End] END, CASE WHEN @now > dateRow.[End] THEN dateRow.[End] ELSE @now END)
				), 0)), 0)
INTO #temp
FROM @daysT dateRow,
	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.DelayCategories c ON c.Id = dc.CategoryId
WHERE 
	(
		d.Start BETWEEN dateRow.Start AND dateRow.[End]
		or
		d.[End] BETWEEN dateRow.Start AND dateRow.[End]
	)
	AND dc.isActive = 1
	AND dc.Internal = 0
	AND children.Id IS NULL
	-- excluding delays that do not alter availability
	AND (dc.ExcludeFromAvailability = 0 OR d.ForceIncludeInAvailability = 1)
GROUP BY 
	dateRow.[Start], c.Id
UNION ALL
SELECT
	[Date] = SUBSTRING(CONVERT(VARCHAR, dateRow.[Start], 121), 1, 10),
	[Category] = 'NOTDECLARED', 
	[Description] = 'Not Declared', 
	[Duration] = ISNULL(SUM(ISNULL(DATEDIFF(
					SECOND, 
					CASE WHEN d.[Start] < dateRow.Start THEN dateRow.Start ELSE d.[Start] END,
					ISNULL(CASE WHEN d.[End] > dateRow.[End] THEN dateRow.[End] ELSE d.[End] END, CASE WHEN @now > dateRow.[End] THEN dateRow.[End] ELSE @now END)
				), 0)), 0)
FROM @daysT dateRow,
	#delays d
WHERE 
	(
		d.Start BETWEEN dateRow.Start AND dateRow.[End]
		or
		d.[End] BETWEEN dateRow.Start AND dateRow.[End]
	)
	AND
	d.DelayCodeId IS NULL
GROUP BY 
	dateRow.[Start], d.DelayCodeParentId
SELECT
	*,
	RowNum = ROW_NUMBER() OVER (ORDER BY [Date] DESC)
INTO #temp2
FROM (SELECT DISTINCT [Date] FROM #temp) AS record


SELECT
	temp1.*
FROM #temp temp1
	JOIN #temp2 record ON record.[Date] = temp1.[Date]
WHERE
	@rowsToFetch = -1
	OR
	(
		record.RowNum >= @fromRow
		AND 
		record.RowNum <= @toRow
	)
ORDER BY 
	record.RowNum ASC,
	[Description] ASC
	

DROP TABLE #delays
DROP TABLE #temp
DROP TABLE #temp2

END




----------------------------------------------------------
--- New Ticket: 390 Added By new Categories
----------------------------------------------------------
USE [IHDelays2]
GO
/****** Object:  StoredProcedure [WebApp].[GetDelaysTimeSummaryByDate_new]    Script Date: 9/21/2023 11:47:21 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER PROCEDURE [WebApp].[GetDelaysTimeSummaryByDateAndCodeGroup]

@ProductionItemId INT = NULL,
@ShiftId INT = NULL, 
@ProductId INT = NULL, 
@From DATETIME,
@To DATETIME, 
@Timezone VARCHAR(10),

@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
@delayCategoryId INT = NULL --
AS
BEGIN

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

DECLARE @now DATETIME = GETUTCDATE()
DECLARE @fromUTC DATETIME, @toUTC DATETIME
SET @fromUTC = dbo.IHBoxSystem_ToUTC(@From, @Timezone)
SET @toUTC = dbo.IHBoxSystem_ToUTC(@To, @Timezone)

DECLARE @daysT TABLE ([Start] DATETIME, [End] DATETIME)


INSERT INTO @daysT
SELECT 
	Start =  dbo.IHBoxSystem_ToUTC([Timestamp], @Timezone),
	[End] = DATEADD(DAY, 1, dbo.IHBoxSystem_ToUTC([Timestamp], @Timezone))
FROM dbo.GetTimeRange(@fromUTC, @toUTC, 'DAY')

SELECT
	@fromUTC = MIN(Start),
	@toUTC = MAX([End])
FROM @daysT


SELECT
d.[DelayCodeId], 
[DelayCodeParentId] = dc.ParentId, 
d.Start,
d.[End],
d.[Comments], 
[ForceIncludeInAvailability] = d.ForceIncludeInAvailability, 
[ExcludeFromAvailability] = CONVERT(BIT, ISNULL(dc.ExcludeFromAvailability, 0))
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
WHERE
((d.Start >= @fromUTC and d.[Start] <= @toUTC)
OR (d.[End] >= @fromUTC and d.[End] <= @toUTC)
OR (d.[Start] <= @fromUTC and d.[End] >= @toUTC))
AND 
(
	@ProductionItemId IS NULL
	OR
	(pitems.Id = @ProductionItemId OR pitems.ParentId = @ProductionItemId)
)
AND (@ShiftId IS NULL OR shifts.Id = @ShiftId)
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
	[Date] = SUBSTRING(CONVERT(VARCHAR, dateRow.[Start], 121), 1, 10),
	[Category] = MAX(c.Name),
	[Description] = MAX(c.[Description]),
	[Duration] = ISNULL(SUM(ISNULL(DATEDIFF(
					SECOND, 
					CASE WHEN d.[Start] < dateRow.Start THEN dateRow.Start ELSE d.[Start] END,
					ISNULL(CASE WHEN d.[End] > dateRow.[End] THEN dateRow.[End] ELSE d.[End] END, CASE WHEN @now > dateRow.[End] THEN dateRow.[End] ELSE @now END)
				), 0)), 0)
INTO #temp
FROM @daysT dateRow,
	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) 
WHERE 
	(
		d.Start BETWEEN dateRow.Start AND dateRow.[End]
		or
		d.[End] BETWEEN dateRow.Start AND dateRow.[End]
	)
	AND dc.isActive = 1
	AND dc.Internal = 0
	AND children.Id IS NULL
	-- excluding delays that do not alter availability
	AND (dc.ExcludeFromAvailability = 0 OR d.ForceIncludeInAvailability = 1)
	AND (@delayCategoryId IS NULL OR c.SRDCategoryId = @delayCategoryId)
GROUP BY 
	dateRow.[Start], dc.Id
UNION ALL
SELECT
	[Date] = SUBSTRING(CONVERT(VARCHAR, dateRow.[Start], 121), 1, 10),
	[Category] = 'NOTDECLARED', 
	[Description] = 'Not Declared', 
	[Duration] = ISNULL(SUM(ISNULL(DATEDIFF(
					SECOND, 
					CASE WHEN d.[Start] < dateRow.Start THEN dateRow.Start ELSE d.[Start] END,
					ISNULL(CASE WHEN d.[End] > dateRow.[End] THEN dateRow.[End] ELSE d.[End] END, CASE WHEN @now > dateRow.[End] THEN dateRow.[End] ELSE @now END)
				), 0)), 0)
FROM @daysT dateRow,
	#delays d
WHERE 
	(
		d.Start BETWEEN dateRow.Start AND dateRow.[End]
		or
		d.[End] BETWEEN dateRow.Start AND dateRow.[End]
	)
	AND
	d.DelayCodeId IS NULL
GROUP BY 
	dateRow.[Start], d.DelayCodeParentId


-- Rank date records specified on the pagination paramaters.
SELECT
	*,
	RowNum = ROW_NUMBER() OVER (ORDER BY [Date] DESC)
INTO #temp2
FROM (SELECT DISTINCT [Date] FROM #temp) AS record


SELECT
	temp1.*
FROM #temp temp1
	JOIN #temp2 record ON record.[Date] = temp1.[Date]
WHERE
	@rowsToFetch = -1
	OR
	(
		record.RowNum >= @fromRow
		AND 
		record.RowNum <= @toRow
	)
ORDER BY 
	record.RowNum ASC,
	[Description] ASC
	

DROP TABLE #delays
DROP TABLE #temp
DROP TABLE #temp2

END