1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
|
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'],
}
|
