def list_agreements_signed(supplier_code):
current_only = request.args.get('current_only', False)
page = get_valid_page_or_1()
agreements = SignedAgreement.query.filter(SignedAgreement.supplier_code == supplier_code)
if current_only:
agreements = agreements.outerjoin(Agreement, Agreement.id == SignedAgreement.agreement_id)\
.filter(Agreement.is_current)
results_per_page = get_positive_int_or_400(
request.args,
'per_page',
current_app.config['DM_API_PAGE_SIZE']
)
agreements = agreements.paginate(
page=page,
per_page=results_per_page
)
return jsonify(
agreements=[agreement.serialize() for agreement in agreements.items],
links=pagination_links(
agreements,
'.list_agreements',
request.args
)
)
def list_case_studies():
page = get_valid_page_or_1()
supplier_code = get_int_or_400(request.args, 'supplier_code')
case_studies = CaseStudy.query
if supplier_code is not None:
case_studies = case_studies.filter(CaseStudy.supplier_code == supplier_code)
if supplier_code:
return jsonify(
caseStudies=[case_study.serialize() for case_study in case_studies.all()],
links={'self': url_for('.list_case_studies', supplier_code=supplier_code)}
)
results_per_page = get_positive_int_or_400(
request.args,
'per_page',
current_app.config['DM_API_PAGE_SIZE']
)
case_studies = case_studies.paginate(
page=page,
per_page=results_per_page
)
return jsonify(
caseStudies=[case_study.serialize() for case_study in case_studies.items],
links=pagination_links(
case_studies,
'.list_case_studies',
request.args
)
)
def list_case_studies():
page = get_valid_page_or_1()
supplier_code = get_int_or_400(request.args, 'supplier_code')
case_studies = CaseStudy.query
if supplier_code is not None:
case_studies = case_studies.filter(
CaseStudy.supplier_code == supplier_code)
if supplier_code:
return jsonify(caseStudies=[
case_study.serialize() for case_study in case_studies.all()
],
links={
'self':
url_for('.list_case_studies',
supplier_code=supplier_code)
})
results_per_page = get_positive_int_or_400(
request.args, 'per_page', current_app.config['DM_API_PAGE_SIZE'])
case_studies = case_studies.paginate(page=page, per_page=results_per_page)
return jsonify(caseStudies=[
case_study.serialize() for case_study in case_studies.items
],
links=pagination_links(case_studies, '.list_case_studies',
request.args))
def list_agreements():
current_only = request.args.get('current_only', False)
page = get_valid_page_or_1()
agreements = Agreement.query
if current_only:
agreements = agreements.filter(Agreement.is_current)
results_per_page = get_positive_int_or_400(
request.args,
'per_page',
current_app.config['DM_API_PAGE_SIZE']
)
agreements = agreements.paginate(
page=page,
per_page=results_per_page
)
return jsonify(
agreements=[agreement.serialize() for agreement in agreements.items],
links=pagination_links(
agreements,
'.list_agreements',
request.args
)
)
def list_suppliers():
page = get_valid_page_or_1()
prefix = request.args.get('prefix', '')
name = request.args.get('name', None)
results_per_page = get_positive_int_or_400(
request.args,
'per_page',
current_app.config['DM_API_SUPPLIERS_PAGE_SIZE']
)
if name is None:
suppliers = Supplier.query.filter(Supplier.abn.is_(None) | (Supplier.abn != Supplier.DUMMY_ABN))
else:
suppliers = Supplier.query.filter((Supplier.name == name) | (Supplier.long_name == name))
suppliers = suppliers.filter(Supplier.status != 'deleted')
if prefix:
if prefix == 'other':
suppliers = suppliers.filter(
Supplier.name.op('~')('^[^A-Za-z]'))
else:
suppliers = suppliers.outerjoin(SupplierContact).outerjoin(Contact)
# case insensitive LIKE comparison for matching supplier names, supplier email and contact email
suppliers = suppliers.filter(or_(
Supplier.name.ilike(prefix + '%'),
Supplier.data['email'].astext.ilike('%{}%'.format(prefix)),
Contact.email.ilike('%{}%'.format(prefix))
))
suppliers = suppliers.distinct(Supplier.name, Supplier.code)
try:
if results_per_page > 0:
paginator = suppliers.paginate(
page=page,
per_page=results_per_page,
)
links = pagination_links(
paginator,
'.list_suppliers',
request.args
)
supplier_results = paginator.items
else:
links = {
'self': url_for('.list_suppliers', _external=True, **request.args),
}
supplier_results = suppliers.all()
supplier_data = [supplier.serializable for supplier in supplier_results]
except DataError:
abort(400, 'invalid framework')
return jsonify(suppliers=supplier_data, links=links)
def list_users():
user_query = User.query.order_by(User.id)
page = get_valid_page_or_1()
results_per_page = get_positive_int_or_400(
request.args, 'per_page', current_app.config['DM_API_USER_PAGE_SIZE'])
# email_address is a primary key
email_address = request.args.get('email_address')
if email_address:
user = user_query.filter(
User.email_address == email_address.lower()).first_or_404()
return jsonify(users=[user.serialize()], links={})
supplier_code = request.args.get('supplier_code')
if supplier_code is not None:
try:
supplier_code = int(supplier_code)
except ValueError:
abort(400, "Invalid supplier_code: {}".format(supplier_code))
supplier = Supplier.query.filter(Supplier.code == supplier_code).all()
if not supplier:
abort(404, "supplier_code '{}' not found".format(supplier_code))
user_query = user_query.filter(User.supplier_code == supplier_code)
application_id = request.args.get('application_id')
if application_id is not None:
try:
application_id = int(application_id)
except ValueError:
abort(400, "Invalid application_id: {}".format(application_id))
application = Application.query.filter(
Application.id == application_id).all()
if not application:
abort(404, "application_id '{}' not found".format(application_id))
user_query = user_query.filter(User.application_id == application_id)
# simple means we don't load the relationships
if request.args.get('simple'):
user_query = (user_query.options(noload('supplier')).options(
noload('application')).options(noload('frameworks')))
users = user_query.paginate(
page=page,
per_page=results_per_page,
)
return jsonify(users=[u.serialize() for u in users.items],
links=pagination_links(users, '.list_users', request.args))
def list_suppliers():
page = get_valid_page_or_1()
prefix = request.args.get('prefix', '')
name = request.args.get('name', None)
results_per_page = get_positive_int_or_400(
request.args, 'per_page',
current_app.config['DM_API_SUPPLIERS_PAGE_SIZE'])
if name is None:
suppliers = Supplier.query.filter(
Supplier.abn.is_(None) | (Supplier.abn != Supplier.DUMMY_ABN))
else:
suppliers = Supplier.query.filter((Supplier.name == name)
| (Supplier.long_name == name))
suppliers = suppliers.filter(Supplier.status != 'deleted')
if prefix:
if prefix == 'other':
suppliers = suppliers.filter(Supplier.name.op('~')('^[^A-Za-z]'))
else:
suppliers = suppliers.outerjoin(SupplierContact).outerjoin(Contact)
# case insensitive LIKE comparison for matching supplier names, supplier email and contact email
suppliers = suppliers.filter(
or_(Supplier.name.ilike(prefix + '%'),
Supplier.data['email'].astext.ilike('%{}%'.format(prefix)),
Contact.email.ilike('%{}%'.format(prefix))))
suppliers = suppliers.distinct(Supplier.name, Supplier.code)
try:
if results_per_page > 0:
paginator = suppliers.paginate(
page=page,
per_page=results_per_page,
)
links = pagination_links(paginator, '.list_suppliers',
request.args)
supplier_results = paginator.items
else:
links = {
'self': url_for('.list_suppliers',
_external=True,
**request.args),
}
supplier_results = suppliers.all()
supplier_data = [
supplier.serializable for supplier in supplier_results
]
except DataError:
abort(400, 'invalid framework')
return jsonify(suppliers=supplier_data, links=links)
def format_applications(applications, with_task_status):
if request.args.get('order_by', None) == 'application.status desc, created_at desc':
order_by = ['application.status desc', 'created_at desc']
else:
order_by = ['application.created_at desc']
applications = applications.order_by(*order_by)
page = get_valid_page_or_1()
results_per_page = get_positive_int_or_400(
request.args,
'per_page',
current_app.config['DM_API_APPLICATIONS_PAGE_SIZE']
)
applications = applications.paginate(
page=page,
per_page=results_per_page
)
apps_results = [_.serializable for _ in applications.items]
if with_task_status and current_app.config['JIRA_FEATURES']:
jira = get_marketplace_jira()
tasks_by_id = jira.assessment_tasks_by_application_id()
def annotate_app(app):
try:
app['tasks'] = tasks_by_id.get(str(app['id']), None)
except KeyError:
pass
return app
apps_results = [annotate_app(_) for _ in apps_results]
return jsonify(
applications=apps_results,
links=pagination_links(
applications,
'.list_applications',
request.args
),
meta={
"total": applications.total,
"per_page": results_per_page
}
)
def format_applications(applications, with_task_status):
if request.args.get('order_by', None) == 'application.status desc, created_at desc':
order_by = ['application.status desc', 'created_at desc']
else:
order_by = ['application.created_at desc']
applications = applications.order_by(*order_by)
page = get_valid_page_or_1()
results_per_page = get_positive_int_or_400(
request.args,
'per_page',
current_app.config['DM_API_APPLICATIONS_PAGE_SIZE']
)
applications = applications.paginate(
page=page,
per_page=results_per_page
)
apps_results = [_.serializable for _ in applications.items]
if with_task_status and current_app.config['JIRA_FEATURES']:
jira = get_marketplace_jira()
tasks_by_id = jira.assessment_tasks_by_application_id()
def annotate_app(app):
try:
app['tasks'] = tasks_by_id.get(str(app['id']), None)
except KeyError:
pass
return app
apps_results = [annotate_app(_) for _ in apps_results]
return jsonify(
applications=apps_results,
links=pagination_links(
applications,
'.list_applications',
request.args
),
meta={
"total": applications.total,
"per_page": results_per_page
}
)
def supplier_search():
search_query = get_json_from_request()
new_domains = False
offset = get_nonnegative_int_or_400(request.args, 'from', 0)
result_count = get_positive_int_or_400(request.args, 'size', current_app.config['DM_API_SUPPLIERS_PAGE_SIZE'])
framework_slug = request.args.get('framework', 'digital-marketplace')
sliced_results, count = do_search(search_query, offset, result_count, new_domains, framework_slug)
result = {
'hits': {
'total': count,
'hits': [{'_source': r} for r in sliced_results]
}
}
try:
return jsonify(result), 200
except Exception as e:
return jsonify(message=str(e)), 500
def list_work_orders():
page = get_valid_page_or_1()
brief_id = get_int_or_400(request.args, 'brief_id')
supplier_code = get_int_or_400(request.args, 'supplier_code')
work_orders = WorkOrder.query
if supplier_code is not None:
work_orders = work_orders.filter(WorkOrder.supplier_code == supplier_code)
if brief_id is not None:
work_orders = work_orders.filter(WorkOrder.brief_id == brief_id)
if brief_id or supplier_code:
return jsonify(
workOrders=[work_order.serialize() for work_order in work_orders.all()],
links={'self': url_for('.list_work_orders', supplier_code=supplier_code, brief_id=brief_id)}
)
results_per_page = get_positive_int_or_400(
request.args,
'per_page',
current_app.config['DM_API_PAGE_SIZE']
)
work_orders = work_orders.paginate(
page=page,
per_page=results_per_page
)
return jsonify(
workOrders=[work_order.serialize() for work_order in work_orders.items],
links=pagination_links(
work_orders,
'.list_work_orders',
request.args
)
)
def list_projects():
page = get_valid_page_or_1()
projects = Project.query
results_per_page = get_positive_int_or_400(
request.args,
'per_page',
current_app.config['DM_API_PAGE_SIZE']
)
projects = projects.paginate(
page=page,
per_page=results_per_page
)
return jsonify(
projects=[project.serialize() for project in projects.items],
links=pagination_links(
projects,
'.list_projects',
request.args
)
)
def supplier_search():
search_query = get_json_from_request()
new_domains = False
offset = get_nonnegative_int_or_400(request.args, 'from', 0)
result_count = get_positive_int_or_400(
request.args, 'size', current_app.config['DM_API_SUPPLIERS_PAGE_SIZE'])
framework_slug = request.args.get('framework', 'digital-marketplace')
sliced_results, count = do_search(search_query, offset, result_count,
new_domains, framework_slug)
result = {
'hits': {
'total': count,
'hits': [{
'_source': r
} for r in sliced_results]
}
}
try:
return jsonify(result), 200
except Exception as e:
return jsonify(message=str(e)), 500
def list_users():
user_query = User.query.order_by(User.id)
page = get_valid_page_or_1()
results_per_page = get_positive_int_or_400(
request.args,
'per_page',
current_app.config['DM_API_USER_PAGE_SIZE']
)
# email_address is a primary key
email_address = request.args.get('email_address')
if email_address:
user = user_query.filter(
User.email_address == email_address.lower()
).first_or_404()
return jsonify(
users=[user.serialize()],
links={}
)
supplier_code = request.args.get('supplier_code')
if supplier_code is not None:
try:
supplier_code = int(supplier_code)
except ValueError:
abort(400, "Invalid supplier_code: {}".format(supplier_code))
supplier = Supplier.query.filter(Supplier.code == supplier_code).all()
if not supplier:
abort(404, "supplier_code '{}' not found".format(supplier_code))
user_query = user_query.filter(User.supplier_code == supplier_code)
application_id = request.args.get('application_id')
if application_id is not None:
try:
application_id = int(application_id)
except ValueError:
abort(400, "Invalid application_id: {}".format(application_id))
application = Application.query.filter(Application.id == application_id).all()
if not application:
abort(404, "application_id '{}' not found".format(application_id))
user_query = user_query.filter(User.application_id == application_id)
# simple means we don't load the relationships
if request.args.get('simple'):
user_query = (
user_query
.options(noload('supplier'))
.options(noload('application'))
.options(noload('frameworks'))
)
users = user_query.paginate(
page=page,
per_page=results_per_page,
)
return jsonify(
users=[u.serialize() for u in users.items],
links=pagination_links(
users,
'.list_users',
request.args
)
)
def casestudies_search():
search_query = get_json_from_request()
offset = get_nonnegative_int_or_400(request.args, 'from', 0)
result_count = get_positive_int_or_400(
request.args, 'size', current_app.config['DM_API_SUPPLIERS_PAGE_SIZE'])
sort_dir = search_query.get('sort_dir', 'asc')
sort_by = search_query.get('sort_by', None)
domains = search_query.get('domains', None)
seller_types = search_query.get('seller_types', None)
search_term = search_query.get('search_term', None)
framework_slug = request.args.get('framework', 'digital-marketplace')
q = db.session.query(CaseStudy).join(Supplier).outerjoin(SupplierDomain).outerjoin(Domain) \
.outerjoin(SupplierFramework).outerjoin(Framework)
q = q.filter(
Supplier.status != 'deleted',
or_(Framework.slug == framework_slug, ~Supplier.frameworks.any()))
tsquery = None
if search_term:
if ' ' in search_term:
tsquery = func.plainto_tsquery(search_term)
else:
tsquery = func.to_tsquery(search_term + ":*")
q = q.add_column(
func.ts_headline(
'english',
func.concat(CaseStudy.data['approach'].astext, ' ',
CaseStudy.data['role'].astext), tsquery,
'MaxWords=150, MinWords=75, ShortWord=3, HighlightAll=FALSE, FragmentDelimiter=" ... " '
))
else:
q = q.add_column("''")
q = q.add_column(Supplier.name)
q = q.add_column(postgres.array_agg(Supplier.data))
q = q.group_by(CaseStudy.id, Supplier.name)
if domains:
d_agg = postgres.array_agg(cast(Domain.name, TEXT))
q = q.having(d_agg.contains(array(domains)))
if seller_types:
selected_seller_types = select(
[postgres.array_agg(column('key'))],
from_obj=func.json_each_text(Supplier.data[('seller_type', )]),
whereclause=cast(column('value'), Boolean)).as_scalar()
q = q.filter(selected_seller_types.contains(array(seller_types)))
if sort_dir in ('desc', 'z-a'):
ob = [desc(CaseStudy.data['title'].astext)]
else:
ob = [asc(CaseStudy.data['title'].astext)]
if search_term:
ob = [
desc(
func.ts_rank_cd(
func.to_tsvector(
func.concat(
Supplier.name, CaseStudy.data['title'].astext,
CaseStudy.data['approach'].astext)), tsquery))
] + ob
condition = func.to_tsvector(
func.concat(Supplier.name, CaseStudy.data['title'].astext,
CaseStudy.data['approach'].astext)).op('@@')(tsquery)
q = q.filter(condition)
q = q.order_by(*ob)
raw_results = list(q)
results = []
for x in range(len(raw_results)):
result = raw_results[x][0].serialize()
if raw_results[x][1] is not None and raw_results[x][1] != '':
result['approach'] = raw_results[x][1]
if raw_results[x][2] is not None:
result['supplierName'] = raw_results[x][2]
if raw_results[x][3] is not None and raw_results[x][3][0] is not None:
result['seller_type'] = raw_results[x][3][0].get('seller_type')
results.append(result)
total_results = len(results)
sliced_results = results[offset:(offset + result_count)]
result = {
'hits': {
'total': total_results,
'hits': [{
'_source': r
} for r in sliced_results]
}
}
try:
response = jsonify(result), 200
except Exception as e:
response = jsonify(message=str(e)), 500
return response
def casestudies_search():
search_query = get_json_from_request()
offset = get_nonnegative_int_or_400(request.args, 'from', 0)
result_count = get_positive_int_or_400(request.args, 'size', current_app.config['DM_API_SUPPLIERS_PAGE_SIZE'])
sort_dir = search_query.get('sort_dir', 'asc')
sort_by = search_query.get('sort_by', None)
domains = search_query.get('domains', None)
seller_types = search_query.get('seller_types', None)
search_term = search_query.get('search_term', None)
framework_slug = request.args.get('framework', 'digital-marketplace')
q = db.session.query(CaseStudy).join(Supplier).outerjoin(SupplierDomain).outerjoin(Domain) \
.outerjoin(SupplierFramework).outerjoin(Framework)
q = q.filter(Supplier.status != 'deleted', or_(Framework.slug == framework_slug, ~Supplier.frameworks.any()))
tsquery = None
if search_term:
if ' ' in search_term:
tsquery = func.plainto_tsquery(search_term)
else:
tsquery = func.to_tsquery(search_term + ":*")
q = q.add_column(func.ts_headline(
'english',
func.concat(
CaseStudy.data['approach'].astext,
' ',
CaseStudy.data['role'].astext),
tsquery,
'MaxWords=150, MinWords=75, ShortWord=3, HighlightAll=FALSE, FragmentDelimiter=" ... " '
))
else:
q = q.add_column("''")
q = q.add_column(Supplier.name)
q = q.add_column(postgres.array_agg(Supplier.data))
q = q.group_by(CaseStudy.id, Supplier.name)
if domains:
d_agg = postgres.array_agg(cast(Domain.name, TEXT))
q = q.having(d_agg.contains(array(domains)))
if seller_types:
selected_seller_types = select(
[postgres.array_agg(column('key'))],
from_obj=func.json_each_text(Supplier.data[('seller_type',)]),
whereclause=cast(column('value'), Boolean)
).as_scalar()
q = q.filter(selected_seller_types.contains(array(seller_types)))
if sort_dir in ('desc', 'z-a'):
ob = [desc(CaseStudy.data['title'].astext)]
else:
ob = [asc(CaseStudy.data['title'].astext)]
if search_term:
ob = [desc(func.ts_rank_cd(func.to_tsvector(
func.concat(Supplier.name, CaseStudy.data['title'].astext,
CaseStudy.data['approach'].astext)), tsquery))] + ob
condition = func.to_tsvector(func.concat(Supplier.name,
CaseStudy.data['title'].astext,
CaseStudy.data['approach'].astext)).op('@@')(tsquery)
q = q.filter(condition)
q = q.order_by(*ob)
raw_results = list(q)
results = []
for x in range(len(raw_results)):
result = raw_results[x][0].serialize()
if raw_results[x][1] is not None and raw_results[x][1] != '':
result['approach'] = raw_results[x][1]
if raw_results[x][2] is not None:
result['supplierName'] = raw_results[x][2]
if raw_results[x][3] is not None and raw_results[x][3][0] is not None:
result['seller_type'] = raw_results[x][3][0].get('seller_type')
results.append(result)
total_results = len(results)
sliced_results = results[offset:(offset + result_count)]
result = {
'hits': {
'total': total_results,
'hits': [{'_source': r} for r in sliced_results]
}
}
try:
response = jsonify(result), 200
except Exception as e:
response = jsonify(message=str(e)), 500
return response