def get(self):
        args = self.parse_arg()

        releases = db.session.query(
            func.count(Release.value).label('count'),
            func.min(Release.activities).label('activities'), 
            func.sum(Release.value).label('total_value'), 
            Buyer.name.label('buyer'),            
            func.min(Buyer.slug).label('buyer_slug'))

        releases = self.filter_request(releases, args)
        releases = releases.filter(Buyer.id == Release.buyer_id)
        releases = releases.group_by(Buyer.name)
        releases = self.sort_request(releases, args)
        
        release_count = releases.count()

        (releases, offset, limit) = self.offset_limit(releases, args)

        #Generate output structure
        output = dict()
            
        output["meta"] = {
            "count": release_count,
            "pagination" : {"offset" : offset, "limit":  limit}
        }

        output["releases"] = [r._asdict() for r in releases] 

        return output 

    def __get_subquery(self, *args, ord_by=None):
        def add_joined_search(field_name):
            joined = db(Search.index, func.min(Search.text).label('text'),
                        func.min(Search.table_name).label('table_name'),
                        index=subquery_search.subquery().c.index).filter(
                    Search.kind.in_(tuple(field_name))).group_by(Search.index)
            return joined

        subquery_search = db(Search.index.label('index'),
                             func.sum(Search.relevance).label('relevance'),
                             func.min(Search.table_name).label('table_name'),
                             func.min(Search.md_tm).label('md_tm'),
                             func.max(Search.position).label('position'),
                             func.max(Search.text).label('text')).filter(
                or_(*self.__get_search_params(*args))).group_by('index')
        if type(ord_by) in (str, list, tuple):
            order = self.__get_order('text', 'text')
            subquery_search = add_joined_search(ord_by)
        elif type(ord_by) == int:
            ord_to_str = self.__order_by_to_str[ord_by]
            order = self.__get_order(ord_to_str, ord_to_str)
        else:
            order = self.__get_order('relevance', 'relevance')
        if 'md_tm' in str(order):
            subquery_search = subquery_search.order_by(order)
        else:
            subquery_search = subquery_search.order_by(order).order_by(
                    self.__get_order('md_tm', 'md_tm'))
        return subquery_search

    def __init__(self, engine=None, start_date='1925-12-31', end_date='',
                limit=None, all_vars=None, **kwargs):
        super(CCMNamesQuery, self).__init__(engine, limit)
        logging.info("---- Creating a CCM-MSENAMES query session. ----")

        msenames = self.tables['msenames']
        ccmxpf_linktable = self.tables['ccmxpf_linktable']

        id_vars = [msenames.c.permno, msenames.c.permco,
                     ccmxpf_linktable.c.gvkey, msenames.c.comnam]

        query = sa.select(id_vars+\
                        [func.min(msenames.c.namedt).label('sdate'),
                        func.max(msenames.c.nameendt).label('edate')],
                    group_by = id_vars,
                    order_by = id_vars,
                    limit= self.limit).\
            where(ccmxpf_linktable.c.linktype.startswith('L')).\
            where(ccmxpf_linktable.c.linkprim.in_(['P','C'])).\
            where(ccmxpf_linktable.c.usedflag==1).\
            where((ccmxpf_linktable.c.linkdt <= msenames.c.namedt) |
                  (ccmxpf_linktable.c.linkdt == None)).\
            where((msenames.c.nameendt <= ccmxpf_linktable.c.linkenddt) |
                  (ccmxpf_linktable.c.linkenddt == None)).\
            where(msenames.c.permno == ccmxpf_linktable.c.lpermno).\
            where(msenames.c.permco == ccmxpf_linktable.c.lpermco)

        if start_date:
            query = query.having(func.min(msenames.c.namedt) >= start_date)

        if end_date:
            query = query.having(func.max(msenames.c.nameendt) <= end_date)

        logging.debug(query)
        self.query = query

    def annotation_objects_in_frame(self, frame):
        """
        Returns annotation objects related to this video that are visible in given time.
        AnnotationValues are lazily-loaded, in comparison to annotation_objects_in_frame_intervals()

        SQL:
            SELECT annotation_objects....., min(annotation_values.frame_from) AS min_1, max(annotation_values.frame_from) AS max_1
            FROM annotation_objects
            INNER JOIN annotation_values ON annotation_objects.id = annotation_values.annotation_object_id
            WHERE annotation_objects.video_id = %s
            GROUP BY annotation_objects.id
            HAVING min(annotation_values.frame_from) <= %s AND max(annotation_values.frame_from) >= %s
            ORDER BY min(annotation_values.frame_from), max(annotation_values.frame_from), annotation_objects.id

        :rtype: list of (AnnotationObject, int, int)
        """

        q = database.db.session.query(AnnotationObject, func.min(AnnotationValue.frame_from), func.max(AnnotationValue.frame_from))
        q = q.filter_by(video_id=self.id)
        q = q.join(AnnotationObject.annotation_values)
        q = q.group_by(AnnotationObject.id)
        q = q.having((func.min(AnnotationValue.frame_from) <= frame) & (func.max(AnnotationValue.frame_from) >= frame))
        q = q.order_by(func.min(AnnotationValue.frame_from), func.max(AnnotationValue.frame_from), AnnotationObject.id)
        q = q.all()

        return q

def listSessions(request):
  
  ssnTmCnt = request.session.query(Session)\
                   .add_column(func.min(Pagerecording.time))\
                   .add_column(func.count(Pagerecording.id))\
                   .join(Pagerecording).group_by(Session)\
                   .order_by(func.min(Pagerecording.time).desc()).all()


  return render_to_response('sessionlist.mako', {'ssnTmCnt':ssnTmCnt}, request=request)  

def backup_duration(bddate):
  s = select([pool.c.name, func.min(job.c.starttime), func.max(job.c.endtime)],  use_labels=True).where(and_(job.c.poolid == pool.c.poolid, cast(job.c.schedtime,Date) <= datetime.fromtimestamp(float(bddate)), cast(job.c.schedtime,Date) >= datetime.fromtimestamp(float(bddate)) - timedelta(days=1))).group_by(pool.c.name, job.c.schedtime)
  bd = db.execute(s).fetchall()
  bd_result = {}
  for bpool in bd:
    bd_result.update({ bpool[0]: { 'start': bpool[1], 'end': bpool[2] } })
  s = select([func.min(job.c.starttime), func.max(job.c.endtime)],  use_labels=True).where(job.c.poolid == pool.c.poolid)
  _min_date, _max_date = db.execute(s).fetchone()
  min_date = int(mktime((strptime(str(_min_date), "%Y-%m-%d %H:%M:%S"))))
  max_date = int(mktime((strptime(str(_max_date), "%Y-%m-%d %H:%M:%S"))))
  return render_template('backup_duration.html', title="Backup duration time", bd_result=bd_result, bddate=int(bddate), min_date=min_date, max_date=max_date)

 def add_joined_search(field_name):
     joined = (
         db(
             Search.index,
             func.min(Search.text).label("text"),
             func.min(Search.table_name).label("table_name"),
             index=subquery_search.subquery().c.index,
         )
         .filter(Search.kind.in_(tuple(field_name)))
         .group_by(Search.index)
     )
     return joined

 def get_order(order_name, desc_asc, field):
     order_name += "+" if desc_asc == "desc" else "-"
     result = {
         "text+": lambda field_name: desc(func.max(getattr(Search, field_name, Search.text))),
         "text-": lambda field_name: asc(func.max(getattr(Search, field_name, Search.text))),
         "md_tm+": lambda field_name: desc(func.min(getattr(Search, field_name, Search.md_tm))),
         "md_tm-": lambda field_name: asc(func.min(getattr(Search, field_name, Search.md_tm))),
         "relevance+": lambda field_name: desc(func.sum(getattr(Search, field_name, Search.relevance))),
         "relevance-": lambda field_name: asc(func.sum(getattr(Search, field_name, Search.relevance))),
         "position+": lambda field_name: desc(func.max(getattr(Search, field_name, Search.position))),
         "position-": lambda field_name: asc(func.max(getattr(Search, field_name, Search.position))),
     }[order_name](field)
     return result

def analytics_closer():
    user1 = db.session.query(User).get(int(request.args.get("user1")))
    user2 = db.session.query(User).get(int(request.args.get("user2")))

    if request.args.get("criterion") == "artist":
        field = Scrobble.artist
    if request.args.get("criterion") == "track":
        field = func.concat(Scrobble.artist, Scrobble.track)

    start_uts = max(
        db.session.query(func.min(Scrobble.uts)).filter_by(user=user1),
        db.session.query(func.min(Scrobble.uts)).filter_by(user=user2)
    )

    def gather_shares(user):
        data = {}
        for (share, uts) in db.session.query(field, Scrobble.uts).filter(Scrobble.user == user, Scrobble.uts >= start_uts):
            week = int(math.floor(uts / (86400 * 7)) * (86400 * 7))
            if week not in data:
                data[week] = set()
            if share not in data[week]:
                data[week].add(share)
        return data
    user1_shares = gather_shares(user1)
    user2_shares = gather_shares(user2)

    if request.args.get("criterion_type") == "integral":
        def integrate_shares(shares):
            prev_week = None
            for week in sorted(shares.keys()):
                if prev_week:
                    shares[week] = set.union(shares[week], shares[prev_week])
                prev_week = week
            return shares
        user1_shares = integrate_shares(user1_shares)
        user2_shares = integrate_shares(user2_shares)

    data = [
        [
            date.fromtimestamp(week).strftime("%b %Y"),
            len(user1_shares[week] - user2_shares[week]) / float(len(user1_shares[week])),
            "",
            ", ".join(sorted(user1_shares[week] - user2_shares[week])),
           -len(user2_shares[week] - user1_shares[week]) / float(len(user2_shares[week])),
            "",
            ", ".join(sorted(user2_shares[week] - user1_shares[week])),
        ]
        for week in sorted(set.intersection(set(user1_shares.keys()), set(user2_shares.keys())))
    ]

    return dict(user1=user1, user2=user2, data=json.dumps(data))

    def annotation_objects_all(self):
        """
        Returns all annotation objects related to this video, with object's first and last frame.
        Annotation objects are sorted by the time of theirs first occurrence in the video.

        :rtype: list of (AnnotationObject, int, int)
        """

        q = database.db.session.query(AnnotationObject, func.min(AnnotationValue.frame_from), func.max(AnnotationValue.frame_from))
        q = q.filter_by(video_id=self.id)
        q = q.join(AnnotationObject.annotation_values)
        q = q.group_by(AnnotationObject.id)
        q = q.order_by(func.min(AnnotationValue.frame_from), func.max(AnnotationValue.frame_from), AnnotationObject.id)

        return q.all()

    def statArtistReleaseCount(self):
        artistAverageCount = self.dbSession.query(func.avg(Artist.releases))
        artistMinimumCount = self.dbSession.query(func.min(Artist.releases)).scalar()
        artistMaximumCount = self.dbSession.query(func.max(Artist.releases)).scalar()
        artistMaximumId = ''
        artistMaxName = ''

        return {
            'title': 'Artist Release Count',
            'class': 'fa-user',
            'average': {
                'type': 'string',
                'value': artistAverageCount,
                'detail': {
                    'text': ''
                }
            },
            'minimum': {
                'type': 'string',
                'value': artistMinimumCount,
                'detail': {
                    'text': 'Many'
                }
            },
            'maximum': {
                'type': 'artist',
                'value': artistMaximumCount,
                'detail': {
                    'id': artistMaximumId,
                    'thumbnailUrl': '/images/artist/thumbnail/' + artistMaximumId,
                    'detailUrl': '/artist/' + artistMaximumId,
                    'text': artistMaxName
                }
            }
        }

    def stats(self, survey_id):
        """Get stats for a survey."""
        result = (
            self.session
            .query(
                func.max(Survey.created_on),
                func.min(Submission.save_time),
                func.max(Submission.save_time),
                func.count(Submission.id),
            )
            .select_from(Submission)
            .join(Survey)
            # TODO: ask @jmwohl what this line is supposed to do
            # .filter(User.id == self.current_user_model.id)
            .filter(Submission.survey_id == survey_id)
            .one()
        )

        response = {
            "created_on": result[0],
            "earliest_submission_time": result[1],
            "latest_submission_time": result[2],
            "num_submissions": result[3]
        }
        return response

def find_day2scrobbles(user, artist):
    day2scrobbles = OrderedDict([(day, 0)
                                 for day in range(int(db.session.query(func.coalesce(func.min(Scrobble.uts), 0)).\
                                                                 filter(Scrobble.user == user,
                                                                        Scrobble.artist == artist).\
                                                                 scalar() / 86400),
                                                  int(db.session.query(func.coalesce(func.max(Scrobble.uts), 0)).\
                                                                 filter(Scrobble.user == user,
                                                                        Scrobble.artist == artist).\
                                                                 scalar() / 86400) + 1)])
    for uts, in db.session.query(Scrobble.uts).\
                           filter(Scrobble.user == user,
                                  Scrobble.artist == artist):
        day2scrobbles[int(uts / 86400)] += 1

    for day in day2scrobbles:
        if day2scrobbles[day] < 4:
            day2scrobbles[day] = 0

    for day in day2scrobbles:
        if day2scrobbles[day] != 0:
            break
        del day2scrobbles[day]
    for day in reversed(day2scrobbles):
        if day2scrobbles[day] != 0:
            break
        del day2scrobbles[day]

    return day2scrobbles

def findMinAndMax(name,field):

    min = {"name":"Min","value":db.session.query(func.min(field)).first()[0]}
    max = {"name":"Max","value":db.session.query(func.max(field)).first()[0]}

    array = {"name":name,"values":[min,max]}
    return array

    def get(self, bucket_id):
        b = Bucket.query.filter_by(id=bucket_id).first()
        if b is None:
            return {'status':'error',
                    'description':'Bucket ' + bucket_id + ' does not exists.'}, 204

        u = User.query.filter_by(id=b.user_id).first()
        if u.id != g.user.id and b.private != '0':
            return {'status':'error',
                    'description':'Private Bucket'}, 401

        result = db.session.query(Post.date).filter(Post.bucket_id==bucket_id).distinct(Post.date).all()

        data = {}
        if len(result) == 0:
            return {'status':'error',
                    'description':'No rows returned'}, 204
        else:
            dateList = []
            for i in range(len(result)):
                dateList.append(result[i][0])

        data['count'] = len(result)
        data['minDate'] = db.session.query(func.min(Post.date).label("min_date")).filter(Post.bucket_id==bucket_id).first().min_date
        data['maxDate'] = db.session.query(func.max(Post.date).label("max_date")).filter(Post.bucket_id==bucket_id).first().max_date
        data['dateList'] = dateList

        return {'status':'success',
                'description': 'Data successfully returned.',
                'data':data}, 200

def associate_activities(user, before=None, after=None):
    assert user.has_strava()
    moves_by_date_time = {}
    for id, date_time in db.session.query(Sample.move_id, func.min(Sample.utc)) \
            .join(Move) \
            .filter(Sample.utc != None) \
            .filter(Move.user == user) \
            .group_by(Sample.move_id):
        utc = date_time.replace(tzinfo=pytz.UTC)
        moves_by_date_time[utc] = id
    moves_by_strava_activity_id = {}
    for id, strava_activity_id in db.session.query(Move.id, Move.strava_activity_id) \
            .filter(Move.user == user) \
            .filter(Move.strava_activity_id != None):
        moves_by_strava_activity_id[strava_activity_id] = id
    new_strava_activities = []
    associated_strava_activities = []
    known_strava_activities = []
    client = get_strava_client(user)
    for activity in client.get_activities(before=before, after=after):
        move_id = None
        start_date = activity.start_date
        if activity.id in moves_by_strava_activity_id:
            move_id = moves_by_strava_activity_id[activity.id]
        elif start_date in moves_by_date_time:
            move_id = moves_by_date_time[start_date]
        else:
            for date_time in moves_by_date_time.keys():
                start_date_delta = abs(date_time - start_date)
                start_date_local_delta = abs(date_time - activity.start_date_local.replace(tzinfo=pytz.UTC))
                max_delta = timedelta(seconds=30)

                if start_date_delta <= max_delta or start_date_local_delta <= max_delta:
                    move_id = moves_by_date_time[date_time]
                    break

            if not move_id:
                potential_moves = []
                for date_time in moves_by_date_time.keys():
                    start_date_delta = abs(date_time - start_date)
                    if -MAX_DATE_TIME_OFFSET <= start_date_delta <= MAX_DATE_TIME_OFFSET:
                        potential_moves.append(moves_by_date_time[date_time])

                if len(potential_moves) == 1:
                    move_id = potential_moves[0]
                elif len(potential_moves) > 1:
                    # too many candidates found
                    pass

        if not move_id:
            new_strava_activities.append(activity)
        elif activity.id in moves_by_strava_activity_id:
            known_strava_activities.append(activity)
        else:
            move = Move.query.filter_by(id=move_id).one()
            move.strava_activity_id = activity.id
            db.session.commit()
            associated_strava_activities.append((activity, move))

    return associated_strava_activities, known_strava_activities, new_strava_activities

def show_stats():
    joint = query.for_field(Transaction.account).filter_by(person='')

    timespan = query.all() \
        .add_columns(func.max(Transaction.date).label('maxDate')) \
        .add_columns(func.min(Transaction.date).label('minDate'))
    datespan = timespan[0][0] - timespan[0][1]

    merchants = query.group_format(Transaction.merchant)
    top_merchants = OrderedDict()
    for key in merchants.keys()[0:20]:
        top_merchants[key] = merchants[key]

    amount_data = [
        make_template_data(query.group_format(Transaction.person), "per person"),
        make_template_data(query.group_format(Transaction.account, joint), "joint transactions by account"),
        make_template_data(query.group_format(Transaction.category), "all transactions by category"),
        make_template_data(top_merchants, "top 20 merchants")
    ]

    return flask.render_template('stats.html',
                                 datespan=datespan,
                                 number_of_days=datespan.total_seconds() / (60*60*24),
                                 number_of_months=datespan.total_seconds() / (60*60*24*30),
                                 amount_data=amount_data)

 def get_min_id(self) :
     rows = self.conn.execute(select([func.min(message.c.yammer_id)]))
     result = rows.fetchone()
     if result :
         return result[0]
     else :
         return None

def tweet_anniversaries():
    now = datetime.now()
    now_uts = time.mktime(now.timetuple())
    builders = {True: PositiveAnniversaryBuilder(),
                False: NegativeAnniversaryBuilder()}

    for user in db.session.query(User).\
                           filter(User.download_scrobbles == True,
                                  User.twitter_username != None,
                                  User.twitter_track_artist_anniversaries == True):
        max_possible_milestone = int(math.floor((now_uts - db.session.query(func.min(Scrobble.uts)).
                                                                      filter(Scrobble.user == user).
                                                                      scalar()) / (365 * 86400)))

        for positive, builder in builders.iteritems():
            for user_artist in builder.query(now, user, max_possible_milestone):
                anniversary = builder.anniversary_for(user_artist, now_uts)
                if db.session.query(Anniversary).\
                              filter(Anniversary.user == user,
                                     Anniversary.artist == user_artist.artist,
                                     Anniversary.anniversary == anniversary,
                                     Anniversary.positive == positive).\
                              first() is None:
                    a = Anniversary()
                    a.user = user
                    a.artist = user_artist.artist
                    a.anniversary = anniversary
                    a.positive = positive
                    db.session.add(a)

                    post_tweet(user, builder.anniversary_tweet(user_artist, anniversary))

    db.session.commit()

def daily_base_query():
    dt = date_col()
    date = func.date(dt)
    dm = func.min(date).label("date")
    # base query, daily means
    query_by_day = select([dm]).group_by(date)
    query_by_day = query_by_day.order_by(dt)
    return query_by_day, dt