Smart Thermostat Raspberry PI - part select temperature scenario

smarttherm_part_select_temp_scenario.py

def getscen(tempdif,outtempdif):
    """
    Returns the number of minutes the boiler should be on for 6 x 10 minute intervalls.
    Returns used sceneario key and array of 6 values containing the number of minutes
    the boiler should be on each interval. For example: [2,2,2,2,2]

    Randomly selects
    1) best sceario (lowest score) given a tempdif or outtempdif
    2) not the best sceario (not lowest score) given a tempdif or outtempdif

    Used by fcontroll() function.
    """
    selselmode=[1,2] #1 is best scenario, 2 is not the best scenario
    selmode = random.choice(selselmode)
    usedkey = 'failed' #returns failed if it is not possible to slect scenario
    runminutes=[]
    try:
        #determine the latest version of the temperature scenario's
        #viepoch is used as version number
        #the same version is used consistently the rest of the function the stay consistent
        #even if the scenario's are updated in the meantime
        dbdconsa5 = dbc.cursor()
        dbdconsa5.execute("SELECT max(viepoch) from actscenario")
        rows = dbdconsa5.fetchall()
        for row in rows:
            maxiepoch = int(row[0])
        dbdconsa5.close()

        #case for selecting best scenario
        if (selmode == 1):
            dbdconsa = dbc.cursor()
            dbdconsa.execute("SELECT vkey, run0,run1,run2,run3,run4,run5 from actscenario a1 \
                             INNER JOIN (SELECT MIN(vscore) as maxvscore, vgroup,vtempdif,vouttempdif FROM actscenario \
                                            WHERE viepoch = %i \
                                            GROUP BY vgroup,vtempdif,vouttempdif) a2 \
                                ON a1.vgroup = a2.vgroup \
                                AND a1.vouttempdif = a2.vouttempdif \
                                AND a1.vtempdif = a2.vtempdif \
                                AND a1.vscore = a2.maxvscore \
                            INNER JOIN (SELECT MIN(ABS(vouttempdif - %i)) as minouttempdif FROM actscenario \
                                        WHERE viepoch = %i) a3 \
                                        ON ABS(a1.vouttempdif - %i) = a3.minouttempdif \
                            WHERE viepoch = %i \
                            ORDER BY ABS(a1.vtempdif - %i) LIMIT 1" % (maxiepoch,outtempdif,maxiepoch,outtempdif,maxiepoch,tempdif))
            rows = dbdconsa.fetchall()
            for row in rows:
                #print(row)
                usedkey = str(row[0])
                runminutes = row[1:]
            dbdconsa.close()

        #case for selecting not the best scenario
        else:
            dbdconsa2 = dbc.cursor()
            dbdconsa2.execute("SELECT a1.vgroup, a1.vscore, a1.vouttempdif from actscenario a1 \
                             INNER JOIN (SELECT MIN(vscore) as maxvscore, vgroup,vtempdif,vouttempdif FROM actscenario \
                                            WHERE viepoch = %i \
                                            GROUP BY vgroup,vtempdif,vouttempdif) a2 \
                                ON a1.vgroup = a2.vgroup \
                                AND a1.vouttempdif = a2.vouttempdif \
                                AND a1.vtempdif = a2.vtempdif \
                                AND a1.vscore <> a2.maxvscore \
                            INNER JOIN (SELECT MIN(ABS(vouttempdif - %i)) as minouttempdif FROM actscenario \
                                          WHERE viepoch = %i  ) a3 \
                                        ON ABS(a1.vouttempdif - %i) = a3.minouttempdif \
                            WHERE viepoch = %i \
                            ORDER BY ABS(a1.vtempdif - %i) LIMIT 1" % (maxiepoch,outtempdif,maxiepoch,outtempdif,maxiepoch,tempdif))
            rows = dbdconsa2.fetchall()
            for row in rows:
                fselgroup = row[0]
                fselscore = row[1]
                fouttempdif = row[2]
            dbdconsa2.close()

            dbdconsa3 = dbc.cursor()
            dbdconsa3.execute("SELECT rowid FROM actscenario WHERE vgroup = %i AND vscore <= %i AND vouttempdif = %i " % (fselgroup,fselscore,fouttempdif))
            rows = dbdconsa3.fetchall()
            selrowid = int(random.choice(rows)[0]) #randomly selects a scenario key from the available alternative scenarios

            #select the number of minues given the scenario key selected in the previous step
            dbdconsa4 = dbc.cursor()
            dbdconsa4.execute("SELECT vkey,run0,run1,run2,run3,run4,run5 from actscenario WHERE rowid = %i" % (selrowid,))
            rows = dbdconsa4.fetchall()
            for row in rows:
                usedkey = str(row[0])
                runminutes = row[1:]
        return usedkey,runminutes

    except:
        logging.exception("getscen")
        return usedkey,runminutes