def __get_result(model, flight_field, **kw): subq = ( DBSession.query( getattr(Flight, flight_field), func.count("*").label("count"), func.sum(Flight.index_score).label("total"), ) .group_by(getattr(Flight, flight_field)) .outerjoin(Flight.model) ) if "year" in kw: try: year = int(kw["year"]) except: raise HTTPBadRequest year_start = date(year, 1, 1) year_end = date(year, 12, 31) subq = subq.filter(Flight.date_local >= year_start).filter(Flight.date_local <= year_end) subq = subq.subquery() result = DBSession.query( model, subq.c.count, subq.c.total, over(func.rank(), order_by=desc("total")).label("rank") ).join((subq, getattr(subq.c, flight_field) == model.id)) result = result.order_by(desc("total")) return result
def analysis(self, **kwargs): """Hidden method that restarts flight analysis.""" analyse_flight(self.flight) DBSession.flush() return redirect('.')
def add_airspace(country_code, airspace_class, name, base, top, geom_str): # this is a real kludge to determine if the polygon has more than 3 points... if (geom_str.count(',') < 3): print name + "(" + airspace_class + ") has not enough points to be a polygon" return False if not airspace_class: print name + " has no airspace class" return False base = normalise_height(base, name) top = normalise_height(top, name) flightlevel_re = re.compile(r'^FL (\d+)$') match = flightlevel_re.match(base) if match and int(match.group(1)) >= 200: print name + " has it's base above FL 200 and is therefore disregarded" return False airspace = Airspace() airspace.country_code = country_code airspace.airspace_class = airspace_class airspace.name = name airspace.base = base airspace.top = top airspace.the_geom = WKTElement(geom_str, srid=4326) DBSession.add(airspace) return True
def remove_country(country_code): print "removing all entries for country_code " + country_code query = DBSession.query(Airspace) \ .filter(Airspace.country_code == country_code) query.delete(synchronize_session=False) DBSession.flush() transaction.commit()
def save(self, email_address, display_name, club, tracking_delay=0, unit_preset=1, distance_unit=1, speed_unit=1, lift_unit=0, altitude_unit=0, eye_candy=False, **kwargs): if not self.user.is_writable(request.identity): raise HTTPForbidden self.user.email_address = email_address self.user.display_name = display_name if not club: club = None self.user.club_id = club self.user.tracking_delay = tracking_delay unit_preset = int(unit_preset) if unit_preset == 0: self.user.distance_unit = distance_unit self.user.speed_unit = speed_unit self.user.lift_unit = lift_unit self.user.altitude_unit = altitude_unit else: self.user.unit_preset = unit_preset self.user.eye_candy = eye_candy DBSession.flush() redirect('.')
def guess_model(self): from skylines.model.flight import Flight from skylines.model.model import Model # first try to find the reg number in the database if self.registration is not None: glider_reg = self.registration result = DBSession.query(Flight) \ .filter(func.upper(Flight.registration) == func.upper(glider_reg)) \ .order_by(desc(Flight.id)) \ .first() if result and result.model_id: return result.model_id # try to find another flight with the same logger and use it's aircraft type if self.logger_id is not None \ and self.logger_manufacturer_id is not None: logger_id = self.logger_id logger_manufacturer_id = self.logger_manufacturer_id result = DBSession.query(Flight).outerjoin(IGCFile) \ .filter(func.upper(IGCFile.logger_manufacturer_id) == func.upper(logger_manufacturer_id)) \ .filter(func.upper(IGCFile.logger_id) == func.upper(logger_id)) \ .filter(Flight.model_id != None) \ .order_by(desc(Flight.id)) if self.logger_manufacturer_id.startswith('X'): result = result.filter(Flight.pilot == self.owner) result = result.first() if result and result.model_id: return result.model_id if self.model is not None: glider_type = self.model.lower() # otherwise, try to guess the glider model by the glider type igc header text_fragments = ['%{}%'.format(v) for v in re.sub(r'[^a-z]', ' ', glider_type).split()] digit_fragments = ['%{}%'.format(v) for v in re.sub(r'[^0-9]', ' ', glider_type).split()] if not text_fragments and not digit_fragments: return None glider_type_clean = re.sub(r'[^a-z0-9]', '', glider_type) result = DBSession.query(Model) \ .filter(and_( \ func.regexp_replace(func.lower(Model.name), '[^a-z]', ' ').like(func.any(text_fragments)), \ func.regexp_replace(func.lower(Model.name), '[^0-9]', ' ').like(func.all(digit_fragments)))) \ .order_by(func.levenshtein(func.regexp_replace(func.lower(Model.name), '[^a-z0-9]', ''), glider_type_clean)) if result.first(): return result.first().id # nothing found return None
def analysis(self): """Hidden method that restarts flight analysis.""" for flight in DBSession.query(Flight): analyse_flight(flight) DBSession.flush() return redirect('/flights/')
def save(self, name, website, **kwargs): if not self.club.is_writable(): raise HTTPForbidden self.club.name = name self.club.website = website DBSession.flush() redirect('.')
def import_openair(filename, country_code): print "reading " + filename country_blacklist = blacklist.get(country_code, []) airspace_file = ogr.Open(filename) if not airspace_file: return layer = airspace_file.GetLayerByIndex(0) feature = layer.GetFeature(0) i = 0 j = 0 while(feature): feature = layer.GetFeature(i) i += 1 if not feature: continue geom_str = "POLYGON" + str(feature.geometry())[8:] name = unicode(feature.GetFieldAsString('name'), 'latin1') airspace_class = feature.GetFieldAsString('class') # this is a real kludge to determine if the polygon has more than 3 points... if (geom_str.count(',') < 3): print name + "(" + airspace_class + ") has not enough points to be a polygon" continue if not airspace_class.strip(): print name + " has no airspace class" continue if name in country_blacklist: print name + " is in blacklist" continue airspace = Airspace() airspace.country_code = country_code airspace.airspace_class = airspace_class airspace.name = name airspace.base = feature.GetFieldAsString('floor') airspace.top = feature.GetFieldAsString('ceiling') airspace.the_geom = WKTSpatialElement(geom_str) if i%100 == 0: print "inserting geometry " + str(i) j += 1 DBSession.add(airspace) airspace_file.Destroy() DBSession.flush() transaction.commit() print "added " + str(j) + " features for country " + country_code
def clubs(self): subq = DBSession.query(Flight.club_id, func.count('*').label('count'), func.sum(Flight.olc_plus_score).label('total')) \ .group_by(Flight.club_id).subquery() result = DBSession.query(Club, subq.c.count, subq.c.total) \ .join((subq, subq.c.club_id == Club.id)) result = result.order_by(desc('total')) result = result.limit(20) return dict(tab='clubs', result=result)
def pilots(self): subq = DBSession.query(Flight.pilot_id, func.count('*').label('count'), func.sum(Flight.olc_plus_score).label('total')) \ .group_by(Flight.pilot_id).subquery() result = DBSession.query(User, subq.c.count, subq.c.total) \ .join((subq, subq.c.pilot_id == User.user_id)) result = result.order_by(desc('total')) result = result.limit(20) return dict(tab='pilots', result=result)
def airports(self): subq = DBSession.query(Flight.takeoff_airport_id, func.count('*').label('count'), func.sum(Flight.olc_plus_score).label('total')) \ .group_by(Flight.takeoff_airport_id).subquery() result = DBSession.query(Airport, subq.c.count, subq.c.total) \ .join((subq, subq.c.takeoff_airport_id == Airport.id)) result = result.order_by(desc('total')) result = result.limit(20) return dict(tab='airports', result=result)
def generate_keys(self): """Hidden method that generates missing tracking keys.""" for user in DBSession.query(User): if user.tracking_key is None: user.generate_tracking_key() DBSession.flush() return redirect('/users/')
def trafficRequestReceived(self, host, port, key, payload): if len(payload) != 8: return data = struct.unpack('!II', payload) pilot = User.by_tracking_key(key) if pilot is None: log.err("No such pilot: %d" % key) return flags = data[0] or_filters = [] if flags & TRAFFIC_FLAG_FOLLOWEES: subq = DBSession.query(Follower.destination_id) \ .filter(Follower.source_id == pilot.id) \ .subquery() or_filters.append(TrackingFix.pilot_id.in_(subq)) if flags & TRAFFIC_FLAG_CLUB: subq = DBSession.query(User.id) \ .filter(User.club_id == pilot.club_id) \ .subquery() or_filters.append(TrackingFix.pilot_id.in_(subq)) if len(or_filters) == 0: return query = DBSession.query(TrackingFix) \ .distinct(TrackingFix.pilot_id) \ .filter(and_(TrackingFix.time >= datetime.datetime.utcnow() - datetime.timedelta(hours=2), TrackingFix.pilot_id != pilot.id, TrackingFix.location_wkt != None, TrackingFix.altitude != None, or_(*or_filters))) \ .order_by(TrackingFix.pilot_id, desc(TrackingFix.time)) \ .limit(32) response = '' count = 0 for fix in query: location = fix.location if location is None: continue t = fix.time t = t.hour * 3600000 + t.minute * 60000 + t.second * 1000 + t.microsecond / 1000 response += struct.pack('!IIiihHI', fix.pilot_id, t, int(location.latitude * 1000000), int(location.longitude * 1000000), int(fix.altitude), 0, 0) count += 1 response = struct.pack('!HBBI', 0, 0, count, 0) + response response = struct.pack('!IHHQ', MAGIC, 0, TYPE_TRAFFIC_RESPONSE, 0) + response response = set_crc(response) self.transport.write(response, (host, port))
def apply_and_commit(func, q): n_success, n_failed = 0, 0 for record in q: if func(record): n_success += 1 else: n_failed += 1 if n_success > 0: print "commit" DBSession.flush() transaction.commit() return n_success, n_failed
def create_pilot(self, email_address, display_name, **kw): if not self.club.is_writable(): raise HTTPForbidden pilot = User(display_name=display_name, email_address=email_address, club=self.club) DBSession.add(pilot) pilots = DBSession.query(Group).filter(Group.group_name == 'pilots').first() if pilots: pilots.users.append(pilot) redirect('pilots')
def select_pilot(self, pilot, co_pilot, **kwargs): if not self.flight.is_writable(request.identity): raise HTTPForbidden if self.flight.pilot_id != pilot: self.flight.pilot_id = pilot if pilot: self.flight.club_id = DBSession.query(User).get(pilot).club_id self.flight.co_pilot_id = co_pilot self.flight.time_modified = datetime.utcnow() DBSession.flush() redirect('.')
def delete(self, yes=False): if not self.flight.is_writable(): raise HTTPForbidden if yes: files.delete_file(self.flight.igc_file.filename) DBSession.delete(self.flight) DBSession.delete(self.flight.igc_file) redirect('/flights/') else: return dict(title='Delete Flight', question='Are you sure you want to delete this flight?', action='', cancel='.')
def igc_headers(self): """Hidden method that parses all missing IGC headers.""" igc_files = DBSession.query(IGCFile) igc_files = igc_files.filter(or_(IGCFile.logger_manufacturer_id == None, IGCFile.logger_id == None, IGCFile.model == None, IGCFile.registration == None)) for igc_file in igc_files: igc_file.update_igc_headers() DBSession.flush() return redirect('/flights/')
def delete(self, **kwargs): if not self.flight.is_writable(request.identity): raise HTTPForbidden if request.method == 'POST': files.delete_file(self.flight.igc_file.filename) DBSession.delete(self.flight) DBSession.delete(self.flight.igc_file) redirect('/flights/') else: return dict(title=_('Delete Flight'), question=_('Are you sure you want to delete this flight?'), action='delete', cancel='.')
def update(self, **kw): flight_id_list = kw.get('flight_id') model_list = kw.get('model') registration_list = kw.get('registration') if not isinstance(flight_id_list, list): flight_id_list = [flight_id_list] if not isinstance(model_list, list): model_list = [model_list] if not isinstance(registration_list, list): registration_list = [registration_list] if flight_id_list is None \ or len(flight_id_list) != len(model_list) \ or len(flight_id_list) != len(registration_list): flash(_('Sorry, some error happened when updating your flight(s). Please contact a administrator for help.'), 'warning') return redirect('/flights/today') for index, id in enumerate(flight_id_list): try: id = int(id) except ValueError: continue try: model_id = int(model_list[index]) except ValueError: model_id = None registration = registration_list[index] if registration is not None: registration = registration.strip() if not 0 < len(registration) < 32: registration = None flight = DBSession.query(Flight).get(id) if not flight.is_writable(): continue flight.model_id = model_id flight.registration = registration flight.time_modified = datetime.utcnow() DBSession.flush() flash(_('Your flight(s) have been successfully updated.')) return redirect('/flights/today')
def create_club(self, name, **kw): if not self.user.is_writable(request.identity): raise HTTPForbidden current_user = request.identity['user'] club = Club(name=name) club.owner_id = current_user.id DBSession.add(club) self.user.club = club DBSession.flush() redirect('.')
def select_aircraft(self, model, registration, **kwargs): if not self.flight.is_writable(): raise HTTPForbidden if registration is not None: registration = registration.strip() if len(registration) == 0: registration = None self.flight.model_id = model self.flight.registration = registration self.flight.time_modified = datetime.utcnow() DBSession.flush() redirect('.')
def new_post(self, display_name, club, email_address, password, **kw): if not club: club = None user = User(display_name=display_name, club_id=club, email_address=email_address, password=password) user.created_ip = request.remote_addr user.generate_tracking_key() DBSession.add(user) pilots = DBSession.query(Group).filter(Group.group_name == 'pilots').first() if pilots: pilots.users.append(user) redirect('/')
def get_flight_path2(pilot, last_update = None): query = DBSession.query(TrackingFix) query = query.filter(and_(TrackingFix.pilot == pilot, TrackingFix.location != None, TrackingFix.altitude != None, TrackingFix.time >= datetime.utcnow() - timedelta(hours=12))) if pilot.tracking_delay > 0 and not pilot.is_readable(): query = query.filter(TrackingFix.time <= datetime.utcnow() - timedelta(minutes=pilot.tracking_delay)) query = query.order_by(TrackingFix.time) start_fix = query.first() if not start_fix: return None start_time = start_fix.time.hour * 3600 + start_fix.time.minute * 60 + start_fix.time.second if last_update: query = query.filter(TrackingFix.time >= \ start_fix.time + timedelta(seconds=(last_update - start_time))) result = [] for fix in query: location = fix.location if location is None: continue time_delta = fix.time - start_fix.time time = start_time + time_delta.days * 86400 + time_delta.seconds result.append((time, location.latitude, location.longitude, fix.altitude, fix.engine_noise_level)) return result
def igc_headers(self, **kwargs): """Hidden method that parses all missing IGC headers.""" igc_files = DBSession.query(IGCFile) igc_files = igc_files.filter(or_(IGCFile.logger_manufacturer_id is None, IGCFile.logger_id is None, IGCFile.model is None, IGCFile.registration is None, IGCFile.competition_id is None, IGCFile.date_utc is None)) for igc_file in igc_files: igc_file.update_igc_headers() DBSession.flush() return redirect('.')
def _my_update_params(self, d, nullable=False): query = DBSession.query(User.id, User.display_name) \ .filter(User.club_id == request.identity['user'].club_id) \ .order_by(User.display_name) options = [(None, 'None')] + query.all() d['options'] = options return d
def update_params(self, d): models = DBSession.query(Model) \ .order_by(Model.name) options = [(None, '[unspecified]')] + \ [(model.id, model) for model in models] d['options'] = options return SingleSelectField.update_params(self, d)
def get_optimised_contest_trace(self, contest_type, trace_type): from skylines.model.trace import Trace query = DBSession.query(Trace) \ .filter(Trace.contest_type == contest_type) \ .filter(Trace.trace_type == trace_type) \ .filter(Trace.flight == self).first() return query
def get_last_year_statistics(self): query = DBSession.query(func.count('*').label('flights'), func.sum(Flight.olc_classic_distance).label('distance'), func.sum(Flight.duration).label('duration')) \ .filter(Flight.pilot == self.user) \ .filter(Flight.date_local > (date.today() - timedelta(days=365))) \ .first() last_year_statistics = dict(flights=0, distance=0, duration=timedelta(0), speed=0) if query and query.flights > 0: duration_seconds = query.duration.days * 24 * 3600 + query.duration.seconds if duration_seconds > 0: last_year_statistics['speed'] = float(query.distance) / duration_seconds last_year_statistics['flights'] = query.flights last_year_statistics['distance'] = query.distance last_year_statistics['duration'] = query.duration last_year_statistics['average_distance'] = query.distance / query.flights last_year_statistics['average_duration'] = query.duration / query.flights return last_year_statistics