USE [openXDA]
GO
/****** Object:  StoredProcedure [dbo].[selectSiteLinesDetailsByDate2]    Script Date: 3/10/2017 9:22:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      <Author, Jeff Walker>
-- Create date: <Create Date, Jun 23, 2014>
-- Description: <Description, Selects Events for a set of sites by Date>
-- selectSiteLinesDetailsByDate '2014-07-21', '140'

-- =============================================
CREATE PROCEDURE [dbo].[selectSiteLinesDetailsByDate2]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(4000)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @localEventDate DATE = CAST(@EventDate AS DATE)
    DECLARE @localMeterID INT = CAST(@MeterID AS INT)

    ; WITH cte AS
    (
        SELECT
            Event.LineID AS thelineid, 
            Event.ID AS theeventid, 
            EventType.Name AS theeventtype,
            CAST(Event.StartTime AS VARCHAR(26)) AS theinceptiontime,
            MeterLine.LineName + ' ' + [Line].[AssetKey] AS thelinename,
            Line.VoltageKV AS voltage,
            COALESCE(FaultSummary.FaultType, Phase.Name, '') AS thefaulttype,
            CASE WHEN FaultSummary.Distance = '-1E308' THEN 'NaN' ELSE COALESCE(CAST(CAST(FaultSummary.Distance AS DECIMAL(16, 4)) AS NVARCHAR(19)) + ' mi', '') END AS thecurrentdistance,
            dbo.EventHasImpactedComponents(Event.ID) AS pqiexists,
            Event.StartTime,
            CASE EventType.Name
                WHEN 'Sag' THEN ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY Magnitude, Disturbance.StartTime, IsSelectedAlgorithm DESC, IsSuppressed, Inception)
                WHEN 'Interruption' THEN ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY Magnitude, Disturbance.StartTime, IsSelectedAlgorithm DESC, IsSuppressed, Inception)
                WHEN 'Swell' THEN ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY Magnitude DESC, Disturbance.StartTime, IsSelectedAlgorithm DESC, IsSuppressed, Inception)
                WHEN 'Fault' THEN ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY IsSelectedAlgorithm DESC, IsSuppressed, IsValid DESC, Inception)
                ELSE ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY Event.ID)
            END AS RowPriority
        FROM
            Event JOIN
            EventType ON Event.EventTypeID = EventType.ID LEFT OUTER JOIN
            Disturbance ON Disturbance.EventID = Event.ID LEFT OUTER JOIN
            FaultSummary ON FaultSummary.EventID = Event.ID  LEFT OUTER JOIN
            Phase ON Disturbance.PhaseID = Phase.ID JOIN
            Meter ON Meter.ID = @MeterID JOIN
            Line ON Event.LineID = Line.ID JOIN
            MeterLine ON MeterLine.MeterID = @MeterID AND MeterLine.LineID = Line.ID
        WHERE
            CAST(Event.StartTime AS DATE) = @localEventDate AND
            Event.MeterID = @localMeterID AND
            (Phase.ID IS NULL OR Phase.Name <> 'Worst')
    )
    SELECT
        thelineid,
        theeventid,
        theeventtype,
        theinceptiontime,
        thelinename,
        voltage,
        thefaulttype,
        thecurrentdistance,
        pqiexists
    INTO #temp
    FROM cte
    WHERE RowPriority = 1
    ORDER BY StartTime

    DECLARE @sql NVARCHAR(MAX)
    SELECT @sql = COALESCE(@sql + ',dbo.' + HasResultFunction + '(theeventid) AS ' + ServiceName, 'dbo.' + HasResultFunction + '(theeventid) AS ' + ServiceName)
    FROM EASExtension

    SET @sql = 'SELECT *,' + @sql + ' FROM #temp'
    EXEC sp_executesql @sql

    DROP TABLE #temp

END