DECLARE @timeTolerance FLOAT = (SELECT CAST(Value AS FLOAT) FROM Setting WHERE Name = 'DataAnalysis.TimeTolerance')
DECLARE @lineID INT
DECLARE @startTime DATETIME2
DECLARE @endTime DATETIME2

SELECT
    @lineID = AssetID,
    @startTime = dbo.AdjustDateTime2(StartTime, -@timeTolerance),
    @endTime = dbo.AdjustDateTime2(EndTime, @timeTolerance)
FROM Event
WHERE ID = {0}

SELECT *
INTO #lineEvent
FROM Event
WHERE
    Event.AssetID = @lineID AND
    Event.EndTime >= @startTime AND
    Event.StartTime <= @endTime
    
SELECT
    ROW_NUMBER() OVER(PARTITION BY BreakerOperation.BreakerNumber ORDER BY BreakerOperation.TripCoilEnergized) AS BreakerOperationNumber,
    BreakerOperation.ID AS BreakerOperationID,
    Meter.AssetKey AS MeterKey,
    Location.Name AS StationName,
    Asset.AssetName AS LineName,
    BreakerOperation.TripCoilEnergized,
    BreakerOperation.BreakerNumber,
    Phase.Name AS Phase,
    BreakerOperation.BreakerTiming,
    BreakerOperation.StatusTiming,
    BreakerOperation.DcOffsetDetected,
    BreakerOperation.BreakerSpeed,
    BreakerOperation.StatusBitChatter,
    RIGHT(DataFile.FilePath, CHARINDEX(CHAR(92), REVERSE(DataFile.FilePath)) - 1) AS FileName,
    RIGHT(DataFile.FilePath, CHARINDEX(',', REVERSE(DataFile.FilePath)) - 1) AS ShortFileName,
    BreakerOperation.EventID,
    Event.StartTime AS EventStartTime,
    Event.EndTime AS EventEndTime
INTO #summaryData
FROM
    #lineEvent Event JOIN
    BreakerOperation ON BreakerOperation.EventID = Event.ID JOIN
    BreakerOperationType ON
        BreakerOperation.BreakerOperationTypeID = BreakerOperationType.ID AND
        BreakerOperationType.Name = 'Late' JOIN
    Phase ON BreakerOperation.PhaseID = Phase.ID JOIN
    DataFile ON DataFile.FileGroupID = Event.FileGroupID JOIN
    Meter ON Event.MeterID = Meter.ID JOIN
    Location ON Meter.LocationID = Location.ID JOIN
    Asset ON Event.AssetID = Asset.ID
WHERE
    DataFile.FilePath LIKE '%.DAT' OR
    DataFile.FilePath LIKE '%.D00' OR
    DataFile.FilePath LIKE '%.PQD' OR
    DataFile.FilePath LIKE '%.RCD' OR
    DataFile.FilePath LIKE '%.RCL' OR
    DataFile.FilePath LIKE '%.SEL' OR
    DataFile.FilePath LIKE '%.EVE' OR
    DataFile.FilePath LIKE '%.CEV'

DECLARE @url VARCHAR(MAX) = (SELECT Value FROM DashSettings WHERE Name = 'System.URL')

SELECT
    (
        SELECT ID AS [@id]
        FROM #lineEvent
        FOR XML PATH('Event'), TYPE
    ) AS [Events],
    (
        SELECT
            BreakerOperationNumber AS [@num],
            (
                SELECT
                    MeterKey,
                    StationName,
                    LineName,
                    BreakerNumber,
                    Phase,
                    TripCoilEnergized,
                    BreakerTiming,
                    StatusTiming,
                    DcOffsetDetected,
                    BreakerSpeed,
                    StatusBitChatter,
                    EventStartTime,
                    EventEndTime,
                    FileName,
                    ShortFileName,
                    EventID,
                    BreakerOperationID
                FROM #summaryData
                WHERE BreakerOperationNumber = BreakerOperation.BreakerOperationNumber
                FOR XML PATH('BreakerOperationData'), TYPE
            )
        FROM
        (
            SELECT DISTINCT BreakerOperationNumber
            FROM #summaryData
        ) BreakerOperation
        FOR XML PATH('BreakerOperation'), TYPE
    ) AS [BreakerOperations],
    Line.AssetName AS [Line/Name],
    Line.AssetKey AS [Line/AssetKey],
    FORMAT(Line.Length, '0.##########') AS [Line/Length],
    FORMAT(SQRT(Line.R1 * Line.R1 + Line.X1 * Line.X1), '0.##########') AS [Line/Z1],
    CASE Line.R1 WHEN 0 THEN '0' ELSE FORMAT(ATN2(Line.X1, Line.R1) * 180 / PI(), '0.##########') END AS [Line/A1],
    FORMAT(Line.R1, '0.##########') AS [Line/R1],
    FORMAT(Line.X1, '0.##########') AS [Line/X1],
    FORMAT(SQRT(Line.R0 * Line.R0 + Line.X0 * Line.X0), '0.##########') AS [Line/Z0],
    CASE Line.R0 WHEN 0 THEN '0' ELSE FORMAT(ATN2(Line.X0, Line.R0) * 180 / PI(), '0.##########') END AS [Line/A0],
    FORMAT(Line.R0, '0.##########') AS [Line/R0],
    FORMAT(Line.X0, '0.##########') AS [Line/X0],
    FORMAT(SQRT(POWER((2.0 * Line.R1 + Line.R0) / 3.0, 2) + POWER((2.0 * Line.X1 + Line.X0) / 3.0, 2)), '0.##########') AS [Line/ZS],
    CASE 2.0 * Line.R1 + Line.R0 WHEN 0 THEN '0' ELSE FORMAT(ATN2((2.0 * Line.X1 + Line.X0) / 3.0, (2.0 * Line.R1 + Line.R0) / 3.0) * 180 / PI(), '0.##########') END AS [Line/AS],
    FORMAT((2.0 * Line.R1 + Line.R0) / 3.0, '0.##########') AS [Line/RS],
    FORMAT((2.0 * Line.X1 + Line.X0) / 3.0, '0.##########') AS [Line/XS],
    @url AS [PQDashboard]
FROM
    Event JOIN
    LineView Line ON Event.AssetID = Line.ID
WHERE Event.ID = {0}
FOR XML PATH('EventDetail'), TYPE