from dcapi.schema import Schema
from dcdata.contracts.models import Contract
from dcdata.utils.sql import parse_date
CONTRACTS_SCHEMA = Schema(
InclusionField('agency_id', 'agencyid'),
InclusionField('contracting_agency_id', 'contractingofficeagencyid'),
InclusionField('fiscal_year'),
InclusionField('place_district', 'congressionaldistrict'),
InclusionField('place_state', 'statecode'),
InclusionField('requesting_agency_id', 'fundingrequestingagencyid'),
InclusionField('vendor_state', 'state'),
InclusionField('vendor_zipcode', 'zipcode'),
InclusionField('vendor_district', 'vendor_cd'),
InclusionField('vendor_duns', 'dunsnumber'),
InclusionField('vendor_parent_duns', 'eeparentduns'),
# agency names have no data. see ticket #835
#FulltextField('agency_name', ['agency_name', 'contracting_agency_name', 'requesting_agency_name']),
FulltextField('vendor_name', ['vendorname']),
FulltextField('vendor_city', ['city']),
ComparisonField('obligated_amount', 'obligatedamount', cast=int),
ComparisonField('current_amount', 'baseandexercisedoptionsvalue',
cast=int),
ComparisonField('maximum_amount', 'baseandalloptionsvalue', cast=int),
ComparisonField('signed_date', 'signeddate', cast=parse_date),
)
def filter_contracts(request):
return CONTRACTS_SCHEMA.build_filter(Contract.objects, request).order_by()
from dcapi.schema import Schema, FunctionField
from dcdata.faca.models import FACARecord
def _year_generator(q, *years):
or_query = Q()
for year in years:
or_query.add(
Q(start_date__lte="%s-12-31" % year,
end_date__gte="%s-01-01" % year), Q.OR)
return q.filter(or_query)
FACA_SCHEMA = Schema(
FulltextField('agency_name', ['agency_abbr', 'agency_name']),
FulltextField('committee_name'), FulltextField('member_name'),
FulltextField('affiliation'), FunctionField('year', _year_generator))
def filter_faca(request):
return FACA_SCHEMA.build_filter(FACARecord.objects, request)
class FACAFilterHandler(FilterHandler):
ordering = ['committee_name']
filename = 'faca'
model = FACARecord
def queryset(self, params):
return filter_faca(self._unquote(params))
from dcapi.common.handlers import DenormalizingFilterHandler
from dcapi.common.schema import InclusionField, ComparisonField, FulltextField
from dcapi.schema import Schema
from dcdata.earmarks.models import Earmark
EARMARKS_SCHEMA = Schema(
InclusionField('year', 'fiscal_year'),
InclusionField('state', 'locations__state'),
InclusionField('member_party', 'members__party'),
InclusionField('member_state', 'members__state'),
FulltextField('bill', ['bill', 'bill_section', 'bill_subsection']),
FulltextField('description', ['earmarks_earmark.description', 'notes']),
FulltextField('city', ['earmarks_location.city']),
FulltextField(
'member',
['earmarks_member.standardized_name', 'earmarks_member.raw_name']),
FulltextField('recipient', [
'earmarks_recipient.standardized_recipient',
'earmarks_recipient.raw_recipient'
]),
ComparisonField('amount', 'final_amount'),
)
def filter_earmarks(request):
qs = EARMARKS_SCHEMA.build_filter(Earmark.objects, request)
# filters do nothing--just here to force the join that's needed for the fulltext search
if 'city' in request:
qs = qs.filter(locations__city__isnull=False)
if 'member' in request:
from dcapi.common.handlers import FilterHandler
from dcapi.common.schema import FulltextField
from dcapi.schema import Schema
from dcdata.contribution.models import LobbyistBundlingDenormalized
BUNDLING_SCHEMA = Schema(
FulltextField('recipient_name',
['committee_name', 'standardized_recipient_name']),
FulltextField('lobbyist_name', [
'standardized_lobbyist_name', 'standardized_firm_name', 'bundler_name',
'bundler_employer'
]),
)
def filter_bundling(request):
return BUNDLING_SCHEMA.build_filter(LobbyistBundlingDenormalized.objects,
request)
class BundlingFilterHandler(FilterHandler):
fields = [f.name for f in LobbyistBundlingDenormalized._meta.fields]
model = LobbyistBundlingDenormalized
ordering = ['committee_name', 'bundler_name', '-report_year']
filename = 'lobbyist_bundled_contributions'
def queryset(self, params):
return filter_bundling(self._unquote(params))
from dcapi.common.handlers import FilterHandler
from dcapi.common.schema import InclusionField, FulltextField, ComparisonField
from dcapi.schema import Schema
from dcdata.pogo.models import Misconduct
CONTRACTOR_MISCONDUCT_SCHEMA = Schema(
InclusionField('date_year'),
ComparisonField('penalty_amount', 'penalty_amount', cast=int),
FulltextField('contractor', ['pogo_contractor.name']),
FulltextField('enforcement_agency'),
FulltextField('instance'),
FulltextField('contracting_party'),
)
def filter_contractor_misconduct(request):
q = CONTRACTOR_MISCONDUCT_SCHEMA.build_filter(Misconduct.objects,
request).order_by()
# filter does nothing--it's here to force the join
if 'contractor' in request:
q = q.filter(contractor__name__isnull=False)
return q.distinct().select_related()
class ContractorMisconductFilterHandler(FilterHandler):
model = Misconduct
ordering = ['-penalty_amount', '-date']
filename = 'contractor_misconduct'
def _recipient_state_in_generator(query, *states):
return query.filter(
recipient_state__in=[state.upper() for state in states])
CONTRIBUTION_SCHEMA = Schema(
FulltextField('committee_ft', ['committee_name']),
FulltextField('contributor_ft', ['contributor_name']),
FulltextField('recipient_ft', ['recipient_name']),
FulltextField('office_ft', ['seat']),
FulltextField('payment_type_ft', ['payment_type']),
FunctionField('contributor_state', _contributor_state_in_generator),
InclusionField('contributor_type'),
InclusionField('contributor_zipcode'),
InclusionField('contributor_zipcode'),
ComparisonField('amount', cast=int),
ComparisonField('date', cast=parse_date),
#FunctionField('cycle', _cycle_in_generator),
#InclusionField('seat'),
#InclusionField('transaction_namespace'),
#InclusionField('committee_ext_id'),
)
def filter_contributions(request):
query = CONTRIBUTION_SCHEMA.build_filter(ContributionDC.objects,
request).order_by()
return query
def _lobbyist_is_rep_generator(query, value):
return query.filter(lobbyists__member_of_congress=True)
LOBBYING_SCHEMA = Schema(
FunctionField('lobbyist_is_rep', _lobbyist_is_rep_generator),
IndustryField('industry', 'client_category'),
InclusionField('transaction_id'),
InclusionField('transaction_type'),
InclusionField('filing_type'),
InclusionField('year'),
InclusionField('issue', 'issues__general_issue_code'),
InclusionField('lobbyist_ext_id', 'lobbyists__lobbyist_ext_id'),
InclusionField('candidate_ext_id', 'lobbyists__candidate_ext_id'),
FulltextField('client_ft', ['client_name']),
FulltextField('client_parent_ft', ['client_parent_name']),
FulltextField('lobbyist_ft', ['lobbying_lobbyist.lobbyist_name']),
FulltextField('registrant_ft', ['registrant_name']),
FulltextField('issue_ft', ['lobbying_issue.specific_issue']),
ComparisonField('amount', cast=int),
)
def filter_lobbying(request):
q = LOBBYING_SCHEMA.build_filter(Lobbying.objects, request).order_by()
# filter does nothing--it's here to force the join on lobbyists
from dcapi.common.handlers import FilterHandler
from dcapi.common.schema import InclusionField, FulltextField, ComparisonField
from dcapi.schema import Schema
from dcdata.grants.models import Grant
GRANTS_SCHEMA = Schema(
InclusionField('assistance_type'), InclusionField('fiscal_year'),
InclusionField('recipient_state', 'recipient_state_code'),
InclusionField('recipient_type'),
FulltextField('agency_ft', ['agency_name']),
FulltextField('recipient_ft', ['recipient_name']),
ComparisonField('amount_total', 'total_funding_amount', cast=int))
def filter_grants(request):
q = GRANTS_SCHEMA.build_filter(Grant.objects, request).order_by()
return q.select_related()
class GrantsFilterHandler(FilterHandler):
# imported_on is marked as an auto-generated field/non-editable,
# so was getting dropped by Django's model_to_dict serialization,
# but still required by the list of fields,
# so we pass the list of fields we want directly instead
fields = Grant._meta.get_all_field_names()
fields.remove('imported_on')
ordering = ['-fiscal_year', '-total_funding_amount']
filename = 'grants'
#
def _terminated_generator(query, terminated_active):
return query.filter(terminated=terminated_active.title())
CLIENT_REGISTRANT_SCHEMA = Schema(
FunctionField('terminated', _terminated_generator),
InclusionField('registrant_id'),
InclusionField('client_id'),
InclusionField('location_id'),
FulltextField('client_ft', [
'client',
]),
FulltextField('registrant_name_ft', [
'registrant_name',
]),
FulltextField('location_of_client_ft', [
'location_of_client',
]),
FulltextField('description_of_service_ft', [
'description_of_service',
]),
)
def filter_client_registrant(request):
return CLIENT_REGISTRANT_SCHEMA.build_filter(ClientRegistrant.objects,
request).order_by()
from dcapi.common.handlers import FilterHandler
from dcapi.common.schema import InclusionField, FulltextField, ComparisonField
from dcapi.schema import Schema
from dcdata.epa import models
from dcdata.epa.models import DenormalizedAction
EPA_SCHEMA = Schema(
InclusionField('case_num'),
FulltextField('case_name'),
FulltextField('defendants', ['defendants']),
FulltextField('location_addresses', ['location_addresses']),
ComparisonField('penalty'),
ComparisonField('first_date'),
ComparisonField('last_date'),
)
def filter_epa(request):
qs = EPA_SCHEMA.build_filter(models.DenormalizedAction.objects, request)
# filters do nothing--just here to force the join that's needed for the fulltext search
# if 'city' in request:
# qs = qs.filter(locations__city__isnull=False)
return qs.order_by().distinct().select_related()
class EPAFilterHandler(FilterHandler):
# todo: ordering should change
ordering = ['-penalty']
filename = 'epa'