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