Project

General

Profile

0001-statistics-add-submission-channel-filter-63376.patch

Valentin Deniaud, 08 November 2022 01:56 PM

Download (13.3 KB)

View differences:

Subject: [PATCH] statistics: add submission channel filter (#63376)

 tests/api/test_statistics.py | 55 +++++++++++++++++++++++++++++++++++-
 wcs/sql.py                   | 38 +++++++++++++++++--------
 wcs/statistics/views.py      | 39 +++++++++++++++++++++++--
 3 files changed, 118 insertions(+), 14 deletions(-)
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
-