def get_active_result(task_id: int) -> dict:
"""Reads competition from json result file
takes com_id as argument"""
from db.tables import TblResultFile as R
with db_session() as db:
file = db.query(R).filter_by(task_id=task_id, active=1).first()
return file.as_dict() if file else {}
def create_classifications(cat_id: int) -> dict:
"""create the output to generate classifications list"""
from db.tables import TblClasCertRank as CC, TblCompetition as C, TblRanking as R, TblCertification as CCT, \
TblClassification as CT
rank = dict()
with db_session() as db:
'''get rankings definitions'''
query = db.query(R.rank_name.label('rank'), CCT.cert_name.label('cert'), CT.female, CT.team) \
.select_from(R) \
.join(CC, R.rank_id == CC.c.rank_id) \
.join(CCT, (CCT.cert_id <= CC.c.cert_id) & (CCT.comp_class == R.comp_class)) \
.join(CT, CT.cat_id == CC.c.cat_id) \
.filter(CC.c.cert_id > 0, CC.c.cat_id == cat_id)
result = query.all()
if len(result) > 0:
for res in result:
if res.rank in rank:
rank[res.rank].append(res.cert)
else:
rank[res.rank] = [res.cert]
rank['female'] = result.pop().female
rank['team'] = result.pop().team
else:
print(f'Ranking list is empty')
return rank
def is_ext(comp_id: int):
"""True if competition is external"""
from db.tables import TblCompetition as C
with db_session() as db:
# comps = db.query(C)
ext = db.query(C.external).filter_by(comp_id=comp_id).scalar()
return bool(ext)
def get_comp_path(comp_id: int):
""" returns comp folder name"""
from db.tables import TblCompetition as C
if type(comp_id) is int and comp_id > 0:
with db_session() as db:
return db.query(
C.comp_path).filter_by(comp_id=comp_id).limit(1).scalar()
def get_comps_with_igc_parsing(igc_config):
from db.tables import TblCompetition
c = aliased(TblCompetition)
with db_session() as db:
return db.query(
c.comp_id).filter(c.igc_config_file == igc_config).all()
def get_task_path(task_id: int):
""" returns task folder name"""
from db.tables import TblTask as T
if type(task_id) is int and task_id > 0:
with db_session() as db:
return db.query(
T.task_path).filter_by(task_id=task_id).limit(1).scalar()
def get_comp_scorekeeper(compid_or_taskid: int, task_id=False):
"""returns owner and list of scorekeepers takes compid by default or taskid if taskid is True"""
from db.tables import TblCompAuth as CA
from airscore.user.models import User
with db_session() as db:
if task_id:
taskid = compid_or_taskid
all_scorekeepers = db.query(User.id, User.username, User.first_name, User.last_name, CA.user_auth) \
.join(CA, User.id == CA.user_id).join(TblTask, CA.comp_id == TblTask.comp_id).filter(
TblTask.task_id == taskid,
CA.user_auth.in_(('owner', 'admin'))).all()
else:
compid = compid_or_taskid
all_scorekeepers = db.query(User.id, User.username, User.first_name, User.last_name, CA.user_auth) \
.join(CA, User.id == CA.user_id).filter(CA.comp_id == compid,
CA.user_auth.in_(('owner', 'admin'))).all()
if all_scorekeepers:
all_scorekeepers = [row._asdict() for row in all_scorekeepers]
scorekeepers = []
all_ids = []
owner = None
for admin in all_scorekeepers:
all_ids.append(admin['id'])
if admin['user_auth'] == 'owner':
del admin['user_auth']
owner = admin
else:
del admin['user_auth']
scorekeepers.append(admin)
return owner, scorekeepers, all_ids
def read_db(cls, region_id):
"""
reads region waypoints
"""
turnpoints = []
with db_session() as db:
q = db.query(R.description.label('name'),
R.waypoint_file.label('filename'),
R.openair_file.label('openair')).filter(
R.reg_id == region_id).first()
name, filename, openair = q.name, q.filename, q.openair
w = db.query(RWV).filter(RWV.region_id == region_id).all()
for tp in w:
if tp.name[0] == 'D':
type = 'launch' # brown
elif tp.name[0] == 'A':
type = 'speed' # green
elif tp.name[0] == 'X':
type = 'restricted' # red
else:
type = 'waypoint' # blue
turnpoint = Turnpoint(radius=400,
type=type,
shape='circle',
how='entry')
tp.populate(turnpoint)
turnpoints.append(turnpoint)
return cls(region_id, name, filename, openair, turnpoints)
def get_download_status(task_id: int):
from db.tables import TblParticipant as P, TblTaskResult as R, TblTask as T
from calcUtils import sec_to_time
valid = []
missing = []
with db_session() as db:
results = db.query(P.ID, P.name, R.result_type, R.distance_flown, R.SSS_time, R.ESS_time, R.goal_time) \
.join(T, P.comp_id == T.comp_id) \
.outerjoin(R, (R.par_id == P.par_id) & (R.task_id == T.task_id)) \
.filter(T.task_id == task_id).all()
pilots = len(results)
valid_results = [
p for p in results
if p.result_type not in ('abs', 'dnf', 'mindist', None)
]
for pilot in results:
data = {'ID': pilot.ID, 'name': pilot.name}
if pilot in valid_results:
if pilot.ESS_time:
time = sec_to_time(pilot.ESS_time - pilot.SSS_time)
if pilot.result_type == 'goal':
result = f'GOAL {time}'
else:
result = f"ESS {round(pilot.distance_flown / 1000, 2)} Km (~{time}~)"
else:
result = f"LO {round(pilot.distance_flown / 1000, 2)} Km"
data['result'] = result
valid.append(data)
elif not pilot.result_type:
missing.append(data)
return pilots, valid, missing
def update_airspace_file(old_filename, new_filename):
"""change the name of the openair file in all regions it is used."""
R = aliased(TblRegion)
with db_session() as db:
db.query(R).filter(R.openair_file == old_filename).update(
{R.openair_file: new_filename}, synchronize_session=False)
db.commit()
return True
def set_comp_scorekeeper(compid: int, userid, owner=False):
from db.tables import TblCompAuth as CA
auth = 'owner' if owner else 'admin'
with db_session() as db:
admin = CA(user_id=userid, comp_id=compid, user_auth=auth)
db.add(admin)
db.flush()
return True
def number_of_tracks_processed(taskid: int):
from db.tables import TblTaskResult as R, TblParticipant as P, TblTask as T
from sqlalchemy import func
with db_session() as db:
results = db.query(func.count()).filter(R.task_id == taskid).scalar()
pilots = db.query(func.count(P.par_id)).outerjoin(
T, P.comp_id == T.comp_id).filter(T.task_id == taskid).scalar()
return results, pilots
def unregister_all_external_participants(comp_id: int):
""" takes comp_id and unregisters all participants from comp without a pil_id."""
from sqlalchemy import and_
with db_session() as db:
results = db.query(P).filter(
and_(P.comp_id == comp_id, P.pil_id.is_(None)))
results.delete(synchronize_session=False)
return True
def unregister_participant(comp_id: int, par_id: int):
""" takes comp_id and a par_id
unregisters participant from comp.
in reality we don't need compid but it is a safeguard"""
with db_session() as db:
db.query(P).filter_by(comp_id=comp_id,
par_id=par_id).delete(synchronize_session=False)
return True
def check_short_code(comp_short_code):
with db_session() as db:
code = db.query(TblCompetition.comp_code).filter(
TblCompetition.comp_code == comp_short_code).first()
if code:
return False
else:
return True
def get_nat_code(iso: str):
"""Get Country Code from ISO2 or ISO3"""
from db.tables import TblCountryCode as CC
if not (type(iso) is str and len(iso) in (2, 3)):
return None
column = getattr(CC, 'natIso' + str(len(iso)))
with db_session() as db:
return db.query(CC.natId).filter(column == iso).limit(1).scalar()
def get_area_wps(region_id: int):
"""query db get all wpts names and pks for region and put into dictionary"""
from db.tables import TblRegionWaypoint as W
with db_session() as db:
wps = db.query(W.name,
W.rwp_id).filter_by(reg_id=region_id,
old=0).order_by(W.name).all()
return dict(wps)
def get_all_regions():
with db_session() as db:
results = db.query(R.reg_id,
R.description.label('name'),
R.waypoint_file.label('filename'),
R.openair_file.label('openair')).order_by(R.description).all()
if results:
results = [row._asdict() for row in results]
return {'regions': results}
def get_task_filepath(task_id: int):
""" returns complete trackfile path"""
from db.conn import db_session
from db.tables import TaskObjectView as T
from Defines import TRACKDIR
from pathlib import Path
with db_session() as db:
task = db.query(T).filter_by(task_id=task_id).one()
return Path(TRACKDIR, task.comp_path, task.task_path)
def get_task_result_file_list(taskid: int):
from db.tables import TblResultFile as R
files = []
with db_session() as db:
rows = db.query(R.created, R.filename, R.status, R.active,
R.ref_id).filter_by(task_id=taskid).all()
if rows:
files = [row._asdict() for row in rows]
return files
def mass_unregister(pilots):
""" gets par_id list
unregisters pilots from comp"""
if not pilots:
print(f"error: pilots list is empty")
return None
with db_session() as db:
db.query(P).filter(P.par_id.in_(pilots)).delete(synchronize_session=False)
return True
def get_pil_track(par_id: int, task_id: int):
"""Get pilot result in a given task"""
from db.tables import TblTaskResult as R
with db_session() as db:
track_id = db.query(R.track_id).filter(
and_(R.par_id == par_id, R.task_id == task_id)).scalar()
if track_id == 0:
"""No result found"""
print(f"Pilot with ID {par_id} has not been scored yet on task ID {task_id} \n")
return track_id
def update_waypoints(self):
insert_mappings = []
for idx, tp in enumerate(self.turnpoints):
wpt = dict(tp.as_dict(), reg_id=self.reg_id)
insert_mappings.append(wpt)
with db_session() as db:
db.bulk_insert_mappings(RW, insert_mappings, return_defaults=True)
for elem in insert_mappings:
next(tp for tp in self.turnpoints if tp.num == elem['num']).wpt_id = elem['wpt_id']
def get_comp_json_filename(comp_id: int, latest=False):
"""returns active json results filename, or latest if latest is True"""
from db.tables import TblResultFile as R
with db_session() as db:
results = db.query(R.filename).filter_by(comp_id=comp_id, task_id=None)
if latest:
filename = results.order_by(R.ref_id.desc()).limit(1).scalar()
else:
filename = results.filter_by(active=1).scalar()
return filename
def get_tasks_result_files(comp_id: int):
""" returns a list of (task_id, active result filename) for tasks in comp"""
from db.tables import TblResultFile as R
files = []
with db_session() as db:
'''getting active json files list'''
results = db.query(R.task_id, R.filename.label('file'))
files = results.filter(R.task_id.isnot(None)).filter_by(
comp_id=comp_id, active=1).order_by(R.task_id).all()
return files
def get_all_scorekeepers():
"""returns a list of all scorekeepers in the system"""
from airscore.user.models import User
with db_session() as db:
all_scorekeepers = db.query(User.id, User.username, User.first_name, User.last_name) \
.filter((User.access == 'scorekeeper') | (User.access == 'admin'))\
.all()
if all_scorekeepers:
all_scorekeepers = [row._asdict() for row in all_scorekeepers]
return all_scorekeepers
def get_all_users():
"""returns a list of all scorekeepers in the system"""
from airscore.user.models import User
with db_session() as db:
all_users = db.query(User.id, User.username, User.first_name,
User.last_name, User.access, User.email,
User.active).all()
if all_users:
all_users = [row._asdict() for row in all_users]
return all_users
def get_region_wpts(reg_id):
with db_session() as db:
wpt_results = db.query(RW.reg_id, RW.rwp_id, RW.name, RW.lat, RW.lon,
RW.altitude, RW.xccSiteID, RW.xccToID,
RW.description).join(R).filter_by(
reg_id=reg_id).order_by(RW.name).all()
if wpt_results:
wpt_results = [row._asdict() for row in wpt_results]
return wpt_results
def get_other_tracks(taskid, pilot_parid):
from db.tables import FlightResultView as R
from db.conn import db_session
with db_session() as db:
tracks = (db.query(R.track_id,
R.name).filter(R.task_id == taskid,
R.par_id != pilot_parid).all())
return tracks
def to_db(self):
"""stores TaskFormula parameters to TblTask table in AirScore database"""
from db.tables import TblTask
with db_session() as db:
'''check if we have already a row for the task'''
row = db.query(TblTask).get(self.task_id)
for k in TaskFormula.task_overrides:
setattr(row, k, getattr(self, k))
db.flush()
return True