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)
