
---------------------------------------
--- BACKUP For versioning
---------------------------------
USE [IHDelays2]
GO
/****** Object:  StoredProcedure [WebApp].[GetDelays_bak]    Script Date: 9/19/2023 10:20:32 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [WebApp].[GetDelays_bak]

@From DATETIME = NULL, 
@To DATETIME = NULL, 
@ProductionItemId INT = NULL, 
@Timezone VARCHAR(10) = 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

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)

SET @From = CAST(FLOOR(CAST(@From AS FLOAT)) AS DATETIME)
SET @To = CAST(FLOOR(CAST(@To AS FLOAT)) AS DATETIME)
SET @To = DATEADD(SECOND, 59, DATEADD(MINUTE, 59, DATEADD(HOUR, 23, @To)))

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

SELECT * FROM
(
SELECT
[Id] = d.Id, 
[ParentId] = d.ParentId, 
[Line] = pitemsparents.Name, 
[ProductionItem] = pitems.Name, 
[Start] = CONVERT(VARCHAR,dbo.IHBoxSystem_ToLocal(d.Start, @Timezone), 120), 
[End] = CONVERT(VARCHAR,dbo.IHBoxSystem_ToLocal(d.[End], @Timezone), 120), 
[Duration] = dbo.GetHHMMSSFromSeconds(DATEDIFF(SECOND, d.Start, d.[End])), 
[Seconds] = DATEDIFF(SECOND, d.Start, d.[End]), 
[Code] = ISNULL(dc.Code, 'NOTDECLARED'),
[Comments] = d.Comments, 
[BatchNumber] = bs.Number, 
[RowNum] = ROW_NUMBER() OVER (ORDER BY d.Start DESC, pitems.Id DESC)
FROM PRD.Delays d WITH(NOLOCK)
LEFT JOIN PRD.Delays children WITH(NOLOCK) ON children.ParentId = d.Id
JOIN CAT.ProductionItems pitems ON pitems.Id = d.MachineProductionItemId
LEFT JOIN CAT.ProductionItems pitemsparents ON pitems.ParentId = pitemsparents.Id
JOIN CAT.ProductionItemTypes pitypes ON pitypes.Id = pitems.ProductionItemTypeId
LEFT JOIN CAT.DelayCodes dc ON dc.Id = d.DelayCodeId
LEFT JOIN PRD.Batches bs ON bs.Id = d.BatchId
WHERE d.Deleted = 0
AND children.Id IS NULL
AND ((d.Start >= @fromUTC and d.[Start] <= @toUTC)
OR (d.[End] >= @fromUTC and d.[End] <= @toUTC)
OR (d.[Start] <= @fromUTC and d.[End] >= @toUTC))
AND ((pitems.Id = @ProductionItemId OR pitems.ParentId = @ProductionItemId) OR @ProductionItemId IS NULL)
AND d.ShiftId IS NOT NULL
) as records
WHERE
	((records.RowNum >= @fromRow
	AND
	records.RowNum < @toRow)
	OR (@fromRow = -1 AND @toRow = -1))
ORDER BY
	records.RowNum

END




--------------------------------------------------------------------------
----LAST  VERSION
-------------------------------------------------

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

@From DATETIME = NULL, 
@To DATETIME = NULL, 
@ProductionItemId INT = NULL, 
@Timezone VARCHAR(10) = NULL, 
@ShiftId INT = NULL, 
@CrewId CHAR(1) = NULL, 
@ProductId INT = 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
@delayCategoryId INT = NULL
AS
BEGIN

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)

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 * FROM
(
SELECT
			d.[Id], 
			d.[DelayCodeId], 
			[ParentId] = d.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), 	
			[Code] = ISNULL(dc.Code, 'NOTDECLARED'),
			[Duration] = dbo.GetHHMMSSFromSeconds(DATEDIFF(SECOND, d.Start, d.[End])), 
			[Seconds] = DATEDIFF(SECOND, d.Start, d.[End]), 
			d.[Comments],
			[CodeGroup] = ISNULL(c.[Name], 'NOTDECLARED'),
			[ProductionItem] = pitems.[Name],
			[Shift] = shifts.[Name],
			[Crew] =  SD.CrewId,
			[RowNum] = ROW_NUMBER() OVER (ORDER BY d.Start DESC, pitems.Id DESC)
		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.DelayCodes dcChild ON dcChild.ParentId = dc.Id
		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
		LEFT JOIN CAT.SRDCategories c on c.DelayCodeId = IHDelays2.[dbo].[GetRootParentByDelayCodeId](dc.Id)
		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
		AND (@delayCategoryId IS NULL OR c.SRDCategoryId = @delayCategoryId) 
		) as records
		WHERE
		((records.RowNum >= @fromRow
		AND
		records.RowNum < @toRow)
		OR (@fromRow = -1 AND @toRow = -1))
	ORDER BY
		records.RowNum

END