예제 #1
0
    def database_data_layer(self) -> tuple:
        if self.subawards:
            queryset = subaward_filter(self.filters)
            obligation_column = "amount"
        else:
            queryset = spending_over_time(self.filters)
            obligation_column = "generated_pragmatic_obligation"

        values = ["fy"]
        if self.group == "month":
            queryset = queryset.annotate(month=FiscalMonth("action_date"),
                                         fy=FiscalYear("action_date"))
            values.append("month")

        elif self.group == "quarter":
            queryset = queryset.annotate(quarter=FiscalQuarter("action_date"),
                                         fy=FiscalYear("action_date"))
            values.append("quarter")

        elif self.group == "fiscal_year":
            queryset = queryset.annotate(fy=FiscalYear("action_date"))

        queryset = (queryset.values(*values).annotate(
            aggregated_amount=Sum(obligation_column)).order_by(
                *["{}".format(value) for value in values]))

        return queryset, values
예제 #2
0
def subaward_annotations():
    annotation_fields = {
        "subaward_action_date_fiscal_year":
        FiscalYear("subaward__action_date"),
        "prime_award_base_action_date_fiscal_year":
        FiscalYear("award__date_signed"),
        "period_of_performance_potential_end_date":
        Cast(
            F("award__latest_transaction__contract_data__period_of_perf_potential_e"
              ), DateField()),
        "prime_award_treasury_accounts_funding_this_award":
        Subquery(
            Award.objects.filter(id=OuterRef("award_id")).annotate(
                value=StringAgg(
                    "financial_set__treasury_account__tas_rendering_label",
                    ";",
                    distinct=True)).values("value"),
            output_field=TextField(),
        ),
        "prime_award_federal_accounts_funding_this_award":
        Subquery(
            Award.objects.filter(id=OuterRef("award_id")).
            annotate(value=StringAgg(
                "financial_set__treasury_account__federal_account__federal_account_code",
                ";",
                distinct=True)).values("value"),
            output_field=TextField(),
        ),
        "usaspending_permalink":
        Concat(
            Value(AWARD_URL),
            Func(F("award__generated_unique_award_id"), function="urlencode"),
            Value("/")),
    }
    return annotation_fields
예제 #3
0
def idv_order_annotations():
    annotation_fields = {
        "award_base_action_date_fiscal_year":
        FiscalYear("date_signed"),
        "treasury_accounts_funding_this_award":
        Subquery(
            Award.objects.filter(id=OuterRef("id")).annotate(value=StringAgg(
                "financial_set__treasury_account__tas_rendering_label",
                ";",
                distinct=True)).values("value"),
            output_field=TextField(),
        ),
        "federal_accounts_funding_this_award":
        Subquery(
            Award.objects.filter(id=OuterRef("id")).annotate(value=StringAgg(
                "financial_set__treasury_account__federal_account__federal_account_code",
                ";",
                distinct=True)).values("value"),
            output_field=TextField(),
        ),
        "usaspending_permalink":
        Concat(Value(AWARD_URL),
               Func(F("generated_unique_award_id"), function="urlencode"),
               Value("/")),
    }
    return annotation_fields
예제 #4
0
    def database_data_layer(self):
        recipient_hash = self.filters['recipient_id'][:-2]
        time_ranges = []
        for t in self.filters['time_period']:
            t['date_type'] = 'action_date'
            time_ranges.append(t)
        queryset = SummaryAwardRecipient.objects.filter()
        queryset &= combine_date_range_queryset(time_ranges,
                                                SummaryAwardRecipient,
                                                API_SEARCH_MIN_DATE,
                                                API_MAX_DATE)

        if self.filters['recipient_id'][-1] == 'P':
            # there *should* only one record with that hash and recipient_level = 'P'
            parent_duns_rows = RecipientProfile.objects.filter(
                recipient_hash=recipient_hash,
                recipient_level='P').values('recipient_unique_id')
            if len(parent_duns_rows) != 1:
                raise InvalidParameterException(
                    'Provided recipient_id has no parent records')
            parent_duns = parent_duns_rows[0]['recipient_unique_id']
            queryset = queryset.filter(parent_recipient_unique_id=parent_duns)
        elif self.filters['recipient_id'][-1] == 'R':
            queryset = queryset.filter(recipient_hash=recipient_hash,
                                       parent_recipient_unique_id__isnull=True)
        else:
            queryset = queryset.filter(
                recipient_hash=recipient_hash,
                parent_recipient_unique_id__isnull=False)

        values = ['fy']
        if self.groupings[self.json_request['group']] == 'month':
            queryset = queryset.annotate(month=FiscalMonth('action_date'),
                                         fy=FiscalYear('action_date'))
            values.append('month')

        elif self.groupings[self.json_request['group']] == 'quarter':
            queryset = queryset.annotate(quarter=FiscalQuarter('action_date'),
                                         fy=FiscalYear('action_date'))
            values.append('quarter')

        elif self.groupings[self.json_request['group']] == 'fiscal_year':
            queryset = queryset.annotate(fy=FiscalYear('action_date'))

        queryset = queryset.values(*values).annotate(count=Count('award_id'))
        return queryset, values
예제 #5
0
    def database_data_layer(self):
        recipient_hash = self.filters["recipient_id"][:-2]
        time_ranges = []
        for t in self.filters["time_period"]:
            t["date_type"] = "action_date"
            time_ranges.append(t)
        queryset = SummaryAwardRecipient.objects.filter()
        queryset &= combine_date_range_queryset(time_ranges,
                                                SummaryAwardRecipient,
                                                API_SEARCH_MIN_DATE,
                                                API_MAX_DATE)

        if self.filters["recipient_id"][-1] == "P":
            # there *should* only one record with that hash and recipient_level = 'P'
            parent_duns_rows = RecipientProfile.objects.filter(
                recipient_hash=recipient_hash,
                recipient_level="P").values("recipient_unique_id")
            if len(parent_duns_rows) != 1:
                raise InvalidParameterException(
                    "Provided recipient_id has no parent records")
            parent_duns = parent_duns_rows[0]["recipient_unique_id"]
            queryset = queryset.filter(parent_recipient_unique_id=parent_duns)
        elif self.filters["recipient_id"][-1] == "R":
            queryset = queryset.filter(recipient_hash=recipient_hash,
                                       parent_recipient_unique_id__isnull=True)
        else:
            queryset = queryset.filter(
                recipient_hash=recipient_hash,
                parent_recipient_unique_id__isnull=False)

        values = ["fy"]
        if self.groupings[self.json_request["group"]] == "month":
            queryset = queryset.annotate(month=FiscalMonth("action_date"),
                                         fy=FiscalYear("action_date"))
            values.append("month")

        elif self.groupings[self.json_request["group"]] == "quarter":
            queryset = queryset.annotate(quarter=FiscalQuarter("action_date"),
                                         fy=FiscalYear("action_date"))
            values.append("quarter")

        elif self.groupings[self.json_request["group"]] == "fiscal_year":
            queryset = queryset.annotate(fy=FiscalYear("action_date"))

        queryset = queryset.values(*values).annotate(count=Count("award_id"))
        return queryset, values
def subaward_annotations():
    annotation_fields = {
        "subaward_action_date_fiscal_year":
        FiscalYear("subaward__action_date"),
        "prime_award_action_date_fiscal_year":
        FiscalYear("award__date_signed"),
        "prime_award_federal_accounts_funding_this_award":
        StringAgg(
            "award__financial_set__treasury_account__federal_account__federal_account_code",
            ";",
            distinct=True),
        "prime_award_treasury_accounts_funding_this_award":
        StringAgg(
            "award__financial_set__treasury_account__tas_rendering_label",
            ";",
            distinct=True),
    }
    return annotation_fields
예제 #7
0
def subaward_annotations():
    annotation_fields = {
        "subaward_action_date_fiscal_year":
        FiscalYear("subaward__action_date"),
        "prime_award_base_action_date_fiscal_year":
        FiscalYear("award__date_signed"),
        "prime_award_federal_accounts_funding_this_award":
        StringAgg(
            "award__financial_set__treasury_account__federal_account__federal_account_code",
            ";",
            distinct=True),
        "prime_award_treasury_accounts_funding_this_award":
        StringAgg(
            "award__financial_set__treasury_account__tas_rendering_label",
            ";",
            distinct=True),
        "usaspending_permalink":
        Concat(
            Value(AWARD_URL),
            Func(F("award__generated_unique_award_id"), function="urlencode"),
            Value("/")),
    }
    return annotation_fields
def idv_transaction_annotations():
    annotation_fields = {
        "action_date_fiscal_year":
        FiscalYear("action_date"),
        "treasury_accounts_funding_this_award":
        StringAgg(
            "award__financial_set__treasury_account__tas_rendering_label",
            ";",
            distinct=True),
        "federal_accounts_funding_this_award":
        StringAgg(
            "award__financial_set__treasury_account__federal_account__federal_account_code",
            ";",
            distinct=True),
    }
    return annotation_fields
예제 #9
0
def award_financial_derivations(derived_fields):
    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["award_base_action_date_fiscal_year"] = FiscalYear("award__date_signed")

    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 subaward_annotations():
    annotation_fields = {
        "subaward_action_date_fiscal_year": FiscalYear("subaward__action_date"),
        "prime_award_base_action_date_fiscal_year": FiscalYear("award__date_signed"),
        "prime_award_period_of_performance_potential_end_date": Cast(
            F("award__latest_transaction__contract_data__period_of_perf_potential_e"), DateField()
        ),
        "prime_award_treasury_accounts_funding_this_award": Subquery(
            Award.objects.filter(id=OuterRef("award_id"))
            .annotate(value=StringAgg("financial_set__treasury_account__tas_rendering_label", ";", distinct=True))
            .values("value"),
            output_field=TextField(),
        ),
        "prime_award_federal_accounts_funding_this_award": Subquery(
            Award.objects.filter(id=OuterRef("award_id"))
            .annotate(
                value=StringAgg(
                    "financial_set__treasury_account__federal_account__federal_account_code", ";", distinct=True
                )
            )
            .values("value"),
            output_field=TextField(),
        ),
        "usaspending_permalink": Concat(
            Value(AWARD_URL), Func(F("award__generated_unique_award_id"), function="urlencode"), Value("/")
        ),
        "prime_award_object_classes_funding_this_award": Subquery(
            FinancialAccountsByAwards.objects.filter(
                filter_limit_to_closed_periods(), award_id=OuterRef("award_id"), object_class_id__isnull=False
            )
            .annotate(
                value=ExpressionWrapper(
                    Concat(F("object_class__object_class"), Value(": "), F("object_class__object_class_name")),
                    output_field=TextField(),
                )
            )
            .values("award_id")
            .annotate(total=StringAgg("value", ";", distinct=True))
            .values("total"),
            output_field=TextField(),
        ),
        "prime_award_program_activities_funding_this_award": Subquery(
            FinancialAccountsByAwards.objects.filter(
                filter_limit_to_closed_periods(), award_id=OuterRef("award_id"), program_activity_id__isnull=False
            )
            .annotate(
                value=ExpressionWrapper(
                    Concat(
                        F("program_activity__program_activity_code"),
                        Value(": "),
                        F("program_activity__program_activity_name"),
                    ),
                    output_field=TextField(),
                )
            )
            .values("award_id")
            .annotate(total=StringAgg("value", ";", distinct=True))
            .values("total"),
            output_field=TextField(),
        ),
        "prime_award_disaster_emergency_fund_codes": Case(
            When(
                broker_subaward__action_date__gte=datetime.date(2020, 4, 1),
                then=Subquery(
                    FinancialAccountsByAwards.objects.filter(
                        filter_limit_to_closed_periods(), award_id=OuterRef("award_id")
                    )
                    .annotate(
                        value=ExpressionWrapper(
                            Case(
                                When(
                                    disaster_emergency_fund__code__isnull=False,
                                    then=Concat(
                                        F("disaster_emergency_fund__code"),
                                        Value(": "),
                                        F("disaster_emergency_fund__public_law"),
                                    ),
                                ),
                                default=Value(None, output_field=TextField()),
                                output_field=TextField(),
                            ),
                            output_field=TextField(),
                        )
                    )
                    .values("award_id")
                    .annotate(total=StringAgg("value", ";", distinct=True))
                    .values("total"),
                    output_field=TextField(),
                ),
            )
        ),
        "prime_award_outlayed_amount_funded_by_COVID-19_supplementals": Case(
            When(
                broker_subaward__action_date__gte=datetime.date(2020, 4, 1),
                then=Subquery(
                    FinancialAccountsByAwards.objects.filter(
                        filter_by_latest_closed_periods(),
                        award_id=OuterRef("award_id"),
                        disaster_emergency_fund__group_name="covid_19",
                        submission__reporting_period_start__gte=str(datetime.date(2020, 4, 1)),
                    )
                    .values("award_id")
                    .annotate(sum=Sum("gross_outlay_amount_by_award_cpe"))
                    .values("sum"),
                    output_field=DecimalField(),
                ),
            ),
        ),
        "prime_award_obligated_amount_funded_by_COVID-19_supplementals": Case(
            When(
                broker_subaward__action_date__gte=datetime.date(2020, 4, 1),
                then=Subquery(
                    FinancialAccountsByAwards.objects.filter(
                        filter_limit_to_closed_periods(),
                        award_id=OuterRef("award_id"),
                        disaster_emergency_fund__group_name="covid_19",
                        submission__reporting_period_start__gte=str(datetime.date(2020, 4, 1)),
                    )
                    .values("award_id")
                    .annotate(sum=Sum("transaction_obligated_amount"))
                    .values("sum"),
                    output_field=DecimalField(),
                ),
            ),
        ),
        "prime_award_latest_action_date_fiscal_year": FiscalYear("award__latest_transaction__action_date"),
    }
    return annotation_fields
def transaction_search_annotations():
    annotation_fields = {
        "action_date_fiscal_year":
        FiscalYear("action_date"),
        "treasury_accounts_funding_this_award":
        Subquery(
            Award.objects.filter(id=OuterRef("award_id")).annotate(
                value=StringAgg(
                    "financial_set__treasury_account__tas_rendering_label",
                    ";",
                    distinct=True)).values("value"),
            output_field=TextField(),
        ),
        "federal_accounts_funding_this_award":
        Subquery(
            Award.objects.filter(id=OuterRef("award_id")).
            annotate(value=StringAgg(
                "financial_set__treasury_account__federal_account__federal_account_code",
                ";",
                distinct=True)).values("value"),
            output_field=TextField(),
        ),
        "usaspending_permalink":
        ConcatAll(
            Value(AWARD_URL),
            Func(F("transaction__award__generated_unique_award_id"),
                 function="urlencode"), Value("/")),
        "disaster_emergency_fund_codes_for_overall_award":
        Case(
            When(
                transaction__action_date__gte=datetime.date(2020, 4, 1),
                then=Subquery(
                    FinancialAccountsByAwards.objects.filter(
                        filter_limit_to_closed_periods(),
                        award_id=OuterRef("award_id")).
                    annotate(value=ExpressionWrapper(
                        Case(
                            When(
                                disaster_emergency_fund__code__isnull=False,
                                then=ConcatAll(
                                    F("disaster_emergency_fund__code"),
                                    Value(": "),
                                    F("disaster_emergency_fund__public_law"),
                                ),
                            ),
                            default=Value(None, output_field=TextField()),
                            output_field=TextField(),
                        ),
                        output_field=TextField(),
                    )).values("award_id").annotate(total=StringAgg(
                        "value", ";", distinct=True)).values("total"),
                    output_field=TextField(),
                ),
            )),
        "outlayed_amount_funded_by_COVID-19_supplementals_for_overall_award":
        Case(
            When(
                transaction__action_date__gte=datetime.date(2020, 4, 1),
                then=_covid_outlay_subquery(),
            ), ),
        "obligated_amount_funded_by_COVID-19_supplementals_for_overall_award":
        Case(
            When(
                transaction__action_date__gte=datetime.date(2020, 4, 1),
                then=_covid_obligation_subquery(),
            ), ),
        "object_classes_funding_this_award":
        Subquery(
            FinancialAccountsByAwards.objects.filter(
                filter_limit_to_closed_periods(),
                award_id=OuterRef("award_id"),
                object_class_id__isnull=False).annotate(
                    value=ExpressionWrapper(
                        ConcatAll(F("object_class__object_class"), Value(": "),
                                  F("object_class__object_class_name")),
                        output_field=TextField(),
                    )).values("award_id").annotate(total=StringAgg(
                        "value", ";", distinct=True)).values("total"),
            output_field=TextField(),
        ),
        "program_activities_funding_this_award":
        Subquery(
            FinancialAccountsByAwards.objects.filter(
                filter_limit_to_closed_periods(),
                award_id=OuterRef("award_id"),
                program_activity_id__isnull=False).annotate(
                    value=ExpressionWrapper(
                        ConcatAll(
                            F("program_activity__program_activity_code"),
                            Value(": "),
                            F("program_activity__program_activity_name"),
                        ),
                        output_field=TextField(),
                    )).values("award_id").annotate(total=StringAgg(
                        "value", ";", distinct=True)).values("total"),
            output_field=TextField(),
        ),
    }
    return annotation_fields