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