def return_all_contracts(filter_and, archived=False):
    """Return all contracts in the event of an empty search

    Arguments:
        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.
    """
    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)
        .filter(*filter_and)
    )

    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 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 return_all_contracts(filter_and, archived=False):
    '''Return all contracts in the event of an empty search

    Arguments:
        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.
    '''
    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).filter(*filter_and)

    contracts = add_archived_filter(contracts, archived)

    return contracts.all()