def json_export(outfile):
    db = Session()
    data = dict()
    data["materials"] = [
        it.to_dict(["locations"])
        for it in db.query(Material).options(subqueryload(Material.locations))
    ]
    data["materialTypes"] = [
        dict(label=it[0], value=it[0])
        for it in db.query(distinct(Material.type)).order_by(Material.type.asc())
        if it[0]
    ]
    data["blueprints"] = [
        it.to_dict(["ingredients"])
        for it in db.query(Blueprint)\
                .options(subqueryload(Blueprint.ingredients))\
                .options(subqueryload("ingredients.material"))
    ]
    data["blueprintTypes"] = [
        dict(label=it[0], value=it[0])
        for it in db.query(distinct(Blueprint.type)).order_by(Blueprint.type.asc())
        if it[0]
    ]
    with open(outfile, "w") as fp:
        fp.write('CollectorDroneData=')
        json.dump(data, fp)

    db.close()

def archive(year=0, month=0):
    """
    Shows archived threads, meaning all threads sorted by year and month.
    If no year is passed, then a list of all the years for which we have archived topics is displayed.
    If a year is passed, a list of all the months for which there are archived topics is displayed.
    If a month is passed, we show all archived topics for that month.

    @todo Need to pass the timezone to the extract() function.

    :param year:
    :type year: int
    :param month:
    :type month: int
    """
    if year > 0 and month > 0:
        elements = Topic.query.filter(func.extract('YEAR', Topic.date_created) == year,
                                      func.extract('MONTH', Topic.date_created) == month).all()
    else:
        if year > 0 and month == 0:
            results = db.session.query(distinct(func.extract('MONTH', Topic.date_created))) \
                .filter(func.extract('YEAR', Topic.date_created) == year)
        if year == 0 and month == 0:
            results = db.session.query(distinct(func.extract('YEAR', Topic.date_created)))
        elements = []
        for result in results.all():
            elements.append(int(result[0]))
    return render_template('archive.html', elements=elements, year=year, month=month)

def home(request):

    session.rollback()
    session.commit()

    filter_date = datetime.datetime.utcnow() - datetime.timedelta(seconds=60)
    
    wemo_device_count = session.query(func.count(distinct(WemoTimeSeries.device_name))).first()[0]
    wemo = session.query(WemoTimeSeries).order_by(WemoTimeSeries.datetime.desc()).limit(wemo_device_count).all()

    hue_device_count = session.query(func.count(distinct(HueTimeSeries.device_name))).first()[0]
    hue = session.query(HueTimeSeries).order_by(HueTimeSeries.datetime.desc()).limit(hue_device_count).all()

    nest = session.query(NestTimeSeries).order_by(NestTimeSeries.datetime.desc()).limit(1).first()

    apex = session.query(ApexTimeSeries).filter(ApexTimeSeries.value != None).filter(ApexTimeSeries.datetime>filter_date).all()
   
    roomba_device_count = session.query(func.count(distinct(RoombaTimeSeries.device_name))).first()[0]
    roomba = session.query(RoombaTimeSeries).order_by(RoombaTimeSeries.datetime.desc()).limit(roomba_device_count).all()
    
    f = Flower()
    flower = f.get_data(.001)[-1]
    
    return render(request, template_name='home.html', dictionary={'wemo': wemo, 
                                                                  'hue': hue, 
                                                                  'nest': nest,
                                                                  'apex': apex,
                                                                  'roomba': roomba,
                                                                  'flower': flower,
                                                                 })

def search_by_tags():
    data = json.loads(request.get_data())
    page = int(request.args.get('page', 1))
    per_page = int(request.args.get('per_page', 10))
    tags_list = data.get('tags')
    if not isinstance(tags_list, list):
        tags_list = tags_list.split(",")
    post_id_select = select([distinct(tags.c.post_id)])\
        .where(tags.c.tag.in_(tags_list))\
        .limit(per_page).offset((page-1) * per_page)
    post_total = select([distinct(tags.c.post_id)])\
        .where(tags.c.tag.in_(tags_list))
    conn = engine.connect()
    result_post_id = conn.execute(post_id_select)
    result_post_total = conn.execute(post_total)
    conn.close()
    if result_post_id.rowcount:
        post_id_list = [dict(r).get('post_id') for r in result_post_id.fetchall()]
        all_matched_posts = posts.select().where(posts.c.id.in_(post_id_list))
        conn = engine.connect()
        res_all_matched_posts = conn.execute(all_matched_posts)
        conn.close()
        res = {
            'total': result_post_total.rowcount,
            'data': [dict(r) for r in res_all_matched_posts.fetchall()]
        }
        return jsonify(res)
    else:
        res = {
            'total': result_post_total.rowcount,
            'data': []
        }
        return jsonify(res)

def wemo(request, command=None, device=None):
    if not command:
        raise NoCommandSpecified()
    if device:
        device = urllib.unquote(device)

    if command == 'ls':
        devices = session.query(distinct(WemoTimeSeries.device_name)).all()
        response = '<p>'
        for d in devices:
            response += "'" + d[0] + "'</br>"
        response += '</p>'
    
    elif command == 'on':
        wemo = WemoCC().on(device)
        response = '<p>' + device + ' turned on.</p>'
    
    elif command == 'off':
        wemo = WemoCC().off(device)
        response = '<p>' + device + ' turned off.</p>'

    elif command == 'completion':
        completion = []
        devices = session.query(distinct(WemoTimeSeries.device_name)).all()
        for d in devices:
            completion.append(d.lower().replace(' ', '_'))
        response = json.dumps(completion)

    return HttpResponse(response)

    def get(self):
        """
        Gathers all events from the database with their data
        return a json object representing the events
        """
        
        session = db.loadSession()

        # Make the sql query
        result = session.query(
            # What to select
            # distinct because of multiple medals per event
            distinct(db.Event.id),
            db.Event.name,
            db.Sport.name,
            func.array_agg_cust(distinct(array([cast(db.Olympics.id, String), cast(db.Olympics.year, String), db.Olympics.season])))
            )\
            .select_from(db.Event)\
            .join(db.Sport)\
            .join(db.Medal)\
            .join(db.Olympics)\
            .group_by(db.Event.id,
            db.Event.name,
            db.Sport.name)\
            .all() # Actually executes the query and returns a list of tuples
        
        session.close()
        
        keys = ('id', 'name', 'sport', ('olympics', ('id', 'year', 'season')))
        
        all_events_dict = list_of_dict_to_dict_of_dict(add_keys(keys, row) for row in result)
        
        return jsonify(all_events_dict)

def populate_debuggerquarters(session):
    # For efficiency, we restrict our search to those debuggers who have touched a bug in some way
    first = lambda tup:tup[0]
    assignee_ids = set(map(first, session.query(distinct(Bug.assignee_id)).all()))
    bugeventful_ids = set(map(first, session.query(distinct(BugEvent.dbid)).all()))

    bugtouchers = set.union(assignee_ids, bugeventful_ids)

    n = 0
    for month in session.query(Month):
        ## XXX testing deleteme
        if month.first <= datetime.date(2008, 6, 4) <= month.last:
            print "asdff 233 should be in here"
        else:
            continue
        quarter = Quarter(first=month)
        graph = MozIRCGraph.load(quarter, session)
        print "Graph with %d vertices" % (len(graph.dbid_to_vertex))
        for (dbid, vertex) in graph.dbid_to_vertex.iteritems():
            if dbid not in bugtouchers:
                continue
            dq = DebuggerQuarter(dbid=dbid, first=quarter.first)

            dq.constraint = vertex.constraint()[0]
            dq.closeness = vertex.closeness()
            dq.clustering = graph.g.transitivity_local_undirected([vertex.index])[0]
            dq.indegree = vertex.indegree()
            dq.outdegree = vertex.outdegree()
            dq.betweenness = vertex.betweenness()
            dq.effective_size = graph.effective_size(vertex)

            session.add(dq)
            n += 1

    print "Added %d dms" % (n)

def populate_debuggermonths(session):
    # For efficiency, we restrict our search to those debuggers who have touched a bug in some way
    first = lambda tup:tup[0]
    assignee_ids = set(map(first, session.query(distinct(Bug.assignee_id)).all()))
    bugeventful_ids = set(map(first, session.query(distinct(BugEvent.dbid)).all()))

    bugtouchers = set.union(assignee_ids, bugeventful_ids)
    n = 0
    for month in session.query(Month):
        graph = MozIRCGraph.load(month, session)
        print "Got graph with %d vertices" % (len(graph.dbid_to_vertex))
        for (dbid, vertex) in graph.dbid_to_vertex.iteritems():
            if dbid not in bugtouchers:
                continue
            dm = DebuggerMonth(dbid=dbid, monthid=month.id)

            dm.constraint = vertex.constraint()[0]
            dm.closeness = vertex.closeness()
            dm.clustering = graph.g.transitivity_local_undirected([vertex.index])[0]
            dm.indegree = vertex.indegree()
            dm.outdegree = vertex.outdegree()
            dm.betweenness = vertex.betweenness()
            dm.effective_size = graph.effective_size(vertex)

            session.add(dm)
            n += 1

    print "Added %d dms" % (n)

    def getSamplingFeatures(self, ids=None, codes=None, uuids=None, type=None, wkt=None, results=False):
        """Retrieve a list of Sampling Feature objects.

        If no arguments are passed to the function, or their values are None,
        all Sampling Feature objects in the database will be returned.

        Args:
            ids (list, optional): List of SamplingFeatureIDs.
            codes (list, optional): List of SamplingFeature Codes.
            uuids (list, optional): List of UUIDs string.
            type (str, optional): Type of Sampling Feature from
                `controlled vocabulary name <http://vocabulary.odm2.org/samplingfeaturetype/>`_.
            wkt (str, optional): SamplingFeature Well Known Text.
            results (bool, optional): Whether or not you want to return only the
                sampling features that have results associated with them.

        Returns:
            list: List of Sampling Feature objects

        Examples:
            >>> READ = ReadODM2(SESSION_FACTORY)
            >>> READ.getSamplingFeatures(ids=[39, 40])
            >>> READ.getSamplingFeatures(codes=['HOME', 'FIELD'])
            >>> READ.getSamplingFeatures(uuids=['a6f114f1-5416-4606-ae10-23be32dbc202',
            ...                                 '5396fdf3-ceb3-46b6-aaf9-454a37278bb4'])
            >>> READ.getSamplingFeatures(type='Site')
            >>> READ.getSamplingFeatures(wkt='POINT (30 10)')
            >>> READ.getSamplingFeatures(results=True)
            >>> READ.getSamplingFeatures(type='Site', results=True)

        """
        if results:
            try:
                fas = [x[0] for x in self._session.query(distinct(Results.FeatureActionID)).all()]
            except:
                return None
            sf = [x[0] for x in self._session.query(distinct(FeatureActions.SamplingFeatureID))
                                    .filter(FeatureActions.FeatureActionID.in_(fas)).all()]
            if ids:
                ids = list(set(ids).intersection(sf))
            else:
                ids = sf

        q = self._session.query(SamplingFeatures)

        if type:
            q = q.filter_by(SamplingFeatureTypeCV=type)
        if ids:
            q = q.filter(SamplingFeatures.SamplingFeatureID.in_(ids))
        if codes:
            q = q.filter(SamplingFeatures.SamplingFeatureCode.in_(codes))
        if uuids:
            q = q.filter(SamplingFeatures.SamplingFeatureUUID.in_(uuids))
        if wkt:
            q = q.filter_by(FeatureGeometryWKT=wkt)
        try:
            return q.all()
        except Exception as e:
            print('Error running Query: {}'.format(e))
            return None

  def counts(self, terms, types=None, contact_id=None,
             extra_params=None, extra_columns=None):
    """Prepare the search query, but return only count for each of
     the requested objects."""
    extra_params = extra_params or {}
    extra_columns = extra_columns or {}
    model_names = self._get_grouped_types(types, extra_params)
    query = db.session.query(
        self.record_type.type, func.count(distinct(
            self.record_type.key)), literal(""))
    query = query.filter(self.get_permissions_query(model_names))
    query = query.filter(self._get_filter_query(terms))
    query = self.search_get_owner_query(query, types, contact_id)
    query = query.group_by(self.record_type.type)
    all_extra_columns = dict(extra_columns.items() +
                             [(p, p) for p in extra_params
                              if p not in extra_columns])
    if not all_extra_columns:
      return query.all()

    # Add extra_params and extra_colums:
    for key, value in all_extra_columns.iteritems():
      extra_q = db.session.query(self.record_type.type,
                                 func.count(distinct(self.record_type.key)),
                                 literal(key))
      extra_q = extra_q.filter(self.get_permissions_query([value]))
      extra_q = extra_q.filter(self._get_filter_query(terms))
      extra_q = self.search_get_owner_query(extra_q, [value], contact_id)
      extra_q = self._add_extra_params_query(extra_q,
                                             value,
                                             extra_params.get(key, None))
      extra_q = extra_q.group_by(self.record_type.type)
      query = query.union(extra_q)
    return query.all()

    def __query_database(self, search=None, page=0, page_size=0, order_by=None, order_dir=None, host_filter={}):
        host_bundle = Bundle('host', Host.id, Host.name, Host.os, Host.description, Host.owned,\
            Host.default_gateway_ip, Host.default_gateway_mac, EntityMetadata.couchdb_id,\
            EntityMetadata.revision, EntityMetadata.update_time, EntityMetadata.update_user,\
            EntityMetadata.update_action, EntityMetadata.creator, EntityMetadata.create_time,\
            EntityMetadata.update_controller_action, EntityMetadata.owner, EntityMetadata.command_id,\
            func.group_concat(distinct(Interface.id)).label('interfaces'),\
            func.count(distinct(Vulnerability.id)).label('vuln_count'),\
            func.count(distinct(Service.id)).label('open_services_count'))

        query = self._session.query(host_bundle)\
                             .outerjoin(EntityMetadata, EntityMetadata.id == Host.entity_metadata_id)\
                             .outerjoin(Interface, Host.id == Interface.host_id)\
                             .outerjoin(Vulnerability, Host.id == Vulnerability.host_id)\
                             .outerjoin(Service, (Host.id == Service.host_id) & (Service.status.in_(('open', 'running', 'opened'))))\
                             .group_by(Host.id)

        # Apply pagination, sorting and filtering options to the query
        query = sort_results(query, self.COLUMNS_MAP, order_by, order_dir, default=Host.id)
        query = apply_search_filter(query, self.COLUMNS_MAP, search, host_filter, self.STRICT_FILTERING)
        count = get_count(query, count_col=Host.id)

        if page_size:
            query = paginate(query, page, page_size)

        results = query.all()

        return results, count

def my_view(request):
    try:
        # query statistics for the main page
        result_dict = dict()
        result_dict["orphan_msrun_count"] = \
            DBSession.query(func.count(distinct(MsRun.filename))).filter(MsRun.source_source_id == None).filter(MsRun.flag_trash == 0).one()[0]
        result_dict["all_msrun_count"] = DBSession.query(func.count(distinct(MsRun.filename))).one()[0]
        result_dict["sources_count"] = DBSession.query(func.count(distinct(Source.sample_id))).one()[0]
        result_dict["trash_count"] = DBSession.query(func.count(distinct(MsRun.filename))).filter(MsRun.flag_trash == 1).one()[0]


        result_dict["orphan_msrun"] = json.dumps(
        DBSession.query(distinct(MsRun.filename).label("orphan_ms_run")).filter(MsRun.source_source_id == None).filter(MsRun.flag_trash == 0).order_by(MsRun.filename.desc()).limit(10).all())

        #SELECT (organ), count(organ) from Source group by organ
        sources = DBSession.query(Source.organ, func.count(Source.organ)).group_by(Source.organ).order_by(func.count(Source.organ).desc()).all()
        merged_sources = dict()
        source_acc = 0
        for i in range(0,len(sources)):
            if i < 6:
                merged_sources[sources[i][0]] = sources[i][1]
            else:
                source_acc += sources[i][1]
        merged_sources["others"] = source_acc
        result_dict["sources"] = json.dumps(merged_sources)

        return result_dict
    except:
        return Response(conn_err_msg, content_type='text/plain', status_int=500)

    def list(self, service_filter={}):
        service_bundle = Bundle('service',
                Service.id, Service.name, Service.description, Service.protocol,
                Service.status, Service.ports, Service.version, Service.owned,
                Service.interface_id,
                func.count(distinct(Vulnerability.id)).label('vuln_count'), EntityMetadata.couchdb_id,\
                EntityMetadata.revision, EntityMetadata.update_time, EntityMetadata.update_user,\
                EntityMetadata.update_action, EntityMetadata.creator, EntityMetadata.create_time,\
                EntityMetadata.update_controller_action, EntityMetadata.owner, EntityMetadata.command_id,
                func.count(distinct(Credential.id)).label("credentials_count"))

        query = self._session.query(service_bundle).\
                group_by(Service.id).\
                outerjoin(EntityMetadata, EntityMetadata.id == Service.entity_metadata_id).\
                outerjoin(Vulnerability, Service.id == Vulnerability.service_id).group_by(Service.id).\
                outerjoin(Interface, Interface.id == Service.interface_id).\
                outerjoin(Credential, (Credential.service_id == Service.id) and (Credential.host_id == None)).\
                outerjoin(Host, Host.id == Interface.host_id)

        query = apply_search_filter(query, self.COLUMNS_MAP, None, service_filter, self.STRICT_FILTERING)

        # 'LIKE' for search services started by hostId.%.%
        if service_filter.get('hostIdCouchdb') is not None:
            query = query.filter(
                EntityMetadata.couchdb_id.like(service_filter.get('hostIdCouchdb') + ".%.%"))

        raw_services = query.all()
        services = [self.__get_service_data(r.service) for r in raw_services]
        result = {'services': services}
        return result

    def generateSummaryJs(self, target_fn):
        n_primers = self.session.query(func.count(PrimerSet.id)).one()[0]
        n_markers = (self.session.query(
            func.count(distinct(Ortholog.id)))
            .join(PrimerSet)
            .one()
        )[0]
        n_species = (self.session.query(
            PrimerSet.num_species,
            func.count(distinct(Ortholog.id)),
            func.count(PrimerSet.id))
            .join(Ortholog)
            .group_by(PrimerSet.num_species)
            .all()
        )
        categories = (self.session.query(
            Category.name,
            func.count(distinct(Ortholog.id)))
            .join(Function)
            .join(Ortholog, Function.orthologs)
            .join(PrimerSet)
            .group_by(Category.name)
            .all()
        )
        functions = (self.session.query(
            Category.name,
            Function.shortcode,
            func.count(distinct(Ortholog.id)))
                     .join(Function)
            .join(Ortholog, Function.orthologs)
            .join(PrimerSet)
            .group_by(Function.shortcode)
            .order_by(Category.name)
            .all()
        )

        out_str = '''var summary = [{
    'n_primers': %i,
    'n_markers': %i
}];

var species = [
    %s
];

var categories = [
    %s
];

var subcats = [
    %s
];''' % (n_primers, n_markers,
             ',\n\t'.join(["[%d, %d, %d]" % x for x in n_species]),
             ',\n\t'.join(["['%s', %i]" % x for x in categories]),
             ',\n\t'.join(["['%s', %i]" % (x[1], x[2]) for x in functions]),)

        with open(target_fn, 'wt') as outfile:
            print(outfile.name)
            outfile.write(out_str)

 def info(*args, **kwargs):
     DB = db_op.rota
     g.main_infos = None
     date = time.strftime('%Y-%m-%d',time.localtime())
     ym = time.strftime('%Y',time.localtime())
     new_date = datetime.date.today()+datetime.timedelta(1)
     user = request.cookies.get('user')
     db = db_op.idc_users
     val = db.query.with_entities(db.grade).filter(db.name == user).all()
     grade = int(val[0][0]) if val else 10
     if user and  '@' in user:
         user = user.split('@')[0]
     data=[user]
     try:
         # 生成今日和明日的运维排班
         users = []
         duty = u'运维值班'
         pools = [u'李晓辉',u'周福成']
         for t in (date,new_date):
             VAL = DB.query.with_entities(DB.name).filter(and_(DB.date == t,DB.duty == duty)).all()
             if VAL:
                 user = VAL[0][0]
             else:
                 user = random.choice(pools)
                 c = DB(name = user,duty = duty,date = t)
                 db_op.DB.session.add(c)
                 db_op.DB.session.commit()
             pools.remove(user)
             users.append(user)
         data.extend(users)
         ip=request.headers.get('X-Forwarded-For')
         if not ip :
             ip = request.remote_addr
         if ',' in ip:
             ip = ip.split(',')[0]
         #获取页面菜单
         DB = db_op.op_menu
         nav_val = {}
         sub_val = {}
         Menu_val = DB.query.with_entities(DB.Menu_name, DB.id_name, DB.module_name, DB.action_name).filter(DB.grade >= grade).all()
         navMenu = DB.query.with_entities(distinct(DB.Menu_name)).filter(and_(DB.Menu == 'navMenu',DB.grade >= grade)).order_by(DB.Menu_id).all()
         navMenu = [Menu[0] for Menu in navMenu]
         for Menu in navMenu:
             val = [val[1:] for val in Menu_val if Menu in val]
             if val:
                 nav_val[Menu] = val
         submenu = DB.query.with_entities(distinct(DB.Menu_name)).filter(and_(DB.Menu == 'submenu',DB.grade >= grade)).order_by(DB.Menu_id).all()
         submenu = [menu[0] for menu in submenu]
         for menu in submenu:
             val = [val[2:] for val in Menu_val if menu in val]
             if val:
                 sub_val[menu] = val
         g.main_infos = {'datas':data,'navMenu':navMenu,'nav_val':nav_val,'submenu':submenu,'sub_val':sub_val,'ip':ip,'ym':ym}
         return func(*args, **kwargs)
     except Exception as e:
         loging.write(e)
         return func(*args, **kwargs)
     finally:
         db_op.DB.session.remove()

def index(page=None, id=None):
    if 'application/json' not in request.headers.get('Accept', ''):
        return render_template('ember-page.jinja', active_page='statistics')

    name = None

    query = db.session.query(Flight.year.label('year'),
                             func.count('*').label('flights'),
                             func.count(distinct(Flight.pilot_id)).label('pilots'),
                             func.sum(Flight.olc_classic_distance).label('distance'),
                             func.sum(Flight.duration).label('duration'))

    pilots_query = db.session.query(func.count(distinct(Flight.pilot_id)))

    if page == 'pilot':
        pilot = get_requested_record(User, id)
        name = unicode(pilot)
        query = query.filter(Flight.pilot_id == pilot.id)

    elif page == 'club':
        club = get_requested_record(Club, id)
        name = unicode(club)
        query = query.filter(Flight.club_id == club.id)
        pilots_query = pilots_query.filter(Flight.club_id == club.id)

    elif page == 'airport':
        airport = get_requested_record(Airport, id)
        name = unicode(airport)
        query = query.filter(Flight.takeoff_airport_id == airport.id)
        pilots_query = pilots_query.filter(Flight.takeoff_airport_id == airport.id)

    elif page is not None:
        abort(404)

    query = query.filter(Flight.is_rankable())

    query = query.group_by(Flight.year).order_by(Flight.year.desc())

    if page == 'pilot':
        sum_pilots = 0
    else:
        sum_pilots = pilots_query.scalar()

    list = []
    for row in query:
        row.average_distance = row.distance / row.flights
        row.average_duration = row.duration / row.flights

        list.append({
            'year': row.year,
            'flights': row.flights,
            'distance': row.distance,
            'duration': row.duration.total_seconds(),
            'pilots': row.pilots,
            'average_distance': row.distance / row.flights,
            'average_duration': row.duration.total_seconds() / row.flights,
        })

    return jsonify(name=name, years=list, sumPilots=sum_pilots)

	def get_sites(self, site_code = ""):
		result = None
		if (site_code):
			result = self._edit_session.query(distinct(Series.site_id), Series.site_code, Series.site_name).filter_by(site_code=site_code).one()
		else:
			result = self._edit_session.query(distinct(Series.site_id), Series.site_code, Series.site_name).order_by(Series.site_code).all()

		return result

 def delete_orphans(self):
     self.session.query(Genre).filter(~Genre.id.in_(self.session.query(distinct(TrackInfo.genre_id)))).delete(False)
     self.session.commit()
     self.session.query(Album).filter(~Album.id.in_(self.session.query(distinct(TrackInfo.album_id)))).delete(False)
     self.session.commit()
     self.session.query(Cover).filter(~Cover.id.in_(self.session.query(distinct(Album.cover_id)))).delete(False)
     self.session.commit()
     self.session.query(Artist).filter(~Artist.id.in_(self.session.query(distinct(Album.artist_id)))).delete(False)
     self.session.commit()

    def deferred_customer_widget(node, kw):

        if default_option:
            values = [default_option]
        else:
            values = []
        if is_admin:
            query = Customer.query().join(Customer.company)
            query = query.options(
                contains_eager(Customer.company).load_only('name')
            )
            query = query.options(load_only('id', 'label'))

            if with_invoice:
                query = query.filter(
                    Customer.id.in_(
                        DBSESSION().query(distinct(Task.customer_id)).filter(
                            Task.type_.in_(['invoice', 'cancelinvoice'])
                        )
                    )
                )
            elif with_estimation:
                query = query.filter(
                    Customer.id.in_(
                        DBSESSION().query(distinct(Task.customer_id)).filter(
                            Task.type_ == 'estimation'
                        )
                    )
                )

            datas = OrderedDict()

            for item in query:
                datas.setdefault(item.company.name, []).append(
                    (item.id, item.label)
                )

            # All customers, grouped by Company
            for company_name, customers in datas.items():
                values.append(
                    deform.widget.OptGroup(
                        company_name,
                        *customers
                    )
                )
        else:
            # Company customers only
            company = kw['request'].context
            for cust in company.customers:
                values.append(
                    (cust.id, u"%s (%s)" % (cust.name, cust.code))
                )

        return deform.widget.Select2Widget(
            values=values,
            **(widget_options or {})
        )

def index(page=None, id=None):
    name = None

    query = db.session.query(
        Flight.year.label("year"),
        func.count("*").label("flights"),
        func.count(distinct(Flight.pilot_id)).label("pilots"),
        func.sum(Flight.olc_classic_distance).label("distance"),
        func.sum(Flight.duration).label("duration"),
    )

    pilots_query = db.session.query(func.count(distinct(Flight.pilot_id)))

    if page == "pilot":
        pilot = get_requested_record(User, id)
        name = pilot.name
        query = query.filter(Flight.pilot_id == pilot.id)

    elif page == "club":
        club = get_requested_record(Club, id)
        name = club.name
        query = query.filter(Flight.club_id == club.id)
        pilots_query = pilots_query.filter(Flight.club_id == club.id)

    elif page == "airport":
        airport = get_requested_record(Airport, id)
        name = airport.name
        query = query.filter(Flight.takeoff_airport_id == airport.id)
        pilots_query = pilots_query.filter(Flight.takeoff_airport_id == airport.id)

    elif page is not None:
        abort(404)

    query = query.filter(Flight.is_rankable())

    query = query.group_by(Flight.year).order_by(Flight.year.desc())

    if page == "pilot":
        sum_pilots = 0
    else:
        sum_pilots = pilots_query.scalar()

    list = []
    for row in query:
        list.append(
            {
                "year": row.year,
                "flights": row.flights,
                "distance": row.distance,
                "duration": row.duration.total_seconds(),
                "pilots": row.pilots,
                "average_distance": row.distance / row.flights,
                "average_duration": row.duration.total_seconds() / row.flights,
            }
        )

    return jsonify(name=name, years=list, sumPilots=sum_pilots)