def annotate_queryset(queryset):
"""
Add some extra annotations to the queryset,
performing database queries as efficiently as possible,
to reduce database trips.
"""
# Annotate with the total 'in stock' quantity
queryset = queryset.annotate(
in_stock=Coalesce(
SubquerySum('stock_items__quantity', filter=StockItem.IN_STOCK_FILTER),
Decimal(0)
),
)
# Annotate with the total number of stock items
queryset = queryset.annotate(
stock_item_count=SubqueryCount('stock_items')
)
# Filter to limit builds to "active"
build_filter = Q(
status__in=BuildStatus.ACTIVE_CODES
)
# Annotate with the total 'building' quantity
queryset = queryset.annotate(
building=Coalesce(
SubquerySum('builds__quantity', filter=build_filter),
Decimal(0),
)
)
# Filter to limit orders to "open"
order_filter = Q(
order__status__in=PurchaseOrderStatus.OPEN
)
# Annotate with the total 'on order' quantity
queryset = queryset.annotate(
ordering=Coalesce(
SubquerySum('supplier_parts__purchase_order_line_items__quantity', filter=order_filter),
Decimal(0),
) - Coalesce(
SubquerySum('supplier_parts__purchase_order_line_items__received', filter=order_filter),
Decimal(0),
)
)
# Annotate with the number of 'suppliers'
queryset = queryset.annotate(
suppliers=Coalesce(
SubqueryCount('supplier_parts'),
Decimal(0),
),
)
return queryset
def annotate_queryset(queryset):
"""
Add some extra annotations to the queryset,
performing database queries as efficiently as possible,
to reduce database trips.
"""
# TODO: Update the "in_stock" annotation to include stock for variants of the part
# Ref: https://github.com/inventree/InvenTree/issues/2240
# Annotate with the total 'in stock' quantity
queryset = queryset.annotate(in_stock=Coalesce(
SubquerySum('stock_items__quantity',
filter=StockItem.IN_STOCK_FILTER),
Decimal(0),
output_field=models.DecimalField(),
), )
# Annotate with the total number of stock items
queryset = queryset.annotate(
stock_item_count=SubqueryCount('stock_items'))
# Filter to limit builds to "active"
build_filter = Q(status__in=BuildStatus.ACTIVE_CODES)
# Annotate with the total 'building' quantity
queryset = queryset.annotate(building=Coalesce(
SubquerySum('builds__quantity', filter=build_filter),
Decimal(0),
output_field=models.DecimalField(),
))
# Filter to limit orders to "open"
order_filter = Q(order__status__in=PurchaseOrderStatus.OPEN)
# Annotate with the total 'on order' quantity
queryset = queryset.annotate(ordering=Coalesce(
SubquerySum('supplier_parts__purchase_order_line_items__quantity',
filter=order_filter),
Decimal(0),
output_field=models.DecimalField(),
) - Coalesce(
SubquerySum('supplier_parts__purchase_order_line_items__received',
filter=order_filter),
Decimal(0),
output_field=models.DecimalField(),
))
# Annotate with the number of 'suppliers'
queryset = queryset.annotate(suppliers=Coalesce(
SubqueryCount('supplier_parts'),
Decimal(0),
output_field=models.DecimalField(),
), )
return queryset
def profile(request, username, template='profile.html', extra_context=None):
coder = get_object_or_404(Coder, user__username=username)
statistics = Statistics.objects.filter(account__coders=coder)
resources = Resource.objects \
.prefetch_related(Prefetch(
'account_set',
queryset=(
Account.objects
.filter(coders=coder)
.order_by('-n_contests')
),
to_attr='coder_accounts',
)) \
.annotate(num_contests=SubquerySum('account__n_contests', filter=Q(coders=coder))) \
.filter(num_contests__gt=0).order_by('-num_contests')
writers = Contest.objects.filter(writers__coders=coder)
context = get_profile_context(request, statistics, writers)
if context['search_resource']:
resources = resources.filter(host=context['search_resource'])
context['coder'] = coder
context['resources'] = resources
if extra_context is not None:
context.update(extra_context)
return render(request, template, context)
def annotate_sales_order_allocations(reference: str = ''):
"""Annotate the total quantity of each part allocated to sales orders:
- This function calculates the total part quantity allocated to open sales orders"
- Finds all sales order allocations for each part (using the provided filter)
- Aggregates the 'allocated quantity' for each relevent sales order allocation item
Args:
reference: The relationship reference of the part from the current model
order_filter: Q object which defines how to filter the allocation items
"""
# Order filter only returns incomplete shipments for open orders
order_filter = Q(
line__order__status__in=SalesOrderStatus.OPEN,
shipment__shipment_date=None,
)
return Coalesce(
SubquerySum(
f'{reference}stock_items__sales_order_allocations__quantity',
filter=order_filter,
),
Decimal(0),
output_field=models.DecimalField(),
)
def annotate_queryset(queryset):
"""
Add some extra annotations to the queryset,
performing database queries as efficiently as possible.
"""
# Annotate the queryset with the total allocated to sales orders
queryset = queryset.annotate(
allocated=Coalesce(
SubquerySum('sales_order_allocations__quantity'), Decimal(0)) +
Coalesce(SubquerySum('allocations__quantity'), Decimal(0)))
# Annotate the queryset with the number of tracking items
queryset = queryset.annotate(
tracking_items=SubqueryCount('tracking_info'))
return queryset
def annotate_queryset(queryset):
"""
Add some extra annotations to the queryset,
performing database queries as efficiently as possible.
"""
# Annotate the queryset with the total allocated to sales orders
queryset = queryset.annotate(
allocated=Coalesce(
SubquerySum('sales_order_allocations__quantity'), Decimal(0)
) + Coalesce(
SubquerySum('allocations__quantity'), Decimal(0)
)
)
# Annotate the queryset with the number of tracking items
queryset = queryset.annotate(
tracking_items=SubqueryCount('tracking_info')
)
# Add flag to indicate if the StockItem has expired
queryset = queryset.annotate(
expired=Case(
When(
StockItem.EXPIRED_FILTER, then=Value(True, output_field=BooleanField()),
),
default=Value(False, output_field=BooleanField())
)
)
# Add flag to indicate if the StockItem is stale
stale_days = common.models.InvenTreeSetting.get_setting('STOCK_STALE_DAYS')
stale_date = datetime.now().date() + timedelta(days=stale_days)
stale_filter = StockItem.IN_STOCK_FILTER & ~Q(expiry_date=None) & Q(expiry_date__lt=stale_date)
queryset = queryset.annotate(
stale=Case(
When(
stale_filter, then=Value(True, output_field=BooleanField()),
),
default=Value(False, output_field=BooleanField()),
)
)
return queryset
def annotate_on_order_quantity(reference: str = ''):
"""Annotate the 'on order' quantity for each part in a queryset"""
# Filter only 'active' purhase orders
order_filter = Q(order__status__in=PurchaseOrderStatus.OPEN)
return Coalesce(
SubquerySum(
f'{reference}supplier_parts__purchase_order_line_items__quantity',
filter=order_filter),
Decimal(0),
output_field=models.DecimalField()
) - Coalesce(
SubquerySum(
f'{reference}supplier_parts__purchase_order_line_items__received',
filter=order_filter),
Decimal(0),
output_field=models.DecimalField(),
)
def annotate_queryset(queryset):
"""Add some extra annotations to the queryset.
Performing database queries as efficiently as possible, to reduce database trips.
"""
# Annotate with the total number of stock items
queryset = queryset.annotate(
stock_item_count=SubqueryCount('stock_items'))
# Annotate with the total variant stock quantity
variant_query = part.filters.variant_stock_query()
queryset = queryset.annotate(
variant_stock=part.filters.annotate_variant_quantity(
variant_query, reference='quantity'), )
# Filter to limit builds to "active"
build_filter = Q(status__in=BuildStatus.ACTIVE_CODES)
# Annotate with the total 'building' quantity
queryset = queryset.annotate(building=Coalesce(
SubquerySum('builds__quantity', filter=build_filter),
Decimal(0),
output_field=models.DecimalField(),
))
# Annotate with the number of 'suppliers'
queryset = queryset.annotate(suppliers=Coalesce(
SubqueryCount('supplier_parts'),
Decimal(0),
output_field=models.DecimalField(),
), )
queryset = queryset.annotate(
ordering=part.filters.annotate_on_order_quantity(),
in_stock=part.filters.annotate_total_stock(),
allocated_to_sales_orders=part.filters.
annotate_sales_order_allocations(),
allocated_to_build_orders=part.filters.
annotate_build_order_allocations(),
)
# Annotate with the total 'available stock' quantity
# This is the current stock, minus any allocations
queryset = queryset.annotate(unallocated_stock=ExpressionWrapper(
F('in_stock') - F('allocated_to_sales_orders') -
F('allocated_to_build_orders'),
output_field=models.DecimalField(),
))
return queryset
def coders(request, template='coders.html'):
coders = Coder.objects.select_related('user')
params = {}
search = request.GET.get('search')
if search:
filt = get_iregex_filter(search, 'username', logger=request.logger)
coders = coders.filter(filt)
countries = request.GET.getlist('country')
countries = set([c for c in countries if c])
if countries:
coders = coders.annotate(filter_country=Exists('account', filter=Q(account__country__in=countries)))
coders = coders.filter(Q(country__in=countries) | Q(filter_country=True))
params['countries'] = countries
resources = request.GET.getlist('resource')
if resources:
resources = [r for r in resources if r]
resources = list(Resource.objects.filter(pk__in=resources))
for r in resources:
coders = coders.annotate(**{f'{r.pk}_rating': SubqueryMax('account__rating', filter=Q(resource=r))})
coders = coders.annotate(**{f'{r.pk}_n_contests': SubquerySum('account__n_contests', filter=Q(resource=r))})
params['resources'] = resources
# ordering
orderby = request.GET.get('sort_column')
if orderby in ['username', 'created', 'n_accounts']:
pass
elif orderby and orderby.startswith('resource_'):
_, pk = orderby.split('_')
orderby = [f'{pk}_rating', f'{pk}_n_contests']
elif orderby:
request.logger.error(f'Not found `{orderby}` column for sorting')
orderby = []
orderby = orderby if not orderby or isinstance(orderby, list) else [orderby]
order = request.GET.get('sort_order')
if order in ['asc', 'desc']:
orderby = [getattr(F(o), order)(nulls_last=True) for o in orderby]
elif order:
request.logger.error(f'Not found `{order}` order for sorting')
orderby = orderby or ['-created']
coders = coders.order_by(*orderby)
context = {
'coders': coders,
'params': params,
}
return template, context
def update_coder_n_accounts_and_n_contests(signal, instance, action, reverse,
pk_set, **kwargs):
when, action = action.split('_', 1)
if when != 'post' or action not in ['add', 'remove']:
return
if reverse:
instance.n_accounts = instance.account_set.count()
instance.n_contests = instance.account_set.aggregate(
total=Sum('n_contests'))['total'] or 0
instance.save()
else:
Coder.objects.filter(pk__in=pk_set) \
.annotate(n_a=SubqueryCount('account')) \
.annotate(n_c=SubquerySum('account__n_contests')) \
.update(n_accounts=F('n_a'), n_contests=Coalesce('n_c', 0))
def annotate_total_stock(reference: str = ''):
"""Annotate 'total stock' quantity against a queryset:
- This function calculates the 'total stock' for a given part
- Finds all stock items associated with each part (using the provided filter)
- Aggregates the 'quantity' of each relevent stock item
Args:
reference: The relationship reference of the part from the current model e.g. 'part'
stock_filter: Q object which defines how to filter the stock items
"""
# Stock filter only returns 'in stock' items
stock_filter = stock.models.StockItem.IN_STOCK_FILTER
return Coalesce(
SubquerySum(
f'{reference}stock_items__quantity',
filter=stock_filter,
),
Decimal(0),
output_field=models.DecimalField(),
)
def annotate_build_order_allocations(reference: str = ''):
"""Annotate the total quantity of each part allocated to build orders:
- This function calculates the total part quantity allocated to open build orders
- Finds all build order allocations for each part (using the provided filter)
- Aggregates the 'allocated quantity' for each relevent build order allocation item
Args:
reference: The relationship reference of the part from the current model
build_filter: Q object which defines how to filter the allocation items
"""
# Build filter only returns 'active' build orders
build_filter = Q(build__status__in=BuildStatus.ACTIVE_CODES)
return Coalesce(
SubquerySum(
f'{reference}stock_items__allocations__quantity',
filter=build_filter,
),
Decimal(0),
output_field=models.DecimalField(),
)
def get_ordered_resources(self):
return Resource.objects \
.annotate(n=SubquerySum('account__n_contests', filter=Q(coders=self))) \
.order_by(F('n').desc(nulls_last=True), '-has_rating_history', '-n_contests')
def test_foreign_key_to_field(self):
brands = Brand.objects.annotate(
purchase_sum=SubquerySum('products__num_purchases')
)
self.assertEqual(brands.first().purchase_sum, 4)
def annotate_queryset(queryset):
"""
Add some extra annotations to the queryset,
performing database queries as efficiently as possible,
to reduce database trips.
"""
# Annotate with the total 'in stock' quantity
queryset = queryset.annotate(in_stock=Coalesce(
SubquerySum('stock_items__quantity',
filter=StockItem.IN_STOCK_FILTER),
Decimal(0),
output_field=models.DecimalField(),
), )
# Annotate with the total number of stock items
queryset = queryset.annotate(
stock_item_count=SubqueryCount('stock_items'))
# Annotate with the total variant stock quantity
variant_query = StockItem.objects.filter(
part__tree_id=OuterRef('tree_id'),
part__lft__gt=OuterRef('lft'),
part__rght__lt=OuterRef('rght'),
).filter(StockItem.IN_STOCK_FILTER)
queryset = queryset.annotate(variant_stock=Coalesce(
Subquery(
variant_query.annotate(total=Func(
F('quantity'), function='SUM',
output_field=FloatField())).values('total')),
0,
output_field=FloatField(),
))
# Filter to limit builds to "active"
build_filter = Q(status__in=BuildStatus.ACTIVE_CODES)
# Annotate with the total 'building' quantity
queryset = queryset.annotate(building=Coalesce(
SubquerySum('builds__quantity', filter=build_filter),
Decimal(0),
output_field=models.DecimalField(),
))
# Filter to limit orders to "open"
order_filter = Q(order__status__in=PurchaseOrderStatus.OPEN)
# Annotate with the total 'on order' quantity
queryset = queryset.annotate(ordering=Coalesce(
SubquerySum('supplier_parts__purchase_order_line_items__quantity',
filter=order_filter),
Decimal(0),
output_field=models.DecimalField(),
) - Coalesce(
SubquerySum('supplier_parts__purchase_order_line_items__received',
filter=order_filter),
Decimal(0),
output_field=models.DecimalField(),
))
# Annotate with the number of 'suppliers'
queryset = queryset.annotate(suppliers=Coalesce(
SubqueryCount('supplier_parts'),
Decimal(0),
output_field=models.DecimalField(),
), )
"""
Annotate with the number of stock items allocated to sales orders.
This annotation is modeled on Part.sales_order_allocations() method:
- Only look for "open" orders
- Stock items have not been "shipped"
"""
so_allocation_filter = Q(
line__order__status__in=SalesOrderStatus.
OPEN, # LineItem points to an OPEN order
shipment__shipment_date=
None, # Allocated item has *not* been shipped out
)
queryset = queryset.annotate(allocated_to_sales_orders=Coalesce(
SubquerySum('stock_items__sales_order_allocations__quantity',
filter=so_allocation_filter),
Decimal(0),
output_field=models.DecimalField(),
))
"""
Annotate with the number of stock items allocated to build orders.
This annotation is modeled on Part.build_order_allocations() method
"""
bo_allocation_filter = Q(build__status__in=BuildStatus.ACTIVE_CODES, )
queryset = queryset.annotate(allocated_to_build_orders=Coalesce(
SubquerySum('stock_items__allocations__quantity',
filter=bo_allocation_filter),
Decimal(0),
output_field=models.DecimalField(),
))
# Annotate with the total 'available stock' quantity
# This is the current stock, minus any allocations
queryset = queryset.annotate(unallocated_stock=ExpressionWrapper(
F('in_stock') - F('allocated_to_sales_orders') -
F('allocated_to_build_orders'),
output_field=models.DecimalField(),
))
return queryset
def get_queryset(self):
return core.models.Space.objects.owned(self.request.user).annotate(
files_count=SubqueryCount('files'),
files_total_size=SubquerySum('files__content_length'),
)
def annotate_queryset(queryset):
"""
Annotate the BomItem queryset with extra information:
Annotations:
available_stock: The amount of stock available for the sub_part Part object
"""
"""
Construct an "available stock" quantity:
available_stock = total_stock - build_order_allocations - sales_order_allocations
"""
build_order_filter = Q(build__status__in=BuildStatus.ACTIVE_CODES)
sales_order_filter = Q(
line__order__status__in=SalesOrderStatus.OPEN,
shipment__shipment_date=None,
)
# Calculate "total stock" for the referenced sub_part
# Calculate the "build_order_allocations" for the sub_part
# Note that these fields are only aliased, not annotated
queryset = queryset.alias(
total_stock=Coalesce(
SubquerySum('sub_part__stock_items__quantity',
filter=StockItem.IN_STOCK_FILTER),
Decimal(0),
output_field=models.DecimalField(),
),
allocated_to_sales_orders=Coalesce(
SubquerySum(
'sub_part__stock_items__sales_order_allocations__quantity',
filter=sales_order_filter,
),
Decimal(0),
output_field=models.DecimalField(),
),
allocated_to_build_orders=Coalesce(
SubquerySum(
'sub_part__stock_items__allocations__quantity',
filter=build_order_filter,
),
Decimal(0),
output_field=models.DecimalField(),
),
)
# Calculate 'available_stock' based on previously annotated fields
queryset = queryset.annotate(available_stock=ExpressionWrapper(
F('total_stock') - F('allocated_to_sales_orders') -
F('allocated_to_build_orders'),
output_field=models.DecimalField(),
))
# Extract similar information for any 'substitute' parts
queryset = queryset.alias(
substitute_stock=Coalesce(
SubquerySum(
'substitutes__part__stock_items__quantity',
filter=StockItem.IN_STOCK_FILTER,
),
Decimal(0),
output_field=models.DecimalField(),
),
substitute_build_allocations=Coalesce(
SubquerySum(
'substitutes__part__stock_items__allocations__quantity',
filter=build_order_filter,
),
Decimal(0),
output_field=models.DecimalField(),
),
substitute_sales_allocations=Coalesce(
SubquerySum(
'substitutes__part__stock_items__sales_order_allocations__quantity',
filter=sales_order_filter,
),
Decimal(0),
output_field=models.DecimalField(),
),
)
# Calculate 'available_substitute_stock' field
queryset = queryset.annotate(
available_substitute_stock=ExpressionWrapper(
F('substitute_stock') - F('substitute_build_allocations') -
F('substitute_sales_allocations'),
output_field=models.DecimalField(),
))
# Annotate the queryset with 'available variant stock' information
variant_stock_query = StockItem.objects.filter(
part__tree_id=OuterRef('sub_part__tree_id'),
part__lft__gt=OuterRef('sub_part__lft'),
part__rght__lt=OuterRef('sub_part__rght'),
).filter(StockItem.IN_STOCK_FILTER)
queryset = queryset.alias(
variant_stock_total=Coalesce(Subquery(
variant_stock_query.annotate(total=Func(
F('quantity'), function='SUM',
output_field=FloatField())).values('total')),
0,
output_field=FloatField()),
variant_stock_build_order_allocations=Coalesce(
Subquery(
variant_stock_query.annotate(total=Func(
F('sales_order_allocations__quantity'),
function='SUM',
output_field=FloatField()), ).values('total')),
0,
output_field=FloatField(),
),
variant_stock_sales_order_allocations=Coalesce(
Subquery(
variant_stock_query.annotate(total=Func(
F('allocations__quantity'),
function='SUM',
output_field=FloatField()), ).values('total')),
0,
output_field=FloatField(),
))
queryset = queryset.annotate(available_variant_stock=ExpressionWrapper(
F('variant_stock_total') -
F('variant_stock_build_order_allocations') -
F('variant_stock_sales_order_allocations'),
output_field=FloatField(),
))
return queryset