USE [COMMONDB]
GO
/****** Object:  StoredProcedure [dbo].[GET_PREDEFINED_DAILY_YIELD_PLANT_DATA]    Script Date: 02/25/2019 17:38:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GET_PREDEFINED_DAILY_YIELD_PLANT_DATA](@database varchar(50), @date varchar(50), @days integer) AS 
BEGIN 
-- This function calculates the total yield of a day for the amount of requested days. It also returns the Radiation Yield
-- for these days.  
-- in daily resolution 
-- standard xml protocol with get_prefefined_yield_daily_data for example
-- @author Milan Rompe
-- @version 1.4.2
-- @since 2017-01-26
-- @param date - day to be requested in plant time zone. Example: '2017-01-24 00:00:00' 
--             - hour minutes and seconds will be ignored
-- @param days - the amount of days to be calculated               
-- @changed
-- 2019-02-25 MR Fixed an inaccuracy when calculating the current day at the last 15 minute avg value. Excluding it now. It could lead to decreasing daily values.
-- 2018-10-12 MR Fixed yield caluclation by energy meter ac power
-- 2018-08-30 MR Fixed uses now ac power of energy meter if available.
-- 2018-06-19 MR Fixed empty radiation parameter
-- 2018-02-23 MR Implemented different Radiation sensors
-- 2017-08-08 MR Changed GET_TZ_DATE_FROM_UTC_DATE to GET_TZ_DATE_FROM_UTC_DATE_2 for implementation of day light saving.
SET NOCOUNT ON

DECLARE @sql nvarchar(2000),
        @selectSQL nvarchar(2000),
        @ParamDefinition NVARCHAR(500),
        @aggregate NVARCHAR(10) = 'avg',
        @udid nvarchar(100) = null,
        @uvid nvarchar(100) = null,
        @st datetime,
        @et datetime,
        @stUTC datetime,
        @etUTC datetime,
        @etUTC_max datetime, 
        @mi smallint,
        @stStr varchar(50),
        @etStr varchar(50),
        @interval integer = 1440,
        @value integer,
        @plantTZOffset integer,
        @plantTZDayLightSaving varchar(10),
        @dc_power_installed float

    CREATE TABLE #pdata (
                RowID INT IDENTITY ( 1 , 1 ),
                date datetime NOT NULL,
                yield integer NULL,
                radyield float NULL,
                pr float NULL,
                targetyield integer NULL
            );

    -- 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
    
    -- 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
    
    print '@plantTZDayLightSaving' + @plantTZDayLightSaving        
    IF @plantTZDayLightSaving is NULL BEGIN
       SET @plantTZDayLightSaving = 'true' 
    END

    
            
    -- Setup startdate and enddate in utc
    SET @et = convert(datetime, @date, 120)
    -- reset hour, minute, seconds and millis
    SET @et = DATEADD(day, datediff(day, 0, @et),0);
    -- add one day
    SET @et = DATEADD(day, 1, @et);  
    -- minus days for starttime
    SET @st = DATEADD(day, -@days, @et);  

    print CONVERT(VARCHAR, @st, 120)
    print CONVERT(VARCHAR, @et, 120)

    SET @stUTC = COMMONDB.DBO.GET_UTC_DATE_FROM_TZ_DATE_2(@st, @plantTZOffset, @plantTZDayLightSaving)
    SET @etUTC = COMMONDB.DBO.GET_UTC_DATE_FROM_TZ_DATE_2(@et, @plantTZOffset, @plantTZDayLightSaving)

	SET @etUTC_max = GETUTCDATE()
	SET @etUTC_max = DATEADD(ms, -DATEPART(ms, @etUTC_max), @etUTC_max)
    SET @etUTC_max = DATEADD(ss, -DATEPART(ss, @etUTC_max), @etUTC_max)
    SET @etUTC_max = DATEADD(minute, datediff(minute, 0, @etUTC_max),0);
    
    SET @mi = (DATEPART(mi, @etUTC_max) / 15) * 15
	SET @etUTC_max = DATEADD(mi, -(DATEPART(mi, @etUTC_max)-@mi), @etUTC_max)
	
	IF @etUTC > @etUTC_max BEGIN
		print 'set @etUTC to @etUTC_max'
		SET @etUTC = @etUTC_max
	END


    SET @stStr = CONVERT(VARCHAR, @stUTC, 120)
    SET @etStr = CONVERT(VARCHAR, @etUTC, 120)

    print @stStr
    print @etStr

    -- Fetch Radiation sensor value
    SET @uvid = NULL
    SET @udid = NULL
    
    -- fill #pdata table with timestamps
    print 'insert timestamps into #pdata table'
    
    ;WITH cSequence AS
    (
        SELECT
           @st AS st, 
           DATEADD(MINUTE, @interval, @st) AS et
        UNION ALL
        SELECT
          et, 
          DATEADD(MINUTE, @interval, et)
        FROM cSequence 
        WHERE DATEADD(MINUTE, @interval, et) <= @et
    )
    
    INSERT INTO #pdata (date, yield, radyield, pr, targetyield)
    SELECT st, null, null, null, null FROM cSequence OPTION (MAXRECURSION 0);

    -- fetch radiation sensor
    SET @ParamDefinition = '@udidOUT nvarchar(100) OUTPUT,
                            @uvidOUT nvarchar(100) OUTPUT'

    SET @sql = 'SELECT @udidOUT = LEFT(col, CHARINDEX(''.'', col)-1), 
                       @uvidOUT = RIGHT(col, LEN(col)-CHARINDEX(''.'', col)) FROM
                (SELECT TOP 1 Value as col FROM ' + @database + '.dbo.xxx_Plant_Device_Properties 
                 WHERE Value_Key=''plant.radiation.plane.device.value.power'' AND Value <> '''')x'
    EXECUTE sp_executesql
            @sql,
            @ParamDefinition,
            @udidOUT = @udid OUTPUT,
            @uvidOUT = @uvid OUTPUT

    -- If there is no radiation sensor defined choose a random sensor
    IF @udid IS NULL OR @uvid IS NULL
    BEGIN
        SET @sql = 'SELECT TOP 1 @udidOUT = Table_Name, 
                                 @uvidOUT=Column_Name
                     FROM ' + @database + '.dbo.xxx_Virtual_Devices
                     WHERE Device_Type = ''RADIATION_SENSOR'' OR Device_Type=''METEO''  AND Device_Class_2 like ''%RADIATION%''
                     ORDER BY Virtual_Device_Name ASC'
        EXECUTE sp_executesql
            @sql,
            @ParamDefinition,
            @udidOUT = @udid OUTPUT,
            @uvidOUT = @uvid OUTPUT
        
        print 'random radiation sensor:' + @udid + '.' + @uvid
        
    END

    print 'Radiation: ' + @udid + '.' + @uvid
    -- if the @udid or @uvid is null we have no chance to get an radiation value
    IF @udid IS NOT NULL AND @uvid IS NOT NULL 
      BEGIN
        -- FETCH LAST RADIATION DATA
        SET @selectSQL =   'SELECT min(DATEADD(DAY, datediff(DAY, 0, B.date), 0)) AS date, sum(B.value) AS value 
                            FROM (SELECT min(A.date) AS date, 
                                  round(avg(CASE WHEN A.value < 0 THEN 0 ELSE CASE WHEN A.value > 1600 THEN null ELSE A.value END END),2) AS value 
                                  FROM (SELECT COMMONDB.DBO.GET_TZ_DATE_FROM_UTC_DATE_2(Date_and_time, 
                                                                                      ' + CAST(@plantTZOffset AS varchar(10) )+ ',
                                                                                      ''' + @plantTZDayLightSaving + ''') AS date, 
                                               ' + @uvid + ' AS value FROM ' + @database + '.dbo. ' + @udid + '
                                  WHERE Date_and_time >= convert(datetime,  ''' + @stStr + ''',21)
                                    AND Date_and_time < convert(datetime, ''' + @etStr + ''',21) ) AS A 
                                  GROUP BY DATEPART(YEAR, A.date),
                                           DATEPART(MONTH, A.date),
                                           DATEPART(DAY, A.date),
                                           DATEPART(HOUR, A.date)
                                  ) AS B
                            GROUP BY 
                            DATEPART(YEAR, B.date),
                            DATEPART(MONTH, B.date),
                            DATEPART(DAY, B.date)'

        --print  @sql
        SET @sql = 'UPDATE #pdata 
                      SET radyield = A.value
                    FROM (' + @selectSQL + ') AS A
                    WHERE #pdata.date = A.date'
        --print @sql
        EXECUTE sp_executesql
               @sql
      END
      -- FETCH YIELD data from energy meter if any is defined as plant energy meter
      SET @uvid = NULL
      SET @udid = NULL
      
      SET @sql = 'SELECT @udidOUT = LEFT(col, CHARINDEX(''.'', col)-1), 
                       @uvidOUT = RIGHT(col, LEN(col)-CHARINDEX(''.'', col)) FROM
                (SELECT TOP 1 Value as col FROM ' + @database + '.dbo.xxx_Plant_Device_Properties 
                 WHERE Value_Key=''plant.em.export.device.value.yield'' AND Value <> '''')x'
      print @sql
      EXECUTE sp_executesql
            @sql,
            @ParamDefinition,
            @udidOUT = @udid OUTPUT,
            @uvidOUT = @uvid OUTPUT
      
      print 'em yield: ' + @udid + '.' + @uvid
      DECLARE @invertACPower varchar(10),
              @ParamDefinition2 NVARCHAR(100),
              @invertSQL varchar(10),
              @isACPower int = 0
              
              
      -- do we have a energy meter yield power value
      IF @udid IS NULL OR @uvid IS NULL BEGIN
            -- FETCH AC_POWER data from energy meter if any is defined as plant energy meter
            SET @sql = 'SELECT @udidOUT = LEFT(col, CHARINDEX(''.'', col)-1), 
                       @uvidOUT = RIGHT(col, LEN(col)-CHARINDEX(''.'', col)) FROM
                    (SELECT TOP 1 Value as col FROM ' + @database + '.dbo.xxx_Plant_Device_Properties 
                     WHERE Value_Key=''plant.em.export.device.value.acpower'' AND Value <> '''')x'
            EXECUTE sp_executesql
                @sql,
                @ParamDefinition,
                @udidOUT = @udid OUTPUT,
                @uvidOUT = @uvid OUTPUT
            print @sql        
      
            -- If we dont have a energy meter
            IF @udid IS NULL OR @uvid IS NULL BEGIN
                SET @udid = 'xxx_Plant_Device'
                SET @uvid = 'Inverter_Power_AC'
            END
            SET @isACPower = 1
      END     
      ELSE BEGIN
        -- grep inverted flag for energy meter power data
        SET @ParamDefinition2 = '@invertOUT nvarchar(100) OUTPUT'
                                
        SET @sql = 'SELECT TOP 1 @invertOut = Value FROM ' + @database + '.dbo.xxx_Plant_Device_Properties 
                    WHERE Value_Key=''plant.em.export.device.value.acpower.invert'' AND Value<> '''''

        EXECUTE sp_executesql
            @sql,
            @ParamDefinition2,
            @invertOUT = @invertACPower OUTPUT
        
        IF @invertACPower = 'true' BEGIN
            SET @invertSQL = '*-1'
        END
        ELSE BEGIN
            SET @invertSQL = ''
        END 
      END
      
      -- for yield calculation values are selected 
      IF @isACPower = 0 BEGIN
        print 'yield calculation by energy meter yield'
        SET @selectSQL = 'SELECT DATEADD(DAY, datediff(DAY, 0, date), 0) AS date, value FROM (
                                            SELECT min(A.date) AS date, 
                                                     MAX(A.value) - MIN(A.value) AS value
                                            FROM (SELECT COMMONDB.DBO.GET_UTC_DATE_FROM_TZ_DATE_2(Date_and_time, 0, ''' + @plantTZDayLightSaving + ''') AS date, 
                                                         ' + @uvid + ' AS value FROM 
                                            ' + @database + '.dbo.' + @udid + '
                                            WHERE Date_and_time > convert(datetime,  ''' + @stStr + ''',21)
                                            AND   Date_and_time < convert(datetime, ''' + @etStr + ''',21)) AS A
                                            GROUP BY DATEPART(YEAR, A.date),
                                                     DATEPART(MONTH, A.date),
                                                     DATEPART(DAY, A.date)) AS B'
      END
      ELSE BEGIN
        print 'yield calculation by plant ac power'
        SET @selectSQL = 'SELECT min(DATEADD(DAY, datediff(DAY, 0, date), 0)) AS date, sum(value) AS value 
                          FROM (SELECT COMMONDB.DBO.GET_UTC_DATE_FROM_TZ_DATE_2(min(Date_and_time), 0, ''' + @plantTZDayLightSaving + ''') AS date, 
                                       round(avg(CASE WHEN ' + @uvid + ' < 0 THEN 0 ELSE ' + @uvid + ' END),2) AS value 
                                FROM ' + @database + '.dbo.'+ @udid + '
                                WHERE Date_and_time >= convert(datetime, ''' + @stStr + ''',21)
                                  AND Date_and_time < convert(datetime, ''' + @etStr + ''',21)  
                                GROUP BY DATEPART(YEAR, Date_and_time),
                                         DATEPART(MONTH, Date_and_time),
                                         DATEPART(DAY, Date_and_time),
                                         DATEPART(HOUR, Date_and_time)) AS A
                          GROUP BY 
                          DATEPART(YEAR, A.date),
                          DATEPART(MONTH, A.date),
                          DATEPART(DAY, A.date)'
       
      END
      print @selectSQL
      SET @sql = 'UPDATE #pdata 
                      SET yield = A.value
                    FROM (' + @selectSQL + ') AS A
                    WHERE #pdata.date = A.date'
      EXECUTE sp_executesql
              @sql
              
              
      -- CALCULATE PR ON #pdata
      SET @ParamDefinition = '@dataOUT integer OUTPUT'
            
      SET @sql = 'SELECT @dataOUT=cast(Value as float)/1000.0
                  FROM ' + @database + '.dbo.xxx_Plant_Device_Properties 
                  WHERE Value_Key=''plant.dc.installedpower.total'' '
      EXECUTE sp_executesql
              @sql,
              @ParamDefinition,
              @dataOUT = @dc_power_installed OUTPUT
      
      print 'plant dc power installed: ' + cast(@dc_power_installed AS varchar) + 'kWp'
      
      UPDATE #pdata SET pr = CASE WHEN radyield <= 0 THEN null ELSE round((yield/10.0/@dc_power_installed) / CASE WHEN radyield/1000.0 = 0 THEN 1 ELSE radyield/1000.0 END, 2) END

      --UPDATE #pdata SET pr = null WHERE pr <= 0 OR pr > 100
      --UPDATE #pdata SET acpower = 0 WHERE acpower < 0
      UPDATE #pdata SET radyield = 0 WHERE radyield < 0 

      DECLARE @avgpr float

      SET @avgpr = (SELECT SUM(yield)/1000.0/@dc_power_installed  / CASE WHEN (SUM(radyield)/1000.0)=0 THEN null ELSE (SUM(radyield)/1000.0) END FROM #pdata)

      IF @avgpr > 1 BEGIN
        SET @avgpr = 1
      END

      UPDATE #pdata SET targetyield=radyield*@dc_power_installed*@avgpr
      print 'avgpr: ' + cast(@avgpr AS varchar)

      SELECT date, yield/1000 AS yield, CAST(radyield AS nvarchar) AS radyield, CAST(pr AS nvarchar) AS pr, targetyield/1000 AS targetyield FROM #pdata

END
