def get_contract_stages(contract):
'''Returns the appropriate stages and their metadata based on a contract id
'''
return db.session.query(
ContractStage.contract_id, ContractStage.stage_id, ContractStage.id,
ContractStage.entered, ContractStage.exited, Stage.name, Stage.default_message,
Stage.post_opportunities, ContractBase.description,
(db.func.extract(db.text('DAYS'), ContractStage.exited - ContractStage.entered)).label('days_spent'),
(db.func.extract(db.text('HOURS'), ContractStage.exited - ContractStage.entered)).label('hours_spent')
).join(Stage, Stage.id == ContractStage.stage_id).join(
ContractBase, ContractBase.id == ContractStage.contract_id
).filter(
ContractStage.contract_id == contract.id,
ContractStage.flow_id == contract.flow_id
).order_by(ContractStage.id).all()
def refresh_search_view():
'''Create and execute a refresh to the search view in a scoped session
'''
print 'Refreshing the search view...'
session = db.create_scoped_session()
session.execute(db.text('''
REFRESH MATERIALIZED VIEW CONCURRENTLY search_view
'''))
session.commit()
db.engine.dispose()
def build_action_log(stage_id, active_stage):
'''Builds and properly orders an action log for a given stage
'''
actions = ContractStageActionItem.query.filter(
ContractStageActionItem.contract_stage_id == stage_id
).order_by(db.text('taken_at asc')).all()
actions = sorted(actions, key=lambda stage: stage.get_sort_key())
return actions
def get_contract_stages(self):
'''Returns the appropriate stages and their metadata based on a contract id
'''
return db.session.query(
ContractStage.contract_id, ContractStage.stage_id,
ContractStage.id, ContractStage.entered, ContractStage.exited,
Stage.name, Stage.default_message, Stage.post_opportunities,
ContractBase.description, Stage.id.label('stage_id'),
db.func.extract(db.text('DAYS'), ContractStage.exited -
ContractStage.entered).label('days_spent'),
db.func.extract(
db.text('HOURS'), ContractStage.exited -
ContractStage.entered).label('hours_spent')).join(
Stage, Stage.id == ContractStage.stage_id).join(
ContractBase,
ContractBase.id == ContractStage.contract_id).filter(
ContractStage.contract_id == self.id,
ContractStage.flow_id == self.flow_id).order_by(
ContractStage.id).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_contract_stages(self):
"""Returns the appropriate stages and their metadata based on a contract id
"""
return (
db.session.query(
ContractStage.contract_id,
ContractStage.stage_id,
ContractStage.id,
ContractStage.entered,
ContractStage.exited,
Stage.name,
Stage.default_message,
Stage.post_opportunities,
ContractBase.description,
Stage.id.label("stage_id"),
(db.func.extract(db.text("DAYS"), ContractStage.exited - ContractStage.entered)).label("days_spent"),
(db.func.extract(db.text("HOURS"), ContractStage.exited - ContractStage.entered)).label("hours_spent"),
)
.join(Stage, Stage.id == ContractStage.stage_id)
.join(ContractBase, ContractBase.id == ContractStage.contract_id)
.filter(ContractStage.contract_id == self.id, ContractStage.flow_id == self.flow_id)
.order_by(ContractStage.id)
.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 filter(department=None):
'''
Filter contracts by which ones have departmental subscribers
'''
pagination_per_page = current_app.config.get('PER_PAGE', 50)
page = int(request.args.get('page', 1))
lower_bound_result = (page - 1) * pagination_per_page
upper_bound_result = lower_bound_result + pagination_per_page
if department not in [i[0] for i in DEPARTMENT_CHOICES]:
flash('You must choose a valid department!', 'alert-danger')
return redirect(url_for('wexplorer.explore'))
contracts = db.session.query(
ContractBase.id, ContractBase.description,
db.func.count(contract_user_association_table.c.user_id).label('cnt'),
db.func.count(
contract_starred_association_table.c.user_id).label('cnt2')
).outerjoin(contract_user_association_table).outerjoin(
contract_starred_association_table).filter(
db.or_(ContractBase.followers.any(department=department),
ContractBase.starred.any(department=department))
).group_by(ContractBase).having(
db.or_(
db.func.count(contract_user_association_table.c.user_id) > 0,
db.func.count(contract_starred_association_table.c.user_id) >
0)).order_by(db.text('cnt DESC')).all()
pagination = SimplePagination(page, pagination_per_page, len(contracts))
results = contracts[lower_bound_result:upper_bound_result]
current_app.logger.info(
'WEXFILTER - {department}: Filter by {department}'.format(
department=department))
return render_template('wexplorer/filter.html',
search_form=SearchForm(),
results=results,
pagination=pagination,
department=department,
choices=DEPARTMENT_CHOICES[1:],
path='{path}?{query}'.format(
path=request.path, query=request.query_string))
def detail(contract_id, stage_id=-1):
'''View to control an individual stage update process
'''
if request.args.get('transition'):
clicked = int(request.args.get('destination')) if \
request.args.get('destination') else None
stage, mod_contract, complete = transition_stage(
contract_id, destination=clicked, user=current_user
)
if complete:
return redirect(url_for('conductor.edit', contract_id=mod_contract.id))
return redirect(url_for(
'conductor.detail', contract_id=contract_id, stage_id=stage.id
))
if stage_id == -1:
# redirect to the current stage page
contract_stage = ContractStage.query.join(
ContractBase, ContractBase.id == ContractStage.contract_id
).filter(
ContractStage.contract_id == contract_id,
ContractStage.stage_id == ContractBase.current_stage_id
).first()
return redirect(url_for(
'conductor.detail', contract_id=contract_id, stage_id=contract_stage.id
))
stages = db.session.query(
ContractStage.contract_id, ContractStage.stage_id, ContractStage.id,
ContractStage.entered, ContractStage.exited, Stage.name,
Stage.send_notifs, Stage.post_opportunities, ContractBase.description
).join(Stage, Stage.id == ContractStage.stage_id).join(
ContractBase, ContractBase.id == ContractStage.contract_id
).filter(
ContractStage.contract_id == contract_id
).order_by(ContractStage.id).all()
try:
active_stage = [i for i in stages if i.id == stage_id][0]
current_stage = [i for i in stages if i.entered and not i.exited][0]
if active_stage.entered is None:
abort(404)
except IndexError:
abort(404)
note_form = NoteForm()
update_form = SendUpdateForm()
opportunity_form = PostOpportunityForm()
forms = {
'activity': note_form, 'update': update_form, 'post': opportunity_form
}
active_tab = '#activity'
submitted_form = request.args.get('form', None)
if submitted_form:
if handle_form(forms[submitted_form], submitted_form, stage_id, current_user):
return redirect(url_for(
'conductor.detail', contract_id=contract_id, stage_id=stage_id
))
else:
active_tab = '#' + submitted_form
actions = ContractStageActionItem.query.filter(
ContractStageActionItem.contract_stage_id == stage_id
).order_by(db.text('taken_at asc')).all()
actions.extend([
ContractStageActionItem(action_type='entered', action_detail=active_stage.entered, taken_at=active_stage.entered),
ContractStageActionItem(action_type='exited', action_detail=active_stage.exited, taken_at=active_stage.exited)
])
actions = sorted(actions, key=lambda stage: stage.get_sort_key())
if len(stages) > 0:
return render_template(
'conductor/detail.html',
stages=stages, actions=actions, active_tab=active_tab,
note_form=note_form, update_form=update_form,
opportunity_form=opportunity_form, contract_id=contract_id,
current_user=current_user, active_stage=active_stage,
current_stage=current_stage
)
abort(404)
def enable_triggers():
for table, column in TRIGGER_TUPLES:
db.session.execute(db.text('''
ALTER TABLE {table} ENABLE TRIGGER USER
'''.format(table=table, column=column)))
