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',
}
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'),
),
team_of_latest_interaction=get_top_related_expression_subquery(
DBInteraction.contacts.field,
F('dit_team__name'),
('-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',
'team_of_latest_interaction': 'Team of latest interaction',
'created_by__dit_team__name': 'Created by team',
}
class OneListReport(QuerySetReport):
"""Generates the One List."""
id = 'one-list'
name = 'One List'
model = Company
permissions_required = ('company.view_company', )
queryset = Company.objects.filter(
headquarter_type_id=constants.HeadquarterType.ghq.value.id,
classification__id__isnull=False,
one_list_account_owner_id__isnull=False,
).annotate(
primary_contact_name=get_full_name_expression(
'one_list_account_owner'),
url=get_front_end_url_expression('company', 'pk'),
).order_by(
'classification__order',
'name',
)
field_titles = {
'name': 'Account Name',
'classification__name': 'Tier',
'sector__segment': 'Sector',
'primary_contact_name': 'Primary Contact',
'one_list_account_owner__telephone_number': 'Contact Number',
'one_list_account_owner__contact_email': 'E-mail',
'registered_address_country__name': 'Home Market',
'registered_address_town': 'Town/City',
'url': 'Data Hub URL',
}
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',
}
def test_get_front_end_url_expression(monkeypatch):
"""Test that get_front_end_url_expression() generates URLs correctly."""
monkeypatch.setitem(settings.DATAHUB_FRONTEND_URL_PREFIXES, 'book', 'http://test')
book = BookFactory()
queryset = Book.objects.annotate(
url=get_front_end_url_expression('book', 'pk'),
)
assert queryset.first().url == f'http://test/{book.pk}'
def get_dataset(self):
"""Returns a list of all interaction records"""
return get_base_interaction_queryset().annotate(
adviser_ids=get_aggregate_subquery(
Interaction,
ArrayAgg('dit_participants__adviser_id',
ordering=('dit_participants__id', )),
),
contact_ids=get_aggregate_subquery(
Interaction,
ArrayAgg('contacts__id', ordering=('contacts__id', )),
),
interaction_link=get_front_end_url_expression('interaction', 'pk'),
policy_area_names=get_array_agg_subquery(
Interaction.policy_areas.through,
'interaction',
'policyarea__name',
ordering=('policyarea__order', ),
),
policy_issue_type_names=get_array_agg_subquery(
Interaction.policy_issue_types.through,
'interaction',
'policyissuetype__name',
ordering=('policyissuetype__order', ),
),
sector=get_sector_name_subquery('company__sector'),
service_delivery=get_service_name_subquery('service'),
).values(
'adviser_ids',
'communication_channel__name',
'company_id',
'contact_ids',
'created_by_id',
'created_on',
'date',
'event_id',
'grant_amount_offered',
'id',
'interaction_link',
'investment_project_id',
'kind',
'modified_on',
'net_company_receipt',
'notes',
'policy_area_names',
'policy_feedback_notes',
'policy_issue_type_names',
'sector',
'service_delivery_status__name',
'service_delivery',
'subject',
'theme',
'were_countries_discussed',
)
class SearchCompanyExportAPIView(SearchCompanyParams, 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'),
)
field_titles = {
'name': 'Name',
'link': 'Link',
'sector_name': 'Sector',
'registered_address_country__name': 'Country',
'uk_region__name': 'UK region',
'archived': 'Archived',
'created_on': 'Date created',
'employee_range__name': 'Number of employees',
'turnover_range__name': 'Annual turnover',
'upper_headquarter_type_name': 'Headquarter type',
}
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 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
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 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
