USE [COMMONDB]
GO
/****** Object:  StoredProcedure [dbo].[GET_PLANT_DATA]    Script Date: 02/20/2020 09:35:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GET_PLANT_DATA] (@database varchar(50), @starttime varchar(50)) AS 
BEGIN 
-- This function retuns a set of current plant data
-- It will mainly be unsed in the control room of the plant.
-- We created a requests for that purpose to speed thinks up, and avoid overhead in the 
-- standard xml protocol with get_device_data for example
-- @author Milan Rompe
-- @version 2.25
-- @since 2016-09-14
-- @changed
-- 2020-02-21 MR: disable @starttime parameter. Always use GETUTCDATE() function 
-- 2020-01-06 MR: improved way of fetching radiation. It is now like in get_plant_inverter
-- 2019-03-12 MR: improved radiation power request to which always returns a row now even if there is no data available.
-- 2018-11-13 MR: improved the plant.communication.date value which will use the communication date of the inverter if there is no date from plant_device
-- 2018-10-29 MR: Fixed exception when energy meter has no data
-- 2018-10-29 MR: Get_Elevalation will be called from COMMONDB now.
-- 2018-06-25 MR: fixed empty parameter request
-- 2018-02-02 MR: fetches only one plant power parameter even if there are more due to inconsitency of the db model
-- 2018-02-02 MR: fixed bug, when @starttime is null
-- 2018-02-01 MR: fetching radiation from 'COL_SR_514'
-- 2018-01-08 MR: changed fetching GET_DEVICE_DATA from COMMONDB now
-- 2018-01-01 MR: fixed future date issue for communication date
-- 2017-10-19 MR: fixed null date issue. 
-- 2017-06-23 MR: active alarms won't be cached.
-- 2017-06-20 MR: inverter data are one hour old. We need to fetch the latest data.
-- 2017-06-20 MR: changed update to 5 Minutes
-- 2017-06-14 MR: migrate to new inverter function
-- 2017-03-29 MR: added METEO for fetching random radiation value

SET NOCOUNT ON

DECLARE @sql nvarchar(2000),
        @ParamDefinition NVARCHAR(500),
        @udid nvarchar(100),
        @uvid nvarchar(100),
        @date datetime,
        @st datetime,
        @et datetime,
        @etYield datetime,
        @stFloatHour datetime ,
        @stStr varchar(50),
        @etStr varchar(50),
        @interval integer,
        @value integer,
        @lazyLoadInterval int = 1 -- refresh ac total power at least all 5 minutes

    SET NOCOUNT ON
    
    --DECLARE @logDate datetime = GETUTCDATE()
    
    /*INSERT INTO [COMMONDB].[dbo].[log_function_call]
           ([date]
           ,[functioncall]
           ,[param1]
           ,[param2])
     VALUES
           (@logDate,
           'GET_PLANT_DATA',
           @database,
           @starttime)*/
   
    
    CREATE TABLE #data (
            RowID INT IDENTITY ( 1 , 1 ),
            pkey varchar(64) NOT NULL PRIMARY KEY,
            date datetime NULL,
            pvalue varchar(64), 
            source varchar(100) NULL
            );
    
    
    --IF @starttime IS NULL BEGIN
    --    print 'starttime is null. Use current server date'
	SET @st = GETUTCDATE()
	SET @st = DATEADD(ms, -DATEPART(ms, @st), @st)
	SET @st = DATEADD(ss, -DATEPART(ss, @st), @st)
	SET @starttime = CONVERT(nvarchar, @st, 120)
    --END
    --ELSE BEGIN
    --   SET @st = CONVERT(datetime, @starttime, 21)    
    --END

    -- create global temp table if not exist
    IF OBJECT_ID('tempdb..'+ @database + '_plant_data_last') IS NULL
    BEGIN
        SET @sql = 'USE tempdb CREATE TABLE ' + @database + '_plant_data_last(
                               RowID int,
                               pkey varchar(64) NOT NULL PRIMARY KEY,
                               date datetime NULL,
                               pvalue varchar(64),
                               source varchar(100) NULL);'
        EXECUTE sp_executesql
                @sql
    END
    ELSE
    BEGIN
        SET @ParamDefinition = '@uvidOUT nvarchar(100) OUTPUT,
                                @dateOUT datetime OUTPUT'

        -- check if data in temp table need to be refreshed. Update every 5 Minutes
        SET @sql = 'SELECT TOP 1 @uvidOUT=pkey, @dateOUT=date
                    FROM tempdb..' + @database + '_plant_data_last
                    WHERE pkey = ''plant.elevation''
                    ORDER BY date DESC'
        EXECUTE sp_executesql
                @sql,
                @ParamDefinition,
                @uvidOUT = @uvid OUTPUT,
                @dateOUT = @date OUTPUT

        SET @ParamDefinition = '@intervalOUT int OUTPUT'
        -- TODO use plant.data.resolution and plant.data.transmissioninterval in future
        SET @sql = 'SELECT @intervalOUT = interval 
                    FROM(SELECT interval FROM (SELECT SUM(cast(Value AS int)) + 5 AS interval, COUNT(Value) as numvalues 
                         FROM '+ @database + '.dbo.xxx_Plant_Device_Properties 
                         WHERE Value_Key=''Send_Interval'' 
                         OR Value_Key=''Data_Interval'') AS A 
                    WHERE numvalues = 2) AS A'
        EXECUTE sp_executesql
                @sql,
                @ParamDefinition,
                @intervalOUT = @interval OUTPUT
        print @sql
        
        print 'starttime UTC: ' + CONVERT(varchar, @st, 21)
        print 'date diff: ' + cast(DATEDIFF(minute, @date, @st) AS varchar)
        print 'plant interval: ' + cast(@interval as varchar) 

        /*SET @lazyLoadInterval = @interval / 2*/

        print 'lazy load interval: ' + cast(@lazyLoadInterval AS varchar)

        print '@date ' + isnull(convert(nvarchar, @date, 21), 'null')
        IF @date IS NOT NULL AND DATEDIFF(minute, @date, @st) <= @lazyLoadInterval
        BEGIN
            print 'use cached data'
            --USE cached data
            SET  @sql = 'INSERT INTO #data 
                         SELECT pkey, 
                                date, 
                                pvalue, 
                                source FROM tempdb..' + @database + '_plant_data_last'
            EXECUTE sp_executesql
                    @sql
            
            
            -- UPDATE ACTIVE ALARMS EVERY request 
            DELETE FROM #data WHERE pkey = 'plant.alarms.active.count'
            SET @sql = 'INSERT INTO #data 
                        SELECT ''plant.alarms.active.count'', null, COUNT(*), null FROM ' + @database +'.dbo.xxx_Active_Alarms;'
                print @sql
                EXECUTE sp_executesql
                @sql
        
            INSERT INTO #data SELECT 'debug',null, 'cache',null
            SELECT * FROM #data        
            RETURN            
        END 
        
        SET @uvid = null        
    END
    
    print 'update plant data'
    
    -- minimum interval is 30 minutes    
    IF @interval < 30 BEGIN
        SET @interval = 30
    END 
    
    SET @date = CONVERT(datetime, @starttime, 21)
    print '@date: ' + CONVERT(VARCHAR, @date, 120)
    SET @et = @date

    print '@interval: ' + cast(@interval as varchar)
    SET @date = DATEADD(mi,-@interval,@date)
    print '@date: ' + CONVERT(VARCHAR, @date, 120)
    -- for floating pr we use one hour data window
    SET @stFloatHour = DATEADD(mi,-60,@et)
    print '@et: ' +  CONVERT(VARCHAR, @et, 120)
    print '@stFloatHour: ' +  CONVERT(VARCHAR, @stFloatHour, 120)

--  1. grep energy meter ac power if configured in xxx_Plant_Device_Properties
    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.em.export.device.value.acpower'' AND Value<>'''')x'

    EXECUTE sp_executesql
            @sql,
            @ParamDefinition,
            @udidOUT = @udid OUTPUT,
            @uvidOUT = @uvid OUTPUT

    -- if udid and uvid is valid select latest data from table
    IF @udid IS NOT NULL and @uvid IS NOT NULL BEGIN
    
        DECLARE @invertACPower varchar(10),
                @ParamDefinition2 NVARCHAR(100),
                @invertSQL varchar(10)
    
        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 

        SET @sql = 'INSERT INTO #data
                    SELECT TOP 1 ''plant.em.export.device.value.acpower'', 
                                   Date_and_Time AS date,
                                   ' + @uvid + @invertSQL + ' AS value,
                                   ''' + @udid + '.' + @uvid + '''
                    FROM ' + @database + '.dbo.' + @udid + '
                    WHERE Date_and_Time > CONVERT(datetime, ''' + CONVERT(VARCHAR, @date, 120) + ''', 21)
                      AND Date_and_Time < CONVERT(datetime, ''' + CONVERT(VARCHAR, @et, 120) + ''', 21)
                    ORDER BY Date_and_Time DESC '
        EXECUTE sp_executesql
                @sql
        -- reset udid and uvid
        SET @udid = NULL;
        SET @uvid = NULL;
        
    END    
        print 'fetch em yield'
        -- get the yield today of the 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.yield'' AND Value<>'''')x'
        print @sql
        EXECUTE sp_executesql
                @sql,
                @ParamDefinition,
                @udidOUT = @udid OUTPUT,
                @uvidOUT = @uvid OUTPUT
    
        print 'udid: ' + @udid
        print 'uvid: ' + @uvid
        -- if there is uvid and udid fetch the energy yield today
        IF @udid IS NOT NULL and @uvid IS NOT NULL BEGIN
            
            print 'fetch yield etoday'
            print '@st: ' + convert(nvarchar, @st,120)
            SET @st = dateadd(day, datediff(day, 0, @st), 0)
            SET @etYield = DATEADD(HH,24,@st)
            
            SET @stStr = CONVERT(VARCHAR, @st, 120)
            SET @etStr = CONVERT(VARCHAR, @etYield, 120)
            
            print '@date: ' + CONVERT(VARCHAR, @date, 120)
            print 'create tmp data table'
            CREATE TABLE #tmpData(
                date datetime NULL,
                pvalue varchar(64) NULL);
                
            SET @sql = 'INSERT INTO #tmpData
                        EXEC COMMONDB.[dbo].[GET_DEVICE_DATA] ''' + @database + ''',
                                                                       ''' + @udid + ''', 
                                                                       ''' + @uvid + ''', 
                                                                       ''bigint'', 
                                                                       ''' + @stStr + ''', 
                                                                       ''' + @etStr + ''', 
                                                                       ''delta'', 
                                                                       ''1440'', 
                                                                       ''plant'' '
            
            print @sql
            EXECUTE sp_executesql
                    @sql

            SET @sql = 'INSERT INTO #data
                        SELECT ''plant.em.export.device.value.yield'', date, pvalue, ''' + @udid + '.' + @uvid + '''
                        FROM #tmpData WHERE pvalue IS NOT NULL'
            print @sql
            EXECUTE sp_executesql
                    @sql

            SET @date = DATEADD(mi, -60, @et)
            SET @sql = 'INSERT INTO #data
                        SELECT ''plant.em.export.device.value.yield.hour'', 
                               max(Date_and_Time) AS date, 
                               max(' + @uvid + ') - MIN(' + @uvid + ') AS delta_yield,
                               ''' + @udid + '.' + @uvid + '''
                        FROM ' + @database + '.dbo.' + @udid + '
                        WHERE Date_and_Time > CONVERT(datetime, ''' + CONVERT(VARCHAR, @stFloatHour, 120) + ''', 21)
                        AND Date_and_Time <= CONVERT(datetime, ''' + CONVERT(VARCHAR, @et, 120) + ''', 21)'
            print 'fetch etoday yield from energy meter'
            print @sql
            EXECUTE sp_executesql
                    @sql
        END
    

    SET @stStr = CONVERT(VARCHAR, @st, 120)
    SET @etStr = CONVERT(VARCHAR, @et, 120)
    
    -- FETCH AC POWER OF INVERTER
    -- This kind of sharing data between two procedures is a little bit weired but 
    -- there is no other option. The GET_PLANT_INVERTER_LAST stores its data in a 
    -- global temp table which is defined in [database]_plant_data_inverter_last
    EXEC COMMONDB.dbo.GET_PLANT_INVERTER_LAST @database 
    -- check if tbl is empty execute again
    DECLARE @rowCount int,
            @ParamDefinitionRowCount nvarchar(100)
    
    SET @ParamDefinitionRowCount = '@rowCountOUT int OUTPUT'
     
    SET @sql = 'SELECT TOP 1 @rowCountOUT = count(*)  FROM tempdb..' + @database + '_plant_data_inverter_last'
    EXECUTE sp_executesql
            @sql,
            @ParamDefinitionRowCount,
            @rowCountOUT = @rowCount OUTPUT
    
    print 'rowCount: ' + cast(@rowCount  AS varchar(10))

    /*UPDATE COMMONDB.dbo.log_function_call SET param3=CAST(@rowCount AS nvarchar(10)) 
    WHERE date=@logDate AND functioncall='GET_PLANT_DATA' AND param1=@database AND param2=@starttime*/
    
    -- calculate the plant power again
    IF @rowCount IS NULL OR @rowCount = 0 BEGIN
        EXEC COMMONDB.dbo.GET_PLANT_INVERTER_LAST @database
    END
    
    SET @sql = 'INSERT INTO #data
                    SELECT ''plant.inv.export.device.value.acpower'', A.date, cast(A.ac_power AS bigint), ''xxx_plant_device.Inverter_Power_AC''
                    FROM (SELECT ROW_NUMBER() OVER(ORDER BY date DESC) AS row, *
                          FROM tempdb..' + @database + '_plant_data_inverter_last 
                          WHERE ac_power IS NOT NULL) AS A
                    WHERE row = 1'
    print @sql
    EXECUTE sp_executesql
            @sql
            
    SET @sql = 'INSERT INTO #data
                    SELECT ''plant.inv.export.device.value.acpower.floating.hour'', A.date, cast(A.ac_power AS bigint), ''xxx_plant_device.Inverter_Power_AC_Float_Hour''
                    FROM (SELECT max(date) as date, AVG(ac_power) as ac_power
                          FROM tempdb..' + @database + '_plant_data_inverter_last) AS A'
    print @sql 
    EXECUTE sp_executesql
    
            @sql

    -- select inverter yield 
    SET @sql = 'INSERT INTO #data
                SELECT TOP 1 ''plant.inv.export.device.value.yield'', A.date, cast(A.value AS bigint), ''xxx_plant_device.Energy_Yield'' FROM
                (SELECT TOP 2 Date_and_Time AS date, Energy_Yield AS value
                    FROM ' + @database + '.dbo.xxx_Plant_Device
                    WHERE Date_and_Time > CONVERT(datetime, ''' + CONVERT(VARCHAR, @date, 120) + ''', 21) 
                      AND Date_and_Time < CONVERT(datetime, ''' + @etStr + ''', 21)
                    ORDER BY Date_and_Time DESC) AS A
                 ORDER BY date ASC'
    EXECUTE sp_executesql
             @sql               

    -- INSERT TOTAL INVERTER COUNT OF THE PLANT

    SET @sql = 'INSERT INTO #data
                SELECT B.pkey, A.date, CASE WHEN A.value IS NOT NULL THEN A.value ELSE B.value END AS value, null FROM 
                    (SELECT ''plant.inv.total'' AS pkey, null as date, cast(Value AS bigint) AS value 
                     FROM ' + @database + '.dbo.xxx_Plant_Device_Properties WHERE Value_Key=''plant.inv.total'')
                     AS A RIGHT JOIN (SELECT ''plant.inv.total'' AS pkey, 0 AS value) AS B ON A.pkey=B.pkey'

    EXECUTE sp_executesql
             @sql

    -- INSERT TOTAL COMMUNICATING INVERTER 
    SET @sql = 'INSERT INTO #data
                    SELECT ''plant.inv.communicating'', A.date, A.inv_communicating, null
                    FROM (SELECT ROW_NUMBER() OVER(ORDER BY date DESC) AS row, *
                          FROM tempdb..' + @database + '_plant_data_inverter_last 
                          WHERE ac_power IS NOT NULL) AS A
                     WHERE row = 1'
                    --ORDER BY A.inv_communicating DESC'
    EXECUTE sp_executesql
            @sql

    -- INSERT TOTAL PRODUCING INVERTER
    SET @sql = 'INSERT INTO #data
                    SELECT ''plant.inv.producing.count'', A.date, A.inv_producing, null
                    FROM (SELECT ROW_NUMBER() OVER(ORDER BY date DESC) AS row, *
                          FROM tempdb..' + @database + '_plant_data_inverter_last 
                          WHERE ac_power IS NOT NULL) AS A
                       WHERE row = 1'
                    --ORDER BY A.inv_producing DESC'
    EXECUTE sp_executesql
            @sql
    
    -- TOTAL NOMINAL POWER PRODUCING
        -- INSERT TOTAL PRODUCING INVERTER
    SET @sql = 'INSERT INTO #data
                    SELECT ''plant.inv.producing.power'', A.date, A.nominal_power_available, null
                    FROM (SELECT ROW_NUMBER() OVER(ORDER BY date DESC) AS row, *
                          FROM tempdb..' + @database + '_plant_data_inverter_last 
                          WHERE ac_power IS NOT NULL) AS A
                    WHERE row = 1'
    EXECUTE sp_executesql
            @sql
            
    -- TOTAL PLANT NOMINAL POWER AVAILABLE
    SET @sql = 'INSERT INTO #data
                SELECT TOP 1 B.pkey, A.date, CASE WHEN A.value IS NOT NULL THEN A.value ELSE B.value END AS value, null FROM 
                    (SELECT DISTINCT ''plant.ac.nominalpower.total'' AS pkey, null as date, cast(Value AS bigint) AS value 
                     FROM ' + @database + '.dbo.xxx_Plant_Device_Properties WHERE Value_Key=''plant.ac.nominalpower.total'')
                     AS A RIGHT JOIN (SELECT ''plant.ac.nominalpower.total'' AS pkey, 0 AS value) AS B ON A.pkey=B.pkey'
    EXECUTE sp_executesql
             @sql
    
    -- TOTAL PLANT DC INSTALLED POWER         
    SET @sql = 'INSERT INTO #data
                SELECT TOP 1 B.pkey, A.date, CASE WHEN A.value IS NOT NULL THEN A.value ELSE B.value END AS value, null FROM 
                    (SELECT ''plant.dc.installedpower.total'' AS pkey, null as date, cast(Value AS bigint) AS value 
                     FROM ' + @database + '.dbo.xxx_Plant_Device_Properties WHERE Value_Key=''plant.dc.installedpower.total'')
                     AS A RIGHT JOIN (SELECT ''plant.dc.installedpower.total'' AS pkey, 0 AS value) AS B ON A.pkey=B.pkey'

    EXECUTE sp_executesql
             @sql         

    -- Fetch Radiation sensor value
    SET @uvid = NULL
    SET @udid = NULL
    print 'find 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
    
    print 'radiation udid: ' + ISNULL(@udid, 'not defined')
    print 'radiation uvid: ' + ISNULL(@uvid, 'not defined')
    
    -- 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 @sql
        print 'random radiation sensor:' + ISNULL(@udid, 'null') + '.' + ISNULL(@uvid, 'null')
        
    END
    
    -- 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
        print '@database: ' + @database
        print '@udid: ' + @udid
        print '@stFloatHour: ' + CONVERT(VARCHAR, @stFloatHour, 120)
        print '@et: ' + CONVERT(VARCHAR, @et, 120)
        SET @sql = 'INSERT INTO #data
                    SELECT ''plant.radiation.plane.device.value.power'', 
                           max(A.date) AS date, 
                           max(A.value) AS value,
                           ''' + @udid + '.' + @uvid + ''' 
                    FROM (SELECT TOP 1 Date_and_Time AS date, ' + @uvid + ' AS value FROM ' + @database +'.dbo.' + @udid + '
                          WHERE Date_and_Time > CONVERT(datetime, ''' + CONVERT(VARCHAR, @stFloatHour, 120) + ''', 21)
                            AND Date_and_Time < CONVERT(datetime, ''' + CONVERT(VARCHAR, @et, 120) + ''', 21) 
                           ORDER BY date DESC ) AS A
                    WHERE value IS NOT NULL'
        print 'radiation last data'
        print @sql
        print 'radiation last data'
        EXECUTE sp_executesql
                @sql
        
        -- FETCH MEAN RADIATION OF THE LAST HOUR 
        SET @sql = 'INSERT INTO #data
                    SELECT TOP 1 ''plant.radiation.plane.device.value.yield.hour'', max(A.date), avg(A.value), ''' + @udid + '.' + @uvid + ''' FROM
                        (SELECT Date_and_Time AS date, ' + @uvid + ' AS value 
                                FROM ' + @database + '.dbo.' + @udid + '
                                WHERE Date_and_Time > CONVERT(datetime, ''' + CONVERT(VARCHAR, @stFloatHour, 120) + ''', 21)
                                AND Date_and_Time <= CONVERT(datetime, ''' + CONVERT(VARCHAR, @et, 120) + ''', 21) 
                        ) AS A'
        print @sql
        EXECUTE sp_executesql
               @sql
        
        DECLARE @em_pr_date datetime,
                @em_pr float,
                @em_ac_power float,
                @inv_pr_date datetime,
                @inv_ac_power float,
                @inv_pr float,
                @dc_power_installed integer,
                @ac_nominal_power integer,
                @radiation_power float,
                @radiation_yield_hour float,
                @radiation_yield_date datetime,
                @em_yield_hour float,
                @em_yield_hour_date datetime,
                @em_pr_floating_hour float,
                @inv_yield_date datetime,
                @inv_yield_hour float,
                @inv_pr_floating_hour float
 
        -- calculating performance ratio

        SELECT @em_pr_date = date, @em_ac_power = pvalue FROM #data WHERE pkey='plant.em.export.device.value.acpower'
        SELECT @dc_power_installed = pvalue  FROM #data WHERE pkey='plant.dc.installedpower.total'
        SELECT @ac_nominal_power = pvalue  FROM #data WHERE pkey='plant.ac.nominalpower.total'
        SELECT @radiation_power = pvalue FROM #data WHERE pkey='plant.radiation.plane.device.value.power'
        
        -- IF THE dc_power_installed is 0 then try to fallback to DC_Power_Installed and setup the 
        -- plant.dc.installedpower.total value
        IF @dc_power_installed = 0
        BEGIN
            print 'Try to fetch dc plant power from old value'
            SET @ParamDefinition = '@dataOUT integer OUTPUT'
            
            SET @sql = 'SELECT @dataOUT=Value
                        FROM ' + @database + '.dbo.xxx_Plant_Device_Properties 
                        WHERE Value_Key=''DC_Power_Installed'' '
            EXECUTE sp_executesql
                    @sql,
                    @ParamDefinition,
                    @dataOUT = @dc_power_installed OUTPUT
            
            IF @dc_power_installed > 0 
            BEGIN 
                print 'INSERT NEW @dc_power_installed: ' +  cast(@dc_power_installed as varchar(30))
                SET @sql = 'INSERT INTO ' + @database + '.DBO.xxx_Plant_Device_Properties (Value_Key, Value) 
                            VALUES(''plant.dc.installedpower.total'',''' + cast(@dc_power_installed as varchar(30)) + ''') '
                EXECUTE sp_executesql
                        @sql    
            END
        END
        -- IF the plant.ac.nominalpower.total is 0 then try to fallback to the AC_Power_Installed
        IF @ac_nominal_power = 0
        BEGIN
            print 'Try to fetch ac nominal plant power from old value'
            SET @ParamDefinition = '@dataOUT integer OUTPUT'
            
            SET @sql = 'SELECT @dataOUT=Value
                        FROM ' + @database + '.dbo.xxx_Plant_Device_Properties 
                        WHERE Value_Key=''AC_Power_Installed'' '
            EXECUTE sp_executesql
                    @sql,
                    @ParamDefinition,
                    @dataOUT = @ac_nominal_power OUTPUT
            
            IF @ac_nominal_power > 0 
            BEGIN 
                print 'INSERT NEW @ac_nominal_power: ' +  cast(@ac_nominal_power as varchar(30))
                SET @sql = 'INSERT INTO ' + @database + '.DBO.xxx_Plant_Device_Properties (Value_Key, Value) 
                            VALUES(''plant.ac.nominalpower.total'',''' + cast(@ac_nominal_power as varchar(30)) + ''') '
                EXECUTE sp_executesql
                        @sql    
            END
        END

        IF @radiation_power IS NOT NULL AND @radiation_power > 40 AND @em_ac_power > 0 AND @dc_power_installed > 0
        BEGIN
          print 'calculate plant.em.pr'
          SET @em_pr = (SELECT round((cast(@em_ac_power AS float) / cast(@dc_power_installed AS float)) / @radiation_power  * 100000, 2) AS PR)
          INSERT INTO #data  SELECT 'plant.em.pr', @em_pr_date, @em_pr, null
        END

        SELECT @radiation_yield_date = date, @radiation_yield_hour = pvalue FROM #data WHERE pkey='plant.radiation.plane.device.value.yield.hour'
        SELECT @em_yield_hour_date = date, @em_yield_hour = pvalue FROM #data WHERE pkey='plant.em.export.device.value.yield.hour'
        
        IF @em_yield_hour IS NOT NULL AND @radiation_yield_hour IS NOT NULL AND @radiation_yield_hour > 0 AND @dc_power_installed > 0
        BEGIN
            print 'calculate plant.em.pr.floating.hour'
            print cast(@em_yield_hour AS varchar)
            print cast(@dc_power_installed as varchar)
            print cast(@radiation_yield_hour as varchar)
            SET @em_pr_floating_hour = (SELECT round(@em_yield_hour / cast(@dc_power_installed AS float) / @radiation_yield_hour  * 100000, 2) AS PR)
            INSERT INTO #data SELECT 'plant.em.pr.floating.hour', @em_yield_hour_date, @em_pr_floating_hour, null
        END

        --IF @em_ac_power IS NULL 
        BEGIN
  
          SELECT @inv_pr_date = date, @inv_ac_power = pvalue FROM #data WHERE pkey='plant.inv.export.device.value.acpower'
          --SELECT @inv_pr_date = date, @inv_ac_power = pvalue FROM #data WHERE pkey='plant.inv.export.device.value.acpower.floating.hour'

          IF @radiation_power > 40 AND @dc_power_installed > 0
          BEGIN
            SET @inv_pr = (SELECT round((cast(@inv_ac_power AS float) / cast(@dc_power_installed AS float)) / @radiation_power  * 100000, 2) AS PR)
            INSERT INTO #data SELECT 'plant.inv.pr', @inv_pr_date, @inv_pr, null
          END
        END

          SET @sql = 'INSERT INTO #data
               SELECT ''plant.inv.export.device.value.yield.hour'',
                      max(date), 
                      cast(max(ac_yield) - min(ac_yield) AS bigint),
                      null
               FROM (SELECT TOP 60 * 
                     FROM tempdb..' + @database + '_plant_data_inverter_last 
                    ) AS A'
          EXECUTE sp_executesql
                  @sql
          
          --SELECT @inv_yield_date = date, @inv_yield_hour = pvalue FROM #data WHERE pkey='plant.inv.export.device.value.yield.hour'
          SELECT @inv_yield_date = date, @inv_yield_hour = pvalue FROM #data WHERE pkey='plant.inv.export.device.value.acpower.floating.hour'

          IF @radiation_yield_hour IS NOT NULL AND @radiation_yield_hour > 0 AND @dc_power_installed > 0
          BEGIN
                SET @inv_pr_floating_hour = (SELECT round(@inv_yield_hour / cast(@dc_power_installed AS float) / @radiation_yield_hour  * 100000, 2) AS PR)
                INSERT INTO #data SELECT 'plant.inv.pr.floating.hour', @inv_yield_date, @inv_pr_floating_hour, null
          END
        END

    -- ACTIVE ALARM COUNT
    SET @sql = 'INSERT INTO #data
                SELECT ''plant.alarms.active.count'', null, COUNT(*), null FROM ' + @database +'.dbo.xxx_Active_Alarms;'
    EXECUTE sp_executesql
            @sql

    -- CURRENT SUN ELEVATION 
    DECLARE @ParamDefinition3 NVARCHAR(100),
            @lat real, 
            @lon real

    SET @ParamDefinition3 = '@vOUT real OUTPUT'
                                
    SET @sql = 'SELECT TOP 1 @vOut = cast(Value AS real) FROM ' + @database + '.dbo.xxx_Plant_Device_Properties 
                WHERE Value_Key=''Latitude'' AND Value<>'''''
    EXECUTE sp_executesql
        @sql,
        @ParamDefinition3,
        @vOUT = @lat OUTPUT
    

    SET @sql = 'SELECT TOP 1 @vOut = cast(Value AS real) FROM ' + @database + '.dbo.xxx_Plant_Device_Properties 
                WHERE Value_Key=''Longitude'' AND Value<>'''''

    EXECUTE sp_executesql
        @sql,
        @ParamDefinition3,
        @vOUT = @lon OUTPUT
    


    IF @lat IS NOT NULL and @lon IS NOT NULL BEGIN
        INSERT INTO #data
                SELECT 'plant.elevation',
                       CONVERT(datetime,'' + CONVERT(VARCHAR, @et, 120) + '', 21), 
                       COMMONDB.dbo.Get_Elevation(@et, @lon, @lat), null
    END

    SET @sql = 'INSERT INTO #data
                SELECT TOP 1 ''plant.communication.date'', null, CONVERT(VARCHAR, Date_and_Time, 120), null
                FROM ' + @database + '.dbo.xxx_Plant_Device 
                WHERE Date_and_Time <= GETUTCDATE()
                ORDER BY Date_and_Time DESC'                
    EXECUTE sp_executesql
            @sql
            
    DECLARE @plant_communication_date datetime, 
            @plant_inverter_comm_date datetime

    SET @plant_communication_date = (SELECT convert(datetime, pValue, 21) FROM #data WHERE pkey='plant.communication.date')
    SET @plant_inverter_comm_date = (SELECT [date] FROM #data WHERE pkey='plant.inv.communicating')
    
    IF @plant_communication_date IS NULL AND @plant_inverter_comm_date IS NOT NULL BEGIN
        print 'insert @plant_inverter_comm_date into data table'
        INSERT INTO #data SELECT 'plant.communication.date', null, CONVERT(NVARCHAR, @plant_inverter_comm_date, 120), 'xxx_plant_device.Inverter_Power_AC'    
    END
    ELSE BEGIN
        IF @plant_communication_date IS NOT NULL AND @plant_inverter_comm_date IS NOT NULL BEGIN
            IF  @plant_communication_date < @plant_inverter_comm_date BEGIN 
                UPDATE #data SET pvalue = convert(nvarchar, @plant_inverter_comm_date, 120) WHERE pkey='plant.communication.date'
            END
        
        END 
    END

    SET @sql = 'DELETE FROM tempdb..'+ @database + '_plant_data_last WHERE pkey<>''plant.controlroom.highlight'' ';
    EXECUTE sp_executesql
            @sql
            
    SET @sql = 'INSERT INTO tempdb..'+ @database + '_plant_data_last SELECT * FROM #data';
    EXECUTE sp_executesql
            @sql

    INSERT INTO #data SELECT 'debug',null, 'no cache', null
    SELECT * FROM #data
END


