def get_all_requirements():
"Joins scheduling requirements of theory and lab for all classes"
## Get all scheduling requirements -- Join theory and lab requirements
f_subjectClassTeacher = da.execquery(
'select s.subjectId, subjectShortName, totalHrs, eachSlot, c.classId, teacherId from subject s, subjectClassTeacher c where s.subjectId = c.subjectId;'
)
f_subjectClassTeacher.insert(5, 'batchId', '-')
f_subjectClassTeacher.insert(6, 'category', 'T') #T for theory
f_subjectBatchTeacher = da.execquery(
'select s.subjectId, subjectShortName, totalHrs, eachSlot, sbt.batchId, bc.classId, teacherId from subject s, subjectBatchTeacher sbt, batchClass bc where s.subjectId = sbt.subjectId AND sbt.batchId = bc.batchId;'
)
f_subjectBatchTeacher.insert(6, 'category', 'L') #L for Lab
f_subjectBatchClassTeacher = pd.concat(
[f_subjectClassTeacher, f_subjectBatchTeacher])
## Split requirements based on each slot
f_subjectBatchClassTeacher = f_subjectBatchClassTeacher.reset_index()
totallectures_list = (f_subjectBatchClassTeacher['totalHrs'] /
f_subjectBatchClassTeacher['eachSlot'])
# Create empty dataframe to save all the requirements
req_all = pd.DataFrame(index=range(int(totallectures_list.sum())),
columns=list(f_subjectBatchClassTeacher))
j = 0
for i in range(len(req_all)):
if ((f_subjectBatchClassTeacher.iloc[j]['totalHrs'] /
f_subjectBatchClassTeacher.iloc[j]['eachSlot']) > 0):
req_all.loc[[i]] = f_subjectBatchClassTeacher.iloc[[j]].values
f_subjectBatchClassTeacher.set_value(
j, 'totalHrs', f_subjectBatchClassTeacher.loc[j]['totalHrs'] -
f_subjectBatchClassTeacher.loc[j]['eachSlot'])
if (f_subjectBatchClassTeacher.iloc[j]['totalHrs'] == 0):
j = j + 1
req_all = req_all.rename(columns={'index': 'id'}) # Added -- Aditi 27/4
return req_all
def test_class_batch_overlap(timetable, req_all):
"""Calculates overlaps for theory classes and (non allowed) overlaps for batches and increments cost accordingly. Inconsistencies in data are handled"""
class_cost = 0
batch_cost = 0
n_classes, n_days, n_slots, n_max_lec_per_slot=timetable.shape
f_batch_can_overlap = da.initialize('batchcanoverlap');
# Get from database classId, batchId, overlapClassId, overlapBatchId
f_overlapping_batches_with_classId = da.execquery("SELECT bc.classId, bc.batchId, bca.classId as 'overlapClassId', bca.batchId as 'overlapBatchId' FROM batchClass bc, batchClass bca, batchCanOverlap bo WHERE bc.batchId = bo.batchId AND bca.batchId = bo.batchOverlapId ");
for cl in range(n_classes):
for day in range(n_days):
for slot in range(n_slots):
class_list = []
batch_list = []
slot_array = timetable[cl,day,slot,:]
# Make 2 lists-class_list having all classes in the sub-slot & batch-list having all batches in sub-slot
# Classes have category 'T' and Batches have category 'L'
for sub_slot in slot_array:
if not np.isnan(sub_slot):
req = req_all.loc[req_all.index == sub_slot]
if req.iloc[0]['category'] == 'T':
class_list.append(req.iloc[0]['classId'])
elif req.iloc[0]['category'] == 'L':
batch_list.append(req.iloc[0]['batchId'])
# If the same class is repeated in the class_list for the same sub-slot, increment cost
if len(class_list) > 1 : # Cost will be incremented only if multiple classes in same sub-slot
for class_id in class_list: class_cost = class_cost + class_list.count(class_id) - 1
if len(batch_list)>1: # Cost will be incremented only if multiple batches in same sub-slot
for batch_id in batch_list: # In case same batch is slotted more than once in sub slot
batch_cost = batch_cost + batch_list.count(batch_id) - 1
# 1. Consider first batch in batch_list.
# 2. Get all batches that are allowed to overlap
# 3. Loop over all batches in batch_list. If any batch does'nt belong to this list, cost incremented
batch_id = batch_list[0]
#batches_can_overlap = f_batch_can_overlap[f_batch_can_overlap['batchId'] == batch_id]
#batches_all = batches_can_overlap[batches_can_overlap.columns[2:3]] # get batch_can_overlap colum
#batches_all_list = batches_all['batchOverlapId'].tolist()
#batches_all_list.append(batch_id)
for batch in batch_list:
overlap_allowed = t.get_overlapping_batch_list(f_overlapping_batches_with_classId, cl, batch);
if (len(overlap_allowed) > 0):
if batch not in overlap_allowed:
batch_cost += 1
# print("ClassId: ", cl, "Batch: ", batch, "Batch_all_lsit:", overlap_allowed, "batch_cost: ", batch_cost, "class_cost: ", class_cost);
#print("Final cost: ", class_cost + batch_cost);
return class_cost + batch_cost
def write_to_timetable_db(time_table, req_all, theory_group, lab_group):
"""Function for writing the created time table in database"""
n_classes, n_days, n_slots, n_max_lec_per_slot = time_table.shape
tt_id = 0
f_time_table = da.execquery('select * from timeTable;')
f_time_table.drop(f_time_table.index, inplace=True)
#lab_group = []
#theory_group = []
#get_room_groups(lab_group, theory_group);
#max_theory = len(theory_group);
#max_lab = len(lab_group);
#f_time_table = pd.DataFrame(index=range(len(req_all.index)), columns=list(f_time_table))
f_time_table = pd.DataFrame(index=range(444), columns=list(f_time_table))
tt_data = []
for day in range(n_days):
for slot in range (n_slots):
temp_array = time_table[:, day, slot, :]
theory_group_index = 0
lab_group_index = 0
for row in temp_array:
for cell in row:
if not np.isnan(cell):
req = req_all.loc[req_all.index == cell]
if req.iloc[0]['category'] == 'T':
if theory_group_index > len(theory_group):
theory_room_id = -1000
else:
theory_room_id = theory_group[theory_group_index]
f_time_table.loc[tt_id] = [tt_id, day, slot, theory_room_id, req.iloc[0]['classId'],
req.iloc[0]['subjectId'], req.iloc[0]['teacherId'],
req.iloc[0]['batchId'], 0, 0, 0]
theory_group_index += 1
else:
if lab_group_index > len(lab_group): # Accounting for the room ids for labs starting from 5
lab_room_id = -1000
else:
lab_room_id = lab_group[lab_group_index]
#required_slots = req.iloc[0]['']
f_time_table.loc[tt_id] = [tt_id, day, slot, lab_room_id, req.iloc[0]['classId'],
req.iloc[0]['subjectId'], req.iloc[0]['teacherId'],
req.iloc[0]['batchId'], 0, 0, 0]
lab_group_index += 1
tt_id += 1
#da.exec_insert('timeTable', f_time_table)
f_time_table.to_csv("tt.csv");
r_day = random.randint(0, n_days - 1)
r_slot = random.randint(0, n_slots - 1)
if (is_slot_available(req_all, timetable_np, c, r_day, r_slot,
req, theory_roomgroup, lab_roomgroup)):
timetable_np, theory_roomgroup, lab_roomgroup = assign(
timetable_np, int(c), r_day, r_slot, req, req_all,
theory_roomgroup, lab_roomgroup)
req_all.set_value(req, 'isAssigned', True)
notassigned = 0
return timetable_np
print("Welcome")
f_subject_subjectClassTeacher = da.execquery(
'select s.subjectId, subjectShortName, totalHrs, eachSlot, c.classId, teacherId from subject s, subjectClassTeacher c where s.subjectId = c.subjectId;'
)
f_subject_subjectClassTeacher.insert(5, 'batchId', '-')
f_subject_subjectClassTeacher.insert(6, 'category', 'T') #T for theory
f_subject_subjectBatchTeacher = da.execquery(
'select s.subjectId, subjectShortName, totalHrs, eachSlot, sbt.batchId, bc.classId, teacherId from subject s, subjectBatchTeacher sbt, batchClass bc where s.subjectId = sbt.subjectId AND sbt.batchId = bc.batchId;'
)
f_subject_subjectBatchTeacher.insert(6, 'category', 'L') #L for Lab
f_subjectBatchClassTeacher = pd.concat(
[f_subject_subjectClassTeacher, f_subject_subjectBatchTeacher])
f_batch_can_overlap = da.execquery(
'select batchId, batchOverlapId from batchCanOverlap;')
#print(f_batch_can_overlap)
x = f_subjectBatchClassTeacher
x = x.reset_index()
for cell in row:
if not np.isnan(cell):
req = req_all.loc[req_all.index == cell]
teacher_list.append(req.iloc[0]['teacherId'])
for teacher_id in teacher_list:
if teacher_id is not None:
teacher_cost = teacher_cost + teacher_list.count(teacher_id) - 1
return teacher_cost
print("welcome");
# Join requirements for lab batches and theory classes
f_join_subject_subjectClassTeacher = da.execquery('select s.subjectId, subjectShortName, totalHrs, eachSlot, c.classId, teacherId from subject s, subjectClassTeacher c where s.subjectId = c.subjectId;')
f_join_subject_subjectClassTeacher.insert(5,'batchId','-')
f_join_subject_subjectClassTeacher.insert(6,'category','T') #T for theory
f_join_subject_subjectBatchTeacher = da.execquery('select s.subjectId, subjectShortName, totalHrs, eachSlot, sbt.batchId, bc.classId, teacherId from subject s, subjectBatchTeacher sbt, batchClass bc where s.subjectId = sbt.subjectId AND sbt.batchId = bc.batchId;')
f_join_subject_subjectBatchTeacher.insert(6,'category','L') #L for Lab
f_subjectBatchClassTeacher = pd.concat([f_join_subject_subjectClassTeacher, f_join_subject_subjectBatchTeacher])
#print(f_subjectBatchClassTeacher)
x = f_subjectBatchClassTeacher
x = x.reset_index()
totallectures_list = (x['totalHrs'] / x['eachSlot'])
def separate_batches (timetable, req_all, theory_roomgroup, lab_roomgroup, max_theory, max_lab):
"Seaparates batches with same batch id in a slot and batches which cannot overlap"
# Get from database classId, batchId, overlapClassId, overlapBatchId
f_overlapping_batches_with_classId = da.execquery("SELECT bc.classId, bc.batchId, bca.classId as 'overlapClassId', bca.batchId as 'overlapBatchId' FROM batchClass bc, batchClass bca, batchCanOverlap bo WHERE bc.batchId = bo.batchId AND bca.batchId = bo.batchOverlapId ");
n_classes, n_days, n_slots, n_lec_per_slot = timetable.shape;
for classId in set(req_all.classId):
# lab requirements for a class
req_class_lab = req_all[(req_all['category'] == 'L') & (req_all['classId'] == classId)]
max_tries = len(req_class_lab);
# Check if requirements are present, there are few cases where this does not satisfy
if (len(req_class_lab) > 0):
while (max_tries > 0):
# Select 1 lab requirement randomly, take its batchId
r_req_no = random.choice((req_class_lab.index).tolist())
r_req = req_all[(req_all.index == r_req_no)];
r_req_batchId = r_req.loc[r_req_no, 'batchId']
# Get its starting location
indices = np.argwhere (timetable[classId] == r_req_no);
r_req_day, r_req_slot, r_req_subslot = indices[0];
# Get batches which can go parallel
batches_allowed = get_overlapping_batch_list (f_overlapping_batches_with_classId, classId, r_req_batchId)
if (len(batches_allowed) > 0):
batches_allowed.remove(r_req_batchId);
# Check for all requirements in this slot if their batchIds are same
for i in range(0, n_lec_per_slot):
req_no = timetable[classId, r_req_day, r_req_slot, i]
if (not np.isnan(req_no)):
req = req_all.loc[req_all.index == req_no]
req_batchId = req.loc[req_no, 'batchId']
if (r_req_no != req_no and (r_req_batchId == req_batchId or (len(batches_allowed) > 0 and (req_batchId not in batches_allowed)))):
#print("found for ", r_req_batchId)
# Take req indices
req_indices = np.argwhere (timetable[classId] == req_no);
# Selct 1 batch randomly
if (len(batches_allowed) > 0):
tries = len(req_class_lab);
isSuccess = False
while ((not isSuccess) and (tries > 0)):
random_batch = random.choice(batches_allowed);
#isSuccess = False
# Get requirements forrandom batch
req_for_random_batch = req_all[(req_all['batchId'] == random_batch)]
# Choose 1 req randomly, take its starting location
req_test = random.choice((req_for_random_batch.index).tolist());
test_indices = np.argwhere (timetable[classId] == req_test);
test_day, test_slot, test_subslot = test_indices[0];
# Check if there is availability for r_req_no in that slot
is_available = m.is_slot_available(req_all, timetable, classId, test_day, test_slot, r_req_no, theory_roomgroup, lab_roomgroup, max_theory, max_lab);
if (is_available):
make_slot_empty(timetable, req_all, classId, r_req_no, theory_roomgroup, lab_roomgroup)
m.assign(timetable, classId, test_day, test_slot, r_req_no, req_all, theory_roomgroup, lab_roomgroup);
isSuccess = True
tries -= 1
# Remove req from earliear allotment
#for j in range(len(indices)):
# timetable[classId, indices[j][0], indices[j][1], indices[j][2]] = np.nan
else:
break;
max_tries -= 1
