DECLARE @timeTolerance FLOAT = (SELECT CAST(Value AS FLOAT) FROM Setting WHERE Name = 'DataAnalysis.TimeTolerance')
DECLARE @assetID INT 
DECLARE @startTime DATETIME2
DECLARE @endTime DATETIME2 
SELECT  @assetID = AssetID, 
	@startTime = dbo.AdjustDateTime2(StartTime, -@timeTolerance),
	@endTime = dbo.AdjustDateTime2(EndTime, @timeTolerance)
FROM Event
WHERE ID = {0}

SELECT * INTO #systemEvent 
FROM Event 
WHERE     
	Event.EndTime >= @startTime AND
	Event.StartTime <= @endTime
	
SELECT * INTO #assetEvent
FROM Event
WHERE  
	Event.AssetID = @assetID AND 
	Event.EndTime >= @startTime AND
	Event.StartTime <= @endTime
	
SELECT  
	SQRT(R1 * R1 + X1 * X1) AS Z1,
	CASE R1 WHEN 0 THEN 0 ELSE ATN2(X1, R1) * 180 / PI() END AS A1,
	R1, 
	X1,
	SQRT(R0 * R0 + X0 * X0) AS Z0,
	CASE R0 WHEN 0 THEN 0 ELSE ATN2(X0, R0) * 180 / PI() END AS A0,
	R0,  
	X0, 
	SQRT(POWER((2.0 * R1 + R0) / 3.0, 2) + POWER((2.0 * X1 + X0) / 3.0, 2)) AS ZS,
	CASE 2.0 * R1 + R0 WHEN 0 THEN 0 ELSE ATN2((2.0 * X1 + X0) / 3.0, (2.0 * R1 + R0) / 3.0) * 180 / PI() END AS [AS],
	(2.0 * R1 + R0) / 3.0 AS RS,     (2.0 * X1 + X0) / 3.0 AS XS,
	AssetKey,
	Length
INTO #lineImpedance 
FROM LineView
WHERE ID = @assetID

SELECT 
	ROW_NUMBER() OVER(PARTITION BY Event.MeterID ORDER BY FaultSummary.Inception) AS FaultNumber,  
	FaultSummary.ID AS FaultSummaryID, 
	Meter.AssetKey AS MeterKey, 
	Meter.Make AS MeterMake,    
	Meter.Name AS MeterName, 
	Location.LocationKey as StationKey, 
	Location.Name AS StationName,
	Asset.AssetKey AS AssetKey, 
	Asset.AssetName AS AssetName,      
	FaultSummary.FaultType,  
	FaultSummary.Inception,  
	FaultSummary.DurationCycles,
	FaultSummary.DurationSeconds * 1000.0 AS DurationMilliseconds,  
	FaultSummary.PrefaultCurrent, 
	FaultSummary.PostfaultCurrent, 
	FaultSummary.ReactanceRatio, 
	FaultSummary.CurrentMagnitude AS FaultCurrent, 
	FaultSummary.Algorithm,
	FaultSummary.Distance AS SingleEndedDistance,
	DoubleEndedFaultSummary.Distance AS DoubleEndedDistance,
	DoubleEndedFaultSummary.Angle AS DoubleEndedAngle,
	RIGHT(DataFile.FilePath, CHARINDEX('\', REVERSE(DataFile.FilePath)) - 1) AS FileName,
	FaultCauseMetrics.TreeFaultResistance, 
	FaultCauseMetrics.LightningMilliseconds,  
	FaultCauseMetrics.InceptionDistanceFromPeak,   
	FaultCauseMetrics.PrefaultThirdHarmonic,   
	FaultCauseMetrics.GroundCurrentRatio,    
	FaultCauseMetrics.LowPrefaultCurrentRatio,
	FaultSummary.EventID,  
	Event.StartTime AS EventStartTime,   
	SimpleSummary.Distance AS Simple,
	ReactanceSummary.Distance AS Reactance, 
	Event.EndTime AS EventEndTime,
	CONCAT((SELECT TOP 1 VALUE FROM Setting WHERE Name = 'PQBrowser.URL'),'/eventsearch&#63;date&#61;',
					FORMAT(FaultSummary.Inception,'MM dd yyyy'),'&amp;time&#61;',FORMAT(FaultSummary.Inception,'HH:mm:ss.000'),
					'&amp;windowSize&#61;10&amp;timeWindowUnits&#61;2&amp;eventid&#61;', FaultSummary.EventID)
					AS PQBrowserLink,
	CASE FaultSummary.FaultType 
		WHEN 'AN' THEN 'A-phase to ground'
		WHEN 'BN' THEN 'B-phase to ground'
		WHEN 'CN' THEN 'C-phase to ground'
		WHEN 'AB' THEN 'A-phase to B-phase'
		WHEN 'BC' THEN 'B-phase to C-phase'
		WHEN 'CA' THEN 'C-phase to A-phase'
		WHEN 'ABG' THEN 'A-phase to B-phase to ground'
		WHEN 'BCG' THEN 'B-phase to C-phase to ground'
		WHEN 'CAG' THEN 'C-phase to A-phase to ground'
		WHEN 'ABC' THEN 'Three phase'         
		WHEN 'ABCG' THEN 'Three phase to ground'
		ELSE FaultSummary.FaultType     END LaymanFaultType
INTO #summaryData
FROM #assetEvent Event JOIN 
	FaultSummary ON FaultSummary.EventID = Event.ID AND   
		FaultSummary.IsSelectedAlgorithm <> 0 AND    
		FaultSummary.IsSuppressed = 0 LEFT OUTER JOIN 
	FaultSummary SimpleSummary ON         FaultSummary.EventID = SimpleSummary.EventID AND  
		FaultSummary.Inception = SimpleSummary.Inception AND  
		SimpleSummary.Algorithm = 'Simple' LEFT OUTER JOIN  
	FaultSummary ReactanceSummary ON         FaultSummary.EventID = ReactanceSummary.EventID AND
		FaultSummary.Inception = ReactanceSummary.Inception AND 
		ReactanceSummary.Algorithm = 'Reactance' LEFT OUTER JOIN 
	FaultCauseMetrics ON FaultSummary.EventID = FaultCauseMetrics.EventID AND FaultSummary.FaultNumber = FaultCauseMetrics.FaultNumber JOIN 
	DataFile ON DataFile.FileGroupID = Event.FileGroupID JOIN 
	Meter ON Event.MeterID = Meter.ID JOIN
	Location ON Meter.LocationID = Location.ID JOIN
	Asset ON Asset.ID = Event.AssetID LEFT OUTER JOIN 
	DoubleEndedFaultDistance ON DoubleEndedFaultDistance.LocalFaultSummaryID = FaultSummary.ID LEFT OUTER JOIN 
	DoubleEndedFaultSummary ON DoubleEndedFaultSummary.ID = DoubleEndedFaultDistance.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'  


SELECT DISTINCT FaultNumber INTO #FaultList FROM #summaryData

DECLARE @url VARCHAR(MAX) = (SELECT Value FROM DashSettings WHERE Name = 'System.URL')
	SELECT 
		( 
			SELECT
				FaultNumber AS [@num],
				(
				SELECT
					DoubleEndedDistance,
					Inception,
					StationKey,
					StationName,
					FaultType,
					EventID,
					FaultSummaryID,
					LaymanFaultType,
					DurationCycles,
					DurationMilliseconds,
					SingleEndedDistance,
					MeterKey,
					MeterName,
					MeterMake,
					FaultCurrent,
					DoubleEndedAngle,
					LowPrefaultCurrentRatio,
					LightningMilliseconds,
					InceptionDistanceFromPeak,
					PrefaultThirdHarmonic,
					Algorithm,
					GroundCurrentRatio,
					RIGHT(FileName, 8) AS ShortFileName,
					PQBrowserLink,
					CASE WHEN ABS(#lineImpedance.ZS * Simple) >= ABS(#lineImpedance.XS * Reactance)            
						THEN FORMAT((SQRT(POWER(#lineImpedance.ZS * Simple, 2) - POWER(#lineImpedance.XS * Reactance, 2)) - #lineImpedance.RS * Reactance) / #lineImpedance.Length, '0.##########')          
						ELSE '0.0'  
					END AS TreeResistance,      
					CASE WHEN ABS(#lineImpedance.ZS * Simple) < ABS(#lineImpedance.XS * Reactance)                     
						THEN 1        
						ELSE 0
					END AS TreeResistanceError
				FROM #summaryData LEFT JOIN #lineImpedance ON #summaryData.AssetKey = #lineImpedance.AssetKey
				WHERE #summaryData.FaultNumber = Fault.FaultNumber FOR XML PATH('Summary'), TYPE
				) AS Summaries
			FROM #FaultList Fault FOR XML PATH('Fault'), TYPE
		) AS Faults,
		FORMAT(#lineImpedance.Length, '0.##########') AS [Line/Length],
		FORMAT(#lineImpedance.Z1, '0.##########') AS [Line/Z1],
		FORMAT(#lineImpedance.R1, '0.##########') AS [Line/R1],
		FORMAT(#lineImpedance.X1, '0.##########') AS [Line/X1],
		FORMAT(#lineImpedance.A1, '0.##########') AS [Line/A1],
		FORMAT(#lineImpedance.Z0, '0.##########') AS [Line/Z0],
		FORMAT(#lineImpedance.R0, '0.##########') AS [Line/R0],
		FORMAT(#lineImpedance.X0, '0.##########') AS [Line/X0],
		FORMAT(#lineImpedance.A0, '0.##########') AS [Line/A0],
		FORMAT(#lineImpedance.XS, '0.##########') AS [Line/XS],
		FORMAT(#lineImpedance.ZS, '0.##########') AS [Line/ZS],
		FORMAT(#lineImpedance.[AS], '0.##########') AS [Line/AS],
		FORMAT(#lineImpedance.RS, '0.##########') AS [Line/RS],
		Asset.AssetKey AS [Asset/AssetKey],
                Asset.AssetName AS [Asset/AssetName],
		Asset.Description AS [Asset/Description],
		(SELECT TOP 1 VALUE FROM Setting WHERE Name = 'PQBrowser.URL') AS [System/PQBrowser],
		(SELECT TOP 1 VALUE FROM Setting WHERE Name = 'Notifications.URL') AS [System/Notifications],
		(   SELECT COALESCE(Point, '')   FROM EDNAPoint   WHERE BreakerID = Asset.ID   FOR XML PATH('Point'), TYPE  ) AS [EDNA]
	FROM    
		Event JOIN   
		Asset ON Event.AssetID = Asset.ID lEFT JOIN #lineImpedance ON #lineImpedance.AssetKey = Asset.AssetKey
		WHERE Event.ID = {0}
		FOR XML PATH('EventDetail'), TYPE