def find_contract_metadata(search_term,
case_statements,
filter_clauses,
archived=False):
'''
Takes a search term, case statements, and filter clauses and
returns out a list of result objects including contract id,
company id, financial id, expiration date, awarded name
'''
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'),
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, ''), 'D')
).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_term,
postgresql_regconfig='english'))).label('rank')).join(
ContractBase,
ContractBase.id == SearchView.contract_id).filter(
db.or_(
db.cast(SearchView.financial_id,
db.String) == search_term,
*filter_clauses), ContractBase.financial_id
is not None, ContractBase.expiration_date
is not None).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'))
if not archived:
contracts = contracts.filter(ContractBase.is_archived == False)
return contracts.all()
def return_all_contracts(archived):
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
).join(ContractBase, ContractBase.id == SearchView.contract_id)
if not archived:
contracts = contracts.filter(ContractBase.is_archived == False)
return contracts.all()
def return_all_contracts(archived):
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).join(
ContractBase, ContractBase.id == SearchView.contract_id)
if not archived:
contracts = contracts.filter(ContractBase.is_archived == False)
return contracts.all()
def find_contract_metadata(search_term, case_statements, filter_clauses, archived=False):
'''
Takes a search term, case statements, and filter clauses and
returns out a list of result objects including contract id,
company id, financial id, expiration date, awarded name
'''
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'),
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, ''), 'D')
).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_term, postgresql_regconfig='english')
)).label('rank')
).join(
ContractBase, ContractBase.id == SearchView.contract_id
).filter(
db.or_(
db.cast(SearchView.financial_id, db.String) == search_term,
*filter_clauses
),
ContractBase.financial_id is not None,
ContractBase.expiration_date is not None
).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')
)
if not archived:
contracts = contracts.filter(ContractBase.is_archived == False)
return contracts.all()
def get_all_companies_query():
return db.session.query(db.distinct(Company.id).label('id'), Company.company_name).order_by(Company.company_name)
def parent_category_query_factory(cls):
'''Return all of the parent categories
'''
return db.session.query(db.distinct(cls.category).label('category')).order_by('category')
def index():
'''Main conductor index page/splash view
Renders two tables that conductors can use to track their progress:
``in_progress``, which contains all of the
:py:class:`~purchasing.data.contracts.ContractBase` objects that are
currently being worked on by a conductor. ``in_progress`` contracts
are generated by selecting only :py:class:`~purchasing.data.contracts.ContractBase`
objects that have a ``parent_id``, have an existing ``flow``,
non-null ``entered`` current contract stage, and are neither ``is_archived`` nor
``is_visible``.
``all_contracts`` contains all contracts that are eligible
to show up in conductor to be worked on. These are filtered based on the
:py:class:`~purchasing.data.contracts.ContractType`
``managed_by_conductor`` field. Additionally, these are
filtered by having no ``children``, and ``is_visible`` set to True
.. seealso:: :py:class:`~purchasing.data.contracts.ContractBase`,
:py:class:`~purchasing.data.contract_stages.ContractStage`,
:py:class:`~purchasing.data.flows.Flow`
:status 200: Render the main conductor index page
'''
parent = aliased(ContractBase)
parent_specs = db.session.query(
ContractBase.id, ContractProperty.value, parent.expiration_date,
parent.contract_href, Company.company_name).join(
ContractProperty,
ContractBase.parent_id == ContractProperty.contract_id).join(
parent, ContractBase.parent).outerjoin(
Company, parent.companies).filter(
db.func.lower(ContractProperty.key) == 'spec number',
ContractType.managed_by_conductor == True).subquery()
in_progress = db.session.query(
db.distinct(ContractBase.id).label('id'),
ContractProperty.value.label('spec_number'),
parent_specs.c.value.label('parent_spec'),
parent_specs.c.expiration_date.label('parent_expiration'),
parent_specs.c.contract_href.label('parent_contract_href'),
ContractBase.description, Flow.flow_name,
Stage.name.label('stage_name'), ContractStage.entered, User.first_name,
User.email, Department.name.label('department'),
db.func.array_remove(
db.func.array_agg(parent_specs.c.company_name),
None).label('companies')).outerjoin(Department).join(
ContractStage,
db.and_(
ContractStage.stage_id == ContractBase.current_stage_id,
ContractStage.contract_id == ContractBase.id,
ContractStage.flow_id == ContractBase.flow_id)
).join(Stage, Stage.id == ContractBase.current_stage_id).join(
Flow, Flow.id == ContractBase.flow_id).outerjoin(
ContractProperty,
ContractProperty.contract_id == ContractBase.id).outerjoin(
parent_specs,
ContractBase.id == parent_specs.c.id).join(
User, User.id == ContractBase.assigned_to).filter(
ContractStage.flow_id == ContractBase.flow_id,
ContractStage.entered != None,
ContractBase.assigned_to != None,
ContractBase.is_visible == False,
ContractBase.is_archived == False).group_by(
ContractBase.id,
ContractProperty.value.label(
'spec_number'),
parent_specs.c.value.label('parent_spec'),
parent_specs.c.expiration_date.label(
'parent_expiration'),
parent_specs.c.contract_href.label(
'parent_contract_href'),
ContractBase.description, Flow.flow_name,
Stage.name.label('stage_name'),
ContractStage.entered, User.first_name,
User.email,
Department.name.label('department')).all()
all_contracts = db.session.query(
ContractBase.id, ContractBase.description, ContractBase.financial_id,
ContractBase.expiration_date,
ContractProperty.value.label('spec_number'),
ContractBase.contract_href, ContractBase.department, User.first_name,
User.email,
db.func.array_remove(
db.func.array_agg(Company.company_name),
None).label('companies')).join(ContractType).outerjoin(
User, User.id == ContractBase.assigned_to
).outerjoin(Company, ContractBase.companies).outerjoin(
Department,
Department.id == ContractBase.department_id).outerjoin(
ContractProperty,
ContractProperty.contract_id == ContractBase.id).filter(
ContractType.managed_by_conductor == True,
db.func.lower(ContractProperty.key) == 'spec number',
ContractBase.children == None,
ContractBase.is_visible == True).group_by(
ContractBase.id, ContractBase.description,
ContractBase.financial_id,
ContractBase.expiration_date,
ContractProperty.value.label('spec_number'),
ContractBase.contract_href,
ContractBase.department, User.first_name,
User.email).order_by(
ContractBase.expiration_date).all()
conductors = User.query.filter(User.roles.any(Role.name == 'conductor'),
User.email != current_user.email).all()
current_app.logger.info('CONDUCTOR INDEX - Conductor index page view')
return render_template('conductor/index.html',
in_progress=in_progress,
_all=all_contracts,
current_user=current_user,
conductors=[current_user] + conductors,
path='{path}?{query}'.format(
path=request.path, query=request.query_string))
def all_companies_query_factory(cls):
'''Query factory of all company ids and names ordered by name
'''
return db.session.query(
db.distinct(cls.id).label('id'),
cls.company_name).order_by(cls.company_name)
def all_companies_query_factory(cls):
'''Query factory of all company ids and names ordered by name
'''
return db.session.query(
db.distinct(cls.id).label('id'), cls.company_name
).order_by(cls.company_name)
def parent_category_query_factory(cls):
'''Query factory to return a query of all of the distinct top-level categories
'''
return db.session.query(db.distinct(
cls.category).label('category')).order_by('category')
def index():
parent = aliased(ContractBase)
parent_specs = db.session.query(
ContractBase.id, ContractProperty.value,
parent.expiration_date, parent.contract_href
).join(
ContractProperty,
ContractBase.parent_id == ContractProperty.contract_id
).join(
parent, ContractBase.parent
).filter(
db.func.lower(ContractProperty.key) == 'spec number',
ContractType.name == 'County'
).subquery()
in_progress = db.session.query(
db.distinct(ContractBase.id).label('id'),
ContractProperty.value.label('spec_number'),
parent_specs.c.value.label('parent_spec'),
parent_specs.c.expiration_date.label('parent_expiration'),
parent_specs.c.contract_href.label('parent_contract_href'),
ContractBase.description, Flow.flow_name,
Stage.name.label('stage_name'), ContractStage.entered,
User.first_name, User.email,
Department.name.label('department')
).outerjoin(Department).join(
ContractStage, db.and_(
ContractStage.stage_id == ContractBase.current_stage_id,
ContractStage.contract_id == ContractBase.id,
ContractStage.flow_id == ContractBase.flow_id
)
).join(
Stage, Stage.id == ContractBase.current_stage_id
).join(
Flow, Flow.id == ContractBase.flow_id
).outerjoin(
ContractProperty, ContractProperty.contract_id == ContractBase.id
).outerjoin(
parent_specs, ContractBase.id == parent_specs.c.id
).join(User, User.id == ContractBase.assigned_to).filter(
ContractStage.flow_id == ContractBase.flow_id,
ContractStage.entered != None,
ContractBase.assigned_to != None,
ContractBase.is_visible == False,
ContractBase.is_archived == False
).all()
all_contracts = db.session.query(
ContractBase.id, ContractBase.description,
ContractBase.financial_id, ContractBase.expiration_date,
ContractProperty.value.label('spec_number'),
ContractBase.contract_href, ContractBase.department,
User.first_name, User.email
).join(ContractType).outerjoin(
User, User.id == ContractBase.assigned_to
).outerjoin(
Department, Department.id == ContractBase.department_id
).outerjoin(
ContractProperty, ContractProperty.contract_id == ContractBase.id
).filter(
db.func.lower(ContractType.name).in_(['county', 'a-bid', 'b-bid']),
db.func.lower(ContractProperty.key) == 'spec number',
ContractBase.children == None,
ContractBase.is_visible == True
).order_by(ContractBase.expiration_date).all()
conductors = User.query.join(Role, User.role_id == Role.id).filter(
Role.name == 'conductor',
User.email != current_user.email
).all()
current_app.logger.info('CONDUCTOR INDEX - Conductor index page view')
return render_template(
'conductor/index.html',
in_progress=in_progress, _all=all_contracts,
current_user=current_user,
conductors=[current_user] + conductors,
path='{path}?{query}'.format(
path=request.path, query=request.query_string
)
)
def parent_category_query_factory(cls):
'''Query factory to return a query of all of the distinct top-level categories
'''
return db.session.query(db.distinct(cls.category).label('category')).order_by('category')
def index():
'''Main conductor index page/splash view
Renders two tables that conductors can use to track their progress:
``in_progress``, which contains all of the
:py:class:`~purchasing.data.contracts.ContractBase` objects that are
currently being worked on by a conductor. ``in_progress`` contracts
are generated by selecting only :py:class:`~purchasing.data.contracts.ContractBase`
objects that have a ``parent_id``, have an existing ``flow``,
non-null ``entered`` current contract stage, and are neither ``is_archived`` nor
``is_visible``.
``all_contracts`` contains all contracts that are eligible
to show up in conductor to be worked on. These are filtered based on the
:py:class:`~purchasing.data.contracts.ContractType`
``managed_by_conductor`` field. Additionally, these are
filtered by having no ``children``, and ``is_visible`` set to True
.. seealso:: :py:class:`~purchasing.data.contracts.ContractBase`,
:py:class:`~purchasing.data.contract_stages.ContractStage`,
:py:class:`~purchasing.data.flows.Flow`
:status 200: Render the main conductor index page
'''
parent = aliased(ContractBase)
parent_specs = db.session.query(
ContractBase.id, ContractProperty.value,
parent.expiration_date, parent.contract_href,
Company.company_name
).join(
ContractProperty,
ContractBase.parent_id == ContractProperty.contract_id
).join(
parent, ContractBase.parent
).outerjoin(
Company, parent.companies
).filter(
db.func.lower(ContractProperty.key) == 'spec number',
ContractType.managed_by_conductor == True
).subquery()
in_progress = db.session.query(
db.distinct(ContractBase.id).label('id'),
ContractProperty.value.label('spec_number'),
parent_specs.c.value.label('parent_spec'),
parent_specs.c.expiration_date.label('parent_expiration'),
parent_specs.c.contract_href.label('parent_contract_href'),
ContractBase.description, Flow.flow_name,
Stage.name.label('stage_name'), ContractStage.entered,
User.first_name, User.email,
Department.name.label('department'),
db.func.array_remove(
db.func.array_agg(parent_specs.c.company_name),
None
).label('companies')
).outerjoin(Department).join(
ContractStage, db.and_(
ContractStage.stage_id == ContractBase.current_stage_id,
ContractStage.contract_id == ContractBase.id,
ContractStage.flow_id == ContractBase.flow_id
)
).join(
Stage, Stage.id == ContractBase.current_stage_id
).join(
Flow, Flow.id == ContractBase.flow_id
).outerjoin(
ContractProperty, ContractProperty.contract_id == ContractBase.id
).outerjoin(
parent_specs, ContractBase.id == parent_specs.c.id
).join(User, User.id == ContractBase.assigned_to).filter(
ContractStage.flow_id == ContractBase.flow_id,
ContractStage.entered != None,
ContractBase.assigned_to != None,
ContractBase.is_visible == False,
ContractBase.is_archived == False
).group_by(
ContractBase.id,
ContractProperty.value.label('spec_number'),
parent_specs.c.value.label('parent_spec'),
parent_specs.c.expiration_date.label('parent_expiration'),
parent_specs.c.contract_href.label('parent_contract_href'),
ContractBase.description, Flow.flow_name,
Stage.name.label('stage_name'), ContractStage.entered,
User.first_name, User.email,
Department.name.label('department')
).all()
all_contracts = db.session.query(
ContractBase.id, ContractBase.description,
ContractBase.financial_id, ContractBase.expiration_date,
ContractProperty.value.label('spec_number'),
ContractBase.contract_href, ContractBase.department,
User.first_name, User.email,
db.func.array_remove(
db.func.array_agg(Company.company_name),
None
).label('companies')
).join(ContractType).outerjoin(
User, User.id == ContractBase.assigned_to
).outerjoin(Company, ContractBase.companies).outerjoin(
Department, Department.id == ContractBase.department_id
).outerjoin(
ContractProperty, ContractProperty.contract_id == ContractBase.id
).filter(
ContractType.managed_by_conductor == True,
db.func.lower(ContractProperty.key) == 'spec number',
ContractBase.children == None,
ContractBase.is_visible == True
).group_by(
ContractBase.id, ContractBase.description,
ContractBase.financial_id, ContractBase.expiration_date,
ContractProperty.value.label('spec_number'),
ContractBase.contract_href, ContractBase.department,
User.first_name, User.email
).order_by(ContractBase.expiration_date).all()
conductors = User.query.filter(
User.roles.any(Role.name == 'conductor'),
User.email != current_user.email
).all()
current_app.logger.info('CONDUCTOR INDEX - Conductor index page view')
return render_template(
'conductor/index.html',
in_progress=in_progress, _all=all_contracts,
current_user=current_user,
conductors=[current_user] + conductors,
path='{path}?{query}'.format(
path=request.path, query=request.query_string
)
)