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 test_get_aggregate_subquery_raises_error_on_on_aggregate_expression(
self):
"""
Test that an error is raised when passed a non-aggregate expression.
"""
with pytest.raises(ValueError):
get_aggregate_subquery(Person, Left('proofread_books__name', 5))
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 CompanySearchApp(SearchApp):
"""SearchApp for company."""
name = 'company'
es_model = Company
view_permissions = (f'company.{CompanyPermission.view_company}',)
export_permission = f'company.{CompanyPermission.export_company}'
queryset = DBCompany.objects.select_related(
'archived_by',
'business_type',
'employee_range',
'export_experience_category',
'headquarter_type',
'one_list_account_owner',
'global_headquarters__one_list_account_owner',
'global_headquarters',
'address_country',
'registered_address_country',
'sector',
'sector__parent',
'sector__parent__parent',
'turnover_range',
'uk_region',
).prefetch_related(
'export_to_countries',
'future_interest_countries',
).annotate(
latest_interaction_date=get_aggregate_subquery(
DBCompany,
Max('interactions__date'),
),
)
def get_dataset(self):
"""Returns a list of all interaction records"""
return Event.objects.annotate(
service_name=get_service_name_subquery('service'),
team_ids=get_aggregate_subquery(
Event,
ArrayAgg('teams__id', ordering=('teams__id',)),
),
).values(
'address_1',
'address_2',
'address_country__name',
'address_county',
'address_postcode',
'address_town',
'created_on',
'end_date',
'event_type__name',
'id',
'lead_team_id',
'location_type__name',
'name',
'notes',
'organiser_id',
'service_name',
'start_date',
'team_ids',
'uk_region__name',
)
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_with_max_aggregate_expression(self, num_books):
"""
Test that Max() can be used to calculate the maximum published-on date for the books a
person has proofread.
"""
proofreader = PersonFactory()
books = BookFactory.create_batch(num_books, proofreader=proofreader)
queryset = Person.objects.annotate(
max_published=get_aggregate_subquery(Person, Max('proofread_books__published_on')),
).filter(
pk=proofreader.pk,
)
actual_max_published = queryset.first().max_published
expected_max_published = max(book.published_on for book in books) if num_books else None
assert actual_max_published == expected_max_published
class CompanyListViewSet(CoreViewSet, DestroyModelMixin):
"""
Views for managing the authenticated user's company lists.
This covers:
- creating a list
- updating (i.e. renaming) a list
- deleting a list
- listing lists
- retrieving details of a single list
"""
required_scopes = (Scope.internal_front_end, )
queryset = CompanyList.objects.annotate(item_count=get_aggregate_subquery(
CompanyList, Count('items')), )
serializer_class = CompanyListSerializer
filter_backends = (DjangoFilterBackend, OrderingFilter)
filterset_fields = ('items__company_id', )
ordering = ('name', 'created_on', 'pk')
def get_queryset(self):
"""Get a query set filtered to the authenticated user's lists."""
return super().get_queryset().filter(adviser=self.request.user)
def get_additional_data(self, create):
"""
Set additional data for when serializer.save() is called.
This makes sure that adviser is set to self.request.user when a list is created
(in the same way created_by and modified_by are).
"""
additional_data = super().get_additional_data(create)
if not create:
# A list is being updated rather than created, so leave the adviser field unchanged
# (as there is no reason to change it)
return additional_data
return {
**additional_data,
'adviser': self.request.user,
}
def get_dataset(self):
"""Returns list of Investment Projects Dataset records"""
return InvestmentProject.objects.annotate(
actual_uk_region_names=get_array_agg_subquery(
InvestmentProject.actual_uk_regions.through,
'investmentproject',
'ukregion__name',
ordering=('ukregion__name', ),
),
business_activity_names=get_array_agg_subquery(
InvestmentProject.business_activities.through,
'investmentproject',
'investmentbusinessactivity__name',
ordering=('investmentbusinessactivity__name', ),
),
competing_countries=get_aggregate_subquery(
InvestmentProject,
ArrayAgg('competitor_countries__name',
ordering=('competitor_countries__name', )),
),
delivery_partner_names=get_array_agg_subquery(
InvestmentProject.delivery_partners.through,
'investmentproject',
'investmentdeliverypartner__name',
ordering=('investmentdeliverypartner__name', ),
),
investor_company_sector=get_sector_name_subquery(
'investor_company__sector'),
level_of_involvement_name=get_empty_string_if_null_expression(
'level_of_involvement__name', ),
project_reference=get_project_code_expression(),
strategic_driver_names=get_array_agg_subquery(
InvestmentProject.strategic_drivers.through,
'investmentproject',
'investmentstrategicdriver__name',
ordering=('investmentstrategicdriver__name', ),
),
sector_name=get_sector_name_subquery('sector'),
team_member_ids=get_aggregate_subquery(
InvestmentProject,
ArrayAgg('team_members__adviser_id',
ordering=('team_members__id', )),
),
uk_company_sector=get_sector_name_subquery('uk_company__sector'),
uk_region_location_names=get_array_agg_subquery(
InvestmentProject.uk_region_locations.through,
'investmentproject',
'ukregion__name',
ordering=('ukregion__name', ),
),
).values(
'actual_land_date',
'actual_uk_region_names',
'address_1',
'address_2',
'address_town',
'address_postcode',
'anonymous_description',
'associated_non_fdi_r_and_d_project_id',
'average_salary__name',
'business_activity_names',
'client_relationship_manager_id',
'client_requirements',
'competing_countries',
'created_by_id',
'created_on',
'delivery_partner_names',
'description',
'estimated_land_date',
'export_revenue',
'fdi_type__name',
'fdi_value__name',
'foreign_equity_investment',
'government_assistance',
'gross_value_added',
'gva_multiplier__multiplier',
'id',
'investment_type__name',
'investor_company_id',
'investor_company_sector',
'investor_type__name',
'level_of_involvement_name',
'likelihood_to_land__name',
'modified_by_id',
'modified_on',
'name',
'new_tech_to_uk',
'non_fdi_r_and_d_budget',
'number_new_jobs',
'number_safeguarded_jobs',
'other_business_activity',
'project_arrived_in_triage_on',
'project_assurance_adviser_id',
'project_manager_id',
'project_reference',
'proposal_deadline',
'r_and_d_budget',
'referral_source_activity__name',
'referral_source_activity_marketing__name',
'referral_source_activity_website__name',
'sector_name',
'specific_programme__name',
'stage__name',
'status',
'strategic_driver_names',
'team_member_ids',
'total_investment',
'uk_company_id',
'uk_company_sector',
'uk_region_location_names',
)
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 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
