def post(self):
'''create login for pilot'''
connection = get_connection("database_server.db")
cursor = connection.cursor()
payload = api.payload
username = payload['username']
password = payload['password']
select_stmt = cursor.execute(
'SELECT Passwort FROM Pilot WHERE Nutzername LIKE ?', [username])
try:
# wenn der pilot schon ein passwort hat, return 512
if select_stmt.fetchone()[0] is not None:
return {}, 512
# falls es keine ergebnisse gibt -> pilot existiert nicht
except TypeError:
return {}, 512
cursor.execute('UPDATE Pilot SET Passwort = ? WHERE Nutzername LIKE ?',
[password, username])
connection.commit()
connection.close()
return {}, 200
def get(self):
'''get pilots'''
connection = get_connection("database_server.db")
cursor = connection.cursor()
if not is_admin(cursor):
return {}, 401
args = pilots_parser.parse_args()
p_id = args['id']
is_active = args['is_active']
return_dict = {
'pilots': []
}
# /pilots?is_active=true
if p_id is None:
if is_active:
select_stmt = cursor.execute(
'SELECT PilotID, Vorname, Nachname, Eintrittsdatum, RFID_Code, Nutzername, Ist_Admin '
'FROM Pilot WHERE RFID_Code IS NOT NULL '
)
else:
select_stmt = cursor.execute(
'SELECT PilotID, Vorname, Nachname, Eintrittsdatum, RFID_Code, Nutzername, Ist_Admin '
'FROM Pilot WHERE RFID_Code IS NULL '
)
# /pilots?id=1
elif is_active is None:
select_stmt = cursor.execute(
'SELECT PilotID, Vorname, Nachname, Eintrittsdatum, RFID_Code, Nutzername, Ist_Admin '
'FROM Pilot WHERE PilotID = ?', [p_id]
)
# /pilots?id=1&is_active=true
else:
if is_active:
select_stmt = cursor.execute(
'SELECT PilotID, Vorname, Nachname, Eintrittsdatum, RFID_Code, Nutzername, Ist_Admin '
'FROM Pilot WHERE PilotID = ? AND RFID_Code IS NOT NULL', [p_id]
)
else:
select_stmt = cursor.execute(
'SELECT PilotID, Vorname, Nachname, Eintrittsdatum, RFID_Code, Nutzername, Ist_Admin '
'FROM Pilot WHERE PilotID = ? AND RFID_Code IS NULL', [p_id]
)
for row in select_stmt:
pilot = {
'pilot_id': row[0],
'pilot_name': row[1],
'pilot_surname': row[2],
'entry_date': row[3],
'rfid': 'null' if row[4] is None else hex(row[4]),
'pilot_username': row[5],
'is_admin': bool(row[6])
}
return_dict['pilots'].append(pilot)
connection.close()
return return_dict
def sync_rfids():
if not is_online():
print("Terminal offline")
return
connection = get_connection("database_server.db")
cursor = connection.cursor()
unsynced_rfid_list = cursor.execute(
'SELECT RFID_Code FROM RFID_Ausweis WHERE Synced = FALSE'
).fetchall()
for rfid in unsynced_rfid_list:
payload = {
'rfid_code': rfid[0]
}
try:
response = requests.post(url=RASPI_URL + '/rfid', data=payload, timeout=2)
except:
print('Post failed')
return
if not response.ok:
continue
set_synced_rfid(rfid[0], True)
connection.close()
return
def sync_pilots():
if not is_online():
print("Terminal is offline")
return
connection = get_connection("database_server.db")
cursor = connection.cursor()
unsynced_pilot_list = cursor.execute(
'SELECT PilotID, RFID_Code, Token FROM Pilot WHERE Synced = FALSE'
).fetchall()
for pilot in unsynced_pilot_list:
payload = {
'pilot_id': pilot[0],
'rfid_code': pilot[1],
'token': pilot[2]
}
try:
response = requests.post(url=RASPI_URL + '/pilot', json=payload, timeout=2)
except:
print('Post failed')
return
if not response.ok:
continue
set_synced_pilot(pilot[0], True)
connection.close()
return
def get(self):
'''all active sessions'''
connection = get_connection("database_server.db")
cursor = connection.cursor()
p_id = is_pilot(cursor)
if p_id == -1:
return {}, 401
return_dict = {'sessions': []}
select_stmt = cursor.execute(
'SELECT P.PilotID, P.Vorname, P.Nachname, time(F.Startzeit), F.Ist_Flugleiter '
'FROM Flugsession F '
'JOIN Pilot P on P.PilotID = F.PilotID '
'WHERE F.Endzeit IS NULL')
for row in select_stmt:
session = {
'pilot_id': row[0],
'pilot_name': row[1] + " " + row[2],
'start_time': row[3],
'session_leader': row[4]
}
return_dict['sessions'].append(session)
connection.close()
return return_dict
def put(self):
'''add guest name and info to session'''
connection = get_connection("database_server.db")
cursor = connection.cursor()
payload = api.payload
args = put_parser.parse_args()
p_id = cursor.execute(
'SELECT PilotID FROM Flugsession WHERE SessionID = ?',
[args['id']]).fetchone()[0]
# wenn der pilot kein admin ist und die p_id nicht übereinstimmt, return 401
if p_id != is_pilot(cursor) and not is_admin(cursor):
connection.close()
return {}, 401
# wenn die endzeit mitgeschickt wurde, darf diese nur geändert werden, wenn die vorherige NULL war.
if 'end_time' in payload.keys():
end_time = cursor.execute(
'SELECT Endzeit FROM Flugsession WHERE SessionID = ?',
[args['id']]).fetchone()[0]
if end_time is None:
cursor.execute(
'UPDATE Flugsession SET Endzeit = ? WHERE SessionID = ?',
[payload['end_time'], args['id']])
else:
connection.close()
return {}, 401
if 'guest_name' in payload.keys():
if 'guest_info' not in payload.keys():
guest_info = None
else:
guest_info = payload['guest_info']
cursor.execute('INSERT INTO Gast(Gastname, Freitext) VALUES (?,?)',
[payload['guest_name'], guest_info])
guest_row_nr = cursor.lastrowid
guest_id = cursor.execute(
'SELECT GastID FROM Gast WHERE ROWID = ?',
[guest_row_nr]).fetchone()[0]
cursor.execute(
'UPDATE Flugsession SET GastID = ? WHERE SessionID = ?',
[guest_id, args['id']])
if 'is_leader' in payload.keys():
cursor.execute(
'UPDATE Flugsession SET Ist_Flugleiter = ? WHERE SessionID = ?',
[payload['is_leader'], args['id']])
connection.commit()
connection.close()
return {}
def set_synced_rfid(rfid_code, is_synced):
connection = get_connection("database_server.db")
cursor = connection.cursor()
cursor.execute(
'UPDATE RFID_Ausweis SET Synced = ? WHERE RFID_Code = ?', [is_synced, rfid_code]
)
connection.commit()
connection.close()
return
def set_synced_pilot(pilot_id, is_synced):
connection = get_connection("database_server.db")
cursor = connection.cursor()
cursor.execute(
'UPDATE Pilot SET Synced = ? WHERE PilotID = ?', [is_synced, pilot_id]
)
connection.commit()
connection.close()
return
def post(self):
'''log off all pilots'''
connection = get_connection("database_server.db")
cursor = connection.cursor()
cursor.execute(
'UPDATE Flugsession SET Endzeit = datetime() WHERE Endzeit is NULL'
)
connection.commit()
connection.close()
return {}
def get(self):
'''get settings'''
connection = get_connection("database_server.db")
cursor = connection.cursor()
if not is_admin(cursor):
connection.close()
return {}, 401
settings = {}
with open('settings.json') as settings_file:
settings['settings'] = json.load(settings_file)
return settings
def post(self):
'''Post finished and unfinished sessions from terminal to backend'''
connection = get_connection("database_server.db")
cursor = connection.cursor()
payload = api.payload
start_time = datetime.combine(
date.fromisoformat(payload['session_date']),
datetime.strptime(payload['start_time'], "%H:%M").time())
# erst wird geschaut, ob der datensatz bereits existiert (ret > 0)
ret = cursor.execute(
'SELECT PilotID FROM Flugsession '
'WHERE PilotID = ? AND Startzeit = ?',
[payload['pilot_id'], start_time]).fetchall()
# case: neue session
if 'end_time' not in payload.keys():
cursor.execute(
'INSERT INTO Flugsession(PilotID, Startzeit, Ist_Flugleiter) VALUES (?,?,?)',
[payload['pilot_id'], start_time, payload['is_leader']])
# vorhandene session updaten
# case: session ohne endzeit vorhanden
# ret ist None, wenn es keine Sessions (mit PilotID, und Startzeit) gibt
elif ret:
end_time = datetime.combine(
date.fromisoformat(payload['session_date']),
datetime.strptime(payload['end_time'], "%H:%M").time())
cursor.execute(
'UPDATE Flugsession SET Endzeit = ?, Ist_Flugleiter = ? WHERE PilotID = ? AND Startzeit = ?',
[
end_time, payload['is_leader'], payload['pilot_id'],
start_time
])
# case: session wird jetzt zum ersten mal synchronisiert (mit endzeit)
else:
end_time = datetime.combine(
date.fromisoformat(payload['session_date']),
datetime.strptime(payload['end_time'], "%H:%M").time())
cursor.execute(
'INSERT INTO Flugsession(PilotID, Startzeit, Endzeit, Ist_Flugleiter) VALUES (?,?,?,?)',
[
payload['pilot_id'], start_time, end_time,
payload['is_leader']
])
connection.commit()
connection.close()
return {}
def sync_settings():
if not is_online():
print("Terminal offline")
return
connection = get_connection("database_server.db")
cursor = connection.cursor()
with open('settings.json') as settings_file:
settings = json.load(settings_file)
try:
response = requests.post(url=RASPI_URL + '/settings', json=settings, timeout=2)
except:
print('Post failed')
return
def put(self):
'''update pilot'''
connection = get_connection("database_server.db")
cursor = connection.cursor()
if not is_admin(cursor):
return {}, 401
payload = api.payload
p_id = payload['pilot_id']
if 'pilot_name' in payload.keys():
new_first_name = payload['pilot_name']
cursor.execute('UPDATE Pilot SET Vorname = ? WHERE PilotID = ?', [new_first_name, p_id])
if 'pilot_surname' in payload.keys():
new_last_name = payload['pilot_surname']
cursor.execute('UPDATE Pilot SET Nachname = ? WHERE PilotID = ?', [new_last_name, p_id])
if 'rfid' in payload.keys():
if not payload['rfid']:
new_rfid = None
else:
new_rfid = int(payload['rfid'], 16)
cursor.execute('UPDATE Pilot SET RFID_Code = ?, Synced = FALSE WHERE PilotID = ?', [new_rfid, p_id])
if 'pilot_username' in payload.keys():
new_user_name = payload['pilot_username']
cursor.execute('UPDATE Pilot SET Nutzername = ? WHERE PilotID = ?', [new_user_name, p_id])
if 'is_admin' in payload.keys():
new_admin = payload['is_admin']
cursor.execute('UPDATE Pilot SET Ist_Admin = ? WHERE PilotID = ?', [new_admin, p_id])
if 'reset_password' in payload.keys():
new_password = payload['reset_password']
if new_password:
cursor.execute('UPDATE Pilot SET Passwort = NULL WHERE PilotID = ?', [p_id])
connection.commit()
connection.close()
# piloten mit terminal synchronisieren
sync_pilots()
return {}
def post(self):
'''add session'''
connection = get_connection("database_server.db")
cursor = connection.cursor()
if not is_admin(cursor):
return {}, 401
payload = api.payload
start_time = datetime.combine(
date.fromisoformat(payload['session_date']),
datetime.strptime(payload['start_time'], "%H:%M").time())
try:
end_time = datetime.combine(
date.fromisoformat(payload['session_date']),
datetime.strptime(payload['end_time'], "%H:%M").time())
except KeyError:
end_time = None
try:
guest_name = payload['guest_name']
guest_info = payload['guest_info']
# gast einfügen
cursor.execute('INSERT INTO Gast(Gastname, Freitext) VALUES (?,?)',
[guest_name, guest_info])
guest_row_nr = cursor.lastrowid
guest_id = cursor.execute(
'SELECT GastID FROM Gast WHERE ROWID = ?',
[guest_row_nr]).fetchone()[0]
except KeyError:
guest_id = None
# session einfügen
cursor.execute(
'INSERT INTO Flugsession(PilotID, GastID, Startzeit, Endzeit, Ist_Flugleiter) VALUES (?,?,?,?,?)',
[
payload['pilot_id'], guest_id, start_time, end_time,
payload['is_leader']
])
connection.commit()
connection.close()
return {}
def post(self):
'''login pilot'''
connection = get_connection("database_server.db")
cursor = connection.cursor()
payload = api.payload
username = payload['username']
password = payload['password']
select_stmt = cursor.execute(
'SELECT Passwort FROM Pilot WHERE Nutzername LIKE ?', [username]
)
try:
# wenn der pilot kein passwort hat, return 403
pwd_db = select_stmt.fetchone()[0]
if pwd_db is None:
connection.close()
return {}, 403
# falls es keine ergebnisse gibt -> pilot existiert nicht, return 403
except TypeError:
connection.close()
return {}, 403
user_stmt = cursor.execute(
'SELECT PilotID, Vorname, Nachname, Nutzername, Ist_Admin, Token FROM Pilot WHERE Nutzername LIKE ?',
[username]
)
# wenn das passwort stimmt, return daten und token, sonst 403
if checkpw(password.encode('utf8'), pwd_db.encode('utf8')):
user = user_stmt.fetchone()
connection.close()
return {
'token': user[5],
'user': {
'id': user[0],
'name': user[1] + ' ' + user[2],
'username': user[3],
'is_admin': user[4]
}
}
else:
connection.close()
return {}, 403
def post(self):
'''post settings to server'''
connection = get_connection("database_server.db")
cursor = connection.cursor()
if not is_admin(cursor):
connection.close()
return {}, 401
file = open('settings.json', 'w')
file.write(json.dumps(api.payload))
file.close()
sync_settings()
# todo: prüfen, ob erfolgreich synchronisiert, ggf. neu synchronisieren
return {}
def post(self):
'''add RFID Tags to DB'''
connection = get_connection("database_server.db")
cursor = connection.cursor()
payload = api.payload
if not is_admin(cursor):
return {}, 401
cursor.execute(
'INSERT INTO RFID_Ausweis (RFID_Code, Synced) VALUES (?, FALSE)', [int(payload['rfid'], 16)]
)
connection.commit()
connection.close()
sync_rfids()
return {}
def get(self):
'''get id, and name of every pilot'''
connection = get_connection("database_server.db")
cursor = connection.cursor()
if not is_admin(cursor):
return {}, 401
return_dict = {
'pilots': []
}
for row in cursor.execute('SELECT PilotID, Vorname, Nachname, Nutzername FROM Pilot'):
pilot = {
'pilot_id': row[0],
'pilot_name': row[1] + " " + row[2],
'pilot_username': row[3]
}
return_dict['pilots'].append(pilot)
connection.close()
return return_dict
def get(self):
'''assigned RFID tags'''
connection = get_connection("database_server.db")
cursor = connection.cursor()
if not is_admin(cursor):
return {}, 401
return_dict = {'rfid_list': []}
for row in cursor.execute(
'SELECT PilotID, RFID_Code, Vorname, Nachname '
'FROM Pilot WHERE RFID_CODE IS NOT NULL'):
list_item = {
'pilot_id': row[0],
'pilot_name': row[2] + ' ' + row[3],
'rfid': hex(row[1])
}
return_dict['rfid_list'].append(list_item)
connection.close()
return return_dict
def post(self):
'''create pilot'''
connection = get_connection("database_server.db")
cursor = connection.cursor()
if not is_admin(cursor):
return {}, 401
payload = api.payload
first_name = payload['pilot_name']
last_name = payload['pilot_surname']
rfid = int(payload['rfid'], 16)
username = payload['pilot_username']
admin = bool(payload['is_admin'])
# erzeuge token
token = abs(hash(username))
select_stmt = cursor.execute(
'SELECT Token FROM Pilot'
).fetchall()
all_tokens = [t[0] for t in select_stmt]
# wenn token vergeben, inkrementiere und prüfe erneut
while token in all_tokens:
token += 1
cursor.execute(
'INSERT INTO Pilot(RFID_Code, Nachname, Vorname, Eintrittsdatum, Nutzername, Passwort, Ist_Admin, Token, Synced) '
'VALUES(?, ?, ?, date(), ?, NULL, ?, ?, FALSE)', [rfid, last_name, first_name, username, admin, str(token)]
)
connection.commit()
connection.close()
# piloten mit terminal synchronisieren
sync_pilots()
return {}
def get(self):
'''get Session info'''
select_stmt = None
connection = get_connection("database_server.db")
cursor = connection.cursor()
p_id = is_pilot(cursor)
if p_id == -1:
return {}, 404
args = get_parser.parse_args()
from_ = args['from']
to = args['to']
# entferne alle argumente mit value 'None' (NULL)
for k, v in list(args.items()):
if v is None:
args.pop(k)
# GET sessions?name&start_date&end_date
if 'name' in args.keys() and 'start_date' in args.keys(
) and 'end_date' in args.keys():
name_list = args['name'].split()
# z.B. 'Mustermann' -> in Vor- und Nachname suchen
if len(name_list) == 1:
select_stmt = cursor.execute(
'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), '
'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext '
'FROM Flugsession F '
'JOIN Pilot P on P.PilotID = F.PilotID '
'LEFT JOIN Gast G on G.GastID = F.GastID '
'WHERE (instr(lower(P.Vorname), lower(?)) > 0 OR instr(lower(P.Nachname), lower(?)) > 0)'
'AND date(F.Startzeit) BETWEEN ? AND ?', [
name_list[0], name_list[0], args['start_date'],
args['end_date']
])
# z.B. 'Max Mustermann' -> vor(Max), nach(Mustermann) oder vor(Mustermann) nach(Max)
if len(name_list) == 2:
select_stmt = cursor.execute(
'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), '
'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext '
'FROM Flugsession F '
'JOIN Pilot P on P.PilotID = F.PilotID '
'LEFT JOIN Gast G on G.GastID = F.GastID '
'WHERE ('
'(instr(lower(P.Vorname), lower(?)) > 0 AND instr(lower(P.Nachname), lower(?)) > 0) '
'OR '
'(instr(lower(P.Vorname), lower(?)) > 0 AND instr(lower(P.Nachname), lower(?)) > 0)'
')'
'AND date(F.Startzeit) BETWEEN ? AND ?', [
name_list[0], name_list[1], name_list[1], name_list[0],
args['start_date'], args['end_date']
])
# GET sessions?name&start_date
elif 'name' in args.keys() and 'start_date' in args.keys():
name_list = args['name'].split()
if len(name_list) == 1:
select_stmt = cursor.execute(
'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), '
'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext '
'FROM Flugsession F '
'JOIN Pilot P on P.PilotID = F.PilotID '
'LEFT JOIN Gast G on G.GastID = F.GastID '
'WHERE (instr(lower(P.Vorname), lower(?)) > 0 OR instr(lower(P.Nachname), lower(?)) > 0)'
'AND date(F.Startzeit) > ?',
[name_list[0], name_list[0], args['start_date']])
if len(name_list) == 2:
select_stmt = cursor.execute(
'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), '
'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext '
'FROM Flugsession F '
'JOIN Pilot P on P.PilotID = F.PilotID '
'LEFT JOIN Gast G on G.GastID = F.GastID '
'WHERE ('
'(instr(lower(P.Vorname), lower(?)) > 0 AND instr(lower(P.Nachname), lower(?)) > 0) '
'OR '
'(instr(lower(P.Vorname), lower(?)) > 0 AND instr(lower(P.Nachname), lower(?)) > 0)'
')'
'AND date(F.Startzeit) BETWEEN ? AND ?'
'AND date(F.Startzeit) > ?', [
name_list[0], name_list[1], name_list[1], name_list[0],
args['start_date']
])
elif 'name' in args.keys() and 'end_date' in args.keys():
name_list = args['name'].split()
if len(name_list) == 1:
select_stmt = cursor.execute(
'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), '
'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext '
'FROM Flugsession F '
'JOIN Pilot P on P.PilotID = F.PilotID '
'LEFT JOIN Gast G on G.GastID = F.GastID '
'WHERE (instr(lower(P.Vorname), lower(?)) > 0 OR instr(lower(P.Nachname), lower(?)) > 0)'
'AND date(F.Startzeit) < ?',
[name_list[0], name_list[0], args['end_date']])
if len(name_list) == 2:
select_stmt = cursor.execute(
'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), '
'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext '
'FROM Flugsession F '
'JOIN Pilot P on P.PilotID = F.PilotID '
'LEFT JOIN Gast G on G.GastID = F.GastID '
'WHERE ('
'(instr(lower(P.Vorname), lower(?)) > 0 AND instr(lower(P.Nachname), lower(?)) > 0) '
'OR '
'(instr(lower(P.Vorname), lower(?)) > 0 AND instr(lower(P.Nachname), lower(?)) > 0)'
')'
'AND date(F.Startzeit) BETWEEN ? AND ?'
'AND date(F.Startzeit) < ?', [
name_list[0], name_list[1], name_list[1], name_list[0],
args['end_date']
])
# GET sessions?start_date&end_date
elif 'start_date' in args.keys() and 'end_date' in args.keys():
select_stmt = cursor.execute(
'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), '
'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext '
'FROM Flugsession F '
'JOIN Pilot P on P.PilotID = F.PilotID '
'LEFT JOIN Gast G on G.GastID = F.GastID '
'WHERE date(F.Startzeit) BETWEEN ? AND ?',
[args['start_date'], args['end_date']])
# /sessions?name
elif 'name' in args.keys():
name_list = args['name'].split()
if len(name_list) == 1:
select_stmt = cursor.execute(
'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), '
'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext '
'FROM Flugsession F '
'JOIN Pilot P on P.PilotID = F.PilotID '
'LEFT JOIN Gast G on G.GastID = F.GastID '
'WHERE (instr(lower(P.Vorname), lower(?)) > 0 OR instr(lower(P.Nachname), lower(?)) > 0)',
[name_list[0], name_list[0]])
if len(name_list) == 2:
select_stmt = cursor.execute(
'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), '
'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext '
'FROM Flugsession F '
'JOIN Pilot P on P.PilotID = F.PilotID '
'LEFT JOIN Gast G on G.GastID = F.GastID '
'WHERE ('
'(instr(lower(P.Vorname), lower(?)) > 0 AND instr(lower(P.Nachname), lower(?)) > 0) '
'OR '
'(instr(lower(P.Vorname), lower(?)) > 0 AND instr(lower(P.Nachname), lower(?)) > 0)'
')'
'AND date(F.Startzeit) BETWEEN ? AND ?',
[name_list[0], name_list[1], name_list[1], name_list[0]])
elif 'start_date' in args.keys():
select_stmt = cursor.execute(
'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), '
'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext '
'FROM Flugsession F '
'JOIN Pilot P on P.PilotID = F.PilotID '
'LEFT JOIN Gast G on G.GastID = F.GastID '
'WHERE date(F.Startzeit) > ?', [args['start_date']])
elif 'end_date' in args.keys():
select_stmt = cursor.execute(
'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), '
'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext '
'FROM Flugsession F '
'JOIN Pilot P on P.PilotID = F.PilotID '
'LEFT JOIN Gast G on G.GastID = F.GastID '
'WHERE date(F.Startzeit) < ?', [args['end_date']])
else:
select_stmt = cursor.execute(
'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), '
'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext '
'FROM Flugsession F '
'JOIN Pilot P on P.PilotID = F.PilotID '
'LEFT JOIN Gast G on G.GastID = F.GastID ')
return_dict = {'sessions': [], 'session_count': 0}
sessions_count = 0
# alle ergebnisse von 'from' bis 'to'
for row in select_stmt.fetchall():
sessions_count += 1
if from_ <= sessions_count <= to:
session = {
'session_id': row[0],
'pilot_id': row[1],
'pilot_name': row[2] + " " + row[3],
'date': row[4],
'start_time': row[5],
'end_time': row[6],
'session_leader': row[7],
'guest': {
'name': row[8],
'text': row[9]
}
}
return_dict['sessions'].append(session)
return_dict['session_count'] = sessions_count
connection.close()
return return_dict