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

# In[ ]:


#Hannes Krueger, 15.Feb.2019
#V2G scheduling algorithm (reactive scheduling layer). Loads EV data from table 'vehicles' in SQL database, grid power flow from 'PowerFlowNow' and power flow goal from 'PowerAvailable'.


# In[ ]:


print('Preprocessing...')

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


# 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 vehicles with highest current charging rate.
def FindHighRate():
    database.execute("SELECT IPaddress, ChargeValue, MaxChargingRate FROM Vehicles ORDER BY ChargeValue DESC  LIMIT 10")
    Vehicles=database.fetchall()
    return Vehicles
#Select vehicle data for all vehicles with lowest current discharging rate.
def FindLowRate():
    database.execute("SELECT IPaddress, ChargeValue, MaxChargingRate FROM Vehicles ORDER BY ChargeValue ASC  LIMIT 10")
    Vehicles=database.fetchall()
    return Vehicles

#What is the grid power flow goal now? Assumes one value every 30 seconds
def updateGridFlowGoal(): 
    time30s = datetime.datetime.now()
    time30s = time30s - datetime.timedelta(seconds=time30s.second %30, microseconds=time30s.microsecond)
    query="SELECT AvailablePower FROM PowerAvailable WHERE time '" + str((time30s).strftime('%H:%M:%S')) + "'"
    database.execute(query)
    data = database.fetchone()
    global GridFlowGoal
    GridFlowGoal = data[0]

#What is the trainstation demand now?
def updateTrainStationDemand():
    query="SELECT total FROM TrainSchedule_Extended WHERE time between '" + str((datetime.datetime.now()-datetime.timedelta(seconds=3)).strftime('%H:%M:%S')) + "' and '" +str((datetime.datetime.now()+datetime.timedelta(seconds=1)).strftime('%H:%M:%S')) + "'" 
    database.execute(query)
    data = database.fetchall()
    global TrainStationDemand
    TrainStationDemand = data

#What is the total "ChargeValue" of all vehicles?
def updatePowerFlow():
    query="SELECT Total FROM PowerFlowNow"
    database.execute(query)
    data = database.fetchone()
    global PowerFlow
    PowerFlow = data[0]
    
#Send charging instruction to EV
def Charge_Rest_Call(IP, Value, PreviousValue):
    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 ChargeValue=" + str("%.2f" % Value) + " WHERE IPaddress = '" + IP + "'")
    #Update total car park power flow
    print("before: " + str(PreviousValue) + ", after: " + str(Value))
    print("UPDATE PowerFlowNow SET Total = Total +" + str(Value-PreviousValue))
    database.execute("UPDATE PowerFlowNow SET Total = Total +" + str(Value-PreviousValue))
    #time.sleep(0.01)

#Fix given mismatch
def fixMismatch(tofix):
    #For now do one EV at a time
    if tofix<0:
        #Lower EV charging rate or start discharging
        EVs=FindHighRate()
        if len(EVs)>0: #Only do if suitable EVs were found
            print("Lower charging rates")
            for i in EVs:
                if (i[1]+MaxPower)>abs(tofix): #A single EV is enough to fix the rest
                    rate=i[1]+tofix
                    tofix=0
                    Charge_Rest_Call(i[0], rate, i[1])
                    break
                else: #Need more than one EV, but take this one offline first
                    rate=-MaxPower
                    tofix=tofix+MaxPower+i[1]
                    Charge_Rest_Call(i[0], rate, i[1])
                if tofix>=0: #Stop loop if mismatch is fixed
                    break
    else:
        #Increase EV charging rate or start charging
        EVs=FindLowRate()
        if len(EVs)>0: #Only do if suitable EVs were found
            print("Increase charging rates")
            for i in EVs:
                if (min(MaxPower,i[2])-i[1])>tofix: #A single EV is enough to fix the rest
                    rate=tofix+i[1]
                    Charge_Rest_Call(i[0], rate, i[1])
                    tofix=0
                    break
                else: #Need more than one EV
                    rate=min(MaxPower,i[2]) #Charge this EV at max
                    tofix=tofix-rate
                    Charge_Rest_Call(i[0], rate, i[1])
                if tofix<=0: #Stop loop if mismatch is fixed
                    break


# In[ ]:


#Assign Global variables
print('Assigning global variables...')
Tolerance = 5.0 #Tolerate 5 kW mismatch between actual power flow and goal

#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")


# In[ ]:


print('Preprocessing complete, starting reactive scheduling')

while True:
    updateGridFlowGoal()
    updateTrainStationDemand()
    updatePowerFlow()

    Mismatch=[]
    for i in TrainStationDemand:
        Mismatch.append(GridFlowGoal-i[0]-PowerFlow)
     
    #All mismatch values of the same sign?
    SameSign=all(item >= 0 for item in Mismatch) or all(item < 0 for item in Mismatch)
    
    if SameSign==True:
        #Take lowest mismatch over 5 second period to avoid "fixing" non-existing mismatch due to delays in data collection
        MinMismatch=min(Mismatch, key=abs)
    else: #Mismatch must have been zero at some point
        MinMismatch=0
          
    if abs(MinMismatch)>Tolerance:
        print(datetime.datetime.now())
        print("Train Supply: " + str(-TrainStationDemand[2][0]))
        print("Goal: " + str(GridFlowGoal))
        print("Total: " + str(GridFlowGoal-TrainStationDemand[2][0]))
        print("PowerFlow: " + str(PowerFlow))
        print("Mismatch: " + str(MinMismatch))
        print(Mismatch)
        print("")
        fixMismatch(MinMismatch)