def main(): vtna = Squadron() dates = [ date(2015, 3, 27), date(2015, 3, 28) ] #Dates to write schedules for. Should be passed via sys.argv Assume unlisted gap dates are blank schedules. #Dealing with blank schedules needs more work. Schedules need to know if crew rests constraints apply from the previous day i = 1 for day in dates: sked = Schedule(day) sked.flyDay = i sked.waves[1].first = True vtna.schedules[day] = sked i = i + 1 #Creates the events in the syllabus. Would be replaced by call to data if necessary. for i in range(-3, 11): e = Event(i) if i > -3: vtna.syllabus[i - 1].followingEvents.add(e) if i > 0: e.flightHours = 1.0 if i != 5 and i != 9: e.onwing = True vtna.syllabus[i] = e vtna.syllabus[-3].initialEvent = True vtna.syllabus[5].offwing = True vtna.syllabus[9].offwing = True vtna.syllabus[9].check = True vtna.syllabus[10].followsImmediately = True s = Student("Test", vtna) d = date(2015, 3, 27) for k in s.events(d, vtna.schedules[d].waves[1]): print d, 1 print k d = date(2015, 3, 28) for k in s.events(d, vtna.schedules[d].waves[1]): print d, 1 print k for k in s.events(d, vtna.schedules[d].waves[2]): print d, 2 print k
def load(vtna,importName): book = xlrd.open_workbook(importName) sh = book.sheet_by_name("Multi") #vtna = Squadron() #Create a squadron object to hold the data dates = [date(2015,3,27),date(2015,3,28),date(2015,3,29)] #Dates to write schedules for. Should be passed via sys.argv Assume unlisted gap dates are blank schedules. #Dealing with blank schedules needs more work. Schedules need to know if crew rests constraints apply from the previous day i=1 for day in dates: sked=Schedule(day) sked.flyDay = i vtna.schedules[i]=sked i=i+1 vtna.totalFlightDays = len(dates) #Creates the events in the syllabus. Would be replaced by call to data if necessary. for i in range(-3,11): e = Event(i) if i > -3: vtna.syllabus[i-1].followingEvents.add(e) e.precedingEvents.add(vtna.syllabus[i-1]) if i>0: e.flightHours=1.0 if i != 5 and i !=9: e.onwing=True vtna.syllabus[i]=e vtna.syllabus[5].offwing=True vtna.syllabus[9].offwing=True vtna.syllabus[9].check=True vtna.syllabus[10].followsImmediately=True #Could modify any schedule data for any day as necessary """days = range(1,numdays+1) events = range(-3,11) numwaves = int(sh.cell_value(0,1)) waves = range(1,numwaves+1) maxstuds = int(sh.cell_value(3,1)) maxweight = int(sh.cell_value(4,1)) limitweight = int(sh.cell_value(5,2)) dcoeff = {} wcoeff = {} icoeff = {} for d in days: dcoeff[d]=float(sh.cell_value(7+d,8)) for w in waves: wcoeff[w]=float(sh.cell_value(7+w,5)) #maxstuds = {} #i=8 #for e in events: # maxstuds[e] = int(sh.cell_value(i,2)) # i = i+1""" sh = book.sheet_by_name("pavail") """planes = [] pavail = {} planetype = {}""" j=2 while True: try: plane = sh.cell_value(j,0) plane = plane.encode('utf8') #planes.append(plane) p = Plane(plane) p.planetype = sh.cell_value(j,1).encode('utf8') vtna.planes[plane]=p j = j+1 except IndexError: break print "Planes loaded" i=2 for p in vtna.planes: plane = vtna.planes[p] d=0 for day in vtna.schedules: plane._available[day]={} j=2 for w in vtna.schedules[day].waves: wave = vtna.schedules[day].waves[w] if int(sh.cell_value(i,5*d+j)) == 1: plane._available[day][wave]=True else: plane._available[day][wave]=False j=j+1 d=d+1 i=i+1 #pavail[planes[i],days[d],waves[j]]=int(sh.cell_value(i+2,5*d+j+2)) print "Plane availability loaded" sh = book.sheet_by_name("inst") """insts = [] imax = {} check = {} iweight = {} iqual = {} itype = {}""" i = 1 while True: try: inst = sh.cell_value(i, 0) inst = inst.encode('utf8') #insts.append(inst) vtna.instructors[inst]=Instructor(inst) #imax[inst]= int(sh.cell_value(i,1)) vtna.instructors[inst].maxEvents=int(sh.cell_value(i,1)) #check[inst]=int(sh.cell_value(i,2)) vtna.instructors[inst].check = int(sh.cell_value(i,2)) #iweight[inst]=int(sh.cell_value(i,3)) vtna.instructors[inst].weight = int(sh.cell_value(i,3)) if (int(sh.cell_value(i,4))==1): vtna.instructors[inst].quals.append('C-172-N') vtna.instructors[inst].quals.append('C-172-SP') vtna.instructors[inst].quals.append('C-172') if (int(sh.cell_value(i,5))==1): vtna.instructors[inst].quals.append('PA-28') """ itype[inst,'C-172-SP'] = int(sh.cell_value(i,4)) itype[inst,'C-172-N'] = int(sh.cell_value(i,4)) itype[inst,'PA-28'] = int(sh.cell_value(i,5)) #pc = 4 for p in planes: if itype[inst,planetype[p]]==1: iqual[inst,p] = 1 else: iqual[inst,p] = 0 # pc = pc+1""" i = i + 1 except IndexError: break print "Instructors loaded" sh = book.sheet_by_name("stud") """ studs = [] syll = {} sweight = {} squal = {} sprior = {}""" i = 1 while True: try: stud = sh.cell_value(i,0) stud = stud.encode('utf8') #studs.append(stud) vtna.students[stud]=Student(stud,vtna) #syll[stud]=int(sh.cell_value(i,1)) eventID = int(sh.cell_value(i,1)) vtna.students[stud].syllabus = 1 vtna.students[stud].nextEvent = vtna.syllabus[eventID] if eventID > -3: vtna.students[stud].scheduledEvents.add(vtna.syllabus[eventID-1]) for x in range(-3,eventID-1): vtna.students[stud].completedEvents.add(vtna.syllabus[x]) #sweight[stud]=int(sh.cell_value(i,2)) vtna.students[stud].weight = int(sh.cell_value(i,2)) vtna.students[stud].quals.append(sh.cell_value(i,3).encode('utf8')) """stype = sh.cell_value(i,3) stype = stype.encode('utf8') sprior[stud] = int(sh.cell_value(i,4)) for p in planes: #print p if planetype[p]==stype: squal[stud,p] = 1 else: squal[stud,p] = 0 # pc = pc+1""" i=i+1 except IndexError: break print "Students loaded" """ sh = book.sheet_by_name("iavail") iavail = {} for i in range(len(insts)): for d in range(len(days)): for w in range(len(waves)): iavail[insts[i],days[d],waves[w]] = int(sh.cell_value(i+2,5*d+w+1)) sh = book.sheet_by_name("ipref") for i in range(len(insts)): for d in range(len(days)): for w in range(len(waves)): icoeff[insts[i],days[d],waves[w]] = int(sh.cell_value(i+2,5*d+w+1)) sh = book.sheet_by_name("savail") savail = {} for s in range(len(studs)): for d in range(len(days)): for w in range(len(waves)): savail[studs[s], days[d], waves[w]]= int(sh.cell_value(s+2,5*d+w+1)) stud1 = [] onwingpair = {} onwinginst = {} """ sh = book.sheet_by_name("onwing") i = 1 while True: try: stud = sh.cell_value(i,0) stud=stud.encode('utf8') #stud1.append(stud) pair = sh.cell_value(i,1) pair = pair.encode('utf8') instructor = sh.cell_value(i,3).encode('utf8') #onwinginst[stud] = instructor #onwinginst[pair] = instructor vtna.students[stud].onwing = vtna.instructors[instructor] #onwingpair[stud]=pair if (pair != ''): vtna.students[stud].partner = vtna.students[pair] vtna.students[pair].partner = vtna.students[stud] vtna.students[pair].onwing = vtna.instructors[instructor] #t = sh.cell_value(i,2) #t = t.encode('utf8') #squal[stud]=t #squal[pair]=t i=i+1 except IndexError: break #istart = [] #sstart = [] sh = book.sheet_by_name("start") i=0 while True: try: wave = int(sh.cell_value(i,0)) plane = sh.cell_value(i,4).encode('utf8') inst = sh.cell_value(i,6).encode('utf8') stud = sh.cell_value(i,7).encode('utf8') event = int(sh.cell_value(i,9)) #istart.append((inst,plane,wave)) #sstart.append((stud,plane,wave,event)) vtna.students[stud].last['wave']=wave vtna.students[stud].last['plane']=plane vtna.instructors[inst].last['wave']=wave vtna.instructors[inst].last['plane']=plane s = Sortie() s.instructor = vtna.instructors[inst] s.plane = vtna.planes[plane] #Plane id s.wave = vtna.today.waves[wave] #Wave id ss = StudentSortie ss.student=vtna.students[stud] ss.event=event s.studentSorties.append(ss) vtna.today.sorties[i]=s i=i+1 except IndexError: break print "Ending load" """
def load(vtna, config): con = mdb.connect(host=config['host'], port=config['port'], user=config['user'], passwd=config['password'], db=config['db']) with con: cur = con.cursor() cur.execute("SELECT VERSION()") ver = cur.fetchone() if verbose: print "Database version : %s " % ver cur = con.cursor(mdb.cursors.DictCursor) #Loop over schedule table where not published and flight_day != NULL and add schedules for each flight_day cur.execute("SELECT * FROM schedule WHERE (published = FALSE)" ) # AND NOT flight_day = NULL rows = cur.fetchall() i = 1 priorities = {} priorities[1] = 1.0 priorities[2] = 0.5 priorities[3] = 0.3 priorities[4] = 0.2 priorities[5] = 0.2 priorities[6] = 0.2 priorities[7] = 0.2 days = config['days'] for row in rows: if i <= days: #i = int(row["flight_day"]) day = row["day"] sked = Schedule(day) sked.flyDay = i sked.id = int(row["schedule_ID"]) #Set priority """if row["priority"]!=None: sked.priority = float(row["priority"])""" sked.priority = priorities[i] if verbose: print 'Computing schedule for schedule ID %d, flight day %d, day %s, with priority %s ' % ( sked.id, sked.flyDay, day, sked.priority) vtna.schedules[i] = sked i = i + 1 vtna.totalFlightDays = days #Find waves cur.execute("SELECT * FROM wave") rows = cur.fetchall() waves = {} for row in rows: waves[int(row["wave_ID"])] = row #Create waves for each schedule. Having no waves makes the schedule blank. for d in vtna.schedules: sked = vtna.schedules[d] sked.waves = {} cur.execute("SELECT * FROM schedule_wave WHERE schedule_ID = %s", (sked.id)) rows = cur.fetchall() createWaves(sked, rows, waves) if verbose: print "Waves loaded" #Create events for squadron cur.execute("SELECT * FROM event") rows = cur.fetchall() for row in rows: i = int(row["event_ID"]) e = Event(i) if row["check_instructor_req"]: e.check = True if row["onwing_req"]: e.onwing = True elif row["not_onwing_req"]: e.offwing = True e.flightHours = float(row["dual_flight_hours"]) + float( row["solo_flight_hours"]) e.planeHours = float(row["ground_plane_hours"]) total_inst = float( row["ground_nonplane_hours"]) + e.planeHours + e.flightHours e.instructionalHours = total_inst e.syllabus = int(row["syllabus_ID"]) e.maxStudents = int(row["max_students"]) if row["follows_immediately"]: e.followsImmediately = True vtna.syllabus[i] = e #Set event precedence and following cur.execute("SELECT * FROM event_precedence") rows = cur.fetchall() for row in rows: i = int(row["following_event_ID"]) j = int(row["preceding_event_ID"]) if verbose: print i, ' follows ', j vtna.syllabus[i].precedingEvents.add(vtna.syllabus[j]) vtna.syllabus[j].followingEvents.add(vtna.syllabus[i]) if verbose: print "Events loaded" #Loop over planes cur.execute("SELECT * FROM plane WHERE active=TRUE") rows = cur.fetchall() for row in rows: #if verbose: print row["tail_number"],row["plane_type_ID"],row["max_cargo"] p = row["tail_number"] plane = Plane(p) plane.planetype = row["plane_type_ID"] if row["next_inspection"] != None and row["tach"] != None: plane.hours = float(row["next_inspection"]) - float( row["tach"]) if (row["max_cargo"] != 0 and row["max_cargo"] != None): plane.maxWeight = row["max_cargo"] if row["priority"] != None: plane.priority = row["priority"] vtna.planes[p] = plane #Add plane types #Add plane availability cur.execute( "SELECT * FROM plane_unavail WHERE (end >= %s and start <= %s)", (vtna.schedules[1].date.strftime('%Y-%m-%d'), vtna.schedules[vtna.totalFlightDays].date.strftime('%Y-%m-%d'))) rows = cur.fetchall() i = 1 for row in rows: p = row["plane_tail_number"] #if verbose: print p,row["start"],row["end"] if p in vtna.planes: plane = vtna.planes[p] s = Sniv() s.begin = row["start"] s.end = row["end"] plane.snivs[i] = s i = i + 1 if verbose: print "Planes loaded" #Loop over instructors, adding them cur.execute("SELECT * FROM cfi WHERE active = TRUE") rows = cur.fetchall() for row in rows: c = int(row["CFI_ID"]) if verbose: print c inst = Instructor(c) inst.maxEvents = row["max_events"] if row["C990"]: inst.check = True vtna.instructors[c] = inst if verbose: print "Instructors loaded" #Loop over students, adding them cur.execute("SELECT * FROM student WHERE status = 'active'") rows = cur.fetchall() for row in rows: s = int(row["student_ID"]) if verbose: print 'Student id ', s stud = Student(s, vtna) stud.syllabus = int(row["syllabus_ID"]) if row["priority"] != None: stud.priority = float(row["priority"]) cfi = row["onwing_CFI_ID"] if cfi in vtna.instructors: #if verbose: print "Add instructor",cfi stud.onwing = vtna.instructors[cfi] elif cfi != None: print 'CFI %d onwing for student %d not in instructors!' % ( cfi, s) else: print 'no cfi for student %d' % (s) vtna.students[s] = stud partner_ID = row["partner_student_ID"] if partner_ID in vtna.students: #if verbose: print "Add partners",s,partner_ID stud.partner = vtna.students[partner_ID] vtna.students[partner_ID].partner = stud if verbose: print "Students loaded" #Add weight for students & CFIs cur.execute("SELECT * FROM user") rows = cur.fetchall() for row in rows: id = row["user_ID"] if row["weight"] != None: if id in vtna.students: vtna.students[id].weight = int(row["weight"]) elif id in vtna.instructors: vtna.instructors[id].weight = int(row["weight"]) if verbose: print "Weights loaded" #Add plane quals for students & CFIs cur.execute("SELECT * FROM plane_quals") rows = cur.fetchall() for row in rows: id = row["user_ID"] if id in vtna.students: vtna.students[id].quals.append(row["plane_type_ID"]) elif id in vtna.instructors: vtna.instructors[id].quals.append(row["plane_type_ID"]) if verbose: print "Quals loaded" #Add snivs for students & CFIs cur.execute( "SELECT * FROM sniv WHERE (end >= %s and start <= %s and approval=TRUE)", (vtna.schedules[1].date.strftime('%Y-%m-%d'), vtna.schedules[vtna.totalFlightDays].date.strftime('%Y-%m-%d'))) rows = cur.fetchall() i = 1 for row in rows: id = row["user_ID"] if verbose: print id, row["start"], row["end"] if id in vtna.students: s = Sniv() s.begin = row["start"] s.end = row["end"] vtna.students[id].snivs[i] = s i = i + 1 elif id in vtna.instructors: s = Sniv() s.begin = row["start"] s.end = row["end"] vtna.instructors[id].snivs[i] = s i = i + 1 if verbose: print "Snivs loaded" #Load most recent published schedule as schedule.today() cur.execute( "SELECT * FROM schedule WHERE published=TRUE ORDER BY day DESC") row = cur.fetchone() vtna.today.id = int(row["schedule_ID"]) vtna.today.date = row["day"] cur.execute("SELECT * FROM schedule_wave WHERE schedule_ID = %s", (vtna.today.id)) rows = cur.fetchall() createWaves(vtna.today, rows, waves) cur.execute("SELECT * FROM sortie WHERE schedule_ID = %s", (vtna.today.id)) rows = cur.fetchall() for row in rows: s = Sortie() id = int(row["sortie_ID"]) if verbose: print id, row["CFI_ID"] s.brief = row["brief"] cfi_id = int(row["CFI_ID"]) if cfi_id in vtna.instructors and row["wave_ID"] != None: s.instructor = vtna.instructors[cfi_id] #Instructor s.studentSorties = [] s.takeoff = row["scheduled_takeoff"] s.land = row["scheduled_land"] if row["wave_ID"] != None: s.wave = vtna.today.waves[int( row["wave_ID"])] #Wave ojbect else: s.wave = vtna.today.waves[ 1] #This is a bad hack. Ought to use a function to determine nearest wave in today's set of waves vtna.today.sorties[id] = s #Create sorties and studentSorties from the entries in those table corresponding to the most recent published sked cur.execute( "SELECT * FROM student_sortie WHERE (status = 'pass' OR status = 'marginal' OR status = 'scheduled')" ) rows = cur.fetchall() for row in rows: if row["student_ID"] != None: s = int(row["student_ID"]) if s in vtna.students: stud = vtna.students[s] event = vtna.syllabus[int(row["event_ID"])] if row["status"] == "scheduled": stud.scheduledEvents.add(event) else: stud.completedEvents.add(event) if row["sortie_ID"] in vtna.today.sorties: sortie = vtna.today.sorties[row["sortie_ID"]] ss = StudentSortie() ss.student = vtna.students[s] ss.event = event if row["plane_tail_number"] in vtna.planes: sortie.plane = vtna.planes[ row["plane_tail_number"]] sortie.studentSorties.append(ss) if vtna.today.date == vtna.schedules[ 1].date + timedelta(days=1): sniv = Sniv() sniv.begin = sortie.brief sniv.end = sortie.wave.times[ "Flyer"].end + stud.crewRest stud.snivs[0] = sniv p = row["plane_tail_number"] if row["status"] == 'scheduled' and p in vtna.planes and row[ "sked_flight_hours"] != None: vtna.planes[p].hours -= float(row["sked_flight_hours"]) for s in vtna.students: stud = vtna.students[s] i = 1 for event in stud.findPossible(1, True): if verbose: print 'student ', s, 'possible event ', event.id if i == 1: stud.nextEvent = event i = i + 1 #Loop over instructor preferences cur.execute( "SELECT * FROM instructor_preference LEFT JOIN cfi ON instructor_preference.cfi_CFI_ID = cfi.CFI_ID WHERE cfi.active = TRUE" ) rows = cur.fetchall() for row in rows: c = int(row["cfi_CFI_ID"]) pref = row["preference"] inst = vtna.instructors[c] begin = row["start"] end = row["end"] for d, sked in vtna.schedules.iteritems(): midnight = datetime.combine(sked.date, time(0)) start_time = midnight + begin end_time = midnight + end s = Sniv() s.begin = start_time s.end = end_time r = Instructor(0) r.snivs[0] = s for w, wave in sked.waves.iteritems(): if not r.available(sked.date, wave): inst.setPreference(d, w, pref) if verbose: print "Set preference for instructor %d, day %d, wave %d for value %d" % ( c, d, w, pref)
def load(vtna, config): con = mdb.connect(host=config['host'], port=config['port'], user=config['user'], passwd=config['password'], db=config['database']) with con: # cur = con.cursor() # cur = con.cursor(dictionary=True) cur = con.cursor(mdb.cursors.DictCursor) days = config['days'] if days > 3: vtna.calculateMaintenance = True # Loop over schedule table where not published and flight_day != NULL and add schedules for each flight_day # Ought to sort by date and not pull past schedules - day >= date.today()! query = "SELECT * FROM schedule WHERE published = FALSE ORDER BY day ASC LIMIT %d" % days cur.execute(query) # AND NOT flight_day = NULL i = 1 rows = cur.fetchall() # .fetchmany(size=days) for row in rows: sked = Schedule(row) sked.flyDay = i # Set priority sked.priority = i**(-0.5) # priorities[i] if verbose: print 'Computing schedule for schedule ID %d, flight day %d, day %s, with priority %s' % ( sked.id, sked.flyDay, sked.day, sked.priority) vtna.schedules[i] = sked i += 1 vtna.days = len(rows) #Find waves cur.execute("SELECT * FROM wave") rows = cur.fetchall() waves = {} for row in rows: waves[int(row["wave_ID"])] = row #Create waves for each schedule for d in vtna.schedules: sked = vtna.schedules[d] if not sked.blank: sked.waves = {} # %(emp_no)s" # cursor.execute(select_stmt, { 'emp_no': 2 }) cur.execute( "SELECT * FROM schedule_wave WHERE schedule_ID = %(schedule_ID)s", {'schedule_ID': sked.id}) rows = cur.fetchall() createWaves(sked, rows, waves) if verbose: print "Waves loaded" #Create events for squadron cur.execute("SELECT * FROM event") rows = cur.fetchall() for row in rows: i = int(row["event_ID"]) e = Event(event_ID=i) if row["check_instructor_req"]: e.check = True if row["onwing_req"]: e.onwing = True elif row["not_onwing_req"]: e.offwing = True e.flightHours = float(row["dual_flight_hours"]) + float( row["solo_flight_hours"]) e.planeHours = float(row["ground_plane_hours"]) total_inst = float( row["ground_nonplane_hours"]) + e.planeHours + e.flightHours e.instructionalHours = total_inst e.syllabus = int(row["syllabus_ID"]) e.max_students = int(row["max_students"]) if row["follows_immediately"] != None and int( row["follows_immediately"]) == 1: e.followsImmediately = True vtna.syllabus[i] = e #Set event precedence and following cur.execute("SELECT * FROM event_precedence") rows = cur.fetchall() for row in rows: i = int(row["following_event_ID"]) j = int(row["preceding_event_ID"]) if verbose: print i, ' follows ', j vtna.syllabus[i].precedingEvents.add(vtna.syllabus[j]) vtna.syllabus[j].followingEvents.add(vtna.syllabus[i]) if verbose: print "Events loaded" #Loop over planes cur.execute("SELECT * FROM plane WHERE active=TRUE") rows = cur.fetchall() for row in rows: # if verbose: # print row["tail_number"],row["plane_type_ID"],row["max_cargo"] p = row["tail_number"] plane = Plane(id=p) plane.planetype = row["plane_type_ID"] ni = None tach = None if row["next_inspection"] != None: ni = float(row["next_inspection"]) if row["tach"] != None: tach = float(row["tach"]) if ni != None and tach != None and ni >= tach: plane.hours = ni - tach if ni != None and tach != None and ni < tach: plane.hours = 0.0 if (row["max_cargo"] != 0 and row["max_cargo"] != None): plane.maxWeight = row["max_cargo"] if row["priority"] != None: plane.priority = row["priority"] vtna.planes[p] = plane #Add plane types #Add plane availability cur.execute( "SELECT * FROM plane_unavail WHERE (end >= %s and start <= %s)", (vtna.schedules[1].day.strftime('%Y-%m-%d'), (vtna.schedules[vtna.days].day + timedelta(days=1)).strftime('%Y-%m-%d'))) rows = cur.fetchall() i = 1 for row in rows: p = row["plane_tail_number"] #if verbose: print p,row["start"],row["end"] if p in vtna.planes: plane = vtna.planes[p] s = Sniv() s.begin = row["start"] s.end = row["end"] plane.snivs[i] = s i = i + 1 if verbose: print "Planes loaded" #Loop over instructors, adding them cur.execute("SELECT * FROM cfi WHERE active = TRUE") rows = cur.fetchall() for row in rows: c = int(row["CFI_ID"]) if verbose: print c inst = Instructor(id=c) inst.max_events = row["max_events"] if row["C990"]: inst.check = True if row["paid"]: inst.paid = 1 vtna.instructors[c] = inst if verbose: print "Instructors loaded" #Loop over students, adding them cur.execute("SELECT * FROM student WHERE status = 'active'") rows = cur.fetchall() for row in rows: s = int(row["student_ID"]) if verbose: print 'Student id ', s stud = Student(id=s, squadron=vtna) stud.crewRestHours = 12 # stud.crewRest = timedelta(hours=12) stud.syllabus = int(row["syllabus_ID"]) if row["priority"] is not None: stud.priority = float(row["priority"]) if row["last_flight"] is not None: stud.last_flight = row["last_flight"] cfi = row["onwing_CFI_ID"] if cfi in vtna.instructors: #if verbose: print "Add instructor",cfi stud.onwing = vtna.instructors[cfi] elif cfi != None: print 'CFI %d onwing for student %d not in instructors!' % ( cfi, s) else: print 'no cfi for student %d' % (s) vtna.students[s] = stud # print vtna.students[s].__dict__ partner_ID = row["partner_student_ID"] if partner_ID in vtna.students: #if verbose: print "Add partners",s,partner_ID stud.partner = vtna.students[partner_ID] vtna.students[partner_ID].partner = stud if verbose: print "Students loaded" #Add weight for students & CFIs cur.execute("SELECT * FROM user") rows = cur.fetchall() for row in rows: id = row["user_ID"] if row["weight"] != None: if id in vtna.students: vtna.students[id].weight = int(row["weight"]) elif id in vtna.instructors: vtna.instructors[id].weight = int(row["weight"]) if verbose: print "Weights loaded" #Add plane quals for students & CFIs cur.execute("SELECT * FROM plane_quals") rows = cur.fetchall() for row in rows: id = row["user_ID"] if id in vtna.students: vtna.students[id].quals.append(row["plane_type_ID"]) elif id in vtna.instructors: vtna.instructors[id].quals.append(row["plane_type_ID"]) if verbose: print "Quals loaded" #Add snivs for students & CFIs cur.execute( "SELECT * FROM sniv WHERE (end >= %s and start <= %s and approval=TRUE)", (vtna.schedules[1].day.strftime('%Y-%m-%d'), (vtna.schedules[vtna.days].day + timedelta(days=1)).strftime('%Y-%m-%d'))) rows = cur.fetchall() i = 1 for row in rows: id = row["user_ID"] if verbose: print id, row["start"], row["end"] s = Sniv(row) if id in vtna.students: vtna.students[id].snivs[i] = s i += 1 elif id in vtna.instructors: vtna.instructors[id].snivs[i] = s i += 1 if verbose: print "Snivs loaded" #Load most recent published schedule as schedule.today() cur.execute( "SELECT * FROM schedule WHERE published=TRUE ORDER BY day DESC LIMIT 1" ) row = cur.fetchone() vtna.today.id = int(row["schedule_ID"]) vtna.today.day = row["day"] midnight = datetime.combine(vtna.today.day, time(0)) query = "SELECT * FROM schedule_wave WHERE schedule_ID = %d" % vtna.today.id cur.execute(query) rows = cur.fetchall() createWaves(vtna.today, rows, waves) query = "SELECT * FROM sortie WHERE schedule_ID = %s" % vtna.today.id cur.execute(query) rows = cur.fetchall() for row in rows: s = Sortie() id = int(row["sortie_ID"]) if verbose: print id, row["CFI_ID"] s.brief = row["brief"] cfi_id = int(row["CFI_ID"]) if cfi_id in vtna.instructors and row["wave_ID"] != None: s.instructor = vtna.instructors[cfi_id] #Instructor s.studentSorties = [] s.takeoff = row["scheduled_takeoff"] s.land = row["scheduled_land"] if row["wave_ID"] in vtna.today.waves: s.wave = vtna.today.waves[int( row["wave_ID"])] #Wave ojbect vtna.today.sorties[id] = s #Create sorties and studentSorties from the entries in those table corresponding to the most recent published sked cur.execute( "SELECT * FROM student_sortie WHERE (status = 'pass' OR status = 'marginal' OR status = 'scheduled')" ) rows = cur.fetchall() for row in rows: if row["student_ID"] != None: s = int(row["student_ID"]) if s in vtna.students: stud = vtna.students[s] event = vtna.syllabus[int(row["event_ID"])] if row["status"] == "scheduled": stud.scheduledEvents.add(event) else: stud.completedEvents.add(event) if row["sortie_ID"] in vtna.today.sorties: sortie = vtna.today.sorties[row["sortie_ID"]] ss = StudentSortie() ss.student = vtna.students[s] ss.event = event if row["plane_tail_number"] in vtna.planes: sortie.plane = vtna.planes[ row["plane_tail_number"]] sortie.studentSorties.append(ss) if vtna.today.day == (vtna.schedules[1].day - timedelta(days=1)): #print "happy dance", stud.id, sortie.wave.id takeoff = row["actual_takeoff"] if not takeoff: takeoff = midnight + sortie.takeoff land = row["actual_land"] if not land: land = midnight + sortie.land sniv = Sniv() sniv.begin = takeoff sniv.end = land + timedelta( hours=event.debrief_hours) + stud.crewRest() stud.snivs[0] = sniv instructor_sniv = Sniv() instructor_sniv.begin = takeoff instructor_sniv.end = land + timedelta( hours=event.debrief_hours ) + sortie.instructor.crewRest() sortie.instructor.snivs['crewrest' + str( row['student_sortie_ID'])] = instructor_sniv p = row["plane_tail_number"] if row["status"] == 'scheduled' and p in vtna.planes and row[ "sked_flight_hours"] != None: if (vtna.planes[p].hours - float(row["sked_flight_hours"])) >= 0: vtna.planes[p].hours -= float(row["sked_flight_hours"]) else: vtna.planes[p].hours = 0.0 """for s in vtna.students: stud = vtna.students[s] print 'Student: ', stud.id if stud.findPossible(1,True): print 'Next event: ', stud.getNextEvent() for event in stud.findPossible(1,True): if verbose: print 'student ', s, 'possible event ', event.id""" #Loop over instructor preferences cur.execute( "SELECT * FROM instructor_preference LEFT JOIN cfi ON instructor_preference.cfi_CFI_ID = cfi.CFI_ID WHERE cfi.active = TRUE" ) rows = cur.fetchall() for row in rows: c = int(row["cfi_CFI_ID"]) pref = row["preference"] inst = vtna.instructors[c] begin = row["start"] end = row["end"] for d, sked in vtna.schedules.iteritems(): midnight = datetime.combine(sked.day, time(0)) start_time = midnight + begin end_time = midnight + end s = Sniv() s.begin = start_time s.end = end_time r = Instructor(id='sample') r.snivs[0] = s for w, wave in sked.waves.iteritems(): if not r.available(sked.day, wave): inst.setPreference(d, w, pref) if verbose: print "Set preference for instructor %d, day %d, wave %d for value %d" % ( c, d, w, pref)