def get_path_fragment_loopback(source_device, address, all_links):
try:
destination_device = Device.objects.get(
physicalinterface__name="lo0",
physicalinterface__logicalinterface__address=address)
multi_links = (all_links.filter(
local_interface__aggregate_interface__isnull=False).values(
"local_interface__device",
"remote_interface__device",
"local_interface__aggregate_interface",
"remote_interface__aggregate_interface",
).annotate(connection_id=StringAgg(
Cast("id", TextField()),
delimiter="_",
ordering="id",
output_field=TextField(),
)))
single_links = all_links.filter(
local_interface__aggregate_interface__isnull=True)
single_links_same_direction = single_links.filter(
local_interface__device=source_device,
remote_interface__device=destination_device).values(
connection_id=Cast("id", TextField()),
source_device_name=F("local_interface__device__name"),
destination_device=F("remote_interface__device"),
destination_device_name=F("remote_interface__device__name"),
direction=Value("same", TextField()),
)
single_links_opposite_direction = single_links.filter(
remote_interface__device=source_device,
local_interface__device=destination_device).values(
connection_id=Cast("id", TextField()),
source_device_name=F("remote_interface__device__name"),
destination_device=F("local_interface__device"),
destination_device_name=F("local_interface__device__name"),
direction=Value("opposite", TextField()),
)
multi_links_same_direction = multi_links.filter(
local_interface__device=source_device,
remote_interface__device=destination_device).values(
"connection_id",
source_device_name=F("local_interface__device__name"),
destination_device=F("remote_interface__device"),
destination_device_name=F("remote_interface__device__name"),
direction=Value("same", TextField()),
)
multi_links_opposite_direction = multi_links.filter(
remote_interface__device=source_device,
local_interface__device=destination_device).values(
"connection_id",
source_device_name=F("remote_interface__device__name"),
destination_device=F("local_interface__device"),
destination_device_name=F("local_interface__device__name"),
direction=Value("opposite", TextField()),
)
connections = single_links_same_direction.union(
single_links_opposite_direction, multi_links_same_direction,
multi_links_opposite_direction).order_by("connection_id")
connection_ids = []
first_connection = None
for connection in connections:
connection_ids.append(
f"{connection['connection_id']}:{connection['direction']}")
if not first_connection:
first_connection = connection
if not connection_ids:
return None
separator = "&"
fragment_id = separator.join(connection_ids)
return {
"information": {
"id":
fragment_id,
"details":
f"{first_connection['source_device_name']} - {first_connection['destination_device_name']}",
},
"destination_device": first_connection["destination_device"],
}
except Device.DoesNotExist:
return None
except Device.MultipleObjectsReturned:
return None
def test_string_agg_distinct_true(self):
values = AggregateTestModel.objects.aggregate(
stringagg=StringAgg('char_field', delimiter=' ', distinct=True))
self.assertEqual(values['stringagg'].count('Foo'), 1)
self.assertEqual(values['stringagg'].count('Bar'), 1)
def sort_by_attribute(self,
attribute_pk: Union[int, str],
descending: bool = False):
"""Sort a query set by the values of the given product attribute.
:param attribute_pk: The database ID (must be a numeric) of the attribute
to sort by.
:param descending: The sorting direction.
"""
from ..attribute.models import AttributeProduct, AttributeValue
qs: models.QuerySet = self
# If the passed attribute ID is valid, execute the sorting
if not (isinstance(attribute_pk, int) or attribute_pk.isnumeric()):
return qs.annotate(
concatenated_values_order=Value(
None, output_field=models.IntegerField()),
concatenated_values=Value(None,
output_field=models.CharField()),
)
# Retrieve all the products' attribute data IDs (assignments) and
# product types that have the given attribute associated to them
associated_values = tuple(
AttributeProduct.objects.filter(
attribute_id=attribute_pk).values_list("pk",
"product_type_id"))
if not associated_values:
qs = qs.annotate(
concatenated_values_order=Value(
None, output_field=models.IntegerField()),
concatenated_values=Value(None,
output_field=models.CharField()),
)
else:
attribute_associations, product_types_associated_to_attribute = zip(
*associated_values)
qs = qs.annotate(
# Contains to retrieve the attribute data (singular) of each product
# Refer to `AttributeProduct`.
filtered_attribute=FilteredRelation(
relation_name="attributes",
condition=Q(
attributes__assignment_id__in=attribute_associations),
),
# Implicit `GROUP BY` required for the `StringAgg` aggregation
grouped_ids=Count("id"),
# String aggregation of the attribute's values to efficiently sort them
concatenated_values=Case(
# If the product has no association data but has
# the given attribute associated to its product type,
# then consider the concatenated values as empty (non-null).
When(
Q(product_type_id__in=
product_types_associated_to_attribute)
& Q(filtered_attribute=None),
then=models.Value(""),
),
default=StringAgg(
F("filtered_attribute__values__name"),
delimiter=",",
ordering=([
f"filtered_attribute__values__{field_name}" for
field_name in AttributeValue._meta.ordering or []
]),
),
output_field=models.CharField(),
),
concatenated_values_order=Case(
# Make the products having no such attribute be last in the sorting
When(concatenated_values=None, then=2),
# Put the products having an empty attribute value at the bottom of
# the other products.
When(concatenated_values="", then=1),
# Put the products having an attribute value to be always at the top
default=0,
output_field=models.IntegerField(),
),
)
# Sort by concatenated_values_order then
# Sort each group of products (0, 1, 2, ...) per attribute values
# Sort each group of products by name,
# if they have the same values or not values
ordering = "-" if descending else ""
return qs.order_by(
f"{ordering}concatenated_values_order",
f"{ordering}concatenated_values",
f"{ordering}name",
)
def test_string_agg_requires_delimiter(self):
with self.assertRaises(TypeError):
AggregateTestModel.objects.aggregate(
stringagg=StringAgg('char_field'))
def test_string_agg_charfield(self):
values = AggregateTestModel.objects.aggregate(
stringagg=StringAgg('char_field', delimiter=';'))
self.assertEqual(values, {'stringagg': 'Foo1;Foo2;Foo4;Foo3'})
def transaction_search_annotations():
annotation_fields = {
"action_date_fiscal_year":
FiscalYear("action_date"),
"treasury_accounts_funding_this_award":
Subquery(
Award.objects.filter(id=OuterRef("award_id")).annotate(
value=StringAgg(
"financial_set__treasury_account__tas_rendering_label",
";",
distinct=True)).values("value"),
output_field=TextField(),
),
"federal_accounts_funding_this_award":
Subquery(
Award.objects.filter(id=OuterRef("award_id")).
annotate(value=StringAgg(
"financial_set__treasury_account__federal_account__federal_account_code",
";",
distinct=True)).values("value"),
output_field=TextField(),
),
"usaspending_permalink":
ConcatAll(
Value(AWARD_URL),
Func(F("transaction__award__generated_unique_award_id"),
function="urlencode"), Value("/")),
"disaster_emergency_fund_codes_for_overall_award":
Case(
When(
transaction__action_date__gte=datetime.date(2020, 4, 1),
then=Subquery(
FinancialAccountsByAwards.objects.filter(
filter_limit_to_closed_periods(),
award_id=OuterRef("award_id")).
annotate(value=ExpressionWrapper(
Case(
When(
disaster_emergency_fund__code__isnull=False,
then=ConcatAll(
F("disaster_emergency_fund__code"),
Value(": "),
F("disaster_emergency_fund__public_law"),
),
),
default=Value(None, output_field=TextField()),
output_field=TextField(),
),
output_field=TextField(),
)).values("award_id").annotate(total=StringAgg(
"value", ";", distinct=True)).values("total"),
output_field=TextField(),
),
)),
"outlayed_amount_funded_by_COVID-19_supplementals_for_overall_award":
Case(
When(
transaction__action_date__gte=datetime.date(2020, 4, 1),
then=_covid_outlay_subquery(),
), ),
"obligated_amount_funded_by_COVID-19_supplementals_for_overall_award":
Case(
When(
transaction__action_date__gte=datetime.date(2020, 4, 1),
then=_covid_obligation_subquery(),
), ),
"object_classes_funding_this_award":
Subquery(
FinancialAccountsByAwards.objects.filter(
filter_limit_to_closed_periods(),
award_id=OuterRef("award_id"),
object_class_id__isnull=False).annotate(
value=ExpressionWrapper(
ConcatAll(F("object_class__object_class"), Value(": "),
F("object_class__object_class_name")),
output_field=TextField(),
)).values("award_id").annotate(total=StringAgg(
"value", ";", distinct=True)).values("total"),
output_field=TextField(),
),
"program_activities_funding_this_award":
Subquery(
FinancialAccountsByAwards.objects.filter(
filter_limit_to_closed_periods(),
award_id=OuterRef("award_id"),
program_activity_id__isnull=False).annotate(
value=ExpressionWrapper(
ConcatAll(
F("program_activity__program_activity_code"),
Value(": "),
F("program_activity__program_activity_name"),
),
output_field=TextField(),
)).values("award_id").annotate(total=StringAgg(
"value", ";", distinct=True)).values("total"),
output_field=TextField(),
),
}
return annotation_fields
def get_queryset(self):
qs = self.queryset
protocol_condition = Case(
When(benthiclit__id__isnull=False,
then=Value(mermaid.BENTHICLIT_PROTOCOL)),
When(benthicpit__id__isnull=False,
then=Value(mermaid.BENTHICPIT_PROTOCOL)),
When(
habitatcomplexity__id__isnull=False,
then=Value(mermaid.HABITATCOMPLEXITY_PROTOCOL),
),
When(
bleachingquadratcollection__id__isnull=False,
then=Value(mermaid.BLEACHINGQC_PROTOCOL),
),
When(beltfish__id__isnull=False,
then=Value(mermaid.FISHBELT_PROTOCOL)),
output_field=CharField(),
)
site_name_condition = Case(
When(
benthiclit__transect__sample_event__site__name__isnull=False,
then="benthiclit__transect__sample_event__site__name",
),
When(
benthicpit__transect__sample_event__site__name__isnull=False,
then="benthicpit__transect__sample_event__site__name",
),
When(
habitatcomplexity__transect__sample_event__site__name__isnull=
False,
then="habitatcomplexity__transect__sample_event__site__name",
),
When(
bleachingquadratcollection__quadrat__sample_event__site__name__isnull
=False,
then=
"bleachingquadratcollection__quadrat__sample_event__site__name",
),
When(
beltfish__transect__sample_event__site__name__isnull=False,
then="beltfish__transect__sample_event__site__name",
),
)
management_name_condition = Case(
When(
benthiclit__transect__sample_event__management__name__isnull=
False,
then="benthiclit__transect__sample_event__management__name",
),
When(
benthicpit__transect__sample_event__management__name__isnull=
False,
then="benthicpit__transect__sample_event__management__name",
),
When(
habitatcomplexity__transect__sample_event__management__name__isnull
=False,
then=
"habitatcomplexity__transect__sample_event__management__name",
),
When(
bleachingquadratcollection__quadrat__sample_event__management__name__isnull
=False,
then=
"bleachingquadratcollection__quadrat__sample_event__management__name",
),
When(
beltfish__transect__sample_event__management__name__isnull=
False,
then="beltfish__transect__sample_event__management__name",
),
)
sample_unit_number_condition = Case(
When(
benthiclit__transect__number__isnull=False,
then="benthiclit__transect__number",
),
When(
benthicpit__transect__number__isnull=False,
then="benthicpit__transect__number",
),
When(
habitatcomplexity__transect__number__isnull=False,
then="habitatcomplexity__transect__number",
),
When(
beltfish__transect__number__isnull=False,
then="beltfish__transect__number",
),
)
depth_condition = Case(
When(
benthiclit__transect__sample_event__depth__isnull=False,
then="benthiclit__transect__sample_event__depth",
),
When(
benthicpit__transect__sample_event__depth__isnull=False,
then="benthicpit__transect__sample_event__depth",
),
When(
habitatcomplexity__transect__sample_event__depth__isnull=False,
then="habitatcomplexity__transect__sample_event__depth",
),
When(
bleachingquadratcollection__quadrat__sample_event__depth__isnull
=False,
then="bleachingquadratcollection__quadrat__sample_event__depth",
),
When(
beltfish__transect__sample_event__depth__isnull=False,
then="beltfish__transect__sample_event__depth",
),
)
sample_date_condition = Case(
When(
benthiclit__transect__sample_event__sample_date__isnull=False,
then="benthiclit__transect__sample_event__sample_date",
),
When(
benthicpit__transect__sample_event__sample_date__isnull=False,
then="benthicpit__transect__sample_event__sample_date",
),
When(
habitatcomplexity__transect__sample_event__sample_date__isnull=
False,
then="habitatcomplexity__transect__sample_event__sample_date",
),
When(
bleachingquadratcollection__quadrat__sample_event__sample_date__isnull
=False,
then=
"bleachingquadratcollection__quadrat__sample_event__sample_date",
),
When(
beltfish__transect__sample_event__sample_date__isnull=False,
then="beltfish__transect__sample_event__sample_date",
),
)
size_condition = Case(
When(
benthiclit__id__isnull=False,
then=Concat(
Cast("benthiclit__transect__len_surveyed", CharField()),
Value("m")),
),
When(
benthicpit__id__isnull=False,
then=Concat(
Cast("benthicpit__transect__len_surveyed", CharField()),
Value("m")),
),
When(
habitatcomplexity__id__isnull=False,
then=Concat(
Cast("habitatcomplexity__transect__len_surveyed",
CharField()),
Value("m"),
),
),
When(
beltfish__id__isnull=False,
then=Concat(
Cast("beltfish__transect__len_surveyed", CharField()),
Value("m x "),
Cast("beltfish__transect__width__name", CharField()),
),
),
When(
bleachingquadratcollection__id__isnull=False,
then=Concat(
Cast("bleachingquadratcollection__quadrat__quadrat_size",
CharField()),
Value("m"),
),
),
)
observers = StringAgg(
Concat(
Cast("observers__profile__first_name", CharField()),
Value(" "),
Cast("observers__profile__last_name", CharField()),
),
delimiter=", ",
distinct=True,
)
qs = qs.annotate(
protocol_name=protocol_condition,
site_name=site_name_condition,
management_name=management_name_condition,
sample_unit_number=sample_unit_number_condition,
depth=depth_condition,
sample_date=sample_date_condition,
size_display=size_condition,
observers_display=observers,
)
return qs
def get(self, request):
field_aliases = {
"agency_code":
F("agency__abbrev"),
"species_code":
F("species__abbrev"),
"strain":
F("strain_raw__strain__strain_code"),
"grid10":
F("grid_10__slug"),
"lifestage_code":
F("lifestage__abbrev"),
"stockingMethod":
F("stocking_method__stk_meth"),
"jurisdiction_code":
F("jurisdiction__slug"),
"man_unit":
F("management_unit__slug"),
"lake":
F("jurisdiction__lake__abbrev"),
"stateProv":
F("jurisdiction__stateprov__abbrev"),
"latitude":
F("geom_lat"),
"longitude":
F("geom_lon"),
"clip":
F("clip_code__clip_code"),
"_tags":
StringAgg("fish_tags__tag_code",
delimiter=";",
ordering="fish_tags__tag_code"),
"_marks":
F("physchem_marks__mark_code"),
# "_marks": StringAgg(
# "physchem_marks__mark_code",
# delimiter=";",
# ordering="physchem_marks__mark_code",
# ),
}
fields = [
"stock_id",
"lake",
"jurisdiction_code",
"man_unit",
"stateProv",
"grid10",
"latitude",
"longitude",
"st_site",
"year",
"date",
"month",
"clip",
"_tags",
"_marks",
"year_class",
"agency_code",
"species_code",
"strain",
"lifestage_code",
"stockingMethod",
"no_stocked",
"yreq_stocked",
]
queryset = (StockingEvent.objects.defer(
"jurisdiction__geom",
"jurisdiction__lake__geom",
"mangement_unit__geom",
"mangement_unit__lake__geom",
"mangement_unit__jurisdiction__lake__geom",
"grid_10__geom",
"grid_10__lake__geom",
).select_related(
"agency",
"species",
"strain",
"lifestage",
"grid_10",
"stocking_method",
"management_unit",
"jurisdiction",
"jurisdiction__lake",
"jurisdiction__stateprov",
).annotate(**field_aliases))
filtered = StockingEventFilter(request.GET,
queryset=queryset).qs.values(*fields)
maxEvents = settings.MAX_FILTERED_EVENT_COUNT
return Response(filtered[:maxEvents])
def sort_by_attribute(self,
attribute_pk: Union[int, str],
ascending: bool = True):
"""Sort a query set by the values of the given product attribute.
:param attribute_pk: The database ID (must be a number) of the attribute
to sort by.
:param ascending: The sorting direction.
"""
qs: models.QuerySet = self
# Retrieve all the products' attribute data IDs (assignments) and
# product types that have the given attribute associated to them
attribute_associations, product_types_associated_to_attribute = zip(
*AttributeProduct.objects.filter(attribute_id=attribute_pk).
values_list("pk", "product_type_id"))
qs = qs.annotate(
# Contains to retrieve the attribute data (singular) of each product
# Refer to `AttributeProduct`.
filtered_attribute=FilteredRelation(
relation_name="attributes",
condition=Q(
attributes__assignment_id__in=attribute_associations),
),
# Implicit `GROUP BY` required for the `StringAgg` aggregation
grouped_ids=Count("id"),
# String aggregation of the attribute's values to efficiently sort them
concatenated_values=Case(
# If the product has no association data but has the given attribute
# associated to its product type, then consider the concatenated values
# as empty (non-null).
When(
Q(product_type_id__in=product_types_associated_to_attribute
)
& Q(filtered_attribute=None),
then=models.Value(""),
),
default=StringAgg(
F("filtered_attribute__values__name"),
delimiter=",",
ordering=([
f"filtered_attribute__values__{field_name}"
for field_name in AttributeValue._meta.ordering
]),
),
output_field=models.CharField(),
),
)
qs = qs.extra(order_by=[
Case(
# Make the products having no such attribute be last in the sorting
When(concatenated_values=None, then=2),
# Put the products having an empty attribute value at the bottom of
# the other products.
When(concatenated_values="", then=1),
# Put the products having an attribute value to be always at the top
default=0,
output_field=models.IntegerField(),
),
# Sort each group of products (0, 1, 2, ...) per attribute values
"concatenated_values",
# Sort each group of products by name,
# if they have the same values or not values
"name",
])
# Descending sorting
if not ascending:
return qs.reverse()
return qs
def with_documents(self):
vector = (SearchVector('text', weight='A') +
SearchVector(StringAgg('hashtags__text_lower', delimiter=' '), weight='A') +
SearchVector(StringAgg('urls__expanded_url', delimiter=' '), weight='B'))
return self.get_queryset().annotate(document=vector)
def get_queryset(self):
field_aliases = {
"glfsd_stock_id": F("stock_id"),
"agency_code": F("agency__abbrev"),
"_lake": F("jurisdiction__lake__abbrev"),
"state_prov": F("jurisdiction__stateprov__abbrev"),
"manUnit": F("management_unit__label"),
"grid_10min": F("grid_10__grid"),
"location_primary": F("st_site"),
"location_secondary": F("site"),
"latitude": F("dd_lat"),
"longitude": F("dd_lon"),
"stock_method": F("stocking_method__stk_meth"),
"species_code": F("species__abbrev"),
"_strain": F("strain_raw__strain__strain_code"),
"yearclass": F("year_class"),
"life_stage": F("lifestage__abbrev"),
"age_months": F("agemonth"),
"_clip": F("clip_code__clip_code"),
"phys_chem_mark": F("physchem_marks"),
"cwt_number": F("tag_no"),
"tag_retention": F("tag_ret"),
"mean_length_mm": F("length"),
"total_weight_kg": F("weight"),
"stocking_mortality": F("condition__condition"),
"lot_code": F("lotcode"),
"hatchery_abbrev": F("hatchery__abbrev"),
"number_stocked": F("no_stocked"),
"tag_type": StringAgg("fish_tags__tag_code", ""),
}
fields = [
"glfsd_stock_id",
"agency_stock_id",
"agency_code",
"_lake",
"state_prov",
"manUnit",
"grid_10min",
"location_primary",
"location_secondary",
"latitude",
"longitude",
"year",
"month",
"day",
"stock_method",
"species_code",
"_strain",
"yearclass",
"life_stage",
"age_months",
"_clip",
"clip_efficiency",
"phys_chem_mark",
"tag_type",
"cwt_number",
"tag_retention",
"mean_length_mm",
"total_weight_kg",
"stocking_mortality",
"lot_code",
"hatchery_abbrev",
"number_stocked",
"notes",
]
queryset = (StockingEvent.objects.select_related(
"jurisdiction",
"agency",
"species",
"strain_raw__strain",
"lifestage",
"condition",
"grid_10",
"stocking_method",
"hatchery",
"fish_tags",
"jurisdiction__lake",
"jurisdiction__stateprov",
).defer(
"jurisdiction__geom",
"jurisdiction__lake__geom",
"grid_10__geom",
"grid_10__lake__geom",
).order_by("-year",
"stock_id").annotate(**field_aliases).values(*fields))
filtered = StockingEventFilter(self.request.GET,
queryset=queryset).qs.values(*fields)
return filtered
from django.contrib.postgres.aggregates import StringAgg
from django.contrib.postgres.search import (
SearchQuery,
SearchRank,
SearchVector,
TrigramSimilarity,
)
from django.db import models
search_vectors = (SearchVector("nickname", weight="A", config="english") +
SearchVector(StringAgg("genres__name", delimiter=" "),
weight="B",
config="english") +
SearchVector("description", weight="D", config="english"))
class BandManager(models.Manager):
def search(self, text):
search_query = SearchQuery(text, config="english")
search_rank = SearchRank(search_vectors, search_query)
trigram_similarity = TrigramSimilarity("nickname", text)
return (self.get_queryset().annotate(search=search_vectors).filter(
search=search_query).annotate(
rank=search_rank + trigram_similarity).order_by("-rank"))
from django.contrib.postgres.aggregates import StringAgg
from django.contrib.postgres.indexes import GinIndex
from django.contrib.postgres.search import (SearchQuery, SearchRank,
SearchVector, SearchVectorField,
TrigramSimilarity)
from django.db import models
from django.urls import reverse
# Create your models here.
search_vectors = (
SearchVector('title', weight='A')
+ SearchVector('content', weight='B')
+ SearchVector(StringAgg('content', delimiter=' '), weight='D')
)
class PageManager(models.Manager):
def search(self, text):
print("using this one")
search_query = SearchQuery(text)
search_rank = SearchRank(search_vectors, search_query)
trigram_similarity = TrigramSimilarity('content', text)
return (
self.get_queryset()
.annotate(search=search_vectors)
.filter(search=search_query)
.annotate(rank=search_rank + trigram_similarity)
.order_by('-rank')
)
from django.contrib.postgres.aggregates import StringAgg
from django.contrib.postgres.search import (
SearchQuery, SearchRank, SearchVector, TrigramSimilarity,
)
from django.db import models
search_vectors = (
SearchVector('short_name', weight='A', config='english') +
SearchVector(StringAgg('shortDescrip', delimiter=' '), weight='B', config='english') +
SearchVector('long_name', weight='D', config='english')
)
class ProduceSearchManager(models.Manager):
def search(self, text):
search_query = SearchQuery(text, config='english')
search_rank = SearchRank(search_vectors, search_query)
trigram_similarity = TrigramSimilarity('short_name', text)
return self.get_queryset().annotate(
search=search_vectors
).filter(
search=search_query
).annotate(
rank=search_rank + trigram_similarity
).order_by('-rank')
def duty_sentence(
self,
component_parent: Union["measures.Measure",
"measures.MeasureCondition"],
):
"""
Returns the human-readable "duty sentence" for a Measure or
MeasureCondition instance as a string. The returned string value is a
(Postgresql) string aggregation of all the measure's "current"
components.
This operation relies on the `prefix` and `abbreviation` fields being
filled in on duty expressions and units, which are not supplied by the
TARIC3 XML by default.
Strings output by this aggregation should be valid input to the
:class:`~measures.parsers.DutySentenceParser`.
The string aggregation will be generated using the below SQL:
.. code:: SQL
STRING_AGG(
TRIM(
CONCAT(
CASE
WHEN (
"measures_dutyexpression"."prefix" IS NULL
OR "measures_dutyexpression"."prefix" = ''
) THEN
ELSE CONCAT("measures_dutyexpression"."prefix",' ')
END,
CONCAT(
"measures_measureconditioncomponent"."duty_amount",
CONCAT(
CASE
WHEN (
"measures_measureconditioncomponent"."duty_amount" IS NOT NULL
AND "measures_measureconditioncomponent"."monetary_unit_id" IS NULL
) THEN '%'
WHEN "measures_measureconditioncomponent"."duty_amount" IS NULL THEN ''
ELSE CONCAT(' ', "measures_monetaryunit"."code")
END,
CONCAT(
CASE
WHEN "measures_measurementunit"."abbreviation" IS NULL THEN ''
WHEN "measures_measureconditioncomponent"."monetary_unit_id" IS NULL THEN "measures_measurementunit"."abbreviation"
ELSE CONCAT(' / ', "measures_measurementunit"."abbreviation")
END,
CASE
WHEN "measures_measurementunitqualifier"."abbreviation" IS NULL THEN
ELSE CONCAT(
' / ',
"measures_measurementunitqualifier"."abbreviation"
)
END
)
)
)
)
),
) AS "duty_sentence"
"""
# Components with the greatest transaction_id that is less than
# or equal to component_parent's transaction_id, are considered 'current'.
component_qs = component_parent.components.approved_up_to_transaction(
component_parent.transaction, )
if not component_qs:
return ""
latest_transaction_id = component_qs.aggregate(
latest_transaction_id=Max(
"transaction_id", ), ).get("latest_transaction_id")
component_qs = component_qs.filter(
transaction_id=latest_transaction_id)
# Aggregate all the current Components for component_parent to form its
# duty sentence.
duty_sentence = component_qs.aggregate(duty_sentence=StringAgg(
expression=Trim(
Concat(
Case(
When(
Q(duty_expression__prefix__isnull=True)
| Q(duty_expression__prefix=""),
then=Value(""),
),
default=Concat(
F("duty_expression__prefix"),
Value(" "),
),
),
"duty_amount",
Case(
When(
monetary_unit=None,
duty_amount__isnull=False,
then=Value("%"),
),
When(
duty_amount__isnull=True,
then=Value(""),
),
default=Concat(
Value(" "),
F("monetary_unit__code"),
),
),
Case(
When(
Q(component_measurement=None)
| Q(component_measurement__measurement_unit=None)
|
Q(component_measurement__measurement_unit__abbreviation
=None, ),
then=Value(""),
),
When(
monetary_unit__isnull=True,
then=F(
"component_measurement__measurement_unit__abbreviation",
),
),
default=Concat(
Value(" / "),
F(
"component_measurement__measurement_unit__abbreviation",
),
),
),
Case(
When(
component_measurement__measurement_unit_qualifier__abbreviation
=None,
then=Value(""),
),
default=Concat(
Value(" / "),
F(
"component_measurement__measurement_unit_qualifier__abbreviation",
),
),
),
output_field=CharField(),
), ),
delimiter=" ",
ordering="duty_expression__sid",
), )
return duty_sentence.get("duty_sentence", "")
class Condition(TimeStampedModel):
name = models.CharField(max_length=512)
def __str__(self):
return self.name
search_vectors = (
SearchVector('sku', weight='A', config='english') +
SearchVector('title', weight='B', config='english') +
SearchVector('brand__name', weight='C', config='english') +
SearchVector('size__name', weight='D', config='english') +
SearchVector('condition__name', weight='D', config='english') +
SearchVector(StringAgg('colors__name', delimiter=' '),
weight='D',
config='english'))
class Category(MPTTModel):
name = models.TextField(blank=True, null=True)
parent = TreeForeignKey('Category',
blank=True,
null=True,
related_name="children",
db_index=True,
on_delete=models.PROTECT)
class MPTTMeta:
order_insertion_by = ['name']
def get_datasets_data_for_user_matching_query(datasets: QuerySet,
query,
use=None,
user=None,
id_field='id'):
"""
Filters the dataset queryset for:
1) visibility (whether the user can know if the dataset exists)
2) matches the search terms
Annotates the dataset queryset with:
1) `has_access`, if the user can use the dataset's data.
"""
is_reference_query = datasets.model is ReferenceDataset
# Filter out datasets that the user is not allowed to even know about.
visibility_filter = Q(published=True)
if user:
if is_reference_query:
reference_type = DataSetType.REFERENCE.value
reference_perm = dataset_type_to_manage_unpublished_permission_codename(
reference_type)
if user.has_perm(reference_perm):
visibility_filter |= Q(published=False)
if datasets.model is DataSet:
master_type, datacut_type = (
DataSetType.MASTER.value,
DataSetType.DATACUT.value,
)
master_perm = dataset_type_to_manage_unpublished_permission_codename(
master_type)
datacut_perm = dataset_type_to_manage_unpublished_permission_codename(
datacut_type)
if user.has_perm(master_perm):
visibility_filter |= Q(published=False, type=master_type)
if user.has_perm(datacut_perm):
visibility_filter |= Q(published=False, type=datacut_type)
datasets = datasets.filter(visibility_filter)
# Filter out datasets that don't match the search terms
search = (SearchVector('name', weight='A', config='english') +
SearchVector('short_description', weight='B', config='english') +
SearchVector(StringAgg('tags__name', delimiter='\n'),
weight='B',
config='english'))
search_query = SearchQuery(query, config='english')
datasets = datasets.annotate(search=search,
search_rank=SearchRank(search, search_query))
if query:
datasets = datasets.filter(search=search_query)
# Mark up whether the user can access the data in the dataset.
access_filter = Q()
if user and datasets.model is not ReferenceDataset:
access_filter &= (Q(user_access_type='REQUIRES_AUTHENTICATION')
& (Q(datasetuserpermission__user=user)
| Q(datasetuserpermission__isnull=True))) | Q(
user_access_type='REQUIRES_AUTHORIZATION',
datasetuserpermission__user=user)
datasets = datasets.annotate(_has_access=Case(
When(access_filter, then=True),
default=False,
output_field=BooleanField(),
) if access_filter else Value(True, BooleanField()), )
# Pull in the source tag IDs for the dataset
datasets = datasets.annotate(source_tag_ids=ArrayAgg(
'tags', filter=Q(tags__type=TagType.SOURCE.value), distinct=True))
datasets = datasets.annotate(source_tag_names=ArrayAgg(
'tags__name', filter=Q(
tags__type=TagType.SOURCE.value), distinct=True))
# Pull in the topic tag IDs for the dataset
datasets = datasets.annotate(topic_tag_ids=ArrayAgg(
'tags', filter=Q(tags__type=TagType.TOPIC.value), distinct=True))
datasets = datasets.annotate(topic_tag_names=ArrayAgg(
'tags__name', filter=Q(tags__type=TagType.TOPIC.value), distinct=True))
# Define a `purpose` column denoting the dataset type.
if is_reference_query:
datasets = datasets.annotate(
purpose=Value(DataSetType.REFERENCE.value, IntegerField()))
else:
datasets = datasets.annotate(purpose=F('type'))
# We are joining on the user permissions table to determine `_has_access`` to the dataset, so we need to
# group them and remove duplicates. We aggregate all the `_has_access` fields together and return true if any
# of the records say that access is available.
datasets = datasets.values(
id_field,
'name',
'slug',
'short_description',
'search_rank',
'source_tag_names',
'source_tag_ids',
'topic_tag_names',
'topic_tag_ids',
'purpose',
'published',
'published_at',
).annotate(has_access=BoolOr('_has_access'))
return datasets.values(
id_field,
'name',
'slug',
'short_description',
'search_rank',
'source_tag_names',
'source_tag_ids',
'topic_tag_names',
'topic_tag_ids',
'purpose',
'published',
'published_at',
'has_access',
)
def get(self, request):
config = PartnershipConfiguration.get_configuration()
current_year = config.get_current_academic_year_for_api()
continents = Continent.objects.prefetch_related(
Prefetch(
'country_set',
queryset=Country.objects.annotate(
cities=StringAgg('entityversionaddress__city', ';', distinct=True)
)
)
)
partners = (
Partner.objects
.having_partnerships()
.distinct('pk')
.order_by('pk')
.values('uuid', 'organization__name')
)
# Get UCL entity parents which children have a partnership
ucl_universities = (
EntityProxy.objects
.ucl_entities_parents()
.with_title()
.with_acronym_path()
.distinct()
.order_by('acronym_path')
)
# label = 'title_fr' if get_language() == settings.LANGUAGE_CODE_FR else 'title_en'
# education_fields = (
# DomainIsced.objects
# .filter(partnershipyear__academic_year=current_year)
# .distinct()
# .values('uuid', label)
# )
education_levels = (
PartnershipYearEducationLevel.objects
.filter(partnerships_years__academic_year=current_year,
partnerships_years__partnership__isnull=False)
.distinct('pk')
.order_by('pk')
.values('code', 'label')
)
label = 'title' if get_language() == settings.LANGUAGE_CODE_FR else 'title_english'
year_offers = EducationGroupYear.objects.filter(
partnerships__isnull=False,
).values('uuid', 'acronym', label).distinct().order_by('acronym', label)
view = FundingAutocompleteView()
view.q = ''
fundings = view.get_list()
tags = (
PartnershipTag.objects
.filter(partnerships__isnull=False)
.values_list('value', flat=True)
.distinct('value')
.order_by('value')
)
partner_tags = (
PartnerTag.objects
.of_partners_having_partnerships()
.values_list('value', flat=True)
.distinct('value')
.order_by('value')
)
data = {
'continents': ContinentConfigurationSerializer(continents, many=True).data,
'partners': PartnerConfigurationSerializer(partners, many=True).data,
'ucl_universities': UCLUniversityConfigurationSerializer(ucl_universities, many=True).data,
# 'education_fields': EducationFieldConfigurationSerializer(education_fields, many=True).data,
'education_levels': EducationLevelSerializer(education_levels, many=True).data,
'fundings': list(fundings),
'partnership_types': PartnershipTypeSerializer(PartnershipType.all(), many=True).data,
'tags': list(tags),
'partner_tags': list(partner_tags),
'offers': OfferSerializer(year_offers, many=True).data,
}
return Response(data)
from django.contrib.postgres.aggregates import StringAgg
from django.contrib.postgres.search import (
SearchQuery,
SearchRank,
SearchVector,
TrigramSimilarity,
)
from django.db import models
search_vectors = (SearchVector('nickname', weight='A', config='english') +
SearchVector(StringAgg('genres__name', delimiter=' '),
weight='B',
config='english') +
SearchVector('description', weight='D', config='english'))
class BandManager(models.Manager):
def search(self, text):
search_query = SearchQuery(text, config='english')
search_rank = SearchRank(search_vectors, search_query)
trigram_similarity = TrigramSimilarity('nickname', text)
return self.get_queryset().annotate(search=search_vectors).filter(
search=search_query).annotate(rank=search_rank +
trigram_similarity).order_by('-rank')
def get_search_vector(self):
return (
SearchVector('title', weight='A') +
SearchVector('description', weight='C') +
SearchVector(StringAgg('tags__name', delimiter=' '), weight='B'))
def test_string_agg_empty_result(self):
AggregateTestModel.objects.all().delete()
values = AggregateTestModel.objects.aggregate(
stringagg=StringAgg('char_field', delimiter=';'))
self.assertEqual(values, {'stringagg': ''})
def get_queryset(self, request):
queryset = super().get_queryset(request)
if 'changelist' in request.resolver_match.view_name:
return queryset.annotate(
noc=StringAgg('operators', ', ', distinct=True))
return queryset
def test_string_agg_delimiter_escaping(self):
values = AggregateTestModel.objects.aggregate(
stringagg=StringAgg('char_field', delimiter="'"))
self.assertEqual(values, {'stringagg': "Foo1'Foo2'Foo4'Foo3"})
def get_queryset(self, request, **kwargs):
queryset = super().get_queryset(request, **kwargs)
return queryset.annotate(operators=StringAgg(
'route__service__operator', ', ', distinct=True))
def test_string_agg_default_output_field(self):
values = AggregateTestModel.objects.aggregate(stringagg=StringAgg(
'text_field', delimiter=';'), )
self.assertEqual(values, {'stringagg': 'Text1;Text2;Text4;Text3'})
def generate_federal_account_query(queryset, account_type, tas_id, filters):
""" Group by federal account (and budget function/subfunction) and SUM all other fields """
derived_fields = {
"reporting_agency_name": StringAgg("submission__reporting_agency_name", "; ", distinct=True),
"budget_function": StringAgg(f"{tas_id}__budget_function_title", "; ", distinct=True),
"budget_subfunction": StringAgg(f"{tas_id}__budget_subfunction_title", "; ", distinct=True),
"submission_period": get_fyp_or_q_notation("submission"),
"agency_identifier_name": get_agency_name_annotation(tas_id, "agency_id"),
"last_modified_date"
+ NAMING_CONFLICT_DISCRIMINATOR: Cast(Max("submission__published_date"), output_field=DateField()),
"gross_outlay_amount": Sum(generate_gross_outlay_amount_derived_field(filters, account_type)),
"gross_outlay_amount_fyb_to_period_end": Sum(generate_gross_outlay_amount_derived_field(filters, account_type)),
}
if account_type != "account_balances":
derived_fields.update(
{
"downward_adj_prior_yr_ppaid_undeliv_orders_oblig_refunds_cpe": Sum(
generate_ussgl487200_derived_field(filters)
),
"downward_adj_prior_yr_paid_delivered_orders_oblig_refunds_cpe": Sum(
generate_ussgl497200_derived_field(filters)
),
}
)
if account_type == "award_financial":
derived_fields = award_financial_derivations(derived_fields)
queryset = queryset.annotate(**derived_fields)
# List of all columns that may appear in A, B, or C files that can be summed
all_summed_cols = [
"budget_authority_unobligated_balance_brought_forward",
"adjustments_to_unobligated_balance_brought_forward",
"budget_authority_appropriated_amount",
"borrowing_authority_amount",
"contract_authority_amount",
"spending_authority_from_offsetting_collections_amount",
"total_other_budgetary_resources_amount",
"total_budgetary_resources",
"obligations_incurred",
"deobligations_or_recoveries_or_refunds_from_prior_year",
"unobligated_balance",
"status_of_budgetary_resources_total",
"transaction_obligated_amount",
]
# Group by all columns within the file that can't be summed
fed_acct_values_dict = query_paths[account_type]["federal_account"]
grouped_cols = [fed_acct_values_dict[val] for val in fed_acct_values_dict if val not in all_summed_cols]
queryset = queryset.values(*grouped_cols)
# Sum all fields from all_summed_cols that appear in this file
values_dict = query_paths[account_type]
summed_cols = {
val: Sum(values_dict["treasury_account"].get(val, None))
for val in values_dict["federal_account"]
if val in all_summed_cols
}
return queryset.annotate(**summed_cols)
def get_queryset(self, request):
queryset = super().get_queryset(request)
if 'changelist' in request.resolver_match.view_name:
return queryset.annotate(operators=StringAgg(
'trip__route__service__operator', ', ', distinct=True))
return queryset
def get_xls_data(self):
queryset = self.filterset.qs
year_qs = PartnershipYear.objects.select_related(
'academic_year',
'funding_source',
'funding_program',
'funding_type',
).prefetch_related(
Prefetch('entities', queryset=EntityProxy.objects.with_acronym()),
'education_levels',
).filter(academic_year=self.academic_year)
queryset = (queryset.annotate_financing(
self.academic_year
).annotate_partner_address('country__continent__name').annotate(
tags_list=StringAgg('partnership__tags__value', ', '),
is_valid_for_year=Case(
When(
partnership__partnership_type=PartnershipType.PROJECT.name,
then=True,
),
default=Exists(
PartnershipAgreement.objects.filter(
partnership=OuterRef('partnership_id'),
status=AgreementStatus.VALIDATED.name,
start_academic_year__year__lte=self.academic_year.year,
end_academic_year__year__gte=self.academic_year.year,
))),
).prefetch_related(
Prefetch(
'partnership__years',
queryset=year_qs,
to_attr='selected_year',
),
Prefetch(
'partnership__years',
queryset=PartnershipYear.objects.select_related(
'academic_year'),
),
Prefetch(
'partnership__years',
queryset=PartnershipYear.objects.select_related(
'academic_year').reverse(),
to_attr='reverse_years',
),
'entity__entityversion_set',
).select_related('entity__partnerentity', ))
for rel in queryset.distinct():
partnership = rel.partnership
year = partnership.selected_year[
0] if partnership.selected_year else ''
last_agreement = partnership.last_valid_agreements[
0] if partnership.last_valid_agreements else None
# Replace funding values if financing is eligible for mobility and not overridden in year
if partnership.is_mobility and year and year.eligible and rel.financing_source and not year.funding_source:
funding_source = rel.financing_source
funding_program = rel.financing_program
funding_type = rel.financing_type
else:
funding_source = year and year.funding_source
funding_program = year and year.funding_program
funding_type = year and year.funding_type
parts = partnership.acronym_path[
1:] if partnership.acronym_path else []
partner = rel.entity.organization.partner
yield [
partnership.pk,
partnership.get_partnership_type_display(),
str(partnership.subtype or ''),
str(funding_source or ''),
str(funding_program or ''),
str(funding_type or ''),
str(rel.country_continent_name),
str(rel.country_name),
str(rel.entity.organization.name),
str(rel.entity.organization.code or ''),
str(partner.erasmus_code or ''),
str(partner.pic_code or ''),
hasattr(rel.entity, 'partnerentity')
and rel.entity.partnerentity.name or '',
str(parts[0] if len(parts) > 0 else ''),
str(parts[1] if len(parts) > 1 else ''),
str(parts[2] if len(parts) > 2 else ''),
str(partnership.supervisor or ''),
year and ', '.join(
map(lambda x: x.acronym or '', year.entities.all())),
year and ', '.join(map(str, year.education_levels.all())),
rel.tags_list,
partnership.created.strftime('%Y-%m-%d'),
partnership.modified.strftime('%Y-%m-%d'),
year and year.is_sms,
year and year.is_smp,
year and year.is_sta,
year and year.is_stt,
str(partnership.years.first().academic_year)
if partnership.is_mobility else partnership.start_date,
str(partnership.reverse_years[0].academic_year)
if partnership.is_mobility else partnership.end_date,
getattr(
last_agreement,
'end_academic_year'
if partnership.is_mobility else 'end_date',
'',
),
rel.is_valid_for_year, # from annotation
partnership.external_id,
partnership.project_acronym,
year and year.eligible,
]
def idv_order_annotations():
annotation_fields = {
"award_base_action_date_fiscal_year":
FiscalYear("date_signed"),
"treasury_accounts_funding_this_award":
Subquery(
Award.objects.filter(id=OuterRef("id")).annotate(value=StringAgg(
"financial_set__treasury_account__tas_rendering_label",
";",
distinct=True)).values("value"),
output_field=TextField(),
),
"federal_accounts_funding_this_award":
Subquery(
Award.objects.filter(id=OuterRef("id")).annotate(value=StringAgg(
"financial_set__treasury_account__federal_account__federal_account_code",
";",
distinct=True)).values("value"),
output_field=TextField(),
),
"usaspending_permalink":
Concat(Value(AWARD_URL),
Func(F("generated_unique_award_id"), function="urlencode"),
Value("/")),
"disaster_emergency_fund_codes":
Subquery(
FinancialAccountsByAwards.objects.filter(
filter_limit_to_closed_periods(),
award_id=OuterRef("id")).annotate(value=ExpressionWrapper(
Case(
When(
disaster_emergency_fund__code__isnull=False,
then=Concat(
F("disaster_emergency_fund__code"),
Value(": "),
F("disaster_emergency_fund__public_law"),
),
),
default=Value(None, output_field=TextField()),
output_field=TextField(),
),
output_field=TextField(),
)).values("award_id").annotate(total=StringAgg(
"value", ";", distinct=True)).values("total"),
output_field=TextField(),
),
"outlayed_amount_funded_by_COVID-19_supplementals":
Subquery(
FinancialAccountsByAwards.objects.filter(
filter_by_latest_closed_periods(),
award_id=OuterRef("id"),
disaster_emergency_fund__group_name="covid_19",
submission__reporting_period_start__gte=str(
datetime.date(2020, 4, 1)),
).values("award_id").annotate(
sum=Sum("gross_outlay_amount_by_award_cpe")).values("sum"),
output_field=DecimalField(),
),
"obligated_amount_funded_by_COVID-19_supplementals":
Subquery(
FinancialAccountsByAwards.objects.filter(
filter_limit_to_closed_periods(),
award_id=OuterRef("id"),
disaster_emergency_fund__group_name="covid_19",
submission__reporting_period_start__gte=str(
datetime.date(2020, 4, 1)),
).values("award_id").annotate(
sum=Sum("transaction_obligated_amount")).values("sum"),
output_field=DecimalField(),
),
"award_latest_action_date_fiscal_year":
FiscalYear(F("latest_transaction__action_date")),
"object_classes_funding_this_award":
Subquery(
FinancialAccountsByAwards.objects.filter(
filter_limit_to_closed_periods(),
award_id=OuterRef("id")).annotate(value=ExpressionWrapper(
Concat(F("object_class__object_class"), Value(": "),
F("object_class__object_class_name")),
output_field=TextField(),
)).values("award_id").annotate(total=StringAgg(
"value", ";", distinct=True)).values("total"),
output_field=TextField(),
),
"program_activities_funding_this_award":
Subquery(
FinancialAccountsByAwards.objects.filter(
filter_limit_to_closed_periods(),
award_id=OuterRef("id"),
program_activity_id__isnull=False).annotate(
value=ExpressionWrapper(
Concat(
F("program_activity__program_activity_code"),
Value(": "),
F("program_activity__program_activity_name"),
),
output_field=TextField(),
)).values("award_id").annotate(total=StringAgg(
"value", ";", distinct=True)).values("total"),
output_field=TextField(),
),
}
return annotation_fields
def get_path_fragment_interface_address(device, neighbour_interface_address,
all_links):
multi_links = (all_links.filter(
local_interface__aggregate_interface__isnull=False).values(
"local_interface__device",
"remote_interface__device",
"local_interface__aggregate_interface",
"remote_interface__aggregate_interface",
).annotate(connection_id=StringAgg(
Cast("id", output_field=TextField()),
delimiter="_",
ordering="id",
output_field=TextField(),
)))
single_links = all_links.filter(
local_interface__aggregate_interface__isnull=True)
single_links_same_direction = single_links.filter(
local_interface__device=device,
remote_interface__logicalinterface__address=neighbour_interface_address
).values(
connection_id=Cast("id", TextField()),
source_device_name=F("local_interface__device__name"),
source_interface_name=F("local_interface__name"),
destination_device=F("remote_interface__device"),
destination_device_name=F("remote_interface__device__name"),
destination_interface_name=F("remote_interface__name"),
direction=Value("same", TextField()),
)
single_links_opposite_direction = single_links.filter(
remote_interface__device=device,
local_interface__logicalinterface__address=neighbour_interface_address
).values(
connection_id=Cast("id", TextField()),
source_device_name=F("remote_interface__device__name"),
source_interface_name=F("remote_interface__name"),
destination_device=F("local_interface__device"),
destination_device_name=F("local_interface__device__name"),
destination_interface_name=F("local_interface__name"),
direction=Value("opposite", TextField()),
)
multi_links_same_direction = multi_links.filter(
local_interface__device=device,
remote_interface__aggregate_interface__logicalinterface__address=
neighbour_interface_address,
).values(
"connection_id",
source_device_name=F("local_interface__device__name"),
source_interface_name=F("local_interface__aggregate_interface__name"),
destination_device=F("remote_interface__device"),
destination_device_name=F("remote_interface__device__name"),
destination_interface_name=F(
"remote_interface__aggregate_interface__name"),
direction=Value("same", TextField()),
)
multi_links_opposite_direction = multi_links.filter(
remote_interface__device=device,
local_interface__aggregate_interface__logicalinterface__address=
neighbour_interface_address,
).values(
"connection_id",
source_device_name=F("remote_interface__device__name"),
source_interface_name=F("remote_interface__aggregate_interface__name"),
destination_device=F("local_interface__device"),
destination_device_name=F("local_interface__device__name"),
destination_interface_name=F(
"local_interface__aggregate_interface__name"),
direction=Value("opposite", TextField()),
)
try:
connection = single_links_same_direction.union(
single_links_opposite_direction, multi_links_same_direction,
multi_links_opposite_direction).get()
return {
"information": {
"id":
f"{connection['connection_id']}:{connection['direction']}",
"details":
f"{connection['source_device_name']} ({connection['source_interface_name']}) - "
f"{connection['destination_device_name']} ({connection['destination_interface_name']})",
},
"destination_device": connection["destination_device"],
}
except Link.DoesNotExist:
logger.warning(
f"LSP PATH: Interface with address {neighbour_interface_address} was not found "
f"in neighbours of {device.ip_address}, pk: {device.pk})")
return None
except Link.MultipleObjectsReturned:
logger.warning(
f"LSP PATH: Multiple interfaces with address {neighbour_interface_address}"
f"in neighbours of {device.ip_address}, pk: {device.pk})")
return None