USE [COMMONDB]
GO
/****** Object:  StoredProcedure [dbo].[GET_DEVICE_DATA]    Script Date: 10/01/2018 11:01:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GET_DEVICE_DATA] ( @database varchar(50), 
                                          @table varchar(200), 
                                          @value varchar(200), 
                                          @dtype varchar(50), 
                                          @starttime varchar(50), 
                                          @endtime varchar(50), 
                                          @aggregate varchar(50), 
                                          @interval varchar(10), 
                                          @timezone varchar(50) )
AS BEGIN

-- supported timezone handling on all request types
-- supported dtypes: bigint
-- supported aggregates: min, max, avg, spot, delta (by spot)
-- supported intervals: 1, 5, 15, 60, 1440 in minutes
-- function returns resutls in utc timestamps 
-- @author Milan Rompe
-- @version 2.3
--
-- @changed MR 2018-09-30 Added RAD_CELL_E_T for delta aggregate
-- @fixed   MR 2018-09-10 Fixed bug when finding the correct ppeak 
-- @changed MR 2018-08-13 Increased the precision of the delta table to make more precise calculations.
-- @changed MR 2018-05-31 Fixed delta bug caused by dls implementation. DLZ is false as default
-- @changed MR 2018-05-23 Fixed timezone bug when timezone has no day light saving. 
-- @changed MR 2018-02-08 Implemented virtual value RAD_E process ability
-- @changed MR 2017-12-07 Removed values < 0 for @value='COL_SR_196627' which are etoday yields
-- @changed MR 2017-11-21 Removed values < 0 for @table='xxx_plant_device' and @value='Energy_Yield' and aggregate delta
-- @changed MR 2017-04-05 Aggregate delta is now available for NORM_ values 

    SET NOCOUNT ON
    DECLARE @sql nvarchar(2000),
            @ParamDefinition nvarchar(500),
            @tsInterval int = 1,
            @utcSt datetime,
            @utcEt datetime,
            @st datetime,
            @et datetime,
            @plantTZOffset integer,
            @normPosInValue tinyint,
            @pPeak integer,
            @plant_yield_delta_filter nvarchar(50),
            @plantTZDayLightSaving varchar(10)    

    -- Fetch Day Light Saving        
    SET @ParamDefinition = '@dlsOUT varchar(10) OUTPUT'
    SET @sql = 'SELECT @dlsOUT = value 
                FROM '+ @database + '.dbo.xxx_plant_device_properties 
                WHERE Value_Key = ''plant.timezone.dst'''
    --print @sql
    EXECUTE sp_executesql
            @sql,
            @ParamDefinition,
            @dlsOUT = @plantTZDayLightSaving OUTPUT

    IF @plantTZDayLightSaving IS NULL BEGIN
        SET @plantTZDayLightSaving = 'true'
    END
            
    SET @normPosInValue = (SELECT CHARINDEX('NORM_', @value))
    
    SET @value = (SELECT REPLACE(@value, 'RAD_E_', ''))


    -- if there is a NORM_ at the beginning of the value the function calculates the 
    -- specific value of the power or yield
    IF @normPosInValue = 1 BEGIN
        SET @value = REPLACE(@value, 'NORM_', '')

        SET @ParamDefinition = '@ppeakOUT integer OUTPUT'
        
        SET @sql = 'SELECT @ppeakOUT = ISNULL(Fix_Value,1) FROM ' + @database + '.DBO.xxx_Virtual_Devices WHERE Table_Name='''+ @table + ''' AND Column_Name=''' + @value + ''''
        EXECUTE sp_executesql
            @sql,
            @ParamDefinition,
            @ppeakOUT = @pPeak OUTPUT
            
        IF @pPeak IS NULL BEGIN
        
            SET @sql = 'SELECT @ppeakOUT = ISNULL(Fix_Value,1) FROM ' + @database + '.DBO.xxx_Virtual_Devices 
                        WHERE Table_Name='''+ @table + ''' 
                        AND Device_Class=''P_AC'' 
                        AND Device_Class_2=''P_AC'''
            EXECUTE sp_executesql
                @sql,
                @ParamDefinition,
                @ppeakOUT = @pPeak OUTPUT
        END
        print 'ppeak: ' + cast(@ppeak AS varchar)            
    END 

    -- Fetch Timezone    
    SET @ParamDefinition = '@tsOUT integer OUTPUT'
    SET @sql = 'SELECT @tsOUT = cast(value AS integer) FROM '+ @database + '.dbo.xxx_plant_device_properties WHERE Value_Key = ''Time_Zone_Offset'''
    EXECUTE sp_executesql
            @sql,
            @ParamDefinition,
            @tsOUT = @plantTZOffset OUTPUT

    print 'TZ Offset: ' + cast(@plantTZOffset AS nvarchar(100))

    IF @timezone = 'plant' BEGIN
        -- we need to transform the timestamp into utc timestamp
        -- before we can do that we need to grep the plant time offset

        SET @utcSt = COMMONDB.DBO.GET_UTC_DATE_FROM_TZ_DATE_2(@starttime, @plantTZOffset, @plantTZDayLightSaving)
        SET @utcEt = COMMONDB.DBO.GET_UTC_DATE_FROM_TZ_DATE_2(@endtime, @plantTZOffset, @plantTZDayLightSaving)
        print 'plantTZ: ' + @starttime + ' UTC: ' + convert(varchar, @utcSt, 120)
        print 'plantTZ: ' + @endtime + ' UTC: ' + convert(varchar, @utcEt, 120)

        SET @st = CONVERT(datetime, @starttime, 21)
        SET @et = CONVERT(datetime, @endtime, 21)
      END
    ELSE BEGIN
        IF upper(@timezone) = 'UTC' BEGIN
           -- if timestamp is in UTC we dont need to do anything with the timestamp
           SET @utcSt = CONVERT(datetime, @starttime, 21)
           SET @utcEt = CONVERT(datetime, @endtime, 21)
           SET @st = @utcSt
           SET @et = @utcEt
        END
    END
    -------------------------------------------------------------
    -- create time series for the request.
    -- time series is inserted into a temptable called #datetable

    IF 'spot' = @aggregate OR 'delta' = @aggregate BEGIN
        SET @tsInterval = CAST(@interval AS integer);
    END 
 
    ;WITH cSequence AS
    (
        SELECT
           @st AS date,
           DATEADD(MINUTE, @tsInterval, @St) AS et
        UNION ALL
        SELECT
          et,
          DATEADD(MINUTE, @tsInterval, et)
        FROM cSequence
        WHERE DATEADD(MINUTE, @tsInterval, et) <= @et
    )

    SELECT date INTO #dateTable FROM cSequence OPTION (MAXRECURSION 0);
	
    
    --SELECT * FROM #dateTable ORDER BY date ASC
    
    --print 'utcST: ' + convert(nvarchar, @utcSt, 120)
    --print 'utcET: ' + convert(nvarchar, @utcEt, 120)

    -------------------------------------------------------------

    -- setup the temp table. The dtype argument is the data type
    -- of the value column

    --IF 'bigint' = @dtype BEGIN
    CREATE TABLE #valueDataTable (
        date datetime,
        value bigint
    )

    -- fetches data from device table
    -- transform into plant time zone before inserting the data
    SET @sql = 'INSERT INTO #valueDataTable
                       SELECT COMMONDB.DBO.GET_TZ_DATE_FROM_UTC_DATE_2(Date_and_Time , ' + CAST(@plantTZOffset AS varchar(10) )+ ', ''' + @plantTZDayLightSaving + ''') AS date, ' + @value + ' AS value
                       FROM  ' + @database + '.dbo.' + @table + '
                       WHERE ' + @value + ' IS NOT NULL AND Date_and_Time >= CONVERT(datetime, ''' + CONVERT(VARCHAR, @utcSt, 120)  + ''', 21)
                       AND Date_and_Time <= CONVERT(datetime, ''' + CONVERT(VARCHAR, @utcEt, 120) + ''', 21)'
    PRINT @sql
    EXECUTE sp_executesql @sql

    -- definitions for dynamic sql query
    DECLARE @sqlSelectPart  varchar(100),
            @sqlGroupByPart varchar(500),
            @sqlOrderByPart varchar(100),
            @sqlSubSelectPart varchar(100)
    
    -- if the @aggregate is not 'delta' use the following group by 
    IF 'delta' != @aggregate 
        BEGIN

            IF @aggregate = 'spot'
                SET @aggregate = 'min'

            SET @sqlSelectPart = 'SELECT COMMONDB.DBO.GET_UTC_DATE_FROM_TZ_DATE_2(min(A.date), ' + CAST(@plantTZOffset AS varchar(10) )+ ', ''' + @plantTZDayLightSaving + ''') AS date, round(' + @aggregate + '(A.value),2) AS value' 

            IF 60 > @interval
                SET @sqlGroupByPart = 'GROUP BY DATEPART(YEAR, A.date),
                                                DATEPART(MONTH, A.date),
                                                DATEPART(DAY, A.date),
                                                DATEPART(HOUR, A.date),
                                                (DATEPART(MINUTE, A.date) / ' + @interval + ')'
            ELSE IF 60 = @interval
                SET @sqlGroupByPart = 'GROUP BY DATEPART(YEAR, A.date),
                                                DATEPART(MONTH, A.date),
                                                DATEPART(DAY, A.date),
                                                DATEPART(HOUR, A.date)'
            ELSE IF 1440 = @interval
                --TODO at this point we must use plant time zone for calculation.

                SET @sqlGroupByPart = 'GROUP BY DATEPART(YEAR, A.date),
                                                DATEPART(MONTH, A.date),
                                                DATEPART(DAY, A.date)'
            
            SET @sqlOrderByPart = 'ORDER BY min(A.date) ASC'

            PRINT @sqlSelectPart

            --SET @sql = 'SELECT A.date, A.value FROM #valueDataTable AS A 
             --                  LEFT JOIN #dateTable AS B ON A.date = B.date
             --                  ORDER BY A.date ASC'

            --EXECUTE sp_executesql @sql
            
            -- change select part statement if the pPeak is requested
            IF @normPosInValue = 1 BEGIN

                SET @sqlSubSelectPart = 'A.value/' + CAST(@pPeak as varchar) + ' AS real'
            END
            ELSE  BEGIN
                SET @sqlSubSelectPart = 'A.value AS '+ @dtype
            END

            
            SET @sql =   @sqlSelectPart + ' FROM (
                                    SELECT B.date, cast('+ @sqlSubSelectPart +') AS value 
                                    FROM #valueDataTable AS A
                                    RIGHT JOIN #dateTable AS B ON A.date=B.date) AS A ' +
                                    @sqlGroupByPart +
                                @sqlOrderByPart

            PRINT @sql 
            EXECUTE sp_executesql @sql    
        END    
    ELSE -- AGGREGATE DELTA
		print 'DETLA AGGREGATE'
        BEGIN
            
            DECLARE @valueDeviceClass2 varchar(100),
                    @isYield smallint = 0
        
            CREATE TABLE #deltaTable (
                date datetime,
                value float(53)
            )
            
            --set the group by clause
            IF 60 > @interval
                SET @sqlGroupByPart = 'GROUP BY DATEPART(YEAR, A.date),
                                                DATEPART(MONTH, A.date),
                                                DATEPART(DAY, A.date),
                                                DATEPART(HOUR, A.date),
                                                (DATEPART(MINUTE, A.date) / ' + @interval + ')'
            ELSE IF 60 = @interval
                SET @sqlGroupByPart = 'GROUP BY DATEPART(YEAR, A.date),
                                                DATEPART(MONTH, A.date),
                                                DATEPART(DAY, A.date),
                                                DATEPART(HOUR, A.date)'
            ELSE IF 1440 = @interval
                --TODO at this point we must use plant time zone for calculation.

                SET @sqlGroupByPart = 'GROUP BY DATEPART(YEAR, A.date),
                                                DATEPART(MONTH, A.date),
                                                DATEPART(DAY, A.date)'

            print 'interval sql part implemented'
            --if the value is an E_TOTAL or E_LIFE value filter all 0 values from valueDataTable
            -- EXAMPLE VALUES: COL_SR_19662, COL_SR_327681
            print 'value: ' + @value
            SET @valueDeviceClass2 = (SELECT Device_Class_2 FROM cdb.dbo.CDB_COLUMN_TYPES WHERE Column_Value=@value)
            
            
            print '@valueDeviceClass2' + @valueDeviceClass2
            IF @interval >= 1440 AND (@valueDeviceClass2 = 'E_TOTAL' OR @valueDeviceClass2 = 'E_LIFE' OR @valueDeviceClass2 = 'RAD_CELL_E_T' OR 
            @valueDeviceClass2 = 'RAD_PYRANO_E_T') 
            BEGIN
                -- grep the dc power of the device
                DECLARE @normalPower AS bigint
                
                SET @ParamDefinition = N'@pnorm bigint OUTPUT'
                
                SET @sql = '(SELECT @pnorm = isnull(Maximum_Value,500000000) 
                                    FROM ' + @database + '.dbo.xxx_Virtual_Devices 
                                    WHERE Table_Name = ''' + @table + ''' AND  Device_Class=''P_DC'');'

                --PRINT @sql

                EXECUTE sp_executesql 
                        @sql,
                        @ParamDefinition,
                        @pnorm = @normalPower OUTPUT    
            
                SET @isYield = 1
                
                
                
                -- filter valueDataTable for all 0 values
                DELETE FROM #valueDataTable WHERE value <= 0
                
                
                -- filter failure value higher than 500MWh
                DELETE FROM #valueDataTable WHERE date IN (
                SELECT A.date FROM
                    (SELECT A.date,
                           A.value,
                           A.min,
                           A.max,
                           A.value-A.min AS mindelta,
                           A.max-A.value AS maxdelta
                           FROM
                    (SELECT A.date,
                           A.value,
                           MIN(A.value) OVER(PARTITION BY DATEPART(YEAR, A.date),
                                                          DATEPART(MONTH, A.date),
                                                          DATEPART(DAY, A.date)
                                                           ) AS "min",
                           MAX(A.value) OVER(PARTITION BY DATEPART(YEAR, A.date),
                                                          DATEPART(MONTH, A.date),
                                                          DATEPART(DAY, A.date)
                                                           ) AS "max"
                           FROM #valueDataTable AS A) AS A) AS A
                  WHERE A.mindelta > 500000000 OR A.mindelta > @normalPower * 24)
                --ORDER BY A.mindelta DESC

                -- calculating minute series for delta request
                SET @tsInterval = 1
                ;WITH cSequence AS
                (
                    SELECT
                       @st AS date,
                       DATEADD(MINUTE, @tsInterval, @st) AS et
                    UNION ALL
                    SELECT
                      et,
                      DATEADD(MINUTE, @tsInterval, et)
                    FROM cSequence 
                    WHERE DATEADD(MINUTE, @tsInterval, et) <= @et
                )

                SELECT date INTO #dateDeltaTable FROM cSequence OPTION (MAXRECURSION 0);
                
                /********************************************************************/
                /*ETODAY OF A E_TOTAL Value                                         */
                /*SELECT B.date, A.value - A.min AS eday 
                FROM (SELECT A.date, 
                             A.value,
                             MIN(A.value) OVER(PARTITION BY DATEPART(YEAR, A.date),
                                                            DATEPART(MONTH, A.date),
                                                            DATEPART(DAY, A.date)
                                                           ) AS "min"
                             FROM #valueDataTable AS A ) AS A RIGHT JOIN 
                             #dateDeltaTable AS B ON A.date=B.date*/
                /********************************************************************/                 

                IF @normPosInValue = 1 BEGIN
                    SET @sqlSubSelectPart = 'CAST(ROUND(CAST(max(A.eday) AS real)/' + CAST(@pPeak as varchar) + ',4) AS varchar)'
                END
                ELSE  BEGIN
                    SET @sqlSubSelectPart = 'max(A.eday)'
                END

                /********************************************************************/
                /** Calculating  Delta **/
                SET @sql = 'SELECT min(A.date) AS date, ' + @sqlSubSelectPart + ' AS value
                                   FROM (SELECT B.date, A.value - A.min AS eday 
                                         FROM (SELECT A.date, 
                                                      A.value,
                                                      MIN(A.value) OVER(PARTITION BY DATEPART(YEAR, A.date),
                                                                                     DATEPART(MONTH, A.date),
                                                                                     DATEPART(DAY, A.date)
                                                                                   ) AS "min"
                                               FROM #valueDataTable AS A ) AS A 
                                               RIGHT JOIN #dateDeltaTable AS B ON A.date=B.date) AS A ' +  
                                         @sqlGroupByPart + 
                                        'ORDER BY min(A.date)'
                PRINT @sql
                EXECUTE sp_executesql @sql
                /********************************************************************/                 
            END
            ELSE 
            BEGIN
                
                -- delta sql have to be defined here
                SET @sqlSelectPart = 'INSERT INTO #deltaTable SELECT min(A.date) AS date, min(A.value) AS value'
                
                SET @sql = @sqlSelectPart + ' FROM (
                                          SELECT B.date, A.value
                                          FROM #valueDataTable AS A
                                          RIGHT JOIN #dateTable AS B ON A.date=B.date) AS A ' +
                                          @sqlGroupByPart +
                                      'ORDER BY min(A.date) ASC'

                --PRINT @sql
                EXECUTE sp_executesql @sql
                
                -- remove values < 0 if the aggreagte is delta and the value is energy_yield of the plant device
                -- 
                IF (@table='xxx_plant_device' and @value='Energy_Yield') OR @value='COL_SR_196627' BEGIN
                    SET @plant_yield_delta_filter = 'CASE WHEN A.value < 0 THEN 0 ELSE A.value END'
                END
                ELSE BEGIN
                    SET @plant_yield_delta_filter = 'A.value'
                END 
                
                
                IF @normPosInValue = 1 BEGIN
                    print 'norm value transformation'
                    SET @sqlSubSelectPart = '(round((A.value - B.value)/' + CAST(@pPeak AS varchar) + ',4))'
                END
                ELSE  BEGIN
                    print 'value'
                    SET @sqlSubSelectPart = 'A.value - B.value'
                END
                
                -- this statement makes sure for every timestamp is a date avaialble even if there is no data at that timestamp
                -- transform timestamp to utc
                SET @sql = 'SELECT (DATEADD(MINUTE, -CAST(' + @interval + ' AS int), B.date)) AS date, cast(' + @plant_yield_delta_filter +  ' AS varchar) AS value 
                                   FROM ( SELECT A.date, ' + @sqlSubSelectPart + ' AS value FROM #deltatable AS A
                                          LEFT JOIN #deltatable AS B ON A.date = (DATEADD(MINUTE, CAST(' + @interval + ' AS int), B.date))
                                        ) AS A
                                   RIGHT JOIN #dateTable AS B ON A.date = B.date
                                   WHERE B.date > CONVERT(datetime, ''' + CONVERT(VARCHAR, @St, 120) + ''', 21)
                                     AND B.date < CONVERT(datetime, ''' + CONVERT(VARCHAR, @Et, 120) + ''', 21)
                                   ORDER BY B.date'
                PRINT @sql
                EXECUTE sp_executesql @sql
            END
        END
END
