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

# In[ ]:


#Hannes Krueger, 17.Oct.2018
#Implementation algorithm that loads 'Schedule' table in SQL database, parses schedule entries into charging instructions and submits instructions to EVs.


# In[ ]:


print('Preprocessing...')

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


# 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...')

#Send charging instruction to EV
def Charge_Rest_Call(IP, Value, ID, PreviousValue):
    try:
        RESTcall = "http://" + IP + "/charge/1/" + str(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)
        #Delete fulfilled order
        database.execute("DELETE FROM Schedule WHERE OrderID = '" + str(ID) + "'")
        #Update vehicle information
        database.execute("UPDATE Vehicles SET SmartCharging=0 ,ChargeValue = " + str("%.2f" % Value) + " WHERE IPaddress = '" + str(IP) + "'")
        #Update total car park power flow
        database.execute("UPDATE PowerFlowNow SET Total = Total + " + str(Value-PreviousValue))
    except: #Any problems at this point?
        print("Could not connect to: " + IP)
        #mark order as failed
        database.execute("UPDATE Schedule SET Status = 'failed' WHERE OrderID = '" + str(ID) + "'")

#Read out schedule information from SQL database
def Find_Orders(timeNOW):
    #Get all orders in table within next 5 seconds
    database.execute("SELECT ExecutionTime, IPaddress, PowerFlow, OrderID, Status, PreviousPowerFlow FROM Schedule WHERE ExecutionTime BETWEEN '" + str(timeNOW) + "' AND '" + str(timeNOW + datetime.timedelta(seconds=queueTime)) + "' AND Status = '' ORDER BY ExecutionTime")
    Orders=database.fetchall()
    #Mark above orders as "queued"
    database.execute("UPDATE Schedule SET Status = 'queued' WHERE ExecutionTime BETWEEN '" + str(timeNOW) + "' AND '" + str(timeNOW + datetime.timedelta(seconds=queueTime)) + "' AND Status = ''")
    return Orders
def Find_Cancellation_Orders(timeNOW):
    #Get all orders in table within next 5 seconds
    database.execute("SELECT ExecutionTime, IPaddress, PowerFlow, OrderID, Status, PreviousPowerFlow FROM Schedule WHERE ExecutionTime BETWEEN '" + str(timeNOW) + "' AND '" + str(timeNOW + datetime.timedelta(seconds=queueTime)) + "' AND Status = 'X' ORDER BY ExecutionTime")
    Orders=database.fetchall()
    #Mark above orders as "queued"
    database.execute("UPDATE Schedule SET Status = 'X queued' WHERE ExecutionTime BETWEEN '" + str(timeNOW) + "' AND '" + str(timeNOW + datetime.timedelta(seconds=queueTime)) + "' AND Status = 'X'")
    return Orders
    


# In[ ]:


#Assign Global variables
print('Assigning global variables...')
Orders=list() #Create initial list of outstanding orders
CancellationOrders=list()
global queueTime
queueTime = 3 #Time in seconds for how long into the future orders are being queued (loaded from database)? 
#Must be well above algorithm execution time, but not too long as queued orders cannot be cancelled.


# In[ ]:


print('Preprocessing complete, start implementing charge decisions')

#Start program loop
while True:
    timeNOW=datetime.datetime.now()
    newOrders=list(Find_Orders(timeNOW)) #Any new orders added to schedule table?
    Orders=Orders+newOrders #Add new orders to list of all unfulfilled orders
    newCancellationOrders=list(Find_Cancellation_Orders(timeNOW)) #Any new orders added to schedule table?
    CancellationOrders=CancellationOrders+newCancellationOrders
    if len(Orders)==0 and len(CancellationOrders)==0:
        time.sleep(0.5)
    if len(Orders)>500 or len(CancellationOrders)>500: #For debugging purposes only
        print("!!!High number of unfullfilled orders!!!")
    for i in Orders:
            #Is it time to execute the current order?
            if i[0] < timeNOW: #i[0] execution time
                #Make charge rest call to execute this order and mark order as "fulfilled"
                Charge_Rest_Call(i[1], i[2], i[3], i[5]) #i[1] IPaddress, i[2] PowerFlow, i[3] Order ID, i[5] PreviousPowerFlow
                Orders.remove(i) #Drop list entry
    for j in CancellationOrders:
            #Is it time to execute the current order?
            if j[0] < timeNOW: #i[0] execution time
                #Make charge rest call to execute this order and mark order as "fulfilled"
                Charge_Rest_Call(j[1], j[2], j[3], j[5]) #j[1] IPaddress, j[2] PowerFlow, j[3] Order ID, j[5] PreviousPowerFlow
                #If EV is taken offline then mark EV as not assigned to event (allowing it to be used for scheduler)
                database.execute("UPDATE Vehicles SET InEvent=0, InEventUntil = '0000-00-00 00:00:00' WHERE IPaddress='" + j[1] + "'")
                CancellationOrders.remove(j) #Drop list entry