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

# In[ ]:


#Hannes Krueger, 06.Nov.2018
#V2G scheduling algorithm (predictive scheduling layer). Loads EV data from table 'vehicles' in SQL database and event schedule. Schedule entries saved in 'schedule' table.


# 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 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 above set CW that are NOT already assigned to an event
def FindHighCW():
    database.execute("SELECT IPaddress, MaxChargingRate, ChargeValue FROM Vehicles WHERE CW>=0 AND InEvent=0 ORDER BY CW DESC")
    Vehicles=database.fetchall()
    return Vehicles
#Select vehicle data for all vehicles above set DCW that are NOT already assigned to an event
def FindHighDCW():
    database.execute("SELECT IPaddress, MaxChargingRate, ChargeValue FROM Vehicles WHERE DCW>=0.05 AND InEvent=0 ORDER BY DCW DESC")
    Vehicles=database.fetchall()
    return Vehicles

#Select vehicle data for all vehicles currently(!) in event and charging.
def FindBusyCharging(Starttime, Endtime):
    database.execute("SELECT IPaddress, ChargeValue, InEventUntil FROM Vehicles WHERE InEvent=1 AND SmartCharging=0 AND ChargeValue>0 AND InEventUntil BETWEEN '" + str(Starttime) + "' AND '" + str(Endtime) + "' ORDER BY InEventUntil")
    Vehicles=database.fetchall()
    return Vehicles
#Select vehicle data for all vehicles currently(!) in event and discharging.
def FindBusyDischarging(Starttime, Endtime):
    database.execute("SELECT IPaddress, ChargeValue, InEventUntil FROM Vehicles WHERE InEvent=1 AND ChargeValue<0 AND InEventUntil BETWEEN '" + str(Starttime) + "' AND '" + str(Endtime) + "' ORDER BY InEventUntil")
    Vehicles=database.fetchall()
    return Vehicles

#Select outstanding charging orders (and corresponding cancellation orders if wanted) for given time frame.
#Used to find orders from last event that can be cancelled for the coming event.
def FindOutstandingChargeOrders(Starttime, Endtime, showCancelOrders=False):
    database.execute("SELECT OrderID, PowerFlow, ExecutionTime, IPaddress, MaxPowerFlow, PreviousPowerFlow From Schedule WHERE PowerFlow > 0 AND Status = '' AND ExecutionTime BETWEEN '" + str(Starttime) + "' AND '" + str(Endtime) + "' ORDER BY ExecutionTime")
    Orders=database.fetchall()
    if showCancelOrders==True:
        CancelOrders=[]
        if len(Orders)>0:
            database.execute("SELECT IPaddress From Schedule WHERE PowerFlow > 0 AND Status = '' AND ExecutionTime BETWEEN '" + str(Starttime) + "' AND '" + str(Endtime) + "' ORDER BY ExecutionTime")
            IPs=database.fetchall()
            database.execute("SELECT OrderID, PowerFlow, ExecutionTime, IPaddress, MaxPowerFlow, PreviousPowerFlow From Schedule WHERE PowerFlow = 0 AND Status = '' AND IPaddress IN " + str(IPs).replace(',)', ')') + " AND ExecutionTime BETWEEN '" + str(Starttime) + "' AND '" + str(Endtime) + "' ORDER BY ExecutionTime")
            CancelOrders=database.fetchall()
        return Orders, CancelOrders
    else:
        return Orders
#Select outstanding discharging orders (and corresponding cancellation orders if wanted) for given time frame.
#Used to find orders from last event that can be cancelled for the coming event.
def FindOutstandingDischargeOrders(Starttime, Endtime, showCancelOrders=False):
    database.execute("SELECT OrderID, PowerFlow, ExecutionTime, IPaddress, MaxPowerFlow, PreviousPowerFlow From Schedule WHERE PowerFlow < 0 AND Status = '' AND ExecutionTime BETWEEN '" + str(Starttime) + "' AND '" + str(Endtime) + "' ORDER BY ExecutionTime")
    Orders=database.fetchall()
    if showCancelOrders==True:
        CancelOrders=[]
        if len(Orders)>0:
            database.execute("SELECT IPaddress From Schedule WHERE PowerFlow < 0 AND Status = '' AND ExecutionTime BETWEEN '" + str(Starttime) + "' AND '" + str(Endtime) + "' ORDER BY ExecutionTime")
            IPs=database.fetchall()
            database.execute("SELECT OrderID, PowerFlow, ExecutionTime, IPaddress, MaxPowerFlow, PreviousPowerFlow From Schedule WHERE PowerFlow = 0 AND Status = '' AND IPaddress IN " + str(IPs).replace(',)', ')') + " AND ExecutionTime BETWEEN '" + str(Starttime) + "' AND '" + str(Endtime) + "' ORDER BY ExecutionTime")
            CancelOrders=database.fetchall()
        return Orders, CancelOrders
    else:
        return Orders

#Cancel specific order
def CancelThisOrder(ID):
    database.execute("UPDATE Schedule SET Status = 'cancelled' WHERE OrderID = " + str(ID))
    
#Cancel all outstanding charging/discharging orders for particular vehicle (from given time onwards)
def CancelOrders(IP, time):
    query="UPDATE Schedule SET Status = 'cancelled' WHERE IPaddress = '" + str(IP)  + "' AND ExecutionTime >= '" + str(time) + "'"
    database.execute(query)

#___________________________________________________________________________________

#Any events within the next 10 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=10)) + "' ORDER BY time")
    Events=database.fetchall()
    return Events


# In[ ]:


#Define main event-based scheduling function
def EventSchedule(timeEvent, Event):
    #Find EVs suitable for charging/discharging    
    AvailableEVsDischarge=list(FindHighDCW())
    AvailableEVsCharge=list(FindHighCW())
    #Find EVs currently busy that could be taken offline
    BusyEVsCharging=list(FindBusyCharging(timeEvent + datetime.timedelta(seconds=1), timeEvent + datetime.timedelta(seconds=len(Event)))) 
    BusyEVsDischarging=list(FindBusyDischarging(timeEvent + datetime.timedelta(seconds=1), timeEvent + datetime.timedelta(seconds=len(Event))))
    #Find outstanding orders to charge, discharge or take an EV offline so that orders can be revised
    ChargeOrders, CancelChargeOrders=list(FindOutstandingChargeOrders(timeEvent, timeEvent + datetime.timedelta(seconds=len(Event)), True))
    DischargeOrders, CancelDischargeOrders=FindOutstandingDischargeOrders(timeEvent + datetime.timedelta(seconds=1), timeEvent + datetime.timedelta(seconds=len(Event)), True)   
    #Need to convert tuples to lists so that entries can be removed
    ChargeOrders=list(ChargeOrders)
    CancelChargeOrders=list(CancelChargeOrders)
    DischargeOrders=list(DischargeOrders)
    CancelDischargeOrders=list(CancelDischargeOrders)
    
    #Append log file with EV numbers before scheduling
    logfile = open("Schedulelog.txt","a+")
    logfile.write(str(len(AvailableEVsDischarge)) + "," + str(len(AvailableEVsCharge)) + ",")
    logfile.close()
    EnoughEVs=True
    
    AssignedEVsDischarge=[] #Create list of vehicles already assigned to discharge for this event
    AssignedEVsCharge=[] #Create list of vehicles already assigned to charge for this event
    print(str(len(AvailableEVsDischarge)) + " vehicles available for discharging")  
    print(str(len(AvailableEVsCharge)) + " vehicles available for charging")  
    
    j=0 #Counter variable for event steps    
    PowerAssigned=0 #Variable for power flow assigned so far
    
    Schedule=[] #List of Strings of form: (ExecutionTime, IPaddress, PowerFlow, MaxPowerFlow, PreviousPowerFlow),
    MarkBusy=[] #List of IP addresses to be marked as "busy" when assigned during scheduling
    MarkFree=[] #List of IP addresses to be marked as "free" when taken offline prematurely
    BusyUntil=[] #List of IP addresses and times until which each EV is "busy"
    EffectExpiry=[] #List of times at which the net effects of taking "busy" vehicles offline "expires"
    
    #Only do main loop if any EVs can be scheduled
    if len(AvailableEVsDischarge)>0 or len(AvailableEVsCharge)>0 or len(BusyEVsCharging)>0 or len(BusyEVsDischarging)>0 or len(ChargeOrders)>0 or len(CancelChargeOrders)>0 or len(DischargeOrders)>0 or len(CancelDischargeOrders)>0:
        for i in Event: #i is train station's power demand at this second
            #When does this step take place?
            timeSTEP=timeEvent + datetime.timedelta(seconds=j) #(assume each step = 1 second)

            #Any net effects of taking "busy" vehicles offline "expiring"?
            if len(EffectExpiry) > 0:
                k=EffectExpiry[0]
                try:
                    while k[0] < timeSTEP + datetime.timedelta(seconds=1):#Effect expires at this time step
                        PowerAssigned=PowerAssigned-k[1] #Adjust assigned power
                        EffectExpiry.remove(k) #Remove effect
                        if len(EffectExpiry) > 0:
                            k=EffectExpiry[0] #Check next entry
                        else:
                            break #No more expiring effects
                except TypeError:
                    print("_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-")
                    print("None type found where datetime was expected")
                    print("_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-")
                    EffectExpiry.remove(k)
                    break
                        
            #Any outstanding orders in this time step?
            ChargeOrdersStep=[]
            if len(ChargeOrders) > 0:
                NextOrder=ChargeOrders[0]
                while NextOrder[2] < timeSTEP + datetime.timedelta(seconds=1): #Order is within next second
                    ChargeOrdersStep.append(NextOrder)
                    ChargeOrders.remove(NextOrder)
                    if len(ChargeOrders) > 0:
                        NextOrder=ChargeOrders[0]
                    else:
                        break #No more outstanding Orders
            DischargeOrdersStep=[]
            if len(DischargeOrders) > 0:
                NextOrder=DischargeOrders[0]
                while NextOrder[2] < timeSTEP + datetime.timedelta(seconds=1): #Order is within next second
                    DischargeOrdersStep.append(NextOrder)
                    DischargeOrders.remove(NextOrder)
                    if len(DischargeOrders) > 0:
                        NextOrder=DischargeOrders[0]
                    else:
                        break #No more outstanding Orders
            CancelChargeOrdersStep=[]
            if len(CancelChargeOrders) > 0:
                NextOrder=CancelChargeOrders[0]
                while NextOrder[2] < timeSTEP + datetime.timedelta(seconds=1): #Order is within next second
                    CancelChargeOrdersStep.append(NextOrder)
                    CancelChargeOrders.remove(NextOrder)
                    if len(CancelChargeOrders) > 0:
                        NextOrder=CancelChargeOrders[0]
                    else:
                        break #No more outstanding Orders
            CancelDischargeOrdersStep=[]
            if len(CancelDischargeOrders) > 0:
                NextOrder=CancelDischargeOrders[0]
                while NextOrder[2] < timeSTEP + datetime.timedelta(seconds=1): #Order is within next second
                    CancelDischargeOrdersStep.append(NextOrder)
                    CancelDischargeOrders.remove(NextOrder)
                    if len(CancelDischargeOrders) > 0:
                        NextOrder=CancelDischargeOrders[0]
                    else:
                        break #No more outstanding Orders
            #Any "busy" vehicles finished by now?
            if len(BusyEVsCharging) > 0:
                EV=BusyEVsCharging[0]
                while EV[2] < timeSTEP + datetime.timedelta(seconds=1): #Is this EV still busy?
                    BusyEVsCharging.remove(EV) #No, so remove
                    if len(BusyEVsCharging) > 0:
                        EV=BusyEVsCharging[0] #Check next EV
                    else:
                        break #No more busy EVs
            if len(BusyEVsDischarging) > 0:
                EV=BusyEVsDischarging[0]
                while EV[2] < timeSTEP + datetime.timedelta(seconds=1): #Is this EV still busy?
                    BusyEVsDischarging.remove(EV) #No, so remove
                    if len(BusyEVsDischarging) > 0:
                        EV=BusyEVsDischarging[0] #Check next EV
                    else:
                        break #No more busy EVs

            print("Step: " + str(j+1) + " " + str(timeSTEP))
            print("Power Demand: " + str(i))
            print("Power Assigned: " + str(PowerAssigned))
            print("EVs busy with charging: " + str(len(BusyEVsCharging)))
            print("EVs busy with discharging: " + str(len(BusyEVsDischarging)))
            print("Charging orders: " + str(len(ChargeOrdersStep)))
            print("Discharging orders: " + str(len(DischargeOrdersStep)))
            
            DemandIncreased = False #Used to make sure that power is not decreased right after increase in the same step (two contradictory actions in same time step)
            DemandDecreased = False
            
            while DemandDecreased == False and i > PowerAssigned + 15: #Is demand increasing (significantly) in this step?
                DemandIncreased = True
                #Can EVs assigned to charge in this event be taken offline?
                if len(AssignedEVsCharge)>0: #Have to take assigned EVs offline
                    EV=AssignedEVsCharge[0] #Assign next vehicle
                    if EV[1]>GlobalMaxChargingRate: #If EV charging rate exceeds global limit
                        ChargeRate=GlobalMaxChargingRate #Use global limit
                    else:
                        ChargeRate=EV[1] #Else, use EV limit
                    #Take EV offline
                    Schedule.append("('" + str(timeSTEP) + "','" + str(EV[0]) + "','" + str(EV[2]) + "','" + str(EV[1]) + "','" + str(ChargeRate) + " ','X'),")
                    print(str(EV[0]) + " taken offline")
                    AssignedEVsCharge.remove(EV)
                    AvailableEVsCharge.append(EV) #Add to available vehicles
                    BusyUntil.append([EV[0], timeSTEP]) #When does the EV come offline?
                    PowerAssigned=PowerAssigned+ChargeRate-EV[2]
                #Can we cancel charging orders from previous event occuring at this time step?
                elif len(ChargeOrdersStep) > 0:
                    #Cancel this order
                    Order=ChargeOrdersStep[0]
                    CancelThisOrder(Order[0])
                    PowerAssigned=PowerAssigned-Order[1]+Order[5]
                    #When does this effect expire?
                    database.execute("SELECT InEventUntil FROM Vehicles WHERE IPaddress = '" + Order[3] + "'")
                    ExpiryTime=database.fetchone()
                    EffectExpiry.append([ExpiryTime[0], -Order[1]+Order[5]])
                    ChargeOrdersStep.remove(Order)
                    #Also need to remove corresponding charge cancellation order
                    for x in CancelChargeOrders:
                        if x[3] == Order[3]: #Compare IP address
                            CancelThisOrder(x[0]) #Remove from database
                            CancelChargeOrders.remove(x) #Remove from local memory
                            #Mark EV as free & make available
                            EV = [Order[3], Order[4], Order[5]]
                            AvailableEVsCharge.append(EV)
                            MarkFree.append(EV[0])
                            break
                    print("Charging order cancelled: " + str(Order))
                #Can we take EVs already charging offline?
                elif len(BusyEVsCharging) > 0:
                    EV=BusyEVsCharging[0] #Assign next vehicle
                    CancelOrders(EV[0], timeEvent) #Cancel all outstanding orders for current "busy" EV
                    if EV[1]>GlobalMaxChargingRate: #If EV charging rate exceeds global limit
                        ChargeRate=GlobalMaxChargingRate #Use global limit
                    else:
                        ChargeRate=EV[1] #Else, use EV limit                    
                    #Take current "busy" EV offline
                    Schedule.append("('" + str(timeSTEP) + "','" + str(EV[0]) + "',0,'" + str(EV[1]) + "','" + str(ChargeRate) + " ','X'),")
                    BusyEVsCharging.remove(EV) #Take EV of "busy" vehicles list
                    MarkFree.append(EV[0]) #Mark EV as "free"
                    PowerAssigned=PowerAssigned-ChargeRate
                    EffectExpiry.append([EV[2], -EV[1]]) #When does this effect expire?
                    print("Charging EV taken offline: " + str(EV[0]))
                #Can we move forward discharge orders from a previous event?
                elif len(DischargeOrders)>0:    
                    Order=DischargeOrders[0]
                    print("Moving a discharge order forward: " + str(Order))
                    #Move first order to this timestep
                    database.execute("UPDATE Schedule SET ExecutionTime = '" + str(timeSTEP) + "' WHERE OrderID = " + str(Order[0]))
                    PowerAssigned=PowerAssigned-Order[1]+Order[5] #What is the effect?
                    EffectExpiry.append([Order[2], -Order[1]+Order[5]]) #When does this effect expire?                                
                    DischargeOrders.remove(Order) #Remove this Order                                
                #Can we move cancellation of dicharge orders back?
                elif len(CancelDischargeOrdersStep)>0:
                    Order=CancelDischargeOrdersStep[0]
                    print("Moving a discharge cancellation order back: " + str(Order))
                    #Delete this order
                    CancelThisOrder(Order[0])
                    #Mark corresponding EV as assigned EV
                    AssignedEVsDischarge.append([Order[3], Order[4], Order[5]])
                    PowerAssigned=PowerAssigned+Order[4] #What is the effect?                                                          
                    CancelDischargeOrdersStep.remove(Order) #Remove this Order
                #Have to make use of available EVs for this event
                elif len(AvailableEVsDischarge)>0: 
                    EV=AvailableEVsDischarge[0] #Assign next vehicle
                    #Discharge EV
                    if EV[1]>GlobalMaxChargingRate: #If EV discharging rate exceeds global limit
                        DischargeRate=-GlobalMaxChargingRate #Use global limit
                    else:
                        DischargeRate=-EV[1] #Else, use EV limit
                    Schedule.append("('" + str(timeSTEP) + "','" + str(EV[0]) + "','" + str(DischargeRate) + "','" + str(EV[1]) + "','" + str(EV[2]) + " ', ''),")
                    MarkBusy.append(EV[0]) #Add IP address to list of EVs to be marked "busy"
                    print(str(EV[0]) + " assigned discharging @" + str(-DischargeRate) + " kW (before " + str(EV[2]) + " kW)") 
                    AvailableEVsDischarge.remove(EV) #Remove from available EVs list
                    try:
                        AvailableEVsCharge.remove(EV) #In case EV is in both lists
                    except: 
                        pass #Ignore errors
                    AssignedEVsDischarge.append(EV) #Add to discharging vehicles
                    PowerAssigned=PowerAssigned-DischargeRate+EV[2]
                else:
                    print("Not enough Vehicles available!")
                    EnoughEVs=False
                    break
            while DemandIncreased == False and i < PowerAssigned - 15: #Is demand decreasing (significantly) in this step?
                DemandDecreased = True
                #Can EVs assigned to discharge in this event be taken offline?
                if len(AssignedEVsDischarge)>0: #Have to take assigned EVs offline
                    EV=AssignedEVsDischarge[0] #Assign next vehicle
                    if EV[1]>GlobalMaxChargingRate: #If EV charging rate exceeds global limit
                        DischargeRate=-GlobalMaxChargingRate #Use global limit
                    else:
                        DischargeRate=-EV[1] #Else, use EV limit
                    #Take EV offline
                    Schedule.append("('" + str(timeSTEP) + "','" + str(EV[0]) + "','" + str(EV[2]) + "','" + str(EV[1]) + "','" + str(DischargeRate) + " ','X'),")
                    print(str(EV[0]) + " taken offline")
                    AssignedEVsDischarge.remove(EV)
                    AvailableEVsDischarge.append(EV) #Add to available vehicles
                    BusyUntil.append([EV[0], timeSTEP]) #When does the EV come offline?
                    PowerAssigned=PowerAssigned+DischargeRate-EV[2]
                #Can we cancel discharging orders from previous event occuring at this time step?
                elif len(DischargeOrdersStep) > 0:
                    #Cancel this order
                    Order=DischargeOrdersStep[0]
                    CancelThisOrder(Order[0])
                    PowerAssigned=PowerAssigned+Order[1]+Order[5]
                    #When does this effect expire?
                    database.execute("SELECT InEventUntil FROM Vehicles WHERE IPaddress = '" + Order[3] + "'")
                    ExpiryTime=database.fetchone()
                    EffectExpiry.append([ExpiryTime[0], Order[1]+Order[5]])
                    DischargeOrdersStep.remove(Order)
                    #Also need to remove corresponding discharge cancellation order
                    for x in CancelDischargeOrders:
                        if x[3] == Order[3]: #Compare IP address
                            CancelThisOrder(x[0]) #Remove from database
                            CancelDischargeOrders.remove(x) #Remove from local memory
                            #Mark EV as free & make available
                            EV = [Order[3], Order[4], Order[5]]
                            AvailableEVsDischarge.append(EV)
                            MarkFree.append(EV[0])
                            break
                    print("Discharging order cancelled: " + str(Order))
                #Can we take EVs already discharging offline?
                elif len(BusyEVsDischarging) > 0:
                    EV=BusyEVsDischarging[0] #Assign next vehicle
                    CancelOrders(EV[0], timeEvent) #Cancel all outstanding orders for current "busy" EV
                    if EV[1]>GlobalMaxChargingRate: #If EV charging rate exceeds global limit
                        DischargeRate=-GlobalMaxChargingRate #Use global limit
                    else:
                        DischargeRate=-EV[1] #Else, use EV limit
                    #Take current "busy" EV offline
                    Schedule.append("('" + str(timeSTEP) + "','" + str(EV[0]) + "',0,'" + str(EV[1]) + "','" + str(DischargeRate) + " ','X'),")
                    BusyEVsDischarging.remove(EV) #Take EV of "busy" vehicles list
                    MarkFree.append(EV[0]) #Mark EV as "free"
                    PowerAssigned=PowerAssigned-DischargeRate
                    EffectExpiry.append([EV[2], EV[1]]) #When does this effect expire?
                    print("Discharging EV taken offline: " + str(EV[0]))
                #Can we move forward discharge orders from a previous event?
                elif len(ChargeOrders)>0:    
                    Order=ChargeOrders[0]
                    print("Moving a charge order forward: " + str(Order))
                    #Move first order to this timestep
                    database.execute("UPDATE Schedule SET ExecutionTime = '" + str(timeSTEP) + "' WHERE OrderID = " + str(Order[0]))
                    PowerAssigned=PowerAssigned-Order[1]+Order[5] #What is the effect?
                    EffectExpiry.append([Order[2], -Order[1]+Order[5]]) #When does this effect expire?                                
                    ChargeOrders.remove(Order) #Remove this Order                                
                #Can we move cancellation of charge orders back?
                elif len(CancelChargeOrdersStep)>0:
                    Order=CancelChargeOrdersStep[0]
                    print("Moving a charge cancellation order back: " + str(Order))
                    #Delete this order
                    CancelThisOrder(Order[0])
                    #Mark corresponding EV as assigned EV
                    AssignedEVsCharge.append([Order[3], Order[4], Order[5]])
                    PowerAssigned=PowerAssigned-Order[4] #What is the effect?                                                          
                    CancelChargeOrdersStep.remove(Order) #Remove this Order
                #Have to make use of available EVs for this event
                elif len(AvailableEVsCharge)>0: 
                    EV=AvailableEVsCharge[0] #Assign next vehicle                    
                    #Charge EV
                    if EV[1]>GlobalMaxChargingRate: #If EV charging rate exceeds global limit
                        ChargeRate=GlobalMaxChargingRate #Use global limit
                    else:
                        ChargeRate=EV[1] #Else, use EV limit
                    Schedule.append("('" + str(timeSTEP) + "','" + str(EV[0]) + "','" + str(ChargeRate) + "','" + str(EV[1]) + "','" + str(EV[2]) + "', ''),")
                    MarkBusy.append(EV[0]) #Add IP address to list of EVs to be marked "busy"
                    print(str(EV[0]) + " assigned charging @" + str(ChargeRate) + " kW (before " + str(EV[2]) + " kW)")
                    AvailableEVsCharge.remove(EV) #Remove from available EVs list
                    try:
                        AvailableEVsDischarge.remove(EV) #In case EV is in both lists
                    except: 
                        pass #Ignore errors
                    AssignedEVsCharge.append(EV) #Add to charging vehicles
                    PowerAssigned=PowerAssigned-ChargeRate+EV[2]
                else:
                    print("Not enough Vehicles available!")
                    EnoughEVs=False
                    break
            
            j+=1 #Count up for next step
            print(str(len(AvailableEVsCharge)) + " vehicles available for charging")
            print(str(len(AssignedEVsCharge)) + " vehicles assigned for charging") 
            print(str(len(AvailableEVsDischarge)) + " vehicles available for discharging")
            print(str(len(AssignedEVsDischarge)) + " vehicles assigned for discharging")        
            print("____________")
    else:
        print("No vehicles for scheduling!")

    print('Event ended')
    
    if len(EffectExpiry)>0:
        #Still need to fix schedule after this event
        print("Post-event schedule fixes required")
        for i in EffectExpiry:
            if i[1]<0:
                #Can we match this with charging EVs?
                if len(AssignedEVsDischarge)>0: #Have to take assigned EVs offline
                    EV=AssignedEVsDischarge[0] #Assign next vehicle
                    #Take EV offline
                    Schedule.append("('" + str(i[0]) + "','" + str(EV[0]) + "',0,'" + str(EV[1]) + "','" + str(EV[2]) + " ','X'),")
                    print(str(EV[0]) + " taken offline post-event at " + str(i[0]))
                    AssignedEVsDischarge.remove(EV)
                    BusyUntil.append([EV[0], i[0]]) #When does the EV come offline?
            elif i[1]>0:
                #Can we match this with discharging EVs?
                if len(AssignedEVsCharge)>0: #Have to take assigned EVs offline
                    EV=AssignedEVsCharge[0] #Assign next vehicle
                    #Take EV offline
                    Schedule.append("('" + str(i[0]) + "','" + str(EV[0]) + "',0,'" + str(EV[1]) + "','" + str(EV[2]) + " ','X'),")
                    print(str(EV[0]) + " taken offline post-event at " + str(i[0]))
                    AssignedEVsCharge.remove(EV)
                    BusyUntil.append([EV[0], i[0]]) #When does the EV come offline?
           
    while PowerAssigned > 1 or PowerAssigned < -1: #Assigned EVs probably not taken offline due to selected margins
        print("Assigned power flows not zero!!! Try fixing post-event...")
        print("Power flow: " + str(PowerAssigned))
        if len(AssignedEVsCharge)>0:
            EV=AssignedEVsCharge[0] #Assign next vehicle
            #Take EV offline
            if EV[1]>GlobalMaxChargingRate: #If EV charging rate exceeds global limit
                ChargeRate=GlobalMaxChargingRate #Use global limit
            else:
                ChargeRate=EV[1] #Else, use EV limit
            Schedule.append("('" + str(timeSTEP) + "','" + str(EV[0]) + "','" + str(EV[2]) + "','" + str(EV[1]) + "','" + str(ChargeRate) + " ','X'),")
            print(str(EV[0]) + " taken offline post-event (due to mismatch)")
            AssignedEVsCharge.remove(EV)
            BusyUntil.append([EV[0], timeSTEP]) #When does the EV come offline?
            PowerAssigned=PowerAssigned+EV[1]-ChargeRate
        elif len(AssignedEVsDischarge)>0:
            if EV[1]>GlobalMaxChargingRate: #If EV charging rate exceeds global limit
                DischargeRate=-GlobalMaxChargingRate #Use global limit
            else:
                DischargeRate=-EV[1] #Else, use EV limit
            EV=AssignedEVsDischarge[0] #Assign next vehicle
            #Take EV offline
            Schedule.append("('" + str(timeSTEP) + "','" + str(EV[0]) + "','" + str(EV[2]) + "','" + str(EV[1]) + "','" + str(DischargeRate) + " ','X'),")
            print(str(EV[0]) + " taken offline")
            AssignedEVsDischarge.remove(EV)
            BusyUntil.append([EV[0], timeSTEP]) #When does the EV come offline?
            PowerAssigned=PowerAssigned-EV[1]+DischargeRate
        else: #Nothing that can be fixed post-event
            print("... no assigned EVs to take offline")
            break
    
    if len(MarkFree)>0:
        #Mark all EVs taken offline prematurely as "free"
        database.execute("UPDATE Vehicles SET InEvent=0 WHERE IPaddress IN (" + str(MarkFree).strip('[').strip('\]') + ")")
        database.execute("UPDATE Vehicles SET InEventUntil='0000-00-00 00:00:00' WHERE IPaddress IN (" + str(MarkFree).strip('[').strip('\]') + ")")
    if len(MarkBusy)>0:
        #Mark all EVs assigned as "busy"
        database.execute("UPDATE Vehicles SET InEvent=1 WHERE IPaddress IN (" + str(MarkBusy).strip('[').strip('\]') + ")")
    if len(BusyUntil)>0:
        #Update times until which each EV is "busy"
        for i in BusyUntil:
            database.execute("UPDATE Vehicles SET InEventUntil='" + str(i[1]) + "' WHERE IPaddress='" + i[0] + "'")
    #Now input schedule into database
    if len(Schedule)>0:
        #Enter data into sql database; formatting required to remove special characters
        database.execute("INSERT INTO Schedule (ExecutionTime, IPaddress, PowerFlow, MaxPowerFlow, PreviousPowerFlow, Status) VALUES " + str(Schedule).replace('[', '').replace(']', '').replace('",', '').replace('"', '').strip('\,'))
    if len(MarkBusy)>len(BusyUntil):
        print("!!!Something is not right!!!")
        print("EVs marked busy without end time")
        print("- - - - - - - - - - - - - - - - - - - - - - - - - - - -")
        print("Assigned EV list charging: ")
        print(AssignedEVsCharge)
        print("- - - - - - - - - - - - - - - - - - - - - - - - - - - -")
        print("Assigned EV list discharging: ")
        print(AssignedEVsDischarge)
        print("- - - - - - - - - - - - - - - - - - - - - - - - - - - -")
        print("Effect Expiry list: ")
        print(EffectExpiry)
        print("- - - - - - - - - - - - - - - - - - - - - - - - - - - -")
        print("MarkBusy list: ")
        print(MarkBusy)
        print("- - - - - - - - - - - - - - - - - - - - - - - - - - - -")
        print("BusyUntil list: ")
        print(BusyUntil)
        print("- - - - - - - - - - - - - - - - - - - - - - - - - - - -")
        print("Schedule: ")
        print(Schedule)
        print("- - - - - - - - - - - - - - - - - - - - - - - - - - - -")
        
        sys.exit("Ending program here")
        
    #Append log file with boolean signalling if enough EVs were availble
    logfile = open("Schedulelog.txt","a+")
    logfile.write(str(EnoughEVs) + ",")
    logfile.close()


# In[ ]:


#Assign Global variables
print('Assigning global variables...')
#Define train event curves (power drawn/supplied per train per second by size)
global EventSmallTrainLeaving
EventSmallTrainLeaving = [0,100,200,300,400,500,600,700,800,900,1000,980,960,940,920,900,880,860,840,820,800,780,760,740,720,715,710,705,700,695,691,686,681,676,671,666,661,656,651,646,641,637,632,627,622,617,612,607,602,597,592,587,583,578,573,568,563,558,553,548,543,538,533,529,524,519,514,509,504,499,494,489,484,479,475,470,465,460,455,450,338,225,113,0]
global EventSmallTrainArriving
EventSmallTrainArriving = [0,-700,-900,-1000,-1100,-1150,-1200,-1250,-1275,-1285,-1295,-1300,-1280,-1260,-1230,-1200,-1145,-1091,-1036,-982,-927,-873,-818,-764,-709,-655,-600,-545,-491,-436,-382,-327,-273,-218,-164,-109,-55,0]
global EventMediumTrainLeaving
EventMediumTrainLeaving = [0,115,231,346,462,577,692,808,923,1038,1154,1269,1385,1500,1474,1448,1421,1395,1369,1343,1316,1290,1264,1238,1211,1185,1159,1133,1106,1080,1073,1066,1059,1052,1044,1037,1030,1023,1016,1009,1002,995,988,981,973,966,959,952,945,938,931,924,917,909,902,895,888,881,874,867,860,853,846,838,831,824,817,810,803,796,789,782,774,767,760,753,746,739,732,725,718,711,703,696,689,682,675,540,405,270,135,0]
global EventMediumTrainArriving
EventMediumTrainArriving = [0,-1050,-1350,-1500,-1650,-1725,-1800,-1875,-1913,-1928,-1943,-1950,-1920,-1890,-1845,-1800,-1718,-1636,-1555,-1473,-1391,-1309,-1227,-1145,-1064,-982,-900,-818,-736,-655,-573,-491,-409,-327,-245,-164,-82,0]
global EventLargeTrainLeaving
EventLargeTrainLeaving = [0,125,250,375,500,625,750,875,1000,1125,1250,1375,1500,1625,1750,1875,2000,1969,1938,1907,1876,1844,1813,1782,1751,1720,1689,1658,1627,1596,1564,1533,1502,1471,1440,1431,1422,1413,1404,1395,1386,1377,1368,1359,1350,1341,1332,1323,1314,1305,1296,1287,1278,1269,1260,1251,1242,1233,1224,1215,1206,1197,1188,1179,1170,1161,1152,1143,1134,1125,1116,1107,1098,1089,1080,1071,1062,1053,1044,1035,1026,1017,1008,999,990,981,972,963,954,945,936,927,918,909,900,750,600,450,300,150,0]
global EventLargeArriving
EventLargeTrainArriving = [0,-1400,-1800,-2000,-2200,-2300,-2400,-2500,-2550,-2570,-2590,-2600,-2560,-2520,-2460,-2400,-2291,-2182,-2073,-1964,-1855,-1745,-1636,-1527,-1418,-1309,-1200,-1091,-982,-873,-764,-655,-545,-436,-327,-218,-109,0]

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


# In[ ]:


print('Preprocessing complete, starting scheduling')

LastEventIDs = [0, 0, 0, 0, 0] #Used to prevent Scheduling the same event twice
while True:
    #Which event in TrainSchedule_EventOnly is the first to consider?
    Events=FindEvents()
    if len(Events)==0:
        time.sleep(1)
    else:
        #Any event(s) within the next 10 seconds?
        for i in Events:
            scheduled = False
            beforeScheduling=datetime.datetime.now() #For Benchmarking
            #Check if this event has been scheduled already
            for j in LastEventIDs:
                if i[2]==j:
                    scheduled = True
            if scheduled == False:
                #Need to convert timedelta pulled from database to datetime (SQL-python compatability weirdness)
                timeEvent = datetime.datetime.combine(datetime.date.today(), (datetime.datetime.min + i[0]).time())
                #What event is happening?
                #L1, L2, L3 - small, medium or large train leaving
                #A1, A2, A3 - small, medium or large train arriving
                if i[1] == "L1":
                    print("small train leaving")
                    Event=EventSmallTrainLeaving
                elif i[1] == "L2":
                    print("medium train leaving")
                    Event=EventMediumTrainLeaving
                elif i[1] == "L3":
                    print("large train leaving")
                    Event=EventLargeTrainLeaving
                elif i[1] == "A1":
                    print("small train arriving")
                    Event=EventSmallTrainArriving
                elif i[1] == "A2":
                    print("medium train arriving")
                    Event=EventMediumTrainArriving
                elif i[1] == "A3":
                    print("large train arriving")
                    Event=EventLargeTrainArriving
                elif i[1] == "C":
                    print("DEBUGGING EVENT")
                    Event=EventCombo
                EventSchedule(timeEvent, Event)
                LastEventIDs.remove(LastEventIDs[0])
                LastEventIDs.append(i[2]) #Last Event to be scheduled
                
                #Benchmarking
                afterScheduling=datetime.datetime.now()
                timetaken=afterScheduling-beforeScheduling
                print("time taken: " + str(timetaken))
                print("- - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                print("")
                
                #Append log file with event identifier and scheduling benchmark
                logfile = open("Schedulelog.txt","a+")
                logfile.write(str(timeEvent) + "," + str(LastEventIDs[-1]) + "," + str(timetaken) + "\n")
                logfile.close()
                
                time.sleep(1)