# -*- coding: utf-8 -*- 
import pymssql


def getFunction():
    
    sql= ("""
   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.3
-- @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

-- 2020-08-31 - MR: convert ac power sum to bigint to avoid an overflow of int 
-- 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, cast(' + @ac_power_col + ' AS bigint) 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


""")

    return sql

def executeQuery(db):

    conn = pymssql.connect(server=db, user='be4visionadmin', password='cdg4s!fcv')
    cur = conn.cursor()

    cur.execute('USE COMMONDB;')

    sql = getFunction()
    cur.execute(sql)

    conn.commit()
    conn.close()
    

print "exec on db01"    
executeQuery('db01.be4energy.com')
print "exec on db02"    
executeQuery('db02.be4energy.com')
print "exec on db03"    
executeQuery('db03.be4energy.com')
print "exec on db04"    
executeQuery('db04.be4energy.com')
print "exec on db05"    
executeQuery('db05.be4energy.com')
print "exec on db06"
executeQuery('db06.be4energy.com')
print "exec on db07"
executeQuery('db07.be4energy.com')
print "exec on db08"
executeQuery('db08.be4energy.com')
print "exec on db09"
executeQuery('db09.be4energy.com')
print "exec on db10"
executeQuery('db10.be4energy.com')
print "exec on db11"
executeQuery('db11.be4energy.com')