class SearchInteractionExportAPIView(SearchInteractionParams, SearchExportAPIView): """Filtered interaction search export view.""" queryset = DBInteraction.objects.annotate( company_link=get_front_end_url_expression('company', 'company__pk'), company_sector_name=get_sector_name_subquery('company__sector'), contact_name=get_full_name_expression('contact'), dit_adviser_name=get_full_name_expression('dit_adviser'), link=get_front_end_url_expression('interaction', 'pk'), kind_name=get_choices_as_case_expression(DBInteraction, 'kind'), ) field_titles = { 'date': 'Date', 'kind_name': 'Type', 'service__name': 'Service', 'subject': 'Subject', 'link': 'Link', 'company__name': 'Company', 'company_link': 'Company link', 'company__registered_address_country__name': 'Company country', 'company__uk_region__name': 'Company UK region', 'company_sector_name': 'Company sector', 'contact_name': 'Contact', 'contact__job_title': 'Contact job title', 'dit_adviser_name': 'Adviser', 'dit_team__name': 'Service provider', 'event__name': 'Event', 'service_delivery_status__name': 'Service delivery status', 'net_company_receipt': 'Net company receipt', }
class SearchOrderExportAPIView(SearchOrderParams, SearchExportAPIView): """Order search export view.""" queryset = DBOrder.objects.annotate( subtotal_in_pounds=Cast( 'subtotal_cost', DecimalField(max_digits=19, decimal_places=2), ) / 100, # This follows the example from # https://docs.djangoproject.com/en/2.1/ref/models/expressions/#using-aggregates-within-a-subquery-expression net_refund_in_pounds=Subquery( Refund.objects.filter( order=OuterRef('pk'), status=RefundStatus.approved, ).order_by( ).values( 'order', ).annotate( total_refund=Cast( Sum('net_amount'), DecimalField(max_digits=19, decimal_places=2), ) / 100, ).values( 'total_refund', ), output_field=DecimalField(max_digits=19, decimal_places=2), ), status_name=get_choices_as_case_expression(DBOrder, 'status'), link=get_front_end_url_expression('order', 'pk'), sector_name=get_sector_name_subquery('sector'), company_link=get_front_end_url_expression('company', 'company__pk'), contact_name=get_full_name_expression('contact'), contact_link=get_front_end_url_expression('contact', 'contact__pk'), ) field_titles = { 'reference': 'Order reference', 'subtotal_in_pounds': 'Net price', 'net_refund_in_pounds': 'Net refund', 'status_name': 'Status', 'link': 'Link', 'sector_name': 'Sector', 'primary_market__name': 'Market', 'uk_region__name': 'UK region', 'company__name': 'Company', 'company__registered_address_country__name': 'Company country', 'company__uk_region__name': 'Company UK region', 'company_link': 'Company link', 'contact_name': 'Contact', 'contact__job_title': 'Contact job title', 'contact_link': 'Contact link', 'created_by__dit_team__name': 'Created by team', 'created_on': 'Date created', 'delivery_date': 'Delivery date', 'quote__created_on': 'Date quote sent', 'quote__accepted_on': 'Date quote accepted', 'paid_on': 'Date payment received', 'completed_on': 'Date completed', }
class SearchInteractionExportAPIView(SearchInteractionAPIViewMixin, SearchExportAPIView): """Filtered interaction search export view.""" queryset = DBInteraction.objects.annotate( company_link=get_front_end_url_expression('company', 'company__pk'), company_sector_name=get_sector_name_subquery('company__sector'), contact_names=get_string_agg_subquery( DBInteraction, get_full_name_expression( person_field_name='contacts', bracketed_field_name='job_title', ), ), adviser_names=get_string_agg_subquery( DBInteraction, get_bracketed_concat_expression( 'dit_participants__adviser__first_name', 'dit_participants__adviser__last_name', expression_to_bracket='dit_participants__team__name', ), ), link=get_front_end_url_expression('interaction', 'pk'), kind_name=get_choices_as_case_expression(DBInteraction, 'kind'), policy_issue_type_names=get_string_agg_subquery( DBInteraction, 'policy_issue_types__name', ), policy_area_names=get_string_agg_subquery( DBInteraction, 'policy_areas__name', # Some policy areas contain commas, so we use a semicolon to delimit multiple values delimiter='; ', ), service_name=get_service_name_subquery('service'), ) field_titles = { 'date': 'Date', 'kind_name': 'Type', 'service_name': 'Service', 'subject': 'Subject', 'link': 'Link', 'company__name': 'Company', 'company_link': 'Company link', 'company__address_country__name': 'Company country', 'company__uk_region__name': 'Company UK region', 'company_sector_name': 'Company sector', 'contact_names': 'Contacts', 'adviser_names': 'Advisers', 'event__name': 'Event', 'communication_channel__name': 'Communication channel', 'service_delivery_status__name': 'Service delivery status', 'net_company_receipt': 'Net company receipt', 'policy_issue_type_names': 'Policy issue types', 'policy_area_names': 'Policy areas', 'policy_feedback_notes': 'Policy feedback notes', }
def test_get_choices_as_case_expression(genre): """ Test that get_choices_as_case_expression() generates display names for a field with choices the same way that model_obj.get_FIELD_display() does. """ book = BookFactory(genre=genre) queryset = Book.objects.annotate(genre_name=get_choices_as_case_expression( Book, 'genre'), ) annotated_book = queryset.first() assert annotated_book.genre_name == book.get_genre_display()
def test_get_collapse_project_status_expression(status, expected): """Tests if Ongoing and Won status get the same value.""" InvestmentProjectFactory(status=status, ) query = InvestmentProject.objects.annotate( status_name=get_choices_as_case_expression(InvestmentProject, 'status'), project_status=get_collapse_status_name_expression(), ).values('project_status') investment_project = query.first() assert investment_project['project_status'] == expected
def get_source_query(self): """Get the query set.""" return InvestmentProject.objects.annotate( # this contains helper annotations _possible_uk_region_names=get_string_agg_subquery( InvestmentProject, 'uk_region_locations__name', ), _actual_uk_region_names=get_string_agg_subquery( InvestmentProject, 'actual_uk_regions__name', ), ).annotate( project_reference=get_project_code_expression(), status_name=get_choices_as_case_expression(InvestmentProject, 'status'), status_collapsed=get_collapse_status_name_expression(), project_url=get_front_end_url_expression('investmentproject', 'pk'), sector_name=Coalesce( get_sector_name_subquery('sector'), Value(NO_SECTOR_ASSIGNED), ), top_level_sector_name=get_top_level_sector_expression(), possible_uk_region_names=Coalesce( '_possible_uk_region_names', Value(NO_UK_REGION_ASSIGNED), ), actual_uk_region_names=Coalesce( '_actual_uk_region_names', Value(NO_UK_REGION_ASSIGNED), ), project_fdi_value=Coalesce('fdi_value__name', Value(NO_FDI_VALUE_ASSIGNED)), sector_cluster=get_sector_cluster_expression('sector'), uk_region_name=get_other_field_if_null_or_empty_expression( '_actual_uk_region_names', '_possible_uk_region_names', default=Value(NO_UK_REGION_ASSIGNED), ), land_date=Coalesce( 'actual_land_date', 'estimated_land_date', ), financial_year=get_financial_year_from_land_date_expression(), dh_fdi_project_id=F('id'), investment_type_name=get_empty_string_if_null_expression( 'investment_type__name'), level_of_involvement_name=get_empty_string_if_null_expression( 'level_of_involvement__name', ), simplified_level_of_involvement= get_level_of_involvement_simplified_expression(), overseas_region=get_empty_string_if_null_expression( 'investor_company__address_country__overseas_region__name', ), country_url=get_country_url(), investor_company_country=get_empty_string_if_null_expression( 'investor_company__address_country__name', ), stage_name=F('stage__name'), total_investment_with_zero=Coalesce('total_investment', Value(0)), number_safeguarded_jobs_with_zero=Coalesce( 'number_safeguarded_jobs', Value(0)), number_new_jobs_with_zero=Coalesce('number_new_jobs', Value(0)), )
class 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 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 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