def crt_semaster_calander_table(cursor): sql = "DROP TABLE IF EXISTS semaster_calander;" success, count = xdb.runSQL(cursor, sql) sql = '''CREATE TABLE semaster_calander ( spclid INTEGER PRIMARY KEY AUTOINCREMENT, gid INTEGER, semid INTEGER, spid INTEGER, nsession INTEGER, nday INTEGER, eid INTEGER, mid INTEGER, nlesson INTEGER, nweek INTEGER, nweekday INTEGER, sesid INTEGER, fitness INTEGER, genetics varchar(100), status INTEGER, adate date, sptype INTEGER); ''' success, count = xdb.runSQL(cursor, sql) return success
def crt_sessions_table(cursor, drop=False): if (drop): sql = "DROP TABLE IF EXISTS sessions;" success, count = xdb.runSQL(cursor, sql) sql = '''CREATE TABLE IF NOT EXISTS sessions ( sesid INTEGER PRIMARY KEY AUTOINCREMENT, name varchar(30), tfr INTEGER, tto INTEGER, fitness ) ''' success, count = xdb.runSQL(cursor, sql) return success
def crt_view_improved_eductor_modules(cursor): sql = '''SELECT educators.name, modules.name, EM.fitness, EM.gid, EM.semid FROM improved_educator_modules as EM INNER JOIN educators , modules ON EM.eid= educators.eid and EM.mid=modules.mid order by EM.gid, Em.semid, EM.eid, EM.mid, EM.fitness; ''' sql = "DROP VIEW IF EXISTS improved_educator_modules_V1;" success, count = xdb.runSQL(cursor, sql) vsql = "CREATE VIEW IF NOT EXISTS improved_educator_modules_V1 AS " + sql success, count = xdb.runSQL(cursor, vsql) return success
def crt_view_semaster_calender(cursor): sql = "DROP VIEW IF EXISTS semaster_calender_V1;" success, count = xdb.runSQL(cursor, sql) sql = ''' SELECT EC.gid, EC.semid, EC.nsession, EC.nday, EC.nweek, EC.nweekday, EC.sesid, spaces.name, educators.name, modules.name, EC.nlesson FROM semaster_calander as EC INNER JOIN spaces, educators , modules ON EC.spid=spaces.spid and EC.eid= educators.eid and EC.mid=modules.mid order by EC.gid, EC.semid, EC.nsession; ''' vsql = "CREATE VIEW IF NOT EXISTS semaster_calender_V1 AS " + sql success, count = xdb.runSQL(cursor, vsql) return success
def crt_educators_table(cursor, drop=False): if (drop): sql = "DROP TABLE IF EXISTS educators;" success, count = xdb.runSQL(cursor, sql) sql = '''CREATE TABLE IF NOT EXISTS educators ( eid INTEGER PRIMARY KEY AUTOINCREMENT, name varchar(30), fitness INTEGER, step INTEGER, gid INTEGER DEFAULT 0, semid INTEGER DEFAULT 0) ''' success, count = xdb.runSQL(cursor, sql) return success
def insert_sessions(cursor, n, delay): sql = "SELECT * FROM sessions LIMIT 1" success, count = xdb.runSQL(cursor, sql) if (count > 0): print("sessions table: Records exist") return False, 0 sqls = "" session_name_arr = ["Morning 1", "Morning 2", "Afternoon 1", "Afternoon 2"] session_data = [(8, 10, 4), (10, 12, 3), (13, 15, 2), (15, 17, 1)] i = 0 for i in range(4): name = session_name_arr[i] tfr, tto, fitness = session_data[i] sql = 'INSERT INTO sessions (name,tfr,tto,fitness) VALUES (' + '"{}" , {}, {}, {}'.format( name, tfr, tto, fitness) + ");" print(sql) sqls = sqls + sql success, count = xdb.runSQL_stmts(cursor, sqls, delay) return success, count
def crt_educator_modules_table(cursor, drop): if (drop): sql = "DROP TABLE IF EXISTS educator_modules;" success, count = xdb.runSQL(cursor, sql) sql = '''CREATE TABLE educator_modules ( emid INTEGER PRIMARY KEY AUTOINCREMENT, mid INTEGER, eid INTEGER, fitness INTEGER, gid INTEGER DEFAULT 0, semid INTEGER DEFAULT 0, sessions INTEGER DEFAULT 0); ''' success, count = xdb.runSQL(cursor, sql) return success
def insert_spaces(cursor, nlect, nlabs, gid, semid, delay): # nlabs is number of labs # nlecs is number of lecture halls # if gid =0 common for all groups else dedicated # if semid=0 common for all semasters else dedicated sql = "SELECT * FROM spaces LIMIT 1" success, count = xdb.runSQL(cursor, sql) if (count > 0): print("spaces table: Records exist") return False, 0 sqls = "" fitness = 1 for i in range(nlect): name = "Lect Hall " + str(i + 1) sptype = 1 sqls = sqls + 'INSERT INTO spaces (name,sptype,fitness,gid,semid) VALUES (' + '"{}",{}, {},{},{}'.format( name, sptype, fitness, gid, semid) + ');' for i in range(nlabs): name = "Lab " + str(i + 1) sptype = 2 sqls = sqls + 'INSERT INTO spaces (name,sptype,fitness,gid,semid) VALUES (' + '"{}",{}, {},{},{}'.format( name, sptype, fitness, gid, semid) + ');' success, count = xdb.runSQL_stmts(cursor, sqls, delay) return success, count
def insert_modules(cursor,n,delay,gid,semid): sql="SELECT * FROM modules LIMIT 1"; success, count=xdb.runSQL(cursor, sql) if (count > 0): print("modules table: Records exist") return False, 0 success, count=False,0 #reset #15 credits per semester, totals 120 credits after four years #7 * 1c + 4 * 2c =15 >----- < 4 x 5 * 15 = 300 sessions = 20 * 7 + 40 * 4 # 2 * 11 * 10 + 4 * 4 * 5=300 lectures and lab distributed to get 299 sessions sqls="" #data : fitness, weeks, lectures, labworks for 11 modules=n (299) n=11 # safety mwll_arr=data.module_data() # read data #for a in mod_weekly_freq_nweeks: # sum=sum+ a[0] * (a[1]+a[2]) for i in range (n): name="Module " + str(i+1) #print(i,n,name) fitness,nweeks,nlect,nlab=mwll_arr[i] step=1 # 1- every day, every 2nd day, every week leclab_gap=1 # days : lecture and lab sessions minumm gap sql='INSERT INTO modules (name,weeks, lectures,labworks,fitness,leclab_gap,gid,semid) VALUES ('+ '"{}" , {}, {}, {}, {},{},{}, {}'.format(name, nweeks,nlect,nlab,fitness,leclab_gap,gid,semid) +');' #print (sql) sqls=sqls + sql success , count=xdb.runSQL_stmts(cursor, sqls,delay) return success, count
def crt_modules_table(cursor,drop=False): if (drop): sql="DROP TABLE IF EXISTS modules;" success, count=xdb.runSQL(cursor, sql) sql='''CREATE TABLE modules ( mid INTEGER PRIMARY KEY AUTOINCREMENT, name varchar(30), weeks INTEGER DEFAULT 0, lectures INTEGER DEFAULT 0, labworks INTEGER DEFAULT 0, fitness INTEGER DEFAULT 0, leclab_gap INTEGER DEFAULT 1, gid INTEGER DEFAULT 0, semid INTEGER DEFAULT 0) ''' success, count=xdb.runSQL(cursor, sql) return success
def crt_fitness_table(cursor): sql = '''CREATE TABLE fitness ( fitid INTEGER PRIMARY KEY AUTOINCREMENT, eid INTEGER, mid INTEGER, spid INTEGER, sessid INTEGER); ''' success, count = xdb.runSQL(cursor, sql) return success
def insert_fitness(cursor, n, delay): sql = "SELECT * FROM fitness LIMIT 1" success, count = xdb.runSQL(cursor, sql) if (count > 0): print("fitness table: Records exist") return False, 0 success, count = False, 0 #reset #15 credits per semester, totals 120 credits after four years #7 * 1c + 4 * 2c =15 >----- < 4 x 5 * 15 = 300 sessions = 20 * 7 + 40 * 4 # 2 * 11 * 10 + 4 * 4 * 5=300 lectures and lab distributed to get 299 sessions sqls = "" #data : weeks, lectures, labworks for 11 fitness=n (299) n = 11 # safety mwll_arr = [(10, 1, 1), (10, 1, 1), (10, 1, 1), (10, 1, 1), (10, 1, 1), (10, 1, 1), (10, 1, 1), (14, 1, 1), (15, 1, 1), (15, 2, 1), (14, 2, 2)] #for a in mod_weekly_freq_nweeks: # sum=sum+ a[0] * (a[1]+a[2]) for i in range(n): name = "Module " + str(i + 1) #print(i,n,name) nweeks, nlect, nlab = mwll_arr[i] step = 1 # 1- every day, every 2nd day, every week sql = 'INSERT INTO fitness (name,weeks, lectures, labworks) VALUES (' + '"{}" , {}, {}, {}'.format( name, nweeks, nlect, nlab) + ');' #print (sql) sqls = sqls + sql success, count = xdb.runSQL_stmts(cursor, sqls, delay) return success, count
def insert_educators(cursor, n, delay): sql = "SELECT * FROM educators LIMIT 1" success, count = xdb.runSQL(cursor, sql) if (count > 0): print("educators table: Records exist") return False, 0 sqls = "" for i in range(n): name = "Lecturer " + str(i + 1) fitness = 1 step = 1 # 1- every day, every 2nd day, every week #here we assume every educator is teaching all semasters and groups #else algorithm or data set need to give semid and gid semid = 0 # 0 : all or 1-8: semaster number gid = 0 # 0 : all or 1-4: group no sqls = sqls + 'INSERT INTO educators (name,fitness, step,gid,semid) VALUES (' + '"{}" , {}, {},{},{}'.format( name, fitness, step, gid, semid) + ');' success, count = xdb.runSQL_stmts(cursor, sqls, delay) return success, count
def create_semaster_calander(cursor, delay, gid, semid): sql = "SELECT * FROM semaster_calander LIMIT 1" success, count = xdb.runSQL(cursor, sql) if (count > 0): print("semaster_calander table: Records exist") return False, 0 success, count = False, 0 #reset sql = 'SELECT * FROM modules WHERE gid={} and semid={} order by fitness'.format( gid, semid) cursor.execute(sql) modules = cursor.fetchall() mid_hits = [] space_hits = [] eid_hits = [] nsession = 0 # session week = 0 last_mod_first_session = 0 last_lab_first_session = 2 for mod in modules: # on fitness 1- 11 #name,weeks, lectures, labworks, leclab_gap, gid, semid mid = mod[0] mod_name = mod[1] mod_nweeks = mod[2] lect_per_week = mod[3] labs_per_week = mod[4] mod_fitness = mod[5] lectlab_gap = mod[6] mod_nlectures = lect_per_week * mod_nweeks mod_nlabs = labs_per_week * mod_nweeks eid = pick_module_educator(cursor, mid, eid_hits) # pick educator best fit if (eid == 0): # no educator print("no educator for module ", mid, eid_hits) continue lesson = 1 mod_lect_done = False mod_lab_done = False nsession = last_mod_first_session + 1 # we first allocate cal for module lectures sptype = 1 # lecture spid = pick_space(cursor, sptype, gid, semid, space_hits) # pick lecture space while not mod_lect_done: weekdone = False fails = 0 while (not weekdone): #try first session # returns success, space_ok, educator_ok, group_ok success, sp, ed, gr = book_calender( cursor, gid, semid, spid, sptype, nsession, mid, eid, lesson, mid_hits, eid_hits, space_hits) # some extra info if (success): print("Success ", spid, nsession, eid, mid, lesson) if (lesson == 1): last_mod_first_session = nsession # next lecture after this if (lesson == mod_nlectures ): # end for this module lectures mod_lect_done = True break else: lesson += 1 if (lect_per_week == 1): weekdone = True nsession += 20 # skip 1 week mean 20 sessions elif (lect_per_week == 2): # do in this week nsession += 4 # skip 1 day else: print("Tried and failed Lecture ", fails, spid, nsession, eid, mid, lesson) if (fails > 100): print("Failed ", fails, spid, nsession, eid, mid, lesson) #last_mod_first_session+=1 mod_lect_done = True break else: nsession += 1 # try next session if nsession > 300: pass #nsession=280 # skip 1 day time.sleep(delay / 5) # week #mod done # work on lab allocation lesson = 1 mod_lab_done = False nday, nweek, nweekday, sesid = convert_session(last_mod_first_session) if sesid == 1: nsession = last_mod_first_session + 2 # 0 elif sesid == 2: nsession = last_mod_first_session + 1 # 0 elif sesid == 3: nsession = last_mod_first_session + 1 # 0 elif sesid == 4: nsession = last_mod_first_session + 3 # 0 sptype = 2 # lab spid = pick_space(cursor, sptype, gid, semid, space_hits) # pick space while not mod_lab_done: weekdone = False fails = 0 while (not weekdone): #try first session # returns success, space_ok, educator_ok, group_ok success, sp, ed, gr = book_calender( cursor, gid, semid, spid, sptype, nsession, mid, eid, lesson, mid_hits, eid_hits, space_hits) # some extra info if (success): print("Success ", spid, nsession, eid, mid, lesson) if (lesson == mod_nlabs): # end for this module mod_lab_done = True break else: lesson += 1 if (labs_per_week == 1): weekdone = True nsession += 20 # skip 1 week elif (labs_per_week == 2): if (fails == 0): nsession += 4 # skip 1 day else: nsession += 1 # skip 1 day else: print("Trying Lab", fails, spid, nsession, eid, mid, lesson) if (fails > 100): print("Failed ", fails, spid, nsession, eid, mid, lesson) #last_mod_first_session+=1 mod_lab_done = True break else: nsession += 1 # try next session if nsession > 300: #nsession=280 pass time.sleep(delay / 5)
def insert_educator_modules(cursor, n, delay, gid, semid): sql = "SELECT * FROM educator_modules LIMIT 1" success, count = xdb.runSQL(cursor, sql) if (count > 0): print("educator_modules table: Records exist") return False, 0 success, count = False, 0 #reset # all educators can teach all modules # else we have to set gid and semid and educator modules relationship sql = '''SELECT * FROM educators ORDER by fitness DESC''' sql = sql.format(semid, gid, semid, gid) cursor.execute(sql) educators = cursor.fetchall() sql = "SELECT * FROM modules WHERE gid=" + str(gid) + " and semid=" + str( semid) + " ORDER by fitness ASC" cursor.execute(sql) modules = cursor.fetchall() edu_mid = [] edu_fitness = [] i = 0 nmods = len(modules) neds = len(educators) #number of educators = number of modules # we assign 3 modules each for educator in educators: eid = educator[0] picked = 0 #at least one module for an educator if (i < nmods): mid = modules[i][0] # default 1-11 : 1-11 if not skip rel1 = mid, eid # fitness 3 if rel1 not in edu_mid: edu_mid.append(rel1) edu_fitness.append(4) picked += 1 else: pass #what #second module if (i < nmods): mid2 = modules[nmods - i - 1][0] # pick from last rel2 = mid2, eid # fitness 1 if rel2 not in edu_mid: edu_mid.append(rel2) edu_fitness.append(3) else: fails = 0 while (True): mid3 = random.randint(1, nmods) rel3 = mid3, eid if rel3 not in edu_mid: edu_mid.append(rel3) edu_fitness.append(2) break if (fails >= (nmods * nmods)): # try 11 x 11 max break print("failed ", eid, mid, fails) fails += 1 # anyone gets 3rd module fails = 0 while (True): mid4 = random.randint(1, nmods) rel4 = mid4, eid if rel4 not in edu_mid: edu_mid.append(rel4) edu_fitness.append(1) break if (fails >= (nmods * nmods)): # try 11 x 11 max break print("failed ", eid, mid, fails) fails += 1 i += 1 # increment module sqls = "" i = 0 for midmods in edu_mid: mid, eid = midmods fitness = edu_fitness[i] i += 1 sql = 'INSERT INTO educator_modules (eid,mid,fitness,gid, semid) VALUES ({}, {}, {},{}, {}'.format( eid, mid, fitness, gid, semid) + ');' sqls = sqls + sql success, count = xdb.runSQL_stmts(cursor, sqls, delay) return success, count
def balance_educator_modules(cursor, gid, semid): # this algorithm analizes modules allocated to educators and balance them for equal distribution sql = '''SELECT educators.name, modules.name, EM.fitness, EM.eid , EM.mid, EM.gid, Em.semid FROM educator_modules as EM INNER JOIN educators , modules ON EM.eid= educators.eid and EM.mid=modules.mid WHERE (EM.gid={} and EM.semid={}) order by EM.gid, Em.semid, EM.eid, EM.fitness DESC, EM.mid; ''' sql = sql.format(gid, semid) print(sql) lect_mods = {} mod_lects = {} cursor.execute(sql) educator_modules = cursor.fetchall() for em in educator_modules: key = str('{:02}'.format(em[3])) tup = (em[4], em[2]) if (key in lect_mods): lect_mods[key].append(tup) else: lect_mods[key] = [tup] #lect_mods[key].mods=[em[4]] key = str('{:02}'.format(em[4])) tup = (em[3], em[2]) if (key in mod_lects): mod_lects[key].append(tup) else: mod_lects[key] = [tup] #mod_lects[key].lects=[em[3]] print(lect_mods) #print(mod_lects) #lect_mods=tools.sort_dict_key(lect_mods) #print(lect_mods) nlecturers = len(lect_mods) min_mods_per_lect = 10000 max_mods_per_lect = 0 for key in lect_mods: xlen = len(lect_mods[key]) if (xlen < min_mods_per_lect): min_mods_per_lect = xlen if (xlen > max_mods_per_lect): max_mods_per_lect = xlen if min_mods_per_lect == max_mods_per_lect: print("equal allocation modules for educator", min_mods_per_lect, ":", max_mods_per_lect, lect_mods) else: print("not equal allocation modules for educator", min_mods_per_lect, ":", max_mods_per_lect, lect_mods) # reallocare algo more -> less #pass ordered_mod_lects = tools.sort_dict_key(mod_lects) nmodules = len(ordered_mod_lects) min_lects_per_mod = 10000 max_lects_per_mod = 0 for key in mod_lects: xlen = len(ordered_mod_lects[key]) if (xlen < min_lects_per_mod): min_lects_per_mod = xlen if (xlen > max_lects_per_mod): max_lects_per_mod = xlen if min_lects_per_mod == max_lects_per_mod: print("equal educator allocation for modules", min_lects_per_mod, ":", max_lects_per_mod, mod_lects) else: print("not equal educator allocation for modules", min_lects_per_mod, ":", max_lects_per_mod, mod_lects) # reallocare algo more -> less #pass av_mods_per_lect = math.ceil((nmodules * 3) / nlecturers) mod_lects_by_val_order = tools.sort_dict_val_len(ordered_mod_lects) print("Module Lecturers", mod_lects_by_val_order) modkeys = mod_lects_by_val_order.keys() # get keys key_arr = [] # key array of modules_lects for key in modkeys: key_arr.append(key) # build a key list nmods = len(key_arr) # modules to handle for i in range(nmods): keyneed = key_arr[i] # ones in front need educators needlen = len(mod_lects_by_val_order[keyneed] ) # actual lecturers for this mod if (needlen < av_mods_per_lect ): # lessor than average , get from last ones for j in range(nmods): keyhave = key_arr[nmods - j - 1] # because ordered pick last havelen = len(mod_lects_by_val_order[keyhave]) m = 0 while (needlen + 1 < havelen): # until equal or less if ((not mod_lects_by_val_order[keyhave][m] in mod_lects_by_val_order[keyneed]) and (mod_lects_by_val_order[keyhave][m][1] != 4)): #print("pop ", i,j, m, mod_lects_by_order[keyhave][m]) mod = mod_lects_by_val_order[keyhave].pop( m) # take mod_lects_by_val_order[keyneed].append(mod) # give needlen += 1 # change new number of lects havelen -= 1 else: if (m < havelen - 1): m += 1 # move to next element else: break # skip to prev improved_mod_lects = tools.sort_dict_key(mod_lects_by_val_order) print("new mod lects", improved_mod_lects) # now based on this recreate lecturer modules improved_lect_mods = {} for key in improved_mod_lects: nkey = int(key) lects = improved_mod_lects[key] #print(lects,key,nkey) for i in range(len(lects)): modkey = lects[i][0] fitness = lects[i][1] if (modkey not in improved_lect_mods): improved_lect_mods[modkey] = [(nkey, fitness)] else: improved_lect_mods[modkey].append((nkey, fitness)) print("New lect mods", improved_lect_mods) for lect_mod in improved_lect_mods: eid = lect_mod for mod in improved_lect_mods[lect_mod]: print(lect_mod, mod) mid = mod[0] fitness = mod[1] sql = 'INSERT INTO improved_educator_modules (eid,mid,fitness,gid,semid) VALUES ({}, {}, {},{}, {}'.format( eid, mid, fitness, gid, semid, ) + ');' success, count = xdb.runSQL(cursor, sql) time.sleep(0.05) pass #print ("Lecturer Modules ", lect_mods) #print ("Module Lectturers" , mod_lects) return True