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='.')