0001-statistics-add-submission-channel-filter-63376.patch
| tests/api/test_statistics.py | ||
|---|---|---|
| 190 | 190 |
], |
| 191 | 191 |
] |
| 192 | 192 | |
| 193 |
resp = get_app(pub).get(sign_uri('/api/statistics/'))
|
|
| 194 |
form_filter = [x for x in resp.json['data'][0]['filters'] if x['id'] == 'channel'][0] |
|
| 195 |
assert form_filter['options'] == [ |
|
| 196 |
{'id': '_all', 'label': 'All'},
|
|
| 197 |
{'id': 'mail', 'label': 'Mail'},
|
|
| 198 |
{'id': 'email', 'label': 'Email'},
|
|
| 199 |
{'id': 'phone', 'label': 'Phone'},
|
|
| 200 |
{'id': 'counter', 'label': 'Counter'},
|
|
| 201 |
{'id': 'fax', 'label': 'Fax'},
|
|
| 202 |
{'id': 'web', 'label': 'Web'},
|
|
| 203 |
{'id': 'social-network', 'label': 'Social Network'},
|
|
| 204 |
] |
|
| 205 | ||
| 193 | 206 | |
| 194 | 207 |
def test_statistics_index_cards(pub): |
| 195 | 208 |
carddef = CardDef() |
| ... | ... | |
| 253 | 266 |
formdef2.store() |
| 254 | 267 |
formdef2.data_class().wipe() |
| 255 | 268 | |
| 256 |
for _i in range(20):
|
|
| 269 |
for i in range(20): |
|
| 257 | 270 |
formdata = formdef.data_class()() |
| 258 | 271 |
formdata.just_created() |
| 259 | 272 |
formdata.receipt_time = datetime.datetime(2021, 1, 1, 0, 0).timetuple() |
| 273 |
# "Web" channel has three equivalent values |
|
| 274 |
if i == 0: |
|
| 275 |
formdata.submission_channel = 'web' |
|
| 276 |
elif i == 1: |
|
| 277 |
formdata.submission_channel = '' |
|
| 278 |
else: |
|
| 279 |
formdata.submission_channel = None |
|
| 260 | 280 |
formdata.store() |
| 261 | 281 | |
| 262 | 282 |
for _i in range(30): |
| 263 | 283 |
formdata = formdef2.data_class()() |
| 264 | 284 |
formdata.just_created() |
| 265 | 285 |
formdata.receipt_time = datetime.datetime(2021, 3, 1, 2, 0).timetuple() |
| 286 |
formdata.submission_channel = 'mail' |
|
| 266 | 287 |
formdata.store() |
| 267 | 288 | |
| 268 | 289 |
# draft should not be counted |
| ... | ... | |
| 363 | 384 |
'err': 0, |
| 364 | 385 |
} |
| 365 | 386 | |
| 387 |
# apply channel filter |
|
| 388 |
resp = get_app(pub).get(sign_uri('/api/statistics/forms/count/?channel=mail'))
|
|
| 389 |
assert resp.json['data']['series'] == [{'data': [30], 'label': 'Forms Count'}]
|
|
| 390 | ||
| 391 |
resp = get_app(pub).get(sign_uri('/api/statistics/forms/count/?channel=web'))
|
|
| 392 |
assert resp.json['data']['series'] == [{'data': [20], 'label': 'Forms Count'}]
|
|
| 393 | ||
| 394 |
resp = get_app(pub).get(sign_uri('/api/statistics/forms/count/?channel=_all'))
|
|
| 395 |
assert resp.json['data']['series'] == [{'data': [20, 0, 30], 'label': 'Forms Count'}]
|
|
| 396 | ||
| 366 | 397 | |
| 367 | 398 |
def test_statistics_forms_count_subfilters(pub, formdef): |
| 368 | 399 |
for i in range(2): |
| ... | ... | |
| 382 | 413 |
'id': 'group-by', |
| 383 | 414 |
'label': 'Group by', |
| 384 | 415 |
'options': [ |
| 416 |
{'id': 'channel', 'label': 'Channel'},
|
|
| 385 | 417 |
{'id': 'test-item', 'label': 'Test item'},
|
| 386 | 418 |
{'id': 'test-items', 'label': 'Test items'},
|
| 387 | 419 |
{'id': 'checkbox', 'label': 'Checkbox'},
|
| ... | ... | |
| 600 | 632 |
formdata.data['2_display'] = 'Foo' |
| 601 | 633 |
formdata.data['3'] = ['bar', 'baz'] |
| 602 | 634 |
formdata.data['3_display'] = 'Bar, Baz' |
| 635 |
# "Web" channel has three equivalent values |
|
| 636 |
if i == 1: |
|
| 637 |
formdata.submission_channel = 'web' |
|
| 638 |
elif i == 2: |
|
| 639 |
formdata.submission_channel = '' |
|
| 640 |
else: |
|
| 641 |
formdata.submission_channel = None |
|
| 603 | 642 |
elif i % 2: |
| 604 | 643 |
formdata.data['1'] = False |
| 605 | 644 |
formdata.data['2'] = 'baz' |
| 606 | 645 |
formdata.data['3'] = ['baz'] |
| 607 | 646 |
formdata.jump_status('2')
|
| 647 |
formdata.submission_channel = 'mail' |
|
| 608 | 648 |
else: |
| 609 | 649 |
formdata.receipt_time = datetime.datetime(2021, 3, 1, 2, 0).timetuple() |
| 610 | 650 |
formdata.store() |
| ... | ... | |
| 670 | 710 |
{'data': [13, None, 4], 'label': 'New status'},
|
| 671 | 711 |
] |
| 672 | 712 | |
| 713 |
# group by channel |
|
| 714 |
resp = get_app(pub).get(sign_uri(url + '&group-by=channel')) |
|
| 715 |
assert resp.json['data']['x_labels'] == ['2021-01', '2021-02', '2021-03'] |
|
| 716 |
assert resp.json['data']['series'] == [ |
|
| 717 |
{'data': [3, None, None], 'label': 'Mail'},
|
|
| 718 |
{'data': [13, None, 4], 'label': 'Web'},
|
|
| 719 |
] |
|
| 720 | ||
| 673 | 721 |
# group by item field without time interval |
| 674 | 722 |
resp = get_app(pub).get(sign_uri(url + '&group-by=test-item&time_interval=none')) |
| 675 | 723 |
assert resp.json['data']['x_labels'] == ['baz', 'Foo', 'None'] |
| 676 | 724 |
assert resp.json['data']['series'] == [{'data': [3, 13, 4], 'label': 'Forms Count'}]
|
| 677 | 725 | |
| 726 |
# group by submission channel without time interval |
|
| 727 |
resp = get_app(pub).get(sign_uri(url + '&group-by=channel&time_interval=none')) |
|
| 728 |
assert resp.json['data']['x_labels'] == ['Mail', 'Web'] |
|
| 729 |
assert resp.json['data']['series'] == [{'data': [3, 17], 'label': 'Forms Count'}]
|
|
| 730 | ||
| 678 | 731 |
# group by on block field is not supported |
| 679 | 732 |
resp = get_app(pub).get(sign_uri(url + '&group-by=blockdata_bool')) |
| 680 | 733 |
assert resp.json['data']['series'] == [{'data': [16, 0, 4], 'label': 'Forms Count'}]
|
| wcs/sql.py | ||
|---|---|---|
| 4162 | 4162 |
return statement |
| 4163 | 4163 | |
| 4164 | 4164 | |
| 4165 |
def get_time_aggregate_query(time_interval, query, group_by, function='DATE_TRUNC'): |
|
| 4165 |
def get_time_aggregate_query(time_interval, query, group_by, function='DATE_TRUNC', null_values=None):
|
|
| 4166 | 4166 |
statement = f"SELECT {function}('{time_interval}', receipt_time) AS {time_interval}, "
|
| 4167 | 4167 |
if group_by: |
| 4168 |
statement += '%s, ' % group_by |
|
| 4168 |
if null_values: |
|
| 4169 |
statement += ( |
|
| 4170 |
f'CASE WHEN {group_by} IN {null_values} THEN null ELSE {group_by} END as {group_by}, '
|
|
| 4171 |
) |
|
| 4172 |
else: |
|
| 4173 |
statement += '%s, ' % group_by |
|
| 4169 | 4174 |
statement += 'COUNT(*) ' |
| 4170 | 4175 |
statement += query |
| 4171 | 4176 | |
| ... | ... | |
| 4220 | 4225 | |
| 4221 | 4226 | |
| 4222 | 4227 |
@guard_postgres |
| 4223 |
def get_weekday_totals(period_start=None, period_end=None, criterias=None, group_by=None): |
|
| 4228 |
def get_weekday_totals(period_start=None, period_end=None, criterias=None, group_by=None, null_values=None):
|
|
| 4224 | 4229 |
conn, cur = get_connection_and_cursor() |
| 4225 | 4230 |
parameters = {}
|
| 4226 | 4231 |
statement = get_period_query( |
| 4227 | 4232 |
period_start=period_start, period_end=period_end, criterias=criterias, parameters=parameters |
| 4228 | 4233 |
) |
| 4229 |
statement = get_time_aggregate_query('dow', statement, group_by, function='DATE_PART')
|
|
| 4234 |
statement = get_time_aggregate_query( |
|
| 4235 |
'dow', statement, group_by, function='DATE_PART', null_values=null_values |
|
| 4236 |
) |
|
| 4230 | 4237 |
cur.execute(statement, parameters) |
| 4231 | 4238 | |
| 4232 | 4239 |
result = cur.fetchall() |
| ... | ... | |
| 4278 | 4285 | |
| 4279 | 4286 | |
| 4280 | 4287 |
@guard_postgres |
| 4281 |
def get_global_totals(period_start=None, period_end=None, criterias=None, group_by=None): |
|
| 4288 |
def get_global_totals(period_start=None, period_end=None, criterias=None, group_by=None, null_values=None):
|
|
| 4282 | 4289 |
conn, cur = get_connection_and_cursor() |
| 4283 | 4290 |
statement = 'SELECT ' |
| 4284 | 4291 |
if group_by: |
| 4285 |
statement += f'{group_by}, '
|
|
| 4292 |
if null_values: |
|
| 4293 |
statement += ( |
|
| 4294 |
f'CASE WHEN {group_by} IN {null_values} THEN null ELSE {group_by} END as {group_by}_new, '
|
|
| 4295 |
) |
|
| 4296 |
group_by += '_new' |
|
| 4297 |
else: |
|
| 4298 |
statement += f'{group_by}, '
|
|
| 4286 | 4299 |
statement += 'COUNT(*) ' |
| 4287 | 4300 | |
| 4288 | 4301 |
parameters = {}
|
| ... | ... | |
| 4304 | 4317 | |
| 4305 | 4318 | |
| 4306 | 4319 |
@guard_postgres |
| 4307 |
def get_hour_totals(period_start=None, period_end=None, criterias=None, group_by=None): |
|
| 4320 |
def get_hour_totals(period_start=None, period_end=None, criterias=None, group_by=None, null_values=None):
|
|
| 4308 | 4321 |
conn, cur = get_connection_and_cursor() |
| 4309 | 4322 |
parameters = {}
|
| 4310 | 4323 |
statement = get_period_query( |
| 4311 | 4324 |
period_start=period_start, period_end=period_end, criterias=criterias, parameters=parameters |
| 4312 | 4325 |
) |
| 4313 |
statement = get_time_aggregate_query('hour', statement, group_by, function='DATE_PART')
|
|
| 4326 |
statement = get_time_aggregate_query( |
|
| 4327 |
'hour', statement, group_by, function='DATE_PART', null_values=null_values |
|
| 4328 |
) |
|
| 4314 | 4329 |
cur.execute(statement, parameters) |
| 4315 | 4330 | |
| 4316 | 4331 |
result = cur.fetchall() |
| ... | ... | |
| 4334 | 4349 |
period_end=None, |
| 4335 | 4350 |
criterias=None, |
| 4336 | 4351 |
group_by=None, |
| 4352 |
null_values=None, |
|
| 4337 | 4353 |
): |
| 4338 | 4354 |
conn, cur = get_connection_and_cursor() |
| 4339 | 4355 |
parameters = {}
|
| 4340 | 4356 |
statement = get_period_query( |
| 4341 | 4357 |
period_start=period_start, period_end=period_end, criterias=criterias, parameters=parameters |
| 4342 | 4358 |
) |
| 4343 |
statement = get_time_aggregate_query('month', statement, group_by)
|
|
| 4359 |
statement = get_time_aggregate_query('month', statement, group_by, null_values=null_values)
|
|
| 4344 | 4360 |
cur.execute(statement, parameters) |
| 4345 | 4361 | |
| 4346 | 4362 |
raw_result = cur.fetchall() |
| ... | ... | |
| 4364 | 4380 | |
| 4365 | 4381 | |
| 4366 | 4382 |
@guard_postgres |
| 4367 |
def get_yearly_totals(period_start=None, period_end=None, criterias=None, group_by=None): |
|
| 4383 |
def get_yearly_totals(period_start=None, period_end=None, criterias=None, group_by=None, null_values=None):
|
|
| 4368 | 4384 |
conn, cur = get_connection_and_cursor() |
| 4369 | 4385 |
parameters = {}
|
| 4370 | 4386 |
statement = get_period_query( |
| 4371 | 4387 |
period_start=period_start, period_end=period_end, criterias=criterias, parameters=parameters |
| 4372 | 4388 |
) |
| 4373 |
statement = get_time_aggregate_query('year', statement, group_by)
|
|
| 4389 |
statement = get_time_aggregate_query('year', statement, group_by, null_values=null_values)
|
|
| 4374 | 4390 |
cur.execute(statement, parameters) |
| 4375 | 4391 | |
| 4376 | 4392 |
raw_result = cur.fetchall() |
| wcs/statistics/views.py | ||
|---|---|---|
| 26 | 26 |
from wcs.backoffice.management import FormPage |
| 27 | 27 |
from wcs.carddef import CardDef |
| 28 | 28 |
from wcs.categories import Category |
| 29 |
from wcs.formdata import FormData |
|
| 29 | 30 |
from wcs.formdef import FormDef |
| 30 | 31 |
from wcs.qommon import _, misc, pgettext_lazy |
| 31 |
from wcs.qommon.storage import Contains, Equal, StrictNotEqual |
|
| 32 |
from wcs.qommon.storage import Contains, Equal, Null, Or, StrictNotEqual
|
|
| 32 | 33 | |
| 33 | 34 | |
| 34 | 35 |
class RestrictedView(View): |
| ... | ... | |
| 45 | 46 |
category_options = [{'id': '_all', 'label': pgettext_lazy('categories', 'All')}] + [
|
| 46 | 47 |
{'id': x.url_name, 'label': x.name} for x in categories
|
| 47 | 48 |
] |
| 49 |
channel_options = [{'id': '_all', 'label': pgettext_lazy('channel', 'All')}] + [
|
|
| 50 |
{'id': key, 'label': label} for key, label in FormData.get_submission_channels().items()
|
|
| 51 |
] |
|
| 48 | 52 |
return JsonResponse( |
| 49 | 53 |
{
|
| 50 | 54 |
'data': [ |
| ... | ... | |
| 81 | 85 |
'required': True, |
| 82 | 86 |
'default': 'month', |
| 83 | 87 |
}, |
| 88 |
{
|
|
| 89 |
'id': 'channel', |
|
| 90 |
'label': _('Channel'),
|
|
| 91 |
'options': channel_options, |
|
| 92 |
'required': True, |
|
| 93 |
'default': '_all', |
|
| 94 |
}, |
|
| 84 | 95 |
{
|
| 85 | 96 |
'id': 'category', |
| 86 | 97 |
'label': _('Category'),
|
| ... | ... | |
| 234 | 245 |
else: |
| 235 | 246 |
totals_kwargs['criterias'].append(Equal('category_id', category.id))
|
| 236 | 247 | |
| 248 |
channel = request.GET.get('channel', '_all')
|
|
| 249 |
if channel == 'web': |
|
| 250 |
totals_kwargs['criterias'].append( |
|
| 251 |
Or( |
|
| 252 |
[ |
|
| 253 |
Equal('submission_channel', 'web'),
|
|
| 254 |
Equal('submission_channel', ''),
|
|
| 255 |
Null('submission_channel'),
|
|
| 256 |
] |
|
| 257 |
) |
|
| 258 |
) |
|
| 259 |
elif channel != '_all': |
|
| 260 |
totals_kwargs['criterias'].append(Equal('submission_channel', channel))
|
|
| 261 | ||
| 237 | 262 |
time_interval_methods = {
|
| 238 | 263 |
'month': sql.get_monthly_totals, |
| 239 | 264 |
'year': sql.get_yearly_totals, |
| ... | ... | |
| 333 | 358 |
{
|
| 334 | 359 |
'id': 'group-by', |
| 335 | 360 |
'label': _('Group by'),
|
| 336 |
'options': [{'id': x[0], 'label': x[1]} for x in field_choices],
|
|
| 361 |
'options': [{'id': 'channel', 'label': _('Channel')}]
|
|
| 362 |
+ [{'id': x[0], 'label': x[1]} for x in field_choices],
|
|
| 337 | 363 |
}, |
| 338 | 364 |
) |
| 339 | 365 | |
| ... | ... | |
| 366 | 392 |
if not group_by: |
| 367 | 393 |
return |
| 368 | 394 | |
| 395 |
if group_by == 'channel': |
|
| 396 |
totals_kwargs['group_by'] = 'submission_channel' |
|
| 397 |
totals_kwargs['null_values'] = ('web', '')
|
|
| 398 | ||
| 399 |
group_labels.update(FormData.get_submission_channels()) |
|
| 400 |
group_labels[None] = _('Web')
|
|
| 401 |
group_labels[''] = _('Web')
|
|
| 402 |
return |
|
| 403 | ||
| 369 | 404 |
group_by_field = self.get_group_by_field(form_page, group_by) |
| 370 | 405 |
if not group_by_field: |
| 371 | 406 |
return |
| 372 |
- |
|