USE [COMMONDB]
GO
/****** Object:  StoredProcedure [dbo].[GET_PREDEFINED_YEARLY_YIELD_PLANT_DATA]    Script Date: 10/12/2018 11:30:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GET_PREDEFINED_YEARLY_YIELD_PLANT_DATA](@database varchar(50), @date varchar(50), @years integer) AS 
BEGIN 
-- This function calculates the total yield of a month for the amount of requested years. 
-- standard xml protocol with get_prefefined_yield_yearly_data for example
-- @author Milan Rompe
-- @version 1.6.2
-- @since 2017-02-01
-- @param date - date to be requested in plant time zone. Example: '2017-01-24 00:00:00' 
--             - month, day, hour, minutes and seconds will be ignored
-- @param years - the amount of days to be calculated

-- 2018-10-12 - MR: fixed yield calculation by energy meter ac power.
-- 2018-10-09 - MR: fixed failed execution if there is no em yield given.
-- 06.10.2017 - MR: CAST yield into integer to avoid floating point value in response 
-- 05.10.2017 - MR: removed sql statement SELECT * FROM #pdata 
-- 26.09.2017 - MR: Requests the inverter directly and sums the 15 Minutes mean ac power values
-- 08.08.2017 - MR: Changed GET_TZ_DATE_FROM_UTC_DATE to GET_TZ_DATE_FROM_UTC_DATE_2 for implementation of day light saving 
-- 08.02.2017 - Yearly Yield will only called once a day. Fixed the last day of a year update bug.
-- 06.02.2017 - Added data cache. Current year will be recalculated each time. 

SET NOCOUNT ON

DECLARE @sql nvarchar(2000),
        @selectSQL nvarchar(2000),
        @ParamDefinition NVARCHAR(500),
        @udid nvarchar(100) = null,
        @uvid nvarchar(100) = null,
        @st datetime,
        @et datetime,
        @stUTC datetime,
        @etUTC datetime,
        @stStr varchar(50),
        @etStr varchar(50),
        @interval integer = 1,
        @plantTZOffset integer,
        @plantTZDayLightSaving varchar(10),
        @dc_power_installed float,
        @mSt datetime, 
        @mEt datetime,
        @mStUTC datetime, 
        @mEtUTC datetime,
        @mStStr nvarchar(50),
        @mEtStr nvarchar(50),
        @ac_power_col varchar(100)
        

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

    -- Setup startdate and enddate in utc
    SET @et = convert(datetime, @date, 120)
    -- reset hour, minute, seconds and millis
    SET @et = DATEADD(YEAR, datediff(YEAR, 0, @et) + 1, 0);

    -- add one year
    SET @mSt = DATEADD(YEAR, -1, @et);
    SET @mEt = @et
    
    -- minus years for starttime
    SET @st = DATEADD(YEAR, -@years, @et);

    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 @stStr
    print @etStr
    
    SET @mStUTC = COMMONDB.DBO.GET_UTC_DATE_FROM_TZ_DATE_2(@mSt, @plantTZOffset, @plantTZDayLightSaving)
    SET @mEtUTC = COMMONDB.DBO.GET_UTC_DATE_FROM_TZ_DATE_2(@mEt, @plantTZOffset, @plantTZDayLightSaving)

    SET @mStStr = CONVERT(VARCHAR, @mStUTC, 120)
    SET @mEtStr = CONVERT(VARCHAR, @mEtUTC, 120)

    print 'mSt: ' + @mStStr
    print 'mEt: ' + @mEtStr

    -- CHECK CACHE CONTENT
    IF OBJECT_ID('tempdb..'+ @database + '_plant_yearly_data') IS NULL BEGIN
        print 'create tmp table'
        SET @sql = 'USE tempdb CREATE TABLE ' + @database + '_plant_yearly_data(
                               date datetime PRIMARY KEY,
                               yield bigint, 
                               radyield bigint, 
                               pr float, 
                               targetyield bigint, 
                               updatetime datetime)'
        print @sql                      
        EXECUTE sp_executesql
                @sql
        -- we need to fetch all data
    END
    ELSE BEGIN
        print 'tmp yield table exist. update only current year if the month is outdated.'
        
        SET @et = @mEt;
        SET @st = @mSt;
        
        SET @etUTC = @mEtUTC;
        SET @stUTC = @mStUTC;
        
        SET @stStr = @mStStr;
        SET @etStr = @mEtStr;
        
        --
        DECLARE @updatetime datetime,
                @dirtydate  datetime

        SET @ParamDefinition = '@dirtyOUT datetime OUTPUT, 
                                @updatetimeOUT datetime OUTPUT'

        -- returns the oldest date of dirty months. Those month need to be recalculated
        SET @sql = 'SELECT TOP 1 @dirtyOUT = date, @updatetimeOUT = updatetime FROM
                        (SELECT date, updatetime,
                            DATEPART(YEAR,updatetime) - DATEPART(YEAR,date) AS yeardiff
                      FROM tempdb..'+ @database + '_plant_yearly_data) AS A
                    WHERE A.yeardiff = 0
                    ORDER BY date ASC'
        EXECUTE sp_executesql
                @sql,
                @ParamDefinition,
                @dirtyOUT = @dirtydate OUTPUT,
                @updatetimeOUT = @updatetime OUTPUT
        print @sql
        -- @dirtydate is the date we need to update to.
        print '@dirtydate: ' + convert(varchar, @dirtydate, 21)
        print '@updatetime: ' + convert(varchar, @updatetime, 21)
        
        --check if the @dirtydate is the same month as @date
        IF DATEPART(YEAR, @dirtydate) = DATEPART(YEAR, @date) BEGIN
            -- if the age of the @updatetime is below one day, do not update tmp
            -- table. Just return the current tmp tbl 
            print 'cache is inactive'
                
            IF DATEDIFF(mi, @updatetime, GETDATE()) < 1440 BEGIN

                SET @sql = 'SELECT date, 
                              cast(yield*0.001 AS integer) AS yield, 
                              CAST(radyield AS nvarchar) AS radyield, 
                              CAST(pr AS nvarchar) AS pr, 
                              targetyield*0.001 AS targetyield 
                            FROM tempdb.dbo.' + @database + '_plant_yearly_data'
                EXECUTE sp_executesql
                        @sql
                print 'use cached data only'
                RETURN
            END

        END
        -- update the @stStr to the @dirtydate
        IF @dirtydate IS NOT NULL BEGIN
            SET @st = @dirtydate;
            SET @stUTC = COMMONDB.DBO.GET_UTC_DATE_FROM_TZ_DATE_2(@st, @plantTZOffset, @plantTZDayLightSaving)
            SET @stStr = CONVERT(VARCHAR, @stUTC, 120)
            print 'use @dirtydate as startdate'
        END
    END

    -- 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
           @st AS st, 
           DATEADD(YEAR, @interval, @st) AS et
        UNION ALL
        SELECT
          et, 
          DATEADD(YEAR, @interval, et)
        FROM cSequence 
        WHERE DATEADD(YEAR, @interval, et) <= @et
    )
    
    INSERT INTO #pdata (date, yield, radyield, pr, targetyield)
    SELECT st, null, null, null, null FROM cSequence OPTION (MAXRECURSION 0);
    
    SET @ParamDefinition = '@udidOUT nvarchar(100) OUTPUT,
                            @uvidOUT nvarchar(100) OUTPUT'
    
    -- FETCH YIELD data from energy meter if any is defined as plant energy meter
    SET @uvid = NULL
    SET @udid = NULL
      
    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'

    EXECUTE sp_executesql
            @sql,
            @ParamDefinition,
            @udidOUT = @udid OUTPUT,
            @uvidOUT = @uvid OUTPUT
      
      
    print 'em yield: ' + @udid + '.' + @uvid
    DECLARE @invertACPower varchar(10),
            @ParamDefinition2 NVARCHAR(100),
            @invertSQL varchar(10),
            @isACPower int = 0
              
    -- If we dont have a
    IF @udid IS NULL OR @uvid IS NULL BEGIN
		CREATE TABLE #inverter(
			udid nvarchar(100) NULL,
			ac_power_uvid nvarchar(64) NOT NULL)
       
		-- Try to use ac power from energy meter        
        print 'try to fetch energy meter ac power from plant parameter'
        -- 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
    
	    IF @udid IS NULL OR @uvid IS NULL BEGIN
			SET @udid = 'xxx_Plant_Device'
			SET @uvid = 'Inverter_Power_AC'
			

			-- fetch all inverters from this plant   
			SET @sql = 'INSERT INTO #inverter
					SELECT DISTINCT Table_Name as udid, Column_Name AS ac_power_uvid
					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'')'
			EXECUTE sp_executesql
					@sql
	    END
	    ELSE BEGIN
			print 'calculating power by energy meter ac power'
			INSERT INTO #inverter (udid, ac_power_uvid) VALUES(@udid,@uvid)
	    END
	    SET @isACPower = 1
    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'''

      EXECUTE sp_executesql
              @sql,
              @ParamDefinition2,
              @invertOUT = @invertACPower OUTPUT
        
      IF @invertACPower = 'true' BEGIN
        SET @invertSQL = '*-1'
      END
      ELSE BEGIN
        SET @invertSQL = ''
      END 
    END
      
    -- for yield calculation values are selected 
    IF @isACPower = 0 BEGIN
      print 'yield calculation by energy meter yield'
      SET @selectSQL = 'SELECT DATEADD(YEAR, datediff(YEAR, 0, B.date), 0) AS date, B.value 
                          FROM (SELECT MIN(A.date) AS date, 
                                         MAX(A.value) - MIN(A.value) AS value
                                  FROM (SELECT COMMONDB.DBO.GET_TZ_DATE_FROM_UTC_DATE_2(Date_and_time, 0, ''' + @plantTZDayLightSaving + ''') AS date, 
                                               ' + @uvid + ' AS value 
                                          FROM ' + @database + '.dbo.' + @udid + ' 
                                          WHERE Date_and_time >= convert(datetime,  ''' + @stStr + ''',21)
                                            AND Date_and_time < convert(datetime, ''' + @etStr + ''',21)) AS A
                                  GROUP BY DATEPART(YEAR, A.date)
                                           ) AS B'
                                           
      SET @sql = 'UPDATE #pdata 
                    SET yield = A.value
                  FROM (' + @selectSQL + ') AS A
                  WHERE #pdata.date = A.date'
      EXECUTE sp_executesql
        @sql
    END
    ELSE BEGIN
        print 'SYSD 8: ' + convert(nvarchar, SYSDATETIME(), 21)
        print 'yield calculation by plant ac power'

        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
        print 'SYSD 9: ' + convert(nvarchar, SYSDATETIME(), 21)
        WHILE @@FETCH_STATUS = 0 BEGIN
            SET @selectSQL = 'SELECT min(B.date) as date, sum(B.ac_power)*0.25 AS value
                        FROM (SELECT MIN(A.date) AS date, AVG(ac_power) AS ac_power 
                                 FROM (SELECT Date_and_Time AS date, ' + @ac_power_col + ' AS ac_power
                                                FROM ' + @database + '.dbo.' + @udid + '
                                                WHERE Date_and_time >= CONVERT(datetime, ''' +  @stStr + ''', 21)
                                                AND Date_and_time < CONVERT(datetime, ''' + @etStr + ''',21) 
                                                AND ' + @ac_power_col + ' IS NOT NULL) AS A
                                       GROUP BY DATEPART(YEAR, A.date),
                                                DATEPART(MONTH, A.date),
                                                DATEPART(DAY, A.date),
                                                DATEPART(HOUR, A.date),
                                                (DATEPART(MINUTE, A.date) / 15) 
                        ) AS B
                        GROUP BY DATEPART(YEAR, B.date)'
                                 
            --print @selectSQL
            
            SET @sql = 'UPDATE #pdata 
                          SET yield = ISNULL(yield,0) + ISNULL(C.value,0)
                        FROM (' + @selectSQL + ') AS C
                        WHERE DATEPART(YEAR, #pdata.date) = DATEPART(YEAR, C.date)'
            --print @sql
            EXECUTE sp_executesql
                    @sql
            
            FETCH NEXT FROM data_cursor INTO @udid, @ac_power_col
            print 'SYSD loop: ' + convert(nvarchar, SYSDATETIME(), 21)
        END
        print 'SYSD 10: ' + convert(nvarchar, SYSDATETIME(), 21)      
      

    END
    print @selectSQL
    
              
              
    -- 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) + 'kWp'


    -- drop requested data from materialized view
    SET @sql = 'DELETE FROM tempdb.dbo.' + @database + '_plant_yearly_data
                WHERE date >= cast(convert(datetime,  ''' + @stStr + ''',21) AS DATE)
                  AND date < cast(convert(datetime,  ''' + @etStr + ''',21) AS DATE);'
    print @sql                      
    EXECUTE sp_executesql
            @sql

    -- insert new calculated data 
    SET @sql = 'INSERT INTO tempdb.dbo.' + @database + '_plant_yearly_data
                (date, yield, radyield, pr, targetyield, updatetime) 
                SELECT date, yield, radyield, pr, targetyield, GETDATE() FROM #pdata'
    print @sql
    EXECUTE sp_executesql
            @sql

    --UPDATE #pdata SET pr = CASE WHEN radyield <= 0 THEN null ELSE round((yield/10.0/@dc_power_installed) / CASE WHEN radyield/1000.0 = 0 THEN 1 ELSE radyield/1000.0 END, 2) END
    --UPDATE #pdata SET pr = null WHERE pr <= 0 OR pr > 100
    --UPDATE #pdata SET acpower = 0 WHERE acpower < 0
    --UPDATE #pdata SET radyield = 0 WHERE radyield < 0

    --DECLARE @avgpr float,
    --        @avgmonthlypr float

    --SET @avgpr = (SELECT SUM(yield)/@dc_power_installed  / (SUM(radyield)) FROM #pdata)
    --TODO we need to check the plausibility of the pr. We can to that again the monthly PR
    --SET @avgmonthlypr = (SELECT avg(CASE WHEN pr < 100 THEN pr ELSE null END)/100 FROM #pdata)
    --IF @avgpr > @avgmonthlypr BEGIN
    --  print 'avg is greater then @avgmonthlypr'
    --  SET @avgpr = @avgmonthlypr
    --END
    --IF @avgpr > 100 BEGIN
    --  SET @avgpr = 100
    --END

    --UPDATE #pdata SET targetyield=radyield*@dc_power_installed*@avgpr
    SET @sql = 'SELECT date,
                       CAST(yield*0.001 AS integer) AS yield,
                       CAST(radyield AS nvarchar) AS radyield,
                       CAST(pr AS nvarchar) AS pr,
                       targetyield*0.001 AS targetyield
                  FROM tempdb.dbo.' + @database + '_plant_yearly_data'
    print @sql
    EXECUTE sp_executesql
            @sql

END
