class SearchCompanyExportAPIView(SearchCompanyAPIViewMixin,
SearchExportAPIView):
"""Company search export view."""
queryset = DBCompany.objects.annotate(
link=get_front_end_url_expression('company', 'pk'),
upper_headquarter_type_name=Upper('headquarter_type__name'),
sector_name=get_sector_name_subquery('sector'),
# get company.turnover if set else company.turnover_range
turnover_value=Case(
When(
turnover__isnull=False,
then=Concat(Value('$'), 'turnover'),
),
default='turnover_range__name',
output_field=CharField(),
),
# get company.number_of_employees if set else company.employee_range
number_of_employees_value=Case(
When(
number_of_employees__isnull=False,
then=Cast('number_of_employees', CharField()),
),
default='employee_range__name',
output_field=CharField(),
),
export_to_countries_list=get_string_agg_subquery(
DBCompany,
Case(
When(
export_countries__status=CompanyExportCountry.Status.
CURRENTLY_EXPORTING,
then='export_countries__country__name',
), ),
),
future_interest_countries_list=get_string_agg_subquery(
DBCompany,
Case(
When(
export_countries__status=CompanyExportCountry.Status.
FUTURE_INTEREST,
then='export_countries__country__name',
), ),
),
)
field_titles = {
'name': 'Name',
'link': 'Link',
'sector_name': 'Sector',
'address_country__name': 'Country',
'uk_region__name': 'UK region',
'export_to_countries_list': 'Countries exported to',
'future_interest_countries_list': 'Countries of interest',
'archived': 'Archived',
'created_on': 'Date created',
'number_of_employees_value': 'Number of employees',
'turnover_value': 'Annual turnover',
'upper_headquarter_type_name': 'Headquarter type',
}
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 get_dataset(self):
"""Returns list of OMIS Dataset records"""
return Order.objects.annotate(
refund_created=get_aggregate_subquery(Order,
Max('refunds__created_on')),
refund_total_amount=get_aggregate_subquery(
Order, Sum('refunds__total_amount')),
sector_name=get_sector_name_subquery('sector'),
services=get_string_agg_subquery(Order, 'service_types__name'),
).values(
'cancellation_reason__name',
'cancelled_on',
'company_id',
'completed_on',
'contact_id',
'created_by__dit_team_id',
'created_on',
'delivery_date',
'id',
'invoice__subtotal_cost',
'paid_on',
'primary_market__name',
'quote__accepted_on',
'quote__created_on',
'reference',
'refund_created',
'refund_total_amount',
'sector_name',
'services',
'status',
'subtotal_cost',
'total_cost',
'uk_region__name',
'vat_cost',
)
def get_investment_project_to_many_string_agg_subquery(expression):
"""
Returns a subquery that uses string_agg to concatenate values of a to-many field
of investment project
"""
return get_string_agg_subquery(
InvestmentProject,
expression,
)
class SearchContactExportAPIView(SearchContactAPIViewMixin, SearchExportAPIView):
"""Company search export view."""
db_sort_by_remappings = {
'address_country.name': 'computed_country_name',
}
queryset = DBContact.objects.annotate(
name=get_full_name_expression(),
link=get_front_end_url_expression('contact', 'pk'),
company_sector_name=get_sector_name_subquery('company__sector'),
company_link=get_front_end_url_expression('company', 'company__pk'),
computed_country_name=Case(
When(address_same_as_company=True, then='company__address_country__name'),
default='address_country__name',
),
computed_postcode=Case(
When(address_same_as_company=True, then='company__address_postcode'),
default='address_postcode',
),
full_telephone_number=ConcatWS(
Value(' '),
NullIf('telephone_countrycode', Value('')),
NullIf('telephone_number', Value('')),
),
date_of_latest_interaction=get_aggregate_subquery(
DBContact,
Max('interactions__date'),
),
teams_of_latest_interaction=get_top_related_expression_subquery(
DBInteraction.contacts.field,
get_string_agg_subquery(DBInteraction, 'dit_participants__team__name', distinct=True),
('-date',),
),
)
field_titles = {
'name': 'Name',
'job_title': 'Job title',
'created_on': 'Date created',
'archived': 'Archived',
'link': 'Link',
'company__name': 'Company',
'company_sector_name': 'Company sector',
'company_link': 'Company link',
'company__uk_region__name': 'Company UK region',
'computed_country_name': 'Country',
'computed_postcode': 'Postcode',
'full_telephone_number': 'Phone number',
'email': 'Email address',
'accepts_dit_email_marketing': 'Accepts DIT email marketing',
'date_of_latest_interaction': 'Date of latest interaction',
'teams_of_latest_interaction': 'Teams of latest interaction',
'created_by__dit_team__name': 'Created by team',
}
def test_get_string_agg_subquery(num_authors):
"""
Test that get_string_agg_subquery() can be used to concatenate the first names of
all authors for each book into one field.
"""
authors = PersonFactory.create_batch(num_authors)
BookFactory(authors=authors)
queryset = Book.objects.annotate(author_names=get_string_agg_subquery(
Book, 'authors__first_name'), )
author_names_str = queryset.first().author_names
actual_author_names = sorted(
author_names_str.split(', ')) if author_names_str else []
expected_author_names = sorted(author.first_name for author in authors)
assert actual_author_names == expected_author_names
def test_can_annotate_queryset(self, names, distinct, expected_result):
"""
Test that the first names of all authors for each book can be concatenated into
one field as a query set annotation for various cases.
"""
authors = PersonFactory.create_batch(
len(names),
first_name=factory.Iterator(
sample(names, len(names)),
),
)
BookFactory(authors=authors)
queryset = Book.objects.annotate(
author_names=get_string_agg_subquery(Book, 'authors__first_name', distinct=distinct),
)
actual_author_names = queryset.first().author_names
assert actual_author_names == expected_result
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 SearchContactExportAPIView(SearchContactAPIViewMixin,
SearchExportAPIView):
"""Company search export view."""
def _is_valid_email(self, value):
"""Validate if emails are valid and return a boolean flag."""
try:
validate_email(value)
return True
except ValidationError:
return False
consent_page_size = 100
db_sort_by_remappings = {
'address_country.name': 'computed_country_name',
'address_area.name': 'computed_area_name',
}
queryset = DBContact.objects.annotate(
name=get_full_name_expression(),
link=get_front_end_url_expression('contact', 'pk'),
company_sector_name=get_sector_name_subquery('company__sector'),
company_link=get_front_end_url_expression('company', 'company__pk'),
computed_country_name=Case(
When(address_same_as_company=True,
then='company__address_country__name'),
default='address_country__name',
),
computed_area_name=Case(
When(address_same_as_company=True,
then='company__address_area__name'),
default='address_area__name',
),
computed_postcode=Case(
When(address_same_as_company=True,
then='company__address_postcode'),
default='address_postcode',
),
date_of_latest_interaction=get_aggregate_subquery(
DBContact,
Max('interactions__date'),
),
teams_of_latest_interaction=get_top_related_expression_subquery(
DBInteraction.contacts.field,
get_string_agg_subquery(
DBInteraction,
Cast('dit_participants__team__name', CharField()),
distinct=True,
),
('-date', ),
),
)
field_titles = {
'name': 'Name',
'job_title': 'Job title',
'created_on': 'Date created',
'archived': 'Archived',
'link': 'Link',
'company__name': 'Company',
'company_sector_name': 'Company sector',
'company_link': 'Company link',
'company__uk_region__name': 'Company UK region',
'computed_country_name': 'Country',
'computed_area_name': 'Area',
'computed_postcode': 'Postcode',
'full_telephone_number': 'Phone number',
'email': 'Email address',
'accepts_dit_email_marketing': 'Accepts DIT email marketing',
'date_of_latest_interaction': 'Date of latest interaction',
'teams_of_latest_interaction': 'Teams of latest interaction',
'created_by__dit_team__name': 'Created by team',
}
def _add_consent_response(self, rows):
"""
Transforms iterable to add user consent from the consent service.
The consent lookup makes an external API call to return consent.
For perfromance reasons the consent amount is limited by consent_page_size.
Due to this limitaion the iterable are sliced into chunks requesting consent for 100 rows
at a time.
"""
# Slice iterable into chunks
row_chunks = slice_iterable_into_chunks(rows, self.consent_page_size)
for chunk in row_chunks:
"""
Loop over the chunks and extract the email and item.
Save the item because the iterator cannot be used twice.
"""
rows = list(chunk)
# Peform constent lookup on emails POST request
consent_lookups = consent.get_many([
row['email']
for row in rows if self._is_valid_email(row['email'])
], )
for row in rows:
# Assign contact consent boolean to accepts_dit_email_marketing
# and yield modified result.
row['accepts_dit_email_marketing'] = consent_lookups.get(
row['email'], False)
yield row
def _get_rows(self, ids, search_ordering):
"""
Get row queryset for constent service.
This populates accepts_dit_email_marketing field from the consent service and
removes the accepts_dit_email_marketing from the field query because the field is not in
the db.
"""
db_ordering = self._translate_search_ordering_to_django_ordering(
search_ordering)
field_titles = self.field_titles.copy()
del field_titles['accepts_dit_email_marketing']
rows = self.queryset.filter(pk__in=ids, ).order_by(
*db_ordering, ).values(*field_titles, ).iterator()
return self._add_consent_response(rows)
class SearchLargeInvestorProfileExportAPIView(
SearchInvestorProfileAPIViewMixin,
SearchExportAPIView,
):
"""Large capital investor profile search export view."""
queryset = DBInvestorProfile.objects.annotate(
required_checks_conducted_by_name=get_full_name_expression(
'required_checks_conducted_by', ),
deal_ticket_sizes_names=get_string_agg_subquery(
DBInvestorProfile,
'deal_ticket_sizes__name',
),
asset_classes_of_interest_names=get_string_agg_subquery(
DBInvestorProfile,
'asset_classes_of_interest__name',
),
investment_types_names=get_string_agg_subquery(
DBInvestorProfile,
'investment_types__name',
),
time_horizons_names=get_string_agg_subquery(
DBInvestorProfile,
'time_horizons__name',
),
restrictions_names=get_string_agg_subquery(
DBInvestorProfile,
'restrictions__name',
),
construction_risks_names=get_string_agg_subquery(
DBInvestorProfile,
'construction_risks__name',
),
desired_deal_roles_names=get_string_agg_subquery(
DBInvestorProfile,
'desired_deal_roles__name',
),
uk_region_locations_names=get_string_agg_subquery(
DBInvestorProfile,
'uk_region_locations__name',
),
other_countries_being_considered_names=get_string_agg_subquery(
DBInvestorProfile,
'other_countries_being_considered__name',
),
link=get_front_end_url_expression(
'company',
'investor_company_id',
url_suffix='/investments/large-capital-profile',
),
)
field_titles = {
'created_on': 'Date created',
'id': 'Data Hub profile reference',
'link': 'Data Hub link',
'investor_company__name': 'Investor company',
'investor_type__name': 'Investor type',
'investable_capital': 'Investable capital',
'global_assets_under_management': 'Global assets under management',
'investor_description': 'Investor description',
'required_checks_conducted__name': 'Required checks conducted',
'required_checks_conducted_by_name': 'Required checks conducted by',
'required_checks_conducted_on': 'Required checks conducted on',
'deal_ticket_sizes_names': 'Deal ticket sizes',
'asset_classes_of_interest_names': 'Asset classes of interest',
'investment_types_names': 'Investment types',
'minimum_return_rate__name': 'Minimum return rate',
'time_horizons_names': 'Time horizons',
'restrictions_names': 'Restrictions',
'construction_risks_names': 'Construction risks',
'minimum_equity_percentage__name': 'Minimum equity percentage',
'desired_deal_roles_names': 'Desired deal roles',
'uk_region_locations_names': 'UK regions of interest',
'other_countries_being_considered_names':
'Other countries being considered',
'notes_on_locations': 'Notes on locations',
'modified_on': 'Date last modified',
}
class SearchCompanyExportAPIView(SearchCompanyAPIViewMixin,
SearchExportAPIView):
"""Company search export view."""
queryset = DBCompany.objects.annotate(
link=get_front_end_url_expression('company', 'pk'),
upper_headquarter_type_name=Upper('headquarter_type__name'),
sector_name=get_sector_name_subquery('sector'),
# get company.turnover if set else company.turnover_range
turnover_value=Case(
When(
turnover__isnull=False,
then=Concat(Value('$'), 'turnover'),
),
default='turnover_range__name',
output_field=CharField(),
),
# get company.number_of_employees if set else company.employee_range
number_of_employees_value=Case(
When(
number_of_employees__isnull=False,
then=Cast('number_of_employees', CharField()),
),
default='employee_range__name',
output_field=CharField(),
),
export_to_countries_list=get_string_agg_subquery(
DBCompany,
Case(
When(
export_countries__status=CompanyExportCountry.Status.
CURRENTLY_EXPORTING,
then=Cast('export_countries__country__name', CharField()),
), ),
),
future_interest_countries_list=get_string_agg_subquery(
DBCompany,
Case(
When(
export_countries__status=CompanyExportCountry.Status.
FUTURE_INTEREST,
then=Cast('export_countries__country__name', CharField()),
), ),
),
)
@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 = {
'name': 'Name',
'link': 'Link',
'sector_name': 'Sector',
'address_country__name': 'Country',
}
field_titles.update({
'address_area__name': 'Area',
'uk_region__name': 'UK region',
'export_to_countries_list': 'Countries exported to',
'future_interest_countries_list': 'Countries of interest',
'archived': 'Archived',
'created_on': 'Date created',
'number_of_employees_value': 'Number of employees',
'turnover_value': 'Annual turnover',
'upper_headquarter_type_name': 'Headquarter type',
})
return field_titles
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 SearchLargeCapitalOpportunityExportAPIView(
SearchOpportunityAPIViewMixin,
SearchExportAPIView,
):
"""Large capital opportunity search export view."""
queryset = DBLargeCapitalOpportunity.objects.annotate(
required_checks_conducted_by_name=get_full_name_expression(
'required_checks_conducted_by',
),
lead_dit_relationship_manager_name=get_full_name_expression(
'lead_dit_relationship_manager',
),
created_by_name=get_full_name_expression('created_by'),
asset_class_names=get_string_agg_subquery(
DBLargeCapitalOpportunity,
Cast('asset_classes__name', CharField()),
),
type_names=get_string_agg_subquery(
DBLargeCapitalOpportunity,
'type__name',
),
status_names=get_string_agg_subquery(
DBLargeCapitalOpportunity,
'status__name',
),
promoter_names=get_string_agg_subquery(
DBLargeCapitalOpportunity,
'promoters__name',
),
other_dit_contact_names=get_string_agg_subquery(
DBLargeCapitalOpportunity,
get_full_name_expression('other_dit_contacts'),
ordering=('other_dit_contacts__first_name', 'other_dit_contacts__last_name'),
),
investment_type_names=get_string_agg_subquery(
DBLargeCapitalOpportunity,
Cast('investment_types__name', CharField()),
),
time_horizons_names=get_string_agg_subquery(
DBLargeCapitalOpportunity,
Cast('time_horizons__name', CharField()),
),
sources_of_funding_names=get_string_agg_subquery(
DBLargeCapitalOpportunity,
Cast('sources_of_funding__name', CharField()),
),
construction_risks_names=get_string_agg_subquery(
DBLargeCapitalOpportunity,
Cast('construction_risks__name', CharField()),
),
uk_region_locations_names=get_string_agg_subquery(
DBLargeCapitalOpportunity,
Cast('uk_region_locations__name', CharField()),
),
reasons_for_abandonment_names=get_string_agg_subquery(
DBLargeCapitalOpportunity,
Cast('reasons_for_abandonment__name', CharField()),
),
link=get_front_end_url_expression(
'largecapitalopportunity',
'id',
url_suffix='/investments/large-capital-opportunity',
),
)
field_titles = {
'created_on': 'Date created',
'created_by_name': 'Created by',
'id': 'Data Hub opportunity reference',
'link': 'Data Hub link',
'name': 'Name',
'description': 'Description',
'type__name': 'Type',
'status__name': 'Status',
'uk_region_locations_names': 'UK region locations',
'promoter_names': 'Promoters',
'lead_dit_relationship_manager_name': 'Lead DIT relationship manager',
'other_dit_contact_names': 'Other DIT contacts',
'required_checks_conducted__name': 'Required checks conducted',
'required_checks_conducted_by_name': 'Required checks conducted by',
'required_checks_conducted_on': 'Required checks conducted on',
'asset_class_names': 'Asset classes',
'opportunity_value_type__name': 'Opportunity value type',
'opportunity_value': 'Opportunity value',
'construction_risks_names': 'Construction risks',
'total_investment_sought': 'Total investment sought',
'current_investment_secured': 'Current investment secured',
'investment_type_names': 'Investment types',
'estimated_return_rate__name': 'Estimated return rate',
'time_horizons_names': 'Time horizons',
'sources_of_funding_names': 'Sources of funding',
'dit_support_provided': 'DIT support provided',
'funding_supporting_details': 'Funding supporting details',
'reasons_for_abandonment_names': 'Reasons for abandonment',
'why_abandoned': 'Why abandoned',
'why_suspended': 'Why suspended',
'modified_on': 'Date last modified',
}
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
