USE [COMMONDB]
GO
/****** Object:  StoredProcedure [dbo].[GET_PREDEFINED_DAILY_PLANT_DATA]    Script Date: 08/30/2018 14:29:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GET_PREDEFINED_DAILY_PLANT_DATA](@database varchar(50), @date varchar(50)) AS 
BEGIN 
-- This function retuns the performace ratio, ac power and radiation power of the plant
-- in plant time 
-- in 15 Minutes resolution 
-- standard xml protocol with get_device_data for example
-- @author Milan Rompe
-- @version 1.6.4
-- @since 2017-01-24
-- @param date - day to be requested in plant time zone. Example: '2017-01-24 00:00:00' 
--             - hour minutes and seconds will be ignored               
-- @changed
-- 2018-08-30 MR: fixed uses ac power from energy meter even if no radiation sensor exists.
-- 2018-06-25 MR: fixed fetching empty parameter
-- 2018-06-19 MR: fixed fetching of empty radiation sensor property
-- 2017-09-27 MR: if @date is null we use the current server date
-- 2017-09-07 MR: Radiation will search for METEO and '%RADIATION%'
-- 2017-08-10 MR: FIXED: Data will be returned in plant time correctly
-- 2017-08-07 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(1000),
        @ParamDefinition NVARCHAR(500),
        @sqlSelectPart NVARCHAR(300),
        @sqlWherePart NVARCHAR(200),
        @sqlGroupByPart NVARCHAR(400),
        @sqlOrderByPart NVARCHAR(100),
        @aggregate NVARCHAR(10) = 'avg',
        @udid nvarchar(100),
        @uvid nvarchar(100),
        @st datetime,
        @et datetime,
        @stUTC datetime,
        @etUTC datetime,
        @stStr varchar(50),
        @etStr varchar(50),
        @interval integer = 15,
        @value integer,
        @plantTZOffset integer,
        @plantTZDayLightSaving varchar(10),
        @dc_power_installed float

    CREATE TABLE #pdata (
                RowID INT IDENTITY ( 1 , 1 ),
                date datetime NOT NULL,
                radpower float NULL,
                acpower float NULL,
                pr float 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

    IF @date IS NULL BEGIN
        SET @st = GETUTCDATE()
        SET @st = DATEADD(ms, -DATEPART(ms, @st), @st)
        SET @st = DATEADD(ss, -DATEPART(ss, @st), @st)
    END
    ELSE BEGIN
        -- Setup startdate and enddate in utc
        SET @st = CONVERT(datetime, @date, 120)    
    END

    -- reset hour, minute, seconds and millis
    SET @st = DATEADD(day, datediff(day, 0, @st),0);
    -- add one day
    SET @et = DATEADD(day, 1, @st);  

    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 @stStr = CONVERT(VARCHAR, @stUTC, 120)
    SET @etStr = CONVERT(VARCHAR, @etUTC, 120)

    print 'st utc: ' + @stStr
    print 'et utc :' + @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
           @stUTC AS st, 
           DATEADD(MINUTE, @interval, @stUTC) AS et
        UNION ALL
        SELECT
          et, 
          DATEADD(MINUTE, @interval, et)
        FROM cSequence 
        WHERE DATEADD(MINUTE, @interval, et) <= @etUTC
    )
    
    INSERT INTO #pdata (date, radpower, acpower, pr)
    SELECT st, null, null, null FROM cSequence OPTION (MAXRECURSION 0);

    --SELECT * FROM #pdata

    -- 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
        print @sql
    END

    SET @sqlSelectPart = 'SELECT min(Date_and_time) AS date, 
                                ' + @aggregate + '(' +  @uvid + ') AS value' 
                               
                                
    SET @sqlGroupByPart = 'GROUP BY DATEPART(YEAR, Date_and_time),
                                             DATEPART(MONTH, Date_and_time),
                                             DATEPART(DAY, Date_and_time),
                                             DATEPART(HOUR, Date_and_time),
                                            (DATEPART(MINUTE, Date_and_time) / 15)'
    SET @sqlWherePart = ' WHERE Date_and_time >= convert(datetime, ''' + @stStr + ''',21)
                            AND Date_and_time <  convert(datetime, ''' + @etStr + ''',21) '
    SET @sqlOrderByPart = 'ORDER BY min(Date_and_time) ASC'

    --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 =   @sqlSelectPart + ' FROM ' + @database + '.dbo.' + @udid +  ' ' +
                                        @sqlWherePart + ' ' +
                                        @sqlGroupByPart

        SET @sql = 'UPDATE #pdata 
                      SET radpower = A.value
                    FROM (' + @selectSQL + ') AS A
                    WHERE DATEPART(YEAR, #pdata.date) = DATEPART(YEAR, A.date)
                      AND DATEPART(MONTH, #pdata.date) = DATEPART(MONTH, A.date)
                      AND DATEPART(DAY, #pdata.date) = DATEPART(DAY, A.date)
                      AND DATEPART(HOUR, #pdata.date) = DATEPART(HOUR, A.date)
                      AND DATEPART(MINUTE, #pdata.date)/15 = DATEPART(MINUTE, A.date)/15'

        print  @sql 
        EXECUTE sp_executesql
                @sql
      SET @udid = NULL
      SET @uvid = NULL
      
      END

      -- 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
      
   
      --print 'em power: ' + @udid + '.' + @uvid
      DECLARE @invertACPower varchar(10),
              @ParamDefinition2 NVARCHAR(100),
              @invertSQL varchar(10) = ''

      IF @udid IS NULL OR @uvid IS NULL BEGIN
        --print 'using inverter plant device ac power'
        SET @udid = 'xxx_Plant_Device'
        SET @uvid = 'Inverter_Power_AC'
      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

      SET @sqlSelectPart = 'SELECT min(Date_and_time) AS date, 
                           ' + @aggregate + '(' +  @uvid +') AS value'

      -- FETCH DAILY PLANT AC POWER DATA
      SET @selectSQL =   @sqlSelectPart + ' FROM ' + @database + '.dbo.' + @udid +  ' ' +
                         @sqlWherePart + ' ' +
                         @sqlGroupByPart

      --print  @selectSQL 
      SET @sql = 'UPDATE #pdata 
                      SET acpower = A.value ' + @invertSQL + '
                    FROM (' + @selectSQL + ') AS A
                    WHERE DATEPART(YEAR, #pdata.date) = DATEPART(YEAR, A.date)
                      AND DATEPART(MONTH, #pdata.date) = DATEPART(MONTH, A.date)
                      AND DATEPART(DAY, #pdata.date) = DATEPART(DAY, A.date)
                      AND DATEPART(HOUR, #pdata.date) = DATEPART(HOUR, A.date)
                      AND DATEPART(MINUTE, #pdata.date)/15 = DATEPART(MINUTE, A.date)/15'
        print @sql
        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)

      UPDATE #pdata SET pr = CASE WHEN radpower <= 0 THEN null ELSE round((acpower/10.0/@dc_power_installed) / CASE WHEN radpower/1000.0 = 0 THEN 1 ELSE radpower/1000.0 END, 2) END
      UPDATE #pdata SET pr = null WHERE pr <= 0 or radpower < 40
      UPDATE #pdata SET acpower = 0 WHERE acpower < 0
      UPDATE #pdata SET radpower = 0 WHERE radpower < 0 
      UPDATE #pdata SET pr = 120 WHERE pr > 120

      SELECT COMMONDB.DBO.GET_TZ_DATE_FROM_UTC_DATE_2(date, 
                                                      CAST(@plantTZOffset AS varchar(10) ), 
                                                      @plantTZDayLightSaving) AS date, 
             cast(radpower AS nvarchar) AS radpower, cast(round(acpower/1000,2) AS nvarchar(20)) AS acpower, cast(pr AS nvarchar(10)) AS pr FROM #pdata 

END
