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