def ShowMyShifts(self, username):
username = (username,)
mydb = mysql.connector.connect(host="localhost", user="******", password='******', database='flight_manager',
auth_plugin='mysql_native_password')
mycursor = mydb.cursor()
query = """
SELECT flight_manager.staffing.flight_date, flight_manager.staffing.flight_type, flight_manager.staffing.leader , shifts.notes
FROM flight_manager.staffing join flight_manager.shifts
where flight_manager.staffing.flight_type = flight_manager.shifts.flight_type
and flight_manager.staffing.flight_date = flight_manager.shifts.flight_date
and personal_num= %s
"""
mycursor.execute(query, username)
results = mycursor.fetchall()
mydb.commit()
my_shifts = []
for row in results:
shift = Shift()
shift.flight_date = str(row[0])
shift.flight_type = str(row[1])
shift.leader = str(row[2])
shift.notes = str(row[3])
my_shifts.append(shift)
return my_shifts
def ShowAvailableShifts(self, status):
arg = (status,)
mydb = mysql.connector.connect(host="localhost", user="******", password='******', database='flight_manager',
auth_plugin='mysql_native_password')
mycursor = mydb.cursor()
query = """
SELECT flight_manager.shifts.flight_date, flight_manager.shifts.flight_type, flight_manager.type_of_shift.crew_role, flight_manager.type_of_shift.leader
from flight_manager.shifts join flight_manager.type_of_shift
on shifts.flight_type = type_of_shift.flight_type
left join flight_manager.staffing
on flight_manager.shifts.flight_date = flight_manager.staffing.flight_date
and flight_manager.type_of_shift.flight_type = flight_manager.staffing.flight_type
and (flight_manager.type_of_shift.crew_role = flight_manager.staffing.crew_role
or flight_manager.type_of_shift.crew_role = 'all')
and flight_manager.type_of_shift.leader = flight_manager.staffing.leader
where personal_num is NULL
and (type_of_shift.crew_role = %s or type_of_shift.crew_role = 'all')
and (type_of_shift.leader ='0' or type_of_shift.leader = '3')
and flight_manager.shifts.flight_date > CURDATE()
"""
mycursor.execute(query, arg)
results = mycursor.fetchall()
mydb.commit()
available_shifts1 = []
for row in results:
shift = Shift()
shift.flight_date = str(row[0])
shift.flight_type = str(row[1])
shift.crew_role = str(row[2])
shift.leader = str(row[3])
available_shifts1.append(shift)
return available_shifts1
def myweek(self):
today = datetime.today().strftime('%Y-%m-%d')
mydb = mysql.connector.connect(host="us-cdbr-iron-east-01.cleardb.net", user="******",
password='******', database='heroku_947e29c06a5b4a3')
mycursor = mydb.cursor()
query = """SELECT *
FROM shifts left join staffing
on staffing.flight_date = shifts.flight_date
and staffing.flight_type = shifts.flight_type
WHERE YEARWEEK(shifts.flight_date) = YEARWEEK(curdate());
"""
mycursor.execute(query, )
results = mycursor.fetchall()
mydb.commit()
mycursor.close()
shifts = []
for row in results:
shift = Shift()
shift.flight_date = str(row[0])
shift.flight_type = str(row[1]).capitalize()
shift.notes = str(row[2]).capitalize()
if shift.flight_date < today:
shift.status = 'Done'
else:
shift.status = "Not Done"
shifts.append(shift)
return shifts
def unmannedshifts(self):
mydb = mysql.connector.connect(host="localhost",
user="******",
password='******',
database='flight_manager',
auth_plugin='mysql_native_password')
mycursor = mydb.cursor()
query = """
SELECT flight_manager.shifts.flight_date, flight_manager.shifts.flight_type, flight_manager.type_of_shift.crew_role, flight_manager.type_of_shift.leader
from flight_manager.shifts join flight_manager.type_of_shift
on shifts.flight_type = type_of_shift.flight_type
left join flight_manager.staffing
on flight_manager.shifts.flight_date = flight_manager.staffing.flight_date
and flight_manager.type_of_shift.flight_type = flight_manager.staffing.flight_type
and (flight_manager.type_of_shift.crew_role = flight_manager.staffing.crew_role
or flight_manager.type_of_shift.crew_role = 'all')
and flight_manager.type_of_shift.leader = flight_manager.staffing.leader
where personal_num is NULL
"""
mycursor.execute(query)
results = mycursor.fetchall()
mydb.commit()
unmenned_shifts = []
count = 1
for row in results:
shift = Shift()
shift.number = count
count = count + 1
shift.flight_date = str(row[0])
shift.flight_type = str(row[1]).capitalize()
shift.crew_role = str(row[2]).capitalize()
if str(row[3]) == '1':
shift.leader = 'Must Be Movil'
elif str(row[3]) == '0':
shift.leader = 'Number 2'
else:
shift.leader = 'All'
unmenned_shifts.append(shift)
return unmenned_shifts
def unmannedshifts(self):
mydb = mysql.connector.connect(host="us-cdbr-iron-east-01.cleardb.net", user="******",
password='******', database='heroku_947e29c06a5b4a3')
mycursor = mydb.cursor()
query = """
SELECT shifts.flight_date, shifts.flight_type, type_of_shift.crew_role, type_of_shift.leader
from shifts join type_of_shift
on shifts.flight_type = type_of_shift.flight_type
left join staffing
on shifts.flight_date = staffing.flight_date
and type_of_shift.flight_type = staffing.flight_type
and (type_of_shift.crew_role = staffing.crew_role
or type_of_shift.crew_role = 'all')
and type_of_shift.leader = staffing.leader
where personal_num is NULL
"""
mycursor.execute(query)
results = mycursor.fetchall()
mydb.commit()
mycursor.close()
unmenned_shifts = []
count = 1
for row in results:
shift = Shift()
shift.number = count
count = count +1
shift.flight_date = str(row[0])
shift.flight_type = str(row[1]).capitalize()
shift.crew_role =str(row[2]).capitalize()
if str(row[3]) =='1':
shift.leader = 'Must Be Movil'
elif str(row[3]) == '0':
shift.leader = 'Number 2'
else :
shift.leader = 'All'
unmenned_shifts.append(shift)
return unmenned_shifts