class ManageLibraryViewSet(
mixins.ListModelMixin,
mixins.RetrieveModelMixin,
mixins.UpdateModelMixin,
mixins.DestroyModelMixin,
viewsets.GenericViewSet,
):
lookup_field = "uuid"
queryset = (
music_models.Library.objects.all()
.filter(channel=None)
.order_by("-id")
.select_related("actor")
.annotate(
followers_count=Coalesce(Subquery(follows_subquery), 0),
_uploads_count=Coalesce(Subquery(uploads_subquery), 0),
)
)
serializer_class = serializers.ManageLibrarySerializer
filterset_class = filters.ManageLibraryFilterSet
required_scope = "instance:libraries"
@rest_decorators.action(methods=["get"], detail=True)
def stats(self, request, *args, **kwargs):
library = self.get_object()
uploads = library.uploads.all()
tracks = uploads.values_list("track", flat=True).distinct()
albums = (
music_models.Track.objects.filter(pk__in=tracks)
.values_list("album", flat=True)
.distinct()
)
artists = set(
music_models.Album.objects.filter(pk__in=albums).values_list(
"artist", flat=True
)
) | set(
music_models.Track.objects.filter(pk__in=tracks).values_list(
"artist", flat=True
)
)
data = {
"uploads": uploads.count(),
"followers": library.received_follows.count(),
"tracks": tracks.count(),
"albums": albums.count(),
"artists": len(artists),
"reports": moderation_models.Report.objects.get_for_target(library).count(),
}
data.update(get_media_stats(uploads.all()))
return response.Response(data, status=200)
@rest_decorators.action(methods=["post"], detail=False)
def action(self, request, *args, **kwargs):
queryset = self.get_queryset()
serializer = serializers.ManageTrackActionSerializer(
request.data, queryset=queryset
)
serializer.is_valid(raise_exception=True)
result = serializer.save()
return response.Response(result, status=200)
def iterate_sheet(self, form_data, sheet):
if sheet == 'invoices':
yield [
_('Invoice number'),
_('Date'),
_('Order code'),
_('E-mail address'),
_('Invoice type'),
_('Cancellation of'),
_('Language'),
_('Invoice sender:') + ' ' + _('Name'),
_('Invoice sender:') + ' ' + _('Address'),
_('Invoice sender:') + ' ' + _('ZIP code'),
_('Invoice sender:') + ' ' + _('City'),
_('Invoice sender:') + ' ' + _('Country'),
_('Invoice sender:') + ' ' + _('Tax ID'),
_('Invoice sender:') + ' ' + _('VAT ID'),
_('Invoice recipient:') + ' ' + _('Company'),
_('Invoice recipient:') + ' ' + _('Name'),
_('Invoice recipient:') + ' ' + _('Street address'),
_('Invoice recipient:') + ' ' + _('ZIP code'),
_('Invoice recipient:') + ' ' + _('City'),
_('Invoice recipient:') + ' ' + _('Country'),
_('Invoice recipient:') + ' ' + pgettext('address', 'State'),
_('Invoice recipient:') + ' ' + _('VAT ID'),
_('Invoice recipient:') + ' ' + _('Beneficiary'),
_('Invoice recipient:') + ' ' + _('Internal reference'),
_('Reverse charge'),
_('Shown foreign currency'),
_('Foreign currency rate'),
_('Total value (with taxes)'),
_('Total value (without taxes)'),
_('Payment matching IDs'),
]
qs = self.event.invoices.order_by(
'full_invoice_no').select_related(
'order',
'refers').prefetch_related('order__payments').annotate(
total_gross=Subquery(
InvoiceLine.objects.filter(invoice=OuterRef(
'pk')).order_by().values('invoice').annotate(
s=Sum('gross_value')).values('s')),
total_net=Subquery(
InvoiceLine.objects.filter(invoice=OuterRef(
'pk')).order_by().values('invoice').annotate(
s=Sum(F('gross_value') -
F('tax_value'))).values('s')))
for i in qs:
pmis = []
for p in i.order.payments.all():
if p.state in (OrderPayment.PAYMENT_STATE_CONFIRMED,
OrderPayment.PAYMENT_STATE_CREATED,
OrderPayment.PAYMENT_STATE_PENDING,
OrderPayment.PAYMENT_STATE_REFUNDED):
pprov = p.payment_provider
if pprov:
mid = pprov.matching_id(p)
if mid:
pmis.append(mid)
pmi = '\n'.join(pmis)
yield [
i.full_invoice_no,
date_format(i.date, "SHORT_DATE_FORMAT"), i.order.code,
i.order.email,
_('Cancellation') if i.is_cancellation else _('Invoice'),
i.refers.full_invoice_no if i.refers else '', i.locale,
i.invoice_from_name, i.invoice_from,
i.invoice_from_zipcode, i.invoice_from_city,
i.invoice_from_country, i.invoice_from_tax_id,
i.invoice_from_vat_id, i.invoice_to_company,
i.invoice_to_name, i.invoice_to_street or i.invoice_to,
i.invoice_to_zipcode, i.invoice_to_city,
i.invoice_to_country, i.invoice_to_state,
i.invoice_to_vat_id, i.invoice_to_beneficiary,
i.internal_reference,
_('Yes') if i.reverse_charge else _('No'),
i.foreign_currency_display, i.foreign_currency_rate,
i.total_gross if i.total_gross else Decimal('0.00'),
Decimal(i.total_net if i.total_net else '0.00').quantize(
Decimal('0.01')), pmi
]
elif sheet == 'lines':
yield [
_('Invoice number'),
_('Line number'),
_('Description'),
_('Gross price'),
_('Net price'),
_('Tax value'),
_('Tax rate'),
_('Tax name'),
_('Event start date'),
_('Date'),
_('Order code'),
_('E-mail address'),
_('Invoice type'),
_('Cancellation of'),
_('Invoice sender:') + ' ' + _('Name'),
_('Invoice sender:') + ' ' + _('Address'),
_('Invoice sender:') + ' ' + _('ZIP code'),
_('Invoice sender:') + ' ' + _('City'),
_('Invoice sender:') + ' ' + _('Country'),
_('Invoice sender:') + ' ' + _('Tax ID'),
_('Invoice sender:') + ' ' + _('VAT ID'),
_('Invoice recipient:') + ' ' + _('Company'),
_('Invoice recipient:') + ' ' + _('Name'),
_('Invoice recipient:') + ' ' + _('Street address'),
_('Invoice recipient:') + ' ' + _('ZIP code'),
_('Invoice recipient:') + ' ' + _('City'),
_('Invoice recipient:') + ' ' + _('Country'),
_('Invoice recipient:') + ' ' + pgettext('address', 'State'),
_('Invoice recipient:') + ' ' + _('VAT ID'),
_('Invoice recipient:') + ' ' + _('Beneficiary'),
_('Invoice recipient:') + ' ' + _('Internal reference'),
]
qs = InvoiceLine.objects.filter(
invoice__event=self.event).order_by('invoice__full_invoice_no',
'position').select_related(
'invoice',
'invoice__order',
'invoice__refers')
for l in qs:
i = l.invoice
yield [
i.full_invoice_no,
l.position + 1,
l.description.replace("<br />", " - "),
l.gross_value,
l.net_value,
l.tax_value,
l.tax_rate,
l.tax_name,
date_format(l.event_date_from, "SHORT_DATE_FORMAT")
if l.event_date_from else "",
date_format(i.date, "SHORT_DATE_FORMAT"),
i.order.code,
i.order.email,
_('Cancellation') if i.is_cancellation else _('Invoice'),
i.refers.full_invoice_no if i.refers else '',
i.invoice_from_name,
i.invoice_from,
i.invoice_from_zipcode,
i.invoice_from_city,
i.invoice_from_country,
i.invoice_from_tax_id,
i.invoice_from_vat_id,
i.invoice_to_company,
i.invoice_to_name,
i.invoice_to_street or i.invoice_to,
i.invoice_to_zipcode,
i.invoice_to_city,
i.invoice_to_country,
i.invoice_to_state,
i.invoice_to_vat_id,
i.invoice_to_beneficiary,
i.internal_reference,
]
def get_object_data(self, contest):
in_contest = contest.is_in_contest(self.request.user)
can_see_rankings = contest.can_see_full_scoreboard(self.request.user)
can_see_problems = (in_contest or contest.ended
or contest.is_editable_by(self.request.user))
problems = list(
contest.contest_problems.select_related('problem').defer(
'problem__description').order_by('order'), )
new_ratings_subquery = Rating.objects.filter(
participation=OuterRef('pk'))
old_ratings_subquery = (Rating.objects.filter(
user=OuterRef('user__pk'),
contest__end_time__lt=OuterRef('contest__end_time')).order_by(
'-contest__end_time'))
participations = (contest.users.filter(
virtual=ContestParticipation.LIVE,
user__is_unlisted=False).annotate(
username=F('user__user__username'),
old_rating=Subquery(old_ratings_subquery.values('rating')[:1]),
new_rating=Subquery(new_ratings_subquery.values('rating')[:1]),
).order_by('-score', 'cumtime', 'tiebreaker'))
return {
'key':
contest.key,
'name':
contest.name,
'start_time':
contest.start_time.isoformat(),
'end_time':
contest.end_time.isoformat(),
'time_limit':
contest.time_limit and contest.time_limit.total_seconds(),
'is_rated':
contest.is_rated,
'rate_all':
contest.is_rated and contest.rate_all,
'has_rating':
contest.ratings.exists(),
'rating_floor':
contest.rating_floor,
'rating_ceiling':
contest.rating_ceiling,
'hidden_scoreboard':
contest.scoreboard_visibility
in (contest.SCOREBOARD_AFTER_CONTEST,
contest.SCOREBOARD_AFTER_PARTICIPATION),
'scoreboard_visibility':
contest.scoreboard_visibility,
'is_organization_private':
contest.is_organization_private,
'organizations':
list(
contest.organizations.values_list('id', flat=True)
if contest.is_organization_private else [], ),
'is_private':
contest.is_private,
'tags':
list(contest.tags.values_list('name', flat=True)),
'format': {
'name': contest.format_name,
'config': contest.format_config,
},
'problems': [{
'points': int(problem.points),
'partial': problem.partial,
'is_pretested': problem.is_pretested
and contest.run_pretests_only,
'max_submissions': problem.max_submissions or None,
'label': contest.get_label_for_problem(index),
'name': problem.problem.name,
'code': problem.problem.code,
} for index, problem in enumerate(problems)]
if can_see_problems else [],
'rankings': [{
'user':
participation.username,
'score':
participation.score,
'cumulative_time':
participation.cumtime,
'tiebreaker':
participation.tiebreaker,
'old_rating':
participation.old_rating,
'new_rating':
participation.new_rating,
'is_disqualified':
participation.is_disqualified,
'solutions':
contest.format.get_problem_breakdown(participation, problems),
} for participation in participations] if can_see_rankings else [],
}
def _get_queryset(self, cl, form_data):
cqs = Checkin.objects.filter(
position_id=OuterRef('pk'),
list_id=cl.pk).order_by().values('position_id').annotate(
m=Max('datetime')).values('m')
cqsin = cqs.filter(type=Checkin.TYPE_ENTRY)
cqsout = cqs.filter(type=Checkin.TYPE_EXIT)
qs = OrderPosition.objects.filter(order__event=self.event, ).annotate(
last_checked_in=Subquery(cqsin),
last_checked_out=Subquery(cqsout),
auto_checked_in=Exists(
Checkin.objects.filter(
position_id=OuterRef('pk'),
list_id=cl.pk,
auto_checked_in=True))).prefetch_related(
'answers', 'answers__question', 'addon_to__answers',
'addon_to__answers__question').select_related(
'order', 'item', 'variation', 'addon_to',
'order__invoice_address', 'voucher', 'seat')
if not cl.all_products:
qs = qs.filter(
item__in=cl.limit_products.values_list('id', flat=True))
if cl.subevent:
qs = qs.filter(subevent=cl.subevent)
if form_data.get('date_from'):
dt = make_aware(
datetime.combine(
dateutil.parser.parse(form_data['date_from']).date(),
time(hour=0, minute=0, second=0)), self.event.timezone)
qs = qs.filter(subevent__date_from__gte=dt)
if form_data.get('date_to'):
dt = make_aware(
datetime.combine(
dateutil.parser.parse(form_data['date_to']).date() +
timedelta(days=1), time(hour=0, minute=0, second=0)),
self.event.timezone)
qs = qs.filter(subevent__date_from__lt=dt)
o = ()
if self.event.has_subevents and not cl.subevent:
o = ('subevent__date_from', 'subevent__name')
sort = form_data.get('sort') or 'name'
if sort == 'name':
qs = qs.order_by(
*o,
Coalesce(
NullIf('attendee_name_cached', Value('')),
NullIf('addon_to__attendee_name_cached', Value('')),
NullIf('order__invoice_address__name_cached', Value('')),
'order__code'))
elif sort == 'code':
qs = qs.order_by(*o, 'order__code')
elif sort.startswith('name:'):
part = sort[5:]
qs = qs.annotate(resolved_name=Case(
When(attendee_name_cached__ne='', then='attendee_name_parts'),
When(addon_to__attendee_name_cached__isnull=False,
addon_to__attendee_name_cached__ne='',
then='addon_to__attendee_name_parts'),
default='order__invoice_address__name_parts',
)).annotate(resolved_name_part=JSONExtract(
'resolved_name', part)).order_by(*o, 'resolved_name_part')
if form_data.get('attention_only'):
qs = qs.filter(
Q(item__checkin_attention=True)
| Q(order__checkin_attention=True))
if not cl.include_pending:
qs = qs.filter(order__status=Order.STATUS_PAID)
else:
qs = qs.filter(order__status__in=(Order.STATUS_PAID,
Order.STATUS_PENDING))
return qs
BooleanField,
Case,
Exists,
OuterRef,
Subquery,
When,
Value,
)
from osf.models import Contributor, Registration, SchemaResponse
from osf.utils.workflows import RegistrationModerationStates
# NOTE: Conveniently assigns None for withdrawn/deleted parents
PARENT_IS_PUBLIC = Subquery(
Registration.objects.filter(
id=OuterRef('object_id'),
deleted__isnull=True,
).exclude(moderation_state=RegistrationModerationStates.WITHDRAWN.db_name,
).values('is_public')[:1],
output_field=BooleanField(),
)
IS_ORIGINAL_RESPONSE = Case(
When(previous_response__isnull=True, then=Value(True)),
default=Value(False),
output_field=BooleanField(),
)
def is_pending_current_user_approval(user):
'''Construct a subquery to see if a given user is a pending_approver for a SchemaResponse.'''
return Exists(
SchemaResponse.pending_approvers.through.objects.filter(
def universal_transaction_matview_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":
Concat(
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=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_for_overall_award":
Case(
When(
transaction__action_date__gte=datetime.date(2020, 4, 1),
then=Subquery(
FinancialAccountsByAwards.objects.filter(
filter_by_latest_closed_periods(),
award_id=OuterRef("award_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_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"),
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(),
),
), ),
"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(
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("award_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 frames_for_video(video):
return [f['number'] for f in
Frame.objects.filter(video=video, shot_boundary=False).annotate(
c=Subquery(Face.objects.filter(frame=OuterRef('pk')).values('frame').annotate(c=Count('*')).values('c')))
.filter(c__gte=1)
.values('number').order_by('number')]
def get_queryset(self, ignore_status=False, ignore_products=False):
cqs = Checkin.objects.filter(
position_id=OuterRef('pk'),
list_id=self.checkinlist.pk).order_by().values(
'position_id').annotate(m=Max('datetime')).values('m')
qs = OrderPosition.objects.filter(
order__event=self.request.event, ).annotate(
last_checked_in=Subquery(cqs)).prefetch_related(
'order__event', 'order__event__organizer')
if self.checkinlist.subevent:
qs = qs.filter(subevent=self.checkinlist.subevent)
if self.request.query_params.get(
'ignore_status', 'false') != 'true' and not ignore_status:
qs = qs.filter(
order__status__in=[Order.STATUS_PAID, Order.STATUS_PENDING]
if self.checkinlist.include_pending else [Order.STATUS_PAID])
if self.request.query_params.get('pdf_data', 'false') == 'true':
qs = qs.prefetch_related(
Prefetch(lookup='checkins',
queryset=Checkin.objects.filter(
list_id=self.checkinlist.pk)), 'checkins',
'answers', 'answers__options', 'answers__question',
Prefetch(
'addons',
OrderPosition.objects.select_related('item', 'variation')),
Prefetch(
'order',
Order.objects.select_related(
'invoice_address').prefetch_related(
Prefetch(
'event',
Event.objects.select_related('organizer')),
Prefetch(
'positions',
OrderPosition.objects.prefetch_related(
'checkins',
'item',
'variation',
'answers',
'answers__options',
'answers__question',
))))).select_related('item', 'variation',
'item__category',
'addon_to', 'order',
'order__invoice_address',
'seat')
else:
qs = qs.prefetch_related(
Prefetch(lookup='checkins',
queryset=Checkin.objects.filter(
list_id=self.checkinlist.pk)), 'answers',
'answers__options', 'answers__question',
Prefetch(
'addons',
OrderPosition.objects.select_related(
'item', 'variation'))).select_related(
'item', 'variation', 'order', 'addon_to',
'order__invoice_address', 'order', 'seat')
if not self.checkinlist.all_products and not ignore_products:
qs = qs.filter(item__in=self.checkinlist.limit_products.
values_list('id', flat=True))
if 'subevent' in self.request.query_params.getlist('expand'):
qs = qs.prefetch_related('subevent', 'subevent__event',
'subevent__subeventitem_set',
'subevent__subeventitemvariation_set',
'subevent__seat_category_mappings',
'subevent__meta_values')
if 'item' in self.request.query_params.getlist('expand'):
qs = qs.prefetch_related(
'item', 'item__addons', 'item__bundles', 'item__meta_values',
'item__variations').select_related('item__tax_rule')
if 'variation' in self.request.query_params.getlist('expand'):
qs = qs.prefetch_related('variation')
if 'pk' not in self.request.resolver_match.kwargs and 'can_view_orders' not in self.request.eventpermset \
and len(self.request.query_params.get('search', '')) < 3:
qs = qs.none()
return qs
def is_in_role(role_name, user_id, project_id):
return RoleMapping.objects.filter(
user_id=user_id,
project_id=project_id,
role_id=Subquery(Role.objects.filter(name=role_name).values('id')),
).exists()
def _compute_orders(self, quotas, q_items, q_vars, size_left):
events = {q.event_id for q in quotas}
subevents = {q.subevent_id for q in quotas}
seq = Q(subevent_id__in=subevents)
if None in subevents:
seq |= Q(subevent__isnull=True)
op_lookup = OrderPosition.objects.filter(
order__status__in=[Order.STATUS_PAID, Order.STATUS_PENDING],
order__event_id__in=events,
).filter(seq).filter(
Q(
Q(variation_id__isnull=True) & Q(
item_id__in={
i['item_id']
for i in q_items
if self._quota_objects[i['quota_id']] in quotas
})) | Q(
variation_id__in={
i['itemvariation_id']
for i in q_vars
if self._quota_objects[i['quota_id']] in quotas
})).order_by()
if any(q.release_after_exit for q in quotas):
op_lookup = op_lookup.annotate(
last_entry=Subquery(
Checkin.objects.filter(
position_id=OuterRef('pk'),
list__allow_entry_after_exit=False,
type=Checkin.TYPE_ENTRY,
).order_by().values('position_id').annotate(
m=Max('datetime')).values('m')),
last_exit=Subquery(
Checkin.objects.filter(
position_id=OuterRef('pk'),
list__allow_entry_after_exit=False,
type=Checkin.TYPE_EXIT,
).order_by().values('position_id').annotate(
m=Max('datetime')).values('m')),
).annotate(is_exited=Case(
When(
Q(last_entry__isnull=False) & Q(last_exit__isnull=False)
& Q(last_exit__gt=F('last_entry')),
then=Value(1, output_field=models.IntegerField()),
),
default=Value(0, output_field=models.IntegerField()),
output_field=models.IntegerField(),
), )
else:
op_lookup = op_lookup.annotate(
is_exited=Value(0, output_field=models.IntegerField()))
op_lookup = op_lookup.values('order__status', 'item_id', 'subevent_id',
'variation_id',
'is_exited').annotate(c=Count('*'))
for line in sorted(
op_lookup,
key=lambda li: (int(li['is_exited']), li['order__status']),
reverse=True): # p before n, exited before non-exited
if line['variation_id']:
qs = self._var_to_quotas[line['variation_id']]
else:
qs = self._item_to_quotas[line['item_id']]
for q in qs:
if q.subevent_id == line['subevent_id']:
if q.release_after_exit and line['is_exited']:
self.count_exited_orders[q] += line['c']
else:
size_left[q] -= line['c']
if line['order__status'] == Order.STATUS_PAID:
self.count_paid_orders[q] += line['c']
q.cached_availability_paid_orders = self.count_paid_orders[
q]
elif line['order__status'] == Order.STATUS_PENDING:
self.count_pending_orders[q] += line['c']
if size_left[q] <= 0 and q not in self.results:
if line['order__status'] == Order.STATUS_PAID:
self.results[q] = Quota.AVAILABILITY_GONE, 0
else:
self.results[q] = Quota.AVAILABILITY_ORDERED, 0
def confidence_summary(self, request):
"""
Generate a summary of geocoding confidence values for property and
tax lot records.
"""
body = dict(request.data)
org_id = self.get_organization(request)
property_view_ids = body.get('property_view_ids')
taxlot_view_ids = body.get('taxlot_view_ids')
result = {}
if property_view_ids:
property_views = PropertyView.objects.filter(
id__in=property_view_ids,
cycle__organization_id=org_id
)
result["properties"] = {
'not_geocoded': PropertyState.objects.filter(
id__in=Subquery(property_views.values('state_id')),
geocoding_confidence__isnull=True
).count(),
'high_confidence': PropertyState.objects.filter(
id__in=Subquery(property_views.values('state_id')),
geocoding_confidence__startswith='High'
).count(),
'low_confidence': PropertyState.objects.filter(
id__in=Subquery(property_views.values('state_id')),
geocoding_confidence__startswith='Low'
).count(),
'manual': PropertyState.objects.filter(
id__in=Subquery(property_views.values('state_id')),
geocoding_confidence='Manually geocoded (N/A)'
).count(),
'missing_address_components': PropertyState.objects.filter(
id__in=Subquery(property_views.values('state_id')),
geocoding_confidence='Missing address components (N/A)'
).count(),
}
if taxlot_view_ids:
taxlot_views = TaxLotView.objects.filter(
id__in=taxlot_view_ids,
cycle__organization_id=org_id
)
result["tax_lots"] = {
'not_geocoded': TaxLotState.objects.filter(
id__in=Subquery(taxlot_views.values('state_id')),
geocoding_confidence__isnull=True
).count(),
'high_confidence': TaxLotState.objects.filter(
id__in=Subquery(taxlot_views.values('state_id')),
geocoding_confidence__startswith='High'
).count(),
'low_confidence': TaxLotState.objects.filter(
id__in=Subquery(taxlot_views.values('state_id')),
geocoding_confidence__startswith='Low'
).count(),
'manual': TaxLotState.objects.filter(
id__in=Subquery(taxlot_views.values('state_id')),
geocoding_confidence='Manually geocoded (N/A)'
).count(),
'missing_address_components': TaxLotState.objects.filter(
id__in=Subquery(taxlot_views.values('state_id')),
geocoding_confidence='Missing address components (N/A)'
).count(),
}
return result
def get_all_tx_hashes(self,
safe_address: str,
queued: bool = True,
trusted: bool = True) -> QuerySet:
"""
Build a queryset with hashes for every tx for a Safe for pagination filtering. In the case of
Multisig Transactions, as some of them are not mined, we use the SafeTxHash
Criteria for building this list:
- Return only multisig txs with `nonce < current Safe Nonce`
- The endpoint should only show incoming transactions that have been mined
- The transactions should be sorted by execution date. If an outgoing transaction doesn't have an execution
date the execution date of the transaction with the same nonce that has been executed should be taken.
- Incoming and outgoing transfers or Eth/tokens must be under a multisig/module tx if triggered by one.
Otherwise they should have their own entry in the list using a EthereumTx
:param safe_address:
:param queued: By default `True`, all transactions are returned. With `False`, just txs wih
`nonce < current Safe Nonce` are returned.
:param trusted: By default `True`, just txs that are trusted are returned (with at least one confirmation,
sent by a delegate or indexed). With `False` all txs are returned
:return: List with tx hashes sorted by date (newest first)
"""
# If tx is not mined, get the execution date of a tx mined with the same nonce
case = Case(When(ethereum_tx__block=None,
then=MultisigTransaction.objects.filter(
safe=OuterRef('safe'),
nonce=OuterRef('nonce')).exclude(
ethereum_tx__block=None).values(
'ethereum_tx__block__timestamp')),
default=F('ethereum_tx__block__timestamp'))
multisig_safe_tx_ids = MultisigTransaction.objects.filter(
safe=safe_address).annotate(
execution_date=case,
block=F('ethereum_tx__block_id'),
safe_nonce=F('nonce'),
).values(
'safe_tx_hash', 'execution_date', 'created', 'block',
'safe_nonce'
) # Tricky, we will merge SafeTx hashes with EthereumTx hashes
# Block is needed to get stable ordering
if not queued: # Filter out txs with nonce >= Safe nonce
last_nonce_query = MultisigTransaction.objects.filter(
safe=safe_address).exclude(
ethereum_tx=None).order_by('-nonce').values('nonce')
multisig_safe_tx_ids = multisig_safe_tx_ids.filter(
nonce__lte=Subquery(last_nonce_query[:1]))
if trusted: # Just show trusted transactions
multisig_safe_tx_ids = multisig_safe_tx_ids.filter(trusted=True)
# Get module txs
module_tx_ids = ModuleTransaction.objects.filter(
safe=safe_address).annotate(
execution_date=F('internal_tx__ethereum_tx__block__timestamp'),
block=F('internal_tx__ethereum_tx__block_id'),
safe_nonce=Value(0, output_field=Uint256Field()),
).distinct().values('internal_tx__ethereum_tx_id',
'execution_date', 'created', 'block',
'safe_nonce')
multisig_hashes = MultisigTransaction.objects.filter(
safe=safe_address).exclude(
ethereum_tx=None).values('ethereum_tx_id')
module_hashes = ModuleTransaction.objects.filter(
safe=safe_address).values('internal_tx__ethereum_tx_id')
multisig_and_module_hashes = multisig_hashes.union(module_hashes)
# Get incoming/outgoing tokens not included on Multisig or Module txs.
# Outgoing tokens can be triggered by another user after the Safe calls `approve`, that's why it will not
# always appear as a MultisigTransaction
event_tx_ids = EthereumEvent.objects.erc20_and_721_events().filter(
Q(arguments__to=safe_address)
| Q(arguments__from=safe_address)).exclude(
ethereum_tx__in=multisig_and_module_hashes).annotate(
execution_date=F('ethereum_tx__block__timestamp'),
created=F('ethereum_tx__block__timestamp'),
block=F('ethereum_tx__block_id'),
safe_nonce=Value(0, output_field=Uint256Field()),
).distinct().values('ethereum_tx_id', 'execution_date',
'created', 'block', 'safe_nonce')
# Get incoming txs not included on Multisig or Module txs
internal_tx_ids = InternalTx.objects.filter(
call_type=EthereumTxCallType.CALL.value,
value__gt=0,
to=safe_address,
).exclude(ethereum_tx__in=multisig_and_module_hashes).annotate(
execution_date=F('ethereum_tx__block__timestamp'),
created=F('ethereum_tx__block__timestamp'),
block=F('ethereum_tx__block_id'),
safe_nonce=Value(0, output_field=Uint256Field()),
).distinct().values('ethereum_tx_id', 'execution_date', 'created',
'block', 'safe_nonce')
# Tricky, we merge SafeTx hashes with EthereumTx hashes
queryset = multisig_safe_tx_ids.distinct().union(event_tx_ids).union(
internal_tx_ids).union(module_tx_ids).order_by(
'-execution_date', '-safe_nonce', 'block', '-created')
# Order by block because `block_number < NULL`, so txs mined will have preference,
# and `created` to get always the same ordering with not executed transactions, as they will share
# the same `execution_date` that the mined tx
return queryset
def portdetail_stats(request):
days = request.GET.get('days', 30)
days_ago = request.GET.get('days_ago', 0)
# Validate days and days_ago
for value in days, days_ago:
check, message = validate_stats_days(value)
if check is False:
return HttpResponse(message)
port_name = request.GET.get('port_name')
port = Port.objects.get(name__iexact=port_name)
days = int(days)
days_ago = int(days_ago)
end_date = datetime.datetime.now(tz=datetime.timezone.utc) - datetime.timedelta(days=days_ago)
start_date = end_date - datetime.timedelta(days=days)
today_day = datetime.datetime.now().day
last_12_months = datetime.datetime.now(tz=datetime.timezone.utc) - datetime.timedelta(days=int(today_day)+365)
# Section for calculation of current stats
submissions = Submission.objects.filter(timestamp__range=[start_date, end_date]).order_by('user', '-timestamp').distinct('user')
port_installations = PortInstallation.objects.filter(submission_id__in=Subquery(submissions.values('id')), port__iexact=port_name)
requested_port_installations_count = port_installations.filter(requested=True).aggregate(Count('submission__user_id', distinct=True))
total_port_installations_count = port_installations.aggregate(Count('submission__user_id', distinct=True))
port_installations_by_port_version = sort_list_of_dicts_by_version(list(port_installations.values('version').annotate(num=Count('version'))), 'version')
port_installations_by_os_and_xcode_version = sort_list_of_dicts_by_version(list(port_installations.values('submission__xcode_version', 'submission__os_version').annotate(num=Count('submission__user_id', distinct=True))), 'submission__os_version')
port_installations_by_os_and_clt_version = sort_list_of_dicts_by_version(list(port_installations.values('submission__clt_version', 'submission__os_version').annotate(num=Count('submission__user_id', distinct=True))), 'submission__os_version')
port_installations_by_os_stdlib_build_arch = sort_list_of_dicts_by_version(list(port_installations.values('submission__os_version', 'submission__build_arch', 'submission__cxx_stdlib').annotate(num=Count('submission__user_id', distinct=True))), 'submission__os_version')
port_installations_by_variants = port_installations.values('variants').annotate(num=Count('submission__user_id', distinct=True))
port_installations_by_month = PortInstallation.objects\
.select_related('submission')\
.only('submission__user', 'submission__timestamp', 'port')\
.filter(port__iexact=port_name,submission__timestamp__gte=last_12_months)\
.annotate(month=TruncMonth('submission__timestamp'))\
.values('month')\
.annotate(num=Count('submission__user', distinct=True))
port_installations_by_version_and_month = PortInstallation.objects\
.select_related('submission')\
.only('submission__user', 'submission__timestamp', 'port')\
.filter(port__iexact=port_name, submission__timestamp__gte=last_12_months)\
.annotate(month=TruncMonth('submission__timestamp'))\
.values('month', 'version')\
.annotate(num=Count('submission__user_id', distinct=True))
return render(request, 'ports/port-detail/installation_stats.html', {
'requested_port_installations_count': requested_port_installations_count,
'total_port_installations_count': total_port_installations_count,
'port_installations_by_port_version': port_installations_by_port_version,
'port_installations_by_os_and_xcode_version': port_installations_by_os_and_xcode_version,
'port_installations_by_os_and_clt_version': port_installations_by_os_and_clt_version,
'port_installations_by_month': port_installations_by_month,
'port_installations_by_version_and_month': port_installations_by_version_and_month,
'port_installations_by_os_stdlib_build_arch': port_installations_by_os_stdlib_build_arch,
'port_installations_by_variants': port_installations_by_variants,
'days': days,
'days_ago': days_ago,
'end_date': end_date,
'start_date': start_date,
'users_in_duration_count': submissions.count(),
'allowed_days': ALLOWED_DAYS_FOR_STATS
})
def without_bed(self):
transplants = Transplanting.objects.filter(active=True).all()
return self.exclude(id__in=Subquery(transplants.values('plant')))
def __init__(self, org_info, *args, **kwargs):
super().__init__(*args, **kwargs)
self.fields['PROJECT_ID'].queryset = AR_product.objects.filter(
ORG_ID__in=Subquery(org_info.values("id")))
def annotate_with_numbers(qs, event):
"""
Modifies a queryset of checkin lists by annotating it with the number of order positions and
checkins associated with it.
"""
# Import here to prevent circular import
from . import Order, OrderPosition, Item
# This is the mother of all subqueries. Sorry. I try to explain it, at least?
# First, we prepare a subquery that for every check-in that belongs to a paid-order
# position and to the list in question. Then, we check that it also belongs to the
# correct subevent (just to be sure) and aggregate over lists (so, over everything,
# since we filtered by lists).
cqs_paid = Checkin.objects.filter(
position__order__event=event,
position__order__status=Order.STATUS_PAID,
list=OuterRef('pk')
).filter(
# This assumes that in an event with subevents, *all* positions have subevents
# and *all* checkin lists have a subevent assigned
Q(position__subevent=OuterRef('subevent'))
| (Q(position__subevent__isnull=True))).order_by().values(
'list').annotate(c=Count('*')).values('c')
cqs_paid_and_pending = Checkin.objects.filter(
position__order__event=event,
position__order__status__in=[
Order.STATUS_PAID, Order.STATUS_PENDING
],
list=OuterRef('pk')
).filter(
# This assumes that in an event with subevents, *all* positions have subevents
# and *all* checkin lists have a subevent assigned
Q(position__subevent=OuterRef('subevent'))
| (Q(position__subevent__isnull=True))).order_by().values(
'list').annotate(c=Count('*')).values('c')
# Now for the hard part: getting all order positions that contribute to this list. This
# requires us to use TWO subqueries. The first one, pqs_all, will only be used for check-in
# lists that contain all the products of the event. This is the simpler one, it basically
# looks like the check-in counter above.
pqs_all_paid = OrderPosition.objects.filter(
order__event=event,
order__status=Order.STATUS_PAID,
).filter(
# This assumes that in an event with subevents, *all* positions have subevents
# and *all* checkin lists have a subevent assigned
Q(subevent=OuterRef('subevent'))
| (Q(subevent__isnull=True))).order_by().values(
'order__event').annotate(c=Count('*')).values('c')
pqs_all_paid_and_pending = OrderPosition.objects.filter(
order__event=event,
order__status__in=[Order.STATUS_PAID, Order.STATUS_PENDING]
).filter(
# This assumes that in an event with subevents, *all* positions have subevents
# and *all* checkin lists have a subevent assigned
Q(subevent=OuterRef('subevent'))
| (Q(subevent__isnull=True))).order_by().values(
'order__event').annotate(c=Count('*')).values('c')
# Now we need a subquery for the case of checkin lists that are limited to certain
# products. We cannot use OuterRef("limit_products") since that would do a cross-product
# with the products table and we'd get duplicate rows in the output with different annotations
# on them, which isn't useful at all. Therefore, we need to add a second layer of subqueries
# to retrieve all of those items and then check if the item_id is IN this subquery result.
pqs_limited_paid = OrderPosition.objects.filter(
order__event=event,
order__status=Order.STATUS_PAID,
item_id__in=Subquery(
Item.objects.filter(
checkinlist__pk=OuterRef(OuterRef('pk'))).values('pk'))
).filter(
# This assumes that in an event with subevents, *all* positions have subevents
# and *all* checkin lists have a subevent assigned
Q(subevent=OuterRef('subevent'))
| (Q(subevent__isnull=True))).order_by().values(
'order__event').annotate(c=Count('*')).values('c')
pqs_limited_paid_and_pending = OrderPosition.objects.filter(
order__event=event,
order__status__in=[Order.STATUS_PAID, Order.STATUS_PENDING],
item_id__in=Subquery(
Item.objects.filter(
checkinlist__pk=OuterRef(OuterRef('pk'))).values('pk'))
).filter(
# This assumes that in an event with subevents, *all* positions have subevents
# and *all* checkin lists have a subevent assigned
Q(subevent=OuterRef('subevent'))
| (Q(subevent__isnull=True))).order_by().values(
'order__event').annotate(c=Count('*')).values('c')
# Finally, we put all of this together. We force empty subquery aggregates to 0 by using Coalesce()
# and decide which subquery to use for this row. In the end, we compute an integer percentage in case
# we want to display a progress bar.
return qs.annotate(
checkin_count=Coalesce(
Case(When(include_pending=True,
then=Subquery(cqs_paid_and_pending,
output_field=models.IntegerField())),
default=Subquery(cqs_paid,
output_field=models.IntegerField()),
output_field=models.IntegerField()), 0),
position_count=Coalesce(
Case(When(all_products=True,
include_pending=False,
then=Subquery(pqs_all_paid,
output_field=models.IntegerField())),
When(all_products=True,
include_pending=True,
then=Subquery(pqs_all_paid_and_pending,
output_field=models.IntegerField())),
When(all_products=False,
include_pending=False,
then=Subquery(pqs_limited_paid,
output_field=models.IntegerField())),
default=Subquery(pqs_limited_paid_and_pending,
output_field=models.IntegerField()),
output_field=models.IntegerField()),
0)).annotate(percent=Case(When(position_count__gt=0,
then=F('checkin_count') * 100 /
F('position_count')),
default=0,
output_field=models.IntegerField()))
def get_users_with_explicit_access(self,
this_privilege,
include_user_granted_access=True,
include_group_granted_access=True,
include_community_granted_access=False):
"""
Gets a QuerySet of Users who have the explicit specified privilege access to the resource.
An empty list is returned if both include_user_granted_access and
include_group_granted_access are set to False.
:param this_privilege: the explicit privilege over the resource for which list of users
needed
:param include_user_granted_access: if True, then users who have been granted directly the
specified privilege will be included in the list
:param include_group_granted_access: if True, then users who have been granted the
specified privilege via group privilege over the resource will be included in the list
:param include_community_granted_access: if True, then users who have been granted the
specified privilege via community privilege over the resource will be included in the list
:return:
"""
incl = None # what to include
excl = None # what to explicitly exclude
if this_privilege == PC.OWNER:
return self.owners # groups and communities cannot own
elif this_privilege == PC.VIEW:
if include_user_granted_access:
incl = Q(u2urp__resource=self.resource,
u2urp__privilege=PC.VIEW)
excl = Q(u2urp__resource=self.resource,
u2urp__privilege__lt=PC.VIEW)
if include_group_granted_access:
i = Q(u2ugp__group__g2grp__resource=self.resource,
u2ugp__group__g2grp__privilege=PC.VIEW)
if incl is not None:
incl = incl | i
else:
incl = i
# exclude higher privilege
e = Q(u2ugp__group__g2grp__resource=self.resource,
u2ugp__group__g2grp__privilege__lt=PC.VIEW)
if excl is not None:
excl = excl | e
else:
excl = e
if include_community_granted_access:
# view privilege results if either group or community privilege is view,
# include exact privilege
i = Q(
u2ugp__group__gaccess__active=True,
u2ugp__group__g2gcp__community__c2gcp__group__g2grp__resource
=self.resource,
u2ugp__group__g2gcp__community__c2gcp__group__gaccess__active
=True)
if incl is not None:
incl = incl | i
else:
incl = i
if incl is not None:
if excl is not None:
# A subquery speeds up the execution of __in by eliminating
# a postgresql invocation, but requires the values operator.
# See Subquery documentation for why this is necessary.
excluded = User.objects.filter(excl).values('pk')
return User.objects.filter(incl)\
.exclude(pk__in=Subquery(excluded))\
.distinct()
else:
return User.objects.filter(incl)
else:
return User.objects.none()
elif this_privilege == PC.CHANGE:
if include_user_granted_access:
incl = Q(u2urp__resource=self.resource,
u2urp__privilege=PC.CHANGE)
excl = Q(u2urp__resource=self.resource,
u2urp__privilege=PC.OWNER)
if include_group_granted_access:
i = Q(u2ugp__group__gaccess__active=True,
u2ugp__group__g2grp__resource=self.resource,
u2ugp__group__g2grp__privilege=PC.CHANGE)
if incl is not None:
incl = incl | i
else:
incl = i
# There is no higher privilege than CHANGE, so there is no need to exclude
if incl is not None:
if excl is not None:
# A subquery speeds up the execution of __in by eliminating
# a postgresql invocation, but requires the values operator.
# See Subquery documentation for why this is necessary.
excluded = User.objects.filter(excl).values('pk')
return User.objects\
.filter(incl)\
.exclude(pk__in=Subquery(excluded))\
.distinct()
else:
return User.objects.filter(incl)
else:
return User.objects.none()
else: # invalid privilege given
return User.objects.none()
def filter_user_projects(self, request):
membership_model = apps.get_model('projects', 'Membership')
memberships_project_ids = membership_model.objects.filter(
user=request.user).values('project_id')
return Subquery(memberships_project_ids)
def post(self, request):
if request.is_ajax():
data = []
checked_records = CheckedRecord.objects.filter(
status='approved',
checked_by__in=Subquery(
User.objects.filter(role=5).values('pk')))
records = Record.objects.filter(
pk__in=Subquery(checked_records.values('record_id')))
# graphs
if request.POST.get('graphs'):
basic_count = Record.objects.filter(classification=1).count()
applied_count = Record.objects.filter(classification=2).count()
psced_count = []
records_per_year_count = []
psced_per_year_count = []
psced_classifications = PSCEDClassification.objects.all()
records_per_year = Record.objects.values(
'year_accomplished').annotate(
year_count=Count('year_accomplished')).order_by(
'year_accomplished')[:10]
for psced in psced_classifications:
psced_count.append({
'name':
psced.name,
'count':
Record.objects.filter(
psced_classification=PSCEDClassification.objects.
get(pk=psced.id)).count()
})
for record_per_year in records_per_year:
records_per_year_count.append({
'year':
record_per_year['year_accomplished'],
'count':
record_per_year['year_count']
})
with connection.cursor() as cursor:
cursor.execute(
"SELECT year_accomplished, COUNT(year_accomplished) AS year_count FROM (SELECT DISTINCT year_accomplished, psced_classification_id FROM records_record) as tbl GROUP BY year_accomplished"
)
rows = cursor.fetchall()
for row in rows:
psced_per_year_count.append({
'year': row[0],
'psced_count': row[1]
})
return JsonResponse({
'success':
True,
'basic':
basic_count,
'applied':
applied_count,
'psced_count':
psced_count,
'records_per_year_count':
records_per_year_count,
'psced_per_year_count':
psced_per_year_count
})
# removing records
elif request.POST.get('remove'):
titles = request.POST.getlist('titles[]')
for title_id in titles:
del_record = Record.objects.get(pk=int(title_id))
del_record.abstract_file.delete()
del_record.delete()
return JsonResponse({'success': True})
# removing accounts
elif request.POST.get('remove-accounts'):
accounts = request.POST.getlist('accounts[]')
success = False
for account_id in accounts:
del_account = User.objects.get(pk=int(account_id))
if not del_account.is_superuser:
del_account.delete()
success = True
return JsonResponse({'success': success})
# filtering records from an ajax request
elif request.POST.get('is_filtered') == 'True':
year_from_filter = request.POST.get('year_from', '0')
year_to_filter = request.POST.get('year_to', '0')
classification_filter = request.POST.get('classification')
psced_classification_filter = request.POST.get(
'psced_classification')
publication_filter = request.POST.get('publication')
if year_from_filter != '' or year_to_filter != '':
records = records.filter(year_accomplished__gte=year_from_filter)\
.filter(year_accomplished__lte=year_to_filter)
if classification_filter != '':
records = records.filter(
classification=classification_filter)
if psced_classification_filter != '':
records = records.filter(
psced_classification=psced_classification_filter)
if publication_filter != '':
publications = Publication.objects.filter(
name=publication_filter)
if len(publications) > 0:
records = records.filter(
publication=publications.first())
else:
records = []
# accounts role change
elif request.POST.get('role-change') == 'true':
accounts = request.POST.getlist('accounts[]')
role_id = int(request.POST.get('role-radio'))
for account_id in accounts:
user = User.objects.get(pk=int(account_id))
user.role = UserRole.objects.get(pk=role_id)
user.save()
# setting datatable records
for record in records:
data.append([
'', record.pk, '<a href="/record/' + str(record.pk) +
'">' + record.title + '</a>', record.year_accomplished,
record.classification.name,
record.psced_classification.name
])
return JsonResponse({"data": data})
else:
# if filter save button is clicked
if request.POST.get('is_filtered') == 'true':
# code if checkbox for year accomplished is clicked
filters = {'is_filtered': False}
if request.POST.get('year_cb', 'off') == 'on':
filters['year_from'] = request.POST.get('year_from')
filters['year_to'] = request.POST.get('year_to')
filters['is_filtered'] = True
if request.POST.get('classification') != '':
filters['classification'] = request.POST.get(
'classification')
filters['is_filtered'] = True
if request.POST.get('psced_classification') != '':
filters['psced_classification'] = request.POST.get(
'psced_classification')
filters['is_filtered'] = True
if request.POST.get('publication_cb', 'off') == 'on':
filters['publication'] = request.POST.get('publication')
filters['is_filtered'] = True
context = {
'records': Record.objects.all(),
'record_form': forms.RecordForm(),
'filters': filters,
}
return render(request, self.name, context)
def api_v1_contest_detail(request, contest):
contest = get_object_or_404(Contest, key=contest)
if not contest.is_accessible_by(request.user):
raise Http404()
in_contest = contest.is_in_contest(request.user)
can_see_rankings = contest.can_see_full_scoreboard(request.user)
problems = list(
contest.contest_problems.select_related('problem').defer(
'problem__description').order_by('order'))
new_ratings_subquery = Rating.objects.filter(participation=OuterRef('pk'))
old_ratings_subquery = (Rating.objects.filter(
user=OuterRef('user__pk'),
contest__end_time__lt=OuterRef('contest__end_time')).order_by(
'-contest__end_time'))
participations = (contest.users.filter(virtual=0).annotate(
new_rating=Subquery(new_ratings_subquery.values('rating')[:1])
).annotate(old_rating=Subquery(old_ratings_subquery.values(
'rating')[:1])).prefetch_related('user__organizations').annotate(
username=F('user__user__username')).order_by(
'-score', 'cumtime', 'tiebreaker') if can_see_rankings else [])
can_see_problems = (in_contest or contest.ended
or contest.is_editable_by(request.user))
return JsonResponse({
'time_limit':
contest.time_limit and contest.time_limit.total_seconds(),
'start_time':
contest.start_time.isoformat(),
'end_time':
contest.end_time.isoformat(),
'tags':
list(contest.tags.values_list('name', flat=True)),
'is_rated':
contest.is_rated,
'rate_all':
contest.is_rated and contest.rate_all,
'has_rating':
contest.ratings.exists(),
'rating_floor':
contest.rating_floor,
'rating_ceiling':
contest.rating_ceiling,
'format': {
'name': contest.format_name,
'config': contest.format_config,
},
'problems': [{
'points': int(problem.points),
'partial': problem.partial,
'name': problem.problem.name,
'code': problem.problem.code,
} for problem in problems] if can_see_problems else [],
'rankings': [{
'user':
participation.username,
'points':
participation.score,
'cumtime':
participation.cumtime,
'tiebreaker':
participation.tiebreaker,
'old_rating':
participation.old_rating,
'new_rating':
participation.new_rating,
'is_disqualified':
participation.is_disqualified,
'solutions':
contest.format.get_problem_breakdown(participation, problems),
} for participation in participations],
})
def validate(self, attrs):
super().validate(attrs)
default_betalingsindicatie = (self.instance.betalingsindicatie
if self.instance else None)
betalingsindicatie = attrs.get("betalingsindicatie",
default_betalingsindicatie)
if betalingsindicatie == BetalingsIndicatie.nvt and attrs.get(
"laatste_betaaldatum"):
raise serializers.ValidationError(
{
"laatste_betaaldatum":
_('Laatste betaaldatum kan niet gezet worden als de betalingsindicatie "nvt" is'
)
},
code="betaling-nvt",
)
# check that productenOfDiensten are part of the ones on the zaaktype
default_zaaktype = self.instance.zaaktype if self.instance else None
zaaktype = attrs.get("zaaktype", default_zaaktype)
assert zaaktype, "Should not have passed validation - a zaaktype is needed"
producten_of_diensten = attrs.get("producten_of_diensten")
if producten_of_diensten:
if not set(producten_of_diensten).issubset(
set(zaaktype.producten_of_diensten)):
raise serializers.ValidationError(
{
"producten_of_diensten":
_("Niet alle producten/diensten komen voor in "
"de producten/diensten op het zaaktype")
},
code="invalid-products-services",
)
# Archiving
default_archiefstatus = (self.instance.archiefstatus if self.instance
else Archiefstatus.nog_te_archiveren)
archiefstatus = (attrs.get("archiefstatus", default_archiefstatus) !=
Archiefstatus.nog_te_archiveren)
if archiefstatus:
# search for related informatieobjects with status != 'gearchiveerd'
canonical_ids = self.instance.zaakinformatieobject_set.values(
"informatieobject_id")
io_ids = (EnkelvoudigInformatieObjectCanonical.objects.filter(
id__in=Subquery(canonical_ids)).annotate(
last=Max("enkelvoudiginformatieobject")).values("last"))
if (EnkelvoudigInformatieObject.objects.filter(
id__in=Subquery(io_ids)).exclude(
status="gearchiveerd").exists()):
raise serializers.ValidationError(
{
"archiefstatus",
_("Er zijn gerelateerde informatieobjecten waarvan de `status` nog niet gelijk is aan "
"`gearchiveerd`. Dit is een voorwaarde voor het zetten van de `archiefstatus` op een andere "
"waarde dan `nog_te_archiveren`."),
},
code="documents-not-archived",
)
for attr in ["archiefnominatie", "archiefactiedatum"]:
if not attrs.get(
attr,
getattr(self.instance, attr)
if self.instance else None):
raise serializers.ValidationError(
{
attr:
_("Moet van een waarde voorzien zijn als de 'Archiefstatus' een waarde heeft anders dan "
"'nog_te_archiveren'.")
},
code=f"{attr}-not-set",
)
# End archiving
return attrs
def generar_resultados(self, fecha_inicial, fecha_final,
con_mo_saldo_inicial, proyecto):
context = {}
mano_obra = HoraHojaTrabajo.objects.values('literal').annotate(
horas_trabajadas=ExpressionWrapper(
Coalesce(Sum('cantidad_minutos') / 60, 0),
output_field=DecimalField(max_digits=2)),
costo_total=ExpressionWrapper(
Sum(
Coalesce(F('cantidad_minutos') / 60, 0) *
(F('hoja__tasa__costo') /
F('hoja__tasa__nro_horas_mes_trabajadas'))),
output_field=DecimalField(max_digits=4))).filter(
verificado=True,
literal_id=OuterRef('id'),
)
materiales = ItemsLiteralBiable.objects.values('literal').annotate(
costo_total=Coalesce(Sum('costo_total'), 0)).filter(
literal_id=OuterRef('id'))
if fecha_inicial and fecha_final:
materiales = materiales.filter(lapso__lte=fecha_final,
lapso__gte=fecha_inicial)
mano_obra = mano_obra.filter(hoja__fecha__lte=fecha_final,
hoja__fecha__gte=fecha_inicial)
qsLiterales = Literal.objects
if proyecto:
qsLiterales = qsLiterales.filter(proyecto=proyecto)
qsLiterales = qsLiterales.annotate(
costo_mano_obra_iniciales=ExpressionWrapper(
Coalesce(Sum('mis_horas_trabajadas_iniciales__valor'), 0),
output_field=DecimalField(max_digits=4)),
cantidad_mano_obra_iniciales=ExpressionWrapper(
Coalesce(
Sum('mis_horas_trabajadas_iniciales__cantidad_minutos'),
0) / 60,
output_field=DecimalField(max_digits=4)),
cantidad_horas_trabajadas=ExpressionWrapper(
Subquery(mano_obra.values('horas_trabajadas')),
output_field=DecimalField(max_digits=4)),
costo_mano_obra=ExpressionWrapper(
Subquery(mano_obra.values('costo_total')),
output_field=DecimalField(max_digits=4)),
costo_mis_materiales=ExpressionWrapper(
Coalesce(Subquery(materiales.values('costo_total')), 0),
output_field=DecimalField(max_digits=4))).distinct()
total_costo_mo = 0
total_costo_mo_ini = 0
total_horas_mo_ini = 0
total_horas_mo = 0
for literal in qsLiterales:
if literal.cantidad_horas_trabajadas:
total_horas_mo += literal.cantidad_horas_trabajadas
if literal.cantidad_mano_obra_iniciales and con_mo_saldo_inicial:
total_horas_mo_ini += literal.cantidad_mano_obra_iniciales
if literal.costo_mano_obra:
total_costo_mo += literal.costo_mano_obra
if literal.costo_mano_obra_iniciales and con_mo_saldo_inicial:
total_costo_mo_ini += literal.costo_mano_obra_iniciales
total_costo_materiales = qsLiterales.aggregate(
Sum('costo_mis_materiales'))['costo_mis_materiales__sum']
context['tipo_consulta'] = 'Todo'
if fecha_inicial and fecha_final:
context['tipo_consulta'] = 'por lapso'
context['fecha_inicial'] = fecha_inicial
context['fecha_final'] = fecha_final
context['literales'] = qsLiterales
context['proyecto'] = proyecto
context['con_mo_saldo_inicial'] = con_mo_saldo_inicial
context['total_costo_mo'] = total_costo_mo
context['total_costo_mo_ini'] = total_costo_mo_ini
context['total_costo_materiales'] = total_costo_materiales
context[
'total_costo'] = total_costo_mo + total_costo_mo_ini + total_costo_materiales
context['total_horas_mo'] = total_horas_mo
context['total_horas_mo_ini'] = total_horas_mo_ini
return context
def __get_buildings(
cls,
condition: Q,
auth_user: VacancyUser,
is_residential: bool,
is_non_residential: bool,
order=[],
):
"""建物リスト取得用メソッド"""
if not auth_user:
raise Exception
conditions = Q(is_deleted=False)
if condition:
conditions.add(condition, Q.AND)
if is_non_residential:
conditions.add(Q(building_type__is_building=True),
Q.AND) # 非居住用が指定されている場合は建物タイプ全て（居住用も含む）
elif is_residential:
conditions.add(Q(building_type__is_residential=True),
Q.AND) # 居住用のみが指定されている場合は居住用のみ
if auth_user.is_company:
# 自社ユーザの場合、管理物件と専任物件を表示
conditions.add(
Q(management_type__is_own=True)
| Q(management_type__is_entrusted=True), Q.AND)
if not settings.INCLUDE_NO_ROOM_BUILDINGS or not is_residential:
# 自社ユーザの時に空き無し物件を含まない設定の場合、または居住用一覧でない場合は、空き無し物件は除外
conditions.add(
Q(id=Subquery(
Room.objects.filter(
Room.get_vacancy_room_condition(
auth_user, is_residential, is_non_residential)
).filter(building_id=OuterRef('pk'), ).values(
'building_id')[:1])), Q.AND)
else:
# 自社ユーザ以外の場合、空室有りの管理物件のみを表示
conditions.add(Q(management_type__is_own=True), Q.AND)
# 空き無し物件は除外
conditions.add(
Q(id=Subquery(
Room.objects.filter(
Room.get_vacancy_room_condition(
auth_user, is_residential,
is_non_residential)).filter(building_id=OuterRef(
'pk'), ).values('building_id')[:1])), Q.AND)
order_by = order
if len(order_by) == 0:
order_by = [
'building_kana',
'id',
]
buildings = cls.objects.filter(conditions).order_by(*order_by)
for building in buildings:
building.auth_user = auth_user
building.is_residential = is_residential
building.is_non_residential = is_non_residential
return buildings
def applicable_shipping_methods_by_channel(shipping_methods, channel_id):
query = ShippingMethodChannelListing.objects.filter(
shipping_method=OuterRef("pk"),
channel_id=channel_id).values_list("price_amount")
return shipping_methods.annotate(
price_amount=Subquery(query)).order_by("price_amount")
def get_all_links(map_):
devices = Subquery(map_.devices.all().values("pk"))
return Link.objects.filter(local_interface__device__in=devices,
remote_interface__device__in=devices)
class Meta(NodeHandler.Meta):
abstract = False
queryset = (
node_prefetch(Machine.objects.all())
.prefetch_related(
"blockdevice_set__iscsiblockdevice__"
"partitiontable_set__partitions"
)
.prefetch_related(
"blockdevice_set__physicalblockdevice__"
"partitiontable_set__partitions__filesystem_set"
)
.prefetch_related(
"blockdevice_set__virtualblockdevice__"
"partitiontable_set__partitions"
)
)
list_queryset = (
Machine.objects.select_related("owner", "zone", "domain", "bmc")
.prefetch_related(
"blockdevice_set__iscsiblockdevice__"
"partitiontable_set__partitions"
)
.prefetch_related(
"blockdevice_set__physicalblockdevice__"
"partitiontable_set__partitions"
)
.prefetch_related(
"blockdevice_set__physicalblockdevice__numa_node"
)
.prefetch_related(
"blockdevice_set__virtualblockdevice__"
"partitiontable_set__partitions"
)
.prefetch_related(
"interface_set__ip_addresses__subnet__vlan__space"
)
.prefetch_related(
"interface_set__ip_addresses__subnet__vlan__fabric"
)
.prefetch_related("interface_set__numa_node")
.prefetch_related("interface_set__vlan__fabric")
.prefetch_related("boot_interface__vlan__fabric")
.prefetch_related("tags")
.prefetch_related("pool")
.annotate(
status_event_type_description=Subquery(
Event.objects.filter(
node=OuterRef("pk"), type__level__gte=logging.INFO
)
.order_by("-created", "-id")
.values("type__description")[:1]
),
status_event_description=Subquery(
Event.objects.filter(
node=OuterRef("pk"), type__level__gte=logging.INFO
)
.order_by("-created", "-id")
.values("description")[:1]
),
numa_nodes_count=Count("numanode"),
sriov_support=Exists(
Interface.objects.filter(
node=OuterRef("pk"), sriov_max_vf__gt=0
)
),
)
)
allowed_methods = [
"list",
"get",
"create",
"update",
"action",
"set_active",
"check_power",
"create_physical",
"create_vlan",
"create_bond",
"create_bridge",
"update_interface",
"delete_interface",
"link_subnet",
"unlink_subnet",
"mount_special",
"unmount_special",
"update_filesystem",
"update_disk",
"delete_disk",
"delete_partition",
"delete_volume_group",
"delete_cache_set",
"delete_filesystem",
"delete_vmfs_datastore",
"update_vmfs_datastore",
"create_partition",
"create_cache_set",
"create_bcache",
"create_raid",
"create_volume_group",
"create_logical_volume",
"create_vmfs_datastore",
"set_boot_disk",
"apply_storage_layout",
"default_user",
"get_summary_xml",
"get_summary_yaml",
"set_script_result_suppressed",
"set_script_result_unsuppressed",
"get_suppressible_script_results",
"get_latest_failed_testing_script_results",
]
form = AdminMachineWithMACAddressesForm
exclude = [
"creation_type",
"status_expires",
"previous_status",
"parent",
"boot_interface",
"boot_cluster_ip",
"token",
"netboot",
"ephemeral_deploy",
"agent_name",
"power_state_queried",
"power_state_updated",
"gateway_link_ipv4",
"gateway_link_ipv6",
"enable_ssh",
"skip_networking",
"skip_storage",
"instance_power_parameters",
"address_ttl",
"url",
"dns_process",
"managing_process",
"last_image_sync",
"install_rackd",
"install_kvm",
]
list_fields = [
"id",
"system_id",
"hostname",
"locked",
"owner",
"cpu_count",
"cpu_speed",
"description",
"error_description",
"memory",
"power_state",
"domain",
"pool",
"zone",
]
listen_channels = ["machine"]
create_permission = NodePermission.admin
view_permission = NodePermission.view
edit_permission = NodePermission.admin
delete_permission = NodePermission.admin
def iterate_orders(self, form_data: dict):
tz = pytz.timezone(self.event.settings.timezone)
p_date = OrderPayment.objects.filter(
order=OuterRef('pk'),
state__in=(OrderPayment.PAYMENT_STATE_CONFIRMED,
OrderPayment.PAYMENT_STATE_REFUNDED),
payment_date__isnull=False).values('order').annotate(
m=Max('payment_date')).values('m').order_by()
s = OrderPosition.objects.filter(
order=OuterRef('pk')).order_by().values('order').annotate(
k=Count('id')).values('k')
qs = self.event.orders.annotate(
payment_date=Subquery(p_date, output_field=DateTimeField()),
pcnt=Subquery(s, output_field=IntegerField())).select_related(
'invoice_address').prefetch_related('invoices')
if form_data['paid_only']:
qs = qs.filter(status=Order.STATUS_PAID)
tax_rates = self._get_all_tax_rates(qs)
headers = [
_('Order code'),
_('Order total'),
_('Status'),
_('Email'),
_('Order date'),
_('Company'),
_('Name'),
]
name_scheme = PERSON_NAME_SCHEMES[self.event.settings.name_scheme]
if len(name_scheme['fields']) > 1:
for k, label, w in name_scheme['fields']:
headers.append(label)
headers += [
_('Address'),
_('ZIP code'),
_('City'),
_('Country'),
pgettext('address', 'State'),
_('VAT ID'),
_('Date of last payment'),
_('Fees'),
_('Order locale')
]
for tr in tax_rates:
headers += [
_('Gross at {rate} % tax').format(rate=tr),
_('Net at {rate} % tax').format(rate=tr),
_('Tax value at {rate} % tax').format(rate=tr),
]
headers.append(_('Invoice numbers'))
headers.append(_('Sales channel'))
headers.append(_('Requires special attention'))
headers.append(_('Comment'))
headers.append(_('Positions'))
yield headers
full_fee_sum_cache = {
o['order__id']: o['grosssum']
for o in OrderFee.objects.values('tax_rate', 'order__id').order_by(
).annotate(grosssum=Sum('value'))
}
fee_sum_cache = {
(o['order__id'], o['tax_rate']): o
for o in OrderFee.objects.values('tax_rate', 'order__id').order_by(
).annotate(taxsum=Sum('tax_value'), grosssum=Sum('value'))
}
sum_cache = {
(o['order__id'], o['tax_rate']): o
for o in OrderPosition.objects.values('tax_rate', 'order__id').
order_by().annotate(taxsum=Sum('tax_value'), grosssum=Sum('price'))
}
for order in qs.order_by('datetime'):
row = [
order.code,
order.total,
order.get_status_display(),
order.email,
order.datetime.astimezone(tz).strftime('%Y-%m-%d'),
]
try:
row += [
order.invoice_address.company,
order.invoice_address.name,
]
if len(name_scheme['fields']) > 1:
for k, label, w in name_scheme['fields']:
row.append(order.invoice_address.name_parts.get(k, ''))
row += [
order.invoice_address.street,
order.invoice_address.zipcode,
order.invoice_address.city,
order.invoice_address.country
if order.invoice_address.country else
order.invoice_address.country_old,
order.invoice_address.state,
order.invoice_address.vat_id,
]
except InvoiceAddress.DoesNotExist:
row += [''] * (8 + (len(name_scheme['fields'])
if len(name_scheme['fields']) > 1 else 0))
row += [
order.payment_date.astimezone(tz).strftime('%Y-%m-%d')
if order.payment_date else '',
full_fee_sum_cache.get(order.id) or Decimal('0.00'),
order.locale,
]
for tr in tax_rates:
taxrate_values = sum_cache.get((order.id, tr), {
'grosssum': Decimal('0.00'),
'taxsum': Decimal('0.00')
})
fee_taxrate_values = fee_sum_cache.get(
(order.id, tr), {
'grosssum': Decimal('0.00'),
'taxsum': Decimal('0.00')
})
row += [
taxrate_values['grosssum'] +
fee_taxrate_values['grosssum'],
(taxrate_values['grosssum'] - taxrate_values['taxsum'] +
fee_taxrate_values['grosssum'] -
fee_taxrate_values['taxsum']),
taxrate_values['taxsum'] + fee_taxrate_values['taxsum'],
]
row.append(', '.join([i.number for i in order.invoices.all()]))
row.append(order.sales_channel)
row.append(_('Yes') if order.checkin_attention else _('No'))
row.append(order.comment or "")
row.append(order.pcnt)
yield row
from django.contrib.auth.models import User
from django.db.models import Subquery, F, Count, Avg
from django.db.models.functions import Substr
from django.db.models import prefetch_related_objects
from orm_practice_app.models import UserInfo, Product, Company
users = User.objects.filter(id__lte=30)
UserInfo.objects.filter(
owned_user__in=Subquery(User.objects.filter(
id__lte=30).values('id'))).explain()
"""
SELECT "orm_practice_app_userinfo"."id",
"orm_practice_app_userinfo"."owned_user_id",
"orm_practice_app_userinfo"."tel_num"
FROM "orm_practice_app_userinfo"
WHERE "orm_practice_app_userinfo"."owned_user_id"
IN (SELECT U0."id" FROM "auth_user" U0 WHERE U0."id" <= 30)
"""
# EXPLAIN QUERY PLAN
"""
Execution time: 0.000090s [Database: default]
'3 0 0 SEARCH TABLE orm_practice_app_userinfo USING INDEX orm_practice_app_userinfo_owned_user_id_e85907f1 (owned_user_id=?)
7 0 0 LIST SUBQUERY 1
def add_person_id(self, team_id: int):
return self.annotate(person_id=Subquery(
PersonDistinctId.objects.filter(
team_id=team_id, distinct_id=OuterRef(
"distinct_id")).order_by().values("person_id")[:1]))
def get_queryset(self):
## qs = super(Dialogs, self).get_queryset()
## qs = qs.filter(Partakers__in=[self.request.user]).annotate(last=Max('messages__id')) #.filter(last__gt=0)
## qs = qs.order_by('-last')
#это должно быть быстрее, тк будет содержать в предзапросе значение каждого последнего сооб
lastMessage = Subquery(
Message.objects.filter(
Target=OuterRef('id')).order_by('-id').values('Content')[:1])
qs = Dialogue.objects.filter(
Partakers__in=[self.request.user]).annotate(lastMess=lastMessage)
qs = qs.annotate(time=Max('messages__Time')).order_by('-time')
## qs = qs.annotate(time=Max('messages__Time')).annotate(Sender=Max('messages__Sender__username')).order_by('-time')
## не то, т.к. неверный результат
## qs = qs.annotate(SenderImg=Max('messages__Sender__Image'), SenderName=Max('messages__Sender__username')).order_by('-time')
## на 90мс дольше, т.к. один лишний запрос
## MessageSender = Subquery(Profile.objects.filter(msgs=OuterRef('id')).order_by('-id').values('Image')[:1])
## qs = qs.prefetch_related(Prefetch('messages', queryset=Message.objects.annotate(sender_img=MessageSender).select_related('Target'), to_attr='mss'))
#получаем картинку
MessageSender = Subquery(
Profile.objects.filter(
msgs=OuterRef('id')).order_by('-id').values('Image')[:1])
lastMessageSender = Subquery(
Message.objects.filter(Target=OuterRef('id')).annotate(
the_sender=MessageSender).order_by('-id').values('the_sender')
[:1])
qs = qs.annotate(SenderImg=lastMessageSender)
#как взять из промежуточной таблицы значение Time - время последнего посещения диалога
## myLastTime = Dialogue_Partakers.objects.filter(Profile_id=self.request.user.id).filter(Dialogue=OuterRef('id')).values('Time')[:1]
## qs = (
## Dialog
## .objects
## .filter(Partakers__in=[self.request.user])
## .annotate(visit=Subquery(myLastTime))
## .annotate(unread=Sum(
## Case(
## When(messages__Time__gt=F('visit'), then=Value(1)),
## default=Value(0),
## output_field=IntegerField(),
## )
## ))
## )
LastReaded = Dialogue_Partakers.objects.filter(
Profile_id=self.request.user.id).filter(
Dialogue=OuterRef('id')).values('Last')[:1]
qs = qs.annotate(visit=Subquery(
LastReaded)) #.annotate(sender_id=Min('messages__Sender__id'))
qs = qs.annotate(unread=Sum(
Case(
When(Q(messages__id__gt=F('visit'))
& ~Q(messages__Sender=self.request.user),
then=Value(1)),
default=Value(0),
output_field=IntegerField(),
)))
## qs = qs.annotate(count=Count('messages'))
## print [q.last for q in qs]
## mess = Message.objects.filter(id__in=[q.last for q in qs]) # опять вопрос к сортировке
## print mess.count()
## ivar=0
## for q in qs:
## print q.sender_id
## if q.last:
## q.message = mess[ivar]
## ivar+=1
## print qs[0].messages.count()
## print qs[0].messages__count
return qs
