def summarise(cls, start_ts=0, end_ts=0):
        """"Gets usage from their snapshots between start_ts and end_ts.

        Maximal usage of the period is returned.
        """
        id_query = Snapshot.id_between(start_ts, end_ts)

        query = cls.query.filter(cls.snapshot_id.in_(id_query)).\
            group_by(cls.virtual_volume_id, cls.owner_id).\
            with_entities(cls.virtual_volume_id, cls.owner_id,
                          func.max(cls.quota).label('quota'),
                          func.max(cls.files).label('files'),
                          func.max(cls.usage).label('usage'))

        fq = VirtualVolume.query.join(Filesystem).\
            with_entities(VirtualVolume.id, Filesystem.name, VirtualVolume.name).all()
        file_systems = {}
        for fs in fq:
            file_systems[fs[0]] = fs[1:]

        # Not all virtual volumes has owner
        owners = dict(Owner.query.with_entities(Owner.id, Owner.name).all())

        fields = ['filesystem', 'virtual_volume', 'owner', 'quota', 'files', 'usage']
        rslt = []

        for q in query.all():
            fn, vn = file_systems[q[0]]
            owner = owners[q[1]] if q[1] else ''
            mappings = (fn, vn, owner, q[2], q[3], q[4])
            rslt.append(dict(zip(fields, mappings)))
        return rslt

    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 summarise(cls, start_ts=0, end_ts=0):
        """"Gets usage from their snapshots between start_ts and end_ts.

        Maximal usage of the period is returned.
        Grouped by filesystem.
        """
        id_query = Snapshot.id_between(start_ts, end_ts)

        # 1. soft and hard quotas are not changed very often
        # 2. Record number of Host, Filesystem and Owner are relatively very small,
        # link them in code to avoid expand usage rows whose number is very very high
        query = Usage.query.filter(Usage.snapshot_id.in_(id_query)).\
            group_by(Usage.filesystem_id).\
            with_entities(Usage.filesystem_id,
                          func.max(Usage.soft).label('soft'),
                          func.max(Usage.hard).label('hard'),
                          func.max(Usage.usage).label('usage'))

        fq = Filesystem.query.join(Host).\
            with_entities(Filesystem.id, Host.name, Filesystem.name).all()
        file_systems = {}
        for fs in fq:
            file_systems[fs[0]] = fs[1:]

        fields = ['host', 'filesystem', 'soft', 'hard', 'usage']
        rslt = []

        for q in query.all():
            hn, fn = file_systems[q[0]]
            mappings = (hn, fn, q[1], q[2], q[3])
            rslt.append(dict(zip(fields, mappings)))
        return rslt

    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 __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 __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 mls_draft_format(self, dtype):
        """
        Return number of rounds in draft, given draft type.

        :param dtype: Draft type (AcquisitionType)
        :return:
        """
        return self.session.query(func.max(PlayerDrafts.round), func.max(PlayerDrafts.selection)).filter(
            PlayerDrafts.path == dtype, PlayerDrafts.year == self.year).one()

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 annotation_object_next(self, current_frame, current_annotation_object=None, to_future=True):
        """
        When current_annotation_object is None, nearest AnnotationObject in the future (in respect to current_frame) is returned.
        With current_annotation_object given, "next" AnnotationObject is returned, i.e. an object with higher ID in the current frame
        or the nearest in the future.

        If to_future is false, the search is done in the past instead of the future.

        Returned tuple contains the next object and its starting and ending frame.

        :rtype: (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)

        if to_future:
            if current_annotation_object is None:
                # nearest AnnotationObject in future
                q = q.having(func.min(AnnotationValue.frame_from) > current_frame)
                q = q.order_by(func.min(AnnotationValue.frame_from), AnnotationObject.id)
            else:
                # nearest AnnotationObject in future, or in the same frame with bigger ID
                current_id = current_annotation_object.id
                q = q.having( (func.min(AnnotationValue.frame_from) > current_frame) |
                              ((AnnotationObject.id > current_id) & (func.min(AnnotationValue.frame_from) <= current_frame) & (func.max(AnnotationValue.frame_from) >= current_frame))
                            )
                q = q.order_by(func.min(AnnotationValue.frame_from), AnnotationObject.id)
        else:
            if current_annotation_object is None:
                # nearest AnnotationObject in past
                q = q.having(func.max(AnnotationValue.frame_from) < current_frame)
                q = q.order_by(desc(func.max(AnnotationValue.frame_from)), desc(AnnotationObject.id))
            else:
                # nearest AnnotationObject in past, or in the same frame with lower ID
                current_id = current_annotation_object.id
                q = q.having( (func.max(AnnotationValue.frame_from) < current_frame) |
                              ((AnnotationObject.id < current_id) & (func.min(AnnotationValue.frame_from) <= current_frame) & (func.max(AnnotationValue.frame_from) >= current_frame))
                            )
                q = q.order_by(desc(func.max(AnnotationValue.frame_from)), desc(AnnotationObject.id))

        q = q.limit(1)
        q = q.all()

        if len(q) < 1:
            return None

        if len(q) > 1:
            raise Exception('Returned collection cannot contain more than 1 item.')

        return q[0]

 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 end(self):
        today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
        # Adjust idColumn
        maxId = db.getSession().query(func.max(Task.id)).one()[0]
        self.idColumn.width = max(2, len(str(maxId)))

        # Adjust titleColumn
        self.titleColumn.width = self.maxTitleWidth
        totalWidth = sum([x.width for x in self.columns]) + len(self.columns)
        if totalWidth >= self.termWidth:
            self.titleColumn.width -= (totalWidth - self.termWidth) + len(self.columns)
        self.titleColumn.formater = TitleFormater(self.titleColumn.width, self.cryptoMgr)

        # Print table
        for sectionName, taskList in self.taskLists:
            dateSplitters = [(1, "day"), (7, "week"), (30, "month"), (30 * 4, "quarter"), (365, "year")]
            splitterRange, splitterName = dateSplitters.pop()
            splitterText = None
            self._renderTaskListHeader(sectionName)
            for task in taskList:
                while self.splitOnDate and task.creationDate > today - timedelta(splitterRange):
                    splitterText = "Last %s" % splitterName
                    if len(dateSplitters) > 0:
                        splitterRange, splitterName = dateSplitters.pop()
                    else:
                        self.splitOnDate = False

                if splitterText:
                    print(C.GREEN + splitterText.center(totalWidth) + C.RESET, file=self.out)
                    splitterText = None

                self._renderTaskListRow(task)

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 extractData(self):
		self.logger.info('Connecting to database')
		engine = create_engine(self.config['database'])
		meta = MetaData()
		meta.bind = engine
		self.job = Table('job', meta, autoload=True)
		self.job_status = Table('job_status', meta, autoload=True)
		self.job_status_type_description = Table('job_status_type_description', meta, autoload=True)

		job=self.job
		job_status=self.job_status

		s=self.job_status_type_description.select()
		self.type_db_values = s.execute().fetchall()

		self.queue_db_values = self.getJobSummary(job.c.queue)
		self.user_db_values = self.getJobSummary(job.c.userId, job.c.localUser)
		self.node_db_values = self.getJobSummary(job.c.workerNode)

		self.logger.info('Generating job list')
		node_job_status = self.config['node_job_status'].split(',')

		maximum = func.max(self.job_status.c.id).label('m')
		s1 = select([maximum]).group_by(job_status.c.jobId).alias('a')
		s2 = select([job.c.id, job.c.lrmsAbsLayerJobId, job.c.workerNode, job_status.c.type,
			job_status.c.time_stamp]).select_from(job.join(job_status).join(s1,job_status.c.id==text('m'))). \
			where(and_(job_status.c.type.in_(node_job_status)))

		self.job_db_values = s2.execute().fetchall()

		return {}

    def __init__(self, sess, unfiltered, filt_crit, tt, window_size=WINDOW_SIZE):
        self.sess = sess
        self.unfiltered = unfiltered
        self.filt_crit = filt_crit
        self.tt = tt
        self.window_size = window_size

        self.skipped = []

        # select-only, can't be used for updates
        self.filtered_s = filtered = select(unfiltered.c).where(filt_crit).alias("filtered")

        self.selectable = (
            select(
                [
                    filtered.c.size,
                    func.count().label("inode_count"),
                    func.max(filtered.c.has_updates).label("has_updates"),
                ]
            )
            .group_by(filtered.c.size)
            .having(and_(literal_column("inode_count") > 1, literal_column("has_updates") > 0))
        )

        # This is higher than selectable.first().size, in order to also clear
        # updates without commonality.
        self.upper_bound = self.sess.query(self.unfiltered.c.size).order_by(-self.unfiltered.c.size).limit(1).scalar()

    def summary(self):
        subquery = (
            self.db.query(
                SQLPackage.name,
                func.max(SQLPackage.last_modified).label("last_modified"),
            )
            .group_by(SQLPackage.name)
            .subquery()
        )
        rows = self.db.query(
            SQLPackage.name, SQLPackage.last_modified, SQLPackage.summary
        ).filter(
            (SQLPackage.name == subquery.c.name)
            & (SQLPackage.last_modified == subquery.c.last_modified)
        )

        # Dedupe because two packages may share the same last_modified
        seen_packages = set()
        packages = []
        for row in rows:
            if row[0] in seen_packages:
                continue
            seen_packages.add(row[0])
            packages.append(
                {"name": row[0], "last_modified": row[1], "summary": row[2]}
            )
        return packages

def _last_assoc_timestamps(session, dataset):
    """
    Get the timestamps of the latest assocxtrc per runningcatalog and band.

    We can't get the assoc ID's directly, because they are unique and can't
    by put in the group by. You can get the eventual assoc ID's by joining
    this query again with the assoc table (see last_assoc_per_band func)

    args:
        session (session): A SQLAlchemy session
        dataset (Dataset): A SQLALchemy dataset model

    returns: a SQLAlchemy subquery containing  runcat id, timestamp, band id
    """
    a = aliased(Assocxtrsource, name='a_timestamps')
    e = aliased(Extractedsource, name='e_timestamps')
    r = aliased(Runningcatalog, name='r_timestamps')
    i = aliased(Image, name='i_timestamps')
    return session.query(r.id.label('runcat'),
                         func.max(i.taustart_ts).label('max_time'),
                         i.band_id.label('band')
                         ). \
        select_from(r). \
        join(a, r.id == a.runcat_id). \
        join(e, a.xtrsrc_id == e.id). \
        join(i, i.id == e.image_id). \
        group_by(r.id, i.band_id). \
        filter(i.dataset == dataset). \
        subquery(name='last_assoc_timestamps')

def upload_media():
    uploaded_file = request.files.getlist('file')[0]
    filename = secure_filename(uploaded_file.filename)
    track_id = request.form['track_id']
    upload_directory = "{}/{}".format("media", str(request.form['track_id']))

    track = ContentTrack.query.filter_by(id=track_id).first_or_404()

    max_order = db.session.query(
        func.max(ContentUploads.order).label("max_order")
    ).filter(ContentUploads.track_id == track.id).one().max_order

    file_data = {}
    file_data['uploaded_by'] = current_user.id
    file_data['name'] = filename
    file_data['type_id'] = track.type_id
    file_data['track_id'] = track_id

    if max_order:
        file_data['order'] = max_order + 1
    else:
        file_data['order'] = 1

    file_data['uri'] = save_uploaded_file(uploaded_file, upload_directory, filename)

    content_media = ContentUploads(**file_data)  # create new object from data

    db.session.add(content_media)
    db.session.commit()

    return json.dumps(file_data)

    def get_biggest_donations(cls, limit=None, offset=None):
        """Getter for biggest donations.

        Donations from the same person are grouped.

        Args:
            limit: Maximum number of donations to be returned.
            offset: Offset of the result.

        Returns:
            Tuple with two items. First is total number if donations. Second
            is a list of donations sorted by amount with a specified offset.
        """
        query = db.session.query(
            cls.first_name.label("first_name"),
            cls.last_name.label("last_name"),
            cls.editor_name.label("editor_name"),
            func.max(cls.payment_date).label("payment_date"),
            func.sum(cls.amount).label("amount"),
            func.sum(cls.fee).label("fee"),
        )
        query = query.filter(cls.anonymous == False)
        query = query.group_by(cls.first_name, cls.last_name, cls.editor_name)
        query = query.order_by(desc("amount"))
        count = query.count()  # Total count should be calculated before limits
        if limit is not None:
            query = query.limit(limit)
        if offset is not None:
            query = query.offset(offset)
        return count, query.all()

def matching_from_cliche_wikipedia_edges():
    # assume there are a few cliche-wikipedia edges

    with app.app_context():
        with session.begin():
            session.query(ClicheWikipediaEdge).update({'available': True})

            while True:
                max_conf = session \
                    .query(func.max(ClicheWikipediaEdge.confidence)) \
                    .filter_by(available=True) \
                    .scalar()
                if not max_conf:
                    break

                matching = session \
                    .query(ClicheWikipediaEdge) \
                    .filter_by(confidence=max_conf, available=True) \
                    .first()

                cliche_work = matching.cliche_work
                wikipedia_work = matching.wikipedia_work

                session.query(ClicheWikipediaEdge) \
                       .filter_by(cliche_work=cliche_work,
                                  available=True) \
                       .update({'available': False})

                session.query(ClicheWikipediaEdge) \
                       .filter_by(wikipedia_work=wikipedia_work,
                                  available=True) \
                       .update({'available': False})

                yield matching

 def set_order_to_last(self, context):
     """Set order of the line to maximum rank + 1."""
     self._set_order(
         self.query.session.query(
             func.coalesce(func.max(self._order_column), 0))
         .filter(context)
         .as_scalar() + 1)