#!/usr/bin/env python
# coding: utf-8

# In[ ]:


#Hannes Krueger, 07.Nov.2018
#Off-event EV charging algorithm.
#Loads EV data from table 'vehicles' in SQL database and power flow goal from 'PowerAvailable'.


# In[ ]:


print('Preprocessing...')

#Import required libraries
print('Importing libraries...')
import pymysql #For mySQL connection
import datetime #For handling datetime datatype
import time #Allows measuring time and delaying loops if needed
import requests #For REST calls to Vehicles
import sys #For premature program termination


# In[ ]:


#Connect to V2G Database
print('Connecting to V2G Database...')
mySQLconnection = pymysql.connect(host='######', user='######', password='######', db='######')
mySQLconnection.autocommit(True) #removes need to commit to database changes after each change is made
database = mySQLconnection.cursor()


# In[ ]:


print('Defining callable functions...')

#Select vehicle data for all vehicles not in an event ordered by CW rating decending
def FindEVs():
    database.execute("SELECT IPaddress, MaxChargingRate, ChargeValue, SmartCharging, CW FROM Vehicles WHERE InEvent=0 AND CW>0 ORDER BY CW DESC")
    Vehicles=database.fetchall()
    return Vehicles

#Any events within the next 15 seconds?
def FindEvents():
    database.execute("SELECT time, event, EventID FROM TrainSchedule_EventOnly WHERE time BETWEEN '" + str(datetime.datetime.now()) + "' AND '" + str(datetime.datetime.now() + datetime.timedelta(seconds=15)) + "' ORDER BY time")
    Events=database.fetchall()
    return Events

#How much power can be assigned to car park?
def FindAvailablePower():
    query="SELECT AvailablePower FROM PowerAvailable WHERE time Between '" + str((datetime.datetime.now().strftime('%H:%M:%S'))) + "' AND '" + str((datetime.datetime.now()+datetime.timedelta(seconds=30)).strftime('%H:%M:%S')) + "'"
    database.execute(query)
    Value = database.fetchone()
    if Value == None:
        Value = [0]
    return Value[0]

#Send charging instruction to EV
def Charge_Rest_Call(IP, Value, PreviousValue):
    try:
        RESTcall = "http://" + IP + "/charge/1/" + str("%.2f" % Value)
        RESTresponse = requests.get(RESTcall)
        Response=str(RESTresponse.text) #Create Response String for parsing
        Response=Response.replace('\n', '') #Remove newline from response string
        Response=Response.replace('"', '') #Remove quotation marks from response string
        print("Charge_Rest_Call on " + IP + " response: " + Response)
        #mark EV in database as "smart charging"
        database.execute("UPDATE Vehicles SET SmartCharging=1 WHERE IPaddress = '" + IP + "'")
        #Update total car park power flow
        database.execute("UPDATE PowerFlowNow SET Total = Total +" + str(Value-PreviousValue))
    except:
        print("Connection error") #Ignore any error at this stage
        #Data collection module deals with rare connection issues
        #Exception maintains system stability


# In[ ]:


#Assign Global variables
print('Assigning global variables...')
InEvent=False
InEventUntil=datetime.datetime.now()

#What is the minimum charging rate?
query="SELECT SmartChargingMinimumRate FROM Setup LIMIT 1"
database.execute(query)
MinPower = database.fetchone()[0]
print("Minimum charging rate per EV = " + str(MinPower) + " kW")

#What is the global maximum charging rate (i.e. hardware charging limit)?
query="SELECT ChargingRateLimit FROM Setup LIMIT 1"
database.execute(query)
MaxPower = database.fetchone()[0]
print("Maximum charging rate per EV = " + str(MaxPower) + " kW")

Tolerance=0.01 #Use 10W tolerance when comparing supposed with actual charging rates (to avoid excessive re-adjustments as CW changes marginally)

TimePowerFlowUpdate = datetime.datetime.now() #Time since last update of total power flow (using sum of all EV power flows)


# In[ ]:


print('Preprocessing complete, starting smart charging')

while True:
    #Is an event going on? (or starting within 15 seconds)
    Events=FindEvents()
    if len(Events)>0:
        InEvent=True
        for i in Events:
            timeEvent = datetime.datetime.combine(datetime.date.today(), (datetime.datetime.min + i[0]).time())
            if i[1] == "L1":
                duration=116
            elif i[1] == "L2":
                duration=83
            elif i[1] == "L3":
                duration=103
            elif i[1] == "A1":
                duration=56
            elif i[1] == "A2":
                duration=83
            elif i[1] == "A3":
                duration=103
            elif i[1] == "C":
                duration=86
            timeEventEnd = timeEvent + datetime.timedelta(seconds=duration)
            if timeEventEnd>InEventUntil:
                InEventUntil=timeEventEnd
    else:
        if InEventUntil<datetime.datetime.now():
            InEvent=False
        #Don't do more often than every 30 seconds as summation is computatinally costly
        if TimePowerFlowUpdate < (datetime.datetime.now()-datetime.timedelta(seconds=30)):
            time.sleep(2) #Wait 2 seconds to ensure database catches up to recent changes
            TimePowerFlowUpdate=datetime.datetime.now()
            #What is the total "ChargeValue" of all vehicles?
            database.execute("SELECT SUM(ChargeValue) FROM Vehicles")
            TotalPowerFlow = database.fetchone()
            if TotalPowerFlow[0] == None: #Needed when no EVs connected
                TotalPowerFlow=[]
                TotalPowerFlow.append(0)
            database.execute("SELECT Total FROM PowerFlowNow")
            temp=database.fetchone()
            #Update running total car park power flow (which may diverge slightly over time)
            database.execute("UPDATE PowerFlowNow SET Total = " + str(TotalPowerFlow[0]))
            print("UPDATED !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!")
            print("before: " + str(temp) + "; after: " + str(TotalPowerFlow[0]))
            
    if InEvent == False:
        #Free for unrestricted smart charging
        EVs = FindEVs() #Load EV data
        TotalPowerAvailable=FindAvailablePower() #How much power is available for car park?
              
        if len(EVs)>0: #Only do if EVs available
            PowerBudget=TotalPowerAvailable-MinPower*len(EVs) #How much power can still be distributed?
            #print("Power Budget: " + str(PowerBudget))
            if PowerBudget < 0:
                PowerBudget=0
            
            CWsum=0.0 #Assign power to EVs based on CW assigned
        
            for j in EVs:
                CWsum=CWsum+j[4] #Sum up CW values for all EVs
            
            for i in EVs:
                PowerPerEV=MinPower+i[4]*PowerBudget/CWsum #Assign fraction of "budget" plus minimum charging rate
                if PowerPerEV > MaxPower: #Ensure charging rate does not exceed global limit
                    PowerPerEV = MaxPower
                #Is EV already within tolerance of charging goal?
                if i[3]==b'\x01' and i[2] < PowerPerEV + Tolerance and i[2] > PowerPerEV - Tolerance:
                    pass #For case i[3]==b'\x01' (zero byte), Charge_Rest_Call will be done regardless and EV will be marked as "smart charging"
                    PowerBudget=PowerBudget-i[2]+MinPower
                elif PowerPerEV > i[1]: #Should not exceed EV's maximum charging rate
                    Charge_Rest_Call(i[0], i[1], i[2])
                    PowerBudget=PowerBudget-i[1]+MinPower
                else:
                    Charge_Rest_Call(i[0], PowerPerEV, i[2])
                    PowerBudget=PowerBudget-PowerPerEV+MinPower                                
                CWsum=CWsum-i[4]
                              
        else:
            print("no EVs")
                
    time.sleep(1)