def test_get_empty_string_if_null_expression(value, expected):
"""Tests if None can be replaced with an empty string."""
PersonFactory()
query = Person.objects.annotate(
possibly_null_value=Value(value, output_field=CharField(null=True)),
some_property=get_empty_string_if_null_expression('possibly_null_value'),
).values('some_property')
person = query.first()
assert person['some_property'] == expected
def get_dataset(self):
"""Returns list of Investment Projects Dataset records"""
return InvestmentProject.objects.annotate(
actual_uk_region_names=get_array_agg_subquery(
InvestmentProject.actual_uk_regions.through,
'investmentproject',
'ukregion__name',
ordering=('ukregion__name', ),
),
business_activity_names=get_array_agg_subquery(
InvestmentProject.business_activities.through,
'investmentproject',
'investmentbusinessactivity__name',
ordering=('investmentbusinessactivity__name', ),
),
competing_countries=get_aggregate_subquery(
InvestmentProject,
ArrayAgg('competitor_countries__name',
ordering=('competitor_countries__name', )),
),
delivery_partner_names=get_array_agg_subquery(
InvestmentProject.delivery_partners.through,
'investmentproject',
'investmentdeliverypartner__name',
ordering=('investmentdeliverypartner__name', ),
),
investor_company_sector=get_sector_name_subquery(
'investor_company__sector'),
level_of_involvement_name=get_empty_string_if_null_expression(
'level_of_involvement__name', ),
project_reference=get_project_code_expression(),
strategic_driver_names=get_array_agg_subquery(
InvestmentProject.strategic_drivers.through,
'investmentproject',
'investmentstrategicdriver__name',
ordering=('investmentstrategicdriver__name', ),
),
sector_name=get_sector_name_subquery('sector'),
team_member_ids=get_aggregate_subquery(
InvestmentProject,
ArrayAgg('team_members__adviser_id',
ordering=('team_members__id', )),
),
uk_company_sector=get_sector_name_subquery('uk_company__sector'),
uk_region_location_names=get_array_agg_subquery(
InvestmentProject.uk_region_locations.through,
'investmentproject',
'ukregion__name',
ordering=('ukregion__name', ),
),
).values(
'actual_land_date',
'actual_uk_region_names',
'address_1',
'address_2',
'address_town',
'address_postcode',
'anonymous_description',
'associated_non_fdi_r_and_d_project_id',
'average_salary__name',
'business_activity_names',
'client_relationship_manager_id',
'client_requirements',
'competing_countries',
'created_by_id',
'created_on',
'delivery_partner_names',
'description',
'estimated_land_date',
'export_revenue',
'fdi_type__name',
'fdi_value__name',
'foreign_equity_investment',
'government_assistance',
'gross_value_added',
'gva_multiplier__multiplier',
'id',
'investment_type__name',
'investor_company_id',
'investor_company_sector',
'investor_type__name',
'level_of_involvement_name',
'likelihood_to_land__name',
'modified_by_id',
'modified_on',
'name',
'new_tech_to_uk',
'non_fdi_r_and_d_budget',
'number_new_jobs',
'number_safeguarded_jobs',
'other_business_activity',
'project_arrived_in_triage_on',
'project_assurance_adviser_id',
'project_manager_id',
'project_reference',
'proposal_deadline',
'r_and_d_budget',
'referral_source_activity__name',
'referral_source_activity_marketing__name',
'referral_source_activity_website__name',
'sector_name',
'specific_programme__name',
'stage__name',
'status',
'strategic_driver_names',
'team_member_ids',
'total_investment',
'uk_company_id',
'uk_company_sector',
'uk_region_location_names',
)
def get_source_query(self):
"""Get the query set."""
return InvestmentProject.objects.annotate(
# this contains helper annotations
_possible_uk_region_names=get_string_agg_subquery(
InvestmentProject,
'uk_region_locations__name',
),
_actual_uk_region_names=get_string_agg_subquery(
InvestmentProject,
'actual_uk_regions__name',
),
).annotate(
project_reference=get_project_code_expression(),
status_name=get_choices_as_case_expression(InvestmentProject,
'status'),
status_collapsed=get_collapse_status_name_expression(),
project_url=get_front_end_url_expression('investmentproject',
'pk'),
sector_name=Coalesce(
get_sector_name_subquery('sector'),
Value(NO_SECTOR_ASSIGNED),
),
top_level_sector_name=get_top_level_sector_expression(),
possible_uk_region_names=Coalesce(
'_possible_uk_region_names',
Value(NO_UK_REGION_ASSIGNED),
),
actual_uk_region_names=Coalesce(
'_actual_uk_region_names',
Value(NO_UK_REGION_ASSIGNED),
),
project_fdi_value=Coalesce('fdi_value__name',
Value(NO_FDI_VALUE_ASSIGNED)),
sector_cluster=get_sector_cluster_expression('sector'),
uk_region_name=get_other_field_if_null_or_empty_expression(
'_actual_uk_region_names',
'_possible_uk_region_names',
default=Value(NO_UK_REGION_ASSIGNED),
),
land_date=Coalesce(
'actual_land_date',
'estimated_land_date',
),
financial_year=get_financial_year_from_land_date_expression(),
dh_fdi_project_id=F('id'),
investment_type_name=get_empty_string_if_null_expression(
'investment_type__name'),
level_of_involvement_name=get_empty_string_if_null_expression(
'level_of_involvement__name', ),
simplified_level_of_involvement=
get_level_of_involvement_simplified_expression(),
overseas_region=get_empty_string_if_null_expression(
'investor_company__address_country__overseas_region__name', ),
country_url=get_country_url(),
investor_company_country=get_empty_string_if_null_expression(
'investor_company__address_country__name', ),
stage_name=F('stage__name'),
total_investment_with_zero=Coalesce('total_investment', Value(0)),
number_safeguarded_jobs_with_zero=Coalesce(
'number_safeguarded_jobs', Value(0)),
number_new_jobs_with_zero=Coalesce('number_new_jobs', Value(0)),
)