def delete_stale_model_entries(self, model):
existing_pks = (model._default_manager.using(self.db_alias).annotate(
object_id=Cast("pk", TextField())).values("object_id"))
content_types_pks = get_descendants_content_types_pks(model)
stale_entries = self.entries.filter(
content_type_id__in=content_types_pks).exclude(
object_id__in=existing_pks)
stale_entries.delete()
def get(self, request):
query = None
user_id = request.GET.get('user', None)
if user_id is None:
return JsonResponse(None, safe=False)
total_count = BookStore.objects.filter(user__pk=user_id).count()
# categorical value sum, average
# Cast Float value
query = BookStore.objects.values('book_format').annotate(
sum_format=Count('book_format')
).annotate(avg_format=Cast(F('sum_format'), FloatField()) / Cast(total_count, FloatField())
).values('book_format', 'sum_format', 'avg_format')
print(list(query))
return JsonResponse(list(query), safe=False)
def _episodes_queryset(podcast: Podcast) -> QuerySet[Podcast]:
"""
Builds a query set that should give out episodes of a podcast according to
the following criteria:
if not older than 7 days: On any day
else if not older than 30 days: On every 7th day
else if older than 30 days: On every 28th day
"""
now = local_now()
today = local_today()
isocalendar_today = today.isocalendar()
standard_day_of_year_today = isocalendar_today[1] * 7 + isocalendar_today[2]
last_available_cutoff = now - dt.timedelta(days=5)
return (podcast.episodes.exclude(spotify_id=None).filter(
Q(available=True)
| Q(last_available_date_time__gt=last_available_cutoff)
).annotate(
# Determine general age
age_td=ExpressionWrapper(
now - F("publication_date_time"),
output_field=DurationField(),
),
# Calculate something akin to "day of year" using the ISO calendar
# and take modulo 28 for filtering
standard_day_of_year_modulo=ExpressionWrapper(
Cast(
F("publication_date_time__week") * 7 +
F("publication_date_time__iso_week_day"),
IntegerField(),
) % 28,
output_field=IntegerField(),
),
).filter(
# New episodes every day
Q(age_td__lte=dt.timedelta(days=7))
# In first month every 7 days (roughly, some weekdays might be more busy)
| (Q(age_td__lte=dt.timedelta(days=30))
& Q(publication_date_time__iso_week_day=isocalendar_today[2]))
# After that on every 28th day
| (Q(age_td__gt=dt.timedelta(days=30))
& Q(standard_day_of_year_modulo=(standard_day_of_year_today % 28))))
)
def with_effective_valid_between(self):
"""
There are five ways in which measures can get end dated:
1. Where the measure is given an explicit end date on the measure record
itself
2. Where the measure's generating regulation is a base regulation, and
the base regulation itself is end-dated
3. Where the measure's generating regulation is a modification
regulation, and the modification regulation itself is end-dated
4. Where the measure's generating regulation is a base regulation,
and any of the modification regulations that modify it are end-dated
5. Where the measure's generating regulation is a modification
regulation, and the base regulation that it modifies is end-dated
Numbers 2–5 also have to take account of the "effective end date" which
if set should be used over any explicit date. The effective end date is
set when other types of regulations are used (abrogation, prorogation,
etc).
"""
# Computing the end date for case 4 is expensive because it involves
# aggregating over all of the modifications to the base regulation,
# where there is one. So we pull this out into a CTE to let Postgres
# know that none of this caluclation depends on the queryset filters.
#
# We also turn NULLs into "infinity" such that they sort to the top:
# i.e. if any modification regulation is open-ended, so is the measure.
# We then turn infinity back into NULL to be used in the date range.
Regulation = self.model._meta.get_field(
"generating_regulation", ).remote_field.model
end_date_from_modifications = With(
Regulation.objects.annotate(amended_end_date=NullIf(
Max(
Coalesce(
F("amendments__enacting_regulation__effective_end_date"
),
EndDate(
"amendments__enacting_regulation__valid_between"),
Cast(Value("infinity"), DateField()),
), ),
Cast(Value("infinity"), DateField()),
), ),
"end_date_from_modifications",
)
return (end_date_from_modifications.join(
self,
generating_regulation_id=end_date_from_modifications.col.id,
).with_cte(end_date_from_modifications).annotate(
db_effective_end_date=Coalesce(
# Case 1 – explicit end date, which is always used if present
EndDate("valid_between"),
# Case 2 and 3 – end date of regulation
F("generating_regulation__effective_end_date"),
EndDate("generating_regulation__valid_between"),
# Case 4 – generating regulation is a base regulation, and
# the modification regulation is end-dated
end_date_from_modifications.col.amended_end_date,
# Case 5 – generating regulation is a modification regulation,
# and the base it modifies is end-dated. Note that the above
# means that this only applies if the modification has no end date.
F("generating_regulation__amends__effective_end_date"),
EndDate("generating_regulation__amends__valid_between"),
),
db_effective_valid_between=Func(
StartDate("valid_between"),
F("db_effective_end_date"),
Value("[]"),
function="DATERANGE",
output_field=TaricDateRangeField(),
),
))
def spreadsheet(request: HttpRequest) -> HttpResponse:
queryset = Invoice.objects.filter(student__legit=True)
queryset = queryset.filter(student__semester__active=True)
queryset = queryset.select_related(
'student__user',
'student__semester',
'student__user__profile',
)
queryset = queryset.prefetch_related('student__user__regs')
queryset = queryset.annotate(owed=Cast(
F("student__semester__prep_rate") * F("preps_taught") +
F("student__semester__hour_rate") * F("hours_taught") +
F("adjustment") + F('extras') - F("total_paid"), FloatField()))
queryset = queryset.annotate(
debt=Cast(F("owed") / (F("owed") + F("total_paid") + 1), FloatField()))
queryset = queryset.order_by('debt')
timestamp = timezone.now().strftime('%Y-%m-%d-%H%M%S')
response = HttpResponse(
content_type='text/csv',
headers={
'Content-Disposition':
f'attachment; filename="otis-{timestamp}.csv"'
},
)
writer = csv.writer(response) # type: ignore
writer.writerow([
'ID',
'Username',
'Name',
'Debt',
'Track',
'Login',
'Gender',
'Year',
'Country',
'AoPS',
'Student email',
'Parent email',
'Owed',
'Preps',
'Hours',
'Adjustment',
'Extras',
'Total Paid',
'Forgive',
])
for invoice in queryset:
student = invoice.student
user = student.user
if user is None:
continue
delta = (timezone.now() - user.profile.last_seen)
days_since_last_seen = round(delta.total_seconds() / (3600 * 24),
ndigits=2)
debt_percent = round(invoice.debt * 100)
try:
reg = user.regs.get(container__semester__active=True)
except StudentRegistration.DoesNotExist:
writer.writerow([
student.pk,
student.user.username if student.user is not None else '',
student.name,
debt_percent,
student.track,
days_since_last_seen,
"",
"",
"",
"",
user.email,
"",
invoice.owed,
invoice.preps_taught,
invoice.hours_taught,
invoice.adjustment,
invoice.extras,
invoice.total_paid,
invoice.forgive,
])
else:
writer.writerow([
student.pk,
student.user.username if student.user is not None else '',
student.name,
debt_percent,
student.track,
days_since_last_seen,
reg.gender,
reg.graduation_year,
reg.country,
reg.aops_username,
user.email,
reg.parent_email,
invoice.owed,
invoice.preps_taught,
invoice.hours_taught,
invoice.adjustment,
invoice.extras,
invoice.total_paid,
invoice.forgive,
])
return response