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)
