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')
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 SearchInteractionExportAPIView(SearchInteractionAPIViewMixin,
SearchExportAPIView):
"""Filtered interaction search export view."""
queryset = DBInteraction.objects.annotate(
company_link=get_front_end_url_expression('company', 'company__pk'),
company_sector_name=get_sector_name_subquery('company__sector'),
contact_names=get_string_agg_subquery(
DBInteraction,
get_full_name_expression(
person_field_name='contacts',
bracketed_field_name='job_title',
),
),
adviser_names=get_string_agg_subquery(
DBInteraction,
get_bracketed_concat_expression(
'dit_participants__adviser__first_name',
'dit_participants__adviser__last_name',
expression_to_bracket='dit_participants__team__name',
),
),
link=get_front_end_url_expression('interaction', 'pk'),
kind_name=get_choices_as_case_expression(DBInteraction, 'kind'),
policy_issue_type_names=get_string_agg_subquery(
DBInteraction,
'policy_issue_types__name',
),
policy_area_names=get_string_agg_subquery(
DBInteraction,
'policy_areas__name',
# Some policy areas contain commas, so we use a semicolon to delimit multiple values
delimiter='; ',
),
service_name=get_service_name_subquery('service'),
)
field_titles = {
'date': 'Date',
'kind_name': 'Type',
'service_name': 'Service',
'subject': 'Subject',
'link': 'Link',
'company__name': 'Company',
'company_link': 'Company link',
'company__address_country__name': 'Company country',
'company__uk_region__name': 'Company UK region',
'company_sector_name': 'Company sector',
'contact_names': 'Contacts',
'adviser_names': 'Advisers',
'event__name': 'Event',
'communication_channel__name': 'Communication channel',
'service_delivery_status__name': 'Service delivery status',
'net_company_receipt': 'Net company receipt',
'policy_issue_type_names': 'Policy issue types',
'policy_area_names': 'Policy areas',
'policy_feedback_notes': 'Policy feedback notes',
}
def get_dataset(self):
"""Returns 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 InteractionCSVRowForm(forms.Form):
"""Form used for validating a single row in a CSV of interactions."""
# Used to map errors from serializer fields to fields in this form
# when running the serializer validators in full_clean()
SERIALIZER_FIELD_MAPPING = {
'event': 'event_id',
}
theme = forms.ChoiceField(choices=Interaction.Theme.choices)
kind = forms.ChoiceField(choices=Interaction.Kind.choices)
date = forms.DateField(input_formats=['%d/%m/%Y', '%Y-%m-%d'])
# Used to attempt to find a matching contact (and company) for the interaction
# Note that if a matching contact is not found, the interaction in question is
# skipped (and the user informed) rather than the whole process aborting
contact_email = forms.EmailField()
# Represents an InteractionDITParticipant for the interaction.
# The adviser will be looked up by name (case-insensitive) with inactive advisers
# excluded.
# If team_1 is provided, this will also be used to narrow down the match (useful
# when, for example, two advisers have the same name).
adviser_1 = forms.CharField()
team_1 = NoDuplicatesModelChoiceField(
Team.objects.all(),
to_field_name='name__iexact',
required=False,
)
# Represents an additional InteractionDITParticipant for the interaction
# adviser_2 is looked up in the same way as adviser_1 (described above)
adviser_2 = forms.CharField(required=False)
team_2 = NoDuplicatesModelChoiceField(
Team.objects.all(),
to_field_name='name__iexact',
required=False,
)
service = NoDuplicatesModelChoiceField(
Service.objects.annotate(name=get_service_name_subquery()).filter(children__isnull=True),
to_field_name='name__iexact',
validators=[_validate_not_disabled],
)
service_answer = forms.CharField(required=False)
communication_channel = NoDuplicatesModelChoiceField(
CommunicationChannel.objects.all(),
to_field_name='name__iexact',
required=False,
validators=[_validate_not_disabled],
)
event_id = forms.ModelChoiceField(
Event.objects.all(),
required=False,
validators=[_validate_not_disabled],
)
# Subject is optional as it defaults to the name of the service
subject = forms.CharField(required=False)
notes = forms.CharField(required=False)
def __init__(self, *args, duplicate_tracker=None, row_index=None, **kwargs):
"""Initialise the form with an optional zero-based row index."""
super().__init__(*args, **kwargs)
self.row_index = row_index
self.duplicate_tracker = duplicate_tracker
@classmethod
def get_required_field_names(cls):
"""Get the required base fields of this form."""
return {name for name, field in cls.base_fields.items() if field.required}
def get_flat_error_list_iterator(self):
"""Get a generator of CSVRowError instances representing validation errors."""
return (
CSVRowError(self.row_index, field, self.data.get(field, ''), error)
for field, errors in self.errors.items()
for error in errors
)
def is_valid_and_matched(self):
"""Return if the form is valid and the interaction has been matched to a contact."""
return self.is_valid() and self.is_matched()
def is_matched(self):
"""
Returns whether the interaction was matched to a contact.
Can only be called post-cleaning.
"""
return self.cleaned_data['contact_matching_status'] == ContactMatchingStatus.matched
def clean(self):
"""Validate and clean the data for this row."""
data = super().clean()
kind = data.get('kind')
subject = data.get('subject')
service = data.get('service')
self._populate_service_answers(data)
# Ignore communication channel for service deliveries (as it is not a valid field for
# service deliveries, but we are likely to get it in provided data anyway)
if kind == Interaction.Kind.SERVICE_DELIVERY:
data['communication_channel'] = None
# Look up values for adviser_1 and adviser_2 (adding errors if the look-up fails)
self._populate_adviser(data, 'adviser_1', 'team_1')
self._populate_adviser(data, 'adviser_2', 'team_2')
self._check_adviser_1_and_2_are_different(data)
# If no subject was provided, set it to the name of the service
if not subject and service:
data['subject'] = service.name
self._populate_contact(data)
self._validate_not_duplicate_of_prior_row(data)
self._validate_not_duplicate_of_existing_interaction(data)
return data
def full_clean(self):
"""
Performs full validation, additionally performing validation using the validators
from InteractionSerializer if the interaction was matched to a contact.
Errors are mapped to CSV fields where possible. If not possible, they are
added to NON_FIELD_ERRORS (but this should not happen).
"""
super().full_clean()
if not self.is_valid_and_matched():
return
transformed_data = self.cleaned_data_as_serializer_dict()
serializer = InteractionSerializer(context={'check_association_permissions': False})
try:
serializer.run_validators(transformed_data)
except serializers.ValidationError as exc:
# Make sure that errors are wrapped in a dict, and values are always a list
normalised_errors = serializers.as_serializer_error(exc)
for field, errors in normalised_errors.items():
self._add_serializer_error(field, errors)
@atomic
def save(self, user, source):
"""Creates an interaction from the cleaned data."""
serializer_data = self.cleaned_data_as_serializer_dict()
contacts = serializer_data.pop('contacts')
dit_participants = serializer_data.pop('dit_participants')
# Remove `is_event` if it's present as it's a computed field and isn't saved
# on the model
serializer_data.pop('is_event', None)
interaction = Interaction(
**serializer_data,
created_by=user,
modified_by=user,
source=source,
)
interaction.save()
interaction.contacts.add(*contacts)
for dit_participant in dit_participants:
InteractionDITParticipant(
interaction=interaction,
**dit_participant,
).save()
return interaction
def _add_serializer_error(self, field, errors):
mapped_field = self.SERIALIZER_FIELD_MAPPING.get(field, field)
if mapped_field in self.fields:
self.add_error(mapped_field, errors)
else:
mapped_errors = [
join_truthy_strings(field, error, sep=': ')
for error in errors
]
self.add_error(None, mapped_errors)
def _populate_adviser(self, data, adviser_field, team_field):
try:
data[adviser_field] = _look_up_adviser(
data.get(adviser_field),
data.get(team_field),
)
except ValidationError as exc:
self.add_error(adviser_field, exc)
def _populate_service_answers(self, data):
"""Transform service_answer into service_answers dictionary."""
service = data.get('service')
if not service:
return
service_answer = data.get('service_answer')
if not service.interaction_questions.exists():
if service_answer:
self.add_error(
'service_answer',
ValidationError(
SERVICE_ANSWER_NOT_REQUIRED,
code='service_answer_not_required',
),
)
# if service has no questions and answer is not provided, there is nothing to do
return
if not service_answer:
self.add_error(
'service_answer',
ValidationError(
SERVICE_ANSWER_REQUIRED,
code='service_answer_required',
),
)
return
try:
service_answer_option_db = ServiceAnswerOption.objects.get(
name__iexact=service_answer,
question__service=service,
)
data['service_answers'] = {
str(service_answer_option_db.question.pk): {
str(service_answer_option_db.pk): {},
},
}
except ServiceAnswerOption.DoesNotExist:
self.add_error(
'service_answer',
ValidationError(
SERVICE_ANSWER_NOT_FOUND,
code='service_answer_not_found',
),
)
@staticmethod
def _populate_contact(data):
"""Attempt to look up the contact using the provided email address."""
contact_email = data.get('contact_email')
if not contact_email:
# No contact email address was provided, or it did not pass validation.
# Skip the look-up in this case.
return
data['contact'], data['contact_matching_status'] = find_active_contact_by_email_address(
contact_email,
)
def _check_adviser_1_and_2_are_different(self, data):
adviser_1 = data.get('adviser_1')
adviser_2 = data.get('adviser_2')
if adviser_1 and adviser_1 == adviser_2:
err = ValidationError(
ADVISER_2_IS_THE_SAME_AS_ADVISER_1,
code='adviser_2_is_the_same_as_adviser_1',
)
self.add_error('adviser_2', err)
def _validate_not_duplicate_of_prior_row(self, data):
if not self.duplicate_tracker:
return
if self.duplicate_tracker.has_item(data):
self.add_error(None, DUPLICATE_OF_ANOTHER_ROW_MESSAGE)
return
self.duplicate_tracker.add_item(data)
def _validate_not_duplicate_of_existing_interaction(self, data):
if is_duplicate_of_existing_interaction(data):
self.add_error(None, DUPLICATE_OF_EXISTING_INTERACTION_MESSAGE)
def cleaned_data_as_serializer_dict(self):
"""
Transforms cleaned data into a dict suitable for use with the validators from
InteractionSerializer.
"""
data = self.cleaned_data
if not self.is_matched():
raise DataHubError('Cannot create a serializer dict for an unmatched contact')
subject = data.get('subject') or data['service'].name
dit_participants = [
{
'adviser': adviser,
'team': adviser.dit_team,
}
for adviser in (data['adviser_1'], data.get('adviser_2'))
if adviser
]
creation_data = {
'contacts': [data['contact']],
'communication_channel': data.get('communication_channel'),
'company': data['contact'].company,
'date': datetime.combine(data['date'], time(), tzinfo=utc),
'dit_participants': dit_participants,
'event': data.get('event_id'),
'kind': data['kind'],
'notes': data.get('notes'),
'service': data['service'],
'service_answers': data.get('service_answers'),
'status': Interaction.Status.COMPLETE,
'subject': subject,
'theme': data['theme'],
'was_policy_feedback_provided': False,
'were_countries_discussed': False,
}
if data['kind'] == Interaction.Kind.SERVICE_DELIVERY:
creation_data['is_event'] = bool(data.get('event_id'))
return creation_data
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',
}