def get_spi_report_queryset():
"""Get SPI Report queryset."""
return InvestmentProject.objects.select_related(
'investmentprojectcode',
'project_manager__dit_team',
).annotate(
spi_propositions=get_array_agg_subquery(
Proposition,
'investment_project',
JSONBBuildObject(
deadline='deadline',
status='status',
adviser_id='adviser_id',
adviser_name=get_full_name_expression('adviser'),
modified_on='modified_on',
),
ordering=('created_on', ),
),
spi_interactions=get_array_agg_subquery(
Interaction,
'investment_project',
JSONBBuildObject(
service_id='service_id',
service_name=get_service_name_subquery('service'),
created_by_id='created_by_id',
created_by_name=get_full_name_expression('created_by'),
created_on='created_on',
),
filter=Q(service_id__in=ALL_SPI_SERVICE_IDS),
ordering=('created_on', ),
),
).order_by('created_on')
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 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',
),
),
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'),
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='; ',
),
)
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',
'dit_adviser_name': 'Adviser',
'dit_team__name': 'Service provider',
'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 AllAdvisersReport(QuerySetReport):
"""Admin report for all advisers."""
id = 'all-advisers'
name = 'All advisers'
model = Advisor
permissions_required = ('company.view_advisor', )
queryset = Advisor.objects.annotate(
name=get_full_name_expression(),
is_team_active=Case(
When(dit_team__disabled_on__isnull=True,
dit_team__isnull=False,
then=True),
When(dit_team__disabled_on__isnull=False, then=False),
default=None,
output_field=NullBooleanField(),
),
).order_by(
'date_joined',
'pk',
)
field_titles = {
'id': 'Adviser ID',
'email': 'Username',
'name': 'Name',
'contact_email': 'Contact email',
'is_active': 'Is active',
'dit_team__name': 'Team',
'is_team_active': 'Is team active',
'dit_team__role__name': 'Team role',
}
def test_none_for_none_relation(self):
"""Tests that None is returned for an unset foreign key."""
BookFactory(proofreader=None)
queryset = Book.objects.annotate(
proofreader_name=get_full_name_expression('proofreader'),
)
assert queryset.first().proofreader_name is None
def _look_up_adviser(adviser_name, team):
if not adviser_name:
return None
get_kwargs = {
'is_active': True,
'name__iexact': adviser_name,
}
if team:
get_kwargs['dit_team'] = team
queryset = Advisor.objects.annotate(name=get_full_name_expression())
try:
return queryset.get(**get_kwargs)
except Advisor.DoesNotExist:
if team:
raise ValidationError(
ADVISER_WITH_TEAM_NOT_FOUND_MESSAGE,
code='adviser_and_team_not_found',
)
raise ValidationError(ADVISER_NOT_FOUND_MESSAGE,
code='adviser_not_found')
except Advisor.MultipleObjectsReturned:
raise ValidationError(MULTIPLE_ADVISERS_FOUND_MESSAGE,
code='multiple_advisers_found')
def test_ignores_blank_first_name(self):
"""Tests that a blank first_name is ignored."""
person = PersonFactory(first_name='')
queryset = Person.objects.annotate(
name=get_full_name_expression(),
)
assert queryset.first().name == person.last_name
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',
}
def get_dataset(self):
"""Returns list of Contacts Dataset records"""
return Contact.objects.annotate(
name=get_full_name_expression(), ).values(
'address_1',
'address_2',
'address_country__name',
'address_county',
'address_postcode',
'address_same_as_company',
'address_town',
'archived',
'archived_on',
'company_id',
'created_by_id',
'created_on',
'email',
'email_alternative',
'id',
'job_title',
'modified_on',
'name',
'notes',
'primary',
'telephone_alternative',
'telephone_number',
)
def get_lead_order_assignee_name_subquery():
"""Get lead order assignee name subquery."""
subquery = OrderAssignee.objects.filter(
order=OuterRef('pk'),
is_lead=True,
).annotate(name=get_full_name_expression('adviser'), ).values('name', )
return Subquery(subquery, output_field=CharField())
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 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',
}
def test_full_name_annotation(self, include_country, country):
"""Tests that a Person query set can be annotated with full names."""
person = PersonFactory(country=country)
bracketed_field_name = 'country' if include_country else None
queryset = Person.objects.annotate(name=get_full_name_expression(
bracketed_field_name=bracketed_field_name), )
expected_name = f'{person.first_name} {person.last_name}'
if country and include_country:
expected_name += f' ({person.country})'
assert queryset.first().name == expected_name
def test_full_name_related_annotation(self):
"""
Tests that a Book query set can be annotated with the full name of the proofreader
of each book.
"""
book = BookFactory()
proofreader = book.proofreader
queryset = Book.objects.annotate(
proofreader_name=get_full_name_expression('proofreader'), )
expected_name = f'{proofreader.first_name} {proofreader.last_name}'
assert queryset.first().proofreader_name == expected_name
def get_company_list_item_queryset():
"""
Returns an annotated query set used by CompanyListItemViewSet and CompanyListViewSet.
The annotations are supported by an index on the Interaction model.
(Note that getting all three interaction fields in one expression currently is not easily
done with the Django ORM, hence three annotations are used.)
"""
return CompanyListItem.objects.annotate(
latest_interaction_id=_get_field_of_latest_interaction('pk'),
latest_interaction_created_on=_get_field_of_latest_interaction(
'created_on'),
latest_interaction_date=_get_field_of_latest_interaction('date'),
latest_interaction_subject=_get_field_of_latest_interaction('subject'),
latest_interaction_dit_participants=_get_field_of_latest_interaction(
get_array_agg_subquery(
InteractionDITParticipant,
'interaction',
JSONBBuildObject(
adviser=_get_null_when_expression(
JSONBBuildObject(
id='adviser__id',
name=get_full_name_expression('adviser'),
),
Q(adviser__isnull=True),
),
team=_get_null_when_expression(
JSONBBuildObject(
id='team__id',
name='team__name',
),
Q(team__isnull=True),
),
),
ordering=('pk', ),
), ),
latest_interaction_time_ago=ExpressionWrapper(
Now() - F('latest_interaction_date'),
output_field=DurationField(),
),
).select_related('company', ).only(
# Only select the fields we need to reduce data transfer time for large lists
# (in particular, companies have a lot of fields which are not needed here)
'id',
'created_on',
'company__id',
'company__archived',
'company__name',
'company__trading_names',
)
def test_full_name_related_annotation(self, include_country, country):
"""
Tests that a Book query set can be annotated with the full name of the proofreader
of each book.
"""
book = BookFactory(proofreader__country=country)
proofreader = book.proofreader
bracketed_field_name = 'country' if include_country else None
queryset = Book.objects.annotate(
proofreader_name=get_full_name_expression(
person_field_name='proofreader',
bracketed_field_name=bracketed_field_name,
), )
expected_name = f'{proofreader.first_name} {proofreader.last_name}'
if country and include_country:
expected_name += f' ({proofreader.country})'
assert queryset.first().proofreader_name == expected_name
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(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 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
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 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 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',
}
def test_full_name_annotation(self):
"""Tests that a Person query set can be annotated with full names."""
person = PersonFactory()
queryset = Person.objects.annotate(name=get_full_name_expression(), )
expected_name = f'{person.first_name} {person.last_name}'
assert queryset.first().name == expected_name
def _format_expected_contacts(interaction):
queryset = interaction.contacts.order_by(
get_full_name_expression(bracketed_field_name='job_title'), )
return ', '.join(
_format_expected_contact_name(contact) for contact in queryset)
