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)),
)
class SearchInvestmentExportAPIView(SearchInvestmentProjectAPIViewMixin,
SearchExportAPIView):
"""Investment project search export view."""
# Note: Aggregations on related fields are only used via subqueries as they become very
# expensive in the main query
queryset = DBInvestmentProject.objects.annotate(
computed_project_code=get_project_code_expression(),
status_name=get_choices_as_case_expression(DBInvestmentProject,
'status'),
link=get_front_end_url_expression('investmentproject', 'pk'),
date_of_latest_interaction=get_aggregate_subquery(
DBInvestmentProject,
Max('interactions__date'),
),
sector_name=get_sector_name_subquery('sector'),
team_member_names=get_string_agg_subquery(
DBInvestmentProject,
get_full_name_expression('team_members__adviser'),
ordering=('team_members__adviser__first_name',
'team_members__adviser__last_name'),
),
delivery_partner_names=get_string_agg_subquery(
DBInvestmentProject,
Cast('delivery_partners__name', CharField()),
),
uk_region_location_names=get_string_agg_subquery(
DBInvestmentProject,
Cast('uk_region_locations__name', CharField()),
),
actual_uk_region_names=get_string_agg_subquery(
DBInvestmentProject,
Cast('actual_uk_regions__name', CharField()),
),
investor_company_global_account_manager=Case(
When(
investor_company__global_headquarters__isnull=False,
then=get_full_name_expression(
'investor_company__global_headquarters__one_list_account_owner',
),
),
default=get_full_name_expression(
'investor_company__one_list_account_owner'),
),
client_relationship_manager_name=get_full_name_expression(
'client_relationship_manager'),
project_manager_name=get_full_name_expression('project_manager'),
project_assurance_adviser_name=get_full_name_expression(
'project_assurance_adviser'),
)
@property
def field_titles(self):
"""
Returns field titles for CSV export
There is implicit ordering here, guaranteed for python >= 3.7 to be insertion order
This is a property because we don't want it to evaluate prior to database instantiation
"""
field_titles = {
'created_on': 'Date created',
'computed_project_code': 'Project reference',
'name': 'Project name',
'investor_company__name': 'Investor company',
'investor_company__address_town': 'Investor company town or city',
}
field_titles.update({
'investor_company__address_area__name': 'Investor company area',
'country_investment_originates_from__name': 'Country of origin',
'investment_type__name': 'Investment type',
'status_name': 'Status',
'stage__name': 'Stage',
'link': 'Link',
'actual_land_date': 'Actual land date',
'estimated_land_date': 'Estimated land date',
'fdi_value__name': 'FDI value',
'sector_name': 'Sector',
'date_of_latest_interaction': 'Date of latest interaction',
'project_manager_name': 'Project manager',
'client_relationship_manager_name': 'Client relationship manager',
'investor_company_global_account_manager':
'Global account manager',
'project_assurance_adviser_name': 'Project assurance adviser',
'team_member_names': 'Other team members',
'delivery_partner_names': 'Delivery partners',
'uk_region_location_names': 'Possible UK regions',
'actual_uk_region_names': 'Actual UK regions',
'specific_programme__name': 'Specific investment programme',
'referral_source_activity__name': 'Referral source activity',
'referral_source_activity_website__name':
'Referral source activity website',
'total_investment': 'Total investment',
'number_new_jobs': 'New jobs',
'average_salary__name': 'Average salary of new jobs',
'number_safeguarded_jobs': 'Safeguarded jobs',
'level_of_involvement__name': 'Level of involvement',
'r_and_d_budget': 'R&D budget',
'non_fdi_r_and_d_budget': 'Associated non-FDI R&D project',
'new_tech_to_uk': 'New to world tech',
'likelihood_to_land__name': 'Likelihood to land',
'fdi_type__name': 'FDI type',
'foreign_equity_investment': 'Foreign equity investment',
'gva_multiplier__multiplier': 'GVA multiplier',
'gross_value_added': 'GVA',
})
return field_titles