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',
)
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',
}
def get_dataset(self):
"""Returns list of PipelineItem records"""
return PipelineItem.objects.annotate(
sector_name=get_sector_name_subquery('sector'),
contact_ids=get_array_agg_subquery(
PipelineItem.contacts.through,
'pipelineitem',
'contact__id',
ordering=('contact__created_on', ),
),
).values(
'adviser_id',
'archived',
'company_id',
'contact_ids',
'created_on',
'expected_win_date',
'id',
'likelihood_to_win',
'modified_on',
'name',
'potential_value',
'sector_name',
'status',
)
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 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',
}
def test_annotated_sector_name_root_node():
"""Test the sector name annotation for a sector at root level."""
sector = SectorFactory(parent=None)
annotated_sector = Sector.objects.annotate(
name_annotation=get_sector_name_subquery(),
).get(
pk=sector.pk,
)
assert annotated_sector.name_annotation == sector.name
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',
)
def test_annotated_sector_name_level_one():
"""Test the sector name annotation for a sector one level deep."""
parent = SectorFactory()
sector = SectorFactory(parent=parent)
annotated_sector = Sector.objects.annotate(
name_annotation=get_sector_name_subquery(),
).get(
pk=sector.pk,
)
assert annotated_sector.name_annotation == sector.name
def get_dataset(self):
"""Returns list of Company records"""
return Company.objects.annotate(
sector_name=get_sector_name_subquery('sector'),
one_list_core_team_advisers=ArrayAgg(
'one_list_core_team_members__adviser_id'),
).values(
'address_1',
'address_2',
'address_county',
'address_country__name',
'address_postcode',
'address_area__name',
'address_town',
'archived',
'archived_on',
'archived_reason',
'business_type__name',
'company_number',
'created_by_id',
'created_on',
'description',
'duns_number',
'export_experience_category__name',
'global_headquarters_id',
'global_ultimate_duns_number',
'headquarter_type__name',
'id',
'is_number_of_employees_estimated',
'is_turnover_estimated',
'modified_on',
'name',
'number_of_employees',
'one_list_account_owner_id',
'one_list_tier__name',
'one_list_core_team_advisers',
'reference_code',
'registered_address_1',
'registered_address_2',
'registered_address_country__name',
'registered_address_county',
'registered_address_postcode',
'registered_address_area__name',
'registered_address_town',
'sector_name',
'export_segment',
'export_sub_segment',
'trading_names',
'turnover',
'uk_region__name',
'vat_number',
'website',
)
def test_annotated_sector_name_via_relation():
"""Test the sector name annotation via a relation."""
grandparent = SectorFactory()
parent = SectorFactory(parent=grandparent)
sector = SectorFactory(parent=parent)
annotated_sector = Sector.objects.annotate(
parent_name_annotation=get_sector_name_subquery('parent'),
).get(
pk=sector.pk,
)
assert annotated_sector.parent_name_annotation == parent.name
def test_annotated_sector_name_level_two():
"""Test the sector name annotation for a sector two levels deep."""
grandparent = SectorFactory()
parent = SectorFactory(parent=grandparent)
sector = SectorFactory(parent=parent)
annotated_sector = Sector.objects.annotate(
name_annotation=get_sector_name_subquery(),
).get(
pk=sector.pk,
)
assert annotated_sector.name_annotation == sector.name
def test_get_sector_cluster_from_project_sector(sector_cluster_id, expected):
"""Tests that sector cluster can be mapped from sector."""
parent_sector = SectorFactory(
segment='Cats',
sector_cluster_id=sector_cluster_id,
)
sector = SectorFactory(segment='Rockets', parent=parent_sector)
InvestmentProjectFactory(sector_id=sector.id, )
query = InvestmentProject.objects.annotate(
sector_name=get_sector_name_subquery('sector'),
sector_cluster=get_sector_cluster_expression('sector'),
).values('sector_cluster')
for project in query.all():
assert project['sector_cluster'] == expected
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',
}
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)),
)
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 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 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
