def get_count_query(self):
'''Override default get count query to conform to above
'''
last_stage = db.session.query(
Flow.id,
Flow.stage_order[db.func.array_upper(Flow.stage_order, 1)].label('last')
).subquery()
return super(ConductorContractAdmin, self).get_count_query().outerjoin(
last_stage
).outerjoin(
ContractStage, db.and_(
ContractBase.current_stage_id == ContractStage.stage_id,
ContractBase.id == ContractStage.contract_id,
ContractBase.flow_id == ContractStage.flow_id
)
).filter(
db.or_(
db.and_(
ContractBase.current_stage_id == last_stage.c.last,
ContractStage.exited != None
),
ContractBase.current_stage_id != last_stage.c.last,
ContractBase.current_stage_id != None
)
)
def get_count_query(self):
'''Override default get query to limit to assigned contracts
'''
last_stage = db.session.query(
Flow.id,
Flow.stage_order[db.func.array_upper(Flow.stage_order, 1)].label('last')
).subquery()
return super(ScoutContractAdmin, self).get_count_query().outerjoin(
last_stage,
).outerjoin(
ContractStage, db.and_(
ContractBase.current_stage_id == ContractStage.stage_id,
ContractBase.id == ContractStage.contract_id,
ContractBase.flow_id == ContractStage.flow_id
)
).filter(
db.or_(
ContractBase.current_stage_id == None,
ContractBase.is_archived == True,
db.and_(
ContractBase.current_stage_id == last_stage.c.last,
ContractStage.exited != None
)
)
)
def find_contract_metadata(search_for,
case_statements,
filter_or,
filter_and,
archived=False):
'''
Takes a search term, case statements, and filter clauses and
returns out a list of search results objects to be rendered into
the template.
Arguments:
search_for: User's search term
case_statements: An iterable of `Sqlalchemy case expressions`_
filter_or: An iterable of `Sqlalchemy query filters`_, used for non-exclusionary filtering
filter_and: An iterable of `Sqlalchemy query filters`_, used for exclusionary filtering
archived: Boolean of whether or not to add the ``is_archived`` filter
Returns:
A Sqlalchemy resultset that contains the fields to render the
search results view.
'''
rank = db.func.max(
db.func.full_text.ts_rank(
db.func.setweight(
db.func.coalesce(SearchView.tsv_company_name, ''), 'A').concat(
db.func.setweight(
db.func.coalesce(SearchView.tsv_contract_description,
''),
'A')).concat(
db.func.setweight(
db.func.coalesce(SearchView.tsv_detail_value,
''), 'D')).
concat(
db.func.setweight(
db.func.coalesce(SearchView.tsv_line_item_description, ''),
'B')),
db.func.to_tsquery(search_for, postgresql_regconfig='english')))
contracts = db.session.query(
db.distinct(SearchView.contract_id).label('contract_id'),
SearchView.company_id, SearchView.contract_description,
SearchView.financial_id, SearchView.expiration_date,
SearchView.company_name,
db.case(case_statements).label('found_in'), rank.label('rank')).join(
ContractBase, ContractBase.id == SearchView.contract_id).filter(
db.or_(
db.cast(SearchView.financial_id, db.String) == search_for,
*filter_or),
*filter_and).group_by(
SearchView.contract_id, SearchView.company_id,
SearchView.contract_description, SearchView.financial_id,
SearchView.expiration_date, SearchView.company_name,
db.case(case_statements)).order_by(db.text('rank DESC'))
contracts = add_archived_filter(contracts, archived)
return contracts.all()
def find_contract_metadata(search_for, case_statements, filter_or, filter_and, archived=False):
'''
Takes a search term, case statements, and filter clauses and
returns out a list of search results objects to be rendered into
the template.
Arguments:
search_for: User's search term
case_statements: An iterable of `Sqlalchemy case expressions`_
filter_or: An iterable of `Sqlalchemy query filters`_, used for non-exclusionary filtering
filter_and: An iterable of `Sqlalchemy query filters`_, used for exclusionary filtering
archived: Boolean of whether or not to add the ``is_archived`` filter
Returns:
A Sqlalchemy resultset that contains the fields to render the
search results view.
'''
rank = db.func.max(db.func.full_text.ts_rank(
db.func.setweight(db.func.coalesce(SearchView.tsv_company_name, ''), 'A').concat(
db.func.setweight(db.func.coalesce(SearchView.tsv_contract_description, ''), 'A')
).concat(
db.func.setweight(db.func.coalesce(SearchView.tsv_detail_value, ''), 'D')
).concat(
db.func.setweight(db.func.coalesce(SearchView.tsv_line_item_description, ''), 'B')
), db.func.to_tsquery(search_for, postgresql_regconfig='english')
))
contracts = db.session.query(
db.distinct(SearchView.contract_id).label('contract_id'),
SearchView.company_id, SearchView.contract_description,
SearchView.financial_id, SearchView.expiration_date,
SearchView.company_name, db.case(case_statements).label('found_in'),
rank.label('rank')
).join(
ContractBase, ContractBase.id == SearchView.contract_id
).filter(
db.or_(
db.cast(SearchView.financial_id, db.String) == search_for,
*filter_or
),
*filter_and
).group_by(
SearchView.contract_id,
SearchView.company_id,
SearchView.contract_description,
SearchView.financial_id,
SearchView.expiration_date,
SearchView.company_name,
db.case(case_statements)
).order_by(
db.text('rank DESC')
)
contracts = add_archived_filter(contracts, archived)
return contracts.all()
def get_count_query(self):
'''Override default get query to limit to assigned contracts
'''
last_stage = db.session.query(
Flow.id,
Flow.stage_order[db.func.array_upper(Flow.stage_order,
1)].label('last')).subquery()
return super(ScoutContractAdmin, self).get_count_query().outerjoin(
last_stage, ).outerjoin(
ContractStage,
db.and_(
ContractBase.current_stage_id == ContractStage.stage_id,
ContractBase.id == ContractStage.contract_id,
ContractBase.flow_id == ContractStage.flow_id)).filter(
db.or_(
ContractBase.current_stage_id == None,
ContractBase.is_archived == True,
db.and_(
ContractBase.current_stage_id ==
last_stage.c.last,
ContractStage.exited != None)))
def get_count_query(self):
'''Override default get count query to conform to above
'''
last_stage = db.session.query(
Flow.id,
Flow.stage_order[db.func.array_upper(Flow.stage_order,
1)].label('last')).subquery()
return super(
ConductorContractAdmin,
self).get_count_query().outerjoin(last_stage).outerjoin(
ContractStage,
db.and_(
ContractBase.current_stage_id == ContractStage.stage_id,
ContractBase.id == ContractStage.contract_id,
ContractBase.flow_id == ContractStage.flow_id)).filter(
db.or_(
db.and_(
ContractBase.current_stage_id ==
last_stage.c.last,
ContractStage.exited != None),
ContractBase.current_stage_id != last_stage.c.last,
ContractBase.current_stage_id != None))
