Example #1
0
    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
Example #2
0
    def analysis(self, **kwargs):
        """Hidden method that restarts flight analysis."""

        analyse_flight(self.flight)
        DBSession.flush()

        return redirect('.')
Example #3
0
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
Example #4
0
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()
Example #5
0
    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('.')
Example #6
0
    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
Example #7
0
    def analysis(self):
        """Hidden method that restarts flight analysis."""

        for flight in DBSession.query(Flight):
            analyse_flight(flight)
            DBSession.flush()

        return redirect('/flights/')
Example #8
0
    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('.')
Example #9
0
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
Example #10
0
 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)
Example #11
0
 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)
Example #12
0
 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)
Example #13
0
    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/')
Example #14
0
    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))
Example #15
0
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
Example #16
0
    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')
Example #17
0
    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('.')
Example #18
0
    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='.')
Example #19
0
    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/')
Example #20
0
    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='.')
Example #21
0
    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')
Example #22
0
    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('.')
Example #23
0
    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('.')
Example #24
0
    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('/')
Example #25
0
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
Example #26
0
    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('.')
Example #27
0
 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
Example #28
0
 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)
Example #29
0
 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
Example #30
0
    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