def apply_pagination(self, query: BaseQuery, page: Optional[int],
page_size: Optional[int]) -> BaseQuery:
if page and page_size:
query = query.offset(page * page_size)
if page_size:
query = query.limit(page_size)
return query
def ql_don_hang_theo_ngay(page):
if not current_user.is_authenticated or current_user.ma_loai_nguoi_dung != 1:
return redirect(url_for('dang_nhap', next=request.url))
form = Form_QL_don_hang()
tieu_de = 'Đơn hàng ngày hôm nay'
today = datetime.now()
ngay_tim_kiem = today.date()
if session.get('ngay_tim_kiem'):
temp_1 = datetime.strptime(session['ngay_tim_kiem'],
"%a, %d %b %Y %H:%M:%S %Z")
ngay_tim_kiem = temp_1.date()
if form.validate_on_submit():
ngay_tim_kiem = form.ngay_tim_kiem.data
session['ngay_tim_kiem'] = ngay_tim_kiem
page = 1
query = BaseQuery(Hoa_don, dbSession)
page_filter = query.filter(
Hoa_don.ngay_tao_hoa_don == ngay_tim_kiem).paginate(page, 5, False)
if page_filter.total == 0:
tieu_de = "Không tìm thấy hóa đơn"
else:
tieu_de = "Đơn hàng của ngày " + str(
ngay_tim_kiem.day) + " tháng " + str(
ngay_tim_kiem.month) + " năm " + str(ngay_tim_kiem.year)
return render_template('Quan_ly/QL_don_hang/QL_don_hang_theo_ngay.html',
page_filter=page_filter,
form=form,
tieu_de=tieu_de)
def query_to_json(query: BaseQuery, limit: int = 100, offset: int = 0) -> dict:
"""Serialize a list of objects into a JSON-compatible dict."""
total_items = query.count()
query = query.limit(limit).offset(offset)
query_json = {
"count": query.count(),
"limit": limit,
"offset": offset or 0,
"total": total_items,
}
for item in query:
if isinstance(item, models.Molt):
molt_list = query_json.get("molts", list())
molt_list.append(molt_to_json(item))
query_json["molts"] = molt_list
elif isinstance(item, models.Bookmark):
molt_list = query_json.get("molts", list())
molt_list.append(molt_to_json(item.molt))
query_json["molts"] = molt_list
elif isinstance(item, models.Crab):
crab_list = query_json.get("crabs", list())
crab_list.append(crab_to_json(item))
query_json["crabs"] = crab_list
return query_json
def xem_hd_theo_trang_thai(id):
if not current_user.is_authenticated or current_user.ma_loai_nguoi_dung != 1:
return redirect(url_for('dang_nhap', next=request.url))
form = Form_lua_chon()
query = BaseQuery(Hoa_don, dbSession)
trang_thai = 0
if session.get('trang_thai'):
trang_thai = session['trang_thai']
if form.validate_on_submit():
trang_thai = int(form.lua_chon.data)
session['trang_thai'] = trang_thai
id = 1
if trang_thai == 0:
tieu_de = 'Chưa thanh toán'
elif trang_thai == 1:
tieu_de = 'Đã thanh toán'
elif trang_thai == 2:
tieu_de = 'Huỷ'
page_filter = query.filter(Hoa_don.trang_thai == trang_thai).paginate(
id, 5, False)
return render_template(
'Quan_ly/QL_don_hang/QL_don_hang_theo_trang_thai.html',
tieu_de=tieu_de,
page_filter=page_filter,
form=form)
def __iter__(self):
"""
override __iter__ to pull results from dogpile
if particular attributes have been configured.
"""
if hasattr(self, '_cache_region'):
return self.get_value(createfunc=lambda: list(BaseQuery.__iter__(self)))
else:
return BaseQuery.__iter__(self)
def query_apply_date_filter(query: BaseQuery, date_from: Optional[int],
date_to: Optional[int]) -> BaseQuery:
if date_from is not None:
query = query.filter(Job.t_end > date_from)
if date_to is not None:
query = query.filter(Job.t_end < date_to)
return query
def gen_where(base_query: BaseQuery, query: BaseQuery,
partition: Optional[str], account: Optional[str],
state: Optional[str]) -> BaseQuery:
if partition is not None:
query = query.filter(base_query.c.partition == partition)
if account is not None:
query = query.filter(base_query.c.account == account)
if state is not None: query = query.filter(base_query.c.state == state)
return query
def __iter__(self):
"""
override __iter__ to pull results from dogpile
if particular attributes have been configured.
"""
if hasattr(self, '_cache_region'):
return self.get_value(
createfunc=lambda: list(BaseQuery.__iter__(self)))
else:
return BaseQuery.__iter__(self)
def apply_order(model: DefaultMeta, query: BaseQuery) -> BaseQuery:
sort_keys = request.args.get('sort')
if sort_keys:
for key in sort_keys.split(','):
desc = False
if key.startswith('-'):
key = key[1:]
desc = True
column_attr = getattr(model, key, None)
if column_attr is not None:
query = query.order_by(column_attr.desc()) if desc else query.order_by(column_attr)
return query
def apply_order(query: BaseQuery, sort_keys: str) -> BaseQuery:
if sort_keys:
for key in sort_keys.split(","):
desc = False
if key.startswith("-"):
key = key[1:]
desc = True
column_attr = getattr(GithubUserInfo, key, None)
if column_attr is not None:
query = (query.order_by(column_attr.desc())
if desc else query.order_by(column_attr))
return query
def ql_don_hang_all(page=1):
if not current_user.is_authenticated or current_user.ma_loai_nguoi_dung != 1:
return redirect(url_for('dang_nhap', next=request.url))
query = BaseQuery(Hoa_don, dbSession)
page_filter = query.order_by(desc(Hoa_don.ngay_tao_hoa_don)).paginate(
page, 10, False)
tieu_de = 'Tất cả đơn hàng'
return render_template('Quan_ly/QL_don_hang/QL_don_hang_all.html',
page_filter=page_filter,
tieu_de=tieu_de)
def __init__(self, start_ts, end_ts):
"""Build a query to get a distinct list of instance_id bewteen sart and end ts."""
az_query = db.session.query(Hypervisor).join(AvailabilityZone).\
filter(Hypervisor.availability_zone_id == AvailabilityZone.id).\
filter(AvailabilityZone.name.like("sa%")).\
with_entities(Hypervisor.id).subquery()
self.query = BaseQuery([Snapshot, InstanceState], db.session()).\
filter(Snapshot.ts >= start_ts, Snapshot.ts < end_ts).\
filter(InstanceState.snapshot_id == Snapshot.id).\
with_entities(InstanceState.instance_id).\
distinct(InstanceState.instance_id).\
filter(InstanceState.hypervisor_id.in_(az_query))
def query_apply_common_filter(query: BaseQuery, accounts: List[str],
partitions: List[str],
states: List[str]) -> BaseQuery:
if len(accounts) > 0:
query = query.filter(Job.account.in_(accounts))
if len(partitions) > 0:
query = query.filter(Job.partition.in_(partitions))
if len(states) > 0:
query = query.filter(Job.state.in_(states))
return query
def __iter__(self):
"""override __iter__ to pull results from dogpile if particular attributes have been configured.
Note that this approach does NOT detach the loaded objects from the current session. If the cache backend
is an in-process cache (like "memory") and lives beyond the scope of the current session's transaction,
those objects may be expired. The method here can be modified to first expunge() each loaded item from the
current session before returning the list of items, so that the items in the cache are not the same ones
in the current session.
"""
if hasattr(self, '_cache_region'):
return self.get_value(
createfunc=lambda: list(BaseQuery.__iter__(self)))
else:
return BaseQuery.__iter__(self)
def get_pagination(query: BaseQuery) -> Tuple[list, dict]:
page = request.args.get("page", 1, type=int)
limit = request.args.get("limit",
current_app.config.get("PER_PAGE", 5),
type=int)
params = {k: v for k, v in request.args.items() if k != "page"}
paginate_object = query.paginate(page, limit, False)
pagination = {
"total_pages":
paginate_object.pages,
"total_records":
paginate_object.total,
"current_page":
url_for("api.users_api.get_users_info", page=page, **params),
}
if paginate_object.has_next:
pagination["next_page"] = url_for("api.users_api.get_users_info",
page=page + 1,
**params)
if paginate_object.has_prev:
pagination["previous_page"] = url_for(
"api.users_api.get_users_info", page=page - 1, **params)
return paginate_object.items, pagination
def get_pagination(query: BaseQuery, func_name: str) -> Tuple[list, dict]:
page = request.args.get('page', 1, type=int)
limit = request.args.get('limit',
current_app.config.get('PER_PAGE', 5),
type=int)
params = {
key: value
for key, value in request.args.items() if key != 'page'
}
paginate_obj = query.paginate(page, limit, False)
pagination = {
'total_pages': paginate_obj.pages,
'total_records': paginate_obj.total,
'current_pages': url_for(func_name, page=page, **params)
}
if paginate_obj.has_next:
pagination['next_page'] = url_for(func_name, page=page + 1, **params)
if paginate_obj.has_prev:
pagination['previous_page'] = url_for(func_name,
page=page - 1,
**params)
return paginate_obj.items, pagination
def force_query_by_request_values(model, query: BaseQuery = None, order=None):
if query is None:
query = model.query
for param, value in filter(lambda x: hasattr(model, x),
request.values.keys()):
query = query.filter(getattr(model, param).like('{}%'.format(value)))
return force_pagination(query, order)
def get_pagination(query: BaseQuery, func_name: str) -> Tuple[list, dict]:
"""
Functionality of paginating response, returns modified query
page - page number to return
limit - number of items in one page to return
"""
page = request.args.get("page", 1, type=int)
limit = request.args.get("limit",
current_app.config.get("PER_PAGE", 5),
type=int)
params = {
key: value
for key, value in request.args.items() if key != "page"
}
paginate_obj = query.paginate(page, limit, False)
pagination = {
"total_pages": paginate_obj.pages,
"total_records": paginate_obj.total,
"current_page": url_for(func_name, page=page, **params),
}
if paginate_obj.has_next:
pagination["next_page"] = url_for(func_name, page=page + 1, **params)
if paginate_obj.has_prev:
pagination["previous_page"] = url_for(func_name,
page=page - 1,
**params)
return paginate_obj.items, pagination
def paginate(query: BaseQuery, schema: Schema) -> dict:
if not request.endpoint:
raise Exception("Cannot paginate outside of request context.")
page = request.args.get("page", DEFAULT_PAGE_NUMBER)
per_page = request.args.get("page_size", DEFAULT_PAGE_SIZE)
page_obj = query.paginate(page=page, per_page=per_page)
next_ = url_for(
request.endpoint,
page=page_obj.next_num if page_obj.has_next else page_obj.page,
per_page=per_page,
**request.view_args)
prev = url_for(
request.endpoint,
page=page_obj.prev_num if page_obj.has_prev else page_obj.page,
per_page=per_page,
**request.view_args)
return {
"total": page_obj.total,
"pages": page_obj.pages,
"next": next_,
"prev": prev,
"results": schema.dump(page_obj.items),
}
def _query_join_relation(self, query: BaseQuery,
root_relation: str) -> BaseQuery:
"""
Helper function that applies necessary joins for dotted columns on a
SQLAlchemy query object
:param query: SQLAlchemy query object
:param root_relation: The root part of a dotted column, so the root relation
:return: Transformed SQLAlchemy Query
"""
relations = self.get_related_model_and_join(root_relation)
for relation in relations:
model_relation, relation_join = relation
# Support multiple joins for the same table
if self.is_model_already_joined(query, model_relation):
# Since the join already exists apply a new aliased one
model_relation = aliased(model_relation)
# The binary expression needs to be inverted
relation_pk = self.get_pk(model_relation)
relation_join = BinaryExpression(relation_join.left,
relation_pk,
relation_join.operator)
query = query.join(model_relation, relation_join, isouter=True)
return query
def set_search_filter(
query: BaseQuery,
obj_model: Model,
search_field: str = None,
search_value=None,
*args,
**kwargs,
) -> BaseQuery:
"""
Utility for modifying a SQLAlchemy query by adding a filter. This uses the 'LIKE' modifier from SQL, and adds
wildcards to either side of the query. This does _not_ support lists of values to search through
Args:
query:
obj_model:
search_field:
search_value:
*args:
**kwargs:
Returns:
object:
"""
if search_field is not None and search_value is not None:
column = next(
(c for c in inspect(obj_model).columns if c.name == search_field),
None)
if column is not None:
query = query.filter(column.like(f"%{search_value}%"))
return query
def get_filtered_query(self, query: BaseQuery, **kwargs) -> BaseQuery:
"""Filter query using query parameters"""
if 'q' in kwargs:
return query.filter(
ProductGroup.slug.ilike('%{}%'.format(kwargs['q'])))
return super().get_filtered_query(query, **kwargs)
def query_articles_by_tag(self, tag: str, query_obj: BaseQuery = None):
if query_obj is not None:
query_obj = query_obj.join(Article.tags).filter(Tag.name == tag)
else:
query_obj = Article.query.join(
Article.tags).filter(Tag.name == tag)
return query_obj
def paginated_search_query(model, query: BaseQuery = None):
if query is None:
query = model.query
for arg_name, value in request.args.items():
if arg_name == 'row_id': arg_name = 'id'
field = getattr(model, arg_name, None)
if field is not None and value:
from sqlalchemy import DateTime, Date
is_date_field = isinstance(field.property.columns[0].type,
(Date, DateTime))
if is_date_field and '_' in value:
value_from, value_to = value.split('_', 1)
query = query.filter(field.between(value_from, value_to))
else:
query = query.filter(field.like('%{}%'.format(value)))
return force_pagination(query)
def _filter_query_on_degrees(degree_list: List[str],
query: BaseQuery) -> BaseQuery:
regular_degree_filters = [
degree for degree in degree_list
if degree not in ["dmd / dds", "md / do"]
]
# TODO replace this with alias tags
md_do_filter = ([
func.bool_or(func.lower(DegreeOption.value) == "md")
| func.bool_or(func.lower(DegreeOption.value) == "do")
] if "md / do" in degree_list else [])
dmd_dds_filter = ([
func.bool_or(func.lower(DegreeOption.value) == "dmd")
| func.bool_or(func.lower(DegreeOption.value) == "dds")
] if "dmd / dds" in degree_list else [])
degree_filters = ([
func.bool_or(func.lower(DegreeOption.value) == degree)
for degree in regular_degree_filters
] + md_do_filter + dmd_dds_filter)
degree_filter = reduce(operator.and_, degree_filters)
return (
query.outerjoin(FacultyProfileDegree).outerjoin(DegreeOption).group_by(
FacultyProfile.id).having(degree_filter))
def force_pagination(query: BaseQuery, order=None):
query = query.order_by(order if order is not None else desc('id'))
page = int(request.values.get('page', 1))
per_page = int(request.values.get('per_page', FETCH_LIMIT))
pagination = query.paginate(page, per_page,
error_out=False) # type: Pagination
return jsonify({
'rows': [instance.to_json() for instance in pagination.items],
'max_page':
pagination.pages,
'curr_page':
pagination.page,
'next_page':
pagination.next_num,
'prev_page':
pagination.prev_num,
})
def query_articles_by_search(self, q: str, query_obj: BaseQuery = None):
if query_obj is not None:
query_obj = query_obj.filter(
or_(Article.title.like(f'%{q}%'), Article.desc.like(f'%{q}%')))
else:
query_obj = Article.query.filter(
or_(Article.title.like(f'%{q}%'), Article.desc.like(f'%{q}%')))
return query_obj
def ql_so_luong_ton(page):
if not current_user.is_authenticated or current_user.ma_loai_nguoi_dung != 2:
return redirect(url_for('log_in', next=request.url))
form = Form_tim_kiem()
query = BaseQuery(San_pham, dbSession)
page_filter = query.paginate(page,5,False)
if form.validate_on_submit():
tim_kiem = form.noi_dung.data
if tim_kiem.isdigit():
page_filter = query.filter(San_pham.ma_san_pham == int(tim_kiem)).paginate(page,5,False)
else:
chuoi_truy_van = '%'+tim_kiem.upper()+'%'
page_filter = query.filter(San_pham.ten_san_pham.like(chuoi_truy_van)).paginate(page,5,False)
if len(page_filter.items) == 0:
thong_bao = 'Không tìm thấy sản phẩm!'
return render_template('Quan_ly/QL_kho_hang/Ton_kho.html', form=form, page_filter = page_filter)
def paginate(self, page=None, per_page=None, error_out=True, max_per_page=None): paginate = BaseQuery.paginate(self, page=page, per_page=per_page, error_out=error_out, max_per_page=max_per_page) return Pagination(self, paginate.page, paginate.per_page, paginate.total, paginate.items )
def new_view_ddl(*entities):
"""
replacement of db.session.query
Can't use db.session.query, as there is no session on start
:param entities:
:return:
"""
return BaseQuery(entities)
def apply_order(model: DefaultMeta, query: BaseQuery) -> BaseQuery:
"""
Functionality of sorting resources, returns sort arguments to query
(example: sort=-id,last_name).
"""
sort_keys = request.args.get("sort")
if sort_keys:
for key in sort_keys.split(","):
desc = False
if key.startswith("-"):
key = key[1:]
desc = True
column_attr = getattr(model, key, None)
if column_attr is not None:
query = (query.order_by(column_attr.desc())
if desc else query.order_by(column_attr))
return query
def __iter__(self):
"""
Override __iter__ to pull results from cache if particular
attributes have been configured.
This approach does *not* detach the loaded objects from the current
session. If the cache backend is an in-process cache (like "memory")
and lives beyond the scope of the current session's transaction, those
objects may be expired.
The method here can be modified to first expunge() each loaded item
from the current session before returning the list of items, so that
the items in the cache are not the same ones in the current Session.
"""
if hasattr(self, '_cache'):
func = lambda: list(BaseQuery.__iter__(self))
return iter(self.get_value(createfunc=func))
else:
return BaseQuery.__iter__(self)
def __init__(self, start_ts, end_ts):
"""Build a query to get a distinct list of instance_id bewteen sart and end ts."""
az_query = db.session.query(Hypervisor).join(AvailabilityZone).\
filter(Hypervisor.availability_zone_id == AvailabilityZone.id).\
filter(AvailabilityZone.name.like("sa%")).\
with_entities(Hypervisor.id).subquery()
self.query = BaseQuery([Snapshot, InstanceState], db.session()).\
filter(Snapshot.ts >= start_ts, Snapshot.ts < end_ts).\
filter(InstanceState.snapshot_id == Snapshot.id).\
with_entities(InstanceState.instance_id).\
distinct(InstanceState.instance_id).\
filter(InstanceState.hypervisor_id.in_(az_query))
class Summary():
"""list distinct instance on sa node between start_ts and end_ts """
query = None
def __init__(self, start_ts, end_ts):
"""Build a query to get a distinct list of instance_id bewteen sart and end ts."""
az_query = db.session.query(Hypervisor).join(AvailabilityZone).\
filter(Hypervisor.availability_zone_id == AvailabilityZone.id).\
filter(AvailabilityZone.name.like("sa%")).\
with_entities(Hypervisor.id).subquery()
self.query = BaseQuery([Snapshot, InstanceState], db.session()).\
filter(Snapshot.ts >= start_ts, Snapshot.ts < end_ts).\
filter(InstanceState.snapshot_id == Snapshot.id).\
with_entities(InstanceState.instance_id).\
distinct(InstanceState.instance_id).\
filter(InstanceState.hypervisor_id.in_(az_query))
def value(self):
return [item[0] for item in self.query.all()]
def __init__(self, query: BaseQuery, page_param: str = 'page') -> None:
"""
Initialize the pagination object.
The current page will be determined from the page parameter in the request arguments. If the page parameter
is not included in the request arguments, the current page defaults to `1`.
:param query: The base query that will be paginated.
:param page_param: The name of the page parameter specifying the current page. Defaults to `page`.
"""
# Get the current page from the request arguments.
try:
self.current_page = request.args.get(page_param, 1, type=int)
except TypeError:
# In tests, the above call fails because get does not take keyword arguments - it does however work outside
# of requests. Thus, fall back to this manual conversion.
self.current_page = int(request.args.get(page_param, 1))
application = get_app()
self.rows_per_page = application.config['ITEMS_PER_PAGE']
self._rows = query.paginate(self.current_page, self.rows_per_page)
def __init__ (self, entities, session = None):
BaseQuery.__init__ (self, entities, session)
self._prefixes = ['SQL_CACHE']
def __init__(self, entities, *args, **kw):
BaseQuery.__init__(self, entities=entities, *args, **kw)
