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
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
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
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
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,
}
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>"
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
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 })
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,
}
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))))
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))))
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
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)
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)
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)
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,
}
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,
}
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
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)))
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()
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
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
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
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()
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]
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
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
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
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
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
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>'
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
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)
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)
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,
}
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,
}
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
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,
}
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],
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
)
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)
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),
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)
def direction(*args, **kwargs):
return nullslast(desc(*args, **kwargs))
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)