def get_csv_sources(json_request):
csv_sources = []
for award_level in json_request['award_levels']:
queryset = VALUE_MAPPINGS[award_level]['filter_function'](json_request['filters'])
award_level_table = VALUE_MAPPINGS[award_level]['table']
award_type_codes = set(json_request['filters']['award_type_codes'])
d1_award_type_codes = set(contract_type_mapping.keys())
d2_award_type_codes = set(assistance_type_mapping.keys())
if award_type_codes & d1_award_type_codes:
# only generate d1 files if the user is asking for contract data
d1_source = CsvSource(VALUE_MAPPINGS[award_level]['table_name'], 'd1', award_level)
d1_filters = {'{}__isnull'.format(VALUE_MAPPINGS[award_level]['contract_data']): False}
d1_source.queryset = queryset & award_level_table.objects.filter(**d1_filters)
csv_sources.append(d1_source)
if award_type_codes & d2_award_type_codes:
# only generate d2 files if the user is asking for assistance data
d2_source = CsvSource(VALUE_MAPPINGS[award_level]['table_name'], 'd2', award_level)
d2_filters = {'{}__isnull'.format(VALUE_MAPPINGS[award_level]['assistance_data']): False}
d2_source.queryset = queryset & award_level_table.objects.filter(**d2_filters)
csv_sources.append(d2_source)
verify_requested_columns_available(tuple(csv_sources), json_request.get('columns', []))
return csv_sources
def award_financial_recipient_parent_derivation(derived_fields):
derived_fields['recipient_parent_name'] = Case(
When(
award__latest_transaction__type__in=list(
contract_type_mapping.keys()),
then=
'award__latest_transaction__contract_data__ultimate_parent_legal_enti'
),
default=
'award__latest_transaction__assistance_data__ultimate_parent_legal_enti',
output_field=CharField())
return derived_fields
def get_csv_sources(json_request):
csv_sources = []
for download_type in json_request['download_types']:
agency_id = json_request['filters'].get('agency', 'all')
filter_function = VALUE_MAPPINGS[download_type]['filter_function']
download_type_table = VALUE_MAPPINGS[download_type]['table']
if VALUE_MAPPINGS[download_type]['source_type'] == 'award':
# Award downloads
queryset = filter_function(json_request['filters'])
award_type_codes = set(json_request['filters']['award_type_codes'])
if award_type_codes & (set(contract_type_mapping.keys())
| set(idv_type_mapping.keys())):
# only generate d1 files if the user is asking for contract data
d1_source = CsvSource(
VALUE_MAPPINGS[download_type]['table_name'], 'd1',
download_type, agency_id)
d1_filters = {
'{}__isnull'.format(VALUE_MAPPINGS[download_type]['contract_data']):
False
}
d1_source.queryset = queryset & download_type_table.objects.filter(
**d1_filters)
csv_sources.append(d1_source)
if award_type_codes & set(assistance_type_mapping.keys()):
# only generate d2 files if the user is asking for assistance data
d2_source = CsvSource(
VALUE_MAPPINGS[download_type]['table_name'], 'd2',
download_type, agency_id)
d2_filters = {
'{}__isnull'.format(VALUE_MAPPINGS[download_type]['assistance_data']):
False
}
d2_source.queryset = queryset & download_type_table.objects.filter(
**d2_filters)
csv_sources.append(d2_source)
verify_requested_columns_available(tuple(csv_sources),
json_request.get('columns', []))
elif VALUE_MAPPINGS[download_type]['source_type'] == 'account':
# Account downloads
account_source = CsvSource(
VALUE_MAPPINGS[download_type]['table_name'],
json_request['account_level'], download_type, agency_id)
account_source.queryset = filter_function(
download_type, VALUE_MAPPINGS[download_type]['table'],
json_request['filters'], json_request['account_level'])
csv_sources.append(account_source)
return csv_sources
def get_csv_sources(json_request):
csv_sources = []
for download_type in json_request["download_types"]:
agency_id = json_request["filters"].get("agency", "all")
filter_function = VALUE_MAPPINGS[download_type]["filter_function"]
download_type_table = VALUE_MAPPINGS[download_type]["table"]
if VALUE_MAPPINGS[download_type]["source_type"] == "award":
# Award downloads
# Use correct date range columns for advanced search
# (Will not change anything for keyword search since "time_period" is not provided))
filters = add_date_range_comparison_types(
json_request["filters"],
is_subaward=download_type != "awards",
gte_date_type="action_date",
lte_date_type="date_signed",
)
queryset = filter_function(filters)
award_type_codes = set(filters["award_type_codes"])
if award_type_codes & (set(contract_type_mapping.keys()) | set(idv_type_mapping.keys())):
# only generate d1 files if the user is asking for contract data
d1_source = CsvSource(VALUE_MAPPINGS[download_type]["table_name"], "d1", download_type, agency_id)
d1_filters = {"{}__isnull".format(VALUE_MAPPINGS[download_type]["contract_data"]): False}
d1_source.queryset = queryset & download_type_table.objects.filter(**d1_filters)
csv_sources.append(d1_source)
if award_type_codes & set(assistance_type_mapping.keys()):
# only generate d2 files if the user is asking for assistance data
d2_source = CsvSource(VALUE_MAPPINGS[download_type]["table_name"], "d2", download_type, agency_id)
d2_filters = {"{}__isnull".format(VALUE_MAPPINGS[download_type]["assistance_data"]): False}
d2_source.queryset = queryset & download_type_table.objects.filter(**d2_filters)
csv_sources.append(d2_source)
verify_requested_columns_available(tuple(csv_sources), json_request.get("columns", []))
elif VALUE_MAPPINGS[download_type]["source_type"] == "account":
# Account downloads
account_source = CsvSource(
VALUE_MAPPINGS[download_type]["table_name"], json_request["account_level"], download_type, agency_id
)
account_source.queryset = filter_function(
download_type,
VALUE_MAPPINGS[download_type]["table"],
json_request["filters"],
json_request["account_level"],
)
csv_sources.append(account_source)
return csv_sources
def _test_correct_response_of_contracts(client):
resp = post(
client,
award_type_codes=list(contract_type_mapping.keys()),
def_codes=["L", "M"],
geo_layer="district",
geo_layer_filters=["4510", "4550", "5350"],
spending_type="obligation",
)
expected_response = {
"geo_layer":
"district",
"scope":
"recipient_location",
"spending_type":
"obligation",
"results": [
{
"amount": 2000000.0,
"display_name": "SC-10",
"per_capita": None,
"population": None,
"shape_code": "4510",
"award_count": 1,
},
{
"amount": 200000.0,
"display_name": "SC-50",
"per_capita": 2000.0,
"population": 100,
"shape_code": "4550",
"award_count": 1,
},
{
"amount": 22000.0,
"display_name": "WA-50",
"per_capita": 22.0,
"population": 1000,
"shape_code": "5350",
"award_count": 2,
},
],
}
assert resp.status_code == status.HTTP_200_OK, "Failed to return 200 Response"
resp_json = resp.json()
resp_json["results"].sort(key=_get_shape_code_for_sort)
assert resp_json == expected_response
def award_financial_derivations(derived_fields):
derived_fields['recipient_parent_name'] = Case(
When(award__latest_transaction__type__in=list(contract_type_mapping.keys()),
then='award__latest_transaction__contract_data__ultimate_parent_legal_enti'),
default='award__latest_transaction__assistance_data__ultimate_parent_legal_enti',
output_field=CharField()
)
derived_fields['award_type_code'] = Coalesce(
'award__latest_transaction__contract_data__contract_award_type',
'award__latest_transaction__assistance_data__assistance_type'
)
derived_fields['award_type'] = Coalesce(
'award__latest_transaction__contract_data__contract_award_type_desc',
'award__latest_transaction__assistance_data__assistance_type_desc'
)
return derived_fields
def award_financial_derivations(derived_fields):
derived_fields["recipient_parent_name"] = Case(
When(
award__latest_transaction__type__in=list(
contract_type_mapping.keys()),
then=
"award__latest_transaction__contract_data__ultimate_parent_legal_enti",
),
default=
"award__latest_transaction__assistance_data__ultimate_parent_legal_enti",
output_field=CharField(),
)
derived_fields["award_type_code"] = Coalesce(
"award__latest_transaction__contract_data__contract_award_type",
"award__latest_transaction__assistance_data__assistance_type",
)
derived_fields["award_type"] = Coalesce(
"award__latest_transaction__contract_data__contract_award_type_desc",
"award__latest_transaction__assistance_data__assistance_type_desc",
)
return derived_fields
def post(self, request):
"""Return all awards matching the provided filters and limits"""
models = [{
'name': 'fields',
'key': 'fields',
'type': 'array',
'array_type': 'text',
'text_type': 'search',
'min': 1
}, {
'name': 'subawards',
'key': 'subawards',
'type': 'boolean',
'default': False
}]
models.extend(copy.deepcopy(AWARD_FILTER))
models.extend(copy.deepcopy(PAGINATION))
for m in models:
if m['name'] in ('award_type_codes', 'fields'):
m['optional'] = False
json_request = TinyShield(models).block(request.data)
fields = json_request["fields"]
filters = json_request.get("filters", {})
subawards = json_request["subawards"]
order = json_request["order"]
limit = json_request["limit"]
page = json_request["page"]
if "no intersection" in filters["award_type_codes"]:
# "Special case": there will never be results when the website provides this value
return Response({
"limit": limit,
"results": [],
"page_metadata": {
"page": page,
"hasNext": False
},
})
sort = json_request.get("sort", fields[0])
if sort not in fields:
raise InvalidParameterException(
"Sort value '{}' not found in requested fields: {}".format(
sort, fields))
subawards_values = list(contract_subaward_mapping.keys()) + list(
grant_subaward_mapping.keys())
awards_values = list(award_contracts_mapping.keys()) + list(loan_award_mapping.keys()) + \
list(non_loan_assistance_award_mapping.keys()) + list(award_idv_mapping.keys())
msg = "Sort value '{}' not found in {{}} mappings: {{}}".format(sort)
if not subawards and sort not in awards_values:
raise InvalidParameterException(msg.format("award", awards_values))
elif subawards and sort not in subawards_values:
raise InvalidParameterException(
msg.format("subaward", subawards_values))
# build sql query filters
if subawards:
queryset = subaward_filter(filters)
values = {'subaward_number', 'piid', 'fain', 'award_type'}
for field in fields:
if contract_subaward_mapping.get(field):
values.add(contract_subaward_mapping.get(field))
if grant_subaward_mapping.get(field):
values.add(grant_subaward_mapping.get(field))
else:
queryset = matview_search_filter(filters,
UniversalAwardView).values()
values = {'award_id', 'piid', 'fain', 'uri', 'type'}
for field in fields:
if award_contracts_mapping.get(field):
values.add(award_contracts_mapping.get(field))
if loan_award_mapping.get(field):
values.add(loan_award_mapping.get(field))
if non_loan_assistance_award_mapping.get(field):
values.add(non_loan_assistance_award_mapping.get(field))
if award_idv_mapping.get(field):
values.add(award_idv_mapping.get(field))
# Modify queryset to be ordered by requested "sort" in the request or default value(s)
if sort:
if subawards:
if set(filters["award_type_codes"]) <= set(
contract_type_mapping): # Subaward contracts
sort_filters = [contract_subaward_mapping[sort]]
elif set(filters["award_type_codes"]) <= set(
grant_type_mapping): # Subaward grants
sort_filters = [grant_subaward_mapping[sort]]
else:
msg = 'Award Type codes limited for Subawards. Only contracts {} or grants {} are available'
msg = msg.format(list(contract_type_mapping.keys()),
list(grant_type_mapping.keys()))
raise UnprocessableEntityException(msg)
else:
if set(filters["award_type_codes"]) <= set(
contract_type_mapping): # contracts
sort_filters = [award_contracts_mapping[sort]]
elif set(filters["award_type_codes"]) <= set(
loan_type_mapping): # loans
sort_filters = [loan_award_mapping[sort]]
elif set(filters["award_type_codes"]) <= set(
idv_type_mapping): # idvs
sort_filters = [award_idv_mapping[sort]]
else: # assistance data
sort_filters = [non_loan_assistance_award_mapping[sort]]
# Explictly set NULLS LAST in the ordering to encourage the usage of the indexes
if sort == "Award ID" and subawards:
if order == "desc":
queryset = queryset.order_by(
F('award__piid').desc(nulls_last=True),
F('award__fain').desc(nulls_last=True)).values(
*list(values))
else:
queryset = queryset.order_by(
F('award__piid').asc(nulls_last=True),
F('award__fain').asc(nulls_last=True)).values(
*list(values))
elif sort == "Award ID":
if order == "desc":
queryset = queryset.order_by(
F('piid').desc(nulls_last=True),
F('fain').desc(nulls_last=True),
F('uri').desc(nulls_last=True)).values(*list(values))
else:
queryset = queryset.order_by(
F('piid').asc(nulls_last=True),
F('fain').asc(nulls_last=True),
F('uri').asc(nulls_last=True)).values(*list(values))
elif order == "desc":
queryset = queryset.order_by(
F(sort_filters[0]).desc(nulls_last=True)).values(
*list(values))
else:
queryset = queryset.order_by(
F(sort_filters[0]).asc(nulls_last=True)).values(
*list(values))
limited_queryset = queryset[(page - 1) * limit:page * limit +
1] # lower limit : upper limit
has_next = len(limited_queryset) > limit
results = []
for award in limited_queryset[:limit]:
if subawards:
row = {"internal_id": award["subaward_number"]}
if award['award_type'] == 'procurement':
for field in fields:
row[field] = award.get(
contract_subaward_mapping[field])
elif award['award_type'] == 'grant':
for field in fields:
row[field] = award.get(grant_subaward_mapping[field])
else:
row = {"internal_id": award["award_id"]}
if award['type'] in loan_type_mapping: # loans
for field in fields:
row[field] = award.get(loan_award_mapping.get(field))
elif award[
'type'] in non_loan_assistance_type_mapping: # assistance data
for field in fields:
row[field] = award.get(
non_loan_assistance_award_mapping.get(field))
elif award['type'] in idv_type_mapping:
for field in fields:
row[field] = award.get(award_idv_mapping.get(field))
elif (award['type'] is None and
award['piid']) or award['type'] in contract_type_mapping:
# IDV + contract
for field in fields:
row[field] = award.get(
award_contracts_mapping.get(field))
if "Award ID" in fields:
for id_type in ["piid", "fain", "uri"]:
if award[id_type]:
row["Award ID"] = award[id_type]
break
results.append(row)
return Response({
"limit": limit,
"results": results,
"page_metadata": {
"page": page,
"hasNext": has_next
}
})
def subaward_filter(filters, for_downloads=False):
queryset = SubawardView.objects.all()
for key, value in filters.items():
if value is None:
raise InvalidParameterException('Invalid filter: ' + key +
' has null as its value.')
key_list = [
'keywords', 'elasticsearch_keyword', 'time_period',
'award_type_codes', 'agencies', 'legal_entities',
'recipient_search_text', 'recipient_scope', 'recipient_locations',
'recipient_type_names', 'place_of_performance_scope',
'place_of_performance_locations', 'award_amounts', 'award_ids',
'program_numbers', 'naics_codes', 'psc_codes',
'contract_pricing_type_codes', 'set_aside_type_codes',
'extent_competed_type_codes'
]
if key not in key_list:
raise InvalidParameterException('Invalid filter: ' + key +
' does not exist.')
if key == "keywords":
def keyword_parse(keyword):
# keyword_ts_vector & award_ts_vector are Postgres TS_vectors.
# keyword_ts_vector = recipient_name + psc_description + subaward_description
# award_ts_vector = piid + fain + uri + subaward_number
filter_obj = Q(keyword_ts_vector=keyword) | \
Q(award_ts_vector=keyword)
if keyword.isnumeric():
filter_obj |= Q(naics_code__contains=keyword)
if len(keyword) == 4 and PSC.objects.all().filter(
code__iexact=keyword).exists():
filter_obj |= Q(product_or_service_code__iexact=keyword)
return filter_obj
filter_obj = Q()
for keyword in value:
filter_obj |= keyword_parse(keyword)
potential_duns = list(
filter((lambda x: len(x) > 7 and len(x) < 10), value))
if len(potential_duns) > 0:
filter_obj |= Q(recipient_unique_id__in=potential_duns) | \
Q(parent_recipient_unique_id__in=potential_duns)
queryset = queryset.filter(filter_obj)
elif key == "elasticsearch_keyword":
keyword = value
transaction_ids = elasticsearch_helper.get_download_ids(
keyword=keyword, field='transaction_id')
# flatten IDs
transaction_ids = list(
itertools.chain.from_iterable(transaction_ids))
logger.info('Found {} transactions based on keyword: {}'.format(
len(transaction_ids), keyword))
transaction_ids = [
str(transaction_id) for transaction_id in transaction_ids
]
queryset = queryset.filter(latest_transaction_id__isnull=False)
queryset &= queryset.extra(where=[
'"latest_transaction_id" = ANY(\'{{{}}}\'::int[])'.format(
','.join(transaction_ids))
])
elif key == "time_period":
min_date = API_SEARCH_MIN_DATE
if for_downloads:
min_date = API_MIN_DATE
queryset &= combine_date_range_queryset(value, SubawardView,
min_date, API_MAX_DATE)
elif key == "award_type_codes":
idv_flag = all(i in value for i in contract_type_mapping.keys())
if len(value) != 0:
filter_obj = Q(prime_award_type__in=value)
if idv_flag:
filter_obj |= Q(pulled_from='IDV')
queryset &= SubawardView.objects.filter(filter_obj)
elif key == "agencies":
# TODO: Make function to match agencies in award filter throwing dupe error
funding_toptier = Q()
funding_subtier = Q()
awarding_toptier = Q()
awarding_subtier = Q()
for v in value:
type = v["type"]
tier = v["tier"]
name = v["name"]
if type == "funding":
if tier == "toptier":
funding_toptier |= Q(funding_toptier_agency_name=name)
elif tier == "subtier":
if 'toptier_name' in v:
funding_subtier |= (
Q(funding_subtier_agency_name=name) &
Q(funding_toptier_agency_name=v['toptier_name']
))
else:
funding_subtier |= Q(
funding_subtier_agency_name=name)
elif type == "awarding":
if tier == "toptier":
awarding_toptier |= Q(
awarding_toptier_agency_name=name)
elif tier == "subtier":
if 'toptier_name' in v:
awarding_subtier |= (Q(
awarding_subtier_agency_name=name
) & Q(
awarding_toptier_agency_name=v['toptier_name'])
)
else:
awarding_subtier |= Q(
awarding_subtier_agency_name=name)
awarding_queryfilter = Q()
funding_queryfilter = Q()
# Since these are Q filters, no DB hits for boolean checks
if funding_toptier:
funding_queryfilter |= funding_toptier
if funding_subtier:
funding_queryfilter |= funding_subtier
if awarding_toptier:
awarding_queryfilter |= awarding_toptier
if awarding_subtier:
awarding_queryfilter |= awarding_subtier
queryset = queryset.filter(funding_queryfilter
& awarding_queryfilter)
elif key == "legal_entities":
# This filter key has effectively become obsolete by recipient_search_text
msg = 'API request included "{}" key. No filtering will occur with provided value "{}"'
logger.info(msg.format(key, value))
elif key == "recipient_search_text":
def recip_string_parse(recipient_string):
upper_recipient_string = recipient_string.upper()
# recipient_name_ts_vector is a postgres TS_Vector
filter_obj = Q(recipient_name_ts_vector=upper_recipient_string)
if len(upper_recipient_string
) == 9 and upper_recipient_string[:5].isnumeric():
filter_obj |= Q(recipient_unique_id=upper_recipient_string)
return filter_obj
filter_obj = Q()
for recipient in value:
filter_obj |= recip_string_parse(recipient)
queryset &= SubawardView.objects.filter(filter_obj)
elif key == "recipient_scope":
if value == "domestic":
queryset = queryset.filter(
recipient_location_country_name="UNITED STATES")
elif value == "foreign":
queryset = queryset.exclude(
recipient_location_country_name="UNITED STATES")
else:
raise InvalidParameterException(
'Invalid filter: recipient_scope type is invalid.')
elif key == "recipient_locations":
or_queryset = geocode_filter_locations('recipient_location', value,
SubawardView, True)
queryset &= or_queryset
elif key == "recipient_type_names":
if len(value) != 0:
queryset &= SubawardView.objects.filter(
business_categories__overlap=value)
elif key == "place_of_performance_scope":
if value == "domestic":
queryset = queryset.filter(
Q(pop_country_name="UNITED STATES")
| Q(pop_country_code="USA"))
elif value == "foreign":
queryset = queryset.exclude(
Q(pop_country_name="UNITED STATES")
| Q(pop_country_code="USA"))
else:
raise InvalidParameterException(
'Invalid filter: place_of_performance_scope is invalid.')
elif key == "place_of_performance_locations":
queryset &= geocode_filter_locations('pop', value, SubawardView,
True)
elif key == "award_amounts":
queryset &= total_obligation_queryset(value, SubawardView, filters)
elif key == "award_ids":
filter_obj = Q()
for val in value:
# award_id_string is a Postgres TS_vector
# award_id_string = piid + fain + uri + subaward_number
filter_obj |= Q(award_ts_vector=val)
queryset &= SubawardView.objects.filter(filter_obj)
# add "naics_codes" (column naics) after NAICS are mapped to subawards
elif key in ("program_numbers", "psc_codes",
"contract_pricing_type_codes"):
filter_to_col = {
"program_numbers": "cfda_number",
"psc_codes": "product_or_service_code",
"contract_pricing_type_codes": "type_of_contract_pricing",
}
in_query = [v for v in value]
if len(in_query) != 0:
queryset &= SubawardView.objects.filter(
**{'{}__in'.format(filter_to_col[key]): in_query})
elif key in ("set_aside_type_codes", "extent_competed_type_codes"):
or_queryset = Q()
filter_to_col = {
"set_aside_type_codes": "type_set_aside",
"extent_competed_type_codes": "extent_competed",
}
in_query = [v for v in value]
for v in value:
or_queryset |= Q(
**{'{}__exact'.format(filter_to_col[key]): in_query})
queryset = queryset.filter(or_queryset)
return queryset
def post(self, request):
"""Return all awards matching the provided filters and limits"""
models = [
{'name': 'fields', 'key': 'fields', 'type': 'array', 'array_type': 'text', 'text_type': 'search', 'min': 1},
{'name': 'subawards', 'key': 'subawards', 'type': 'boolean', 'default': False}
]
models.extend(copy.deepcopy(AWARD_FILTER))
models.extend(copy.deepcopy(PAGINATION))
for m in models:
if m['name'] in ('award_type_codes', 'fields'):
m['optional'] = False
json_request = TinyShield(models).block(request.data)
fields = json_request["fields"]
filters = json_request.get("filters", {})
subawards = json_request["subawards"]
order = json_request["order"]
limit = json_request["limit"]
page = json_request["page"]
if "no intersection" in filters["award_type_codes"]:
# "Special case": there will never be results when the website provides this value
return Response({
"limit": limit,
"results": [],
"page_metadata": {"page": page, "hasNext": False},
})
sort = json_request.get("sort", fields[0])
if sort not in fields:
raise InvalidParameterException("Sort value '{}' not found in requested fields: {}".format(sort, fields))
subawards_values = list(contract_subaward_mapping.keys()) + list(grant_subaward_mapping.keys())
awards_values = list(award_contracts_mapping.keys()) + list(loan_award_mapping.keys()) + \
list(non_loan_assistance_award_mapping.keys()) + list(award_idv_mapping.keys())
msg = "Sort value '{}' not found in {{}} mappings: {{}}".format(sort)
if not subawards and sort not in awards_values:
raise InvalidParameterException(msg.format("award", awards_values))
elif subawards and sort not in subawards_values:
raise InvalidParameterException(msg.format("subaward", subawards_values))
# build sql query filters
if subawards:
queryset = subaward_filter(filters)
values = {'subaward_number', 'piid', 'fain', 'award_type'}
for field in fields:
if contract_subaward_mapping.get(field):
values.add(contract_subaward_mapping.get(field))
if grant_subaward_mapping.get(field):
values.add(grant_subaward_mapping.get(field))
else:
queryset = matview_search_filter(filters, UniversalAwardView).values()
values = {'award_id', 'piid', 'fain', 'uri', 'type'}
for field in fields:
if award_contracts_mapping.get(field):
values.add(award_contracts_mapping.get(field))
if loan_award_mapping.get(field):
values.add(loan_award_mapping.get(field))
if non_loan_assistance_award_mapping.get(field):
values.add(non_loan_assistance_award_mapping.get(field))
if award_idv_mapping.get(field):
values.add(award_idv_mapping.get(field))
# Modify queryset to be ordered by requested "sort" in the request or default value(s)
if sort:
if subawards:
if set(filters["award_type_codes"]) <= set(contract_type_mapping): # Subaward contracts
sort_filters = [contract_subaward_mapping[sort]]
elif set(filters["award_type_codes"]) <= set(grant_type_mapping): # Subaward grants
sort_filters = [grant_subaward_mapping[sort]]
else:
msg = 'Award Type codes limited for Subawards. Only contracts {} or grants {} are available'
msg = msg.format(list(contract_type_mapping.keys()), list(grant_type_mapping.keys()))
raise UnprocessableEntityException(msg)
else:
if set(filters["award_type_codes"]) <= set(contract_type_mapping): # contracts
sort_filters = [award_contracts_mapping[sort]]
elif set(filters["award_type_codes"]) <= set(loan_type_mapping): # loans
sort_filters = [loan_award_mapping[sort]]
elif set(filters["award_type_codes"]) <= set(idv_type_mapping): # idvs
sort_filters = [award_idv_mapping[sort]]
else: # assistance data
sort_filters = [non_loan_assistance_award_mapping[sort]]
# Explictly set NULLS LAST in the ordering to encourage the usage of the indexes
if sort == "Award ID" and subawards:
if order == "desc":
queryset = queryset.order_by(
F('award__piid').desc(nulls_last=True),
F('award__fain').desc(nulls_last=True)).values(*list(values))
else:
queryset = queryset.order_by(
F('award__piid').asc(nulls_last=True),
F('award__fain').asc(nulls_last=True)).values(*list(values))
elif sort == "Award ID":
if order == "desc":
queryset = queryset.order_by(
F('piid').desc(nulls_last=True),
F('fain').desc(nulls_last=True),
F('uri').desc(nulls_last=True)).values(*list(values))
else:
queryset = queryset.order_by(
F('piid').asc(nulls_last=True),
F('fain').asc(nulls_last=True),
F('uri').asc(nulls_last=True)).values(*list(values))
elif order == "desc":
queryset = queryset.order_by(F(sort_filters[0]).desc(nulls_last=True)).values(*list(values))
else:
queryset = queryset.order_by(F(sort_filters[0]).asc(nulls_last=True)).values(*list(values))
limited_queryset = queryset[(page - 1) * limit:page * limit + 1] # lower limit : upper limit
has_next = len(limited_queryset) > limit
results = []
for award in limited_queryset[:limit]:
if subawards:
row = {"internal_id": award["subaward_number"]}
if award['award_type'] == 'procurement':
for field in fields:
row[field] = award.get(contract_subaward_mapping[field])
elif award['award_type'] == 'grant':
for field in fields:
row[field] = award.get(grant_subaward_mapping[field])
else:
row = {"internal_id": award["award_id"]}
if award['type'] in loan_type_mapping: # loans
for field in fields:
row[field] = award.get(loan_award_mapping.get(field))
elif award['type'] in non_loan_assistance_type_mapping: # assistance data
for field in fields:
row[field] = award.get(non_loan_assistance_award_mapping.get(field))
elif award['type'] in idv_type_mapping:
for field in fields:
row[field] = award.get(award_idv_mapping.get(field))
elif (award['type'] is None and award['piid']) or award['type'] in contract_type_mapping:
# IDV + contract
for field in fields:
row[field] = award.get(award_contracts_mapping.get(field))
if "Award ID" in fields:
for id_type in ["piid", "fain", "uri"]:
if award[id_type]:
row["Award ID"] = award[id_type]
break
results.append(row)
return Response({"limit": limit, "results": results, "page_metadata": {"page": page, "hasNext": has_next}})
def post(self, request):
"""Return all budget function/subfunction titles matching the provided search text"""
models = [
{'name': 'fields', 'key': 'fields', 'type': 'array', 'array_type': 'text', 'text_type': 'search'},
{'name': 'subawards', 'key': 'subawards', 'type': 'boolean', 'default': False}
]
models.extend(copy.deepcopy(AWARD_FILTER))
models.extend(copy.deepcopy(PAGINATION))
for m in models:
if m['name'] in ('award_type_codes', 'fields'):
m['optional'] = False
json_request = TinyShield(models).block(request.data)
fields = json_request.get("fields", None)
filters = json_request.get("filters", None)
subawards = json_request["subawards"]
order = json_request["order"]
limit = json_request["limit"]
page = json_request["page"]
lower_limit = (page - 1) * limit
upper_limit = page * limit
sort = json_request.get("sort", fields[0])
if sort not in fields:
raise InvalidParameterException("Sort value not found in fields: {}".format(sort))
subawards_values = list(contract_subaward_mapping.keys()) + list(grant_subaward_mapping.keys())
awards_values = list(award_contracts_mapping.keys()) + list(loan_award_mapping) + \
list(non_loan_assistance_award_mapping.keys())
if (subawards and sort not in subawards_values) or (not subawards and sort not in awards_values):
raise InvalidParameterException("Sort value not found in award mappings: {}".format(sort))
# build sql query filters
if subawards:
# We do not use matviews for Subaward filtering, just the Subaward download filters
queryset = subaward_filter(filters)
values = {'subaward_number', 'award__piid', 'award__fain', 'award_type'}
for field in fields:
if contract_subaward_mapping.get(field):
values.add(contract_subaward_mapping.get(field))
if grant_subaward_mapping.get(field):
values.add(grant_subaward_mapping.get(field))
else:
queryset = matview_search_filter(filters, UniversalAwardView).values()
values = {'award_id', 'piid', 'fain', 'uri', 'type'}
for field in fields:
if award_contracts_mapping.get(field):
values.add(award_contracts_mapping.get(field))
if loan_award_mapping.get(field):
values.add(loan_award_mapping.get(field))
if non_loan_assistance_award_mapping.get(field):
values.add(non_loan_assistance_award_mapping.get(field))
# Modify queryset to be ordered if we specify "sort" in the request
if sort and "no intersection" not in filters["award_type_codes"]:
if subawards:
if set(filters["award_type_codes"]) <= set(contract_type_mapping): # Subaward contracts
sort_filters = [contract_subaward_mapping[sort]]
elif set(filters["award_type_codes"]) <= set(grant_type_mapping): # Subaward grants
sort_filters = [grant_subaward_mapping[sort]]
else:
msg = 'Award Type codes limited for Subawards. Only contracts {} or grants {} are available'
msg = msg.format(list(contract_type_mapping.keys()), list(grant_type_mapping.keys()))
raise UnprocessableEntityException(msg)
else:
if set(filters["award_type_codes"]) <= set(contract_type_mapping): # contracts
sort_filters = [award_contracts_mapping[sort]]
elif set(filters["award_type_codes"]) <= set(loan_type_mapping): # loans
sort_filters = [loan_award_mapping[sort]]
else: # assistance data
sort_filters = [non_loan_assistance_award_mapping[sort]]
# Explictly set NULLS LAST in the ordering to encourage the usage of the indexes
if sort == "Award ID" and subawards:
if order == "desc":
queryset = queryset.order_by(
F('award__piid').desc(nulls_last=True),
F('award__fain').desc(nulls_last=True)).values(*list(values))
else:
queryset = queryset.order_by(
F('award__piid').asc(nulls_last=True),
F('award__fain').asc(nulls_last=True)).values(*list(values))
elif sort == "Award ID":
if order == "desc":
queryset = queryset.order_by(
F('piid').desc(nulls_last=True),
F('fain').desc(nulls_last=True),
F('uri').desc(nulls_last=True)).values(*list(values))
else:
queryset = queryset.order_by(
F('piid').asc(nulls_last=True),
F('fain').asc(nulls_last=True),
F('uri').asc(nulls_last=True)).values(*list(values))
elif order == "desc":
queryset = queryset.order_by(F(sort_filters[0]).desc(nulls_last=True)).values(*list(values))
else:
queryset = queryset.order_by(F(sort_filters[0]).asc(nulls_last=True)).values(*list(values))
limited_queryset = queryset[lower_limit:upper_limit + 1]
has_next = len(limited_queryset) > limit
results = []
for award in limited_queryset[:limit]:
if subawards:
row = {"internal_id": award["subaward_number"]}
if award['award_type'] == 'procurement':
for field in fields:
row[field] = award.get(contract_subaward_mapping[field])
elif award['award_type'] == 'grant':
for field in fields:
row[field] = award.get(grant_subaward_mapping[field])
else:
row = {"internal_id": award["award_id"]}
if award['type'] in loan_type_mapping: # loans
for field in fields:
row[field] = award.get(loan_award_mapping.get(field))
elif award['type'] in non_loan_assistance_type_mapping: # assistance data
for field in fields:
row[field] = award.get(non_loan_assistance_award_mapping.get(field))
elif (award['type'] is None and award['piid']) or award['type'] in contract_type_mapping:
# IDV + contract
for field in fields:
row[field] = award.get(award_contracts_mapping.get(field))
if "Award ID" in fields:
for id_type in ["piid", "fain", "uri"]:
if award[id_type]:
row["Award ID"] = award[id_type]
break
results.append(row)
# build response
response = {
'limit': limit,
'results': results,
'page_metadata': {
'page': page,
'hasNext': has_next
}
}
return Response(response)
def validate_disaster_recipient_request(request_data):
_validate_required_parameters(request_data, ["filters"])
model = [
{
"key":
"filters|def_codes",
"name":
"def_codes",
"type":
"array",
"array_type":
"enum",
"enum_values":
sorted(
DisasterEmergencyFundCode.objects.values_list("code",
flat=True)),
"allow_nulls":
False,
"optional":
False,
},
{
"key": "filters|query",
"name": "query",
"type": "text",
"text_type": "search",
"allow_nulls": False,
"optional": True,
},
{
"key": "filters|award_type_codes",
"name": "award_type_codes",
"type": "array",
"array_type": "enum",
"enum_values": sorted(award_type_mapping.keys()),
"allow_nulls": False,
"optional": True,
},
]
filters = TinyShield(model).block(request_data)["filters"]
# Determine what to use in the filename based on "award_type_codes" filter;
# Also add "face_value_of_loans" column if only loan types
award_category = "All-Awards"
award_type_codes = set(
filters.get("award_type_codes", award_type_mapping.keys()))
columns = [
"recipient", "award_obligations", "award_outlays", "number_of_awards"
]
if award_type_codes <= set(contract_type_mapping.keys()):
award_category = "Contracts"
elif award_type_codes <= set(idv_type_mapping.keys()):
award_category = "Contract-IDVs"
elif award_type_codes <= set(grant_type_mapping.keys()):
award_category = "Grants"
elif award_type_codes <= set(loan_type_mapping.keys()):
award_category = "Loans"
columns.insert(3, "face_value_of_loans")
elif award_type_codes <= set(direct_payment_type_mapping.keys()):
award_category = "Direct-Payments"
elif award_type_codes <= set(other_type_mapping.keys()):
award_category = "Other-Financial-Assistance"
# Need to specify the field to use "query" filter on if present
query_text = filters.pop("query", None)
if query_text:
filters["query"] = {"text": query_text, "fields": ["recipient_name"]}
json_request = {
"award_category": award_category,
"columns": tuple(columns),
"download_types": ["disaster_recipient"],
"file_format": str(request_data.get("file_format", "csv")).lower(),
"filters": filters,
}
_validate_file_format(json_request)
return json_request
def award_financial_derivations(derived_fields):
derived_fields["recipient_parent_name"] = Case(
When(
award__latest_transaction__type__in=list(
contract_type_mapping.keys()),
then=
"award__latest_transaction__contract_data__ultimate_parent_legal_enti",
),
default=
"award__latest_transaction__assistance_data__ultimate_parent_legal_enti",
output_field=CharField(),
)
derived_fields["award_type_code"] = Coalesce(
"award__latest_transaction__contract_data__contract_award_type",
"award__latest_transaction__assistance_data__assistance_type",
)
derived_fields["award_type"] = Coalesce(
"award__latest_transaction__contract_data__contract_award_type_desc",
"award__latest_transaction__assistance_data__assistance_type_desc",
)
derived_fields["awarding_agency_code"] = Coalesce(
"award__latest_transaction__contract_data__awarding_agency_code",
"award__latest_transaction__assistance_data__awarding_agency_code",
)
derived_fields["awarding_agency_name"] = Coalesce(
"award__latest_transaction__contract_data__awarding_agency_name",
"award__latest_transaction__assistance_data__awarding_agency_name",
)
derived_fields["awarding_subagency_code"] = Coalesce(
"award__latest_transaction__contract_data__awarding_sub_tier_agency_c",
"award__latest_transaction__assistance_data__awarding_sub_tier_agency_c",
)
derived_fields["awarding_subagency_name"] = Coalesce(
"award__latest_transaction__contract_data__awarding_sub_tier_agency_n",
"award__latest_transaction__assistance_data__awarding_sub_tier_agency_n",
)
derived_fields["awarding_office_code"] = Coalesce(
"award__latest_transaction__contract_data__awarding_office_code",
"award__latest_transaction__assistance_data__awarding_office_code",
)
derived_fields["awarding_office_name"] = Coalesce(
"award__latest_transaction__contract_data__awarding_office_name",
"award__latest_transaction__assistance_data__awarding_office_name",
)
derived_fields["funding_agency_code"] = Coalesce(
"award__latest_transaction__contract_data__funding_agency_code",
"award__latest_transaction__assistance_data__funding_agency_code",
)
derived_fields["funding_agency_name"] = Coalesce(
"award__latest_transaction__contract_data__funding_agency_name",
"award__latest_transaction__assistance_data__funding_agency_name",
)
derived_fields["funding_sub_agency_code"] = Coalesce(
"award__latest_transaction__contract_data__funding_sub_tier_agency_co",
"award__latest_transaction__assistance_data__funding_sub_tier_agency_co",
)
derived_fields["funding_sub_agency_name"] = Coalesce(
"award__latest_transaction__contract_data__funding_sub_tier_agency_na",
"award__latest_transaction__assistance_data__funding_sub_tier_agency_na",
)
derived_fields["funding_office_code"] = Coalesce(
"award__latest_transaction__contract_data__funding_office_code",
"award__latest_transaction__assistance_data__funding_office_code",
)
derived_fields["funding_office_name"] = Coalesce(
"award__latest_transaction__contract_data__funding_office_name",
"award__latest_transaction__assistance_data__funding_office_name",
)
derived_fields["recipient_duns"] = Coalesce(
"award__latest_transaction__contract_data__awardee_or_recipient_uniqu",
"award__latest_transaction__assistance_data__awardee_or_recipient_uniqu",
)
derived_fields["recipient_name"] = Coalesce(
"award__latest_transaction__contract_data__awardee_or_recipient_legal",
"award__latest_transaction__assistance_data__awardee_or_recipient_legal",
)
derived_fields["recipient_parent_duns"] = Coalesce(
"award__latest_transaction__contract_data__ultimate_parent_unique_ide",
"award__latest_transaction__assistance_data__ultimate_parent_unique_ide",
)
derived_fields["recipient_parent_name"] = Coalesce(
"award__latest_transaction__contract_data__ultimate_parent_legal_enti",
"award__latest_transaction__assistance_data__ultimate_parent_legal_enti",
)
derived_fields["recipient_country"] = Coalesce(
"award__latest_transaction__contract_data__legal_entity_country_code",
"award__latest_transaction__assistance_data__legal_entity_country_code",
)
derived_fields["recipient_state"] = Coalesce(
"award__latest_transaction__contract_data__legal_entity_state_code",
"award__latest_transaction__assistance_data__legal_entity_state_code",
)
derived_fields["recipient_county"] = Coalesce(
"award__latest_transaction__contract_data__legal_entity_county_name",
"award__latest_transaction__assistance_data__legal_entity_county_name",
)
derived_fields["recipient_city"] = Coalesce(
"award__latest_transaction__contract_data__legal_entity_city_name",
"award__latest_transaction__assistance_data__legal_entity_city_name",
)
derived_fields["recipient_congressional_district"] = Coalesce(
"award__latest_transaction__contract_data__legal_entity_congressional",
"award__latest_transaction__assistance_data__legal_entity_congressional",
)
derived_fields["recipient_zip_code"] = Coalesce(
"award__latest_transaction__contract_data__legal_entity_zip4",
Concat(
"award__latest_transaction__assistance_data__legal_entity_zip5",
"award__latest_transaction__assistance_data__legal_entity_zip_last4",
),
)
derived_fields["primary_place_of_performance_country"] = Coalesce(
"award__latest_transaction__contract_data__place_of_perf_country_desc",
"award__latest_transaction__assistance_data__place_of_perform_country_n",
)
derived_fields["primary_place_of_performance_state"] = Coalesce(
"award__latest_transaction__contract_data__place_of_perfor_state_desc",
"award__latest_transaction__assistance_data__place_of_perform_state_nam",
)
derived_fields["primary_place_of_performance_county"] = Coalesce(
"award__latest_transaction__contract_data__place_of_perform_county_na",
"award__latest_transaction__assistance_data__place_of_perform_county_na",
)
derived_fields["primary_place_of_performance_congressional_district"] = Coalesce(
"award__latest_transaction__contract_data__place_of_performance_congr",
"award__latest_transaction__assistance_data__place_of_performance_congr",
)
derived_fields["primary_place_of_performance_zip_code"] = Coalesce(
"award__latest_transaction__contract_data__place_of_performance_zip4a",
"award__latest_transaction__assistance_data__place_of_performance_zip4a",
)
derived_fields["usaspending_permalink"] = Case(
When(
**{
"award__generated_unique_award_id__isnull":
False,
"then":
Concat(
Value(AWARD_URL),
Func(F("award__generated_unique_award_id"),
function="urlencode"), Value("/")),
}),
default=Value(""),
output_field=CharField(),
)
return derived_fields
def __init__(self, request_data: dict):
super().__init__(request_data)
self.tinyshield_models.extend([
{
"key":
"filters|def_codes",
"name":
"def_codes",
"type":
"array",
"array_type":
"enum",
"enum_values":
sorted(
DisasterEmergencyFundCode.objects.values_list("code",
flat=True)),
"allow_nulls":
False,
"optional":
False,
},
{
"key": "filters|query",
"name": "query",
"type": "text",
"text_type": "search",
"allow_nulls": False,
"optional": True,
},
{
"key": "filters|award_type_codes",
"name": "award_type_codes",
"type": "array",
"array_type": "enum",
"enum_values": sorted(award_type_mapping.keys()),
"allow_nulls": False,
"optional": True,
},
])
self._json_request["filters"] = request_data.get("filters")
self._json_request = self.get_validated_request()
self._json_request["download_types"] = [self.name]
# Determine what to use in the filename based on "award_type_codes" filter;
# Also add "face_value_of_loans" column if only loan types
award_category = "All-Awards"
award_type_codes = set(self._json_request["filters"].get(
"award_type_codes", award_type_mapping.keys()))
columns = [
"recipient", "award_obligations", "award_outlays",
"number_of_awards"
]
if award_type_codes <= set(contract_type_mapping.keys()):
award_category = "Contracts"
elif award_type_codes <= set(idv_type_mapping.keys()):
award_category = "Contract-IDVs"
elif award_type_codes <= set(grant_type_mapping.keys()):
award_category = "Grants"
elif award_type_codes <= set(loan_type_mapping.keys()):
award_category = "Loans"
columns.insert(3, "face_value_of_loans")
elif award_type_codes <= set(direct_payment_type_mapping.keys()):
award_category = "Direct-Payments"
elif award_type_codes <= set(other_type_mapping.keys()):
award_category = "Other-Financial-Assistance"
self._json_request["award_category"] = award_category
self._json_request["columns"] = self._json_request.get(
"columns") or tuple(columns)
# Need to specify the field to use "query" filter on if present
query_text = self._json_request["filters"].pop("query", None)
if query_text:
self._json_request["filters"]["query"] = {
"text": query_text,
"fields": ["recipient_name"]
}
assistance_type_mapping,
contract_type_mapping,
direct_payment_type_mapping,
grant_type_mapping,
idv_type_mapping,
loan_type_mapping,
other_type_mapping,
procurement_type_mapping,
)
TYPES_TO_QUOTE_IN_SQL = (str, date)
CATEGORY_TO_MODEL = {
"contracts": {"model": ContractAwardSearchMatview, "types": set(contract_type_mapping.keys())},
"direct_payments": {"model": DirectPaymentAwardSearchMatview, "types": set(direct_payment_type_mapping.keys())},
"grants": {"model": GrantAwardSearchMatview, "types": set(grant_type_mapping.keys())},
"idvs": {"model": IDVAwardSearchMatview, "types": set(idv_type_mapping.keys())},
"loans": {"model": LoanAwardSearchMatview, "types": set(loan_type_mapping.keys())},
"other": {"model": OtherAwardSearchMatview, "types": set(other_type_mapping.keys())},
}
class AwardGroupsException(Exception):
"""Custom Exception for a specific event"""
class FiscalMonth(Func):
function = "EXTRACT"
template = "%(function)s(MONTH from (%(expressions)s) + INTERVAL '3 months')"
def get_download_sources(json_request: dict,
origination: Optional[str] = None):
download_sources = []
for download_type in json_request["download_types"]:
agency_id = json_request.get("agency", "all")
filter_function = VALUE_MAPPINGS[download_type]["filter_function"]
download_type_table = VALUE_MAPPINGS[download_type]["table"]
if VALUE_MAPPINGS[download_type]["source_type"] == "award":
# Award downloads
# Use correct date range columns for advanced search
# (Will not change anything for keyword search since "time_period" is not provided))
filters = add_date_range_comparison_types(
json_request["filters"],
is_subaward=download_type != "awards",
gte_date_type="action_date",
lte_date_type="date_signed",
)
queryset = filter_function(filters)
if filters.get("prime_and_sub_award_types") is not None:
award_type_codes = set(
filters["prime_and_sub_award_types"][download_type])
else:
award_type_codes = set(filters["award_type_codes"])
if (award_type_codes & (set(contract_type_mapping.keys())
| set(idv_type_mapping.keys()))
or "procurement" in award_type_codes):
# only generate d1 files if the user is asking for contract data
d1_source = DownloadSource(
VALUE_MAPPINGS[download_type]["table_name"], "d1",
download_type, agency_id)
d1_filters = {
f"{VALUE_MAPPINGS[download_type]['contract_data']}__isnull":
False
}
d1_source.queryset = queryset & download_type_table.objects.filter(
**d1_filters)
download_sources.append(d1_source)
if award_type_codes & set(assistance_type_mapping.keys()) or (
"grant" in award_type_codes):
# only generate d2 files if the user is asking for assistance data
d2_source = DownloadSource(
VALUE_MAPPINGS[download_type]["table_name"], "d2",
download_type, agency_id)
d2_filters = {
f"{VALUE_MAPPINGS[download_type]['assistance_data']}__isnull":
False
}
d2_source.queryset = queryset & download_type_table.objects.filter(
**d2_filters)
download_sources.append(d2_source)
elif VALUE_MAPPINGS[download_type]["source_type"] == "account":
# Account downloads
account_source = DownloadSource(
VALUE_MAPPINGS[download_type]["table_name"],
json_request["account_level"], download_type, agency_id)
account_source.queryset = filter_function(
download_type,
VALUE_MAPPINGS[download_type]["table"],
json_request["filters"],
json_request["account_level"],
)
download_sources.append(account_source)
verify_requested_columns_available(tuple(download_sources),
json_request.get("columns", []))
return download_sources
def matview_search_filter(filters, model):
queryset = model.objects.all()
faba_flag = False
faba_queryset = FinancialAccountsByAwards.objects.filter(
award__isnull=False)
for key, value in filters.items():
if value is None:
raise InvalidParameterException('Invalid filter: ' + key +
' has null as its value.')
key_list = [
'keyword',
'time_period',
'award_type_codes',
'agencies',
'legal_entities',
'recipient_search_text',
'recipient_scope',
'recipient_locations',
'recipient_type_names',
'place_of_performance_scope',
'place_of_performance_locations',
'award_amounts',
'award_ids',
'program_numbers',
'naics_codes',
'psc_codes',
'contract_pricing_type_codes',
'set_aside_type_codes',
'extent_competed_type_codes',
# next 3 keys used by federal account page
'federal_account_ids',
'object_class',
'program_activity'
]
if key not in key_list:
raise InvalidParameterException('Invalid filter: ' + key +
' does not exist.')
if key == "keyword":
keyword = value
upper_kw = keyword.upper()
# keyword_string & award_id_string are Postgres TS_vectors.
# keyword_string = recipient_name + naics_code + naics_description + psc_description + awards_description
# award_id_string = piid + fain + uri
compound_or = Q(keyword_ts_vector=keyword) | \
Q(award_ts_vector=keyword) | \
Q(recipient_unique_id=upper_kw) | \
Q(parent_recipient_unique_id=keyword)
if keyword.isnumeric():
compound_or |= Q(naics_code__contains=keyword)
if len(keyword) == 4 and PSC.objects.all().filter(
code__iexact=keyword).exists():
compound_or |= Q(product_or_service_code__iexact=keyword)
queryset = queryset.filter(compound_or)
elif key == "time_period":
success, or_queryset = date_or_fy_queryset(value, model,
"fiscal_year",
"action_date")
if success:
queryset &= or_queryset
elif key == "award_type_codes":
idv_flag = all(i in value for i in contract_type_mapping.keys())
if len(value) != 0:
filter_obj = Q(type__in=value)
if idv_flag:
filter_obj |= Q(pulled_from='IDV')
queryset &= model.objects.filter(filter_obj)
elif key == "agencies":
# TODO: Make function to match agencies in award filter throwing dupe error
funding_toptier = Q()
funding_subtier = Q()
awarding_toptier = Q()
awarding_subtier = Q()
for v in value:
type = v["type"]
tier = v["tier"]
name = v["name"]
if type == "funding":
if tier == "toptier":
funding_toptier |= Q(funding_toptier_agency_name=name)
elif tier == "subtier":
if 'toptier_name' in v:
funding_subtier |= (
Q(funding_subtier_agency_name=name) &
Q(funding_toptier_agency_name=v['toptier_name']
))
else:
funding_subtier |= Q(
funding_subtier_agency_name=name)
else:
raise InvalidParameterException(
'Invalid filter: agencies ' + tier +
' tier is invalid.')
elif type == "awarding":
if tier == "toptier":
awarding_toptier |= Q(
awarding_toptier_agency_name=name)
elif tier == "subtier":
if 'toptier_name' in v:
awarding_subtier |= (Q(
awarding_subtier_agency_name=name
) & Q(
awarding_toptier_agency_name=v['toptier_name'])
)
else:
awarding_subtier |= Q(
awarding_subtier_agency_name=name)
else:
raise InvalidParameterException(
'Invalid filter: agencies ' + tier +
' tier is invalid.')
else:
raise InvalidParameterException(
'Invalid filter: agencies ' + type +
' type is invalid.')
awarding_queryfilter = Q()
funding_queryfilter = Q()
# Since these are Q filters, no DB hits for boolean checks
if funding_toptier:
funding_queryfilter |= funding_toptier
if funding_subtier:
funding_queryfilter |= funding_subtier
if awarding_toptier:
awarding_queryfilter |= awarding_toptier
if awarding_subtier:
awarding_queryfilter |= awarding_subtier
queryset = queryset.filter(funding_queryfilter
& awarding_queryfilter)
elif key == "legal_entities":
in_query = [v for v in value]
if len(in_query) != 0:
queryset &= model.objects.filter(recipient_id__in=in_query)
elif key == "recipient_search_text":
if len(value) != 1:
raise InvalidParameterException(
'Invalid filter: recipient_search_text must have exactly one value.'
)
upper_recipient_string = str(value[0]).upper()
# recipient_name_ts_vector is a postgres TS_Vector
filter_obj = Q(recipient_name_ts_vector=upper_recipient_string)
if len(upper_recipient_string
) == 9 and upper_recipient_string[:5].isnumeric():
filter_obj |= Q(recipient_unique_id=upper_recipient_string)
queryset &= model.objects.filter(filter_obj)
elif key == "recipient_scope":
if value == "domestic":
queryset = queryset.filter(
recipient_location_country_name="UNITED STATES")
elif value == "foreign":
queryset = queryset.exclude(
recipient_location_country_name="UNITED STATES")
else:
raise InvalidParameterException(
'Invalid filter: recipient_scope type is invalid.')
elif key == "recipient_locations":
or_queryset = geocode_filter_locations('recipient_location', value,
model, True)
queryset &= or_queryset
elif key == "recipient_type_names":
if len(value) != 0:
queryset &= model.objects.filter(
business_categories__overlap=value)
elif key == "place_of_performance_scope":
if value == "domestic":
queryset = queryset.filter(pop_country_name="UNITED STATES")
elif value == "foreign":
queryset = queryset.exclude(pop_country_name="UNITED STATES")
else:
raise InvalidParameterException(
'Invalid filter: place_of_performance_scope is invalid.')
elif key == "place_of_performance_locations":
queryset &= geocode_filter_locations('pop', value, model, True)
elif key == "award_amounts":
success, and_queryset = total_obligation_queryset(
value, model, filters)
if success:
queryset &= and_queryset
elif key == "award_ids":
if len(value) != 0:
filter_obj = Q()
for val in value:
# award_id_string is a Postgres TS_vector
# award_id_string = piid + fain + uri
filter_obj |= Q(award_ts_vector=val)
queryset &= model.objects.filter(filter_obj)
elif key == "program_numbers":
in_query = [v for v in value]
if len(in_query) != 0:
queryset &= model.objects.filter(cfda_number__in=in_query)
elif key == "naics_codes":
in_query = [v for v in value]
if len(in_query) != 0:
queryset &= model.objects.filter(naics_code__in=in_query)
elif key == "psc_codes":
in_query = [v for v in value]
if len(in_query) != 0:
queryset &= model.objects.filter(
product_or_service_code__in=in_query)
elif key == "contract_pricing_type_codes":
in_query = [v for v in value]
if len(in_query) != 0:
queryset &= model.objects.filter(
type_of_contract_pricing__in=in_query)
elif key == "set_aside_type_codes":
or_queryset = Q()
for v in value:
or_queryset |= Q(type_set_aside__exact=v)
queryset = queryset.filter(or_queryset)
elif key == "extent_competed_type_codes":
or_queryset = Q()
for v in value:
or_queryset |= Q(extent_competed__exact=v)
queryset = queryset.filter(or_queryset)
# Federal Account Filter
elif key == "federal_account_ids":
faba_flag = True
or_queryset = Q()
for v in value:
or_queryset |= Q(treasury_account__federal_account_id=v)
faba_queryset = faba_queryset.filter(or_queryset)
# Federal Account Filter
elif key == "object_class":
faba_flag = True
result = Q()
for oc in value:
subresult = Q()
for (key, values) in oc.items():
subresult &= filter_on(
"treasury_account__program_balances__object_class",
key, values)
result |= subresult
faba_queryset = faba_queryset.filter(result)
# Federal Account Filter
elif key == "program_activity":
faba_flag = True
or_queryset = Q()
for v in value:
or_queryset |= Q(
treasury_account__program_balances__program_activity__program_activity_code
=v)
faba_queryset = faba_queryset.filter(or_queryset)
if faba_flag:
award_ids = faba_queryset.values('award_id')
queryset = queryset.filter(award_id__in=award_ids)
return queryset
def matview_search_filter(filters, model, for_downloads=False):
queryset = model.objects.all()
recipient_scope_q = Q(recipient_location_country_code="USA") | Q(
recipient_location_country_name="UNITED STATES")
pop_scope_q = Q(pop_country_code="USA") | Q(
pop_country_name="UNITED STATES")
faba_flag = False
faba_queryset = FinancialAccountsByAwards.objects.filter(
award__isnull=False)
for key, value in filters.items():
if value is None:
raise InvalidParameterException('Invalid filter: ' + key +
' has null as its value.')
key_list = [
'keywords',
'elasticsearch_keyword',
'time_period',
'award_type_codes',
'agencies',
'legal_entities',
'recipient_id',
'recipient_search_text',
'recipient_scope',
'recipient_locations',
'recipient_type_names',
'place_of_performance_scope',
'place_of_performance_locations',
'award_amounts',
'award_ids',
'program_numbers',
'naics_codes',
'psc_codes',
'contract_pricing_type_codes',
'set_aside_type_codes',
'extent_competed_type_codes',
# next 3 keys used by federal account page
'federal_account_ids',
'object_class',
'program_activity'
]
if key not in key_list:
raise InvalidParameterException('Invalid filter: ' + key +
' does not exist.')
if key == "keywords":
def keyword_parse(keyword):
# keyword_ts_vector & award_ts_vector are Postgres TS_vectors.
# keyword_ts_vector = recipient_name + naics_code + naics_description
# + psc_description + awards_description
# award_ts_vector = piid + fain + uri
filter_obj = Q(keyword_ts_vector=keyword) | \
Q(award_ts_vector=keyword)
if keyword.isnumeric():
filter_obj |= Q(naics_code__contains=keyword)
if len(keyword) == 4 and PSC.objects.all().filter(
code__iexact=keyword).exists():
filter_obj |= Q(product_or_service_code__iexact=keyword)
return filter_obj
filter_obj = Q()
for keyword in value:
filter_obj |= keyword_parse(keyword)
potential_duns = list(
filter((lambda x: len(x) > 7 and len(x) < 10), value))
if len(potential_duns) > 0:
filter_obj |= Q(recipient_unique_id__in=potential_duns) | \
Q(parent_recipient_unique_id__in=potential_duns)
queryset = queryset.filter(filter_obj)
elif key == "elasticsearch_keyword":
keyword = " ".join(value) if isinstance(value, list) else value
transaction_ids = elasticsearch_helper.get_download_ids(
keyword=keyword, field='transaction_id')
# flatten IDs
transaction_ids = list(
itertools.chain.from_iterable(transaction_ids))
logger.info('Found {} transactions based on keyword: {}'.format(
len(transaction_ids), keyword))
transaction_ids = [
str(transaction_id) for transaction_id in transaction_ids
]
if model is UniversalAwardView:
queryset = queryset.filter(
latest_transaction__id__isnull=False)
queryset &= queryset.extra(where=[
'"transaction_normalized"."id" = ANY(\'{{{}}}\'::int[])'.
format(','.join(transaction_ids))
])
elif key == "time_period":
min_date = API_SEARCH_MIN_DATE
if for_downloads:
min_date = API_MIN_DATE
queryset &= combine_date_range_queryset(value, model, min_date,
API_MAX_DATE)
elif key == "award_type_codes":
idv_flag = all(i in value for i in contract_type_mapping.keys())
filter_obj = Q(type__in=value)
if idv_flag:
filter_obj |= Q(pulled_from='IDV') & Q(type__isnull=True)
queryset &= model.objects.filter(filter_obj)
elif key == "agencies":
# TODO: Make function to match agencies in award filter throwing dupe error
funding_toptier = Q()
funding_subtier = Q()
awarding_toptier = Q()
awarding_subtier = Q()
for v in value:
type = v["type"]
tier = v["tier"]
name = v["name"]
if type == "funding":
if tier == "toptier":
funding_toptier |= Q(funding_toptier_agency_name=name)
elif tier == "subtier":
if 'toptier_name' in v:
funding_subtier |= (
Q(funding_subtier_agency_name=name) &
Q(funding_toptier_agency_name=v['toptier_name']
))
else:
funding_subtier |= Q(
funding_subtier_agency_name=name)
elif type == "awarding":
if tier == "toptier":
awarding_toptier |= Q(
awarding_toptier_agency_name=name)
elif tier == "subtier":
if 'toptier_name' in v:
awarding_subtier |= (Q(
awarding_subtier_agency_name=name
) & Q(
awarding_toptier_agency_name=v['toptier_name'])
)
else:
awarding_subtier |= Q(
awarding_subtier_agency_name=name)
awarding_queryfilter = Q()
funding_queryfilter = Q()
# Since these are Q filters, no DB hits for boolean checks
if funding_toptier:
funding_queryfilter |= funding_toptier
if funding_subtier:
funding_queryfilter |= funding_subtier
if awarding_toptier:
awarding_queryfilter |= awarding_toptier
if awarding_subtier:
awarding_queryfilter |= awarding_subtier
queryset = queryset.filter(funding_queryfilter
& awarding_queryfilter)
elif key == "legal_entities":
# This filter key has effectively become obsolete by recipient_search_text
msg = 'API request included "{}" key. No filtering will occur with provided value "{}"'
logger.info(msg.format(key, value))
# in_query = [v for v in value]
# if len(in_query) != 0:
# queryset &= model.objects.filter(recipient_id__in=in_query)
elif key == "recipient_search_text":
all_filters_obj = Q()
for recip in value:
upper_recipient_string = str(recip).upper()
# recipient_name_ts_vector is a postgres TS_Vector
filter_obj = Q(recipient_name_ts_vector=upper_recipient_string)
if len(upper_recipient_string
) == 9 and upper_recipient_string[:5].isnumeric():
filter_obj |= Q(recipient_unique_id=upper_recipient_string)
all_filters_obj |= filter_obj
queryset &= model.objects.filter(all_filters_obj)
elif key == "recipient_id":
filter_obj = Q()
recipient_hash = value[:-2]
if value.endswith(
'P'
): # For parent types, gather all of the children's transactions
parent_duns_rows = (RecipientProfile.objects.filter(
recipient_hash=recipient_hash,
recipient_level='P').values('recipient_unique_id'))
if len(parent_duns_rows) == 1:
parent_duns = parent_duns_rows[0]['recipient_unique_id']
filter_obj = Q(parent_recipient_unique_id=parent_duns)
elif len(parent_duns_rows) > 2:
# shouldn't occur
raise InvalidParameterException(
'Non-unique parent record found in RecipientProfile')
elif value.endswith('C'):
filter_obj = Q(recipient_hash=recipient_hash,
parent_recipient_unique_id__isnull=False)
else:
# "R" recipient level
filter_obj = Q(recipient_hash=recipient_hash,
parent_recipient_unique_id__isnull=True)
queryset &= model.objects.filter(filter_obj)
elif key == "recipient_scope":
if value == "domestic":
queryset = queryset.filter(recipient_scope_q)
elif value == "foreign":
queryset = queryset.exclude(recipient_scope_q)
else:
raise InvalidParameterException(
'Invalid filter: recipient_scope type is invalid.')
elif key == "recipient_locations":
queryset = queryset.filter(
geocode_filter_locations('recipient_location', value, True))
elif key == "recipient_type_names":
if len(value) != 0:
queryset &= model.objects.filter(
business_categories__overlap=value)
elif key == "place_of_performance_scope":
if value == "domestic":
queryset = queryset.filter(pop_scope_q)
elif value == "foreign":
queryset = queryset.exclude(pop_scope_q)
else:
raise InvalidParameterException(
'Invalid filter: place_of_performance_scope is invalid.')
elif key == "place_of_performance_locations":
queryset = queryset.filter(
geocode_filter_locations('pop', value, True))
elif key == "award_amounts":
queryset &= total_obligation_queryset(value, model, filters)
elif key == "award_ids":
filter_obj = Q()
for val in value:
# award_id_string is a Postgres TS_vector
# award_id_string = piid + fain + uri
filter_obj |= Q(award_ts_vector=val)
queryset &= model.objects.filter(filter_obj)
elif key == "program_numbers":
in_query = [v for v in value]
if len(in_query) != 0:
queryset &= model.objects.filter(cfda_number__in=in_query)
elif key == "naics_codes":
in_query = [v for v in value]
if len(in_query) != 0:
queryset &= model.objects.filter(naics_code__in=in_query)
elif key == "psc_codes":
in_query = [v for v in value]
if len(in_query) != 0:
queryset &= model.objects.filter(
product_or_service_code__in=in_query)
elif key == "contract_pricing_type_codes":
in_query = [v for v in value]
if len(in_query) != 0:
queryset &= model.objects.filter(
type_of_contract_pricing__in=in_query)
elif key == "set_aside_type_codes":
or_queryset = Q()
for v in value:
or_queryset |= Q(type_set_aside__exact=v)
queryset = queryset.filter(or_queryset)
elif key == "extent_competed_type_codes":
or_queryset = Q()
for v in value:
or_queryset |= Q(extent_competed__exact=v)
queryset = queryset.filter(or_queryset)
# Federal Account Filter
elif key == "federal_account_ids":
faba_flag = True
or_queryset = Q()
for v in value:
or_queryset |= Q(treasury_account__federal_account_id=v)
faba_queryset = faba_queryset.filter(or_queryset)
# Federal Account Filter
elif key == "object_class":
faba_flag = True
result = Q()
for oc in value:
subresult = Q()
for (key, values) in oc.items():
subresult &= filter_on(
"treasury_account__program_balances__object_class",
key, values)
result |= subresult
faba_queryset = faba_queryset.filter(result)
# Federal Account Filter
elif key == "program_activity":
faba_flag = True
or_queryset = Q()
for v in value:
or_queryset |= Q(
treasury_account__program_balances__program_activity__program_activity_code
=v)
faba_queryset = faba_queryset.filter(or_queryset)
if faba_flag:
award_ids = faba_queryset.values('award_id')
queryset = queryset.filter(award_id__in=award_ids)
return queryset
assistance_type_mapping,
contract_type_mapping,
direct_payment_type_mapping,
grant_type_mapping,
idv_type_mapping,
loan_type_mapping,
other_type_mapping,
procurement_type_mapping,
)
TYPES_TO_QUOTE_IN_SQL = (str, date)
CATEGORY_TO_MODEL = {
"contracts": {
"model": ContractAwardSearchMatview,
"types": set(contract_type_mapping.keys())
},
"direct_payments": {
"model": DirectPaymentAwardSearchMatview,
"types": set(direct_payment_type_mapping.keys())
},
"grants": {
"model": GrantAwardSearchMatview,
"types": set(grant_type_mapping.keys())
},
"idvs": {
"model": IDVAwardSearchMatview,
"types": set(idv_type_mapping.keys())
},
"loans": {
"model": LoanAwardSearchMatview,
