USE [COMMONDB]
GO
/****** Object:  StoredProcedure [dbo].[GET_PLANT_INVERTER]    Script Date: 05/31/2018 15:02:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GET_PLANT_INVERTER](@database varchar(50), @interval int) AS 
BEGIN 
-- This function returns the ac power of all inverter in the plant available
-- @author Milan Rompe
-- @version 1.7.2
-- @since 2016-10-10
-- Changelog
--
-- 2018-09-11 MR: fixed calculation of total available power even if the etoday yield value is not present
-- 2018-08-20 MR: roolback to avoid a cast exception.
-- 2018-05-31 MR: changed nominal_power_available into percentage 
-- 2018-05-18 MR: fixed a glitch with the nominal power in the second pass
-- 2018-01-02 MR: fixed timestamp issue with datarequest
-- 2017-10-25 MR: improved filter for inverter power value.
-- 2017-06-14 MR: grep a second row of the inverter an hour before to have 2 entries in the inverter table
-- 2017-06-13 MR: just grep only one data point of the last available data withhin 15 Min + data resolution + send interval minutes

SET NOCOUNT ON

DECLARE @sql nvarchar(3000),
        @udid nvarchar(100),
        @ac_power_col varchar(100),
        @ac_yield_col varchar(100), 
        @ac_nominal_power int,
        @date datetime = GETUTCDATE(),
        @ac_nominal_power_max float

CREATE TABLE #inverter(
    udid nvarchar(100) NULL,
    ac_power_uvid nvarchar(64) NOT NULL,
    ac_yield_uvid nvarchar(64) DEFAULT NULL,
    ac_nominal_power int);

CREATE TABLE #datainv(
    date datetime,
    ac_power float,
    ac_yield float,
    inv_total_count int,
    inv_available int, 
    nominal_power_available int)

    print '[GET_PLANT_INVERTER2]'
    SET @date = GETUTCDATE()
    SET @date = DATEADD(ms, -DATEPART(ms, @date), @date)
    SET @date = DATEADD(ss, -DATEPART(ss, @date), @date)

    -- fill #datainv table with timestamps
    print 'insert timestamps into #datainv table: ' + convert(varchar, @date, 121)
    SET @sql = 'INSERT INTO #datainv (date) VALUES(CONVERT(datetime, ''' + CONVERT(varchar, @date, 121) + ''', 21))'
    EXECUTE sp_executesql
            @sql
            
    print '@interval: ' + cast(@interval as varchar)
    IF @interval < 15 BEGIN
        SET @interval = 15
    END 

    SET @date = DATEADD(mi, -@interval, @date)
    print 'interval'
    print -@interval
    print 'date minus interval: ' 
    print CONVERT(varchar, @date, 121)

    SET @sql = 'INSERT INTO #inverter
                SELECT DISTINCT Table_Name as udid, Column_Name AS ac_power_uvid, null, Maximum_Value AS nominal_power
                FROM ' + @database + '.dbo.xxx_Virtual_Devices
                WHERE Device_Type=''Inverter'' AND Device_Class=''P_AC'' AND (Device_Class_2 =''P_AC'' OR Device_Class_2=''P_AC_TOTAL'')'
    print  @sql
    EXECUTE sp_executesql
            @sql

    SET @sql = 'UPDATE #inverter SET ac_yield_uvid=Column_Name 
                FROM ' + @database + '.dbo.xxx_Virtual_Devices AS A
                    
                    INNER JOIN  #inverter AS B ON A.Device_Type=''Inverter'' 
                                              AND A.Device_Class=''E_TODAY'' 
                                              AND A.Table_Name=B.udid
                WHERE A.Table_Name=B.udid'
    EXECUTE sp_executesql
            @sql

    DECLARE data_cursor CURSOR LOCAL FAST_FORWARD FOR
       SELECT * FROM #inverter AS A
    OPEN data_cursor

    FETCH NEXT FROM data_cursor INTO @udid, @ac_power_col, @ac_yield_col, @ac_nominal_power

    IF @ac_nominal_power IS NULL BEGIN
        SET @ac_nominal_power = 0
    END

    print 'ac_power: ' + cast(@ac_power_col as varchar)
    print 'ac_yield: ' + cast(@ac_yield_col as varchar)
    print '@ac_nominal_power: ' + cast(@ac_nominal_power as varchar)
    print '@ac_yield_col: ' + @ac_yield_col
    print '@ac_power_col: ' + @ac_power_col
    print '@database: ' + @database
    print '@udid: ' + @udid
    print '@date: ' + CONVERT(varchar, @date, 121)
    
    
    SET @sql = 'UPDATE #datainv
                    SET ac_power=B.ac_power, 
                        ac_yield=B.ac_yield,
                        inv_total_count=B.inv_total_count, 
                        inv_available=B.inv_available,
                        nominal_power_available = B.nominal_power_available
                    FROM 
                        (SELECT date, 
                           ac_power, 
                           ac_yield, 
                           1 AS inv_total_count,  
                           CASE WHEN ac_power IS NULL OR ac_power <= 0 THEN 0 ELSE 1 END AS inv_available, 
                           ' + cast(@ac_nominal_power AS varchar(20)) + ' AS nominal_power_available
                           FROM (SELECT Date_and_Time as date,
                                            ' + @ac_power_col + ' AS ac_power
                                            ,' + ISNULL(@ac_yield_col, 0) + ' AS ac_yield
                                     FROM ' + @database + '.dbo.' + @udid + '
                                     WHERE Date_and_time =(SELECT max(Date_and_Time)
                                                           FROM ' + @database + '.dbo.' + @udid + '
                                                           WHERE Date_and_time > CONVERT(datetime, ''' +  CONVERT(varchar, @date, 121) + ''', 21) 
                                                           AND Date_and_time < GETUTCDATE() 
                                                           AND ' + @ac_power_col + ' IS NOT NULL )) AS A
                          ) AS B'
    EXECUTE sp_executesql
            @sql
    --print 'sql statement first phase'
    --print @sql

    WHILE @@FETCH_STATUS = 0
      BEGIN
        FETCH NEXT FROM data_cursor INTO @udid, @ac_power_col, @ac_yield_col, @ac_nominal_power
        
        IF @ac_nominal_power IS NULL BEGIN
            SET @ac_nominal_power = 0
        END
        --print 'next inverter: ' + @udid
        --print '@ac_power_col: ' + @ac_power_col
        --print '@ac_yield_col: ' + @ac_yield_col
        --print '@database: ' + @database
        --print '@ac_nominal_power: ' + cast(@ac_nominal_power as varchar)
        SET @sql = 'UPDATE #datainv 
                    SET ac_power= ISNULL(ac_power,0) + pac, 
                        ac_yield= ISNULL(ac_yield,0) + eac,
                        inv_total_count= ISNULL(inv_total_count, 0) + itc, 
                        inv_available= ISNULL(inv_available,0) + ia,
                        nominal_power_available = ISNULL(nominal_power_available,0) + npa
                    FROM (SELECT date, 
                                      ac_power AS pac, 
                                      ac_yield AS eac, 
                                      1 AS itc, 
                                      CASE WHEN ac_power IS NULL OR ac_power <= 0 THEN 0 ELSE 1 END AS ia,
                                      CASE WHEN ac_power IS NULL OR ac_power <= 0 THEN 0 ELSE ' + cast(@ac_nominal_power AS varchar(20)) + ' END AS npa
                               FROM (SELECT Date_and_Time as date,
                                            ' + @ac_power_col + ' AS ac_power,
                                            ' + ISNULL(@ac_yield_col, 0) + ' AS ac_yield
                                     FROM ' + @database + '.dbo.' + @udid + '
                                     WHERE Date_and_time =(SELECT max(Date_and_Time)
                                                           FROM ' + @database + '.dbo.' + @udid + '
                                                           WHERE Date_and_time > CONVERT(datetime, ''' +  CONVERT(varchar, @date, 121) + ''', 21) 
                                                           AND Date_and_time < GETUTCDATE() 
                                                           AND ' + @ac_power_col + ' IS NOT NULL )) AS A
                        ) AS B'
        --print @sql
        IF @@FETCH_STATUS = 0 
        BEGIN
            EXECUTE sp_executesql
                @sql
        END
      END
    CLOSE data_cursor
    DEALLOCATE data_cursor


    -- start second pass - fetch data one hour before

    DECLARE @tmpdate datetime = (SELECT date FROM #datainv),
            @hourdate datetime,
            @hourdateStart datetime

    SET @hourdate = DATEADD(HOUR, -1, @tmpdate)
    SET @hourdateStart = DATEADD(HOUR, -2, @tmpdate)
    
    print '@hourdate: ' + convert(varchar, @hourdate, 21)
    print '@hourdateStart: ' + convert(varchar, @hourdateStart, 21)

    SET @sql = 'INSERT INTO #datainv (date) VALUES(CONVERT(datetime, ''' + CONVERT(varchar, @hourdate, 121) + ''', 21))'
    EXECUTE sp_executesql
            @sql

    DECLARE data_cursor CURSOR LOCAL FAST_FORWARD FOR
       SELECT * FROM #inverter AS A
    OPEN data_cursor

    FETCH NEXT FROM data_cursor INTO @udid, @ac_power_col, @ac_yield_col, @ac_nominal_power

    IF @ac_nominal_power IS NULL BEGIN
        SET @ac_nominal_power = 0
    END

    SET @sql = 'UPDATE #datainv
                    SET ac_power=B.ac_power, 
                        ac_yield=B.ac_yield,
                        inv_total_count=B.inv_total_count, 
                        inv_available=B.inv_available,
                        nominal_power_available = B.nominal_power_available
                    FROM 
                        (SELECT date, 
                           ac_power, 
                           ac_yield, 
                           1 AS inv_total_count,  
                           CASE WHEN ac_power IS NULL OR ac_power <= 0 THEN 0 ELSE 1 END AS inv_available, 
                           ' + cast(@ac_nominal_power AS varchar(20)) + ' AS nominal_power_available
                           FROM (SELECT Date_and_Time as date,
                                            ' + @ac_power_col + ' AS ac_power,
                                            ' + @ac_yield_col + ' AS ac_yield
                                     FROM ' + @database + '.dbo.' + @udid + '
                                     WHERE Date_and_time =(SELECT max(Date_and_Time)
                                                           FROM ' + @database + '.dbo.' + @udid + '
                                                           WHERE Date_and_time < CONVERT(datetime, ''' +  CONVERT(varchar, @hourdate, 121) + ''', 21) 
                                                             AND Date_and_time > CONVERT(datetime, ''' +  CONVERT(varchar, @hourdateStart, 121) + ''', 21)
                                                           AND ' + @ac_power_col + ' IS NOT NULL )) AS A
                          ) AS B
                    WHERE #datainv.date=CONVERT(datetime, ''' +  CONVERT(varchar, @hourdate, 121) + ''', 21)' 
    EXECUTE sp_executesql
            @sql
    --print @sql

    WHILE @@FETCH_STATUS = 0
      BEGIN
        FETCH NEXT FROM data_cursor INTO @udid, @ac_power_col, @ac_yield_col, @ac_nominal_power

		IF @ac_nominal_power IS NULL BEGIN
            SET @ac_nominal_power = 0
        END

        SET @sql = 'UPDATE #datainv 
                    SET ac_power= ISNULL(ac_power,0) + pac, 
                        ac_yield= ISNULL(ac_yield,0) + eac,
                        inv_total_count= ISNULL(inv_total_count, 0) + itc, 
                        inv_available= ISNULL(inv_available,0) + ia,
                        nominal_power_available = ISNULL(nominal_power_available,0) + npa
                    FROM (SELECT date, 
                                      ac_power AS pac, 
                                      ac_yield AS eac, 
                                      1 AS itc, 
                                      CASE WHEN ac_power IS NULL OR ac_power <= 0 THEN 0 ELSE 1 END AS ia,
                                      CASE WHEN ac_power IS NULL OR ac_power <= 0 THEN 0 ELSE ' + cast(@ac_nominal_power AS varchar(20)) + ' END AS npa
                               FROM (SELECT Date_and_Time as date,
                                            ' + @ac_power_col + ' AS ac_power,
                                            ' + @ac_yield_col + ' AS ac_yield
                                     FROM ' + @database + '.dbo.' + @udid + '
                                     WHERE Date_and_time =(SELECT max(Date_and_Time)
                                                           FROM ' + @database + '.dbo.' + @udid + '
                                                           WHERE Date_and_time < CONVERT(datetime, ''' +  CONVERT(varchar, @hourdate, 121) + ''', 21) 
                                                             AND Date_and_time > CONVERT(datetime, ''' +  CONVERT(varchar, @hourdateStart, 121) + ''', 21)
                                                           AND ' + @ac_power_col + ' IS NOT NULL )) AS A
                        ) AS B
                    WHERE #datainv.date=CONVERT(datetime, ''' +  CONVERT(varchar, @hourdate, 121) + ''', 21)' 
        --print @sql
        IF @@FETCH_STATUS = 0 
        BEGIN
            EXECUTE sp_executesql
                @sql
        END
      END

    CLOSE data_cursor
    DEALLOCATE data_cursor

    -- end of second pass

    UPDATE #datainv SET nominal_power_available=round(cast(nominal_power_available * 100.0 AS float) / A.max_power, 2) FROM (SELECT SUM(ac_nominal_power) AS max_power FROM #inverter) AS A
    --SELECT date,ac_power,ac_yield, inv_total_count, inv_available, cast(nominal_power_available as varchar) FROM #datainv --WHERE ac_power IS NOT NULL ORDER BY date ASC
    SELECT * FROM #datainv 
END
