USE [COMMONDB]
GO
/****** Object:  StoredProcedure [dbo].[GET_INVERTER_STATS_2]    Script Date: 12/18/2019 14:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

    
ALTER PROCEDURE [dbo].[GET_INVERTER_STATS] (@database VARCHAR(100), @threshold VARCHAR(100), @criteria VARCHAR(100), @amount VARCHAR(10), @order VARCHAR(100))
AS BEGIN

-- Function return a list of data for each inverter
-- Parameter:
-- database: DB on which the function should run on
-- threshold: ratio of the inverter related to the mean of specific yield of all inverter.
-- criteria: only ERROR implemented at the moment
-- amount: maximal number of returning row
-- @order: [ASC|DESC]
--
-- @author Milan Rompe
-- @version 1.8.9
-- @changed
-- 2019-12-12 MR: CHANGED: fetching default radiation threshold from plant parameters
-- 2019-07-05 MR: FIXED: Bug when finding inverter with no production.
-- 2019-05-21 MR: FIXED: Problem when data_last is not working as intended when there is Data available but was not set or erased in the data last table
-- 2019-03-04 MR: FIXED: Added Inverter Power for Solarlog (COL_SL_040).
-- 2018-12-11 MR: FIXED: if the ration sensor does not exists ignore it.
-- 2018-08-30 MR: FIXED: added SMA Power VPC to the requested values
-- 2018-08-29 MR: TWEAK: if ac_power is null it will lead to an error and those inverters will be shown.
-- 2018-08-27 MR: ADDED: db_plant_data_last will be removed to sync both calculations.
-- 2018-07-05 MR: REVERTED: Changed device_class2 from P_AC to P_AC_TOTAL
-- 2018-07-05 MR: Changed device_class2 from P_AC to P_AC_TOTAL
-- 2018-06-18 MR: Fixed radiation issue when the radiation parameter is set but empty
-- 2018-01-19 MR: Fixed uses configured time interval for allowed plant communication
-- 2018-01-11 MR: Fixed Uses ac power date if there is no today yield.
-- 2018-01-11 MR: Fixed radiation data won't be take from future dates.
-- 2017-12-21 MR: Added serial from 'serial' parameter
-- 2017-12-18 MR: Fixed finds skytron energy tagged etoday 
-- 2017-12-11 MR: Fixed casted ac_power to as varchar to get correct formated float.
-- 2017-12-11 MR: Fixed issue of to small allowed amount of udid characters in the #inverter table
-- 2017-11-30 MR: Fixed finding radiation sensors from mc weblogs now.
-- 2017-06-29 MR: Fixed duplicate entries when there are more than one version of the device in the raw_device_property table
-- 2017-06-20 MR: Fixed another bug in the calculation of the total station count. The total count the to be calculated at a point where all inverters are still in progress.
-- 2017-06-15 MR: Fixed: Type could not be null. Changed to ISNULL(type, '-') 
-- 2017-06-15 MR: Fixed a calculation bug for the total station count.

SET NOCOUNT ON

    DECLARE @sql nvarchar(3000),
        @systemState varchar(75),
        @errorState varchar(75),
        @tableName varchar (200),
        @ParamDefinition NVARCHAR(500),
        @tmp varchar(2000),
        @sqlWHERE varchar(200),
        @sqlORDER varchar(100),
        @sqlTOP varchar(50),
        @udid nvarchar(200),
        @uvid nvarchar(200),
        @radiation float,
        @radiation_threshold int,
        @interval integer

    -- Fetch Radiation sensor value
    SET @uvid = NULL
    SET @udid = NULL

    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 'interval'
    print 'interval: ' + cast(@interval as varchar)
    
    IF @interval < 30 BEGIN
        SET @interval = 30
    END 

    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'
    --print @sql
    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
        
        /* test if sensor exists */
        
        
        print 'random radiation sensor:' + @udid + '.' + @uvid
    END

    IF @udid IS NOT NULL AND @uvid IS NOT NULL 
    BEGIN
        
        CREATE TABLE #data (
            RowID INT IDENTITY ( 1 , 1 ),
            pkey varchar(64) NOT NULL,
            date datetime NULL,
            pvalue varchar(64), 
            source varchar(100) NULL);
      
        -- FETCH LAST RADIATION DATA
        SET @sql = 'INSERT INTO #data
                    SELECT TOP 1 ''plant.radiation.plane.device.value.power'', 
                                 A.date, 
                                 A.value,
                                 ''' + @udid + '.' + @uvid + ''' FROM
                        (SELECT Date_and_Time AS date, ' + @uvid + ' AS value FROM ' + @database +'.dbo.' + @udid + '
                         WHERE Date_and_Time > DATEADD(MINUTE, -60, GETUTCDATE ())
                         AND Date_and_Time < GETUTCDATE()
                        ) AS A
                    WHERE value IS NOT NULL
                    ORDER BY date DESC'
        BEGIN TRY 
            EXECUTE sp_executesql
                    @sql
            SET @radiation = (SELECT pvalue FROM #data);
        END TRY
        BEGIN CATCH  
            print 'radiation table or value does not exists: ' + isnull(@udid, 'null') + ':' + isnull(@uvid, 'null') 
        END CATCH  
    END

    CREATE TABLE #inverterTable(
        RowID INT IDENTITY ( 1 , 1 ),
        date datetime NULL,
        did nvarchar(100) NOT NULL,
        alias nvarchar(64) NOT NULL,
        type  nvarchar (100) NOT NULL,
        serial nvarchar(100),
        installed_power varchar(50) NULL,
        station_id varchar(128) NULL,
        etoday float,
        specific_etoday float,
        Specific_etoday_avg float,
        ac_power float,
        station_id_count int,
        calc_limit float, 
        system_state_date datetime,
        system_state text,
        error_state_date datetime,
        error_state text);

    IF 'ERROR' = @criteria
            SET @sqlWHERE = 'WHERE CASE WHEN A.Specific_etoday_avg = 0 THEN 0 ELSE ISNULL(A.Specific_etoday/A.Specific_etoday_avg,0) END < ' + @threshold + ' OR ac_power <= 0 OR ac_power IS NULL OR Date < DATEADD(MINUTE, -60, GETUTCDATE ())'
        ELSE 
            SET @sqlWHERE = '';
        
        IF 'ASC' <> @order AND 'DESC' <> @order
            SET @sqlORDER = 'DESC'
        ELSE 
            SET @sqlORDER = @order

	SET @ParamDefinition = '@radiationOUT int OUTPUT'

	SET @sql = 'SELECT @radiationOUT = radiation 
                FROM(SELECT SUM(cast(Value AS int))AS radiation 
                         FROM '+ @database + '.dbo.xxx_Plant_Device_Properties 
                         WHERE Value_Key=''view.controlroom.threshold.radiation'') AS A'
    EXECUTE sp_executesql
            @sql,
            @ParamDefinition,
            @radiationOUT = @radiation_threshold OUTPUT

    IF @radiation_threshold IS NULL BEGIN
        SET @radiation_threshold = 80
    END 

    --only check inverter if the radiation is >= 80W/m²
    IF @radiation is NULL OR cast(@radiation as float) >= @radiation_threshold
    BEGIN

        SET @sql = 'INSERT INTO #inverterTable SELECT A.*, 
                                      CASE WHEN A.Specific_etoday_avg = 0 THEN 0 ELSE ISNULL(A.Specific_etoday/A.Specific_etoday_avg,0) END AS calc_limit, 
                                      (null) AS system_state_date, 
                                      (null) AS system_state, 
                                      (null) AS error_state_date, 
                                      (null) AS error_state
        FROM (SELECT DISTINCT ISNULL(C.Date_and_Time, B.Date_and_Time) AS date,
                  A.Table_Name AS did,
                  A.Virtual_Device_Name AS alias,
                  ISNULL(A.User_Device_Key, ''-'') AS type,
                  D.Device_Value AS serial,
                  A.Fix_Value AS installed_power,
                  A.Station_ID AS station_id,
                  CASE WHEN (B.Value_Value/ISNULL(A.Fix_Value,1)) < 24 THEN B.Value_Value ELSE null END AS etoday,
                  ROUND(CASE WHEN (B.Value_Value/ISNULL(A.Fix_Value,1)) < 24 THEN B.Value_Value/ISNULL(A.Fix_Value,1) ELSE null END, 3) AS specific_etoday,
                  AVG(ROUND(CASE WHEN (B.Value_Value/ISNULL(A.Fix_Value,1)) < 24 THEN (B.Value_Value/ISNULL(A.Fix_Value,1)) ELSE 1 END, 3)) OVER() AS Specific_etoday_avg,
                  C.Value_Value AS ac_power,
                  COUNT(A.Station_ID) OVER (PARTITION BY A.Station_ID) AS station_id_count
        FROM ' + @database + '.dbo.xxx_Virtual_Devices AS A
        LEFT JOIN ' + @database + '.dbo.xxx_last_data AS B ON A.Table_Name=B.Table_Name AND B.Value_Type =''COL_E_TODAY_CALC'' AND B.Date_and_Time > DATEADD(day, DATEDIFF(day, 0, GETUTCDATE()), 0) 
        LEFT JOIN ' + @database + '.dbo.xxx_last_data AS C ON A.Table_Name=C.Table_Name 
              AND (C.Value_Type =''COL_SR_196620'' OR C.Value_Type=''COL_SMA_027'' OR C.Value_Type=''COL_SL_040'') AND C.Date_and_Time > DATEADD(day, DATEDIFF(day, 0, GETUTCDATE()), 0)
        LEFT JOIN (SELECT Table_Name, Device_Value FROM ' + @database + '.dbo.xxx_RAW_Device_Properties 
                   WHERE ID IN (SELECT DISTINCT max(Id) OVER (PARTITION BY TABLE_NAME) AS id FROM ' + @database + '.dbo.xxx_RAW_Device_Properties 
                                WHERE DEVICE_NAME=''Serial_Number'' OR DEVICE_NAME=''serial'')) AS D ON A.Table_Name = D.Table_Name                                 
        WHERE A.Device_Type=''Inverter'' AND A.Device_Class=''P_AC'' AND A.Device_Class_2 = ''P_AC'') AS A ' + 
        @sqlWHERE + ' '

        --print @sql
        EXECUTE sp_executesql @sql
        print 'sql executed'
    END

    IF @amount IS NOT NULL AND ISNUMERIC(@amount) = 1
        SET @sqlTOP = ' TOP ' + @amount
    ELSE
        SET @sqlTOP = ''

    DECLARE @inverter_did nvarchar(200),
            @inverter_alias nvarchar(200),
            @ParamDefinition2 NVARCHAR(500),
            @ac_power bigint,
            @dateout datetime

    --SELECT did, alias FROM #inverterTable AS A WHERE ac_power IS NULL ORDER BY alias ASC

    -- we need to check if the inverter is offline if the ac_power is NULL since there might be events which cause 
    DECLARE data_cursor CURSOR LOCAL FAST_FORWARD FOR
       SELECT did, alias FROM #inverterTable AS A WHERE ac_power IS NULL ORDER BY alias ASC
    OPEN data_cursor

    FETCH NEXT FROM data_cursor INTO @inverter_did, @inverter_alias
    print @inverter_did + ' ' + @inverter_alias

    
    SET @ParamDefinition = '@uvidOUT nvarchar(50) OUTPUT'
    SET @ParamDefinition2 = '@dateOUT datetime OUTPUT, 
                             @valueOUT bigint OUTPUT'
    
    SET @sql = 'SELECT @uvidOUT=Column_Name FROM ' + @database + '.dbo.xxx_Virtual_Devices WHERE Table_Name=''' + @inverter_did + ''' AND Device_Class=''P_AC'' AND Device_Class_2=''P_AC'' '

    --print @sql

    IF @@FETCH_STATUS = 0
    BEGIN
      EXECUTE sp_executesql
              @sql,
              @ParamDefinition,
              @uvidOUT = @uvid OUTPUT
              
              
              
      SET @sql = 'SELECT TOP 1 @dateOUT=Date_And_Time, @valueOUT=' + @uvid + ' 
                  FROM ' + @database + '.dbo.' + @inverter_did + '
                  WHERE Date_And_Time > DATEADD(minute, -' + cast(@interval AS nvarchar) +', GETUTCDATE())
                  AND Date_and_Time < GETUTCDATE() AND ' + @uvid + ' IS NOT NULL'
      print @sql
      EXECUTE sp_executesql
              @sql,
              @ParamDefinition2,
              @dateOUT = @dateout OUTPUT, 
              @valueOUT = @ac_power OUTPUT
      IF @ac_power IS NOT NULL BEGIN
        print 'date:' + convert(nvarchar, @dateout, 120) + 'ac power: ' + ISNULL(cast(@ac_power AS nvarchar(100)), 'null')
        DELETE FROM #inverterTable WHERE did=@inverter_did
      END
    END

    WHILE @@FETCH_STATUS = 0
      BEGIN
        FETCH NEXT FROM data_cursor INTO  @inverter_did, @inverter_alias

        print @inverter_did + ' ' + @inverter_alias
        SET @sql = 'SELECT @uvidOUT=Column_Name FROM ' + @database + '.dbo.xxx_Virtual_Devices AS A WHERE Table_Name=''' + @inverter_did + ''' AND Device_Class=''P_AC'' AND Device_Class_2=''P_AC'' '

        --print @sql
        IF @@FETCH_STATUS = 0
        BEGIN
            EXECUTE sp_executesql
                    @sql,
                    @ParamDefinition,
                    @uvidOUT = @uvid OUTPUT
                    
             SET @ac_power = NULL
             SET @sql = 'SELECT TOP 1 @dateOUT=Date_And_Time, @valueOUT=' + @uvid + ' 
                         FROM ' + @database + '.dbo.' + @inverter_did + '
                         WHERE Date_And_Time > DATEADD(minute, -' + cast(@interval AS nvarchar) +', GETUTCDATE())
                         AND Date_and_Time < GETUTCDATE() AND ' + @uvid + ' IS NOT NULL'
             print @sql
             EXECUTE sp_executesql
                    @sql,
                    @ParamDefinition2,
                    @dateOUT = @dateout OUTPUT, 
                    @valueOUT = @ac_power OUTPUT
             IF @ac_power IS NOT NULL BEGIN
               print 'date:' + convert(nvarchar, @dateout, 120) + 'ac power: ' + ISNULL(cast(@ac_power AS nvarchar(100)), 'null')
               DELETE FROM #inverterTable WHERE did=@inverter_did
             END
        END
      END
    CLOSE data_cursor
    DEALLOCATE data_cursor

    SET @sql = 'SELECT RowID,
                       date,
                       did,
                       alias,
                       type,
                       serial,
                       installed_power,
                       station_id,
                       etoday,
                       specific_etoday,
                       cast(Specific_etoday_avg AS varchar) AS Specific_etoday_avg,
                       cast(ac_power AS varchar) AS ac_power,
                       station_id_count,
                       calc_limit, 
                       system_state_date,
                       system_state,
                       error_state_date,
                       error_state FROM #inverterTable
                       WHERE ac_power <= 0 OR ac_power IS NULL OR date IS NULL OR date < DATEADD(minute, -' + cast(@interval AS nvarchar) +', GETUTCDATE())
                       ORDER BY alias ' + @sqlORDER + ', etoday ' + @sqlORDER 
    --print @sql
    EXECUTE sp_executesql @sql

    SET @sql = 'DELETE FROM tempdb.dbo.' + @database + '_plant_data_last
                DELETE FROM tempdb.dbo.' + @database + '_plant_data_inverter_last'
    EXECUTE sp_executesql @sql
END



