def select_tz (self):
        """Select time zones and other related fields from database.

        Selects count of messages, count of distinct senders,
        time zone.

        Returns
        -------

        Query object

        """

        query = self.add_columns(
            label("tz",
                  ((DB.Messages.first_date_tz.op('div')(3600) + 36) % 24) - 12),
            label("messages",
                  func.count(func.distinct(DB.Messages.message_ID))),
            label("authors",
                  func.count(func.distinct(DB.MessagesPeople.email_address))))
        self.joined.append (DB.Messages)
        if DB.MessagesPeople not in self.joined:
            query = query.join (DB.MessagesPeople)
            self.joined.append (DB.MessagesPeople)
            query = query.filter (DB.MessagesPeople.type_of_recipient == "From")
        return query

    def crawler_stats(cls, crawler_id):
        stats = {}
        col = func.count(func.distinct(cls.crawler_run))
        q = db.session.query(col)
        q = q.filter(cls.crawler_id == crawler_id)
        stats['run_count'] = q.scalar()
        last_run_id, last_run_time = cls.crawler_last_run(crawler_id)

        # Check if the crawler was active very recently, if so, don't
        # allow the user to execute a new run right now.
        timeout = (datetime.utcnow() - CrawlerState.TIMEOUT)
        stats['running'] = last_run_time > timeout if last_run_time else False

        q = db.session.query(func.count(func.distinct(cls.foreign_id)))
        q = q.filter(cls.crawler_id == crawler_id)
        for section in ['last', 'all']:
            data = {}
            sq = q
            if section == 'last':
                sq = sq.filter(cls.crawler_run == last_run_id)
            okq = sq.filter(cls.status == cls.STATUS_OK)
            data['ok'] = okq.scalar() if last_run_id else 0
            failq = sq.filter(cls.status == cls.STATUS_FAIL)
            data['fail'] = failq.scalar() if last_run_id else 0
            stats[section] = data
        stats['last']['updated'] = last_run_time
        stats['last']['run_id'] = last_run_id
        return stats

    def nearest_neighbors(self, limit=10):
        '''
        Returns a list of (user, score) tuples with the closest matching
        skills.  If they haven't answered the equivalent skill question, we
        consider that a very big difference (12).

        Order is closest to least close, which is an ascending score.
        '''
        my_skills = aliased(UserSkill, name='my_skills', adapt_on_names=True)
        their_skills = aliased(UserSkill, name='their_skills', adapt_on_names=True)

        # difference we assume for user that has not answered question
        unanswered_difference = (LEVELS['LEVEL_I_CAN_DO_IT']['score'] -
                                 LEVELS['LEVEL_I_WANT_TO_LEARN']['score']) * 2

        return User.query_in_deployment().\
                add_column(((len(self.skills) - func.count(func.distinct(their_skills.id))) *
                            unanswered_difference) + \
                       func.sum(func.abs(their_skills.level - my_skills.level))).\
                filter(their_skills.user_id != my_skills.user_id).\
                filter(User.id == their_skills.user_id).\
                filter(their_skills.name == my_skills.name).\
                filter(my_skills.user_id == self.id).\
                group_by(User).\
                order_by(((len(self.skills) - func.count(func.distinct(their_skills.id)))
                          * unanswered_difference) + \
                     func.sum(func.abs(their_skills.level - my_skills.level))).\
                limit(limit)

 def connections(self):
     '''
     Count the number of distinct email addresses this person has sent or
     received messages from in the deployment.
     '''
     sent = db.session.query(func.count(func.distinct(Email.to_user_id))).\
             filter(Email.to_user_id != self.id).\
             filter(Email.from_user_id == self.id).first()[0]
     received = db.session.query(func.count(func.distinct(Email.from_user_id))).\
             filter(Email.from_user_id != self.id).\
             filter(Email.to_user_id == self.id).first()[0]
     return sent + received

    def select_listpersons(self, kind = "all"):
        """Select a list of persons (authors, committers)

        - kind: kind of person to select
           authors: authors of commits
           committers: committers of commits
           all: authors and committers

        Returns a SCMQuery object, with (id, name, email) selected.
        """

        query = self.add_columns (label("id", func.distinct(People.id)),
                                  label("name", People.name),
                                  label('email', People.email))
        if kind == "authors":
            return query.join (SCMLog, People.id == SCMLog.author_id)    
        elif kind == "committers":
            return query.join (SCMLog, People.id == SCMLog.committer_id)    
        elif kind == "all":
            return query.join (SCMLog,
                               People.id == SCMLog.author_id or
                               People.id == SCMLog.committer_id)
        else:
            raise Exception ("select_listpersons: Unknown kind %s." \
                             % kind)

    def select_listpersons_uid(self, kind = "all"):
        """Select a list of persons (authors, committers), using uids

        - kind: kind of person to select
           authors: authors of commits
           committers: committers of commits
           all: authors and committers
        Returns a SCMQuery object, with (id, name, email) selected.
        """
        
        query = self.add_columns (label("id", func.distinct(UPeople.id)),
                                  label("name", UPeople.identifier)) \
                .join (PeopleUPeople, UPeople.id == PeopleUPeople.upeople_id)
        if kind == "authors":
            return query.join (SCMLog,
                               PeopleUPeople.people_id == SCMLog.author_id)
        elif kind == "committers":
            return query.join (SCMLog,
                               PeopleUPeople.people_id == SCMLog.committer_id)
        elif kind == "all":
            return query.join (SCMLog,
                               PeopleUPeople.people_id == SCMLog.author_id or
                               PeopleUPeople.people_id == SCMLog.committer_id)
        else:
            raise Exception ("select_listpersons_uid: Unknown kind %s." \
                             % kind)

    def select_nscmlog(self, variables):
        """Select a variable which is a field in Scmlog.

        - variables (list): variables to select
            Currently supported: "commits", "authors", "committers"
        """

        if not isinstance(variables, (list, tuple)):
            raise Exception ("select_nscmlog: Argument is not list or tuple")
        elif len (variables) == 0:
            raise Exception ("select_nscmlog: No variables")
        fields = []
        for variable in variables:
            if variable == "commits":
                name = "nocommits"
                field = SCMLog.id
            elif variable == "authors":
                name = "nauthors"
                field = SCMLog.author_id
            elif variable == "committers":
                name = "ncommitters"
                field = SCMLog.committer_id
            else:
                raise Exception ("select_nscmlog: Unknown variable %s." \
                                     % variable)
            fields.append (label (name,
                                  func.count(func.distinct(field))))
        return self.add_columns (*fields)

def update_user_interests(date_from, date_to):
    active_users = readonly_session.query(UserActivity.user).filter(
        UserActivity.date_actioned.between(date_from, date_to)).subquery()
    activity_categories = readonly_session.query(
        UserActivity.user,
        Channel.category,
        func.count(func.distinct(Channel.id))
    ).outerjoin(
        VideoInstance,
        (UserActivity.object_type == 'video_instance') &
        (UserActivity.object_id == VideoInstance.id)
    ).filter(
        ((UserActivity.object_type == 'channel') & (UserActivity.object_id == Channel.id)) |
        (VideoInstance.channel == Channel.id)
    ).filter(
        UserActivity.user.in_(active_users),
        Channel.category != None
    ).group_by('1, 2').order_by('1, 3 desc')

    for user, categories in groupby(activity_categories, lambda x: x[0]):
        UserInterest.query.filter_by(user=user).delete()
        db.session.execute(UserInterest.__table__.insert(), [
            dict(user=user, explicit=False, category=category, weight=weight)
            for user, category, weight in categories
        ][:10])

    def index_old(self):
        from gviz_data_table import Table
        from rockpack.mainsite.services.user.models import User, UserActivity
        user_count = readonly_session.query(func.count(User.id)).\
            filter(User.refresh_token != '').scalar()
        header = ('user count', 'max lifetime', 'avg lifetime', 'stddev lifetime',
                  'max active days', 'avg active days', 'stddev active days')
        lifetime = func.date_part('days', func.max(UserActivity.date_actioned) -
                                  func.min(UserActivity.date_actioned)).label('lifetime')
        active_days = func.count(func.distinct(func.date(
            UserActivity.date_actioned))).label('active_days')
        activity = readonly_session.query(UserActivity.user, lifetime, active_days).\
            group_by(UserActivity.user)
        ctx = {}
        for key, having_expr in ('all', None), ('1day', lifetime > 1), ('7day', lifetime > 7):
            data = activity.having(having_expr).from_self(
                func.count('*'),
                func.max(lifetime),
                func.avg(lifetime),
                func.stddev_samp(lifetime),
                func.max(active_days),
                func.avg(active_days),
                func.stddev_samp(active_days)
            ).one()
            table = Table([
                dict(id='metric', type=str),
                dict(id='value', type=float),
                dict(id='%', type=str),
            ])
            pdata = ('%d%%' % (data[0] * 100 / user_count),) + ('',) * 6
            table.extend(zip(*(header, map(float, data), pdata)))
            ctx['ret_%s_data' % key] = table.encode()

        return self.render('admin/retention_stats_old.html', **ctx)

    def index(self):
        from gviz_data_table import Table
        from rockpack.mainsite.services.user.models import User, UserActivity, UserAccountEvent
        if request.args.get('activity') == 'activity':
            activity_model, activity_date = UserActivity, UserActivity.date_actioned
        else:
            activity_model, activity_date = UserAccountEvent, UserAccountEvent.event_date

        try:
            interval_count = int(request.args['interval_count'])
        except Exception:
            interval_count = 10

        interval = request.args.get('interval')
        if interval not in ('week', 'month'):
            interval = 'week'

        cohort = func.date_part(interval, User.date_joined)
        cohort_label = func.min(func.date(User.date_joined))
        active_interval = (func.date_part(interval, activity_date) - cohort).label('active_interval')

        q = readonly_session.query(User).filter(
            User.date_joined > LAUNCHDATE, User.refresh_token != '')
        if request.args.get('gender') in ('m', 'f'):
            q = q.filter(User.gender == request.args['gender'])
        if request.args.get('locale') in app.config['ENABLED_LOCALES']:
            q = q.filter(User.locale == request.args['locale'])
        if request.args.get('age') in ('13-18', '18-25', '25-35', '35-45', '45-55'):
            age1, age2 = map(int, request.args['age'].split('-'))
            q = q.filter(between(
                func.age(User.date_of_birth),
                text("interval '%d years'" % age1),
                text("interval '%d years'" % age2)
            ))

        active_users = dict(
            ((c, int(w)), u) for c, w, u in
            q.join(
                activity_model,
                (activity_model.user == User.id) &
                (activity_date >= User.date_joined)
            ).group_by(cohort, active_interval).values(
                cohort, active_interval, func.count(func.distinct(activity_model.user))
            )
        )

        table = Table(
            [dict(id='cohort', type=date)] +
            [dict(id='%s%d' % (interval, i), type=str) for i in range(interval_count)]
        )

        totals = q.group_by(cohort).order_by(cohort)
        for c, l, t in totals.values(cohort, cohort_label, func.count()):
            data = []
            for i in range(interval_count):
                a = active_users.get((c, i), '')
                data.append(a and '%d%% (%d)' % (ceil(a * 100.0 / t), a))
            table.append([l] + data)

        return self.render('admin/retention_stats.html', data=table.encode())

def get_total_unique_skills():
    return db.session.query(func.count(
        func.distinct(cast(models.UserSkill.level, String) + '-' +
                      cast(models.UserSkill.name, String))
    )).\
    filter(models.UserSkill.level != \
           LEVELS['LEVEL_I_WANT_TO_LEARN']['score']).scalar()

def landing():
    """
    Show a landing page giving a short intro blurb to unregistered users
    and very basic metrics such as total users.
    """
    # Create a list of total project counts in the form
    # [(day, count), ...].
    projects_graph_data = []
    now = datetime.datetime.utcnow()
    for day_ago in range(30):
        limit = now - datetime.timedelta(days=day_ago)

        projects_graph_data.append((
            time.mktime(limit.timetuple()) * 1000,
            Project.query.filter(Project.created <= limit).count()
        ))

    # Find the 10 latest public projects.
    new_projects = (
        Project.visible(Project.query, user=g.user)
        .order_by(False)
        .order_by(Project.created.desc())
    ).paginate(1, 10, False)

    # Sum the total number of messages across all projects, caching
    # it for the next two minutes.
    total_messages = g.redis.get('cache_message_count')
    if total_messages is None:
        total_messages = g.db.session.query(
            func.sum(Project.message_count)
        ).scalar()
        if total_messages is None:
            total_messages = 0

        g.redis.setex('cache_message_count', 120, total_messages)

    # Total # of users.
    total_users = User.query.count()

    # Find the 10 most popular networks.
    top_networks = (
        Channel.visible(g.db.session.query(
            Channel.host,
            func.count(func.distinct(Channel.channel)).label('count')
        ), user=g.user)
        .group_by(Channel.host)
        .order_by('count desc')
    )
    total_networks = top_networks.count()
    top_networks = top_networks.limit(10)

    return render_template(
        'landing.html',
        projects_graph_data=projects_graph_data,
        new_projects=new_projects,
        top_networks=top_networks,
        total_networks=total_networks,
        total_messages=total_messages,
        total_users=total_users
    )

    def get_content_item_ids(self, org, type, **kw):
        content_items = db.session.query(func.distinct(ContentItem.id))\
            .filter_by(org_id=org.id)\
            .filter_by(type=type)\
            .all()

        return [c[0] for c in content_items]

 def select_listcommits(self):
     """Select a list of commits"""
     
     if DB.SCMLog not in self.joined:
         self.joined.append(DB.SCMLog)
     return self \
         .add_columns (label("id", func.distinct(DB.SCMLog.id)),
                       label("date", DB.SCMLog.date))

 def projects_total(self, **kwargs):
     """Total number of projects"""
     filters = list(self.get_filters(**kwargs))
     res = db.session.query(func.count(func.distinct(self.project_id))) \
                     .filter(*filters).scalar()
     if res is None:
         res = 0
     return res

 def domains(self):
     """
     Domains which an organization manages.
     """
     domains = db.session.query(func.distinct(ContentItem.domain))\
         .filter_by(org_id=self.id)\
         .all()
     return [d[0] for d in domains]

    def get_voted_users_num(self):
        from models.vote import Vote
        result = db.session\
            .query(func.count(func.distinct(Vote.user_id)))\
            .filter_by(voting_id=self.id)\
            .first()

        return result[0] if len(result) > 0 else 0

 def find_running_or_queued_action_workflow_ids(datastore_id):
     resultset = db.session\
         .query(func.distinct(ActionDao.data['workflow_id'].astext))\
         .filter(ActionDao.data['datastore_id'].astext == datastore_id)\
         .filter(ActionDao.data['state'].astext.in_([ActionState.RUNNING, ActionState.PENDING, ActionState.QUEUED]))\
         .filter(ActionDao.data['workflow_id'] != 'null')\
         .all()
     return [r[0] for r in resultset]

 def test_query_macro(self):
     query = sql[(
         func.distinct(x.continent) for x in db.country
         if (
             func.sum(w.population) for w in db.country
             if w.continent == x.continent
         ) > 100000000
     )]
     sql_results = engine.execute(query).fetchall()
     query_macro_results = query[(
         func.distinct(x.continent) for x in db.country
         if (
             func.sum(w.population) for w in db.country
             if w.continent == x.continent
         ) > 100000000
     )]
     assert sql_results == query_macro_results

    def page_backlinks(self, title):
        """Gives a list of pages linking to specified page."""

        backlinks = self.db.query(func.distinct(Title.title)).\
                join((Link, Link.src==Title.id)).\
                filter(Link.target==title).\
                order_by(Title.title)
        for (backlink,) in backlinks:
            yield unicode(backlink)