예제 #1
0
def get_latest_releases(series_ids):

    query = (
        Releases.query.with_entities(Releases.series, Releases.volume, Releases.chapter)
        .order_by(Releases.series)
        .order_by(nullslast(desc(Releases.volume)))
        .order_by(nullslast(desc(Releases.chapter)))
        .distinct(Releases.series)
        .filter(Releases.series.in_(series_ids))
        .filter(Releases.include == True)
    )

    latest = query.all()

    ret = {}
    for series, vol, chp in latest:
        if vol == None:
            vol = -1
        if chp == None:
            chp = -1
        if series in ret:
            if vol > ret[series][0]:
                ret[series] = [vol, chp]
            elif vol == ret[series][0] and chp > ret[series][1]:
                ret[series] = [vol, chp]

        else:
            ret[series] = [vol, chp]

            # Fill out any items which returned nothing.
    for sid in series_ids:
        if not sid in ret:
            ret[sid] = [-1, -1]

    return ret
예제 #2
0
def get_latest_release(series):
    latest = (
        Releases.query.filter(Releases.series == series.id)
        .filter(Releases.include == True)
        .order_by(nullslast(desc(Releases.volume)))
        .order_by(nullslast(desc(Releases.chapter)))
        .limit(1)
        .scalar()
    )

    return latest
예제 #3
0
def get_latest_release(series):
	latest = Releases                                         \
				.query                                        \
				.filter(Releases.series==series.id)           \
				.filter(Releases.include==True)               \
				.order_by(nullslast(desc(Releases.volume)))   \
				.order_by(nullslast(desc(Releases.chapter)))  \
				.order_by(nullslast(desc(Releases.fragment))) \
				.limit(1)                                     \
				.scalar()

	return latest
예제 #4
0
def get_latest_release(series):
    latest = Releases                                         \
       .query                                        \
       .filter(Releases.series==series.id)           \
       .filter(Releases.include==True)               \
       .order_by(nullslast(desc(Releases.volume)))   \
       .order_by(nullslast(desc(Releases.chapter)))  \
       .order_by(nullslast(desc(Releases.fragment))) \
       .limit(1)                                     \
       .scalar()

    return latest
예제 #5
0
    def data(self, *args, **kw):
        security = get_service('security')
        length = int(kw.get("iDisplayLength", 0))
        start = int(kw.get("iDisplayStart", 0))
        sort_dir = kw.get("sSortDir_0", "asc")
        echo = int(kw.get("sEcho", 0))
        search = kw.get("sSearch", "").replace("%", "").strip().lower()

        end = start + length
        query = Group.query \
            .options(sa.orm.noload('*'))
        total_count = query.count()

        if search:
            # TODO: gérer les accents
            query = query.filter(
                func.lower(Group.name).like("%" + search + "%"),
            )

        count = query.count()
        columns = [func.lower(Group.name)]
        direction = asc if sort_dir == 'asc' else desc
        order_by = list(map(direction, columns))

        # sqlite does not support 'NULLS FIRST|LAST' in ORDER BY clauses
        engine = query.session.get_bind(Group.__mapper__)
        if engine.name != 'sqlite':
            order_by[0] = nullslast(order_by[0])

        query = query.order_by(*order_by) \
            .add_columns(Group.members_count)
        groups = query.slice(start, end).all()
        data = []

        for group, members_count in groups:
            # TODO: this should be done on the browser.
            group_url = url_for(".groups_group", group_id=group.id)
            name = escape(getattr(group, "name") or "")
            roles = [r for r in security.get_roles(group) if r.assignable]

            columns = [
                '<a href="{url}">{name}</a>'.format(url=group_url, name=name),
                text_type(members_count or 0),
                render_template_string(
                    '''{%- for role in roles %}
                        <span class="badge badge-default">{{ role }}</span>
                        {%- endfor %}''',
                    roles=roles,
                ),
                '\u2713' if group.public else '',
            ]

            data.append(columns)

        return {
            "sEcho": echo,
            "iTotalRecords": total_count,
            "iTotalDisplayRecords": count,
            "aaData": data,
        }
예제 #6
0
def test_oc():
    a = asc("a")
    b = desc("a")
    c = asc("b")
    n = nullslast(desc("a"))

    a = OC(a)
    b = OC(b)
    c = OC(c)
    n = OC(n)

    assert str(a) == str(OC("a"))
    assert a.is_ascending
    assert not b.is_ascending
    assert not n.reversed.reversed.is_ascending
    assert n.reversed.is_ascending
    assert not n.is_ascending  # make sure reversed doesn't modify in-place
    assert str(a.element) == str(b.element) == str(n.element)
    assert str(a) == str(b.reversed)
    assert str(n.reversed.reversed) == str(n)

    assert a.name == "a"
    assert n.name == "a"
    assert n.quoted_full_name == "a"
    assert repr(n) == "<OC: a DESC NULLS LAST>"
예제 #7
0
파일: rest.py 프로젝트: yaelmi3/backslash
 def _build_sort_expr(self, model, field_name):
     returned = getattr(model, field_name)
     if field_name in self.INVERSE_SORTS:
         returned = nullslast(returned.desc())
     else:
         returned = returned.asc()
     return returned
예제 #8
0
    def circulation_events(self):
        annotator = AdminAnnotator(self.circulation)
        num = min(int(flask.request.args.get("num", "100")), 500)

        results = self._db.query(CirculationEvent) \
            .join(LicensePool) \
            .join(Work) \
            .join(DataSource) \
            .join(Identifier) \
            .order_by(nullslast(desc(CirculationEvent.start))) \
            .limit(num) \
            .all()

        events = map(lambda result: {
            "id": result.id,
            "type": result.type,
            "patron_id": result.foreign_patron_id,
            "time": result.start,
            "book": {
                "title": result.license_pool.work.title,
                "url": annotator.permalink_for(result.license_pool.work, result.license_pool, result.license_pool.identifier)
            }
        }, results)

        return dict({ "circulation_events": events })
예제 #9
0
    def data(self, *args, **kw) -> Dict:
        security = get_service("security")
        length = int(kw.get("iDisplayLength", 0))
        start = int(kw.get("iDisplayStart", 0))
        sort_dir = kw.get("sSortDir_0", "asc")
        echo = int(kw.get("sEcho", 0))
        search = kw.get("sSearch", "").replace("%", "").strip().lower()

        end = start + length
        # pyre-fixme[16]: `Group` has no attribute `query`.
        query = Group.query.options(sa.orm.noload("*"))
        total_count = query.count()

        if search:
            # TODO: gérer les accents
            query = query.filter(
                func.lower(Group.name).like("%" + search + "%"))

        count = query.count()
        columns = [func.lower(Group.name)]
        direction = asc if sort_dir == "asc" else desc
        order_by = list(map(direction, columns))

        # sqlite does not support 'NULLS FIRST|LAST' in ORDER BY clauses
        # pyre-fixme[16]: `Group` has no attribute `__mapper__`.
        engine = query.session.get_bind(Group.__mapper__)
        if engine.name != "sqlite":
            order_by[0] = nullslast(order_by[0])

        query = query.order_by(*order_by).add_columns(Group.members_count)
        groups = query.slice(start, end).all()
        data = []

        for group, members_count in groups:
            # TODO: this should be done on the browser.
            group_url = url_for(".groups_group", group_id=group.id)
            name = html.escape(group.name or "")
            # pyre-fixme[16]: `Service` has no attribute `get_roles`.
            roles = [r for r in security.get_roles(group) if r.assignable]

            columns = [
                f'<a href="{group_url}">{name}</a>',
                str(members_count or 0),
                render_template_string(
                    """{%- for role in roles %}
                        <span class="badge badge-default">{{ role }}</span>
                        {%- endfor %}""",
                    roles=roles,
                ),
                "\u2713" if group.public else "",
            ]

            data.append(columns)

        return {
            "sEcho": echo,
            "iTotalRecords": total_count,
            "iTotalDisplayRecords": count,
            "aaData": data,
        }
예제 #10
0
파일: order_by.py 프로젝트: ophiradi/dart
    def apply_order_by(self, order_by, query, dao, schemas):
        dir_fn = self._dir_fn(order_by)
        if order_by.key in ['id', 'version_id', 'created', 'updated']:
            field = getattr(dao, order_by.key)
            return query.order_by(nullslast(dir_fn(field)))

        field = dao.data[order_by.key]
        path_keys = order_by.key.split('.')
        type_ = 'string'
        for schema in schemas:
            result = self._get_type(path_keys, schema)
            if result:
                type_ = result
                break
        cast = _pg_cast(type_)
        dir_fn = self._dir_fn(order_by)
        return query.order_by(nullslast(dir_fn(cast(field))))
예제 #11
0
    def apply_order_by(self, order_by, query, dao, schemas):
        dir_fn = self._dir_fn(order_by)
        if order_by.key in ['id', 'version_id', 'created', 'updated']:
            field = getattr(dao, order_by.key)
            return query.order_by(nullslast(dir_fn(field)))

        field = dao.data[order_by.key]
        path_keys = order_by.key.split('.')
        type_ = 'string'
        for schema in schemas:
            result = self._get_type(path_keys, schema)
            if result:
                type_ = result
                break
        cast = _pg_cast(type_)
        dir_fn = self._dir_fn(order_by)
        return query.order_by(nullslast(dir_fn(cast(field))))
예제 #12
0
파일: action.py 프로젝트: aophir-rmn/dart
    def _find_action_query(datastore_id=None,
                           datastore_state=None,
                           gt_order_idx=None,
                           limit=None,
                           action_type_names=None,
                           states=None,
                           workflow_id=None,
                           workflow_instance_id=None,
                           order_by=None,
                           offset=None):
        query = ActionDao.query
        if datastore_id:
            query = query.join(
                DatastoreDao,
                DatastoreDao.id == ActionDao.data['datastore_id'].astext)
            query = query.filter(DatastoreDao.id == datastore_id)
            query = query.filter(DatastoreDao.data['state'].astext ==
                                 datastore_state) if datastore_state else query
        query = query.filter(
            ActionDao.data['state'].astext.in_(states)) if states else query
        query = query.filter(ActionDao.data['action_type_name'].astext.in_(
            action_type_names)) if action_type_names else query
        query = query.filter(
            ActionDao.data['order_idx'].cast(Float) > gt_order_idx
        ) if gt_order_idx else query
        query = query.filter(ActionDao.data['workflow_id'].astext ==
                             workflow_id) if workflow_id else query
        query = query.filter(
            ActionDao.data['workflow_instance_id'].astext ==
            workflow_instance_id) if workflow_instance_id else query

        if order_by:
            for field, direction in order_by:
                if direction == 'desc':
                    query = query.order_by(
                        nullslast(desc(ActionDao.data[field].astext)))
                else:
                    query = query.order_by(
                        nullslast(ActionDao.data[field].astext))
        else:
            query = query.order_by(ActionDao.data['order_idx'].cast(Float))
            query = query.order_by(ActionDao.created)
        query = query.limit(limit) if limit else query
        query = query.offset(offset) if offset else query
        return query
예제 #13
0
파일: v2.py 프로젝트: Code4SA/pmg-cms-2
def committee_tabled_reports(id):
    cte = Committee.query.get(id)
    if not cte:
        abort(404)

    query = TabledCommitteeReport.query\
        .filter(TabledCommitteeReport.committee == cte)\
        .order_by(nullslast(desc(TabledCommitteeReport.start_date)))
    return api_list_items(query, TabledCommitteeReportSchema)
예제 #14
0
def committee_tabled_reports(id):
    cte = Committee.query.get(id)
    if not cte:
        abort(404)

    query = TabledCommitteeReport.query\
        .filter(TabledCommitteeReport.committee == cte)\
        .order_by(nullslast(desc(TabledCommitteeReport.start_date)))
    return api_list_items(query, TabledCommitteeReportSchema)
예제 #15
0
def test_warn_on_nullslast():
    with warns(UserWarning):
        ob = [OC(nullslast(column("id")))]
        Paging(T1,
               10,
               ob,
               backwards=False,
               current_marker=None,
               get_marker=getitem)
예제 #16
0
파일: views.py 프로젝트: debon/abilian-core
  def data(self, *args, **kw):
    security = current_app.services['security']
    length = int(kw.get("iDisplayLength", 0))
    start = int(kw.get("iDisplayStart", 0))
    sort_dir = kw.get("sSortDir_0", "asc")
    echo = int(kw.get("sEcho", 0))
    search = kw.get("sSearch", "").replace("%", "").strip().lower()

    end = start + length
    q = Group.query \
      .options(sa.orm.noload('*'))
    total_count = q.count()

    if search:
      # TODO: gérer les accents
      q = q.filter(func.lower(Group.name).like("%" + search + "%"))

    count = q.count()
    columns = [func.lower(Group.name)]
    direction = asc if sort_dir == 'asc' else desc
    order_by = map(direction, columns)

    # sqlite does not support 'NULLS FIRST|LAST' in ORDER BY clauses
    engine = q.session.get_bind(Group.__mapper__)
    if engine.name != 'sqlite':
      order_by[0] = nullslast(order_by[0])

    q = q.order_by(*order_by) \
      .add_columns(Group.members_count)
    groups = q.slice(start, end).all()
    data = []

    for group, members_count in groups:
      # TODO: this should be done on the browser.
      group_url = url_for(".groups_group", group_id=group.id)
      name = escape(getattr(group, "name") or "")
      roles = [r for r in security.get_roles(group) if r.assignable]
      columns = []
      columns.append(
        u'<a href="{url}">{name}</a>'.format(url=group_url, name=name)
      )
      columns.append(unicode(members_count or 0))
      columns.append(render_template_string(
        u'''{%- for role in roles %}
            <span class="badge badge-default">{{ role }}</span>
            {%- endfor %}''',
        roles=roles))
      columns.append(u'\u2713' if group.public else u'')
      data.append(columns)

    return {
      "sEcho": echo,
      "iTotalRecords": total_count,
      "iTotalDisplayRecords": count,
      "aaData": data,
    }
예제 #17
0
    def data(self, *args, **kw):
        security = get_service("security")
        length = int(kw.get("iDisplayLength", 0))
        start = int(kw.get("iDisplayStart", 0))
        sort_dir = kw.get("sSortDir_0", "asc")
        echo = int(kw.get("sEcho", 0))
        search = kw.get("sSearch", "").replace("%", "").strip().lower()

        end = start + length
        query = Group.query.options(sa.orm.noload("*"))
        total_count = query.count()

        if search:
            # TODO: gérer les accents
            query = query.filter(func.lower(Group.name).like("%" + search + "%"))

        count = query.count()
        columns = [func.lower(Group.name)]
        direction = asc if sort_dir == "asc" else desc
        order_by = list(map(direction, columns))

        # sqlite does not support 'NULLS FIRST|LAST' in ORDER BY clauses
        engine = query.session.get_bind(Group.__mapper__)
        if engine.name != "sqlite":
            order_by[0] = nullslast(order_by[0])

        query = query.order_by(*order_by).add_columns(Group.members_count)
        groups = query.slice(start, end).all()
        data = []

        for group, members_count in groups:
            # TODO: this should be done on the browser.
            group_url = url_for(".groups_group", group_id=group.id)
            name = html.escape(group.name or "")
            roles = [r for r in security.get_roles(group) if r.assignable]

            columns = [
                f'<a href="{group_url}">{name}</a>',
                str(members_count or 0),
                render_template_string(
                    """{%- for role in roles %}
                        <span class="badge badge-default">{{ role }}</span>
                        {%- endfor %}""",
                    roles=roles,
                ),
                "\u2713" if group.public else "",
            ]

            data.append(columns)

        return {
            "sEcho": echo,
            "iTotalRecords": total_count,
            "iTotalDisplayRecords": count,
            "aaData": data,
        }
예제 #18
0
def get_latest_releases(series_ids):

	query = Releases                                                               \
				.query                                                             \
				.with_entities(Releases.series, Releases.volume, Releases.chapter, Releases.fragment, Releases.published) \
				.order_by(Releases.series)                                         \
				.order_by(nullslast(desc(Releases.volume)))                        \
				.order_by(nullslast(desc(Releases.chapter)))                       \
				.order_by(nullslast(desc(Releases.fragment)))                      \
				.distinct(Releases.series)                                         \
				.filter(Releases.series.in_(series_ids))                           \
				.filter(Releases.include==True)

	latest = query.all()



	ret = {}
	for series, vol, chp, frag, pubdate in latest:
		if vol == None:
			vol = -1
		if chp == None:
			chp = -1
		if frag == None:
			frag = -1
		if series in ret:
			if vol > ret[series][0]:
				ret[series][0] = (vol, chp, frag)
			elif vol == ret[series][0] and chp > ret[series][1]:
				ret[series][0] = (vol, chp, frag)

			if ret[series][1] < pubdate:
				ret[series][1] = pubdate

		else:
			ret[series] = [(vol, chp, frag), pubdate]

	# Fill out any items which returned nothing.
	for sid in series_ids:
		if not sid in ret:
			ret[sid] = [(-1, -1, -1), None]

	return ret
예제 #19
0
 def version_at_time_q(cls, base_id, timestamp, db=None):
     db = db or cls.default_db
     # Version that can be used without first
     # return db.query(cls).distinct(cls.base_id).filter(
     #     cls.base_id == self.base_id,
     #     (cls.tombstone_date == None) || (cls.tombstone_date > timestamp)
     #     ).order_by(cls.base_id, nullslast(asc(cls.tombstone_date)))
     return db.query(cls).filter(cls.base_id == base_id,
                                 (cls.tombstone_date == None) |
                                 (cls.tombstone_date > timestamp)).order_by(
                                     nullslast(asc(cls.tombstone_date)))
예제 #20
0
파일: dal.py 프로젝트: kotenev/mybookshelf2
async def get_existing_conversion(ebook_id, user_id, to_format):
    format_id = await get_format_id(to_format)
    async with engine.acquire() as conn:
        source = model.Source.__table__
        conversion = model.Conversion.__table__
        res = await conn.execute(select([conversion.c.id]).select_from(conversion.join(source))\
                           .where(and_(source.c.ebook_id == ebook_id,
                                       conversion.c.created_by_id == user_id,
                                       conversion.c.format_id == format_id))\
                           .order_by(nullslast(desc(source.c.quality))))
        return await res.scalar()
예제 #21
0
def get_latest_releases(series_ids):

    query = Releases                                                               \
       .query                                                             \
       .with_entities(Releases.series, Releases.volume, Releases.chapter, Releases.fragment, Releases.published) \
       .order_by(Releases.series)                                         \
       .order_by(nullslast(desc(Releases.volume)))                        \
       .order_by(nullslast(desc(Releases.chapter)))                       \
       .order_by(nullslast(desc(Releases.fragment)))                      \
       .distinct(Releases.series)                                         \
       .filter(Releases.series.in_(series_ids))                           \
       .filter(Releases.include==True)

    latest = query.all()

    ret = {}
    for series, vol, chp, frag, pubdate in latest:
        if vol == None:
            vol = -1
        if chp == None:
            chp = -1
        if frag == None:
            frag = -1
        if series in ret:
            if vol > ret[series][0]:
                ret[series][0] = (vol, chp, frag)
            elif vol == ret[series][0] and chp > ret[series][1]:
                ret[series][0] = (vol, chp, frag)

            if ret[series][1] < pubdate:
                ret[series][1] = pubdate

        else:
            ret[series] = [(vol, chp, frag), pubdate]

    # Fill out any items which returned nothing.
    for sid in series_ids:
        if not sid in ret:
            ret[sid] = [(-1, -1, -1), None]

    return ret
예제 #22
0
파일: util.py 프로젝트: d-demirci/indico
def build_user_search_query(criteria,
                            exact=False,
                            include_deleted=False,
                            include_pending=False,
                            include_blocked=False,
                            favorites_first=False):
    unspecified = object()
    query = User.query.distinct(User.id).options(
        db.joinedload(User._all_emails))

    if not include_pending:
        query = query.filter(~User.is_pending)
    if not include_deleted:
        query = query.filter(~User.is_deleted)
    if not include_blocked:
        query = query.filter(~User.is_blocked)

    affiliation = criteria.pop('affiliation', unspecified)
    if affiliation is not unspecified:
        query = query.join(UserAffiliation).filter(
            unaccent_match(UserAffiliation.name, affiliation, exact))

    email = criteria.pop('email', unspecified)
    if email is not unspecified:
        query = query.join(UserEmail).filter(
            unaccent_match(UserEmail.email, email, exact))

    # search on any of the name fields (first_name OR last_name)
    name = criteria.pop('name', unspecified)
    if name is not unspecified:
        if exact:
            raise ValueError("'name' is not compatible with 'exact'")
        if 'first_name' in criteria or 'last_name' in criteria:
            raise ValueError("'name' is not compatible with (first|last)_name")
        query = query.filter(_build_name_search(name.replace(',', '').split()))

    for k, v in criteria.items():
        query = query.filter(unaccent_match(getattr(User, k), v, exact))

    # wrap as subquery so we can apply order regardless of distinct-by-id
    query = query.from_self()

    if favorites_first:
        query = (query.outerjoin(
            favorite_user_table,
            db.and_(favorite_user_table.c.user_id == session.user.id,
                    favorite_user_table.c.target_id == User.id)).order_by(
                        nullslast(favorite_user_table.c.user_id)))
    query = query.order_by(
        db.func.lower(db.func.indico.indico_unaccent(User.first_name)),
        db.func.lower(db.func.indico.indico_unaccent(User.last_name)), User.id)
    return query
예제 #23
0
파일: action.py 프로젝트: jsteigs642/dart
 def _find_action_query(datastore_id=None, datastore_state=None, gt_order_idx=None, limit=None, action_type_names=None, states=None, workflow_id=None, order_by=None, offset=None):
     query = ActionDao.query
     if datastore_id:
         query = query.join(DatastoreDao, DatastoreDao.id == ActionDao.data['datastore_id'].astext)
         query = query.filter(DatastoreDao.id == datastore_id)
         query = query.filter(DatastoreDao.data['state'].astext == datastore_state) if datastore_state else query
     query = query.filter(ActionDao.data['state'].astext.in_(states)) if states else query
     query = query.filter(ActionDao.data['action_type_name'].astext.in_(action_type_names)) if action_type_names else query
     query = query.filter(ActionDao.data['order_idx'].cast(Float) > gt_order_idx) if gt_order_idx else query
     query = query.filter(ActionDao.data['workflow_id'].astext == workflow_id) if workflow_id else query
     if order_by:
         for field, direction in order_by:
             if direction == 'desc':
                 query = query.order_by(nullslast(desc(ActionDao.data[field].astext)))
             else:
                 query = query.order_by(nullslast(ActionDao.data[field].astext))
     else:
         query = query.order_by(ActionDao.data['order_idx'].cast(Float))
         query = query.order_by(ActionDao.created)
     query = query.limit(limit) if limit else query
     query = query.offset(offset) if offset else query
     return query
예제 #24
0
async def get_existing_conversion(ebook_id, user_id, to_format):
    format_id = await get_format_id(to_format)
    async with engine.acquire() as conn:
        source = model.Source.__table__
        conversion = model.Conversion.__table__
        res = await conn.execute(select([conversion.c.id]).select_from(conversion.join(source))\
                           .where(and_(source.c.ebook_id == ebook_id,
                                       conversion.c.created_by_id == user_id,
                                       conversion.c.format_id == format_id))\
                           .order_by(nullslast(desc(source.c.quality)))) 
        return await res.scalar()

        
예제 #25
0
def reln_in_history(self, name, timestamp):
    """read a relation at a given timestamp

    monkey-patched as a method of Base in modules.__init__"""
    my_cls = self.__class__
    reln = my_cls.__mapper__.relationships.get(name, None)
    if not reln:
        # AssociationProxy
        raise NotImplementedError()
    if reln.secondary:
        raise NotImplementedError()
    target_cls = reln.mapper.class_
    if not (issubclass(target_cls, (OriginMixin, TombstonableMixin))
            or isinstance(my_cls, (OriginMixin, TombstonableMixin))):
        return getattr(self, name)
    h = Dehistoricizer(target_cls, my_cls)
    join_condition = h.traverse(reln.primaryjoin)
    if isinstance(self, HistoryMixin):
        filter = my_cls.base_id == self.base_id
    else:
        filter = my_cls.id == self.id
    if isinstance(self, TombstonableMixin):
        filter = filter & ((my_cls.tombstone_date == None) |
                           (my_cls.tombstone_date > timestamp))
    if isinstance(self, OriginMixin):
        filter = filter & (my_cls.creation_date <= timestamp)
    if issubclass(target_cls, TombstonableMixin):
        filter = filter & ((target_cls.tombstone_date == None) |
                           (target_cls.tombstone_date > timestamp))
    if issubclass(target_cls, OriginMixin):
        filter = filter & (target_cls.creation_date <= timestamp)
    if issubclass(target_cls, HistoryMixin):
        results = self.db.query(target_cls).distinct(target_cls.base_id).join(
            my_cls, join_condition).filter(filter).order_by(
                target_cls.base_id,
                nullslast(asc(target_cls.tombstone_date))).all()
    else:
        results = self.db.query(target_cls).join(
            my_cls, join_condition).filter(filter).all()
    if reln.uselist:
        return results
    else:
        assert len(results) <= 1
        if results:
            return results[0]
예제 #26
0
 def build_sqlalchemy_ordering(order_params, visible_fields):
     '''
     returns a scalar or list of ClauseElement objects which will comprise 
     the ORDER BY clause of the resulting select.
     @param order_params passed as list in the request.GET hash
     '''
     DEBUG_ORDERING = False or logger.isEnabledFor(logging.DEBUG)
     
     if DEBUG_ORDERING:
         logger.info('build sqlalchemy ordering: %s, visible fields: %s',
             order_params,visible_fields.keys())
     if order_params and isinstance(order_params, basestring):
         # standard, convert single valued list params
         order_params = [order_params]
     order_clauses = []
     for order_by in order_params:
         field_name = order_by
         order_clause = None
         if order_by.startswith('-'):
             field_name = order_by[1:]
             order_clause = nullslast(desc(column(field_name)))
             if ( field_name in visible_fields 
                 and visible_fields[field_name]['data_type'] == 'string'):
                 # For string field ordering, double sort as numeric and text
                 order_clause = text(
                     "(substring({field_name}, '^[0-9]+'))::int desc " # cast to integer
                     ",substring({field_name}, '[^0-9_].*$')  "  # works as text
                     .format(field_name=field_name))
         else:
             order_clause = nullsfirst(asc(column(field_name)))
             if ( field_name in visible_fields 
                 and visible_fields[field_name]['data_type'] == 'string'):
                 order_clause = text(
                     "(substring({field_name}, '^[0-9]+'))::int "
                     ",substring({field_name}, '[^0-9_].*$') "
                     .format(field_name=field_name))
         if field_name in visible_fields:
             order_clauses.append(order_clause)
         else:
             logger.warn(
                 'order_by field %r not in visible fields, skipping: ', 
                 order_by)
     if DEBUG_ORDERING:
         logger.info('order_clauses %s',order_clauses)     
     return order_clauses
예제 #27
0
파일: util.py 프로젝트: indico/indico
def build_user_search_query(criteria, exact=False, include_deleted=False, include_pending=False,
                            favorites_first=False):
    unspecified = object()
    query = User.query.distinct(User.id).options(db.joinedload(User._all_emails))

    if not include_pending:
        query = query.filter(~User.is_pending)
    if not include_deleted:
        query = query.filter(~User.is_deleted)

    affiliation = criteria.pop('affiliation', unspecified)
    if affiliation is not unspecified:
        query = query.join(UserAffiliation).filter(unaccent_match(UserAffiliation.name, affiliation, exact))

    email = criteria.pop('email', unspecified)
    if email is not unspecified:
        query = query.join(UserEmail).filter(unaccent_match(UserEmail.email, email, exact))

    # search on any of the name fields (first_name OR last_name)
    name = criteria.pop('name', unspecified)
    if name is not unspecified:
        if exact:
            raise ValueError("'name' is not compatible with 'exact'")
        if 'first_name' in criteria or 'last_name' in criteria:
            raise ValueError("'name' is not compatible with (first|last)_name")
        query = query.filter(_build_name_search(name.replace(',', '').split()))

    for k, v in criteria.iteritems():
        query = query.filter(unaccent_match(getattr(User, k), v, exact))

    # wrap as subquery so we can apply order regardless of distinct-by-id
    query = query.from_self()

    if favorites_first:
        query = (query.outerjoin(favorite_user_table, db.and_(favorite_user_table.c.user_id == session.user.id,
                                                              favorite_user_table.c.target_id == User.id))
                 .order_by(nullslast(favorite_user_table.c.user_id)))
    query = query.order_by(db.func.lower(db.func.indico.indico_unaccent(User.first_name)),
                           db.func.lower(db.func.indico.indico_unaccent(User.last_name)),
                           User.id)
    return query
예제 #28
0
 def sort(self, keys,direction = None,explicit_nullsfirst = False):
     #we sort by a single argument
     if direction:
         keys = ((keys,direction),)
     order_bys = []
     for key,direction in keys:
         if direction > 0:
             #when sorting in ascending direction, NULL values should come first
             if explicit_nullsfirst:
                 direction = lambda *args,**kwargs: nullsfirst(asc(*args,**kwargs))
             else:
                 direction = asc
         else:
             #when sorting in descending direction, NULL values should come last
             if explicit_nullsfirst:
                 direction = lambda *args,**kwargs: nullslast(desc(*args,**kwargs))
             else:
                 direction = desc
         order_bys.append((key,direction))
     self.order_bys = order_bys
     self.objects = None
     return self
예제 #29
0
 def sort(self, keys,direction = None,explicit_nullsfirst = False):
     #we sort by a single argument
     if direction:
         keys = ((keys,direction),)
     order_bys = []
     for key,direction in keys:
         if direction > 0:
             #when sorting in ascending direction, NULL values should come first
             if explicit_nullsfirst:
                 direction = lambda *args,**kwargs: nullsfirst(asc(*args,**kwargs))
             else:
                 direction = asc
         else:
             #when sorting in descending direction, NULL values should come last
             if explicit_nullsfirst:
                 direction = lambda *args,**kwargs: nullslast(desc(*args,**kwargs))
             else:
                 direction = desc
         order_bys.append((key,direction))
     self.order_bys = order_bys
     self.objects = None
     return self
예제 #30
0
파일: dal.py 프로젝트: kotenev/mybookshelf2
async def get_conversion_candidate(ebook_id, to_format):
    to_format_id = await get_format_id(to_format)
    async with engine.acquire() as conn:
        source = model.Source.__table__
        format = model.Format.__table__
        res = await conn.execute(select([source.c.id, format.c.extension]).where(and_(source.c.ebook_id == ebook_id,
                                                                  source.c.format_id == to_format_id,
                                                                  source.c.format_id == format.c.id))\
                                 .order_by(nullslast(desc(source.c.quality))))
        res = await res.first()
        if res:
            return res.as_tuple()

        #TODO: Consider optimal selection of the source
        # in previous version we first selected format (from available convertable in ebook)
        # and then one with best quality -   so actually the other way around
        q=select([source.c.id, format.c.extension])\
        .where(and_(source.c.format_id == format.c.id, source.c.ebook_id == ebook_id)).order_by(nullslast(desc(source.c.quality)))
        async for row in conn.execute(q):
            if row.extension in settings.CONVERTABLE_TYPES:
                return row.id, row.extension

        return None, None
예제 #31
0
def test_oc():
    a = asc('a')
    b = desc('a')
    c = asc('b')
    n = nullslast(desc('a'))

    a = OC(a)
    b = OC(b)
    c = OC(c)
    n = OC(n)

    assert str(a) == str(OC('a'))
    assert a.is_ascending
    assert not b.is_ascending
    assert not n.reversed.reversed.is_ascending
    assert str(a.element) == str(b.element) == str(n.element)
    assert str(a) == str(b.reversed)
    assert str(n.reversed.reversed) == str(n)

    assert a.name == 'a'
    assert n.name == 'a'
    assert n.quoted_full_name == 'a'
    assert repr(n) == '<OC: a DESC NULLS LAST>'
예제 #32
0
async def get_conversion_candidate(ebook_id, to_format):     
    to_format_id = await get_format_id(to_format)
    async with engine.acquire() as conn:
        source = model.Source.__table__
        format = model.Format.__table__
        res = await conn.execute(select([source.c.id, format.c.extension]).where(and_(source.c.ebook_id == ebook_id,
                                                                  source.c.format_id == to_format_id,
                                                                  source.c.format_id == format.c.id))\
                                 .order_by(nullslast(desc(source.c.quality))))
        res = await res.first()  
        if res:
            return res.as_tuple()
        
        #TODO: Consider optimal selection of the source 
        # in previous version we first selected format (from available convertable in ebook)
        # and then one with best quality -   so actually the other way around  
        q=select([source.c.id, format.c.extension])\
        .where(and_(source.c.format_id == format.c.id, source.c.ebook_id == ebook_id)).order_by(nullslast(desc(source.c.quality)))
        async for row in conn.execute(q):
            if row.extension in settings.CONVERTABLE_TYPES:
                return row.id, row.extension
            
        return None, None
예제 #33
0
def users_dt_json():
    """JSON call to fill a DataTable.

    Needs some refactoring.
    """
    args = request.args

    length = int(args.get("iDisplayLength", 0))
    start = int(args.get("iDisplayStart", 0))
    sort_col = int(args.get("iSortCol_0", 1))
    sort_dir = args.get("sSortDir_0", "asc")
    echo = int(args.get("sEcho", 0))
    search = args.get("sSearch", "").replace("%", "").lower()

    end = start + length

    query = User.query
    total_count = query.count()

    if search:
        # TODO: gérer les accents
        filter = or_(
            func.lower(User.first_name).like("%" + search + "%"),
            func.lower(User.last_name).like("%" + search + "%"),
        )
        query = query.filter(filter).reset_joinpoint()

    count = query.count()
    SORT_COLS = {
        1: [],  # will be set to [User.last_name, User.first_name]
        2: [User.created_at],
        3: [User.last_active],
    }
    columns = list(SORT_COLS.get(sort_col, []))
    columns.extend([func.lower(User.last_name), func.lower(User.first_name)])

    direction = asc if sort_dir == "asc" else desc
    order_by = [direction(column) for column in columns]

    # sqlite does not support 'NULLS FIRST|LAST' in ORDER BY clauses
    engine = query.session.get_bind(User.__mapper__)
    if engine.name != "sqlite":
        order_by[0] = nullslast(order_by[0])

    query = query.order_by(*order_by)

    users = query.slice(start, end).all()

    data = []
    MUGSHOT_SIZE = 45
    for user in users:
        # TODO: this should be done on the browser.
        user_url = url_for(".user", user_id=user.id)
        mugshot = user_photo_url(user, size=MUGSHOT_SIZE)
        name = escape(getattr(user, "name") or "")

        cell0 = (
            '<a href="{url}"><img src="{src}" width="{size}" height="{size}">'
            "</a>".format(url=user_url, src=mugshot, size=MUGSHOT_SIZE))
        cell1 = '<div class="info"><a href="{user_url}">{name}</a> ' "</div>".format(
            **locals())
        cell2 = age(user.created_at)
        cell3 = age(user.last_active)

        cell4 = ""  # TODO: follow / unfollow?
        data.append([cell0, cell1, cell2, cell3])

    result = {
        "sEcho": echo,
        "iTotalRecords": total_count,
        "iTotalDisplayRecords": count,
        "aaData": data,
    }
    return jsonify(result)
예제 #34
0
def finder():
    Oppt_Teams = aliased(Teams)
    Oppt_Box = aliased(TeamBox)
    dfs = '%Y-%m-%d %H:%M:%S'
    query_args = {}
    for arg in [
            "mode", "Seasons0", "Seasons1", "Overtime", "Game_Type",
            "Game_Month", "Team", "Opponent", "Game_Result", "Game_Location",
            "stats0", "stats1", "stats2", "stats3", "operators0", "operators1",
            "operators2", "operators3", "input0", "input1", "input2", "input3",
            "order", "page"
    ]:
        query_args[arg] = request.args.get(arg)
    mode = query_args["mode"]
    if mode == "Single":
        headers = ["Rk", "Date", "Tm", "Opp", "W/L", 'MIN'] + ['PTS', 'FGM', "FGA", "FG_PCT", 'FG3M', 'FG3A', 'FG3_PCT',
                                                               'FTM', 'FTA', 'FT_PCT', 'REB', 'AST', 'STL', 'BLK'] + \
                  ['OPPT_PTS', 'OPPT_FG', "OPPT_FGA", "OPPT_FG_PCT", 'OPPT_FG3M', 'OPPT_FG3A', 'OPPT_FG3_PCT',
                   'OPPT_FT', 'OPPT_FTA', 'OPPT_FT_PCT', 'OPPT_REB', 'OPPT_AST', 'OPPT_STL', 'OPPT_BLK']
        cols = [
            'MIN', 'PTS', "FGM", "FGA", "FG_PCT", "FG3M", "FG3A", "FG3_PCT",
            "FTM", "FTA", "FT_PCT", 'REB', 'AST', 'STL', 'BLK'
        ]
        data = [Games.GAME_DATE, Teams.NAME, Oppt_Teams.NAME, TeamBox.WIN] + \
               [getattr(TeamBox, col) for col in cols] + [getattr(Oppt_Box, col) for col in cols[1:]]
        team_data = db.session.query(*data).join(Oppt_Box.__table__, and_(TeamBox.GAME_ID == Oppt_Box.GAME_ID,
                                                 TeamBox.OPPONENT_TEAM_ID == Oppt_Box.TEAM_ID)).join \
            (Games.__table__, TeamBox.GAME_ID == Games.GAME_ID).join \
            (Teams.__table__, TeamBox.TEAM_ID == Teams.TEAM_ID)
    else:
        headers = ["Rank", "Team", "Count"]
        team_data = db.session.query(Teams.NAME, db.func.count(Teams.NAME).label('total'))\
            .join(TeamBox.__table__, Teams.TEAM_ID == TeamBox.TEAM_ID) \
            .join(Games.__table__, TeamBox.GAME_ID == Games.GAME_ID)
    search_text = "Current search: In a single game" if mode == "Single" else "Current search: In multiple seasons"
    filter_args = [Oppt_Teams.TEAM_ID == TeamBox.OPPONENT_TEAM_ID]
    if query_args["Seasons0"] != "Any":
        filter_args.append(Games.SEASON_ID >= query_args["Seasons0"])
        search_text += ", from {}".format(query_args["Seasons0"])
    if query_args["Seasons1"] != "Any":
        filter_args.append(Games.SEASON_ID <= query_args["Seasons1"])
        search_text += ", until {}".format(query_args["Seasons1"])
    if query_args["Game_Month"] != "Any":
        filter_args.append(
            extract('month', Games.GAME_DATE) == int(query_args["Game_Month"]))
        search_text += ", in month {}".format(calendar.month_name[int(
            query_args["Game_Month"])])
    if query_args["Team"] != "Any":
        filter_args.append(Teams.NAME == query_args["Team"])
        search_text += ", playing for {}".format(query_args["Team"])
    if query_args["Opponent"] != "Any":
        filter_args.append(Oppt_Teams.NAME == query_args["Opponent"])
        search_text += ", against {}".format(query_args["Opponent"])
    if query_args["Game_Result"] != "Either":
        filter_args.append(TeamBox.WIN == (
            True if query_args["Game_Result"] == "Won" else False))
        search_text += ", {} game".format(query_args["Game_Result"].lower())
    if query_args["Game_Location"] != "Either":
        filter_args.append(TeamBox.HOME == (
            True if query_args["Game_Location"] == "Home" else False))
        search_text += ", game played at {}".format(
            query_args["Game_Location"])
    if query_args["Game_Type"] != "Either":
        filter_args.append(Games.PLAYOFFS == (
            True if query_args["Game_Type"] == "Playoffs" else False))
        search_text += ", game played at {}".format(
            query_args["Game_Location"])
    if query_args["Overtime"] != "Either":
        filter_args.append(TeamBox.MIN > 240 if query_args["Overtime"] ==
                           "Yes" else TeamBox.MIN <= 240)
        search_text += ", game played at {}".format(
            query_args["Game_Location"])
    for i in range(4):
        s = str(i)
        if query_args["input" + s] and query_args["stats" + s] != "Any":
            if query_args["operators" + s] == "gt":
                filter_args.append(
                    getattr(
                        Oppt_Box if query_args["stats" + s].
                        startswith("OPPT") else TeamBox, query_args[
                            "stats" + s]) >= float(query_args["input" + s]))
                search_text += ", {} >= {}".format(query_args["stats" + s],
                                                   query_args["input" + s])
            else:
                filter_args.append(
                    getattr(
                        Oppt_Box if query_args["stats" + s].
                        startswith("OPPT") else TeamBox, query_args[
                            "stat" + s]) <= float(query_args["input" + s]))
                search_text += ", {} <= {}".format(query_args["stats" + s],
                                                   query_args["input" + s])
    page = int(query_args["page"])
    if mode == "Single":
        query_results = team_data.filter(*filter_args).order_by\
            (nullslast(getattr(Oppt_Box if query_args["order"].startswith("OPPT") else TeamBox, query_args["order"][5:] if
            query_args["order"].startswith("OPPT") else query_args["order"]).desc())).paginate\
            (page=page, per_page=100, error_out=True)
    else:
        query_results = team_data.filter(*filter_args).group_by(Teams.NAME).order_by\
            (text('total DESC')).paginate\
            (page=page, per_page=100, error_out=True)
    search_text += ", sorted by {}.".format(
        query_args["order"] if mode ==
        "Single" else "most games matching criteria")
    query_results_list = [list(i) for i in query_results.items]
    col = headers.index(
        query_args["order"]) if mode == "Single" else headers.index("Count")
    if mode == "Single":
        for n, row in enumerate(query_results_list):
            row.insert(0, n + 1 + (page - 1) * 100)
            row[4] = 'W' if row[4] else 'L'
            row[1] = datetime.strftime(row[1], '%m-%d-%Y')
            for i in [8, 11, 14]:
                if row[i]:
                    row[i] = round(row[i], 3)
            for i in range(len(row)):
                if row[i] is None:
                    row[i] = 0
        for n, i in enumerate(headers):
            if i == "PLUS_MINUS":
                headers[n] = "+/-"
            headers[n] = headers[n].replace("_", " ").replace(
                " PCT", "%").replace("FG3", "3P").replace("OPPT", "")
    else:
        for n, row in enumerate(query_results_list):
            row.insert(0, n + 1 + (page - 1) * 100)
    return render_template('tgf_data.html',
                           search_text=search_text,
                           title='Team Game Finder',
                           headers=headers,
                           data_dict=query_results_list,
                           col=col,
                           has_prev=query_results.has_prev,
                           has_next=query_results.has_next,
                           page=page)
예제 #35
0
파일: views.py 프로젝트: debon/abilian-core
    def data(self, *args, **kw):
        security = current_app.services['security']
        length = int(kw.get("iDisplayLength", 0))
        start = int(kw.get("iDisplayStart", 0))
        sort_col = int(kw.get("iSortCol_0", 1))
        sort_dir = kw.get("sSortDir_0", "asc")
        echo = int(kw.get("sEcho", 0))
        search = kw.get("sSearch", "").replace("%", "").strip().lower()

        end = start + length
        q = User.query \
          .options(sa.orm.subqueryload('groups'),
                   sa.orm.undefer('photo'), ) \
          .filter(User.id != 0)
        total_count = q.count()

        if search:
            # TODO: gérer les accents
            filter = or_(
                func.lower(User.first_name).like("%" + search + "%"),
                func.lower(User.last_name).like("%" + search + "%"),
                func.lower(User.email).like("%" + search + "%"))
            q = q.filter(filter)

        count = q.count()
        SORT_COLS = {
            1: [],  # [User.last_name, User.first_name] will be added anyway
            2: [func.lower(User.email)],
            5: [User.last_active],
        }
        columns = list(SORT_COLS.get(sort_col, []))
        columns.extend(
            [func.lower(User.last_name),
             func.lower(User.first_name)])

        direction = asc if sort_dir == 'asc' else desc
        order_by = map(direction, columns)

        # sqlite does not support 'NULLS FIRST|LAST' in ORDER BY clauses
        engine = q.session.get_bind(User.__mapper__)
        if engine.name != 'sqlite':
            order_by[0] = nullslast(order_by[0])

        q = q.order_by(*order_by)

        users = q.slice(start, end).all()

        data = []
        MUGSHOT_SIZE = 45
        for user in users:
            # TODO: this should be done on the browser.
            user_url = url_for(".users_user", user_id=user.id)
            mugshot = user_photo_url(user, size=MUGSHOT_SIZE)
            name = escape(getattr(user, "name") or "")
            email = escape(getattr(user, "email") or "")
            roles = [
                r for r in security.get_roles(user, no_group_roles=True)
                if r.assignable
            ]
            columns = []
            columns.append(
                u'<a href="{url}"><img src="{src}" width="{size}" height="{size}">'
                u'</a>'.format(url=user_url, src=mugshot, size=MUGSHOT_SIZE))
            columns.append(u'<a href="{url}">{name}</a>'.format(url=user_url,
                                                                name=name))
            columns.append(u'<a href="{url}"><em>{email}</em></a>'.format(
                url=user_url, email=email))
            columns.append(u'\u2713' if user.can_login else u'')
            columns.append(
                render_template_string(u'''{%- for g in groups %}
            <span class="badge badge-default">{{ g.name }}</span>
            {%- endfor %}''',
                                       groups=sorted(user.groups)))
            columns.append(
                render_template_string(u'''{%- for role in roles %}
            <span class="badge badge-default">{{ role }}</span>
            {%- endfor %}''',
                                       roles=roles))

            if user.last_active:
                last_active = format_datetime(user.last_active)
            else:
                last_active = _(u'Never logged in')
            columns.append(last_active)

            data.append(columns)

        return {
            "sEcho": echo,
            "iTotalRecords": total_count,
            "iTotalDisplayRecords": count,
            "aaData": data,
        }
예제 #36
0
    def data(self, *args, **kw) -> Dict:
        security = cast(SecurityService, get_service("security"))
        length = int(kw.get("iDisplayLength", 0))
        start = int(kw.get("iDisplayStart", 0))
        sort_col = int(kw.get("iSortCol_0", 1))
        sort_dir = kw.get("sSortDir_0", "asc")
        echo = int(kw.get("sEcho", 0))
        search = kw.get("sSearch", "").replace("%", "").strip().lower()

        end = start + length
        query = User.query.options(
            sa.orm.subqueryload("groups"),
            sa.orm.undefer("photo")).filter(User.id != 0)
        total_count = query.count()

        if search:
            # TODO: gérer les accents
            filter = or_(
                func.lower(User.first_name).like("%" + search + "%"),
                func.lower(User.last_name).like("%" + search + "%"),
                func.lower(User.email).like("%" + search + "%"),
            )
            query = query.filter(filter)

        count = query.count()
        SORT_COLS = {
            1: [],  # [User.last_name, User.first_name] will be added anyway
            2: [func.lower(User.email)],
            5: [User.last_active],
        }
        columns = list(SORT_COLS.get(sort_col, []))
        columns.extend(
            [func.lower(User.last_name),
             func.lower(User.first_name)])

        direction = asc if sort_dir == "asc" else desc
        order_by = list(map(direction, columns))

        # sqlite does not support 'NULLS FIRST|LAST' in ORDER BY clauses
        engine = query.session.get_bind(User.__mapper__)
        if engine.name != "sqlite":
            order_by[0] = nullslast(order_by[0])

        query = query.order_by(*order_by)

        users = query.slice(start, end).all()

        data = []
        for user in users:
            # TODO: this should be done on the browser.
            user_url = url_for(".users_user", user_id=user.id)
            mugshot = user_photo_url(user, size=MUGSHOT_SIZE)
            name = html.escape(user.name or "")
            email = html.escape(user.email or "")
            roles = [
                r for r in security.get_roles(user, no_group_roles=True)
                if r.assignable
            ]
            columns = [
                '<a href="{url}"><img src="{src}" width="{size}" height="{size}">'
                "</a>".format(url=user_url, src=mugshot, size=MUGSHOT_SIZE),
                f'<a href="{user_url}">{name}</a>',
                f'<a href="{user_url}"><em>{email}</em></a>',
                "\u2713" if user.can_login else "",
                render_template_string(
                    """{%- for g in groups %}
                        <span class="badge badge-default">{{ g.name }}</span>
                    {%- endfor %}""",
                    groups=sorted(user.groups),
                ),
                render_template_string(
                    """{%- for role in roles %}
                       <span class="badge badge-default">{{ role }}</span>
                    {%- endfor %}""",
                    roles=roles,
                ),
            ]

            if user.last_active:
                last_active = format_datetime(user.last_active)
            else:
                last_active = _("Never logged in")
            columns.append(last_active)

            data.append(columns)

        return {
            "sEcho": echo,
            "iTotalRecords": total_count,
            "iTotalDisplayRecords": count,
            "aaData": data,
        }
예제 #37
0
def do_advanced_search(params, queried_columns=None):

	if queried_columns:
		print("Queried columns overridden: ", queried_columns)
		queried_columns = list(queried_columns)
	else:
		queried_columns = (Series.id, Series.title, Series.latest_published, Series.release_count)

	q = db.session.query(*queried_columns).group_by(Series.id)

	# q = q.join(Releases)
	# q = q.filter(Releases.series == Series.id)

	if 'tag-category' in params:
		q = q.join(Tags)
		for text, mode in params['tag-category'].items():
			if mode == "included":
				q = q.filter(Tags.tag == str(text))
			elif mode == 'excluded':
				q = q.filter(Tags.tag != str(text))

	if 'genre-category' in params:
		q = q.join(Genres)
		for text, mode in params['genre-category'].items():
			if mode == "included":
				q = q.filter(Genres.genre == str(text))
			elif mode == 'excluded':
				q = q.filter(Genres.genre != str(text))


	if 'title-search-text' in params and params['title-search-text']:
		searchterm = params['title-search-text']
		q = add_similarity_query(searchterm, q)


	if 'chapter-limits' in params:
		if len(params['chapter-limits']) == 2:
			minc, maxc = params['chapter-limits']
			minc = int(minc)
			maxc = int(maxc)
			params['chapter-limits'] = [minc, maxc]
			if minc > 0:
				q = q.having(Series.release_count >= minc)
			if maxc > 0:
				q = q.having(Series.release_count <= maxc)

	type_map = {
		'Translated'                : 'translated',
		'Original English Language' : 'oel',
	}

	if 'series-type' in params:
		ops = []
		for key, value in params['series-type'].items():
			if key in type_map:
				if value == 'included':
					ops.append((Series.tl_type == type_map[key]))
				elif value == 'excluded':
					ops.append((Series.tl_type != type_map[key]))
				else:
					print("wut?")
		if ops:
			q = q.filter(and_(*ops))

	if "sort-mode" in params:

		if params['sort-mode'] == "update":
			q = q.order_by(nullslast(desc(Series.latest_published)))
		elif params['sort-mode'] == "chapter-count":
			q = q.order_by(nullslast(desc(Series.release_count)))
		else: # params['sort-mode'] == "name"
			q = q.order_by(Series.title)
	else:
		q = q.order_by(Series.title)

	return q
예제 #38
0
파일: views.py 프로젝트: debon/abilian-core
  def data(self, *args, **kw):
    security = current_app.services['security']
    length = int(kw.get("iDisplayLength", 0))
    start = int(kw.get("iDisplayStart", 0))
    sort_col = int(kw.get("iSortCol_0", 1))
    sort_dir = kw.get("sSortDir_0", "asc")
    echo = int(kw.get("sEcho", 0))
    search = kw.get("sSearch", "").replace("%", "").strip().lower()

    end = start + length
    q = User.query \
      .options(sa.orm.subqueryload('groups'),
               sa.orm.undefer('photo'), ) \
      .filter(User.id != 0)
    total_count = q.count()

    if search:
      # TODO: gérer les accents
      filter = or_(func.lower(User.first_name).like("%" + search + "%"),
                   func.lower(User.last_name).like("%" + search + "%"),
                   func.lower(User.email).like("%" + search + "%"))
      q = q.filter(filter)

    count = q.count()
    SORT_COLS = {
      1: [],  # [User.last_name, User.first_name] will be added anyway
      2: [func.lower(User.email)],
      5: [User.last_active],
    }
    columns = list(SORT_COLS.get(sort_col, []))
    columns.extend([func.lower(User.last_name), func.lower(User.first_name)])

    direction = asc if sort_dir == 'asc' else desc
    order_by = map(direction, columns)

    # sqlite does not support 'NULLS FIRST|LAST' in ORDER BY clauses
    engine = q.session.get_bind(User.__mapper__)
    if engine.name != 'sqlite':
      order_by[0] = nullslast(order_by[0])

    q = q.order_by(*order_by)

    users = q.slice(start, end).all()

    data = []
    MUGSHOT_SIZE = 45
    for user in users:
      # TODO: this should be done on the browser.
      user_url = url_for(".users_user", user_id=user.id)
      mugshot = user_photo_url(user, size=MUGSHOT_SIZE)
      name = escape(getattr(user, "name") or "")
      email = escape(getattr(user, "email") or "")
      roles = [r for r in security.get_roles(user, no_group_roles=True)
               if r.assignable]
      columns = []
      columns.append(
        u'<a href="{url}"><img src="{src}" width="{size}" height="{size}">'
        u'</a>'.format(url=user_url, src=mugshot, size=MUGSHOT_SIZE)
      )
      columns.append(
        u'<a href="{url}">{name}</a>'.format(url=user_url, name=name))
      columns.append(
        u'<a href="{url}"><em>{email}</em></a>'.format(url=user_url,
                                                       email=email))
      columns.append(u'\u2713' if user.can_login else u'')
      columns.append(render_template_string(
        u'''{%- for g in groups %}
            <span class="badge badge-default">{{ g.name }}</span>
            {%- endfor %}''',
        groups=sorted(user.groups)))
      columns.append(render_template_string(
        u'''{%- for role in roles %}
            <span class="badge badge-default">{{ role }}</span>
            {%- endfor %}''',
        roles=roles))

      if user.last_active:
        last_active = format_datetime(user.last_active)
      else:
        last_active = _(u'Never logged in')
      columns.append(last_active)

      data.append(columns)

    return {
      "sEcho": echo,
      "iTotalRecords": total_count,
      "iTotalDisplayRecords": count,
      "aaData": data,
    }
예제 #39
0
    LANGUAGE_NAME = 'LGN'
    GENRE = 'GNR'

    other_name = Column(String(512), nullable=False)
    our_name = Column(String(512), nullable=False)
    category = Column(String(3), nullable=False)
    
class Version(Base):
    version = Column(Integer)

# It's critical for paging that sorting unambiguous 
sortings = {'ebook': {'title': [Ebook.title, Ebook.id],
                      '-title': [desc(Ebook.title), desc(Ebook.id)],
                      'created': [Ebook.created, Ebook.id],
                      '-created': [desc(Ebook.created), desc(Ebook.id)],
                      'rating': [nullslast(Ebook.rating), Ebook.rating_count, Ebook.id],
                      '-rating': [nullslast(desc(Ebook.rating)), desc(Ebook.rating_count), desc(Ebook.id)],
                      },
            'bookshelf': {'name': [Bookshelf.name, Bookshelf.id],
                      '-name': [desc(Bookshelf.name), desc(Bookshelf.id)],
                      'created': [Bookshelf.created, Bookshelf.id],
                      '-created': [desc(Bookshelf.created), desc(Bookshelf.id)],
                      'modified': [Bookshelf.modified, Bookshelf.id],
                      '-modified': [desc(Bookshelf.modified), desc(Bookshelf.id)],
                },
             'bookshelf_item': {'order': [BookshelfItem.order, BookshelfItem.id],
                      '-order': [desc(BookshelfItem.order), desc(BookshelfItem.id)],
                      'created': [BookshelfItem.created, BookshelfItem.id],
                      '-created': [desc(BookshelfItem.created), desc(BookshelfItem.id)],
                },
            'shelf': {'name': [Bookshelf.name, Bookshelf.id],
예제 #40
0
def listings(page):
    """
    List of all companies we're tracking.

    order_bys: fields to order by.  assumes Indicator model, unless prefixed with the model name
    order_by: attribute value passed from client (no model)
    order_bys_no_fk: order_bys with no model prefixed.  this is passed to the template.
    """
    order_bys = Indicators.get_attributes()
    order_bys_no_fk = Indicators.get_attributes_no_fk()

    # configure models (for determing column model) and entities (for retrieving columns)
    entities = []
    models = []
    for o in order_bys:
        if o.find(".") != -1:
            entities.append(eval(o))
            models.append(o.split(".")[0])
        else:
            entities.append(eval("Indicators."+o))

    # Search filter, redirect if it exists
    form = FilterForm()
    #filter_by = form.filter.data if form.validate_on_submit() else None

    if form.is_submitted():
        if form.validate():
            direction = request.args.get('direction')
            order_by = request.args.get('order_by')
            query_state = {}
            if direction:
                query_state['direction'] = direction
            if order_by:
                query_state['order_by'] = order_by

            return redirect(url_for('main.listings', page=page, filter_by=form.filter.data, **query_state))
        else:
            filter_by = ""
    else:
        if Company.validate_symbol(request.args.get('filter_by', '').upper()):
            filter_by = request.args.get('filter_by')
        else:
            filter_by = ''

    print "your filter by is", filter_by

    #if form.validate_on_submit():
    #    direction = request.args.get('direction')
    #    order_by = request.args.get('order_by')
    #    query_state = {}
    #    if direction:
    #        query_state['direction'] = direction
    #    if order_by:
    #        query_state['order_by'] = order_by

    #    return redirect(url_for('main.listings', page=page, filter_by=form.filter.data, **query_state))

    #if Company.validate_symbol(request.args.get('filter_by', '').upper()):
    #    filter_by = request.args.get('filter_by')
    #else:
    #    filter_by = ''

    # Get values from client
    if request.args.get("direction") == "False":
        direction = False
    else:
        direction = True

    if request.args.get('order_by') in order_bys_no_fk:
        order_by = request.args.get('order_by')
    else:
        #order_by = "company.symbol"
        order_by = "roe"

    which_way = "asc" if direction == True else "desc"

    # Set the order based on the order_by that has been passed in.
    for model in models:
        if model+"."+order_by in order_bys:
            order = getattr(getattr(eval(model), order_by), which_way)()
        else:
            order = getattr(getattr(Indicators, order_by), which_way)()


    # get the most recent collection date
    try:
        #date = db.session.query(Indicators.date).order_by(desc("date")).distinct().limit(2).all()[-1].date # second to last day
        date = db.session.query(Indicators.date).order_by(desc("date")).distinct().limit(2).all()[0].date   # last day
    except IndexError:
        return render_template('listings.html',
                               pagination=None,
                               listings = None,
                               order_by = order_by,
                               direction = direction,
                               order_bys = order_bys_no_fk,
                               date = datetime.today(),
                               count = 0,
                               form = form,
                               filter_by=filter_by
                               )
        
    #date = db.session.query(Indicators.date).order_by(order).distinct().limit(2).all()[-1].date
    #date = db.session.query(Indicators.date).order_by(desc(Indicators.date)).distinct().limit(2).all()[-1].date
    #db.session.query(Indicators).join(Company).filter(Indicators.date == date).order_by(Company.symbol).all()

    #pagination = Indicators.query.order_by(order).paginate(page, current_app.config['INDICATORS_PER_PAGE'], error_out=False)
    #pagination = db.session.query(Indicators).join(Company).filter(Indicators.date == date).order_by(Company.symbol).paginate(page, current_app.config['INDICATORS_PER_PAGE'], error_out=False)

    # Build our query
    query = Indicators.query.join(Company)
    if filter_by:
        #query = query.filter((Indicators.date == date) & ( Company.symbol.startswith("{}".format(filter_by)) | Company.name.startswith("{}".format(filter_by)) )  )
        query = query.filter((Indicators.date == date) & ( Company.symbol.ilike("{}".format(filter_by)))  )
    else:
        query = query.filter(Indicators.date == date)

    query = query.distinct(*entities)
    query = query.order_by(nullslast(order))
    query = query.with_entities(*entities)

    pagination = query.paginate(page, current_app.config['INDICATORS_PER_PAGE'], error_out=False)
    listings = pagination.items

    return render_template('listings.html',
                           pagination=pagination,
                           listings = listings,
                           order_by = order_by,
                           direction = direction,
                           order_bys = order_bys_no_fk,
                           date = datetime.today(),
                           count = pagination.total,
                           form = form,
                           filter_by=filter_by
                           )
예제 #41
0
def finder():
    Oppt_Teams = aliased(Teams)
    dfs = '%Y-%m-%d %H:%M:%S'
    query_args = {}
    for arg in [
            "mode", "Seasons0", "Seasons1", "Age0", "Age1", "Game_Month",
            "Game_Type", "Team", "Opponent", "Game_Result", "Role",
            "Game_Location", "stats0", "stats1", "stats2", "stats3",
            "operators0", "operators1", "operators2", "operators3", "input0",
            "input1", "input2", "input3", "order", "page"
    ]:
        query_args[arg] = request.args.get(arg)
    mode = query_args["mode"]
    if mode == "Single":
        headers = ["Rank", "Player", "Date", "Team", "Opponent", "W/L", "GS"
                   ] + PlayerBoxProd.__table__.columns.keys()[:-4]
        data = [Player.NAME, Games.GAME_DATE, Teams.NAME, Oppt_Teams.NAME, TeamBox.WIN, PlayerBoxProd.STARTED] + \
               [getattr(PlayerBoxProd, col) for col in headers[7:]] + [PlayerBoxProd.PLAYER_ID]
        player_data = db.session.query(*data).join \
            (Player.__table__).join(Games.__table__, PlayerBoxProd.GAME_ID == Games.GAME_ID).join \
            (TeamBox.__table__, and_(Games.GAME_ID == TeamBox.GAME_ID, PlayerBoxProd.TEAM_ID == TeamBox.TEAM_ID)).join \
            (Teams.__table__, PlayerBoxProd.TEAM_ID == Teams.TEAM_ID)
    else:
        headers = ["Rank", "Player", "Count"]
        player_data = db.session.query(Player.NAME, db.func.count(Player.NAME).label('total'), Player.PLAYER_ID).join \
            (PlayerBoxProd.__table__).join(Games.__table__, PlayerBoxProd.GAME_ID == Games.GAME_ID).join \
            (TeamBox.__table__, and_(Games.GAME_ID == TeamBox.GAME_ID, PlayerBoxProd.TEAM_ID == TeamBox.TEAM_ID)).join \
            (Teams.__table__, PlayerBoxProd.TEAM_ID == Teams.TEAM_ID)
    search_text = "Current search: In a single game" if mode == "Single" else "Current search: In multiple seasons"
    filter_args = [Oppt_Teams.TEAM_ID == TeamBox.OPPONENT_TEAM_ID]
    if query_args["Seasons0"] != "Any":
        filter_args.append(Games.SEASON_ID >= query_args["Seasons0"])
        search_text += ", from {}".format(query_args["Seasons0"])
    if query_args["Seasons1"] != "Any":
        filter_args.append(Games.SEASON_ID <= query_args["Seasons1"])
        search_text += ", until {}".format(query_args["Seasons1"])
    if query_args["Game_Month"] != "Any":
        filter_args.append(
            extract('month', Games.GAME_DATE) == int(query_args["Game_Month"]))
        search_text += ", in month {}".format(calendar.month_name[int(
            query_args["Game_Month"])])
    if query_args["Age0"] != "Any":
        search_text += ", player older than {}".format(query_args["Age0"])
        filter_args.append(Games.GAME_DATE - Player.DOB >= text(
            "INTERVAL '{} YEAR'".format(query_args["Age0"])))
    if query_args["Age1"] != "Any":
        filter_args.append(Games.GAME_DATE - Player.DOB <= text(
            "INTERVAL '{} YEAR'".format(query_args["Age1"])))
        search_text += ", player younger than {}".format(query_args["Age1"])
    if query_args["Team"] != "Any":
        filter_args.append(Teams.NAME == query_args["Team"])
        search_text += ", playing for {}".format(query_args["Team"])
    if query_args["Opponent"] != "Any":
        filter_args.append(Oppt_Teams.NAME == query_args["Opponent"])
        search_text += ", against {}".format(query_args["Opponent"])
    if query_args["Game_Result"] != "Either":
        filter_args.append(TeamBox.WIN == (
            True if query_args["Game_Result"] == "Won" else False))
        search_text += ", {} game".format(query_args["Game_Result"].lower())
    if query_args["Role"] != "Either":
        filter_args.append(PlayerBoxProd.STARTED == (
            True if query_args["Role"] == "Starter" else False))
        search_text += ", played as {}".format(query_args["Role"].lower())
    if query_args["Game_Location"] != "Either":
        filter_args.append(TeamBox.HOME == (
            True if query_args["Game_Location"] == "Home" else False))
        search_text += ", game played at {}".format(
            query_args["Game_Location"])
    if query_args["Game_Type"] != "Either":
        filter_args.append(Games.PLAYOFFS == (
            True if query_args["Game_Type"] == "Playoffs" else False))
        search_text += ", game played at {}".format(
            query_args["Game_Location"])
    for i in range(4):
        s = str(i)
        if query_args["input" + s] and query_args["stats" + s] != "Any":
            if query_args["operators" + s] == "gt":
                filter_args.append(
                    getattr(PlayerBoxProd, query_args["stats" + s]) >= float(
                        query_args["input" + s]))
                search_text += ", {} >= {}".format(query_args["stats" + s],
                                                   query_args["input" + s])
            else:
                filter_args.append(
                    getattr(PlayerBoxProd, query_args["stat" + s]) <= float(
                        query_args["input" + s]))
                search_text += ", {} <= {}".format(query_args["stats" + s],
                                                   query_args["input" + s])
    page = int(query_args["page"])
    if mode == "Single":
        query_results = player_data.filter(*filter_args).order_by\
            (nullslast(getattr(PlayerBoxProd, query_args["order"]).desc())).paginate\
            (page=page, per_page=100, error_out=True)
    else:
        query_results = player_data.filter(*filter_args).group_by(Player.NAME, Player.PLAYER_ID).order_by\
            (text('total DESC')).paginate\
            (page=page, per_page=100, error_out=True)
    search_text += ", sorted by {}.".format(
        query_args["order"] if mode ==
        "Single" else "most games matching criteria")
    query_results_list = [list(i) for i in query_results.items]
    if mode == "Single":
        for n, row in enumerate(query_results_list):
            row.insert(0, n + 1 + (page - 1) * 100)
            row[7] = int(row[7] / 60)
            row[5] = 'W' if row[5] else 'L'
            row[6] = 1 if row[6] else 0
            row[2] = datetime.strftime(row[2], '%m-%d-%Y')
            for i in [10, 13, 16]:
                if row[i]:
                    row[i] = round(row[i], 3)
            for i in range(len(row)):
                if row[i] is None:
                    row[i] = 0

        for i in [10, 13, 16]:
            headers[i] = headers[i].replace("_", " ")
        headers[len(headers) - 1] = "+/-"
    else:
        for n, row in enumerate(query_results_list):
            row.insert(0, n + 1 + (page - 1) * 100)
    col = headers.index(
        query_args["order"]) if mode == "Single" else headers.index("Count")
    return render_template('pgf_data.html',
                           search_text=search_text,
                           title='Player Game Finder',
                           headers=headers,
                           data_dict=query_results_list,
                           col=col,
                           has_prev=query_results.has_prev,
                           has_next=query_results.has_next,
                           page=page)
예제 #42
0
    our_name = Column(String(512), nullable=False)
    category = Column(String(3), nullable=False)


class Version(Base):
    version = Column(Integer)


# It's critical for paging that sorting unambiguous
sortings = {
    'ebook': {
        'title': [Ebook.title, Ebook.id],
        '-title': [desc(Ebook.title), desc(Ebook.id)],
        'created': [Ebook.created, Ebook.id],
        '-created': [desc(Ebook.created), desc(Ebook.id)],
        'rating': [nullslast(Ebook.rating), Ebook.rating_count, Ebook.id],
        '-rating': [
            nullslast(desc(Ebook.rating)),
            desc(Ebook.rating_count),
            desc(Ebook.id)
        ],
    },
    'bookshelf': {
        'name': [Bookshelf.name, Bookshelf.id],
        '-name': [desc(Bookshelf.name),
                  desc(Bookshelf.id)],
        'created': [Bookshelf.created, Bookshelf.id],
        '-created': [desc(Bookshelf.created),
                     desc(Bookshelf.id)],
        'modified': [Bookshelf.modified, Bookshelf.id],
        '-modified': [desc(Bookshelf.modified),
예제 #43
0
파일: exercises.py 프로젝트: Kareeeeem/AMC
def get_exercises(favorited_by=None):
    '''Get exercise collection, if favorited_by is set then get the
    collection of favorites of the user.'''

    user_id = auth.current_user.id if auth.current_user else None

    # client requests favorites that are not his.
    if favorited_by and favorited_by != user_id:
        raise AuthorizationError

    search = request.args.get('search')
    category = request.args.get('category')
    order_by = request.args.get('order_by')
    author = request.args.get('author')

    orderfunc = desc
    if order_by and len(order_by) > 0 and order_by[-1] in '+ -'.split():
        if order_by[-1] == '+':
            orderfunc = asc
        order_by = order_by[:-1]

    query = Exercise.query

    if search:
        search_terms = (' | ').join(search.split())
        query = query.add_columns(func.ts_rank(
            Exercise.tsv, func.to_tsquery(search_terms)).label('search_rank')).\
            filter(Exercise.tsv.match(search_terms))

        if order_by == 'relevance':
            query = query.order_by(nullslast(orderfunc('search_rank')))

    if user_id:
        user_rating = aliased(Rating, name='user_rating')

        query = query.add_entity(user_rating).\
            outerjoin(user_rating, and_(user_rating.exercise_id == Exercise.id,
                                        user_rating.user_id == user_id))

        if order_by == 'user_rating':
            query = query.order_by(nullslast(orderfunc(user_rating.rating)))
        elif order_by == 'user_fun_rating':
            query = query.order_by(nullslast(orderfunc(user_rating.fun)))
        elif order_by == 'user_effective_rating':
            query = query.order_by(nullslast(orderfunc(user_rating.effective)))
        elif order_by == 'user_clear_rating':
            query = query.order_by(nullslast(orderfunc(user_rating.clear)))

        # when if favorited_by is not None then we only want the user favorites
        # and isouter will be set to False. Meaning we will do an inner join If
        # favorited_by is None then isouter will be True and we will do an
        # outer join meaning we want to know which exercises the user favorited
        # but we want all of them.
        isouter = not bool(favorited_by)

        # include a column from the UserFavoriteExercise table or `0`.
        # this will get serialized as a Boolean to signify favorited or not.
        query = query.\
            add_columns(func.coalesce(UserFavoriteExercise.exercise_id, 0).label('favorited')).\
            join(UserFavoriteExercise,
                 and_(UserFavoriteExercise.exercise_id == Exercise.id,
                      UserFavoriteExercise.user_id == user_id),
                 isouter=isouter)

    if author:
        query = query.join(
            User, and_(User.id == Exercise.author_id, User.username == author))

    if category:
        category = parse_query_params(request.args, key='category')
        query = query.join(Category).filter(Category.name.in_(category))

    if 'author' in parse_query_params(request.args, key='expand'):
        query = query.options(joinedload(Exercise.author))

    if order_by in ['average_rating', 'rating']:
        query = query.order_by(nullslast(orderfunc(Exercise.avg_rating)))
    elif order_by == 'average_fun_rating':
        query = query.order_by(nullslast(orderfunc(Exercise.avg_fun_rating)))
    elif order_by == 'average_clear_rating':
        query = query.order_by(nullslast(orderfunc(Exercise.avg_clear_rating)))
    elif order_by == 'average_effective_rating':
        query = query.order_by(
            nullslast(orderfunc(Exercise.avg_effective_rating)))
    elif order_by == 'popularity':
        query = query.order_by(nullslast(orderfunc(Exercise.popularity)))
    elif order_by == 'updated_at':
        query = query.order_by(orderfunc(Exercise.updated_at))
    else:
        query = query.order_by(orderfunc(Exercise.created_at))

    page = Pagination(request, query=query)
    return Serializer(ExerciseSchema, request.args).dump_page(page)
예제 #44
0
 def direction(*args, **kwargs):
     return nullslast(desc(*args, **kwargs))
예제 #45
0
파일: admin.py 프로젝트: mitchmcdee/signup
def admin_accept(s, user):
    q = s.query(m.Member).filter(m.Member.paid == None).order_by(
        nullslast(m.Member.time_registered))
    return lookup.get_template('accept.mako').render(members=q)