USE [COMMONDB]
GO
CREATE PROCEDURE [dbo].[GET_PLANT_INVERTER_LAST](@database varchar(50)) AS 
BEGIN
-- This function retuns the latest plant ac power buffered into a global temp table
-- @author Milan Rompe
-- @version 1.1
-- @since 2016-10-10
--
-- @changed  MR 2017-06-13 - Update of _plant_data_inverter_last with new function which only contains one line any longer.
--           MR 2017-03-29 - Added idx on tempdb..'+ @database + '_plant_data_inverter_last table to avoid double inserts.
--           
SET NOCOUNT ON

DECLARE @sql nvarchar(2000),
        @ParamDefinition NVARCHAR(500),
        @interval integer,
        @date datetime,
        @ac_power bigint,
        @inv_total_count int,
        @inv_available int,
        @lazyLoadInterval int = 5 -- refresh ac total power at least all 5 minutes

    SET @ParamDefinition = '@intervalOUT int OUTPUT'

    SET @sql = 'SELECT @intervalOUT = interval 
                FROM(SELECT CASE WHEN interval < 30 THEN 30 ELSE interval END AS interval
                     FROM (SELECT SUM(cast(Value AS int)) + 15 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
    
    -- create global temp table if not exist
    IF OBJECT_ID('tempdb..'+ @database + '_plant_data_inverter_last') IS NULL
    BEGIN
        SET @sql = 'USE tempdb CREATE TABLE ' + @database + '_plant_data_inverter_last(
                        date datetime,
                        ac_power float,
                        ac_yield float,
                        inv_communicating int,
                        inv_producing int, 
                        nominal_power_available int, 
                        CONSTRAINT ' + @database + '_plant_data_inverter_last_data_idx UNIQUE (date) );'
        EXECUTE sp_executesql
                @sql
    END

    SET @ParamDefinition = '@dateOUT nvarchar(100) OUTPUT'

    SET @sql = 'SELECT TOP 1 @dateOUT = date
                FROM tempdb..' + @database + '_plant_data_inverter_last
                ORDER BY date DESC'
    --print @sql
    EXECUTE sp_executesql
            @sql,
            @ParamDefinition,
            @dateOUT = @date OUTPUT

    -- update global temp table only if the table is empty or the data is older than 5 Minutes
    --print convert(varchar, @date, 120)
    --print convert(varchar, GETUTCDATE(), 120)
    --print 'datediff: ' + cast(DATEDIFF(minute, @date, GETUTCDATE()) AS varchar)
    IF @date IS NULL OR DATEDIFF(minute, @date, GETUTCDATE()) > @lazyLoadInterval
    BEGIN
        --print 'GET_PLANT_INVERTER_LAST: update data'

        SET @sql = 'DELETE FROM tempdb..' + @database + '_plant_data_inverter_last'
        EXECUTE sp_executesql
            @sql
            
        --print 'update temp table'
        SET @sql = 'INSERT INTO tempdb..' + @database + '_plant_data_inverter_last
                    EXEC COMMONDB.dbo.GET_PLANT_INVERTER ''' + @database + ''', ''' + cast(@interval AS varchar) + ''' '

        EXECUTE sp_executesql
            @sql
        
    END
END