class SearchInteractionExportAPIView(SearchInteractionParams,
SearchExportAPIView):
"""Filtered interaction search export view."""
queryset = DBInteraction.objects.annotate(
company_link=get_front_end_url_expression('company', 'company__pk'),
company_sector_name=get_sector_name_subquery('company__sector'),
contact_name=get_full_name_expression('contact'),
dit_adviser_name=get_full_name_expression('dit_adviser'),
link=get_front_end_url_expression('interaction', 'pk'),
kind_name=get_choices_as_case_expression(DBInteraction, 'kind'),
)
field_titles = {
'date': 'Date',
'kind_name': 'Type',
'service__name': 'Service',
'subject': 'Subject',
'link': 'Link',
'company__name': 'Company',
'company_link': 'Company link',
'company__registered_address_country__name': 'Company country',
'company__uk_region__name': 'Company UK region',
'company_sector_name': 'Company sector',
'contact_name': 'Contact',
'contact__job_title': 'Contact job title',
'dit_adviser_name': 'Adviser',
'dit_team__name': 'Service provider',
'event__name': 'Event',
'service_delivery_status__name': 'Service delivery status',
'net_company_receipt': 'Net company receipt',
}
class SearchOrderExportAPIView(SearchOrderParams, SearchExportAPIView):
"""Order search export view."""
queryset = DBOrder.objects.annotate(
subtotal_in_pounds=Cast(
'subtotal_cost',
DecimalField(max_digits=19, decimal_places=2),
) / 100,
# This follows the example from
# https://docs.djangoproject.com/en/2.1/ref/models/expressions/#using-aggregates-within-a-subquery-expression
net_refund_in_pounds=Subquery(
Refund.objects.filter(
order=OuterRef('pk'),
status=RefundStatus.approved,
).order_by(
).values(
'order',
).annotate(
total_refund=Cast(
Sum('net_amount'),
DecimalField(max_digits=19, decimal_places=2),
) / 100,
).values(
'total_refund',
),
output_field=DecimalField(max_digits=19, decimal_places=2),
),
status_name=get_choices_as_case_expression(DBOrder, 'status'),
link=get_front_end_url_expression('order', 'pk'),
sector_name=get_sector_name_subquery('sector'),
company_link=get_front_end_url_expression('company', 'company__pk'),
contact_name=get_full_name_expression('contact'),
contact_link=get_front_end_url_expression('contact', 'contact__pk'),
)
field_titles = {
'reference': 'Order reference',
'subtotal_in_pounds': 'Net price',
'net_refund_in_pounds': 'Net refund',
'status_name': 'Status',
'link': 'Link',
'sector_name': 'Sector',
'primary_market__name': 'Market',
'uk_region__name': 'UK region',
'company__name': 'Company',
'company__registered_address_country__name': 'Company country',
'company__uk_region__name': 'Company UK region',
'company_link': 'Company link',
'contact_name': 'Contact',
'contact__job_title': 'Contact job title',
'contact_link': 'Contact link',
'created_by__dit_team__name': 'Created by team',
'created_on': 'Date created',
'delivery_date': 'Delivery date',
'quote__created_on': 'Date quote sent',
'quote__accepted_on': 'Date quote accepted',
'paid_on': 'Date payment received',
'completed_on': 'Date completed',
}
class SearchInteractionExportAPIView(SearchInteractionAPIViewMixin,
SearchExportAPIView):
"""Filtered interaction search export view."""
queryset = DBInteraction.objects.annotate(
company_link=get_front_end_url_expression('company', 'company__pk'),
company_sector_name=get_sector_name_subquery('company__sector'),
contact_names=get_string_agg_subquery(
DBInteraction,
get_full_name_expression(
person_field_name='contacts',
bracketed_field_name='job_title',
),
),
adviser_names=get_string_agg_subquery(
DBInteraction,
get_bracketed_concat_expression(
'dit_participants__adviser__first_name',
'dit_participants__adviser__last_name',
expression_to_bracket='dit_participants__team__name',
),
),
link=get_front_end_url_expression('interaction', 'pk'),
kind_name=get_choices_as_case_expression(DBInteraction, 'kind'),
policy_issue_type_names=get_string_agg_subquery(
DBInteraction,
'policy_issue_types__name',
),
policy_area_names=get_string_agg_subquery(
DBInteraction,
'policy_areas__name',
# Some policy areas contain commas, so we use a semicolon to delimit multiple values
delimiter='; ',
),
service_name=get_service_name_subquery('service'),
)
field_titles = {
'date': 'Date',
'kind_name': 'Type',
'service_name': 'Service',
'subject': 'Subject',
'link': 'Link',
'company__name': 'Company',
'company_link': 'Company link',
'company__address_country__name': 'Company country',
'company__uk_region__name': 'Company UK region',
'company_sector_name': 'Company sector',
'contact_names': 'Contacts',
'adviser_names': 'Advisers',
'event__name': 'Event',
'communication_channel__name': 'Communication channel',
'service_delivery_status__name': 'Service delivery status',
'net_company_receipt': 'Net company receipt',
'policy_issue_type_names': 'Policy issue types',
'policy_area_names': 'Policy areas',
'policy_feedback_notes': 'Policy feedback notes',
}
def test_get_choices_as_case_expression(genre):
"""
Test that get_choices_as_case_expression() generates display names for a field with
choices the same way that model_obj.get_FIELD_display() does.
"""
book = BookFactory(genre=genre)
queryset = Book.objects.annotate(genre_name=get_choices_as_case_expression(
Book, 'genre'), )
annotated_book = queryset.first()
assert annotated_book.genre_name == book.get_genre_display()
def test_get_collapse_project_status_expression(status, expected):
"""Tests if Ongoing and Won status get the same value."""
InvestmentProjectFactory(status=status, )
query = InvestmentProject.objects.annotate(
status_name=get_choices_as_case_expression(InvestmentProject,
'status'),
project_status=get_collapse_status_name_expression(),
).values('project_status')
investment_project = query.first()
assert investment_project['project_status'] == expected
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(SearchInvestmentProjectParams,
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('investment-project', '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'),
),
delivery_partner_names=get_string_agg_subquery(
DBInvestmentProject,
'delivery_partners__name',
),
uk_region_location_names=get_string_agg_subquery(
DBInvestmentProject,
'uk_region_locations__name',
),
actual_uk_region_names=get_string_agg_subquery(
DBInvestmentProject,
'actual_uk_regions__name',
),
investor_company_global_account_manager=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'),
)
field_titles = {
'created_on': 'Date created',
'computed_project_code': 'Project reference',
'name': 'Project name',
'investor_company__name': 'Investor company',
'investor_company__registered_address_country__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',
}
class SearchInteractionPolicyFeedbackExportAPIView(
SearchInteractionAPIViewMixin,
SearchExportAPIView,
):
"""Filtered interaction policy feedback search export view."""
queryset = DBInteraction.objects.select_related(
'company',
'company__global_headquarters',
'company__sector',
).prefetch_related(
Prefetch('contacts', queryset=Contact.objects.order_by('pk')),
'policy_areas',
'policy_issue_types',
Prefetch(
'dit_participants',
queryset=(
InteractionDITParticipant.objects.order_by('pk').select_related('adviser', 'team')
),
),
).annotate(
company_link=get_front_end_url_expression('company', 'company__pk'),
company_sector_name=get_sector_name_subquery('company__sector'),
company_sector_cluster=Sector.objects.filter(
parent_id__isnull=True,
tree_id=OuterRef('company__sector__tree_id'),
).values('sector_cluster__name'),
contact_names=get_string_agg_subquery(
DBInteraction,
get_full_name_expression(
person_field_name='contacts',
bracketed_field_name='job_title',
),
),
created_by_name=get_full_name_expression(
person_field_name='created_by',
),
adviser_names=get_string_agg_subquery(
DBInteraction,
get_bracketed_concat_expression(
'dit_participants__adviser__first_name',
'dit_participants__adviser__last_name',
expression_to_bracket='dit_participants__team__name',
),
),
adviser_emails=get_string_agg_subquery(
DBInteraction,
'dit_participants__adviser__email',
),
team_names=get_string_agg_subquery(
DBInteraction,
Cast('dit_participants__team__name', CharField()),
),
team_countries=get_string_agg_subquery(
DBInteraction,
Cast('dit_participants__team__country__name', CharField()),
),
link=get_front_end_url_expression('interaction', 'pk'),
kind_name=get_choices_as_case_expression(DBInteraction, 'kind'),
policy_issue_type_names=get_string_agg_subquery(
DBInteraction,
Cast('policy_issue_types__name', CharField()),
),
policy_area_names=get_string_agg_subquery(
DBInteraction,
Cast('policy_areas__name', CharField()),
# Some policy areas contain commas, so we use a semicolon to delimit multiple values
delimiter='; ',
),
service_name=get_service_name_subquery('service'),
# Placeholder values
tags_prediction=Value('', CharField()),
tag_1=Value('', CharField()),
probability_score_tag_1=Value('', CharField()),
tag_2=Value('', CharField()),
probability_score_tag_2=Value('', CharField()),
tag_3=Value('', CharField()),
probability_score_tag_3=Value('', CharField()),
tag_4=Value('', CharField()),
probability_score_tag_4=Value('', CharField()),
tag_5=Value('', CharField()),
probability_score_tag_5=Value('', CharField()),
)
field_titles = {
'date': 'Date',
'created_on': 'Created date',
'modified_on': 'Modified date',
'link': 'Link',
'service_name': 'Service',
'subject': 'Subject',
'company__name': 'Company',
'company__global_headquarters__name': 'Parent',
'company__global_headquarters__address_country__name': 'Parent country',
'company__address_country__name': 'Company country',
'company__uk_region__name': 'Company UK region',
'company__one_list_tier__name': 'One List Tier',
'company_sector_name': 'Company sector',
'company_sector_cluster': 'Company sector cluster',
'company__turnover': 'turnover',
'company__number_of_employees': 'number_of_employees',
'team_names': 'team_names',
'team_countries': 'team_countries',
'kind_name': 'kind_name',
'communication_channel__name': 'Communication channel',
'was_policy_feedback_provided': 'was_policy_feedback_provided',
'policy_issue_type_names': 'Policy issue types',
'policy_area_names': 'Policy areas',
'policy_feedback_notes': 'Policy feedback notes',
'adviser_names': 'advisers',
'adviser_emails': 'adviser_emails',
'tag_1': 'tag_1',
'probability_score_tag_1': 'probability_score_tag_1',
'tag_2': 'tag_2',
'probability_score_tag_2': 'probability_score_tag_2',
'tag_3': 'tag_3',
'probability_score_tag_3': 'probability_score_tag_3',
'tag_4': 'tag_4',
'probability_score_tag_4': 'probability_score_tag_4',
'tag_5': 'tag_5',
'probability_score_tag_5': 'probability_score_tag_5',
'contact_names': 'Contacts',
'event__name': 'Event',
'service_delivery_status__name': 'Service delivery status',
'net_company_receipt': 'Net company receipt',
}
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
