async def list_instances(cls: models.BASE,
session: models.Session,
filter_spec: List[Dict[str, Any]] = None,
sort_spec: List[Dict[str, str]] = None,
offset: types.NonNegativeInt = 0,
limit: PositiveInt = None,
options: Any = None) -> List[dict]:
""" Return all instances of cls """
query = session.query(cls)
if filter_spec:
query = apply_filters(query, filter_spec)
if sort_spec:
query = apply_sort(query, sort_spec)
if options:
query = query.options(options)
if limit:
query = query.limit(limit)
query = query.offset(offset)
def _list():
return [instance.as_dict() for instance in query.all()]
return await run_in_threadpool(_list)
def get_records(
table,
engine,
limit=None,
offset=None,
order_by=[],
filters=[],
):
"""
Returns records from a table.
Args:
table: SQLAlchemy table object
engine: SQLAlchemy engine object
limit: int, gives number of rows to return
offset: int, gives number of rows to skip
order_by: list of dictionaries, where each dictionary has a 'field' and
'direction' field.
See: https://github.com/centerofci/sqlalchemy-filters#sort-format
filters: list of dictionaries, where each dictionary has a 'field' and 'op'
field, in addition to an 'value' field if appropriate.
See: https://github.com/centerofci/sqlalchemy-filters#filters-format
"""
query = select(table).limit(limit).offset(offset)
if order_by is not None:
query = apply_sort(query, order_by)
if filters is not None:
query = apply_filters(query, filters)
with engine.begin() as conn:
return conn.execute(query).fetchall()
def _apply_filters_and_pagination(self,
page_number=PAGE_DEFAULT,
page_size=PER_PAGE_DEFAULT,
sort_field=None,
sort_dir=None,
status=None,
noticeofworktype=None,
mine_region=None,
trackingnumber=None,
mine_search=None):
filters = []
base_query = Application.query
if noticeofworktype is not None:
filters.append(
func.lower(Application.noticeofworktype).contains(
func.lower(noticeofworktype)))
if trackingnumber is not None:
filters.append(Application.trackingnumber == trackingnumber)
if mine_region is not None or mine_search is not None:
base_query = base_query.join(Mine)
if mine_region is not None:
region_filter_values = mine_region.split(',')
filters.append(Mine.mine_region.in_(region_filter_values))
if mine_search is not None:
filters.append(
or_(
func.lower(Application.minenumber).contains(
func.lower(mine_search)),
func.lower(Mine.mine_name).contains(
func.lower(mine_search)),
func.lower(Mine.mine_no).contains(
func.lower(mine_search))))
status_filter_values = []
if status is not None:
status_filter_values = status.split(',')
if len(status_filter_values) > 0:
status_filters = []
for status in status_filter_values:
status_filters.append(
func.lower(Application.status).contains(
func.lower(status)))
filters.append(or_(*status_filters))
base_query = base_query.filter(*filters)
if sort_field and sort_dir:
sort_criteria = [{
'model': 'Application',
'field': sort_field,
'direction': sort_dir
}]
base_query = apply_sort(base_query, sort_criteria)
return apply_pagination(base_query, page_number, page_size)
def search_filter_sort_paginate(
db_session,
model,
query_str: str = None,
filter_spec: List[dict] = None,
page: int = 1,
items_per_page: int = 5,
sort_by: List[str] = None,
descending: List[bool] = None,
current_user: DispatchUser = None,
):
"""Common functionality for searching, filtering, sorting, and pagination."""
model_cls = get_class_by_tablename(model)
sort_spec = create_sort_spec(model, sort_by, descending)
query = db_session.query(model_cls)
if query_str:
sort = False if sort_spec else True
query = search(db_session=db_session, query_str=query_str, model=model, sort=sort)
query = apply_model_specific_filters(model, query, current_user)
query = apply_filters(query, filter_spec)
query = apply_sort(query, sort_spec)
if items_per_page == -1:
items_per_page = None
query, pagination = apply_pagination(query, page_number=page, page_size=items_per_page)
return {
"items": query.all(),
"itemsPerPage": pagination.page_size,
"page": pagination.page_number,
"total": pagination.total_results,
}
def get_sections(self, db: Session, *, filter_spec: list, paginate: dict, sort_spec: list) -> List[Optional[Section]]:
"""
查询所有板块信息
:param filter_spec: 过滤查询的条件
:param sort_spec: 排序（field: 字段名，direction: 排序方式）
:param paginate: page: 当前页数，limit: 每页显示的条数
:return: 板块信息列表, 总记录数, 总页数
"""
query = db.query(Section).filter(Section.status == 0) # 查询出所有板块信息
# --------------- 过滤 ---------------- #
filters = []
for filter in filter_spec:
if filter["value"]:
data = {"model": "Section", "field": filter["field"], "option": filter["option"], "value": filter["value"]}
filters.append(data)
filters_query = apply_filters(query, filters)
# ------------- 排序 -------------- #
sort_specs = []
for sort in sort_spec:
sort_specs.append({"model": "Section", "field": sort["field"], "direction": sort["direction"]})
sorted_query = apply_sort(filters_query, sort_specs)
# --------------- 分页 ---------------- #
page_number = paginate["page_number"]
page_size = paginate["page_size"]
pagination_query, pagination = apply_pagination(
sorted_query, page_number=page_number, page_size=page_size)
db_obj = pagination_query.all()
total = pagination.total_results # 总记录数
number_page = pagination.num_pages # 总页数
return db_obj, total, number_page
def search_filter_sort_paginate(
db_session,
model,
query_str: str = None,
page: int = 1,
items_per_page: int = 5,
sort_by: List[str] = None,
descending: List[bool] = None,
fields: List[str] = None,
ops: List[str] = None,
values: List[str] = None,
):
"""Common functionality for searching, filtering and sorting"""
if query_str:
query = search(db_session=db_session, query_str=query_str, model=model)
else:
query = get_all(db_session=db_session, model=model)
filter_spec = create_filter_spec(model, fields, ops, values)
query = apply_filters(query, filter_spec)
sort_spec = create_sort_spec(model, sort_by, descending)
query = apply_sort(query, sort_spec)
if items_per_page == -1:
items_per_page = None
query, pagination = apply_pagination(query, page_number=page, page_size=items_per_page)
return {
"items": query.all(),
"itemsPerPage": pagination.page_size,
"page": pagination.page_number,
"total": pagination.total_results,
}
def test_get_group_counts_limit_offset_ordering(roster_table_obj, limit,
offset):
roster, engine = roster_table_obj
order_by = [{"field": "Grade", "direction": "desc", "nullslast": True}]
group_by = [roster.c["Grade"]]
counts = records.get_group_counts(roster,
engine,
group_by,
limit=limit,
offset=offset,
order_by=order_by)
query = select(group_by[0])
query = apply_sort(query, order_by)
with engine.begin() as conn:
all_records = list(conn.execute(query))
if limit is None:
end = None
elif offset is None:
end = limit
else:
end = limit + offset
limit_offset_records = all_records[offset:end]
manual_count = Counter(limit_offset_records)
assert len(counts) == len(manual_count)
for value, count in manual_count.items():
assert value in counts
assert counts[value] == count
def get_group_counts(
table,
engine,
group_by,
limit=None,
offset=None,
order_by=[],
filters=[],
):
"""
Returns counts by specified groupings
Args:
table: SQLAlchemy table object
engine: SQLAlchemy engine object
limit: int, gives number of rows to return
offset: int, gives number of rows to skip
group_by: list or tuple of column names or column objects to group by
order_by: list of dictionaries, where each dictionary has a 'field' and
'direction' field.
See: https://github.com/centerofci/sqlalchemy-filters#sort-format
filters: list of dictionaries, where each dictionary has a 'field' and 'op'
field, in addition to an 'value' field if appropriate.
See: https://github.com/centerofci/sqlalchemy-filters#filters-format
"""
if type(group_by) not in (tuple, list):
raise BadGroupFormat(f"Group spec {group_by} must be list or tuple.")
for field in group_by:
if type(field) not in (str, Column):
raise BadGroupFormat(
f"Group field {field} must be a string or Column.")
field_name = field if type(field) == str else field.name
if field_name not in table.c:
raise GroupFieldNotFound(
f"Group field {field} not found in {table}.")
query = (select(table).limit(limit).offset(offset))
if order_by is not None:
query = apply_sort(query, order_by)
if filters is not None:
query = apply_filters(query, filters)
subquery = query.subquery()
group_by = [
subquery.columns[col]
if type(col) == str else subquery.columns[col.name] for col in group_by
]
query = select(*group_by, func.count(subquery.c[ID])).group_by(*group_by)
with engine.begin() as conn:
records = conn.execute(query).fetchall()
# Last field is the count, preceding fields are the group by fields
counts = {(*record[:-1], ): record[-1] for record in records}
return counts
def list(self, filters=None, order_by=None, offset=None, limit=None):
query = self.query
if filters:
query = apply_filters(query, filters)
if order_by:
query = apply_sort(query, order_by)
if offset:
query = query.offset(offset)
if limit:
query = query.limit(limit)
return query.all()
def get(self):
user_name = User().get_user_username()
mine_query = Mine.query.filter_by(
deleted_ind=False).join(Subscription).filter_by(
user_name=user_name)
sort_criteria = [{
'model': 'Mine',
'field': 'mine_name',
'direction': 'asc'
}]
mine_query = apply_sort(mine_query, sort_criteria)
mines = mine_query.all()
return mines
def apply_filter_and_search(self, args):
# Handle ListView request
items_per_page = args['per_page']
page = args['page']
# parse the filter terms
first_name_filter_term = args['first_name']
last_name_filter_term = args['last_name']
party_name_filter_term = args['party_name']
type_filter_term = args['type']
role_filter_term = args['role']
email_filter_term = args['email']
phone_filter_term = args['phone_no']
conditions = []
if first_name_filter_term:
conditions.append(
Party.first_name.ilike('%{}%'.format(first_name_filter_term)))
if last_name_filter_term:
conditions.append(
Party.party_name.ilike('%{}%'.format(last_name_filter_term)))
if party_name_filter_term:
conditions.append(
Party.party_name.ilike('%{}%'.format(party_name_filter_term)))
if email_filter_term:
conditions.append(
Party.email.ilike('%{}%'.format(email_filter_term)))
if type_filter_term:
conditions.append(Party.party_type_code.like(type_filter_term))
if phone_filter_term:
conditions.append(
Party.phone_no.ilike('%{}%'.format(phone_filter_term)))
if role_filter_term == "NONE":
conditions.append(Party.mine_party_appt == None)
contact_query = Party.query.filter(and_(*conditions))
if role_filter_term and not role_filter_term == "NONE":
role_filter = MinePartyAppointment.mine_party_appt_type_code.like(
role_filter_term)
role_query = Party.query.join(MinePartyAppointment).filter(
role_filter)
contact_query = contact_query.intersect(
role_query) if contact_query else role_query
sort_criteria = [{
'model': 'Party',
'field': 'party_name',
'direction': 'asc'
}]
contact_query = apply_sort(contact_query, sort_criteria)
return apply_pagination(contact_query, page, items_per_page)
def get(self, mine_guid):
sort_field = request.args.get('sort_field', 'receiveddate', type=str)
sort_dir=request.args.get('sort_dir', 'desc', type=str)
filtered_query = self._apply_filters(
mine_guid,
status=request.args.get('status', type=str),
noticeofworktype=request.args.get('noticeofworktype', type=str),
trackingnumber=request.args.get('trackingnumber', type=int))
if sort_field and sort_dir:
sort_criteria = [{'model': 'Application', 'field': sort_field, 'direction': sort_dir}]
filtered_query = apply_sort(filtered_query, sort_criteria)
return filtered_query.all()
def search_filter_sort_paginate(
db_session,
model,
query_str: str = None,
filter_spec: List[dict] = None,
page: int = 1,
items_per_page: int = 5,
sort_by: List[str] = None,
descending: List[bool] = None,
fields: List[str] = None,
ops: List[str] = None,
values: List[str] = None,
join_attrs: List[str] = None,
user_role: UserRoles = UserRoles.user,
):
"""Common functionality for searching, filtering and sorting"""
model_cls = get_class_by_tablename(model)
sort_spec = create_sort_spec(model, sort_by, descending)
if query_str:
sort = False if sort_spec else True
query = search(db_session=db_session,
query_str=query_str,
model=model,
sort=sort)
else:
query = db_session.query(model_cls)
query = join_required_attrs(query, model_cls, join_attrs, fields, sort_by)
if not filter_spec:
filter_spec = create_filter_spec(model, fields, ops, values, user_role)
query = apply_filters(query, filter_spec)
query = apply_sort(query, sort_spec)
if items_per_page == -1:
items_per_page = None
query, pagination = apply_pagination(query,
page_number=page,
page_size=items_per_page)
return {
"items": query.all(),
"itemsPerPage": pagination.page_size,
"page": pagination.page_number,
"total": pagination.total_results,
}
def get_topics(self, db: Session, *, filter_spec: list, paginate: dict,
sort_spec: list) -> List[Optional[Topic]]:
"""
查询所有主贴信息
:param filter_spec: 过滤查询的条件
:param sort_spec: 排序（field: 字段名，direction: 排序方式）
:param paginate: page: 当前页数，limit: 每页显示的条数
:return: 主贴信息列表, 总记录数, 总页数
"""
query = db.query(Topic, Section,
User).filter(Topic.userid == User.id,
Topic.sectionid == Section.id,
Topic.status == 0)
# --------------- 过滤 ---------------- #
filters = []
for filter in filter_spec: # 过滤
if filter["value"]:
data = {
"model": "Topic",
"field": filter["field"],
"option": filter["option"],
"value": filter["value"]
}
filters.append(data)
filters_query = apply_filters(query, filters) # 根据查询条件查询
# ------------- 排序 -------------- #
sort_specs = []
for sort in sort_spec:
sort_specs.append({
"model": "Topic",
"field": sort["field"],
"direction": sort["direction"]
})
sorted_query = apply_sort(filters_query, sort_specs)
# --------------- 分页 ---------------- #
page_number = paginate["page_number"]
page_size = paginate["page_size"]
pagination_query, pagination = apply_pagination(
sorted_query, page_number=page_number, page_size=page_size) # 分页
db_result_obj = pagination_query.all()
total = pagination.total_results # 总记录数
number_page = pagination.num_pages # 总页数
return db_result_obj, total, number_page
async def count_instances(
cls: models.BASE,
session: models.Session,
filter_spec: List[Dict[str, Any]] = None,
sort_spec: List[Dict[str, Any]] = None,
) -> int:
""" Total count of instances matching the given criteria """
query = session.query(cls)
if filter_spec:
query = apply_filters(query, filter_spec)
if sort_spec:
query = apply_sort(query, sort_spec)
def _count():
return query.count()
return await run_in_threadpool(_count)
def _apply_filters_and_pagination(self,
page_number=DEFAULT_PAGE_NUMBER,
page_size=DEFAULT_PAGE_SIZE,
sort_field=None,
sort_dir=None,
id=None,
guid=None,
company_name=None,
application_status_code=[],
application_phase_code=[]):
base_query = Application.query
filters = []
if id:
filters.append(Application.id == id)
if guid:
filters.append(Application.guid == guid)
if application_status_code:
base_query = base_query.filter(
Application.application_status_code.in_(
application_status_code))
if application_phase_code:
base_query = base_query.filter(
Application.application_phase_code.in_(application_phase_code))
if company_name:
filters.append(Application.json['company_details']['company_name']
['label'].astext.contains(company_name.upper()))
base_query = base_query.filter(*filters)
if sort_field and sort_dir:
sort_criteria = [{
'model': 'Application',
'field': sort_field,
'direction': sort_dir,
}]
base_query = apply_sort(base_query, sort_criteria)
return apply_pagination(base_query, page_number, page_size)
def get_signin_logs(self, db: Session, *, filter_spec: list,
paginate: dict, sort_spec: list) -> List[User]:
"""
查询所有用户登录日志信息
:param filter_spec: 过滤查询的条件
:param sort_spec: 排序（field: 字段名，direction: 排序方式）
:param paginate: page: 当前页数，limit: 每页显示的条数
:return: 用户登录日志信息关联列表, 总记录数, 总页数
"""
query = db.query(SigninLog,
User).filter(SigninLog.userid == User.id,
User.is_logoff == 0) # 查询关联表之间的所有信息
# --------------- 过滤 ---------------- #
filters = []
for filter in filter_spec:
if filter["value"]:
filters.append({
"model": "SigninLog",
"field": filter["field"],
"option": filter["option"],
"value": filter["value"]
})
filters_query = apply_filters(query, filters)
# --------------- 排序 ---------------- #
sort_specs = []
for sort in sort_spec:
sort_specs.append({
"model": "SigninLog",
"field": sort["field"],
"direction": sort["direction"]
})
sorted_query = apply_sort(filters_query, sort_specs)
# --------------- 分页 ---------------- #
page_number = paginate["page_number"]
page_size = paginate["page_size"]
pagination_query, pagination = apply_pagination(
sorted_query, page_number=page_number, page_size=page_size)
db_result_obj = pagination_query.all()
total = pagination.total_results # 总记录数
number_page = pagination.num_pages # 总页数
return db_result_obj, total, number_page
def get_users(self, db: Session, *, filter_spec: list, paginate: dict,
sort_spec: list) -> List[User]:
"""
查询所有用户信息
:param filter_spec: 过滤查询的条件
:param sort_spec: 排序（field: 字段名，direction: 排序方式）
:param paginate: page: 当前页数，limit: 每页显示的条数
:return: 用户信息列表, 总记录数, 总页数
"""
query = db.query(User).filter(User.is_logoff == 0) # 查询出所有未注销的用户
# --------------- 过滤 ---------------- #
filters = []
for filter in filter_spec:
if filter["value"]:
data = {
"model": "User",
"field": filter["field"],
"option": filter["option"],
"value": filter["value"]
}
filters.append(data)
filters_query = apply_filters(query, filters) # 根据查询条件查询
# ------------- 排序 -------------- #
sort_specs = []
for sort in sort_spec:
sort_specs.append({
"model": "User",
"field": sort["field"],
"direction": sort["direction"]
})
sorted_query = apply_sort(filters_query, sort_specs)
# --------------- 分页 ---------------- #
page_number = paginate["page_number"]
page_size = paginate["page_size"]
pagination_query, pagination = apply_pagination(
sorted_query, page_number=page_number, page_size=page_size) # 分页
db_result_obj = pagination_query.all() # 符合条件记录
total = pagination.total_results # 总记录数
number_page = pagination.num_pages # 总页数
return db_result_obj, total, number_page
def _apply_filters_and_pagination(self, args):
sort_models = {
'variance_id': 'Variance',
'compliance_article_id': 'Variance',
'lead_inspector': 'Variance',
'received_date': 'Variance',
'mine_name': 'Mine',
'variance_application_status_code': 'Variance'
}
status_filter_values = list(
map(lambda x: x.variance_application_status_code,
VarianceApplicationStatusCode.active()))
conditions = []
if args["application_status"] is not None:
status_filter_values = args["application_status"].split(',')
conditions.append(
self._build_filter('Variance',
'variance_application_status_code', 'in',
status_filter_values))
if args["compliance_codes"] is not None:
compliance_codes_values = args["compliance_codes"].split(',')
conditions.append(
self._build_filter('Variance', 'compliance_article_id', 'in',
compliance_codes_values))
if args["expiry_date_before"] is not None:
conditions.append(
self._build_filter('Variance', 'expiry_date', '<=',
args["expiry_date_before"]))
if args["expiry_date_after"] is not None:
conditions.append(
self._build_filter('Variance', 'expiry_date', '>=',
args["expiry_date_after"]))
if args["issue_date_before"] is not None:
conditions.append(
self._build_filter('Variance', 'issue_date', '<=',
args["issue_date_before"]))
if args["issue_date_after"] is not None:
conditions.append(
self._build_filter('Variance', 'issue_date', '>=',
args["issue_date_after"]))
if args["major"] is not None:
conditions.append(
self._build_filter('Mine', 'major_mine_ind', '==',
args["major"]))
if args["search_terms"] is not None:
search_conditions = [
self._build_filter('Mine', 'mine_name', 'ilike',
'%{}%'.format(args["search_terms"])),
self._build_filter('Mine', 'mine_no', 'ilike',
'%{}%'.format(args["search_terms"]))
]
conditions.append({'or': search_conditions})
if args["region"] is not None:
region_list = args["region"].split(',')
conditions.append(
self._build_filter('Mine', 'mine_region', 'in', region_list))
query = Variance.query.join(Mine).join(ComplianceArticle)
# Apply sorting
if args['sort_field'] and args['sort_dir']:
# The compliance sorting must be custom due to the code being stored in multiple columns.
if args['sort_field'] == "compliance_article_id":
if args['sort_dir'] == 'desc':
filtered_query = apply_filters(
query.order_by(
desc(cast(ComplianceArticle.section, NUMERIC)),
desc(cast(ComplianceArticle.sub_section, NUMERIC)),
desc(cast(ComplianceArticle.paragraph, NUMERIC)),
desc(ComplianceArticle.sub_paragraph)), conditions)
elif args['sort_dir'] == 'asc':
filtered_query = apply_filters(
query.order_by(
cast(ComplianceArticle.section, NUMERIC),
cast(ComplianceArticle.sub_section, NUMERIC),
cast(ComplianceArticle.paragraph, NUMERIC),
ComplianceArticle.sub_paragraph), conditions)
elif args['sort_field'] == "lead_inspector":
query = query.outerjoin(
Party, Variance.inspector_party_guid == Party.party_guid)
filtered_query = apply_filters(query, conditions)
sort_criteria = [{
'model': 'Party',
'field': 'party_name',
'direction': args['sort_dir']
}]
filtered_query = apply_sort(filtered_query, sort_criteria)
else:
filtered_query = apply_filters(query, conditions)
sort_criteria = [{
'model': sort_models[args['sort_field']],
'field': args['sort_field'],
'direction': args['sort_dir']
}]
filtered_query = apply_sort(filtered_query, sort_criteria)
else:
filtered_query = apply_filters(query, conditions)
# default sorting is by submission date descending
sort_criteria = [{
'model': 'Variance',
'field': 'received_date',
'direction': 'desc'
}]
filtered_query = apply_sort(filtered_query, sort_criteria)
return apply_pagination(filtered_query, args["page_number"],
args["page_size"])
def apply_filter_and_search(self, args):
sort_models = {
'mine_name': 'Mine',
'mine_no': 'Mine',
'mine_region': 'Mine'
}
# Handle ListView request
items_per_page = args.get('per_page', 25, type=int)
page = args.get('page', 1, type=int)
sort_field = args.get('sort_field', 'mine_name', type=str)
sort_dir = args.get('sort_dir', 'asc', type=str)
sort_model = sort_models.get(sort_field)
search_term = args.get('search', None, type=str)
# Filters to be applied
commodity_filter_terms = args.get('commodity', None, type=str)
status_filter_term = args.get('status', None, type=str)
tenure_filter_term = args.get('tenure', None, type=str)
region_code_filter_term = args.get('region', None, type=str)
major_mine_filter_term = args.get('major', None, type=str)
tsf_filter_term = args.get('tsf', None, type=str)
# Base query:
mines_query = Mine.query
# Filter by search_term if provided
if search_term:
search_term = search_term.strip()
name_filter = Mine.mine_name.ilike('%{}%'.format(search_term))
number_filter = Mine.mine_no.ilike('%{}%'.format(search_term))
permit_filter = Permit.permit_no.ilike('%{}%'.format(search_term))
mines_name_query = Mine.query.filter(name_filter | number_filter)
permit_query = Mine.query.join(Permit).filter(permit_filter)
mines_query = mines_name_query.union(permit_query)
# Filter by Major Mine, if provided
if major_mine_filter_term == "true" or major_mine_filter_term == "false":
major_mine_filter = Mine.major_mine_ind.is_(
major_mine_filter_term == "true")
major_mine_query = Mine.query.filter(major_mine_filter)
mines_query = mines_query.intersect(major_mine_query)
# Filter by TSF, if provided
if tsf_filter_term == "true" or tsf_filter_term == "false":
tsf_filter = Mine.mine_tailings_storage_facilities != None if tsf_filter_term == "true" else \
Mine.mine_tailings_storage_facilities == None
tsf_query = Mine.query.filter(tsf_filter)
mines_query = mines_query.intersect(tsf_query)
# Filter by region, if provided
if region_code_filter_term:
region_filter_term_array = region_code_filter_term.split(',')
region_filter = Mine.mine_region.in_(region_filter_term_array)
region_query = Mine.query.filter(region_filter)
mines_query = mines_query.intersect(region_query)
# Filter by commodity if provided
if commodity_filter_terms:
commodity_filter_term_array = commodity_filter_terms.split(',')
commodity_filter = MineTypeDetail.mine_commodity_code.in_(
commodity_filter_term_array)
mine_type_active_filter = MineType.active_ind.is_(True)
commodity_query = Mine.query \
.join(MineType) \
.join(MineTypeDetail) \
.filter(commodity_filter, mine_type_active_filter)
mines_query = mines_query.intersect(commodity_query)
# Create a filter on tenure if one is provided
if tenure_filter_term:
tenure_filter_term_array = tenure_filter_term.split(',')
tenure_filter = MineType.mine_tenure_type_code.in_(
tenure_filter_term_array)
mine_type_active_filter = MineType.active_ind.is_(True)
tenure_query = Mine.query \
.join(MineType) \
.filter(tenure_filter, mine_type_active_filter)
mines_query = mines_query.intersect(tenure_query)
# Create a filter on mine status if one is provided
if status_filter_term:
status_filter_term_array = status_filter_term.split(',')
status_filter = MineStatusXref.mine_operation_status_code.in_(
status_filter_term_array)
status_reason_filter = MineStatusXref.mine_operation_status_reason_code.in_(
status_filter_term_array)
status_subreason_filter = MineStatusXref.mine_operation_status_sub_reason_code.in_(
status_filter_term_array)
all_status_filter = status_filter | status_reason_filter | status_subreason_filter
status_query = Mine.query \
.join(MineStatus) \
.join(MineStatusXref) \
.filter(all_status_filter, MineStatus.active_ind == True)
mines_query = mines_query.intersect(status_query)
deleted_filter = [{
'field': 'deleted_ind',
'op': '==',
'value': 'False'
}]
mines_query = apply_filters(mines_query, deleted_filter)
# Apply sorting
if sort_model and sort_field and sort_dir:
sort_criteria = [{
'model': sort_model,
'field': sort_field,
'direction': sort_dir
}]
mines_query = apply_sort(mines_query, sort_criteria)
return apply_pagination(mines_query, page, items_per_page)
def _apply_filters_and_pagination(self, args):
sort_models = {
"mine_report_id": 'MineReport',
"mine_report_category": 'MineReportCategoryXref',
"report_name": 'MineReportDefinition',
"due_date": 'MineReport',
"received_date": 'MineReport',
"submission_year": 'MineReport',
"mine_report_status_code": 'MineReportSubmissionStatusCode',
"created_by_idir": 'MineReport',
"mine_name": 'Mine',
}
sort_field = {
"mine_report_id": 'mine_report_id',
"mine_report_category": 'mine_report_category',
"report_name": 'report_name',
"due_date": 'due_date',
"received_date": 'received_date',
"submission_year": 'submission_year',
"mine_report_status_code": 'mine_report_status_description',
"created_by_idir": 'created_by_idir',
"mine_name": 'mine_name',
}
query = MineReport.query.filter_by(deleted_ind=False)
conditions = []
if args["search_terms"] or args["major"] or args["region"] or (
args["sort_field"]
and sort_models[args['sort_field']] == 'Mine'):
query = query.join(Mine)
if args["report_type"] or args["report_name"] or (
args['sort_field'] and sort_models[args['sort_field']]
in ['MineReportCategoryXref', 'MineReportDefinition']):
query = query.join(
MineReportDefinition, MineReport.mine_report_definition_id ==
MineReportDefinition.mine_report_definition_id)
query = query.join(
MineReportCategoryXref,
MineReportDefinition.mine_report_definition_id ==
MineReportCategoryXref.mine_report_definition_id)
query = query.join(
MineReportCategory, MineReportCategoryXref.mine_report_category
== MineReportCategory.mine_report_category)
if args["major"]:
conditions.append(
self._build_filter('Mine', 'major_mine_ind', '==',
args["major"]))
if args["region"]:
conditions.append(
self._build_filter('Mine', 'mine_region', 'in',
args["region"]))
if args["report_type"]:
conditions.append(
self._build_filter('MineReportCategoryXref',
'mine_report_category', 'in',
args["report_type"]))
if args["report_name"]:
conditions.append(
self._build_filter('MineReportDefinition',
'mine_report_definition_guid', 'in',
args["report_name"]))
if args["status"]:
query = query.filter(
MineReport.mine_report_status_code.in_(args["status"]))
if args["compliance_year"]:
conditions.append(
self._build_filter('MineReport', 'submission_year', '==',
args["compliance_year"]))
if args["due_date_before"]:
conditions.append(
self._build_filter('MineReport', 'due_date', '<=',
args["due_date_before"]))
if args["due_date_after"]:
conditions.append(
self._build_filter('MineReport', 'due_date', '>=',
args["due_date_after"]))
if args["received_date_before"]:
conditions.append(
self._build_filter('MineReport', 'received_date', '<=',
args["received_date_before"]))
if args["received_date_after"]:
conditions.append(
self._build_filter('MineReport', 'received_date', '>=',
args["received_date_after"]))
if not args["received_only"]:
query = query.filter(MineReport.received_date.isnot(None))
if args["requested_by"]:
conditions.append(
self._build_filter('MineReport', 'created_by_idir', 'ilike',
'%{}%'.format(args["requested_by"])))
if args["search_terms"]:
search_conditions = [
self._build_filter('Mine', 'mine_name', 'ilike',
'%{}%'.format(args["search_terms"])),
self._build_filter('Mine', 'mine_no', 'ilike',
'%{}%'.format(args["search_terms"])),
]
conditions.append({'or': search_conditions})
filtered_query = apply_filters(query, conditions)
if args['sort_field'] == 'mine_report_status_code':
if args['sort_dir'] == 'asc':
filtered_query = filtered_query.order_by(
asc(MineReport.mine_report_status_description))
else:
filtered_query = filtered_query.order_by(
desc(MineReport.mine_report_status_description))
else:
if args['sort_field'] and args['sort_dir']:
sort_criteria = [{
'model': sort_models[args['sort_field']],
'field': sort_field[args['sort_field']],
'direction': args['sort_dir']
}]
else:
sort_criteria = [{
'model': 'MineReport',
'field': 'received_date',
'direction': 'desc'
}]
filtered_query = apply_sort(filtered_query, sort_criteria)
return apply_pagination(filtered_query, args["page_number"],
args["page_size"])
def get(self, mine_no_or_guid=None):
if mine_no_or_guid:
mine = Mine.find_by_mine_no_or_guid(mine_no_or_guid)
if mine:
return mine.json()
return self.create_error_payload(404, 'Mine not found'), 404
else:
# Handle MapView request
_map = request.args.get('map', None, type=str)
if _map and _map.lower() == 'true':
records = MineMapViewLocation.query.all()
result = list((map(lambda x: x.json_for_map(), records)))
return {'mines': result}
# Handle ListView request
items_per_page = request.args.get('per_page', 25, type=int)
page = request.args.get('page', 1, type=int)
search_term = request.args.get('search', None, type=str)
status_search_term = request.args.get('status', None, type=str)
# Create a filter on mine status if one is provided
if status_search_term:
status_search_term_array = status_search_term.split(',')
status_filter = MineStatusXref.mine_operation_status_code.in_(
status_search_term_array)
status_reason_filter = MineStatusXref.mine_operation_status_reason_code.in_(
status_search_term_array)
status_subreason_filter = MineStatusXref.mine_operation_status_sub_reason_code.in_(
status_search_term_array)
all_status_filter = status_filter | status_reason_filter | status_subreason_filter
if search_term:
name_filter = Mine.mine_name.ilike('%{}%'.format(search_term))
number_filter = Mine.mine_no.ilike('%{}%'.format(search_term))
permit_filter = Permit.permit_no.ilike('%{}%'.format(search_term))
mines_query = Mine.query.filter(name_filter | number_filter)
permit_query = Mine.query.join(Permit).filter(permit_filter)
mines_permit_join_query = mines_query.union(permit_query)
if status_search_term:
status_query = Mine.query\
.join(MineStatus)\
.join(MineStatusXref)\
.filter(all_status_filter)
mines_permit_join_query = mines_permit_join_query.intersect(status_query)
result_query, pagination_details = apply_pagination(mines_permit_join_query, page,
items_per_page)
else:
sort_criteria = [{'model': 'Mine', 'field': 'mine_name', 'direction': 'asc'}]
if status_search_term:
mine_query_with_status = Mine.query\
.join(MineStatus)\
.join(MineStatusXref)\
.filter(all_status_filter)
result_query = apply_sort(mine_query_with_status, sort_criteria)
else:
result_query = apply_sort(Mine.query, sort_criteria)
paginated_mine_query, pagination_details = apply_pagination(
result_query, page, items_per_page)
mines = paginated_mine_query.all()
return {
'mines': list(map(lambda x: x.json_for_list(), mines)),
'current_page': pagination_details.page_number,
'total_pages': pagination_details.num_pages,
'items_per_page': pagination_details.page_size,
'total': pagination_details.total_results,
}
def search_filter_sort_paginate(
db_session,
model,
query_str: str = None,
filter_spec: List[dict] = None,
page: int = 1,
items_per_page: int = 5,
sort_by: List[str] = None,
descending: List[bool] = None,
fields: List[str] = None,
ops: List[str] = None,
values: List[str] = None,
join_attrs: List[str] = None,
user_role: UserRoles = UserRoles.user.value,
user_email: str = None,
):
"""Common functionality for searching, filtering, sorting, and pagination."""
model_cls = get_class_by_tablename(model)
sort_spec = create_sort_spec(model, sort_by, descending)
if query_str:
sort = False if sort_spec else True
query = search(db_session=db_session,
query_str=query_str,
model=model,
sort=sort)
else:
query = db_session.query(model_cls)
if user_role != UserRoles.admin.value:
if model.lower() == "incident":
# we filter restricted incidents based on incident participation
query = (query.join(Participant).join(IndividualContact).filter(
not_(
and_(
Incident.visibility == Visibility.restricted.value,
IndividualContact.email != user_email,
))))
if model.lower() == "incidenttype":
query = query.filter(
IncidentType.visibility == Visibility.open.value)
query = join_required_attrs(query, model_cls, join_attrs, fields, sort_by)
if not filter_spec:
filter_spec = create_filter_spec(model, fields, ops, values)
query = apply_filters(query, filter_spec)
query = apply_sort(query, sort_spec)
if items_per_page == -1:
items_per_page = None
query, pagination = apply_pagination(query,
page_number=page,
page_size=items_per_page)
return {
"items": query.all(),
"itemsPerPage": pagination.page_size,
"page": pagination.page_number,
"total": pagination.total_results,
}
def search_filter_sort_paginate(
db_session,
model,
query_str: str = None,
page: int = 1,
items_per_page: int = 5,
sort_by: List[str] = None,
descending: List[bool] = None,
fields: List[str] = None,
ops: List[str] = None,
values: List[str] = None,
current_user: DispatchUser = None,
role: UserRoles = UserRoles.CUSTOMER,
):
"""Common functionality for searching, filtering, sorting, and pagination."""
model_cls = get_class_by_tablename(model)
try:
query = db_session.query(model_cls)
if query_str:
sort = False if sort_by else True
query = search(query_str=query_str,
query=query,
model=model,
sort=sort)
query = apply_model_specific_filters(model_cls, query, current_user,
role)
filter_spec = create_filter_spec(model, fields, ops, values)
query = apply_filters(query, filter_spec)
if sort_by:
sort_spec = create_sort_spec(model, sort_by, descending)
query = apply_sort(query, sort_spec)
except FieldNotFound as e:
raise ValidationError(
[
ErrorWrapper(FieldNotFoundError(msg=str(e)), loc="filter"),
],
model=BaseModel,
)
except BadFilterFormat as e:
raise ValidationError(
[ErrorWrapper(InvalidFilterError(msg=str(e)), loc="filter")],
model=BaseModel)
if items_per_page == -1:
items_per_page = None
# sometimes we get bad input for the search function
# TODO investigate moving to a different way to parsing queries that won't through errors
# e.g. websearch_to_tsquery
# https://www.postgresql.org/docs/current/textsearch-controls.html
try:
query, pagination = apply_pagination(query,
page_number=page,
page_size=items_per_page)
except sqlalchemy.exc.ProgrammingError as e:
log.debug(e)
return {
"items": [],
"itemsPerPage": items_per_page,
"page": page,
"total": 0,
}
return {
"items": query.all(),
"itemsPerPage": pagination.page_size,
"page": pagination.page_number,
"total": pagination.total_results,
}
def apply_filter_and_search(self, args):
sort_models = {
'party_name': 'Party',
}
# Handle ListView request
items_per_page = args.get('per_page', 25)
if items_per_page == 'all':
items_per_page = None
else:
items_per_page = int(items_per_page)
page = args.get('page', 1, type=int)
# parse the filter terms
first_name_filter_term = args.get('first_name', None, type=str)
last_name_filter_term = args.get('last_name', None, type=str)
party_name_filter_term = args.get('party_name', None, type=str)
name_search_filter_term = args.get('name_search', None, type=str)
type_filter_term = args.get('type', None, type=str)
role_filter_term = args.get('role', None, type=str)
email_filter_term = args.get('email', None, type=str)
phone_filter_term = args.get('phone_no', None, type=str)
sort_field = args.get('sort_field', 'party_name', type=str)
sort_dir = args.get('sort_dir', 'asc', type=str)
business_roles = args.getlist('business_role', None)
sort_model = sort_models.get(sort_field)
conditions = [Party.deleted_ind == False]
if first_name_filter_term:
conditions.append(Party.first_name.ilike('%{}%'.format(first_name_filter_term)))
if last_name_filter_term:
conditions.append(Party.party_name.ilike('%{}%'.format(last_name_filter_term)))
if party_name_filter_term:
conditions.append(Party.party_name.ilike('%{}%'.format(party_name_filter_term)))
if email_filter_term:
conditions.append(Party.email.ilike('%{}%'.format(email_filter_term)))
if type_filter_term:
conditions.append(Party.party_type_code.like(type_filter_term))
if phone_filter_term:
conditions.append(Party.phone_no.ilike('%{}%'.format(phone_filter_term)))
if role_filter_term == "NONE":
conditions.append(Party.mine_party_appt == None)
if name_search_filter_term:
name_search_conditions = []
for name_part in name_search_filter_term.strip().split(" "):
name_search_conditions.append(Party.first_name.ilike('%{}%'.format(name_part)))
name_search_conditions.append(Party.party_name.ilike('%{}%'.format(name_part)))
conditions.append(or_(*name_search_conditions))
contact_query = Party.query.filter(and_(*conditions))
if role_filter_term and not role_filter_term == "NONE":
role_filter = MinePartyAppointment.mine_party_appt_type_code.like(role_filter_term)
role_query = Party.query.join(MinePartyAppointment).filter(role_filter)
contact_query = contact_query.intersect(role_query) if contact_query else role_query
if business_roles and len(business_roles) > 0:
business_role_filter = PartyBusinessRoleAppointment.party_business_role_code.in_(
business_roles)
business_role_query = Party.query.join(PartyBusinessRoleAppointment).filter(
business_role_filter)
contact_query = contact_query.intersect(
business_role_query) if contact_query else business_role_query
# Apply sorting
if sort_model and sort_field and sort_dir:
sort_criteria = [{'model': sort_model, 'field': sort_field, 'direction': sort_dir}]
contact_query = apply_sort(contact_query, sort_criteria)
return apply_pagination(contact_query, page, items_per_page)
def _apply_filters_and_pagination(self,
page_number=PAGE_DEFAULT,
page_size=PER_PAGE_DEFAULT,
sort_field=None,
sort_dir=None,
mine_guid=None,
lead_inspector_name=None,
notice_of_work_type_description=[],
mine_region=[],
mine_name=None,
now_number=None,
mine_search=None,
now_application_status_description=[],
originating_system=None,
submissions_only=None):
filters = []
base_query = NoticeOfWorkView.query
if submissions_only:
filters.append(
and_(NoticeOfWorkView.originating_system != None,
NoticeOfWorkView.originating_system != 'MMS'))
if mine_guid:
filters.append(NoticeOfWorkView.mine_guid == mine_guid)
if lead_inspector_name:
filters.append(
func.lower(NoticeOfWorkView.lead_inspector_name).contains(
func.lower(lead_inspector_name)))
if notice_of_work_type_description:
filters.append(
NoticeOfWorkView.notice_of_work_type_description.in_(
notice_of_work_type_description))
if now_number:
filters.append(NoticeOfWorkView.now_number == now_number)
if mine_region or mine_search or mine_name:
base_query = base_query.join(Mine)
if mine_region:
filters.append(Mine.mine_region.in_(mine_region))
if mine_name:
filters.append(
func.lower(Mine.mine_name).contains(func.lower(mine_name)))
if mine_search:
filters.append(
or_(
func.lower(NoticeOfWorkView.mine_no).contains(
func.lower(mine_search)),
func.lower(Mine.mine_name).contains(
func.lower(mine_search)),
func.lower(Mine.mine_no).contains(
func.lower(mine_search))))
if now_application_status_description:
filters.append(
NoticeOfWorkView.now_application_status_description.in_(
now_application_status_description))
base_query = base_query.filter(*filters)
if sort_field and sort_dir:
sort_criteria = None
if sort_field in ['mine_region', 'mine_name']:
sort_criteria = [{
'model': 'Mine',
'field': sort_field,
'direction': sort_dir
}]
else:
sort_criteria = [{
'model': 'NoticeOfWorkView',
'field': sort_field,
'direction': sort_dir,
}]
base_query = apply_sort(base_query, sort_criteria)
return apply_pagination(base_query, page_number, page_size)
def _apply_filters_and_pagination(self, args):
sort_models = {
"mine_incident_report_no": 'MineIncident',
"incident_timestamp": 'MineIncident',
"determination": 'MineIncident',
"incident_status": 'MineIncident',
"mine_name": 'Mine',
}
sort_field = {
"mine_incident_report_no": 'mine_incident_report_no',
"incident_timestamp": 'incident_timestamp',
"determination": 'determination_type_code',
"incident_status": 'status_code',
"mine_name": 'mine_name',
}
query = MineIncident.query.join(Mine)
conditions = []
if args["status"] is not None:
status_values = args["status"].split(',')
conditions.append(
self._build_filter('MineIncident', 'status_code', 'in', status_values))
if args["determination"] is not None:
determination_values = args["determination"].split(',')
conditions.append(
self._build_filter('MineIncident', 'determination_type_code', 'in',
determination_values))
if args["codes"] is not None:
query = MineIncident.query.join(Mine).outerjoin(MineIncidentDoSubparagraph)
code_values = args["codes"].split(',')
conditions.append(
self._build_filter('MineIncidentDoSubparagraph', 'compliance_article_id', 'in',
code_values))
if args["year"] is not None:
min_datetime = datetime(int(args["year"]), 1, 1)
max_datetime = datetime(int(args["year"]) + 1, 1, 1)
conditions.append(
self._build_filter('MineIncident', 'incident_timestamp', '>=', min_datetime))
conditions.append(
self._build_filter('MineIncident', 'incident_timestamp', '<', max_datetime))
if args["major"] is not None:
conditions.append(self._build_filter('Mine', 'major_mine_ind', '==', args["major"]))
if args["search_terms"] is not None:
search_conditions = [
self._build_filter('Mine', 'mine_name', 'ilike',
'%{}%'.format(args["search_terms"])),
self._build_filter('Mine', 'mine_no', 'ilike', '%{}%'.format(args["search_terms"])),
]
conditions.append({'or': search_conditions})
if args["region"] is not None:
region_list = args["region"].split(',')
conditions.append(self._build_filter('Mine', 'mine_region', 'in', region_list))
filtered_query = apply_filters(query, conditions)
# Apply sorting
if args['sort_field'] and args['sort_dir']:
if args['sort_field'] == 'mine_incident_report_no':
sort_criteria = [{
'model': 'MineIncident',
'field': 'mine_incident_id_year',
'direction': args['sort_dir']
}, {
'model': 'MineIncident',
'field': 'mine_incident_id',
'direction': args['sort_dir']
}]
else:
# sorting by code section is not applicable since a single incident may have many sections associated.
sort_criteria = [{
'model': sort_models[args['sort_field']],
'field': sort_field[args['sort_field']],
'direction': args['sort_dir']
}]
else:
# default sorting is by descending date.
sort_criteria = [{
'model': 'MineIncident',
'field': 'incident_timestamp',
'direction': 'desc'
}]
filtered_query = apply_sort(filtered_query, sort_criteria)
return apply_pagination(filtered_query, args["page_number"], args["page_size"])
def sort_query(self):
if self.__data['sort_by']:
self.__query = apply_sort(self.__query, self.__data['sort_by'])
return self.__query
else:
return self.__query
