from django import forms from django.http import Http404 from .models import Conference from .reporting import reporttypes from postgresqleu.util.forms import GroupedModelMultipleChoiceField _trendlines = ( ('', 'None'), ('linear', 'Linear'), ) class TimeReportForm(forms.Form): reporttype = forms.ChoiceField(required=True, choices=enumerate([r[0] for r in reporttypes], 1), label="Report type") filter = forms.CharField(required=False, label="Filter", help_text="Type part of conference name to filter list") conferences = GroupedModelMultipleChoiceField('series', required=True, queryset=Conference.objects.all().order_by('-startdate')) trendline = forms.ChoiceField(required=False, choices=_trendlines) def __init__(self, user, *args, **kwargs): self.user = user super(TimeReportForm, self).__init__(*args, **kwargs) if not self.user.is_superuser: self.fields['conferences'].queryset = Conference.objects.filter(series__administrators=self.user) self.fields['filter'].widget.attrs.update({ 'data-filter-select': 'id_conferences', }) class QueuePartitionForm(forms.Form): report = forms.CharField(required=True, widget=forms.HiddenInput()) partitions = forms.IntegerField(required=True, label="Number of partitions") def build_query(self, conference): if conference.queuepartitioning == 1: partitionfield = 'lastname' elif conference.queuepartitioning == 2: partitionfield = 'firstname' else: raise Http404("Queue partitioning not enabled for this conference") return """WITH t AS ( SELECT CASE WHEN upper(substring({0}, 1, 1)) BETWEEN 'A' AND 'Z' THEN upper(substring({0}, 1, 1)) ELSE NULL END AS letter, count(*) AS num FROM confreg_conferenceregistration WHERE conference_id=%(confid)s AND payconfirmedat IS NOT NULL AND canceledat IS NULL GROUP BY 1 ), t2 AS ( SELECT letter, num, sum(num) OVER (ORDER BY letter) / sum(num) OVER () AS part FROM t ), g AS ( SELECT gg, gg/(%(partitions)s::double precision) AS ggg from generate_series(1,%(partitions)s) gg(gg) ), t3 as ( SELECT letter, num, part, (SELECT gg FROM g WHERE ggg >= part ORDER BY ggg LIMIT 1) AS bucket FROM t2 ) SELECT string_agg(CASE WHEN letter IS NULL THEN 'Other' ELSE letter END, ', ') AS "Letters", sum(num) AS "Number in partition" FROM t3 GROUP BY bucket order by 1 """.format(partitionfield) def extra_params(self): return { 'partitions': self.cleaned_data['partitions'], }