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
