import pyodbc
import matplotlib.pyplot as plt

#DB Connection
def dbGetConnection():
    server = 'rooftopsqlserveras.database.windows.net'
    database = 'Rooftop'
    username = 'rooftopreader@rooftopsqlserveras'
    password = '!one#0oftp'
    driver= '{ODBC Driver 13 for SQL Server}'
    cnxn = pyodbc.connect('DRIVER='+driver+';PORT=1433;SERVER='+server+';PORT=1443;DATABASE='+database+';UID='+username+';PWD='+ password)
    connection = cnxn.cursor()
    return connection

def plotRoomTotal(connection,roomkey):
    x_beoordeling = []
    y_SumCount = []
    query = ("SELECT Mn.PartitionKey, Mn.RoomKey, Mn.Value, Mp.CloseDateTime, Mn.MeasureDateTime, "
             "SUM(ZM.Count) AS SumCount, MAX(ZM.Count) AS MaxValue, COUNT(ZM.Count) AS CountsCount, "
             "CASE WHEN SUM(ZM.Count)=0 THEN 0 ELSE SUM(ZM.Count)/COUNT(CASE WHEN ZM.Count>1 THEN 1 ELSE NULL END) end AS Mean, "
             "MIN(ZM.MeasureDateTime) AS IntervalMin, MAX(ZM.MeasureDateTime) AS IntervalMax "
             "FROM dbo.Measure Mn OUTER APPLY "
             "(SELECT TOP 1 CloseDateTime FROM Measure Mi WHERE Mi.RoomId = Mn.RoomId AND Mi.CloseDateTime < Mn.MeasureDateTime ORDER BY CloseDateTime DESC) Mp "
             "JOIN Room R ON Mn.RoomId = R.Id "
             "JOIN ZoneMeasure ZM ON R.ZoneId = ZM.ZoneId AND ZM.MeasureDateTime BETWEEN Mp.CloseDateTime AND Mn.MeasureDateTime "
             "WHERE Mn.Roomkey IN ('213','214','217','218','221','226','313','314','317','318','321','326')"
             "GROUP BY Mn.PartitionKey, Mn.RoomKey, Mn.Value, Mp.CloseDateTime, Mn.MeasureDateTime	")
    connection.execute(query)
    rows = connection.fetchall()
    for row in rows:
        one = []
        two = []
        three = []
        #Split in 3 buckets
        if row[2] is 1:
            one.append(row[2])
        if row[2] is 2:
            two.append(row[2])
        if row[2] is 3:
            three.append(row[2])

        #Calculate averages of buckets
        sum1 = 0
        for num in one:
            sum1+=num
        x_beoordeling.append(1)
        y_SumCount.append(sum1/len(one))

        sum2 = 0
        for num in two:
            sum2+=num
        x_beoordeling.append(2)
        y_SumCount.append(sum2 / len(two))

        sum3 = 0
        for num in three:
            sum3+=num
        x_beoordeling.append(3)
        y_SumCount.append(sum3 / len(three))
    #plt.plot(x_beoordeling,y_SumCount,"o")
    #plt.show()

plotRoomTotal(dbGetConnection(),"'226'")