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

	DECLARE @startDate DATETIME = @EventDate 
	DECLARE @endDate DATETIME

    IF @context = '180d'
    BEGIN
		SET @startDate = DATEADD(HOUR, -180, @EventDate)
		SET @endDate = @EventDate
    END

	IF @context = '90d'
    BEGIN
		SET @startDate = DATEADD(DAY, -90, @EventDate)
		SET @endDate = @EventDate
    END

    IF @context = '30d'
    BEGIN
		SET @startDate = DATEADD(DAY, -30, @EventDate)
		SET @endDate = @EventDate
    END

    IF @context = '7d'
    BEGIN
		SET @startDate = DATEADD(DAY, -7, @EventDate)
		SET @endDate = @EventDate
    END

	IF @context = '24h'
	BEGIN
		SET @startDate = DATEADD(HOUR, -24, @EventDate)
		SET @endDate = @EventDate
	END

    IF @context = 'day'
    BEGIN
        SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @endDate = DATEADD(DAY, 1, @startDate)
    END

    if @context = 'hour'
    BEGIN
        SET @startDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, @EventDate), 0)
        SET @endDate = DATEADD(HOUR, 1, @startDate)
    END

    if @context = 'minute'
    BEGIN
        SET @startDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @EventDate), 0)
        SET @endDate = DATEADD(MINUTE, 1, @startDate)
    END

    if @context = 'second'
    BEGIN
        DECLARE @tempDate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @startDate = DATEADD(SECOND, DATEDIFF(SECOND, @tempDate, @EventDate), @tempDate)
        SET @endDate = DATEADD(SECOND, 1, @startDate)
    END


	DECLARE @simStartDate DATETIME = DATEADD(SECOND, -5, @startDate)
	DECLARE @simEndDate DATETIME = DATEADD(SECOND, 5, @endDate)
	print @simStartDate
	print @simEndDate
    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,
			(SELECT COUNT(*) FROM Event as EventCount WHERE EventCount.StartTime BETWEEN DateAdd(SECOND, -5, Event.StartTime) and  DateAdd(SECOND, 5, Event.StartTime)) as SimultaneousCount,
			(SELECT COUNT(*) FROM Event as EventCount WHERE EventCount.LineID = Event.LineID AND EventCount.StartTime BETWEEN DateAdd(Day, -60, Event.StartTime) and  Event.StartTime) as SixtyDayCount,
			Event.UpdatedBy,
			(SELECT COUNT(*) FROM EventNote WHERE EventID = Event.ID) as Note
        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
            Event.StartTime >= @startDate AND Event.StartTime < @endDate AND 
            Event.MeterID = @localMeterID AND
            (Phase.ID IS NULL OR Phase.Name <> 'Worst')
    )
    SELECT
        thelineid,
        theeventid,
        theeventtype,
        theinceptiontime,
        thelinename,
        voltage,
        thefaulttype,
        thecurrentdistance,
        pqiexists,
		SimultaneousCount,
		SixtyDayCount,
		UpdatedBy,
		Note
    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

	DECLARE @serviceList NVARCHAR(MAX)
	SELECT @serviceList = COALESCE(@serviceList + ',' + ServiceName, ServiceName)
	FROM EASExtension
	Set @serviceList = '''' + @serviceList + ''''
	
	
    SET @sql = COALESCE('SELECT *,' + @sql + ', '+ @ServiceList +'as ServiceList FROM #temp', 'SELECT *, '''' AS ServiceList FROM #temp')
    print @sql
	EXEC sp_executesql @sql

    DROP TABLE #temp
	
END